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= > 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] 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] 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] 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 FROM WHERE recID IN ( 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
At 15:47 30/06/2005, Steve O'Hara wrote: Some databases do actually allow you to maintain an insertion order. They do this for performance reasons so that the high cost of sorting is avoided - we have a few newspaper databases (>30 million full text stories) that have their primary key defined as the inverse story insertion date - this means that when a journalist searches for a story, they always get the results in 'latest first' order, which is nearly always what they want. Hmm, that just means that the database is storing the data in the order of the primary key. Note that *I* wouldn't rely on an unordered search doing returning data in order of primary key - it sounds like a maintenance nightmare when that behaviour changes in the underlying DB. I'd always do a 'order by ' in the query. The database should optimise that out if that's how it normally returns data anyway, but then, if the underlying behaviour changes, then it would still work correctly, if marginally slower (any DB should always be able to order by an index (especially the primary key index) very quickly anyway) 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] 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] Question about the LIMIT keyword
At 14:46 14/06/2005, you wrote: An alternative method is to define a separate table that keeps the COUNT of the rows, and define a trigger that keeps that COUNT updated every time you DELETE/INSERT/UPDATE on the main table. I actually thought of that as well, but he wants to know how many records match a particular query - not how many are in a particular table. If there are only a few different queries, it might still be doable, but if the number of possible queries is large, it's not really practical. PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/
Re: [sqlite] Question about the LIMIT keyword
At 14:06 14/06/2005, you wrote: If I would use SELECT COUNT(*) then I have to ask the query again, right? E.g: First I have to do: SELECT * FROM data WHERE Foo == "bar" LIMIT 1000; to get the data and then SELECT COUNT(*) FROM data WHERE Foo == "bar"; to get the total lnumber of lines. The problem is that the database is very large and the query can be complex, so I want to avoid to use two queries. I had hoped that there would be a way to do just ask one query with the LIMIT keyword and also get the total number of lines. In that case, I'd probably just do the query once, without the LIMIT, but throw away the results after you've reached 1000, just count the number of rows. In C++ this seems to be pretty quick, but I'm not sure what it would be like if the client code was written in TCL. Johan >Bert Verhees wrote: >IMHO COUNT does a complete tablescan to count the records, it did in a >previous version of sqlite >Bert > >> Paolo Vernazza wrote: >> I'm not sure what do you need... but you tried using >> SELECT COUNT(*) FROM etc etc etc >> Paolo >> >>> Trygg Johan wrote: >>> >>> Hello, >>> >>> I'm using SQLite with TCL and I have a small question: >>> >>> Is it possible to get information on how many lines a select query with >>> the LIMIT keyword would have produced if I hadn't used the LIMIT >>> keyword? >>> >>> One way of doing this would be to do another query without the LIMIT >>> keyword, count the number of lines you got, and then throw away the >>> resulting data, but this seem to be a bit of waste of both resources and >>> time... so I hope someone has a better solution :) >>> >>> Thanks in advance, >>> Johan Trygg >>> 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)); ... ... 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] Re: sqlite-users Digest 22 May 2004 05:23:11 -0000 Issue 115
Sorry, Not paying too much attention to the authors in the thread. It never ceases to amaze me the endless number of "software developers" wishing to develop a "commercial" product using "free" software. "Unfortunately", some times the best tools for the job are free... SQLite and Lua are two 'free' components we use in our commercial software. There is just nothing like either of these for ease of integration, capabilities and performance as well small size. In general we look for commercial components first, but for databases there's not much out there. You have either CTree or SQL Server/Oracle etc (obviously for a small low cost commercial product, requiring someone to buy SQL server to go with it is a bit impossible, and CTree's run-time licencing tends to the extortionate), or free programs like MySQL, SQLite, MSDE etc. For scripting you have things like VBS (nightmare if you're not writing a very COM-ish program) or free programs like Python, Perl (nightmares to integrate) or Lua.. (We also use commercial components like SmartHeap, Leadtools etc - because those are good - there may be open source free equivalents, but we do generally prefer commercial WHERE POSSIBLE) So, what do you do if you don't want to use free software? Write your own? I'd prefer to use an open source free program than write my own.. If all else fails I'm in no worse a position anyway (I can always debug the code myself if the authors disappear) and the open source alternative (especially if it's as popular as SQLite or Lua) is almost certainly more extensively tested (if only by all the users doing weird things to it) In my experience, commercial doesn't *always* mean less buggy, or better support (if you pick your free components wisely), it just means more expensive. When we start adding SSL support, we'll probably use OpenSSL - again there's nothing else suitable for the job. BTW, if there were commercial developer support licences for SQLite and Lua, we'd probably look into it seriously, but (AFAIK) there aren't, so we can't. - 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] Ticket 575
Is there any progress on this ticket (temporary file storage method problem)? If not, can anyone suggest any workarounds? I've just discovered here that it looks like we're getting really bad performance hits on a Windows machine with temporary files when people use certain virus scanners. Also, in that case, there seems to be a big difference when using temporary files depending on whether writes are done in a transaction or not (which is not supposed to be the case) - 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] Let us make SQLite more powerful
> If one always store fax numbers in the third element of the Phones > collection, one could find all rows that contain a particular fax > number > like this: > > SELECT LastName FROM Contacts WHERE Phones(3) = "2064814442" SELECT LastName FROM Contacts WHERE Phones LIKE ':%::%::phonenumber:%'; No, that would find phone number in the third, fourth, fifth etc element as well.. (Regexps? ;-) ) > One can query data in a collection or structure-valued column just as > one can query data in a conventional table. To the query interface, a > collection of structures should appears as a logical table with its > parent table name automatically prefixed. So, for instance, a query > to find all LineItems in the Invoices table would look like this: > > SELECT PartNo, QtyOrdered, UnitPrice, QtyOnHand, EditDate > FROM Invoices.LineItems Why exactly do you think the JOIN operator is the wrong tool for this? If you think this is easier to read, then consider creating VIEWs to store intermediate queries. That was my thought. SQL doesn't have collections (AFAIAA), and SQ **Lite** definitely shouldn't You can do this by having a 'phone numbers' table with a contact ID column and join it to your contacts table linking on the contact ID. That's what we do with our systems here, and it works fine. - 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] Concurrency in SQLite
At 16:21 24/11/2003, Doug Currie wrote: It looks to me that several users are (a) in a uniprocess environment, and (b) inventing their own SQLite db access synchronization code. An SQLite fine grained lock manager for threads in a single process would address these same issues, with better concurrency as well. Are others in the position of having to create their own SQLite db access synchronization code? Yes, we have to do that. It's made a bit harder by the fact that the DB file could be accessed by other software so we have to handle busy states as well as trying to stop them with our own synchronisation code. - 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]
Re: [sqlite] backslash problem
At 10:28 31/10/2003, you wrote:
Hi!
> I see sqlite doesn't recognize the \ (backslash) as the backslash
> character, instead sqlite considers it as a normal character infact
> if I insert data into a field like:
> insert into table1 values('pippo\\pluto')
> sqlite inserts the value as: pippo\\pluto
> while other database (PostgreSQL, MySQL, BerkeleyDB) insert it as:
> pippo\pluto
>
> Seems such behavior is conform to the SQL standard.
I don't know what the standard says.
Oracle inserts the value as 'pippo\\pluto'.
So does MS SQL Server
I'd say that it's PostgreSQL and MySQL that are behaving 'incorrectly'..
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Where statements are they case sensitive?
I just checked something and noticed that the WHERE statement is case sensitive. I have check this in SQL Server and it is not case sensitive. I am using 2.8.5 and 2.8.6. As an example in the northwind DB I have for SQLite . There is a table called Orders select * from sqlite_master where Name = 'orders' return no rows but select * from sqlite_master where Name = 'Orders' does return rows but create table orders(a) returns an error with the table already exists. Should the where statement be case sensitive , By default I don't think it should. Should I report a bug on this or was it by design?? I think MS SQL Server is the odd one out here. Oracle is case sensitive by default, as are many others. Personally, I'd like a pragma or something to make index searches case insensitive, but I get by by forcing all case insensitive search fields to upper case when storing to the database and when doing the search. (You can do case insensitive searches by using "field like 'xyz'" instead of "field='xyz'" or "upper(field)='XYZ'", but these won't use the indices, so it'd be nice to be able to set the case sensitivity of indices individually) - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Testing the new SQLite mailing list
At 19:43 16/10/2003, Bernie Cosell wrote: On 16 Oct 2003 at 17:08, Paul Smith wrote: > At 16:44 16/10/2003, you wrote: > >I prefer the 'reply to sender' default rather than > >'reply to all'. ... > It really depends what you see the purpose of the list being. > > If replies only go back to the original message sender, then there are > quite a few disadvantages: The problem with this is that it presumes [by your use of 'only'] that the list membership is either entirely [or primarily] made of folk who cannot manage to do anything fancier/cleverer with their email clients than hit "reply". now, if you had said that "..._some_ replies will go back ...unintentionally..." that'd be closer to the fact, I think... I'm not too fussed either way - but I find it much more convenient to have 'reply-to-list' (One of the things that REALLY annoys me is receiving duplicate emails - you'll notice that if I reply to a list where I have to reply-all, then I'll always edit the To/Cc fields so it only goes back to the list.) - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Testing the new SQLite mailing list
At 16:44 16/10/2003, you wrote: I prefer the 'reply to sender' default rather than 'reply to all'. As the list membership grows, the latter doesn't scale as well. At some point in their growth, most lists and newsgroups hit this barrier, and I think we're seeing that now in sqlite-users, where several people will offer the same answer, or make similar followup comments or even just send 'Thanks, that worked' to everyone. (Sun-managers adopted a great convention a long time ago, asking people to only reply to sender, but that senders were asked to summarize the answers back to the list, so that there was a useful price for asking questions that was paid back to the entire list) I'd tend to disagree... It really depends what you see the purpose of the list being. If replies only go back to the original message sender, then there are quite a few disadvantages: - there is no archive of the replies for future users to see, so the question will be asked over and over again (even more so than normal) - there will be no 'peer review' of answers, so answers may be total rubbish, and no one will see it to say 'that's wrong' - group discussions can't take place - just lots of single responses If you have replies going to the original message sender only, then, really, you should just have a '[EMAIL PROTECTED]' email address, with no mailing list... I understand what the mailing list docs mean about autoresponders etc - I'd solve that by having a moderated list with messages going to the whole list, there are other ways of solving it as well for most cases (ie delivery failure reports etc) (Remember the Yahoo groups default was 'reply to list'. I don't recall anyone complaining about that particular behaviour :-) ) - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Testing the new SQLite mailing list
At 16:20 16/10/2003, Kevin Waterson wrote: This one time, at band camp, "D. Richard Hipp" <[EMAIL PROTECTED]> wrote: > If you have received this message, it means you are automatically > subscribed to the new SQLite mailing list. There should be instructions > at the bottom of this message telling you how to unsubscribe if that > is your choice. Seems to work fine :) Hmm, almost. If I just do a 'reply', then it goes to the message sender, rather than back to the list. I have to do a 'reply-all' to get it to go back to the list. This is different from the Yahoo Groups behaviour and may catch some people out. (Also, it might be a good idea to post a message to the old Yahoo Groups to tell everyone about the new list in case it isn't working for some people - if you haven't already done so) - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
