[sqlite] Including PRAGMA statements on connection string
Hellom, I am using the c# variant of sqlite (system.sqlite.data.org) and am using a connection string similar to this: public static string OMconnectionString = "datasource=database.db; Default Timeout=20; PRAGMA busy_timeout = 600; PRAGMA synchronous=Off"; I then use the following statements to open the connection to the database: OMConnection.sqConn = new SQLiteConnection(OMConnection.OMconnectionString); OMConnection.sqConn.Open(); However, I do not seem to notice any effect when including the PRAGMA synchronous=Off.? I have tried this without the PRAGMA statement,but still do not see any effect.? What is the proper way to do this in sqlite with a connection string?
[sqlite] User-defined types -- in Andl
Thank you. Exactly so. One of the problems with this kind of project is finding 'good enough' challenges to tackle. I'll let you know how I get on. [BTW I don't remember the last time I saw SQL like this. Understanding it might be the challenge...] Regards David M Bennett FACS Andl - A New Database Language - andl.org -Original Message- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Jean-Christophe Deschamps Sent: Tuesday, 9 June 2015 5:16 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] User-defined types -- in Andl At 08:27 09/06/2015, you wrote: >Andl is at a slightly higher level than SQL for writing simple queries. >Where it shines is writing complex queries that involve user-defined >types, custom transformations and custom aggregations. For complex >relational operations there is nothing I know that can come close, >productivity wise. `--- You call for complexity, you get it! What I would find pretty convincing is seeing how andl would translate the self-contained sudoku solver posted by E. Pasma in this post: http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2014-Mar ch/051982.html Granted, this is far from the typical SQL you can find in routine use, but I believe that andl being able to elegantly translate it would certainly impress a number of readers and make many of us more interested in digging further. I'm not throwing such a mayhem challenge at you in the tone of "if andl can't do that, then shut up". Maybe andl is not yet complete enough today to achieve that and this wouldn't be a big issue. But if it can I'm sure andl will attract more attention. JcD ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Optimizer and redundant joins
On 9 Jun 2015, at 6:52pm, giles burgess wrote: > Looking at the query plan Did you run ANALYZE after creating your tables, views and indexes ? Simon.
[sqlite] DROP statement on Virtual Tables
On 06/09/2015 04:46 PM, Samuel Debionne wrote: > Hello, > > AFAIU, when executing a drop statement on a virtual table, the XConnect > callback is executed first, then XDestroy. > > Now I have the following scenario where my virtual table is related to a > file (say a CSV file): > > 1. Create the virtual table > 2. Delete the CSV file from disk > 3. Try to drop the table (fails) > > When creating the vtable, xCreate / xConnect should fail (returns > SQLITE_ERROR) if the underlying file does not exists. > > But when dropping the vtable, this very same xConnect should continue to > let the user drop a table on a deleted file. > > Is there a way to know the "context", e.g. the SQL command, that has > triggered a call to xConnect ? Or is there a better way to tackle this > issue ? I don't think there is a way to detect the current statement. You probably have to allow the xConnect() to succeed and set a flag within the sqlite3_vtab object to indicate that the underlying file is not actually present. Then return SQLITE_IOERR or similar on all operations that actually require the CSV file. Dan. > > Thank you, > Samuel > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Newbie issue - Linux error malloc.c:2372: sysmalloc: Assertion `(old_top == (((mbinptr) (((char *)
On Sun, 7 Jun 2015 21:36:51 +0200 George wrote: > On Sat, 06 Jun 2015 21:14:46 +0700 > Dan Kennedy wrote: > > > On 06/06/2015 03:19 AM, George wrote: > > > Hello everyone, > > > > > > I am new to the list. I am working on an application in which I > > > will be embedding SQLite as the database engine. The application > > > is written in C. > > > > > > I am currently having an issue which I am not able to resolve at > > > the moment so I thought I would ask here since I am just starting > > > out with SQLite. > > > > > > My problem is, from my point of view, that I am not able to > > > perform an action to the same database file in the following > > > manner: > > > > > > 1) I open a database via: > > > sqlite3_initialize() > > > sqlite3_open_v2 > > > 2) I do some work on getting metadata from the database like table > > > names and their fields and then > > > 3) I close the connection via: > > > sqlite3_close_v2 > > > sqlite3_shutdown > > > 4) After all of this is done I wish to process an import file so I > > > need to open another connection to the same database file and run > > > some statements but when I try to do that I get this on the open > > > call in step 1 (above): > > > > I guess that assert() failing means the heap is corrupted. Which > > might be SQLite related or might not. > > > > Running the app under [valgrind] might tell you more. Post its > > complete output here if there are errors but it's not obvious what > > the problem is. > > > > Dan. > > Thanks to everyone who answered! > > I am doing this on Linux not on an embedded system, Ubuntu 14.04 LTS > to be more precise. > > I removed the initialize and shutdown which I had wrapped into my > connect and close methods after reading the "Using SQLite" book and > probably misunderstanding it... > > The problem is still there even after I removed the 2 function calls. > > I ran the whole app under the valgrind memory checker and it and > valgrind crashed at the end when the explosion happens. Here is the > summary: > > ==6013== HEAP SUMMARY: > ==6013== in use at exit: 0 bytes in 0 blocks > ==6013== total heap usage: 0 allocs, 0 frees, 0 bytes allocated > ==6013== > ==6013== All heap blocks were freed -- no leaks are possible > ==6013== > ==6013== For counts of detected and suppressed errors, rerun with: -v > ==6013== Use --track-origins=yes to see where uninitialised values > come from ==6013== ERROR SUMMARY: 2113 errors from 137 contexts > (suppressed: 0 from 0) > Aborted (core dumped) > make: *** [vg-check-import] Error 134 > > I do check the return codes and print any errors with sqlite3_errmsg > to see if I can move into another direction. > > I also checked my code and I do finalize my statement before the > close. > > It seems that when I go second time around things go bad... > > Not sure what is going on here... > Looking ... > Thanks guys, > George Well just for the record. This was a self inflicted problem I had made a call to a function which did not finalize a statement... once that was fixed everything works as expected. Thanks again. > > > > > > > > > > > > > > > malloc.c:2372: sysmalloc: Assertion `(old_top == (((mbinptr) > > > (((char *) &((av)->bins[((1) - 1) * 2])) - __builtin_offsetof > > > (struct malloc_chunk, fd && old_size == 0) || ((unsigned long) > > > (old_size) > > >> = (unsigned long)__builtin_offsetof (struct malloc_chunk, > > >> fd_nextsize))+((2 *(sizeof(size_t)) < __alignof__ (long double) ? > > >> __alignof__ (long double) : 2 *(sizeof(size_t))) - 1)) & ~((2 > > >> *(sizeof(size_t)) < __alignof__ (long double) ? __alignof__ (long > > >> double) : 2 *(sizeof(size_t))) - 1))) && ((old_top)->size & 0x1) > > >> && ((unsigned long) old_end & pagemask) == 0)' failed. Aborted > > >> (core dumped) > > > This happens in on line 17149 when calling: > > > > > > p = SQLITE_MALLOC( nByte+8 ) > > > > > > nByte is 64000 > > > > > > in sqlite3.c (amalgamation latest version > > > sqlite-amalgamation-3081002.zip) > > > > > > I am compiling and running the code on: > > > Linux x140e 3.13.0-53-generic #89-Ubuntu SMP Wed May 20 10:34:28 > > > UTC 2015 i686 athlon i686 GNU/Linux > > > > > > NAME="Ubuntu" > > > VERSION="14.04.2 LTS, Trusty Tahr" > > > ID=ubuntu > > > ID_LIKE=debian > > > PRETTY_NAME="Ubuntu 14.04.2 LTS" > > > VERSION_ID="14.04" > > > HOME_URL="http://www.ubuntu.com/; > > > SUPPORT_URL="http://help.ubuntu.com/; > > > BUG_REPORT_URL="http://bugs.launchpad.net/ubuntu/; > > > > > > I have compiled sqlite.o with the following: > > > > > > gcc -c -Wall -O0 -g -std=c99 -Dlinux -I/usr/local/include > > > sqlite3.c \ -DSQLITE_THREADSAFE=1 -DSQLITE_OMIT_LOAD_EXTENSION -o > > > obj/sqlite3.o > > > > > > Any suggestions or directions greatly appreciated. > > > TIA, > > > George > > > ___ > > > sqlite-users mailing list > > > sqlite-users at mailinglists.sqlite.org > > >
[sqlite] User-defined types -- in Andl
I think you'd be in a minority. I went through the first 50 questions about SQLite on Stack Overflow and only one was C++. Android/Java are dominant, with a smattering of C# and various other languages. Those are my target users, eventually. C/C++ is the drug of choice for low-level byte and bit twiddling like implementing SQLite. I know: I've written many tens of thousands of lines of the stuff but I can get more done faster in C#. The higher the language, the faster you get results. Try coding your own joins, compared to just using SQL. Andl is at a slightly higher level than SQL for writing simple queries. Where it shines is writing complex queries that involve user-defined types, custom transformations and custom aggregations. For complex relational operations there is nothing I know that can come close, productivity wise. Regards David M Bennett FACS Andl - A New Database Language - andl.org -Original Message- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Nelson, Erik - 2 Sent: Monday, 8 June 2015 11:51 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] User-defined types -- in Andl david at andl.org wrote on Monday, June 08, 2015 9:23 AM > > Ultimately, I don't think it will really matter, because the role of > Andl is to be platform independent. Do you care what your SQL product > is written in? > Absolutely. I wouldn't be using SQLite if it wasn't C/C++, and I suspect that I'm not the only one. It wouldn't even make sense for me to spend time looking at Andl, no matter how good it is. Implementation technology is critical to anyone that embeds SQLite. I'd guess that the SQLite developers' choice to use C was not accidental. Many people are perfectly productive using C/C++. Erik -- This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer. If you are not the intended recipient, please delete this message. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] User-defined types -- in Andl
You need to see code to know if you're interested. But I take your point: the grammar is here: http://www.andl.org/downloads/. Regards David M Bennett FACS Andl - A New Database Language - andl.org -Original Message- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Eduardo Morras Sent: Tuesday, 9 June 2015 4:02 AM To: sqlite-users at mailinglists.sqlite.org Subject: Re: [sqlite] User-defined types -- in Andl On Mon, 8 Jun 2015 15:28:11 +1000 wrote: > Thanks for pointing it out, but I knew that the best way to show off a > language is with examples. That's why there are nine sample Andl > scripts comprising dozens of individual examples in the Samples > folder. My guess is if that you're asking me to write examples, the > real lesson is that I didn't make them easy enough to find. > > I have a formal grammar, but I don't expect anyone to read that. More > and better examples is the way to go. No, a big bold No. If I want implement your language in some product I need the formal grammar. Learn by example means learn white rules (the dos), I need to know the black rules too (the don'ts) to get full knowledge of the language. > Regards > David M Bennett FACS > > Andl - A New Database Language - andl.org --- --- Eduardo Morras ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Including PRAGMA statements on connection string
Mike McWhinney wrote: > > public static string OMconnectionString = "datasource=database.db; > Default Timeout=20; PRAGMA busy_timeout = 600; PRAGMA synchronous=Off"; > The connection string should contain name/value pairs, delimited by "=" and ";" (i.e. connection string properties), not SQL statements. There is not currently a connection string property that directly sets the busy timeout; however, one may be added in the future. Meanwhile, the synchronous property can be set by using the following fragment in the connection string: "Synchronous=Off;" -- Joe Mistachkin
[sqlite] DROP statement on Virtual Tables
Removing the persistant instance of a virtual table is the most common way of tripping up a virtual table implementation. Just because xCreate and xConnect have identical interfaces does not mean that they are identical (although, with care, you can use the same routine to perform both tasks). Even if the documentation is a little misleading in this matter. xCreate is called in response to the SQL "CREATE VIRTUAL TABLE" command and is expected to create a (persistant) instance of the table. If it fails, the transaction fails and the table is not registered in sqlite3_master. xConnect is called whenever SQLite decides it needs to do something with the existing virtual table. There must have been a successful xCreate call (in another session, another thread or another process) before. xConnect is not allowed to fail, even if whatever storage implements the persistant data is no longer present. It should just take note of that fact and let the other routines react appropriately. xDestroy is called in response to the SQL "DROP TABLE" command and is passed the structure allocated by xConnect (which is why this must NEVER fail). It is expected to remove whatever persistant instance xCreate created. xDisconnect is just expected to free any resources allocated by xConnect. If your CSV File goes missing before your application is finished reading, too bad, just make sure that DROP TABLE can be executed so that SQlite can remove the bookkeeping entries from the SQLite db file. If your CSV File may be needed later, xDestroy should not delete it... -Urspr?ngliche Nachricht- Von: Samuel Debionne [mailto:samuel.debionne at ujf-grenoble.fr] Gesendet: Dienstag, 09. Juni 2015 11:47 An: sqlite-users at mailinglists.sqlite.org Betreff: [sqlite] DROP statement on Virtual Tables Hello, AFAIU, when executing a drop statement on a virtual table, the XConnect callback is executed first, then XDestroy. Now I have the following scenario where my virtual table is related to a file (say a CSV file): 1. Create the virtual table 2. Delete the CSV file from disk 3. Try to drop the table (fails) When creating the vtable, xCreate / xConnect should fail (returns SQLITE_ERROR) if the underlying file does not exists. But when dropping the vtable, this very same xConnect should continue to let the user drop a table on a deleted file. Is there a way to know the "context", e.g. the SQL command, that has triggered a call to xConnect ? Or is there a better way to tackle this issue ? Thank you, Samuel ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] User-defined types -- in Andl
At 13:50 09/06/2015, you wrote: >BTW I don't remember the last time I saw SQL like this. Understanding it >might be the challenge `--- Most probably! I can imagine that you don't encounter such style in common business-like environments. Take your time, this SQL piece is clearly beyond normal human understanding. Perhaps getting in touch with the author could help. Everyone could possibly benefit of innovative views and avenues. JcD
[sqlite] User-defined types -- in Andl
On Tue, Jun 9, 2015 at 1:50 PM, wrote: > Thank you. Exactly so. One of the problems with this kind of project is > finding 'good enough' challenges to tackle. > See also from the CTE doc: - https://www.sqlite.org/lang_with.html#sudoku - https://www.sqlite.org/lang_with.html#mandelbrot Thanks, --DD
[sqlite] DROP statement on Virtual Tables
On Tue, 9 Jun 2015 15:13:47 + Hick Gunter wrote: > xConnect is called whenever SQLite decides it needs to do something > with the existing virtual table. There must have been a successful > xCreate call (in another session, another thread or another process) > before. xConnect is not allowed to fail, even if whatever storage > implements the persistant data is no longer present. It should just > take note of that fact and let the other routines react appropriately. I don't see how xConnect can not be allowed to fail. The documentation says it may return SQLITE_ERROR, and it must allocate the sqlite3_vtab structure, which can fail. If a required resource is not available, ISTM returning an error is well advised, lest a call to later function fail more mysteriously. That said, the putative CSV file cannot be deleted between xCreate and xConnect if the vtable holds a descriptor to the file. In Linux, the file may be unlinked, but the inode is preserved while any open descriptor remains. In Windows, the user is prevented from deleting an open file. Of course the file may go away anyway. It might be on a USB drive and the user may pull it. In general, the hardware may fail. That's another reason xConnect may return an error. --jkl
[sqlite] Optimizer and redundant joins
I think without your actual schema and query plan it might be hard to tell, but generally the optimizer is not a panacea, it's limited in what it can divine from your queries. On Tue, Jun 9, 2015 at 10:52 AM, giles burgess wrote: > Hi, I have a question about the SQLite query optimizer. > > I have a SQLite embedded database with a star-like schema, with the wrinkle > that each record in the fact table can map to a range of records in each > dimension table. This is represented in the fact table as min and max > rowids for each dimension. > > This is not a particularly friendly physical schema to query directly, so I > have a view that dereferences the rowid foreign keys and also expands the > dimension ranges into separate rows. The multiplication factor for all > dimensions can get quite large, with a single physical fact record mapping > to thousands of records in the view. > > Looking at the query plan, it appears that a select * on this view gets > executed as nested scans over all the dimension tables. > > Obviously this isn't very fast, but I would expect that if I perform a > projection on this view that contains only a subset of the dimension field > values, eg: > > select distinct dimension1_value from friendly_view; > > the query optimizer ought to be able to avoid the joins with the tables for > dimension2, dimension3 etc since they don't contribute to the result. But > according to the planner it's scanning over all dimension tables > regardless. > > Is this expected? Is there some way to define the view so that the > optimizer can avoid these redundant joins? Or do I need to always > explicitly limit the joins to only the tables that are needed? > > > Thanks, > > Giles Burgess > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] DROP statement on Virtual Tables
Hello, AFAIU, when executing a drop statement on a virtual table, the XConnect callback is executed first, then XDestroy. Now I have the following scenario where my virtual table is related to a file (say a CSV file): 1. Create the virtual table 2. Delete the CSV file from disk 3. Try to drop the table (fails) When creating the vtable, xCreate / xConnect should fail (returns SQLITE_ERROR) if the underlying file does not exists. But when dropping the vtable, this very same xConnect should continue to let the user drop a table on a deleted file. Is there a way to know the "context", e.g. the SQL command, that has triggered a call to xConnect ? Or is there a better way to tackle this issue ? Thank you, Samuel
[sqlite] Optimizer and redundant joins
Hi, I have a question about the SQLite query optimizer. I have a SQLite embedded database with a star-like schema, with the wrinkle that each record in the fact table can map to a range of records in each dimension table. This is represented in the fact table as min and max rowids for each dimension. This is not a particularly friendly physical schema to query directly, so I have a view that dereferences the rowid foreign keys and also expands the dimension ranges into separate rows. The multiplication factor for all dimensions can get quite large, with a single physical fact record mapping to thousands of records in the view. Looking at the query plan, it appears that a select * on this view gets executed as nested scans over all the dimension tables. Obviously this isn't very fast, but I would expect that if I perform a projection on this view that contains only a subset of the dimension field values, eg: select distinct dimension1_value from friendly_view; the query optimizer ought to be able to avoid the joins with the tables for dimension2, dimension3 etc since they don't contribute to the result. But according to the planner it's scanning over all dimension tables regardless. Is this expected? Is there some way to define the view so that the optimizer can avoid these redundant joins? Or do I need to always explicitly limit the joins to only the tables that are needed? Thanks, Giles Burgess
[sqlite] User-defined types -- in Andl
At 08:27 09/06/2015, you wrote: >Andl is at a slightly higher level than SQL for writing simple queries. >Where it shines is writing complex queries that involve user-defined >types, >custom transformations and custom aggregations. For complex relational >operations there is nothing I know that can come close, productivity >wise. `--- You call for complexity, you get it! What I would find pretty convincing is seeing how andl would translate the self-contained sudoku solver posted by E. Pasma in this post: http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2014-March/051982.html Granted, this is far from the typical SQL you can find in routine use, but I believe that andl being able to elegantly translate it would certainly impress a number of readers and make many of us more interested in digging further. I'm not throwing such a mayhem challenge at you in the tone of "if andl can't do that, then shut up". Maybe andl is not yet complete enough today to achieve that and this wouldn't be a big issue. But if it can I'm sure andl will attract more attention. JcD
[sqlite] User-defined types -- in Andl
I hope you do try it. I'm looking for feedback. Sorry about the C#. Problem is, I'm way more productive in C# than any other language. C/C++ is just too slow to get things done and Java is still lagging. It would have taken far longer to do the SQLite C interface without .NET interop (JNI is seriously horrible). It was just the pragmatic choice. Ultimately, I don't think it will really matter, because the role of Andl is to be platform independent. Do you care what your SQL product is written in? If Andl turns out to be a good direction, then porting the VM and libraries is a straightforward exercise. First I need to find out if it's a good idea! Regards David M Bennett FACS Andl - A New Database Language - andl.org On Sun, Jun 7, 2015 at 4:17 AM, wrote: > I've been reading this thread with great interest. It parallels the > project I've been working on: Andl. > > Andl is A New Database Language. > > Andl does what SQL does, but it is not SQL. Andl has been developed as > a fully featured database programming language following the > principles set out by Date and Darwen in The Third Manifesto. It > includes a full implementation of the Relational Model published by > E.F. Codd in 1970, an advanced extensible type system, database > updates and other SQL-like capabilities in a novel and highly expressive > syntax. > > The intended role of Andl is to be the implementation language for the > data model of an application. It is already possible to code the > business model of an application in an SQL dialect, but few people do > this because of limitations in SQL. Andl aims to provide a language > free of these problems that works on all these platforms. > > The current implementation on SQLite uses a mixture of generated SQL > and a runtime VM. User-defined types are blobs, which the VM > understands. A future implementation could generate SQLite VM code > directly instead of SQL, which would save some overhead. > > The website is andl.org. The GitHub project is > https://github.com/davidandl/Andl. It's a work in progress. Any > feedback welcomed. > ?Looks interesting. Too bad it's written in C#. I'm basically a Linux-only guy (use Windows at work under protest, so to speak). Yes, I can use Mono on Linux to compile C# and run it. And I may.? > > Regards > David M Bennett FACS > > Andl - A New Database Language - andl.org > > -- Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. My sister opened a computer store in Hawaii. She sells C shells down by the seashore. If someone tell you that nothing is impossible: Ask him to dribble a football. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
