Issue: Unable to register or access Sqlite User Defined Functions
Author: amandeep-r
State: closed
Created: 2019-09-18 22:36:05

Hello, I'm trying to use the median sqlite user defined function. I cloned peewee through git and ran the command,  python setup.py build_ext -i , after installing it. I verified that my environment contains squlite3 as well. 

In a python terminal I am able to import playhouse._sqlite_udf and playhouse._sqlite_ext with no issue which leads me to believe the cython extensions built correctly.

Upon executing the following code, I get the error: 
peewee.OperationalError: no such function: median

The code in question:
```
from peewee import *
from database2 import Trial, Datapoint
from datetime import datetime as dt
from datetime import timedelta
from playhouse.sqlite_udf import *

db = SqliteDatabase('sbir.db')
register_all(db)

def gen_aggregate(timestamp, trial_id, delta_t=20):
    delta_t_timedelta = timedelta(minutes=delta_t)
    time_initial = timestamp - delta_t_timedelta
    time_final = timestamp
    query = Datapoint.select(fn.median(Datapoint.pH).alias('pH_median')).where(
        Datapoint.trial == trial_id,
        Datapoint.timestamp.between(time_initial, time_final)
        )
    
    return query

results = gen_aggregate(dt.now(), 1)
print(results)
```
The same results occurs for other sqlite_udf functions but not for the normal ones (such as COUNT). 

The traceback is below:
```
Traceback (most recent call last):
  File "/home/aman/anaconda3/envs/sbir/lib/python3.7/site-packages/peewee-3.10.0-py3.7-linux-x86_64.egg/peewee.py", line 3002, in execute_sql
    cursor.execute(sql, params or ())
sqlite3.OperationalError: no such function: median

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/aman/Code/Babylon-Farm/Babylon-Farm/code/db/db_actions2.py", line 72, in <module>
    for entry in results:
  File "/home/aman/anaconda3/envs/sbir/lib/python3.7/site-packages/peewee-3.10.0-py3.7-linux-x86_64.egg/peewee.py", line 6651, in __iter__
    self.execute()
  File "/home/aman/anaconda3/envs/sbir/lib/python3.7/site-packages/peewee-3.10.0-py3.7-linux-x86_64.egg/peewee.py", line 1809, in inner
    return method(self, database, *args, **kwargs)
  File "/home/aman/anaconda3/envs/sbir/lib/python3.7/site-packages/peewee-3.10.0-py3.7-linux-x86_64.egg/peewee.py", line 1880, in execute
    return self._execute(database)
  File "/home/aman/anaconda3/envs/sbir/lib/python3.7/site-packages/peewee-3.10.0-py3.7-linux-x86_64.egg/peewee.py", line 2051, in _execute
    cursor = database.execute(self)
  File "/home/aman/anaconda3/envs/sbir/lib/python3.7/site-packages/peewee-3.10.0-py3.7-linux-x86_64.egg/peewee.py", line 3015, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/home/aman/anaconda3/envs/sbir/lib/python3.7/site-packages/peewee-3.10.0-py3.7-linux-x86_64.egg/peewee.py", line 3009, in execute_sql
    self.commit()
  File "/home/aman/anaconda3/envs/sbir/lib/python3.7/site-packages/peewee-3.10.0-py3.7-linux-x86_64.egg/peewee.py", line 2780, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "/home/aman/anaconda3/envs/sbir/lib/python3.7/site-packages/peewee-3.10.0-py3.7-linux-x86_64.egg/peewee.py", line 183, in reraise
    raise value.with_traceback(tb)
  File "/home/aman/anaconda3/envs/sbir/lib/python3.7/site-packages/peewee-3.10.0-py3.7-linux-x86_64.egg/peewee.py", line 3002, in execute_sql
    cursor.execute(sql, params or ())
peewee.OperationalError: no such function: median
```

Please advise.

You need to register the functions, which is not done automatically.

```python
from playhouse.sqlite_udf import median

db = SqliteDatabase(':memory:')
db.register_aggregate(median, 'median')  # Will be available on all connections.
```

If you want all of the functions/aggregates,

```python
from playhouse.sqlite_udf import register_all

db = SqliteDatabase(...)
register_all(db)

# Or register just particular groupings, e.g.
from playhouse.sqlite_udf import DATE, MATH
from playhouse.sqlite_udf import register_groups

register_groups(db, DATE, MATH)
```

I have tried all three methods and keep getting the same traceback.

```
from peewee import *
from database2 import Trial, Datapoint
from datetime import datetime as dt
from datetime import timedelta
from playhouse.sqlite_udf import median

db = SqliteDatabase(':memory:')
db.register_aggregate(median, 'median')
```
I believe I had already included the register_all method in my previous code? 
```
from peewee import *
from database2 import Trial, Datapoint
from datetime import datetime as dt
from datetime import timedelta
from playhouse.sqlite_udf import register_all

db = SqliteDatabase('sbir.db')
register_all(db)
```

```
from peewee import *
from database2 import Trial, Datapoint
from datetime import datetime as dt
from datetime import timedelta
from playhouse.sqlite_udf import DATE, MATH
from playhouse.sqlite_udf import register_groups

db = SqliteDatabase('sbir.db')
register_groups(db, DATE, MATH)
```

My function has remained unchanged each time I ran the above changes. Do you have any other ideas?

Works fine for me:

```python

In [1]: from peewee import *                                                                        

In [2]: from playhouse.sqlite_udf import *                                                          

In [3]: db = SqliteDatabase(':memory:')                                                             

In [4]: db.register_aggregate(median, 'median')                                                     

In [5]: class Reg(Model): 
   ...:     value = IntegerField() 
   ...:     class Meta: 
   ...:         database = db 
   ...:                                                                                             

In [6]: Reg.create_table()                                                                          

In [7]: Reg.insert_many([(i,) for i in [1, 5, 3, 6, 4, 2, 9]]).execute()                            
Out[7]: 7

In [8]: Reg.select(fn.median(Reg.value)).scalar()                                                   
Out[8]: 4
```

Looking at your code, are you sure that your models are bound to the same database that you are registering the aggregate on?

Also note this is covered by unit tests, and is not fundamentally broken.