Re: [sqlite] sqlite3 error: database or disk is full when commit transaction
Hi Donald, Thanks for your warmly help.Yes, I am working on POS terminal which is based on WinCE platform. After carefully reading the article you sent to me, I had a deep understanding of SQLITE transaction. For your suggestion, I think the first one is good for me since our project will be released soon and I do not have much time to do compression work. And I have a further question for the first solution, if the power was off during the multiple delete transaction, for example, transaction 1 finished and transaction 2 not started, will it caused that I only delete partial data of my database? Do you have any comments on this case? Regards,Tim > From: donald.gri...@allscripts.com > To: sqlite-users@sqlite.org > CC: bh_s...@hotmail.com > Date: Thu, 25 Mar 2010 12:41:28 -0400 > Subject: RE: [sqlite] sqlite3 error: database or disk is full when commit > transaction > > Tim, > > Regarding: "I guess when do the transaction, the database was duplicated, > and it caused no free disk space" > > > Unless you do something such as "vacuum" the database should not actually be > duplicated. A rollback journal file, > though, **is** created. > (details at http://www.sqlite.org/atomiccommit.html and elsewhere) > > > Others on this list can likely give better advice, but I wondered if: >1) Perhaps deleting only part of the table in multiple transactions would > help. >2) If your operating system allows it, you might experiment with applying > transparent compression to the database directory and perhaps more. >3) If #2 is not feasible, would applying compression yourself to certain > data fields be worthwhile? > > I assume you're working with an embedded device of some sort which would > explain such severe memory constraints? > > Donald _ The New Busy is not the old busy. Search, chat and e-mail from your inbox. http://www.windowslive.com/campaign/thenewbusy?ocid=PID27925::T:WLMTAGL:ON:WL:en-US:WM_HMP:032010_3 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR Replace Help
"P Kishor" wrote... > In any case, for OP's purpose, and esp. since he seems to be inserting > numbers as strings, as implied by the quoted numbers, he might benefit > from > > CREATE TABLE test ( > t1 TEXT, > t2 TEXT, > t3 TEXT, > t4 TEXT, > PRIMARY KEY(t1, t2) > ); > > I don't know if INSERT or REPLACE will work in that case, but seems > like it should. Yes, the benefit is great. Thanks, that works. josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "out of memory" in ANALYZE with SQLITE_ENABLE_STAT2
On Thu, Mar 25, 2010 at 10:22 AM, Martin Wilck
wrote:
> Hello,
>
> with sqlite 3.6.23 with SQLITE_ENABLE_STAT2, I get
> an "out of memory" error running the following simple SQL code:
>
> CREATE TABLE dist_T (
> d_row INTEGER PRIMARY KEY,
> dist TEXT UNIQUE NOT NULL
> );
>
> INSERT INTO dist_T VALUES (NULL, '');
> INSERT INTO dist_T VALUES (NULL, 'a');
> INSERT INTO dist_T VALUES (NULL, 'b');
> INSERT INTO dist_T VALUES (NULL, 'c');
> INSERT INTO dist_T VALUES (NULL, 'd');
> INSERT INTO dist_T VALUES (NULL, 'e');
> INSERT INTO dist_T VALUES (NULL, 'f');
> INSERT INTO dist_T VALUES (NULL, 'g');
> INSERT INTO dist_T VALUES (NULL, 'h');
> INSERT INTO dist_T VALUES (NULL, 'i');
>
> ANALYZE;
>
>
> This is always reproducable if dist_T contains 10 or more rows. With 0-9
> rows, I don't see the problem. Also not if SQLITE_ENABLE_STAT2 is not set.
>
> The problem happens when interpreting the results of "SELECT
> idx,sampleno,sample FROM 'main'.sqlite_stat2".on line 66154 in sqlite3.c
> (sqlite3AnalysisLoad()), apparently because
> sqlite3_column_bytes() returned a size of 0 bytes for column 2 in line
> 66145:
>
> 66145 int n = sqlite3_column_bytes(pStmt, 2);
> 66146 if( n>24 ){
> 66147 n = 24;
> 66148 }
> 66149 pSample->nByte = (u8)n;
> 66150 pSample->u.z = sqlite3DbMallocRaw(dbMem, n);
> 66151 if( pSample->u.z ){
> 66152 memcpy(pSample->u.z, z, n);
> 66153 }else{
> 66154 db->mallocFailed = 1;
> 66155 break;
> 66156 }
>
> I am including some gdb output. Note that pResultSet[2].n = 0.
>
> (gdb) bt
> #0 sqlite3AnalysisLoad (db=0x55af68, iDb=0) at sqlite3.c:66154
> #1 0x2af0c5884bd8 in sqlite3VdbeExec (p=0x569e38) at sqlite3.c:57585
> #2 0x2af0c587c02d in sqlite3Step (p=0x569e38) at sqlite3.c:51342
> #3 0x2af0c587c249 in sqlite3_step (pStmt=0x569e38) at sqlite3.c:51402
> #4 0x00403eea in shell_exec (db=0x55af68, zSql=0x55aee0
> "ANALYZE;",
> xCallback=0x402ba1 , pArg=0x7fffe5377850,
> pzErrMsg=0x7fffe5375aa8) at shell.c:1012
> #5 0x00408019 in process_input (p=0x7fffe5377850, in=0x55ab50)
> at shell.c:2236
> #6 0x00406d1a in do_meta_command (zLine=0x5537d0 ".read",
> p=0x7fffe5377850) at shell.c:1860
> #7 0x00407deb in process_input (p=0x7fffe5377850, in=0x0) at
> shell.c:2195
> #8 0x00409183 in main (argc=1, argv=0x7fffe5378eb8) at
> shell.c:2616
>
>
> (gdb) p *pIdx
> $4 = {zName = 0x56c23d "sqlite_autoindex_dist_T_1", nColumn = 1,
> aiColumn = 0x56c230, aiRowEst = 0x56c234,
> pTable = 0x56b328, tnum = 3, onError = 99 'c', autoIndex = 1 '\001',
> zColAff = 0x56bce8 "ab", pNext = 0x0,
> pSchema = 0x55bb88, aSortOrder = 0x56c23c "", azColl = 0x56c228,
> aSample = 0x570058}
> (gdb) p *((Vdbe*) pStmt)
>
> $5 = {db = 0x55af68, pPrev = 0x0, pNext = 0x569e38, nOp = 15, nOpAlloc =
> 42, aOp = 0x570d88, nLabel = 4,
> nLabelAlloc = 26, aLabel = 0x0, apArg = 0x570fd0, aColName =
> 0x571188, pResultSet = 0x570ef0, nResColumn = 3,
> nCursor = 1, apCsr = 0x570fd0, errorAction = 2 '\002', okVar = 0
> '\0', nVar = 0, aVar = 0x570fd0,
> azVar = 0x570fd0, magic = 3186757027, nMem = 4, aMem = 0x570eb8,
> cacheCtr = 3, pc = 8, rc = 0, zErrMsg = 0x0,
> explain = 0 '\0', changeCntOn = 0 '\0', expired = 0 '\0', runOnlyOnce
> = 0 '\0', minWriteFileFormat = 255 '377',
> inVtabMethod = 0 '\0', usesStmtJournal = 0 '\0', readOnly = 1 '\001',
> isPrepareV2 = 0 '\0', nChange = 0,
> btreeMask = 1, startTime = 0, aMutex = {nMutex = 0, aBtree = {0x0
> }}, aCounter = {0, 0},
> zSql = 0x5685b0 "SELECT idx,sampleno,sample FROM
> 'main'.sqlite_stat2", pFree = 0x0, nFkConstraint = 0,
> nStmtDefCons = 0, iStatement = 0, pFrame = 0x0, nFrame = 0, expmask = 0}
>
> (gdb) p ((Vdbe*) pStmt)->pResultSet[0]
> $8 = {u = {i = 0, nZero = 0, pDef = 0x0, pRowSet = 0x0, pFrame = 0x0}, r
> = 0, db = 0x55af68,
> z = 0x568fd8 "sqlite_autoindex_dist_T_1", n = 25, flags = 514, type =
> 3 '\003', enc = 1 '\001', xDel = 0,
> zMalloc = 0x568fd8 "sqlite_autoindex_dist_T_1"}
>
> (gdb) p ((Vdbe*) pStmt)->pResultSet[1]
> $9 = {u = {i = 0, nZero = 0, pDef = 0x0, pRowSet = 0x0, pFrame = 0x0}, r
> = 0, db = 0x55af68, z = 0x568208 "0",
> n = 1, flags = 514, type = 3 '\003', enc = 1 '\001', xDel = 0,
> zMalloc = 0x568208 "0"}
>
> (gdb) p ((Vdbe*) pStmt)->pResultSet[2]
> $10 = {u = {i = 0, nZero = 0, pDef = 0x0, pRowSet = 0x0, pFrame = 0x0},
> r = 0, db = 0x55af68, z = 0x568bc8 "",
> n = 0, flags = 514, type = 3 '\003', enc = 1 '\001', xDel = 0,
> zMalloc = 0x568bc8 ""}
>
>
> Regards
> Martin
>
>
> --
> Dr. Martin Wilck
> PRIMERGY System Software Engineer
> x86 Server Engineering
>
> Fujitsu Technology Solutions GmbH
> Heinz-Nixdorf-Ring 1
> 33106 Paderborn, Germany
>
> Phone: ++49 5251 525 2796
> Fax:++49 5251
Re: [sqlite] Available alternatives to syntax diagrams in documentation
On Thu, Mar 25, 2010 at 6:48 PM, D. Richard Hipp wrote:
>
> On Mar 25, 2010, at 7:38 PM, P Kishor wrote:
>>
>> If you see the docs at Pg or MySQL, they are in BNF format (I believe
>> that is what it is called). It is plain text, so it can be parsed,
>> seen, read aloud, and viewed via lynx (for those who care).
>
>
> I am very sorry that you find the syntax diagrams of SQLite difficult
> to read. That is not the experience of most other users, however, who
> report finding the syntax diagrams easier to comprehend at a quick
> glance.
>
> We do not have the resources to maintain two parallel sets of syntax
> documentation with different representations to suite the preferences
> of individual readers.. And so the syntax diagrams will be all that
> is available for the foreseeable future. I apologize for whatever
> inconvenience this may cause you.
>
>
Richard, I have no problems with the syntax diagrams. They are just
fine for me. I am only advocating on behalf of those who, for whatever
reason, physical inability (y'know Section 508 and stuff) or political
reasons ("kill all gifs") may be unable to see the images.
I also understand that hwaci or sqlite.org may not have the resources
to create both textual and pictorial docs.
Personally, I find the pictures fine, but maybe defaulting to pictures
leaves out a few of the constituents, while defaulting to text
excludes no one.
In any case, I have no felines in this skirmish.
--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Available alternatives to syntax diagrams in documentation
On Mar 25, 2010, at 7:38 PM, P Kishor wrote: > > If you see the docs at Pg or MySQL, they are in BNF format (I believe > that is what it is called). It is plain text, so it can be parsed, > seen, read aloud, and viewed via lynx (for those who care). I am very sorry that you find the syntax diagrams of SQLite difficult to read. That is not the experience of most other users, however, who report finding the syntax diagrams easier to comprehend at a quick glance. We do not have the resources to maintain two parallel sets of syntax documentation with different representations to suite the preferences of individual readers.. And so the syntax diagrams will be all that is available for the foreseeable future. I apologize for whatever inconvenience this may cause you. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite performance with large and multiple attached databases
On Thu, Mar 25, 2010 at 05:22:04PM +0100, Akbar Syed scratched on the wall: > I have been trying to improve the performance and memory usage for my > application whereby i have maximum of 30 databases attached. In total I have > 31 databases with 30 databases attached to the first one. Each database has > a single table with approx 65 columns and the records in each table may go > upto 50,000. I have 31 connections for 31 databases i.e. 1 connection for > one database. For each connection I have a cache size of 500 pages (1 Page = > 1KB), temporary cache 500 pages and for each attached connection cache size > of 100 pages. My efforts to minimize memory usage as much as I can also the > speed of reading is tolerable. I dont mind the writing speed, but I do care > for reading speed. In one attempt, I would select all the records from all > the databases and thats the purpose I am using attached databases with a > single query. If you want to improve utilization memory, just put everything in one database. Each attached database in each connection has its own cache. With that many data sources you're going to get a very uneven distribution of cache utilization, and very high worst-case usage. > In one attempt i tried to fetch just 250 records of 65 columns from 31 > databases and I observed that I spend approx 1-5 seconds in the first call > to sqlite3_step() function and the subsequent calls to sqlite3_step() are > some microseconds. "Just 250 records" implies a sort. Unless an index is available, a sort requires doing the whole query and sorting the results. That's the large lead time. After that it is just popping records off the stack, so to speak. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Available alternatives to syntax diagrams in documentation
On Thu, Mar 25, 2010 at 6:28 PM, Shane Harrelson wrote: > On Thu, Mar 25, 2010 at 7:24 PM, P Kishor wrote: > >> On Thu, Mar 25, 2010 at 6:22 PM, P Kishor wrote: >> > On Thu, Mar 25, 2010 at 6:19 PM, Shane Harrelson >> wrote: >> >> >> >> >> >> On Thu, Mar 25, 2010 at 7:06 PM, P Kishor wrote: >> >>> >> >>> On Thu, Mar 25, 2010 at 5:59 PM, Shane Harrelson >> wrote: >> >>> > I added a psuedo-BNF renderer for the bubble syntax graph data at >> >>> > >> >>> > http://www.sqlite.org/docsrc/artifact/873cf35adf >> >>> > >> >>> > to go along with the text based bubble graph at >> >>> > >> >>> > http://www.sqlite.org/docsrc/artifact/645054606c >> >>> > >> >>> > These are not meant to replace the official syntax specification at >> >>> > >> >>> > http://www.sqlite.org/syntaxdiagrams.html >> >>> > >> >>> >> >>> >> >>> This is gorgeous, but is it not possible to just have plain text docs? >> >>> Much like most other database manuals do? >> >>> >> >>> http://www.postgresql.org/docs/8.4/static/ddl-basics.html >> >>> http://dev.mysql.com/doc/refman/5.5/en/create-table.html >> >>> >> >>> or am I missing something? >> >>> >> >>> >> >>> -- >> >>> Puneet Kishor >> >>> >> >> >> >> Is this >> >> >> >> http://www.sqlite.org/lang.html >> >> >> >> or >> >> >> >> http://www.sqlite.org/lang_createtable.html >> >> >> >> what you're looking for? Otherwise, I'm not sure what you're asking >> for. >> >> >> >> >> > >> > The links you provided goes back to diagrams. I believe the OP was >> > asking if there was plain text documentation available. Whether or not >> > he was asking that, I am wondering... is there plain text >> > documentation available at all? I mean, where can I see syntax spelled >> > out like so >> > >> > DELETE FROM qualified_table-name WHERE expr >> > >> > It used to be available until the docs were migrated to the diagrams. >> > >> >> >> >> Here is another way of conveying the problem here. Go to your >> browser's settings and turn off images. Then try to read the >> documentation at sqlite.org. >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > If you scroll down on on any of the lang_*.html pages, like > > http://www.sqlite.org/lang_createtable.html > > you'll see text based descriptions of the individual command. > Even with graphics turned off. > > All of the individual pages are referenced from: > > http://www.sqlite.org/lang.html > > Is that what you mean? > I must be in an alternate universe. So, I have turned off images in my browser and I am looking at http://www.sqlite.org/lang_delete.html No matter how much I squint at it, I just cannot see the following text anywhere on the page that would be the equivalent of the diagram at http://www.sqlite.org/images/syntax/delete-stmt.gif DELETE FROM qualified-table-name WHERE expr If you see the docs at Pg or MySQL, they are in BNF format (I believe that is what it is called). It is plain text, so it can be parsed, seen, read aloud, and viewed via lynx (for those who care). -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Available alternatives to syntax diagrams in documentation
On Tue, Mar 23, 2010 at 11:50:10PM -0400, Shane Harrelson wrote: > I have no idea what kinds of things a "screen reader" would need to make > this version useful, but if you let me know, I will try to add them. Screen readers are used by those who have impaired sight or not sight (i.e., blindness). Screen readers look for character data and generate speech to read it. GIFs, PNGs, ... are not readable by screen readers -- not without resorting to OCR. Thus just generating text to put in the ALT tag of the IMGs would be a huge improvement in making the docs accessible to the visually impaired. What you made available at http://www.sqlite.org/docsrc/artifact/873cf35adf is readable via a screen reader because it's all text (the only IMG in that page is for the SQLite3 documentation logo). All you have to do now is make the scripts that generate http://www.sqlite.org/lang.html and friends put the text diagrams as ALT text for the GIFs and you're done :) (I don't need accessible docs, but clearly others do; on their behalf: thanks!) Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Available alternatives to syntax diagrams in documentation
On Thu, Mar 25, 2010 at 7:24 PM, P Kishor wrote: > On Thu, Mar 25, 2010 at 6:22 PM, P Kishor wrote: > > On Thu, Mar 25, 2010 at 6:19 PM, Shane Harrelson > wrote: > >> > >> > >> On Thu, Mar 25, 2010 at 7:06 PM, P Kishor wrote: > >>> > >>> On Thu, Mar 25, 2010 at 5:59 PM, Shane Harrelson > wrote: > >>> > I added a psuedo-BNF renderer for the bubble syntax graph data at > >>> > > >>> > http://www.sqlite.org/docsrc/artifact/873cf35adf > >>> > > >>> > to go along with the text based bubble graph at > >>> > > >>> > http://www.sqlite.org/docsrc/artifact/645054606c > >>> > > >>> > These are not meant to replace the official syntax specification at > >>> > > >>> > http://www.sqlite.org/syntaxdiagrams.html > >>> > > >>> > >>> > >>> This is gorgeous, but is it not possible to just have plain text docs? > >>> Much like most other database manuals do? > >>> > >>> http://www.postgresql.org/docs/8.4/static/ddl-basics.html > >>> http://dev.mysql.com/doc/refman/5.5/en/create-table.html > >>> > >>> or am I missing something? > >>> > >>> > >>> -- > >>> Puneet Kishor > >>> > >> > >> Is this > >> > >> http://www.sqlite.org/lang.html > >> > >> or > >> > >> http://www.sqlite.org/lang_createtable.html > >> > >> what you're looking for? Otherwise, I'm not sure what you're asking > for. > >> > >> > > > > The links you provided goes back to diagrams. I believe the OP was > > asking if there was plain text documentation available. Whether or not > > he was asking that, I am wondering... is there plain text > > documentation available at all? I mean, where can I see syntax spelled > > out like so > > > > DELETE FROM qualified_table-name WHERE expr > > > > It used to be available until the docs were migrated to the diagrams. > > > > > > Here is another way of conveying the problem here. Go to your > browser's settings and turn off images. Then try to read the > documentation at sqlite.org. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > If you scroll down on on any of the lang_*.html pages, like http://www.sqlite.org/lang_createtable.html you'll see text based descriptions of the individual command. Even with graphics turned off. All of the individual pages are referenced from: http://www.sqlite.org/lang.html Is that what you mean? -Shane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Available alternatives to syntax diagrams in documentation
On Thu, Mar 25, 2010 at 6:22 PM, P Kishor wrote: > On Thu, Mar 25, 2010 at 6:19 PM, Shane Harrelson wrote: >> >> >> On Thu, Mar 25, 2010 at 7:06 PM, P Kishor wrote: >>> >>> On Thu, Mar 25, 2010 at 5:59 PM, Shane Harrelson wrote: >>> > I added a psuedo-BNF renderer for the bubble syntax graph data at >>> > >>> > http://www.sqlite.org/docsrc/artifact/873cf35adf >>> > >>> > to go along with the text based bubble graph at >>> > >>> > http://www.sqlite.org/docsrc/artifact/645054606c >>> > >>> > These are not meant to replace the official syntax specification at >>> > >>> > http://www.sqlite.org/syntaxdiagrams.html >>> > >>> >>> >>> This is gorgeous, but is it not possible to just have plain text docs? >>> Much like most other database manuals do? >>> >>> http://www.postgresql.org/docs/8.4/static/ddl-basics.html >>> http://dev.mysql.com/doc/refman/5.5/en/create-table.html >>> >>> or am I missing something? >>> >>> >>> -- >>> Puneet Kishor >>> >> >> Is this >> >> http://www.sqlite.org/lang.html >> >> or >> >> http://www.sqlite.org/lang_createtable.html >> >> what you're looking for? Otherwise, I'm not sure what you're asking for. >> >> > > The links you provided goes back to diagrams. I believe the OP was > asking if there was plain text documentation available. Whether or not > he was asking that, I am wondering... is there plain text > documentation available at all? I mean, where can I see syntax spelled > out like so > > DELETE FROM qualified_table-name WHERE expr > > It used to be available until the docs were migrated to the diagrams. > Here is another way of conveying the problem here. Go to your browser's settings and turn off images. Then try to read the documentation at sqlite.org. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Available alternatives to syntax diagrams in documentation
On Thu, Mar 25, 2010 at 7:22 PM, P Kishor wrote: > On Thu, Mar 25, 2010 at 6:19 PM, Shane Harrelson wrote: > > > > > > On Thu, Mar 25, 2010 at 7:06 PM, P Kishor wrote: > >> > >> On Thu, Mar 25, 2010 at 5:59 PM, Shane Harrelson > wrote: > >> > I added a psuedo-BNF renderer for the bubble syntax graph data at > >> > > >> > http://www.sqlite.org/docsrc/artifact/873cf35adf > >> > > >> > to go along with the text based bubble graph at > >> > > >> > http://www.sqlite.org/docsrc/artifact/645054606c > >> > > >> > These are not meant to replace the official syntax specification at > >> > > >> > http://www.sqlite.org/syntaxdiagrams.html > >> > > >> > >> > >> This is gorgeous, but is it not possible to just have plain text docs? > >> Much like most other database manuals do? > >> > >> http://www.postgresql.org/docs/8.4/static/ddl-basics.html > >> http://dev.mysql.com/doc/refman/5.5/en/create-table.html > >> > >> or am I missing something? > >> > >> > >> -- > >> Puneet Kishor > >> > > > > Is this > > > > http://www.sqlite.org/lang.html > > > > or > > > > http://www.sqlite.org/lang_createtable.html > > > > what you're looking for? Otherwise, I'm not sure what you're asking for. > > > > > > The links you provided goes back to diagrams. I believe the OP was > asking if there was plain text documentation available. Whether or not > he was asking that, I am wondering... is there plain text > documentation available at all? I mean, where can I see syntax spelled > out like so > > DELETE FROM qualified_table-name WHERE expr > > It used to be available until the docs were migrated to the diagrams. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > Is this http://www.sqlite.org/lang.html or http://www.sqlite.org/lang_createtable.html what you're looking for? Otherwise, I'm not sure what you're asking for. -Shane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Available alternatives to syntax diagrams in documentation
On Thu, Mar 25, 2010 at 6:19 PM, Shane Harrelson wrote: > > > On Thu, Mar 25, 2010 at 7:06 PM, P Kishor wrote: >> >> On Thu, Mar 25, 2010 at 5:59 PM, Shane Harrelson wrote: >> > I added a psuedo-BNF renderer for the bubble syntax graph data at >> > >> > http://www.sqlite.org/docsrc/artifact/873cf35adf >> > >> > to go along with the text based bubble graph at >> > >> > http://www.sqlite.org/docsrc/artifact/645054606c >> > >> > These are not meant to replace the official syntax specification at >> > >> > http://www.sqlite.org/syntaxdiagrams.html >> > >> >> >> This is gorgeous, but is it not possible to just have plain text docs? >> Much like most other database manuals do? >> >> http://www.postgresql.org/docs/8.4/static/ddl-basics.html >> http://dev.mysql.com/doc/refman/5.5/en/create-table.html >> >> or am I missing something? >> >> >> -- >> Puneet Kishor >> > > Is this > > http://www.sqlite.org/lang.html > > or > > http://www.sqlite.org/lang_createtable.html > > what you're looking for? Otherwise, I'm not sure what you're asking for. > > The links you provided goes back to diagrams. I believe the OP was asking if there was plain text documentation available. Whether or not he was asking that, I am wondering... is there plain text documentation available at all? I mean, where can I see syntax spelled out like so DELETE FROM qualified_table-name WHERE expr It used to be available until the docs were migrated to the diagrams. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Imposinga minimum limit on a select. Anyway to do this?
sorka wrote: > I'm sitting here banging my head trying to decide the subject for > this post because I don't know what I'd call what I want to do :) > > Here's what I want to do but don't know how. The schema is simplified > for discussion. > > CREATE TABLE program (time_received INTEGER, name TEXT); > Assume indices where appropriate for performance. > > The table has roughly 10K entries. > > I'd like a select that gets all programs received within the last 10 > days and then order the results by name. However, if there are less > than 50 results say because there aren't 50 programs that fit the > time crieteria, I need to keep getting records, the next newest > records until I hit 50 of them. This should work, though probably somewhat inefficient: select * from program where rowid in (select rowid from program order by time_received desc limit 50 union all select rowid from program where time_received > :cutoff_time) order by name; Here's another: select * from program where time_received > min(:cutoff_time, (select time_received from program order by time_received desc limit 1 offset 50)); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Available alternatives to syntax diagrams in documentation
On Thu, Mar 25, 2010 at 7:06 PM, P Kishor wrote: > On Thu, Mar 25, 2010 at 5:59 PM, Shane Harrelson wrote: > > I added a psuedo-BNF renderer for the bubble syntax graph data at > > > > http://www.sqlite.org/docsrc/artifact/873cf35adf > > > > to go along with the text based bubble graph at > > > > http://www.sqlite.org/docsrc/artifact/645054606c > > > > These are not meant to replace the official syntax specification at > > > > http://www.sqlite.org/syntaxdiagrams.html > > > > > This is gorgeous, but is it not possible to just have plain text docs? > Much like most other database manuals do? > > http://www.postgresql.org/docs/8.4/static/ddl-basics.html > http://dev.mysql.com/doc/refman/5.5/en/create-table.html > > or am I missing something? > > > -- > Puneet Kishor > > Is this http://www.sqlite.org/lang.html or http://www.sqlite.org/lang_createtable.html what you're looking for? Otherwise, I'm not sure what you're asking for. -Shane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Imposinga minimum limit on a select. Anyway to do this?
OK, it just hit me that I can use a subselect to get the 50th recorder ordered by time and use a LIMIT 1 OFFSET 50 to get the time at that location. I should be be able to use that a MAX result.I hope.:) -- View this message in context: http://old.nabble.com/Imposinga-minimum-limit-on-a-select.-Anyway-to-do-this--tp28035954p28036162.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR Replace Help
P Kishor wrote: > With regards to having a PK on a column with no affinity, I guess > things will just default to strings, no? No. Just as with any other column with no affinity, no conversions will take place. Thus, 1 and '1' will be considered distinct, and 20 will sort before '1'. > In any case, for OP's purpose, and esp. since he seems to be inserting > numbers as strings, as implied by the quoted numbers, he might benefit > from > > CREATE TABLE test ( > t1 TEXT, > t2 TEXT, > t3 TEXT, > t4 TEXT, > PRIMARY KEY(t1, t2) > ); > > I don't know if INSERT or REPLACE will work in that case, but seems > like it should. It will. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Available alternatives to syntax diagrams in documentation
On Thu, Mar 25, 2010 at 5:59 PM, Shane Harrelson wrote: > I added a psuedo-BNF renderer for the bubble syntax graph data at > > http://www.sqlite.org/docsrc/artifact/873cf35adf > > to go along with the text based bubble graph at > > http://www.sqlite.org/docsrc/artifact/645054606c > > These are not meant to replace the official syntax specification at > > http://www.sqlite.org/syntaxdiagrams.html > This is gorgeous, but is it not possible to just have plain text docs? Much like most other database manuals do? http://www.postgresql.org/docs/8.4/static/ddl-basics.html http://dev.mysql.com/doc/refman/5.5/en/create-table.html or am I missing something? -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Available alternatives to syntax diagrams in documentation
I added a psuedo-BNF renderer for the bubble syntax graph data at http://www.sqlite.org/docsrc/artifact/873cf35adf to go along with the text based bubble graph at http://www.sqlite.org/docsrc/artifact/645054606c These are not meant to replace the official syntax specification at http://www.sqlite.org/syntaxdiagrams.html -Shane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR Replace Help
On Thu, Mar 25, 2010 at 5:06 PM, Igor Tandetnik wrote: > P Kishor wrote: >> On Thu, Mar 25, 2010 at 4:44 PM, jose isaias cabrera >> wrote: >>> Create table test (t1 primary key, t2 secundary key, t3, t4); >> >> >> I am pretty certain that SQLite has no idea what 't1 primary key' >> means. > > No, it's perfectly OK. It means a column with no affinity, which also > constitutes a primary key. > >> Perhaps you meant to say 't1 integer primary key'? > > Unlikely, seeing as the OP insersts strings into it. > >> I am completely certain that SQLite has no idea what 't2 secundary >> key' means. > > Well, actually, it means a column named t2 whose declared type is "secundary > key". Of course such a type has no special meaning to SQLite. One can as well > write "create table test(t2 here be dragons)" (which is a valid SQLite > statement). You are absolutely correct re. "secundary key" not having any special meaning to sqlite, which is precisely what I implied when I wrote "SQLite has no idea what 't2 secundary key' means. It is pointless syntax. With regards to having a PK on a column with no affinity, I guess things will just default to strings, no? In any case, for OP's purpose, and esp. since he seems to be inserting numbers as strings, as implied by the quoted numbers, he might benefit from CREATE TABLE test ( t1 TEXT, t2 TEXT, t3 TEXT, t4 TEXT, PRIMARY KEY(t1, t2) ); I don't know if INSERT or REPLACE will work in that case, but seems like it should. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Imposinga minimum limit on a select. Anyway to do this?
I'm sitting here banging my head trying to decide the subject for this post because I don't know what I'd call what I want to do :) Here's what I want to do but don't know how. The schema is simplified for discussion. CREATE TABLE program (time_received INTEGER, name TEXT); Assume indices where appropriate for performance. The table has roughly 10K entries. I'd like a select that gets all programs received within the last 10 days and then order the results by name. However, if there are less than 50 results say because there aren't 50 programs that fit the time crieteria, I need to keep getting records, the next newest records until I hit 50 of them. This can't be that difficult but I'm just not seeing :( -- View this message in context: http://old.nabble.com/Imposinga-minimum-limit-on-a-select.-Anyway-to-do-this--tp28035954p28035954.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR Replace Help
P Kishor wrote: > On Thu, Mar 25, 2010 at 4:44 PM, jose isaias cabrera > wrote: >> Create table test (t1 primary key, t2 secundary key, t3, t4); > > > I am pretty certain that SQLite has no idea what 't1 primary key' > means. No, it's perfectly OK. It means a column with no affinity, which also constitutes a primary key. > Perhaps you meant to say 't1 integer primary key'? Unlikely, seeing as the OP insersts strings into it. > I am completely certain that SQLite has no idea what 't2 secundary > key' means. Well, actually, it means a column named t2 whose declared type is "secundary key". Of course such a type has no special meaning to SQLite. One can as well write "create table test(t2 here be dragons)" (which is a valid SQLite statement). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR Replace Help
On Thu, Mar 25, 2010 at 4:44 PM, jose isaias cabrera wrote: > Create table test (t1 primary key, t2 secundary key, t3, t4); I am pretty certain that SQLite has no idea what 't1 primary key' means. Perhaps you meant to say 't1 integer primary key'? I am completely certain that SQLite has no idea what 't2 secundary key' means. First, perhaps you meant to write 'secondary' instead of 'secundary'. Even so, perhaps you meant to write 't2 integer secondary key'. Even so, there is no such syntax. Perhaps you meant to define a composite primary key on t1 and t2. In that case, use the syntax 'PRIMARY KEY (t1, t2)' -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite performance with large and multiple attached databases
I have been trying to improve the performance and memory usage for my application whereby i have maximum of 30 databases attached. In total I have 31 databases with 30 databases attached to the first one. Each database has a single table with approx 65 columns and the records in each table may go upto 50,000. I have 31 connections for 31 databases i.e. 1 connection for one database. For each connection I have a cache size of 500 pages (1 Page = 1KB), temporary cache 500 pages and for each attached connection cache size of 100 pages. My efforts to minimize memory usage as much as I can also the speed of reading is tolerable. I dont mind the writing speed, but I do care for reading speed. In one attempt, I would select all the records from all the databases and thats the purpose I am using attached databases with a single query. In one attempt i tried to fetch just 250 records of 65 columns from 31 databases and I observed that I spend approx 1-5 seconds in the first call to sqlite3_step() function and the subsequent calls to sqlite3_step() are some microseconds. Can anyone throw much light on this? I appreciate your suggestions to further improve my speed and memory usage. -Akbar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] INSERT OR Replace Help
the following is
sqlite> Create table test (t1 primary key, t2 secundary key, t3, t4);
sqlite> INSERT or REPLACE into test values ('1','2','3','4');
sqlite> select * from test;
1|2|3|4
sqlite> INSERT or REPLACE into test values ('1','2','3','5');
sqlite> select * from test;
1|2|3|5
sqlite> INSERT or REPLACE into test values ('1','2','4','5');
sqlite> select * from test;
1|2|4|5
sqlite> INSERT or REPLACE into test values ('1','3','3','5');
sqlite> select * from test;
1|3|3|5
sqlite>
this last one should have kept the 1,2,4,5 entries and added a new one.
What I want is to be able to update and replace t3 and t4 if the first two
values are the same. If the first two values differ, then I want to add
that one. By the way, I also tried unique and it does the same thing.
Using SQLite version 3.6.11.
Any help would be greatly appreciated.
thanks,
josé
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite compile error
On 3/25/2010 5:12 PM, Magnus Torfason wrote: > On 3/5/2010 3:45 PM, Simon Slavin wrote: >> On 5 Mar 2010, at 7:18pm, Gary Zigmann wrote: >> >>> [Linker error] undefined reference to `winm...@16' >>> >>> Can you help me? >> >> http://lmgtfy.com/?q=undefined+reference+to+%60WinMain%4016%27 > > Searching "undefined reference to `winm...@16' sqlite" turns up Simon's > email as the first link. That's an ironic recursion :-) > > My guess is that Gary asked this question because he downloaded the > amalgamation and just typed "gcc sqlite3.c", hoping to end up with the > command line shell (that is what I just did). > > http://www.sqlite.org/sqlite.html states that "The SQLite library > *includes* a simple command-line utility named sqlite3" so this hope > does not seem entirely unfounded. But of course now the question > becomes, how should one compile the command line shell from the source. > Any hints on how to do that, oriented towards intermediate users (who > have compiled a program or two, and sometimes fixed a minor annoyance, > but who are not in the business of developing software whole-cloth) ? > > Best, > Magnus > Well, now I feel a tiny bit silly, because http://www.sqlite.org/sqlite.html does contain a link that eventually leads to the shell.c file that is needed to compile the command line client. Best, Magnus ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite compile error
On 3/5/2010 3:45 PM, Simon Slavin wrote: > On 5 Mar 2010, at 7:18pm, Gary Zigmann wrote: > >> [Linker error] undefined reference to `winm...@16' >> >> Can you help me? > > http://lmgtfy.com/?q=undefined+reference+to+%60WinMain%4016%27 Searching "undefined reference to `winm...@16' sqlite" turns up Simon's email as the first link. That's an ironic recursion :-) My guess is that Gary asked this question because he downloaded the amalgamation and just typed "gcc sqlite3.c", hoping to end up with the command line shell (that is what I just did). http://www.sqlite.org/sqlite.html states that "The SQLite library *includes* a simple command-line utility named sqlite3" so this hope does not seem entirely unfounded. But of course now the question becomes, how should one compile the command line shell from the source. Any hints on how to do that, oriented towards intermediate users (who have compiled a program or two, and sometimes fixed a minor annoyance, but who are not in the business of developing software whole-cloth) ? Best, Magnus ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] more on mac database corruption
We're not talking about reliability or robustness of networked file systems - those caveats are valid of course - but not relevant for the case in point. The case in point is a simple sequence of operations that you can execute one at a time, as slowly as you like, and results in a corrupt database every time. -- The "different locking strategies" explanation sounds plausible to me, but its curious that the main database, which is accessed the same way, is working fine. Something different (and buggy) seems to be happening with attached databases. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] more on mac database corruption
On Thu, Mar 25, 2010 at 10:58:41AM -0700, Dave Dyer scratched on the wall: > > > > >AFAIK it's a general rule: don't use SQLite with database somewhere on > >network shared file system, otherwise bad things can happen. > > That's definitely not the general rule. Yes it is-- for *networked* file systems. http://www.sqlite.org/faq.html#q5 "People who have a lot of experience with Windows tell me that file locking of network files is very buggy and is not dependable. If what they say is true, sharing an SQLite database between two or more Windows machines might cause unexpected problems." http://www.sqlite.org/whentouse.html "...file locking logic of many network filesystems implementation contains bugs (on both Unix and Windows). If file locking does not work like it should, it might be possible for two or more client programs to modify the same part of the same database at the same time, resulting in database corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it." SQLite locked is very robust and dependable for multiple processes on the same machine accessing a single database on storage that is physically connected to that machine. All bets are off when talking about networked shares. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] more on mac database corruption
> That's definitely not the general rule. Generally, you can open > a sqlite database from multiple processes and all of them can modify > at will without corrupting the database. You didn't understand my words correctly. Of course SQLite database can be opened from multiple processes and used safely. Unless your database is *somewhere on network shared file system*. SQLite database shared over network is a call for troubles. And _it's a general rule_ to not use SQLite database *on a network file system*. Read please http://www.sqlite.org/whentouse.html, section "Situations Where Another RDBMS May Work Better", first subsection "Client/Server Applications". Pavel On Thu, Mar 25, 2010 at 1:58 PM, Dave Dyer wrote: > >> >>AFAIK it's a general rule: don't use SQLite with database somewhere on >>network shared file system, otherwise bad things can happen. > > That's definitely not the general rule. Generally, you can open > a sqlite database from multiple processes and all of them can modify > at will without corrupting the database. Of course, if they're all > modifying the same records, there's uncertainty what the final state > will be, but the database is still intact and consistent. > > ___ > 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] FTS & Doc Compression
Hello! On Tuesday 02 March 2010 02:41:46 Jason Lee wrote: > I've been playing around with the FTS3 (via the amalgamation src) on a > mobile device and it's working well. But my db file size is getting > pretty big and I was looking for a way to compress it. I've seen some > earlier posts from Alexey for his compression modifications to the > FTS3 extension, but nothing for the amalgamation file. It's easy to build SQLite from full source tree. The modified files ext/fts3/fts3.c and ext/fts3/fts3_write.c for SQLite 3.6.23 are here: http://sqlite.mobigroup.ru/src/vinfo/d3d9906674 or direct links: http://sqlite.mobigroup.ru/src/artifact/57b279352c http://sqlite.mobigroup.ru/src/artifact/daee6be790 (click on "download" link) Or you can patch you amalgamation... Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] more on mac database corruption
> >AFAIK it's a general rule: don't use SQLite with database somewhere on >network shared file system, otherwise bad things can happen. That's definitely not the general rule. Generally, you can open a sqlite database from multiple processes and all of them can modify at will without corrupting the database. Of course, if they're all modifying the same records, there's uncertainty what the final state will be, but the database is still intact and consistent. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS & Doc Compression
Hello! On Tuesday 02 March 2010 15:25:35 Max Vlasov wrote: > can you calculate the ratio between your text data and fts3 data? In my databases with unicode texts compressed data is about 25% of original. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 error: database or disk is full when commit transaction
On Thu, Mar 25, 2010 at 10:56:40PM +0800, Tim scratched on the wall: > > I have a 32MB hard disk. My OS is about 17MB and my application's size is > about 3MB. So there is only 10MB for program data. > > My database is about 4.74MB. When I perform following sql query in a > transaction > > sqlite3_exec(m_sqlite3, "delete from table", 0, 0, &errmsg); > > I got the error "database or disk is full". I guess when do the transaction, > the database was duplicated, and it caused no free disk space. The database is not duplicated, but a journal file is going to be created that needs to hold a copy of every page the delete command touches. If this is the main table in that database, that will be most of the database. This will happen if the delete is inside an explicit transaction or not. That's just the first of your problems, however. Even if the delete command worked, the database file would not get any smaller. To actually recover the disk space you need to VACUUM the database. That does copy the database, plus creating a journal file, so it requires up to 2x the size of the database in freespace-- although in your case the copy would be much smaller, so you might get away with ~1x the free space. The *safest* way to get past the delete is to delete a small number of rows at a time. The journal file will grow roughly in step with the number of deleted rows. So if you delete 10% of the rows at a time (as different transactions) you may be able to get by on more limited space. In the end, the database size will remain the same however. You'll still need to VACUUM to make the database smaller, and you still won't have room to do that. You will have room to add new rows, however. The *easiest* way to get around this is to turn off journaling. The delete should take no extra disk space and the vacuum should only need enough free space to hold the a copy of the new database. Of course, if anything goes wrong or you run out of space anyways, your original database is toast. Make sure you back it up first. http://www.sqlite.org/pragma.html#pragma_journal_mode -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 error: database or disk is full when commit transaction
Tim, Regarding: "I guess when do the transaction, the database was duplicated, and it caused no free disk space" Unless you do something such as "vacuum" the database should not actually be duplicated. A rollback journal file, though, **is** created. (details at http://www.sqlite.org/atomiccommit.html and elsewhere) Others on this list can likely give better advice, but I wondered if: 1) Perhaps deleting only part of the table in multiple transactions would help. 2) If your operating system allows it, you might experiment with applying transparent compression to the database directory and perhaps more. 3) If #2 is not feasible, would applying compression yourself to certain data fields be worthwhile? I assume you're working with an embedded device of some sort which would explain such severe memory constraints? Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3 error: database or disk is full when commit transaction
Hello, I have a 32MB hard disk. My OS is about 17MB and my application's size is about 3MB. So there is only 10MB for program data. My database is about 4.74MB. When I perform following sql query in a transaction sqlite3_exec(m_sqlite3, "delete from table", 0, 0, &errmsg); I got the error "database or disk is full". I guess when do the transaction, the database was duplicated, and it caused no free disk space. How to resolve this issue without a larger hard disk? Thanks, Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] "out of memory" in ANALYZE with SQLITE_ENABLE_STAT2
Hello,
with sqlite 3.6.23 with SQLITE_ENABLE_STAT2, I get
an "out of memory" error running the following simple SQL code:
CREATE TABLE dist_T (
d_row INTEGER PRIMARY KEY,
dist TEXT UNIQUE NOT NULL
);
INSERT INTO dist_T VALUES (NULL, '');
INSERT INTO dist_T VALUES (NULL, 'a');
INSERT INTO dist_T VALUES (NULL, 'b');
INSERT INTO dist_T VALUES (NULL, 'c');
INSERT INTO dist_T VALUES (NULL, 'd');
INSERT INTO dist_T VALUES (NULL, 'e');
INSERT INTO dist_T VALUES (NULL, 'f');
INSERT INTO dist_T VALUES (NULL, 'g');
INSERT INTO dist_T VALUES (NULL, 'h');
INSERT INTO dist_T VALUES (NULL, 'i');
ANALYZE;
This is always reproducable if dist_T contains 10 or more rows. With 0-9
rows, I don't see the problem. Also not if SQLITE_ENABLE_STAT2 is not set.
The problem happens when interpreting the results of "SELECT
idx,sampleno,sample FROM 'main'.sqlite_stat2".on line 66154 in sqlite3.c
(sqlite3AnalysisLoad()), apparently because
sqlite3_column_bytes() returned a size of 0 bytes for column 2 in line
66145:
66145 int n = sqlite3_column_bytes(pStmt, 2);
66146 if( n>24 ){
66147 n = 24;
66148 }
66149 pSample->nByte = (u8)n;
66150 pSample->u.z = sqlite3DbMallocRaw(dbMem, n);
66151 if( pSample->u.z ){
66152 memcpy(pSample->u.z, z, n);
66153 }else{
66154 db->mallocFailed = 1;
66155 break;
66156 }
I am including some gdb output. Note that pResultSet[2].n = 0.
(gdb) bt
#0 sqlite3AnalysisLoad (db=0x55af68, iDb=0) at sqlite3.c:66154
#1 0x2af0c5884bd8 in sqlite3VdbeExec (p=0x569e38) at sqlite3.c:57585
#2 0x2af0c587c02d in sqlite3Step (p=0x569e38) at sqlite3.c:51342
#3 0x2af0c587c249 in sqlite3_step (pStmt=0x569e38) at sqlite3.c:51402
#4 0x00403eea in shell_exec (db=0x55af68, zSql=0x55aee0 "ANALYZE;",
xCallback=0x402ba1 , pArg=0x7fffe5377850,
pzErrMsg=0x7fffe5375aa8) at shell.c:1012
#5 0x00408019 in process_input (p=0x7fffe5377850, in=0x55ab50)
at shell.c:2236
#6 0x00406d1a in do_meta_command (zLine=0x5537d0 ".read",
p=0x7fffe5377850) at shell.c:1860
#7 0x00407deb in process_input (p=0x7fffe5377850, in=0x0) at
shell.c:2195
#8 0x00409183 in main (argc=1, argv=0x7fffe5378eb8) at shell.c:2616
(gdb) p *pIdx
$4 = {zName = 0x56c23d "sqlite_autoindex_dist_T_1", nColumn = 1,
aiColumn = 0x56c230, aiRowEst = 0x56c234,
pTable = 0x56b328, tnum = 3, onError = 99 'c', autoIndex = 1 '\001',
zColAff = 0x56bce8 "ab", pNext = 0x0,
pSchema = 0x55bb88, aSortOrder = 0x56c23c "", azColl = 0x56c228,
aSample = 0x570058}
(gdb) p *((Vdbe*) pStmt)
$5 = {db = 0x55af68, pPrev = 0x0, pNext = 0x569e38, nOp = 15, nOpAlloc =
42, aOp = 0x570d88, nLabel = 4,
nLabelAlloc = 26, aLabel = 0x0, apArg = 0x570fd0, aColName =
0x571188, pResultSet = 0x570ef0, nResColumn = 3,
nCursor = 1, apCsr = 0x570fd0, errorAction = 2 '\002', okVar = 0
'\0', nVar = 0, aVar = 0x570fd0,
azVar = 0x570fd0, magic = 3186757027, nMem = 4, aMem = 0x570eb8,
cacheCtr = 3, pc = 8, rc = 0, zErrMsg = 0x0,
explain = 0 '\0', changeCntOn = 0 '\0', expired = 0 '\0', runOnlyOnce
= 0 '\0', minWriteFileFormat = 255 '377',
inVtabMethod = 0 '\0', usesStmtJournal = 0 '\0', readOnly = 1 '\001',
isPrepareV2 = 0 '\0', nChange = 0,
btreeMask = 1, startTime = 0, aMutex = {nMutex = 0, aBtree = {0x0
}}, aCounter = {0, 0},
zSql = 0x5685b0 "SELECT idx,sampleno,sample FROM
'main'.sqlite_stat2", pFree = 0x0, nFkConstraint = 0,
nStmtDefCons = 0, iStatement = 0, pFrame = 0x0, nFrame = 0, expmask = 0}
(gdb) p ((Vdbe*) pStmt)->pResultSet[0]
$8 = {u = {i = 0, nZero = 0, pDef = 0x0, pRowSet = 0x0, pFrame = 0x0}, r
= 0, db = 0x55af68,
z = 0x568fd8 "sqlite_autoindex_dist_T_1", n = 25, flags = 514, type =
3 '\003', enc = 1 '\001', xDel = 0,
zMalloc = 0x568fd8 "sqlite_autoindex_dist_T_1"}
(gdb) p ((Vdbe*) pStmt)->pResultSet[1]
$9 = {u = {i = 0, nZero = 0, pDef = 0x0, pRowSet = 0x0, pFrame = 0x0}, r
= 0, db = 0x55af68, z = 0x568208 "0",
n = 1, flags = 514, type = 3 '\003', enc = 1 '\001', xDel = 0,
zMalloc = 0x568208 "0"}
(gdb) p ((Vdbe*) pStmt)->pResultSet[2]
$10 = {u = {i = 0, nZero = 0, pDef = 0x0, pRowSet = 0x0, pFrame = 0x0},
r = 0, db = 0x55af68, z = 0x568bc8 "",
n = 0, flags = 514, type = 3 '\003', enc = 1 '\001', xDel = 0,
zMalloc = 0x568bc8 ""}
Regards
Martin
--
Dr. Martin Wilck
PRIMERGY System Software Engineer
x86 Server Engineering
Fujitsu Technology Solutions GmbH
Heinz-Nixdorf-Ring 1
33106 Paderborn, Germany
Phone: ++49 5251 525 2796
Fax:++49 5251 525 2820
Email: martin.wi...@ts.fujitsu.com
Internet: http://ts.fujitsu.com
Company Details:http://de.ts.fujitsu.com/imprint.html
___
sqlite-users mailing list
sqli
Re: [sqlite] SQLite 16 bits
On 25 Mar 2010, at 12:53pm, Miroslav Zagorac wrote: > It will not work, there are many problems with memory allocation, huge > far near etc. pointers, memory models... Okay, I was wrong. Thanks to you both for the correction. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 16 bits
On Thu, Mar 25, 2010 at 12:16:28PM +, Simon Slavin scratched on the wall: > On 25 Mar 2010, at 2:04am, Andi Suhandi wrote: > There is no reason that the source code supplied should not > compile properly on a 16 bit OS. Most 16 bit systems do not offer 64 bit integer operations, which SQLite depends on. Not to mention all the memory issues... 16 bit systems can typically only access banks of 64KB at a time. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 16 bits
Simon Slavin wrote: > On 25 Mar 2010, at 2:04am, Andi Suhandi wrote: > >> Is there SQLite for 16 bit OS ? >> I want to use sqlite in my program in turbo c++ and running in DOS. > > You can download SQLite as source code, and use whatever C compiler you're > using for your own software to compile it. What you probably want is the > first download link on this page: > > http://www.sqlite.org/download.html > > There is no reason that the source code supplied should not compile properly > on a 16 bit OS. However, you may need to make minor changes to some of the > compiler directives if Turbo C++ makes assumptions about word lengths, etc.. > > Simon. It will not work, there are many problems with memory allocation, huge far near etc. pointers, memory models... -- Zaga You have worked and not worked. Not working is the hardest work of all. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 16 bits
Actually, if your machine is 32 bits or 64 bits to run command-line version of sqlite you don't need 16 bit version! What OS are you using? - Mensagem original - De: "Andi Suhandi" Para: sqlite-users@sqlite.org Enviadas: Quarta-feira, 24 de Março de 2010 23:04:36 Assunto: [sqlite] SQLite 16 bits Is there SQLite for 16 bit OS ? I want to use sqlite in my program in turbo c++ and running in DOS. regards Andi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Regards , Israel Lins Albuquerque Desenvolvimento Polibrás Brasil Software Ltda. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 16 bits
2010/3/25 Simon Slavin : > > On 25 Mar 2010, at 2:04am, Andi Suhandi wrote: > >> Is there SQLite for 16 bit OS ? >> I want to use sqlite in my program in turbo c++ and running in DOS. > > You can download SQLite as source code, and use whatever C compiler you're > using for your own software to compile it. What you probably want is the > first download link on this page: > > http://www.sqlite.org/download.html > > There is no reason that the source code supplied should not compile properly > on a 16 bit OS. However, you may need to make minor changes to some of the > compiler directives if Turbo C++ makes assumptions about word lengths, etc.. > It was heard that SQLite 3.x was ported to DJGPP and Borland C++. http://groups.google.com/group/comp.os.msdos.djgpp/browse_thread/thread/5f40742a2df9844b http://fixunix.com/ms-dos/21520-sqlite-3-0-7-ported-djgpp.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] more on mac database corruption
On 25 Mar 2010, at 11:42am, Pavel Ivanov wrote: > For me your sequence of commands clearly shows database file is badly > managed by your Mac file system and/or network sharing mechanism. Or by the client computer (which is running Windows). The setup described by Mr. Dyer appears to involve a Mac client using advisory locks and fsync(), and a Windows client using LockFile() and FlushFileBuffers(), both at the same time on the same file. These systems may each be self-consistent but I wonder if they will interact in an appropriate manner. Another thing which may be connected to this problem is that one access to the database is done via a network protocol but another access is done by directly accessing the file on the computer's hard disk. Generally, if one is running a server the server only serves: all access to a shared file is performed through a network protocol, not via access to the hard disk itself. If SQLite changes locking behaviour and uses different locking protocols the two clients are going to handle locking differently. I don't know enough about SQLite to know if this is the case. I started reading section 6 of lockingv3.html but my brain exploded. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 16 bits
On 25 Mar 2010, at 2:04am, Andi Suhandi wrote: > Is there SQLite for 16 bit OS ? > I want to use sqlite in my program in turbo c++ and running in DOS. You can download SQLite as source code, and use whatever C compiler you're using for your own software to compile it. What you probably want is the first download link on this page: http://www.sqlite.org/download.html There is no reason that the source code supplied should not compile properly on a 16 bit OS. However, you may need to make minor changes to some of the compiler directives if Turbo C++ makes assumptions about word lengths, etc.. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 'Introduction to SQLite' Presentation
Hi all, It would be really helpful if someone could send me the presentation slides of the session "Introduction to SQLite" by Richard Hipp, taken at Google TechTalks May 31, 2006. I could only find the video(Youtube), but i am not able to read the slides in the video(At full screen mode it is not at all clear). Thanks and regards, Sen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite 16 bits
Is there SQLite for 16 bit OS ? I want to use sqlite in my program in turbo c++ and running in DOS. regards Andi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] more on mac database corruption
For me your sequence of commands clearly shows database file is badly managed by your Mac file system and/or network sharing mechanism. Apparently when you try to delete rows from table on Mac SQLite didn't understand that database was changed (probably because of some caching either on Mac side or on PC side). So SQLite tried to change database as if it wasn't changed already, hit some inconsistency with assumption somewhere in the middle of the process and it resulted in corrupted database... AFAIK it's a general rule: don't use SQLite with database somewhere on network shared file system, otherwise bad things can happen. Pavel On Wed, Mar 24, 2010 at 5:42 PM, Dave Dyer wrote: > I was able to reproduct the problem using a trivial set of commands > to the standard sqlite command tool: > > > On the Mac: > > gorp:~/2010 yeartech/yearbook tools/resource davedyer$ > /applications/utilities/sqlite3-shell actiontool2.sqlite > SQLite version 3.6.10 with the Encryption Extension > sqlite> attach database 'indexer.sqlite' as indexer; > sqlite> begin transaction; > > > On the PC: > > M:\2010 yeartech\yearbook tools\resource>sqlite3 actiontool2.sqlite > sqlite> attach database 'indexer.sqlite' as indexer; > sqlite> begin transaction; > sqlite> delete from indexer.preference_table; > sqlite> insert into indexer.preference_table select * from preference_table; > sqlite> commit; > > On the Mac: > > > sqlite> delete from indexer.preferences_table; > SQL error: no such table: indexer.preferences_table > sqlite> delete from indexer.preference_table; > sqlite> insert into indexer.preference_table select * from preference_table; > SQL error: database disk image is malformed > sqlite> > > ___ > 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] Updated FTS3 compression patch
Hello! On Thursday 25 March 2010 08:29:45 Alexandre Courbot wrote: > > Patch is here > > http://sqlite.mobigroup.ru/src/vinfo/d3d9906674 > > Would love to try it - but for some reason I cannot find a way to get > an actual "patch" on this page. Could you produce a diff that could be > applied on top of 2.6.23's source, or even better amalgamation? There are a lot of ways to get diff: 1. The diff is available from repository: http://sqlite.mobigroup.ru/src/ci/d3d9906674 2. The diffs for single files are available from repository: http://sqlite.mobigroup.ru/src/fdiff?v1=31896&v2=31918 http://sqlite.mobigroup.ru/src/fdiff?v1=31743&v2=31919 3. Or you can download files before and after the check-in and make diff manually. See links on the page http://sqlite.mobigroup.ru/src/vinfo/d3d9906674 Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
