headers
Simon Wei | 24 May 2013 14:10
Picon
Favicon

Use more than one database in one application

Hi,

I use two databases in a application (.exe):

sqlite3_open16 (“db1.db”, db_1);  // open first
sqlite3_open16 (“db2.db”, db_2);  // open second

when I INSERT some data into db_1, data is actually inserted into db_2.

Is this my bug or Sqlite’s bug?

Thank you.

BR,

Simon 

_______________________________________________
sqlite-users mailing list
sqlite-users <at> sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Permalink | Reply | 1 comment |
headers
Matthijs ter Woord | 25 May 2013 13:05
Picon

Unattended install

Hi,

I'm looking for a way to install System.Data.SQLite unattended, as part of
the installation procedure of my product.
How can this be achieved?

Regards,
Matthijs ter Woord
Permalink | Reply | 1 comment |
headers
Roland Hughes | 24 May 2013 17:03
Favicon

Correct way to open multiple in memory databases

All,

I'm looking for the correct way to build a string (within Qt) to open multiple in memory databases.  The
example here:

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

Does not appear to work.

"file:memdb1?mode=memory&cache=shared",

It ends up creating files in the executable directory.

Thanks,
Roland
Permalink | Reply | 5 comments |
headers
Chris King | 23 May 2013 21:59
Favicon

BUG joining two views (3.7.16)

Download http://www.fstutoring.com/~chris/temp/bug.sqlite3 or load the  
.dump below the ---snip---; then perform the following:

$ sqlite3 -header bug.sqlite3

sqlite> select * from marking_next;
transition|place|marking
t1|p1|0
t1|p3|3
t1|p4|1
t2|p1|2
t2|p3|1
t2|p4|2

sqlite> select * from marking_next_tbl;
transition|place|marking
t1|p1|0
t1|p3|3
t1|p4|1
t2|p1|2
t2|p3|1
t2|p4|2

sqlite> select * from marking_next natural join active_transition;
transition|place|marking
t1|p3|3

sqlite> select * from marking_next_tbl natural join active_transition;
transition|place|marking
t1|p1|0
(Continue reading)

Permalink | Reply | 1 comment |
headers
kyan | 24 May 2013 13:07
Picon

Is pragma foreign_key_check fast enough to be used in normal application flow?

Hello Dr. Hipp and all,

Due to its implementation of foreign keys and their deferring, SQLite does
not provide any useful information when a foreign key is violated; it gives
a not very helpful "Foreign key constraint failed" message instead. This is
a problem because an application developer cannot produce a proper message
to a user when a FK is violated, for instance "You cannot delete X because
it is connected to Ys" because they don't know and can't somehow deduce X
and Y.

It occurred to me that this may be adequately worked around if foreign keys
are deferred, using the "pragma foreign_key_check" command. The idea is
that when a SQLITE_CONSTRAINT error occurs at commit, the application code
can catch it and use the foreign_key_check pragma to get information about
the FK violation(s) that caused the commit error so that a meaningful user
message can be produced before doing a rollback. But this would impose a
serious performance penalty if the check is "exhaustive" meaning that ALL
records in the database are checked against ALL foreign key constraints.

So my question is, is this pragma exhaustive or is it somehow optimised so
that it does not always perform a full database scan -e.g. by means of some
internal per-transaction FK violation counter or list? Because if it is
optimised and thus fast enough then I suppose I can try to use it for the
purpose I have described.

TIA.

--
Constantine Yannakopoulos
(Continue reading)

Permalink | Reply | 7 comments |
headers
Udon Shaun | 24 May 2013 12:54
Picon
Favicon
Gravatar

sqlite3_randomness Quality

Hi All.

I'm sure it's been asked before, but google hasn't revealed anything apart from statements that the
function sqlite3_randomness is "High Quality". I'm looking more for a  quantitative definition
(repetition, entropy etc)

Looking at the code, it seems to get a random seed from the OS (once only it seems). In the case of nix, this is
using /dev/urandom which is stated as appropriate for cryptographic purposes and maintains an entropy
pool from random sources. 

For windows. It seems to use the high-performance counter mangled with time. I'm not sure this is random at
all but would be acceptable for seeding a block cipher in counter mode to generate a csprng - e.g. Fortuna.
(the subsequent processing doesn't seem to be this, though).

For VxWorks, it is problematic since VxWorks <=6.3 do not have a /dev/urandom so the nix method defaults to a
time and PID based one. Since the PID is always constant, this strikes me that it may not have much entropy
(for the seed at least). I have run into problems with locking in the past where the random lock algo always
returns 0. Assuming that a time is returned, then again, like with windows, if it is used for a block cipher
in counter mode it may be sufficient.

But assuming that a value of some description is obtained for the seed, what is the quantitative assessment
of sqlite3_randomness? What is the repetition rate? (2^n). Is the result of sqlite3_randomness
acceptable as a cryptographically secure pseudo random number generator (csprng) or is it only
sufficient to seed a csprng - what does "High Quality" actually mean?

Regards
Shaun
Permalink | Reply | 2 comments |
headers
Joe Mistachkin | 24 May 2013 11:33
Gravatar

System.Data.SQLite version 1.0.86.0 released


System.Data.SQLite version 1.0.86.0 (with SQLite 3.7.17) is now available on
the System.Data.SQLite website:

     http://system.data.sqlite.org/

Further information about this release can be seen at

     http://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki

Please post on the SQLite mailing list (sqlite-users at sqlite.org) if you
encounter any problems with this release.

--
Joe Mistachkin
Permalink | Reply | 0 comments |
headers
Peter Aronson | 23 May 2013 21:09
Picon
Favicon

Are RTREE virtual tables supposed to treat NULL values as 0.0?

So, I was looking at some triggers to update an RTREE virtual table that someone 
else wrote.  I noticed that the trigger didn't handle NULLs.  I was curious, and 
decided to see what happened if you tried to insert NULL values into an RTREE.  
Actually, I rather expected it to throw an error.  Instead, the values (aside 
from the id which is a separate issue) became 0.0 like so:

CREATE VIRTUAL TABLE nulltest USING RTREE (pkid,v1,v2);
INSERT INTO nulltest DEFAULT VALUES;
SELECT * FROM nulltest;
pkid       v1         v2 
---------- ---------- ----------
1          0.0        0.0 
 
This is not actually an ideal result, since 0.0 could either be a legitimate 
value, which means a search of the rtree table could produce a false positive; 
or it could be a completely unexpected value and cause who know what sort of 
problems.
 
Mind you, while I use RTREEs in SQLite myself, this isn't a problem for me, as I 
always supply legitimate values.
 
When I looked at the SQLite source, you can see that there is no check for NULL 
in the rtree code except for ids, so the calls to sqlite3_value_double in 
rtreeValueDown and rtreeValueUp are just going to return 0.0 when NULLs are 
supplied.  It seems to me that logically, they should probably throw constraint 
violations instead, but at this point that would be an issue for backwards 
compatibility.
 
Peter
(Continue reading)

Permalink | Reply | 1 comment |
headers
veneff | 23 May 2013 21:07

Best way to implement Find commands


I've got a prepared statement from a original query that may have been stepped x times to row X.
I want to implement the functions:
FindFirst - find the first row (between row 1 and the last available row inclusive) that satisifies a new query.
FindLast - find the last row (between row 1 and the last available row inclusive) that satisifies a new query.
FindNext - find the next row (between row X and the last available row inclusive) that satisfies a new query.
FindPrevious - find the last row (between row 1 and row X-1 inclusive) that satisfies a new query.

What I thought I would do is generate a temporary table based on the original select statement and then
execute the new query combined with rowid constraints and LIMIT of 1 in order to generate the row for the
Find statement.
The new query is defined by SELECT * FROM original_select_sql WHERE new_expr.
I would generate atemporary table with CREATE TEMP TABLE temp_xxx AS original_select_sql;
So, for FindFirst the resultant new query would be SELECT * from temp_xxx WHERE (new_expr) LIMIT 1;
For FindNext: SELECT * from temp_xxx WHERE (new_expr) AND rowid > X LIMIT 1;

Is this the bset approach?
And if so, can you suggest queries for FindLast and FindPrevious?
And, can I depend on SQLite to generate results in the same order as the original prepare/step sequence and
the temp table generation, ie. is the temp table's rowid going to be consistent with the original step order?

Thanks for any insight?

Vance
Permalink | Reply | 7 comments |
headers
Jill Rabinowitz | 23 May 2013 20:06
Picon

SQLite NULL or 0-Length

Hello,

I am having trouble with a SQLite  IFNULL and replace statements.  I am
trying to put a value into a column that has no value / has a zero length.
   I am wondering whether anyone can shed light on this.

I'm trying to set column firstname to 'xxx' if the column has a NULL value
(or has length = 0).   I am able to check the number of rows returned by
running a select statement with the following "where" clauses:
1)  where firstname IS NULL       -------------> 0 rows returned
2)  where length(firstname) = 0   -------------> returns 100 rows
3)  where firstname=""                --------------> returns 100 rows

The problem is that the IFNULL and REPLACE functions are not working in my
SELECT statement, so I am unable to set firstname = 'xxx' where no value
exists.

1) select IFNULL(firstname, 'xxx')                 <--------------- does
not set the value to 'xxx', which is consistent with (1) above
from tablename;

2) select replace(firstname, '','xxx')              <--------------- does
not set the value to 'xxx', which contradicts (3) above, as the string is
empty

Does anyone know how I can check the column and set it to a value if it has
nothing in it?

Thank you in advance!
(Continue reading)

Permalink | Reply | 4 comments |
headers
Kevin Keigwin | 23 May 2013 19:19
Favicon

[SQLite.NET] Cannot read BLOB data

I am using the 10.0.85 version of the Win32 SQLite.NET data adapter to read
a spatialite database table.  This table has a single geometry column in it,
which stores data as a BLOB.  I cannot read the data into a DataTable using
the adapter, because the exception "Invalid storage type: DBNull." is
thrown.
The exception appears to be thrown by the DbDataAdapter base class as a
result of the SQLiteDataAdapter assigning the BLOB column the type of
System.DBNull.  Stepping through the code, I can see in SQLiteConvert.cs the
method SQLiteTypeToType() uses the column's type (Object) and its affinity
(null) to determine the "equivalent" .NET Type as DBNull.  I see that the
lookup array used by SQLiteTypeToType() provides a conversion from affinity
BLOB to Type byte[], which is what is desired here.  However, the SQLite 3
documentation is clear that there is no such affinity - BLOBs are given
affinity "none".  At this point, I am unclear how to read this data using
the SQLiteDataAdapter.
Thanks in advance for any help.
Kevin
Permalink | Reply | 2 comments |

Gmane