Re: [sqlite] Question about concurrency
On Jan 11, 2009, at 3:37 AM, Lawrence Gold wrote: > This question may sound a bit daft, but I'll ask anyway: If I use > SQLite > in the default SQLITE_CONFIG_SERIALIZED threading mode and share a > connection among threads, does that connection support multiple > simultaneous reads, or will each read access be serialized? With CONFIG_SERIALIZED, database and prepared statement handles are thread-safe objects. All API calls to a single database handle or any of its statement handles are serialized. > I'm assuming that for maximum concurrency in a multithreaded app, I > would need to set the threading mode to SQLITE_CONFIG_MULTITHREAD and > have a connection per thread, but I wanted to confirm this before I > made > any big code changes. In CONFIG_MULTITHREAD mode, you are responsible for serializing calls on a single database handle and its statement handles yourself. If you make calls on a single database handle from two or more threads simultaneously the application will likely crash or malfunction. The degree of concurrency provided is the same in either case, just that in CONFIG_MULTITHREAD mode you are responsible for enforcing it yourself. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory loss with sqlite3_bind - issue resolved
Hi Jay, Thanks for your response and it is the cache as you say. After ~ 150 inserts memory stabilises thereafter. This equates approx to the 2000 cache pages. Thanks again. John D. > Using SQLite3 3.5.9, I am seeing a consistent rise in memory with each > > call to sqlite3_step(). >> Jay A. Kreibich wrote: >> I'd assume it is the normal cache behavior. By default, SQLite>> database >> pages are 1K and the max page cache is 2000, using a total>> of about 3MB of >> RAM (each page as a ~0.5K overhead).>> >> You could try a lot more queries >> and see if things level out and/or>> lower the cache limit via PRAGMA. _ Get 30 Free Emoticons for your Windows Live Messenger http://www.livemessenger-emoticons.com/funfamily/en-ie/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .dump and transactions
On Friday, 9. January 2009 10:34:32 Thomas Jarosch wrote: > I run a small script every night via cron to backup a database > using the ".dump" statement. SQlite version is 3.6.6.2 on Linux. > > Normally this script works fine and from time to time > I get a backup file that looks like this: > --- > BEGIN TRANSACTION; > END TRANSACTION; > --- Here's a short example to reproduce the problem: sqlite3 test.db create table test (name varchar(16)); begin transaction; insert into test values ('test'); Now open a second shell and do this: sqlite3 test.db .dump This will output: --- BEGIN TRANSACTION; END TRANSACTION; --- I tested sqlite 3.5.9 on my workstation, it correctly dumps the table even if a transaction is open, only 3.6.6.2 shows this behavior so far. Is this supposed to be? Cheers, Thomas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hi, a question from Colombia
Ribeiro, Glauber writes: > > Carlos, > > If you don't mind, I'll answer through the list, because there are > people there who know much more than I do. > > > glauber > Hey, glauber, you didn't say which country you were from ! ;-) MikeW (UK) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .dump and transactions
On Mon, Jan 12, 2009 at 4:30 AM, Thomas Jarosch wrote: > On Friday, 9. January 2009 10:34:32 Thomas Jarosch wrote: >> I run a small script every night via cron to backup a database >> using the ".dump" statement. SQlite version is 3.6.6.2 on Linux. >> >> Normally this script works fine and from time to time >> I get a backup file that looks like this: >> --- >> BEGIN TRANSACTION; >> END TRANSACTION; >> --- > > Here's a short example to reproduce the problem: > > sqlite3 test.db > create table test (name varchar(16)); > begin transaction; > insert into test values ('test'); > did you forget to COMMIT here? > Now open a second shell and do this: > > sqlite3 test.db > .dump > > This will output: > --- > BEGIN TRANSACTION; > END TRANSACTION; > --- > > I tested sqlite 3.5.9 on my workstation, it correctly > dumps the table even if a transaction is open, > only 3.6.6.2 shows this behavior so far. > > Is this supposed to be? > > Cheers, > Thomas > -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .dump and transactions
On Monday, 12. January 2009 13:52:47 P Kishor wrote: > > Here's a short example to reproduce the problem: > > > > sqlite3 test.db > > create table test (name varchar(16)); > > begin transaction; > > insert into test values ('test'); > > did you forget to COMMIT here? Thanks for your reply. In fact it's the core of the problem: You can't dump a database if there is an open, uncommited transaction. This used to work in 3.5.9 and is needed for creating backups of databases. If an open transaction would block the dump of the database, then the second command line tool should busy wait until a timeout occurs or atleast return an error message. Thomas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] week number - feature request - extend strftime for ISO week?
PooLpi writes: > > Thanks MikeW, > > Is there a way to do strftime %W with another function. > It don't seems to be possible with datetime. > > Thanks > > PooLpi The Linux 'date' command does support %G/%g/%V referring to ISO week num, which suggests an extension feature for ISO support. Perhaps an SQLite feature request to add these format specifiers would be looked on favourably, after all, there is a precedent with %f and %J. If using the C API, you could also create your own function to do the job using http://www.sqlite.org/c3ref/create_function.html Regards, MikeW ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Testing SQLite on embedded platform with no Tcl
Hello, I am looking into porting the latest SQLite to an embedded platform running a commercial real-time operating system. I'd like to use as much of the existing regression test code as possible to ensure the port is correct but unfortunately the target environment does not have Tcl. The host environment is Windows and we will have a host-target link (via serial or telnet) to a CLI (a port of sqlite3) on the target. At present I am thinking about writing scripts that send SQL over the serial link and capture the response. Can anyone suggest a testing approach that would allow us to make use of the existing regression test code without having Tcl on the target system? Should I give serious consideration to porting Tcl just to run the regression tests? Thanks, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing SQLite on embedded platform with no Tcl
John Efstathiades schrieb: > Hello, > > I am looking into porting the latest SQLite to an embedded platform running > a commercial real-time operating system. I'd like to use as much of the > existing regression test code as possible to ensure the port is correct but > unfortunately the target environment does not have Tcl. Thats rare. Tcl gets ported to really weird places. Unless its really tiny there should be some Tcl port. Which OS are you using? If its vxworks have a look at: http://wiki.tcl.tk/21062 > > Should I give serious consideration to porting Tcl just to run the > regression tests? Might be easier than the work around. Usually Tcl is pretty easy to port as it has very few external dependencies. Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing SQLite on embedded platform with no Tcl
On Jan 12, 2009, at 10:53 AM, John Efstathiades wrote: > Hello, > > I am looking into porting the latest SQLite to an embedded platform > running > a commercial real-time operating system. I'd like to use as much of > the > existing regression test code as possible to ensure the port is > correct but > unfortunately the target environment does not have Tcl. > > The host environment is Windows and we will have a host-target link > (via > serial or telnet) to a CLI (a port of sqlite3) on the target. At > present I > am thinking about writing scripts that send SQL over the serial link > and > capture the response. > > Can anyone suggest a testing approach that would allow us to make > use of the > existing regression test code without having Tcl on the target system? > > Should I give serious consideration to porting Tcl just to run the > regression tests? > One approach taken by many companies is to trust that the workstation- based TCL tests correctly validate SQLite and don't worry so much about doing complete testing on the target. Just write a few simple sanity checks to make sure that it was compiled correctly and make due with that. Another approach is to port TCL to your target platform. Depending on your platform, this might be a small or a huge task. The third approach is the TH3 test suite for SQLite described at http://www.sqlite.org/testing.html . This third way is, alas, not a free option, but it is available to you if are using SQLite in an embedded mission-critical application and are uncomfortable with either of the other two options above. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.6.8
Version 3.6.8 of SQLite is now available on the website: http://www.sqlite.org/ SQLite version 3.6.8 adds support for nested transactions and improved optimization of WHERE clauses with OR-connected terms. There is also a new compile-time option that changes the way full-text search patterns are parsed so that they can contain nested paratheses. These are substantial changes. Even so, the release testing for SQLite has become so extensive that the developers have high confidence that this release is stable and ready for production use. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Number precision
Hello, As described in SQL syntax (http://www.sqlite.org/syntaxdiagrams.html#type-name) a column type may be declared like this : int(x) or float(x, y) ... what does x and y means ? max number of digits ? max value ? max bytes encoding ? ... a subsidiary question is : how to specify a precision on numbers if this syntax is not appropriated ? Thanks, Vincent ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Number precision
On Jan 12, 2009, at 11:10 AM, vlema...@ausy.org wrote: > Hello, > > As described in SQL syntax > (http://www.sqlite.org/syntaxdiagrams.html#type-name) a column type > may be > declared like this : > int(x) > or float(x, y) > ... > > what does x and y means ? max number of digits ? max value ? max bytes > encoding ? ... > > a subsidiary question is : how to specify a precision on numbers if > this > syntax is not appropriated ? The x and y are for compatibility with other database engines. They are ignored by SQLite. SQLite stores numbers as either (1) 64-bit signed integers or (2) 64-bit IEEE floating point numbers. > > > Thanks, > > Vincent > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is this legal SQL?
Hello all, I am working on connecting SQLite up to another programming language, and had a question about how SQLite (or SQL in general) would handle this... I want to be able to create some tables dynamically (same structure, different name), and I thought this might work... CREATE TABLE ?1 (x double, y double); And then I would assign '?1' to a string... BUT, it would appear that SQLite does not like this, because when I try to 'prepare' the SQL, it complains about "error near '?1': syntax error"... Is there something that I can do, short of generating the SQL dynamically (ie allow the use of placeholders for the table name)? Thanks, Mike -- Peace may sound simple—one beautiful word— but it requires everything we have, every quality, every strength, every dream, every high ideal. —Yehudi Menuhin (1916–1999), musician ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hi, a question from Colombia
Brazil, but living in the Chicago area of the USA since 1991. -Original Message- From: MikeW [mailto:mw_p...@yahoo.co.uk] Sent: Monday, January 12, 2009 6:35 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Hi, a question from Colombia Ribeiro, Glauber writes: > > Carlos, > > If you don't mind, I'll answer through the list, because there are > people there who know much more than I do. > > > glauber > Hey, glauber, you didn't say which country you were from ! ;-) MikeW (UK) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this legal SQL?
You can only bind data elemeents. If you want to chjange table names you have to recompile the SQL (think about it). Mike McGonagle wrote: > Hello all, > > I am working on connecting SQLite up to another programming language, > and had a question about how SQLite (or SQL in general) would handle > this... > > I want to be able to create some tables dynamically (same structure, > different name), and I thought this might work... > > CREATE TABLE ?1 (x double, y double); > > And then I would assign '?1' to a string... > > BUT, it would appear that SQLite does not like this, because when I > try to 'prepare' the SQL, it complains about "error near '?1': syntax > error"... > > Is there something that I can do, short of generating the SQL > dynamically (ie allow the use of placeholders for the table name)? > > Thanks, > > Mike > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.8
D. Richard Hipp wrote: > SQLite version 3.6.8 adds support for nested transactions This is *excellent* news! Thank you so much for implementing nested transactions in SQLite! As far as I was concerned, and AFAIK had argued in the past, that was the single most important piece of missing functionality. Something whose presence can make reliable database development an order of magnitude easier. Unlike some add-on features which could be done in wrappers, nested transactions was definitely something that was only appropriate to be implemented in the same low level as normal transactions in the DBMS. Its about code being able to declare and have enforced at any level of granularity that a set of operations is atomic, as a transaction, without worrying about what calling code is doing with transactions, and making it easier to do error handling and retry/abort etc. I'm now a step closer to being able to easily implement my Muldis D relational database language over SQLite. I'll hopefully be able to start testing in a few weeks, assuming that DBD::SQLite et al / the Perl bindings are still functional under Mac OS X and Linuxen with the new version. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this legal SQL?
You should use sprintf(buf, "CREATE TABLE %s(x double, y double)", tableName); then use prepare and execute using from that string. - Original Message - From: "Mike McGonagle" To: "General Discussion of SQLite Database" Sent: Tuesday, January 13, 2009 3:43 AM Subject: [sqlite] Is this legal SQL? Hello all, I am working on connecting SQLite up to another programming language, and had a question about how SQLite (or SQL in general) would handle this... I want to be able to create some tables dynamically (same structure, different name), and I thought this might work... CREATE TABLE ?1 (x double, y double); And then I would assign '?1' to a string... BUT, it would appear that SQLite does not like this, because when I try to 'prepare' the SQL, it complains about "error near '?1': syntax error"... Is there something that I can do, short of generating the SQL dynamically (ie allow the use of placeholders for the table name)? Thanks, Mike -- Peace may sound simple—one beautiful word— but it requires everything we have, every quality, every strength, every dream, every high ideal. —Yehudi Menuhin (1916–1999), musician ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this legal SQL?
Mike McGonagle wrote: > I want to be able to create some tables dynamically (same structure, > different name), and I thought this might work... > > CREATE TABLE ?1 (x double, y double); Parameters can only appear where a literal could legally appear. Why do you want to create a bunch of identical tables in the first place? What are you trying to achieve? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.8
Please post back with success/failure of the Perl bindings. Thanks Jim On Mon, Jan 12, 2009 at 3:32 PM, Darren Duncan wrote: > D. Richard Hipp wrote: > > SQLite version 3.6.8 adds support for nested transactions > > This is *excellent* news! > > Thank you so much for implementing nested transactions in SQLite! > > As far as I was concerned, and AFAIK had argued in the past, that was the > single > most important piece of missing functionality. Something whose presence > can > make reliable database development an order of magnitude easier. Unlike > some > add-on features which could be done in wrappers, nested transactions was > definitely something that was only appropriate to be implemented in the > same low > level as normal transactions in the DBMS. Its about code being able to > declare > and have enforced at any level of granularity that a set of operations is > atomic, as a transaction, without worrying about what calling code is doing > with > transactions, and making it easier to do error handling and retry/abort > etc. > > I'm now a step closer to being able to easily implement my Muldis D > relational > database language over SQLite. > > I'll hopefully be able to start testing in a few weeks, assuming that > DBD::SQLite et al / the Perl bindings are still functional under Mac OS X > and > Linuxen with the new version. > > -- Darren Duncan > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Jim Dodgen j...@dodgen.us ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this legal SQL?
Thanks for the advice, everyone was a help in this... On Mon, Jan 12, 2009 at 6:41 PM, Igor Tandetnik > Why do you want to create a bunch of identical tables in the first > place? What are you trying to achieve? I am working with Multimedia stuff, and each table, while holding the same fields, are used to represent different time frames of the larger work. Do you have any better suggestions? I am trying to figure out what the best way to handle this sort of thing. While all the data is pretty much the same, it does have different parts that come at different times. Thanks again, Mike > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Peace may sound simple—one beautiful word— but it requires everything we have, every quality, every strength, every dream, every high ideal. —Yehudi Menuhin (1916–1999), musician ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this legal SQL?
"Mike McGonagle" wrote in message news:370dda580901121949u3d2bf52ai87bfd7102cd7...@mail.gmail.com > Thanks for the advice, everyone was a help in this... > > On Mon, Jan 12, 2009 at 6:41 PM, Igor Tandetnik > Why do you want to > create a bunch of identical tables in the first >> place? What are you trying to achieve? > > I am working with Multimedia stuff, and each table, while holding the > same fields, are used to represent different time frames of the larger > work. How about create table work(timeframe int, x double, y double); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.8
Jim Dodgen wrote: > Please post back with success/failure of the Perl bindings. Yes, about that ... it seems the bindings have gotten stale and have some outstanding issues ... I'm hoping that someone will step up and maintain them ... I currently lack the C experience to do it easily myself if any such maintenance is needed, except on an experimental basis ... I'll also try contacting the most recent maintainers about it. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.8
I am having better luck with the amalgamation that has been created by Audrey Tang. My production is still on 3.4 and testing on 3.6 with the amalgamation has been promising On Mon, Jan 12, 2009 at 8:14 PM, Darren Duncan wrote: > Jim Dodgen wrote: > > Please post back with success/failure of the Perl bindings. > > Yes, about that ... it seems the bindings have gotten stale and have some > outstanding issues ... I'm hoping that someone will step up and maintain > them > ... I currently lack the C experience to do it easily myself if any such > maintenance is needed, except on an experimental basis ... I'll also try > contacting the most recent maintainers about it. -- Darren Duncan > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Jim Dodgen j...@dodgen.us ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.8
I have contacted both flavors' authors about the update, even offering to co-maintain the binding experimentally if necessary, so we'll see what happens. Part of the outstanding issue is that according to some users I trust DBD::SQLite 1.14 introduced significant bugs that weren't in 1.13, such that they recommend using 1.13 instead, so there are binding-specific issues still to resolve as well. Hence doing the update well isn't as simple as just substituting updated SQLite source files. -- Darren Duncan Jim Dodgen wrote: > I am having better luck with the amalgamation that has been created by > Audrey Tang. > My production is still on 3.4 and testing on 3.6 with the amalgamation has > been promising > > On Mon, Jan 12, 2009 at 8:14 PM, Darren Duncan wrote: > >> Jim Dodgen wrote: >>> Please post back with success/failure of the Perl bindings. >> Yes, about that ... it seems the bindings have gotten stale and have some >> outstanding issues ... I'm hoping that someone will step up and maintain >> them >> ... I currently lack the C experience to do it easily myself if any such >> maintenance is needed, except on an experimental basis ... I'll also try >> contacting the most recent maintainers about it. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Cross compilation
Hello all How can i cross compile tcl and sqlite for arm and ppc Tahnks in advance Chandru K ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Upgrade sqlite 3.3.4 to sqlite 3.6.7
Hi, I consider upgrade sqlite 3.3.4 to sqlite 3.6.7. So, I wonder there is any change (or problem) of file format. -- Best Regards, Edward J. Yoon @ NHN, corp. edwardy...@apache.org http://blog.udanax.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users