[sqlite] Getting Cygwin error under MinGW
Hi Folks: I just downloaded the latest amalgamated source and got the following error. Do I need to change or set someting in the environment? $ cd sqlite/sqlite-amalgamation-3080900 $ gcc -DSQLITE_THREADSAFE=0 shell.c sqlite3.c -ldl sqlite3.c: In function `winGetTempname': sqlite3.c:37577: error: `CCP_POSIX_TO_WIN_W' undeclared (first use in this function) sqlite3.c:37577: error: (Each undeclared identifier is reported only once sqlite3.c:37577: error: for each function it appears in.) sqlite3.c:37577: error: `CCP_POSIX_TO_WIN_A' undeclared (first use in this function) sqlite3.c: In function `winFullPathname': sqlite3.c:38286: error: `CCP_POSIX_TO_WIN_W' undeclared (first use in this function) sqlite3.c:38286: error: `CCP_POSIX_TO_WIN_A' undeclared (first use in this function) sqlite3.c:38287: error: `CCP_RELATIVE' undeclared (first use in this function) $ gcc -v Reading specs from /usr/lib/gcc/i686-pc-msys/3.4.4/specs Configured with: /home/cstrauss/build/gcc3/gcc-3.4.4/configure --prefix=/usr --sysconfdir=/etc --localstatedir=/var --infodir=/share/info --mandir=/share/man --libexecdir=/lib --en able-languages=c,c++ --disable-nls --enable-threads=posix --enable-sjlj-exceptions --enable-hash-synchronization --enable-libstdcxx-debug --with-newlib Thread model: posix gcc version 3.4.4 (msys special) $
[sqlite] possible Bug
On 2015-04-09 07:57 PM, Simon Slavin wrote: > On 9 Apr 2015, at 6:04pm, Gustav Melno wrote: > >> Thanks for the help. Adding a trailing underscore helped also. I'm still >> wondering why insertion worked at all because defining oid as column name >> with the type VARCHAR should result in an error on execution. > The three names for the integer primary key column (oid, rowid, _rowid_) work > only if the table doesn't have a defined column of that name. This allows > compatibility with people who didn't know that they were had special meanings > for SQLite. Clever, isn't it ? Except it clearly did not work like that in the OP's case. He had defined "oid" in his table, but then when he inserted into column oid, the value either went to the rowid column or somehow messed up the rowid enough to cause a constraint failure. I'm starting to think this might be a bug. I've checked it like this (Copy paste this to a file and feed to an sqlite parser): -- 2015-04-09 20:34:18.791 | [Info] Script Initialized, Started executing... -- CREATE TABLE idTest( -- Table with every row id reference id INTEGER PRIMARY KEY, col1 TEXT, oid INTEGER, rowid INTEGER, _rowid_ INTEGER ); CREATE TABLE fkTest( -- table to test the Foreign Key id INTEGER PRIMARY KEY, Data TEXT, FOREIGN KEY (id) REFERENCES idTest(id) ON UPDATE CASCADE ON DELETE CASCADE -- changes and deletes must cascade ); INSERT INTO idTest (col1, oid, rowid, _rowid_) VALUES ('TestA', 10, 100, 1000), ('TestA', 20, 200, 2000), ('TestA', 30, 300, 3000) ; INSERT INTO idTest (col1, oid) VALUES ('TestB', 'aaa'); INSERT INTO fkTest (id, Data) VALUES (last_insert_rowid(), 'FK 1'); INSERT INTO idTest (col1, oid) VALUES ('TestC', 20); INSERT INTO fkTest (id, Data) VALUES (last_insert_rowid(), 'FK 2'); -- So far all happens as expected and works perfectly... SELECT * FROM idTest; -- id | col1| oid | rowid | _rowid_ -- | --- | - | - | --- -- 1 | TestA | 10 | 100 | 1000 -- 2 | TestA | 20 | 200 | 2000 -- 3 | TestA | 30 | 300 | 3000 -- 4 | TestB | aaa | | -- 5 | TestC | 20 | | SELECT F.*, I.* FROM fkTest AS F LEFT JOIN idTest AS I ON F.id=I.id; -- id | Data | id | col1| oid | rowid | _rowid_ -- | -- | | --- | - | - | --- -- 4 | FK 1 | 4 | TestB | aaa | | -- 5 | FK 2 | 5 | TestC | 20 | | -- Here the problem happens. this statement should in SQL terms succeed and update the fkTest -- table - here I am NOT using oid as the OP did, I am using the correct reference but it still fails. -- And - it works if I remove the rowid references from the first table create. UPDATE idTest SET id = 40 WHERE id = 4; -- 2015-04-09 20:34:18.807 | [ERROR] FOREIGN KEY constraint failed -- Script Stats: Total Script Execution Time: 0d 00h 00m and 00.029s -- Total Script Query Time: 0d 00h 00m and 00.005s -- Total Database Rows Changed: 8 -- Total Virtual-Machine Steps: 396 -- Last executed Item Index:12 -- Last Script Error: Script Failed in Item 11: FOREIGN KEY constraint failed -- I won't add more test script outputs, but it fails for a DELETE request too. I think including rowid / oid / _rowid_ in a table works normally since the correct values ended up in the correct columns, but somehow including those aliases confuses the FK check mechanism.
[sqlite] Request: Metadata about C API constants and functions
On 4/9/15, Roger Binns wrote: > These are the details of what I need: > > For each function, its name, the doc page and fragment, and ideally a > few word description of it (the title on the doc page is fine). > > For each family of constants (eg result codes, extended result codes, > run time limits), the doc page, and a few word description (of the family) > > A JSON or XML file with that would make my life a lot easier. JSON/XML? Seriously? We eat our own dogfood here, thank you! When you get the data it will be in an SQLite database file! JSON/XML Good grief... As it happens, the SQLite documentation build process already parses out most of this and puts it into a database already. If you check-out the documentation sources (https://www.sqlite.org/docsrc/timeline) and successfully build the documentation, it creates a docinfo.db file that contains a lot of what you have requested above. Probably it just needs to be enhanced a little. Most of the relevant information is extracted from sqlite3.h using the https://www.sqlite.org/docsrc/artifact/5c48dd261dbe5804 script. -- D. Richard Hipp drh at sqlite.org
[sqlite] Request: Metadata about C API constants and functions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/09/2015 06:43 PM, Simon Slavin wrote: > Hmm. We could add an output mode to the shell tool which outputs > in JSON format. And by "We" I mean someone who can write good C, > which isn't me. Get yourself a time machine, go back 6 years, and enjoy the shell which has had that all along: http://rogerbinns.github.io/apsw/shell.html The shell is in Python, but you don't need to know any to use it. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlUnNdQACgkQmOOfHg372QRiPQCgp3OWyyxIhnKvUfOSYoQG8Mnz RvkAnRFOXOukgmWyB8TRnEHVtpTYNzIG =gLhP -END PGP SIGNATURE-
[sqlite] Request: Metadata about C API constants and functions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/09/2015 05:42 PM, Richard Hipp wrote: > On 4/9/15, Roger Binns wrote: >> A JSON or XML file with that would make my life a lot easier. > > JSON/XML? Seriously? We eat our own dogfood here, thank you! > When you get the data it will be in an SQLite database file! > JSON/XML Good grief... So the URL for this SQLite database is? :-) > As it happens, the SQLite documentation build process already > parses out most of this and puts it into a database already. Any chance you can put a copy of the database on the website that gets updated along with documentation updates? Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlUnNLwACgkQmOOfHg372QSr3gCfcR77qmpDg0ZOntG0BHXFtHY8 EbwAnA3DjcRx1aCvJj31DbNpyV/XVgSe =Puuy -END PGP SIGNATURE-
[sqlite] sqlite 3.8.2 foreign key issue
You are always welcome. SQLite is not strongly typed - you are very welcome to store an integer in a text field (in fact it results in only using the data associated with the smallest integer internal type that can hold the value, so a clear advantage in the embedded world). You are also welcome to put a string or blob into an integer field. You can check the value of a type using the typeof(col) SQL function in a query - different rows may indeed hold differently typed values for the same column. You can add a check constraint to a column you wish not to contain a value that isn't of an exact type. That's the very very short version, there are many caveats and things to note and the best place to do so is at: https://www.sqlite.org/datatype3.html Hope that helps! Ryan On 2015-04-09 07:04 PM, Gustav Melno wrote: > Thanks for the help. Adding a trailing underscore helped also. I'm > still wondering why insertion worked at all because defining oid as > column name with the type VARCHAR should result in an error on execution.
[sqlite] sqlite 3.8.2 foreign key issue
Thanks for the help. Adding a trailing underscore helped also. I'm still wondering why insertion worked at all because defining oid as column name with the type VARCHAR should result in an error on execution. > > > On 2015-04-09 12:00 AM, Gustav Melno wrote: >> The example below is s subset of my ical storage database which has >> problems with foreign keys. Although I tried a lot I couldn't figure >> out why the foreign key doesn't work. I took the example from the >> documentation and compared to my two tables, there is no major >> difference or I don't see the mistake. The delete statement in the >> last line doesn't work. I also tried the lates sqlite version but the >> error is the same. I always get "Error: near line 44: FOREIGN KEY >> constraint failed". Any help is appreciated. >> //... > > Funny glitch - It's because you use "oid" which is an alias for the > row_id of the table. > > To prove that point, here it is running in SQLitespeed and the results > is much the same through the Command-line utility (I added some > selects to just be sure the data actually goes in and actually gets > deleted). This first iteration fails: > > -- Processing SQL in: D:\Documents\SQLiteAutoScript.sql > -- Script Items: 10 Parameter Count: 0 > -- 2015-04-09 16:23:06.966 | [Info] Script Initialized, > Started executing... > -- > > > CREATE TABLE ical( > id INTEGER PRIMARY KEY, > oid VARCHAR, > description VARCHAR > ); > > CREATE TABLE icalentry( > id INTEGER PRIMARY KEY, > calendar INTEGER REFERENCES ical(id) ON UPDATE CASCADE ON DELETE > CASCADE, > uuid VARCHAR > ); > > INSERT INTO ical(oid,description) VALUES('123',''); > > INSERT INTO icalentry(calendar,uuid) VALUES(last_insert_rowid(),'abcd'); > > SELECT * FROM ical; > > > -- ||descript- > -- id | oid| ion > -- ||-- > -- 1 | 123| > > SELECT * FROM icalentry; > > > -- id | calendar |uuid > -- ||-- > -- 1 | 1 |abcd > > SELECT C.*, CE.* > FROM ical AS C > LEFT JOIN icalentry AS CE ON CE.calendar=C.id > ; > > > -- ||descript-| || > -- id | oid| ion | id | calendar |uuid > -- > ||-|||-- > -- 1 | 123| | 1 | 1 |abcd > > DELETE FROM ical; > > -- 2015-04-09 16:23:06.983 | [ERROR] FOREIGN KEY constraint > failed > -- Script Stats: Total Script Execution Time: 0d 00h 00m and > 00.030s > -- Total Script Query Time: 0d 00h 00m and > 00.006s > -- Total Database Rows Changed: 2 > -- Total Virtual-Machine Steps: 196 > -- Last executed Item Index:8 > -- Last Script Error: Script Failed in Item 7: > FOREIGN KEY constraint failed > -- > > > -- 2015-04-09 16:23:06.984 | [Info] Script failed - Rolling > back... > -- 2015-04-09 16:23:06.985 | [Success]Transaction Rolled back. > -- 2015-04-09 16:23:06.985 | [ERROR] Failed to complete: > Script Failed in Item 7: FOREIGN KEY constraint failed > -- --- DB-Engine Logs (Contains logged information from all DB > connections during run) -- > -- [2015-04-09 16:23:06.937] APPLICATION : Script > D:\Documents\SQLiteAutoScript.sql started at 16:23:06.937 on 09 April. > -- [2015-04-09 16:23:06.981] ERROR (284) : automatic index on > icalentry(calendar) > -- > > > > Nevermind that error 284, that's just SQLite infoming us it had to > make an index to do the queries, so you should add an index there, but > it matters none in the test. > > And this next iteration succeeds after I changed the "oid" to "xid": > > > -- Processing SQL in: D:\Documents\SQLiteAutoScript.sql > -- Script Items: 10 Parameter Count: 0 > -- 2015-04-09 16:25:11.141 | [Info] Script Initialized, > Started executing... > -- > > > CREATE TABLE ical( > id INTEGER PRIMARY KEY, > xid VARCHAR, > description VARCHAR > ); > > CREATE TABLE icalentry( > id INTEGER PRIMARY KEY, > calendar INTEGER REFERENCES ical(id) ON UPDATE CASCADE ON DELETE > CASCADE, > uuid VARCHAR > ); > > INSERT INTO ical(xid,description) VALUES('123',''); > > INSERT INTO icalentry(calendar,uuid) VALUES(last_insert_rowid(),'abcd'); > > SELECT * FROM ical; > > > -- ||descr
[sqlite] sqlite 3.8.2 foreign key issue
On 9 Apr 2015, at 6:04pm, Gustav Melno wrote: > Thanks for the help. Adding a trailing underscore helped also. I'm still > wondering why insertion worked at all because defining oid as column name > with the type VARCHAR should result in an error on execution. The three names for the integer primary key column (oid, rowid, _rowid_) work only if the table doesn't have a defined column of that name. This allows compatibility with people who didn't know that they were had special meanings for SQLite. Clever, isn't it ? Simon.
[sqlite] Request: Metadata about C API constants and functions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am the author of a Python wrapper for SQLite. As part of my documentation build process I link function names to the relevant page in the SQLite doc. I also have to wrap all constants - eg making sure that all SQLITE_LIMIT_ ones are wrapped and pointing to their doc. (I point to most C functions and wrap virtually all constants.) In order to point to the correct doc pages, and verify that I pick up all constants including new ones with each release, I have code that parses the SQLite website building up lists of functions and constants and which page is the correct one for them. That means regular expressions like r"""(sqlite3_.+?)<""" This is a little brittle as it breaks every time there are formatting changes. Would it be possible to have some data file on the website instead that I could use? Perhaps other wrapper authors have the same needs. These are the details of what I need: For each function, its name, the doc page and fragment, and ideally a few word description of it (the title on the doc page is fine). For each family of constants (eg result codes, extended result codes, run time limits), the doc page, and a few word description (of the family) A JSON or XML file with that would make my life a lot easier. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlUnF1UACgkQmOOfHg372QT6wACggLHovwyZvwyYQJgD2UPmhH+N g7YAn08hjxOP5Qol/jy0QZekxPgWMy4v =261V -END PGP SIGNATURE-
[sqlite] sqlite 3.8.2 foreign key issue
On 2015-04-09 12:00 AM, Gustav Melno wrote: > The example below is s subset of my ical storage database which has > problems with foreign keys. Although I tried a lot I couldn't figure > out why the foreign key doesn't work. I took the example from the > documentation and compared to my two tables, there is no major > difference or I don't see the mistake. The delete statement in the > last line doesn't work. I also tried the lates sqlite version but the > error is the same. I always get "Error: near line 44: FOREIGN KEY > constraint failed". Any help is appreciated. > //... Funny glitch - It's because you use "oid" which is an alias for the row_id of the table. To prove that point, here it is running in SQLitespeed and the results is much the same through the Command-line utility (I added some selects to just be sure the data actually goes in and actually gets deleted). This first iteration fails: -- Processing SQL in: D:\Documents\SQLiteAutoScript.sql -- Script Items: 10 Parameter Count: 0 -- 2015-04-09 16:23:06.966 | [Info] Script Initialized, Started executing... -- CREATE TABLE ical( id INTEGER PRIMARY KEY, oid VARCHAR, description VARCHAR ); CREATE TABLE icalentry( id INTEGER PRIMARY KEY, calendar INTEGER REFERENCES ical(id) ON UPDATE CASCADE ON DELETE CASCADE, uuid VARCHAR ); INSERT INTO ical(oid,description) VALUES('123',''); INSERT INTO icalentry(calendar,uuid) VALUES(last_insert_rowid(),'abcd'); SELECT * FROM ical; -- ||descript- -- id | oid| ion -- ||-- -- 1 | 123| SELECT * FROM icalentry; -- id | calendar |uuid -- ||-- -- 1 | 1 |abcd SELECT C.*, CE.* FROM ical AS C LEFT JOIN icalentry AS CE ON CE.calendar=C.id ; -- ||descript-| || -- id | oid| ion | id | calendar |uuid -- ||-|||-- -- 1 | 123| | 1 | 1 |abcd DELETE FROM ical; -- 2015-04-09 16:23:06.983 | [ERROR] FOREIGN KEY constraint failed -- Script Stats: Total Script Execution Time: 0d 00h 00m and 00.030s -- Total Script Query Time: 0d 00h 00m and 00.006s -- Total Database Rows Changed: 2 -- Total Virtual-Machine Steps: 196 -- Last executed Item Index:8 -- Last Script Error: Script Failed in Item 7: FOREIGN KEY constraint failed -- -- 2015-04-09 16:23:06.984 | [Info] Script failed - Rolling back... -- 2015-04-09 16:23:06.985 | [Success]Transaction Rolled back. -- 2015-04-09 16:23:06.985 | [ERROR] Failed to complete: Script Failed in Item 7: FOREIGN KEY constraint failed -- --- DB-Engine Logs (Contains logged information from all DB connections during run) -- -- [2015-04-09 16:23:06.937] APPLICATION : Script D:\Documents\SQLiteAutoScript.sql started at 16:23:06.937 on 09 April. -- [2015-04-09 16:23:06.981] ERROR (284) : automatic index on icalentry(calendar) -- Nevermind that error 284, that's just SQLite infoming us it had to make an index to do the queries, so you should add an index there, but it matters none in the test. And this next iteration succeeds after I changed the "oid" to "xid": -- Processing SQL in: D:\Documents\SQLiteAutoScript.sql -- Script Items: 10 Parameter Count: 0 -- 2015-04-09 16:25:11.141 | [Info] Script Initialized, Started executing... -- CREATE TABLE ical( id INTEGER PRIMARY KEY, xid VARCHAR, description VARCHAR ); CREATE TABLE icalentry( id INTEGER PRIMARY KEY, calendar INTEGER REFERENCES ical(id) ON UPDATE CASCADE ON DELETE CASCADE, uuid VARCHAR ); INSERT INTO ical(xid,description) VALUES('123',''); INSERT INTO icalentry(calendar,uuid) VALUES(last_insert_rowid(),'abcd'); SELECT * FROM ical; -- ||descript- -- id | xid| ion -- ||-- -- 1 | 123| SELECT * FROM icalentry; -- id | calendar |uuid -- ||-- -- 1 | 1 |abcd SELECT C.*, CE.* FROM ical AS C LEFT JOIN icalentry AS CE ON CE.calendar=C.id ; -- ||descript-| |
[sqlite] error during sqlite_bind
I tried suggested and see the correct query.. so i am not sure whats going wrong.. any insight.. query is select kindex from TBL where dn=? select kindex from TBL where dn=? debug information string a/b/c/d size 8 Error Insert : sqlite3_bind_blob, Error code : 25 snprintf(command, 512, SELECT_DN); // todo remove this printf call fprintf(stderr, "query is %s\n", command); if ( (rv = sqlite3_prepare_v2(sqlHandle->db, command, strlen(command), &newStmt, NULL) ) != SQLITE_OK ) { fprintf(stderr, "Error Insert : sqlite3_prepare_v2, Error code : %d\n", rv); return; } // todo remove the 2 lines const char *savedcopy = sqlite3_sql(newStmt); fprintf(stderr, "%s\n", savedcopy); fprintf(stderr, "debug information string %s size %lu\n", aInBuffer.ptr, sizeof(aInBuffer.ptr)); rv = sqlite3_bind_blob(newStmt, 1, aInBuffer.ptr, sizeof(aInBuffer.ptr), SQLITE_STATIC); On Tue, Apr 7, 2015 at 6:40 PM, Richard Hipp wrote: > On 4/7/15, Igor Tandetnik wrote: > > On 4/7/2015 9:11 PM, Kumar Suraj wrote: > >> You can add this to top of the code.. > >> > >> char command[512]; > >> snprintf(command, 512, SELECT_DN); > > > > I don't see anything wrong in the code you've shown. The problem must > > lie in the code you haven't. My guess would be, the query you are > > preparing is not the query you think you are preparing. Print "command" > > and strlen(command) right before sqlite3_prepare_v2 call, to > double-check. > > Or, print the result of sqlite3_sql(newStmt) right after > sqlite3_prepare_v2() returns successfully. > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Regarding SeekGe and Next opcodes in VDBE
//create table sql="create table em(name text primary key,age text,pts text);"\ "create table l(name text primary key,fame text);"; //insert values sql="insert into em values(44,20,1);"\ "insert into em values(11,20,2);"\ "insert into em values(5,21,3);"\ "insert into l values(11,11);"\ "insert into l values(12,20);"\ "insert into l values(1,20);"; //query sql = "select * from em,l where l.fame=em.age"; Below is a sample VDBE program: 0 Init 0 270 00 1 OpenRead 020 3 00 2 OpenRead 1 150 2 00 3 Rewind 0 250 00 4 Once 0 130 00 5 OpenAutoindex230 k(3,nil,nil,nil) 00 6 Rewind 1 130 00 7 Column 112 00 8 Column 103 00 9 Rowid140 00 10 MakeRecord 231 00 11 IdxInsert210 10 12 Next 170 03 13 Column 015 00 14 IsNull 5 240 00 15 SeekGE 2 245 1 00 16 IdxGT2 245 1 00 17 Column 006 00 18 Copy 570 00 19 Column 028 00 20 Column 219 00 21 Column 20 10 00 22 ResultRow650 00 23 Next 2 160 00 24 Next 040 01 25 Close000 00 26 Halt 000 00 27 Transaction 00 48833 0 01 28 TableLock020 em00 29 TableLock0 150 l 00 30 Goto 010 00 Question: whenever the condition in the where clause is false, the program jumps to the instruction pointed by p2 of SeekGe but if the condition proves to be false for the row 1 of both the tables, then the program jumps to line 24(in this case) which corresponds to outer table and takes the second row of outer table for next iteration, then when will the program fetch 1st row of table-1 and remaining rows of table-2 ???
[sqlite] NtFlushBuffersFileEx for SQLITE_SYNC_DATAONLY onWindows ?
> > Why does data-only sync exist? Is it a perf thing? Reliability? Other? Performance. On Linux/ext4 fdatasync() flushes only the file's contents instead of flushing contents + metadata as fsync() would do. For some workloads this can be a 2x improvement. Best, Thiemo On Mon, Apr 6, 2015 at 7:30 PM, Howard Kapustein < Howard.Kapustein at microsoft.com> wrote: > >data-only sync does not have a performance advantage over a full sync > Does data-only sync have a perf advantage w/synchronous=normal? > > Why does data-only sync exist? Is it a perf thing? Reliability? Other? > > > >Our experience with various Unix flavors teaches us > Windows != Unix. Although FlushFileBuffers is conceptually equivalent to > fsync we're well into the realm of details mattering. > > > >Nt*() interfaces may cause portability problems for things like WinCE > Not just CE - NtFlushBuffersFileEx was added in Win8. But SQLite can and > does use functions new in the last decade. A simple > LoadLibrary/GetProcAddress can detect availability at runtime, and there's > already plenty of #ifdef options. I already build a custom DLL from > amalgamated source (for reasons) and I'm targeting Win8+ so adding > /DSQLITE_ENABLE_WIN_DATASYNC would be trivial. Likewise enabling this > #ifdef SQLITE_OS_WINRT is safe enough since WinRT only exists on Win8+ > > > I'm trying to understand (a) if this has been considered, (b) if it's > planned, and (c) if I wanted to hack it myself, do the resident experts > have any implementation advice? Both to make it work, and if it's useful > what's likeliest to be smoothest to be accepted as a patch. > > - Howard > > -Original Message- > From: Joe Mistachkin [mailto:joe at mistachkin.com] > Sent: Friday, April 3, 2015 10:35 AM > To: 'General Discussion of SQLite Database' > Cc: Howard Kapustein > Subject: RE: [sqlite] NtFlushBuffersFileEx for SQLITE_SYNC_DATAONLY > onWindows ? > > > Howard Kapustein wrote: > > > > Has anyone considered supporting SQLITE_SYNC_DATAONLY on Windows using > > NtFlushBuffersFileEx? > > > > http://msdn.microsoft.com/en-us/library/windows/hardware/hh967720(v=vs.85).a > spx > > > > Our experience with various Unix flavors teaches us that data-only sync > does not have a performance advantage over a full sync. And the Nt*() > interfaces may cause portability problems for things like WinCE. > > -- > Joe Mistachkin > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Thiemo Nagel | Software Engineer | tnagel at google.com | +49 89 839309091
[sqlite] Regarding SeekGe and Next opcodes in VDBE
Sairam Gaddam wrote: > sql="create table em(name text primary key,age text,pts text);"\ > "create table l(name text primary key,fame text);"; > > sql = "select * from em,l where l.fame=em.age"; > >4 Once 0 130 00 >5 OpenAutoindex230 k(3,nil,nil,nil) 00 >6 Rewind 1 130 00 >7 Column 112 00 >8 Column 103 00 >9 Rowid140 00 > 10 MakeRecord 231 00 > 11 IdxInsert210 10 > 12 Next 170 03 > 13 Column 015 00 > 14 IsNull 5 240 00 > 15 SeekGE 2 245 1 00 > 16 IdxGT2 245 1 00 > 17 Column 006 00 > 18 Copy 570 00 > 19 Column 028 00 > 20 Column 219 00 > 21 Column 20 10 00 > 22 ResultRow650 00 > 23 Next 2 160 00 > 24 Next 040 01 > ... > > whenever the condition in the where clause is false, the program jumps to > the instruction pointed by p2 of SeekGe Yes. > but if the condition proves to be false for the row 1 of both the > tables, then the program jumps to line 24(in this case) which > corresponds to outer table and takes the second row of outer table > for next iteration, then when will the program fetch 1st row > of table-1 and remaining rows of table-2 ??? In the join loop, this VDBE program does not fetch any rows from the second table: explain query plan select * from em,l where l.fame=em.age; 0|0|0|SCAN TABLE em 0|1|1|SEARCH TABLE l USING AUTOMATIC COVERING INDEX (fame=?) All accesses to "l" are actually handled by the temporary index (which is created by instructions 5..12). One index search is enough to determine whether a fame value exists. Regards, Clemens
[sqlite] fts5
On 04/08/2015 04:49 AM, Scott Hess wrote: > On Thu, Sep 11, 2014 at 8:58 AM, Dan Kennedy wrote: >> Fts5 is still in the experimental stage at the moment. >> >> If anybody has any ideas for useful features, or knows of problems with FTS4 >> that could be fixed in FTS5, don't keep them to yourself! > Apologies for not noticing this thread earlier! > > After fts2 was released, someone engaged me on a discussion about > whether I had considered an alternate storage strategy. The current > system of {term,doclist} where doclist is something like > [{docid,[pos]}] means that the index b-tree is very lumpy because > doclists are (extremely) variably-sized. The suggestion was to store > things as an ordered set of {term,doc,pos} tuples, then use some sort > of delta encoding between them. This would quite naturally balance > the interior of the index versus the leaves, and would also work well > with incremental merging since you only needed to worry about the head > block for each segment being scanned. I believe the current fts5 code > gets similar results by keeping an index for large doclists to allow > quickly scanning to the right point, so this might not add much. > > Something that bugged me a lot was that I had used deletion markers to > cancel out hits, but did not provide a way for deletion markers to > cancel out. The main problem with this was that a large delete would > stay in the system until it reached the final segment, even if it had > already overtaken all of the original inserts. I wished that I had > either maintained a separate structure tracking _document_ deletion > (which would make merges somewhat more complicated because they > wouldn't be term-centric), or code updates as "delete+insert". In the > latter case deletes could drop out at the point where they reached the > original insert. Thanks for this. The "delete+insert" idea sounds like quite an interesting one. So instead of just "delete" and "insert" keys, the merge tree now also contains "delete+insert" keys (call them "update" keys). Then maintain the tree so that (a) for each "insert", the next youngest duplicate key must either not exist or be a "delete", (b) for each "update", the next youngest duplicate key must exist and must be an "insert" or "update", and (c) for each "delete", the next youngest duplicate key must exist and must be an "insert" or "update". And as a result, when a "delete" catches up with an "insert" while merging they can both be discarded. Instead of the current situation, where we retain the "delete" unless the output segment is the oldest in the database. Cool. I guess they don't generally do this in merge-trees because the cost of figuring out whether to use "update" or "insert" keys when writing a new segments is prohibitively high. But FTS doesn't have that problem, as it never does a true "blind write". When it clobbers a key it always knows it at time of writing. Dan. > > I seem to recall being upset by the amount of compression gzip could > manage against index blocks, even though they mostly aren't very > large. I think things got around 1/4 or 1/3 smaller. To me that > implied that there were probably some gains to be had in encoding. > [This is distinct from compression of content data, which fts3/4 > already support.] > > I'm 100% convinced that merging could be improved :-). Clearly there > is a lot of benefit to merging together the low-order segments, but I > never figured out a good way to model whether merging the larger > segments actually improved anything, since at some point you no longer > can really enforce locality anyhow. But I'm guessing that your > experiments with the sqlite4 key/value store probably involve lots of > exploration along these lines. > > -scott > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite 3.8.2 foreign key issue
The example below is s subset of my ical storage database which has problems with foreign keys. Although I tried a lot I couldn't figure out why the foreign key doesn't work. I took the example from the documentation and compared to my two tables, there is no major difference or I don't see the mistake. The delete statement in the last line doesn't work. I also tried the lates sqlite version but the error is the same. I always get "Error: near line 44: FOREIGN KEY constraint failed". Any help is appreciated. PRAGMA foreign_keys=1; BEGIN TRANSACTION; CREATE TABLE artist( artistidINTEGER PRIMARY KEY, artistname VARCHAR ); CREATE TABLE track( trackid INTEGER PRIMARY KEY, trackname VARCHAR, trackartist INTEGER REFERENCES artist(artistid) ON UPDATE CASCADE ON DELETE CASCADE ); END TRANSACTION; INSERT INTO artist(artistname) VALUES('Testartist'); INSERT INTO track(trackname,trackartist) VALUES('Testsong',last_insert_rowid()); DELETE FROM artist; BEGIN TRANSACTION; CREATE TABLE ical( id INTEGER PRIMARY KEY, oid VARCHAR, description VARCHAR ); CREATE TABLE icalentry( id INTEGER PRIMARY KEY, calendar INTEGER REFERENCES ical(id) ON UPDATE CASCADE ON DELETE CASCADE, uuid VARCHAR ); END TRANSACTION; INSERT INTO ical(oid,description) VALUES('123',''); INSERT INTO icalentry(calendar,uuid) VALUES(last_insert_rowid(),'abcd'); DELETE FROM ical;