[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;
>
>
> -- |
[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),
, 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;
[sqlite] Prevent database file from being overwritten by other processes
On 04/08/2015 10:52 PM, R.Smith wrote: > > > On 2015-04-08 05:38 PM, Dan Kennedy wrote: >> On 04/08/2015 09:51 PM, R.Smith wrote: >>> >>> >>> On 2015-04-08 04:18 PM, Fabian Pr?bstl wrote: Hi there! Currently, we are using SQLite as our application file format for a Windows 7/C#/System.Data.SQLite based desktop application. We only allow one instance to open the file by running "set locking_mode=EXCLUSIVE;BEGIN EXCLUSIVE;COMMIT" when connecting to the database. >>> >>> BEGIN EXCLUSIVE - Locks the database from other SQLite3 database >>> connections for the time being. >>> COMMIT; - Unlocks it again - so calling all this in one go is >>> pointless. >> >> Almost always correct. But the "PRAGMA locking_mode=EXCLUSIVE" thing >> changes the behaviour: >> >> https://www.sqlite.org/pragma.html#pragma_locking_mode > > But you need an actual SELECT to get a shared lock and an actual write > operation to lock it exclusively, just starting the transaction and > ending it does nothing to that effect? Or is my understanding wrong? That's the usual case. But "BEGIN EXCLUSIVE" actually does take an exclusive lock: https://www.sqlite.org/lang_transaction.html
