Define--> What does undefine() and define_free() actually do?

[drjdpowell]

Hi, I have this confusing behaviour (using a compiled for 32-bit define.dll):

select define('sumn', ':n * (:n + 1) / 2');
select sumn(5); --> 15
select undefine('sumn');
select define_free();
select sumn(5); --> '15'

Why does the defined function still work if I have undefined and called define_free? I checked that the 'sumn' function is properly deleted from the sqlean_define table.

[nalgeon]

Quoting the docs:

To delete a scalar function, execute undefine(), then reconnect to the database:

sqlite> select undefine('sumn');
sqlite> select define_free();
... reconnect
sqlite> select sumn(5);
Parse error: no such function: sumn

[drjdpowell]

Ah, I did not appreciate that the sqlean_define table is permanent, and survives reconnect.

Is there a way to make the table Temporary, so that each connection has its own private set of defined functions, that disappear on disconnect?

[nalgeon]

Not at the moment. Why would you need it, what is your use case?

[drjdpowell]

Similar to using a Temporary View, to make following SQL simpler and clearer, without two independant connections (with possibly different versions of the View) interfering with each other.

[nalgeon]

Not sure I understand. What prevents you from using the same function from two different connections? How would they interfere?

[drjdpowell]

An example might be a Distance() function that includes a conversion into desired display units (Miles versus km vs yards vs meters, say). Two connections to the same db might be set for different display units. Each connection would want to be able to define the distance() function differently, and be able to change it freely.

[nalgeon]

I'm sure we can come up with a lot of hypothetical scenarios like the one you mentioned. But do you have a specific use case in your real work?

[nalgeon]

I'll be happy to revisit this issue when there's a real use case.