Re: [sqlite] a familiar name in the iPhone SDK
SQLite is in iPhone since the beginning. I think that it is used in almost any iPhone application that stores data in a structured way: Contacts, call history, Google Maps, Safari bookmarks and history, etc, except the iPod application. At least that's what I remember since I browsed the file system of my jailbreaked iPhone. On 3/6/08, P Kishor <[EMAIL PROTECTED]> wrote: > > -"The Media layer is everything you'd expect from Apple" > -Also include SQLite, Core Location > -Core OS has the OS X Kernel, Lib System, BSD TCP/IP, Sockets, > Security, Power Mgmt, Keychain, Certificates, File System, Bonjour > -Took everything we knew about creating stuff with Cocoa and > everything about a touch API for iPhone to build Cocoa Touch -Cocoa is > great, but based on mouse & keyboard input > -Used all of the above (except Cocoa) for iPhone OS > -Cocoa, Media, Core Services, CoreOS" > > > attribution: Ars coverage at > > < > http://arstechnica.com/news.ars/post/20080306-live-coverage-of-the-iphone-software-roadmap-announcement.html > > > > -- > Puneet Kishor http://punkish.eidesis.org/ > Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ > Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] a familiar name in the iPhone SDK
On 3/6/08, P Kishor <[EMAIL PROTECTED]> wrote: > -"The Media layer is everything you'd expect from Apple" > -Also include SQLite, Core Location Yup. Very prominently mentioned, by name, I might add, as part of Core Services. SQLite, the only buzzword-compliant SQL database on the iPhone. http://events.apple.com.edgesuite.net/rtp20e92/event/index.html?internal=fj2l3s9dm > -Core OS has the OS X Kernel, Lib System, BSD TCP/IP, Sockets, > Security, Power Mgmt, Keychain, Certificates, File System, Bonjour > -Took everything we knew about creating stuff with Cocoa and > everything about a touch API for iPhone to build Cocoa Touch -Cocoa is > great, but based on mouse & keyboard input > -Used all of the above (except Cocoa) for iPhone OS > -Cocoa, Media, Core Services, CoreOS" > > > attribution: Ars coverage at > > > <http://arstechnica.com/news.ars/post/20080306-live-coverage-of-the-iphone-software-roadmap-announcement.html> > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] a familiar name in the iPhone SDK
-"The Media layer is everything you'd expect from Apple" -Also include SQLite, Core Location -Core OS has the OS X Kernel, Lib System, BSD TCP/IP, Sockets, Security, Power Mgmt, Keychain, Certificates, File System, Bonjour -Took everything we knew about creating stuff with Cocoa and everything about a touch API for iPhone to build Cocoa Touch -Cocoa is great, but based on mouse & keyboard input -Used all of the above (except Cocoa) for iPhone OS -Cocoa, Media, Core Services, CoreOS" attribution: Ars coverage at <http://arstechnica.com/news.ars/post/20080306-live-coverage-of-the-iphone-software-roadmap-announcement.html> -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_column_int64 fails!
I use a C++ wrapper library libsqlite3x from http://wanderinghorse.net/computing/sqlite/. I have a table which has file dates as INTEGER and I have inserted FILETIME (8 bytes) values using code. Now I am trying to retrieve those dates. Here is my code: FILETIME ft; INT64 n; // sqlite3_column_int64 does not return correct value! Why?? n = cursor.getint64(2); n = sqlite3_column_int64(cmd.handle(), 2); int size = 8; // Force it to be INT64 n = *(PINT64)cursor.getblob(2, size); ft.dwLowDateTime = n & 0x; ft.dwHighDateTime = n >> 32; SYSTEMTIME st; FileTimeToSystemTime(&ft, &st); std::string path = cursor.getstring(4); printf("%d/%d/%d %d:%d:%d.%03d - %s\n", st.wYear, st.wMonth, st.wDay, st.wHour, st.wMinute, st.wSecond, st.wMilliseconds, path.c_str()); cursor.getint64 calls sqlite3_column_int64, so they are essentially the same. But neither of the two methods returns correct value (most are 0's). But getblob works correctly. BTW, I have sqlite3 source built into my program. I tried to examine the source, but could not see why. Help please! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance
I believe you can speed up the inserts by batching things in a transaction. The prevents a journalling operation after each insert. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of James Kimble Sent: Thursday, March 06, 2008 1:30 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Performance > > >That's sounds like good advice. I'll do that. >> >> Working with flash in this way is going to be a challenge. With >> limited number of writes in a lifetime (this device needs to last >> approx 20 >> years...) I will have to make some major design decisions around how >> I handle the writes. > > >> How important is the persisent data? Is it kept for audit, >> statistical analysis, what? Basically, can you afford to lose it, or >> at least a subset of it? If so , then I'd say maintain the data in an >> in-memory database, and write out the data to disk (using safe >> synchronous writes) at whatever intervals you desire. >> I say use safe synchronous writes, as recovery may be an issue if you >> don't write safely. Not what you need on an embedded system where >> user interaction may be required. >> Christian Most of my data is not persistent. I am thinking of keeping everything RAM based with a write out only done opportunistically. Values need to be retained for logging purposes but configuration settings are very stable so they can be saved only when changed. I have another question and I think I know the answer but just to be sure... I created a 40 column table with 10,000 rows as a test database for a reader and a writer process to bang on (performance proof). The table is as so: sqlite3 test.db 'create table PerfTest1 (name varchar(20), value1 int, value2 int, value3 int, value4 int, value5 int, value6 int, value7 int, value8 int, value9 int, value10 int, value11 int, value12 int, value13 int, value14 int, value15 int, value16 int, value17 int, value18 int, value19 int, value20 int, value21 int, value22 int, value23 int, value24 int, value25 int, value26 int, value27 int, value28 int, value29 int, value30 int, value31 int, value32 int, value33 int, value34 int, value35 int, value36 int, value37 int, value38 int, value39 int)' The data is repetitive junk. Just: "key1", 1, 2, ,3 . "key2", 1, 2, 3 What's driving me mad is that when I do a select from the command line like so: sqlite3 test.db `select name from PerfTest1 where name like "key1%"' The rows returned stop at "key199". No select will return a row past that point. However if I do a simple: sqlite3 test.db 'select name from PerfTest1' and just let it go it prints all 1 rows!! Is this due to the type of query prepartion done from the command line interface? Maybe limits the size of something? That doesn't make a lot of sense either though because if I query the specific row I want it returns nothing. sqlite3 test.db 'select name from PerfTest1 where name = "key1000"' Returns 0 rows. Any idea what's going on there?? It also took about 15 minutes for the .import command to insert the 10,000 rows into the table from a text file. That's was a little scarey. (Sorry for the length. Probably should have started another thread) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Are callbacks re-entrant and can you setjmp/throw?
I am working on a language binding to SQLite, and I am trying to figure out what features can safely be made available to untrusted code. And I am trying to figure out if I can make callbacks/handlers/authorizer safe for untrusted code, I suspect not. So, is it safe to make normal SQLite API calls from inside a SQLite callback/handler? Is it safe to setjmp/throw out of a callback/handler? Basically is the state of the statement and database in a consistent state when callbacks/handlers are called? William Bardwell [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance
> > >That's sounds like good advice. I'll do that. >> >> Working with flash in this way is going to be a challenge. With limited >> number of writes in a lifetime (this device needs to last approx 20 >> years...) I will have to make some major design decisions around how >> I handle the writes. > > >> How important is the persisent data? Is it kept for audit, statistical >> analysis, what? Basically, can you afford to lose it, or at least a subset of >> it? If so , then I'd say maintain the data in an in-memory database, and >> write >> out the data to disk (using safe synchronous writes) at whatever intervals >> you desire. >> I say use safe synchronous writes, as recovery may be an issue if you don't >> write safely. Not what you need on an embedded system where user interaction >> may be required. >> Christian Most of my data is not persistent. I am thinking of keeping everything RAM based with a write out only done opportunistically. Values need to be retained for logging purposes but configuration settings are very stable so they can be saved only when changed. I have another question and I think I know the answer but just to be sure... I created a 40 column table with 10,000 rows as a test database for a reader and a writer process to bang on (performance proof). The table is as so: sqlite3 test.db 'create table PerfTest1 (name varchar(20), value1 int, value2 int, value3 int, value4 int, value5 int, value6 int, value7 int, value8 int, value9 int, value10 int, value11 int, value12 int, value13 int, value14 int, value15 int, value16 int, value17 int, value18 int, value19 int, value20 int, value21 int, value22 int, value23 int, value24 int, value25 int, value26 int, value27 int, value28 int, value29 int, value30 int, value31 int, value32 int, value33 int, value34 int, value35 int, value36 int, value37 int, value38 int, value39 int)' The data is repetitive junk. Just: "key1", 1, 2, ,3 . "key2", 1, 2, 3 What's driving me mad is that when I do a select from the command line like so: sqlite3 test.db `select name from PerfTest1 where name like "key1%"' The rows returned stop at "key199". No select will return a row past that point. However if I do a simple: sqlite3 test.db 'select name from PerfTest1' and just let it go it prints all 1 rows!! Is this due to the type of query prepartion done from the command line interface? Maybe limits the size of something? That doesn't make a lot of sense either though because if I query the specific row I want it returns nothing. sqlite3 test.db 'select name from PerfTest1 where name = "key1000"' Returns 0 rows. Any idea what's going on there?? It also took about 15 minutes for the .import command to insert the 10,000 rows into the table from a text file. That's was a little scarey. (Sorry for the length. Probably should have started another thread) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance
On Wed, Mar 05, 2008 at 09:02:17PM -0500, James Kimble wrote: > > > One thing I can highly recommend on embedded systems, especially flash > > based ones, is turn pragma synchronous to off. Having sqlite write every > > record modification to the flash, is a stunningly expensive process, > > even when it's all encapsulated in a large transaction. Let linux handle > > the synchronisation and write caching for you. A lot less robust, but, > > the benefits more than outweighed the cost. If you need guaranteed write > > to disk, then perform the synch yourself. > > > Cost vs benefit and all that guff. > > > That's sounds like good advice. I'll do that. > > Working with flash in this way is going to be a challenge. With limited > number of writes in a lifetime (this device needs to last approx 20 > years...) I will have to make some major design decisions around how > I handle the writes. How important is the persisent data? Is it kept for audit, statistical analysis, what? Basically, can you afford to lose it, or at least a subset of it? If so , then I'd say maintain the data in an in-memory database, and write out the data to disk (using safe synchronous writes) at whatever intervals you desire. I say use safe synchronous writes, as recovery may be an issue if you don't write safely. Not what you need on an embedded system where user interaction may be required. Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Limiting the number of results from a query per group
"Paul Hilton" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I have a query where I wish to limit the number of rows in the result > per group, but not limit them overall. > > So for example if I have a table Intable > > Create Table Intable (ID Integer Primary Key, Grp Integer, .other > stuff.); > > I would like to select up to a limited number of records for each > value of grp, say 10. This seems to work: select * from (select distinct grp from Intable) t1, Intable t2 where t2.ID in (select ID from Intable t3 where t3.grp=t1.grp limit 10); Performance will probably be abysmal. At least create an index on Grp. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to recover from database corruption? (and why does it happen?)
En/na Luca Olivetti ha escrit: >> I don't believe that there is any reason to expect a vacuum to correct a >> corrupt database. > > Ah, ok, I thought that it did (at least it did with 3.3.8). Side note: it wasn't actually completely corrupted (hard corruption came after), or at least I think these were only minor errors: $ sqlite3 almacen.db.malo SQLite version 3.5.6 Enter ".help" for instructions sqlite> pragma integrity_check; *** in database main *** Main freelist: 1 of 1 pages missing from overflow list starting at 0 wrong # of entries in index fifo_referencias wrong # of entries in index sqlite_autoindex_fifo_fis_1 vacuum with 3.3.8 fixed all three, while with 3.5.6 it only fixes the first one. Bye. -- Luca Olivetti Wetron Automatización S.A. http://www.wetron.es/ Tel. +34 93 5883004 Fax +34 93 5883007 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to recover from database corruption? (and why does it happen?)
En/na Dennis Cote ha escrit: > Luca Olivetti wrote: > >> Well, I posted all the error messages I got back from sqlite, as well as >> the snippet of code I use to execute all my queries, but I can post more >> details if it can be useful (I'd only need to know which details could be). >> > > Ah, I'm sorry, I missed your first post. > > The original corruption may well have been caused by omitting the thread > safe option on the version of SQLite you were using. Pheew, I hope it is really so. > I don't believe that there is any reason to expect a vacuum to correct a > corrupt database. Ah, ok, I thought that it did (at least it did with 3.3.8). > In general database corruption must be avoided, since > once it happens, it can't be corrected. That's my aim, yes, avoid the corruption, but I first must know what caused it. > The purpose of the vacuum operation is to reduce the size of the > database file by releasing any unused pages that may be contained in the > database file. If you are not concerned about disk space, there is no > need to vacuum ever. No, I'm not concerned about disk space, I just tried a vacuum (actually a vaccum after each detected corruption) as a misguided attempt of automatic recovery. > If you have valuable data in the database file you may be better off > adding an automatic backup process that saves backup copies of the > database file periodically. Then you can restore a backup and continue > from their if you detect a corrupt database. The data is only valuable as it is controlling a (relatively simple[*]) industrial machine that needs this data to work. The constantly changing data is relatively easy to introduce manually, but it's a long winded, manual, process (it takes around an hour, and you can't stop the line for an hour). I'm currently doing a backup every hour, and keeping the last day worth of backups, but it's not possible to work directly from the backup (you have to check manually what you have since the data changes every minute). [*]sorry for not explaining it before: it's an automated store in a car factory, where you put boxes full of pieces on one side, and take one one piece at a time from the other side depending on what's coming on the production line. Basically the database is used to keep track what's in the store and to decide what you need to fill it up with, based on historical data and on the next 200-300 cars coming down the line. > You might also want to add a process that does nothing but run integrity > checks on the database in order to try to determine when the corruption > occurred in case it happens again. This process would log the success or > failure of each check. This may greatly reduce the number of operations > you have to consider prior to a failure if the corruption happens again. I could consider it, but since I'm already logging everything and every couple of hours an external program is spawned (and it does an integrity check, as every process that opens the database) I don't think it's necessary. Thank you. Bye -- Luca Olivetti Wetron Automatización S.A. http://www.wetron.es/ Tel. +34 93 5883004 Fax +34 93 5883007 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users