Re: [sqlite] Compile test example problem

[P Kishor]

On Fri, Oct 2, 2009 at 9:01 PM, David Morris  wrote:
>
> Thanks for your reply Roger, but doesn't the inclusion of the sqlite3.h file
> do that?

No, you have to link against the correct library. For example, if you
have your sqlite library under /usr/local/lib, you will add the
following

-L/usr/local/lib -lsqlite3


> If not, could you give me a better example please?


>
> David
>
> Roger Binns wrote:
>>
>> -BEGIN PGP SIGNED MESSAGE-
>> Hash: SHA1
>>
>> David Morris wrote:
>>> Building sqliteFirst.obj.
>>> Building SQLiteFirst.exe.
>>> POLINK: error: Unresolved external symbol '_sqlite3_open'.
>>> POLINK: error: Unresolved external symbol '_sqlite3_errmsg'.
>>> POLINK: error: Unresolved external symbol '_sqlite3_close'.
>>> POLINK: error: Unresolved external symbol '_sqlite3_exec'.
>>> POLINK: error: Unresolved external symbol '_sqlite3_free'.
>>> POLINK: fatal error: 5 unresolved external(s).
>>> *** Error code: 1 ***
>>
>> You need to link against the sqlite library which provides those symbols.
>>
>> Roger
>>
>> -BEGIN PGP SIGNATURE-
>> Version: GnuPG v1.4.9 (GNU/Linux)
>> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>>
>> iEYEARECAAYFAkrGrnYACgkQmOOfHg372QSc8QCgxRY6MJbcdOsGYvy329Drb0h0
>> 39EAoN0id+k2WFyDPqO4rUuOAnFMOzC1
>> =gool
>> -END PGP SIGNATURE-
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
> --
> View this message in context: 
> http://www.nabble.com/Compile-test-example-problem-tp25724582p25725034.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compile test example problem

[David Morris]

Thanks for your reply Roger, but doesn't the inclusion of the sqlite3.h file
do that?  If not, could you give me a better example please?

David

Roger Binns wrote:
> 
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> David Morris wrote:
>> Building sqliteFirst.obj.
>> Building SQLiteFirst.exe.
>> POLINK: error: Unresolved external symbol '_sqlite3_open'.
>> POLINK: error: Unresolved external symbol '_sqlite3_errmsg'.
>> POLINK: error: Unresolved external symbol '_sqlite3_close'.
>> POLINK: error: Unresolved external symbol '_sqlite3_exec'.
>> POLINK: error: Unresolved external symbol '_sqlite3_free'.
>> POLINK: fatal error: 5 unresolved external(s).
>> *** Error code: 1 ***
> 
> You need to link against the sqlite library which provides those symbols.
> 
> Roger
> 
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
> 
> iEYEARECAAYFAkrGrnYACgkQmOOfHg372QSc8QCgxRY6MJbcdOsGYvy329Drb0h0
> 39EAoN0id+k2WFyDPqO4rUuOAnFMOzC1
> =gool
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Compile-test-example-problem-tp25724582p25725034.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compile test example problem

[Jean-Christophe Deschamps]

Gidday,

>Building sqliteFirst.obj.
>Building SQLiteFirst.exe.
>POLINK: error: Unresolved external symbol '_sqlite3_open'.
>POLINK: error: Unresolved external symbol '_sqlite3_errmsg'.
>POLINK: error: Unresolved external symbol '_sqlite3_close'.
>POLINK: error: Unresolved external symbol '_sqlite3_exec'.
>POLINK: error: Unresolved external symbol '_sqlite3_free'.
>POLINK: fatal error: 5 unresolved external(s).

You must link your code against the SQLite core.  You can either:
   -) build another object target (e.g. sqlite3.obj) and link this with 
your main
   -) build a library (e.g. sqlite3.lib) and tell you linker to use it
   -) tell your linker you're going to use a shared library (e.g. 
sqlite3.dll) which you can download as a precompiled Windows file or 
build yourself from the amalgamation.

I don't know Pelles C so I can't give you more precise instructions on 
how to do that, so you need to search in your compiler help.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compile test example problem

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

David Morris wrote:
> Building sqliteFirst.obj.
> Building SQLiteFirst.exe.
> POLINK: error: Unresolved external symbol '_sqlite3_open'.
> POLINK: error: Unresolved external symbol '_sqlite3_errmsg'.
> POLINK: error: Unresolved external symbol '_sqlite3_close'.
> POLINK: error: Unresolved external symbol '_sqlite3_exec'.
> POLINK: error: Unresolved external symbol '_sqlite3_free'.
> POLINK: fatal error: 5 unresolved external(s).
> *** Error code: 1 ***

You need to link against the sqlite library which provides those symbols.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkrGrnYACgkQmOOfHg372QSc8QCgxRY6MJbcdOsGYvy329Drb0h0
39EAoN0id+k2WFyDPqO4rUuOAnFMOzC1
=gool
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Compile test example problem

[David Morris]

I am using Pelles C 6.00.4 for Windows on Vista. It doesn't compile and any
help would be appreciated.


/**See also the Introduction To The SQLite C/C++ Interface for an
introductory overview and roadmap to the dozens of SQLite interface
functions.**/

#include 
#include 

static int callback(void *NotUsed, int argc, char **argv, char **azColName){
  int i;
  for(i=0; ihttp://www.nabble.com/Compile-test-example-problem-tp25724582p25724582.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiline SQL command via C or Objective-C

[Igor Tandetnik]

BareFeet wrote:
> By contrast (to points 3 and 4), the sqlite3 command line utility
> handles multiple table outputs (with different numbers of columns)
> fine and seems to understand the types of data if using a mode that
> discerns it.

Because it runs these statements one by one using sqlite3_prepare* et 
al.

> Is there any way to get this same functionality?

Yes.

> Is the only way to
> write my own routines using a loop that keeps running
> sqlite3_prepare_v2 and stepping through the rows of each prepare?

The only one I know of.

Igor Tandetnik



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] performance of select in transactions

[Simon Slavin]

On 2 Oct 2009, at 8:40pm, Mike Shal wrote:

> Ahh, ok - that makes sense. Does this locking overhead not occur on
> other platforms (I've only tried linux -- gentoo and ubuntu), or if
> sqlite is built differently?

It's to do with how the platform (OS and hard disk format) support  
file locking.  Those two things together determine what really happens  
when SQLite uses the C call that locks a file.  So even saying  
'Gentoo' is too general, you want to know what format the disk your  
database is on has too.

> It seems a bit disingenuous to claim
> there will be no performance gain by putting selects in a transaction,
> when sqlite clearly does less work with the transaction (in the form
> of not getting the read lock multiple times).

I've used SQLite for multivariate analysis.  The sort of thing where  
you collect many variables worth of data on lots of cases, then  
analyse every possible combination of variables because you are  
looking for /anything/: you didn't have a strong idea of what to look  
like when you set out, you just want to know if there's anything  
interesting in the data you got.

A census of 25 questions gives you 25 * 24 = 600 SELECT commands.   
That the first simple step, and you're unlikely to find anything you  
didn't already suspect.  Then you try every combination of three  
variables: 25 * 24 * 23 = 13800 SELECT commands.  If you still get  
nothing significant and are scared you're going to lose your funding  
you try 25 * 24 * 23 * 22 = 303600.  And all this data is unchanging:  
might was well be on a data DVD (and data DVD drivers don't implement  
file locking !).

So yes, thousands of consecutive SELECT commands is a perfectly normal  
use of SQLite.  And until you raised the subject it hadn't occurred to  
me that it was necessary to lock on a SELECT but thinking about it,  
it's obvious.  Duh.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] performance of select in transactions

[Mike Shal]

On 10/2/09, Pavel Ivanov  wrote:
> > It seems a bit disingenuous to claim
>  > there will be no performance gain by putting selects in a transaction,
>  > when sqlite clearly does less work with the transaction (in the form
>  > of not getting the read lock multiple times).
>
>
> It's pretty ingenuous in fact to silently assume that nobody wants to
>  do 5 selects at once. Lower that to 5 (already a huge amount of
>  work for "one request to database" that blocks out any writers) or to
>  1 (the real number when somebody speaks about "difference of putting
>  select inside transaction") and you'll get a difference of 0.08 ms or
>  0.016 ms which is pretty small number to ignore when it's compared to
>  the time of actual reading from disk for the actual select.

Sorry, I was not aware of these implicit assumptions. Nor did I mean
to imply that you were being disingenuous -- you have answered in 10
minutes what took me several hours to narrow down to my original
example. I guess my usage is a bit peculiar, where the results of a
single select must be analyzed to determine what to select next, and
this can potentially cascade into many other selects, which must also
be analyzed, and so on. During this time no writes are allowed (due to
other requirements that I have), so for me there is no other
consideration as to whether or not it is in a transaction.

Thanks for your help,
-Mike
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] performance of select in transactions

[Pavel Ivanov]

> It seems a bit disingenuous to claim
> there will be no performance gain by putting selects in a transaction,
> when sqlite clearly does less work with the transaction (in the form
> of not getting the read lock multiple times).

It's pretty ingenuous in fact to silently assume that nobody wants to
do 5 selects at once. Lower that to 5 (already a huge amount of
work for "one request to database" that blocks out any writers) or to
1 (the real number when somebody speaks about "difference of putting
select inside transaction") and you'll get a difference of 0.08 ms or
0.016 ms which is pretty small number to ignore when it's compared to
the time of actual reading from disk for the actual select.

Pavel

On Fri, Oct 2, 2009 at 3:40 PM, Mike Shal  wrote:
> On 10/2/09, Pavel Ivanov  wrote:
>> > Does anybody know why just adding the begin/commit here improves
>>  > performance? If I have to do a large number of selects like this in my
>>  > application, should I always wrap it in a transaction?
>>
>>
>> This looks like some overhead of your file system. When you don't put
>>  begin/commit around selects then every select is a different read-only
>>  transaction. So before each select SQLite takes read lock on database
>>  and then after executing select SQLite releases the lock. If you put
>>  begin/commit around all selects then SQLite will take read lock only
>>  once at the beginning and release lock once at the end. So you have
>>  just found how long will it take to acquire/release read lock on
>>  database 5 times. So if your application indeed needs to do so
>>  many selects and it needs to do it in minimum amount of time then
>>  beginning transaction could be indeed a good solution. But be aware
>>  that by doing this you're blocking any other instance of your
>>  application which wants to write to database. It will not be able to
>>  do it until you make commit in your read-only transaction. If it's
>>  okay for you then why not.
>
> Ahh, ok - that makes sense. Does this locking overhead not occur on
> other platforms (I've only tried linux -- gentoo and ubuntu), or if
> sqlite is built differently? It seems a bit disingenuous to claim
> there will be no performance gain by putting selects in a transaction,
> when sqlite clearly does less work with the transaction (in the form
> of not getting the read lock multiple times).
>
> Thanks,
> -Mike
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] performance of select in transactions

[Mike Shal]

On 10/2/09, Pavel Ivanov  wrote:
> > Does anybody know why just adding the begin/commit here improves
>  > performance? If I have to do a large number of selects like this in my
>  > application, should I always wrap it in a transaction?
>
>
> This looks like some overhead of your file system. When you don't put
>  begin/commit around selects then every select is a different read-only
>  transaction. So before each select SQLite takes read lock on database
>  and then after executing select SQLite releases the lock. If you put
>  begin/commit around all selects then SQLite will take read lock only
>  once at the beginning and release lock once at the end. So you have
>  just found how long will it take to acquire/release read lock on
>  database 5 times. So if your application indeed needs to do so
>  many selects and it needs to do it in minimum amount of time then
>  beginning transaction could be indeed a good solution. But be aware
>  that by doing this you're blocking any other instance of your
>  application which wants to write to database. It will not be able to
>  do it until you make commit in your read-only transaction. If it's
>  okay for you then why not.

Ahh, ok - that makes sense. Does this locking overhead not occur on
other platforms (I've only tried linux -- gentoo and ubuntu), or if
sqlite is built differently? It seems a bit disingenuous to claim
there will be no performance gain by putting selects in a transaction,
when sqlite clearly does less work with the transaction (in the form
of not getting the read lock multiple times).

Thanks,
-Mike
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite performance with lots of data

[Cory Nelson]

On Fri, Oct 2, 2009 at 9:45 AM, Francisc Romano  wrote:
> Wow. I did not expect such a quick answer...
> Is there somewhere I can read exactly how fast and how big databases SQLite
> can take, please?

SQLite uses a b+tree internally, which is logarithmic in complexity.
Every time your dataset doubles in size, worse-case performance will
be halved.

-- 
Cory Nelson
http://int64.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] performance of select in transactions

[Pavel Ivanov]

> Does anybody know why just adding the begin/commit here improves
> performance? If I have to do a large number of selects like this in my
> application, should I always wrap it in a transaction?

This looks like some overhead of your file system. When you don't put
begin/commit around selects then every select is a different read-only
transaction. So before each select SQLite takes read lock on database
and then after executing select SQLite releases the lock. If you put
begin/commit around all selects then SQLite will take read lock only
once at the beginning and release lock once at the end. So you have
just found how long will it take to acquire/release read lock on
database 5 times. So if your application indeed needs to do so
many selects and it needs to do it in minimum amount of time then
beginning transaction could be indeed a good solution. But be aware
that by doing this you're blocking any other instance of your
application which wants to write to database. It will not be able to
do it until you make commit in your read-only transaction. If it's
okay for you then why not.


Pavel

On Fri, Oct 2, 2009 at 2:39 PM, Mike Shal  wrote:
> It seems to be common knowledge that running selects inside a
> transaction should offer no performance benefit (for example, this
> thread has a number of replies to that effect:
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg41699.html).
> However, I noticed a curious behavior in my application. I was running
> a large number of selects on my database, and when I removed the
> seemingly superfluous begin/commit statements around the selects, the
> application slowed down. I was able to reproduce this using the
> sqlite3 command line:
>
> $ time sqlite3 tmpdb '.read transaction.sql' > /dev/null
>
> real    0m2.014s
> user    0m1.985s
> sys     0m0.030s
>
> $ time sqlite3 tmpdb '.read notransaction.sql' > /dev/null
>
> real    0m2.800s
> user    0m2.222s
> sys     0m0.578s
>
> $ diff -au notransaction.sql transaction.sql
> --- notransaction.sql   2009-10-02 14:12:12.0 -0400
> +++ transaction.sql     2009-10-02 14:12:12.0 -0400
> @@ -1,3 +1,4 @@
> +begin;
>  select * from foo where entry='A';
>  select * from foo where entry='a';
>  select * from foo where entry='aa';
> @@ -49998,3 +4,4 @@
>  select * from foo where entry='degraded';
>  select * from foo where entry='degradedly';
>  select * from foo where entry='degradedness';
> +commit;
>
> Granted it's not an order of magnitude style difference, but ~800ms
> out of 2800ms seems like a pretty significant savings for just adding
> a begin/commit around a bunch of select statements. These results were
> fairly consistent with sqlite 3.6.18, 3.6.11, and 3.6.5 built from
> source (I was just trying a few different versions to see if it might
> have been a regression).
>
> Does anybody know why just adding the begin/commit here improves
> performance? If I have to do a large number of selects like this in my
> application, should I always wrap it in a transaction?
>
> If you want to try it out yourself, I used these scripts to create a
> database of words from /usr/share/dict/words, and then generate the
> sql to test:
>
>  create.sh (overwrites 'tmpdb' and 'create.sql' in the current directory)
> #! /bin/sh
>
> if [ $# -lt 1 ]; then
>        echo "Usage: $0 num" 1>&2
>        exit 1
> fi
> rm -f tmpdb; (echo 'create table foo (id integer primary key not null,
> entry varchar(100), unique(entry));'; echo 'begin;'; for i in `cat
> /usr/share/dict/words  | head -n $1`; do echo "insert into foo(entry)
> values('$i');"; done; echo 'commit;') > create.sql
> echo "Create:"
> time sqlite3 tmpdb '.read create.sql'
>  end create.sh
>
>  read.sh (overwrites 'transaction.sql' and 'notransaction.sql' in
> the current directory)
> #! /bin/sh
>
> if [ $# -lt 1 ]; then
>        echo "Usage: $0 num" 1>&2
>        exit 1
> fi
> rm -f transaction.sql
> rm -f notransaction.sql
> for i in `cat /usr/share/dict/words | head -n $1`; do echo "select *
> from foo where entry='$i';" >> notransaction.sql; done
> (echo 'begin;'; cat notransaction.sql; echo 'commit;') > transaction.sql
>
> sync
> echo "Without transaction:"
> time sqlite3 tmpdb '.read notransaction.sql' > /dev/null
>
> echo "Transaction:"
> time sqlite3 tmpdb '.read transaction.sql' > /dev/null
>  end read.sh
>
> The parameter is the size of the table to create / number of entries to read:
> $ sh create.sh 5
> $ sh read.sh 5
>
> Thanks,
> -Mike
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite performance with lots of data

[Francisc Romano]

Very good idea! Thank you!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] performance of select in transactions

[Mike Shal]

It seems to be common knowledge that running selects inside a
transaction should offer no performance benefit (for example, this
thread has a number of replies to that effect:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg41699.html).
However, I noticed a curious behavior in my application. I was running
a large number of selects on my database, and when I removed the
seemingly superfluous begin/commit statements around the selects, the
application slowed down. I was able to reproduce this using the
sqlite3 command line:

$ time sqlite3 tmpdb '.read transaction.sql' > /dev/null

real0m2.014s
user0m1.985s
sys 0m0.030s

$ time sqlite3 tmpdb '.read notransaction.sql' > /dev/null

real0m2.800s
user0m2.222s
sys 0m0.578s

$ diff -au notransaction.sql transaction.sql
--- notransaction.sql   2009-10-02 14:12:12.0 -0400
+++ transaction.sql 2009-10-02 14:12:12.0 -0400
@@ -1,3 +1,4 @@
+begin;
 select * from foo where entry='A';
 select * from foo where entry='a';
 select * from foo where entry='aa';
@@ -49998,3 +4,4 @@
 select * from foo where entry='degraded';
 select * from foo where entry='degradedly';
 select * from foo where entry='degradedness';
+commit;

Granted it's not an order of magnitude style difference, but ~800ms
out of 2800ms seems like a pretty significant savings for just adding
a begin/commit around a bunch of select statements. These results were
fairly consistent with sqlite 3.6.18, 3.6.11, and 3.6.5 built from
source (I was just trying a few different versions to see if it might
have been a regression).

Does anybody know why just adding the begin/commit here improves
performance? If I have to do a large number of selects like this in my
application, should I always wrap it in a transaction?

If you want to try it out yourself, I used these scripts to create a
database of words from /usr/share/dict/words, and then generate the
sql to test:

 create.sh (overwrites 'tmpdb' and 'create.sql' in the current directory)
#! /bin/sh

if [ $# -lt 1 ]; then
echo "Usage: $0 num" 1>&2
exit 1
fi
rm -f tmpdb; (echo 'create table foo (id integer primary key not null,
entry varchar(100), unique(entry));'; echo 'begin;'; for i in `cat
/usr/share/dict/words  | head -n $1`; do echo "insert into foo(entry)
values('$i');"; done; echo 'commit;') > create.sql
echo "Create:"
time sqlite3 tmpdb '.read create.sql'
 end create.sh

 read.sh (overwrites 'transaction.sql' and 'notransaction.sql' in
the current directory)
#! /bin/sh

if [ $# -lt 1 ]; then
echo "Usage: $0 num" 1>&2
exit 1
fi
rm -f transaction.sql
rm -f notransaction.sql
for i in `cat /usr/share/dict/words | head -n $1`; do echo "select *
from foo where entry='$i';" >> notransaction.sql; done
(echo 'begin;'; cat notransaction.sql; echo 'commit;') > transaction.sql

sync
echo "Without transaction:"
time sqlite3 tmpdb '.read notransaction.sql' > /dev/null

echo "Transaction:"
time sqlite3 tmpdb '.read transaction.sql' > /dev/null
 end read.sh

The parameter is the size of the table to create / number of entries to read:
$ sh create.sh 5
$ sh read.sh 5

Thanks,
-Mike
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] dump in-memory db to file in tcl

[Simon Slavin]

On 2 Oct 2009, at 5:20pm, Ned Fleming wrote:

> Is it possible to dump an in-memory sqlite database (or table?) to a
> file from within Tcl?

Not easily.  MySQL has a pseudo-command which does what .dump does:  
returns a long piece of text with all the commands needed to reproduce  
the table.  If I suggest adding this as a PRAGMA people will scream  
'bloat'.  In SQLite it's easy to find the command needed to create the  
TABLE and INDEXes, but what's hard is devising a short way to recreate  
the records.

> I have tried the following (and variations) -- but no go, kokomo.
> ($fileOut3 is a handle to a command-line file name.)
>
>   exec sqlite3 dump dbFireData $fileOut3

 From a shell:

echo '.dump' | sqlite3 mydb.sql > mydb.dump

echo '.dump' | sqlite3 mydb.sql | gzip -c > mydb.dump.gz

I don't know enough about Tcl to know if you can do piping and routing  
in its 'exec' commands.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite performance with lots of data

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Francisc Romano wrote:
> how big databases SQLite can take, please?

Someone told me recently they have 37GB and 66 million rows in their data
set.  Another user is using the virtual table functionality together with
synthetic indices to optimise working with large amounts of 3D data.

Quite simply the best thing to do is to generate test data that is
representative of what you want to do and the kind of queries you want to
execute.  Generate your test data and queries as a text file and feed it to
the SQLite shell.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkrGPmwACgkQmOOfHg372QRY4wCeLYdh9or2DHdT+/iJ3OO6pGap
LawAoNDryZiYyd/UZ/ljqRgC/5bwOmf8
=Dthc
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite performance with lots of data

[P Kishor]

On Fri, Oct 2, 2009 at 11:45 AM, Francisc Romano  wrote:
> Wow. I did not expect such a quick answer...
> Is there somewhere I can read exactly how fast and how big databases SQLite
> can take, please?

See http://www.sqlite.org/limits.html for "how big." You will have to
do your own measurements for "how fast."

A simple answer is "fast enough," as that question, standing alone,
makes no sense at all. How fast for what? On which machine? What else
is going on in that machine concurrently? In which application? How
well have you programmed?

-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite performance with lots of data

[Francisc Romano]

Wow. I did not expect such a quick answer...
Is there somewhere I can read exactly how fast and how big databases SQLite
can take, please?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite performance with lots of data

[P Kishor]

On Fri, Oct 2, 2009 at 11:42 AM, Francisc Romano  wrote:
> Hello!
>
> I am not entirely certain this is the right way to proceed, but I haven't
> been able to find the appropriate official SQLite forum (if one exists).
> I want to create a rather complex AIR application that will have to deal
> with a massive database.
> My question is:
>
> Is SQLite capable of dealing with large ammounts of data and still perform
> quickly?
>

yes.

> Thank you!
> Francisc
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor h
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite performance with lots of data

[Francisc Romano]

Hello!

I am not entirely certain this is the right way to proceed, but I haven't
been able to find the appropriate official SQLite forum (if one exists).
I want to create a rather complex AIR application that will have to deal
with a massive database.
My question is:

Is SQLite capable of dealing with large ammounts of data and still perform
quickly?

Thank you!
Francisc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] dump in-memory db to file in tcl

[Ned Fleming]

Is it possible to dump an in-memory sqlite database (or table?) to a
file from within Tcl?

I create it like so:

sqlite3 dbFireData :memory:

and insert a bunch of records, and then commit.

I have tried the following (and variations) -- but no go, kokomo.
($fileOut3 is a handle to a command-line file name.)

exec sqlite3 dump dbFireData $fileOut3

Something tells me I'm wildly off-base.

Ned

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DB Corruption

[Simon Slavin]

On 2 Oct 2009, at 2:13pm, Reusche, Andrew wrote:

> sqlite> pragma integrity_check;
>
> wrong # of entries in index sales_datetime

Which version of SQLite are you using ?

DROP the index then MAKE it again, then do the 'PRAGMA  
integrity_check' thing again.

If that didn't help I suspect that that table (or maybe the entire  
database) is terminally corrupt somehow.  Using the command-line tool  
on that database (take a backup copy first):

Dump all the data from that table using .dump .
DROP the table.
Read the dump you made using .read .

Do the 'PRAGMA integrity_check' thing again.  If it's still bad,

Dump all the data from all tables using .dump .
Quit the tool.  Delete your database file.  Start the tool up again  
(it'll make a blank database file for you)
Read the dump you made using .read .

If you're using a recent version of SQLite please keep a copy of the  
corrupt database file: someone on this list might be interested in  
looking at it for debugging purposes.

For more information on the command-line tool see



Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DB Corruption

[Pavel Ivanov]

> So any idea what might have caused this, a good way to fix it, or detect
> it at runtime?  The other threads that I have read point to either
> multithreading issues, or an ungraceful shutdown.

And they're right, except that ungraceful shutdown will hurt your
database only if you set pragma synchronous = off and multithreading
issues will hurt if you compiled SQLite with threading turned off and
then tried to use SQLite from multiple threads...

And there's not much you can do about the problems themselves. Just
try to .dump it from sqlite3 command line utility and recreate
database from the dump. And don't think about detecting such problems
in runtime, think about fixing your application so that these problems
don't appear.


Pavel

On Fri, Oct 2, 2009 at 9:13 AM, Reusche, Andrew
 wrote:
> I have an issue with a sqlite3 db.  I have a table that yields invalid
> data in some queries.
>
>
>
>
>
> Select sales.*
>
> From sales
>
> Where sales.record_type = 1
>
> Order By sales.datetime Desc
>
>
>
> Yields the top 124 or so records as all duplicates, and the record_type
> column has a "0" in each record.  By the query above, these shouldn't
> show.  What's worse is that there is really only one instance of this
> record in the row:
>
>
>
> Select sales.*, sales.sales_id
>
> From sales
>
> Where sales.sales_id = 617284
>
> Order By sales.datetime Desc
>
>
>
> This query yields 1 value.  The original query in this email returned
> 124 copies of this record.
>
>
>
> More troubleshooting:
>
>
>
> sqlite> select max(sales_id) from sales;
>
> 617283
>
>
>
> This value returned for max is actually less than the value returned in
> the query with the bogus results.
>
>
>
> sqlite> pragma integrity_check;
>
> rowid 609189 missing from index sales_datetime
>
> rowid 609190 missing from index sales_datetime
>
> rowid 609191 missing from index sales_datetime
>
> rowid 609192 missing from index sales_datetime
>
> rowid 609193 missing from index sales_datetime
>
> rowid 609194 missing from index sales_datetime
>
> rowid 609195 missing from index sales_datetime
>
> rowid 609196 missing from index sales_datetime
>
> rowid 609197 missing from index sales_datetime
>
> rowid 609198 missing from index sales_datetime
>
> rowid 609199 missing from index sales_datetime
>
> rowid 609200 missing from index sales_datetime
>
> rowid 609201 missing from index sales_datetime
>
> rowid 609202 missing from index sales_datetime
>
> rowid 609203 missing from index sales_datetime
>
> rowid 609204 missing from index sales_datetime
>
> rowid 609198 missing from index sales_datetime
>
> rowid 609199 missing from index sales_datetime
>
> rowid 609200 missing from index sales_datetime
>
> rowid 609201 missing from index sales_datetime
>
> rowid 609202 missing from index sales_datetime
>
> rowid 609203 missing from index sales_datetime
>
> rowid 609204 missing from index sales_datetime
>
> rowid 625656 missing from index sales_datetime
>
> rowid 625657 missing from index sales_datetime
>
> rowid 625658 missing from index sales_datetime
>
> rowid 625661 missing from index sales_datetime
>
> rowid 625662 missing from index sales_datetime
>
> rowid 625663 missing from index sales_datetime
>
> rowid 625664 missing from index sales_datetime
>
> rowid 625665 missing from index sales_datetime
>
> rowid 625666 missing from index sales_datetime
>
> wrong # of entries in index sales_datetime
>
>
>
> then I run reindex.  Then another integrity_check:
>
>
>
>
>
> sqlite> pragma integrity_check;
>
> wrong # of entries in index sales_datetime
>
>
>
>
>
>
>
> So any idea what might have caused this, a good way to fix it, or detect
> it at runtime?  The other threads that I have read point to either
> multithreading issues, or an ungraceful shutdown.
>
>
>
>
>
> Thanks in advance
>
>
>
>
>
> Andrew
>
>
>
> This communication (including any attachments) is intended for the use of the 
> intended recipient(s) only and may contain information that is confidential, 
> privileged or legally protected. Any unauthorized use or dissemination of 
> this communication is strictly prohibited. If you have received this 
> communication in error, please immediately notify the sender by return e-mail 
> message and delete all copies of the original communication. Thank you for 
> your cooperation.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiline SQL command via C or Objective-C

[BareFeet]

On 02/10/2009, at 12:48 PM, Igor Tandetnik wrote:

> BareFeet wrote:
>> The one hurdle I've struck is how to send a multi-statement SQL
>> command. Say for instance I want to process a bunch of text such as:
>>
>> How do I submit it via C functions?
>
> You run the statements one at a time. sqlite3_prepare et al helpfully
> provide a pointer to the first character past the first complete
> statement.

Ahh, I see. I didn't realise that sqlite_prepare_v2 offers the  
uncompiled remainder.

>> It seems that I need to use sqlite3_get_table() to get the result in
>> one hit, rather than sqlite3_prepare_v2 and stepping through each  
>> row.
>> Is that right?
>
> What's wrong with stepping through each row?

I didn't have a particular objection to it. I was just stating my  
understanding of the difference between using sqlite3_get_table and  
sqlite3_prepare or sqlite3_prepare_v2.

I've tested sqlite3_get_table a bit more. It seems to:

1. Be a much simpler approach if you just want to execute or grab the  
results from a statement or multi-command statement.

2. Returns the column headers in the first row of the result table.

3. Doesn't offer any way to return the data types of the result  
columns or values. They appear to all be strings.

4. Fails if multiple select statements return a different number of  
columns.

By contrast (to points 3 and 4), the sqlite3 command line utility  
handles multiple table outputs (with different numbers of columns)  
fine and seems to understand the types of data if using a mode that  
discerns it. eg:

.headers on
begin;
select 'Mickey' as Name, 65 as Age;
select 'Disneyland' as Suburb, 'USA' as Country;
select 'no' as Test;
end;

fails using sqlite3_get_table but the sqlite3 command line utility  
gives:

Name|Age
Mickey|65

Suburb|Country
Disneyland|USA

Test
no

Is there any way to get this same functionality? Is the only way to  
write my own routines using a loop that keeps running  
sqlite3_prepare_v2 and stepping through the rows of each prepare?

Thanks,
Tom
BareFeet

  --
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/?ml

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] DB Corruption

[Reusche, Andrew]

I have an issue with a sqlite3 db.  I have a table that yields invalid
data in some queries.  

 

 

Select sales.*

>From sales

Where sales.record_type = 1

Order By sales.datetime Desc

 

Yields the top 124 or so records as all duplicates, and the record_type
column has a "0" in each record.  By the query above, these shouldn't
show.  What's worse is that there is really only one instance of this
record in the row:

 

Select sales.*, sales.sales_id

>From sales

Where sales.sales_id = 617284

Order By sales.datetime Desc

 

This query yields 1 value.  The original query in this email returned
124 copies of this record.

 

More troubleshooting:

 

sqlite> select max(sales_id) from sales;

617283

 

This value returned for max is actually less than the value returned in
the query with the bogus results.

 

sqlite> pragma integrity_check;

rowid 609189 missing from index sales_datetime

rowid 609190 missing from index sales_datetime

rowid 609191 missing from index sales_datetime

rowid 609192 missing from index sales_datetime

rowid 609193 missing from index sales_datetime

rowid 609194 missing from index sales_datetime

rowid 609195 missing from index sales_datetime

rowid 609196 missing from index sales_datetime

rowid 609197 missing from index sales_datetime

rowid 609198 missing from index sales_datetime

rowid 609199 missing from index sales_datetime

rowid 609200 missing from index sales_datetime

rowid 609201 missing from index sales_datetime

rowid 609202 missing from index sales_datetime

rowid 609203 missing from index sales_datetime

rowid 609204 missing from index sales_datetime

rowid 609198 missing from index sales_datetime

rowid 609199 missing from index sales_datetime

rowid 609200 missing from index sales_datetime

rowid 609201 missing from index sales_datetime

rowid 609202 missing from index sales_datetime

rowid 609203 missing from index sales_datetime

rowid 609204 missing from index sales_datetime

rowid 625656 missing from index sales_datetime

rowid 625657 missing from index sales_datetime

rowid 625658 missing from index sales_datetime

rowid 625661 missing from index sales_datetime

rowid 625662 missing from index sales_datetime

rowid 625663 missing from index sales_datetime

rowid 625664 missing from index sales_datetime

rowid 625665 missing from index sales_datetime

rowid 625666 missing from index sales_datetime

wrong # of entries in index sales_datetime

 

then I run reindex.  Then another integrity_check:

 

 

sqlite> pragma integrity_check;

wrong # of entries in index sales_datetime

 

 

 

So any idea what might have caused this, a good way to fix it, or detect
it at runtime?  The other threads that I have read point to either
multithreading issues, or an ungraceful shutdown.

 

 

Thanks in advance

 

 

Andrew

 

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error in type-def syntax diagram notation??

[Igor Tandetnik]

Keith Roberts wrote:
> I'm trying to learn the syntax for SQLite, and I'm getting
> stuck with the type-def production in:
>
> http://www.sqlite.org/lang_createtable.html
>
> Should name not be a selection of available type names,
> instead of an iteration?

With SQLite, there is no exhaustive list of "available type names". For 
more details, see

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

> Can a type-name be followed by a signed number?

Yes. This is allowed for compatibility with other databases, which often 
support types like CHAR(100) or NUMERIC(10, 2). SQLite accepts these 
numbers in the syntax, but doesn't act on them in any way; they are 
simply ignored.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DROP TRIGGER-Statement within a Trigger

[Kees Nuyt]

On Fri, 2 Oct 2009 12:18:11 +0200, Michael Werber
 wrote:

>KN> The need to change a schema on the fly is usually caused by
>KN> bad design. What is the probvlem you try to resolve?
>
> the problem is i have a table that should contain only the 
> last xxx inserts.
>
> to do so i created a trigger:
> CREATE TRIGGER trg_TrimMessdataLast
>AFTER INSERT ON MessdataAll 
>WHEN (
>SELECT count(*) FROM MessdataLast 
>WHERE 
>MeasuringMode=NEW.MeasuringMode
> AND Maschinennummer=NEW.Maschinennummer
> AND Merkmalnummer=NEW.Merkmalnummer)
>>= 10
> BEGIN
>DELETE FROM MessdataLast WHERE 
>MeasuringMode=NEW.MeasuringMode AND
>Maschinennummer=NEW.Maschinennummer AND 
>MessId=(
>SELECT MIN(MessId) FROM MessdataLast 
>WHERE Maschinennummer=NEW.Maschinennummer 
>AND MeasuringMode=NEW.MeasuringMode);
> END
>
> This one will delete 1st entries whenever there are more than 10
> measurements (not datasets. one measurement consits of more than one
> dataset, column "messid" defines the measurement).
>
> I wanted to make the 10 changeable so i tried to change the
> trigger from within a trigger that gets fired when some specific
> field in another table (that contains the "10" changes).
>
> So now i have changed the trigger to
> CREATE TRIGGER trg_TrimMessdataLast
>AFTER INSERT ON MessdataAll 
>WHEN (
>SELECT count(*) FROM MessdataLast 
>WHERE 
>MeasuringMode=NEW.MeasuringMode
> AND Maschinennummer=NEW.Maschinennummer
> AND Merkmalnummer=NEW.Merkmalnummer)
>>= (Select IntValue FROM SystemData WHERE ID=10)
> BEGIN
>DELETE FROM MessdataLast WHERE 
>MeasuringMode=NEW.MeasuringMode AND
>Maschinennummer=NEW.Maschinennummer AND 
>MessId=(
>SELECT MIN(MessId) FROM MessdataLast 
>WHERE Maschinennummer=NEW.Maschinennummer 
>AND MeasuringMode=NEW.MeasuringMode);
> END
>
> (changed the WHERE-part in the WHEN-clause)
>
> wich works bus has to do a select on every trigger-call.
> i wanted to have a fixed number there

Not a bad design after all.
I wouldn't worry about the 
(Select IntValue FROM SystemData WHERE ID=10)

For this kind of use, the SystemData table will occupy just
one page, and a page for the primary key index.
Not much of a burden for the page cache (default 2000
database pages).

-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Mailing List, different view from DB.

[Igor Tandetnik]

It appears that you aren't asking about SQLite, but about nabble.com 
mirror of SQLite's mailing lists. Realize that Nabble is in no way 
affiliated with SQLite - they are a third party that chooses to 
aggregate and archive lots of different mailing lists, including those 
where SQLite is discussed. If you have a problem with Nabble, they have 
a support forum at

http://n2.nabble.com/Nabble-Support-f1.html

Igor Tandetnik

Basheer.Yakoob wrote:
> I have the following question from the SQLite Mailing List, would be
> great if anyone can answer.
>
> How the different view in the mailing list is working:
>
> 1) For example from the DB if we have three different views. How will
> be the view updated if one Tuple updated or changed from the DB?
> (a) only the particular Tuple updated in the different view?
> (b) or updated the complete views?
>
> 2) Three separate mailing lists have been established to help support
> SQLite.  from Sqlit home page "http://www.sqlite.org/support.html";
> can not be viewed (opened).
> (a) Is there any other link where we can have access? 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DROP TRIGGER-Statement within a Trigger

[Michael Werber]

KN> The need to change a schema on the fly is usually caused by
KN> bad design. What is the probvlem you try to resolve?
the problem is i have a table that should contain only the last xxx
inserts.

to do so i created a trigger:
CREATE TRIGGER trg_TrimMessdataLast
AFTER INSERT ON MessdataAll 
WHEN (
SELECT count(*) FROM MessdataLast 
WHERE 
MeasuringMode=NEW.MeasuringMode AND 
Maschinennummer=NEW.Maschinennummer AND Merkmalnummer=NEW.Merkmalnummer)
>= 10
BEGIN
DELETE FROM MessdataLast WHERE 
MeasuringMode=NEW.MeasuringMode AND
Maschinennummer=NEW.Maschinennummer AND 
MessId=(
SELECT MIN(MessId) FROM MessdataLast 
WHERE Maschinennummer=NEW.Maschinennummer 
AND MeasuringMode=NEW.MeasuringMode);
END

this one will delete 1st entries whenever there are more than 10
measurements (not datasets. one measurement consits of more than one
dataset, column "messid" defines the measurement).

i wanted to make the 10 changeable so i tried to change the
trigger from within a trigger that gets fired when some specific
field in another table (that contains the "10" changes).

so now i have changed the trigger to
CREATE TRIGGER trg_TrimMessdataLast
AFTER INSERT ON MessdataAll 
WHEN (
SELECT count(*) FROM MessdataLast 
WHERE 
MeasuringMode=NEW.MeasuringMode AND 
Maschinennummer=NEW.Maschinennummer AND Merkmalnummer=NEW.Merkmalnummer)
>= (Select IntValue FROM SystemData WHERE ID=10)
BEGIN
DELETE FROM MessdataLast WHERE 
MeasuringMode=NEW.MeasuringMode AND
Maschinennummer=NEW.Maschinennummer AND 
MessId=(
SELECT MIN(MessId) FROM MessdataLast 
WHERE Maschinennummer=NEW.Maschinennummer 
AND MeasuringMode=NEW.MeasuringMode);
END

(changed the WHERE-part in the WHEN-clause)

wich works bus has to do a select on every trigger-call.
i wanted to have a fixed number there



greetings

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DROP TRIGGER-Statement within a Trigger

[Kees Nuyt]

On Fri, 2 Oct 2009 10:43:38 +0200, Michael Werber
 wrote:

>hallo Sqlite-users,
>
>i want to update a trigger from within another trigger. because there is no
>update-trigger statement i tried to drop and recreate my trigger. i
>tried this:
>
>CREATE TRIGGER trg_update AFTER UPDATE ON SystemData
>BEGIN
>   DROP TRIGGER trg_TrimMessdataLast;
>
>   -- CREATE TRIGGER trg_TrimMessdataLast 
>   -- Statement omitted, it will follow here
>END;
>
>but i get: "near ´DROP´: syntax error"
>
>so the question is am i doing something wrong or 
>s it impossible to rop a trigger from within a trigger?

It is impossible to execute DDL (Data Definition Language)
from a trigger.

DDL = {CREATE|DROP} {TABLE|VIEW|TRIGGER|}

Triggers can only execute DML (Data Manipulation Language).

DML = {SELECT|INSERT|UPDATE|DELETE}

The need to change a schema on the fly is usually caused by
bad design. What is the probvlem you try to resolve?
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite Mailing List, different view from DB.

[Basheer.Yakoob]

I have the following question from the SQLite Mailing List, would be great if
anyone can answer. 

How the different view in the mailing list is working: 

1) For example from the DB if we have three different views. How will be the
view updated if one Tuple updated or changed from the DB? 
(a) only the particular Tuple updated in the different view?
(b) or updated the complete views?

2) Three separate mailing lists have been established to help support
SQLite.  from Sqlit home page "http://www.sqlite.org/support.html"; can not
be viewed (opened).
(a) Is there any other link where we can have access?
-- 
View this message in context: 
http://www.nabble.com/Sqlite-Mailing-List%2C-different-view-from-DB.-tp25713274p25713274.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] DROP TRIGGER-Statement within a Trigger

[Michael Werber]

hallo Sqlite-users,

i want to update a trigger from within another trigger. because there is no
update-trigger statement i tried to drop and recreate my trigger. i
tried this:

CREATE TRIGGER trg_update AFTER UPDATE ON SystemData
BEGIN
   DROP TRIGGER trg_TrimMessdataLast;

   -- CREATE TRIGGER trg_TrimMessdataLast Statement omitted, it will follow 
here
END;

but i get: "near ´DROP´: syntax error"

so the question is am i doing something wrong or is it impossible to
drop a trigger from within a trigger?

thank you

michael werber


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error in type-def syntax diagram notation??

[Dan Kennedy]

On Oct 2, 2009, at 12:33 PM, Keith Roberts wrote:

> I'm trying to learn the syntax for SQLite, and I'm getting
> stuck with the type-def production in:
>
> http://www.sqlite.org/lang_createtable.html
>
> I read it as a type-def production is a sequence of one or
> more instances of a terminal symbol - called name,
> optionally followed by a non-terminal signed number in
> parantheses, which may optionally be followed by a comma and
> another signed number within those parantheses.

I think that reading is correct.

   sqlite> create table t(a b c d e(-43));
   sqlite> pragma table_info(t);
   0|a|b c d e(-43)|0||0


Dan.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users