Re: [sqlite] An "unable to open database file" error that has nothing to do with opening database file
On Wed, Jun 5, 2013 at 4:51 PM, Warren Young wrote: > > Bottom line, I suspect there is a bug in SQLite here. It shouldn't be using > backslashes in a Cygwin build. I think the problem is that the Cygwin distribution has the wrong version of SQLite, one built for unix. I downloaded a Windows executable SQLite command shell, and it worked fine with that same database. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Not reading data in the -wal file? (Not Reproducible)
On 6/6/2013 6:04 PM, Jerry Krinock wrote: • Launch Process 2. • Launch Process 1. • Add a record in Process 1. Possibly does not COMMIT. • Launch Process 3. • In Process 3, run the query, then terminate. New record is PRESENT. • In Process 2, open database, run the query, checkpoint, and close. New record is ABSENT. One possibility: Process 2 already has a connection, that has a pending read transaction (and so doesn't observe the concurrent write). Shared cache is enabled. The new connection then also effectively sees the same open read transaction. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Not reading data in the -wal file? (Not Reproducible)
On Thu, Jun 6, 2013 at 6:04 PM, Jerry Krinock wrote: > > On 2013 Jun 06, at 13:28, Richard Hipp wrote: > > > My guess is that App1 never actually committed the transaction. Are you > > sure that you ran COMMIT? And are you sure that the COMMIT was > successful? > > Thank you, Richard. I didn't have a scope on App 1, and it's much more > complicated. It's possible that it did not successfully COMMIT. > > Let's retell the story, calling them processes instead of apps. The > command-line tool is Process 3. The App2, which misses the new record at > first, is Process 2, then Process 4. > > • Launch Process 2. > • Launch Process 1. > • Add a record in Process 1. Possibly does not COMMIT. > • Launch Process 3. > • In Process 3, run the query, then terminate. New record is PRESENT. > • In Process 2, open database, run the query, checkpoint, and close. New > record is ABSENT. > • Terminate Process 2, relaunch as Process 4. > • Process 4 opens database, runs the query, checkpoints, and closes. New > record is PRESENT. > > Examining my code carefully, I'm sure that Process 2/4 does *not* keep the > database open. It opens the database when it needs to do a query, does the > query, checkpoints, closes it, and forgets the database connection handle. > > It don't see how Process 3 could get the new record, but Process 2 would > not get it until a relaunch. > It might be that Process2 started a read transaction before process3 even launched. This might happen accidentally because of a prepared statement that you neglected to sqlite3_reset() or sqlite3_finalize(), for example. Since process2 is holding a read transaction open, it is looking at a snapshot of an historical version of the database from before the changes of process3 and it continues to see that historical version even after process3 commits. > > Is the story as I told it possible, or must I have gotten something wrong? > > Jerry > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Not reading data in the -wal file? (Not Reproducible)
On 2013 Jun 06, at 13:28, Richard Hipp wrote: > My guess is that App1 never actually committed the transaction. Are you > sure that you ran COMMIT? And are you sure that the COMMIT was successful? Thank you, Richard. I didn't have a scope on App 1, and it's much more complicated. It's possible that it did not successfully COMMIT. Let's retell the story, calling them processes instead of apps. The command-line tool is Process 3. The App2, which misses the new record at first, is Process 2, then Process 4. • Launch Process 2. • Launch Process 1. • Add a record in Process 1. Possibly does not COMMIT. • Launch Process 3. • In Process 3, run the query, then terminate. New record is PRESENT. • In Process 2, open database, run the query, checkpoint, and close. New record is ABSENT. • Terminate Process 2, relaunch as Process 4. • Process 4 opens database, runs the query, checkpoints, and closes. New record is PRESENT. Examining my code carefully, I'm sure that Process 2/4 does *not* keep the database open. It opens the database when it needs to do a query, does the query, checkpoints, closes it, and forgets the database connection handle. It don't see how Process 3 could get the new record, but Process 2 would not get it until a relaunch. Is the story as I told it possible, or must I have gotten something wrong? Jerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Not reading data in the -wal file? (Not Reproducible)
On Thu, Jun 6, 2013 at 4:12 PM, Jerry Krinock wrote: > I just spent a couple hours on a really strange problem that went away. > > • Ann sqlite database had 13 rows in one of its tables. > • In App 1, which uses the "C" interface, add a new row. > • In App 2, which also uses the "C" interface, open that database with > sqlite3_open(), then run query "SELECT * from 'tableName'", using > sqlite3_prepare(), sqlite3_step() iteratively, and sqlite3_finalize(), then > sqlite3_wal_checkpoint_v2(passing SQLITE_CHECKPOINT_PASSIVE) and > sqlite3_close(). This is working code which I have not touched in five > years, except to update to the new checkpoint function. > > Expected Result: 14 rows > > Actual Result: 13 rows. The new row is absent. > My guess is that App1 never actually committed the transaction. Are you sure that you ran COMMIT? And are you sure that the COMMIT was successful? > > • Open the database using the sqlite3 command line tool built into Mac OS > X 10.8.4, and do the same query. Result: 14 rows. > • Repeat the open,query,checkpoint,close in App 2. Result: still 13 rows. > > • Peek inside the database main file, -shm file and -wal file with a text > editor. I see that the new row is not in the main file but is still in the > -wal file. I understand that -shm and -wal files are an implementation > detail of sqlite3, and that I should not worry about where the records are. > > • Quit and relaunch App 2. > > • Repeat the open,query,checkpoint,close in App 2. Result: now it gets > all 14 rows! > > I've since been retesting these apps for the last couple hours and all has > been fine since. > > App 1 is built with sqlite 3.7.15. > App 2 is built with sqlite 3.7.14. > The Mac's command line tool is sqlite 3.7.12. > > So, there's no explanation in the version numbers. > > This is very strange, but I did get down to the query level with the > debugger. > > Just prior to this sequence of events, I had deleted a half dozen records > in App 1, which kept showing up in queries in App 2. > > Any possible explanations would be appreciated. > > Jerry Krinock > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Not reading data in the -wal file? (Not Reproducible)
I just spent a couple hours on a really strange problem that went away. • Ann sqlite database had 13 rows in one of its tables. • In App 1, which uses the "C" interface, add a new row. • In App 2, which also uses the "C" interface, open that database with sqlite3_open(), then run query "SELECT * from 'tableName'", using sqlite3_prepare(), sqlite3_step() iteratively, and sqlite3_finalize(), then sqlite3_wal_checkpoint_v2(passing SQLITE_CHECKPOINT_PASSIVE) and sqlite3_close(). This is working code which I have not touched in five years, except to update to the new checkpoint function. Expected Result: 14 rows Actual Result: 13 rows. The new row is absent. • Open the database using the sqlite3 command line tool built into Mac OS X 10.8.4, and do the same query. Result: 14 rows. • Repeat the open,query,checkpoint,close in App 2. Result: still 13 rows. • Peek inside the database main file, -shm file and -wal file with a text editor. I see that the new row is not in the main file but is still in the -wal file. I understand that -shm and -wal files are an implementation detail of sqlite3, and that I should not worry about where the records are. • Quit and relaunch App 2. • Repeat the open,query,checkpoint,close in App 2. Result: now it gets all 14 rows! I've since been retesting these apps for the last couple hours and all has been fine since. App 1 is built with sqlite 3.7.15. App 2 is built with sqlite 3.7.14. The Mac's command line tool is sqlite 3.7.12. So, there's no explanation in the version numbers. This is very strange, but I did get down to the query level with the debugger. Just prior to this sequence of events, I had deleted a half dozen records in App 1, which kept showing up in queries in App 2. Any possible explanations would be appreciated. Jerry Krinock ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select count(*) does not use primary key??
On 6/6/2013 11:26 AM, Gabriel Corneanu wrote: Again sorry for count(a), I wrote too fast. I understand of course about null values. Otherwise by rowid I mean the autogenerated primary key. In my actual case, I have a field as alias. CREATE TABLE t(id integer primary key, a); explain query plan select count(*) from t -> scan table create index ia on t(id); explain query plan select count(*) from t -> SCAN TABLE t USING COVERING INDEX... 1. It means, the primary key is not as good as a cover index?? Again - there was *no* separate index until you explicitly created one. Another way to look at it is that the table as a whole *is* in fact a covering index for itself, ordered by rowid and covering all the fields in the table. In this view, "scan table" is just a shorthand for "scan table using covering index which is the table itself". If you need this count real fast for some reason, then create a separate table, with one column and one row, that would store the count. Then create INSERT and DELETE triggers that would maintain the count. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select count(*) does not use primary key??
On Thu, Jun 6, 2013 at 11:26 AM, Gabriel Corneanu wrote: > > 2. Is there NO WAY to quickly get the row count WITHOUT full scan if I > only have the auto primary key?? > The b-tree structures in the SQLite file format do not store the row count, as that slows down writes (since the row count would have to be updated with each insert or delete). If you want fast access to a row count, store it in a separate table and keep it up-to-date using triggers. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select count(*) does not use primary key??
Gabriel Corneanu wrote: > I was surprised to find that simple query "select count(*) from table" took > too much time, and found that it does NOT use the primary key index?? In SQLite, indexes are stored as B-trees, ordered by the indexed columns. Tables are _also_ stored as B-trees, ordered by the rowid. This means that if the primary key is the rowid, the table _is_ the index corresponding to the primary key. (There is no separate index structure in this case.) > If I use CREATE TABLE t(a unique), then it uses the auto-generated cover > index. Because any index has exactly the same number of entries as its table, but is likely to occupy fewer pages. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select count(*) does not use primary key??
Again sorry for count(a), I wrote too fast. I understand of course about null values. Otherwise by rowid I mean the autogenerated primary key. In my actual case, I have a field as alias. CREATE TABLE t(id integer primary key, a); explain query plan select count(*) from t -> scan table create index ia on t(id); explain query plan select count(*) from t -> SCAN TABLE t USING COVERING INDEX... 1. It means, the primary key is not as good as a cover index?? 2. Is there NO WAY to quickly get the row count WITHOUT full scan if I only have the auto primary key?? Thanks, Gabriel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select count(*) does not use primary key??
OK I understand, then it remains the question why it does not use the primary key?? Thanks, Gabriel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select count(*) does not use primary key??
On Thu, Jun 06, 2013 at 04:52:12PM +0200, Gabriel Corneanu scratched on the wall: > I was surprised to find that simple query "select count(*) from table" took > too much time, and found that it does NOT use the primary key index?? > e.g. > CREATE TABLE t(a); > explain query plan select count(*) from t > > I get : SCAN TABLE t (~100 rows) > > If I use CREATE TABLE t(a unique), then it uses the auto-generated cover > index. > Even if I write > select count(rowid) from t > it still uses scan table... > However I would expect that it should also use the primary key for > counting, or not?? What PK? Rowid is not a PK unless you define it as such. The table itself is stored in rowid order, so the "index" for rowid is the table itself-- there is no "other" index for rowid. > In my opinion, count(*) is the same as count(rowid) (I see that even > count() is accepted); I could say it's even the same as count(x) (any other > field). That is not true. The SQLite docs are quite clear: http://www.sqlite.org/lang_aggfunc.html#count count(X) count(*) The count(X) function returns a count of the number of times that X is not NULL in a group. The count(*) function (with no arguments) returns the total number of rows in the group. If you provide an actual column name, count() only counts non-NULL rows. The two versions of the function are equivalent if "X" prohibits NULL entries (such as the rowid column), but not in the general case. This is not SQLite specific... this is standard SQL. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Request to register Application-ID
On Thu, Jun 6, 2013 at 3:04 PM, Richard Hipp wrote: > I encourage you to "claim" and use that application ID. This kind of > thing is what the application-ID was created for. > > However, I think it will be best to delay adding this ID to the official > list until PayMaster V8 Export files are actually spotted in the wild. :-) > Side note: does it make sense only to register Unix-based apps this way, since Windows has no facility similar to "file"? -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
- Original Message - From: Donald Griggs To: phi...@blastbay.com ; General Discussion of SQLite Database Sent: Thursday, June 06, 2013 3:13 PM Subject: Re: [sqlite] Serialize an in-memory database Hi Philip, Maybe neither of these two thoughts are helpful, but fyi: 1. Licensing for existing memory vfs. Regarding this memory vfs implementation referenced earlier: http://article.gmane.org/gmane.comp.db.sqlite.general/46450 http://spserver.googlecode.com/files/spmemvfs-0.1.src.tar.gz Would it not be worth an email to the author ( gmail user stephen.nil ) to see if he might quickly release his code (already open source) to public domain or another acceptable license? 2. Performance of existing solutions. Regarding, "I would like to avoid saving the data out to a temporary disk file... That seems wasteful to me" I can certainly understand why you'd write that, but it's different from saying, "I've tested that solution (or put numerical bounds on its maximum likely performance) and found its performance to be unacceptable for my intended use." Of course, using sqlite at all for your purpose (mainly to avoid writing custom sorts, as I understand) is wasteful in some sense of the word -- but I suspect its nevertheless an entirely appropriate application. One of Donald Knuth's famous quotes was, "Premature optimization is the root of all evil (or at least most of it) in programming." If there's a chance that's applicable here (maybe its not), then since the code to backup to a temp file is already present, would it be worth a try? Respectfully, Donald G. (definitely NOT Donald K!) Hi Donald, You have several good points. Let's see if I can respond to them properly: 1. I can definitely drop a line to the author and ask about the licensing. But one appealing part of the vfs idea is actually sitting down and learning enough to implement it myself. If I write the code, I will also be able to debug it much more easily and will learn something new to boot. I have no urgent need for a solution to this problem, so while I certainly would use an existing memory vfs if it was available in SqLite itself I would also enjoy the challenge of writing my own. 2. Let me change the word wasteful to unnecessary. I certainly have no doubt that writing the temporary file and reading it back in would still be acceptable performance wise in my scenario, and I have no figures to prove otherwise. But it seems unnecessary to do so if an alternative method exists. Copying it from one memory location to another seems a lot more elegant if nothing else. Kind regards, Philip Bennefall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select count(*) does not use primary key??
On Thu, Jun 6, 2013 at 10:52 AM, Gabriel Corneanu wrote: > Strange is, count(*) uses the cover index for a but "select count(a)" does > NOT use the same cover index... > count(a) has to check for NULL values of a, which are not counted. count(*) does not. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] select count(*) does not use primary key??
I was surprised to find that simple query "select count(*) from table" took too much time, and found that it does NOT use the primary key index?? e.g. CREATE TABLE t(a); explain query plan select count(*) from t I get : SCAN TABLE t (~100 rows) If I use CREATE TABLE t(a unique), then it uses the auto-generated cover index. Even if I write select count(rowid) from t it still uses scan table... However I would expect that it should also use the primary key for counting, or not?? In my opinion, count(*) is the same as count(rowid) (I see that even count() is accepted); I could say it's even the same as count(x) (any other field). Strange is, count(*) uses the cover index for a but "select count(a)" does NOT use the same cover index... Am I making any mistake here?? Thanks, Gabriel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
Hi Philip, Maybe neither of these two thoughts are helpful, but fyi: 1.* Licensing for existing memory vfs.* Regarding this memory vfs implementation referenced earlier: http://article.gmane.org/gmane.comp.db.sqlite.general/46450 http://spserver.googlecode.com/files/spmemvfs-0.1.src.tar.gz Would it not be worth an email to the author ( gmail user stephen.nil ) to see if he might quickly release his code (already open source) to public domain or another acceptable license? 2. * Performance of existing solutions.* * * Regarding, "*I would like to avoid saving the data out to a temporary disk file... That seems wasteful** to me*" I can certainly understand why you'd write that, but it's different from saying, "I've tested that solution (or put numerical bounds on its maximum likely performance) and found its performance to be unacceptable for my intended use." Of course, using sqlite at all for your purpose (mainly to avoid writing custom sorts, as I understand) is wasteful in some sense of the word -- but I suspect its nevertheless an entirely appropriate application. One of Donald Knuth's famous quotes was, "Premature optimization is the root of all evil (or at least most of it) in programming." If there's a chance that's applicable here (maybe its not), then since the code to backup to a temp file is already present, would it be worth a try? Respectfully, Donald G. (definitely NOT Donald K!) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
If you accept the slowdown of .dump you can directly use the backup option... On Thu, Jun 6, 2013 at 2:10 PM, Philip Bennefall wrote: > > - Original Message - From: "Simon Slavin" > > To: ; "General Discussion of SQLite Database" > > Sent: Thursday, June 06, 2013 1:45 PM > > Subject: Re: [sqlite] Serialize an in-memory database > > > > On 6 Jun 2013, at 10:45am, Philip Bennefall wrote: > >> I have a bunch of data structures in memory that I am looking to replace >> with an SqLite database, primarily for the purpose of avoiding reinventing >> the wheel with various sorts etc. I would then like to serialize the data >> into a memory buffer and do additional processing before finally rendering >> it to disk. The additional processing might include compression, encryption, >> and a few other things specific to my application. > > > Two problems: > > Unlike the SQLite file format, the format SQLite uses when it keeps things > in memory is not published, and changes from version to version. Because > the writers of SQLite expect the in-memory format to be accessed only by > things built into the SQLite API, you have to read the source code to know > what's going on. So any routines you come up will have to just deal with > whatever they find rather than trying to understand its structure. Also > your data will be able to restored only back to versions of SQLite where the > internal data format hasn't changed. > > SQLite does not, by its nature, keep everything in one long block of memory. > It allocates and frees smaller blocks of memory as data is stored or > deleted, and also as it needs to create temporary structures such as indexes > needed to speed up a specific command. So turning a stored database into > one stream of octets takes more than just reading a section of memory. > > Rather than try to mess with the internals of SQLite I suspect you would be > better served by doing the following: > > 1) Using SQLite's existing in-memory databases to keep your data in memory > while your app executes. > > 2) Writing your own routine in your preferred programming language to dump > your data into text or octets in memory or disk in whatever format you want. > One standard way to do this is to generate the SQL commands needed to > reproduce your database. Since these are very repetitive standard ASCII > commands they compress down extremely well and you can do encryption at the > same time using any of a number of standard libraries. Data in this format > has the added advantages that it is human-readable (after decompression) and > can be passed straight to sqlite3_exec() to rebuild the database. However, > you might prefer to invent your own format, perhaps more like CSV, that > makes implicit use of your data structures. > > Simon.= > > Hi Simon, > > Oh I never intended to attempt to rip the data right out of an SqLite memory > database. I realize that it is not at all the same as the disk file that I > could create with, say, the backup API. I am considering two options: > > 1. Writing a memory vfs that I use when I want to save my data, backing up > the existing in-memory database to a new database that uses this memory vfs > and then taking the data from the resulting block where SqLite writes what > it thinks is the database file. > > 2. Doing something like .dump in the shell, but writing the output to memory > and then processing that. This seems to be the simplest approach, but would > waste a lot of space and import/export would be slower as far as I can > judge. This would primarily be the case if I export as SQL, as I would then > not be able to reuse prepared statements with parameters but would have to > use sqlite3_exec. > > The memory vfs seems like the most appealing choice in the longterm, but the > second approach is much more straightforward. > > Kind regards, > > Philip Bennefall > ___ > 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] Request to register Application-ID
On Thu, Jun 6, 2013 at 8:29 AM, Dennis Jenkins
wrote:
> I just read the Sqlite mail list exchange between Eduardo Morras and Dr.
> Hipp ("Header Application-ID list"). I was unaware that such a feature
> existed. Now that I know, I feel compelled to chase a shiny object :)
>
> I maintain a legacy proprietary payroll processing system (from the 1980s!
> Yeah). Anyway, a few years ago I integrated sqlite into it for doing data
> exports. If acceptable, I would like to register an application-id for the
> data export file. I request 0x504d3858 ("PM8X", "PayMaster v8 export").
>
I encourage you to "claim" and use that application ID. This kind of
thing is what the application-ID was created for.
However, I think it will be best to delay adding this ID to the official
list until PayMaster V8 Export files are actually spotted in the wild. :-)
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Header Application-ID list
> > On Thu, Jun 6, 2013 at 8:15 AM, Richard Hipp wrote: > > Ideally, this content would be picked up by unix "file" command and be > distributed to all unix systems. However, my repeated emails to the > maintainer Christos Zoulas about this have gone unanswered. > > Sounds like it may be time to try christos zoulas com instead of at astron [dot] com ,'o) -- -- -- --Ô¿Ô-- K e V i N ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Request to register Application-ID
I just read the Sqlite mail list exchange between Eduardo Morras and Dr.
Hipp ("Header Application-ID list"). I was unaware that such a feature
existed. Now that I know, I feel compelled to chase a shiny object :)
I maintain a legacy proprietary payroll processing system (from the 1980s!
Yeah). Anyway, a few years ago I integrated sqlite into it for doing data
exports. If acceptable, I would like to register an application-id for the
data export file. I request 0x504d3858 ("PM8X", "PayMaster v8 export").
Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Header Application-ID list
On Thu, Jun 6, 2013 at 8:05 AM, Eduardo Morras wrote: > > Hi, > > Is there an official list of assigned application id sqlite header? If > exist, How can I register my application-id? > The official list is here: www.sqlite.org/src/artifact/f2b23a6bde8f Send a request to this mailing list to add new items to the official list. Ideally, this content would be picked up by unix "file" command and be distributed to all unix systems. However, my repeated emails to the maintainer Christos Zoulas about this have gone unanswered. So for now the unix "file" command won't recognize the app-id unless you configure it yourself. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
- Original Message - From: "Simon Slavin" To: ; "General Discussion of SQLite Database" Sent: Thursday, June 06, 2013 1:45 PM Subject: Re: [sqlite] Serialize an in-memory database On 6 Jun 2013, at 10:45am, Philip Bennefall wrote: I have a bunch of data structures in memory that I am looking to replace with an SqLite database, primarily for the purpose of avoiding reinventing the wheel with various sorts etc. I would then like to serialize the data into a memory buffer and do additional processing before finally rendering it to disk. The additional processing might include compression, encryption, and a few other things specific to my application. Two problems: Unlike the SQLite file format, the format SQLite uses when it keeps things in memory is not published, and changes from version to version. Because the writers of SQLite expect the in-memory format to be accessed only by things built into the SQLite API, you have to read the source code to know what's going on. So any routines you come up will have to just deal with whatever they find rather than trying to understand its structure. Also your data will be able to restored only back to versions of SQLite where the internal data format hasn't changed. SQLite does not, by its nature, keep everything in one long block of memory. It allocates and frees smaller blocks of memory as data is stored or deleted, and also as it needs to create temporary structures such as indexes needed to speed up a specific command. So turning a stored database into one stream of octets takes more than just reading a section of memory. Rather than try to mess with the internals of SQLite I suspect you would be better served by doing the following: 1) Using SQLite's existing in-memory databases to keep your data in memory while your app executes. 2) Writing your own routine in your preferred programming language to dump your data into text or octets in memory or disk in whatever format you want. One standard way to do this is to generate the SQL commands needed to reproduce your database. Since these are very repetitive standard ASCII commands they compress down extremely well and you can do encryption at the same time using any of a number of standard libraries. Data in this format has the added advantages that it is human-readable (after decompression) and can be passed straight to sqlite3_exec() to rebuild the database. However, you might prefer to invent your own format, perhaps more like CSV, that makes implicit use of your data structures. Simon.= Hi Simon, Oh I never intended to attempt to rip the data right out of an SqLite memory database. I realize that it is not at all the same as the disk file that I could create with, say, the backup API. I am considering two options: 1. Writing a memory vfs that I use when I want to save my data, backing up the existing in-memory database to a new database that uses this memory vfs and then taking the data from the resulting block where SqLite writes what it thinks is the database file. 2. Doing something like .dump in the shell, but writing the output to memory and then processing that. This seems to be the simplest approach, but would waste a lot of space and import/export would be slower as far as I can judge. This would primarily be the case if I export as SQL, as I would then not be able to reuse prepared statements with parameters but would have to use sqlite3_exec. The memory vfs seems like the most appealing choice in the longterm, but the second approach is much more straightforward. Kind regards, Philip Bennefall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Header Application-ID list
Hi, Is there an official list of assigned application id sqlite header? If exist, How can I register my application-id? Thanks --- --- Eduardo Morras ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
On 6 Jun 2013, at 10:45am, Philip Bennefall wrote: > I have a bunch of data structures in memory that I am looking to replace with > an SqLite database, primarily for the purpose of avoiding reinventing the > wheel with various sorts etc. I would then like to serialize the data into a > memory buffer and do additional processing before finally rendering it to > disk. The additional processing might include compression, encryption, and a > few other things specific to my application. Two problems: Unlike the SQLite file format, the format SQLite uses when it keeps things in memory is not published, and changes from version to version. Because the writers of SQLite expect the in-memory format to be accessed only by things built into the SQLite API, you have to read the source code to know what's going on. So any routines you come up will have to just deal with whatever they find rather than trying to understand its structure. Also your data will be able to restored only back to versions of SQLite where the internal data format hasn't changed. SQLite does not, by its nature, keep everything in one long block of memory. It allocates and frees smaller blocks of memory as data is stored or deleted, and also as it needs to create temporary structures such as indexes needed to speed up a specific command. So turning a stored database into one stream of octets takes more than just reading a section of memory. Rather than try to mess with the internals of SQLite I suspect you would be better served by doing the following: 1) Using SQLite's existing in-memory databases to keep your data in memory while your app executes. 2) Writing your own routine in your preferred programming language to dump your data into text or octets in memory or disk in whatever format you want. One standard way to do this is to generate the SQL commands needed to reproduce your database. Since these are very repetitive standard ASCII commands they compress down extremely well and you can do encryption at the same time using any of a number of standard libraries. Data in this format has the added advantages that it is human-readable (after decompression) and can be passed straight to sqlite3_exec() to rebuild the database. However, you might prefer to invent your own format, perhaps more like CSV, that makes implicit use of your data structures. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
- Original Message - From: "Simon Slavin" To: ; "General Discussion of SQLite Database" Sent: Thursday, June 06, 2013 10:51 AM Subject: Re: [sqlite] Serialize an in-memory database On 6 Jun 2013, at 9:01am, Philip Bennefall wrote: Since I don't believe that Windows for example has tmpfs (seems to be a Unix thing), would the idea of constructing a vfs that just reads and writes a huge memory block be doable? Doable ? Yes. Use the code from one of the file-based VFSes and replace all the 'read/write to file offset N" with 'read/write to memory offset N', then sort out locking and a few other problems. Tedious and annoying to do but doable if you write C. Of advantage to many users ? I don't know. I don't see what the advantage of doing this is over SQLite's standard ways of storing data in memory or in a file. Who would use this ? Also, I have a question. How big do you expect to make that block of memory you grab when someone creates a new database ? One of the advantages of SQL databases is that they grow as you get more data. You can't do this if you're going to pre-grab a continuous block of memory. Do you expect to use the C function realloc() a lot ? Simon. Hi Simon, For my own part, I would usually have a database that is no more than a few megabyte in size. A generic solution would be a lot harder than the one I am considering for my own project, where I can cut corners due to the fact that I know the size of my data at least roughly. What I want to achieve is to serialize the data in such a way so that I can do other processing on it before I render it to disk, such as custom compression and/or other things. I am aware that there is an SqLite add-on to do this, but aside from the fact that I cannot afford it I don't need to do this processing on the fly either. I just want to take an in-memory database and put it in a compressed and possibly encrypted file on disk in the end, without having to use a temporary file as an intermediary. I write C, so would have no trouble modifying one of the existing vfs example implementations. Correct me if I am wrong, but do I really need to do any kind of locking if I am not working with disk files? I am not working with shared cache, either. I would have one database connection that would only be accessed from one thread. Kind regards, Philip Bennefall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
On 6 Jun 2013, at 9:01am, Philip Bennefall wrote: > Since I don't believe that Windows for example has tmpfs (seems to be a Unix > thing), would the idea of constructing a vfs that just reads and writes a > huge memory block be doable? Doable ? Yes. Use the code from one of the file-based VFSes and replace all the 'read/write to file offset N" with 'read/write to memory offset N', then sort out locking and a few other problems. Tedious and annoying to do but doable if you write C. Of advantage to many users ? I don't know. I don't see what the advantage of doing this is over SQLite's standard ways of storing data in memory or in a file. Who would use this ? Also, I have a question. How big do you expect to make that block of memory you grab when someone creates a new database ? One of the advantages of SQL databases is that they grow as you get more data. You can't do this if you're going to pre-grab a continuous block of memory. Do you expect to use the C function realloc() a lot ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
- Original Message - From: "Paolo Bolzoni" To: ; "General Discussion of SQLite Database" Sent: Thursday, June 06, 2013 10:33 AM Subject: Re: [sqlite] Serialize an in-memory database What is you use case? Why do you need this? I am asking because maybe it helps thinking alternate solutions... Hi Paolo, I have a bunch of data structures in memory that I am looking to replace with an SqLite database, primarily for the purpose of avoiding reinventing the wheel with various sorts etc. I would then like to serialize the data into a memory buffer and do additional processing before finally rendering it to disk. The additional processing might include compression, encryption, and a few other things specific to my application. I would like to avoid saving the data out to a temporary disk file, reading it back in, doing my processing, writing it out into a new file and then finally deleting the temporary file. That seems wasteful to me, and so that's why I am looking into solutions that avoid the temporary files. So far, the memory vfs seems like the best approach. Kind regards, Philip Bennefall P.S. I have looked at the encryption and compression add-ons for SqLite, but I don't need encryption/compression on the fly (just on the entire database in one go), and I don't have the money to purchase the code in the first place. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
What is you use case? Why do you need this? I am asking because maybe it helps thinking alternate solutions... On Thu, Jun 6, 2013 at 10:05 AM, Philip Bennefall wrote: > > - Original Message - From: "Paolo Bolzoni" > > > To: ; "General Discussion of SQLite Database" > > Sent: Thursday, June 06, 2013 10:02 AM > > Subject: Re: [sqlite] Serialize an in-memory database > > >> Sorry I am missing a bit, >> What is the problem of using sqlite3_backup again? >> > > Hi Paolo, > > I would like to avoid using a temporary file, but rather just save and load > the database as a memory block. Serialize to and from memory, in other > words. > > > Kind regards, > > Philip Bennefall > ___ > 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] Serialize an in-memory database
- Original Message - From: "Paolo Bolzoni" To: ; "General Discussion of SQLite Database" Sent: Thursday, June 06, 2013 10:02 AM Subject: Re: [sqlite] Serialize an in-memory database Sorry I am missing a bit, What is the problem of using sqlite3_backup again? Hi Paolo, I would like to avoid using a temporary file, but rather just save and load the database as a memory block. Serialize to and from memory, in other words. Kind regards, Philip Bennefall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialize an in-memory database
Sorry I am missing a bit, What is the problem of using sqlite3_backup again? On Thu, Jun 6, 2013 at 10:01 AM, Philip Bennefall wrote: > > - Original Message - From: "Simon Slavin" > To: ; "General Discussion of SQLite Database" > > Sent: Thursday, June 06, 2013 12:15 AM > > Subject: Re: [sqlite] Serialize an in-memory database > > > > On 5 Jun 2013, at 8:38pm, Philip Bennefall wrote: > >> On 5 Jun 2013, at 8:32pm, Petite Abeille wrote: >> >>> write to tmpfs… read the file into byte[]… do what you meant to do… to >>> reload… write byte[] do tmpfs… open db… and be merry… or something along >>> these lines... >> >> >> I don't want it in a file, however. I want it in a memory block. > > > That's why you read from tmpfs (or any other file stored in any other file > system) into byte[]. Once your data is in byte[] you will have entire > SQLite database in one run of memory. > > You can't usefully store a memory database of SQLite because SQLite's data > in memory isn't all in one big run of memory. The data is stored in various > little chunks, some here, some there. If you tried to read the data directly > out of those chunks you would have to read lots of little chunks, not one > big run of continuous memory. And you'd be storing lots of pointers to > various locations in memory. When you 'restore' the data back into memory > you're not going to be allocated the same locations in memory so those > pointers won't mean anything any more. > > A database stored in a file, however, has pointers to locations in that file > instead of pointers to locations in memory. If you save and restore the > whole file in one big run, those pointers will become valid again: the same > bits of data will be at the same offsets of the file. > > Doesn't have to be tmpfs. You can use any file system that SQLite thinks is > file storage rather than memory storage. > > Simon. > > Hi Simon, > > Since I don't believe that Windows for example has tmpfs (seems to be a Unix > thing), would the idea of constructing a vfs that just reads and writes a > huge memory block be doable? If so, how difficult of a task do you estimate > that this might be? I want to reuse as much of the existing vfs code as > possible (e.g. I don't want to reimplement randomness, date etc). Could you > possibly give me some pointers? I read the chapter about the virtual file > systems, but it seems incomplete. > > Kind regards, > > Philip Bennefall > ___ > 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] Serialize an in-memory database
- Original Message - From: "Simon Slavin" To: ; "General Discussion of SQLite Database" Sent: Thursday, June 06, 2013 12:15 AM Subject: Re: [sqlite] Serialize an in-memory database On 5 Jun 2013, at 8:38pm, Philip Bennefall wrote: On 5 Jun 2013, at 8:32pm, Petite Abeille wrote: write to tmpfs… read the file into byte[]… do what you meant to do… to reload… write byte[] do tmpfs… open db… and be merry… or something along these lines... I don't want it in a file, however. I want it in a memory block. That's why you read from tmpfs (or any other file stored in any other file system) into byte[]. Once your data is in byte[] you will have entire SQLite database in one run of memory. You can't usefully store a memory database of SQLite because SQLite's data in memory isn't all in one big run of memory. The data is stored in various little chunks, some here, some there. If you tried to read the data directly out of those chunks you would have to read lots of little chunks, not one big run of continuous memory. And you'd be storing lots of pointers to various locations in memory. When you 'restore' the data back into memory you're not going to be allocated the same locations in memory so those pointers won't mean anything any more. A database stored in a file, however, has pointers to locations in that file instead of pointers to locations in memory. If you save and restore the whole file in one big run, those pointers will become valid again: the same bits of data will be at the same offsets of the file. Doesn't have to be tmpfs. You can use any file system that SQLite thinks is file storage rather than memory storage. Simon. Hi Simon, Since I don't believe that Windows for example has tmpfs (seems to be a Unix thing), would the idea of constructing a vfs that just reads and writes a huge memory block be doable? If so, how difficult of a task do you estimate that this might be? I want to reuse as much of the existing vfs code as possible (e.g. I don't want to reimplement randomness, date etc). Could you possibly give me some pointers? I read the chapter about the virtual file systems, but it seems incomplete. Kind regards, Philip Bennefall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Change of behavior dealing with invalid views
On Wed, Jun 5, 2013 at 12:02 PM, Dominique Devienne wrote: > But that brings up the question about why the create view itself does not > fail? > > C:\Users\DDevienne>sqlite3 > SQLite version 3.7.15.2 2013-01-09 11:53:05 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> create table a (b int primary key, c); > sqlite> create table d (e int primary key, f, g); > sqlite> create view v as select * from a union all select * from d; > FWIW, the statements above fails on create view in Oracle (see below). Could SQLite3 similarly fail "early" on the view creation? If not, what's the rationale for not doing so? Thanks, --DD Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production SQL> create table a (b integer primary key, c char); Table created. SQL> create table d (e integer primary key, f char, g char); Table created. SQL> create view v as select * from a union all select * from d; create view v as select * from a union all select * from d * ERROR at line 1: ORA-01789: query block has incorrect number of result columns ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
