Re: [sqlite] syntax for sqlite to query across 2 databases

2010-03-14 Thread David Lyon

Thanks everyone for your helpfinally got it working.
very simple answer and trivial but took me hours to look for this online and I 
still dont see this answer online but here it is for anyone who may be a newbie 
like myself

sqlite3 db1 "attach ARGS1_DB as db1; select * from args"






--- On Sun, 3/14/10, P Kishor  wrote:

> From: P Kishor 
> Subject: Re: [sqlite] syntax for sqlite to query across 2 databases
> To: "General Discussion of SQLite Database" 
> Date: Sunday, March 14, 2010, 10:35 PM
> Perhaps you should start by telling
> *all* your sqlite issues. That
> would make for more efficient help being provided. See more
> below --
> 
> 
> On Sun, Mar 14, 2010 at 8:21 PM, David Lyon 
> wrote:
> > I tried getting ATTACH to work since this am (EST) and
> have been looking in the different sqlite forums but have
> yet to find a simple and clear description of how to get
> this to work.
> >
> > I have the ARGS1_DB and contains args table. when I
> attach the ARGS1_DB to db1, I dont get the args table from
> ARGS1_DB in db1. any ideas?
> >
> > Apologies for the inconvenience.
> >
> >
> > sqlite3   ARGS1_DB  "select * from args" | more
> > 1|1
> > 2|2
> > 3|3
> > 4|4
> > 5|5
> > 6|6
> > 7|7
> > 8|8
> >
> > sqlite3 db1 "attach ARGS1_DB as db1"
> >
> 
> Think of how sqlite3 works. The way you are working with
> it, you are
> running a command, ostensibly from the command line, and
> then that
> command is done, fini. Now, think of the above command. The
> ATTACH sql
> command is run and then sqlite3 quits. Its work is done.
> Anything you
> do further, like the commands below, are not longer going
> to be
> affected by the ATTACH command above.
> 
> > sqlite3 db1 "select * from args"
> > SQL error: no such table: args
> >
> >  sqlite3 db1 "select * from db1.args"
> > SQL error: no such table: db1.args
> >
> >
> 
> Assuming you are working from the sqlite3 shell (again,
> since you
> haven't given any information how you are working with your
> db, I am
> not even sure you are aware of the sqlite3 shell), from
> your
> computer's shell command line
> 
> $ sqlite3 db1
> SQLite version 3.6.19
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> CREATE TABLE t1 (id INTEGER PRIMARY KEY, a
> TEXT);
> sqlite> INSERT INTO t1 (a) VALUES ('blah');
> sqlite> SELECT * FROM t1;
> id          a
> --  --
> 1           blah
> sqlite> .q
> $ ls
> db1
> $ sqlite3 db2
> SQLite version 3.6.19
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> CREATE TABLE t2 (id INTEGER PRIMARY KEY, b
> TEXT);
> sqlite> INSERT INTO t2 (b) VALUES ('meh');
> sqlite> SELECT * FROM t2;
> id          b
> --  --
> 1           meh
> sqlite> .q
> $ ls
> db1    db2
> $sqlite3 db1
> SQLite version 3.6.19
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> ATTACH DATABASE db2 AS db2;
> sqlite> SELECT t1.a, t2.b FROM t1 JOIN t2 ON t1.id =
> t2.id;
> a           b
> --  --
> blah        meh
> sqlite>
> 
> Hope that helps.
> 
> 
> 
> 
> 
> >
> >
> > --- On Sun, 3/14/10, Jay A. Kreibich 
> wrote:
> >
> >> From: Jay A. Kreibich 
> >> Subject: Re: [sqlite] syntax for sqlite to query
> across 2 databases
> >> To: "General Discussion of SQLite Database" 
> >> Date: Sunday, March 14, 2010, 9:37 PM
> >> On Sun, Mar 14, 2010 at 06:22:15PM
> >> -0700, David Lyon scratched on the wall:
> >>
> >> > Can you or someone provide the exact syntax
> for ATTACH
> >>
> >>
> >>
> >>   http://www.lmgtfy.com/?q=sqlite+attach+command=1
> >>
> >>
> >>
> >> ___
> >> 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
> >
> 
> 
> 
> -- 
> 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
> ===
> ___
> 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] syntax for sqlite to query across 2 databases

2010-03-14 Thread P Kishor
Perhaps you should start by telling *all* your sqlite issues. That
would make for more efficient help being provided. See more below --


On Sun, Mar 14, 2010 at 8:21 PM, David Lyon  wrote:
> I tried getting ATTACH to work since this am (EST) and have been looking in 
> the different sqlite forums but have yet to find a simple and clear 
> description of how to get this to work.
>
> I have the ARGS1_DB and contains args table. when I attach the ARGS1_DB to 
> db1, I dont get the args table from ARGS1_DB in db1. any ideas?
>
> Apologies for the inconvenience.
>
>
> sqlite3   ARGS1_DB  "select * from args" | more
> 1|1
> 2|2
> 3|3
> 4|4
> 5|5
> 6|6
> 7|7
> 8|8
>
> sqlite3 db1 "attach ARGS1_DB as db1"
>

Think of how sqlite3 works. The way you are working with it, you are
running a command, ostensibly from the command line, and then that
command is done, fini. Now, think of the above command. The ATTACH sql
command is run and then sqlite3 quits. Its work is done. Anything you
do further, like the commands below, are not longer going to be
affected by the ATTACH command above.

> sqlite3 db1 "select * from args"
> SQL error: no such table: args
>
>  sqlite3 db1 "select * from db1.args"
> SQL error: no such table: db1.args
>
>

Assuming you are working from the sqlite3 shell (again, since you
haven't given any information how you are working with your db, I am
not even sure you are aware of the sqlite3 shell), from your
computer's shell command line

$ sqlite3 db1
SQLite version 3.6.19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE t1 (id INTEGER PRIMARY KEY, a TEXT);
sqlite> INSERT INTO t1 (a) VALUES ('blah');
sqlite> SELECT * FROM t1;
id  a
--  --
1   blah
sqlite> .q
$ ls
db1
$ sqlite3 db2
SQLite version 3.6.19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE t2 (id INTEGER PRIMARY KEY, b TEXT);
sqlite> INSERT INTO t2 (b) VALUES ('meh');
sqlite> SELECT * FROM t2;
id  b
--  --
1   meh
sqlite> .q
$ ls
db1db2
$sqlite3 db1
SQLite version 3.6.19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> ATTACH DATABASE db2 AS db2;
sqlite> SELECT t1.a, t2.b FROM t1 JOIN t2 ON t1.id = t2.id;
a   b
--  --
blahmeh
sqlite>

Hope that helps.





>
>
> --- On Sun, 3/14/10, Jay A. Kreibich  wrote:
>
>> From: Jay A. Kreibich 
>> Subject: Re: [sqlite] syntax for sqlite to query across 2 databases
>> To: "General Discussion of SQLite Database" 
>> Date: Sunday, March 14, 2010, 9:37 PM
>> On Sun, Mar 14, 2010 at 06:22:15PM
>> -0700, David Lyon scratched on the wall:
>>
>> > Can you or someone provide the exact syntax for ATTACH
>>
>>
>>
>>   http://www.lmgtfy.com/?q=sqlite+attach+command=1
>>
>>
>>
>> ___
>> 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
>



-- 
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
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] syntax for sqlite to query across 2 databases

2010-03-14 Thread David Lyon
I tried getting ATTACH to work since this am (EST) and have been looking in the 
different sqlite forums but have yet to find a simple and clear description of 
how to get this to work.

I have the ARGS1_DB and contains args table. when I attach the ARGS1_DB to db1, 
I dont get the args table from ARGS1_DB in db1. any ideas?

Apologies for the inconvenience.


sqlite3   ARGS1_DB  "select * from args" | more
1|1
2|2
3|3
4|4
5|5
6|6
7|7
8|8

sqlite3 db1 "attach ARGS1_DB as db1"

sqlite3 db1 "select * from args"
SQL error: no such table: args

 sqlite3 db1 "select * from db1.args"
SQL error: no such table: db1.args




--- On Sun, 3/14/10, Jay A. Kreibich  wrote:

> From: Jay A. Kreibich 
> Subject: Re: [sqlite] syntax for sqlite to query across 2 databases
> To: "General Discussion of SQLite Database" 
> Date: Sunday, March 14, 2010, 9:37 PM
> On Sun, Mar 14, 2010 at 06:22:15PM
> -0700, David Lyon scratched on the wall:
> 
> > Can you or someone provide the exact syntax for ATTACH
> 
> 
> 
>   http://www.lmgtfy.com/?q=sqlite+attach+command=1
> 
> 
> 
> ___
> 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] syntax for sqlite to query across 2 databases

2010-03-14 Thread Jay A. Kreibich
On Sun, Mar 14, 2010 at 06:22:15PM -0700, David Lyon scratched on the wall:

> Can you or someone provide the exact syntax for ATTACH 


  http://www.lmgtfy.com/?q=sqlite+attach+command=1



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


Re: [sqlite] syntax for sqlite to query across 2 databases

2010-03-14 Thread Jay A. Kreibich
On Sun, Mar 14, 2010 at 05:01:18PM -0700, David Lyon scratched on the wall:
> if I had a database called db1 with table tbl1 with field id and a
> second db called db2 and a table called tbl2 with field id, whats the
> correct syntax to query across the 2 databases eg:
> 
> "select * db1..tbl1 a , db2..tbl2 b where a.id=b.id"

  The FROM clause expects a table, so it only takes one dot to move up
  a level from table to database. 

   SELECT * FROM db1.tbl1 a, db2.tbl2 b WHERE a.id = b.id

  This should also work if both databases have tables with the same name:
  
   SELECT * FROM db1.tbl a, db2.tbl b WHERE a.id = b.id
  
  However, if the table names are unique, you don't actually need to
  qualify them:

   SELECT * FROM tbl1, tbl2 WHERE tbl1.id = tbl2.id



   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] syntax for sqlite to query across 2 databases

2010-03-14 Thread David Lyon
thanks for your reply

Can you or someone provide the exact syntax for ATTACH and then the subsequent 
SQL syntax so I get the equivalent to:

"select * db1..tbl1 a , db2..tbl2 b where a.id=b.id"

Thanks again!


--- On Sun, 3/14/10, P Kishor  wrote:

> From: P Kishor 
> Subject: Re: [sqlite] syntax for sqlite to query across 2 databases
> To: "General Discussion of SQLite Database" 
> Date: Sunday, March 14, 2010, 9:02 PM
> On Sun, Mar 14, 2010 at 6:01 PM,
> David Lyon 
> wrote:
> > if I had a database called db1 with table tbl1 with
> field id and a second db called db2 and a table called tbl2
> with field id, whats the correct syntax to query across the
> 2 databases eg:
> >
> > "select * db1..tbl1 a , db2..tbl2 b where a.id=b.id"
> >
> > This doesnt work, can someone modify it to work?
> >
> 
> You can't query across two separate databases with one db
> connection,
> because a connection is to one db at a time.
> 
> You can ATTACH the second db to the first db, and then, for
> all
> purposes, you have one db within which you can make queries
> across
> tables.
> 
> 
> 
> >
> > Thanks for your help in advance.
> >
> >
> > Dave
> >
> >
> >
> 
> 
> -- 
> Puneet Kishor
> ___
> 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] VACUUM & journal size

2010-03-14 Thread Jay A. Kreibich
On Sun, Mar 14, 2010 at 07:19:59PM -0400, Matthew L. Creech scratched on the 
wall:
> Hi,
> 
> I have a SQLite database with one large table, and I'd like to shrink
> the size of that table to free up space in the filesystem.  My problem
> is that the database is (for example) 100 MB, and I have 80 MB of free
> filesystem space.  I figured that I could DELETE, say, half of the
> records from the table, then VACUUM, and the VACUUM would
> [temporarily] need ~50 MB of free space for the journal (since that's
> how much real data there is).
> 
> Instead, I'm finding that it needs a full 100 MB for the journal, even
> though once the VACUUM succeeds the resulting DB is only 50 MB.  As a
> result, I'm stuck unable to shrink the database, since VACUUM fails
> with a disk I/O error (out of space), seemingly no matter many entries
> I remove ahead of time.  I know the space is being freed, since
> "PRAGMA freelist_count" shows the expected numbers.  So presumably
> this is just an artifact of the way VACUUM is implemented internally.

  Are you sure it is the journal file that is growing too large? 

  VACUUM works by making a logical copy of the database from the
  original database into a temp database.  This restructures the
  database and recovers space.  The temp database is then copied back
  to the original database using low-level page copy.  This low-level
  copy then truncates the original database file, recovering
  filesystem space.

  This also means the total space required to VACUUM a database is:
  [old database size] + [new database size] + [journal file]

  While I have not tested this, I was under the impression that the
  journal file is very very small, as no modifications are made to the
  database, other than the final low-level copy (which is not a
  journaled operation).

  Now, if I'm following you correctly, the numbers you gave seem to
  indicate that this should work... If the old database is 100MB and
  the new database is 50MB and I'm saying the journal file is small,
  then 80MB free before you start should be enough.

  Except... all that disk space isn't taken from the same spot.  The
  temp database is opened as '' (e.g. a zero-length string), which
  creates it in the temporary space.  Where, exactly, this is depends
  on the host OS.  Unix likes /var/tmp, /usr/tmp/ or /tmp, for example.

  If /tmp is a different filesystem (as it often is), and if it isn't
  big enough to hold the new database, you're going to get a space
  error.

  So...  Just to verify if this really is or isn't the journal file,
  you can try to turn the journal file off:

PRAGMA journal_mode = OFF;

  I would do this on a test system.  If the problem really is the
  journal file, this should allow things to work.  If you still get a
  space error, we're dealing with something else.

  If that doesn't work, move the temporary files to some place that has
  enough space to hold the new database file.  It should be OK to set
  this to the same directory as the original database
 
PRAGMA temp_store_directory = '/path/to/database/directory';

  If that filesystem has enough space to hold both the old database and
  the new database, you should be able to vacuum it correctly.

  Let us know how it works out.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] syntax for sqlite to query across 2 databases

2010-03-14 Thread P Kishor
On Sun, Mar 14, 2010 at 6:01 PM, David Lyon  wrote:
> if I had a database called db1 with table tbl1 with field id and a second db 
> called db2 and a table called tbl2 with field id, whats the correct syntax to 
> query across the 2 databases eg:
>
> "select * db1..tbl1 a , db2..tbl2 b where a.id=b.id"
>
> This doesnt work, can someone modify it to work?
>

You can't query across two separate databases with one db connection,
because a connection is to one db at a time.

You can ATTACH the second db to the first db, and then, for all
purposes, you have one db within which you can make queries across
tables.



>
> Thanks for your help in advance.
>
>
> Dave
>
>
>


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


[sqlite] syntax for sqlite to query across 2 databases

2010-03-14 Thread David Lyon
if I had a database called db1 with table tbl1 with field id and a second db 
called db2 and a table called tbl2 with field id, whats the correct syntax to 
query across the 2 databases eg:

"select * db1..tbl1 a , db2..tbl2 b where a.id=b.id"

This doesnt work, can someone modify it to work?


Thanks for your help in advance.


Dave


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


Re: [sqlite] Error in documentation for FOREIGN KEY ?

2010-03-14 Thread Simon Slavin

On 14 Mar 2010, at 11:13pm, Kees Nuyt wrote:

> Well, it is consistent, when you take 
> http://www.sqlite.org/syntaxdiagrams.html#table-constraint
> into account.
> 
> CREATE TABLE is the only statement where references
> constraints can be defined. Have a look at
> http://www.sqlite.org/lang_createtable.html
> 
> A column definition can contain a foreign-key-clause, which
> is simply a REFERENCES column-constraint.

Got it.  Thanks to Kees and Jay.

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


[sqlite] VACUUM & journal size

2010-03-14 Thread Matthew L. Creech
Hi,

I have a SQLite database with one large table, and I'd like to shrink
the size of that table to free up space in the filesystem.  My problem
is that the database is (for example) 100 MB, and I have 80 MB of free
filesystem space.  I figured that I could DELETE, say, half of the
records from the table, then VACUUM, and the VACUUM would
[temporarily] need ~50 MB of free space for the journal (since that's
how much real data there is).

Instead, I'm finding that it needs a full 100 MB for the journal, even
though once the VACUUM succeeds the resulting DB is only 50 MB.  As a
result, I'm stuck unable to shrink the database, since VACUUM fails
with a disk I/O error (out of space), seemingly no matter many entries
I remove ahead of time.  I know the space is being freed, since
"PRAGMA freelist_count" shows the expected numbers.  So presumably
this is just an artifact of the way VACUUM is implemented internally.

Is there anything that I can do to shrink the database in-place?  It
looks like auto-vacuum is out of the question, since it has to have
been enabled before the table was initially created.  FYI, this
situation exists on a few dozen devices in various locations, so I'd
need a programmatic solution - "move the DB somewhere else, VACUUM,
then move it back" won't work, unfortunately.  :-)

Any tips are appreciated.  Thanks!

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


Re: [sqlite] Can I add a FOREIGN KEY later ?

2010-03-14 Thread Jay A. Kreibich
On Sun, Mar 14, 2010 at 10:05:14PM +, Simon Slavin scratched on the wall:
> I was trying to work out if I could add a FOREIGN KEY to a table
> after it is originally created, using the ALTER TABLE command. 
> Assuming that my database was originally created with 3.6.19 or
> later, can I do this ?

  The first paragraph:   http://www.sqlite.org/lang_altertable.html

SQLite supports a limited subset of ALTER TABLE. The
ALTER TABLE command in SQLite allows the user to
rename a table or to add a new column to an existing table.
It is not possible to rename a column, remove a column, or add
or remove constraints from a table.



  A FOREIGN KEY is a constraint.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Error in documentation for FOREIGN KEY ?

2010-03-14 Thread Kees Nuyt
On Sun, 14 Mar 2010 22:04:18 +, Simon Slavin
 wrote:

> I was trying to work out if I could add a FOREIGN KEY to a table after
> it is originally created, using the ALTER TABLE command. I think there's an 
> error on
>
>http://www.sqlite.org/syntaxdiagrams.html
>
> I cannot find anything on that page that includes the actual words 'FOREIGN 
> KEY'.  The section
>
>http://www.sqlite.org/syntaxdiagrams.html#foreign-key-clause
>
> starts with the word 'REFERENCES'.  I traced up and down the hierarchy 
> but I couldn't find 'FOREIGN KEY'.  This is not consistent with the
> description of creating FK constraints as described in
>
>http://www.sqlite.org/foreignkeys.html

Well, it is consistent, when you take 
http://www.sqlite.org/syntaxdiagrams.html#table-constraint
into account.

CREATE TABLE is the only statement where references
constraints can be defined. Have a look at
http://www.sqlite.org/lang_createtable.html

A column definition can contain a foreign-key-clause, which
is simply a REFERENCES column-constraint.

In a table-constraint (defined after all column definitions)
a REFERENCES constraint is introduced by the FOREIGN KEY
keyword.

The difference between the two is that a table constraint
can (but doesn't have to) use composite keys, consisting of
more than one column.

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


Re: [sqlite] Error in documentation for FOREIGN KEY ?

2010-03-14 Thread Jay A. Kreibich
On Sun, Mar 14, 2010 at 10:04:18PM +, Simon Slavin scratched on the wall:

> http://www.sqlite.org/syntaxdiagrams.html
> 
>  I cannot find anything on that page that includes the actual
> words 'FOREIGN KEY'.

  http://www.sqlite.org/syntaxdiagrams.html#table-constraint

  For a column constraint, the FK phrase starts with "REFERENCES...".

  For a table constraint, the FK phrase starts with "FOREIGN KEY...
  REFERENCES...".

> The section
> 
> http://www.sqlite.org/syntaxdiagrams.html#foreign-key-clause
> 
> starts with the word 'REFERENCES'.  I traced up and down the hierarchy
> but I couldn't find 'FOREIGN KEY'.

  If you follow both "Used by" links, and find the reference to the
  child foreign-key-clause, it is pretty obvious.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Can I add a FOREIGN KEY later ?

2010-03-14 Thread Kees Nuyt
On Sun, 14 Mar 2010 22:05:14 +, Simon Slavin
 wrote:

> I was trying to work out if I could add a FOREIGN KEY to a table after
> it is originally created, using the ALTER TABLE command. 
> Assuming that my database was originally created with 3.6.19
> or later, can I do this ?

No, ALTER TABLE can only rename tables or add columns.

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


[sqlite] Can I add a FOREIGN KEY later ?

2010-03-14 Thread Simon Slavin
I was trying to work out if I could add a FOREIGN KEY to a table after it is 
originally created, using the ALTER TABLE command.  Assuming that my database 
was originally created with 3.6.19 or later, can I do this ?

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


[sqlite] Error in documentation for FOREIGN KEY ?

2010-03-14 Thread Simon Slavin
I was trying to work out if I could add a FOREIGN KEY to a table after it is 
originally created, using the ALTER TABLE command. I think there's an error on

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

 I cannot find anything on that page that includes the actual words 'FOREIGN 
KEY'.  The section

http://www.sqlite.org/syntaxdiagrams.html#foreign-key-clause

starts with the word 'REFERENCES'.  I traced up and down the hierarchy but I 
couldn't find 'FOREIGN KEY'.  This is not consistent with the description of 
creating FK constraints as described in

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

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


Re: [sqlite] Fastest concurrent read-only performance (+ threads vsprocesses) [BUG?]

2010-03-14 Thread Luke Evans
Absolutely.  

For what I'm trying to do, and given my experiments thus far, I would love to 
replicate the performance of the one-query/thread-per-process concurrency in 
the multithreaded case, foregoing the resource optimisations (shared cache 
etc.) and just having each query/thread do whatever is happening in the process 
case.

-- Luke

On 2010-03-14, at 6:54 AM, Olaf Schmidt wrote:

> If my assumption is right, that running such a multithreaded
> scenario against a singlethreaded compiled library performs
> at the same level as the multiple-processes-scenario,
> then the question remains, how one could make this mode
> "secure and usable" in the same way as with the "naturally isolated"
> process-memory.

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


Re: [sqlite] structure question

2010-03-14 Thread Max Vlasov
nci...@aquarelo.com> wrote:

> Thanks Dannis,
>
> The problem is a little bigger. I must have 2 instances of same table:
> original and latest. Then my problem is what is the best way to:
>  - transform 'original' with same data as 'latest'. This is 'save'.
>  - transform 'latest' with same data as 'original'. This is 'undo'.
>
> I must always have 2 tables. Client will read from original and
> Administrator will make changes. Then, if Administrator wants to apply
> those changes to Client a copy from 'latest' to 'original' must happen.
>
> Francisco A
>

Francisco, you probably want to find some easy solution and I understand
that. But what about just adding some extra logic inside your own code. What
is undo feature, it's just information about what should be done in order to
reverse changes. So you could create your own log table inside your sqlite
base logging your changes.It works if all the changes is controlled by you,
so before any DELETE, INSERT, UPDATE you can prepare this information and
write it into your log table. In case you want to apply undo, you read this
information and make all necessary undo operations.

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


Re: [sqlite] structure question

2010-03-14 Thread Francisco Azevedo
Thanks Dannis,

The problem is a little bigger. I must have 2 instances of same table: 
original and latest. Then my problem is what is the best way to:
  - transform 'original' with same data as 'latest'. This is 'save'.
  - transform 'latest' with same data as 'original'. This is 'undo'.

I must always have 2 tables. Client will read from original and 
Administrator will make changes. Then, if Administrator wants to apply 
those changes to Client a copy from 'latest' to 'original' must happen.

Francisco A

> On 10-02-26 2:25 PM, Francisco Azevedo wrote:
>> Hi all,
>>
>> I want to create a "publish/undo system" for some tables but i don't
>> know what is the best approach to do it.
>> Imagine i have a table with columns id (auto-inc), data (text) then i
>> want to edit table data (eg: create 2 new rows now, delete one tomorrow,
>> update 3 rows tomorow too) and then decide if i want to preserve that
>> modification or reverse it to the state it was before start that
>> modifications.
>>
>>
> Hi Francisco,
> 
> You should read this page in the wiki 
> http://www.sqlite.org/cvstrac/wiki?p=UndoRedo. It explains how to do 
> this sort of undo system using triggers.
> 
> HTH
> Dennis Cote
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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


[sqlite] FTS3 (Version 2 .6.23) - Is the boolean operator "AND" no more available?

2010-03-14 Thread gerard.jouannot
Hello all SQLlite users.
 
I wonder if the operator "AND" (in capitals letters) is yet available and
different from the basic term "and" (in lower letters).
 
Using the "MatchInfo" example of the documentation, I build an FTS3 virtual
table like this:
-- Create and populate an FTS3 table with four rows of two columns:


CREATE VIRTUAL TABLE t1 USING fts3(a, b);
INSERT INTO t1 VALUES('transaction default models default', 'Non transaction
reads');
INSERT INTO t1 VALUES('the default transaction', 'these semantics present');
INSERT INTO t1 VALUES('single request', 'default data');
INSERT INTO t1 VALUES('the default transaction and the default models', 'all
these semantics are written');

The documentation of FTS3 says: "the results are the same as if the two
basic queries were separated by an AND operator". In this case, these 2
lines should be equivalent, giving the same results:
 
SELECT ROWID FROM t1 WHERE t1 MATCH 'default transaction "these semantics"';
> Results OK: 2 rowids (2 and 4)
SELECT ROWID FROM t1 WHERE t1 MATCH 'default AND transaction AND "these
semantics"'; > Results false: 1 rowid (4)
 
In fact, it seems that the second SELECT is equivalent to this one (where
the term "and" is in lower letters): 
 
SELECT ROWID FROM t1 WHERE t1 MATCH 'default and transaction and "these
semantics"'; > Results OK: 1 rowid (4)
 
Is an erroneous understanding of the documentation or a bug?
 
A bug should explain that using the snippet function or the new MatchInfo
function, I obtain these results:
 
SELECT snippet(t1) FROM t1 WHERE t1 MATCH 'default AND transaction AND
"these semantics"';
the default transaction and the default
models...all these semantics are written
instead of
the default transaction and the default
models...all these semantics are written
 
SELECT quote(matchinfo(t1)) FROM t1 WHERE t1 MATCH 'default AND transaction
AND "these semantics"';
X'050002000500010001000300010001
00020002000100010001
000100'
   ^
   | should be 3, as below:
 
X'030002000500010003000100020001
0001000100'
 
 
Could you check it?
 
Many thanks for any explanation.
 
Best regards
 
Gérard
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Can default column values use max() ?

2010-03-14 Thread Simon Slavin

On 14 Mar 2010, at 3:14pm, P Kishor wrote:

> CREATE TABLE books (
>  id INTEGER PRIMARY KEY,
>  title TEXT,
>  author INTEGER DEFAULT (SELECT Max(id) FROM authors)
> );
> 
> and that just ain't gonna work.

Thanks, Puneet, I think that's the syntax I was groping towards.  Good to know 
it wasn't just me failing to do it right.

> A TRIGGER is the most elegant solution, and trivial to implement.


But if anyone has any ideas I'd love to see them.

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


Re: [sqlite] Can default column values use max() ?

2010-03-14 Thread P Kishor
On Sun, Mar 14, 2010 at 8:26 AM, Simon Slavin  wrote:
> My real database is too ridiculous to explain here, so here is an analogy:
>
> Table 1: Authors        -- columns id (INTEGER PRIMARY KEY), name (TEXT)
> Table 2: Books          -- columns id (INTEGER PRIMARY KEY), author 
> (INTEGER), title (TEXT)
>
> When I enter a new book, I want the author to default to the last author in 
> the database.  (For the sake of this example you can ignore tricks involving 
> deleting authors and reusing ids.)
>
> Suppose I add an author, than many books, then another author and many books 
> by the second author.  According to
>
> http://www.sqlite.org/syntaxdiagrams.html#column-constraint
>
> I can use an expression as the default value.  Can I use max(something) to do 
> what I want and, if so, how ?  I'm drawing a blank on the syntax and I can't 
> seem to google up an example.
>
> If it's not possible to do it this way, I assume I can use a TRIGGER to reset 
> the author field.  I tried that and it worked but using a default constraint 
> would be more elegant.
>


I am pretty sure you can't. Max(id) is not a standalone, column
expression such as Sin() or Length(). It is an aggregate expression
available inside a query, and not only that, you want to use Max(id)
from a different table.

Only way to get Max(id) is via 'SELECT Max(id) FROM table', which
would render your table definition as

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  author INTEGER DEFAULT (SELECT Max(id) FROM authors)
);

and that just ain't gonna work.

A TRIGGER is the most elegant solution, and trivial to implement.


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



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


[sqlite] Can default column values use max() ?

2010-03-14 Thread Simon Slavin
My real database is too ridiculous to explain here, so here is an analogy:

Table 1: Authors-- columns id (INTEGER PRIMARY KEY), name (TEXT)
Table 2: Books  -- columns id (INTEGER PRIMARY KEY), author (INTEGER), 
title (TEXT)

When I enter a new book, I want the author to default to the last author in the 
database.  (For the sake of this example you can ignore tricks involving 
deleting authors and reusing ids.)

Suppose I add an author, than many books, then another author and many books by 
the second author.  According to

http://www.sqlite.org/syntaxdiagrams.html#column-constraint

I can use an expression as the default value.  Can I use max(something) to do 
what I want and, if so, how ?  I'm drawing a blank on the syntax and I can't 
seem to google up an example.

If it's not possible to do it this way, I assume I can use a TRIGGER to reset 
the author field.  I tried that and it worked but using a default constraint 
would be more elegant.

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


Re: [sqlite] Fastest concurrent read-only performance (+ threads vsprocesses) [BUG?]

2010-03-14 Thread Olaf Schmidt

"Marcus Grimm"  schrieb im
Newsbeitrag news:4b9a01e8.2060...@medcom-online.de...

> Increasing the number of threads does affect the overall
> read performance slightly, example:
> 1 Thread: 11.2 sec
> 16 Threads: 8.2 sec
> Single Process: 11sec.
>
> I still would expect a better scaling, ...

IMO, what Lukes timings have clearly shown is,
that if you would enhance your "single-process-test"
to a "multiple, parallel-working-processes-test",
that the scaling would work nearly perfectly (as
it should).

Processes (only running their single "Default-Thread")
when working in parallel, would not profit from an
enabled Shared-Cache (no matter what the compile-
settings or the open-settings are).

Nearly the same should be expectable (naively thought) from
multiple, parallel threads within one single process. If Shared
Cache is "Off" in this mode, then the behaviour should be the
same as with multiple-running-processes (each process
only running one single thread).

In both cases the mem-consumption is higher, since
each "worker-thread" or "worker-process" runs its
own, separate cache, but the benefits in performance
(when all the caches are hot after a while) should not
only occur in the multiple-process-scenario.

So the question really is, what is different, when we
compare the multiple-process-based mode with the
multiple-threads mode (Shared-Cache = "Off").
Both modes want to trade memory versus performance,
but only the multiple-processes-mode gets the bargain
from this exchange currently.

One obvious thing (which very well could be the only
cause) is, that in process-mode, we have isolated
memory - and all the (cross-thread-)Locking-requests are
meaningless (don't wait, or cost much performance), because
there's only one single thread in each running process. Only
the "cross-process-capable locks" near the file-level take
effect and ensure correct concurrent behaviour between
multiple *processes*.
This should work Ok for multiple processes, even when
SQLite was compiled in "plain singlethreaded mode".

Now, using a similar, singlethreaded (no mutexes, no locking)
compiled sqlite3-library should result in the same performance as
with multiple-processes, when used from multiple-threads
(No Shared Cache).

If my assumption is right, that running such a multithreaded
scenario against a singlethreaded compiled library performs
at the same level as the multiple-processes-scenario,
then the question remains, how one could make this mode
"secure and usable" in the same way as with the "naturally isolated"
process-memory. Speaking only for the Win-platform, thread-
local storage comes to mind, or maybe it is enough (when all the
larger, important SQLite-object-types are created from the heap -
or at least can be forced to...), that the Heap-handle gets created
with the appropriate Creation-Flags for secure, threadsafe
allocations from this Heap-Handle later on.

Olaf



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