Re: [sqlite] Questions about analyze
Clodo wrote: Many thanks, it's a good news that resolve my problem. But still remain a trick, i think the behaviour descripted in my original feedback is strange.. i understand, if all fields have the same value, an index on that have a zero height in computing the best indexes to use, but not use index at all and do a full-table-scan, for what i understand about sqlite, imho is strange... If an index is useless for the query, then a full table scan will generally be quicker than an indexed scan. An indexed scan requires accessing two things (the index plus the data) and also traversing the index which is more 'random access' than sequentially scanning through the table. If the analyse has analysed the right data, then letting it do the table scan is probably the best thing. If it hasn't, then put the right data in the table before doing the analyse... (PostgreSQL does the same thing, if the index is useless, or the database clustering is highly correlated with the index, then it won't use the index at all). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Listing duplicate entries
flakpit wrote: Is there a way of querying the database to list all duplicate entries from a column in the same table? Something like SELECT * FROM mytable WHERE last NOT UNIQUE fred, johnson roger, johnson An unoptimised 'off the top of my head' solution would be: select * from mytable where last in (select last from mytable group by last having count(last) 1); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] delete rows but the database files still have the big size
Joanne Pham wrote: Do I miss some commands here? I thought the database file size shoud get much smaller after the delete operation but it isn't. Can you please help to let me know how to get the database file szie smaller. I have tried VACUUM but the file's size didn't change. VACUUM should do it. When you delete rows, the file size won't change (this is common with most database engines) as that would require a lot of extra work (ie all the bits at the end of the file which are still in use will need moving into all the gaps which are now unused). VACUUM does that for you, but requires exclusive access to the database. DELETE doesn't do it, or it would be really time consuming. DELETE just marks the gaps as unused, so they can be re-used later. Did you make sure that nothing else was using the database before you ran VACUUM on it? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to manage separate lists of ordered items?
[EMAIL PROTECTED] wrote: If I have something like a real estate database where each customer can have an ordered list of houses they want to visit, is there a recommended way to design tables that just link to other tables to create the ordered list? I have a table of houses and a table of customers. My (probably incorrect) intuition is to create a separate table for each ordered list the customer wants. But looking at other databases, I see that people just create one big table for all customers like this: create table customer_list_map ( customer_id references customers( id ), house_id references houses( id ), visit_order integer ); Then they do the following to find an ordered, customer-specific house list: select * from customer_list_map where customer_id=some customer id order by visit_order I don't know anything about databases, but that seems inefficient and more work to maintain (e.g. if multiple lists per customer are later supported). Is there a better way to do stuff like this with SQLite? One general rule about SQL work is that you don't create tables dynamically. So the 'customer_list_map' idea is the 'proper' way to do it. Sometimes there are good reasons to create tables dynamically (especially temporary ones), but this requirement certainly isn't one of them, since the 'customer_list_map' is a good solution to the problem. There's a reason everyone else is doing it that way... As long as you have an index on the customer_list_map on the 'customer_id' column, and you have an index on the house list table on the 'house_id' column, then it should be quick. If you are thinking of having multiple lists per customer, then just add a 'list_id' column to the customer_list_map table in anticipation. Then your select can select on that as well as the customer id. You'll find that the 'customer_list_map' way works well, and is actually a lot simpler to handle in the long run than doing it your proposed way. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Rowid After Sorting
But I need my rowid to be chaged as follows. Rowid Id Name 1 4 aaa 2 3 bbb 3 2 xxx 4 1 zzz You can't. Rowid isn't an index of where the row appeared in the results, it's a 'hidden' field in each row in the table. It just 'happens' that it's sequential by the order that rows were written to the table. If you think of it as just being like any other field in the data, then it'll all make sense. I suspect you're trying to use it for something it's not suitable for. The only thing you should really use it for (IMHO) is as a unique row identifier (hence the name). Some databases use a row 'GUID' or 'OID' instead, but they're essentially the same. Also, note that if you deleted the 'bbb' row from the table (for example), the results would come back as 14 aaa 32 xxx 4 1 zzz So, rowid '2' would be missing. PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] which is faster, PHP or SQLite?
At 16:25 18/02/2008, you wrote: I am new to SQLite and databases, so I am stil learning how to optimize their use... I am working on a shopping cart type of feature, it is actually a favorites feature for a system that displays images in multiple galleries. There is a SQLite table that contains the user_id, gallery_id, and image_id. When a index page is displayed, only a sub set of the images in the gallery are displayed. So the question is what will be faster: 1: Doing a SELECT for each image on the favorites table to see if it is selected 2: Doing one SELECT to get all the images for the current gallery and store that into a PHP array and then simply look in the PHP for each image? My thought is option 2. Is that correct? My thought is that it would depend. I'd guess that If you have 100 images, and you are wanting to show 20, then (2) may be quicker, but if you have 1,000,000 images, then (1) would be quicker. (Assuming you have a usable index on the table). Leaving aside possible database design considerations, it's generally best to let the database engine do the work if it can. With SQLite, I'd qualify that to say that it's best to let SQLite do the work if the queries are simple enough that its optimiser will use indices to do the work. We have found that it can be quicker to do things partially in SQLite and partially in C++. When SQLite would have to do a sequential scan to get the result, it can be quicker to do, say, two indexed scans in SQLite and then operate on the two result sets (eg doing a union or intersect) to produce the final result set, but this is the exception rather than the rule (for us anyway). Paul Smith ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimize a query
At 16:46 17/04/2007, you wrote: This is news to me. Why can't SQlite use more than one index? Possibly because it's 'SQ *Lite*'? The query optimiser in SQLite is a lot less powerful than in some other SQL databases - but then it's a fraction of the size as well... Instead of having two indices on columns A and B, you need to consider having another index on both columns at once. PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is this a valid syntax
At 16:45 17/04/2007, Stef Mientki wrote: I don't understand this behaviour, is this too complex ? or am I doing something wrong ? I use the following syntax, and I get 7 records back, (which is not correct in my opinion) SELECT PO.* FROM Koppel LEFT JOIN PO WHERE (Koppel.K_App == PO.App) AND (Koppel.K_naam == 'MVE') AND (PO.ALL_answered == '0') SELECT PO.* FROM Koppel LEFT JOIN PO WHERE (Koppel.K_App == PO.App) AND (Koppel.K_naam == 'MVE') AND (PO.ALL_answered != '0') I don't know if this is the problem, but, for some reason you're mixing C/C++ syntax in with SQL there. You don't use '==', you should just use '=' You don't use '!=', you should use '' So, try SELECT PO.* FROM Koppel LEFT JOIN PO WHERE (Koppel.K_App = PO.App) AND (Koppel.K_naam = 'MVE') AND (PO.ALL_answered '0') PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Can anyone recommend some ISAM db to me?
At 11:38 08/10/2006, you wrote: Hi, all After trying SQLite on my embedded platform, I feel that it's a little too complicated and time-consuming to my platform, especially the parsing. So, could someone recommend several ISAM ones to me?(I'm a newbie of database*^_^*) You could have a look at Codebase (www.codebase.com) - whether it will work 'out of the box' on your platform or not I can't say, but it comes with source code, and is royalty free, and is quick. We dumped it for SQLite because we wanted the flexibility of SQL, but otherwise it's a decent DB engine supporting xBASE database files. PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Best way to compare two databases
At 16:48 04/09/2006, you wrote: Hi all: I have developed a program that uses a sqlite database. Until now the users downloaded an entire new version of the database weekly from the FTP server. But now the database is too big (about 500.000 records) and i want to make a database actualization system. So, what is the best way (having the old database and the new one) to obtain a file with the differences. Something like this: Hmm, I don't think I'd do it that way. If you do that, then you need to have a copy of the old new database to compare. One way around it is to have a 'journal' table which just contains all the SQL queries which have been actioned (you have to take care if you use transactions) along with an incrementing serial number. Then, the user's software can say 'I have all journal entries up to 252376', and then you can just given them all the journal entries after that number, and they can run the SQL on their end. which will give. You can make your routine which modifies the database just keep a copy of the SQL used whenever the action succeeds, and store that in the Journal table. PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Index usage
At 14:25 20/06/2006, Mikey C wrote: Hi, I just wanted to ask for confirmation that my understanding on how the query optimiser works is correct. SQLite only uses one index for each table in a FROM? Yes What if tables are joined? Does an index get used for each joined table? No, just one index for the query. (It tries to pick the best one) PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/
Re: [sqlite] speed of ORDER BY clause?
At 16:56 14/06/2006, [EMAIL PROTECTED] wrote: I'm finding that ORDER BY is surprisingly slow, and it makes me wonder if I'm doing something wrong. Here's the situation: I need to select a large set of records out of a table, sort them by one column, and then get just a subset of the sorted list. (For example, I might want records 40-60 ordered by date, which is a completely different set than records 40-60 ordered by user ID.) I start with the full list of record IDs I want, and a query something like this: SELECT fields FROM table WHERE recID IN (record IDs ORDER BY dateFld I have a unique index on recID, and an index on dateFld. Try making another index on both fields at once. SQLite can only use one index at a time for each query. So, CREATE INDEX table_recdate ON table (recID, dateFld); See if that makes any difference. When my record IDs list is about 13000 items, the ORDER BY takes about 10 seconds (i.e., the query takes 10 seconds longer than the same query without the ORDER BY clause). Yet if I remove the ORDER BY, grab all the dateFld values into my own array, and sort it myself, the sort takes about 2 seconds. This has left me with the weird result that it's actually *faster* for me to query the database twice: first to get the unordered list of all records and their dates, which I then sort myself, and then query again to get just the subset of records I really want. (That's what we do in some cases, eg if we have to do some sorts of joins it's quicker to get all the data and merge it in memory, rather than use the DB) Am I missing something here? If my own code can sort these dates in 2 seconds, why does sqlite take 10? And why did indexing the dateFld not make any difference (i.e., it took about 10 seconds before I added the index too)? SQLite hasn't got as powerful an optimiser as some DBs such as MySQL etc (but then it is a tiny fraction of the size, so what do you expect). Some DBs also automatically create indices as they decide they're necessary - SQLite doesn't, you need to do it yourself. This means you need to think about things a bit more yourself. The 'EXPLAIN' command is your friend - learn how to use it at least a bit if performance is an issue - you can usually see where there are plain loops (which go around all records) or indexed loops (which are much quicker). Usually careful creation of the suitable indices helps a lot. Read http://www.sqlite.org/optoverview.html - this gives some details of the limited optimisations that SQLite can do, so you can try to take advantage of them PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/
RE: [sqlite] How dangerous is PRAGMA Synchronous OFF?
At 00:54 17/11/2005, Preston Z wrote: If the power never goes out and no programs ever crash on you system then Synchronous = OFF is for you, but the rest of the world might still want it ON. Really it sounds like the thing you need to worry about most is the unexpected termination of your program. If you aren't worried about that... I'm pretty sure that even with Synchronous=off, a program crash won't cause a problem. It's only OS crashes or power failures that are a problem. From the docs: With synchronous OFF (0), SQLite continues without pausing as soon as it has handed data off to the operating system. If the application running SQLite crashes, the data will be safe, but the database might become corrupted if the operating system crashes or the computer loses power before that data has been written to the disk surface. On the other hand, some operations are as much as 50 or more times faster with synchronous OFF. So, if you have a UPS (with ordered shutdown software!) and are happy with the OS reliability, I'd consider using synchronous off as long as I kept regular backups, and didn't care if very recent data was lost. (But this is really the case with most DBs - even with 'synchronous normal' there is a small risk that the database could be corrupted) The bottom line is how important is the speed VS Data? As for detecting a corrupt database, it won't tell you on opening it that it is corrupt... it will even execute statements till it hits a piece of the db that is corrupt (at least the corruption that i have seen, which was from a bad disk, so might not be the same). What has happened here with data corruption has generally been that the program has started OK, then crashed later on. (At least, I'm guessing it was database corruption because the crash happened in the SQLite DLL, and restoring a backup DB file stopped the crashing) PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/
Re: [sqlite] Multi-threading.
At 03:21 27/07/2005, Mrs. Brisby wrote: On Mon, 2005-07-25 at 09:48 -0500, Jay Sprenkle wrote: The theory has been proposed that threads aren't better than separate processes, or application implemented context switching. Does anyone have an experiment that will prove the point either way? It will have to be OS specific though, since I'm sure not all thread implementations are equal. This page might be interesting.. http://john.redmood.com/osfastest.html It shows (pretty conclusively), that 'one process per task' is not the way to go if you want any sort of performance. One thread per task is very good One thread for many tasks is slightly better, with the benefit growing as more threads are created (up to 300-500 tasks there's not a massive difference, by the time you get to 1000 tasks there's a 35% benefit to using one thread for many tasks. Of course, this assumes a well designed architecture... But, one process per task is very poor in comparison to the other ways (on all the platforms they tested) handling only about 5% of load of the 'one thread for many tasks' architecture. PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/
Re: [sqlite] Insert all rows from old table into new table but in sorted order
I can insert all rows of existing table into new table having same columns using query : Insert into NEWTABLE select * from OLDTABLE But I want all rows of NEWTABLE sorted by field No, So I used query Insert into NEWTABLE select * from OLDTABLE order by no desc But it is not giving me sorted output as new table? Can you tell me where I am wrong ??? You can't do that. The 'Insert' may (I'm not sure..) insert the data into 'NEWTABLE' in the right order , but then, when you do an unordered query on 'NEWTABLE', the results are returned in an undefined order - not necessarily in the order they were inserted into the table You should do the sorting when you read 'NEWTABLE' So, instead of Insert into NEWTABLE select * from OLDTABLE order by no desc select * from NEWTABLE do Insert into NEWTABLE select * from OLDTABLE select * from NEWTABLE order by no desc PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/
RE: [sqlite] Insert all rows from old table into new table but in sorted order
At 12:52 30/06/2005, you wrote: you misinterpreted my problem, I want to add all rows of old table into new table but with sorted order I don't want to fire another query (select * from newtable order by desc no ) to give sorted rows, I want to insert all rows in sorted order into new table. But why? It doesn't matter what order the rows are stored in the table. What matters is what order you get them from the table. That's why you do the sorting when you do the query, whenever and whatever the query is. There may be implementation dependent ways to do what you want (eg Richard says that what you're doing should work in current versions of SQLite), but also, these ARE implementation dependent, so, if the underlying engine changes (eg you use a newer version of SQLite, or you switch to MySQL or something), it'll all fall over in unpredictable ways if you depend on this implementation dependent behaviour. Good programming practice dictates that you DON'T rely on implementation dependent behaviour. If SQLite had an *explicit* way of requesting that 'order by'd inserts are honoured, and that an unordered query returns by rowid, and that the rowid can never overflow (like other DBs have clustered indices which are an explicit mechanism), then you may be able to do it, as it would fail in a definite way if you tried to use this implementation dependent behaviour when it wasn't available. But relying on implicit implementation dependent behaviour (eg like expecting a perl hash to be interated through in alphabetic order) is asking for trouble down the line, and shouldn't get through any internal code reviews. (IMHO) SQLite can handle sorting on an index very quickly. So, if you'll often want to sort by 'no', just make an index on the 'no' column, and do 'order by no desc' in all your queries requiring that ordering. You'll be glad you did it that way in the future! -Original Message- From: Paul Smith [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 4:53 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Insert all rows from old table into new table but in sorted order I can insert all rows of existing table into new table having same columns using query : Insert into NEWTABLE select * from OLDTABLE But I want all rows of NEWTABLE sorted by field No, So I used query Insert into NEWTABLE select * from OLDTABLE order by no desc But it is not giving me sorted output as new table? Can you tell me where I am wrong ??? You can't do that. The 'Insert' may (I'm not sure..) insert the data into 'NEWTABLE' in the right order , but then, when you do an unordered query on 'NEWTABLE', the results are returned in an undefined order - not necessarily in the order they were inserted into the table You should do the sorting when you read 'NEWTABLE' So, instead of Insert into NEWTABLE select * from OLDTABLE order by no desc select * from NEWTABLE do Insert into NEWTABLE select * from OLDTABLE select * from NEWTABLE order by no desc PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/ PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/
Re: [sqlite] download db - security question
At 06:54 24/01/2005, you wrote: I was wondering if someone can just download off my webpage the sqlite database. And if they can is there a way to block this type of download throw apache? Don't put the SQLite database in an apache web site directory.. This is easily done if you run your own web server or with some of the better web hosting companies, but with a basic 'home page' hosting service, it might not be possible. If you can only upload to a single directory structure which contains your website, but you can modify the .htaccess file freely, then there are a few options 1) Prevent access to the database directory Put the database in its own directory Make a .htaccess file in that directory. Add the following line to it: deny from all 2) Redirect access to the database Add to your .htaccess file in the folder where the database lives: Redirect mydatabase.db http://www.mysite.com/index.php This will make Apache redirect any attempts to download 'mydatabase.db' to the page 'http://www.mysite.com/index.php' (Test these two options before you rely on them!) If you can't modify .htaccess (or only modify it in a limited way) , then you'll be stuck with 'hiding' the database (eg have it so it could be downloaded from http://www.mysite.com/gwegwedh9874y634nvf7fv/rojr8astasdug4/gfw07y32t23h3.jeg (note that the file extension doesn't need to be .db) Yes, this is security by obscurity, which isn't great, but it'll be reasonably effective, as long as the path to your database isn't shown in the downloadable website page source anywhere... PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/
[sqlite] Is it possible to 'fix' a malformed database
If I do pragma integrity_check on a database I get: *** in database main *** On page 8 cell 0: invalid page number 1581 On page 8 at right child: invalid page number 1582 On page 7 cell 0: invalid page number 593 On page 7 cell 1: invalid page number 594 On page 7 cell 2: invalid page number 1171 On page 7 at right child: invalid page number 1712 On page 5 cell 0: invalid page number 372 On page 5 cell 0: invalid page number 551 On page 5 cell 1: invalid page number 737 On page 5 cell 1: invalid page number 552 On page 5 cell 2: invalid page number 1103 On page 5 cell 2: invalid page number 1014 On page 5 cell 3: invalid page number 1466 On page 5 cell 3: invalid page number 1465 On page 5 at right child: invalid page number 1830 Page 9 is never used Page 10 is never used Page 11 is never used Page 12 is never used Page 13 is never used Page 14 is never used Page 15 is never used Page 16 is never used Page 17 is never used Page 18 is never used Page 19 is never used Page 20 is never used Page 21 is never used Page 22 is never used Page 23 is never used Page 24 is never used Page 25 is never used Page 26 is never used Page 27 is never used Page 28 is never used Page 29 is never used Page 30 is never used Page 31 is never used Page 32 is never used SQL error: database disk image is malformed --- Is there any way to fix this (even if some data is lost), or does it just need throwing away and restoring from backup? (Any ideas what could have caused these types of errors?) PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/
Re: [sqlite] Apostrophes in strings
Apply the php-function sqlite_escape_string on all the string data you insert/update to the database. That should to the trick. Thanks, it just makes a '' from ' instead of \' as with MySQL. Reminds me a bit of Visual Basic... It's the standard SQL way of escaping a ' character (MySQL (and some others) are incorrect to use \' - those need escaping of the \ character as well, which isn't necessary with the proper SQL method) PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/
Re: [sqlite] Problems adding a new column
At 14:03 26/05/2004, Tito Ciuro wrote: Hello, I would like to add a new column to an existing table on-the-fly. I've followed the code found on SQLite's website: http://sqlite.org/faq.html#q13 and modified it slightly to the following: Adding table 'address' to the database... - CREATE TABLE address(ROWID INTEGER PRIMARY KEY,First VARCHAR(255),Country VARCHAR(255),Last VARCHAR(255)); ... add data to the 'address' table ... Adding one more column to 'address'... - BEGIN TRANSACTION; - CREATE TABLE address_backup(ROWID INTEGER PRIMARY KEY,First VARCHAR(255),Country VARCHAR(255),Last VARCHAR(255),SSN VARCHAR(255)); - INSERT INTO address_backup SELECT ROWID,First,Last,Country FROM address; - COMMIT TRANSACTION; The problem I've found is that SQLite reports the following error when INSERT INTO is executed: table address_backup has 5 columns but 4 values were supplied I understand that the source table 'address' contains 4 columns and destination table 'address_backup' has 5, so I would have to copy the source data while ignoring the newly created column in the destination table? Shouldn't the INSERT be INSERT INTO address_backup (ROWID, First, Country, Last) SELET * from Address PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] vers 3.0 concurrency issues
At 17:22 06/05/2004, D. Richard Hipp wrote: Thomas, Basil wrote: I am no technical expert but...could not page locking at least be implemented by the pager module to increase concurrency(very naive...but better than file locking). Page-level locking will not help. For one thing, we cannot do both page-level locking and reader/writer locks on win95/98/ME. Presumably, reader/writer locks are more desirable than page locks and we are not yet ready to abandon win95/98/ME. (You can do both on unix and winNT/2K/XP.) But more importantly, locking is less than half the problem. The hard part is not locking but recovering from a program crash or OS crash or power failure. If we didn't have to deal with crashes and power failures, doing page-level or row-level locking would be (relatively) easy. In version 3.0, you will be able to ATTACH multiple databases and update them all at once (and atomicially). Then if you put each of your tables in a separate database file and ATTACH them as needed, the end result will be something very like table-level locking. Without a central server process to coordinate things, getting any more concurrency than that is not a viable option, as far as I can determine. Given that (IMHO) most concurrency problems seem to be centred around a single application with multiple threads, might it not be possible for that application to 'register' with SQLite in order to implement table locks. So, my app says to SQLite 'register lock manager' Then SQLite says to my lock manager function: - lock database xyz.db for writing - lock table aa for writing - lock table bb for writing - unlock table bb - unlock table aa - unlock database xyz.db The application should put a file lock on the database when it gets the 'lock database' callback, so that other applications sharing the file can't write to it, but if another thread in the same application also does a 'lock database xyz.db', then it would just increment a reference count, not try to lock it again. Similarly for the tables. The application could do all the hard work in this case. If there was no callback registered, SQLite would act like it does now, with file level locks PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] SQLite version 3.0
I've looked at the proposed changes for SQLite V3, and, whilst it all looks reasonable, it does absolutely nothing for me... The things I'd like would be more at the 'lower' levels of the database. I'd like to see the query engine be able to use multiple indices if appropriate, rather than just one as it can currently do. (It might be possible then to extend this to a simple query optimiser or automatic index generation etc at a later stage - possibly as a plug-in-able system based on the 'explain' output of the parser) I'd also like to see the facility for writing our own locking system to be used by SQLite. For instance, SQLite could have callbacks for 'table locks', 'row locks', 'page locks' or whatever. If the callback isn't implemented in the application they could equate to file locks as they do now, but having the callbacks would allow the application to put a file lock on the database to stop other programs interfering, then implement its own table/row/page lock as appropriate if it would help. This would help concurrency within a single (multithreaded) application or server, whilst still allowing the current system for simple usage. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Command-line SQLite
At 15:20 28/11/2003, [EMAIL PROTECTED] wrote:
I'm attempting to use the command-line SQLite to test the speed of certain
selects and how writing them in different fashions affects speed
OK, can anyone explain (no pun intended!) what I should be looking for in
what information explain returns? Are there any timing numbers in there
I can look at?
I always look for the use of indices, and loops and things
create temp table xx (name varchar(30), title varchar(30));
create temp index xx_y on xx(name);
sqlite explain select * from xx where name=fred;
addr opcodep1 p2 p3
-- --
0 ColumnName0 0 name
1 ColumnName1 0 title
2 Integer 1 0
3 OpenRead 0 3 xx
4 Integer 1 0
5 OpenRead 1 4 xx_y
6 String0 0 fred
7 MakeKey 1 0 t
8 MemStore 0 0
9 MoveTo1 18
10MemLoad 0 0
11IdxGT 1 18
12IdxRecno 1 0
13MoveTo0 0
14Column0 0
15Column0 1
16Callback 2 0
17Next 1 10
18Close 0 0
19Close 1 0
20Halt 0 0
This shows (step 5) that the index is being opened, steps 6-9 (I think)
that a lookup on the index for 'fred' is being perfomed,
then step 11 checks if the current index value is bigger than 'fred', and
jumps to step 18 if so
12-13 moves to the next index record
steps 14-15 get the data
step 17 moves to the next record in the index at step 10
So, this will iterate through the index 'xx_y' from 'fred' until the value
of the index 'fred'. (ie not much looping)
(I think)
Then
sqlite explain select * from xx where title=fred;
addr opcodep1 p2 p3
-- -- ---
0 ColumnName0 0 name
1 ColumnName1 0 title
2 Integer 1 0
3 OpenRead 0 3 xx
4 Rewind0 12
5 Column0 1
6 String0 0 fred
7 StrNe 1 11
8 Column0 0
9 Column0 1
10Callback 2 0
11Next 0 5
12Close 0 0
13Halt 0 0
This doesn't open the index.
5-7 compares column 1 ('title') with the text 'fred', if it isn't that it
jumps to step 11
8-9 gives the data
11 goes to the next record at step 5
So, this will iterate through the entire database looking for 'fred'
(potentially lots of looping)
(Note I'm not entirely sure what everything means, but this is what I've
surmised over time)
In general, in a loop, index operations are good, things like 'strne', 'ne'
etc aren't as good because they probably operate more often.
You can't have timing information, because, the 'explain' doesn't look at
the actual data available, so, if you just look at timing, my unindexed
query above would probably look to be quicker, but in a large data set, the
indexed query would actually probably be a lot quicker, because it'd have
to go around the loop less times, even though the index operations
themselves might well be slower than the plain comparisons.
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Improving concurrency in SQLite
Looking at the feedback paper, I wonder whether, possibly, things are being made more complicated than necessary. In most (if not all) of the situations I've seen people mention where concurrency is a problem (and in our own similar situation), it looks as if the problem is all in 'single application, multiple threads' In this case, some of the problems which SQLite are struggling to solve will never happen, eg, file locks are actually unnecessary, and the section 1.3 becomes less of an issue (SQLite could actually work like a client-server database and treat the journal as a recovery mechanism only) I wonder if there couldn't be a case for 'formalising' this behaviour. So, you could specify that an *application* wants exclusive access to a particular SQLite database. So, once the DB is opened, a file lock is put on it, until the application closes. So, the application could call a different API at startup to grab ownership of a DB file, and set up the finer grained lock tables that might be necessary, and then each thread could call something like sqlite_open, with a reference to a handle created by this first API call. So, in effect, SQLite would gain a lot of the advantages of 'client-server' (ie single application, therefore more flexible locking facilities, easier fair queuing etc ) without the extra complexities involved (eg inter-process/network communications facilities) Yes, this wouldn't solve issues where there are multiple processes accessing a DB, but those could use the current facilities. The above changes could also be a staging post on the way to a full client/server version of SQLite - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
