Extra functions - New Project?

classic Classic list List threaded Threaded
41 messages Options
123
Reply | Threaded
Open this post in threaded view
|

Extra functions - New Project?

Mikey C
Hi,

I am in need of some new SQL functions and wanted to ask advice on the best way to integrate these functions into SQLite 3. I am not a proficient C coder unfortunately.

I have compiled the source for 3.5.5 using Visual Studio.NET 2003 and all works fine.

I have added a couple of simple functions into func.c and these work.  Great so far.

However it would be good if there were a project somewhere to collate extension functions into a set of C files to enable a more powerful version of SQLite.

I have  found a few already on the web. Eg.

http://www.brayden.org/twiki/bin/view/Software/SqliteExtensions#SQLite_Extensions


What I am looking for specifically are more powerful string manipulation functions that mimic Microsoft SQL Server.  In order of importance:

charindex - This one is a show stopper for me.  Need this function badly.
patindex
ltrim
rtrim
replace
difference (integer diff on soundex values)

What is the best way forward?  Have someone develop these and add them directly to func.c or (to aid upgrading) create a new source and header file and add them to the project?  How can new functions be added without removing the ability to upgrade the source to 3.5.6 etc when patches are released to func.c?

Does anyone know how these string functions might be implemented?


Any help appreciated.

Thanks,

Mike
Reply | Threaded
Open this post in threaded view
|

Re: Extra functions - New Project?

Roberto-10
On 24/05/06, Mikey C <[hidden email]> wrote:

>
> Hi,
>
> I am in need of some new SQL functions and wanted to ask advice on the best
> way to integrate these functions into SQLite 3. I am not a proficient C
> coder unfortunately.
> What is the best way forward?  Have someone develop these and add them
> directly to func.c or (to aid upgrading) create a new source and header file
> and add them to the project?  How can new functions be added without
> removing the ability to upgrade the source to 3.5.6 etc when patches are
> released to func.c?

You don't need to modify the SQlite source to keep your user defined
functions. Write your routines and keep them seperate from sqlite, and
register them in your applicaiton when you first open your database.
Unless the API for user defined finctions changes, you won't need to
make any modifications you your code on each sqlite release.
Reply | Threaded
Open this post in threaded view
|

Re: Extra functions - New Project?

Mikey C
Thanks for the response.

I did think of this, but this is a pain since:

1. I am using the Finisar ADO.NET provider and to do this these functions would need to be registered every time the database connection is opened and closed and I don't want to have to mess with the ADO.NET provider code.

2. I would like these extra functions to always be availabe to me (and others), regardless of which project I am working on.

3. They help complete the SQL-92 features since these functions are defined in the standards (CharIndex in MS SQL Server is Position in SQL-92 spec)

4. I am not concerned with footprint size since I use SQLite on desktops and web servers where RAM and CPU power is not an issue.

I guess there is a way to use a new C source file (e.g. funcext.c and funcext.h) for these extra functions and compile them in using conditional compilation?

If anyone knows what funcext.c and funcext.h might look like I could get started on someone with good C coding skills to implement all the missing SQL-92 scalar and aggregate functions into these files.

I would then put them out in the public domain under the same license as SQLite itself (ie. do what you like with them).

Thanks

Mike
Reply | Threaded
Open this post in threaded view
|

Re: Extra functions - New Project?

Jay Sprenkle
On 5/24/06, Mikey C <[hidden email]> wrote:

>
> Thanks for the response.
>
> I did think of this, but this is a pain since:
>
> 1. I am using the Finisar ADO.NET provider and to do this these functions
> would need to be registered every time the database connection is opened and
> closed and I don't want to have to mess with the ADO.NET provider code.
>
> 2. I would like these extra functions to always be availabe to me (and
> others), regardless of which project I am working on.

I can think of two options:

1. Create a .NET assembly that wraps Finisar-Sqlite and implements the
new functions you want.
2. Modify Sqlite as you suggest.

I would think option 1 would be quicker personally, but that's just a guess.

What would be really nice would be some way of adding "plugin" functions
to Sqlite. It would be pretty operating system dependent though.
Reply | Threaded
Open this post in threaded view
|

Re: Extra functions - New Project?

Robert Simpson
----- Original Message -----
From: "Jay Sprenkle" <[hidden email]>
To: <[hidden email]>
Sent: Wednesday, May 24, 2006 6:31 AM
Subject: Re: [sqlite] Extra functions - New Project?


>I can think of two options:
>
>1. Create a .NET assembly that wraps Finisar-Sqlite and implements the
>new functions you want.
>2. Modify Sqlite as you suggest.
>
>I would think option 1 would be quicker personally, but that's just a
>guess..
>
>What would be really nice would be some way of adding "plugin" functions
>to Sqlite. It would be pretty operating system dependent though.=0

Actually your only option using Finisar (aside from recompiling sqlite) is
to write all the functions in C/C++ in a separate DLL, and then modify
Finisar to call some main exported function in that DLL, passing in a
sqlite3 * object every time it creates one.  That main function would then
register all the sqlite3 functions on the connection.

You can't write sqlite3 userdef functions in .NET 1.1 without modifying the
core sqlite3 codebase, since .NET 1.1 doesn't support cdecl callbacks
without modifying the generated MSIL and changing the signature of the
delegate manually.

Robert


Reply | Threaded
Open this post in threaded view
|

Re: Extra functions - New Project?

Jay Sprenkle
On 5/24/06, Robert Simpson <[hidden email]> wrote:

> Actually your only option using Finisar (aside from recompiling sqlite) is
> to write all the functions in C/C++ in a separate DLL, and then modify
> Finisar to call some main exported function in that DLL, passing in a
> sqlite3 * object every time it creates one.  That main function would then
> register all the sqlite3 functions on the connection.
>
> You can't write sqlite3 userdef functions in .NET 1.1 without modifying the
> core sqlite3 codebase, since .NET 1.1 doesn't support cdecl callbacks
> without modifying the generated MSIL and changing the signature of the
> delegate manually.

Flip that around and it's easier.
Write a dll that loads finisar then registers the new functions.
You don't have to modify anyone else's code that way.
Reply | Threaded
Open this post in threaded view
|

Re: Extra functions - New Project?

Mikey C
In reply to this post by Robert Simpson
I would rather add these functions directly to the core SQLite DLL in C in and compile them directly into the code (using a conditional).

For example on the web I found an example of adding a sign() function:

/*
** Implementation of the sign() function
*/
static void signFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
  assert( argc==1 );
  switch( sqlite3_value_type(argv[0]) ){
    case SQLITE_INTEGER: {
      i64 iVal = sqlite3_value_int64(argv[0]);
 /* 1st change below. Line below was:  if( iVal<0 ) iVal = iVal * -1;     */

      iVal = ( iVal > 0) ? 1: ( iVal < 0 ) ? -1: 0;
      sqlite3_result_int64(context, iVal);
      break;
    }
    case SQLITE_NULL: {
      sqlite3_result_null(context);
      break;
    }
    default: {
 /* 2nd change below. Line for abs was: if( rVal<0 ) rVal = rVal * -1.0;  */

      double rVal = sqlite3_value_double(argv[0]);
      rVal = ( rVal > 0) ? 1: ( rVal < 0 ) ? -1: 0;
      sqlite3_result_double(context, rVal);
      break;
    }
  }
}

They then register this function by adding it to the array of existing functions:

  } aFuncs[] = {
    { "min",               -1, 0, SQLITE_UTF8,    1, minmaxFunc },
    { "min",                0, 0, SQLITE_UTF8,    1, 0          },
    { "max",               -1, 2, SQLITE_UTF8,    1, minmaxFunc },
    { "max",                0, 2, SQLITE_UTF8,    1, 0          },
    { "typeof",             1, 0, SQLITE_UTF8,    0, typeofFunc },
    { "length",             1, 0, SQLITE_UTF8,    0, lengthFunc },
    { "substr",             3, 0, SQLITE_UTF8,    0, substrFunc },
    { "substr",             3, 0, SQLITE_UTF16LE, 0, sqlite3utf16Substr },
    { "abs",                1, 0, SQLITE_UTF8,    0, absFunc    },
    /*  Added here */
    { "sign",               1, 0, SQLITE_UTF8,    0, signFunc   },
    { "round",              1, 0, SQLITE_UTF8,    0, roundFunc  },
    { "round",              2, 0, SQLITE_UTF8,    0, roundFunc  },


This seems to work (I've tried it).

HOWEVER, it means altering func.c and I was looking for how to add these functions in a separate C file without having to alter any existing code?

Anyone any ideas how best to extend the codebase of SQLite with minimal alteration to existing code?

Cheers,

Mike
Reply | Threaded
Open this post in threaded view
|

Re: Extra functions - New Project?

Robert Simpson
In reply to this post by Jay Sprenkle
----- Original Message -----
From: "Jay Sprenkle" <[hidden email]>
To: <[hidden email]>
Sent: Wednesday, May 24, 2006 7:17 AM
Subject: Re: [sqlite] Extra functions - New Project?


>Flip that around and it's easier.
>Write a dll that loads finisar then registers the new functions.
>You don't have to modify anyone else's code that way.=0

Can't do it that way.  Since Finsiar wraps sqlite and doesn't give you
underlying access to any of the raw pointers, you won't be able to register
your functions.  Even if it did let you have access, there are quite a few
ADO.NET tools such as the DataAdapter that will automatically open and close
a connection for you when you call its Fill() method.  In such a case,
there'd be no way to interject your code to initialize your functions after
the connection was opened but before the SQL query was executed.



Reply | Threaded
Open this post in threaded view
|

Re: Extra functions - New Project?

Robert Simpson
In reply to this post by Mikey C
----- Original Message -----
From: "Mikey C" <[hidden email]>
To: <[hidden email]>
Sent: Wednesday, May 24, 2006 7:25 AM
Subject: Re: [sqlite] Extra functions - New Project?

[snip]
>
> This seems to work (I've tried it).
>
> HOWEVER, it means altering func.c and I was looking for how to add these
> functions in a separate C file without having to alter any existing code?
>
> Anyone any ideas how best to extend the codebase of SQLite with minimal
> alteration to existing code?

Here's the easiest way I can think of:

Add one more exported function in sqlite3 called sqlite3_open_ex() which
will call sqlite3_open() and then afterwards automatically register your new
functions with the sqlite3_create_function() API's.

Robert


Reply | Threaded
Open this post in threaded view
|

Re: Extra functions - New Project?

Robert Simpson
----- Original Message -----
From: "Robert Simpson" <[hidden email]>
To: <[hidden email]>
Sent: Wednesday, May 24, 2006 7:32 AM
Subject: Re: [sqlite] Extra functions - New Project?


>
> Here's the easiest way I can think of:
>
> Add one more exported function in sqlite3 called sqlite3_open_ex() which
> will call sqlite3_open() and then afterwards automatically register your
> new functions with the sqlite3_create_function() API's.
>
> Robert

Add one more thing to that step:  Fix sqlite3.def so that sqlite3_open_ex()
is exported as sqlite3_open() so Finisar and everyone else using the DLL
will end up calling the ex() function automagically.



Reply | Threaded
Open this post in threaded view
|

Re: Extra functions - New Project?

Jay Sprenkle
In reply to this post by Robert Simpson
On 5/24/06, Robert Simpson <[hidden email]> wrote:

> ----- Original Message -----
> From: "Jay Sprenkle" <[hidden email]>
> To: <[hidden email]>
> Sent: Wednesday, May 24, 2006 7:17 AM
> Subject: Re: [sqlite] Extra functions - New Project?
>
>
> >Flip that around and it's easier.
> >Write a dll that loads finisar then registers the new functions.
> >You don't have to modify anyone else's code that way.=0
>
> Can't do it that way.  Since Finsiar wraps sqlite and doesn't give you
> underlying access to any of the raw pointers, you won't be able to register
> your functions.  Even if it did let you have access, there are quite a few

bummer!
Reply | Threaded
Open this post in threaded view
|

Re: Extra functions - New Project?

Christian Smith-4
In reply to this post by Mikey C
On Wed, 24 May 2006, Mikey C wrote:

>
> I would rather add these functions directly to the core SQLite DLL in C in
> and compile them directly into the code (using a conditional).
>
> They then register this function by adding it to the array of existing
> functions:
>
> ...
>
> This seems to work (I've tried it).
>
> HOWEVER, it means altering func.c and I was looking for how to add these
> functions in a separate C file without having to alter any existing code?
>
> Anyone any ideas how best to extend the codebase of SQLite with minimal
> alteration to existing code?


You best bet is to open a ticket in CVSTrac:
http://www.sqlite.org/cvstrac/tktnew

Attach a patch to the ticket that implements your new functions. Send your
declaration of dedication of the code to the public domain to the list,
and hope DRH includes the patch in the next release.


>
> Cheers,
>
> Mike
>

Christian
Reply | Threaded
Open this post in threaded view
|

Re: Extra functions - New Project?

Roberto-10
On 24/05/06, Christian Smith <[hidden email]> wrote:
> Attach a patch to the ticket that implements your new functions. Send your
> declaration of dedication of the code to the public domain to the list,
> and hope DRH includes the patch in the next release.

IIRC, That has been suggested in the past, the consensus was to not
include extra functions, in keeping with the 'lite' in the project
name.
Reply | Threaded
Open this post in threaded view
|

Re: Extra functions - New Project?

Mikey C
Roberto-10 wrote
On 24/05/06, Christian Smith <csmith@thewrongchristian.org.uk> wrote:
> Attach a patch to the ticket that implements your new functions. Send your
> declaration of dedication of the code to the public domain to the list,
> and hope DRH includes the patch in the next release.

IIRC, That has been suggested in the past, the consensus was to not
include extra functions, in keeping with the 'lite' in the project
name.
I can see the argument for this, but these extra functions are part of the ANSI SQL-92 spec, so it is in keeping with the aim of achieving 100% SQL-92 compatibility?????

Otherwise you might say make it lighter, ditch triggers, views and most of the the other SQL already implemented?
Reply | Threaded
Open this post in threaded view
|

Re: Extra functions - New Project?

John Stanton-3
In reply to this post by Mikey C
We added some date functions into Sqlite, and it was a trivial exercise
because the function interface is tidy and easy to figure out.

What would be elegant is to have an ability to compile user written
functions into new versions of Sqlite without having to modify the
source of the new version.

A simple way to do that would be to have a conditional compile built
into the function tables in func.c so that user written modules could be
conditionally compiled in.  A quick glance at the code suggests that two
conditional compile points would be necessary, one in
sqlite3RegisterBuiltInFunctions and another elsewhere in the file to
include the code for the added functions.

The only downside seems to be that the table of function names is not
ordered and appears to be searched linearly, so making the table large
could pose a problem.  A change to make this an ordered table with a
binary search would solve that potential problem and allow a large set
of added functions.

If you were to define the string routines it would be a handy addition
to Sqlite to have a conditional compile which includes functions giving
compatibility with other much used DBMS's.  It would bloat Sqlite to
include such things as standard, which is why it should be an option.

Such an option would facilitate adding application functions to Sqlite.
  Since Sqlite links into the application, that would be a tidy way of
partitioning the application by integrating more complex business rules
etc with the SQL.

Mikey C wrote:

> Hi,
>
> I am in need of some new SQL functions and wanted to ask advice on the best
> way to integrate these functions into SQLite 3. I am not a proficient C
> coder unfortunately.
>
> I have compiled the source for 3.5.5 using Visual Studio.NET 2003 and all
> works fine.
>
> I have added a couple of simple functions into func.c and these work.  Great
> so far.
>
> However it would be good if there were a project somewhere to collate
> extension functions into a set of C files to enable a more powerful version
> of SQLite.
>
> I have  found a few already on the web. Eg.
>
> http://www.brayden.org/twiki/bin/view/Software/SqliteExtensions#SQLite_Extensions
>
>
> What I am looking for specifically are more powerful string manipulation
> functions that mimic Microsoft SQL Server.  In order of importance:
>
> charindex - This one is a show stopper for me.  Need this function badly.
> patindex
> ltrim
> rtrim
> replace
> difference (integer diff on soundex values)
>
> What is the best way forward?  Have someone develop these and add them
> directly to func.c or (to aid upgrading) create a new source and header file
> and add them to the project?  How can new functions be added without
> removing the ability to upgrade the source to 3.5.6 etc when patches are
> released to func.c?
>
> Does anyone know how these string functions might be implemented?
>
>
> Any help appreciated.
>
> Thanks,
>
> Mike
>
> --
> View this message in context: http://www.nabble.com/Extra+functions+-+New+Project--t1674436.html#a4539325
> Sent from the SQLite forum at Nabble.com.
>

Reply | Threaded
Open this post in threaded view
|

Re: Extra functions - New Project?

Jay Sprenkle
On 5/24/06, John Stanton <[hidden email]> wrote:
>
> A simple way to do that would be to have a conditional compile built
> into the function tables in func.c so that user written modules could be
> conditionally compiled in.  A quick glance at the code suggests that two
> conditional compile points would be necessary, one in
> sqlite3RegisterBuiltInFunctions and another elsewhere in the file to
> include the code for the added functions.


I'd like to see plugins added to Sqlite. It solves the issues with keeping the
software lightweight and defining user functionality. Let the user
include addons at run time. You might even be able to implement
stored procedures using this concept.

--
SqliteImporter, SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite
Reply | Threaded
Open this post in threaded view
|

Re: Extra functions - New Project?

John Stanton-3
In reply to this post by Mikey C
Repeatedly installing a set of functions is not a good approach unless
the application is persistent.  A particularly bad case is a very common
one, opening and closing an Sqlite DB in response to WWW requests.  Much
better that the functions be linked in with the Sqlite routines.

Mikey C wrote:

> Thanks for the response.
>
> I did think of this, but this is a pain since:
>
> 1. I am using the Finisar ADO.NET provider and to do this these functions
> would need to be registered every time the database connection is opened and
> closed and I don't want to have to mess with the ADO.NET provider code.
>
> 2. I would like these extra functions to always be availabe to me (and
> others), regardless of which project I am working on.
>
> 3. They help complete the SQL-92 features since these functions are defined
> in the standards (CharIndex in MS SQL Server is Position in SQL-92 spec)
>
> 4. I am not concerned with footprint size since I use SQLite on desktops and
> web servers where RAM and CPU power is not an issue.
>
> I guess there is a way to use a new C source file (e.g. funcext.c and
> funcext.h) for these extra functions and compile them in using conditional
> compilation?
>
> If anyone knows what funcext.c and funcext.h might look like I could get
> started on someone with good C coding skills to implement all the missing
> SQL-92 scalar and aggregate functions into these files.
>
> I would then put them out in the public domain under the same license as
> SQLite itself (ie. do what you like with them).
>
> Thanks
>
> Mike
> --
> View this message in context: http://www.nabble.com/Extra+functions+-+New+Project--t1674436.html#a4541011
> Sent from the SQLite forum at Nabble.com.
>

Reply | Threaded
Open this post in threaded view
|

Re: Extra functions - New Project?

John Stanton-3
In reply to this post by Mikey C
Mikey, I think that you are on the right track and placing your code in
the right place.

I probably have the core of many of the functions you want, all coded in
ANSI C which fits straight into Sqlite.  The conditional compile
approach would work very well and efficiently, applying no overhead to
speak of.  As I envisage it you would have a "myfuncs.c" file and a
conditional compile called say "SQLITE_USER_FUNCS" which if set in the
makefile would compile in your user functions to a new version of Sqlite.

You would just have to convince Dr Hipp to add the conditional compile
to the release code.

Mikey C wrote:

> I would rather add these functions directly to the core SQLite DLL in C in
> and compile them directly into the code (using a conditional).
>
> For example on the web I found an example of adding a sign() function:
>
> /*
> ** Implementation of the sign() function
> */
> static void signFunc(sqlite3_context *context, int argc, sqlite3_value
> **argv){
>   assert( argc==1 );
>   switch( sqlite3_value_type(argv[0]) ){
>     case SQLITE_INTEGER: {
>       i64 iVal = sqlite3_value_int64(argv[0]);
>  /* 1st change below. Line below was:  if( iVal<0 ) iVal = iVal * -1;     */
>
>       iVal = ( iVal > 0) ? 1: ( iVal < 0 ) ? -1: 0;
>       sqlite3_result_int64(context, iVal);
>       break;
>     }
>     case SQLITE_NULL: {
>       sqlite3_result_null(context);
>       break;
>     }
>     default: {
>  /* 2nd change below. Line for abs was: if( rVal<0 ) rVal = rVal * -1.0;  */
>
>       double rVal = sqlite3_value_double(argv[0]);
>       rVal = ( rVal > 0) ? 1: ( rVal < 0 ) ? -1: 0;
>       sqlite3_result_double(context, rVal);
>       break;
>     }
>   }
> }
>
> They then register this function by adding it to the array of existing
> functions:
>
>   } aFuncs[] = {
>     { "min",               -1, 0, SQLITE_UTF8,    1, minmaxFunc },
>     { "min",                0, 0, SQLITE_UTF8,    1, 0          },
>     { "max",               -1, 2, SQLITE_UTF8,    1, minmaxFunc },
>     { "max",                0, 2, SQLITE_UTF8,    1, 0          },
>     { "typeof",             1, 0, SQLITE_UTF8,    0, typeofFunc },
>     { "length",             1, 0, SQLITE_UTF8,    0, lengthFunc },
>     { "substr",             3, 0, SQLITE_UTF8,    0, substrFunc },
>     { "substr",             3, 0, SQLITE_UTF16LE, 0, sqlite3utf16Substr },
>     { "abs",                1, 0, SQLITE_UTF8,    0, absFunc    },
>     /*  Added here */
>     { "sign",               1, 0, SQLITE_UTF8,    0, signFunc   },
>     { "round",              1, 0, SQLITE_UTF8,    0, roundFunc  },
>     { "round",              2, 0, SQLITE_UTF8,    0, roundFunc  },
>
>
> This seems to work (I've tried it).
>
> HOWEVER, it means altering func.c and I was looking for how to add these
> functions in a separate C file without having to alter any existing code?
>
> Anyone any ideas how best to extend the codebase of SQLite with minimal
> alteration to existing code?
>
> Cheers,
>
> Mike
>
> --
> View this message in context: http://www.nabble.com/Extra+functions+-+New+Project--t1674436.html#a4542123
> Sent from the SQLite forum at Nabble.com.
>

Reply | Threaded
Open this post in threaded view
|

Re: Extra functions - New Project?

John Stanton-3
In reply to this post by Roberto-10
Roberto wrote:

> On 24/05/06, Christian Smith <[hidden email]> wrote:
>
>> Attach a patch to the ticket that implements your new functions. Send
>> your
>> declaration of dedication of the code to the public domain to the list,
>> and hope DRH includes the patch in the next release.
>
>
> IIRC, That has been suggested in the past, the consensus was to not
> include extra functions, in keeping with the 'lite' in the project
> name.
A very sound decision.  Bloat is the enemy of all good software.

A conditional compile point however would not bloat the product and
would make it easy for users to keep user functions and up-to-date
Sqlite releases.
Reply | Threaded
Open this post in threaded view
|

Re: Extra functions - New Project?

Jay Sprenkle
In reply to this post by John Stanton-3
On 5/24/06, John Stanton <[hidden email]> wrote:
> Repeatedly installing a set of functions is not a good approach unless
> the application is persistent.  A particularly bad case is a very common
> one, opening and closing an Sqlite DB in response to WWW requests.  Much
> better that the functions be linked in with the Sqlite routines.

I see it this way:
* plugins need not be loaded into memory until they're called. It adds almost
  no overhead unless the extended features are actually used. We did this with
  overlays in DOS many years ago and it worked very well.
* The OS will probably cache the plugins. CGI already
  achieves reasonable performance by relying on this
123