Re: [sqlite] Foreign keys and Temp Tables

[Dan Kennedy]

On Feb 9, 2010, at 8:54 AM, Paul Vercellotti wrote:

>
>
> Hi there,
>
> Are there any restrictions in SQLite on foreign key references in  
> temporary tables?   Is referential integrity enforced between temp  
> tables and persistent ones? (That is, does the referential integrity  
> checking take into account key references in a temp table to a  
> regular table's key?)   Couldn't find anything about this one way or  
> another in the docs.

Both tables in an FK relationship must be in the same database.

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

Re: [sqlite] Bug / Feature: CREATE VIRTUAL TABLE IF NOT EXISTS

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hamish Allan wrote:
> Whether it's
> storage-backed or not, won't attempting to create it a second time
> always give a "table already exists" error?

Yes.  But so what?  Your code is already specific to SQLite - ignore the error!

> Also, what is the recommended way to build sqlite3 if you want to make
> changes to parse.y, given that using the complete source tree tarball
> is heavily recommended against (in favour of the amalgamation, which
> only contains the generated code)?

The amalgamation is effectively 60 or so source files all concatenated
together.  If you want to change parse.y then you have to use the source
tarball since various tools like Lemon need to be built and run.  If you do
not want to do this as a one off then you are better off doing a checkout
from the source control system (Fossil) and having the changes on top of the
trunk.

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

iEYEARECAAYFAktw3RAACgkQmOOfHg372QRxHQCgvcuWLsFb2ukf4mQQBnD3B4kP
41kAoLjBiCvv2Z6IApznnBCD0aafahTn
=P3cr
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQlite vs. SQlite3 - a beginners question

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Simon Slavin wrote:
> SQLite3 is any version of SQLite from 3.0.0 upwards.  In the move from 2.x.x 
> to 3.x.x there were so many changes in how SQLite worked internally that it 
> was almost a different product.

The changes aren't actually that big.  The major difference is that SQLite
version 2 stored all values as strings and would try to convert to other
types as appropriate.  Version 3 uses manifest typing.

While the core functionality was very similar (ie making queries and getting
values), SQLite 3 has added more non-query functionality like virtual
tables, VFS, blob I/O, backup API, FTS, Rtree, ICU etc.

> Nevertheless, version 3 can read the files made by version 2.

No it can't.  The file formats are different.

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

iEYEARECAAYFAktw2+UACgkQmOOfHg372QQpxACfcbQKOrTTGkVvtriBWJv2w6X7
ZVYAoM78Bs1FWp/T2jBcXMCBRYnw6DXV
=1ROr
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQlite vs. SQlite3 - a beginners question

[Simon Slavin]

On 9 Feb 2010, at 3:17am, Richard Cooke wrote:

> I'm trying to teach myself PHP and SQLite and I've tried to understand 
> whether SQLite and SQLite3 are the same animal or are they completely 
> different.  I am using WAMP on a PC and the PHP version is 5.31.  If I 
> use sqlite_libversion(); I get a result of 2.8.17.  If I use 
> SQLite3::version() I get 3.6.15.  Does this make sense?
> 
> If they are completely different animals how do you decide on which one 
> to use?

SQLite3 is any version of SQLite from 3.0.0 upwards.  In the move from 2.x.x to 
3.x.x there were so many changes in how SQLite worked internally that it was 
almost a different product.  Nevertheless, version 3 can read the files made by 
version 2.

Needless to say, version 3 is better, faster, has more abilities, and it 
handles non-ASCII characters better.  If you have no reason to use the version 
3 libraries, use version 3.

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

Re: [sqlite] SQlite vs. SQlite3 - a beginners question

[Alex Mandel]

Richard Cooke wrote:
> I'm trying to teach myself PHP and SQLite and I've tried to understand 
> whether SQLite and SQLite3 are the same animal or are they completely 
> different.  I am using WAMP on a PC and the PHP version is 5.31.  If I 
> use sqlite_libversion(); I get a result of 2.8.17.  If I use 
> SQLite3::version() I get 3.6.15.  Does this make sense?
> 
> If they are completely different animals how do you decide on which one 
> to use?
> 
> Thanks,
> 
> RC

Use SQLite3 via PDO. sqlite 2.x is actually quite old at this point and
really shouldn't be used for new development, it there more for
backwards compatibility and maintainability of older apps.

Alex

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

[sqlite] SQlite vs. SQlite3 - a beginners question

[Richard Cooke]

I'm trying to teach myself PHP and SQLite and I've tried to understand 
whether SQLite and SQLite3 are the same animal or are they completely 
different.  I am using WAMP on a PC and the PHP version is 5.31.  If I 
use sqlite_libversion(); I get a result of 2.8.17.  If I use 
SQLite3::version() I get 3.6.15.  Does this make sense?

If they are completely different animals how do you decide on which one 
to use?

Thanks,

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

[sqlite] Foreign keys and Temp Tables

[Paul Vercellotti]

Hi there,

Are there any restrictions in SQLite on foreign key references in temporary 
tables?   Is referential integrity enforced between temp tables and persistent 
ones? (That is, does the referential integrity checking take into account key 
references in a temp table to a regular table's key?)   Couldn't find anything 
about this one way or another in the docs.

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

Re: [sqlite] Downloading older version of sqlite

[D. Richard Hipp]

On Feb 8, 2010, at 5:10 PM, Fantoosh wrote:

> Hi All,
>
> I need to download an older version of sqlite - 3.5.3. Any ideas on  
> how/where
> i can get this version? I looked at the CVS branches/tags and I  
> couldn't find
> one that said 3.5.3.


http://www.sqlite.org/src/info/a39007d5b1

D. Richard Hipp
d...@hwaci.com



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

[sqlite] Downloading older version of sqlite

[Fantoosh]

Hi All,

I need to download an older version of sqlite - 3.5.3. Any ideas on how/where 
i can get this version? I looked at the CVS branches/tags and I couldn't find 
one that said 3.5.3.

Thanks.

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

Re: [sqlite] Bug / Feature: CREATE VIRTUAL TABLE IF NOT EXISTS

[Hamish Allan]

On Mon, Feb 8, 2010 at 9:26 PM, Roger Binns  wrote:

> Virtual tables do not directly have any storage - they are just a row in
> sqlite_master.
>
> The implementation may do something.  For example FTS3 creates 3 real tables
> behind the scenes.  Virtual tables that map to a CSV file generally do not
> delete the CSV file when the virtual table is dropped.
>
> What this means is that for some cases it is perfectly harmless to drop the
> virtual table as it has no side effects.  In other cases (eg FTS3) then you
> would lose its behind the scenes tables.

I am using FTS3, so in my case I cannot just drop the table.

Even if the virtual table is just a row in sqlite_master, doesn't it
still makes sense to support IF NOT EXISTS? Whether it's
storage-backed or not, won't attempting to create it a second time
always give a "table already exists" error?

Also, what is the recommended way to build sqlite3 if you want to make
changes to parse.y, given that using the complete source tree tarball
is heavily recommended against (in favour of the amalgamation, which
only contains the generated code)?

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

Re: [sqlite] Bug / Feature: CREATE VIRTUAL TABLE IF NOT EXISTS

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hamish Allan wrote:
> The workaround suggested on the tracker ("Couldn't you just do a 'drop
> table if exists' first?") does not make sense, as it ensures the
> emptiness of the table rather than just its existence.

Virtual tables do not directly have any storage - they are just a row in
sqlite_master.

The implementation may do something.  For example FTS3 creates 3 real tables
behind the scenes.  Virtual tables that map to a CSV file generally do not
delete the CSV file when the virtual table is dropped.

What this means is that for some cases it is perfectly harmless to drop the
virtual table as it has no side effects.  In other cases (eg FTS3) then you
would lose its behind the scenes tables.

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

iEYEARECAAYFAktwgYgACgkQmOOfHg372QTbGACfZE/GuQxo+Te2IXJ/gnzs5Het
x1QAn0EJPFpFjvyroPnP84TPkbvHP8mO
=8ClE
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] any command to find last rowid in a table

[Shane Harrelson]

Assuming you have a table with the following schema:

CREATE TABLE t1 (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   col2 REAL,
   col3 TEXT
);

your C code *could* look something like the following:

 sqlite3_exec(db, "INSERT INTO t1 (col2,col3) VALUES (1.0,'row one');",
0, 0, 0);
 rowid = sqlite3_last_insert_rowid(db);

HTH.
-Shane



On Mon, Feb 8, 2010 at 2:59 PM, Vasanta  wrote:

> I understand "SELECT last_insert_rowid()" gives rowird, but I have to call
> these SQL statements in C language code, for that I have to sqlite3_prepare
> and sqlite3_step() calls, I am looking for sample of sqlite3_step, how that
> return the rowid, can I call like this:
>
> int rowid;
> sqlite3_stmt *pStmt;
>
> sqlite3(pDB, "SELECT last_insert_rowid()", -1, &pStmt, 0);
> rowid = sqlite3_step(pStmt);
>
> On Mon, Feb 8, 2010 at 2:36 PM, Kees Nuyt  wrote:
>
> > On Mon, 8 Feb 2010 11:44:39 -0500, Vasanta
> >  wrote:
> >
> > > I tried to use this function call
> > > "sqlite3_last_insert_rowid()<
> > http://www.sqlite.org/c3ref/last_insert_rowid.html>"
> > > calling from C language function, but it always returns zero, any
> idea?.
> > > I have valid DB handle.
> >
> > The function only returns the rowid of the last successful
> > INSERT statement on the same connection / DB handle. It
> > tells you which row has been inserted.
> >
> > Perhaps you expected it to predict which row would be
> > inserted on the next INSERT statement?
> >
> > If you need a new ID for every row you insert, don't try to
> > find out which value to use, but let SQLite do the work.
> >
> > Sample code:
> >
> > CREATE TABLE t1 (
> >id INTEGER PRIMARY KEY AUTOINCREMENT,
> >col2 REAL,
> >col3 TEXT
> > );
> >
> > BEGIN;
> > INSERT INTO t1 (col2,col3)
> >VALUES (julianday('now'),'row one');
> > SELECT last_insert_rowid();
> > INSERT INTO t1 (col2,col3)
> >VALUES (julianday('now'),'row two');
> > SELECT last_insert_rowid();
> > INSERT INTO t1 (col2,col3)
> >VALUES (julianday('now'),'row three');
> > SELECT last_insert_rowid();
> > COMMIT;
> >
> > Read http://www.sqlite.org/c3ref/last_insert_rowid.html
> > again for more details.
> > --
> >  (  Kees Nuyt
> >  )
> > c[_]
> >  ___
> > 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] any command to find last rowid in a table

[Vasanta]

I understand "SELECT last_insert_rowid()" gives rowird, but I have to call
these SQL statements in C language code, for that I have to sqlite3_prepare
and sqlite3_step() calls, I am looking for sample of sqlite3_step, how that
return the rowid, can I call like this:

int rowid;
sqlite3_stmt *pStmt;

sqlite3(pDB, "SELECT last_insert_rowid()", -1, &pStmt, 0);
rowid = sqlite3_step(pStmt);

On Mon, Feb 8, 2010 at 2:36 PM, Kees Nuyt  wrote:

> On Mon, 8 Feb 2010 11:44:39 -0500, Vasanta
>  wrote:
>
> > I tried to use this function call
> > "sqlite3_last_insert_rowid()<
> http://www.sqlite.org/c3ref/last_insert_rowid.html>"
> > calling from C language function, but it always returns zero, any idea?.
> > I have valid DB handle.
>
> The function only returns the rowid of the last successful
> INSERT statement on the same connection / DB handle. It
> tells you which row has been inserted.
>
> Perhaps you expected it to predict which row would be
> inserted on the next INSERT statement?
>
> If you need a new ID for every row you insert, don't try to
> find out which value to use, but let SQLite do the work.
>
> Sample code:
>
> CREATE TABLE t1 (
>id INTEGER PRIMARY KEY AUTOINCREMENT,
>col2 REAL,
>col3 TEXT
> );
>
> BEGIN;
> INSERT INTO t1 (col2,col3)
>VALUES (julianday('now'),'row one');
> SELECT last_insert_rowid();
> INSERT INTO t1 (col2,col3)
>VALUES (julianday('now'),'row two');
> SELECT last_insert_rowid();
> INSERT INTO t1 (col2,col3)
>VALUES (julianday('now'),'row three');
> SELECT last_insert_rowid();
> COMMIT;
>
> Read http://www.sqlite.org/c3ref/last_insert_rowid.html
> again for more details.
> --
>  (  Kees Nuyt
>  )
> c[_]
>  ___
> 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] Performance regression with many joins (3.6.11 -> 3.6.{21, 22})

[Andreas Klöckner]

(I had trouble posting this to the mailing list--I've tried a number
of times from various addresses, but the message never showed up in
the archive. I am thus trying again.)


Hi there,

I'm running the query:

select dg_order,
avg((sum_n_flops_lift.value+sum_n_flops_diff.value+sum_n_flops_gather.value+sum_n_flops_vector_math.value+sum_n_flops_rk4.value)/(max_t_step.value))/1e9
as gflops from runs  inner join (select run_id, step, sum(value) as value from
n_flops_gather group by run_id,step) as sum_n_flops_gather on
(sum_n_flops_gather.run_id = runs.id)  inner join (select run_id, step,
sum(value) as value from n_flops_vector_math group by run_id,step) as
sum_n_flops_vector_math on (sum_n_flops_vector_math.run_id = runs.id and
sum_n_flops_gather.step = sum_n_flops_vector_math.step)  inner join (select
run_id, step, sum(value) as value from n_flops_lift group by run_id,step) as
sum_n_flops_lift on (sum_n_flops_lift.run_id = runs.id and
sum_n_flops_vector_math.step = sum_n_flops_lift.step)  inner join (select
run_id, step, max(value) as value from t_step group by run_id,step) as
max_t_step on (max_t_step.run_id = runs.id and sum_n_flops_lift.step =
max_t_step.step)  inner join (select run_id, step, sum(value) as value from
n_flops_diff group by run_id,step) as sum_n_flops_diff on
(sum_n_flops_diff.run_id = runs.id and max_t_step.step =
sum_n_flops_diff.step)  inner join (select run_id, step, max(value) as value
from step group by run_id,step) as max_step on (max_step.run_id = runs.id and
sum_n_flops_diff.step = max_step.step)  inner join (select run_id, step,
sum(value) as value from n_flops_rk4 group by run_id,step) as sum_n_flops_rk4
on (sum_n_flops_rk4.run_id = runs.id and max_step.step = sum_n_flops_rk4.step)
where run_target="gpu" and rank_count=1 and max_step.value>3 group by
dg_order;

On Sqlite 3.6.11, the query returns in a few seconds on a slow network
filesystem. On Sqlite 3.6.{21,22}, the query does not appear to finish (10+
minutes) even when on a local filesystem.

The whole database (~150M), explain output from both versions, and the
schema can be found at http://tiker.net/tmp/sqlite-bug

Any clues?

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

Re: [sqlite] any command to find last rowid in a table

[Kees Nuyt]

On Mon, 8 Feb 2010 11:44:39 -0500, Vasanta
 wrote:

> I tried to use this function call
> "sqlite3_last_insert_rowid()"
> calling from C language function, but it always returns zero, any idea?. 
> I have valid DB handle.

The function only returns the rowid of the last successful
INSERT statement on the same connection / DB handle. It
tells you which row has been inserted.

Perhaps you expected it to predict which row would be
inserted on the next INSERT statement?

If you need a new ID for every row you insert, don't try to
find out which value to use, but let SQLite do the work.

Sample code:

CREATE TABLE t1 (
id INTEGER PRIMARY KEY AUTOINCREMENT,
col2 REAL,
col3 TEXT
);

BEGIN;
INSERT INTO t1 (col2,col3) 
VALUES (julianday('now'),'row one');
SELECT last_insert_rowid();
INSERT INTO t1 (col2,col3) 
VALUES (julianday('now'),'row two');
SELECT last_insert_rowid();
INSERT INTO t1 (col2,col3) 
VALUES (julianday('now'),'row three');
SELECT last_insert_rowid();
COMMIT;

Read http://www.sqlite.org/c3ref/last_insert_rowid.html
again for more details.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug / Feature: CREATE VIRTUAL TABLE IF NOT EXISTS

[Hamish Allan]

Hi,

I was surprised to find the "IF NOT EXISTS" syntax missing from
"CREATE VIRTUAL TABLE". I googled and found:

http://www.sqlite.org/cvstrac/tktview?tn=2604

The workaround suggested on the tracker ("Couldn't you just do a 'drop
table if exists' first?") does not make sense, as it ensures the
emptiness of the table rather than just its existence.

Somebody has submitted a patch, but I'm using the amalgamation sources
and I'd rather not have to keep re-applying patches manually if
there's no good reason not to support the syntax in the official
version.

I'd like to add my vote for this to be fixed.

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

Re: [sqlite] any command to find last rowid in a table

[Vasanta]

I tried to use this function call
"sqlite3_last_insert_rowid()"
calling from C language function, but it always returns zero, any idea?. I
have valid DB handle.



On Sat, Feb 6, 2010 at 9:25 AM, Vasanta  wrote:

> I found this C function call, this solved my problem,
> sqlite3_last_insert_rowid(),
>  I just want to append new entries to existing imported table.
>
>
>
>
>
>
>
> On Sat, Feb 6, 2010 at 8:36 AM, Ibrahim A wrote:
>
>> Am 05.02.2010 22:33, schrieb Vasanta:
>> > Kittayya:
>> >
>> > My issue is, I already have imported table in the Database, there alreay
>> > around 1000 records in that table where ROWID is from 1 to 1000, now
>> system
>> > generates new events, where ROWID again starts from beginning from 1,
>> now
>> > these new events are overwriting the earlier imported events by "REPLACE
>> > INTO..", I made that change to instead REPLACE, I need INSERT, but
>> now I
>> > need new ROWID (I need to update at the end of previous imported
>> records. I
>> > don't want to overwrite original records.
>> >
>> >
>> >
>> First of all a few questions to make things clear :
>>
>> 1) you import your data with a script that inserts rows with existing
>> rowid's ?
>>
>> after that :
>>
>> 2) you try to insert new rows with INSERT ... but it doesn't work as you
>> expect because you think you need to specify a new unused rowid ?
>>
>> if so :
>>
>> a) don't classify the field rowid in your insert command because sqlite
>> then generates automatically new unused values.
>> b) if your script (from earlier posts) restores your database with sql
>> commands (most likely) then try to avoid using rowid in that script at
>> all. The default behaviour for rowid works fine especially for your
>> problem with only a few thousand entries in the database
>> c) if rowid is your primary key to identify your data, then you depend
>> on a internal feature of the engine and you should change this with a
>> user defined field called id which could also be a autoincrement field
>> similar to rowid for better performanc (integer primary key autoinc ---
>> look at the documentation)
>>
>> your problem is that you try to insert new rows with a rowid and you
>> will only get performance problems when you have to search for the
>> maximum value of a rowid each time you search for a new valid id. try to
>> avoid this by defining your own primary key. At least don't try to
>> specify a rowid value when you insert new rows.
>>
>> Hope this could solve your problem ;)
>>  ___
>> 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] system.data.sqlite & encryption

[Shane Harrelson]

I've taken a cursory glance at the encryption support in the
system.data.sqlite C# wrapper from http://sqlite.phxsoftware.com
It does appear, that you could, with a little effort, modify the wrapper to
work with the SEE extension.   I don't see anything in the wrapper that
would make it impossible.   The only tricky bit would be utilizing the
support for passing the encryption key on the open commands (although you
should be able to sidestep this and use the encryption pragmas of the SEE
extension directly.)

HTH.
-Shane

On Sat, Feb 6, 2010 at 7:21 PM, Sylvain Pointeau  wrote:

> I already recompiled it, but integrating the encryption extension is again
> another step.
> how to be sure of that before to buy the extension ($2'000)  ?
>
> Sylvain
>
> On Sat, Feb 6, 2010 at 1:44 AM, Shane Harrelson  wrote:
>
>> Yes.   If you're willing to compile (and possibly modify) the
>> system.data.sqlite libraries for yourself, it should work.
>>
>> -Shane
>>
>> On Fri, Feb 5, 2010 at 5:05 AM, Sylvain Pointeau <
>> sylvain.point...@gmail.com
>> > wrote:
>>
>> > I posted on this forum
>> > http://sqlite.phxsoftware.com/forums/p/2170/8904.aspx
>> >
>> > The answer was: "I'm afraid not.  This is an open-source project, and
>> the
>> > encryption Dr. Hipp sells is not open source."
>> >
>> > but as the license of system.data.sqlite is "Released to the public
>> domain,
>> > use at your own risk!"
>> > I think I can integrate the encrypted sqlite lib, that I will buy, into
>> > system.data.sqlite.
>> >
>> > I would just like to have a bit of guidance...
>> >
>> > Best regards,
>> > Sylvain
>> >
>> > On Mon, Feb 1, 2010 at 4:41 PM, Shane Harrelson 
>> wrote:
>> >
>> > > I think you should probably ask this question on the
>> System.data.sqlite
>> > > support forums at
>> > >
>> > > http://sqlite.phxsoftware.com/forums
>> > >
>> > > I'm not familiar with enough with their ADO .NET implementation to
>> answer
>> > > your question.
>> > >
>> > > -Shane
>> > >
>> > >
>> > > On Sun, Jan 31, 2010 at 11:54 AM, Sylvain Pointeau <
>> > > sylvain.point...@gmail.com> wrote:
>> > >
>> > > > Hi all,
>> > > >
>> > > > I am using System.data.sqlite from my .NET project,
>> > > > but I am planning to access my db from C/C++ and C# / C++/CI
>> > > >
>> > > > The point is that I want an encryted database, and I plan to use the
>> > > > extension from
>> > > > http://www.hwaci.com/sw/sqlite/prosupport.html#crypto
>> > > >
>> > > > how to use / implement this exension in system.data.sqlite?
>> > > > or are they compatible both?
>> > > >
>> > > > thank you in advance for your help.
>> > > >
>> > > > Best regards,
>> > > > Sylvain
>> > > > ___
>> > > > 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-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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] system.data.sqlite & encryption

[Shane Harrelson]

I've taken a cursory glance at the encryption support in the
system.data.sqlite C# wrapper from http://sqlite.phxsoftware.com
It does appear, that you could, with a little effort, modify the wrapper to
work with the SEE extension.   I don't see anything in the wrapper that
would make it impossible.   The only tricky bit would be utilizing the
support for passing the encryption key on the open commands (although you
should be able to sidestep this and use the encryption pragmas of the SEE
extension directly.)

HTH.
-Shane


On Sat, Feb 6, 2010 at 7:21 PM, Sylvain Pointeau  wrote:

> I already recompiled it, but integrating the encryption extension is again
> another step.
> how to be sure of that before to buy the extension ($2'000)  ?
>
> Sylvain
>
> On Sat, Feb 6, 2010 at 1:44 AM, Shane Harrelson  wrote:
>
>> Yes.   If you're willing to compile (and possibly modify) the
>> system.data.sqlite libraries for yourself, it should work.
>>
>> -Shane
>>
>> On Fri, Feb 5, 2010 at 5:05 AM, Sylvain Pointeau <
>> sylvain.point...@gmail.com
>> > wrote:
>>
>> > I posted on this forum
>> > http://sqlite.phxsoftware.com/forums/p/2170/8904.aspx
>> >
>> > The answer was: "I'm afraid not.  This is an open-source project, and
>> the
>> > encryption Dr. Hipp sells is not open source."
>> >
>> > but as the license of system.data.sqlite is "Released to the public
>> domain,
>> > use at your own risk!"
>> > I think I can integrate the encrypted sqlite lib, that I will buy, into
>> > system.data.sqlite.
>> >
>> > I would just like to have a bit of guidance...
>> >
>> > Best regards,
>> > Sylvain
>> >
>> > On Mon, Feb 1, 2010 at 4:41 PM, Shane Harrelson 
>> wrote:
>> >
>> > > I think you should probably ask this question on the
>> System.data.sqlite
>> > > support forums at
>> > >
>> > > http://sqlite.phxsoftware.com/forums
>> > >
>> > > I'm not familiar with enough with their ADO .NET implementation to
>> answer
>> > > your question.
>> > >
>> > > -Shane
>> > >
>> > >
>> > > On Sun, Jan 31, 2010 at 11:54 AM, Sylvain Pointeau <
>> > > sylvain.point...@gmail.com> wrote:
>> > >
>> > > > Hi all,
>> > > >
>> > > > I am using System.data.sqlite from my .NET project,
>> > > > but I am planning to access my db from C/C++ and C# / C++/CI
>> > > >
>> > > > The point is that I want an encryted database, and I plan to use the
>> > > > extension from
>> > > > http://www.hwaci.com/sw/sqlite/prosupport.html#crypto
>> > > >
>> > > > how to use / implement this exension in system.data.sqlite?
>> > > > or are they compatible both?
>> > > >
>> > > > thank you in advance for your help.
>> > > >
>> > > > Best regards,
>> > > > Sylvain
>> > > > ___
>> > > > 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-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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-Table constraint

[German Escallon]

Uhmm.. Interesting. Thanks for your help.

Jay A. Kreibich wrote:
> On Fri, Feb 05, 2010 at 06:42:34PM -0500, German Escallon scratched on the 
> wall:
>
>   
>> create table X(xid integer primary key);
>> create table Y(yid integer primary key, y_xid references X(xid) );
>> create table Z(zid integer primary key, z_xid references X(xid) );
>>
>> create table Y_Z
>>(
>>yzid integer primary key autoincrement,
>>yz_yid references Y(yid),  /*references primary key in y */
>>yz_zid references Z(zid),  /* references primary key in z */
>>CHECK
>>   (
>>  (select y_xid from Y where yid= yz_yid ) =  (select z_xid from 
>> Z where zid= yz_zid )
>>   )
>>);
>> 
>
>
>   OK, this is a tad on the ugly side, but it might work.  Basically I'm
>   having rows in y_z carry around a copy of the xid from the two
>   tables.  The CHECK constraint then becomes simple.   I've fiddled
>   with the names a bit to keep things straight in my head.
>
>   Yes, some of the UNIQUEs are redundant, but they're required for the
>   FKs to work correctly.
>
> --
> PRAGMA foreign_keys = 1;
>
> CREATE TABLE x ( xid  INTEGER   PRIMARY KEY ); 
> CREATE TABLE y ( yid  INTEGER   PRIMARY KEY,
>  xid  INTEGER   REFERENCES x ( xid ),
>  UNIQUE ( yid, xid ) );
> CREATE TABLE z ( zid  INTEGER   PRIMARY KEY,
>  xid  INTEGER   REFERENCES x ( xid ),
>  UNIQUE ( zid, xid ) );
>
> CREATE TABLE y_z ( yzid INTEGER   PRIMARY KEY   AUTOINCREMENT,
> yid INTEGER,
> y_xid   INTEGER,
> zid INTEGER,
> z_xid   INTEGER,
>
> FOREIGN KEY ( yid, y_xid ) REFERENCES y ( yid, xid ),
> FOREIGN KEY ( zid, z_xid ) REFERENCES z ( zid, xid ),
> CHECK ( y_xid == z_xid )
> );
>
> INSERT INTO x ( xid ) VALUES ( 1 );
> INSERT INTO x ( xid ) VALUES ( 2 );
>
> INSERT INTO y ( yid, xid ) VALUES ( 111, 1 );
> INSERT INTO y ( yid, xid ) VALUES ( 112, 1 );
> INSERT INTO y ( yid, xid ) VALUES ( 121, 2 );
> INSERT INTO y ( yid, xid ) VALUES ( 122, 2 );
>
> INSERT INTO z ( zid, xid ) VALUES ( 211, 1 );
> INSERT INTO z ( zid, xid ) VALUES ( 212, 1 );
> INSERT INTO z ( zid, xid ) VALUES ( 221, 2 );
> INSERT INTO z ( zid, xid ) VALUES ( 222, 2 );
>
> -- this should work:
> INSERT INTO y_z ( yid, y_xid, zid, z_xid ) VALUES ( 111, 1, 211, 1 );
>
> -- fails check constraint:
> INSERT INTO y_z ( yid, y_xid, zid, z_xid ) VALUES ( 111, 1, 221, 2 );
>
> -- fails foreign key constraint ( there is no y(221,1) )
> INSERT INTO y_z ( yid, y_xid, zid, z_xid ) VALUES ( 111, 1, 221, 1 );
> --
>
>   Of course, the INSERT on y_z gets... interesting.  There are ways to
>   reduce that, however.  Maybe something like:
>
> INSERT INTO y_z ( yid, y_xid, zid, z_xid )
> VALUES ( ?1, ( SELECT xid FROM y WHERE yid = ?1 ),
>  ?2, ( SELECT xid FROM z WHERE zid = ?2 ) );
>
>   You might even be able to work that into a trigger so you can just
>   insert yid and zid and the trigger fills in the y_xid and z_xid.
>
>   Actually... come to think of it, you might just build a trigger into
>   your original schema.  You can't put a sub-select into a CHECK, but
>   you can put one in a trigger.  Try an INSERT/UPDATE trigger that
>   makes sure all the references line up and throws an error if they
>   do not.
>
>-j
>
>
>   
>> Jusding by this diagram 
>>  I thought 
>> my last create table statement was syntactically correct.  But I got the 
>> following message:
>> "Error: subqueries prohibited in CHECK constraints"..  Uhmm..  Bummer.
>>
>> Here's some background to my problem.
>>
>> Table (X) is referenced by two other tables (Y & Z).  This are 
>> one-to-many relationships (for every record in X there are 0 or more 
>> records in both Y, and in Z).
>> Now..  I also have a many-to-many relationship between Y & Z (for every 
>> record in Y there are 0 or more records in Z, and for every record in Z 
>> there are 0 or more records in Y).  
>> So, I created a table Y_Z to keep track of this relationship.  If I 
>> remove the check constraint that is causing the error, how can I ensure 
>> that all Y_Z entries associate entries that reference the same record in 
>> X?  In other words, say I have..
>>
>> insert into X (xid) values(1);
>> insert into X (xid) values(2);
>> insert into Y (yid, y_xid) values (1, 1); /* references entry 1 in X */
>> insert into Z (zid, z_xid) values (1, 2); /* references entry 2 in X */
>>
>> I need a constraint that would fail the following statement, and 
>> maintain my data integrity.
>>
>> insert into Y_Z( yz_yid, yz_xid) values (1, 1).
>>
>> Is there any alternative you can suggest?  Thank you in advance.
>>
>> _

Re: [sqlite] any command to find last rowid in a table

[Robert Citek]

On Mon, Feb 8, 2010 at 9:31 AM, Vasanta  wrote:
> Can I use this function call in C code to return last rowid to be inserted?.

On Fri, Feb 5, 2010 at 2:50 PM, Petite Abeille  wrote:
> Help Vampires: A Spotter’s Guide
> http://slash7.com/2006/12/22/vampires/

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

Re: [sqlite] any command to find last rowid in a table

[Vasanta]

Can I use this function call in C code to return last rowid to be inserted?.





On Sat, Feb 6, 2010 at 9:25 AM, Vasanta  wrote:

> I found this C function call, this solved my problem,
> sqlite3_last_insert_rowid(),
>  I just want to append new entries to existing imported table.
>
>
>
>
>
>
>
> On Sat, Feb 6, 2010 at 8:36 AM, Ibrahim A wrote:
>
>> Am 05.02.2010 22:33, schrieb Vasanta:
>> > Kittayya:
>> >
>> > My issue is, I already have imported table in the Database, there alreay
>> > around 1000 records in that table where ROWID is from 1 to 1000, now
>> system
>> > generates new events, where ROWID again starts from beginning from 1,
>> now
>> > these new events are overwriting the earlier imported events by "REPLACE
>> > INTO..", I made that change to instead REPLACE, I need INSERT, but
>> now I
>> > need new ROWID (I need to update at the end of previous imported
>> records. I
>> > don't want to overwrite original records.
>> >
>> >
>> >
>> First of all a few questions to make things clear :
>>
>> 1) you import your data with a script that inserts rows with existing
>> rowid's ?
>>
>> after that :
>>
>> 2) you try to insert new rows with INSERT ... but it doesn't work as you
>> expect because you think you need to specify a new unused rowid ?
>>
>> if so :
>>
>> a) don't classify the field rowid in your insert command because sqlite
>> then generates automatically new unused values.
>> b) if your script (from earlier posts) restores your database with sql
>> commands (most likely) then try to avoid using rowid in that script at
>> all. The default behaviour for rowid works fine especially for your
>> problem with only a few thousand entries in the database
>> c) if rowid is your primary key to identify your data, then you depend
>> on a internal feature of the engine and you should change this with a
>> user defined field called id which could also be a autoincrement field
>> similar to rowid for better performanc (integer primary key autoinc ---
>> look at the documentation)
>>
>> your problem is that you try to insert new rows with a rowid and you
>> will only get performance problems when you have to search for the
>> maximum value of a rowid each time you search for a new valid id. try to
>> avoid this by defining your own primary key. At least don't try to
>> specify a rowid value when you insert new rows.
>>
>> Hope this could solve your problem ;)
>>  ___
>> 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