[sqlite] Reader.GetBytes() - when is a byte not a byte?
Lots of good replies. My problem is solved, thank you. My assumption was that GetBytes() could be used to read the bytes of any column regardless of type. What difference should column type or affinity make if I'm asking for bytes? If I'm using GetBytes() on a column with REAL affinity, it should put 8 bytes in the read buffer, but it doesn't. If I send text to a BLOB column it's typeof() changes to TEXT which confuses GetBytes(). I think this is pretty silly because bytes are bytes, right? Why bother verifying type when the method name already tells you what you are getting and bytes can be obtained from any data type? (Rhetorical questions, but feel free to respond anyway.) Anyway, the problem is solved. Thank you all. ( cast('x y z' as blob) ) is especially convenient. Thank you for that excellent suggestion, Jean (and thanks for the Stack Overflow link, that helped too). And thanks to R. Smith for posting the informative examples that also helped me understand what was happening. -- Bill Drago Staff Engineer L3 Narda-MITEQ 435 Moreland Road Hauppauge, NY 11788 631-272-5947 / William.Drago at L-3COM.com > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite- > users-bounces at mailinglists.sqlite.org] On Behalf Of Jean Chevalier > Sent: Thursday, July 02, 2015 1:40 PM > To: sqlite-users at mailinglists.sqlite.org > Subject: Re: [sqlite] Reader.GetBytes() - when is a byte not a byte? > > It's not mandatory to use x'' notation to insert into a blob, when > one can use cast. > > The following should return blob content correctly without explicitly > lying it down as Hex: > > sqlite> create table T (c blob check(typeof(c) = 'blob')); > > sqlite> insert into T values ( cast('x y z' as blob) ); > > sqlite> select c from T; > > x y z > > Please check whether your calling program consumes the above as text or > blob, I'd expect blob. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any attachments are solely for the use of the addressee and may contain information that is privileged or confidential. Any disclosure, use or distribution of the information contained herein is prohibited. In the event this e-mail contains technical data within the definition of the International Traffic in Arms Regulations or Export Administration Regulations, it is subject to the export control laws of the U.S.Government. The recipient should check this e-mail and any attachments for the presence of viruses as L-3 does not accept any liability associated with the transmission of this e-mail. If you have received this communication in error, please notify the sender by reply e-mail and immediately delete this message and any attachments.
[sqlite] Reader.GetBytes() - when is a byte not a byte?
It's not mandatory to use x'' notation to insert into a blob, when one can use cast. The following should return blob content correctly without explicitly lying it down as Hex: sqlite> create table T (c blob check(typeof(c) = 'blob')); sqlite> insert into T values ( cast('x y z' as blob) ); sqlite> select c from T; x y z Please check whether your calling program consumes the above as text or blob, I'd expect blob.
[sqlite] Could frequent 'database is locked' errors mean SQLite is not a good fit for my application?
Thanks so much for the help. I set the timeout to 5 seconds (at each instance of a db open connection) and was still seeing the database locked errors with some frequency (haven't quantified it yet), so I will try extending it to 30 seconds, as you suggest, and hopefully that makes a difference. Failing that, PostgreSQL looks promising (thanks so much for the suggestion!), so I will start trying to implement it. Thanks Again! On Thu, Jul 2, 2015 at 6:06 PM, Simon Slavin wrote: > > On 2 Jul 2015, at 3:59pm, Kathleen Alexander wrote: > > > I don't explicitly set sqlite3_busy_timeout(sqlite3*, int ms) so I will > try > > adding that after the database connection is opened to see if it limits > > those errors. > > This will have an extreme effect. The default timeout for SQLite is not > to use a timeout at all: any clash in access will result in immediate > failure. Set your timeout to 30 seconds and see what happens. > > Worth noting that the timeout needs to be set by each application for each > connection. If you set it for the first application which opens the > database it won't automatically be applied by other connections. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Could frequent 'database is locked' errors mean SQLite is not a good fit for my application?
Hi, Kathleen, On Thu, Jul 2, 2015 at 5:34 PM, James K. Lowden wrote: > On Thu, 2 Jul 2015 10:09:12 -0400 > Kathleen Alexander wrote: > >> Essentially, I have written an application in C++ that interfaces >> (reads and writes) with a SQLite database, and I am getting lots of >> 'database is locked' errors. [...] >> >> My application runs on Linux (ubuntu 13.10), and is driven by a bash >> script that spawns many (~60 on a 64 core workstation) instances of a >> serial, C++ program, each of which opens its own connection to the >> database and performs reads and writes. > > It may be that SQLite is not a good fit for your application. > Concurrent update is SQLite's Achilles heel. > > Each insert/update/delete in SQLite requires exclusive access. In WAL > mode, it requires exclusive access to the table; else it requires > exclusive access to the whole database. That means, by default, only > one process can update the database at a time. If you have 60 > processes, 59 will wait. > > Depending on your requirements, that may still be OK. If the updates > run quickly enough for your purposes, then increasing the timeout may > do the trick. Estimate the processing time and number of updates to > compute an overall throughput. If that's acceptable, problem solved. > > Most DBMSs, the heavy kind, devote extensive resources to support > concurrent update. Usually contention is managed at the page or row > level, and a whole section of the manual covers how the system > implements SQL's "isolation levels". Just exactly how many processes > can update the database at once is a function of almost everything: > DBMS configuration, table design, index definition, and isolation > level, not to mention hardware capacity and the OS I/O subsystem. Your > mileage will most certainly vary. > >> B. If SQLite is not a good fit for my program, do you have any >> suggestions of an alternative database engine that is free or might >> be free or inexpensive for academic use? > > *If* that's the case, your best option IMO is Postgres. If you want to > handle ~1000 concurrent updates, though, you will have to pay attention > to the details, and may have to arrange to minimize resource contention > in the DBMS. It all depends on the throughput and response-time > requirements. +1 for PostgreSQL. We use it in our application where there are many threads runs concurrently. and the DB needs updating almost continuosly. Also, it is free and available to every Linux distribution. Thank you. > > HTH. > > --jkl > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Could frequent 'database is locked' errors mean SQLite is not a good fit for my application?
On Thu, 2 Jul 2015 10:09:12 -0400 Kathleen Alexander wrote: > Essentially, I have written an application in C++ that interfaces > (reads and writes) with a SQLite database, and I am getting lots of > 'database is locked' errors. [...] > > My application runs on Linux (ubuntu 13.10), and is driven by a bash > script that spawns many (~60 on a 64 core workstation) instances of a > serial, C++ program, each of which opens its own connection to the > database and performs reads and writes. It may be that SQLite is not a good fit for your application. Concurrent update is SQLite's Achilles heel. Each insert/update/delete in SQLite requires exclusive access. In WAL mode, it requires exclusive access to the table; else it requires exclusive access to the whole database. That means, by default, only one process can update the database at a time. If you have 60 processes, 59 will wait. Depending on your requirements, that may still be OK. If the updates run quickly enough for your purposes, then increasing the timeout may do the trick. Estimate the processing time and number of updates to compute an overall throughput. If that's acceptable, problem solved. Most DBMSs, the heavy kind, devote extensive resources to support concurrent update. Usually contention is managed at the page or row level, and a whole section of the manual covers how the system implements SQL's "isolation levels". Just exactly how many processes can update the database at once is a function of almost everything: DBMS configuration, table design, index definition, and isolation level, not to mention hardware capacity and the OS I/O subsystem. Your mileage will most certainly vary. > B. If SQLite is not a good fit for my program, do you have any > suggestions of an alternative database engine that is free or might > be free or inexpensive for academic use? *If* that's the case, your best option IMO is Postgres. If you want to handle ~1000 concurrent updates, though, you will have to pay attention to the details, and may have to arrange to minimize resource contention in the DBMS. It all depends on the throughput and response-time requirements. HTH. --jkl
[sqlite] Weird FTS bugs (wrong, missing and false positive results)
We use SQLite for indexing and searching the text contents of our app using FTS4 (about 27k unique words, about 1 million words for the whole contents). In particular, we use the offsets function. Currently, after some testing, we?re experiencing a plenty of problems with finding the results needed. For the forms of the words searching we use the ?all-to-all? way, which means we have some standard English endings and words forms and modify the initial request so that all possible combinations are included and separated by OR from each other. I. e. if we have two forms and two words in the request (for simplicity), that would look like (the MATCH part): ?(word1_form1 NEAR/10 word2_form1) OR (word1_form1 NEAR/10 word2_form2) OR (word1_form2 NEAR/10 word2_form1) OR (word1_form2 NEAR/10 word2_form2)?. Initially, the problem appeared that the query returned offsets for absolutely wrong words. While searching for ?honest fair?, we got words like ?good?, ?sport?, ?natural? offsets. We moved from the system provided (iOS, OS X) SQLite (version 3.8.5, further called ?old SQLite?) to the latest one available on the official site (version 3.8.10.2, further called ?new SQLite? or just ?SQLite?), which solved the issue, but returned offsets for words which were not near (according to NEAR/10). I supposed it?s because the request was too big because of lots of the words forms. But, for example, this request for ?offense is rank?: select documents.name, contents.rowid, part, offsets(contents) from contents left join documents on contents.document_id = documents.id where text match '(\"offense\" NEAR/10 \"is\" NEAR/10 \"rank\")' order by document_id returned the correct results, while this one returned false positive results (0 stands for apostrophe so it?s not interpreted as a separate token for the simple tokenizer we use): select documents.name, contents.rowid, part, offsets(contents) from contents left join documents on contents.document_id = documents.id where text match '(\"offense\" NEAR/10 \"is\" NEAR/10 \"rank\") OR (\"offense\" NEAR/10 \"is\" NEAR/10 \"rank0s\")' order by document_id The experiments revealed that ?rank0s? can be whatever: ranks", "rankqwerty" and so on, even "rankqwertyvrfbgbrevwkefovmwpsrvrm" or "yugbuoiipkipnuo?. If it's removed, nothing found. If after that the first word is modified, the one correct result found. Also, a search for ?speak again? didn?t find the results at all, though there should be quite a lot of them. The ?database disk image is malformed? error was reported, but integrity check completed with no errors, the database was recreated and that didn?t help, meanwhile the old SQLite did find the results for this request in exactly that file with no error reported. Also, the new SQLite worked well for the same request if the offsets function was replaced with the snippet function (that also solved the false positive results problem described above). The search for ?father? returned not all results. For example, the first result which can manually be found in the contents table using SQLite Database Browser for this request: select documents.name, contents.rowid, part, offsets(contents) from contents left join documents on contents.document_id = documents.id where text match '(\"father\") OR (\"fathere\") OR (\"fathering\") OR (\"fatherish\") OR (\"fathers\") OR (\"fatheres\") OR (\"fatherian\") OR (\"fatheral\") OR (\"father0s\")' and document_id in (25) order by document_id missed when actually searched, the snippet function doesn?t help here, neither helps searching for a single word form. Error logging with sqlite3_config(SQLITE_CONFIG_LOG, errorLogCallback, NULL) was never called. Any ideas?
[sqlite] Slow real world performance - Any suggestions please (warning long)
> On Jul 2, 2015, at 3:16 PM, Rob Willett > wrote: > > We?re trying to understand whether or not we have a performance problem with > our Sqlite database. It may or may not apply to your situation, but after doing lots of inserts, running ANALYZE can sometimes work wonders. -- Ward
[sqlite] Reader.GetBytes() - when is a byte not a byte?
On 2015-07-02 03:24 PM, R.Smith wrote: > That exception is only thrown if the source column is not a BLOB (as > far as I can tell). > > You specify the column Type (or type affinity) as BLOB but then you > store a non-BLOB TEXT value in it ('"blue" "red" "orange" "yellow"' > <-- Not a BLOB) > SQLite will store this as TEXT, not BLOB. > > A BLOB starts with an x followed by a string of Hex, like in your > other inserted values such as: X'FFFEFDFCFBFAF9F8F7F6' > > The BLOB for your '"blue" "red" "orange" "yellow"' string will look > like this: > x'22626C75652220227265642220226F72616E676522202279656C6C6F7722' > > Insert that and getBytes() will work just fine. > > PS: This solution presupposes the enforcement of BLOB-ness, I'm not > exactly sure if this is the intention from SQLite or indeed a bug / > undesired side-effect. > To be sure, here is a script to point out the difference: CREATE TABLE myTable ( myText Text, myTextArray BLOB, --Text myBool Boolean, myBoolArray BLOB, --Boolean myUInt8 UInt8, myUInt8Array BLOB, --UInt8 myUInt16 UInt16, myUInt16Array BLOB, --UInt16 myInt16 Int16, myInt16Array BLOB, --Int16 myInt32 Int32, myInt32Array BLOB, --Int32 myInt64 Int64, myInt64Array BLOB, --Int64 myReal32 Real32, myReal32Array BLOB, --Real32 myReal64 Real64, myReal64Array BLOB --Real64 ); INSERT INTO "myTable" VALUES ('This is text','"blue" "red" "orange" "yellow"', 0,X'00010001', 255,X'FFFEFDFCFBFAF9F8F7F6', 65535,X'FEFFFDFFFCFFFBFFFAFFFAFFFBFFFCFFFDFF', 32767,X'FF7F01800100020003000400050006000700', 2147483647,X'FF7F00800100', 9223372036854775807,X'FF7F00800100', 3.40282346638528897567e+38,X'7F7F70443901803F004040408040A040C040E040', 1.79769313486231063271e+308,X'66E62440AE47E17A14AEF33F33B323C0007862A441A7C043'), ('This is text2',x'22626C75652220227265642220226F72616E676522202279656C6C6F7722', 0,X'00010001', 255,X'FFFEFDFCFBFAF9F8F7F6', 65535,X'FEFFFDFFFCFFFBFFFAFFFAFFFBFFFCFFFDFF', 32767,X'FF7F01800100020003000400050006000700', 2147483647,X'FF7F00800100', 9223372036854775807,X'FF7F00800100', 3.40282346638528897567e+38,X'7F7F70443901803F004040408040A040C040E040', 1.79769313486231063271e+308,X'66E62440AE47E17A14AEF33F33B323C0007862A441A7C043'); // Query showing only [BLOB] placeholders for all actual BLOBs SELECT typeof(myTextArray), myText, myTextArray, myBool, myBoolArray, myUInt8, myUInt8Array FROM myTable; -- typeof(m- | ||| | | -- yTextArr- | ||| myBoolAr- | | myUInt8A- -- ay) | myText | myTextArray| myBool | ray | myUInt8 | rray -- - | --- | -- | -- | - | --- | - -- text | This is text| "blue" "red" "orange" "yellow" | 0 | [Blob]| 255 | [Blob] -- blob | This is text2 | [Blob] | 0 | [Blob]| 255 | [Blob] // Same query showing full BLOB values(this program uses getBytes() internally when an actual BLOB is encountered) SELECT typeof(myTextArray), myText, myTextArray, myBool, myBoolArray, myUInt8, myUInt8Array FROM myTable; -- typeof(m- | | || | | -- yTextArr- | | || | | -- ay) | myText | myTextArray | myBool | myBoolArray | myUInt8 | myUInt8Array -- - | --- | | -- | | --- | -- text | This is text| "blue" "red" "orange" "yellow" |0 | 0x00010001 | 255 | 0xFFFEFDFCFBFAF9F8F7F6 -- blob | This is text2 | 0x22626C75652220227265642220226F72616E676522202279656C6C6F7722 |0 | 0x00010001 | 255 | 0xFFFEFDFCFBFAF9F8F7F6 -- > > > On 2015-07-02 01:47 PM, William Drago wrote: >> All, >> >> Below is a .dump of the database I'm working with. I can use >> GetBytes() successfully on all the BLOB columns except myTextArray. >> On that column I get a System.InvalidCastException error. Can anyone >> tell me why? Thanks. >> >> -Bill >> >> >> ---.dump file--- >> >> PRAGMA foreign_keys=OFF; >> BEGIN TRANSACTION; >> CREATE TABLE myTable ( >> >> myText Text, >> >> myTextArray BLOB, --Text >> >> myBool Boolean, >> >> myBoolArray BLOB, --Boolean >> >> myUInt8 UInt8, >> >> myUInt8Array BLOB, --UInt8 >> >> myUInt16 UInt16, >> >> myUInt16Array BLOB, --UInt16 >>
[sqlite] Could frequent 'database is locked' errors mean SQLite is not a good fit for my application?
On 2 July 2015 at 15:09, Kathleen Alexander wrote: > Hi, > > I apologize if this is an incorrect forum for this question, but I am > pretty new to SQLite and have been unable to resolve this issue through > other searches. Feel free to direct me to a more appropriate forum. > > Essentially, I have written an application in C++ that interfaces (reads > and writes) with a SQLite database, and I am getting lots of 'database is > locked' errors. Right now, I am trying to establish whether those errors > are due to my improper use of SQLite itself, or if the real problem is that > SQLite is not a good fit for my application. > > My application runs on Linux (ubuntu 13.10), and is driven by a bash script > that spawns many (~60 on a 64 core workstation) instances of a serial, C++ > program, each of which opens its own connection to the database and > performs reads and writes. http://www.sqlite.org/whentouse.html#dbcklst item 2 may be relevant Regards, Simon
[sqlite] Reader.GetBytes() - when is a byte not a byte?
That exception is only thrown if the source column is not a BLOB (as far as I can tell). You specify the column Type (or type affinity) as BLOB but then you store a non-BLOB TEXT value in it ('"blue" "red" "orange" "yellow"' <-- Not a BLOB) SQLite will store this as TEXT, not BLOB. A BLOB starts with an x followed by a string of Hex, like in your other inserted values such as: X'FFFEFDFCFBFAF9F8F7F6' The BLOB for your '"blue" "red" "orange" "yellow"' string will look like this: x'22626C75652220227265642220226F72616E676522202279656C6C6F7722' Insert that and getBytes() will work just fine. PS: This solution presupposes the enforcement of BLOB-ness, I'm not exactly sure if this is the intention from SQLite or indeed a bug / undesired side-effect. On 2015-07-02 01:47 PM, William Drago wrote: > All, > > Below is a .dump of the database I'm working with. I can use > GetBytes() successfully on all the BLOB columns except myTextArray. On > that column I get a System.InvalidCastException error. Can anyone tell > me why? Thanks. > > -Bill > > > ---.dump file--- > > PRAGMA foreign_keys=OFF; > BEGIN TRANSACTION; > CREATE TABLE myTable ( > > myText Text, > > myTextArray BLOB, --Text > > myBool Boolean, > > myBoolArray BLOB, --Boolean > > myUInt8 UInt8, > > myUInt8Array BLOB, --UInt8 > > myUInt16 UInt16, > > myUInt16Array BLOB, --UInt16 > > myInt16 Int16, > > myInt16Array BLOB, --Int16 > > myInt32 Int32, > > myInt32Array BLOB, --Int32 > > myInt64 Int64, > > myInt64Array BLOB, --Int64 > > myReal32 Real32, > > myReal32Array BLOB, --Real32 > > myReal64 Real64, > > myReal64Array BLOB --Real64 > > ); > INSERT INTO "myTable" VALUES('This is text','"blue" "red" "orange" > "yellow"',0,X'00010001',255,X'FFFEFDFCFBFAF9F8F7F6',65535,X'FEFFFDFFFCFFFBFFFAFFFAFFFBFFFCFFFDFF',32767,X'FF7F01800100020003000400050006000700',2147483647,X'FF7F00800100',9223372036854775807,X'FF7F00800100',3.40282346638528897567e+38,X'7F7F70443901803F004040408040A040C040E040',1.79769313486231063271e+308,X'66E62440AE47E17A14AEF33F33B323C0007862A441A7C043'); > > > COMMIT; > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Compiling sqlite as as library
I tried with the following flags : gcc -arch x86_64 -o sqlite3.o sqlite3.c shell.c and > gcc -m32 -o sqlite3.o sqlite3.c shell.c but both results in the same error. Can you please help me solve this ? On Thu, Jul 2, 2015 at 2:47 PM, Andy Ling wrote: > > I get the following error while trying to compile after linking with the > > library > > > > ld: warning: ignoring file > > > /Users/prakash-2282/Downloads/sqlite-amalgamation- > > 201506301510/libsqlite3.a, > > > file was built for archive which is not the architecture being linked > > > (x86_64): > > > /Users/prakash-2282/Downloads/sqlite-amalgamation- > > 201506301510/libsqlite3.a > > > > I'm guessing, but it looks like your compiling for 64bit and linking to a > 32bit system > or vice versa. > > Regards > > Andy Ling > > --- > This email has been scanned for email related threats and delivered safely > by Mimecast. > For more information please visit http://www.mimecast.com > > --- > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Compiling sqlite as as library
Hi Andy, I get the following error while trying to compile after linking with the library ld: warning: ignoring file > /Users/prakash-2282/Downloads/sqlite-amalgamation-201506301510/libsqlite3.a, > file was built for archive which is not the architecture being linked > (x86_64): > /Users/prakash-2282/Downloads/sqlite-amalgamation-201506301510/libsqlite3.a Can you kindly help me resolve this ? On Thu, Jul 2, 2015 at 1:51 PM, Andy Ling wrote: > > Can you please tell me how to compile it as a library on Mac OSX and > Linux > > platforms ? > > > > Use your favourite compiler to generate sqlite3.o then use ar to wrap > that up as a .a file. Give that file a name something like > libsqlite3.a and you will be able to add -Lsqlite3 to your LDFLAGS. > > The ar command will be something like > > ar -rus libsqlite3.a sqlite3.o > > Check the man page for more information > > HTH > > Andy Ling > > --- > This email has been scanned for email related threats and delivered safely > by Mimecast. > For more information please visit http://www.mimecast.com > > --- > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Reader.GetBytes() - when is a byte not a byte?
Please provide your code context, as in, http://stackoverflow.com/questions/10746237/sqlite-in-c-sharp-throws-invalidcastexception-using-getbytes Today, Bill wrote: > > All, > Below is a .dump of the database I'm working with. I can use > GetBytes() successfully on all the BLOB columns except > myTextArray. On that column I get a > System.InvalidCastException error. Can anyone tell me why? > Thanks > -Bill >
[sqlite] Compiling sqlite as as library
Thanks a ton Andy for your reply. can you specify how I should specify the include flags (-I). The kore build complais that "sqlite3.h" header file is not found . Thanks a lot for your time On Thu, Jul 2, 2015 at 1:51 PM, Andy Ling wrote: > > Can you please tell me how to compile it as a library on Mac OSX and > Linux > > platforms ? > > > > Use your favourite compiler to generate sqlite3.o then use ar to wrap > that up as a .a file. Give that file a name something like > libsqlite3.a and you will be able to add -Lsqlite3 to your LDFLAGS. > > The ar command will be something like > > ar -rus libsqlite3.a sqlite3.o > > Check the man page for more information > > HTH > > Andy Ling > > --- > This email has been scanned for email related threats and delivered safely > by Mimecast. > For more information please visit http://www.mimecast.com > > --- > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Reader.GetBytes() - when is a byte not a byte?
On 2 Jul 2015, at 12:47pm, William Drago wrote: > Below is a .dump of the database I'm working with. I can use GetBytes() > successfully on all the BLOB columns except myTextArray. On that column I get > a System.InvalidCastException error. Can anyone tell me why? Thanks. SQLite has no inherent understanding of arrays. Although your code looks like it should just be storing and recalling the string "blue" "red" "orange" "yellow" with its quotes and space I suspect that some part of the Reader.GetBytes() routine is assuming it will get an array, whereas it's really just getting a chunk of octets. You may have to read the column into text, then use your language to split the text up into array elements. Simon.
[sqlite] Could frequent 'database is locked' errors mean SQLite is not a good fit for my application?
Hi Adam, Thank you very much for your response. I had not considered using copies of the database. In my method there are cases where writes to the database by one process may be relevant to another process, so I'm not sure that that would be a good option. I don't explicitly set sqlite3_busy_timeout(sqlite3*, int ms) so I will try adding that after the database connection is opened to see if it limits those errors. Thanks Again. On Thu, Jul 2, 2015 at 10:28 AM, Adam Devita wrote: > Good day, > > I'm sure others on the list will add better insight, but is your task > parallel enough that your nodes can work with a copy of the database > and submit changes the one the others copy from when 'done' their > calculation? > > Are you using https://www.sqlite.org/c3ref/busy_timeout.html ? > > regards, > Adam > > This may be beside the point in terms of optimization, but your query > looks rather character based on int like information. > > On Thu, Jul 2, 2015 at 10:09 AM, Kathleen Alexander > wrote: > > Hi, > > > > I apologize if this is an incorrect forum for this question, but I am > > pretty new to SQLite and have been unable to resolve this issue through > > other searches. Feel free to direct me to a more appropriate forum. > > > > Essentially, I have written an application in C++ that interfaces (reads > > and writes) with a SQLite database, and I am getting lots of 'database is > > locked' errors. Right now, I am trying to establish whether those errors > > are due to my improper use of SQLite itself, or if the real problem is > that > > SQLite is not a good fit for my application. > > > > My application runs on Linux (ubuntu 13.10), and is driven by a bash > script > > that spawns many (~60 on a 64 core workstation) instances of a serial, > C++ > > program, each of which opens its own connection to the database and > > performs reads and writes. > > > > *An example SELECT query from my program looks like:* > > //open db connection > > sqlite3 *db; > > char *zErrMsg = 0; > > SQLITE3 sql(dbase.c_str()); > > > > statement = "SELECT * from configs_table WHERE id='31'"; > > sql.exe(statement.c_str()); > > if( sql.vcol_head.size() > 0 ){ > >//do things with sql.vdata[] > > }//end query returned results > > > > *An example of a write statement looks like:* > > statement = "UPDATE configs_table SET searched='2' WHERE id='31'"; > > sql.exe(statement.c_str()); > > > > About 97% of the time, the select statement works fine, but in the other > 3% > > of cases, I see a 'database is locked' error in the log file of my > program. > > About 50% of the time, the write statement returns 'database is locked'. > > > > Additionally, if this application is running and I try to query the > > database from the terminal, I almost always get a 'database is locked' > > error. > > > > Thus, I am wondering if I am doing something wrong in my implementation > of > > the C++ --> SQLite interaction, or if the real problem is that this > > application is not well suited to use with SQLite (I went through the > > checklist before implementing it and thought my application passed the > > suitability requirements). > > > > Lastly: > > A. if it seems like this is an implementation issue, rather than a > > capability issue, if I were to scale up my method to spawn say 500-1000 > > processes at a time (on a supercomputing cluster), would there be any > > concern about SQLite scaling to that level? > > B. If SQLite is not a good fit for my program, do you have any > suggestions > > of an alternative database engine that is free or might be free or > > inexpensive for academic use? > > > > Thanks in advance, > > Kathleen > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > -- > -- > VerifEye Technologies Inc. > 151 Whitehall Dr. Unit 2 > Markham, ON > L3R 9T1 > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Compiling sqlite as as library
Maybe this will help? http://stackoverflow.com/questions/4234767/how-do-i-build-sqlite3-from-command-line On Thu, Jul 2, 2015 at 10:25 AM, Prakash Premkumar wrote: > I tried with the following flags : > > gcc -arch x86_64 -o sqlite3.o sqlite3.c shell.c > > > and > > >> gcc -m32 -o sqlite3.o sqlite3.c shell.c > > > but both results in the same error. > > Can you please help me solve this ? > > On Thu, Jul 2, 2015 at 2:47 PM, Andy Ling wrote: > >> > I get the following error while trying to compile after linking with the >> > library >> > >> > ld: warning: ignoring file >> > > /Users/prakash-2282/Downloads/sqlite-amalgamation- >> > 201506301510/libsqlite3.a, >> > > file was built for archive which is not the architecture being linked >> > > (x86_64): >> > > /Users/prakash-2282/Downloads/sqlite-amalgamation- >> > 201506301510/libsqlite3.a >> > >> >> I'm guessing, but it looks like your compiling for 64bit and linking to a >> 32bit system >> or vice versa. >> >> Regards >> >> Andy Ling >> >> --- >> This email has been scanned for email related threats and delivered safely >> by Mimecast. >> For more information please visit http://www.mimecast.com >> >> --- >> >> ___ >> 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] Could frequent 'database is locked' errors mean SQLite is not a good fit for my application?
Good day, I'm sure others on the list will add better insight, but is your task parallel enough that your nodes can work with a copy of the database and submit changes the one the others copy from when 'done' their calculation? Are you using https://www.sqlite.org/c3ref/busy_timeout.html ? regards, Adam This may be beside the point in terms of optimization, but your query looks rather character based on int like information. On Thu, Jul 2, 2015 at 10:09 AM, Kathleen Alexander wrote: > Hi, > > I apologize if this is an incorrect forum for this question, but I am > pretty new to SQLite and have been unable to resolve this issue through > other searches. Feel free to direct me to a more appropriate forum. > > Essentially, I have written an application in C++ that interfaces (reads > and writes) with a SQLite database, and I am getting lots of 'database is > locked' errors. Right now, I am trying to establish whether those errors > are due to my improper use of SQLite itself, or if the real problem is that > SQLite is not a good fit for my application. > > My application runs on Linux (ubuntu 13.10), and is driven by a bash script > that spawns many (~60 on a 64 core workstation) instances of a serial, C++ > program, each of which opens its own connection to the database and > performs reads and writes. > > *An example SELECT query from my program looks like:* > //open db connection > sqlite3 *db; > char *zErrMsg = 0; > SQLITE3 sql(dbase.c_str()); > > statement = "SELECT * from configs_table WHERE id='31'"; > sql.exe(statement.c_str()); > if( sql.vcol_head.size() > 0 ){ >//do things with sql.vdata[] > }//end query returned results > > *An example of a write statement looks like:* > statement = "UPDATE configs_table SET searched='2' WHERE id='31'"; > sql.exe(statement.c_str()); > > About 97% of the time, the select statement works fine, but in the other 3% > of cases, I see a 'database is locked' error in the log file of my program. > About 50% of the time, the write statement returns 'database is locked'. > > Additionally, if this application is running and I try to query the > database from the terminal, I almost always get a 'database is locked' > error. > > Thus, I am wondering if I am doing something wrong in my implementation of > the C++ --> SQLite interaction, or if the real problem is that this > application is not well suited to use with SQLite (I went through the > checklist before implementing it and thought my application passed the > suitability requirements). > > Lastly: > A. if it seems like this is an implementation issue, rather than a > capability issue, if I were to scale up my method to spawn say 500-1000 > processes at a time (on a supercomputing cluster), would there be any > concern about SQLite scaling to that level? > B. If SQLite is not a good fit for my program, do you have any suggestions > of an alternative database engine that is free or might be free or > inexpensive for academic use? > > Thanks in advance, > Kathleen > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- -- VerifEye Technologies Inc. 151 Whitehall Dr. Unit 2 Markham, ON L3R 9T1
[sqlite] Weird FTS bugs (wrong, missing and false positive results)
FTFY, you're welcome ;-) Now *MAYBE* someone can read it and possibly reply. -- -- -- --???-- K e V i N On Thu, Jul 2, 2015 at 9:11 AM, wrote: > We use SQLite for indexing and searching the text contents of our app > using FTS4 > (about 27k unique words, about 1 million words for the whole contents). In > particular, > we use the offsets function. Currently, after some testing, we?re > experiencing a plenty > of problems with finding the results needed. > > For the forms of the words searching we use the ?all-to-all? way, which > means we have > some standard English endings and words forms and modify the initial > request so that > all possible combinations are included and separated by OR from each > other. > > I. e. if we have two forms and two words in the request (for simplicity), > that would look > like (the MATCH part): > > ?(word1_form1 NEAR/10 word2_form1) > OR (word1_form1 NEAR/10 word2_form2) > OR (word1_form2 NEAR/10 word2_form1) > OR (word1_form2 NEAR/10 word2_form2)?. > > Initially, the problem appeared that the query returned offsets for > absolutely wrong words. > While searching for ?honest fair?, we got words like ?good?, ?sport?, > ?natural? offsets. > We moved from the system provided (iOS, OS X) SQLite (version 3.8.5, > further called > ?old SQLite?) to the latest one available on the official site (version > 3.8.10.2, > further called ?new SQLite? or just ?SQLite?), which solved the issue, but > returned offsets > for words which were not near (according to NEAR/10). I supposed it?s > because the request > was too big because of lots of the words forms. But, for example, this > request > > for ?offense is rank?: > select documents.name, contents.rowid, part, offsets(contents) > from contents left join documents on contents.document_id = documents.id > where text match '(\"offense\" NEAR/10 \"is\" NEAR/10 \"rank\")' > order by document_id > > returned the correct results, while this one returned false positive > results (0 stands for > apostrophe so it?s not interpreted as a separate token for the simple > tokenizer we use): > > select documents.name, contents.rowid, part, offsets(contents) > from contents left join documents on contents.document_id = documents.id > where text match '(\"offense\" NEAR/10 \"is\" NEAR/10 \"rank\") > OR (\"offense\" NEAR/10 \"is\" NEAR/10 \"rank0s\")' > order by document_id > > The experiments revealed that ?rank0s? can be whatever: ranks", > "rankqwerty" and so on, > even "rankqwertyvrfbgbrevwkefovmwpsrvrm" or "yugbuoiipkipnuo?. If it's > removed, nothing > found. If after that the first word is modified, the one correct result > found. Also, a > search for ?speak again? didn?t find the results at all, though there > should be quite a > lot of them. > > The ?database disk image is malformed? error was reported, but integrity > check completed > with no errors, the database was recreated and that didn?t help, meanwhile > the old SQLite > did find the results for this request in exactly that file with no error > reported. > > Also, the new SQLite worked well for the same request if the offsets > function was replaced > with the snippet function (that also solved the false positive results > problem described > above). The search for ?father? returned not all results. For example, the > first result > which can manually be found in the contents table using SQLite Database > Browser for this > request: > > select documents.name, contents.rowid, part, offsets(contents) > from contents left join documents on contents.document_id = documents.id > where text match '(\"father\") > OR (\"fathere\") OR (\"fathering\") > OR (\"fatherish\") OR (\"fathers\") > OR (\"fatheres\") OR (\"fatherian\") > OR (\"fatheral\") OR (\"father0s\")' > and document_id in (25) > order by document_id > > missed when actually searched, the snippet function doesn?t help here, > neither helps > searching for a single word form. Error logging with > > sqlite3_config(SQLITE_CONFIG_LOG, errorLogCallback, NULL) > > was never called. > Any ideas? >
[sqlite] Compiling sqlite as as library
Thanks for your reply Kees. I am using kore web server and I want to pass sqlite library in the LDFLAGS option to the kore webserver. ( https://kore.io/doc/#cli ) For this purpose, I would like to compile it as a library, I wont be able to pass the sqlite3.c in the list of sources. Can you please tell me how to compile it as a library on Mac OSX and Linux platforms ? On Wed, Jul 1, 2015 at 7:28 PM, Kees Nuyt wrote: > On Wed, 1 Jul 2015 14:28:37 +0530, Prakash Premkumar > wrote: > > >Hi, > > > >Can you please tell me how to compile sqlite as a library and link with > >other applications using the "-L" flag ? > > That depends on the operating system you want to use it on. > For many common platforms a precompiled binary is available on > the download page: > http://www.sqlite.org/download.html > > The link recipe depends on your platform and toolchain. > Oftentimes, is it sufficent to add the sqlite3 amalgamation > source (which you can make with 'make sqlite3.c') to your list > of sources. For example the sqlite3 command line tool is build > using: > gcc ${CFLAGS} -o sqlite3 sqlite3.c src/shell.c > > More info: > http://www.sqlite.org/howtocompile.html > > I hope this helps. If you need more detailed info, please reply > with the specifications of your platform. > > -- > Regards, > > Kees Nuyt > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Could frequent 'database is locked' errors mean SQLite is not a good fit for my application?
Hi, I apologize if this is an incorrect forum for this question, but I am pretty new to SQLite and have been unable to resolve this issue through other searches. Feel free to direct me to a more appropriate forum. Essentially, I have written an application in C++ that interfaces (reads and writes) with a SQLite database, and I am getting lots of 'database is locked' errors. Right now, I am trying to establish whether those errors are due to my improper use of SQLite itself, or if the real problem is that SQLite is not a good fit for my application. My application runs on Linux (ubuntu 13.10), and is driven by a bash script that spawns many (~60 on a 64 core workstation) instances of a serial, C++ program, each of which opens its own connection to the database and performs reads and writes. *An example SELECT query from my program looks like:* //open db connection sqlite3 *db; char *zErrMsg = 0; SQLITE3 sql(dbase.c_str()); statement = "SELECT * from configs_table WHERE id='31'"; sql.exe(statement.c_str()); if( sql.vcol_head.size() > 0 ){ //do things with sql.vdata[] }//end query returned results *An example of a write statement looks like:* statement = "UPDATE configs_table SET searched='2' WHERE id='31'"; sql.exe(statement.c_str()); About 97% of the time, the select statement works fine, but in the other 3% of cases, I see a 'database is locked' error in the log file of my program. About 50% of the time, the write statement returns 'database is locked'. Additionally, if this application is running and I try to query the database from the terminal, I almost always get a 'database is locked' error. Thus, I am wondering if I am doing something wrong in my implementation of the C++ --> SQLite interaction, or if the real problem is that this application is not well suited to use with SQLite (I went through the checklist before implementing it and thought my application passed the suitability requirements). Lastly: A. if it seems like this is an implementation issue, rather than a capability issue, if I were to scale up my method to spawn say 500-1000 processes at a time (on a supercomputing cluster), would there be any concern about SQLite scaling to that level? B. If SQLite is not a good fit for my program, do you have any suggestions of an alternative database engine that is free or might be free or inexpensive for academic use? Thanks in advance, Kathleen
[sqlite] Compiling sqlite as as library
> I tried with the following flags : > > gcc -arch x86_64 -o sqlite3.o sqlite3.c shell.c > > > and > > > > gcc -m32 -o sqlite3.o sqlite3.c shell.c > > > but both results in the same error. > > Can you please help me solve this ? > It's getting outside my level of expertise (and not really sqlite specific). Try googling, there are lots of results. Maybe something like this will help https://github.com/tpoechtrager/osxcross/issues/11 Regards Andy Ling --- This email has been scanned for email related threats and delivered safely by Mimecast. For more information please visit http://www.mimecast.com ---
[sqlite] Compiling sqlite as as library
> I get the following error while trying to compile after linking with the > library > > ld: warning: ignoring file > > /Users/prakash-2282/Downloads/sqlite-amalgamation- > 201506301510/libsqlite3.a, > > file was built for archive which is not the architecture being linked > > (x86_64): > > /Users/prakash-2282/Downloads/sqlite-amalgamation- > 201506301510/libsqlite3.a > I'm guessing, but it looks like your compiling for 64bit and linking to a 32bit system or vice versa. Regards Andy Ling --- This email has been scanned for email related threats and delivered safely by Mimecast. For more information please visit http://www.mimecast.com ---
[sqlite] Compiling sqlite as as library
> Thanks a ton Andy for your reply. > > can you specify how I should specify the include flags (-I). > The kore build complais that "sqlite3.h" header file is not found . > Sounds like you need to read a few more manuals :^) Normally there are compiler options that let you tell it which directories to look in. For header files it is normally -I for include directories and for libraries it is -L. So just use these to add the directories that contain your sqlite3.h and libsqlite3.a files. Regards Andy Ling --- This email has been scanned for email related threats and delivered safely by Mimecast. For more information please visit http://www.mimecast.com ---
[sqlite] Compiling sqlite as as library
> Can you please tell me how to compile it as a library on Mac OSX and Linux > platforms ? > Use your favourite compiler to generate sqlite3.o then use ar to wrap that up as a .a file. Give that file a name something like libsqlite3.a and you will be able to add -Lsqlite3 to your LDFLAGS. The ar command will be something like ar -rus libsqlite3.a sqlite3.o Check the man page for more information HTH Andy Ling --- This email has been scanned for email related threats and delivered safely by Mimecast. For more information please visit http://www.mimecast.com ---
[sqlite] Could frequent 'database is locked' errors mean SQLite is not a good fit for my application?
If you are not using WAL mode that might help. On 7/2/2015 7:59 AM, Kathleen Alexander wrote: > Hi Adam, > > Thank you very much for your response. I had not considered using copies of > the database. In my method there are cases where writes to the database by > one process may be relevant to another process, so I'm not sure that that > would be a good option. > > I don't explicitly set sqlite3_busy_timeout(sqlite3*, int ms) so I will try > adding that after the database connection is opened to see if it limits > those errors. > > Thanks Again. > > On Thu, Jul 2, 2015 at 10:28 AM, Adam Devita wrote: > >> Good day, >> >> I'm sure others on the list will add better insight, but is your task >> parallel enough that your nodes can work with a copy of the database >> and submit changes the one the others copy from when 'done' their >> calculation? >> >> Are you using https://www.sqlite.org/c3ref/busy_timeout.html ? >> >> regards, >> Adam >> >> This may be beside the point in terms of optimization, but your query >> looks rather character based on int like information. >> >> On Thu, Jul 2, 2015 at 10:09 AM, Kathleen Alexander >> wrote: >>> Hi, >>> >>> I apologize if this is an incorrect forum for this question, but I am >>> pretty new to SQLite and have been unable to resolve this issue through >>> other searches. Feel free to direct me to a more appropriate forum. >>> >>> Essentially, I have written an application in C++ that interfaces (reads >>> and writes) with a SQLite database, and I am getting lots of 'database is >>> locked' errors. Right now, I am trying to establish whether those errors >>> are due to my improper use of SQLite itself, or if the real problem is >> that >>> SQLite is not a good fit for my application. >>> >>> My application runs on Linux (ubuntu 13.10), and is driven by a bash >> script >>> that spawns many (~60 on a 64 core workstation) instances of a serial, >> C++ >>> program, each of which opens its own connection to the database and >>> performs reads and writes. >>> >>> *An example SELECT query from my program looks like:* >>> //open db connection >>> sqlite3 *db; >>> char *zErrMsg = 0; >>> SQLITE3 sql(dbase.c_str()); >>> >>> statement = "SELECT * from configs_table WHERE id='31'"; >>> sql.exe(statement.c_str()); >>> if( sql.vcol_head.size() > 0 ){ >>> //do things with sql.vdata[] >>> }//end query returned results >>> >>> *An example of a write statement looks like:* >>> statement = "UPDATE configs_table SET searched='2' WHERE id='31'"; >>> sql.exe(statement.c_str()); >>> >>> About 97% of the time, the select statement works fine, but in the other >> 3% >>> of cases, I see a 'database is locked' error in the log file of my >> program. >>> About 50% of the time, the write statement returns 'database is locked'. >>> >>> Additionally, if this application is running and I try to query the >>> database from the terminal, I almost always get a 'database is locked' >>> error. >>> >>> Thus, I am wondering if I am doing something wrong in my implementation >> of >>> the C++ --> SQLite interaction, or if the real problem is that this >>> application is not well suited to use with SQLite (I went through the >>> checklist before implementing it and thought my application passed the >>> suitability requirements). >>> >>> Lastly: >>> A. if it seems like this is an implementation issue, rather than a >>> capability issue, if I were to scale up my method to spawn say 500-1000 >>> processes at a time (on a supercomputing cluster), would there be any >>> concern about SQLite scaling to that level? >>> B. If SQLite is not a good fit for my program, do you have any >> suggestions >>> of an alternative database engine that is free or might be free or >>> inexpensive for academic use? >>> >>> Thanks in advance, >>> Kathleen >>> ___ >>> sqlite-users mailing list >>> sqlite-users at mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> >> >> -- >> -- >> VerifEye Technologies Inc. >> 151 Whitehall Dr. Unit 2 >> Markham, ON >> L3R 9T1 >> ___ >> 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] Reader.GetBytes() - when is a byte not a byte?
All, Below is a .dump of the database I'm working with. I can use GetBytes() successfully on all the BLOB columns except myTextArray. On that column I get a System.InvalidCastException error. Can anyone tell me why? Thanks. -Bill ---.dump file--- PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE myTable ( myText Text, myTextArray BLOB, --Text myBool Boolean, myBoolArray BLOB, --Boolean myUInt8 UInt8, myUInt8Array BLOB, --UInt8 myUInt16 UInt16, myUInt16Array BLOB, --UInt16 myInt16 Int16, myInt16Array BLOB, --Int16 myInt32 Int32, myInt32Array BLOB, --Int32 myInt64 Int64, myInt64Array BLOB, --Int64 myReal32 Real32, myReal32Array BLOB, --Real32 myReal64 Real64, myReal64Array BLOB --Real64 ); INSERT INTO "myTable" VALUES('This is text','"blue" "red" "orange" "yellow"',0,X'00010001',255,X'FFFEFDFCFBFAF9F8F7F6',65535,X'FEFFFDFFFCFFFBFFFAFFFAFFFBFFFCFFFDFF',32767,X'FF7F01800100020003000400050006000700',2147483647,X'FF7F00800100',9223372036854775807,X'FF7F00800100',3.40282346638528897567e+38,X'7F7F70443901803F004040408040A040C040E040',1.79769313486231063271e+308,X'66E62440AE47E17A14AEF33F33B323C0007862A441A7C043'); COMMIT;