Re: [sqlite] [?? Probable Spam] Re: Vista frustrations
Virgilio Alexandre Fornazin pisze: > Sure. I just tell to do this test to check if the bug is related to this > component, since it debuted on Vista. > Silly thought,but could it be related to database file extension ? I mean , is it possible that OS is caching some files depending on extension like it was with Windows XP system restore "feature". I know it is almost impossible in this case but who knows...\ Boguslaw ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reading a damaged database file?
Gunnar Roth wrote: 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 That's all true, but I have one corrupted sqlite 2.8.16 database from flash disc which cannot be opened either by sqlite.exe or any other sqlite based tool. "Unable to open database file" error (number 14) appear.Though database looks quite ordinary inside :-( A tool to recover data from such databases or at least to print what is corrupted is highly appreciated. sqlite_analyzer cannot open this file too, so it's not helpful. P.S. I could send this database to sqlite author if interested. Regards Boguslaw
Re: [sqlite] SQLite Vs VistaDB - Comparison ???
Christian Smith wrote: Bogus�aw Brandys uttered: [EMAIL PROTECTED] wrote: Mikey C <[EMAIL PROTECTED]> wrote: Please implement table and row level locking. :-) People commonly believe that doing so must be easy. I certainly get a lot of requests for it from people who think they know how. But in fact, row-level locking is extraordinarily difficult. To my knowledge, nobody has yet come up with a way to do it unless you: * Add a server process to coordinate access. * Accept that the database might be corrupted if an application crashes while writing. * Make writing to the database very, very slow. * Double the size of the database file. If you think you know a way to implement row-level locking that does not impose one of the above limitations, then please tell me and I will look into the matter. I'm not an expert but let me propose such solution: Multi- Generational Architecture like Interbase/Firebird can cope with all except the last point (double size of database file) - however in the last case periodical vaccum could shrink database. There is also no need to have server process - each instance of sqlite library could be a server. Shared lock manager could be required or simply each instance of sqlite library could have separate one. Shared lock manager is tricky idea (maybe it could be for example dumb manager in each sqlite library with shared memory pool and if one instance terminate another one could detect it and play that role?) In fact that is as I fairy know how it's implemented in Firebird Classic Server (where each server process has separate lock manager I suppose) This classic server processes are spawn by xinetd deamon. I see sqlite in very similar manner : sqlite library is attached to each spawned process which uses it. In order to communicate with the other lock managers, all instances of the SQLite library would have to be on the same box. Or share the same lock data for example within sqlite database special table (internal like sqlite_master) In that case problem is to serialize access to lock data ,but we are talking about MG architecture where pessimistic locks are rare. If you want MVCC without process communication (as not all processes would be on the same box) you'd need each row update to be synchronous and synced, which would be slower than what we have now. Here I don't quite understand.I thought that MG architecture use transaction manager to manage transactions. There is not need to sync row update because each row has many record versions (and old committed are not removed until vacuum for example) each one with transaction ID and stamp Problem: need to serialize transaction manager if working from concurrent computers on the same database Problem: without vacuum there is more and more garbage inside database The locking protocol could maybe be changed to allow locking at the table level, but such a change would be incompatible with the current locking protocol. And how do you manage multiple rollback journals for multiple writers? A sort of table level locking is already possible anyway using attached databases. This is all about locking (pessimistic) not about MG architecture. I can't see this being a feasible project. Hey! As I stated I'm not an expert. ;-) Regards Boguslaw Brandys
Re: [sqlite] SQLite Vs VistaDB - Comparison ???
[EMAIL PROTECTED] wrote: Mikey C <[EMAIL PROTECTED]> wrote: Please implement table and row level locking. :-) People commonly believe that doing so must be easy. I certainly get a lot of requests for it from people who think they know how. But in fact, row-level locking is extraordinarily difficult. To my knowledge, nobody has yet come up with a way to do it unless you: * Add a server process to coordinate access. * Accept that the database might be corrupted if an application crashes while writing. * Make writing to the database very, very slow. * Double the size of the database file. If you think you know a way to implement row-level locking that does not impose one of the above limitations, then please tell me and I will look into the matter. I'm not an expert but let me propose such solution: Multi- Generational Architecture like Interbase/Firebird can cope with all except the last point (double size of database file) - however in the last case periodical vaccum could shrink database. There is also no need to have server process - each instance of sqlite library could be a server. Shared lock manager could be required or simply each instance of sqlite library could have separate one. Shared lock manager is tricky idea (maybe it could be for example dumb manager in each sqlite library with shared memory pool and if one instance terminate another one could detect it and play that role?) In fact that is as I fairy know how it's implemented in Firebird Classic Server (where each server process has separate lock manager I suppose) This classic server processes are spawn by xinetd deamon. I see sqlite in very similar manner : sqlite library is attached to each spawned process which uses it. Regards Boguslaw Brandys
Re: [sqlite] Random error SQLITE_CANTOPEN on Windows XP SP2 with sqlite3_exec
[EMAIL PROTECTED] wrote: Dave Dyer <[EMAIL PROTECTED]> wrote: The real problem is that sqlite assumes it "owns" the temporary transaction file that it created, and can do anything it wants with it; for example read, rename, or delete it. I think this is a very reasonable assumption. Any other process which gets it's hooks into the file can obviously invalidate this assumption. This is not at all obvious to someone who does not user or program for windows. The obvious quick fix is to retry these file operations that must succeed,... Can some windows programmers suggest patches to os_win.c to implement this. but the underlying problem is a fundamental one that deserves to be addressed. Yes, I agree. But microsoft is unlikely to address the gross deficiencies in their filesystem. So I suppose it will fall to me to find a work-around. I'll look into the matter when I have a chance. -- D. Richard Hipp <[EMAIL PROTECTED]> The solution used by others (for example Firebird) is to create exclusive lock on file (in case of Firebird it is database file) to prevent this situation.However in case of sqlite.dll it not so obvious, because there is no database engine working all the time. Regards Boguslaw Brandys
Re: [sqlite] Random error SQLITE_CANTOPEN on Windows XP SP2 with sqlite3_exec
A. Pagaltzis wrote: * Roger Binns <[EMAIL PROTECTED]> [2006-06-05 00:05]: I don't know if anyone has tracked how well Spotlight (Mac) or Beagle (Linux) work with SQLite files. They cause no problems. The semantics of the filesystem API on *nix systems differ from those of Windows; in short, a file and its name are separate entities on *nix and locking is advisory. One effect is that you can delete the name of a file without the file being (immediately) affected. It is only "garbage collected" once there are no other names for it elsewhere in the filesystem nor any processes holding it open. There are several other effects that cumulatively make concurrent modifications of the filesystem by multiple processes a non-issue in most cases where it requires hacky workarounds on Windows. (It does mean you have to be more careful and diligent about some things, as well.) This is what makes it a reasonable assumption for SQLite that it "owns" any temporary transaction file it created. On *nix, that Just Works with no further effort or any thought about action at a distance. Regards, Sqlite could create journal with exclusive RW access under Windows. That way no other process can read from it.The only problem is that the other process could be just another sqlite driven application ;-) In this case must use the same lock (this means probably single sqlite instance?) which further complicates this issue (shared memory needed (?)) - I don't even know if this is possible. Anyway - it is a bad design of other applications (like TortoiseSVN) - which should never exclusively lock not owned files. Just my 2 cents. Regards Boguslaw Brandys
Re: [sqlite] First query on database takes forever...?
Mats Gefvert wrote: Usually Windows XP/SP2. The file name of the database is usually "vision8020" with no extension. That couldn't be a problem, could it? Shut down system restore Pardon? / Mats System restore feature of Windows XP. I'm guessing now... Regards Boguslaw
Re: [sqlite] First query on database takes forever...?
Mats Gefvert wrote: What system you are using ? If this is Windows XP or 2003, then what is the name of this sqlite database ? Usually Windows XP/SP2. The file name of the database is usually "vision8020" with no extension. That couldn't be a problem, could it? / Mats Shut down system restore
Re: [sqlite] large table performance
Daniel Franke wrote: Hi all. I spent the last days bragging that a single database file as provided by sqlite is a far better approach to store data than -literally- thousands of flat files. Now, I got a small amount of testing data an wow ... I'm stuck. Area: Bioinformatics. Imagine a matrix of data: genetic marker names (attribute A) in columns and individuals (attribute B) in rows. Since the number of features per attribute varies between projects, I decided to create three tables: * Table markers: the genetic markers (attribute A), e.g. 100.000 rows * Table individuals: individual ids (attribute B), e.g. 1.000 rows * Table genotypes: the genetic data Tables "markers" and "individuals" have 2 and 6 columns respectively, a unique primary key, and the (basically) the name of the feature, "genotypes" holds foreign keys to "markers"/"individuals" respectively as well as the genotype column(s), see below. Genotypes are inserted by: INSERT OR ROLLBACK INTO genotypes VALUES ((SELECT id FROM marker WHERE name='$markername$'), (SELECT id FROM individuals WHERE pedigree='$pedigree$' AND person='$person$'), $genA$, $genB$); Where $markername$, ..., $genB$ are replaced with the appropiate values. Given the schema below, feeding a million INSERTs into the database by sqlite3_exec() takes about 30 minutes (this includes transactions, indices and "PRAGMA synchronous=off" as well as a fully optimized build of sqlite). Is there any chance to speed this up? Production datasets could easily bring a billion genotypes ... Any pointer would be appreciated! With kind regards Daniel Franke -- The database schema: CREATE TABLE marker ( id integer PRIMARY KEY AUTOINCREMENT, name varchar UNIQUE); CREATE INDEX markernameidx on marker(name); CREATE TABLE individuals ( id integer PRIMARY KEY AUTOINCREMENT, pedigree varchar NOT NULL, person varchar NOT NULL, father varchar, mother varchar, sex integer NOT NULL, UNIQUE(pedigree, person)); CREATE INDEX individualidx ON individuals (pedigree, person); CREATE TABLE genotypes( markerid integer NOT NULL REFERENCES marker(id), individualid integer NOT NULL REFERENCES individuals(id), genA integer, genB integer, UNIQUE(markerid, individualid)); CREATE INDEX genotypeidx ON genotypes(markerid, individualid); Test with sqlite3 command line shell with transaction(s) That would be a better (an easier to reproduce) test. Regards Boguslaw
Re: [sqlite] Decimal separator
Bert Verhees wrote:
Carl Jacobs wrote:
All would be fine but look at this :
create table test(
price double,
amount double default 0
);
insert into test(price) values("12,0");
amount now = 0.0
The world seems to have settled on using Arabic numerals 0, 1, 2 ... 9. I
think we should think about settling on . as the decimal separator, it
would
save a bit of confusion if we all used the same notation.
It is only the English speaking part of the world using Arabic numerals
is a '.', which is a minority
The rest uses a ','
I think we should settle there
;-)
I think a pragma would be a good thing.
I suspect that "12,0" is being stored as a string. Don't forget that
for all
intents and purposes sqlite3 is typless, so it will store your value in
whatever is the most compact form. So, if you want to, you can store a
picture of yourself in field price!
Regards,
Carl.
The problem is that I use not finished wrapper which is not using
sqlite3_bind_double but instead I must pass double/currency as text
which is created based on Windows regional decimal separator.
That way :
select cast(price as double) from test
always return 0.0
not good
I understand that using sqlite3_bind_double would help but I cannot.
Pragma for decimal separator could help in that case and sqlite3 should
automatically convert 12,0 into double and store as double (like when I
use 12.0)
So,it's not so stupid but rather a conversion hell.
Regards
Boguslaw Brandys
Re: [sqlite] Decimal separator
Bogusław Brandys wrote:
Bogusław Brandys wrote:
Hello,
Maybe someone could explain me how to properly store float/decimal
values into sqlite 3.X database ?
I created test table:
create table test(number double);
insert into test(number) values(11);
Now it looks like:
11.0
so, '.' seems to be always decimal separator.
But under my Windows XP decimal separator is ',' and I have problem
retrieving this value using delphi.
Of course I could set DecimalSeparator to '.' , but this require to
use '.' widely in program which is not feasible.
Is there any pragma to set decimal separator it for sqlite database ?
Regards
Boguslaw Brandys
Ok.Found that when store value as text with proper decimal separator and
precision like : 12,0 all is fine so it's rather a problem of formatting
values.
Regards
Boguslaw Brandys
All would be fine but look at this :
create table test(
price double,
amount double default 0
);
insert into test(price) values("12,0");
amount now = 0.0
Of course it's not critical but maybe new pragma would be useful.
Regards
Boguslaw Brandys
Re: [sqlite] Decimal separator
Bogusław Brandys wrote: Hello, Maybe someone could explain me how to properly store float/decimal values into sqlite 3.X database ? I created test table: create table test(number double); insert into test(number) values(11); Now it looks like: 11.0 so, '.' seems to be always decimal separator. But under my Windows XP decimal separator is ',' and I have problem retrieving this value using delphi. Of course I could set DecimalSeparator to '.' , but this require to use '.' widely in program which is not feasible. Is there any pragma to set decimal separator it for sqlite database ? Regards Boguslaw Brandys Ok.Found that when store value as text with proper decimal separator and precision like : 12,0 all is fine so it's rather a problem of formatting values. Regards Boguslaw Brandys
[sqlite] Decimal separator
Hello, Maybe someone could explain me how to properly store float/decimal values into sqlite 3.X database ? I created test table: create table test(number double); insert into test(number) values(11); Now it looks like: 11.0 so, '.' seems to be always decimal separator. But under my Windows XP decimal separator is ',' and I have problem retrieving this value using delphi. Of course I could set DecimalSeparator to '.' , but this require to use '.' widely in program which is not feasible. Is there any pragma to set decimal separator it for sqlite database ? Regards Boguslaw Brandys
Re: [sqlite] Slow query after reboot
Geoff Simonds wrote: Thanks to everyone for all the help on this problem. I am going to try creating a new thread to touch the tables at startup. Chris Schirlinger wrote: We have the same issue, to get around it we fire a thread when the program starts, intelligently "touching" every table that the user is likely to access (As Michael Sizaki already mentioned a select count(last_column) from big_table; will do it) Since a user is very unlikely to run a program and start doing anything complex within the first 10 to 20 seconds, by the time they DO try and run a query, it is the "second time" the query has been run and this we get the benefit of the Windows XP cache and thus the fast speed I have created a client application that is always running on a users desktop. The application accepts user input and then uses SQLite to perform a few simple queries against a single db file that contains 4 tables. The performance is fantastic after the initial install and normal usage. When the user leaves for the night and tries a query in the morning, the app hangs for 20 seconds and then finally comes back with the results. If the user then duplicates the query immediately afterward, the query is almost instantaneous. In addition, if at any point the user reboots the machine and then retries the query, the same delay happens. The time is spent in the SQLiteDataReader.read() method. Does anybody have any thoughts on why this is happening? Thanks for any help. What is the name of your database (with extension please) ? There are chances that such extension is registered into Windows XP crap called System Restore and each time on start it detect that this file is changed and create a restore snapshot for it. Regards Boguslaw Brandys
Re: [sqlite] Re: Stored Procedure or Functions
Igor Tandetnik wrote: "Vishal Kashyap" wrote Is their any way we can write simple stored procedures or functions in sqlite. If yes please do guide me I need this functionality in one of my open source project. Not in the usual sense, meaning some language that gets stored in the database itself together with the data. The only thing that comes somewhat close is a trigger. It is possible to create a poor man's stored procedure like this: create table sp_dosomething (param1 int, param2 char); create trigger sp_dosomething_impl instead of insert on sp_dosomething begin -- one or more sql statements possibly referring to -- new.param1 and new.param2 end; -- To invoke: insert into sp_dosomething values(1, 'hello'); Note that triggers are rather limited in what they can do. They are just a bunch of SQL statements, there is no control flow (loops, if then else, goto) beyond what little you can implement in pure SQL. They cannot return values, except indirectly by inserting or updating some table. SQLite does not support cascading triggers, so if your "stored procedure" manipulates some table to which regular triggers are attached (perhaps ensuring data integrity), those triggers won't run. SQLite supports custom functions - see sqlite3_create_function[16]. You write them in C (or any other language that has bindings to SQLite API) and you have to install them every time you open a DB handle with sqlite3_open, before you can refer to them in your SQL statements. They are not stored in the database file itself. Finally, SQLite prepared statements (sqlite_prepare) can be thought of as simple stored procedures defined in your program. Similar to custom functions, you can prepare a statement right after opening the database, then keep it around. Igor Tandetnik Maybe it would be fine and useful to add lua scripting language to sqlite (maybe in shared library to not broke current code ) Regards Boguslaw Brandys
Re: [sqlite] Follow-up to "database disk image is corrupted" problem
Eric Scouten wrote: Earlier today I posted a question about SQLite compiled into my application generating a "database disk image is corrupted" on a particular query, but the command-line application does not (even using the same database file). I haven't seen that message posted yet, but I have some additional information that may help in understanding the problem. The query that's running is essentially this: (table names obfuscated since this is from an unannounced product) SELECT foo.rowid FROM foo WHERE foo.x == ?; When run from the command-line application (either version 3.1.3 or 3.2.8), this query runs without error. sqlite> SELECT foo.rowid FROM foo WHERE foo.x == 25; 61// expected result sqlite> explain SELECT foo.rowid FROM foo WHERE foo.x == ?; 0|Goto|0|20| 1|Integer|0|0| 2|OpenRead|1|117|keyinfo(1,BINARY) 3|SetNumColumns|1|2| 4|Variable|1|0| 5|NotNull|-1|8| 6|Pop|1|0| 7|Goto|0|18| 8|MakeRecord|1|0|o 9|MemStore|0|0| 10|MoveGe|1|18| 11|MemLoad|0|0| 12|IdxGE|1|18|+ 13|RowKey|1|0| 14|IdxIsNull|1|17| 15|IdxRowid|1|0| 16|Callback|1|0| 17|Next|1|11| 18|Close|1|0| 19|Halt|0|0| 20|Transaction|0|0| 21|VerifyCookie|0|181| 22|Goto|0|1| 23|Noop|0|0| When run from inside our application (using version 3.2.7 -- sorry, haven't upgraded yet), however, we get different VDBE instructions: 2005-12-22 15:30:33.478 Got error: database disk image is malformed Statement: SELECT foo.rowid FROM foo WHERE foo.x == ?; addr | opcode| p1 | p2 | p3 -- 0| Goto | 0 | 20 | 1| Integer | 0 | 0 | # (index for value x on table foo) 2| OpenRead | 1 | 100 | keyinfo(1,BINARY) 3| SetNumColumns | 1 | 2 | 4| Variable | 1 | 0 | 5| NotNull | -1 | 8 | 6| Pop | 1 | 0 | 7| Goto | 0 | 18 | 8| MakeRecord| 1 | 0 | o 9| MemStore | 0 | 0 | 10 | MoveGe| 1 | 18 | 11 | MemLoad | 0 | 0 | 12 | IdxGE | 1 | 18 | + 13 | RowKey| 1 | 0 | 14 | IdxIsNull | 1 | 17 | 15 | IdxRowid | 1 | 0 | 16 | Callback | 1 | 0 | 17 | Next | 1 | 11 | 18 | Close | 1 | 0 | 19 | Halt | 0 | 0 | 20 | Transaction | 0 | 0 | 21 | VerifyCookie | 0 | 199 | 22 | Goto | 0 | 1 | 23 | Noop | 0 | 0 | For the record, our application and the SQLite command-line application have been pointed to exactly the same database file. No other changes have been made to the file, so they should be expected to behave identically. But they don't. At address 2, the P2 parameter (which should be the root page # for the index of field "x" on table "foo") is 100 in the embedded case and 117 in the command-line case. When I inspect the sqlite_master table, it is obvious that 117 is the correct answer. Page 100 is the root page of an unrelated table (not an index). Has anyone seen anything like this before, and if so, can you help us understand what would cause SQLite to generate this incorrect VDBE? -Eric Don't know if it's related but I have (under 2.8.17 and 2.8.16 also) simple update query UPDATE PRACOWNICY SET IMIE='Jacek',NAZWISKO='Placek' WHERE NR_PRACOWNIKA=12 which runs fine on sqlite.exe but in my application it simply do nothing (return SQLITE_OK but do not update row) More to say; this query runs correctly on both sqlite.exe and my application: UPDATE PRACOWNICY SET IMIE='Jacek' where NAZWISKO='Kowalski' Regards Boguslaw Brandys
Re: [sqlite] Convert / Import
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Griggs, Donald wrote: > -Original Message- > From: administrator [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 16, 2005 5:01 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Convert / Import > > > Does anybody know a good/quick way to convert from V.Fox 8 .dbf to sqlite > format ? I tried to export to .csv from Visual Fox , then to import into .db > using SQLite Database Browser but it just takes to much time (running for > 2h50m now and still going, a ~ 2.000.000. rows each with 32 fields ) and > hassle. Any quick way to do this ? > > Thank you. > > Dumitru Florin. Tray SQLiteExplorer function to import from ADO source. Regards Boguslaw Brandys -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.0 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDBvbZtuGICzHOh+YRAtHbAJ966TneTsI84Dn+IuzLcSEfOtcwYgCfaz9s Y2nQ8YJo+1JqNvcN4lW2wcE= =90l6 -END PGP SIGNATURE-
Re: [sqlite] using sqlite as a temporary database to process lots of data
Patrick Dunnigan wrote: > I am currently using SQLite to process @ 400 million records (and climbing) > a day by reading files, importing them into SQLite, and summarizing. The Wow! How much memory those 400 milion of records is using ? Do you use Sqlite 2 or 3 version ? I was told that SQlite 3 has much lighter overhead in record size then Sqlite 2. Is that true ? How much is larger SQlite memory Btree then simple C/C++ structure ? Does it make sense to create index on memory table ? Best regards Boguslaw Brandys
