Re: [sqlite] Extra functions - New Project?
This is not directly related to this extra functions case, but I think that a few authors of such "drive by patches" would invest more time into refining their patches if they had some feedback as to whether they might be considered for inclusion in the main tree. Even if you marked the patch in CVSTrac as 'Rejected' with a reason why, this would be useful for the person volunteering the patch. Having said that, I'm find many drive-by patches quite useful even though they may never be candidates for the main tree or lack sufficient test cases or documentation. - Original Message From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Monday, October 23, 2006 7:08:56 AM Subject: Re: [sqlite] Extra functions - New Project? Mikey C <[EMAIL PROTECTED]> wrote: > > I sent the source code to DRH with the extra functions. I don't myself have > the time now to incorporate the extra functions into SQLite. > Writing code a chunk of code is only a small fraction of the work needed to support the code in a maintained product such as SQLite. Writing the code is, in fact, the easy part. After the code is written, somebody then has to develop regression tests that provide near 100% code coverage. The code has to be documented. Then it has to be maintained for years. By my estimate, writing code is perhaps 15% of the total work. The code for the extra functions was submitted to me with the promise that the author would provide no help in completing the work of integration. In other words, the author did about 15% of the work and left the other 85% to me. Such a submission is often referred to as a "drive by patch". I'm happy to have help on SQLite. But if you contribute code, you should finish the job. That means providing test cases that give 100% code coverage, documentation, and being available to support your code for years in the future. If you write a bunch of code and toss it over the wall, then please do not be disappointed if nobody picks it up. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Extra functions - New Project?
I entirely agree. I had the functions coded because I needed them for my own project. I never intended to do the other 85% of the work required to make them a supported part of SQLite. drh wrote: > > Mikey C <[EMAIL PROTECTED]> wrote: >> >> I sent the source code to DRH with the extra functions. I don't myself >> have >> the time now to incorporate the extra functions into SQLite. >> > > Writing code a chunk of code is only a small fraction of > the work needed to support the code in a maintained product > such as SQLite. Writing the code is, in fact, the easy > part. After the code is written, somebody then has to > develop regression tests that provide near 100% code > coverage. The code has to be documented. Then it has > to be maintained for years. By my estimate, writing code > is perhaps 15% of the total work. > > The code for the extra functions was submitted to me with > the promise that the author would provide no help in completing > the work of integration. In other words, the author did > about 15% of the work and left the other 85% to me. Such > a submission is often referred to as a "drive by patch". > > I'm happy to have help on SQLite. But if you contribute > code, you should finish the job. That means providing test > cases that give 100% code coverage, documentation, and being > available to support your code for years in the future. > If you write a bunch of code and toss it over the wall, > then please do not be disappointed if nobody picks it up. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > -- View this message in context: http://www.nabble.com/Extra-functions---New-Project--tf1674436.html#a6956723 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Extra functions - New Project?
Mikey C <[EMAIL PROTECTED]> wrote: > > I sent the source code to DRH with the extra functions. I don't myself have > the time now to incorporate the extra functions into SQLite. > Writing code a chunk of code is only a small fraction of the work needed to support the code in a maintained product such as SQLite. Writing the code is, in fact, the easy part. After the code is written, somebody then has to develop regression tests that provide near 100% code coverage. The code has to be documented. Then it has to be maintained for years. By my estimate, writing code is perhaps 15% of the total work. The code for the extra functions was submitted to me with the promise that the author would provide no help in completing the work of integration. In other words, the author did about 15% of the work and left the other 85% to me. Such a submission is often referred to as a "drive by patch". I'm happy to have help on SQLite. But if you contribute code, you should finish the job. That means providing test cases that give 100% code coverage, documentation, and being available to support your code for years in the future. If you write a bunch of code and toss it over the wall, then please do not be disappointed if nobody picks it up. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Extra functions - New Project?
Hi Rohit. I sent the source code to DRH with the extra functions. I don't myself have the time now to incorporate the extra functions into SQLite. I don't know if DRH plans to add the extra functions. If he does not and he doesn't mind, I am happy to send the source code to anyone that is interested. Regards, Mike RohitPatel wrote: > > Mike > > When are you planning to put code of your SQL functions for SQLite ? > > Waiting...eagerly... > I may try to use it in my app. > > Thanks > Rohit > > -- View this message in context: http://www.nabble.com/Extra-functions---New-Project--tf1674436.html#a6919718 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Extra functions - New Project?
Mike When are you planning to put code of your SQL functions for SQLite ? Waiting...eagerly... I may try to use it in my app. Thanks Rohit -- View this message in context: http://www.nabble.com/Extra-functions---New-Project--tf1674436.html#a6887312 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Extra functions - New Project?
Cool, I all ready have the code for a library of functions working with SQLite V3.3.5 source. This has been integrated into the SQLite code at compile time using conditional compilation. Math functions: acos asin atan atn2 atan2 acosh asinh atanh degrees radians cos sin tan cot cosh sinh tanh coth exp log log10 power sqrt square sign ceil floor pi String functions: replicate charindex charindex leftstr rightstr ltrim rtrim trim reverse proper padl padr padc strfilter difference Aggregate functions: stdev variance mode percentiles (median, lowerquartile, upperquartile) I will be releasing all the code after testing to the public domain. drh, are you interested in putting this code on your website? Thanks, Mike -- View this message in context: http://www.nabble.com/Extra-functions---New-Project--t1674436.html#a4789862 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Extra functions - New Project?
Mikey C <[EMAIL PROTECTED]> wrote: > > 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. > See http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions This is still a work in progress... -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Extra functions - New Project?
There is one from MySQL I use a lot: MD5() Which comes from this collection: http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html Very useful for implementing Transparent Databases Mikey C wrote: These are the functions that I tend to have implemented: Numeric Functions: Sqrt Floor Ceiling Sign Pi - constant function 3.141.. ACos ASin ATan Atn2 Cos Cot Degrees Exp Log Log10 Power Radians Sin Square Tan String Functions: Charindex Patindex Left Right LTrim RTrim Trim Replicate Reverse Replace Difference - numeric diff in Soundex values using built in soundex function. Aggregate Functions: StdDev Variance Median - Possibly a more flexible function Percentile where 0.5 is the Median Mode - Most frequently occuring value -- View this message in context: http://www.nabble.com/Extra+functions+-+New+Project--t1674436.html#a4563121 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Extra functions - New Project?
Mikey C wrote: StdDev Variance When implementing these, make sure *not* to use the "textbook" one-pass formula (mean of the squares - square of the mean); it simply doesn't work properly in floating point (you can find yourself subtracting one large number from another and losing most of your precision). I believe many versions of Excel made that mistake. There are some numerically stable one-pass algorithms for computing variance; search for them if you don't want to make two passes.
Re: [sqlite] Extra functions - New Project?
I tend to use SQLLite via the command line, feeding in queries from an external file. A non-standard SQL function I like, and don't think I've seen anywhere but an Aspentech product is SUBSTRING. It works in two ways 1 - Delimited strFoo = 'abcd efgh'; substring(1 of strFoo between ' ') = 'abcd' strFoo = 'abcd,efgh'; substring(2 of strFoo between ',') = 'efgh' 2 - Positional strFoo = 'abcdefgh'; substring(strFoo from 1 for 4) = 'abcd' substring(strFoo from 4 for 4) = 'defg' I'd be interested in seeing that. If I can be of help, feel free to ask. -alex On May 25, 2006, at 7:35 AM, Mikey C wrote: With some assistance I intend to implement pretty much all the SQL Server 2000 arithmetic and string functions into SQLite 3 codebase as well as a few others, such as aggregates for StdDev and Variance. I will then release the source under the same license as SQLite itself. If anyone has any comments or suggestions, please let me know. -- View this message in context: http://www.nabble.com/Extra+functions +-+New+Project--t1674436.html#a4559014 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Extra functions - New Project?
These are the functions that I tend to have implemented: Numeric Functions: Sqrt Floor Ceiling Sign Pi - constant function 3.141.. ACos ASin ATan Atn2 Cos Cot Degrees Exp Log Log10 Power Radians Sin Square Tan String Functions: Charindex Patindex Left Right LTrim RTrim Trim Replicate Reverse Replace Difference - numeric diff in Soundex values using built in soundex function. Aggregate Functions: StdDev Variance Median - Possibly a more flexible function Percentile where 0.5 is the Median Mode - Most frequently occuring value -- View this message in context: http://www.nabble.com/Extra+functions+-+New+Project--t1674436.html#a4563121 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Extra functions - New Project?
Good idea. I can help with code and ideas. Contact me at [EMAIL PROTECTED] Mikey C wrote: With some assistance I intend to implement pretty much all the SQL Server 2000 arithmetic and string functions into SQLite 3 codebase as well as a few others, such as aggregates for StdDev and Variance. I will then release the source under the same license as SQLite itself. If anyone has any comments or suggestions, please let me know. -- View this message in context: http://www.nabble.com/Extra+functions+-+New+Project--t1674436.html#a4559014 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Extra functions - New Project?
With some assistance I intend to implement pretty much all the SQL Server 2000 arithmetic and string functions into SQLite 3 codebase as well as a few others, such as aggregates for StdDev and Variance. I will then release the source under the same license as SQLite itself. If anyone has any comments or suggestions, please let me know. -- View this message in context: http://www.nabble.com/Extra+functions+-+New+Project--t1674436.html#a4559014 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Extra functions - New Project?
John Stanton wrote:
Jay Sprenkle wrote:
On 5/24/06, John Stanton <[EMAIL PROTECTED]> wrote:
> 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.
Conditional compiles won't help your user functions code migrate
to new versions unless you're very careful not to build in dependencies.
You must stick to the functions the API provides to ensure it will
work. I haven't found conditional compiles to be very helpful in that
regard.
Why add indirection when it is not necessary and is outside the concept
of the product? Sqlite is not a program like a WWW browser, it is a
library of functions. What is wrong with adding to the library?
Nothing at all. I threw it out as an option to consider. If you don't
want to consider it that's up to you.
The drawbacks:
* You have to understand how Sqlite works internally to do it.
* some overhead when functions are used
The benefits:
* Plugins enfore encapsulation so they're easier to write
* porting code to changing versions of the engine requires no effort
* The "minimalist" users and the "everything and the kitchen sink"
users can both have what they want.
Overlays were a nightmare, and have very thankfully been banished by
virtual memory systems. Why do you hold onto the concept? It is more
effective to have direct addressing and let the VM manager do what it
does best.
They worked fine for me. You must have gotten a poor implementation.
When people would complain about a poorly performing program experience
showed that one of the first culprits to look for was overlays.
Good luck on your project John.
Have a look at the Sqlite code dealing with functions, and you will see
that the area of code which would have the conditional compile has a
comment indicating that it has an external linkage. It also already has
conditional compiles for, inter alia, soundex, test functions etc. I
just propose a further use for what is a well thought out interface.
** This function registered all of the above C functions as SQL
** functions. This should be the only routine in this file with
** external linkage.
*/
void sqlite3RegisterBuiltinFunctions(sqlite3 *db){
static const struct {
char*zName;
signed char nArg;
u8 argType; /* 0: none. 1: db 2: (-1) */
u8 eTextRep;/* 1: UTF-16. 0: UTF-8 */
u8 needCollSeq;
void(*xFunc)(sqlite3_context*,int,sqlite3_value **);
} 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 },
#ifndef SQLITE_OMIT_UTF16
{ "substr", 3, 0, SQLITE_UTF16LE, 0, sqlite3utf16Substr },
#endif
{ "abs",1, 0, SQLITE_UTF8,0, absFunc},
{ "round", 1, 0, SQLITE_UTF8,0, roundFunc },
{ "round", 2, 0, SQLITE_UTF8,0, roundFunc },
{ "upper", 1, 0, SQLITE_UTF8,0, upperFunc },
{ "lower", 1, 0, SQLITE_UTF8,0, lowerFunc },
{ "coalesce", -1, 0, SQLITE_UTF8,0, ifnullFunc },
{ "coalesce", 0, 0, SQLITE_UTF8,0, 0 },
{ "coalesce", 1, 0, SQLITE_UTF8,0, 0 },
{ "ifnull", 2, 0, SQLITE_UTF8,1, ifnullFunc },
{ "random",-1, 0, SQLITE_UTF8,0, randomFunc },
{ "nullif", 2, 0, SQLITE_UTF8,1, nullifFunc },
{ "sqlite_version", 0, 0, SQLITE_UTF8,0, versionFunc},
{ "quote", 1, 0, SQLITE_UTF8,0, quoteFunc },
{ "last_insert_rowid", 0, 1, SQLITE_UTF8,0, last_insert_rowid },
{ "changes",0, 1, SQLITE_UTF8,0, changes},
{ "total_changes", 0, 1, SQLITE_UTF8,0, total_changes },
#ifdef SQLITE_SOUNDEX
{ "soundex",1, 0, SQLITE_UTF8, 0, soundexFunc},
#endif
#ifdef SQLITE_TEST
{ "randstr", 2, 0, SQLITE_UTF8, 0, randStr},
{ "test_destructor", 1, 1, SQLITE_UTF8, 0, test_destructor},
{ "test_destructor_count", 0, 0, SQLITE_UTF8, 0,
test_destructor_count},
{ "test_auxdata", -1, 0, SQLITE_UTF8, 0, test_auxdata},
{ "test_error",1, 0, SQLITE_UTF8, 0, test_error},
#endif
};
I just realized that adding functions is simpler than it originally
appeared. Sqlite
Re: [sqlite] Extra functions - New Project?
Jay Sprenkle wrote:
On 5/24/06, John Stanton <[EMAIL PROTECTED]> wrote:
> 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.
Conditional compiles won't help your user functions code migrate
to new versions unless you're very careful not to build in dependencies.
You must stick to the functions the API provides to ensure it will
work. I haven't found conditional compiles to be very helpful in that
regard.
Why add indirection when it is not necessary and is outside the concept
of the product? Sqlite is not a program like a WWW browser, it is a
library of functions. What is wrong with adding to the library?
Nothing at all. I threw it out as an option to consider. If you don't
want to consider it that's up to you.
The drawbacks:
* You have to understand how Sqlite works internally to do it.
* some overhead when functions are used
The benefits:
* Plugins enfore encapsulation so they're easier to write
* porting code to changing versions of the engine requires no effort
* The "minimalist" users and the "everything and the kitchen sink"
users can both have what they want.
Overlays were a nightmare, and have very thankfully been banished by
virtual memory systems. Why do you hold onto the concept? It is more
effective to have direct addressing and let the VM manager do what it
does best.
They worked fine for me. You must have gotten a poor implementation.
When people would complain about a poorly performing program experience
showed that one of the first culprits to look for was overlays.
Good luck on your project John.
Have a look at the Sqlite code dealing with functions, and you will see
that the area of code which would have the conditional compile has a
comment indicating that it has an external linkage. It also already has
conditional compiles for, inter alia, soundex, test functions etc. I
just propose a further use for what is a well thought out interface.
** This function registered all of the above C functions as SQL
** functions. This should be the only routine in this file with
** external linkage.
*/
void sqlite3RegisterBuiltinFunctions(sqlite3 *db){
static const struct {
char*zName;
signed char nArg;
u8 argType; /* 0: none. 1: db 2: (-1) */
u8 eTextRep;/* 1: UTF-16. 0: UTF-8 */
u8 needCollSeq;
void(*xFunc)(sqlite3_context*,int,sqlite3_value **);
} 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 },
#ifndef SQLITE_OMIT_UTF16
{ "substr", 3, 0, SQLITE_UTF16LE, 0, sqlite3utf16Substr },
#endif
{ "abs",1, 0, SQLITE_UTF8,0, absFunc},
{ "round", 1, 0, SQLITE_UTF8,0, roundFunc },
{ "round", 2, 0, SQLITE_UTF8,0, roundFunc },
{ "upper", 1, 0, SQLITE_UTF8,0, upperFunc },
{ "lower", 1, 0, SQLITE_UTF8,0, lowerFunc },
{ "coalesce", -1, 0, SQLITE_UTF8,0, ifnullFunc },
{ "coalesce", 0, 0, SQLITE_UTF8,0, 0 },
{ "coalesce", 1, 0, SQLITE_UTF8,0, 0 },
{ "ifnull", 2, 0, SQLITE_UTF8,1, ifnullFunc },
{ "random",-1, 0, SQLITE_UTF8,0, randomFunc },
{ "nullif", 2, 0, SQLITE_UTF8,1, nullifFunc },
{ "sqlite_version", 0, 0, SQLITE_UTF8,0, versionFunc},
{ "quote", 1, 0, SQLITE_UTF8,0, quoteFunc },
{ "last_insert_rowid", 0, 1, SQLITE_UTF8,0, last_insert_rowid },
{ "changes",0, 1, SQLITE_UTF8,0, changes},
{ "total_changes", 0, 1, SQLITE_UTF8,0, total_changes },
#ifdef SQLITE_SOUNDEX
{ "soundex",1, 0, SQLITE_UTF8, 0, soundexFunc},
#endif
#ifdef SQLITE_TEST
{ "randstr", 2, 0, SQLITE_UTF8, 0, randStr},
{ "test_destructor", 1, 1, SQLITE_UTF8, 0, test_destructor},
{ "test_destructor_count", 0, 0, SQLITE_UTF8, 0,
test_destructor_count},
{ "test_auxdata", -1, 0, SQLITE_UTF8, 0, test_auxdata},
{ "test_error",1, 0, SQLITE_UTF8, 0, test_error},
#endif
};
Re: [sqlite] Extra functions - New Project?
On 5/24/06, John Stanton <[EMAIL PROTECTED]> wrote: > 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. Conditional compiles won't help your user functions code migrate to new versions unless you're very careful not to build in dependencies. You must stick to the functions the API provides to ensure it will work. I haven't found conditional compiles to be very helpful in that regard. Why add indirection when it is not necessary and is outside the concept of the product? Sqlite is not a program like a WWW browser, it is a library of functions. What is wrong with adding to the library? Nothing at all. I threw it out as an option to consider. If you don't want to consider it that's up to you. The drawbacks: * You have to understand how Sqlite works internally to do it. * some overhead when functions are used The benefits: * Plugins enfore encapsulation so they're easier to write * porting code to changing versions of the engine requires no effort * The "minimalist" users and the "everything and the kitchen sink" users can both have what they want. Overlays were a nightmare, and have very thankfully been banished by virtual memory systems. Why do you hold onto the concept? It is more effective to have direct addressing and let the VM manager do what it does best. They worked fine for me. You must have gotten a poor implementation. Good luck on your project John.
Re: [sqlite] Extra functions - New Project?
Jay Sprenkle wrote: On 5/24/06, John Stanton <[EMAIL PROTECTED]> 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 Overlays were a nightmare, and have very thankfully been banished by virtual memory systems. Why do you hold onto the concept? It is more effective to have direct addressing and let the VM manager do what it does best.
Re: [sqlite] Extra functions - New Project?
Jay Sprenkle wrote: On 5/24/06, John Stanton <[EMAIL PROTECTED]> 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. Why add indirection when it is not necessary and is outside the concept of the product? Sqlite is not a program like a WWW browser, it is a library of functions. What is wrong with adding to the library?
Re: [sqlite] Extra functions - New Project?
Roberto wrote: On 24/05/06, Christian Smith <[EMAIL PROTECTED]> 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.
Re: [sqlite] Extra functions - New Project?
On 5/24/06, John Stanton <[EMAIL PROTECTED]> 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
Re: [sqlite] Extra functions - New Project?
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.
Re: [sqlite] Extra functions - New Project?
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.
Re: [sqlite] Extra functions - New Project?
Roberto-10 wrote: > > On 24/05/06, Christian Smith <[EMAIL PROTECTED]> 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? -- View this message in context: http://www.nabble.com/Extra+functions+-+New+Project--t1674436.html#a4543591 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Extra functions - New Project?
On 24/05/06, Christian Smith <[EMAIL PROTECTED]> 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.
Re: [sqlite] Extra functions - New Project?
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
Re: [sqlite] Extra functions - New Project?
On 5/24/06, Robert Simpson <[EMAIL PROTECTED]> wrote: - Original Message - From: "Jay Sprenkle" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> 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!
Re: [sqlite] Extra functions - New Project?
- Original Message - From: "Robert Simpson" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> 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.
Re: [sqlite] Extra functions - New Project?
- Original Message - From: "Mikey C" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> 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
Re: [sqlite] Extra functions - New Project?
- Original Message - From: "Jay Sprenkle" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> 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.
Re: [sqlite] Extra functions - New Project?
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.
Re: [sqlite] Extra functions - New Project?
On 5/24/06, Robert Simpson <[EMAIL PROTECTED]> 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.
Re: [sqlite] Extra functions - New Project?
- Original Message - From: "Jay Sprenkle" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> 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
Re: [sqlite] Extra functions - New Project?
On 5/24/06, Mikey C <[EMAIL PROTECTED]> 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.
Re: [sqlite] Extra functions - New Project?
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.
Re: [sqlite] Extra functions - New Project?
On 24/05/06, Mikey C <[EMAIL PROTECTED]> 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.
[sqlite] Extra functions - New Project?
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.
