[sqlite] Under what circumstances can a table be locked when the database is first opened?
Locking mode: Is this documented anywhere? As I thought I said, it's a standalone program; run it to normal termination; then run it again. Single connection, no flags enabled at open (just the default). Yes, I've been using Process Explorer for at least 10 years, since it was at sysinternals. First place I looked and no, the database file is not locked. If it was, I wouldn't have been able to delete it. Regards David M Bennett FACS Andl - A New Data Language - andl.org -Original Message- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Barry Smith Sent: Saturday, 23 May 2015 5:11 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Under what circumstances can a table be locked when the database is first opened? Hi, Unless you are using shared cache, SQLite does not lock on a per table level - only it locks the entire database. Under what circumstances are you trying to access the database both times? Are these multiple connections within the same process or are you shutting down the process and then restarting? Do you have shared cached enabled? Process Explorer is a windows tool that will tell you which process has open handles on a particular file. I found it a right pita to use though. Cheers, Barry > On 23 May 2015, at 1:42 pm, "Keith Medcalf" wrote: > > > 1) Something else has the database open and locked. > 2) You are using Shared Cache > 3) Something forgot to finalize a select > 4) The database is stored on a non-locally-attached filesystem > 5) An issue in the version of SQLite you are using (and you did not > say which version you are using) > 6) A buggy filesystem driver (you did not say which one you are > using) > 7) Badly designed antivirus software > 8) Badly designed file syncronization software (for example, storing the database in a directory that is being monitored and synced by badly designed software (dropbox for example)). > >> -Original Message- >> From: sqlite-users-bounces at mailinglists.sqlite.org >> [mailto:sqlite-users- bounces at mailinglists.sqlite.org] On Behalf Of >> david at andl.org >> Sent: Friday, 22 May, 2015 21:13 >> To: 'General Discussion of SQLite Database' >> Subject: [sqlite] Under what circumstances can a table be locked when >> the database is first opened? >> >> Question: Under what circumstances can a table be locked when the >> database is first opened? >> >> My program does: >> >> DROP TABLE IF EXISTS >> CREATE TABLE >> INSERT INTO (multiple times) >> SELECT * (for each row) >> >> Run it once and it works perfectly. Run it twice and the DROP TABLE >> triggers the error: >> >> SQLITE_LOCKED, database table is locked >> >> Delete the database and run it again and it works. Just once. >> >> Nothing in the documentation tells me how a table can be locked when >> the database is first opened. I'm using the raw C interface on >> Windows, so what can I be doing wrong? >> >> The code is actually written in C#, but uses Interop to call the C >> API directly. The database open code looks like this. No open flags are used. >> >> LastResult = (Result)sqlite3_open(path, out _dbhandle); >> >> Regards >> David M Bennett FACS >> >> Andl - A New Data Language - andl.org >> >> >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Under what circumstances can a table be locked when the database is first opened?
Thanks for the summary. Could be a useful addition to the docs. 1) single user. 2) I'm not 'using' anything. Just default open. 3) --> this could be it. How does this work exactly, and how do you avoid it/correct it (after the event)? 4) N/A 5) Latest download. 6) Windows 8.1 NTFS. 7) None. 8) N/A. Regards David M Bennett FACS Andl - A New Data Language - andl.org -Original Message- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Keith Medcalf Sent: Saturday, 23 May 2015 1:42 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Under what circumstances can a table be locked when the database is first opened? 1) Something else has the database open and locked. 2) You are using Shared Cache 3) Something forgot to finalize a select 4) The database is stored on a non-locally-attached filesystem 5) An issue in the version of SQLite you are using (and you did not say which version you are using) 6) A buggy filesystem driver (you did not say which one you are using) 7) Badly designed antivirus software 8) Badly designed file syncronization software (for example, storing the database in a directory that is being monitored and synced by badly designed software (dropbox for example)). > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org > [mailto:sqlite-users- bounces at mailinglists.sqlite.org] On Behalf Of > david at andl.org > Sent: Friday, 22 May, 2015 21:13 > To: 'General Discussion of SQLite Database' > Subject: [sqlite] Under what circumstances can a table be locked when > the database is first opened? > > Question: Under what circumstances can a table be locked when the > database is first opened? > > My program does: > > DROP TABLE IF EXISTS > CREATE TABLE > INSERT INTO (multiple times) > SELECT * (for each row) > > Run it once and it works perfectly. Run it twice and the DROP TABLE > triggers the error: > > SQLITE_LOCKED, database table is locked > > Delete the database and run it again and it works. Just once. > > Nothing in the documentation tells me how a table can be locked when > the database is first opened. I'm using the raw C interface on > Windows, so what can I be doing wrong? > > The code is actually written in C#, but uses Interop to call the C API > directly. The database open code looks like this. No open flags are used. > > LastResult = (Result)sqlite3_open(path, out _dbhandle); > > Regards > David M Bennett FACS > > Andl - A New Data Language - andl.org > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sample SQL code thats beyond me :(
On 23-5-2015 18:41, Keith Medcalf wrote: > > You also lastly mention that the UUID fields are also used in the selection, > so the problem statement is really: > > Return the projection of Users and Perimeter_Notifications using the common > email field as the equijoin key, but return only the results where there is > not a Devices record with the email and uuid matching the corresponding > fields in Perimeter_Notifications which has Holiday_Mode = 1: > > SELECT * >FROM Users, Perimeter_Notifications > WHERE Users.email = Perimeter_Notifications.email > AND NOT EXISTS (SELECT 1 > FROM Devices > WHERE Devicess.email = Perimeter_Notifications.email >AND Devices.UUID = Perimeter_Notifications.UUID >AND Holiday_Mode = 1); > I would do: SELECT * FROM Users LEFT JOIN Devices ON Users.email = Devices.Email LEFT JOIN Perimeter_Notifications ON Users.email = Perimeter_Notifications.email WHERE (Holiday_Mode = 1 OR Holiday_Mode IS NULL)
[sqlite] Sample SQL code thats beyond me :(
Rich, I have lived and worked in New York and DC but not for too many years. I have fond memories of the eastern seaboard. I think you are correct with first, second and third passes on design. We?re into our second pass now. As we work through the issues we may redesign bits of it. However the chain of data flow is now getting complex so ?small' changes can take a long time to move through (and yes we have abstracted our designs out). All the very best. Rob. > On 23 May 2015, at 18:56, Rich Shepard wrote: > > On Sat, 23 May 2015, Rob Willett wrote: > >> Thanks again and as its Saturday have a drink. If any of you are near York >> in England I?ll happily buy you a pint. > > Rob, > > I'm in the upper left corner of the US so I'll have to pass on your kind > offer. > > Germane to your fundamental concern, over the years I've found that my > first pass at a database schema is usually sub-optimal. The first design is > based on initial assumptions, and further deep thinking can bring up issues > not recognized before. > > I'm sure you will evolve a schema that works well for your needs and > avoids hidden problems. > > Rich > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sample SQL code thats beyond me :(
Rich, Keith, Luuk,
Thank you all for taking the time to write such cogent and helpful replies.
I?ve tried out the ideas and they all work fine. We?ve also been looking at the
design of our database and our logic to see if thats right. As I mentioned
previously, I have feeling that this issue is possibly due to bad DB design on
our part so we need to look at that carefully. I?m rather nervous of
propagating bad design any further so we?ll check carefully.
Either way we have a working solution, so I feel optimistic.
Thanks again and as its Saturday have a drink. If any of you are near York in
England I?ll happily buy you a pint.
Best wishes,
Rob.
> On 23 May 2015, at 17:41, Keith Medcalf wrote:
>
>
> You also lastly mention that the UUID fields are also used in the selection,
> so the problem statement is really:
>
> Return the projection of Users and Perimeter_Notifications using the common
> email field as the equijoin key, but return only the results where there is
> not a Devices record with the email and uuid matching the corresponding
> fields in Perimeter_Notifications which has Holiday_Mode = 1:
>
> SELECT *
> FROM Users, Perimeter_Notifications
> WHERE Users.email = Perimeter_Notifications.email
> AND NOT EXISTS (SELECT 1
> FROM Devices
>WHERE Devicess.email = Perimeter_Notifications.email
> AND Devices.UUID = Perimeter_Notifications.UUID
> AND Holiday_Mode = 1);
>
> Your index on the Devices table will need to include the UUID as in Devices
> (email, uuid, holiday_mode ...) (the order within the first three columns of
> the index are irrelevant for this query's performance.
>
>> -Original Message-
>> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
>> bounces at mailinglists.sqlite.org] On Behalf Of Keith Medcalf
>> Sent: Saturday, 23 May, 2015 10:26
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] Sample SQL code thats beyond me :(
>>
>> To re-phrase your problem statement:
>>
>> Join the table Users and Perimeter_Notifications using the common email
>> field and return the results as long as there does not exist a Devices
>> record where Holiday_Mode is 1 for that Users email.
>>
>> Which translates directly to:
>>
>> SELECT *
>> FROM Users, Perimeter_Notifications
>> WHERE Users.email = Perimeter_Notifications.email
>> AND NOT EXISTS (SELECT 1
>> FROM Devices
>>WHERE Devices.email = Users.email
>> AND Holiday_Mode = 1);
>>
>> You should have an index on Devices (email, Holiday_Mode ...), and of
>> course you will need an index on Perimeter_Notifications (email ...). You
>> could also phrase it as an outer join, but that will be far less efficient
>> that the correlated subquery. Some people are in love with outer joins,
>> however. You would only need to use an outer join if you also needed some
>> data from the Devices table to be returned.
>>
>> It also has the advantage that when you read it, it translates directly
>> back into the original (re-phrased) problem statement, so it is self-
>> documenting.
>>
>>
>>> -Original Message-
>>> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
>>> bounces at mailinglists.sqlite.org] On Behalf Of Rob Willett
>>> Sent: Saturday, 23 May, 2015 09:41
>>> To: General Discussion of SQLite Database
>>> Subject: [sqlite] Sample SQL code thats beyond me :(
>>>
>>> Hi,
>>>
>>> I?m trying to do some analysis across a couple of tables and the SQL is
>>> beyond my meagre skills. I?m struggling to even describe the problem to
>> be
>>> honest.
>>>
>>> The high level description is that I have three tables, Users, Devices
>> and
>>> Perimeter_Notifications. The high level description is that I want to
>>> extract a list of users from a database to send information to if they
>> are
>>> not on holiday. However I don?t necessarily have the holiday_mode set by
>>> the user and so our assumption is that unless the holiday mode is set to
>> 1
>>> (they are on holiday) its is assumed to be 0. Its the assumption thats
>>> causing the problem. If there is no entries in Perimeter_Notifications
>>> thats also fine, no rows get returned.
>>>
>>> CREATE TABLE "Users" (
>>>"email" TEXT NOT NULL,
>>>"password" TEXT NOT NULL,
>>>"salt" TEXT NOT NULL,
>>>"creation_timestamp" TEXT NOT NULL DEFAULT
>>> (datetime('now','localtime')),
>>> PRIMARY KEY("email")
>>> );
>>>
>>> CREATE TABLE "Devices" (
>>>"Email" TEXT NOT NULL,
>>>"UUID" text NOT NULL,
>>>"Holiday_Mode" integer NOT NULL
>>> );
>>>
>>> CREATE TABLE "Perimeter_Notifications" (
>>> "Email" text NOT NULL ,
>>>"UUID" text NOT NULL,
>>>"route_id" INTEGER NOT NULL,
>>>"day" integer NOT NULL,
>>>"hour" integer NOT NULL
>>> );
>>>
>>> (Please note the UUID
[sqlite] Sample SQL code thats beyond me :(
Rich, Thanks. Simply writing the initial e-mail helped clarify things for me. We?re trying to work out the logic of setting holiday_mode to an explicit 0 rather than an assumed 0. Its not quite as simple as setting it in a table as its linked back to a mobile app and the synchronisation logic is a little convoluted. If we can force holiday_mode to be set to either 0 or 1 then the problem goes away, which comes down to getting the design right. I think that this ?issue? we have is indicative of a poor DB design and wrong assumptions (bad pun) and we should fix that. Rob. > On 23 May 2015, at 17:06, Rich Shepard wrote: > > On Sat, 23 May 2015, Rob Willett wrote: > >> What I want to do is join the table Users and Perimeter Notifications >> together but only if the value of Devices.Holiday_Mode is either non >> existent or if Devices.Holiday_Mode does exist and its 0. If >> Devices.Holiday_Mode is 1 it means the user is on holiday and don?t send >> them anything. > > Rob, > > First, you can set holiday_mode to 0 by default rather than leaving it > NULL (unknown). As you wrote, unless the user explicitly sets the mode to 1 > the assumption is that its value is 0. After all, it's gotta' be one or the > other, right? > > Second, select * from Devices where holiday_mode == 0. Use that as a > sub-query and join users to the results. Now you have a list of user email > addresses for only those with holiday_mode of zero. > > HTH, > > Rich > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sample SQL code thats beyond me :(
Rich, Thanks. Simply writing the e-mail helped clarify things for me. We?re trying to work out the logic of setting holiday_mode to an explicit 0 rather than an assumed 0. Its not quite as simple as setting it in a table as its linked back to a mobile app and the synchronisation logic is a little convoluted. If we can force holiday_mode to be set to either 0 or 1 then the problem goes away, which comes down to getting the design right. I think that this ?issue? we have is indicative of a poor DB design and wrong assumptions (bad pun) and we should fix that. Rob. > On 23 May 2015, at 17:06, Rich Shepard wrote: > > On Sat, 23 May 2015, Rob Willett wrote: > >> What I want to do is join the table Users and Perimeter Notifications >> together but only if the value of Devices.Holiday_Mode is either non >> existent or if Devices.Holiday_Mode does exist and its 0. If >> Devices.Holiday_Mode is 1 it means the user is on holiday and don?t send >> them anything. > > Rob, > > First, you can set holiday_mode to 0 by default rather than leaving it > NULL (unknown). As you wrote, unless the user explicitly sets the mode to 1 > the assumption is that its value is 0. After all, it's gotta' be one or the > other, right? > > Second, select * from Devices where holiday_mode == 0. Use that as a > sub-query and join users to the results. Now you have a list of user email > addresses for only those with holiday_mode of zero. > > HTH, > > Rich > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Under what circumstances can a table be locked when the database is first opened?
Hi, Unless you are using shared cache, SQLite does not lock on a per table level - only it locks the entire database. Under what circumstances are you trying to access the database both times? Are these multiple connections within the same process or are you shutting down the process and then restarting? Do you have shared cached enabled? Process Explorer is a windows tool that will tell you which process has open handles on a particular file. I found it a right pita to use though. Cheers, Barry > On 23 May 2015, at 1:42 pm, "Keith Medcalf" wrote: > > > 1) Something else has the database open and locked. > 2) You are using Shared Cache > 3) Something forgot to finalize a select > 4) The database is stored on a non-locally-attached filesystem > 5) An issue in the version of SQLite you are using (and you did not say > which version you are using) > 6) A buggy filesystem driver (you did not say which one you are using) > 7) Badly designed antivirus software > 8) Badly designed file syncronization software (for example, storing the > database in a directory that is being monitored and synced by badly designed > software (dropbox for example)). > >> -Original Message- >> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- >> bounces at mailinglists.sqlite.org] On Behalf Of david at andl.org >> Sent: Friday, 22 May, 2015 21:13 >> To: 'General Discussion of SQLite Database' >> Subject: [sqlite] Under what circumstances can a table be locked when the >> database is first opened? >> >> Question: Under what circumstances can a table be locked when the database >> is first opened? >> >> My program does: >> >> DROP TABLE IF EXISTS >> CREATE TABLE >> INSERT INTO (multiple times) >> SELECT * (for each row) >> >> Run it once and it works perfectly. Run it twice and the DROP TABLE >> triggers >> the error: >> >> SQLITE_LOCKED, database table is locked >> >> Delete the database and run it again and it works. Just once. >> >> Nothing in the documentation tells me how a table can be locked when the >> database is first opened. I'm using the raw C interface on Windows, so >> what >> can I be doing wrong? >> >> The code is actually written in C#, but uses Interop to call the C API >> directly. The database open code looks like this. No open flags are used. >> >> LastResult = (Result)sqlite3_open(path, out _dbhandle); >> >> Regards >> David M Bennett FACS >> >> Andl - A New Data Language - andl.org >> >> >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sample SQL code thats beyond me :(
Hi,
I?m trying to do some analysis across a couple of tables and the SQL is beyond
my meagre skills. I?m struggling to even describe the problem to be honest.
The high level description is that I have three tables, Users, Devices and
Perimeter_Notifications. The high level description is that I want to extract a
list of users from a database to send information to if they are not on
holiday. However I don?t necessarily have the holiday_mode set by the user and
so our assumption is that unless the holiday mode is set to 1 (they are on
holiday) its is assumed to be 0. Its the assumption thats causing the problem.
If there is no entries in Perimeter_Notifications thats also fine, no rows get
returned.
CREATE TABLE "Users" (
"email" TEXT NOT NULL,
"password" TEXT NOT NULL,
"salt" TEXT NOT NULL,
"creation_timestamp" TEXT NOT NULL DEFAULT
(datetime('now','localtime')),
PRIMARY KEY("email")
);
CREATE TABLE "Devices" (
"Email" TEXT NOT NULL,
"UUID" text NOT NULL,
"Holiday_Mode" integer NOT NULL
);
CREATE TABLE "Perimeter_Notifications" (
"Email" text NOT NULL ,
"UUID" text NOT NULL,
"route_id" INTEGER NOT NULL,
"day" integer NOT NULL,
"hour" integer NOT NULL
);
(Please note the UUID is nothing to do with the UUID discussion a few days ago,
I?m not brave enough to open up that little can of worms, we just happen to
have chosen that column name a few months ago for mobile devices. Also in case
anybody asks, we're not storing passwords in plain text either).
I?m not sure if my database design is wrong or I simply cannot work out the SQL
to make it work. It might actually be both :)
What I want to do is join the table Users and Perimeter Notifications together
but only if the value of Devices.Holiday_Mode is either non existent or if
Devices.Holiday_Mode does exist and its 0. If Devices.Holiday_Mode is 1 it
means the user is on holiday and don?t send them anything.
I can work out the logic if Devices.Holiday_Mode actually exists and is either
1 or 0. Thats pretty basic SQL.However if there is no row in Devices with that
Email and UUID then thats the equivalent as Devices.Holiday_Mode being 0. I?ve
looked at IS NULL or NOT EXISTS but I?m struggling to get my head around it
all. I can do all of this in a higher level language (not sure if Perl is
higher level than SQL) but I should be able to do this in SQL itself.
Any advice or guidance welcomed please.
Thanks for reading,
Rob.
[sqlite] How mature/stable is SQLite 4 now? ETA?
SQLite4 looks neat! Last code commit was in September, is this because it's so stable or because other priorities took over? (https://sqlite.org/src4/tree?ci=trunk) Thanks! Mikael
[sqlite] Contentless FTS4 Tables
On 05/23/2015 04:33 AM, ShadowMarta at yahoo.de wrote: > Hi! > > I have made a Contentless FTS4 Table like: > > "CREATE VIRTUAL TABLE if not exists OCR USING fts4(content="", `FullOCR` > varchar;" > > And managed to insert some data into it. > > I have 2 questions: > > 1.) How to get the proper COUNT on the table ? > The only query seems to work is: > "SELECT COUNT(*) FROM OCR_docsize;" is this the right way to do it ? > > 2.) How to perform a "JOIN" operation with it? > "SELECT docid FROM OCR WHERE FullOCR MATCH 'framework';" result is "2". > > "SELECT * FROM ART INNER JOIN OCR ON ART.ID = (SELECT docid FROM OCR > WHERE FullOCR MATCH 'framework') ORDER BY ID;" > Gives "SQL logic error or missing database:" > > "SELECT * FROM ART INNER JOIN OCR_docsize ON ART.ID = (SELECT docid > FROM OCR WHERE FullOCR MATCH 'framework') ORDER BY ID;" > Gives me 9 results back - should be only 1 - > > "SELECT * FROM ART NATURAL JOIN (SELECT docid FROM OCR WHERE FullOCR > MATCH 'framework') ORDER BY ID;" > Gives me 9 results back - should be only 1 - > > What I am missing here? Are you able to make the database file available for download somewhere? Thanks, Dan.
[sqlite] How mature/stable is SQLite 4 now? ETA?
SQLite4 is a dev "toy". It isn't going to be released any time soon. On Sat, May 23, 2015 at 6:09 AM, Mikael wrote: > SQLite4 looks neat! > > Last code commit was in September, is this because it's so stable or > because other priorities took over? > > (https://sqlite.org/src4/tree?ci=trunk) > > Thanks! > Mikael > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] NOT EXISTS and LEFT JOIN Performance problem
ShadowMarta at yahoo.de wrote: > CREATE VIRTUAL TABLE `OCR` using fts4 ( > `ID`integer primary key NOT NULL, This is not how FTS tables work. SQLite ignores pretty much anything except the column names; it does not matter whether you write PRIMARY KEY or NO KEY PLEASE. All columns get full-text indexed. All FTS tables have the usual internal rowid as primary key; it's also available under the name "docid". You should drop the ID column, and in your queries use the docid instead. Regards, Clemens
[sqlite] Under what circumstances can a table be locked when the database is first opened?
Question: Under what circumstances can a table be locked when the database is first opened? My program does: DROP TABLE IF EXISTS CREATE TABLE INSERT INTO (multiple times) SELECT * (for each row) Run it once and it works perfectly. Run it twice and the DROP TABLE triggers the error: SQLITE_LOCKED, database table is locked Delete the database and run it again and it works. Just once. Nothing in the documentation tells me how a table can be locked when the database is first opened. I'm using the raw C interface on Windows, so what can I be doing wrong? The code is actually written in C#, but uses Interop to call the C API directly. The database open code looks like this. No open flags are used. LastResult = (Result)sqlite3_open(path, out _dbhandle); Regards David M Bennett FACS Andl - A New Data Language - andl.org
[sqlite] How mature/stable is SQLite 4 now? ETA?
More like It'll be out in time for Christmas, where the specific year isn't mentioned. -- Darren Duncan On 2015-05-23 11:09 AM, Mikael wrote: > This sounds like it means we'll have it 2.5-5 years then.. so 2018 maybe, > > Sounds about correct? :) > > > 2015-05-23 23:06 GMT+05:30 Stephen Chrzanowski : > >> SQLite4 is a dev "toy". It isn't going to be released any time soon. >> >> On Sat, May 23, 2015 at 6:09 AM, Mikael wrote: >> >>> SQLite4 looks neat! >>> >>> Last code commit was in September, is this because it's so stable or >>> because other priorities took over? >>> >>> (https://sqlite.org/src4/tree?ci=trunk) >>> >>> Thanks! >>> Mikael
[sqlite] Contentless FTS4 Tables
Hello Dan, sorry it is just like: CREATE VIRTUAL TABLE if not exists OCR USING fts4(content="", `FullOCR`); & CREATE TABLE `ART` ( `ID`integer NOT NULL, `Kundennummer`integer, `Rechnungsnummer`varchar, `Rechnungsdatum`datetime, `PDF`varchar, PRIMARY KEY(ID) ); And the "FullOCR" content is: Row1: "WORD01" "WORD02" . . Row2: "WORD01" "WORD02" "framework" . . I have inserted only 9 rows into "FullOCR" and maybe 4 into "ART". Have one matching ID = docid = 2 to test "JOIN". But you can use just any 2 tables and try to do a "JOIN" or "COUNT" operation. "SELECT COUNT(*) FROM OCR;" produces error so my workaround war using "OCR_docsize" in place of "OCR" "SELECT docid FROM OCR WHERE FullOCR MATCH 'framework';" gives correctly one row with the docid = 2. but when I try to use this in a "JOIN" statement produces error and no workaround with "OCR_docsize" gives correct result. BR, Marta -Original Message- From: Dan Kennedy Sent: Saturday, May 23, 2015 9:34 AM To: sqlite-users at mailinglists.sqlite.org Subject: Re: [sqlite] Contentless FTS4 Tables On 05/23/2015 04:33 AM, ShadowMarta at yahoo.de wrote: > Hi! > > I have made a Contentless FTS4 Table like: > > "CREATE VIRTUAL TABLE if not exists OCR USING fts4(content="", `FullOCR` > varchar;" > > And managed to insert some data into it. > > I have 2 questions: > > 1.) How to get the proper COUNT on the table ? > The only query seems to work is: > "SELECT COUNT(*) FROM OCR_docsize;" is this the right way to do it > ? > > 2.) How to perform a "JOIN" operation with it? > "SELECT docid FROM OCR WHERE FullOCR MATCH 'framework';" result is > "2". > > "SELECT * FROM ART INNER JOIN OCR ON ART.ID = (SELECT docid FROM OCR > WHERE FullOCR MATCH 'framework') ORDER BY ID;" > Gives "SQL logic error or missing database:" > > "SELECT * FROM ART INNER JOIN OCR_docsize ON ART.ID = (SELECT docid > FROM OCR WHERE FullOCR MATCH 'framework') ORDER BY ID;" > Gives me 9 results back - should be only 1 - > > "SELECT * FROM ART NATURAL JOIN (SELECT docid FROM OCR WHERE FullOCR > MATCH 'framework') ORDER BY ID;" > Gives me 9 results back - should be only 1 - > > What I am missing here? Are you able to make the database file available for download somewhere? Thanks, Dan. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sample SQL code thats beyond me :(
On Sat, 23 May 2015, Rob Willett wrote: > Thanks again and as its Saturday have a drink. If any of you are near York > in England I?ll happily buy you a pint. Rob, I'm in the upper left corner of the US so I'll have to pass on your kind offer. Germane to your fundamental concern, over the years I've found that my first pass at a database schema is usually sub-optimal. The first design is based on initial assumptions, and further deep thinking can bring up issues not recognized before. I'm sure you will evolve a schema that works well for your needs and avoids hidden problems. Rich
[sqlite] Sample SQL code thats beyond me :(
You also lastly mention that the UUID fields are also used in the selection, so
the problem statement is really:
Return the projection of Users and Perimeter_Notifications using the common
email field as the equijoin key, but return only the results where there is not
a Devices record with the email and uuid matching the corresponding fields in
Perimeter_Notifications which has Holiday_Mode = 1:
SELECT *
FROM Users, Perimeter_Notifications
WHERE Users.email = Perimeter_Notifications.email
AND NOT EXISTS (SELECT 1
FROM Devices
WHERE Devicess.email = Perimeter_Notifications.email
AND Devices.UUID = Perimeter_Notifications.UUID
AND Holiday_Mode = 1);
Your index on the Devices table will need to include the UUID as in Devices
(email, uuid, holiday_mode ...) (the order within the first three columns of
the index are irrelevant for this query's performance.
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Keith Medcalf
> Sent: Saturday, 23 May, 2015 10:26
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Sample SQL code thats beyond me :(
>
> To re-phrase your problem statement:
>
> Join the table Users and Perimeter_Notifications using the common email
> field and return the results as long as there does not exist a Devices
> record where Holiday_Mode is 1 for that Users email.
>
> Which translates directly to:
>
> SELECT *
> FROM Users, Perimeter_Notifications
> WHERE Users.email = Perimeter_Notifications.email
>AND NOT EXISTS (SELECT 1
> FROM Devices
> WHERE Devices.email = Users.email
> AND Holiday_Mode = 1);
>
> You should have an index on Devices (email, Holiday_Mode ...), and of
> course you will need an index on Perimeter_Notifications (email ...). You
> could also phrase it as an outer join, but that will be far less efficient
> that the correlated subquery. Some people are in love with outer joins,
> however. You would only need to use an outer join if you also needed some
> data from the Devices table to be returned.
>
> It also has the advantage that when you read it, it translates directly
> back into the original (re-phrased) problem statement, so it is self-
> documenting.
>
>
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> > bounces at mailinglists.sqlite.org] On Behalf Of Rob Willett
> > Sent: Saturday, 23 May, 2015 09:41
> > To: General Discussion of SQLite Database
> > Subject: [sqlite] Sample SQL code thats beyond me :(
> >
> > Hi,
> >
> > I?m trying to do some analysis across a couple of tables and the SQL is
> > beyond my meagre skills. I?m struggling to even describe the problem to
> be
> > honest.
> >
> > The high level description is that I have three tables, Users, Devices
> and
> > Perimeter_Notifications. The high level description is that I want to
> > extract a list of users from a database to send information to if they
> are
> > not on holiday. However I don?t necessarily have the holiday_mode set by
> > the user and so our assumption is that unless the holiday mode is set to
> 1
> > (they are on holiday) its is assumed to be 0. Its the assumption thats
> > causing the problem. If there is no entries in Perimeter_Notifications
> > thats also fine, no rows get returned.
> >
> > CREATE TABLE "Users" (
> > "email" TEXT NOT NULL,
> > "password" TEXT NOT NULL,
> > "salt" TEXT NOT NULL,
> > "creation_timestamp" TEXT NOT NULL DEFAULT
> > (datetime('now','localtime')),
> >PRIMARY KEY("email")
> > );
> >
> > CREATE TABLE "Devices" (
> > "Email" TEXT NOT NULL,
> > "UUID" text NOT NULL,
> > "Holiday_Mode" integer NOT NULL
> > );
> >
> > CREATE TABLE "Perimeter_Notifications" (
> >"Email" text NOT NULL ,
> > "UUID" text NOT NULL,
> > "route_id" INTEGER NOT NULL,
> > "day" integer NOT NULL,
> > "hour" integer NOT NULL
> > );
> >
> > (Please note the UUID is nothing to do with the UUID discussion a few
> days
> > ago, I?m not brave enough to open up that little can of worms, we just
> > happen to have chosen that column name a few months ago for mobile
> > devices. Also in case anybody asks, we're not storing passwords in plain
> > text either).
> >
> > I?m not sure if my database design is wrong or I simply cannot work out
> > the SQL to make it work. It might actually be both :)
> >
> > What I want to do is join the table Users and Perimeter Notifications
> > together but only if the value of Devices.Holiday_Mode is either non
> > existent or if Devices.Holiday_Mode does exist and its 0. If
> > Devices.Holiday_Mode is 1 it means the user is on holiday and don?t send
> > them anything.
> >
> > I can work out the logic if
[sqlite] Sample SQL code thats beyond me :(
To re-phrase your problem statement:
Join the table Users and Perimeter_Notifications using the common email field
and return the results as long as there does not exist a Devices record where
Holiday_Mode is 1 for that Users email.
Which translates directly to:
SELECT *
FROM Users, Perimeter_Notifications
WHERE Users.email = Perimeter_Notifications.email
AND NOT EXISTS (SELECT 1
FROM Devices
WHERE Devices.email = Users.email
AND Holiday_Mode = 1);
You should have an index on Devices (email, Holiday_Mode ...), and of course
you will need an index on Perimeter_Notifications (email ...). You could also
phrase it as an outer join, but that will be far less efficient that the
correlated subquery. Some people are in love with outer joins, however. You
would only need to use an outer join if you also needed some data from the
Devices table to be returned.
It also has the advantage that when you read it, it translates directly back
into the original (re-phrased) problem statement, so it is self-documenting.
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Rob Willett
> Sent: Saturday, 23 May, 2015 09:41
> To: General Discussion of SQLite Database
> Subject: [sqlite] Sample SQL code thats beyond me :(
>
> Hi,
>
> I?m trying to do some analysis across a couple of tables and the SQL is
> beyond my meagre skills. I?m struggling to even describe the problem to be
> honest.
>
> The high level description is that I have three tables, Users, Devices and
> Perimeter_Notifications. The high level description is that I want to
> extract a list of users from a database to send information to if they are
> not on holiday. However I don?t necessarily have the holiday_mode set by
> the user and so our assumption is that unless the holiday mode is set to 1
> (they are on holiday) its is assumed to be 0. Its the assumption thats
> causing the problem. If there is no entries in Perimeter_Notifications
> thats also fine, no rows get returned.
>
> CREATE TABLE "Users" (
> "email" TEXT NOT NULL,
> "password" TEXT NOT NULL,
> "salt" TEXT NOT NULL,
> "creation_timestamp" TEXT NOT NULL DEFAULT
> (datetime('now','localtime')),
>PRIMARY KEY("email")
> );
>
> CREATE TABLE "Devices" (
> "Email" TEXT NOT NULL,
> "UUID" text NOT NULL,
> "Holiday_Mode" integer NOT NULL
> );
>
> CREATE TABLE "Perimeter_Notifications" (
>"Email" text NOT NULL ,
> "UUID" text NOT NULL,
> "route_id" INTEGER NOT NULL,
> "day" integer NOT NULL,
> "hour" integer NOT NULL
> );
>
> (Please note the UUID is nothing to do with the UUID discussion a few days
> ago, I?m not brave enough to open up that little can of worms, we just
> happen to have chosen that column name a few months ago for mobile
> devices. Also in case anybody asks, we're not storing passwords in plain
> text either).
>
> I?m not sure if my database design is wrong or I simply cannot work out
> the SQL to make it work. It might actually be both :)
>
> What I want to do is join the table Users and Perimeter Notifications
> together but only if the value of Devices.Holiday_Mode is either non
> existent or if Devices.Holiday_Mode does exist and its 0. If
> Devices.Holiday_Mode is 1 it means the user is on holiday and don?t send
> them anything.
>
> I can work out the logic if Devices.Holiday_Mode actually exists and is
> either 1 or 0. Thats pretty basic SQL.However if there is no row in
> Devices with that Email and UUID then thats the equivalent as
> Devices.Holiday_Mode being 0. I?ve looked at IS NULL or NOT EXISTS but I?m
> struggling to get my head around it all. I can do all of this in a higher
> level language (not sure if Perl is higher level than SQL) but I should be
> able to do this in SQL itself.
>
> Any advice or guidance welcomed please.
>
> Thanks for reading,
>
> Rob.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sample SQL code thats beyond me :(
On Sat, 23 May 2015, Rob Willett wrote: > What I want to do is join the table Users and Perimeter Notifications > together but only if the value of Devices.Holiday_Mode is either non > existent or if Devices.Holiday_Mode does exist and its 0. If > Devices.Holiday_Mode is 1 it means the user is on holiday and don?t send > them anything. Rob, First, you can set holiday_mode to 0 by default rather than leaving it NULL (unknown). As you wrote, unless the user explicitly sets the mode to 1 the assumption is that its value is 0. After all, it's gotta' be one or the other, right? Second, select * from Devices where holiday_mode == 0. Use that as a sub-query and join users to the results. Now you have a list of user email addresses for only those with holiday_mode of zero. HTH, Rich
[sqlite] Under what circumstances can a table be locked when the database is first opened?
On 23 May 2015, at 4:13am, david at andl.org wrote: > Run it once and it works perfectly. Run it twice and the DROP TABLE triggers > the error: > > SQLITE_LOCKED, database table is locked I suspect Keith has it right. To help you figure out which of his options is right, Run it once. Stay logged in for an hour. Run it again. Does it work now ? Simon.
[sqlite] NOT EXISTS and LEFT JOIN Performance problem
Hello! Sorry if this report shows up as a duplicate but didn?t figure out jet why some of my emails not showing up or getting rejected. This is my first "bug" report here so please bear with me for blunders. Using: ?sqlite-amalgamation-3081002.zip? Build as: ?cl sqlite3.c -O2 -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_FTS4 -DSQLITE_API=__declspec(dllexport) -link -dll -SUBSYSTEM:WINDOWS,"5.01" -out:sqlite3.dll? I have 2 Tables: CREATE TABLE `ART` ( `ID`integer NOT NULL, `Kundennummer`integer, `Rechnungsnummer`varchar, `Rechnungsdatum`datetime, `PDF`varchar, PRIMARY KEY(ID) ); and CREATE VIRTUAL TABLE `OCR` using fts4 ( `ID`integer primary key NOT NULL, `FullOCR` varchar ); "PRAGMA journal_mode = OFF" Both table has ~10.000 records but for testing more than 1000 is not recommended - one query can take up to 4 hours - ?FullOCR? has list of words in rows averaging 700/words * ~8 chars per row. The problem: Query (for 1000 records) 498 Rows returned from: SELECT ID FROM ART WHERE NOT EXISTS (SELECT ID FROM OCR WHERE OCR.ID = ART.ID); (took 34157ms) (For 10.000 records it takes ~ 3.5 hours.) - Result seems to be correct. - 498 Rows returned from: SELECT ART.ID FROM ART LEFT JOIN OCR ON OCR.ID = ART.ID WHERE OCR.ID IS NULL; (took 47924ms) (For 10.000 records it takes ~ 4 hours.) - Result seems to be correct. - 498 Rows returned from: SELECT ID FROM ART WHERE ID NOT IN (SELECT ID FROM OCR); (took 103ms) (For 10.000 records it takes 1759ms) Result seems to be correct as well. I am not pretending to be an expert but it looks like that some serious optimization flub is going on with ?NOT EXISTS? and ?LEFT JOIN?, the timings are "horrific". ?NOT IN? looks just fine. On MySQL all 3 queries timing is nearly identical ~1-2 seconds for 10.000 records. MR, Marta
[sqlite] Contentless FTS4 Tables
Hi! I have made a Contentless FTS4 Table like: "CREATE VIRTUAL TABLE if not exists OCR USING fts4(content="", `FullOCR` varchar;" And managed to insert some data into it. I have 2 questions: 1.) How to get the proper COUNT on the table ? The only query seems to work is: "SELECT COUNT(*) FROM OCR_docsize;" is this the right way to do it ? 2.) How to perform a "JOIN" operation with it? "SELECT docid FROM OCR WHERE FullOCR MATCH 'framework';" result is "2". "SELECT * FROM ART INNER JOIN OCR ON ART.ID = (SELECT docid FROM OCR WHERE FullOCR MATCH 'framework') ORDER BY ID;" Gives "SQL logic error or missing database:" "SELECT * FROM ART INNER JOIN OCR_docsize ON ART.ID = (SELECT docid FROM OCR WHERE FullOCR MATCH 'framework') ORDER BY ID;" Gives me 9 results back - should be only 1 - "SELECT * FROM ART NATURAL JOIN (SELECT docid FROM OCR WHERE FullOCR MATCH 'framework') ORDER BY ID;" Gives me 9 results back - should be only 1 - What I am missing here? Any idea? Thanks, Marta
