add generate_series function

[l1t1]

Is your feature request related to a problem? Please describe.
this function exists in monetdb but not in monetdbe

C:\Users\LD>pip show monetdbe
Name: monetdbe
Version: 0.10.1
Summary: MonetDBe - the Python embedded MonetDB
Home-page: https://github.com/monetdBSolutions/MonetDBe-Python/
Author: Gijs Molenaar
Author-email: [email protected]
License: UNKNOWN
Location: d:\python38\lib\site-packages
Requires: numpy, cffi, pandas
Required-by:

C:\Users\LD>python
Python 3.8.8 (tags/v3.8.8:024d805, Feb 19 2021, 13:18:16) [MSC v.1928 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> from monetdbe import connect
>>> con = connect()
>>> con.execute('select * from tables').fetchdf()
       id             name  schema_id                                              query  type  system  commit_action  access  temporary
0    2001          schemas       2000                                               None    10    True              0       0          0
1    2007            types       2000                                               None    10    True              0       0          0
2    2016        functions       2000                                               None    10    True              0       0          0
3    2028             args       2000                                               None    10    True              0       0          0
4    2037        sequences       2000                                               None    10    True              0       0          0
..    ...              ...        ...                                                ...   ...     ...            ...     ...        ...
113  6771  dump_privileges       2000  create view sys.dump_privileges as\nselect\n'I...    11    True              0       0          0
114  6787  dump_statements       2000                                               None    10    True              0       0          0
115  6812       statistics       2000                                               None    10    True              0       0          0
116  6871         compinfo       6835  create view logging.compinfo as select * from ...    11    True              0       0          0
117  6876  systemfunctions       2000  create view sys.systemfunctions as select id a...    11    True              0       0          0

[118 rows x 9 columns]
>>> import time
>>> t=time.time();print(con.execute('select sum(value) from generate_series(1,100000001)').fetchdf());print(time.time()-t)
ParseException:SQLparser:42000!SELECT: no such table returning function 'generate_series'(tinyint, int)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "D:\Python38\lib\site-packages\monetdbe\connection.py", line 167, in execute
    cur = self.cursor(factory=cursor).execute(query, args, paramstyle)
  File "D:\Python38\lib\site-packages\monetdbe\cursors.py", line 142, in execute
    return self._execute_monetdbe(operation, parameters)
  File "D:\Python38\lib\site-packages\monetdbe\cursors.py", line 123, in _execute_monetdbe
    statement = self.connection.prepare(operation)
  File "D:\Python38\lib\site-packages\monetdbe\connection.py", line 316, in prepare
    return self._internal.prepare(operation)  # type: ignore[union-attr]
  File "D:\Python38\lib\site-packages\monetdbe\_cffi\internal.py", line 270, in prepare
    check_error(lib.monetdbe_prepare(self._monetdbe_database, str(query).encode(), stmt))
  File "D:\Python38\lib\site-packages\monetdbe\_cffi\errors.py", line 59, in check_error
    raise exception(msg)
monetdbe.exceptions.OperationalError: SELECT: no such table returning function 'generate_series'(tinyint, int)
>>>

Describe the solution you'd like
the function works as that in monetdb

[njnes]

in current default we added support for some loading of modules (not all monetdb modules are part of the monetdbe library). To load those used .enable_load_extension() and .load_extension('extension_name'). The current limitation is that lib_extension_name.so, should be in your local directory (cwd). Also the sql scripts aren't directly loaded, ie. you should a those by hand. Above example works with:

import monetdbe
db = monetdbe.connect()
db.enable_load_extension()
db.load_extension('generator')
db.execute("""
create function sys.generate_series(first hugeint, "limit" hugeint)
returns table (value hugeint)
external name generator.series;
create function sys.generate_series(first hugeint, "limit" hugeint, stepsize hugeint)
returns table (value hugeint)
external name generator.series;
""")
print(db.execute('select sum(value) from generate_series(1,100000001)').fetchdf());

[njnes]

Also the default automatically down casts 128bit ints into 64 bits when the client (python) doesn't support it

[l1t1]

thanks njnes