Re: [sqlite] using sqlite for searching
On Fri, 13 Feb 2009 16:06:42 +0530, aalap shah <aalap@gmail.com> wrote in General Discussion of SQLite Database <sqlite-users@sqlite.org>: >Hi, > >I am using sqlite3 for my search application and i want an optimized >way for retrieving values from table. I need a way in which I can >query records from the result of previous query based on next search >character. >so for example >If i search for words starting with "a" then if user enters "b" then i >want to search for words starting with "ab" from the ones that were >retrieved in my last query . You can try to build something smart with TEMPORARY TABLEs and/or indexes on substrings of words, but the first approach would be well chosen page- and cache sizes. When the database is only used for this purpose and there is no concurrent access this will usually be good enough. Will you use FTS? http://www.sqlite.org/cvstrac/wiki?p=FtsTwo >Can any one help me with this. It would be really helpful to me. >Thank You in Advance -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] open/close db's across threads
On Fri, 13 Feb 2009 09:22:42 -0800, ed <epdm...@gmail.com> wrote in General Discussion of SQLite Database <sqlite-users@sqlite.org>: >any help on this would be appreciated. >thanks, >ed Perhaps your question is too general to react upon. >-- Forwarded message -- > >Hello,I have an application that is calling sqlite3_open() >in one thread and sqlite3_close() in a different thread. >The db's can potentially be opened and closed many times >during execution of my app. Opening and closing all the time would indicate a bad program structure. Opening a database means the schema has to be interpreted. That takes time, especially if it contains more than a few simple tables. >Could this potentially introduce any problems? As far as I know not with recent versions of SQLite. Once upon a time, there was a problem with locks when using threads, where locks owned by one thread couldn't be released in another thread in some OS implementations of threads / locks. If you have any doubt, you could try to postpone opening the database and have it done by the thread that will close it. >In particular, I am trying to determine why sqlite3 >is consuming increasingly more memory as the db's >are opened and closed (as seen with >valgrind's massif). Doesn't valgrind indicate which allocations aren't freed? Perhaps you buffer result sets in your program and forget to release them? Do you sqlite3_reset() and sqlite3_finalize()? Do you check the return code of sqlite3_close() ? -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Full Table Read
On Fri, 13 Feb 2009 16:33:38 -0500, Nathan Biggs <nbi...@mycfs.com> wrote in General Discussion of SQLite Database <sqlite-users@sqlite.org>: >Is there a faster way to read an entire table other then: > >select * from table; It is the fastest SQL way. >Not that is is slow, just curious. The speed very much depends on what you do with the output. If it scrolls over a terminal, the terminal will be the bottleneck. It should be very fast if you redirect the output to /dev/null. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite- Memory heap usage query
On Mon, 16 Feb 2009 20:02:11 -0800 (PST), jaya_kumar <jayakumar.ananthakrish...@wipro.com> wrote in General Discussion of SQLite Database <sqlite-users@sqlite.org>: > >Hi All, > >In the following use case, when I try to update the following number of >objects SQLite DB file size is very large > >1. 1k object - DB file size was 264 Kb >2. 41k object - DB file size was 11 Mb >3. 100k object - DB file size was 26 Mb > >Is there any way to reduce the DB file size? Try this: - Reduce redundant data by strict normalisation of the database schema - Do not define indexes that are seldomly used - Try to use integers as primary key, make it the first column, and use the exact code CREATE TABLE tablename ( columnname INTEGER PRIMARY KEY, ); - Do not store data that you don't need >Thanks in advance, >Jai Good luck. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite- Memory heap usage query
On Tue, 17 Feb 2009 19:11:56 +0100, Kees Nuyt <k.n...@zonnet.nl> wrote in sqlite-users@sqlite.org: I'm not fond of replying to myself, but this needs some clarification: >- Try to use integers as primary key, > make it the first column, << wrong > and use the exact code > CREATE TABLE tablename ( > columnname INTEGER PRIMARY KEY, > > ); I looked a little closer: it doesn't have to be the first column. The trick is to use the explicitly defined INTEGER PRIMARY KEY column as the internal ROWID column at the same time. In other words, ROWID becomes an automatic alias for your INTEGER PRIMARY KEY column (or the other way around). Any other PRIMARY KEY definition will add an extra ROWID column behind the scenes. That column isn't visible in the schema. Although invisible, it can still be queried using the appropriate keywords. HTH -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newb-ish performance questions
On Sun, 22 Feb 2009 08:48:00 +, Kim Boulton <k...@jesk.co.uk> wrote in k...@jesk.co.uk, General Discussion of SQLite Database <sqlite-users@sqlite.org>: > *Then queried the Sqlite3 table with:* > PRAGMA cache_size = 2000; */uses up 1.5GB RAM regardless*/ cache_size is expressed in number of pages. default_cache_size is useful too. > PRAGMA page_size = 2000; /*this doesn't make any difference*/ PRAGMA page_size will only make a difference if you use it when creating the database (before the first table is created), or just before a VACUUM statement. Don't make it too big. 4096 or 8192 are a good start to experiment with. > Unless anyone has some good ideas I might > have to give up on Sqlite. I'm sure you can get more performance if you tweak page_size and cache_size with some more understanding. Use whatever engine is best for the task. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] running sqlite with gcc C
On Wed, 25 Feb 2009 09:05:29 -0500 (EST), mrobi...@cs.fiu.edu wrote in General Discussion of SQLite Database <sqlite-users@sqlite.org>: >Dear sqlite users group, > >I am new at SQlite3 and I would be very thankful for your help. > >I read that SQlite does not require installation, so I downloaded the >Precompiled Binaries For Windows and run sqlite3.exe in windows xp, no >problem, however, I have some gcc C programs that I run in windows xp >"dos" shell and in Linux and Unix, same code, just recompiled in its >corresponding OS. I would like to use SQlite3 with these gcc C programs. > >Reading thru the users group postings and in google in general, I came to >the conclusion that some kind of installation may be required, but I have >not been able to find instructions about it. Please guide me to get this >issued resolved so that I can start creating great apps with SQlite. Download the amalgamation. Compile it to create the SQLite library. Start without SQLITE_* defines, the defaults are Ok. Write your program. Link the SQLite library with it. Later, optimize SQLITE_* defined when needed. For platforms yet unknown to SQLite, you may have to write your own OS interface functions (sqlite3_vfs). http://www.sqlite.org/cvstrac/wiki?p=HowToCompile http://www.sqlite.org/cvstrac/wiki?p=TheAmalgamation http://www.sqlite.org/cvstrac/wiki?p=SqliteBuildProcess http://www.sqlite.org/docs.html http://www.sqlite.org/sitemap.html http://www.sqlite.org/cvstrac/wiki >Thank you very much > >Michael R HTH -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] running sqlite with gcc C
On Wed, 25 Feb 2009 10:04:18 -0500 (EST), Michael (mrobi...@cs.fiu.edu) wrote : >Thank you, it works perfectly >The links at the bottom of your email helped a lot. I'm glad it does work. By the way, the links were really easy to find. In my humble opinion you would benefit from spending more time on the site ;) -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] manual with sample C programs
On Wed, 25 Feb 2009 19:51:07 -0500 (EST), Michael (mrobi...@cs.fiu.edu) wrote: >Hello users group, > >I have been looking in the documentation for sample programs >in C, but no luck yet. The source of the sqlite3 commandline tool may serve as an example. Alternatively, you could take a look at the source of fossil, a Distributed Revision Control, Wiki, and Bug-Tracking application, which uses SQLite extensively. http://www.fossil-scm.org/index.html/doc/tip/www/index.wiki >Could you tell me where can I find such documentation, >or can you recommend some books. >Thanks very much > >Michael R -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question
On Thu, 26 Feb 2009 17:33:25 -0500, Bryan Lemster <bryan.lems...@gmail.com> wrote: >Hello, >I have a question on sqlite - Is there a good way to launch an >executable when a table is updated? Thanks. Not out-of-the-box, but you could write a function in a loadable extension which forks a process and have that function called in an ON UPDATE trigger. http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions http://www.sqlite.org/c3ref/create_function.html http://www.sqlite.org/lang_createtrigger.html Alternatively, you could change the SQLite source for your purpose. I don't advise this, it's hard to maintain. A loadable extension is the "proper mechanism". >-Bryan -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] view and attach database problem
On Sat, 28 Feb 2009 00:20:48 +0300, Alexey Pechnikov <pechni...@mobigroup.ru> wrote: >Hello! > >On Friday 27 February 2009 21:50:30 Kees Nuyt wrote: >> A view or trigger in one database is not allowed to >> reference tables (or other views) in other databases. The >> reason is, the schema of the main database (in this case >> your :memory: database) would be invalid once the main >> database is opened without the attached database, or after >> detaching it. > > So I can create table to saving view definitions and > create these as "temp view" to all of attached > databases Yes, you could store the text of the SELECT statements ready for preparing in a table, with ? placeholders for the value bindings. Or delegate the cross-database SELECTs to application code and not use VIEWs at all. > or disable the check of views. I think the define such > as SQLITE_DISABLE_VIEW_ON_ATTACHED_DATABASE may be > useful in code. You could implement that feature in your own branch/fork of SQLite, but I think it will never make it to the mainstream source version, because would be a bad thing to be able to create inconsistent schemas. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DateTime Objects
On Sat, 28 Feb 2009 12:27:10 -0800 (PST), jonwood
<nab...@softcircuits.com> wrote:
>Derrell Lipman wrote:
>>
>> http://sqlite.org/lang_datefunc.html
>>
>
>Exactly. No 2-digit year format, no AM/PM format, and no way to eliminate
>leading zeros, etc. Just as I pointed out in my original post.
Well, I would say you (or your users) live in the past.
The rest of the world uses ISO-8601 ;)
http://www.cl.cam.ac.uk/~mgk25/iso-time.html
Pun aside, you can always deliver epoch (or something else
you find more convenient) to your application and let the
application do the formatting.
SQL isn't meant for presentation anyway, it's for relational
storage.
Example:
Compute the time since the unix epoch in seconds (like
strftime('%s','now') except this includes the fractional
part):
SELECT (julianday('now') - 2440587.5)*86400.0;
HTH
--
( Kees Nuyt
)
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DateTime Objects
On Sat, 28 Feb 2009 17:30:24 -0800 (PST), jonwood <nab...@softcircuits.com> wrote: >Thanks, but I'm not sure what this means. "SQLite date storage format and >support" doesn't appear to be a specific term (at least, it didn't turn up >anything specific on Google). I'm almost sure John Stanton had this in mind: The number of days since noon in Greenwich on November 24, 4714 B.C. , as described in the return value of julianday(). http://www.sqlite.org/lang_datefunc.html http://en.wikipedia.org/wiki/Proleptic_Gregorian_calendar Then, use the SQLite datetime functions to return any of a few supported formats your application can cope with. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Joins
On Mon, 2 Mar 2009 13:59:13 -0500, "Fazoogled" <fazoog...@gmail.com> wrote: >I used to have a good cheat sheet on Joins that I cannot find anywhere. Must >have been cleaning house and had a \delete' attack. But I'm going to need it >for a little exercise I'm doing at home where I have a many to many table >with a 'connector' (what I call it) table in between. I'm going to be >futzing around with the joins 'til I get them right (I'm a C/C++ coder, not >a db guy) and I'm missing that cheat sheet! I got it somewhere in the web, >just can't seem to find it.. > >TIA A quick search shows there are many of those, I have no idea which one you are looking for. http://www.google.com/search?hl=en=SQL+join+"cheat+sheet; http://www.google.com/search?hl=en=SQL+join+"many+to+many"+"cheat+sheet; http://www.google.com/search?hl=en=SQL+join+"cheat+sheet"+sqlite >Michael > >___ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Double entry bookkeeping
On Thu, 5 Mar 2009 09:35:46 -0600, Alan Cohen <alanbco...@gmail.com> wrote: >What you have laid out as a schema might me a bare minimum for most >purposes. I've spent most of the last 30 years configuring and implementing >financial systems, mostly General Ledger for a variety of clients and >developers. Here are the top group of items I think you have missed: [snip] Excellent article! -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] which func could get the number of rows
On Fri, 6 Mar 2009 02:15:10 -0800 (PST), liubin liu <7101...@sina.com> wrote: >which func could get the number of rows? There is no function to retrieve the number of rows in a result set. SQLite doesn't know the number in advance, but returns row by row while iterating through the tables. The application can increment a row counter as needed at every successful sqlite3_step() . Some wrappers are able to collect all rows in a resultset in a in-memory table, so they can return the number of rows. You can always get the number of rows that a certain SELECT statement would return at the cost of some performance: BEGIN IMMEDIATE TRANSACTION; SELECT COUNT(*) FROM x WHERE y; SELECT a,b,c FROM x WHERE y; ROLLBACK TRANSACTION; You have to wrap this in a transaction to prevent other connections from inserting / deleting rows between the two SELECT statements. http://www.sqlite.org/lang_transaction.html I hope this helps and I added it to the wiki FAQ: http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
On Fri, 06 Mar 2009 10:26:38 +0100, Marcus Grimm <mgr...@medcom-online.de> wrote: >The website is allready excellent, I'm not at all complaining... > >Anyway, I've placed an updated version of the thread test program on the >web: > >http://www.exomio.de/sqlitethreadtest.c > >If somebody found it useful or good enough: Feel free to use it, change it, >or put on wiki pages. I added it to the Wiki : http://www.sqlite.org/cvstrac/wiki?p=SampleCode -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] which func could get the number of rows
On Sat, 7 Mar 2009 01:09:28 -0800 (PST), liubin liu <7101...@sina.com> wrote: >I'm very happy that my question have triggerred the wiki-FAQ's question. > >but if the next version could solve(settle?) the question, >many guys will be happy, :) In my opinion, there is no problem, so there is nothing to solve. By the way, there is new code in the works which will improve the performance of COUNT(*) in some cases. See the timeline: http://www.sqlite.org/cvstrac/timeline 2009-Feb-24 and 2009-Feb-25 >and now, does it mean that we have to use link-list struct to write such >kind of codes when using "sqlite3_prepare_v2() + sqlite3_step() + >sqlite3_column_*()"? Perhaps the sqlite3_get_table() API is what you are looking for? http://www.sqlite.org/c3ref/free_table.html > >Kees Nuyt wrote: >> >> On Fri, 6 Mar 2009 02:15:10 -0800 (PST), liubin liu >> <7101...@sina.com> wrote: >> >>>which func could get the number of rows? >> >> There is no function to retrieve the number of rows in a >> result set. SQLite doesn't know the number in advance, but >> returns row by row while iterating through the tables. The >> application can increment a row counter as needed at every >> successful sqlite3_step() . >> >> Some wrappers are able to collect all rows in a resultset in >> a in-memory table, so they can return the number of rows. >> >> You can always get the number of rows that a certain SELECT >> statement would return at the cost of some performance: >> >>BEGIN IMMEDIATE TRANSACTION; >>SELECT COUNT(*) FROM x WHERE y; >>SELECT a,b,c FROM x WHERE y; >>ROLLBACK TRANSACTION; >> >> You have to wrap this in a transaction to prevent other >> connections from inserting / deleting rows between the two >> SELECT statements. >> >> http://www.sqlite.org/lang_transaction.html >> >> I hope this helps and I added it to the wiki FAQ: >> >> http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there any way to attach more than 10 databases ?
On Sat, 7 Mar 2009 04:18:42 -0800 (PST), baxy77bax <b...@hi.htnet.hr> wrote: > > hi > > i have a problem , my program is returning me the > message that sqlite is complaining, because it > can't attach more than 10 databases at once. > is that true ? and is there a way to attach > at least 30 db at once ? It's in the documentation: http://www.sqlite.org/lang_attach.html >thanx! > >bax -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to do fulltest with amalgamation source
On Mon, 9 Mar 2009 14:54:45 -0500 (CDT), Tim Mooney <tim.moo...@ndsu.edu> wrote: > >All- > >If this is a FAQ, please point me at the answer. I've looked in the FAQ, >skimmed and grepped the archives for this list, read the >http://www.sqlite.org/testing.html page and others and still don't have >an answer, so I thought I would pose the question here. > >I've been building sqlite from source on various UNIX platforms for quite >a long time. I've recently converted to building from the amalgamation >sources, since that's what the developers recommend. > >The old (multi-file) source distribution had a "fulltest" make target >which allowed me to do at least some verification of the build I had >performed on the various platforms. > >I can't find any test target whatsoever with the amalgamation source. Is >there an extra .tar.gz I can download that would add at least some >rudimentary tests for the version compiled from the amalgamation? The test suite is not included in the amalgamation source. The only way to test is with the full source tree. As far as I know, for some tests, a special SQLite library is built to inject errors or introduce lower limits. I think the test suite will never be part of the amalgamation, because it's purposes are: - to optimize the resulting library / executable - to simplify the build process (with default options) Please feel free to add this to the FAQ. http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq >If not, >are there any plans to add a "check" or "test" or "fulltest" target to >the amalgamation source? > >Thanks, > >Tim -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LEFT INNER JOIN a second database
On Wed, 11 Mar 2009 06:12:37 -0700 (PDT), Derek Developer <derekdevelo...@yahoo.com> wrote: >I have read and searched but I am not able to >get the following statement to run: > SELECT MyID, Zip FROM TableOne d > LEFT OUTER JOIN DatabseTwo.sdb.TableTwo n > ON n.MyID=d.MyID WHERE d.Zip > 8 ORDER BY d.Zip > >I just get error at "." > >I tried specifiying the databse name without the file extension by no joy. >What am I missing here? Perhaps: ATTACH DATABASE 'DatabaseTwo.sdb' AS db2; SELECT MyID, Zip FROM TableOne d LEFT OUTER JOIN db2.TableTwo n ON n.MyID=d.MyID WHERE d.Zip > 80000 ORDER BY d.Zip; DETACH DATABASE db2; -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] writting a text file from sqlite db in C
On Thu, 12 Mar 2009 19:06:52 -0400 (EDT), mrobi...@cs.fiu.edu wrote: >Hi everybody, > >I would like to find the exact code in C to >output to a text file, data from a sqlite db in "C" Try the source for the sqlite command line tool. >Also, is there a place to find C sample code for Sqlite? Apart from the sqlite command line tool, I can recommend the source of fossil, which does all its magic around a SQLite database: http://www.fossil-scm.org/index.html/doc/tip/www/index.wiki Also, there is some code in the SQLite wiki: http://www.sqlite.org/cvstrac/wiki?p=SampleCode >Thank you very much > >Michael -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LEFT INNER JOIN a second database
On Thu, 12 Mar 2009 15:30:11 -0700 (PDT), Derek Developer
<derekdevelo...@yahoo.com> wrote:
>Hello,
>There is no need to get upset. I am trying to simplify the table names and
>column names to make your life easier...
>
>You can assume that I have carefully studied the responses I got and tried
>everything suggested. I have also read all the links given. Thank you for those
>
>I then implemented the suggestion:
>
>well, for one, do you have an "ATTACH 'DatabseTwo.sdb' AS db2" first?
>
>
>If yes, you could do
>
>
>SELECT..
>
>FROM TableOne d LEFT OUTER JOIN db2.TableTwo n ON n.MyID=d.MyID
>
>WHERE d.Zip > 8 ORDER BY d.Zip
>
>
>This is not working for me. TableTwo is not found.
Nobody is upset, but we just aren't able to help you if you
don't show the way you build your testdatabase.
Come on, it's just 2 minutes to type a simple testcase:
sqlite3 database1.sdb
create table tableone(MyID integer primary key, );
insert into tableone (MyID, ) VALUES (1,"");
.quit
sqlite3 database2.sdb
create table tabletwo(MyID integer primary key, );
insert into tabletwo (MyID, ) VALUES (2,"");
.quit
sqlite3 database1.sdb
attach database 'database2.sdb' as db2
select .
{{results}}
.quit
Hope this helps.
--
( Kees Nuyt
)
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Database Corruption
On Fri, 13 Mar 2009 14:17:12 +0530, "Chaitali" <chaitali.chattopadh...@cesc.co.in> wrote: >I am using SQLite database on Windows XP Professional as a back-end for my >application in VB6. Generally SQLite is functioning satisfactorily except >for a few instances when the database is getting corrupted. In one such case >of corruption the data from two tables got intermingled. In another case I >am getting the prompt "Database disk image is malformed". Since this is >causing data loss I would like to know how do I recover my data in case of >such a scenario First of all: save a copy of the database and journal, so you can retry any of your steps. - Restore from a recent backup This is the only reliable way. You may be able to rescue some data in one or more of these ways: - Try to dump the database echo .dump |sqlite3 dbname >all.sql - Try to dump table by table echo .dump tblN|sqlite3 dbname >tblN.sql - SELECT a range of rows using LIMIT, avoiding broken ranges > and secondly how to avoid such occurrence ? - Use the latest SQLite: v3.6.11 - Make sure you don't use dangerous PRAGMAs, like PRAGMA synchronous=OFF; - Use transactions (BEGIN / COMMIT) - Handle errors in your app - Do NOT delete journal files - make sure there is enough diskspace for = database = journal (in the database directory) = temp files (in /tmp) - Check your hardware See also: http://www.sqlite.org/lockingv3.html#how_to_corrupt http://www.sqlite.org/cvstrac/wiki?p=DatabaseCorruption http://www.sqlite.org/atomiccommit.html >Thanks and regards > >Chaitali -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Upgrade from 3.5.9 to 3.6.11
On Fri, 13 Mar 2009 08:57:35 -0700 (PDT), Joanne Pham <joannekp...@yahoo.com> wrote: > Hi All, > We have a application using SQLite 3.5.9 now > and we will be releasing this product in June. > I am think about upgrading SQLite from 3.5.9 to > SQLite 3.6.11 but I don't know what are the > impact for the application and is it worth > to upgrade SQLite to newest one before the > product is releaseed. > Would like to have your input on this. It is hard to have an opinion on this, because we don't know your application. I would advise to study the release notes: http://www.sqlite.org/changes.html If you require more detail, there is always the timeline: http://www.sqlite.org/cvstrac/timeline which documents all solved bug tickets and all code checkins. If you have a testsuite which covers your application very well, you can just give it a go with 3.6.11. > Thank in advance, > JP -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query Doesn't Find Record
On Fri, 13 Mar 2009 12:42:46 -0700 (PDT), jonwood <nab...@softcircuits.com> wrote: > > >P Kishor-3 wrote: >> >> why don't you try it? See below -- >> > >What are you folks using to type these queries? I've yet to find any good >utilities that do this for the Windows platform. sqlite3.exe in a CMD window. If I suspect the SQL is difficult enough to make typoos, I write a script with an ascii editor. sqlite3 test.db3 Thanks. > >Jonathan -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LEFT INNER JOIN a second database
On Sat, 14 Mar 2009 03:45:43 -0700 (PDT), Derek Developer <derekdevelo...@yahoo.com> wrote: >To make it really easy, I have created three .sql files and an application >that is NOT command line akward. There are three .sql files with the >statements needed to create two databases and execute the outer join. >Drag and drop them onto the application to execute them... >http://www.transferbigfiles.com/Get.aspx?id=ebd730fd-17ad-45c9-a341-43d078b118e3 That works as a charm with the original command line tool, that is to say, I don't get the error you mentioned before, "Error = UNKNOWN TABLE db2.TableTwo" Differences between your and my environment: - I didn't use your application, the sqlite3.exe from sqlite.org is not behaving "command line awkward" to me. - To obtain full output I added .echo on to the .sql scripts, no changes otherwise. I hope this helps. Versions Microsoft Windows [Version 6.0.6001] (=Vista) SQLite version 3.6.11 Shell script sqlite3.exe Db1.sql3 2 ORDER BY d.season; 3|1|The Robbery| 4|1|The Stock Tip|Bear Claws 5|2|The Ex-Girlfriend| 6|2|The Pony Remark| 7|2|The Busboy| 8|2|The Baby Shower|Carrot Cake 9|2|The Jacket| 10|2|The Chinese Restaurant| 11|2|The Phone Message| 12|2|The Apartment| 13|2|The Stranded|Cinnamon Bobka 14|2|The Statue| # -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IP from number with SQL
On Sun, 15 Mar 2009 21:10:02 +0100, "Roger Andersson" <r...@telia.com> wrote: >Hi! > > The SQL below might be out there but I didn't find it > and since there might be other that need to get > 32-bit integer IP in a sqlite3 database to the > a.b.c.d format using SQL > > I did get started from > http://acidlab.sourceforge.net/acid_faq.html#faq_e1 > and for me what's below does the trick in sqlite3 :-) > > SELECT > CAST((intIP & 4278190080) >> 24 AS text)||'.'|| > CAST((intIP & 16711680) >> 16 AS text)||'.'|| > CAST((intIP & 65280) >> 8 AS text)||'.'|| > CAST((intIP & 255) AS text) AS strIP > FROM IP_table; Cute code, thanks. The implementation of BitAnd and ShiftRight in the SQLite VM (~line 10176 in the amalgamated sqlite.c) is very straightforward and should be pretty fast. >Cheers >Roger -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question
On Wed, 18 Mar 2009 16:04:51 +, Dermot <paik...@googlemail.com> wrote: >2009/3/18 Hoover, Jeffrey <jhoo...@jcvi.org>: >> >> Assumming this is only one row in tbl2 where name='Joe'... >> >> this should work: >> SELECT * FROM tbl1 >> WHERE description='someval' >> AND foreign_key_id=(select id from tbl2 where name='Joe'); subselect (If there's more than one 'Joe', only the first 'Joe' is used) >> this is better: >> select tbl1.* from tbl1, tbl2 >> where tbl1.description='someval' >> AND tbl2.name='Joe' and tbl2.id=tbl1.foreign_key_id; implicit join In this case you could also write: SELECT tbl1.* FROM tbl1 INNER JOIN tbl2 ON tbl2.id=tbl1.foreign_key_id WHERE tbl1.description='someval' AND tbl2.name='Joe'; which is an explicit join. >> if there may be many rows in tbl2 where name =- 'Joe' then >> SELECT * FROM tbl1 >> WHERE description='someval' >> AND foreign_key_id in (select id from tbl2 where name='Joe'); 'IN subselect set' (I'm making this one up). >Both of the top 2 worked. Thank you. I'll try and stick to the less >ambiguous form and bear in mind all the comments about single quotes. > >A bit more information though. Is there a term for >that type of SELECT statement? See above. >Thanx, >Dp. HTH -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Schema syntax error
On Thu, 19 Mar 2009 03:39:11 +0200, Tristan Seligmann <mithra...@mithrandi.net> wrote: >Divmod Axiom[1] is a Python ORM built on SQLite; one of the book >keeping tables it creates in the database has a column named >"indexed", which became a reserved word around SQLite 3.6.4 (?). The >"obvious" fix for this problem is to simply quote the column name >using "", but the problem is that it is now impossible to load older >databases which didn't have the column created with the name quoted: > >Error: malformed database schema (axiom_attributes) - near "indexed": >syntax error > >What sort of migration path exists for converting / fixing these old >databases? Ideally there would be some mechanism that does not require >reinstalling an older version of SQLite. Digging in the mailing list archives, I found this: == To: sqlite-users@sqlite.org Subject: Re: [sqlite] FTS and upgrades From: d...@hwaci.com Date: Tue, 10 Jul 2007 22:26:21 + I probably shouldn't tell you this, but There is a pragma: PRAGMA writable_schema=ON; Which when enabled allows you to UPDATE or DELETE against the sqlite_master table. == Using this PRAGMA, you can UPDATE the sql column in the sqlite_master table. Of course this is undocumented and unsupported, and you risk corrupting your databases. Backups and rigorous testing required. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is primary key already indexed ?
On Thu, 19 Mar 2009 02:54:34 -0700 (PDT), baxy77bax <b...@hi.htnet.hr> wrote: > >hi my question is : if i create table that contains primary key like; > >create table TEST (field1 varchar not null primary key); > >do i need to create index on it or not? No, the primary key implies an index will be created automatically. >and is it better to create table with a primary key and then import data in >it or create table without a key , import data and then just create index on >the table. (which is faster- or should i ask which is the fastest way to >import data in the table?) You can't add a primary key afterwards. The best way is to create the table with the primary key and then insert records sorted by the primary key field. For speed you also have to wrap the INSERT statements in a transaction (BEGIN; INSERT; . INSERT; COMMIT;). If you want to use the .import command of the SQLite command line tool, you would sort the import file beforehand on the primary key column. >then if i create proper table , is it advisable to order data by >column with primary key on it or data with index on it. In your case (with a primary key on a text column) that won't make a difference, I think. >thank you -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Any available free SQLite Database Comparer/Diff Tool?
On Thu, 19 Mar 2009 19:06:52 +0800, Ev <eversog...@gmail.com> wrote: >Any available free SQLite Database Comparer/Diff Tool? At least one tool is listed on http://www.sqlite.org/cvstrac/wiki?p=ManagementTools And of course you can diff a dump: # sqlite3 test_38.db3 .dump >t38 # sqlite3 test_41.db3 .dump >t41 # diff t38 t41 >Thanks, >Ev -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is primary key already indexed ?
On Thu, 19 Mar 2009 06:38:33 -0700 (PDT), baxy77bax <b...@hi.htnet.hr> wrote: >:-D > >one more question : > >if i create primary key on my table and then import the data (assuming i >used transactions) will my import be faster or slower vs the case where i >import the data first and then create key (foreign key ??) and index the >table. That's not a foreign key, it would just be a unique index. It's hard to guess what would be faster, The best way to find out would be to test both cases, you can do that yourself. Make sure you import in sort order of primary key and use large transactions (BEGIN / COMMIT). >the point is that i need my import to be faster. i have 3890660 rows to >import and row van be up to 50M large, so it's taking a pretty long time Optimize for your use case: PRAGMA page_size PRAGMA default_cache_size and cache_size PRAGMA synchronous=off (only during loading) By the way, INTEGER PRIMARY KEY is faster than a TEXT column (VARCHAR is also TEXT) as primary key. >thnx -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] memory and db size
On Thu, 19 Mar 2009 08:14:52 -0700 (PDT), anjela patnaik <anjela_...@yahoo.com> wrote: > Hello all, > > I'm working on a GUI application using Tcl/Tk with > sqlite back end on windows. Now, multiple users will be > running this and all users must have a copy of the db > local to their PC or in a shared drive. At least that > is my understanding of how sqlite works. > > 1. What are my options if the db gets very large (say > 1 G) > and we don't want users to allocate that much disk > space and RAM? The same as for any other file or program, that is to say it is possible to make huge files or consume much memory with any other program. Think of bad awk scripts (with huge in-memory associative arrays) or Java or the tempfiles of a large sort. The usual system management strategies apply. > 2. How does sqlite allocate RAM when doing select > statements? what about when opening the db ? The allocation on open() is limited, most will be for the schema and perhaps page allocation maps. During SELECT / UPDATE / INSERT / DELETE the cache will grow. SQLite cache memory size = cache_size times page_size. There is quite some info on http://www.sqlite.org/ about architecture and optimization. > 3. Are there any ways to compress db files in disk (for > free) and then is sqlite able to still get to the data > easily? Donald already gave a good answer to that question. > Thank you! -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] writting a text file from sqlite db in C
On Fri, 20 Mar 2009 08:12:28 -0400 (EDT), mrobi...@cs.fiu.edu wrote: >Thanks for your help. > >I checked those sites, and I worked with sqlite3.exe using the command >line tool, no problems, however I can not find anywhere samples of how to >applied these commands in C. > >Thru trial and error, I can now use sqlite3_open(), sqlite3_close(), to >open and close databases as well sqlite3_exec() to create tables, fields, >indexes, and insert data into the tables. I am stuck trying to do a >"select" and process the data in the tables. > >Please help!!! Did you have a look at: http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/shell.c=1.207 ? For example, check the callback() function. >Michael > > > > >> On Thu, 12 Mar 2009 19:06:52 -0400 (EDT), >> mrobi...@cs.fiu.edu wrote: >> >>>Hi everybody, >>> >>>I would like to find the exact code in C to >>>output to a text file, data from a sqlite db in "C" >> >> Try the source for the sqlite command line tool. >> >>>Also, is there a place to find C sample code for Sqlite? >> >> Apart from the sqlite command line tool, I can recommend >> the source of fossil, which does all its magic around a >> SQLite database: >> http://www.fossil-scm.org/index.html/doc/tip/www/index.wiki >> >> Also, there is some code in the SQLite wiki: >> http://www.sqlite.org/cvstrac/wiki?p=SampleCode >> >>>Thank you very much >>> >>>Michael >> >> -- >> ( Kees Nuyt >> ) >> c[_] >> ___ >> 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 -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] speeding up row by row lookup in a large db
Hi Puneet, On Sat, 21 Mar 2009 09:31:45 -0500, P Kishor <punk.k...@gmail.com> wrote: >Part 1. >--- > >I have the following schema in a SQLite db that is 430 MB on my >Macbook laptop's 320 GB HFS+ formatted 7200 RPM disk with an 8 MB >cache. [...] >How can I, if at all, speed this up? What is your page_size? Does it match your platforms optimum I/O size? Is the database connection kept open, so the cache remains valid? >Part 2. >--- [...] > Well, I haven't yet completed this test because >each BLOB is taking about 430 KB. [...] > I broke the load_blob_table routine after about a >third of the records had been processed because I found even the >loading_the_blobs to be excruciatingly slow. > >Suggestions? Especially BLOBs will benefit from a large page_size, I think. For this schema and estimated BLOB size I would start with the maximum page_size allowed, that is 32768 bytes. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] speeding up row by row lookup in a large db
Hi Puneet, On Sat, 21 Mar 2009 10:47:44 -0500, P Kishor <punk.k...@gmail.com> wrote: >I should have mentioned the page_size in my OP. It is 32768 set by me >at the start of db creation. > >Yes, the db connection is kept open. Hm, apart from faster disks (15k RPM or high end SSD) I have no idea what else can be done to improve performance. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] speeding up row by row lookup in a large db
On Sat, 21 Mar 2009 14:07:17 -0500, Nicolas Williams <nicolas.willi...@sun.com> wrote: >But the thing is, you might just set the cache size large enough and let >it warm up as you go -- the effect should be the same if your DB doesn't >grow very fast. > >> [Puneet:] Also, isn't there a 2 GB limit to the amount of RAM >> that 32-bit processes can address? > >Even so, 1GB of cache is much better than the 2000 page (x 1KB page >size == 2MB) default. I second this. By the way, the SQLite team is changing the cache purging strategy between 3.6.11 and 3.6.12 (e.g. checkin 6341 http://www.sqlite.org/cvstrac/chngview?cn=6341 ). The effect would be that often used pages (like non-leaf index pages) are retained, which could reduce I/O. >Also, you might consider going 64-bit. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] speeding up row by row lookup in a large db
On Sat, 21 Mar 2009 16:37:15 -0500, P Kishor <punk.k...@gmail.com> wrote: > [04:24 PM] ~/Data/carbonmodel$perl carbonmodel.pl > Creating in memory tables... done. > Transferring data to memory... done. Took: 90 wallclock secs (75.88 > usr + 8.44 sys = 84.32 CPU) > Creating indexes... done. Took: 38 wallclock secs (23.82 usr + 13.36 > sys = 37.18 CPU) > Prepare load testing > ...timethis 1000: 33 wallclock secs (30.74 usr + 1.02 sys = 31.76 > CPU) @ 31.49/s (n=1000) Loading and indexing is pretty fast. >So, I increased the cache_size to 1048576 but got the same results... >30 odd SELECTs per second. With a cache of 1M pages with a page_size of 32 kByte, your cache would amount to 32 GByte, that's not realistic on a 32bit-mode OS. Maybe you should try PRAGMA [default-]cache_size=5 ? That's 1.5 GByte, and would leave some headroom for OS diskbuffers. > I will try out with PostGres and report back on what I get. I'm curious. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Step Query
Hi Dennis, On Tue, 24 Mar 2009 18:23:23 -0600, Dennis Cote <r.dennis.c...@gmail.com> wrote: >vinod1 wrote: >> I am new to sqlite and C. >> >> I have not been able to write a code which would read row by row using >> sqlite3_step. >> >> Could anybody guide me please. >> >Hi, > >This code is equivalent to the very old callback style code shown at >http://www.sqlite.org/quickstart.html. > >It should provide the same results using the newer prepare/step/finalize >set of calls that are discussed at http://www.sqlite.org/cintro.html. > >Hopefully it provides a complete, if somewhat basic, intro to the use of >the preferred C API functions. > >#include >#include [snip] > return rc!=SQLITE_DONE; >} > >HTH >Dennis Cote This seems a very nice addition to the http://www.sqlite.org/cvstrac/wiki?p=SampleCode we already have. I feel tempted to put it in the wiki http://www.sqlite.org/cvstrac/wiki under the 'Hints For Using SQLite More Effectively' heading, as http://www.sqlite.org/cvstrac/wiki?p=SimpleCode. Would you mind if I do? -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strict affinity again
On Wed, 25 Mar 2009 13:06:24 -0400, "Wilson, Ron P" <ronald.wil...@tycoelectronics.com> wrote: >Cool! I didn't think of doing that. >I presume this would incur a performance hit >on insert/update to check the constraint Not much. The column data is _dynamically_ typed, so SQLite will determine the type of each value offered anyway. >and sqlite3_prepare* would return SQLITE_CONSTRAINT >if the check failed. Right? Wrong. sqlite3_prepare* doesn't know the data you are going to offer with sqlite3_bind*. The same 'prepared' statement can be used with valid and invalid data. CONSTRAINT violations will be discovered during VM execution of your INSERT / UPDATE statements. See how it works with something like: EXPLAIN INSERT ... ; >RW > >Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 > > > > > >If you want to place a restriction on a column such that it will only > >hold an integer (for example) you can use a CHECK constraint. > > > > CREATE TABLE example1(x INTEGER CHECK( typeof(x)='integer' )); > > > >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-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert performance in 3.6.11 vs. 3.5.5
On Wed, 25 Mar 2009 15:24:47 +0100, Günter Obiltschnig <guenter.obiltsch...@appinf.com> wrote: >Hi there, > >I have just upgraded SQLite in our application from 3.5.5 to 3.6.11 >(we are using the amalgamation), and I have noticed a sharp drop in >insert performance to more than half the speed that we had with 3.5.5. >We are using SQLite in an embedded Linux device, and the database >files are on a CompactFlash device. > >The inserts are being done into an initially empty table with 28 >columns, and all inserts (can be more than 10) are done within one >large transaction, using a prepared insert statement. One additional >unique index on a single column is used on the table as well. Sounds good. >With 3.5.5, inserting 1000 rows into that table took about 7 seconds, >with 3.6.11 it takes 14-16 seconds. > >We are using PRAGMA synchronous = OFF and a cache size of 6000 pages. >The main reason why we updated was because we experienced memory >issues with 3.5.5. Reducing the cache size (PRAGMA cache_size) would >not release memory. > >Any ideas what causes this? Is the page_size the same in both cases? Does the page_size fit the I/O unity of the Compact Flash? (the default is not always optimal) For releasing cache memory, have a look at http://www.sqlite.org/c3ref/release_memory.html >Apart from that we are very happy with sqlite. A big thank you to >D. Richard Hipp and everyone who contributed to this great peace of >software. +1 >Thanks and best regards, > >Günter -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Step Query
On Wed, 25 Mar 2009 18:47:14 -0600, Dennis Cote <r.dennis.c...@gmail.com> wrote: >Kees Nuyt wrote: >> >> This seems a very nice addition to the >> http://www.sqlite.org/cvstrac/wiki?p=SampleCode >> we already have. >> >> I feel tempted to put it in the wiki >> http://www.sqlite.org/cvstrac/wiki >> under the 'Hints For Using SQLite More Effectively' heading, >> as http://www.sqlite.org/cvstrac/wiki?p=SimpleCode. >> >> Would you mind if I do? >> >No, I wouldn't mind in the least. In fact I think it's a good idea, and >thank you for making the edits. Done. http://www.sqlite.org/cvstrac/wiki?p=SimpleCode The first Q and A in the FAQ points to it as well. http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq >I also think it would be a good replacement for, or addition to, the >sample code on the quick start page, since that seems to be where many >beginners get started with the C API. It's a shame they get started with >depreciated API functions. I agree. >Dennis Cote Thanks for posting your code, and for your frequent valuable contributions to the mailing list. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with SQL and index (or schema?)
On Fri, 27 Mar 2009 15:53:18 +0100, Jonas Sandman <jonas.sand...@gmail.com> wrote: >Hello, > >I have a database with about 137000 * 2 rows with four columns; >fileid, filename, filepath and istarget. >It's used to determine if two scanned directories are equal so I run a >simply query to get the rows that are missing on the target directory >but do exists in the source directory. > >I use this query: > >SELECT f.filepath, f.filename FROM files f >WHERE f.istarget=0 >AND NOT EXISTS (SELECT * FROM files WHERE filepath=f.filepath AND >filename=f.filename AND istarget=1) In the sub-SELECT you use * so you get all columns. That's not necessary. SELECT f.filepath, f.filename FROM files f WHERE f.istarget=0 AND NOT EXISTS ( SELECT ROWID FROM files WHERE filepath=f.filepath AND filename=f.filename AND istarget=1 ); It might be better to rewrite as a self-join using LEFT OUTER JOIN on filename and filepath, using an NULL istarget from one or the other alias as an indication that target instance of the file is missing. Something like: SELECT source.filepath, source.filename FROM files AS source LEFT OUTER JOIN files AS target USING (filepath,filename) WHERE source.istarget = 0 AND target.istarget IS NULL ORDER BY whatever you like; (untested) >and I have experimented with some index to improve the speed, both >index (filename, filepath, istarget), (filename), (filepath) etc... The SQLite optimizer can only use one index at a time. In general, the index should be as selective as possible. This is known as cardinality. In your case, (filename, filepath) should do, unless all files in all directories have the same series of names, in which (filepath, filename) could be better. Adding istarget doesn't hurt, it would make the index usable as a primary key. If you defined more than one index, you can help the optimizer to choose the best index by running ANALYZE; on a database filled with representative data. >I am still not quite satisfied with the speed (a few seconds to check this). >Perhaps the table schema itself is the problem? You could normalise some more, by creating a second table CREATE TABLE Pathnames ( pathidINTEGER PRIMARY KEY, pathname UNIQUE ); and referring to its with a foreign key in the file table. CREATE TABLE Files ( pathid INTEGER CONSTRAINT fk_path REFERENCES Pathnames (pathid) ON INSERT RESTRICT ON UPDATE RESTRICT, filename TEXT, istarget INTEGER, PRIMARY KEY (filepathid,filename,istarget) ); This reduces the overall database size, improves the amount of unique data in a database page, and above that comparing integers is faster than strings. I can't help noticing that comparing two directories in the same system would cause the source and target paths to be different by definition (assuming the filepath column represents the absolute path), but I guess you are aware of that :) >Can someone help me? > >Regards, >Jonas -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with SQL and index (or schema?)
On Fri, 27 Mar 2009 18:08:13 +0100, Kees Nuyt <k.n...@zonnet.nl> wrote: > PRIMARY KEY (filepathid,filename,istarget) Oops, make that PRIMARY KEY (pathid,filename,istarget) -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sequential row numbers from query
On Fri, 27 Mar 2009 16:37:37 -0400, Thomas Briggs <t...@briggs.cx> wrote: > > Holy cow that feels inefficient. Yes, it certainly is. > It's a bit clunky, but why not insert into a temporary table, >ordered as desired, and then use the rowid from the temp table? Yes, or solve it in the host language, which is what I would do. Actually, OP's original: sqlite3 db 'select * from foo order by field desc; '|cat -n is pretty good. Oh, well, there are 11 roads to Rome :) -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie what does & do.
On Sat, 28 Mar 2009 09:42:59 +, Dermot <paik...@googlemail.com> wrote: >2009/3/28 Jay A. Kreibich <j...@kreibi.ch>: >> On Fri, Mar 27, 2009 at 05:37:49PM +, Dermot scratched on the wall: >> >>> The statement is: >>> >>> SELECT COUNT(*) FROM products WHERE productid=808800033 AND >>> (allowcountry2 & 0x0001) >>> SELECT COUNT(*) FROM products WHERE productid=808800033 AND >>> (allowcountry1 & 0x8000) >>> >>> I believe the allowcountry part is a bit pattern lookup but I can't >>> find a definition for the & and so I am not sure what the statement >>> does bar count the results. >> >> To actually answer your question, yes, "&" in SQLite and some other >> DBs is a bitwise "and" operator, just like the C/C++ "&" operator. >> "|" is a bitwise "or". >> >> It is my understanding that these operators are not part of the >> SQL standard, but a number of databases implement the operator. > >First off, sorry it was such an off-topic question. Thank you Jay for >the answer. > >I can't find any reference to the ampersand's usage in my new book, my >copy of learning MySQL or the SQLite Syntax documentation. I would >have expected symbols to be listed before A in the index myself. There >are references to bitmap indices. Symbols refer to the BNF notation >but without reference to &. I have posted to SQLQueries. Hopefully I >can get an understanding of what that statement means from there. For future reference, here's an explanation of all binary operators, that is the -> [expr] -> (binary-operator) -> [expr] -> part of the expr BNF diagram on http://www.sqlite.org/lang_expr.html A few of those are bitwise. pre- ce- den- opera- ce toroperation -- --- 1|| concatenation 2* multiplication 2/ division 2% modulo (remainder of integer division) 3+ addition 3- subtraction 4<< bitwise shift left 4>> bitwise shift right 4& bitwise AND 4| bitwise OR 5< less than 5<= less than or equal 5> greater than 5>= greater than or equal 6= equal 6== equal 6!= not equal 6<> not equal 6IN subset of 6LIKE case insensitive equality with % and _ wildcards 6GLOB case sensitive equality with Unix style file globbing syntax 6MATCH reserved for user C function match() 6REGEXP reserved for user C function regexp() 7ANDboolean (logical) AND 8OR boolean (logical) OR (Note to the SQLite developers: I could put this in the wiki but it might be better to update the http://www.sqlite.org/lang_expr.html#binaryops page itself) > Again sorry for the post. No problem. >Dp. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] creating unique data takes many hours, help
On Sun, 29 Mar 2009 15:19:00 -0400 (EDT),
mrobi...@cs.fiu.edu wrote:
>Hi,
>
>I am new with sqlite, and I create a program that reads several mllion
>records and puts them into a sqlite db using.
>
>The table has one column ONLY indexed and unique, but it takes many hours.
>
>Is it necessary to pre-allocate the space, or is the anything that I can
>do to reduce the time it takes.
>
>this is how I create the db, table and index.
>
>void openSqliteFile()
>{
>rc = sqlite3_open(genome_probesDB, ); //if it !exist creates it
>
>if (rc == SQLITE_OK) {
> printf("RC=%d database=%s was opened\n", rc, genome_probesDB );
>}
>else {
> printf("RC=%d database=%s COULD NOT OPEN\n", rc, genome_probesDB );
>}
>
>rc = sqlite3_exec(db, "CREATE TABLE probes (probe STRING unique);",
>NULL, NULL, );
>if (rc == SQLITE_OK) {
> printf("RC=%d table probes with field probe was created\n", rc );
>}
>else {
> printf("RC=%d table %s already exists, so it was NOT created\n",
>rc, genome_probesDB );
>}
>
>rc = sqlite3_exec(db, "CREATE INDEX probe on probes (probe);", NULL,
>NULL, );
>if (rc == SQLITE_OK) {
> printf("RC=%d INDEX probe on table probes for field probe was
>created\n", rc );
>}
>else {
>printf("RC=%d INDEX probe on table %s already exists, so it was NOT
>created\n", rc, genome_probesDB );
>}
>
>
>}//end void openSqliteFile()
>
>
>and this is how I add the data:
>char *zSQL = sqlite3_mprintf("INSERT INTO probes VALUES(%Q)", probe);
>
>sqlite3_exec(db, zSQL, 0, 0, 0);
>sqlite3_free(zSQL);
Two common optimizations:
1) Wrap the INSERT statements in a transaction
while not EOF on input file
BEGIN
loop 1 times or EOF
read input record
INSERT
endloop
COMMIT
endwhile
2) Sort your input file on the PRIMARY KEY
or on some other INDEX
>Thanks very much
>
>Michael
--
( Kees Nuyt
)
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert performance in 3.6.11 vs. 3.5.5
On Wed, 1 Apr 2009 06:08:47 +0200, Günter Obiltschnig <guenter.obiltsch...@appinf.com> wrote: >Well, seems that was a false alarm. We were not able to reproduce this >on other systems - there the 3.6.11 release even performed slightly >better than 3.5.5. Still no idea what caused this, as now even the >original system no longer shows this effect, but it's very probably >not SQLite. > >Best regards, > >Günter Ok, I'm glad it works for you. Thanks for letting us know. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Library Linking Issues on Ubuntu
On Thu, 2 Apr 2009 00:20:15 -0700, centipede moto
<cent1p...@hotmail.com> wrote:
>g++ -Wall cmxmc.cpp -lsqlite3 -o cmxmc
I won't comment on your compile / link problems, I'm not
familiar with Ubuntu and g++.
I hope you read the documentation and FAQ on the SQLite
site.
>Here is my app code:
>
>#include
>#include
>#include
>#include
>#include
>
>int main()
>{
>// create the database
>sqlite3 *db;
>int rc;
>
>rc = sqlite3_open("cmx.db", );
>}
This code will not create a database file, because there is
no schema to store.
You have to create at least one table in the database to
convince sqlite it's worth to create it.
>What folders should I be looking in to verify that
>sqlite3 is where it needs to be on my system?
Try:
find / -name '*sqlite*' -ls | more
It will take quite a while, but if it's your personal system
it won't hurt anybody.
>Thanks!
--
( Kees Nuyt
)
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite performance on select/update with 11GB database file
On Wed, 08 Apr 2009 23:17:02 +0200, Florian Nigsch <f...@nigsch.eu> wrote: >Hi all, > >I have been trying to implement a couple of things in SQLite because I >only need it for myself (so no concurrency issues here). > >I am working on Arch Linux (uname: 2.6.28-ARCH #1 SMP PREEMPT Sun Mar >8 10:18:28 UTC 2009 i686 Intel(R) Xeon(TM) CPU 3.40GHz). I have a flat >text file that I want to put into SQLite to be able to query it and >also update fields it required. This raw text file has 7.2GB, roughly >11 million entries and 60 fields. (Perhaps not the best design, but >that shall not be the issue here) We can't ignore that. If you have 11 million entries and 60 fields, I bet there is a lot of redundant data in the database, which makes it big. Normalization can make a lot of difference here. >Getting it into SQLite was not as easy as I anticipated. At first, I >used the execute() method for every insert, and committed every 1 >rows. This run didn't finish overnight. > >The a commit every 100,000 rows: at around 2.5 million entries it >slowed down so much that it was unacceptable. I used PRAGMA >journal_mode = OFF which improved a bit, but not enough. > >The final rescue was to use executemany() for every 100,000 records >combined with a commit(). That put the whole file in the DB in approx >17 min. Creation of indices where required took another 40 minutes. >The final database file has roughly 11GB. > >Now, I have 3 spare fields in the table that I want to populate with >data from another file. I loop over the file in question, from there I >get the ID of the record to update in the table and the info to >populate the empty field with. Again, I used executemany() every >100,000 records, assuming that it would work. But no. > >Even when I use executemany() every 10,000 rows, these 10,000 updates >take around 5 minutes or so (I haven't properly timed it). That is >also with PRAGMA journal_mode = OFF. I don't recognize executemany() or commit(). They are not part of the sqlite3_* API. Can I assume they are your own wrapper functions? You did tell you commit() your transactions, but do you also BEGIN them? (I apologize for asking the obvious, but someone has to ask it anyway). >When I just get the IDs of the entries that I need to update, look >them up in the table (select a,b,c from table where id in (...)), and >retrieve them with fetchall() and do nothing with them (no printing, >etc.) then this works fine for the first 10,000 records. After that, >again, it get's so slow that it's basically unusable. The ID field >that I use for the lookups has an index on it. The fields that I am >updating do not have indices on them. It's just text fields that are >being populated with strings up to approx 150 characters. > >Why are the retrievals that slow, and why are they getting slower the >further I go? And why are the updates so slow? > >Is it because the database file is too big? > >Any help or suggestions would be highly appreciated! > >Regards, > >Flo -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Heuristics of when to vacuum
On Sat, 11 Apr 2009 20:16:32 -0700, Tito Ciuro <tci...@mac.com> wrote: >Hi Lawrence, > >On Apr 11, 2009, at 7:51 PM, Lawrence Gold wrote: > >> I can't offer a formula, but I suggest making it an option for the >> users of the software, with sufficient warning that it could take some >> time, as well as a Cancel button. Another thing you could do is to >> schedule the vacuum for a time when you know the software won't be in >> use -- for example, those of us who write software for K-12 schools >> can safely schedule operations like this for midnight on >> weekends. :-) > >It's not an application. It's a framework which is used by a daemon >process. There can't be a UI, and scheduling a vacuum when it's not >needed is wasteful, especially because the databases can be quite >large. This is why I was looking for some way to determine whether >vacuum is needed, so that it's performed when it makes sense to do so. > >Thanks anyway, I appreciate your input! :-) PRAGMA freelist_count; tells you how many pages are free. If there are many free pages, you may have a reason to vacuum. It doesn't tell anything about the average percentage of payload in database pages, which would be another reason to vacuum. For a full analysis, you'd have to run the sqlite3_analyzer program, or incorporate part of its code in your application. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] fail to drop table in transaction
On Sun, 12 Apr 2009 20:46:40 -0700 (PDT), Wenton Thomas <thomas.wen...@yahoo.com> wrote: >I created two tables A and B. >There exists a record which contains B's information. > >Now I need to drop table B and delete all its information in table A. >The two actions were wrapped in a transaction,but dropping table always >fail. >Error no is >SQLITE_CANTOPENwhich means"Unable to open the database file". >The sqlite version is 3.5.9. > >Could anyone help me? It's hard to tell from your description what you are doing exactly. Does the same SQL work correctly from the command line tool? It is not very common to drop a table when data changes, usually the schema is stable and rows are inserted into and deleted from tables, or column values changed. It might indicate a flaw in your database schema design. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] fail to drop table in transaction
On Mon, 13 Apr 2009 02:35:46 -0700 (PDT), Wenton Thomas <thomas.wen...@yahoo.com> wrote: > I didn't test it from command tool yet. Well, that is the first thing to try. > I have a table (call it table A) to record other > table's information. When I delete a table, I also > delete all its information recorded in table A. > I wonder whether it is legal to drop a table and > delete another table's records in one transaction. That shouldn't be a problem. But it is a weird construction, unless tableB is an application table, and tableA part of a dictionary. For a normal application, you wouldn't have to drop tableB, but just insert / delete rows. What are you trying to accomplish? -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mismatched columns in union can repeatably crash 2.6.10
On Wed, 15 Apr 2009 21:38:04 +0100, Tom Sillence <t...@sillence.co.uk> wrote: >I don't have all that much time to research whether this is a known issue. >The following query crashes 2.6.10 every time, on linux and windows: > >create table crash(a,b); insert into crash select >1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 union all select >1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26 union >select 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26 >order by 1 2.6.10 is out of date. Ok, it crashes 3.6.11 and 3.6.13 too, but to be honest, I don't mind any embedded database to crash when it is thrown nonsensical code like this; in the same way I don't mind any kernel to panic when the plug is pulled on the root disk. >If people are interested, I'm happy to build a debug version of sqlite and >get a stacktrace. I'm also looking for advice on the proper way to report a >bug like this - and how to check whether similar things have been reported >already. Timeline of tickets and checkins: http://www.sqlite.org/cvstrac/timeline Report a bug: http://www.sqlite.org/cvstrac/tktnew If your looking for anything else, start with: http://www.sqlite.org/sitemap.html >Cheers, -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tree structure in SQLite DB
On Mon, 20 Apr 2009 12:02:36 -0700 (PDT), Mächi <mhae...@gmail.com> wrote: >Hello everybody, > >I'm trying to figure out how to make a tree structure in a SQLite DB. Can >anybody help on that point? Do I need to specify the parentkey attribut >specialy? how can I query this DB? Roger Binns is right. Just another hint: Search for "adjacency model" and "nested set", which are the two most common models. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] retrieval of blobs stored in sqlite
On Tue, 21 Apr 2009 08:02:45 + (GMT), Martin Pfeifle <martinpfei...@yahoo.de> wrote: >Hi, >I have a question regarding the retrieval of BLOBs. >Assume you have a table mytable(id, blob) and the page size is 1k. >If we now carry out an sql-query like "select blob from mytable >where id=4711" and the blob size is 100k. >Am I then correct that the pager asks 100 times for a page of >size 1k (going through the linked list of overflow pages) >and that in whatever virtual file system, we do 100 times >a seek operation to the currently requested page. >Is this correct or am I here mistaken? >Best Martin That's correct, though the number of seeks might be lower because some (many) of those overflow pages will be sequential, depending on the fragmentation rate. I think you would benefit from a larger page size. Run benchmarks to find the optimum for your environment and application. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite syntax railroad diagrams
On Tue, 21 Apr 2009 12:04:58 -0400, Andrey Fedorov <anfedo...@gmail.com> wrote: >Hi All, > >Does anyone know how the railroad-style syntax diagrams on this page were >made? > >http://sqlite.org/syntaxdiagrams.html According to From: "D. Richard Hipp" <d...@hwaci.com> Date: Mon, 20 Apr 2009 06:53:37 -0400 : http://wiki.tcl.tk/21708 >They're very nice :) > >Cheers, >Andrey -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] create the trigger to delete the data from other database
On Wed, 22 Apr 2009 10:33:18 -0700 (PDT), Joanne Pham <joannekp...@yahoo.com> wrote: >Hi All, >Can we have the trigger to delete to data from different database? >My application has 2 databases and when ever the application is >deleting the rows in one of tables in DB1 I would like to have a >trigger to delete the rows in table in DB2. >Is this possible? >Thanks, >JP I added this question and the answer to the SQLite wiki FAQ: http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq Thanks drh for the text, I took it from: [sqlite] Foreign Constraint Triggers Across Attached Databases (2008-01-24 11:56:16 UTC). Cheers, -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to exit from sqlite shell?
On Mon, 4 May 2009 14:00:45 -0400, "D. Richard Hipp" <d...@hwaci.com> wrote: > >On May 4, 2009, at 1:44 PM, Sam Carleton wrote: > >> prefix with a period: >> >> .exit > >Yes. Also ".quit" or ".q" or Control-D (on most Unix systems > - I don't know if Control-D works on windows) Control-D doesn't work in sqlite3.exe on windows, Control-C does, both in the windows shell (CMD.EXE) and in msys (mingw32) bash. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to exit from sqlite shell?
On Mon, 4 May 2009 15:01:26 -0400, Pavel Ivanov <paiva...@gmail.com> wrote: >In windows shell Control-Z should be equivalent of Control-D on Unix >(it sends EOF to stdin). Yes, you're right. Control-Z, Return does it. The Return key is required to terminate the line editor. On Unix the Control-D by itself is enough. >Pavel -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to check the healthy of database and the indexes of the tables
On Tue, 5 May 2009 13:02:13 -0700 (PDT), Joanne Pham <joannekp...@yahoo.com> wrote: >Hi All, > I had the database and one of the index is >not good condition. Every time I use the >index by select ... group by .. the result >only return few rows and the message print >out that "database disk image is malformed". > Is there any command to check if the index >or database in good condition. PRAGMA integrity_check; http://www.sqlite.org/pragma.html#debug >Thanks, >JP -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL error: database disk image is malformed
On Tue, 5 May 2009 16:55:42 -0400, Pavel Ivanov <paiva...@gmail.com> wrote: >Is it just me or somebody else is >seeing too that the sql statement > "select blobid, fbid from sig group by peerid" >is invalid and shouldn't be >executed or prepared at all? You are right, it doesn't make sense. @Joannek: When using group by, your select columns can only use aggregate functions and the columns you group by. Perhaps you meant to use ORDER BY here ? >Pavel -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL error: database disk image is malformed
On Wed, 6 May 2009 00:40:22 -0500, "Jay A. Kreibich" <j...@kreibi.ch> wrote: >On Tue, May 05, 2009 at 11:46:38PM +0200, Kees Nuyt scratched on the wall: >> On Tue, 5 May 2009 16:55:42 -0400, Pavel Ivanov >> <paiva...@gmail.com> wrote: >> >> >Is it just me or somebody else is >> >seeing too that the sql statement >> > "select blobid, fbid from sig group by peerid" >> >is invalid and shouldn't be >> >executed or prepared at all? >> >> You are right, it doesn't make sense. > > It doesn't make a lot of sense, but it is still valid. > >> @Joannek: When using group by, your select columns can only >> use aggregate functions and the columns you group by. > > "should only", not "can only." SQLite will happily execute that > statement. I stand corrected. >The results are unlikely to be useful, however. Indeed. We had a discussion before about generating an error in these cases. I wouldn't mind, but I'm sure it would break a lot of code. > When SQLite is asked to output a column that is not aggregated or > grouped, the returned value for that column is simply the value > of the last found row in the group. I suspect this is the same for > the grouped columns as well, they just happen to always be the same. > > -j -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 source code modification
On Wed, 6 May 2009 20:59:06 +1000, Maria <koal0...@gmail.com> wrote: >Hi, I would like to modify sqlite source code. >Before, start of modification, I wanted to print 'hello world!' message from >'sqlite3_initialize()' routine. > >I've downloaded sqlite-amalgamation-3.6.13.tar.gz ><http://www.sqlite.org/sqlite-amalgamation-3.6.13.tar.gz>on ubuntu system. >then, I followed the install step, such as: > >>./configure >>make >>make install > >After I install it successfully, I opened the 'sqlite3.c' file and found the >'sqlite3_initialize()' routine and add 'fprintf(stdout, "hello world!\n");' >then, I recompiled. >> make > >and run the sqlite >>sqlite3 test.db >>.tables >>.read createStudent.sql > >etc.. >I thought when I start the sqlite3, it would call initialize routine and >print 'hello world'. but it dosen't. >I also put some printing message in 'sqlite3StartTable()' and >'sqlite3EndTable()' then recompiled it and run create or drop table >statement. > >Two days ago, actually, I could print messages by above way. But since >yesterday, it's never printed anything. I even reinstalled my os system. >I am pretty much confused with it. Could anyone give me some advice, please? In your shell, type: which sqlite3 Does the result point to the result of yur compilation of sqlite3 ? >Thanks so much. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Increasing output
On Wed, 6 May 2009 19:13:29 +0200, Daniel Wolny <dan...@szelka.net> wrote: >Hi, >Is possible to increase output of -column? > >1 nightwalker nightwalker.szelka.net 1 >3 nightwalker stolezka.pl 1 >4 nightwalker czteroipolkilogramowya 1 >259 nightwalker satan.edu.pl 1 >260 nightwalker prison.net.pl 1 > >czteroipolkilogramowya should be displayed as czteroipolkilogramowyarbuz.pl In the sqlite command line tool: .width 10 13 30 3 >I need -column sorting type. I'm not sure what you mean here. Guessing: To sort by the third column of your result set, use: SELECT * FROM mytable ORDER BY name_of_third_column; or SELECT * FROM mytable ORDER BY 3; >Thanks in advance. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite in shell loop
On Wed, 6 May 2009 23:01:24 +0200, Daniel Wolny <dan...@szelka.net> wrote: >Hi, >Is possible to use sqlite in shell for loop? > >eg. >#!/bin/sh > >for i in `sqlite db "SELECT smt FROM db"` >do > echo "$i" DUPA >done > >I want to act sqlite like any other command in for loop, i mean one >record from db as a one iteration, above will display: > >first1 first2 >second1 second2 DUPA > >I want something like this: >first 1 first2 DUPA >second1 second2 DUPA It should be possible, sqlite sends its output to stdout, so it works like any other unix utility. Just give it a go. Experiment. And enjoy. >Thanks in advance -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3 tables hidden from program to program
On Wed, 6 May 2009 14:13:46 -0700 (PDT), rajyalakshmi bommaraju <rbommar...@yahoo.com> wrote: >Hi, > I started using sqlite3 recently on Ubuntu. I came > across an issue with the database that, I was able to > create database and table from commandline > successfully, I also inserted couple of rows, no > problem. When I tried to open database from C program, > It is fine but I cant access the table. It says that > the table doesnt exist ,I get error when I try to query > from the table. I had to recreate the table from the C > Program then I can insert or read from the table. It > looks like the tables are not global and are hidden > from one program to other. > > Please throw some light on this one. What can I do to > make them accessible from every interested program on > the machine. Did you use the /path/databasefilename parameter on the sqlite command line? As in: # sqlite3 my.db See also: http://www.sqlite.org/quickstart.html If you leave out the databasefilename, sqlite uses an in-memory database, which is destroyed as soon as you leave/quit/exit the command line tool program. >Thanks >rb -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite in shell loop
On Wed, 6 May 2009 23:24:00 +0200, Daniel Wolny <dan...@szelka.net> wrote: >2009/5/6 Kees Nuyt <k.n...@zonnet.nl>: >> On Wed, 6 May 2009 23:01:24 +0200, Daniel Wolny >> <dan...@szelka.net> wrote: >> It should be possible, sqlite sends its output to stdout, so >> it works like any other unix utility. >> >> Just give it a go. Experiment. >> And enjoy. >> > >It doesn't work to me: > >#!/bin/sh > >HANDLER=`sqlite -noheader /root/adm/var/database/vhosts "SELECT * FROM >vhosts WHERE login='nightwalker';"` > >for i in "$HANDLER" >do >echo "$i" dupa >done > >Result: >1|nightwalker|nightwalker.szelka.net|1 >3|nightwalker|stolezka.pl|1 >4|nightwalker|czteroipolkilogramowyarbuz.pl|1 >259|nightwalker|satan.edu.pl|1 >260|nightwalker|prison.net.pl|1 dupa That's a shell problem. You can either pick another separator in sqlite or change your shells' IFS. Pavel Ivanovs remarks are relevant as well. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie trying to list resultSet with C
On Fri, 8 May 2009 16:26:20 +0100, Nuno Magalhães <nunomagalh...@eu.ipp.pt> wrote: >Greetings. > >I've managed to compile the example, after installing the amalgamation >and using -lsqlite3 in gcc, otherwise it'll complain about undefined >references. > >I can't figure out how to read a simple result set. I know i shoud use >sqlite3_exec and/or sqlite3_step and i'm required to have a >sqlite3_stmt* somewhere, but i can't find good examples and lots of >the ones i find use sqlite3_prepare_v2, which i think is deprecated >for SQLite3... > >Can someone please give me some nice RTFM links will good basic >tutorials for the C API? Ones that include the aforementioned task >preferably ;) http://www.sqlite.org/cvstrac/wiki , more specifically: http://www.sqlite.org/cvstrac/wiki?p=SimpleCode Quickstart C code for executing any SQL against an SQLite database. Very basic but fully functional nevertheless. http://www.sqlite.org/cvstrac/wiki?p=SampleCode Example C code for creating / writing to / reading from a database. >TIA, >Nuno Magalhães -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple counts between two tables
On Sun, 10 May 2009 13:51:29 -0700 (PDT), ckeen <ste.fied...@googlemail.com> wrote: > >Hi, I'm trying to count two different column combinations using two tables. >Table one (tag_name) contains the 'id' and the 'name' for a tag. Table two >is stuffed with entries that links those tags with different applications >and their contents. >Now I would like to select how often each tag is used in each application. >Some of my previous tries worked fine (using JOIN ON) – but only one COUNT >was possible. Now I'm trying to get a statement to work which returns me the >tag amounts for both apps. > >tag_name >id | tag > >1 | sql >2 | xml >3 | foo > >tag_link >id | app | app_id | tag_id > >1 | d| 331 | 2 >2 | t | 49 | 1 > >Here is my current statement: >SELECT > tag_name.id, > (SELECT COUNT(*) FROM tag_link WHERE tag_link.tag_id = tag_name.id AND >tag_link.app = 't') AS cntTwt, > (SELECT COUNT(*) FROM tag_link WHERE tag_link.tag_id = tag_name.id AND >tag_link.app = 'd') AS cntDel >FROM > tag_name >GROUP BY > tag_name.id sqlite_version():3.6.13 CREATE TABLE tag_name ( id INTEGER PRIMARY KEY, tag TEXT ); CREATE TABLE app_name ( id INTEGER PRIMARY KEY, app TEXT ); CREATE TABLE tag_link ( app_id INTEGER, tag_id INTEGER, PRIMARY KEY (app_id,tag_id) ); INSERT INTO tag_name values (1,'sql'); INSERT INTO tag_name values (2,'xml'); INSERT INTO tag_name values (3,'foo'); INSERT INTO app_name values (30,'a'); INSERT INTO app_name values (39,'b'); INSERT INTO app_name values (49,'t'); INSERT INTO app_name values (331,'d'); INSERT INTO tag_link values (331,1); INSERT INTO tag_link values (331,2); INSERT INTO tag_link values (49,1); INSERT INTO tag_link values (30,1); INSERT INTO tag_link values (39,2); INSERT INTO tag_link values (331,3); INSERT INTO tag_link values (49,3); SELECT app_name.id,app,COUNT(tag_link.tag_id) AS nrtags FROM tag_link INNER JOIN app_name ON (app_name.id = tag_link.app_id) GROUP BY tag_link.app_id; app_name.id|app_name.app|nrtags 30|a|1 39|b|1 49|t|2 331|d|3 >The parser returns no error, only seems to freeze. Tipps, hints – all kind >of advice. >sincerely, ckeen Hope this helps. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple counts between two tables
On Sun, 10 May 2009 15:09:01 -0700 (PDT), S Fiedler
<ste.fied...@googlemail.com> wrote:
>
>Hi Kees,
>
>thanks for your help. Thats a neater way than I structured my JOIN version
>before. But my goal is to have all tag COUNTs for each application in one
>result row + id and name of the tag. Like:
>
>tag-id | tag-name | count_app_t | count_app_d
>-
>1 | sql | 9| 2
>2 | xml| 61 | 0
>3 | foo | 47 | 826
>
>Until now no 'JOIN construction' allowed more than one COUNT. Thats why I
>tried out the SELECT (SELECT …), (SELECT) way – which didn't though errors,
>but produced the freeze of the script.
Aha, I see, you mean a pivot report.
That can't be easily done in plain SQL.
What is weird in your example, is that the same application
('d') uses the same tag more then once. Is that on purpose?
In other words, it's not completely clear to me what you are
trying to accomplish.
>Regards,
>-steffen
>
>
>Kees Nuyt wrote:
>>
>> On Sun, 10 May 2009 13:51:29 -0700 (PDT), ckeen
>> <ste.fied...@googlemail.com> wrote:
>>
>>>
>>>Hi, I'm trying to count two different column combinations using two
>tables.
>>>Table one (tag_name) contains the 'id' and the 'name' for a tag. Table two
>>>is stuffed with entries that links those tags with different applications
>>>and their contents.
>>>Now I would like to select how often each tag is used in each application.
>>>Some of my previous tries worked fine (using JOIN ON) – but only one COUNT
>>>was possible. Now I'm trying to get a statement to work which returns me
>the
>>>tag amounts for both apps.
>>>
>>>tag_name
>>>id | tag
>>>
>>>1 | sql
>>>2 | xml
>>>3 | foo
>>>
>>>tag_link
>>>id | app | app_id | tag_id
>>>
>>>1 | d| 331 | 2
>>>2 | t | 49 | 1
>>>
>>>Here is my current statement:
>>>SELECT
>>> tag_name.id,
>>> (SELECT COUNT(*) FROM tag_link WHERE tag_link.tag_id = tag_name.id AND
>>>tag_link.app = 't') AS cntTwt,
>>> (SELECT COUNT(*) FROM tag_link WHERE tag_link.tag_id = tag_name.id AND
>>>tag_link.app = 'd') AS cntDel
>>>FROM
>>> tag_name
>>>GROUP BY
>>> tag_name.id
>>
>>
>> sqlite_version():3.6.13
>> CREATE TABLE tag_name (
>> id INTEGER PRIMARY KEY,
>> tag TEXT
>> );
>> CREATE TABLE app_name (
>> id INTEGER PRIMARY KEY,
>> app TEXT
>> );
>> CREATE TABLE tag_link (
>> app_id INTEGER,
>> tag_id INTEGER,
>> PRIMARY KEY (app_id,tag_id)
>> );
>> INSERT INTO tag_name values (1,'sql');
>> INSERT INTO tag_name values (2,'xml');
>> INSERT INTO tag_name values (3,'foo');
>>
>> INSERT INTO app_name values (30,'a');
>> INSERT INTO app_name values (39,'b');
>> INSERT INTO app_name values (49,'t');
>> INSERT INTO app_name values (331,'d');
>>
>> INSERT INTO tag_link values (331,1);
>> INSERT INTO tag_link values (331,2);
>> INSERT INTO tag_link values (49,1);
>> INSERT INTO tag_link values (30,1);
>> INSERT INTO tag_link values (39,2);
>> INSERT INTO tag_link values (331,3);
>> INSERT INTO tag_link values (49,3);
>>
>> SELECT app_name.id,app,COUNT(tag_link.tag_id) AS nrtags
>> FROM tag_link
>> INNER JOIN app_name ON (app_name.id = tag_link.app_id)
>> GROUP BY tag_link.app_id;
>>
>> app_name.id|app_name.app|nrtags
>> 30|a|1
>> 39|b|1
>> 49|t|2
>> 331|d|3
>>
>>
>>>The parser returns no error, only seems to freeze. Tipps, hints – all kind
>>>of advice.
>>>sincerely, ckeen
>>
>> Hope this helps.
--
( Kees Nuyt
)
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite as a FIFO buffer?
_SSN% %JOB_TSN% %JOB_rc%
%JOB_endmsg%>>log\log.txt
goto DISPNEXT
:DISPDONE
call :GETDT
echo %MYDATE% %MYTIME% 9 %0 %PAR_SSN%
echo %MYDATE% %MYTIME% 9 %0 %PAR_SSN% >>log\log.txt
goto Z
::
:: Reset a TSN from status A to W
:RESET
set SQL=UPDATE jobs SET status = 'W' WHERE TSN=='%1' AND status IN
('A','I','T','R');
%GNU_ECHO% "%SQL_PFX%%SQL%" | %SQLITE% %PAR_SDB%
if errorlevel 1 goto SQLERR
set PAR_RESTART=
goto Z
::
:: level 0 MAIN entrypoint
:: dispatch [RESTART=TSN#] [SSN=SSN#] [SDB=jobdbpathfile]
::
::
:MAIN
call \data\opt\cfg\setenv.cmd
call \data\opt\cfg\setdir.cmd
set SQL_PFX=.echo off\n.bail on\n.timeout 1000\n
cd /D %0\..
call \data\opt\cfg\%cfg%\setdrives.cmd >log\drives.txt
if errorlevel 1 goto P01
:: reset all possible parameters
for %%p in (RESTART SDB SSN) do set PAR_%%p=
:: set defaults
:: - schedule serial number (TSN is fetched from the job database)
set PAR_SSN=
:: - job database
set PAR_SDB=%DRIV6%\data\opt\db\li\job.db3
:GETPAR
if "%1"=="" goto PROCESS
if "%2"=="" goto P02
set PAR_%1=%2
shift
shift
goto GETPAR
:PROCESS
set PAR_ >log\SSN#%PAR_SSN%.txt
set DRIV >>log\SSN#%PAR_SSN%.txt
if DEFINED PAR_RESTART call :RESET %PAR_RESTART% >>log\SSN#%PAR_SSN%.txt
set PRV_TSN=
echo %MYDATE% %MYTIME% 1 %0 %PAR_SSN% %PAR_SDB% >>log\log.txt
echo %MYDATE% %MYTIME% 1 %0 %PAR_SSN% %PAR_SDB% >>log\SSN#%PAR_SSN%.txt
:: dispatcher loop, one task at a time
call :DISPATCH >>log\SSN#%PAR_SSN%.txt 2>&1
if "%PAR_SSN%"=="" goto Z
@cls
@exit /b 0
:: MAIN Environment and Parameter errors
:P01
echo Can't get all required driveletters.
goto R
:P02
echo Parameters must be specified as pairs 'name value' or 'name=value'
echo dispatch [RESTART=TSN#] [SSN=SSN#] [SDB=jobdbpathfile]
goto R
:: Dispatcher errors
:R
PROMPT $P$G
exit /B 1
:Z
@echo off
=== php fragment to create a job with or without parameters
=== it's part of a class which extends PDO
=== I prefer php_pdo_sqlite_external
function enter_job($userid,$cmnd,$parlist,$ntuid,$ntpsw,$jobprio = 8){
$msg = '';
/* Execute a prepared statement by passing an array of values
--> */
$sql = 'INSERT INTO jobs (userid,cmnd,pars,jobprio) VALUES
(:userid,:cmnd,:pars,:jobprio)';
$stjob = $this->prepare($sql);
$this->beginTransaction();
$stjob->execute(array(':userid' => $userid, ':cmnd' => $cmnd,
':pars' => '@list', ':jobprio' => $jobprio));
/*
*
* This will accommodate up to 99 999 999 requests,
* then we have to reset by deleting the database.
* It will be rebuilt automatically
* 01234567 oO0
*/
$jobid = $this->lastInsertId();
$tsn = substr(1000 + $jobid,4,4);
if (isset($parlist)){
/* there are parameters */
$sql = 'INSERT INTO pars (jobid,partx) VALUES
(:jobid,:partx)';
$stpar = $this->prepare($sql);
if (is_array($parlist)){
/* we got a text array with params */
foreach ($parlist as $aval){
$stpar->execute(array(':jobid' =>
$jobid, ':partx' => $aval));
}
} else if (is_object($parlist)) {
/* we got a resultset from a query as paramlist
*/
while ($row = $parlist->fetch(PDO::FETCH_NUM)){
$stpar->execute(array(':jobid' =>
$jobid, ':partx' => $row[0]));
}
}
} else {
}
$this->commit();
/*
* ugly code to launch the dispatcher asynchronously using
* Windows schtasks.exe is left to the imagination of the reader
*/
}
--
( Kees Nuyt
)
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite as a FIFO buffer? (How make atomic?)
On Mon, 18 May 2009 12:17:25 -0700 (PDT), Allen Fowler <allen.fow...@yahoo.com> wrote: > >> >I have several CGI and cron scripts and that I would like coordinate via a > >> "First In >> >/ First Out" style buffer.That is, some processes are adding work >> >units, and some take the oldest and start work on them. >> > >> >Could SQLite be used for this? >> > >> >> For what it's worth, here you go. >> Perhaps you can borrow a few ideas from it. >> > > >Thank you for posting the code. >I'll try to look through it. > (Like I said, I've never used complex SQL before... and for me this is > complex.) >Can you point me to the part that takes care of making >an atomic removal of a task from the queue, such that >one and only one worker process can get access to a task? >That's what's got me stumped. It's not guaranteed here, I think. The code is used on a site with very low concurrency. My 'solution' has only one worker, the dispatcher. I use schtask.exe to schedule dispatchers, it was the only way I could find to run something on windows outside the context of Apache/PHP. (the at utility would have been better, but it wasn't available to my account profile). Every time a new job is submitted, any previously scheduled dispatchers (which don't run yet) are removed from the scheduler queue. Then the new dispatcher is scheduled to run. Once it starts, the dispatcher runs all waiting jobs it can find, one by one, and exits when all jobs are done. In hindsight I don't like my code that much ;) So I guess this doesn't solve your problem. On Linux/Unix, you could implement a similar dispatcher, which would be the only process which removes tasks from the sqlite queue and starts each task as a background job. >The simple solution would just create a race condition... i think: > >1) INSERT INTO status_table FROM SELECT oldest task in queue >2) DELETE task in queue > >Right? It might work fine if you wrap it in an exclusive transaction. >Thank you, >AF > > > >P.S. > >Am I correct to assume your code is a more flashed out version of what I was >trying to do before > >Table: task_log => (id, task_data, time_stamp) >Table: task_fifo = > (id, fk_task_log) >Table: task_status_log => (id, fk_task_log, status_code, time_stamp) > >And in psudo SQL: > >TRIGGER ON INSERT INTO task_log: >BEGIN >INSERT INTO task_fifo (fk_task_log) VALUES (NEW.id) >END; > >TRIGGER ON DELETE FROM task_fifo: >BEGIN > INSERT INTO task_status_log VALUES (OLD.fk_task_log, "CLAIMED") >END; > > >And then, again in psudo SQL, the worker does something like: > >DELETE 1 OLDEST FROM task_fifo; I don't think it is exactly the same. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] most efficient way to get 1st row
On Tue, 19 May 2009 11:26:31 -0400, Sam Carleton <scarle...@gmail.com> wrote: >Marco Bambini wrote: >> SELECT ... LIMIT 1; >> >Marco, Is this to say that adding the LIMIT 1 does make it more efficient? Not necessarily. Imagine a SELECT with an ORDER BY which makes SQLite sort the resultset before it can return the first row in the resultset. Need I say more? http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Outer Join question?
On Mon, 25 May 2009 23:14:50 +0200, Leo Freitag <leofrei...@netcologne.de> wrote: >Hallo, > >I have a table 'person' and a table 'group'. Every person can join none, >one or more groups. >No I want to select all persons except those who are member in group 1. >- Sounds simple, but not for me. This is an n:m relationship. If group has more attributes (columns) than just its number, you need a third table: person_group. Then join person with person_group where group_id != 1; The person_group table could be called membership, if you like. >Thanks in advance >Leo -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] create indexed view
On Tue, 26 May 2009 14:44:25 +0800, wying wy <joyousl...@gmail.com> wrote: >Hi > >May I know if we can create index on a View? You can't create an index on a VIEW. A VIEW can be seen as a stored SELECT statement. >Thanks in advance. >wying -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Deleting database content : SQLite3 API
On Sat, 30 May 2009 17:50:36 +0530, <souvik.da...@wipro.com> wrote: > >Yes , I understand that. Infact I was doing that >through a script during system startup. I wanted >to know whether SQLite provides any API to do the same. No, it doesn't. You could write it yourself: foreach $name in \ sql(SELECT name FROM sqlite_master WHERE type='table';) do sql(DELETE FROM $name;) done If you use a startup script, there is no need for the C API, you might as well do something like: sqlite3 dbfile .schema|sqlite3 dbfile.new or (if the database is not overly large) sqlite3 dbfile .dump|grep - v INSERT|sqlite3 dbfile.new You may want to add a few initialization PRAGMA's to the pipe. >Thanks and Regards, >Souvik >-Original Message- >From: sqlite-users-boun...@sqlite.org on behalf of John Stanton >Sent: Sat 5/30/2009 5:30 PM >To: General Discussion of SQLite Database >Subject: Re: [sqlite] Deleting database content : SQLite3 API > >An Sqlite database is just a file. Delete the file when you start >your program and when you open the database Sqlite will create a fresh >dne, a very low overhead process.. >. >souvik.da...@wipro.com wrote: >> Hello, >> >> Please let me know if we have an API in SQLite3 which allows me to >> retain the database but delete it's content at runtime. The problem I am >> facing is that : Every time I restart my system , I need to create the >> database. If the database exits already it's contents need to be >> deleted. The issue is that the contents of the database varies during >> one power ON - Power OFF cycle . As a result , after finding that the >> database already exits at the system startup, I cannot just drop the >> tables. ( As the table which are present in the existing data base is >> not known. ) >> >> I am using sqlite3wrapped C++ library APIs. >> >> Thanks and Regards, >> Souvik -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On Mon, 1 Jun 2009 04:38:37 -0700 (PDT), "Manasi Save" <manasi.s...@artificialmachines.com> wrote: >Hi, > >we are developing an application on android we are using SQLite Database >and on phone we are getting SQLiteException:no such table. but, it is >working fine on simulator. > >Can anyone provide any input on this? ASCII versus UTF-8 or UTF-16? -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on Indexing
On Mon, 1 Jun 2009 12:32:26 +0200, "Ralf" <ral...@ntschek.de> wrote: >Hello, >[>> ] considering a m:n relation a.id <- a.id,b.id -> b.id, is it due to >performance, advisable to put an index on a.id,b.id ? a_id,b_id should be unique in the relationship table, so you should make (a_id,b_id) the primary key to enforce that constraint. >Thanks >Ralf -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should we upgrade the SQLite to 6.6.14.2 from 3.59
On Mon, 1 Jun 2009 15:39:11 -0700 (PDT), Joanne Pham <joannekp...@yahoo.com> wrote: > I send this email to the group to ask the question > just in case if someone in group has done the > benchmark then it will save my time. You are the only one who can run that benchmark, because no one else knows your application, its databaseschema and its data. General benchmark results will tell you almost nothing about the effect on _your_ application. There have been some speed improvements between 3.5.9 and 3.6.14.2 but certainly nothing like a 4-fold improvement. If you are interested in what happened to sqlite, a weekly peek at http://www.sqlite.org/cvstrac/timeline is worth the effort. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how can we solve IF EXIST in SQLite
On Tue, 2 Jun 2009 03:36:46 -0700 (PDT), robinsmathew <robinsmat...@hotmail.com> wrote: > >hey thanx for the reply... u leave the things happening inside.. wat i jus >wanna do is i wanna insert a new row to a table >the table will be like this >stock_id PKproduct_id FK quantitystock_date >1 10001028-05-2009 >10001 1001 527-05-2009 > >and wen i insert a new row with values NULL, 1000, 15,30-05-2009 > >i dont want want it as a new recorde i jus want to update the first row coz >its also having the same product id i jus want set the quantity = 10+15 and >the date new date that is 30-05-2009 >and suppose if i insert row with different product_id it should be inserted >as it is.. Pseudocode: BEGIN; UPDATE stock_tab SET . WHERE stock_id = 1; if sqlite_error() INSERT INTO stock_tab SET (...) VALUES (...); endif COMMIT; -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Syntax to set the conflict resolution of a transaction
On Tue, 2 Jun 2009 08:40:01 -0300, Karl Brandt <brandk...@gmail.com> wrote: >I'm trying to set the conflict resolution of an entire transaction by >using the ON CONFLICT clause without success. > >I'm using the following syntax: > >BEGIN ON CONFLICT ROLLBACK; >INSERT INTO TableX (Id) Values (1); >INSERT INTO TableX (Id) Values (2); >INSERT INTO TableX (Id) Values (3); >COMMIT; > >But get the error (using 3.6.14.2): SQLITE_ERROR - near "on": syntax error > >I found that syntax at the mail archives: > >http://thread.gmane.org/gmane.comp.db.sqlite.general/1563 >http://thread.gmane.org/gmane.comp.db.sqlite.general/5200 >http://thread.gmane.org/gmane.comp.db.sqlite.general/2276 >http://thread.gmane.org/gmane.comp.db.sqlite.general/1562 > >I also tried the syntax found in the SQL wikipedia page: > >BEGIN; >[..] >IF ERRORS=0 COMMIT; >IF ERRORS<>0 ROLLBACK; > >Also no luck. > >Is there a way to set the conflict resolution for an entire transaction? It's not part of the syntax of BEGIN. http://www.sqlite.org/lang_transaction.html As far as I can tell you'll have to use it in every INSERT statement, which has implications for your program flow. http://www.sqlite.org/lang_insert.html http://www.sqlite.org/lang_conflict.html (You probably already read those pages, I included the links for the convenience of other readers) >Luiz -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Syntax to set the conflict resolution of a transaction
On Tue, 2 Jun 2009 10:35:12 -0300, Karl Brandt <brandk...@gmail.com> wrote: >2009/6/2 J. King <jk...@jkingweb.ca> >> >> On Tue, 02 Jun 2009 07:40:01 -0400, Karl Brandt <brandk...@gmail.com> >> wrote: >> >> > I'm trying to set the conflict resolution of an entire transaction by >> > using the ON CONFLICT clause without success. >> > >> > [...] >> > >> > Is there a way to set the conflict resolution for an entire transaction? >> >> Such a thing is not possible. You may specify a conflict resolution on a >> given statement (eg. 'INSERT OR ROLLBACK') or on a table (on PRIMARY KEY, >> NOT NULL and UNIQUE constraints), but not on a transaction. > >Thanks for the info. > >Let me explain the complete picture so someone can help me. > >I develop a wrapper around sqlite that tracks the changed records and >than save the changes to the database by building and executing a SQL >query (a transaction). > >Currently it executes the SQL and check the return value. >If the return value is different from SQLITE_OK it executes a >separated ROLLBACK command so another transaction can be started. > >The problem is that after the ROLLBACK command, sqlite3_errmsg will >return "no error", giving the user no clues of what happened. > >I tried INSERT OR ROLLBACK syntax but it will work only for >SQLITE_CONSTRAINT. I would need to handle also SQLITE_ERROR. > >So there's a way to check if a transaction failed (for constraint or >another error) and than rollback without clearing the error message >returned by sqlite3_errmsg? After a ROLLBACK; there is no error (ROLLBACK is succesful), so the error message will be cleared. You can use INSERT ON CONFLICT ABORT ... ; Catch the constraint error, fetch the sqlite3_errmsg() and ROLLBACK yourself. http://www.sqlite.org/lang_conflict.html (untested) >Thanks in advance. > >Luiz -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Before Update trigger question
On Tue, 2 Jun 2009 09:38:18 -0700 (PDT), Boris Ioffe
<bioff...@yahoo.com> wrote:
>
>Hello Gang,
>This is my first question on this mail list. I noticed that BEFORE UPDATE
>trigger goes off even for insert statements.
>
>My example:
>CREATE TRIGGER validate_players_update BEFORE UPDATE ON players
>WHEN (new.role in (1,2) and
>(select count(*) from players where table_group_id =
> new.table_group_id
>and table_id = new.table_id
>and role = new.role))
>
>BEGIN
>SELECT RAISE(FAIL, "1002: Can not sit player at this role at the
> table");
>END;
>
>
>INSERT INTO players (device_id,table_group_id,table_id,role ) VALUES((select
>device_id from registrations where mesg_token ="aa"), 1, 2 , 2);
>2009-06-02 10:43:36,086 SQLEngine->pysqlite2.dbapi2.IntegrityError
>Traceback (most recent call last):
> File "SQLEngine.py", line 39, in executeUpdate
>self.cur.execute(SQL, args)
>IntegrityError: 1002: Can not sit player at this role at the table
>
>
>Can someone please shed a light on this issue?
>Thanks,
>Boris
I couldn't reproduce your problem. See code below.
(By the way, you really shouldn't use double quotes for
string literals!)
The validate_players_update never fires.
The validate_players_insert does.
If I comment the validate_players_insert trigger out, no
trigger fires.
Please provide a script that demonstrates the problem.
It should run against the command line tool, like the SQL
below, so things aren't obfuscated by a wrapper.
sqlite_version():3.6.13 -- yeah, I should update.
CREATE TABLE players (
table_group_id INTEGER,
table_idINTEGER,
device_id INTEGER,
roleINTEGER
);
CREATE TABLE registrations (
mesg_token TEXT,
device_id INTEGER
);
CREATE TRIGGER validate_players_insert
BEFORE INSERT ON players
WHEN (new.role IN (1,2) AND
(SELECT count(*) FROM players
WHERE table_group_id = new.table_group_id
AND table_id = new.table_id
AND role = new.role))
BEGIN
SELECT RAISE(FAIL, '1001: Insert');
END;
CREATE TRIGGER validate_players_update
BEFORE UPDATE ON players
WHEN (new.role IN (1,2) AND
(SELECT count(*) FROM players
WHERE table_group_id = new.table_group_id
AND table_id = new.table_id
AND role = new.role))
BEGIN
SELECT RAISE(FAIL, '1002: Update');
END;
INSERT INTO registrations VALUES ('aa',1);
INSERT INTO registrations VALUES ('bb',2);
INSERT INTO players (device_id,table_group_id,table_id,role
) VALUES (
(SELECT device_id FROM registrations
WHERE mesg_token = 'bb')
, 1, 2, 2);
INSERT INTO players (device_id,table_group_id,table_id,role
) VALUES (
(SELECT device_id FROM registrations
WHERE mesg_token = 'aa')
, 1, 2, 2);
SQL error near line 38: 1001: Insert
--
( Kees Nuyt
)
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite programmed in C++
On Tue, 2 Jun 2009 21:30:51 +0200, Sylvain Pointeau <sylvain.point...@gmail.com> wrote: >... because I experienced C++ to be easier with the classes and resource >management via the destructor.I was just wondering why C++ is not used? > >was it for a performance issue? >or a compiler issue? What Virgilio said: : Because there are many platforms that : sqlite runs (and can run at some time) : that doesn't have a C++ compiler : available, but they always have a : C compiler. >or anything else? C is more portable than C++ (fewer dialects, more standardized). For ease of use on platforms with a decent C++ compiler, there are good C++ wrappers. So, there is a choice for application development. >I just read the Linus Torvalds comment on the C++ for Git >What do you think? > >Cheers, >Sylvain -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Before Update trigger question
On Tue, 2 Jun 2009 12:28:31 -0700 (PDT), Boris Ioffe
<bioff...@yahoo.com> wrote:
>
> Kees,
> Thank you very much for quick prototype. I will use single
> quotes from now on. It turns out Igor was right.
> I had another trigger.
Yes, always read Igor's replies first ;)
Triggers are very powerful, yet tricky. Nevertheless, I
think it's a good idea to use TRIGGERs (and CONSTRAINTs) as
much as possible instead of application code.
>CREATE TRIGGER insert_players_timeStamp AFTER INSERT ON players
>BEGIN
> UPDATE players SET create_ts = DATETIME('NOW', 'localtime')
> WHERE rowid = new.rowid;
>END;
>
>
> it ticked another update trigger. I found workaround by
> adding UPDATE OF clause for a specific field
>
>CREATE TRIGGER validate_players_update_role BEFORE UPDATE OF role ON players
>
>
>Now I even understand why it works.
>Thanks a lot,
You're welcome, have fun.
>-B
--
( Kees Nuyt
)
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how can we solve IF EXIST in SQLite
On Wed, 3 Jun 2009 00:42:53 -0700 (PDT), Harold Wood <hwoody2w...@yahoo.com> wrote: >you should use the insert or replace statement, >it inserts if the row doesnt exist, if the row >does exists then it updates the row. No, that doesn't fulfil the requirement, because quantity isn't incremented. >--- On Wed, 6/3/09, robinsmathew <robinsmat...@hotmail.com> wrote: > > >From: robinsmathew <robinsmat...@hotmail.com> >Subject: Re: [sqlite] how can we solve IF EXIST in SQLite >To: sqlite-users@sqlite.org >Date: Wednesday, June 3, 2009, 3:15 AM > > > >its showing an error near "if": syntax error > > >Kees Nuyt wrote: >> >> On Tue, 2 Jun 2009 03:36:46 -0700 (PDT), robinsmathew >> <robinsmat...@hotmail.com> wrote: >> >>> >>>hey thanx for the reply... u leave the things happening inside.. wat i jus >>>wanna do is i wanna insert a new row to a table >>>the table will be like this >>>stock_id PK product_id FK quantity stock_date >>>1 1000 10 28-05-2009 >>>10001 1001 5 27-05-2009 >>> >>>and wen i insert a new row with values NULL, 1000, 15, 30-05-2009 >>> >>>i dont want want it as a new recorde i jus want to update the first row >coz >>>its also having the same product id i jus want set the quantity = 10+15 >and >>>the date new date that is 30-05-2009 >>>and suppose if i insert row with different product_id it should be >inserted >>>as it is.. >> >> Pseudocode: >> BEGIN; >> UPDATE stock_tab SET . WHERE stock_id = 1; >> if sqlite_error() >> INSERT INTO stock_tab SET (...) VALUES (...); >> endif >> COMMIT; >> -- >> ( Kees Nuyt -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OT-ish] ResultSet size
On Sun, 7 Jun 2009 12:47:58 -0500, P Kishor <punk.k...@gmail.com> wrote: >2009/6/7 Nuno Magalhães <nunomagalh...@eu.ipp.pt>: >> Greetings, >> >> I'm using SQLite for an academic project, through Java's JDBC >> (sqlitejdbc-0.5.4.jar). After executing a simple select, i can iterate >> the ResultSet all the way (showing to output), no problem. The >> problem, which may be silly, is that i need to get a row count so i >> can initialize a variable. >> >> I tried using rs.getFetchSize() but it returns 0. This is the only >> method i could relate to "getting number of rows" from the method >> list. >> >> I tried rs.last(); but get "SQLException: ResultSet is >> TYPE_FORWARD_ONLY" and wouldn't be able to get back anyway. >> >> I tried iterating the set and using rs.isLast() but i get >> "SQLException: function not yet implemented for SQLite". >> >> I know this si more related to JDBC than SQLite, but maybe someone can >> give me a hint? > >Either run a SELECT Count(*) prior to running your full select query, >or run the SELECT query, iterate over it counting the records, find >the total number in the set, and then iterate over it and display as >you are doing now. Either way, you would have to do a two-pass. I >would prefer the two SELECTs, once for the Count() and second time for >the query, for small results sets ... keep in mind, SELECT Count() in >SQLite is not optimized. It has recently been optimized, but only for the form: SELECT count(*) FROM without WHERE or LIMIT clauses. http://www.sqlite.org/cvstrac/chngview?cn=6316 >For big result sets, I might want to just do >one mongo select and then count the results in memory. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compite with DSQLITE_THREADSAFE=1 but application has mulitple threads using the same connection
On Tue, 9 Jun 2009 12:06:44 -0700 (PDT), Joanne Pham <joannekp...@yahoo.com> wrote: > > >Hi All, >What was the problem with the SQLite library is builded >with DSQLITE_THREADSAFE=1 but the application is using >multiple threads with the same connection. >Thanks, >JP Joannek, I think this same issue was discussed very recently in the mailing list. You may want to consult the archives. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compite with DSQLITE_THREADSAFE=1 but application has mulitple threads using the same connection
On Tue, 9 Jun 2009 15:23:42 -0700 (PDT), Joanne Pham <joannekp...@yahoo.com> wrote: >Sorry Couldn't locate the email about Compite with> DSQLITE_THREADSAFE=1 bu the application has multiple >threads using the same connection? >Would you pleas direct me to any document that has this info. >Thanks, >JP As far as I can tell there is no problem with it, if there ever was a problem with it is has been solved. Perhaps you were thinking of this article: To: <sqlite-users@sqlite.org> Subject: Re: [sqlite] SQLite spawns multiple processes? From: "D. Richard Hipp" <d...@hwaci.com> Date: Fri, 15 May 2009 14:03:05 -0400 Of course you have to search the ticket database. http://www.sqlite.org/cvstrac/search?t=1=1 Any problem ever reported is registered there. > >From: Kees Nuyt <k.n...@zonnet.nl> >To: sqlite-users@sqlite.org >Sent: Tuesday, June 9, 2009 12:52:47 PM >Subject: Re: [sqlite] Compite with DSQLITE_THREADSAFE=1 but application has >mulitple threads using the same connection > >On Tue, 9 Jun 2009 12:06:44 -0700 (PDT), Joanne Pham ><joannekp...@yahoo.com> wrote: > >> >> >>Hi All, >>What was the problem with the SQLite library is builded >>with DSQLITE_THREADSAFE=1 but the application is using >>multiple threads with the same connection. >>Thanks, >>JP > >Joannek, > >I think this same issue was discussed very recently in the >mailing list. You may want to consult the archives. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE with inline view/ derived table
On Thu, 11 Jun 2009 20:17:59 +0200, Frank Naude <naud...@telkomsa.net> wrote: >Hi, > >I need some help getting this UPDATE to work with sqlite 3.3.8: > >UPDATE fud28_read >SET user_id=2, msg_id=t.last_post_id, last_view=1244710953 >FROM (SELECT id, last_post_id FROM fud28_thread WHERE forum_id=4 AND >last_post_date > 0) t >WHERE user_id=2 AND thread_id=t.id > >Error: near "FROM" - syntax error: HY000 > >Does sqlite support inline views/ derived tables within UPDATE >statements? Any suggestions on how to get it to work? Not directly, but you can update "the tables behind" a view with an INSTEAD OF trigger. The trigger can contain any update statement you like. http://www.sqlite.org/lang_createtrigger.html#instead_of_trigger >Best regards. > >Frank -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sql query with sqlite3_exec
On Fri, 12 Jun 2009 07:05:36 -0700 (PDT), sql_newbie <jedn...@yahoo.de> wrote: > >I have another question about sqlite3_exec : > >How can i interact with the database and save the result in a C string for >forther use. For example: > >sqlite3_exec( db, "SELECT FROM urls", NULL, NULL, ); > >How can i save the returned result-table in a C string for further use in >the program? Have a look at some sample code: http://www.sqlite.org/cvstrac/wiki?p=SimpleCode http://www.sqlite.org/cvstrac/wiki?p=SampleCode >Thanks. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database logic in TRIGGER and CONFLICT, or in software ?
On Sat, 13 Jun 2009 23:42:21 +0100, Simon Slavin <slav...@hearsay.demon.co.uk> wrote: >I'm writing an application which involves lots of relations between >tables. Seen from a high level, my application will have to enforce >lots of rules to ensure database integrity. Before I used SQLite I >would have enforced all these rules in my software. But SQLite has >lots of ways to do this itself, with ON CONFLICT, TRIGGERs, and stuff >like that. But I don't see any real reason to use these features, and >I'm concerned about how well I can document what each of them is there >for. > >I'm an experienced programmer and will have no problem enforcing the >rules in my software. On the other hand, SQLite does some of them >very neatly, with less code than I'd need in my application. On the >gripping hand, if my software fails to do an operation it knows why >and can generate a specific error message, whereas if SQLite hits a >CONFLICT my error message would have to list all the possible reasons >and let the user decide which one was the cause. That's a trade off you have to decide on for yourself. User input should be validated by the application anyway, so the most common errors will be handled by the application. Using CONSTRAINTs and TRIGGERs protects you against programming errors, I would . >Do any of you have experience with doing this ? Are there some >conclusive points which will make me decide immediately that I should >do it one way or the other ? I accept reasoned argument, URLs, >anecdotes, or anything else relevant. I tried to enforce consistency and integrity by implementing a "value domain" system in awk. The schema source uses domain names instead of types. They are simply substituted by the domain definition. The utility primes a new database, creates dictionary tables and registers domains, tables, columns, including the comments from the schema definition, together with dtcreated and dtmodified timestamps. It also keeps a log of all DDL and DML passed through it, loads .csv files by generating INSERT statements, trims values, and optionally analyses the datatypes, min and max values, and min and max length of the values. It focuses on creating (portentially large) databases in batch. No support for referential integrity. It's undocumented, and I don't have time to answer any questions about it, so it's not fit for publication. Snipped of such a schema: --[domains] longname = VARCHAR(64) -- long name alphanum_64 shortname = CHAR(8) -- identifier (userid, account, ...) longtext = CLOB-- text field of arbitrary length counter = INTEGER -- integer --[help] CREATE TABLE %OBJECT% ( -- hlpforshortname, -- knowledge domain hlpname longname, -- name or short description hlptext longtext, -- descriptive text PRIMARY KEY (hlpfor,hlpname) ON CONFLICT ABORT ); >By the way, the SQLite documentation is excellent but it's a little >short on examples (unless there are a treasure trove of them somewhere >I missed ?). How would I, for example, make SQLite refuse to delete >an account if any transactions are recorded for it ? Make up your own >schema for the two tables, as long at they're convincing. Referential integrity can be obtained with REFERENCES constraints (foreign key relations). SQLite parses the syntax but doesn't enforce them yet. But the sqlite3 command line tool has a command, .genfkey, which converts those constraints into TRIGGERs that implement them. http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers There's also a site that implements it: http://www.rcs-comp.com/site/index.php/view/Utilities-SQLite_foreign_key_trigger_generator >Simon. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database inserts gradually slowing down
On Tue, 16 Jun 2009 14:23:47 +, Jens Páll Hafsteinsson <j...@lsretail.com> wrote: > Yes, I'm perfectly aware of this and hence I > would expect the disk to be sweating like hell > running this test while the CPU is relatively > relaxed (given that sqlite is disk bound in > this case and not CPU bound). > > But this is not happening; neither the disk nor > the CPU are practically doing anything, which > is a bit strange. It's as if both the disk and > the CPU are waiting for each other or that > sqlite is playing 'nice' behind my back and > giving up the CPU when it shouldn't. Apart from seeks, the disk has to spin until the correct start sector is under the head. Then it can write a database page, perhaps a few database pages. There are a few parameters you can use to optimize this: - PRAGMA page_size - PRAGMA [default_]cache_size - the number of INSERTs per transaction - The schema: INDEX PRIMARY KEY on the first column instead of a non-unique index (if the application allows it) - load the database in order of index(es) Especially a non-unique index with low cardinality has a lot of overhead. >JP -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] search in archive
On Fri, 19 Jun 2009 13:56:52 -0400, "Rizzuto, Raymond" <raymond.rizz...@sig.com> wrote: > Is it possible to have a search feature for the > archive? Which archive? I'll assume you have 18 different databases and you want to search them in parallel. > I.e. rather than having to do a linear > search through 18 archives for an answer > to a question, have a google-like search > across all of the archives? Yes, make your application multithreaded, one thread for the user interface and 18 for databases. Every dbthread would open a different database. It will only really help if your system has multiple processor cores, and if the databases are each on a different disk. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3 data mode for emacs?
On Sun, 21 Jun 2009 10:01:22 -0700, Kelly Jones <kelly.terry.jo...@gmail.com> wrote: >Emacs' "forms mode" lets you edit a text file as though each line were >a database record. > >Is there a similar mode that lets you edit data inside an sqlite3 db? Not that I know of in Emacs. There are several database browsers for SQLite databases though, with an editable grid. I use Sqlite3explorer, SqliteSpy and SqliteManager. The last one is a Firefox add-on. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3 data mode for emacs?
On Sun, 21 Jun 2009 12:44:51 -0700, Kelly Jones <kelly.terry.jo...@gmail.com> wrote: >On 6/21/09, Kees Nuyt <k.n...@zonnet.nl> wrote: >> On Sun, 21 Jun 2009 10:01:22 -0700, Kelly Jones >> <kelly.terry.jo...@gmail.com> wrote: >> >>>Emacs' "forms mode" lets you edit a text file as though each line were >>>a database record. >>> >>>Is there a similar mode that lets you edit data inside an sqlite3 db? >> >> Not that I know of in Emacs. There are several database >> browsers for SQLite databases though, with an editable grid. >> >> I use Sqlite3explorer, SqliteSpy and SqliteManager. The last >> one is a Firefox add-on. > >Do any of these work in VT100 mode? I'm big on command-line stuff. No -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory leak with sqlite3_exec on qnx 6.4.1
On Fri, 26 Jun 2009 17:07:16 -0400, "Greg Morehead" <gmoreh...@premiumpower.com> wrote: > >If I close then reopen the database all my memory is recovered. > >Is this by design??? Yes, what you see is probably the page cache. >I was intending on keeping a connection open most of time. That's a good idea, for at least two reasons: - opening a connection has to parse the schema, and though it's fast code, it should be avoided. - the contents of the page cache aren't wasted, it may be re-used by subsequent statements. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] View error
On Mon, 29 Jun 2009 10:12:25 +0200, Bruno Carlus <b.car...@ipnl.in2p3.fr> wrote: >Hi, > >when I try to execute >SELECT num_id_cycle FROM vw_last_cycles_mapping WHERE num_cycle = 10 > >in a c function it issues a column does not exixt error for num_cycle >... but it works when I execute the same request in the sqlite3 shell ... > >vw_last_cycles is a view listing the 10 last inserted rows of a table. > >any idea ? Yes, you say you SELECT ... FROM vw_last_cycles , but the code above references vw_last_cycles_mapping. >Thanks, >Bruno. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
