Re: [sqlite] Recovering when the SQLite database is corrupt
At 6:23 AM +0200 6/14/06, Olaf Beckman Lapré wrote: Hi, I'm using SQLite as the storage engine in my e-mail client but I'm worried that a user crashes or kills the client during normal operation resulting in a corrupted SQLite database. If the file is corrupted I'm afraid the user's entire e-mail database may be lost. Are there any standard procedures to recover from a corrupted database? Thanks, O. A user simply killing off the email client process, or shutting off the power to your machine, should not corrupt the database, since the database is ACID compliant; it will correct itself using its journal file when your client next is run. That said, if they manually edit the database file with some other program, or remove its journal file, then that could cause problems, as that goes outside the SQLite code and its ability to enforce ACID. -- Darren Duncan
[sqlite] Recovering when the SQLite database is corrupt
Hi, I'm using SQLite as the storage engine in my e-mail client but I'm worried that a user crashes or kills the client during normal operation resulting in a corrupted SQLite database. If the file is corrupted I'm afraid the user's entire e-mail database may be lost. Are there any standard procedures to recover from a corrupted database? Thanks, O.
[sqlite] How to add a table ?
Hello, I have a database. I want to add a table with columns id integer name varchar addresse varchar ... Which instructions (exactly) I have to give. I work with REALBASIC 2006 on Mac Thank you -- Thierry NAUZESaint-Denis de la Réunion
Re: [sqlite] vacuum changes db format
JP <[EMAIL PROTECTED]> wrote: > I don't know if it is a bug or works as designed, but, should VACUUM be > changing the format of the DB? > > I created a database with sqlite 3.2.7, and after a VACUUM in sqlite > 3.3.6 I could no longer open it in the older program - error message #1, > unsupported file format. > VACUUM does update the database to the latest file format. You can prevent this using either a pragma PRAGMA legacy_file_format=ON; Or by compiling SQLite with -DSQLITE_DEFAULT_FILE_FORMAT=1 -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] vacuum changes db format
I don't know if it is a bug or works as designed, but, should VACUUM be changing the format of the DB? I created a database with sqlite 3.2.7, and after a VACUUM in sqlite 3.3.6 I could no longer open it in the older program - error message #1, unsupported file format. jp
Re[2]: [sqlite] Avoiding Fragmentation of SQLite database file ???
Hello Jeff, Tuesday, June 13, 2006, 5:01:15 PM, you wrote: JM> On 6/13/06, Teg <[EMAIL PROTECTED]> wrote: JM> >> I'd think allocating the space for the file, then copying the SQLite >> DB to this new file, renaming the old and renaming the new would >> defragment the file too. >> >> As an experiment, I copied a 5G db from one filename to another and it >> changed from 6700 fragments to 1. JM> Forgive my ignorance, but how are you folks determining that the files JM> are fragmented? I used this program that Rene posted about. It's pretty slick. http://www.defragmentor.com/dmlcl/en/home.asp -- Best regards, Tegmailto:[EMAIL PROTECTED]
Re: [sqlite] Avoiding Fragmentation of SQLite database file ???
On 6/13/06, Teg <[EMAIL PROTECTED]> wrote: I'd think allocating the space for the file, then copying the SQLite DB to this new file, renaming the old and renaming the new would defragment the file too. As an experiment, I copied a 5G db from one filename to another and it changed from 6700 fragments to 1. Forgive my ignorance, but how are you folks determining that the files are fragmented? -- Jeff Macdonald Ayer, MA
Re: [sqlite] Avoiding Fragmentation of SQLite database file ???
Hello RohitPatel, Tuesday, June 13, 2006, 10:43:23 AM, you wrote: R> Hi SQLiteUsers R> Developing MFC Application (Small Business Accounting Application) R> (developed using Visual Studio) R> - App will run on Windows 98/2000 R> - App uses SQLite database files for storage of data R> - It will have one database for each company accounts/info. So if accounts R> of 10 companies, then info will be in 10 different SQLite DB files. R> - All Database files on same disk. R> - User of App may create more company file for accounts of another company. R> - User of App may open any existing company file and enter/modify R> transactions through different GUI screens. R> - User closes file and may open another company file and enter/modify R> transactions through different GUI screens. R> After using such application, all used SQLite DB files gets fragmented. R> How to avoid such fragmentation ? R> Is there any feature, to pre-allocate disk-space to SQLite database file so R> that initially some free space will be allocated to new file ? R> Is there any feature, so that when database file gets filled, everytime R> automatically increment file size by say 25% or 40% of size ? R> FYI: Such pre-allocation of space and automatic incrementing file size is R> provided for Database files in SQLServer, Oracle etc. R> Rohit R> -- R> View this message in context: R> http://www.nabble.com/Avoiding-Fragmentation-of-SQLite-database-file-t1780629.html#a4848030 R> Sent from the SQLite forum at Nabble.com. I'd think allocating the space for the file, then copying the SQLite DB to this new file, renaming the old and renaming the new would defragment the file too. As an experiment, I copied a 5G db from one filename to another and it changed from 6700 fragments to 1. -- Best regards, Tegmailto:[EMAIL PROTECTED]
Re: [sqlite] Wildcards
DJ Anubis wrote: Alex Roston a écrit : Ideally I'd like to do something like: "UPDATE card SET foo='100' where bar='ABC*';" Use SQL standard construct: UPDATE card SET foo='100' WHERE bar LIKE 'ABC%' ; Excellent. Thank You. My problems are solved!! Alex
Re: [sqlite] Wildcards
Thanks Jay, but I've been there, and didn't see any examples, so I wasn't sure how it fit into the other commands. (I'm a little dull today - too many crisises - crisi? whatever... this week.) Alex Jay Sprenkle wrote: On 6/13/06, Alex Roston <[EMAIL PROTECTED]> wrote: Does SQLite have anything resembling a wildcard function? I need to either get rid or modify a whole group of rows which have some common elements in the primary key. Ideally I'd like to do something like: "UPDATE card SET foo='100' where bar='ABC*';" see this page: http://sqlite.org/lang_expr.html look at "LIKE"
Re: [sqlite] Wildcards
I think you mean % not * % is the wildcard. -alex On Jun 13, 2006, at 3:03 PM, Alex Roston wrote: Does SQLite have anything resembling a wildcard function? I need to either get rid or modify a whole group of rows which have some common elements in the primary key. Ideally I'd like to do something like: "UPDATE card SET foo='100' where bar='ABC*';" The construct above doesn't work, but is something like it possible? Thanks, Alex
Re: [sqlite] Wildcards
Alex Roston a écrit : > Ideally I'd like to do something like: > > "UPDATE card SET foo='100' where bar='ABC*';" > Use SQL standard construct: UPDATE card SET foo='100' WHERE bar LIKE 'ABC%' ;
Re: [sqlite] Wildcards
On 6/13/06, Alex Roston <[EMAIL PROTECTED]> wrote: Does SQLite have anything resembling a wildcard function? I need to either get rid or modify a whole group of rows which have some common elements in the primary key. Ideally I'd like to do something like: "UPDATE card SET foo='100' where bar='ABC*';" see this page: http://sqlite.org/lang_expr.html look at "LIKE"
[sqlite] Wildcards
Does SQLite have anything resembling a wildcard function? I need to either get rid or modify a whole group of rows which have some common elements in the primary key. Ideally I'd like to do something like: "UPDATE card SET foo='100' where bar='ABC*';" The construct above doesn't work, but is something like it possible? Thanks, Alex
Re: [sqlite] Avoiding Fragmentation of SQLite database file ???
Hi, There are several command-line defragmenters (gui as well) available for windows that are able to defragment certain files or directories only, like: http://www.defragmentor.com/dmlcl/en/home.asp http://support.microsoft.com/?kbid=283080 or you may like to create your own routines http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fileio/fs/defragmenting_files.asp I used such tool (first link above) and was able to defragment live sqlite databases varying from 400kB to 12GB. A large (5GB+) database indeed generally consist of hundreds of fragments if it was slowly build, so it may be worth the effort. kind regards, Rene RohitPatel schreef: Hi SQLiteUsers Developing MFC Application (Small Business Accounting Application) (developed using Visual Studio) - App will run on Windows 98/2000 - App uses SQLite database files for storage of data - It will have one database for each company accounts/info. So if accounts of 10 companies, then info will be in 10 different SQLite DB files. - All Database files on same disk. - User of App may create more company file for accounts of another company. - User of App may open any existing company file and enter/modify transactions through different GUI screens. - User closes file and may open another company file and enter/modify transactions through different GUI screens. After using such application, all used SQLite DB files gets fragmented. How to avoid such fragmentation ? Is there any feature, to pre-allocate disk-space to SQLite database file so that initially some free space will be allocated to new file ? Is there any feature, so that when database file gets filled, everytime automatically increment file size by say 25% or 40% of size ? FYI: Such pre-allocation of space and automatic incrementing file size is provided for Database files in SQLServer, Oracle etc. Rohit -- View this message in context: http://www.nabble.com/Avoiding-Fragmentation-of-SQLite-database-file-t1780629.html#a4848030 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Avoiding Fragmentation of SQLite database file ???
On 6/13/06, RohitPatel <[EMAIL PROTECTED]> wrote: After using such application, all used SQLite DB files gets fragmented. How to avoid such fragmentation ? You want to use the scheduled task function of windows to run a disk defragmenter. I find that once every two weeks is sufficient for a busy system. Once per month is probably fine. This is an operating system issue, not sqlite. Sqlite does not control where it's data is placed on the disk
Re: [sqlite] sqlite system table names
On 6/13/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Hi, I want to know the names of the system tables in sqlite. I only know of the table sqlite_master. try this: select * from sqlite_master where type = 'table'
Re: [sqlite] sqlite system table names
"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > Hi, > I want to know the names of the system tables in sqlite. I only know of the > table sqlite_master. sqlite_master is it. there are no others. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Re: sqlite system table names
[EMAIL PROTECTED] wrote: I want to know the names of the system tables in sqlite. I only know of the table sqlite_master. There's also sqlite_sequence : http://www.sqlite.org/autoinc.html . As far as I know, this is it - there are just two special tables. Igor Tandetnik
[sqlite] sqlite system table names
Hi, I want to know the names of the system tables in sqlite. I only know of the table sqlite_master. Thanks in advance. Bill
[sqlite] Avoiding Fragmentation of SQLite database file ???
Hi SQLiteUsers Developing MFC Application (Small Business Accounting Application) (developed using Visual Studio) - App will run on Windows 98/2000 - App uses SQLite database files for storage of data - It will have one database for each company accounts/info. So if accounts of 10 companies, then info will be in 10 different SQLite DB files. - All Database files on same disk. - User of App may create more company file for accounts of another company. - User of App may open any existing company file and enter/modify transactions through different GUI screens. - User closes file and may open another company file and enter/modify transactions through different GUI screens. After using such application, all used SQLite DB files gets fragmented. How to avoid such fragmentation ? Is there any feature, to pre-allocate disk-space to SQLite database file so that initially some free space will be allocated to new file ? Is there any feature, so that when database file gets filled, everytime automatically increment file size by say 25% or 40% of size ? FYI: Such pre-allocation of space and automatic incrementing file size is provided for Database files in SQLServer, Oracle etc. Rohit -- View this message in context: http://www.nabble.com/Avoiding-Fragmentation-of-SQLite-database-file-t1780629.html#a4848030 Sent from the SQLite forum at Nabble.com.
[sqlite] Which is most appropriate encoding ?
Hi SQLiteUsers, Need some guidance. While developing Win32/MFC Application (with Visual C++ 6.0) - Application uses SQLite DB for it's data storage - Application must run on most windows (Windows 98, ME, NT, XP, 2000) - User should be able to copy Database from one PC to another PC (one PC may be running Windows 98 and another one Windows 2000 or XP) In this case, which encoding should be used for SQLite database ? Default encoding, UTF-8 or UTF-16 Thanks for any suggestion. Rohit -- View this message in context: http://www.nabble.com/Which-is-most-appropriate-encoding---t1780532.html#a4847672 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Problem with lempar.c revision 1.17
"Garrett Rooney" <[EMAIL PROTECTED]> wrote: > On 6/13/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > "Garrett Rooney" <[EMAIL PROTECTED]> wrote: > > > I've been using lemon as the parser generator for ETL > > > and we've been running into some problems with recent > > > versions. The first problem is in revision 1.17 of lempar.c. You > > > removed an if( yymajor==0 ) return; from Parse, > > > > The EOF token is necessary. But I think Lemon is correct as > > stands - as far as I can tell. Can you please tell me more about > > the circumstances of your segfault - specifically where it is > > happening. > > Well, it doesn't manifest itself in all my test cases, but with the > ones that do crash it's because they end up in a %syntax_error block > with the %extra_argument set to NULL. With the if( yymajor==0 ) check > they parse just fine, never falling into the syntax error block at > all. Your grammer does not generate an empty string. That means if you call the parser with an EOF token first, without any prior tokens, you are going to get a syntax error. The "yymajor==0" test was removed in order to get this to work correctly. Geert Janssen at IBM found this bug back in November - his grammar does not generate an empty string but lemon was not giving him a syntax error like it should if he passed in an empty string. If I add the yymajor==0 test back, it will reintroduce the bug. Perhaps you are calling Parse() with EOF twice in a row when you should only be calling it once? That would trigger the syntax error. Or perhaps you should adjust your grammar to accept an empty string? The %extra_argument that gets passed to the %syntax_error procedure should be the %extra_argument that got passed into with the call to Parse() that contained the EOF token. If that %extra_argument was NULL, then the %syntax_error procedure will get a NULL. Perhaps you can either pass in a non-null parameter to Parse or check for NULL in %syntax_error and branch accordingly. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Problem with lempar.c revision 1.17
On 6/13/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: "Garrett Rooney" <[EMAIL PROTECTED]> wrote: > I've been using lemon as the parser generator for ETL > (http://etl.i-want-a-pony.com/) an open source C based template > language, and we've been running into some problems with recent > versions. The first problem is in revision 1.17 of lempar.c. You > removed an if( yymajor==0 ) return; from Parse, which appears to be > necessary for the final Parse(pParser, 0, sToken) line in the lemon > examples (from http://www.hwaci.com/sw/lemon/lemon.html), which we've > been following in ETL. Putting that line back keeps things from > segfaulting. If it's more correct to not have that final Parse call I > can simply remove it, but for now I've been sticking with the > documentation. The EOF token is necessary. But I think Lemon is correct as stands - as far as I can tell. Can you please tell me more about the circumstances of your segfault - specifically where it is happening. Well, it doesn't manifest itself in all my test cases, but with the ones that do crash it's because they end up in a %syntax_error block with the %extra_argument set to NULL. With the if( yymajor==0 ) check they parse just fine, never falling into the syntax error block at all. You can see the code in question in our Subversion repository: http://svn.i-want-a-pony.com/repos/etl/trunk The parser I'm currently seeing fail is src/template/parser.y, and it happens on most of the input in the test cases (for example, tests/print/2.etl is causing it). The lexer associated with that parser is generated by re2c and is in src/template/lexer.re. (Note that this parser does have a number of parsing conflicts, so it's certainly possible that I've just done something wrong, but it "worked" right up until we upgraded to a new version of lemon.c and lempar.c. The changes to lemon.c were more substantial, so I can't easily point to a line of code and say "that broke it", but for lempar.c it seems to come down to that one change.) Thanks, -garrett
Re: [sqlite] Problem with lempar.c revision 1.17
"Garrett Rooney" <[EMAIL PROTECTED]> wrote: > I've been using lemon as the parser generator for ETL > (http://etl.i-want-a-pony.com/) an open source C based template > language, and we've been running into some problems with recent > versions. The first problem is in revision 1.17 of lempar.c. You > removed an if( yymajor==0 ) return; from Parse, which appears to be > necessary for the final Parse(pParser, 0, sToken) line in the lemon > examples (from http://www.hwaci.com/sw/lemon/lemon.html), which we've > been following in ETL. Putting that line back keeps things from > segfaulting. If it's more correct to not have that final Parse call I > can simply remove it, but for now I've been sticking with the > documentation. The EOF token is necessary. But I think Lemon is correct as stands - as far as I can tell. Can you please tell me more about the circumstances of your segfault - specifically where it is happening. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] how to create an autoincremented rowid in a view
Hello, Is there a way to have the rowid not null in a view? .nullvalue NULL create table t(c); insert into t values(1); insert into t values(2); create view v as select * from t, t as t2; select rowid, * from v; rowid is always null but I want it to be different for every row of the view. Actually, rowid should only identify a row in the view during a query, not between two queries on the view (and I suspect this behaviour to be easier than enforcing serial number to a view's rows). Oracle has the ROWNUM pseudo column that records the SELECT iterarion number. I think it could be used to have row serial number in a view. Is there a work around for sqlite? I can create my view with a SELECT random(), * ... since random() is now 64 bits, but I definetly prefer small, localized numbers. -- jt
RE: [sqlite] Re: How to realize the ROWID in a view?
I have a similar problem (I am using SQLite as a on disk local cache for
a list of about 1,000,000 rows and then filtering and sorting that list
as needed) and a slightly different solution, The best way I have come
up with so far is to create a virtual table with a INTEGER PRIMARY KEY
on it - IE
CREATE TEMP TABLE t
(
id INT PRIMARY KEY,
v VARCHAR(10)
);
INSERT INTO t (v) SELECT DISTINCT x FROM foo;
The advantage for me, since each filtered/sorted list has a reasonably
short life time, is that I can then update the underlying table (which
occurs relatively frequently) and then notify myself and update/recreate
my temp tables (you could do this via a trigger) and I also have
guaranteed unblocked reads from my temp table during updates of the
original table allowing me to have near atomic updates of my sub lists
(by creating a new temp table, swaping which one I use and then deleting
the old one).
Not perfect but it seems to work so far..
- James
-Original Message-
From: Christian Nassau [mailto:[EMAIL PROTECTED]
Sent: 13 June 2006 07:41
To: sqlite-users@sqlite.org
Subject: [sqlite] Re: How to realize the ROWID in a view?
I think you could use min(rowids) as your new rowid like this:
sqlite> create view v as select x,min(id) as id from foo group by x;
sqlite> select * from v;
x id
X 1
Y 2
Z 4
Does this do what you want?
PY wrote:
> Hi All,
>
> I have a problem about the ROWID in a view. I want to simulate a ROWID
in a
> view just like the same purpose in a table.
>
> For Example:
>
> Create Table foo(id INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT);
> insert into foo(x) values('X');
> insert into foo(x) values('Y');
> insert into foo(x) values('X');
> insert into foo(x) values('Z');
>
> Create View v_foo AS
> SELECT distinct(x) FROM foo
> ORDER BY x desc;
>
>
> SELECT * from foo;
>
> id x
> -- --
> 1 X
> 2 Y
> 3 X
> 4 Z
>
>
> My expect result of "select * from v_foo;" is
>
> id x
> -- --
> 1 Z
> 2 Y
> 3 X
>
>
>
> Would you please help to tell me how to finish that?
> Thanks for your grest help.
>
>
>
>
> Thanks,
> VK
>
[sqlite] Re: How to realize the ROWID in a view?
I think you could use min(rowids) as your new rowid like this:
sqlite> create view v as select x,min(id) as id from foo group by x;
sqlite> select * from v;
x id
X 1
Y 2
Z 4
Does this do what you want?
PY wrote:
> Hi All,
>
> I have a problem about the ROWID in a view. I want to simulate a ROWID
in a
> view just like the same purpose in a table.
>
> For Example:
>
> Create Table foo(id INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT);
> insert into foo(x) values('X');
> insert into foo(x) values('Y');
> insert into foo(x) values('X');
> insert into foo(x) values('Z');
>
> Create View v_foo AS
> SELECT distinct(x) FROM foo
> ORDER BY x desc;
>
>
> SELECT * from foo;
>
> id x
> -- --
> 1 X
> 2 Y
> 3 X
> 4 Z
>
>
> My expect result of "select * from v_foo;" is
>
> id x
> -- --
> 1 Z
> 2 Y
> 3 X
>
>
>
> Would you please help to tell me how to finish that?
> Thanks for your grest help.
>
>
>
>
> Thanks,
> VK
>
