Re: [sqlite] Complex Query
On 3/21/08, Derek Developer <[EMAIL PROTECTED]> wrote: > Hello, > > Thank you for your help with SQLite > > > Also I would really like to figure out how to respond to a particular thread > rather than starting a new thread. Is it possible from the digest? I have no idea. I don't subscribe to the digest. I find digests to be way more trouble than they are worth. If you change your setting from digest to individual messages, you can just reply to a thread by hitting reply. Nothing more fancy than that. > The only URL I have for posting is: > sqlite-users@sqlite.org That's all that we all have. > > Is the trick to make the subject lines match or something? Nope, there is no trick at all. In fact, the trick is to not use any trick. Just hit reply. In fact, don't change the subject line just to start a new thread. That hijacks the thread and messes up the threading for others. Hope this helps. > > > > P Kishor <[EMAIL PROTECTED]> wrote: > > On 3/19/08, Derek Developer wrote: > > > > (2nd attempt... bounced back for some reason...) > > > > In testing a my code I came across this example. > > Could someone help me understand what this syntax is doing please > > (from the Seinfeld demo database examples) > > > > ...m col > > ...h on > > ...w 20 17 6 23 6 > > ...e on > > > > Is this some form of typecasting? > > > All of the above commands are abbreviations of a dot command in the > sqlite3 shell tool. Please see the list below to figure out which is > which... > > sqlite> .help > .bail ON|OFF Stop after hitting an error. Default OFF > .databases List names and files of attached databases > .dump ?TABLE? ... Dump the database in an SQL text format > .echo ON|OFF Turn command echo on or off > .exit Exit this program > .explain ON|OFFTurn output mode suitable for EXPLAIN on or off. > .header(s) ON|OFF Turn display of headers on or off > .help Show this message > .import FILE TABLE Import data from FILE into TABLE > .indices TABLE Show names of all indices on TABLE > .load FILE ?ENTRY? Load an extension library > .mode MODE ?TABLE? Set output mode where MODE is one of: > csv Comma-separated values > column Left-aligned columns. (See .width) > html HTML > > code > insert SQL insert statements for TABLE > line One value per line > list Values delimited by .separator string > tabs Tab-separated values > tcl TCL list elements > .nullvalue STRING Print STRING in place of NULL values > .output FILENAME Send output to FILENAME > .output stdout Send output to the screen > .prompt MAIN CONTINUE Replace the standard prompts > .quit Exit this program > .read FILENAME Execute SQL in FILENAME > .schema ?TABLE?Show the CREATE statements > .separator STRING Change separator used by output mode and .import > .show Show the current values for various settings > .tables ?PATTERN? List names of tables matching a LIKE pattern > .timeout MSTry opening locked tables for MS milliseconds > .timer ON|OFF Turn the CPU timer measurement on or off > .width NUM NUM ... Set column widths for "column" mode > sqlite> > > > > This is how they are used > > > > > > SELECT f.name as food, e1.name, e1.season, e2.name, e2.season > > FROM episodes e1, foods_episodes fe1, foods f, > > episodes e2, foods_episodes fe2 > > WHERE > > -- Get foods in season 4 > > (e1.id = fe1.episode_id AND e1.season = 4) AND fe1.food_id = f.id > > -- Link foods with all other epsisodes > > AND (fe1.food_id = fe2.food_id) > > -- Link with their respective episodes and filter out e1's season > > AND (fe2.episode_id = e2.id AND e2.season != e1.season) > > ORDER BY f.name; > > > > SELECT e.name AS Episode, COUNT(f.id) AS Foods > > FROM foods f > > JOIN foods_episodes fe on f.id=fe.food_id > > JOIN episodes e on fe.episode_id=e.id > > GROUP BY e.id > > ORDER BY Foods DESC > > LIMIT 10; > > > > SELECT 1 IN (1,2,3); > > SELECT 2 IN (3,4,5); > > SELECT COUNT(*) FROM foods WHERE type_id IN (1,2); > > SELECT COUNT(*) FROM foods WHERE type_id > > IN (SELECT id FROM food_types WHERE name='Bakery' OR name='Cereal'); > > > > SELECT name, > > (SELECT COUNT(id) FROM foods_episodes WHERE food_id=f.id) count > > FROM foods f ORDER BY count DESC LIMIT 10; > > > > > > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Malformed database schema with SQLite version > 3.5.x
On Fri, 21 Mar 2008 08:54:22 +0100, you wrote: >I actually created all my views by means of "CREATE VIEW ... AS ...", and >(as Mr. Hipp said) these views was accepted from SQLite as "valid" SQL >statements. >The problem (was) that right now I couldn't open the DB anymore, due to the >malformed schema error. Ok, I understand now. >Thanks again >Marco -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple Row Updates
Use this sequence - sqlite3_prepare_v2 while not finished sqlite3_bind_xxx sqlite3_step until SQLITE_DONE sqlite3_reset repeat sqlite3_finalize The sqlite3_reset readies the compiled statement for binding with another value. Derek Developer wrote: > Just wanted to check that there is no "reset" statement that I can use with > UPDATE's. > > My understanding is that for multiple Row updates the complete sequence needs > to be executed for each row ie: > > "UPDATE mytable SET ' name=?, street=?, Phone=? WHERE ROWID=333" > Prepare > Bind the data > step > finalize > > then the next row must complete the same sequence ie: > "UPDATE mytable SET ' name=?, street=?, Phone=? WHERE ROWID=333" > Prepare > Bind the data > step > finalize > > Obviously I can do all these within a BEGIN/END TRANSACTION > > but was just wondering if there is any way to use the reset statement and > update the ROWID? > > This would save a lot of time for large numbers of rows. > > Testing with Seinfeld data from Michael Owens excellent book: > http://books.google.com/books?id=VsZ5bUh0XAkC=PA75=PA75=sqlite+seinfeld=web=u42Lep_3F7=A3whrQ0XJbW7DBQbPhyspKdHJuc=en > Download: > http://www.apress.com/book/downloadfile/2847 > > > (also would love to figure out how to respond to my own threads. I cannot > seem to find the link in the digests I am getting at my email account) > > > > > - > Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it > now. > ___ > 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
[sqlite] dot locking
I seem to recall a post from someone (drh?) stating that the Old School dot-locking routines one sees in the amalgamation have not yet been thoroughly tested and should not be used. Is this still true? Thanks, - Richard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiple Row Updates
Just wanted to check that there is no "reset" statement that I can use with UPDATE's. My understanding is that for multiple Row updates the complete sequence needs to be executed for each row ie: "UPDATE mytable SET ' name=?, street=?, Phone=? WHERE ROWID=333" Prepare Bind the data step finalize then the next row must complete the same sequence ie: "UPDATE mytable SET ' name=?, street=?, Phone=? WHERE ROWID=333" Prepare Bind the data step finalize Obviously I can do all these within a BEGIN/END TRANSACTION but was just wondering if there is any way to use the reset statement and update the ROWID? This would save a lot of time for large numbers of rows. Testing with Seinfeld data from Michael Owens excellent book: http://books.google.com/books?id=VsZ5bUh0XAkC=PA75=PA75=sqlite+seinfeld=web=u42Lep_3F7=A3whrQ0XJbW7DBQbPhyspKdHJuc=en Download: http://www.apress.com/book/downloadfile/2847 (also would love to figure out how to respond to my own threads. I cannot seem to find the link in the digests I am getting at my email account) - Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Formating SUM?
On 3/21/08, Gilles Ganault <[EMAIL PROTECTED]> wrote: > Hello > > I searched on the web site and "The definitive guide to SQLite", but > didn't find if it's possible to format the output from the SUM() > function: > > SELECT SUM(invoice_amount) AS Amount FROM invoice"; > > => With invoice_amount defined as TEXT, I'd like this number to be > formatted as "100.000,00', ie. using the dot as the thousands' > separator, and the comma as the decimal separator. If required, I can > turn this from TEXT to REAL, but I'd rather keep this column as TEXT. I don't think there is any built in string formatting function in SQlite, but you are free to roll your own using sqlite3_create_function(). See http://www.sqlite.org/c3ref/create_function.html > > Thank you. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Formating SUM?
Hello I searched on the web site and "The definitive guide to SQLite", but didn't find if it's possible to format the output from the SUM() function: SELECT SUM(invoice_amount) AS Amount FROM invoice"; => With invoice_amount defined as TEXT, I'd like this number to be formatted as "100.000,00', ie. using the dot as the thousands' separator, and the comma as the decimal separator. If required, I can turn this from TEXT to REAL, but I'd rather keep this column as TEXT. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Moving port from 3.5.1 to 3.5.7...
The closest memory implementation would be the default one. The other implementations that try to do their own pool management are unacceptable as memory system wide to too valuable to allow SQLite to allocate pool that it is not currently using. I am also fundamentally opposed to trying to outsmart the memory manager until someone can demonstrate a need to do so. I have yet to see a successful effort to do this in my environment. Back to the default implementation, the trouble is that there is too much heavy synchronization and it prevents me from using available high performance memory management primitives and available debugging support. Performance and space are critical factors. I'm working in the NT system it would be unreasonable of me to expect that any of your implementations to be well suited to that environment. That's why I found the SQLITE_OMIT_MEMORY_ALLOCATION exciting. I was fully expecting from the beginning that this would be an area that I would have to implement just like the VFS and mutex support. The difference is that now I have to make a few changes to the amalgamated source to do it rather than just #define a value. No problem. Thanks for your help. [EMAIL PROTECTED] wrote: > Mark Spiegel <[EMAIL PROTECTED]> wrote: > >> I'm looking to jump my code port forward from 3.5.1 to 3.5.7. >> >> Clearly I have some memory management work to do since >> SQLITE_OMIT_MEMORY_ALLOCATION support has been dropped. None of the >> existing allocation implementations look acceptable so I'll have to roll >> my own, >> > > What do you need that none of mem[12345].c provide? > > >> but that looks pretty straight forward. >> >> Two questions: >> >> 1) Has the VFS interface changed from 3.5.1 to 3.5.7? >> > > No. > > >> 2) Is the SQLITE_MUTEX_APPDEF #define still supported in the same manner >> from 3.5.1 to 3.5.7? (It appears that it is, but it never hurts to ask.) >> > > Yes. > > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Moving port from 3.5.1 to 3.5.7...
Mark Spiegel <[EMAIL PROTECTED]> wrote: > I'm looking to jump my code port forward from 3.5.1 to 3.5.7. > > Clearly I have some memory management work to do since > SQLITE_OMIT_MEMORY_ALLOCATION support has been dropped. None of the > existing allocation implementations look acceptable so I'll have to roll > my own, What do you need that none of mem[12345].c provide? > but that looks pretty straight forward. > > Two questions: > > 1) Has the VFS interface changed from 3.5.1 to 3.5.7? No. > 2) Is the SQLITE_MUTEX_APPDEF #define still supported in the same manner > from 3.5.1 to 3.5.7? (It appears that it is, but it never hurts to ask.) Yes. -- D. Richard Hipp <[EMAIL PROTECTED]> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Moving port from 3.5.1 to 3.5.7...
I'm looking to jump my code port forward from 3.5.1 to 3.5.7. Clearly I have some memory management work to do since SQLITE_OMIT_MEMORY_ALLOCATION support has been dropped. None of the existing allocation implementations look acceptable so I'll have to roll my own, but that looks pretty straight forward. Two questions: 1) Has the VFS interface changed from 3.5.1 to 3.5.7? 2) Is the SQLITE_MUTEX_APPDEF #define still supported in the same manner from 3.5.1 to 3.5.7? (It appears that it is, but it never hurts to ask.) Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Malformed database schema with SQLite version > 3.5.x
On Fri, Mar 21, 2008 at 10:12 AM, Noah Hart <[EMAIL PROTECTED]> wrote: > Suggestion to SQLite Developers ... Have PRAGMA integrity_check reparse > the SQL in sqlite_master, looking for errors. > > Regards, > > Noah I don't think that would actually help. It seems that this problem was caused by older versions of SQLite accepting certain invalid SQL syntax. Since the bad syntax was accepted by the older parser, it's not going to just start rejecting the database. What I would recommend is a twofold change: 1. Improve the error message -- perhaps display the table/view name and/or full SQL that it couldn't parse 2. When PRAGMA writable_schema=ON, treat schema errors as warnings and simply disallow access to the affected tables/views. This shouldn't cause any compatibility problems because nobody should be using writable_schema anyway. -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tables dropped (mac)
=== Re: Maybe I left off the .db? Sqlite doesn't enforce filename restrictions -- you can name the file simply "test" if you prefer. Re: Am I right that you can only have one database file per sqlite installation? I'm not certain I understand correctly, since sqlite isn't really "installed" per se, but no, you may have lots of sqlite database files on any given system. Re: So there is no way to change database files? You'll want to read about the ATTACH command. This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking. This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tables dropped (mac)
Well I missed that in the doucmentation, thanks. I just re-read it. Although at the prompt I thought I did put test as the filename. Maybe I left off the .db? So this should be to get the prompt and save the fiel. sqlite3 test.db > Am I right that you can only have one database file per sqlite installation? So there is no way to change database files? Tia, On 3/21/08 6:47 AM, "Griggs, Donald" <[EMAIL PROTECTED]> wrote: > Regarding: > I created some tables at the sqlite prompt using the terminal > window on a Mac. I checked with .tables and .schema and they were > created and I inserted data. I closed the terminal window and went back > later. At the sqlite prompt there were no tables. I tried .tables and > .schema and nothing. > So what happened to my tables? > > -- > Hi Janis, > > Is it possible that when you ran sqlite from the command prompt, you did > not specify a filename for the database to be created? > > If you run "sqlite3" with no file specified it will create a temporary > database in RAM memory. > > > > > > This email and any attachments have been scanned for known viruses using > multiple scanners. We believe that this email and any attachments are virus > free, however the recipient must take full responsibility for virus checking. > This email message is intended for the named recipient only. It may be > privileged and/or confidential. If you are not the named recipient of this > email please notify us immediately and do not copy it or use it for any > purpose, nor disclose its contents to any other person. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Janis -- http://fpdownload.macromedia.com/pub/shockwave/cabs/flash/swflash. cab#version=8,0,0,0" width="300" height="190" id="votd" align="middle"> http://www.biblegateway.com/usage/votd/votd.swf; /> http://www.biblegateway.com/usage/votd/votd.swf; quality="high" bgcolor="#ff" width="300" height="190" name="votd" align="middle" allowScriptAccess="sameDomain" type="application/x-shockwave-flash" pluginspage="http://www.macromedia.com/go/getflashplayer; /> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tables dropped (mac)
What was your filename? Janis Rough wrote: > I created some tables at the sqlite prompt using the terminal window on a > Mac. I checked with .tables and .schema and they were created and I > inserted data. I closed the terminal window and went back later. At the > sqlite prompt there were no tables. I tried .tables and .schema and > nothing. > So what happened to my tables? > > Thanks, > ___ > 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
[sqlite] added ticket 3007 as a feature request
Ticket: 3007 Add, #define SQLITE_OPEN_OMIT_JOURNAL 0x8000 Test this flag and set the omit_journal parameter to Btree factory... To cause sqlite to omit journal creation. Obviously this has serious impact to a databases recoverability. But when the Durability component of ACID is not needed it should provide a nice performance boost! I did a quick one off on the code hard coding the omitFlag to one for the btreeFactory call. All appeared ok, but I didn't run the regression testing, I'm guessing many of these would fail due to the lack of journalling. In my particular case, I have many small db files that have data. These are "batched" into a db file. If the process fails the in progress batch will simply be overwritten and re-created. Hence it is an all or none approach and does not need any recoverability. Thanks for considering this! Ken ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how to reply?
Sorry for this post, but I can't find a way to reply to a post. It seems that I can only reply via email to the person who answered me. How to reply and see my answer on this site? thanks - Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] group by optimization
L B <[EMAIL PROTECTED]> wrote: > Hi all, > I have this query: > > select CATALOGUES.idcatalogue, CATALOGUES.type, PERSONS.surname, > max(HISTORY.startingfrom) as maxdate > FROM PERSONS > INNER JOIN CATALOGUES > ON CATALOGUES.idperson = PERSONS.idperson > INNER JOIN HISTORY > ON HISTORY.idcatalogue = CATALOGUES.idcatalogue > WHERE CATALOGUES.active='y' > group by PERSON.surname, CATALOGUES.idcatalogue, CATALOGUES.type > > with indexes on: > > PERSONS.surname > PERSONS.idperson > CATALOGUES.active > CATALOGUES.idcatalogue > CATALOGUES.type > HISTORY.idcatalogue > HISTORY.startingfrom Try adding an index on CATALOGUES.idperson Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] group by optimization
Hi all, I have this query: select CATALOGUES.idcatalogue, CATALOGUES.type, PERSONS.surname, max(HISTORY.startingfrom) as maxdate FROM PERSONS INNER JOIN CATALOGUES ON CATALOGUES.idperson = PERSONS.idperson INNER JOIN HISTORY ON HISTORY.idcatalogue = CATALOGUES.idcatalogue WHERE CATALOGUES.active='y' group by PERSON.surname, CATALOGUES.idcatalogue, CATALOGUES.type with indexes on: PERSONS.surname PERSONS.idperson CATALOGUES.active CATALOGUES.idcatalogue CATALOGUES.type HISTORY.idcatalogue HISTORY.startingfrom Tables contains about: PERSONS 70 records CATALOGUES 70 records HISTORY 4 million records My query is extremely slow (about 90 seconds). If I exclude the group by and the max functions, it is extremely fast. So problem should be in group by. How can I improve the performance? Thank you very much for your explanation. - Looking for last minute shopping deals? Find them fast with Yahoo! Search. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] endless loop example
Just to quantify a cartisian join is a join operation that returns the product of the rows. So in the example given episodes 8 foods100 food_episodes 800 episodes e1, foods_episodes fe1, foods f, episodes e2, foods_episodes fe2 Result rows = 8*800*100*8*800 = 4,096,000,000 rows... (over 4 billion rows!) As you can see Cartesian joins get out of hand very quickly. P Kishor <[EMAIL PROTECTED]> wrote: On 3/21/08, Derek Developer wrote: > In trying to break my code with the seinfeld database examples, I found this. Derek, You are going to get much better help from the list, not to mention that you will probably get further with your "code breaking," if you provide some more background to your question. For example, what on earth is this "seinfeld database example" that you are talking about? > SELECT f.name as food, e1.name, e1.season, e2.name, e2.season FROM episodes > e1, foods_episodes fe1, foods f, episodes e2, foods_episodes fe2 > Why does this put SQLite into an endless loop? Probably because there is no JOIN clause causing a cartesian join across 5 tables. > (I am not using the shell tool, just preparing the statement as is and > stepping throug the rows) > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Major memory leak
My SQLite library is built from the single translation unit sqlite.c/sqlite.h. That file contains the version number 3.3.17. I do not have valgrind, but circumstantial evidence that this is a SQLite problem is strong. When stepping through my code, I see that my application's memory jumps by over 2.5 megabytes when the sqlite3_step() method is called when using either the sorted query or the query using max(). The unsorted query doesn't show any memory jump. Also, the difference in memory consumption before this part of the code is executed and after it is left is the same size as the jump in memory when sqlite3_step() is called. RobR On 3/20/08, Ken <[EMAIL PROTECTED]> wrote: > It might be helpful to include the version of sqlite. > > Have you run your code through a memory analysis routine such as valgrind, to > validate that the leak is not occuring in your application code? > > > HTH, > Ken ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Malformed database schema with SQLite version > 3.5.x
Suggestion to SQLite Developers ... Have PRAGMA integrity_check reparse the SQL in sqlite_master, looking for errors. Regards, Noah -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of MarcoN Sent: Friday, March 21, 2008 12:54 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Malformed database schema with SQLite version > 3.5.x I actually created all my views by means of "CREATE VIEW ... AS ...", and (as Mr. Hipp said) these views was accepted from SQLite as "valid" SQL statements. The problem (was) that right now I couldn't open the DB anymore, due to the malformed schema error. Thanks again Marco ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New crashes with SQLite 3.5.7
"Peter Weilbacher" <[EMAIL PROTECTED]> wrote:
> On Thu, 20 Mar 2008 17:52:21 UTC, drh wrote:
>
> > Any additional information you can send, such as the size of
> > the database file at the point of failure, or the exact line
> > on which the problem occurs, will be appreciated. (I know the
> > bug report gives a line-number, but line numbers shift from
> > one amalgamation to another, and I don't know which amalgamation
> > you are using - I want the text of the line on which the problem
> > occurs.)
>
> I didn't know there were different amalgamations, I just picked the
> tarball from the SQLite download page...
Well, there is the one on the download page, obviously. But
you might also have pulled a different version of SQLite from
the CVS tree and built the amalgamation yourself. Many users
will sometimes pull down a standard amalgamation but add their
on private header comment to explain where the code came from -
thus throwing off the line numbering. If you can tell me that
the amalgamation in the bug report is *exactly* the same as the
one on the download.html page, that is all I need to know.
>
> Looking at the call stacks of the crashes that Shawn pointed to, they
> seem to be different between Windows and MacOSX. On Linux it doesn't
> happen.
I spent some time writing a whole bunch of new tests for the
Bitvec module in SQLite. It is a new module, so it immediately
comes under suspicion. But the module is used on *every* transaction
and millions of transactions are run by the SQLite test suite and
they all work perfectly on all platforms. And the Bitvec module
has its own set of unit tests which also work perfectly. And
yesterday, I wrote up a whole new set of Bitvec tests and they
all work perfectly too. I also observe that all the Bitvec tests,
and indeed all SQLite tests, work perfectly and report no errors
when running under valgrind.
But I also observe that line 22783 is common between the two
failures. I'll focus on writing new tests to further exercise
that line of code, and reanalyze the code looking for problems.
If you have additional failures, please send me stack traces,
as this might provide additional clues.
>
> Windows has this at the top:
> 0 sqlite3BitvecSet mozilla/db/sqlite3/src/sqlite3.c:22783
> return sqlite3BitvecSet(p->u.apSub[bin], i);
> 1 sqlite3BitvecSet mozilla/db/sqlite3/src/sqlite3.c:22800 if(
> aiValues[j] ) rc |= sqlite3BitvecSet(p, aiValues[j]);
> 2 sqlite3PagerDontRollback mozilla/db/sqlite3/src/sqlite3.c:27282
> sqlite3BitvecSet(pPager->pInJournal, pPg->pgno);
> [etc.]
>
> For MacOSX it is:
> 0 sqlite3BitvecSetmozilla/db/sqlite3/src/sqlite3.c:22769 if(
> p->iSize<=BITVEC_NBIT ){
> 1 sqlite3BitvecSetmozilla/db/sqlite3/src/sqlite3.c:22783 return
> sqlite3BitvecSet(p->u.apSub[bin], i);
> 2 allocateBtreePage mozilla/db/sqlite3/src/sqlite3.c:27282
> sqlite3BitvecSet(pPager->pInJournal, pPg->pgno);
>
> I pasted the code from the lines pointed to by the line number for the
> topmost entries, perhaps that helps.
>
--
D. Richard Hipp <[EMAIL PROTECTED]>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tables dropped (mac)
Regarding: I created some tables at the sqlite prompt using the terminal window on a Mac. I checked with .tables and .schema and they were created and I inserted data. I closed the terminal window and went back later. At the sqlite prompt there were no tables. I tried .tables and .schema and nothing. So what happened to my tables? -- Hi Janis, Is it possible that when you ran sqlite from the command prompt, you did not specify a filename for the database to be created? If you run "sqlite3" with no file specified it will create a temporary database in RAM memory. This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking. This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Tables dropped (mac)
I created some tables at the sqlite prompt using the terminal window on a Mac. I checked with .tables and .schema and they were created and I inserted data. I closed the terminal window and went back later. At the sqlite prompt there were no tables. I tried .tables and .schema and nothing. So what happened to my tables? Thanks, ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Use of two columns for a key and query on first clm.
order by id is not guaranteed. use 'order by id' and it will. Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Neville Franks Sent: Thursday, March 20, 2008 8:07 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Use of two columns for a key and query on first clm. Let me rephrase the question slightly. If I do select * from table where clm1='def'; then step through the results will I see all rows that include 'def'. The answer must be yes. The only issue is what order they will be in. If I want them ordered by clm2 then yes I'd probably need use ORDER BY. However in this specific example I would have thought the index order would be used, which is clm1+clm2 which is the same as using ORDER BY clm2. But I appreciate this isn't guaranteed. Friday, March 21, 2008, 11:41:52 AM, you wrote: JS> No, use ORDER BY JS> Neville Franks wrote: >> If I use two columns for a key (primary or separate index) and query >> just on the first column component will I always get back the first >> match in a set. For example. >> >> - >> create table mytable ( clm1 text collate nocase, clm2 text >> collate nocase, constraint mycs1 primary key( clm1, clm2 ) ); >> >> insert following: >> Clm1 Clm2 >> abc 123 >> abc 456 >> abc 789 >> def 123 >> def 456 >> def 789 >> >> select * from table where clm1='def'; >> - >> >> Will the returned row always be def - 123. ie. the first row for def? >> >> I've looked at the query plan for this select and it does use the >> index if clm1 alone is in the query and it appears to match on the >> first row. >> >> Also my tests indicate I do get back the first matching row. But I'd >> like confirmation if possible. >> -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.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
Re: [sqlite] Malformed database schema with SQLite version > 3.5.x
I actually created all my views by means of "CREATE VIEW ... AS ...", and (as Mr. Hipp said) these views was accepted from SQLite as "valid" SQL statements. The problem (was) that right now I couldn't open the DB anymore, due to the malformed schema error. Thanks again Marco ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] endless loop example
On Mar 21, 2008, at 12:17 PM, Derek Developer wrote: > In trying to break my code with the seinfeld database examples, I > found this. > SELECT f.name as food, e1.name, e1.season, e2.name, e2.season FROM > episodes e1, foods_episodes fe1, foods f, episodes e2, > foods_episodes fe2 > Why does this put SQLite into an endless loop? Why do you figure it is an infinite loop? From the looks of the query it is probably just returning a very large number of rows. Dan. > (I am not using the shell tool, just preparing the statement as is > and stepping throug the rows) > > > > - > Looking for last minute shopping deals? Find them fast with Yahoo! > Search. > ___ > 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
