Re: [sqlite] Attached database
Ok, as the ATTACH is not committed into the database, I understand why we cannot have cross-database constraints. Thanks! Sylvain -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov Sent: Wednesday, January 20, 2010 6:14 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Attached database Is it possible? If I attach database B to database A and database A to database B. As these statements are committed into A and B, they stay attached even if I close and reopen. So If another process opens A (or B), it has B (or A) attached to A (or B). I don't have to repeat the ATTACH statement each time I open the database right? No, that's incorrect. ATTACH commands are per-connection and never get committed to database. You have to attach databases any time you want to use them. You can attach them in any order or do not attach them at all - doesn't make any difference for SQLite, it's just for your convenience and it can make difference in how you write your queries. Pavel On Wed, Jan 20, 2010 at 12:07 PM, Tiberio, Sylvain sylvain.tibe...@eads.com wrote: Hi, And how precisely do you expect SQLite to pull that off? Ignore the DETACH command and set and error... No? As when you insert a row that doesn't respect a constraints. How it is going to stop you from, say, opening a separate connection to one of these databases (and never attaching the other), possibly from a different process? Is it possible? If I attach database B to database A and database A to database B. As these statements are committed into A and B, they stay attached even if I close and reopen. So If another process opens A (or B), it has B (or A) attached to A (or B). I don't have to repeat the ATTACH statement each time I open the database right? I agree that if data are split in several files, it is a way to introduce constraints violation (for instance if I backup/restore one file and not the others...). My first need was to separate my database into two separate files. In the 1st file I store my main data, In the second I store relation between data (and I accept to lost it). I would like to be able to backup/restore only the 1st file. It works fine with ATTACH but I don't have constraints on reference and view! Sylvain -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Wednesday, January 20, 2010 5:17 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Attached database Tiberio, Sylvain sylvain.tibe...@eads.com wrote: About cross-database foreign key constraints: I agree with you that attached database can be dettached or changed. So as it is not possible to create the foreign key constraint with no attached database (because parent table doesn't exist), I can imagine that SQLite doesn't allow to detach database when foreign key constraints exist on it. And how precisely do you expect SQLite to pull that off? How it is going to stop you from, say, opening a separate connection to one of these databases (and never attaching the other), possibly from a different process? Igor Tandetnik ___ 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] Parsing create statements
BareFeet wrote: At the moment I am resorting to developing regular expressions to do the parsing. They work, but it seems to be re-inventing the wheel. You won't be able to do parsing completely with regular expressions. Create statements let you specify default values for a column and that can be any arbitrary SQL expression. You can use SQLite's own SQL parser (http://www.hwaci.com/sw/lemon/lemon.html) with SQLite's own grammar (to ba found in SQLite's sources) and adapt it to you needs. Regards, Vivien ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Index creation in a memory database
Hi Igor! Thanks for your reply. I tested my self that solution before sending the question, but didn't work. I have tested again now (may be I did not ok before), but the same result: near .: syntax error Any idea? Thanks in advance, David ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index creation in a memory database
2010/1/21 David Alcelay dalce...@manutencion.ulma.es: Hi Igor! Thanks for your reply. I tested my self that solution before sending the question, but didn't work. I have tested again now (may be I did not ok before), but the same result: near .: syntax error Did you try create index memoria.Dispositivos_TipoDispositivo on Dispositivos (Tipo_Dispositivo) ? Any idea? Thanks in advance, David Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index creation in a memory database
Hi Simon! That was the solution. I thought I have tested all the combinations but I was wrong. Maybe the question for this issue is why it's not ok this syntax: create index memoria.Dispositivos_TipoDispositivo on memoria.Dispositivos Thank you, David ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL Crash with sqlite 3.6.22 commandline
I hava a table. The table schema is CREATE TABLE AddressTable ( AddressID INTEGER PRIMARY KEY , AddressType INTEGER , Name TEXT COLLATE RMNOCASE , Street1 TEXT , Street2 TEXT , City TEXT , State TEXT , Zip TEXT , Country TEXT , Phone1 TEXT , Phone2 TEXT , Fax TEXT , Email TEXT , URL TEXT , Latitude INTEGER , Longitude INTEGER , Note BLOB ) ;. if I execute following sql to query data , the sqlite 3.6.22 command line downloaded from www.sqlite.org will crash. SELECT Adr.Name COLLATE NOCASE AS AddressName FROM AddressTable AS Adr WHERE Adr.Name LIKE '%_'. if I change the Adr.Name to AddressName , the sql execute result is ok. SELECT Adr.Name COLLATE NOCASE AS AddressName FROM AddressTable AS Adr WHERE AddressName LIKE '%_' ; it seems the crash was related with the collate RMNOCASE of AddressTable table's field Name. in default sqlite command line, there is no rmnocase collation. so I mapped it to the default nocase collation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Compile time warning
Hello, I have a simple program that uses SQLite. When I compile it I see the following warning: /home/daniel/.local/lib/libsqlite3.a(sqlite3.o): In function `memset': /usr/include/bits/string3.h:82: warning: memset used with constant zero length parameter; this could be due to transposed parameters Does anyone know what this means and how I can remove it? Thanks, Daniel. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compile time warning
On Jan 21, 2010, at 7:54 AM, Daniel Carrera wrote: Hello, I have a simple program that uses SQLite. When I compile it I see the following warning: /home/daniel/.local/lib/libsqlite3.a(sqlite3.o): In function `memset': /usr/include/bits/string3.h:82: warning: memset used with constant zero length parameter; this could be due to transposed parameters Does anyone know what this means and how I can remove it? This is caused by a bug in GCC. http://gcc.gnu.org/bugzilla/show_bug.cgi?id=42688 has additional information. But the bug appears to be harmless (correct code is generated in spite of the warning). So you should simply ignore it. Thanks, Daniel. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index creation in a memory database
Regarding: Maybe the question for this issue is why it's not ok this syntax: create index memoria.Dispositivos_TipoDispositivo on memoria.Dispositivos From my reading of the CREATE INDEX syntax at: http://www.sqlite.org/lang_createindex.html the parentheses are required, not optional. Are they optional under the sql99 standard? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Crash with sqlite 3.6.22 commandline
I am unable to reproduce this problem. Using the script below, with RMNOCASE changed to just NOCASE, everything works fine on the SQLite command-line shell on the website on Linux. I also tried various other versions of SQLite with the same result. On Jan 21, 2010, at 8:00 AM, Hub Dog wrote: I hava a table. The table schema is CREATE TABLE AddressTable ( AddressID INTEGER PRIMARY KEY , AddressType INTEGER , Name TEXT COLLATE RMNOCASE , Street1 TEXT , Street2 TEXT , City TEXT , State TEXT , Zip TEXT , Country TEXT , Phone1 TEXT , Phone2 TEXT , Fax TEXT , Email TEXT , URL TEXT , Latitude INTEGER , Longitude INTEGER , Note BLOB ) ;. if I execute following sql to query data , the sqlite 3.6.22 command line downloaded from www.sqlite.org will crash. SELECT Adr.Name COLLATE NOCASE AS AddressName FROM AddressTable AS Adr WHERE Adr.Name LIKE '%_'. if I change the Adr.Name to AddressName , the sql execute result is ok. SELECT Adr.Name COLLATE NOCASE AS AddressName FROM AddressTable AS Adr WHERE AddressName LIKE '%_' ; it seems the crash was related with the collate RMNOCASE of AddressTable table's field Name. in default sqlite command line, there is no rmnocase collation. so I mapped it to the default nocase collation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Crash with sqlite 3.6.22 commandline
I am unable to reproduce this problem. Using the script below, with RMNOCASE changed to just NOCASE Probably that's exactly the point of crash in the OP's test case. He created table when RMNOCASE collation existed but then tries to execute query when that collation is not registered and unknown. Pavel On Thu, Jan 21, 2010 at 9:30 AM, D. Richard Hipp d...@hwaci.com wrote: I am unable to reproduce this problem. Using the script below, with RMNOCASE changed to just NOCASE, everything works fine on the SQLite command-line shell on the website on Linux. I also tried various other versions of SQLite with the same result. On Jan 21, 2010, at 8:00 AM, Hub Dog wrote: I hava a table. The table schema is CREATE TABLE AddressTable ( AddressID INTEGER PRIMARY KEY , AddressType INTEGER , Name TEXT COLLATE RMNOCASE , Street1 TEXT , Street2 TEXT , City TEXT , State TEXT , Zip TEXT , Country TEXT , Phone1 TEXT , Phone2 TEXT , Fax TEXT , Email TEXT , URL TEXT , Latitude INTEGER , Longitude INTEGER , Note BLOB ) ;. if I execute following sql to query data , the sqlite 3.6.22 command line downloaded from www.sqlite.org will crash. SELECT Adr.Name COLLATE NOCASE AS AddressName FROM AddressTable AS Adr WHERE Adr.Name LIKE '%_'. if I change the Adr.Name to AddressName , the sql execute result is ok. SELECT Adr.Name COLLATE NOCASE AS AddressName FROM AddressTable AS Adr WHERE AddressName LIKE '%_' ; it seems the crash was related with the collate RMNOCASE of AddressTable table's field Name. in default sqlite command line, there is no rmnocase collation. so I mapped it to the default nocase collation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compile time warning
D. Richard Hipp wrote: This is caused by a bug in GCC. http://gcc.gnu.org/bugzilla/show_bug.cgi?id=42688 has additional information. But the bug appears to be harmless (correct code is generated in spite of the warning). So you should simply ignore it. Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Crash with sqlite 3.6.22 commandline
I originally experienced the problem using a couple of Windows sqlite managers. It seems that ones using the latest few versions of sqlite have the problem, variously reported as: Access violation at address x in module . Read of address 0005, where x is dependent on the application. Of the three that I tested that had this crash, two reported it thusly, one threw up an unhandled Win32 exception and croaked - they are all, I believe using 3.6.21 or 22. Running the sqlite.exe command line version 3.6.22 throws up the unhandled Win32 exception. Running the 3.6.17 release gracefully reports SQL error near line 1: no such collation sequence: RMNOCASE. The errors and crashes are from the following query: SELECT Name COLLATE NOCASE FROM AddressTable WHERE Name LIKE '%_'; This revised query works on all versions listed above: SELECT Name COLLATE NOCASE AS NewName FROM AddressTable WHERE NewName LIKE '%_' ; as does: SELECT Name FROM AddressTable WHERE Name COLLATE NOCASE LIKE '%_'; I can forward you a sample database but I do not have access to the RMNOCASE collation. Tom D. Richard Hipp d...@hwaci.com wrote in message news:41371dfd-279f-429d-9186-476efb63e...@hwaci.com... I am unable to reproduce this problem. Using the script below, with RMNOCASE changed to just NOCASE, everything works fine on the SQLite command-line shell on the website on Linux. I also tried various other versions of SQLite with the same result. On Jan 21, 2010, at 8:00 AM, Hub Dog wrote: I hava a table. The table schema is CREATE TABLE AddressTable ( AddressID INTEGER PRIMARY KEY , AddressType INTEGER , Name TEXT COLLATE RMNOCASE , Street1 TEXT , Street2 TEXT , City TEXT , State TEXT , Zip TEXT , Country TEXT , Phone1 TEXT , Phone2 TEXT , Fax TEXT , Email TEXT , URL TEXT , Latitude INTEGER , Longitude INTEGER , Note BLOB ) ;. if I execute following sql to query data , the sqlite 3.6.22 command line downloaded from www.sqlite.org will crash. SELECT Adr.Name COLLATE NOCASE AS AddressName FROM AddressTable AS Adr WHERE Adr.Name LIKE '%_'. if I change the Adr.Name to AddressName , the sql execute result is ok. SELECT Adr.Name COLLATE NOCASE AS AddressName FROM AddressTable AS Adr WHERE AddressName LIKE '%_' ; it seems the crash was related with the collate RMNOCASE of AddressTable table's field Name. in default sqlite command line, there is no rmnocase collation. so I mapped it to the default nocase collation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index creation in a memory database
Hi! Uppsss, sorry about it, I wrote it incomplete, because we were talking about another part of the sentence. The next one is the original sentence I asked here what was wrong:create index memoria.Dispositivos_TipoDispositivo on memoria.Dispositivos (Tipo_Dispositivo) So you see that the final parentesis and the column are there. The error text the library was returning was:near .: syntax error The final solution was to erase the last 'memoria.' but it's extrange because the table is in that attached database (really it's a memory attached database), so it was confusing for me Hope now it's clear. Regards, David ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Crash with sqlite 3.6.22 commandline
The point is not how the table was created but rather that the absence of the RMNOCASE collation causes the query to crash the latest versions of sqlite while earlier versions gracefully report an error. Moreover, having saved a VIEW from this query resulted in these managers of later releases of sqlite (e.g. 3.6.21/22) reporting the access violation on opening the database. Go back far enough, to, say 3.5.4, and the query runs with no problem. I think that may have been where the VIEW was created. So what is a working query and VIEW in 3.5.4, became syntactically an error by 3.6.17 and a crash by 3.6.21. Tom - Original Message - From: Pavel Ivanov paiva...@gmail.com To: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Thursday, January 21, 2010 9:36 AM Subject: Re: [sqlite] SQL Crash with sqlite 3.6.22 commandline I am unable to reproduce this problem. Using the script below, with RMNOCASE changed to just NOCASE Probably that's exactly the point of crash in the OP's test case. He created table when RMNOCASE collation existed but then tries to execute query when that collation is not registered and unknown. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Parsing create statements
Hi Vivien, BareFeet wrote: Is there any way to parse a create statement (eg create table, create view, create trigger) into its components? Since SQLite does this internally using the Lemon parser, surely there's a simple way to get the parser results? Vivien wrote: You can use SQLite's own SQL parser (http://www.hwaci.com/sw/lemon/lemon.html) with SQLite's own grammar (to ba found in SQLite's sources) and adapt it to you needs. That's the theory, but how can I do that easily? It seems that I either have to reverse engineer the virtual machine code it produces or else write my own parsers in Lemon. I would hope there's a way to intercept the parsing built into SQLite to extract the parse tree, but none of my attempts to investigate this have been fruitful. Thanks, Tom BareFeet -- Comparison of SQLite GUI tools: 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
Re: [sqlite] Parsing create statements
On Jan 21, 2010, at 5:36 PM, BareFeet wrote: Vivien wrote: You can use SQLite's own SQL parser (http://www.hwaci.com/sw/lemon/lemon.html) with SQLite's own grammar (to ba found in SQLite's sources) and adapt it to you needs. That's the theory, but how can I do that easily? It seems that I either have to reverse engineer the virtual machine code it produces or else write my own parsers in Lemon. I would hope there's a way to intercept the parsing built into SQLite to extract the parse tree, but none of my attempts to investigate this have been fruitful. SQLite doesn't really do a parse tree so much. It does a little. Sometimes. But its style of operation is closer to syntax directed translation, especially for the CREATE TABLE statement. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Parsing create statements
Hi Roger, BareFeet wrote: At the moment I am resorting to developing regular expressions to do the parsing. They work, but it seems to be re-inventing the wheel. You won't be able to do parsing completely with regular expressions. Create statements let you specify default values for a column and that can be any arbitrary SQL expression. Yes, bracketed and quoted expressions, along with comments complicate the regex parsing. So, as I mentioned: I have to allow for quoted identifiers (eg Family ID), comments and nested brackets by tokenizing the string and substituting quoted/bracketed/commented sections with word placeholders before applying the regex. I do this by tokenizing the SQL string, then replacing any bracketed, quoted or commented tokens with a placeholder that survives the regex expression \w+ (without quotes). Please send me an example of any complex create table statement so I can try my method on it. Thanks, Tom BareFeet -- Comparison of SQLite GUI tools: 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
Re: [sqlite] Parsing create statements
Hi Richard, Thanks for your reply: SQLite doesn't really do a parse tree so much. It does a little. Sometimes. But its style of operation is closer to syntax directed translation, especially for the CREATE TABLE statement. So is there any way to extract a meaningful breakup of a create statement (I showed create table, but I am also after views, triggers etc) using a call to an SQLite method or is there some prepared Lemon syntax generator to do this? Thanks, Tom BareFeet -- Comparison of SQLite GUI tools: 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
