Re: [sqlite] Math Functions
This all makes eminently good sense; thank you for the explanation. I have updated the comments at the head of the file and uploaded a new version, which is now at http://www.sqlite.org/contrib/download/extension-functions.c?get=25. Nothing in the code has changed, so there's no reason to download the new version if you already have the old file. Liam On Fri, Jun 13, 2008 at 10:31 AM, Jay A. Kreibich <[EMAIL PROTECTED]> wrote: > On Fri, Jun 13, 2008 at 09:43:51AM -0400, Liam Healy scratched on the wall: > >> Interesting; I tried it on Debian sid (unstable) and it worked as >> well. I guess the statement in >> http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions that the >> extensions loading mechanism being turned off by default doesn't apply >> to Debian. > > It isn't a compile-time flag (unless you omit the whole thing), it is > something you turn on and off in code-- and the default start-up > state FOR THE LIBRARY is off. > > More recent versions of the "sqlite3" command-line tool are nice > enough to enable to feature every time they open a database, however. > So the default behavior for the sqlite3 interface is for the feature > to be enabled. > > > If you understand the security concerns behind having the loading system > off by default and the specific threat it is trying to protect against, > this all makes sense: basically if you're in a position to turn the > feature on via code, you're already in a position to do whatever you > could do with the extension system. > > On the other hand, if you can get at the raw database file (with > sqlite3 or any other tool you've custom compiled) you can pretty much > do anything you want, so there isn't a huge need to protect against that. > >> I've revised the instructions to include -lm for Linux and >> to indicate the possibility that the program as compiled by some >> distributions will work. Does anyone know if -lm is need on Mac OS X >> as well? > > No, it is not. In both 10.4 and 10.5 (and perhaps earlier) the math > lib is associated with the core libSystem run-time lib, so the math > library (and all symbols) are always available. > > -j > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "'People who live in bamboo houses should not throw pandas.' Jesus said that." > - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Math Functions
On Fri, Jun 13, 2008 at 09:43:51AM -0400, Liam Healy scratched on the wall: > Interesting; I tried it on Debian sid (unstable) and it worked as > well. I guess the statement in > http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions that the > extensions loading mechanism being turned off by default doesn't apply > to Debian. It isn't a compile-time flag (unless you omit the whole thing), it is something you turn on and off in code-- and the default start-up state FOR THE LIBRARY is off. More recent versions of the "sqlite3" command-line tool are nice enough to enable to feature every time they open a database, however. So the default behavior for the sqlite3 interface is for the feature to be enabled. If you understand the security concerns behind having the loading system off by default and the specific threat it is trying to protect against, this all makes sense: basically if you're in a position to turn the feature on via code, you're already in a position to do whatever you could do with the extension system. On the other hand, if you can get at the raw database file (with sqlite3 or any other tool you've custom compiled) you can pretty much do anything you want, so there isn't a huge need to protect against that. > I've revised the instructions to include -lm for Linux and > to indicate the possibility that the program as compiled by some > distributions will work. Does anyone know if -lm is need on Mac OS X > as well? No, it is not. In both 10.4 and 10.5 (and perhaps earlier) the math lib is associated with the core libSystem run-time lib, so the math library (and all symbols) are always available. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Math Functions
On Fri, Jun 13, 2008 at 4:31 AM, Alexey Pechnikov <[EMAIL PROTECTED]> wrote:
>> The extension-functions file doesn't actually implement any of the
>> math functinos, it simply acts as a glue layer between SQLite and the
>> system math library. In this case, it looks like the run-time linker
>> that loads the extension can't resolve the call for log() from the
>> extension into the math library, resulting in an unresolved link.
>>
>> On many systems the math library is part of the standard set of libs
>> that are imported by the linker for all applications. There are a
>> few systems, however, where the math library is not part of the
>> standard lib set. On those systems, you need to explicitly tell the
>> linker you want the math library made avaliable. You can do that
>> by compiling the lib with -lm to import the math library. The math
>> lib won't be pulled into the .so, but it will be noted that if the
>> run-time linker pulls in the extension, it will also need to pull in
>> the math library before it attepts to resolve all the symbols.
>>
>> At least, in theory.
>
> Thanks for coherent explanation. I am compiling library with switch -lm and
> now it's work.
>
> $ gcc -fPIC -lm -shared extension-functions.c -o libsqlitefunctions.so
> $ sqlite3 :memory:
> sqlite> SELECT load_extension('./libsqlitefunctions.so');
>
> sqlite> select sqrt(16);
> 4.0
> sqlite>
>
>>
>> What OS are you trying this on?
>
> Linux Debian Etch (sqlite pakage backported from Debian Lenny).
>
Interesting; I tried it on Debian sid (unstable) and it worked as
well. I guess the statement in
http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions that the
extensions loading mechanism being turned off by default doesn't apply
to Debian. I've revised the instructions to include -lm for Linux and
to indicate the possibility that the program as compiled by some
distributions will work. Does anyone know if -lm is need on Mac OS X
as well?
Liam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Math Functions
> The extension-functions file doesn't actually implement any of the
> math functinos, it simply acts as a glue layer between SQLite and the
> system math library. In this case, it looks like the run-time linker
> that loads the extension can't resolve the call for log() from the
> extension into the math library, resulting in an unresolved link.
>
> On many systems the math library is part of the standard set of libs
> that are imported by the linker for all applications. There are a
> few systems, however, where the math library is not part of the
> standard lib set. On those systems, you need to explicitly tell the
> linker you want the math library made avaliable. You can do that
> by compiling the lib with -lm to import the math library. The math
> lib won't be pulled into the .so, but it will be noted that if the
> run-time linker pulls in the extension, it will also need to pull in
> the math library before it attepts to resolve all the symbols.
>
> At least, in theory.
Thanks for coherent explanation. I am compiling library with switch -lm and
now it's work.
$ gcc -fPIC -lm -shared extension-functions.c -o libsqlitefunctions.so
$ sqlite3 :memory:
sqlite> SELECT load_extension('./libsqlitefunctions.so');
sqlite> select sqrt(16);
4.0
sqlite>
>
> What OS are you trying this on?
Linux Debian Etch (sqlite pakage backported from Debian Lenny).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Math Functions
On Thu, Jun 12, 2008 at 04:26:13PM +0400, Alexey Pechnikov scratched on the
wall:
> В сообщении от Friday 06 June 2008 03:51:38 P Kishor написал(а):
> > http://sqlite.org/contrib/download/extension-functions.c?get=22
>
> $ wget http://sqlite.org/contrib/download/extension-functions.c?get=22
> $ mv extension-functions.c?get=22 extension-functions.c
> $ gcc -fPIC -shared extension-functions.c -o libsqlitefunctions.so
> $ sqlite3 :memory:
> SQLite version 3.5.9
> Enter ".help" for instructions
> sqlite> SELECT load_extension('./libsqlitefunctions.so');
> SQL error: ./libsqlitefunctions.so: undefined symbol: log
>
> What can I do?
The extension-functions file doesn't actually implement any of the
math functinos, it simply acts as a glue layer between SQLite and the
system math library. In this case, it looks like the run-time linker
that loads the extension can't resolve the call for log() from the
extension into the math library, resulting in an unresolved link.
On many systems the math library is part of the standard set of libs
that are imported by the linker for all applications. There are a
few systems, however, where the math library is not part of the
standard lib set. On those systems, you need to explicitly tell the
linker you want the math library made avaliable. You can do that
by compiling the lib with -lm to import the math library. The math
lib won't be pulled into the .so, but it will be noted that if the
run-time linker pulls in the extension, it will also need to pull in
the math library before it attepts to resolve all the symbols.
At least, in theory.
What OS are you trying this on?
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"'People who live in bamboo houses should not throw pandas.' Jesus said that."
- "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Math Functions
> Note: You cannot use these functions from the sqlite3 program, you
> must write your own program using the sqlite3 API, and call
> sqlite3_enable_load_extension. See "Security Considerations" in
> http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions.
I can load other extensions and use them.
> When I try your load from the command line, I get a similar
> (but not the same) error.
> sqlite> SELECT load_extension('./libsqlitefunctions.so');
> SQL error: ./libsqlitefunctions.so: undefined symbol: sqrt
> and I certainly have a sqrt function and use these extensions
> without problems. So I guess it's a symptom of the security feature
> in sqlite that prohibits loading extensions.
sqlite> SELECT load_extension('./libsqlitefunctions.so');
SQL error: ./libsqlitefunctions.so: undefined symbol: log
sqlite> select sqrt(1);
SQL error: no such function: sqrt
> On the chance that log is not defined for your environment.
> I recommend you change the line
> #define HAVE_LOG10 1
> to
> #define HAVE_LOG10 0
> and try again.
I try with no effect.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Math Functions
In the file you have downloaded, there is the following statement:
Note: You cannot use these functions from the sqlite3 program, you
must write your own program using the sqlite3 API, and call
sqlite3_enable_load_extension. See "Security Considerations" in
http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions.
When I try your load from the command line, I get a similar
(but not the same) error.
sqlite> SELECT load_extension('./libsqlitefunctions.so');
SQL error: ./libsqlitefunctions.so: undefined symbol: sqrt
and I certainly have a sqrt function and use these extensions
without problems. So I guess it's a symptom of the security feature
in sqlite that prohibits loading extensions.
On the chance that log is not defined for your environment.
I recommend you change the line
#define HAVE_LOG10 1
to
#define HAVE_LOG10 0
and try again.
Liam
On Thu, Jun 12, 2008 at 8:26 AM, Alexey Pechnikov <[EMAIL PROTECTED]> wrote:
> В сообщении от Friday 06 June 2008 03:51:38 P Kishor написал(а):
>> http://sqlite.org/contrib/download/extension-functions.c?get=22
>
> $ wget http://sqlite.org/contrib/download/extension-functions.c?get=22
> $ mv extension-functions.c?get=22 extension-functions.c
> $ gcc -fPIC -shared extension-functions.c -o libsqlitefunctions.so
> $ sqlite3 :memory:
> SQLite version 3.5.9
> Enter ".help" for instructions
> sqlite> SELECT load_extension('./libsqlitefunctions.so');
> SQL error: ./libsqlitefunctions.so: undefined symbol: log
>
> What can I do?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Math Functions
В сообщении от Friday 06 June 2008 03:51:38 P Kishor написал(а):
> http://sqlite.org/contrib/download/extension-functions.c?get=22
$ wget http://sqlite.org/contrib/download/extension-functions.c?get=22
$ mv extension-functions.c?get=22 extension-functions.c
$ gcc -fPIC -shared extension-functions.c -o libsqlitefunctions.so
$ sqlite3 :memory:
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> SELECT load_extension('./libsqlitefunctions.so');
SQL error: ./libsqlitefunctions.so: undefined symbol: log
What can I do?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Math Functions
On 6/5/08, BareFeet <[EMAIL PROTECTED]> wrote: > > requires square root and I'm not sure if I can do this with sqlite. > > Something like: > > > > SELECT PlaceName, sqrt((PlaceX - 1)^2 - (PlaceY - 3)^2) AS > > DistFromHome > > FROM Table WHERE PlaceGroup = 3; > > > I was going to suggest using "^ 0.5" for square root, but now I see > that SQLite doesn't even support the "^" power of operator. Surely > this is a fundamental requirement of any syntax that supports basic > maths like addition, multiplication etc. > > Can/will the power operator be added to SQLite? http://sqlite.org/contrib/download/extension-functions.c?get=22 > > Thanks, > Tom > > BareFeet > -- > Comparison of SQLite GUI tools for Mac OS X: > http://www.tandb.com.au/sqlite/compare/?ml > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Math Functions
On Thu, Jun 05, 2008 at 03:55:44PM -0700, Scott Baker scratched on the wall: > I have a database with coordinates in it. I'd like to calculate distance at > the SQL level, but the equation for calculating distance requires square > root and I'm not sure if I can do this with sqlite. Something like: > > SELECT PlaceName, sqrt((PlaceX - 1)^2 - (PlaceY - 3)^2) AS DistFromHome > FROM Table WHERE PlaceGroup = 3; Be aware that SQLite doesn't recognize the ^ operator. > What mathematical functions can I do at the sqlite level? I'll preface this > with, with stock SQLite (no added components) as it's on a shared web host. If you must use a vanilla SQLite install, the built-in functions are listed here: http://www.sqlite.org/lang_corefunc.html http://www.sqlite.org/lang_aggfunc.html If you don't want to deal with extensions but can modify the SQLite binary, it is very easy to add your own functions directly to the SQLite library. See src/func.c for more info. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Math Functions
> requires square root and I'm not sure if I can do this with sqlite. > Something like: > > SELECT PlaceName, sqrt((PlaceX - 1)^2 - (PlaceY - 3)^2) AS > DistFromHome > FROM Table WHERE PlaceGroup = 3; I was going to suggest using "^ 0.5" for square root, but now I see that SQLite doesn't even support the "^" power of operator. Surely this is a fundamental requirement of any syntax that supports basic maths like addition, multiplication etc. Can/will the power operator be added to SQLite? Thanks, Tom BareFeet -- Comparison of SQLite GUI tools for Mac OS X: http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Math Functions
You can do your own custom functions using the C extentions. You should buy the book. "The Definitive Guide SQLite" On Thu, Jun 5, 2008 at 6:55 PM, Scott Baker <[EMAIL PROTECTED]> wrote: > I have a database with coordinates in it. I'd like to calculate distance at > the SQL level, but the equation for calculating distance requires square > root and I'm not sure if I can do this with sqlite. Something like: > > SELECT PlaceName, sqrt((PlaceX - 1)^2 - (PlaceY - 3)^2) AS DistFromHome > FROM Table WHERE PlaceGroup = 3; > > What mathematical functions can I do at the sqlite level? I'll preface this > with, with stock SQLite (no added components) as it's on a shared web host. > > -- > Scott Baker - Canby Telcom > RHCE - System Administrator - 503.266.8253 > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Math Functions
I have a database with coordinates in it. I'd like to calculate distance at the SQL level, but the equation for calculating distance requires square root and I'm not sure if I can do this with sqlite. Something like: SELECT PlaceName, sqrt((PlaceX - 1)^2 - (PlaceY - 3)^2) AS DistFromHome FROM Table WHERE PlaceGroup = 3; What mathematical functions can I do at the sqlite level? I'll preface this with, with stock SQLite (no added components) as it's on a shared web host. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Math functions
> On 4/4/07, Nathan Biggs <[EMAIL PROTECTED]> wrote: > Does anyone know if there is a floor function in sqlite, or of a way to > implement it. > Well, the suggestions about extending SQLite are probably quicker, but if a SQL solution is required for portability, you can try something like this: select case when @float >= 0.0 then cast(@float as int) else cast(@float-1 as int) end The extra cast is there because in my tests, the cast *always* truncates towards 0, for both positive and negative numbers. If you never have to deal with negatives, you can get away with the simple cast. Brad - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Math functions
Please see http://sqlite.org/contrib under extension-functions.tgz. On 4/4/07, Nathan Biggs <[EMAIL PROTECTED]> wrote: Does anyone know if there is a floor function in sqlite, or of a way to implement it.
Re: [sqlite] Math functions
yes you can implement math functions with user defined functions by the sqlite3_create_function. sqlite.org is your freind :) 2007/4/4, Nathan Biggs <[EMAIL PROTECTED]>: Does anyone know if there is a floor function in sqlite, or of a way to implement it.
[sqlite] Math functions
Does anyone know if there is a floor function in sqlite, or of a way to implement it.
