coleifer / sqlite-vtfunc Goto Github PK

View Code? Open in Web Editor NEW

56.0 5.0 7.0 182 KB

Implement SQLite table-valued functions with Python

License: MIT License

Python 24.14% C 23.23% Cython 52.63%

virtual-tables python sqlite sql cython

sqlite-vtfunc's People

Contributors

Stargazers

Watchers

Forkers

pombredanne giserh geperez-r7 gaybro8777 ra2003 ouseful-backup sweat-tiger

sqlite-vtfunc's Issues

Here's what I've been building

I want to play with table functions for Datasette plugins. Here's what I've got so far: https://github.com/simonw/datasette-sql-scraper

And here's a live demo: https://datasette-sql-scraper-demo.now.sh/fixtures-fda0fea?sql=select+*+from+scraper%28%22http%3A%2F%2Fwww.example.com%2F%22%29

https://datasette-sql-scraper-demo.now.sh/fixtures-fda0fea?sql=select+*+from+scraper%28%22http%3A%2F%2Fnews.ycombinator.com%2F%22%29+order+by+length%28description%29+desc

Still need to figure out the best way of further developing this. Biggest problem at the moment is that I'm getting SQL logic error in many situations e.g. https://datasette-sql-scraper-demo.now.sh/fixtures-fda0fea?sql=select+*+from+scraper%28%22https%3A%2F%2Fsimonwillison.net%2F%22%29 and I don't know the best way to debug why.

Consider merging this functionality into pysqlite

Hi,
I found your pysqlite extension hugely useful as I could have a dbapi2 sqlite driver to use with SQLAlchemy and have control over all of the goodies compiled in from the amalgamation. Thanks for that. I find the backup API particularly useful (a common pattern is to do a bunch of stuff in a :memory: database and then back that up to persistent storage if everything goes OK).

What do you think of merging the virtual table functionality in this package into pysqlite? I think that would be hugely useful and am quite happy to slog through the details of getting it working but want to check with you first what you think of the idea.

thanks in advance,
pjjH

Install on Mac 12.5.1 / Py 3.10.6 Fails

Trying to install vtfunc`` on Mac 12.5.1 / Py 3.10.6 fails in the wheel build if Cython` is not installed (presumably the file shipped as a workaround has rotted in some way?).

Ensuring that Cython is installed fixes the issue.

vtfunc.c is missing so setup.py install fails

b22cf79 deleted vtfunc.c - was this a mistake?

I couldn't run python setup.py install without it. I downloaded a copy of vtfunc.c manually and ran python setup.py install again and it worked just fine. Here's the full transcript of my successful installation process:

$ python3 -mvenv vtfunc-demo
$ source vtfunc-demo/bin/activate
(vtfunc-demo) tmp $ cd vtfunc-demo/
(vtfunc-demo) vtfunc-demo $ wget https://github.com/coleifer/sqlite-vtfunc/archive/master.zip
(vtfunc-demo) vtfunc-demo $ unzip master.zip 
(vtfunc-demo) vtfunc-demo $ cd sqlite-vtfunc-master/
(vtfunc-demo) sqlite-vtfunc-master $ wget https://raw.githubusercontent.com/coleifer/sqlite-vtfunc/5e4a0fcad386ce4b194654e3f02b3d92873f9cef/vtfunc.c
(vtfunc-demo) sqlite-vtfunc-master $ python setup.py install
setup.py:10: UserWarning: Cython not installed, using pre-generated C source file.
  warnings.warn('Cython not installed, using pre-generated C source file.')
/usr/local/Cellar/python/3.6.5/Frameworks/Python.framework/Versions/3.6/lib/python3.6/distutils/dist.py:261: UserWarning: Unknown distribution option: 'setup_requires'
  warnings.warn(msg)
/usr/local/Cellar/python/3.6.5/Frameworks/Python.framework/Versions/3.6/lib/python3.6/distutils/dist.py:261: UserWarning: Unknown distribution option: 'dependency_links'
  warnings.warn(msg)
running install
running build
running build_ext
building 'vtfunc' extension
creating build
creating build/temp.macosx-10.13-x86_64-3.6
clang -Wno-unused-result -Wsign-compare -Wunreachable-code -fno-common -dynamic -DNDEBUG -g -fwrapv -O3 -Wall -Wstrict-prototypes -DMODULE_NAME="vtfunc" -I/usr/local/include -I/usr/local/opt/openssl/include -I/usr/local/opt/sqlite/include -I/private/tmp/vtfunc-demo/include -I/usr/local/Cellar/python/3.6.5/Frameworks/Python.framework/Versions/3.6/include/python3.6m -c vtfunc.c -o build/temp.macosx-10.13-x86_64-3.6/vtfunc.o
vtfunc.c:3898:25: warning: incompatible pointer types assigning to 'struct __pyx_t_6vtfunc_sqlite3_index_constraint *' from 'struct sqlite3_index_constraint *'
      [-Wincompatible-pointer-types]
    __pyx_v_pConstraint = (&(__pyx_v_pIdxInfo->aConstraint[__pyx_v_i]));
                        ^ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
vtfunc.c:4402:33: warning: incompatible pointer types assigning to 'int (*)(sqlite3 *, void *, int, const char *const *, sqlite3_vtab **, char **)' (aka 'int
      (*)(struct sqlite3 *, void *, int, const char *const *, struct sqlite3_vtab **, char **)') from
      'int (sqlite3 *, void *, int, char **, sqlite3_vtab **, char **)' (aka 'int (struct sqlite3 *, void *, int, char **, struct sqlite3_vtab **, char **)')
      [-Wincompatible-pointer-types]
  __pyx_v_self->module.xConnect = __pyx_f_6vtfunc_pwConnect;
                                ^ ~~~~~~~~~~~~~~~~~~~~~~~~~
vtfunc.c:5486:79: warning: cast to 'void *' from smaller integer type 'int' [-Wint-to-void-pointer-cast]
  sqlite3_busy_handler(__pyx_v_db, __pyx_f_6vtfunc__aggressive_busy_handler, ((void *)__pyx_v_n));
                                                                              ^
3 warnings generated.
creating build/lib.macosx-10.13-x86_64-3.6
clang -bundle -undefined dynamic_lookup build/temp.macosx-10.13-x86_64-3.6/vtfunc.o -L/usr/local/lib -L/usr/local/opt/openssl/lib -L/usr/local/opt/sqlite/lib -lsqlite3 -o build/lib.macosx-10.13-x86_64-3.6/vtfunc.cpython-36m-darwin.so
running install_lib
copying build/lib.macosx-10.13-x86_64-3.6/vtfunc.cpython-36m-darwin.so -> /private/tmp/vtfunc-demo/lib/python3.6/site-packages
running install_egg_info
Writing /private/tmp/vtfunc-demo/lib/python3.6/site-packages/vtfunc-0.3.2-py3.6.egg-info
(vtfunc-demo) sqlite-vtfunc-master $ python
Python 3.6.5 (default, Mar 30 2018, 06:41:53) 
[GCC 4.2.1 Compatible Apple LLVM 9.0.0 (clang-900.0.39.2)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import vtfunc
>>> vtfunc
<module 'vtfunc' from '/private/tmp/vtfunc-demo/lib/python3.6/site-packages/vtfunc.cpython-36m-darwin.so'>
>>>

Document the minimum required version of SQLite

This module seems to work with SQLite 3.23.1 ( https://datasette-sql-scraper-demo.now.sh/-/versions ) but throws errors when run against 3.8.7.1:

https://datasette-sql-scraper-broken-on-older-version.now.sh/fixtures-fda0fea?sql=select+*+from+scraper%28%22http%3A%2F%2Fwww.example.com%2F%22%29

Invalid SQL: near "(": syntax error

Version shown here: https://datasette-sql-scraper-broken-on-older-version.now.sh/-/versions

It would be useful to document the minimum required version of SQLite for this to work.

Feature request: if possible, support variable column definitions?

Would it be possible to allow virtual table functions to dynamically decide the columns that they are planning to return based on their input?

I'm assuming this is impossible (if so, please close this issue) - based on my very loose understanding of how SQLite virtual tables work and this bit of code here:

sqlite-vtfunc/vtfunc.pyx

Lines 210 to 213 in 411c411

 rc = sqlite3_declare_vtab( 

 db, 

 encode('CREATE TABLE x(%s);' % 

 table_func_cls.get_table_columns_declaration()))

The reason I want this is that I'd like to build a fetch_json_array(url) function which can fetch data from a JSON API that returns data something like like this:

[
  {"id": 4, "name": "Bob", "age": 34},
  {"id": 6, "name": "Karen", "age": 37}
]

Ideally I'd be able to do the following:

select * from fetch_json_array("http://example.com/users.json")

And get back a table like this:

id	name	age
4	Bob	34
6	Karen	37

That's one column for each of the properties in the returned JSON.

But... vtfunc definitions are required to hard-code the list of columns - so this isn't currently possible.

If this DOES turn out to be impossible then I'll probably work around this using features from SQLite's json1 module, but it would be fantastic if there was a way to dynamically calculate columns in this way.

Looking for sqlite.h

Hi,

Python 3.9.1 (tags/v3.9.1:1e5d33e, Dec 7 2020, 17:08:21) [MSC v.1927 64 bit (AMD64)] on win32
Cython and
C++ Build Tools installed.

Then it appears to be looking for sqlite.h

Can you advise?

David

C:\Users\david>pip install vtfunc
Collecting vtfunc
  Using cached vtfunc-0.4.0.tar.gz (70 kB)
Building wheels for collected packages: vtfunc
  Building wheel for vtfunc (setup.py) ... error
  ERROR: Command errored out with exit status 1:
   command: 'c:\users\david\appdata\local\programs\python\python39\python.exe' -u -c 'import sys, setuptools, tokenize; sys.argv[0] = '"'"'C:\\Users\\david\\AppData\\Local\\Temp\\pip-install-09_uz0xr\\vtfunc_50b60fa58fd44c1bac627fac6a5eef0c\\setup.py'"'"'; __file__='"'"'C:\\Users\\david\\AppData\\Local\\Temp\\pip-install-09_uz0xr\\vtfunc_50b60fa58fd44c1bac627fac6a5eef0c\\setup.py'"'"';f=getattr(tokenize, '"'"'open'"'"', open)(__file__);code=f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' bdist_wheel -d 'C:\Users\david\AppData\Local\Temp\pip-wheel-2lqq8y2w'
       cwd: C:\Users\david\AppData\Local\Temp\pip-install-09_uz0xr\vtfunc_50b60fa58fd44c1bac627fac6a5eef0c\
  Complete output (11 lines):
  running bdist_wheel
  running build
  running build_ext
  building 'vtfunc' extension
  creating build
  creating build\temp.win-amd64-3.9
  creating build\temp.win-amd64-3.9\Release
  C:\Program Files (x86)\Microsoft Visual Studio\2019\BuildTools\VC\Tools\MSVC\14.28.29910\bin\HostX86\x64\cl.exe /c /nologo /Ox /W3 /GL /DNDEBUG /MD -DMODULE_NAME="vtfunc" -Ic:\users\david\appdata\local\programs\python\python39\include -Ic:\users\david\appdata\local\programs\python\python39\include -IC:\Program Files (x86)\Microsoft Visual Studio\2019\BuildTools\VC\Tools\MSVC\14.28.29910\include -IC:\Program Files (x86)\Windows Kits\10\include\10.0.19041.0\ucrt -IC:\Program Files (x86)\Windows Kits\10\include\10.0.19041.0\shared -IC:\Program Files (x86)\Windows Kits\10\include\10.0.19041.0\um -IC:\Program Files (x86)\Windows Kits\10\include\10.0.19041.0\winrt -IC:\Program Files (x86)\Windows Kits\10\include\10.0.19041.0\cppwinrt /Tcvtfunc.c /Fobuild\temp.win-amd64-3.9\Release\vtfunc.obj
  vtfunc.c
  vtfunc.c(636): fatal error C1083: Cannot open include file: 'sqlite3.h': No such file or directory
  error: command 'C:\\Program Files (x86)\\Microsoft Visual Studio\\2019\\BuildTools\\VC\\Tools\\MSVC\\14.28.29910\\bin\\HostX86\\x64\\cl.exe' failed with exit code 2
  ----------------------------------------
  ERROR: Failed building wheel for vtfunc
  Running setup.py clean for vtfunc
Failed to build vtfunc
Installing collected packages: vtfunc
    Running setup.py install for vtfunc ... error
    ERROR: Command errored out with exit status 1:
     command: 'c:\users\david\appdata\local\programs\python\python39\python.exe' -u -c 'import sys, setuptools, tokenize; sys.argv[0] = '"'"'C:\\Users\\david\\AppData\\Local\\Temp\\pip-install-09_uz0xr\\vtfunc_50b60fa58fd44c1bac627fac6a5eef0c\\setup.py'"'"'; __file__='"'"'C:\\Users\\david\\AppData\\Local\\Temp\\pip-install-09_uz0xr\\vtfunc_50b60fa58fd44c1bac627fac6a5eef0c\\setup.py'"'"';f=getattr(tokenize, '"'"'open'"'"', open)(__file__);code=f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' install --record 'C:\Users\david\AppData\Local\Temp\pip-record-x8yuy7pv\install-record.txt' --single-version-externally-managed --compile --install-headers 'c:\users\david\appdata\local\programs\python\python39\Include\vtfunc'
         cwd: C:\Users\david\AppData\Local\Temp\pip-install-09_uz0xr\vtfunc_50b60fa58fd44c1bac627fac6a5eef0c\
    Complete output (11 lines):
    running install
    running build
    running build_ext
    building 'vtfunc' extension
    creating build
    creating build\temp.win-amd64-3.9
    creating build\temp.win-amd64-3.9\Release
    C:\Program Files (x86)\Microsoft Visual Studio\2019\BuildTools\VC\Tools\MSVC\14.28.29910\bin\HostX86\x64\cl.exe /c /nologo /Ox /W3 /GL /DNDEBUG /MD -DMODULE_NAME="vtfunc" -Ic:\users\david\appdata\local\programs\python\python39\include -Ic:\users\david\appdata\local\programs\python\python39\include -IC:\Program Files (x86)\Microsoft Visual Studio\2019\BuildTools\VC\Tools\MSVC\14.28.29910\include -IC:\Program Files (x86)\Windows Kits\10\include\10.0.19041.0\ucrt -IC:\Program Files (x86)\Windows Kits\10\include\10.0.19041.0\shared -IC:\Program Files (x86)\Windows Kits\10\include\10.0.19041.0\um -IC:\Program Files (x86)\Windows Kits\10\include\10.0.19041.0\winrt -IC:\Program Files (x86)\Windows Kits\10\include\10.0.19041.0\cppwinrt /Tcvtfunc.c /Fobuild\temp.win-amd64-3.9\Release\vtfunc.obj
    vtfunc.c
    vtfunc.c(636): fatal error C1083: Cannot open include file: 'sqlite3.h': No such file or directory
    error: command 'C:\\Program Files (x86)\\Microsoft Visual Studio\\2019\\BuildTools\\VC\\Tools\\MSVC\\14.28.29910\\bin\\HostX86\\x64\\cl.exe' failed with exit code 2
    ----------------------------------------
ERROR: Command errored out with exit status 1: 'c:\users\david\appdata\local\programs\python\python39\python.exe' -u -c 'import sys, setuptools, tokenize; sys.argv[0] = '"'"'C:\\Users\\david\\AppData\\Local\\Temp\\pip-install-09_uz0xr\\vtfunc_50b60fa58fd44c1bac627fac6a5eef0c\\setup.py'"'"'; __file__='"'"'C:\\Users\\david\\AppData\\Local\\Temp\\pip-install-09_uz0xr\\vtfunc_50b60fa58fd44c1bac627fac6a5eef0c\\setup.py'"'"';f=getattr(tokenize, '"'"'open'"'"', open)(__file__);code=f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' install --record 'C:\Users\david\AppData\Local\Temp\pip-record-x8yuy7pv\install-record.txt' --single-version-externally-managed --compile --install-headers 'c:\users\david\appdata\local\programs\python\python39\Include\vtfunc' Check the logs for full command output.

Segfaults on OS X when calling Scraper.register(conn)

This is really weird. In #3 I reported that everything was working fine for me. Now I'm getting a setfault every time I try to run register(). Here's a sample session:

Python 3.6.5 (default, Mar 30 2018, 06:41:53) 
[GCC 4.2.1 Compatible Apple LLVM 9.0.0 (clang-900.0.39.2)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import re, requests, sqlite3
>>> from vtfunc import TableFunction
>>> 
>>> class Scraper(TableFunction):
...     params = ['url']  # Function argument names.
...     columns = ['href', 'description']  # Result rows have these columns.
...     name = 'scraper'  # Name we use to invoke the function from SQL.
...     def initialize(self, url):
...         # Note that this is bytes:
...         self.html = requests.get(url).content
...         self._iter = re.finditer(
...             '<a[^\>]+?href="([^\"]+?)"[^\>]*?>([^\<]+?)</a>',
...             self.html)
...     def iterate(self, idx):
...         return next(self._iter).groups()
... 
>>> conn = sqlite3.connect(':memory:')
>>> Scraper.register(conn)
Segmentation fault: 11

The OS X crash reporter kicks in at this point, which means I can grab the following stack trace:

Process:               Python [44252]
Path:                  /usr/local/Cellar/python/3.6.5/Frameworks/Python.framework/Versions/3.6/Resources/Python.app/Contents/MacOS/Python
Identifier:            Python
Version:               3.6.5 (3.6.5)
Code Type:             X86-64 (Native)
Parent Process:        bash [27024]
Responsible:           Python [44252]
User ID:               502

Crashed Thread:        0  Dispatch queue: com.apple.main-thread

Exception Type:        EXC_BAD_ACCESS (SIGSEGV)
Exception Codes:       KERN_INVALID_ADDRESS at 0x0000000000000000
Exception Note:        EXC_CORPSE_NOTIFY

Termination Signal:    Segmentation fault: 11
Termination Reason:    Namespace SIGNAL, Code 0xb
Terminating Process:   exc handler [0]

VM Regions Near 0:
--> 
    __TEXT                 000000010e135000-000000010e137000 [    8K] r-x/rwx SM=COW  /usr/local/Cellar/python/3.6.5/Frameworks/Python.framework/Versions/3.6/Resources/Python.app/Contents/MacOS/Python

Thread 0 Crashed:: Dispatch queue: com.apple.main-thread
0   ???                           	000000000000000000 0 + 0
1   libsqlite3.dylib              	0x00007fff7db348b0 createModule + 80
2   vtfunc.cpython-36m-darwin.so  	0x0000000110115cbc __pyx_f_6vtfunc_18_TableFunctionImpl_create_module + 364
3   vtfunc.cpython-36m-darwin.so  	0x000000011011b189 __pyx_pw_6vtfunc_13TableFunction_1register + 169
4   org.python.python             	0x000000010e143f9c _PyObject_FastCallDict + 143
5   org.python.python             	0x000000010e1e0356 call_function + 441
6   org.python.python             	0x000000010e1d923b _PyEval_EvalFrameDefault + 4811
7   org.python.python             	0x000000010e1e0ae8 _PyEval_EvalCodeWithName + 1719
8   org.python.python             	0x000000010e1d7f35 PyEval_EvalCodeEx + 57
9   org.python.python             	0x000000010e1641c4 function_call + 339
10  org.python.python             	0x000000010e143e52 PyObject_Call + 101
11  org.python.python             	0x000000010e1d94a4 _PyEval_EvalFrameDefault + 5428
12  org.python.python             	0x000000010e1e0ae8 _PyEval_EvalCodeWithName + 1719
13  org.python.python             	0x000000010e1e11eb fast_function + 218
14  org.python.python             	0x000000010e1e035d call_function + 448
15  org.python.python             	0x000000010e1d92d4 _PyEval_EvalFrameDefault + 4964
16  org.python.python             	0x000000010e1e0ae8 _PyEval_EvalCodeWithName + 1719
17  org.python.python             	0x000000010e1e11eb fast_function + 218
18  org.python.python             	0x000000010e1e035d call_function + 448
19  org.python.python             	0x000000010e1d923b _PyEval_EvalFrameDefault + 4811
20  org.python.python             	0x000000010e1e148b _PyFunction_FastCall + 121
21  org.python.python             	0x000000010e1e035d call_function + 448
22  org.python.python             	0x000000010e1d923b _PyEval_EvalFrameDefault + 4811
23  org.python.python             	0x000000010e1e0ae8 _PyEval_EvalCodeWithName + 1719
24  org.python.python             	0x000000010e1e13bb _PyFunction_FastCallDict + 449
25  org.python.python             	0x000000010e143fd1 _PyObject_FastCallDict + 196
26  org.python.python             	0x000000010e1440f4 _PyObject_Call_Prepend + 156
27  org.python.python             	0x000000010e143e52 PyObject_Call + 101
28  org.python.python             	0x000000010e18e510 slot_tp_call + 50
29  org.python.python             	0x000000010e143f9c _PyObject_FastCallDict + 143
30  org.python.python             	0x000000010e144355 _PyObject_FastCallKeywords + 97
31  org.python.python             	0x000000010e1e0356 call_function + 441
32  org.python.python             	0x000000010e1d92d4 _PyEval_EvalFrameDefault + 4964
33  org.python.python             	0x000000010e1e148b _PyFunction_FastCall + 121
34  org.python.python             	0x000000010e1e035d call_function + 448
35  org.python.python             	0x000000010e1d923b _PyEval_EvalFrameDefault + 4811
36  org.python.python             	0x000000010e1e148b _PyFunction_FastCall + 121
37  org.python.python             	0x000000010e1e035d call_function + 448
38  org.python.python             	0x000000010e1d923b _PyEval_EvalFrameDefault + 4811
39  org.python.python             	0x000000010e1e0ae8 _PyEval_EvalCodeWithName + 1719
40  org.python.python             	0x000000010e1d7f35 PyEval_EvalCodeEx + 57
41  org.python.python             	0x000000010e1641c4 function_call + 339
42  org.python.python             	0x000000010e143e52 PyObject_Call + 101
43  org.python.python             	0x000000010e1d94a4 _PyEval_EvalFrameDefault + 5428
44  org.python.python             	0x000000010e1e0ae8 _PyEval_EvalCodeWithName + 1719
45  org.python.python             	0x000000010e1e13bb _PyFunction_FastCallDict + 449
46  org.python.python             	0x000000010e143fd1 _PyObject_FastCallDict + 196
47  org.python.python             	0x000000010e1440f4 _PyObject_Call_Prepend + 156
48  org.python.python             	0x000000010e143e52 PyObject_Call + 101
49  org.python.python             	0x000000010e1d94a4 _PyEval_EvalFrameDefault + 5428
50  org.python.python             	0x000000010e1e148b _PyFunction_FastCall + 121
51  org.python.python             	0x000000010e1e035d call_function + 448
52  org.python.python             	0x000000010e1d923b _PyEval_EvalFrameDefault + 4811
53  org.python.python             	0x000000010e1e0ae8 _PyEval_EvalCodeWithName + 1719
54  org.python.python             	0x000000010e1e11eb fast_function + 218
55  org.python.python             	0x000000010e1e035d call_function + 448
56  org.python.python             	0x000000010e1d923b _PyEval_EvalFrameDefault + 4811
57  org.python.python             	0x000000010e1e0ae8 _PyEval_EvalCodeWithName + 1719
58  org.python.python             	0x000000010e1e13bb _PyFunction_FastCallDict + 449
59  org.python.python             	0x000000010e143fd1 _PyObject_FastCallDict + 196
60  org.python.python             	0x000000010e1440f4 _PyObject_Call_Prepend + 156
61  org.python.python             	0x000000010e143e52 PyObject_Call + 101
62  org.python.python             	0x000000010e1d94a4 _PyEval_EvalFrameDefault + 5428
63  org.python.python             	0x000000010e1e0ae8 _PyEval_EvalCodeWithName + 1719
64  org.python.python             	0x000000010e1e13bb _PyFunction_FastCallDict + 449
65  org.python.python             	0x000000010e143fd1 _PyObject_FastCallDict + 196
66  org.python.python             	0x000000010e1440f4 _PyObject_Call_Prepend + 156
67  org.python.python             	0x000000010e143e52 PyObject_Call + 101
68  org.python.python             	0x000000010e18e510 slot_tp_call + 50
69  org.python.python             	0x000000010e143f9c _PyObject_FastCallDict + 143
70  org.python.python             	0x000000010e1e0356 call_function + 441
71  org.python.python             	0x000000010e1d923b _PyEval_EvalFrameDefault + 4811
72  org.python.python             	0x000000010e1e0ae8 _PyEval_EvalCodeWithName + 1719
73  org.python.python             	0x000000010e1d7ef6 PyEval_EvalCode + 42
74  org.python.python             	0x000000010e200c2a run_mod + 54
75  org.python.python             	0x000000010e1ffc4b PyRun_FileExFlags + 160
76  org.python.python             	0x000000010e1ff328 PyRun_SimpleFileExFlags + 285
77  org.python.python             	0x000000010e213673 Py_Main + 3471
78  org.python.python             	0x000000010e136e1d 0x10e135000 + 7709
79  libdyld.dylib                 	0x00007fff7e22f015 start + 1

Thread 0 crashed with X86 Thread State (64-bit):
  rax: 0x00000000a029a697  rbx: 0x00007fb15ae23820  rcx: 0x00007fb15cc44848  rdx: 0x00000001101928d0
  rdi: 0x00007fb15ae23820  rsi: 0x000000010ff6e080  rbp: 0x00007ffee1ac7a00  rsp: 0x00007ffee1ac79c8
   r8: 0x0000000000000000   r9: 0x000000010e28c770  r10: 0x00007ffee1ac790f  r11: 0x00000000012f00e0
  r12: 0x000000010ff6e080  r13: 0x00000001101928d0  r14: 0x0000000000000000  r15: 0x00007fb15ae4b830
  rip: 0x0000000000000000  rfl: 0x0000000000010202  cr2: 0x0000000000000000
  
Logical CPU:     4
Error Code:      0x00000014
Trap Number:     14

Returning anything other than a tuple from iterate() throws unhelpful "OperationalError: SQL logic error"

I kept getting OperationalError: SQL logic error when I tried to run SQL queries against my new table function.

Eventually I realized it was because I was returning a [] list from my iterate() method. Returning a tuple instead made the error go away.

A few options for improvements here:

Allow lists in addition to tuples to be returned from iterate()
Improve the error message that is displayed in this case
Improve the documentation to make it VERY clear that it needs to display tuples (and maybe to ensure that if someone searches for "SQL logic error" they get a clue about this)

I don't have a strong opinion on which of these I prefer - really just wanted to raise this as a minor usability issue.

expecting bytes, NoneType found

Any clues as to what is going on here?

After successful pip install vtfunc on Windows 10, Python39 (See comments on #10 )

Got these errors

RegexSearch.register(conn)
File "vtfunc.pyx", line 612, in vtfunc.TableFunction.register
File "vtfunc.pyx", line 576, in vtfunc._TableFunctionImpl.create_module
TypeError: expected bytes, NoneType found

when executing sample code from https://github.com/coleifer/sqlite-vtfunc .

import re

from vtfunc import TableFunction


class RegexSearch(TableFunction):
    params = ['regex', 'search_string']
    columns = ['match']
    name = 'regex_search'

    def initialize(self, regex=None, search_string=None):
        self._iter = re.finditer(regex, search_string)

    def iterate(self, idx):
        # We do not need `idx`, so just ignore it.
        return (next(self._iter).group(0),)

import sqlite3

conn = sqlite3.connect(':memory:')  # Create an in-memory database.

RegexSearch.register(conn)  # Register our module.

query_params = ('[0-9]+', '123 xxx 456 yyy 789 zzz 0')
cursor = conn.execute('SELECT * FROM regex_search(?, ?);', query_params)
print cursor.fetchall()

Python segfault crashes if a initialize() method throws an exception

Here's a full transcript (after installing using the steps in #2):

(vtfunc-demo) sqlite-vtfunc-master $ python
Python 3.6.5 (default, Mar 30 2018, 06:41:53) 
[GCC 4.2.1 Compatible Apple LLVM 9.0.0 (clang-900.0.39.2)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import re, requests, sqlite3
>>> from vtfunc import TableFunction
>>> 
>>> class Scraper(TableFunction):
...     params = ['url']  # Function argument names.
...     columns = ['href', 'description']  # Result rows have these columns.
...     name = 'scraper'  # Name we use to invoke the function from SQL.
...     def initialize(self, url):
...         # Note that this is bytes:
...         self.html = requests.get(url).content
...         self._iter = re.finditer(
...             '<a[^\>]+?href="([^\"]+?)"[^\>]*?>([^\<]+?)</a>',
...             self.html)
...     def iterate(self, idx):
...         return next(self._iter).groups()
... 
>>> conn = sqlite3.connect(':memory:')
>>> Scraper.register(conn)
>>> curs = conn.execute('SELECT * FROM scraper(?) ORDER BY length(description) DESC  LIMIT 3', ('https://news.ycombinator.com/',))
Traceback (most recent call last):
  File "<stdin>", line 10, in initialize
  File "/usr/local/Cellar/python/3.6.5/Frameworks/Python.framework/Versions/3.6/lib/python3.6/re.py", line 229, in finditer
    return _compile(pattern, flags).finditer(string)
TypeError: cannot use a string pattern on a bytes-like object
Exception ignored in: 'vtfunc.pwFilter'
Traceback (most recent call last):
  File "<stdin>", line 10, in initialize
  File "/usr/local/Cellar/python/3.6.5/Frameworks/Python.framework/Versions/3.6/lib/python3.6/re.py", line 229, in finditer
    return _compile(pattern, flags).finditer(string)
TypeError: cannot use a string pattern on a bytes-like object
Segmentation fault: 11

Here's that code in an easier-to-copy-and-paste format:

import re, requests, sqlite3
from vtfunc import TableFunction

class Scraper(TableFunction):
    params = ['url']  # Function argument names.
    columns = ['href', 'description']  # Result rows have these columns.
    name = 'scraper'  # Name we use to invoke the function from SQL.
    def initialize(self, url):
        # Note that this is bytes:
        self.html = requests.get(url).content
        self._iter = re.finditer(
            '<a[^\>]+?href="([^\"]+?)"[^\>]*?>([^\<]+?)</a>',
            self.html)
    def iterate(self, idx):
        return next(self._iter).groups()

conn = sqlite3.connect(':memory:')
Scraper.register(conn)
curs = conn.execute('SELECT * FROM scraper(?) ORDER BY length(description) DESC  LIMIT 3', ('https://news.ycombinator.com/',))

If I change that line to self.html = requests.get(url).content.decode('utf8') it works and I get the following:

>>> conn = sqlite3.connect(':memory:')
>>> Scraper.register(conn)
>>> curs = conn.execute('SELECT * FROM scraper(?) ORDER BY length(description) DESC  LIMIT 3', ('https://news.ycombinator.com/',))
>>> print(curs.fetchall())
[('https://www.ntsb.gov/investigations/AccidentReports/Reports/HWY18MH010-prelim.pdf', 'Uber Self-Driving Car That Struck Pedestrian Wasn’t Set to Stop in an Emergency'), ('https://www.macrumors.com/2018/05/24/apple-knew-about-bendgate-and-touch-disease/', 'Apple Knew About iPhone 6 Bendgate/Touch Disease Months Before Repair Programs'), ('https://www.theguardian.com/technology/2018/may/24/mark-zuckerberg-set-up-fraudulent-scheme-weaponise-data-facebook-court-case-alleges?CMP=share_btn_tw', "Zuckerberg set up fraudulent scheme to 'weaponise' data, court case alleges")]

Pre-generated C file fails to build, missing _pysqlite/connection.h

I got this error running pipenv install vtfunc on OS X:

vtfunc $ pipenv install vtfunc
Creating a virtualenv for this project…
Using /usr/local/Cellar/pipenv/11.5.2/libexec/bin/python3.6 (3.6.4) to create virtualenv…
⠋Already using interpreter /usr/local/Cellar/pipenv/11.5.2/libexec/bin/python3.6
Using real prefix '/usr/local/Cellar/python/3.6.4_4/Frameworks/Python.framework/Versions/3.6'
New python executable in /Users/simonw/.local/share/virtualenvs/vtfunc-Q7wM-0tr/bin/python3.6
Also creating executable in /Users/simonw/.local/share/virtualenvs/vtfunc-Q7wM-0tr/bin/python
Installing setuptools, pip, wheel...done.

Virtualenv location: /Users/simonw/.local/share/virtualenvs/vtfunc-Q7wM-0tr
Creating a Pipfile for this project…
Installing vtfunc…
Collecting vtfunc
  Downloading vtfunc-0.3.0.tar.gz (60kB)
Building wheels for collected packages: vtfunc
  Running setup.py bdist_wheel for vtfunc: started
  Running setup.py bdist_wheel for vtfunc: finished with status 'error'
  Complete output from command /Users/simonw/.local/share/virtualenvs/vtfunc-Q7wM-0tr/bin/python3.6 -u -c "import setuptools, tokenize;__file__='/private/var/folders/jj/fngnv0810tn2lt_kd3911pdc0000gp/T/pip-build-jaloi37r/vtfunc/setup.py';f=getattr(tokenize, 'open', open)(__file__);code=f.read().replace('\r\n', '\n');f.close();exec(compile(code, __file__, 'exec'))" bdist_wheel -d /var/folders/jj/fngnv0810tn2lt_kd3911pdc0000gp/T/tmpx4vm8s4dpip-wheel- --python-tag cp36:
  /private/var/folders/jj/fngnv0810tn2lt_kd3911pdc0000gp/T/pip-build-jaloi37r/vtfunc/setup.py:10: UserWarning: Cython not installed, using pre-generated C source file.
    warnings.warn('Cython not installed, using pre-generated C source file.')
  running bdist_wheel
  running build
  running build_ext
  building 'vtfunc' extension
  creating build
  creating build/temp.macosx-10.13-x86_64-3.6
  clang -Wno-unused-result -Wsign-compare -Wunreachable-code -fno-common -dynamic -DNDEBUG -g -fwrapv -O3 -Wall -Wstrict-prototypes -DMODULE_NAME="vtfunc" -I/usr/local/Cellar/python/3.6.4_4/Frameworks/Python.framework/Versions/3.6/include/python3.6m -c vtfunc.c -o build/temp.macosx-10.13-x86_64-3.6/vtfunc.o
  vtfunc.c:552:10: fatal error: '_pysqlite/connection.h' file not found
  #include "_pysqlite/connection.h"
           ^~~~~~~~~~~~~~~~~~~~~~~~
  1 error generated.
  error: command 'clang' failed with exit status 1
  
  ----------------------------------------
  Running setup.py clean for vtfunc
Failed to build vtfunc
Installing collected packages: vtfunc
  Running setup.py install for vtfunc: started
    Running setup.py install for vtfunc: finished with status 'error'
    Complete output from command /Users/simonw/.local/share/virtualenvs/vtfunc-Q7wM-0tr/bin/python3.6 -u -c "import setuptools, tokenize;__file__='/private/var/folders/jj/fngnv0810tn2lt_kd3911pdc0000gp/T/pip-build-jaloi37r/vtfunc/setup.py';f=getattr(tokenize, 'open', open)(__file__);code=f.read().replace('\r\n', '\n');f.close();exec(compile(code, __file__, 'exec'))" install --record /var/folders/jj/fngnv0810tn2lt_kd3911pdc0000gp/T/pip-mgwklly9-record/install-record.txt --single-version-externally-managed --compile --install-headers /Users/simonw/.local/share/virtualenvs/vtfunc-Q7wM-0tr/bin/../include/site/python3.6/vtfunc:
    /private/var/folders/jj/fngnv0810tn2lt_kd3911pdc0000gp/T/pip-build-jaloi37r/vtfunc/setup.py:10: UserWarning: Cython not installed, using pre-generated C source file.
      warnings.warn('Cython not installed, using pre-generated C source file.')
    running install
    running build
    running build_ext
    building 'vtfunc' extension
    creating build
    creating build/temp.macosx-10.13-x86_64-3.6
    clang -Wno-unused-result -Wsign-compare -Wunreachable-code -fno-common -dynamic -DNDEBUG -g -fwrapv -O3 -Wall -Wstrict-prototypes -DMODULE_NAME="vtfunc" -I/usr/local/Cellar/python/3.6.4_4/Frameworks/Python.framework/Versions/3.6/include/python3.6m -c vtfunc.c -o build/temp.macosx-10.13-x86_64-3.6/vtfunc.o
    vtfunc.c:552:10: fatal error: '_pysqlite/connection.h' file not found
    #include "_pysqlite/connection.h"
             ^~~~~~~~~~~~~~~~~~~~~~~~
    1 error generated.
    error: command 'clang' failed with exit status 1
    
    ----------------------------------------

Error:  An error occurred while installing vtfunc!

	rc = sqlite3_declare_vtab(
	db,
	encode('CREATE TABLE x(%s);' %
	table_func_cls.get_table_columns_declaration()))