Re: [sqlite] how to unsubscribe this mail list
On 8/2/06, jason zhang <[EMAIL PROTECTED]> wrote: Hi All, I don't find the method to unsubscribe this mail list from www.sqlite.org, could you please tell me how? Send an email to [EMAIL PROTECTED] -- Nemanja Corlija <[EMAIL PROTECTED]>
[sqlite] how to unsubscribe this mail list
Hi All, I don't find the method to unsubscribe this mail list from www.sqlite.org, could you please tell me how? Thanks Jason
Re: [sqlite] UTF8
Cesar David Rodas Maldonado wrote: Thanks Daniel! Now i have another question! Is any way to serialize all the dates given a preference to SELECT a delay to the insert. I am building a Small Library in C & SQLite that will be under GPL, is something like Lucene. Please help me how to give a preference to SELECT and a delay to INSERT or update. Maintain two queues--one for SELECT and one for everything else. When each db access completes, check the SELECT queue first.
RE: [sqlite] SQLite and ASP Classic
It's less filling... :-) -Original Message- From: Jay Sprenkle [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 01, 2006 3:20 PM To: sqlite-users@sqlite.org; [EMAIL PROTECTED] Subject: Re: [sqlite] SQLite and ASP Classic On 8/1/06, Artie Pagan <[EMAIL PROTECTED]> wrote: > Does anyone know what the connection string is for SQLite using ASP classic? Does that have fewer calories than "New ASP" or does it just taste better? ;)
Re: [sqlite] SQLite and ASP Classic
On 8/1/06, Artie Pagan <[EMAIL PROTECTED]> wrote: Does anyone know what the connection string is for SQLite using ASP classic? Does that have fewer calories than "New ASP" or does it just taste better? ;)
Re: [sqlite] SQLite and ASP Classic
Try with the ODBC or use PHP (PHP is better than ASP and multi OS) On 8/1/06, Artie Pagan <[EMAIL PROTECTED]> wrote: Does anyone know what the connection string is for SQLite using ASP classic?
[sqlite] SQLite and ASP Classic
Does anyone know what the connection string is for SQLite using ASP classic?
RE: [sqlite] IO Access Time
Using an SQL engine to drive a relatively small database is not really very simple, IMHO. But you will notice I am a member of the SQLite fan club for some time now. My largest dataset is somewhere just over 10K rows. I justify using SQLite by noting the advantage of using a well maintained, widely used, small footprint, database which allows me to use a well implemented "standard" data access language (read superior transportability). These advantages (read superior transportability) provide great value over my previous data management systems. Remember ISAM, Paradox, xBase, and etc.? Some of my stuff running today "grew up" on some pretty archaic technology. With each "upgrade" I noticed the things supporting SQL provided a much easier path than those that did not. Now, if it don't do no sten'ken SQL, it is toast! Fred > -Original Message- > From: John Stanton [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 01, 2006 12:29 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] IO Access Time > > > [EMAIL PROTECTED] wrote: > > John Stanton <[EMAIL PROTECTED]> wrote: > > > >>Sqlite is more than one flat file. It is impossible for it > to use less > >>resources than one flat file. > >> > > > > > > I guess this depends on the format and content of the flat file. > > > > As an experiment, I constructed a database with 16K rows with each > > row containing 3 random integers. The flat file (ascii text) was > > 999KB. The SQLite database was 566KB. So in this one case, at > > least, the SQLite database is smaller than the flat file. > > > > Without additional information on your flat file format, I would > > think it was likely that the flat file is smaller than the > > SQLite database. But it is not impossible for an SQLite database > > to be smaller. > > -- > > D. Richard Hipp <[EMAIL PROTECTED]> > > > However if you stored your data as binary integers your flat > file with > 16K records would take 192K of 32 bit words and 384K of 64 > bit and each > record would be accessed very fast. > > If the data in this small set were to be addressed by content > a string > search would also be very fast and make indexing of little value. > > "Everything should be made as simple as possible, but not simpler". > ~Albert Einstein
Re: [sqlite] IO Access Time
[EMAIL PROTECTED] wrote: John Stanton <[EMAIL PROTECTED]> wrote: Sqlite is more than one flat file. It is impossible for it to use less resources than one flat file. I guess this depends on the format and content of the flat file. As an experiment, I constructed a database with 16K rows with each row containing 3 random integers. The flat file (ascii text) was 999KB. The SQLite database was 566KB. So in this one case, at least, the SQLite database is smaller than the flat file. Without additional information on your flat file format, I would think it was likely that the flat file is smaller than the SQLite database. But it is not impossible for an SQLite database to be smaller. -- D. Richard Hipp <[EMAIL PROTECTED]> However if you stored your data as binary integers your flat file with 16K records would take 192K of 32 bit words and 384K of 64 bit and each record would be accessed very fast. If the data in this small set were to be addressed by content a string search would also be very fast and make indexing of little value. "Everything should be made as simple as possible, but not simpler". ~Albert Einstein
Re: [sqlite] IO Access Time
John Stanton <[EMAIL PROTECTED]> wrote: > > Sqlite is more than one flat file. It is impossible for it to use less > resources than one flat file. > I guess this depends on the format and content of the flat file. As an experiment, I constructed a database with 16K rows with each row containing 3 random integers. The flat file (ascii text) was 999KB. The SQLite database was 566KB. So in this one case, at least, the SQLite database is smaller than the flat file. Without additional information on your flat file format, I would think it was likely that the flat file is smaller than the SQLite database. But it is not impossible for an SQLite database to be smaller. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] IO Access Time
Christian Smith wrote: John Stanton uttered: In general you must expect Sqlite to use more of all resources compared to a flat file. After all Sqlite is a flat file with additional logic. Except updates and selective reads will be cheaper in general, as less IO is required due to additional logic. Storage will go up, however. SQLite trades CPU cycles for IO compared to flat file access. Christian Sqlite is more than one flat file. It is impossible for it to use less resources than one flat file. The only advantage you would gain from Sqlite is in the case where you use the flat file inefficiently. Sqlite is useful when you want data management and SQL access implemented in a very elegant and simple way. -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] IO Access Time
John Stanton uttered: In general you must expect Sqlite to use more of all resources compared to a flat file. After all Sqlite is a flat file with additional logic. Except updates and selective reads will be cheaper in general, as less IO is required due to additional logic. Storage will go up, however. SQLite trades CPU cycles for IO compared to flat file access. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] IO Access Time
Aseem Rastogi wrote: Hi All, I am planning to migrate to SQLite for my embedded system application. Currently we have a flat text file which we read and write using normal C++ routines. Whole of the configuration is written everytime some change happens. We do it using ofstream. For reading any object, we keep all the objects in memory and return from there (to prevent file read every time). This takes up a lot of memory but makes it faster. I am a bit concerned about following issues while upgrading to SQLite : 1. How does SQLite behave as compared to our current approach regarding I/O access time ? 2. How much disk space does SQLite take as compared to flat text file ? If somebody has some benchmarking data for these issues, it will be really helpful if he can enlighten me on this. Thanks in advance, Regards, Aseem. In general you must expect Sqlite to use more of all resources compared to a flat file. After all Sqlite is a flat file with additional logic.
Re: [sqlite] IO Access Time
On 8/1/06, Aseem Rastogi <[EMAIL PROTECTED]> wrote: 1. How does SQLite behave as compared to our current approach regarding I/O access time ? 2. How much disk space does SQLite take as compared to flat text file ? If somebody has some benchmarking data for these issues, it will be really helpful if he can enlighten me on this. You could test these issues using the command line program provided with sqlite in a few minutes.
Re: [sqlite] Reading a damaged database file?
[EMAIL PROTECTED] schrieb: Gunnar Roth <[EMAIL PROTECTED]> wrote: Well what happens if i write the db to a compact flash card and remove it while writing and put it back again? The database should recover automatically. Actually i have tried that on our ce device while importing cvs records with .read command of sqlite.exe Afterwards the integrity check failed, but the db could be repaired with the vacuum command. First of all the OS is windows ce 3.0 Did you set "PRAGMA synchronous=OFF"? If you do, then pulling a flash card could corrupt it. No. synchronous is default of sqlite 3 It might also corrupt if your OS ignores the FlushFileBuffers() system call. Maybe the db would not have been corrupted, if the .read command had closed the db file on error. so on open the journal file had been used. is this right? You mean if you have a file on a flash card open, and you remove the card, the file is not closed automatically by your OS? Really? I'd say this is an OS problem and not something that SQLite can work around. So if SQLite is writing a file on flash and you pull the flash card, and SQLite does not receive an error but continues writing (under the obviously false impression that data is making it to the flash device) Well sqlite receives an error for every sqlite3_exec in process_input(struct callback_data *p, FILE *in) of shell.c , the error is printed on the screen, but it dous not call sqlite3_close() then you reinsert the flash card and SQLite continues writing and the later writes get to the flash card - yes it continues writing because now the exec does not fail anymore. that will definitely corrupt the database. This is the equivalent of the operating system choosing to ignore some write requests without giving an error. Is your OS really doing that? Well it gives an error, but is does recover from the error it seems to me. would be interessting to see whta happens on other os's. i will try windows xp later. what does happend if the power supply of a harddisk fails for a few seconds and comes back again? i think the problem could be that after the first error the sqlite file is not closed and opend again to use the journal file. after a program crash or a system crash this happens "by default" ( because there is no other way). regards, gunnar
[sqlite] insert default values - implemented
Mario Frasca wrote: I'm throwing this here, I assume that it would not be too much work to complete this patch. the aim is to support the sql92 syntax insert into default values; all right, a possible complete patch, maybe could be added to the rest... don't tell me that it looks ugly, I totally agree, but it behaves as described... :) regards, MF cvs diff: Diffing src Index: src/insert.c === RCS file: /sqlite/sqlite/src/insert.c,v retrieving revision 1.170 diff -u -r1.170 insert.c --- src/insert.c 19 Jun 2006 03:05:10 - 1.170 +++ src/insert.c 1 Aug 2006 11:56:15 - @@ -123,6 +123,7 @@ ** **insert into TABLE (IDLIST) values(EXPRLIST) **insert into TABLE (IDLIST) select +**insert into TABLE default values ** ** The IDLIST following the table name is always optional. If omitted, ** then a list of all columns for the table is substituted. The IDLIST @@ -380,14 +381,36 @@ }else{ sqlite3VdbeJumpHere(v, iInitCode); } - }else{ + }else if (pList == 0){ +assert( pColumn == 0 ); +/* This is the case if no data has been supplied and DEFAULT VALUES are +** to be inserted. this is a minimalistic impact approach... a fake +** list of columns containing just the primary key and a fake list of +** values containing just a NULL are created, as if the user had issued +** the SQL command: "insert into () values (NULL)". the +** rest of the function remains untouched. error messages are issued if +** the equivalent command causes them. +*/ +Expr *A; +Token the_null = { (u8*)"NULL", 0, 4 }; + +A = sqlite3Expr(TK_NULL, 0, 0, &the_null); +pList = sqlite3ExprListAppend(0,A,0); +nColumn = 1; /* same as pList->nExpr */ + +Token the_pkey = { 0, 0, 0 }; +the_pkey.z = pTab->aCol[pTab->iPKey].zName; +the_pkey.n = strlen(pTab->aCol[pTab->iPKey].zName); + +pColumn = sqlite3IdListAppend(0, &the_pkey); + + } else{ /* This is the case if the data for the INSERT is coming from a VALUES ** clause */ NameContext sNC; memset(&sNC, 0, sizeof(sNC)); sNC.pParse = pParse; -assert( pList!=0 ); srcTab = -1; useTempTable = 0; assert( pList ); Index: src/parse.y === RCS file: /sqlite/sqlite/src/parse.y,v retrieving revision 1.206 diff -u -r1.206 parse.y --- src/parse.y 11 Jul 2006 10:42:36 - 1.206 +++ src/parse.y 1 Aug 2006 11:56:16 - @@ -598,6 +598,10 @@ cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) VALUES LP itemlist(Y) RP. {sqlite3Insert(pParse, X, Y, 0, F, R);} +cmd ::= insert_cmd(R) INTO fullname(X) DEFAULT VALUES. +{ sqlite3Insert(pParse, X, 0, 0, 0, R);} +cmd ::= insert_cmd(R) INTO fullname(X) LP RP VALUES LP RP. +{ sqlite3Insert(pParse, X, 0, 0, 0, R);} cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) select(S). {sqlite3Insert(pParse, X, 0, S, F, R);} cvs diff: Diffing src/ex
Re: [sqlite] Reading a damaged database file?
Thaniks, I hope it works as expected since I'm using SQLite in my e-mail client project (https://sourceforge.net/projects/envoy/) and I expect millions of people to use it eventually. I fear having to answer e-mails and complaints of hundreds of users whose e-mail databases have been corrupted. So far the SQLite database has behaved very well and it's very fast. Olaf - Original Message - From: <[EMAIL PROTECTED]> To: Sent: Tuesday, August 01, 2006 12:35 PM Subject: Re: [sqlite] Reading a damaged database file? =?utf-8?Q?Olaf_Beckman_Lapr=C3=A9?= <[EMAIL PROTECTED]> wrote: > Hi, > > Is there a way to do this programmatically? I would like to recover from a > damaged database upon program startup. > If your program crashes, or your computer loses power in the middle of a database change, and thus leaves the database in an inconsistent state, recovery is automatic. You do not need to do anything. The database automatically reverts to its prior state. If, however, you set "PRAGMA synchronous=OFF" or if you delete a hot journal file, or if you write random garbage into the middle of a database file, then there isn't much you can do to recover. How exactly is your database being damaged and why do you think you need some kind of automatic recovery mechanism over and above what is already implemented? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Reading a damaged database file?
Gunnar Roth <[EMAIL PROTECTED]> wrote: > > > Well what happens if i write the db to a compact flash card and remove > it while writing and put it back again? The database should recover automatically. > Actually i have tried that on our ce device while importing cvs records > with .read command of sqlite.exe > Afterwards the integrity check failed, but the db could be repaired with > the vacuum command. Did you set "PRAGMA synchronous=OFF"? If you do, then pulling a flash card could corrupt it. It might also corrupt if your OS ignores the FlushFileBuffers() system call. > Maybe the db would not have been corrupted, if the .read command had > closed the db file on error. so on open the journal file had been used. > is this right? > You mean if you have a file on a flash card open, and you remove the card, the file is not closed automatically by your OS? Really? I'd say this is an OS problem and not something that SQLite can work around. So if SQLite is writing a file on flash and you pull the flash card, and SQLite does not receive an error but continues writing (under the obviously false impression that data is making it to the flash device) then you reinsert the flash card and SQLite continues writing and the later writes get to the flash card - that will definitely corrupt the database. This is the equivalent of the operating system choosing to ignore some write requests without giving an error. Is your OS really doing that? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Reading a damaged database file?
[EMAIL PROTECTED] schrieb: =?utf-8?Q?Olaf_Beckman_Lapr=C3=A9?= <[EMAIL PROTECTED]> wrote: Hi, Is there a way to do this programmatically? I would like to recover from a damaged database upon program startup. If your program crashes, or your computer loses power in the middle of a database change, and thus leaves the database in an inconsistent state, recovery is automatic. You do not need to do anything. The database automatically reverts to its prior state. Well what happens if i write the db to a compact flash card and remove it while writing and put it back again? Actually i have tried that on our ce device while importing cvs records with .read command of sqlite.exe Afterwards the integrity check failed, but the db could be repaired with the vacuum command. Maybe the db would not have been corrupted, if the .read command had closed the db file on error. so on open the journal file had been used. is this right? regards, gunnar
Re: [sqlite] Reading a damaged database file?
Olaf Beckman Lapré schrieb: Hi, Is there a way to do this programmatically? I would like to recover from a damaged database upon program startup. 1. Copy the code for dump command from shell.c 2. Try if "vacuum;" command repairs the database. with "pragma integrity_check;" you can check if the repair succeeded. regards, gunnar
Re: [sqlite] why I can't find the implementation of functions in IoMethod structure?
Sarah schrieb: > Why I can't find the implementation of functions(xClose, xRead, xWrite, > xSeek) in IoMethod in the whole SQLite project? > > Are these functions implemented in OS level? > > Of course, where did you expect these os specific function implementation to be? static const IoMethod sqlite3WinIoMethod = { winClose, winOpenDirectory, winRead, winWrite, winSeek, winTruncate, winSync, winSetFullSync, winFileHandle, winFileSize, winLock, winUnlock, winLockState, winCheckReservedLock, }; in os_win.c for example. allocateWinFile sets this table to IOFile.pMethod. regards, gunnar
Re: [sqlite] why I can't find the implementation of functions in IoMethod structure?
"Sarah" <[EMAIL PROTECTED]> wrote: > Why I can't find the implementation of functions(xClose, xRead, xWrite, > xSeek) in IoMethod in the whole SQLite project? > > Are these functions implemented in OS level? > > Can anyone help me? Separate implementions exist in os_unix.c and os_win.c -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Moving journal storage to another location...
Bill KING <[EMAIL PROTECTED]> wrote: > Is there a way to move the journal to say use a temp directory? We're > doing some stuff with dbs on SD-cards and it's dog slow. Writing to them > is dog slow too. So is transacting to them. 3 writes on slw media. > Is there a way to have the journal in the faster local flash memory, > then when it's done/committed, have that written to the sd media? > The journal file must be in the same directory as the original database so that a hot journal (that results from a program crash or system power failure) can be located and used to rollback changes the next time some other program tries to open the database file. If the next user of the database cannot locate the hot journal, the journal will not be played back and the database will not be restored to a consistent state. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Reading a damaged database file?
=?utf-8?Q?Olaf_Beckman_Lapr=C3=A9?= <[EMAIL PROTECTED]> wrote: > Hi, > > Is there a way to do this programmatically? I would like to recover from a > damaged database upon program startup. > If your program crashes, or your computer loses power in the middle of a database change, and thus leaves the database in an inconsistent state, recovery is automatic. You do not need to do anything. The database automatically reverts to its prior state. If, however, you set "PRAGMA synchronous=OFF" or if you delete a hot journal file, or if you write random garbage into the middle of a database file, then there isn't much you can do to recover. How exactly is your database being damaged and why do you think you need some kind of automatic recovery mechanism over and above what is already implemented? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Reading a damaged database file?
Hi, Is there a way to do this programmatically? I would like to recover from a damaged database upon program startup. Regards, Olaf - Original Message - From: "Christian Smith" <[EMAIL PROTECTED]> To: "SQLite Mailing List" Sent: Monday, July 31, 2006 4:53 PM Subject: Re: [sqlite] Reading a damaged database file? Olaf Beckman Lapr uttered: > Hi, > > Is it possible to read the undamaged parts of a damaged .db file? This > way I can copy the undamaged parts to another database. Or support to > repair the .db file in such a way that it becomes readable again and the > undamaged parts can be read. You can try doing a .dump from the SQLite shell. It will dump out as much data that can be read without errors. > > Olaf -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
[sqlite] Compobj.dll error
Hello, I am using the vb method for using the sqlite wrapper dll in my application. But on few PC's I am getting the error as compobj.dll is too old for the ole2.dll initialized What could be the reason? Thanks in advance.
[sqlite] why I can't find the implementation of functions in IoMethod structure?
Why I can't find the implementation of functions(xClose, xRead, xWrite, xSeek) in IoMethod in the whole SQLite project? Are these functions implemented in OS level? Can anyone help me?
Re: [sqlite] Re: insert default values - supporting it?
just to show that it does not crash: sqlite> create table test2 (k integer primary key autoincrement); sqlite> insert into test2 default values; sqlite> insert into test2 () values (); sqlite> select * from test2; 1 2 sqlite>
Re: [sqlite] Re: insert default values - supporting it?
I refined the patch. it constructs a list of values with one NULL, but I don't see how to construct an idList with just the primary key. also added the grammar rule to recognize both: insert into default values; insert into () values (); anybody completing/correcting the work? sqlite> insert into test () values (); SQL error: table test has 3 columns but 1 values were supplied MF. cvs diff: Diffing src Index: src/insert.c === RCS file: /sqlite/sqlite/src/insert.c,v retrieving revision 1.170 diff -u -r1.170 insert.c --- src/insert.c 19 Jun 2006 03:05:10 - 1.170 +++ src/insert.c 1 Aug 2006 08:26:28 - @@ -123,6 +123,7 @@ ** **insert into TABLE (IDLIST) values(EXPRLIST) **insert into TABLE (IDLIST) select +**insert into TABLE default values ** ** The IDLIST following the table name is always optional. If omitted, ** then a list of all columns for the table is substituted. The IDLIST @@ -380,7 +381,22 @@ }else{ sqlite3VdbeJumpHere(v, iInitCode); } - }else{ + }else if (pList == 0){ +assert( pColumn == 0 ); +/* This is the case if no data has been supplied and DEFAULT VALUES are +** to be inserted. a minimal impact approach would be to create here a +** temporary list of columns containing just the primary key and a +** temporary list of values containing just a NULL. the rest of the +** function would remain untouched. +*/ +Expr *A; +Token the_null = { (u8*)"NULL", 0, 4 }; + +A = sqlite3Expr(TK_NULL, 0, 0, &the_null); +pList = sqlite3ExprListAppend(0,A,0); +nColumn = 1; + + } else{ /* This is the case if the data for the INSERT is coming from a VALUES ** clause */ Index: src/parse.y === RCS file: /sqlite/sqlite/src/parse.y,v retrieving revision 1.206 diff -u -r1.206 parse.y --- src/parse.y 11 Jul 2006 10:42:36 - 1.206 +++ src/parse.y 1 Aug 2006 08:26:28 - @@ -598,6 +598,10 @@ cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) VALUES LP itemlist(Y) RP. {sqlite3Insert(pParse, X, Y, 0, F, R);} +cmd ::= insert_cmd(R) INTO fullname(X) DEFAULT VALUES. +{ sqlite3Insert(pParse, X, 0, 0, 0, R);} +cmd ::= insert_cmd(R) INTO fullname(X) LP RP VALUES LP RP. +{ sqlite3Insert(pParse, X, 0, 0, 0, R);} cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) select(S). {sqlite3Insert(pParse, X, 0, S, F, R);} cvs diff: Diffing src/ex