Re: [sqlite] Memory databases

[Joe Wilson]

--- Unit 5 <[EMAIL PROTECTED]> wrote:
> I have a couple of questions on :memory: databases:
> 
> 1) What happens when a table grows in size more than
> the available RAM?  Does sqlite revert to file based
> mechanism to handle it or would it throw an error?

If your OS is Windows or UNIX, your system will just trash
and become unresponsive long before SQLite returns an out
of memory error. This is due to the magic of virtual memory.
At least this is my experience with temp store = memory.

> 2) From some of the other posters who have done more
> testing than I, it seems that there is not much
> difference in performance between :memory: and regular
> file based databases.  Is that so?  in what
> circumstances would the memory database perform
> better?

File store, strangely, seems to have a slight performance 
edge over :memory: in the most recent CVS version of SQLite
for many operations. Why this is the case is a bit of a 
mystery to me. It's not logical that the code path for file
page manipulation (which includes operating system calls) be 
faster than memory pages under any circumstance. I wonder if
memory pages are being accidentally double cached or something
equally weird.

Memory store used to be considerably slower than file store
in SQLite3 until two memory page cache issues were fixed after 
the 3.3.5 release.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] GROUP BY regression workaround?

[Joe Wilson]

> In the meantime, you can work around the problem by
> implementing the GROUP BY function in your application
> code.  Remove the aggregate functions and GROUP BY
> clause from your query and just return every row of
> the intermediate table, then compute the aggregates
> and grouping yourself.  This should be just as fast
> as the old algorithm in your case.

Ah, so that's why someone was asking for a GROUP_CONCAT() 
to be added to the SQLite codebase.

  http://www.sqlite.org/cvstrac/tktview?tn=1779
  http://perlcabal.org/~autrijus/tmp/sqlite3-src-func.c.diff

I did not realize what it was for until your suggestion.
They likely encountered the same issue. Their patch was made
against 3.2.7 (after the GROUP BY algorithm change) and it 
apparently offered them a 20X speed increase.

Although the users of my system prefer to use standard SQL,
I can either use something similar to that as a workaround
or continue using 3.2.1 until I absolutely need a feature
or bugfix in the latest version.

Thanks for your response and suggestion.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] Re: updating with unique match

[David Bicking]

On Wed, 2006-05-24 at 11:20 -0400, Igor Tandetnik wrote:
> David Bicking  wrote:
> > This is more an SQL than SQLITE question.
> >

> Something like this:
> 
> update requests r set psref =
> (select psref from actuals a where r.cust=a.cust and 
> r.amount=a.amount)
> where r.psref is null and
> not exists (select * from requests r1 where
> r.req_id != r1.req_id and r.cust=r1.cust and r.amount=r1.amount 
> and r1.psref is null)
> and 1 = (select count(*) from actuals a1 where r.cust=a1.cust and 
> r.amount=a1.amount);
> 
Thanks, I would never have come up with this. Heck, I have to strain to
understand exactly what it is doing.

> > This is what I have come up with, but it doesn't discount actuals that
> > have already been matched.
> >
> > UPDATE REQUESTS R JOIN ACTUALS A
> > ON R.CUST = A.CUST AND R.AMOUNT = A.AMOUNT
> 
> Have you tried this query in SQLite? I don't believe it supports this 
> syntax (a join in the update statement). It does not work for me, but 
> perhaps I'm not running the latest version.
> 
No, I hadn't tried it in Sqlite yet. Something in the back of my mind
was saying I saw on the list that that doesn't work in Sqlite. I'm more
familiar with another dialect of SQL and that does work there.

> > Secondly, for each match made, I need to launch an external document
> > (an excternal file), so I can manually type the PSREF in to that
> > document and run a macro.
> >
> > I figure for the launch requirement, I need to run two queries, the
> > first a select of the matches it could make, which I can step through
> > and do what I need to do, then the second an update query to actually
> > update the request table, or can I step through the update query and
> > "see" what it is updating?
> 
> You can define an ON UPDATE trigger on requests table. This trigger may 
> invoke a custom function you write.
> 

Nice idea. To implement, I would create a function and register it with
sqlite, then call the function in the trigger?

> Igor Tandetnik 
> 

Thanks Igor.

David

Re: [sqlite] GROUP BY regression workaround?

[drh]

Joe Wilson <[EMAIL PROTECTED]> wrote:
> 
> The old GROUP BY algorithm was extremely efficient whether or 
> not the GROUP BY terms were indexable or not.  
> 

There were cases where the old algorithm performed
very, very poorly.  You have found a case where the
new algorithm performs poorly, though I think the
performance degradation is not nearly as bad as 
some of the pathological cases with the old
algorithm.

I'm sorry the new algorithm displeases you.  I will
make a mental note to try to come up with a better
way to do GROUP BY that always works well.

Please note that going back to the old algorithm is
not an option.  Also note that a new algorithm (or
going back to the old algorithm) is a large change,
requiring weeks of work, and that I have other 
things that will take priority.  So the current 
algorithm seems likely to be your only option for 
the foreseeable future.

In the meantime, you can work around the problem by
implementing the GROUP BY function in your application
code.  Remove the aggregate functions and GROUP BY
clause from your query and just return every row of
the intermediate table, then compute the aggregates
and grouping yourself.  This should be just as fast
as the old algorithm in your case.

--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] GROUP BY regression workaround?

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> > Joe Wilson <[EMAIL PROTECTED]> wrote:
> > > Anyone have any ideas how to speed up GROUP BY on huge views
> > > in recent versions of SQLite?
> > >
> > >   http://www.sqlite.org/cvstrac/tktview?tn=1809
> > >
> > > The older versions of SQLite (prior to SQLite 3.2.6) used to
> > > perform GROUP BY operations in the main table loop, grouping
> > > rows as it went along. But the new version appears to create
> > > a huge temp table of results, sort them, and only then determine
> > > the groups. This takes up a massive amount of temp store.
> > > The old GROUP BY algorithm excels in the case where there are
> > > a lot of groupable rows in the dataset; i.e., the typical case.
> > >
> >
> > The old algorithm required enough memory to hold the
> > entire results set.  The new algorithm uses bounded
> > memory, which is a very important advantage if you
> > have a GROUP BY with a large result set.

Temp store certainly is not bounded.

There's a huge increase in temp store due to the new algorithm 
used in GROUP BY. In one simple case temp store memory use is 
190 times larger - from 1.2M to 230M.  I did not mention the 
cases that do not run at all with the latest SQLite due to lack 
of disk space. The same cases run in under a minute in SQLite 
3.2.1 using < 100 megs of RAM and no temp store.

As you know, the old GROUP BY algorithm did not hold the 
entire intermediate result set in memory - just the unique rows 
and aggregated intermediate results. The new algorithm no longer 
employs this extremely efficient time/space optimization.

> > The new algorithm will run much faster if you have
> > an index on the GROUP BY term(s).

...but if you cannot use an index because the items being 
SELECTed/GROUPed are expressions, you pay a very large penalty
with the new GROUP BY algorithm - the query can run 100 times 
slower or more if your data set is in the tens of millions of 
rows.

The old GROUP BY algorithm was extremely efficient whether or 
not the GROUP BY terms were indexable or not.  

Can't the old 3.2.5 algorithm for GROUP BY be reinstated but with 
its temporary intermediate results stored in temp store instead of 
memory? That way you get the best features of both algorithms.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

RE: [sqlite] In the year 4461763

[Chris Werner]

On Wednesday, May 24 2006, Christian Smith wrote:

> Datetime in SQLite is represented as a 64 bit floating point 
> value. The units are seconds since the unix epoch.
>
> What you're seeing is the limited precision (48 bits I believe)
> of 64 bit floating point numbers. Not a problem for real world
> values, but a problem if high sub-second precision or long 
> distant dates are needed.


A reasonable explanation, and the insight I was seeking.
Thank you.
Christian Werner

PS: The code and comments in date.c make for a good read.

Re: [sqlite] Extra functions - New Project?

[John Stanton]

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 does

Re: [sqlite] Extra functions - New Project?

[John Stanton]

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?

[Jay Sprenkle]

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?

[John Stanton]

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?

[John Stanton]

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?

[John Stanton]

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?

[Jay Sprenkle]

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?

[John Stanton]

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] one table per file?

[John Stanton]

You can have one table per file with Sqlite by have multiple databases, 
each one with one table and then ATTACHing them.  You might, however, be 
better off with one file and multiple tables.


Petr Krenzelok wrote:

Hi,

sorry if my question is kind of stupid, but I would like to ask 
following. I am new here, and have not found any such topic in ML 
history, so:


I am coming from REBOL language land. We now have wrapper for SQLite. It 
is very cool, tiny database, kind of free form (no columns constraints), 
which fits REBOL very well. With REBOL we had in-memory database, but 
the organisation was - one table per file. While everything in one file 
is not bad either for smaller systems, I am used to simplicity, so e.g. 
copying particular tables to backup. That is the reason I am not too 
comfort with SQLite aproach, although I believe it is just matter of taste.


Anyway - is there any possibility to have one table per file aproach?

Thanks,
Petr

Re: [sqlite] Extra functions - New Project?

[John Stanton]

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.

[sqlite] Compiling a Library or Mac OS X using Xcode

[Jerry Krinock]

Apple is currently shipping SQLite 3.1.3 in Mac OS 10.4, but SQLite 3.3.5 is
much better.  Anybody with a Mac and Developer Tools installed can build a
the latest version of SQLite as a library in 5 minutes.

The hard part was getting my project to use it instead of
/usr/lib/libsqlite3.dylib (version 3.1.3) which comes with Mac OS 10.4.
According to some advice I got from Apple DTS, to "insure" that your app to
uses the version in its package instead of a dylib installed on the system
using the same name and symbol set, you should build and ship it as a
*static* library.

The following builds a static library of sqlite's C API from the latest
source code, which you can then use in other Xcode projects.  (It does not
build the command-line tool.)

*  Activate Xcode.  I have Xcode 2.2.1.
*  menu File > New Project > Static Library > BSD Static Library.  Name the
project "sqlite3".  (Xcode will add the prefix "lib" and the extension ".a"
to the product).
*  Browse to http://sqlite.org/download.html#cvs
*  Download the link named something like "sqlite-source-x_x_x.zip" which is
described as the "pure C code".
*  You will get a folder named something like "sqlite-source-X_X_X" which
contains .c, .h and one .def files.
*  Move this folder into the project folder of your new Xcode project.
*  In Xcode project window, select the "sqlite3" group at the top and click
menu > Project > Add to project.
*  Navigate to and choose the "sqlite-source-X_X_X" directory in your new
project's folder.
*  In Xcode project window, select the "sqlite3" group at the top, click the
gear pulldown menu and select Add > New Group.
*  Give your new group a name, I suggest: "Frameworks".
*  Select this new group and click menu > Project > Add to project.
*  Navigate to and choose /System/Library/Frameworks/Tcl.framework
*  In Xcode, menu > Project > Set Active Build Configuration > Release
*  In the Groups and Files pane, expand Target and doubleclick on sqlite3 to
show the target settings.  Click the "Build" pane.  Find "Installation
Directory" and change it from "/usr/local/lib" to
"@executable_path/../Libraries"
*  In the Groups and Files pane, expand Products > sqlite > Copy Headers.
Change the Role of sqlite.h to "public".
*  cmd-B to Build.
*  You'll get four warnings about "build" directories not existing, but
ignore these.
*  You should get "Build succeeded".
*  cmd-B again and you should immediately get a clean "Build succeeded"
because the warned-about "build" directories will have been created during
the first build.

In any project which depends directly on this sqlite build,

*  Add the just-built libsqlite3.a to your project
*  In the Target, Build settings, find the "Other Linker Flags" Setting and
enter the Value "-Wl,-search_paths_first".
*  While you're there, check that the path to it has been added in the
Target > Build > Libary Search Paths
*  #include "sqlite3.h" where needed.

In the final product project (which may be the same as above)

*  Add a "Shell Script Build Phase" to copy the libsqlite3.a you have
produced to the product's Contents/Libraries/.  (There may be other ways to
"Copy Files", but I don't trust Xcode.  I like my shell scripts because I
know what they're going to do.)
*  To verify that everything worked, log the value of the global char*
variable sqlite3_version.

The Tcl framework which I used is symlinked to the latest version of Tcl,
which is 8.4 in Mac OS 10.4.  The product of the above procedure seems to
work in Mac OS 10.3 too; at least, the library loads and responds to
sqlite_version() when the app runs.  I have not done any real testing in
10.3 yet.

Re: [sqlite] NOT NULL in create table command not work

[Will Leshner]

On 5/23/06, Nguyen Dang Quang <[EMAIL PROTECTED]> wrote:


But my application still insert emty string into DIRECTORY field


An empty string? Or NULL? There is a difference.

Re: [sqlite] Extra functions - New Project?

[Jay Sprenkle]

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.

--
SqliteImporter, SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Re: [sqlite] Extra functions - New Project?

[John Stanton]

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] List of functions

[Ran]

http://www.sqlite.org/lang_expr.html

On 5/24/06, Unit 5 <[EMAIL PROTECTED]> wrote:


I see references to typecasting functions here in the
mailing list.  I have not found where they are
discussed on the website.  I saw some of them in the
"expressions" page but seems to cover a subset of
them.

Is there a page that provides a list of all supported
functions?  These could be typecasting as well as
other mathematical functions.

Thanks in advance!


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

[sqlite] Memory databases

[Unit 5]

I have a couple of questions on :memory: databases:

1) What happens when a table grows in size more than
the available RAM?  Does sqlite revert to file based
mechanism to handle it or would it throw an error?

2) From some of the other posters who have done more
testing than I, it seems that there is not much
difference in performance between :memory: and regular
file based databases.  Is that so?  in what
circumstances would the memory database perform
better?



__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

[sqlite] List of functions

[Unit 5]

I see references to typecasting functions here in the
mailing list.  I have not found where they are
discussed on the website.  I saw some of them in the
"expressions" page but seems to cover a subset of
them. 

Is there a page that provides a list of all supported
functions?  These could be typecasting as well as
other mathematical functions.

Thanks in advance!


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] Extra functions - New Project?

[Mikey C]

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] In the year 4461763

[Christian Smith]

On Tue, 23 May 2006, Chris Werner wrote:


Hello,

I am just curious about SQLite's date and time manipulation functions. I am
using the sqlite3 command line interface on the above described platform.



When I select a datetime for 2^47:

sqlite> SELECT datetime(140737488355328, 'unixepoch');

4461763-06-20 05:22:07



Adding a second to the time  [2^47 +1] seems to add 2 seconds to the
return??

sqlite> SELECT datetime(140737488355329, 'unixepoch');

4461763-06-20 05:22:09



I suspect an overflow of some sort, but I cannot phrase it concisely.



Datetime in SQLite is represented as a 64 bit floating point value. The 
units are seconds since the unix epoch.


What you're seeing is the limited precision (48 bits I believe) of 64 bit 
floating point numbers. Not a problem for real world values, but a problem 
if high sub-second precision or long distant dates are needed.







Date time seems to return reasonable values up until  185327782012799 [you
tell me], after which the return format is not a valid date.

Again, I suspect an overflow of some sort, can anyone explain?



sqlite> SELECT datetime(185327782012799, 'unixepoch');

5874773-08-15 23:59:58

sqlite> SELECT datetime(185327782012800, 'unixepoch');

-5884205--1-00 00:00:00




Not sure about this one. Check out computeYMD() in date.c. It has some 
pretty funky calculations to work out the year from the time. I won't 
pretend to pretend what all the figures are for, but there is probably 
some 32 bit integer overflow that messes up the calculations.


Potential bug note:
I notice all the variables in computeYMD() and computeJD() use integer 
intermediate values, yet all the intermediate values might be best off 
being held in real value variables due to the use of floating point 
arithmatic. Is there a real threat of wrong dates coming from this?


I can understand the use of integers from a performance POV, and some 
small embedded processors have no FPU. Perhaps there could be a compile 
time flag to choose between ints and doubles for these intermediate 
results?








Not a critical item, nor important enough to be reported as a bug,

Just curious,

Christian Werner

[sqlite] Re: updating with unique match

[Igor Tandetnik]

David Bicking  wrote:

This is more an SQL than SQLITE question.

I have two tables: (The relevant fields are)

CREATE TABLE REQUESTS (
REQ_ID INTEGER PRIMARY KEY,
CUST TEXT,
AMOUNT FLOAT,
PSREF TEXT)

CREATE TABLE ACTUALS (
PSREF TEXT,
CUST TEXT,
AMOUNT FLOAT )


SELECT * FROM REQUESTS;
1 | 100 | 1.01 |
2 | 200 | 2.02 |
3 | 200 | 2.02 |
4 | 300 | 3.03 | 700
5 | 300 | 3.03 |
6 | 400 | 4.04 |

SELECT * FROM ACTUALS;
700 | 300 | 3.03
701 | 100 | 1.01
702 | 200 | 2.02
703 | 300 | 3.03
704 | 400 | 4.04
705 | 400 | 4.04

Now, what I want to do is to update requests with the PSREF value from
actuals, if the request is already set and there is one and only one
unmatched request and one and only one Actual with the same cust and
amount.

SELECT * FROM REQUESTS;  -- after update
1 | 100 | 1.01 | 701  can match because there is only one actual
2 | 200 | 2.02 |  since there are two 200/2.02 requests, can't
tell 3 | 200 | 2.02 | which the actual actually matches.
4 | 300 | 3.03 | 700
5 | 300 | 3.03 | 703  can match since the 700 actual was already
matched 6 | 400 | 4.04 |  can't match since there are 2 actuals
that fit


Something like this:

update requests r set psref =
   (select psref from actuals a where r.cust=a.cust and 
r.amount=a.amount)

where r.psref is null and
   not exists (select * from requests r1 where
   r.req_id != r1.req_id and r.cust=r1.cust and r.amount=r1.amount 
and r1.psref is null)
   and 1 = (select count(*) from actuals a1 where r.cust=a1.cust and 
r.amount=a1.amount);



This is what I have come up with, but it doesn't discount actuals that
have already been matched.

UPDATE REQUESTS R JOIN ACTUALS A
ON R.CUST = A.CUST AND R.AMOUNT = A.AMOUNT


Have you tried this query in SQLite? I don't believe it supports this 
syntax (a join in the update statement). It does not work for me, but 
perhaps I'm not running the latest version.



Secondly, for each match made, I need to launch an external document
(an excternal file), so I can manually type the PSREF in to that
document and run a macro.

I figure for the launch requirement, I need to run two queries, the
first a select of the matches it could make, which I can step through
and do what I need to do, then the second an update query to actually
update the request table, or can I step through the update query and
"see" what it is updating?


You can define an ON UPDATE trigger on requests table. This trigger may 
invoke a custom function you write.


Igor Tandetnik 

Re: [sqlite] Extra functions - New Project?

[Roberto]

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?

[Christian Smith]

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

[sqlite] Re: one table per file?

[Igor Tandetnik]

Petr Krenzelok <[EMAIL PROTECTED]> wrote:

Anyway - is there any possibility to have one table per file aproach?


http://www.sqlite.org/lang_attach.html

Igor Tandetnik

Re: [sqlite] Extra functions - New Project?

[Jay Sprenkle]

On 5/24/06, Robert Simpson <[EMAIL PROTECTED]> wrote:

- Original Message -
From: "Jay Sprenkle" <[EMAIL PROTECTED]>
To: 
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?

[Robert Simpson]

- Original Message - 
From: "Robert Simpson" <[EMAIL PROTECTED]>

To: 
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?

[Robert Simpson]

- Original Message - 
From: "Mikey C" <[EMAIL PROTECTED]>

To: 
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?

[Robert Simpson]

- Original Message - 
From: "Jay Sprenkle" <[EMAIL PROTECTED]>

To: 
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?

[Mikey C]

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?

[Jay Sprenkle]

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?

[Robert Simpson]

- Original Message - 
From: "Jay Sprenkle" <[EMAIL PROTECTED]>

To: 
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

[sqlite] one table per file?

[Petr Krenzelok]

Hi,

sorry if my question is kind of stupid, but I would like to ask 
following. I am new here, and have not found any such topic in ML 
history, so:


I am coming from REBOL language land. We now have wrapper for SQLite. It 
is very cool, tiny database, kind of free form (no columns constraints), 
which fits REBOL very well. With REBOL we had in-memory database, but 
the organisation was - one table per file. While everything in one file 
is not bad either for smaller systems, I am used to simplicity, so e.g. 
copying particular tables to backup. That is the reason I am not too 
comfort with SQLite aproach, although I believe it is just matter of taste.


Anyway - is there any possibility to have one table per file aproach?

Thanks,
Petr

Re: [sqlite] Extra functions - New Project?

[Jay Sprenkle]

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] Patch to let SQLite run on OS/2 again

[Peter Weilbacher (Mozilla)]

On Wed, 24 May 2006 08:15:12 -0400, [EMAIL PROTECTED] wrote:

>Yes.  In order to directly import your patch, I need to have on 
>file a signed copyright release from anybody who has contributed
>to the patch.  The form must also be signed by your employer.
>See http://www.sqlite.org/copyright.html for additional information
>and links for downloading suitable copyright release forms.

Thanks for the hint. Will see what I can do about that. Fortunately, I did 
this in my spare time, that should simplify the procedure a bit. :-)

>> Will I get emails automatically when that bug entry is 
>> updated? (I input my email into the Trac form but it doesn't show up 
>> there now.)
>
>No.  You have to poll CVSTrac to monitor the progress of the
>ticket.  Email addresses are not displayed (except to authorized
>developers) to prevent them from being harvested by spammers.
>I can see your email address though the public at large cannot.

OK.

Cheers,
   Peter.

Re: [sqlite] 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
--
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] "SQL logic error or missing database"

[Ran]

Actually, the multiple connections are created from different threads. But
those threads did not access the database in the same moment when the
problem occured, so I assumed (correctly) that the bug happens also when the
connections are done from the same thread. And this is how I created the
script that demonstrated the problem.

Currently the problem is fixed according to the guidelines I got from all of
you. This is how I now step:

Have a prepared statement.
while (true) {
 try to step the prepared statement.
 If failed to step {
reset the prepared statement to get the correct error.
if the error is not SQLITE_SCHEMA {
  deal with the error and break from the loop.
}
if the error is SQLITE_SCHEMA {
  finalize the prepared statement.
  prepare the statement again.
  continue in the loop (so step again with the newly prepared
statement).
}
  }
  if the step was successful - break from the loop and continue as usuall
after a successful
  step.
}

I write it here because I could not find an explanation like this in the
documentation (did I miss it somewhere?). Obviously, if anyone still finds
mistakes above, I will be happy if those mistakes are explained.

In addition, I think it might be helpful for others to add the comment that
sqlite3_errmsg() does not return the correct text till sqlite3_reset() or
sqlite3_finalize() are called.

Thanks again,

Ran

On 5/23/06, John Stanton <[EMAIL PROTECTED]> wrote:


Why do you connect twice to the DB?  You then run into synchronization
issues.
JS

[sqlite] updating with unique match

[David Bicking]

This is more an SQL than SQLITE question.

I have two tables: (The relevant fields are)

CREATE TABLE REQUESTS (
REQ_ID INTEGER PRIMARY KEY,
CUST TEXT,
AMOUNT FLOAT,
PSREF TEXT)

CREATE TABLE ACTUALS (
PSREF TEXT,
CUST TEXT,
AMOUNT FLOAT )


SELECT * FROM REQUESTS;
1 | 100 | 1.01 | 
2 | 200 | 2.02 | 
3 | 200 | 2.02 |
4 | 300 | 3.03 | 700
5 | 300 | 3.03 |
6 | 400 | 4.04 |

SELECT * FROM ACTUALS;
700 | 300 | 3.03
701 | 100 | 1.01
702 | 200 | 2.02
703 | 300 | 3.03
704 | 400 | 4.04
705 | 400 | 4.04

Now, what I want to do is to update requests with the PSREF value from
actuals, if the request is already set and there is one and only one
unmatched request and one and only one Actual with the same cust and
amount.

SELECT * FROM REQUESTS;  -- after update
1 | 100 | 1.01 | 701  can match because there is only one actual
2 | 200 | 2.02 |  since there are two 200/2.02 requests, can't tell
3 | 200 | 2.02 | which the actual actually matches.
4 | 300 | 3.03 | 700
5 | 300 | 3.03 | 703  can match since the 700 actual was already matched
6 | 400 | 4.04 |  can't match since there are 2 actuals that fit

I figure I need sub-queries, but can't figure out how to state it.
A complication is that the Amount is a float and thus joins on it are
not always accurate.

This is what I have come up with, but it doesn't discount actuals that
have already been matched.

UPDATE REQUESTS R JOIN ACTUALS A 
ON R.CUST = A.CUST AND R.AMOUNT = A.AMOUNT
SET R.PSREF = A.PSREF
WHERE R.PSREF IS NULL
AND R.REQ_ID IN (SELECT R.REQ_ID FROM REQUESTS R JOIN ACTUALS A
ON R.CUST = A.CUST AND R.AMOUNT = A.AMOUNT
GROUP BY R.CUST, R.AMOUNT
WHERE R.PSREF IS NULL
HAVING COUNT(*)=1)

Secondly, for each match made, I need to launch an external document (an
excternal file), so I can manually type the PSREF in to that document
and run a macro.

I figure for the launch requirement, I need to run two queries, the
first a select of the matches it could make, which I can step through
and do what I need to do, then the second an update query to actually
update the request table, or can I step through the update query and
"see" what it is updating?

Thanks for any pointers/help you can give,
David

Re: [sqlite] Extra functions - New Project?

[Roberto]

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.

Re: [sqlite] Patch to let SQLite run on OS/2 again

[drh]

"Peter Weilbacher (Mozilla)" <[EMAIL PROTECTED]> wrote:
> I am not really sure if this is the place to discuss this. But the 
> SQLite webpage doesn't list any developer related lists or contacts, so 
> I give it a try.
> 
> We (that is mainly Daniel Lee Kruse, with a little bit of help from Andy
> Willis and me) have re-ported SQLite to the OS/2 platform. My interest is 
> mainly that Mozilla uses SQLite in its backend and we want to continue 
> to run the Mozilla apps on OS/2. I didn't find any documentation on what
> I have to do to get patches into the SQLite CVS source tree and the next
> release. So I uploaded the patch for the port to the Trac system at 
> . Do I need to do 
> anything else? 

Yes.  In order to directly import your patch, I need to have on 
file a signed copyright release from anybody who has contributed
to the patch.  The form must also be signed by your employer.
See http://www.sqlite.org/copyright.html for additional information
and links for downloading suitable copyright release forms.

> Will I get emails automatically when that bug entry is 
> updated? (I input my email into the Trac form but it doesn't show up 
> there now.)

No.  You have to poll CVSTrac to monitor the progress of the
ticket.  Email addresses are not displayed (except to authorized
developers) to prevent them from being harvested by spammers.
I can see your email address though the public at large cannot.

--
D. Richard Hipp   <[EMAIL PROTECTED]>

[sqlite] Patch to let SQLite run on OS/2 again

[Peter Weilbacher (Mozilla)]

I am not really sure if this is the place to discuss this. But the 
SQLite webpage doesn't list any developer related lists or contacts, so 
I give it a try.

We (that is mainly Daniel Lee Kruse, with a little bit of help from Andy
Willis and me) have re-ported SQLite to the OS/2 platform. My interest is 
mainly that Mozilla uses SQLite in its backend and we want to continue 
to run the Mozilla apps on OS/2. I didn't find any documentation on what
I have to do to get patches into the SQLite CVS source tree and the next
release. So I uploaded the patch for the port to the Trac system at 
. Do I need to do 
anything else? Will I get emails automatically when that bug entry is 
updated? (I input my email into the Trac form but it doesn't show up 
there now.)
-- 
Greetings,
   Peter.

Re: [sqlite] GROUP BY regression workaround?

[drh]

Joe Wilson <[EMAIL PROTECTED]> wrote:
> Anyone have any ideas how to speed up GROUP BY on huge views 
> in recent versions of SQLite?
> 
>   http://www.sqlite.org/cvstrac/tktview?tn=1809
> 
> The older versions of SQLite (prior to SQLite 3.2.6) used to 
> perform GROUP BY operations in the main table loop, grouping
> rows as it went along. But the new version appears to create 
> a huge temp table of results, sort them, and only then determine 
> the groups. This takes up a massive amount of temp store.
> The old GROUP BY algorithm excels in the case where there are 
> a lot of groupable rows in the dataset; i.e., the typical case.
> 

The old algorithm required enough memory to hold the
entire results set.  The new algorithm uses bounded 
memory, which is a very important advantage if you 
have a GROUP BY with a large result set.

The new algorithm will run much faster if you have
an index on the GROUP BY term(s).
--
D. Richard Hipp   <[EMAIL PROTECTED]>

[sqlite] 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

--
View this message in context: 
http://www.nabble.com/Extra+functions+-+New+Project--t1674436.html#a4539325
Sent from the SQLite forum at Nabble.com.