Re: [sqlite] Berkeley DB adds SQL using SQLite API !!
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 P Kishor wrote: > thanks for the clarification, but how does the above statement > reconcile with "the btree/paging layer is replaced with BDB"? Does > that refer to a different version of SQLite being offered by Oracle > that includes BDB for storage? I hope that will be called something > else... mebbe sqlbdb? You are thinking about this too hard. Due to SQLite's license it can be combined in any way with proprietary or open source code. (Technically public domain is the absence of a license but you get the point.) Separately SQLite is trademarked and that trademark is owned by HWACI (DRH's company). I can't find an explicit policy anywhere, but in general you can only call the software/download from SQLite.org by the name "SQLite". Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuwY5YACgkQmOOfHg372QTE9wCfSryo2T34R5wv6nL2nIywOxEW Jh0AoMUgbl+wRcqS5r8bSOrKffPkCXUf =xY4d -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite bug with collating sequences?
I've managed to break a database, and I think it's related to defining new text collating sequences in Tcl. If I run "VACUUM" on it, it works fine again. I was told that this would likely be a bug in SQLite. I've created a tarball with my broken database, and a small Tcl script that showcases the problem. The script gets the all the elements in a column, and proceed to "SELECT foo FROM table WHERE foo=foo" for all of the elements. This fails for some of them on the broken database, but succeeds after I VACUUM it. Here's the tarball with the database and script: http://www.pix.pp.se/sqlite-bork.tar.gz -- Rickard Utgren ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bug with collating sequences?
On Mar 29, 2010, at 4:55 PM, Rickard Utgren wrote: > I've managed to break a database, and I think it's related to defining > new text collating sequences in Tcl. If I run "VACUUM" on it, it works > fine again. I was told that this would likely be a bug in SQLite. This database was created with a different version of the collation sequence than found in the script. I think the -nocase switch was probably passed to [string compare]. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bug with collating sequences?
On Mon, Mar 29, 2010 at 12:07, Dan Kennedy wrote:
> This database was created with a different version of the collation
> sequence than found in the script. I think the -nocase switch was
> probably passed to [string compare].
You are correct, it was creates with this sequence:
proc rfccomp {a b} {
string compare [string map [list "\[" "{" "\]" "}" "\\" "|" "~" "^"]
[string tolower $a]] [string map [list "\[" "{" "\]" "}" "\\" "|" "~"
"^"] [string tolower $b]]
}
Which I later changed to this:
proc rfccomp {a b} {
string compare [string map [list \{ \[ \} \] ~ ^ | \\] [string
toupper $a]] [string map [list \{ \[ \} \] ~ ^ | \\] [string toupper
$b]]
}
I didn't realize this would matter, but at least now I don't have to
worry about my code breaking it again, thanks.
--
Rickard Utgren
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite memory consume..
Hi, why this code consume free memory ?
the same code but 'UPDATE' works fine
any help?
#include
#include
#include "sqlite3.h"
int main(int argc, char **argv)
{
sqlite3 *db;
char *zErrMsg = 0;
int rc;
char st1[200];
sqlite3_initialize();
// sprintf (st1,"UPDATE Table1 SET Temp = %d WHERE Id = %d",1,1);
sprintf (st1,"INSERT INTO Table1 (Id, Temp,Data) VALUES
(%d,1,'1234')",1);
sqlite3_soft_heap_limit(10);
rc = sqlite3_open("pippo.sqlite", &db);
if( rc )
{
fprintf(stderr, "Can't open database: %s\n",
sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
rc = sqlite3_exec(db,"PRAGMA cache_size=0;", 0, 0, &zErrMsg);
if( rc!=SQLITE_OK )
{
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
rc = sqlite3_exec(db,"CREATE TABLE Table1(Id INTEGER,Temp INTEGER,Data
TEXT);", 0, 0, &zErrMsg);
if( rc!=SQLITE_OK )
{
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
do
{
rc = sqlite3_exec(db,st1, 0, 0, &zErrMsg);
if( rc!=SQLITE_OK )
{
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
} while (1);
sqlite3_close(db);
return 0;
}
--
View this message in context:
http://old.nabble.com/sqlite-memory-consume..-tp28068504p28068504.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Will CEROD work with the new BerkleyDB implementation?
Will CEROD work with the new BerkleyDB implementation? -- Garry Watkins Managing Director DynaFOCUS, LLC ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Will CEROD work with the new BerkleyDB implementation?
On Mar 29, 2010, at 9:08 AM, Garry Watkins wrote: > Will CEROD work with the new BerkleyDB implementation? Please understand that the new SQL parser on BDB is entirely the work of Oracle and is independent of SQLite. We here at SQLite.org have not participated in that effort. We don't have any control over it, no rights to it, and no special knowledge of what Oracle has done, other than what we can see for ourselves by downloading and looking at the Oracle code. We didn't even know it was taking place until it was largely complete. So questions about what works or doesn't work with BDB should probably be directed to Oracle, not here. That said, it seems to me that the Oracle effort has replaced the Btree and Pager layers of SQLite with their own BDB implementation. But CEROD works on the pager layer. So CEROD is not likely to be compatible with BDB, even if you are using the SQLite front end on BDB. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Northwind example database
On 27 Mar 2010, at 10:46am, GeoffW wrote: > Just for educational purposes I have been experimenting a little with > the Northwind Sqlite database contained on the sqlite official site. > Download link: http://download.vive.net/Northwind.zip. > > Am I misunderstanding here or are the dates in the wrong format for > sqlite within this converted database ? > Assuming it is wrong and not my understsanding, are there any easy > ways to get the dates reversed and corrected to sqlite order and > written back out to the database ? Perhaps this is better performed in the calling language, but the following sql should reformat these dates. Of course, you'd need to substitute and repeat for the other fields. -- Reformat date from, e.g., '1/5/2010 12:00:00 AM' to '2010-01-05' update employees set birthdate = replace (birthdate, ' 12:00:00 AM', ''); update employees set birthdate = '0' || birthdate where substr(birthdate, 2,1) == '/'; update employees set birthdate = substr(birthdate, 1, 3) || '0' || substr(birthdate, 4,99) where substr(birthdate, 5,1) == '/'; -- Date should now be formatted as dd/mm/ -- Now change to -mm-dd update employees set birthdate = substr(birthdate, 7,4) || '-' || substr(birthdate, 1,2) || '-' || substr(birthdate, 4,2); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Tera-byte Scalability
Hello all, I would like to get a clarification on one point: "SQLite has had to sacrifice other characteristics that some people find useful, such as high concurrency, fine-grained access control, a rich set of built-in functions, stored procedures, esoteric SQL language features, XML and/or Java extensions, *tera- or peta-byte scalability*, and so forth" Source : http://www.sqlite.org/whentouse.html Does that mean it cannot support data sizes greater than 1TB. Can somebody explain me the meaning of terabyte scalability with respect to DB's. I would also like to know whether SQLite can handle *.mts* files(AVCHD). -- * NAVANEETH SEN B.** * ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bug with collating sequences?
On Mon, Mar 29, 2010 at 12:16:57PM +0200, Rickard Utgren scratched on the wall: > On Mon, Mar 29, 2010 at 12:07, Dan Kennedy wrote: > > > This database was created with a different version of the collation > > sequence than found in the script. I think the -nocase switch was > > probably passed to [string compare]. > I didn't realize this would matter, but at least now I don't have to > worry about my code breaking it again, thanks. If you change a collation that is used in an index, you must rebuild the index. That's what the VACUUM command is doing, and why it is "fixing" the problem. You can do this more quickly with the REINDEX command. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Poor query planning with GROUP BY on virtual tables in release 3.6.23.1
Hello! create view view_role as select role.id as rowid, role.uid, role.timestamp, role.user_id, role_fts.* from role, role_fts where role_fts.rowid=role.id group by uid having timestamp=max(timestamp); sqlite> explain query plan select * from view_role where rowid=1000; 0|1|TABLE role_fts VIRTUAL TABLE INDEX 0: 1|0|TABLE role USING PRIMARY KEY sqlite> select * from view_role where rowid=1000; 1000|�ƈ]�4b�cg�|2455285.05859278|veter|Role 1000|role 1000 CPU Time: user 17.385087 sys 0.556035 sqlite> select count(*) from view_role; 100 CPU Time: user 18.933183 sys 0.744047 sqlite> explain select * from view_role where rowid=1000; 0|Trace|0|0|0||00| 1|OpenEphemeral|3|7|0|keyinfo(1,BINARY)|00| 2|Integer|0|9|0||00| 3|Integer|0|8|0||00| 4|Gosub|11|64|0||00| 5|Goto|0|73|0||00| 6|VOpen|2|0|0|vtab:90C3C30:B6B08200|00| 7|OpenRead|1|2|0|4|00| 8|Integer|0|14|0||00| 9|Integer|0|15|0||00| 10|VFilter|2|25|14||00| 11|Rowid|2|17|0||00| 12|MustBeInt|17|24|0||00| 13|NotExists|1|24|17||00| 14|Column|1|1|18||00| 15|Sequence|3|19|0||00| 16|Rowid|1|20|0||00| 17|Column|1|2|21||00| 18|RealAffinity|21|0|0||00| 19|Column|1|3|22||00| 20|VColumn|2|0|23||00| 21|VColumn|2|1|24||00| 22|MakeRecord|18|7|17||00| 23|IdxInsert|3|17|0||00| 24|VNext|2|11|0||00| 25|Close|2|0|0||00| 26|Close|1|0|0||00| 27|Sort|3|72|0||00| 28|Column|3|0|13||00| 29|Compare|12|13|1|keyinfo(1,BINARY)|00| 30|Jump|31|35|31||00| 31|Move|13|12|1||00| 32|Gosub|10|50|0||00| 33|IfPos|9|72|0||00| 34|Gosub|11|64|0||00| 35|Column|3|3|18||00| 36|CollSeq|0|0|0|collseq(BINARY)|00| 37|AggStep|0|18|7|max(1)|01| 38|Column|3|2|1||00| 39|Column|3|0|2||00| 40|Column|3|3|3||00| 41|Column|3|4|4||00| 42|Column|3|5|5||00| 43|Column|3|6|6||00| 44|Integer|1|8|0||00| 45|Next|3|28|0||00| 46|Gosub|10|50|0||00| 47|Goto|0|72|0||00| 48|Integer|1|9|0||00| 49|Return|10|0|0||00| 50|IfPos|8|52|0||00| 51|Return|10|0|0||00| 52|AggFinal|7|1|0|max(1)|00| 53|Integer|1000|17|0||00| 54|Ne|17|51|1||6c| 55|Ne|7|51|3|collseq(BINARY)|6d| 56|SCopy|1|25|0||00| 57|SCopy|2|26|0||00| 58|SCopy|3|27|0||00| 59|SCopy|4|28|0||00| 60|SCopy|5|29|0||00| 61|SCopy|6|30|0||00| 62|ResultRow|25|6|0||00| 63|Return|10|0|0||00| 64|Null|0|1|0||00| 65|Null|0|2|0||00| 66|Null|0|3|0||00| 67|Null|0|4|0||00| 68|Null|0|5|0||00| 69|Null|0|6|0||00| 70|Null|0|7|0||00| 71|Return|11|0|0||00| 72|Halt|0|0|0||00| 73|Transaction|0|0|0||00| 74|VerifyCookie|0|14|0||00| 75|TableLock|0|2|0|role|00| 76|Goto|0|6|0||00| Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite over NFS
Hi, I am using SQLite version 3.5.9. My db sizes are 50-100 Gb. My DB is a typical star schema. I am seeing an order of magnitude more time to do a simple select query when doing over NFS (30-60 seconds) as compared to local disk (2 seconds). Is there any way to optimize this ? Thanks, Abhinav ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bug with collating sequences?
On Mar 29, 2010, at 10:40 AM, Jay A. Kreibich wrote: > On Mon, Mar 29, 2010 at 12:16:57PM +0200, Rickard Utgren scratched > on the wall: >> On Mon, Mar 29, 2010 at 12:07, Dan Kennedy >> wrote: >> >>> This database was created with a different version of the collation >>> sequence than found in the script. I think the -nocase switch was >>> probably passed to [string compare]. > >> I didn't realize this would matter, but at least now I don't have to >> worry about my code breaking it again, thanks. > > If you change a collation that is used in an index, you must rebuild > the index. That's what the VACUUM command is doing, and why it is > "fixing" the problem. You can do this more quickly with the REINDEX > command. Yes. In fact, the REINDEX command was originally created for the specific purpose of rebuilding indices when the definition of a collating function changed. That's why REINDEX will accept the name of a collating function as its argument and only rebuild those indices that use the named collating function. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite over NFS
On Mar 29, 2010, at 10:50 AM, Kumar, Abhinav wrote: > Hi, > > I am using SQLite version 3.5.9. My db sizes are 50-100 Gb. My DB is > a typical star schema. I am seeing an order of magnitude more time > to do a simple select query when doing over NFS (30-60 seconds) as > compared to local disk (2 seconds). Is there any way to optimize > this ? No. In any SQL database implementation (any non-SQL database too) the database engine itself normally does a lot of filtering of content before handing the results over to the application. So if you will imagine three boxes -- the disk drive, the database engine, and the application -- the traffic between the database engine and the disk drive is normally much larger than the traffic between the database engine and the application. Now, if the disk drive is separated from the application by a network, at some point you are going to need to move content across the network. Because the network is normally the slowest piece of hardware, you normally want to minimize the amount of traffic you move over the network. For that reason, you want the database engine and the disk drive to be on the same side of the network, not on opposite sides. The link between the database engine and the application should be the one that spans the network, since that is your lowest bandwidth link. Hence, if you have an NFS disk, you really ought to be using a client/ server database engine which will allow you to position the database engine on the server so that it can talk to the disk directly and without a network hop. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tera-byte Scalability
On 29 Mar 2010, at 3:36pm, Navaneeth Sen B wrote: > Does that mean it cannot support data sizes greater than 1TB. SQLite can handle whatever your file system can handle. Probably 4TB, if your OS will handle that much. However there are a number of limits involved in what it does. You're probably interested in sections 12 and 13 of http://www.sqlite.org/limits.html > Can somebody explain me the meaning of terabyte scalability with respect > to DB's. The question is too vague for me. You're going to have to explain what you want to do. > I would also like to know whether SQLite can handle *.mts* files(AVCHD). SQLite is a database system which reads and writes its own database files, which contain data of various types. AVCHD files are video files which contain video information. Due to restrictions on the licensing associated with AVCHD you may not extract AVCHD information and use it as data in other files, licensed tools will handle it only if it's in a file of its own. So even though SQLite (and many other DBMSs) could conceivably handle AVCHD data as BLOBs, the tools should refused to handle raw data. Again, what is it you actually want to do ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Berkeley DB adds SQL using SQLite API !!
On Mon, Mar 29, 2010 at 3:23 AM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > P Kishor wrote: >> thanks for the clarification, but how does the above statement >> reconcile with "the btree/paging layer is replaced with BDB"? Does >> that refer to a different version of SQLite being offered by Oracle >> that includes BDB for storage? I hope that will be called something >> else... mebbe sqlbdb? > > You are thinking about this too hard. Due to SQLite's license it can be > combined in any way with proprietary or open source code. (Technically > public domain is the absence of a license but you get the point.) > You misunderstood my question (or, perhaps my question wasn't clear), but it is answered now. 1. Essentially, stuff downloaded from sqlite.org is still pure sqlite, purely and completely in public domain in the US, with nothing from Oracle within it. 2. Stuff downloaded from elsewhere can of course use sqlite, and may be subject to its own terms. What matters to me is #1 above. > Separately SQLite is trademarked and that trademark is owned by HWACI > (DRH's company). I can't find an explicit policy anywhere, but in > general you can only call the software/download from SQLite.org by the > name "SQLite". > > Roger -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tera-byte Scalability
On Mon, Mar 29, 2010 at 10:26 AM, Simon Slavin wrote: > > On 29 Mar 2010, at 3:36pm, Navaneeth Sen B wrote: > >> Does that mean it cannot support data sizes greater than 1TB. > > SQLite can handle whatever your file system can handle. Probably 4TB, if > your OS will handle that much. However there are a number of limits involved > in what it does. You're probably interested in sections 12 and 13 of > > http://www.sqlite.org/limits.html > >> Can somebody explain me the meaning of terabyte scalability with respect >> to DB's. > > The question is too vague for me. You're going to have to explain what you > want to do. > >> I would also like to know whether SQLite can handle *.mts* files(AVCHD). > Well, if by "SQLite can handle" the OP wants to know whether or not SQLite "can store" *.mts* files, then, yes... SQLite is capable of storing any binary blobs. It doesn't care whether they are video or music or any other binary format, no matter how vague or obscure or digitally restricted it might be. > SQLite is a database system which reads and writes its own database files, > which contain data of various types. AVCHD files are video files which > contain video information. Due to restrictions on the licensing associated > with AVCHD you may not extract AVCHD information and use it as data in other > files, licensed tools will handle it only if it's in a file of its own. So > even though SQLite (and many other DBMSs) could conceivably handle AVCHD data > as BLOBs, the tools should refused to handle raw data. > > Again, what is it you actually want to do ? > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tera-byte Scalability
On Mon, Mar 29, 2010 at 10:33:53AM -0500, P Kishor scratched on the wall: > On Mon, Mar 29, 2010 at 10:26 AM, Simon Slavin wrote: > > > > On 29 Mar 2010, at 3:36pm, Navaneeth Sen B wrote: > > > >> Does that mean it cannot support data sizes greater than 1TB. > >> I would also like to know whether SQLite can handle *.mts* files(AVCHD). > > Well, if by "SQLite can handle" the OP wants to know whether or not > SQLite "can store" *.mts* files, then, yes... SQLite is capable of > storing any binary blobs. Rows and BLOBs are limited to 2GB; less by default. It isn't unusual for an MTS file to be larger than this. So while there isn't a format issue, there is an environment issue. Personally, the idea of putting large MTS files into an SQLite database as BLOBs seems like a very Bad Idea. If you just want single-file bundles, something like TAR or ZIP or some other generic "container" seems much more likely to work, but I still don't really see the point. If you want a meta-filesystem (filesystem-in-a-file), find a product that does that. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tera-byte Scalability
On 29 Mar 2010, at 4:33pm, P Kishor wrote: > On Mon, Mar 29, 2010 at 10:26 AM, Simon Slavin wrote: >> > Well, if by "SQLite can handle" the OP wants to know whether or not > SQLite "can store" *.mts* files, then, yes... SQLite is capable of > storing any binary blobs. It doesn't care whether they are video or > music or any other binary format, no matter how vague or obscure or > digitally restricted it might be. Which is, in fact, what I wrote in the message you were following up to: >> [snip] So even though SQLite (and many other DBMSs) could conceivably handle >> AVCHD data as BLOBs, Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Berkeley DB adds SQL using SQLite API !!
On Mon, Mar 29, 2010 at 09:15:45AM +0530, Roger Binns scratched on the wall: > I believe the btree/paging layer is replaced with BDB. Didn't SQLite "1" use a dbm library for the storage layer? The more things change -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Berkeley DB adds SQL using SQLite API !!
On Mar 29, 2010, at 12:03 PM, Jay A. Kreibich wrote: > On Mon, Mar 29, 2010 at 09:15:45AM +0530, Roger Binns scratched on > the wall: > >> I believe the btree/paging layer is replaced with BDB. > > Didn't SQLite "1" use a dbm library for the storage layer? > > The more things change > SQLite version 1 used gdbm for storage. There were many problems with that approach: (1) gdbm is a hash-based system so it could not do range queries (2) gdbm is GPL, (3) Each table and index is in a separate file so your "database" was a directory full of files instead of a single file, (4) there is no support for transactions, (5) gdbm is highly vulnerable to corruption if a power loss occurs while it is being updated. So after less than a year of SQLite 1, I wrote my own b-tree library and hooked it up to SQLite, changed the license from GPL to Public Domain, and called the result "SQLite version 2". That was in 2001, starting on http://www.sqlite.org/src/timeline? c=2001-04-17+20%3a09%3a11 and continuing through http://www.sqlite.org/src/timeline?c=2001-09-13+13%3A46%3A56 and beyond. >-j > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Our opponent is an alien starship packed with atomic bombs. We have > a protractor." "I'll go home and see if I can scrounge up a ruler > and a piece of string." --from Anathem by Neal Stephenson > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Northwind example database
It would be nice if SQLite had a strptime-like function for things like this and not just strftime. On Mon, Mar 29, 2010 at 10:13 AM, Griggs, Donald wrote: > > > On 27 Mar 2010, at 10:46am, GeoffW wrote: > >> Just for educational purposes I have been experimenting a little with >> the Northwind Sqlite database contained on the sqlite official site. >> Download link: http://download.vive.net/Northwind.zip. >> >> Am I misunderstanding here or are the dates in the wrong format for >> sqlite within this converted database ? > >> Assuming it is wrong and not my understsanding, are there any easy >> ways to get the dates reversed and corrected to sqlite order and >> written back out to the database ? > > > Perhaps this is better performed in the calling language, but the following > sql should reformat these dates. Of course, you'd need to substitute and > repeat for the other fields. > > > -- Reformat date from, e.g., '1/5/2010 12:00:00 AM' to '2010-01-05' > > update employees > set birthdate = replace (birthdate, ' 12:00:00 AM', ''); > > update employees > set birthdate = '0' || birthdate > where substr(birthdate, 2,1) == '/'; > > update employees > set birthdate = > substr(birthdate, 1, 3) > || '0' > || substr(birthdate, 4,99) > where substr(birthdate, 5,1) == '/'; > > -- Date should now be formatted as dd/mm/ > -- Now change to -mm-dd > > update employees > set birthdate = > substr(birthdate, 7,4) > || '-' > || substr(birthdate, 1,2) > || '-' > || substr(birthdate, 4,2); > > ___ > 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] Unwanted CLI CR-LF to LF conversion
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tomáš Szépe wrote: > Happens even with CLI ".read" command. > I believe this to be a bug. You are supplying text to a text based program and finding that it treats text as text :-) Note that on Unix end of line is LF and that is exactly what is happening. Your work around is to supply blobs where you can provide the exact UTF-8 byte sequence you want and then use CAST to convert to or from a string. Alternatively you can write your own shell to handle things exactly the way you want. At the heart of the shell is a loop that processes internal commands (.dump, .read etc) if the line starts with a dot, else sqlite3_complete() is called until it returns true and then the SQL is executed. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuw4x4ACgkQmOOfHg372QT6HACfR5LnemoEQvmWV3lYu+K//pmh wB8An0oB8d6LPP90TLZkrhlwPVCaEJma =oToI -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite over NFS
On Mon, Mar 29, 2010 at 07:50:38AM -0700, Kumar, Abhinav wrote: > I am using SQLite version 3.5.9. My db sizes are 50-100 Gb. My DB is a > typical star schema. I am seeing an order of magnitude more time to do > a simple select query when doing over NFS (30-60 seconds) as compared > to local disk (2 seconds). Is there any way to optimize this ? Does setting the DB page size to 4KB help? Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Berkeley DB adds SQL using SQLite API !!
Hello! On Monday 29 March 2010 20:22:36 D. Richard Hipp wrote: > SQLite version 1 used gdbm for storage. There were many problems with > that approach: (1) gdbm is a hash-based system so it could not do > range queries (2) gdbm is GPL, (3) Each table and index is in a > separate file so your "database" was a directory full of files instead > of a single file, (4) there is no support for transactions, (5) gdbm > is highly vulnerable to corruption if a power loss occurs while it is > being updated. May be the Tokyo Cabinet DBMS is more better? BerkeleyDB is required administration and it's strange solution in many embedded devices and for some desktops and servers too. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Berkeley DB adds SQL using SQLite API !!
On Mar 29, 2010, at 1:56 PM, Alexey Pechnikov wrote: > Hello! > > On Monday 29 March 2010 20:22:36 D. Richard Hipp wrote: >> SQLite version 1 used gdbm for storage. There were many problems >> with >> that approach: (1) gdbm is a hash-based system so it could not do >> range queries (2) gdbm is GPL, (3) Each table and index is in a >> separate file so your "database" was a directory full of files >> instead >> of a single file, (4) there is no support for transactions, (5) gdbm >> is highly vulnerable to corruption if a power loss occurs while it is >> being updated. > > May be the Tokyo Cabinet DBMS is more better? BerkeleyDB is required > administration and it's strange solution in many embedded devices and > for some desktops and servers too. (1) Tokyo cabinet did not exist in 2001 when I was looking for an alternative to gdbm. (2) When you configure Tokyo cabinet so that it is robust against power loss (so that it doesn't corrupt the database file during a power loss) it is about 10x slower than SQLite. (3) Tokyo cabinet has less concurrency than SQLite. (4) Tokyo cabinet is not able to put an entire SQL database into a single file. It would require a directory full of files, just as gdbm did. (5) Tokyo cabinet does not support transactions across files, so it cannot be used in an SQL engine that is transaction (since at the SQL level you must have transactions that span multiple tables.) I can come up with additional reasons why replacing the existing SQLite backend with TC is not a good idea, but perhaps the above will suffice. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Preserving column size
I have a C/C++ application in which I want to store data from a struct into a table (using SQLite 3.6.23) and later retrieve data from the table and store it back in the struct. But, I need a general interface for this as there are many types of structs used. So, what I have is a function that accepts a pointer to the struct (ptr), the struct size, and a sqlite3_stmt* variable for the current row. I then iterate through each column of the row and store the value retrieved at ptr's address. Then I increment ptr based on the size of the column. Thus, as long as I store and retrieve columns in the same order I should be storing the right values in the right variables for a particular struct instance. However, this method breaks down if a store a 16-bit integer value like "99" and SQLite stores it internally as an 8-bit value (to save space) and subsequently retrieves it and gives me a value of 1 for sqlite3_column_bytes(). This causes alignment issues with the variables in the struct. So, is there a way I can tell SQLite to preserve the data size in a particular column? E.g.: CREATE TABLE test ( val1 INTEGER, val2 INT2, val3 INT1 ... ); Here val1 is always 4-bytes, val2 is always 2 bytes, and val3 is always 1 byte. I'd prefer a way to do this without having to cast every last column in a SELECT query to the right size. Hopefully that made sense. Thanks, Kevin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Preserving column size
Kevin M. wrote: > I have a C/C++ application in which I want to store data from a > struct into a table (using SQLite 3.6.23) and later retrieve data > from the table and store it back in the struct. But, I need a > general interface for this as there are many types of structs used. > So, what I have is a function that accepts a pointer to the struct > (ptr), the struct size, and a sqlite3_stmt* variable for the current > row. I then iterate through each column of the row and store the > value retrieved at ptr's address. Then I increment ptr based on the > size of the column. Thus, as long as I store and retrieve columns in > the same order I should be storing the right values in the right > variables for a particular struct instance. Can't you just store the struct as a blob and be done with it? > However, this method breaks down if a store a 16-bit integer value > like "99" Why is 99 a 16-bit integer value and not, say, an 8-bit or a 32-bit one? How is SQLite supposed to know? > and SQLite stores it internally as an 8-bit value (to save > space) and subsequently retrieves it and gives me a value of 1 for > sqlite3_column_bytes(). sqlite3_column_bytes is really only meaningful for strings and blobs, not for numbers. > This causes alignment issues with the > variables in the struct. So, is there a way I can tell SQLite to > preserve the data size in a particular column? In order to preserve it, you need some way to specify it first. How do you plan to do that? > E.g.: > > CREATE TABLE test ( val1 INTEGER, val2 INT2, val3 INT1 ... ); > > Here val1 is always 4-bytes, val2 is always 2 bytes, and val3 is > always 1 byte. Well, if you want to invent a naming convention like that, why don't you act on it yourself? Retrieve the type name with sqlite3_column_decltype, handle it as you see fit. > I'd prefer a way to do this without having to cast every last column > in a SELECT query to the right size. You can't do that anyway, even if you wanted to. There's no syntax in SQLite that would support that. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Preserving column size
> So, is there a way I can tell SQLite to preserve the data size in a > particular column? As long as you use blobs to store your structs, yes you can tell SQLite to preserve data size. You cannot do that for any other type, but it sounds like you don't need any other type because all you need is to store your structs in binary format. But in this case be aware of endianness and alignment issues that can be different on different platforms with different compilers. So my good advice will be: rethink your pattern of working with data to avoid using "pointer to struct" and "struct size" but use real data values instead. Pavel On Mon, Mar 29, 2010 at 4:19 PM, Kevin M. wrote: > I have a C/C++ application in which I want to store data from a struct into a > table (using SQLite 3.6.23) and later retrieve data from the table and store > it back in the struct. But, I need a general interface for this as there are > many types of structs used. So, what I have is a function that accepts a > pointer to the struct (ptr), the struct size, and a sqlite3_stmt* variable > for the current row. I then iterate through each column of the row and store > the value retrieved at ptr's address. Then I increment ptr based on the size > of the column. Thus, as long as I store and retrieve columns in the same > order I should be storing the right values in the right variables for a > particular struct instance. > > However, this method breaks down if a store a 16-bit integer value like "99" > and SQLite stores it internally as an 8-bit value (to save space) and > subsequently retrieves it and gives me a value of 1 for > sqlite3_column_bytes(). This causes alignment issues with the variables in > the struct. So, is there a way I can tell SQLite to preserve the data size > in a particular column? E.g.: > > CREATE TABLE test ( val1 INTEGER, val2 INT2, val3 INT1 ... ); > > Here val1 is always 4-bytes, val2 is always 2 bytes, and val3 is always 1 > byte. > > I'd prefer a way to do this without having to cast every last column in a > SELECT query to the right size. > > Hopefully that made sense. > > Thanks, > Kevin > > > > ___ > 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] Preserving column size
> Can't you just store the struct as a blob and be done with it? No, I can't use a blob because there are other queries where I want individual columns. Storing to and from a struct is only one part of the application. Sorry, perhaps I should have specified that too. > Why is 99 a 16-bit integer value and not, say, an 8-bit or a 32-bit one? How > is SQLite supposed to know? How would SQLite know if "99" is 16-bit, 8-bit, 32-bit, etc... using the CREATE TABLE specified data types as a hint. > sqlite3_column_bytes is really only meaningful for strings and blobs, not for numbers. Perhaps, but I know for SQLITE_INTEGER type it gives me either 1, 2, or 4 for various inputs I've tried. It's just not always the same as the original value used. > In order to preserve it, you need some way to specify it first. How do you > plan to do that? Again, the data types specified in a CREATE TABLE statement can serve as a hint. >> E.g.: >> >> CREATE TABLE test ( val1 INTEGER, val2 INT2, val3 INT1 ... ); >> >> Here val1 is always 4-bytes, val2 is always 2 bytes, and val3 is >> always 1 byte. > Well, if you want to invent a naming convention like that, why don't you act on it yourself? Retrieve the type name with sqlite3_column_decltype, handle it as you see fit. That was just an example, not an actual table declaration for this app. So, basically I either store everything as a blob and lose the ability to query individual columns, or I have to rework a lot of existing code (the app uses MySQL now) to work with SQLite's own nuances? -- Kevin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Preserving column size
On Mar 29, 2010, at 4:19 PM, Kevin M. wrote: > > However, this method breaks down if a store a 16-bit integer value > like "99" and SQLite stores it internally as an 8-bit value (to save > space) and subsequently retrieves it and gives me a value of 1 for > sqlite3_column_bytes(). I think you are misunderstanding what sqlite3_column_bytes() does The sqlite3_column_bytes() function converts the result (an integer in your case) into a string, then returns the number of bytes in that string, exclusive of the final nul terminator. SQLite does not provide a means to determine the number of bytes of underlying storage used for a value. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Preserving column size
Kevin M. wrote: >> Why is 99 a 16-bit integer value and not, say, an 8-bit or a 32-bit >> one? How is SQLite supposed to know? > > How would SQLite know if "99" is 16-bit, 8-bit, 32-bit, etc... using > the CREATE TABLE specified data types as a hint. INT1, INT2 and so on don't have any special meaning to SQLite. You are engaging in wishful thinking. See also http://sqlite.org/datatype3.html >> sqlite3_column_bytes is really only meaningful for strings and blobs, > not for numbers. > > Perhaps, but I know for SQLITE_INTEGER type it gives me either 1, 2, > or 4 for various inputs I've tried. It's just not always the same as > the original value used. What do you mean, "original value used"? What original value, used where and how? SQLite doesn't provide any way to specify how many bytes of storage you want reserved for a number. >>> E.g.: >>> >>> CREATE TABLE test ( val1 > INTEGER, val2 INT2, val3 INT1 ... ); >>> >>> Here val1 > is always 4-bytes, val2 is always 2 bytes, and val3 is >>> > always 1 byte. > >> Well, if you want to invent a naming > convention like that, why don't you act on it yourself? Retrieve the > type name with sqlite3_column_decltype, handle it as you see fit. > > That was just an example, not an actual table declaration for this > app. I'm confused. Do you, or don't you, want to use type names like INT1 or INT2 to indicate the width in bytes of an integer field? If you do, you can act on them yourself using sqlite3_column_decltype. If you don't, the question remains: how do you plan to indicate the width of the field? > So, basically I either store everything as a blob and lose the > ability to query individual columns, or I have to rework a lot of > existing code (the app uses MySQL now) to work with SQLite's own > nuances? Yes. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Preserving column size
On Mon, Mar 29, 2010 at 4:19 PM, Kevin M. wrote: > I have a C/C++ application in which I want to store data from a struct into > a table (using SQLite 3.6.23) and later retrieve data from the table and > store it back in the struct. But, I need a general interface for this as > there are many types of structs used. You could convert the structs to JSON format and store them in a TEXT field. There are a number of C++ JSON libraries listed here: http://www.json.org/ Regards Tim Romano P.S. Apologies if this reply comes through twice -- I resubscribed under a new email address but replied under the old address. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Preserving column size
On Mon, Mar 29, 2010 at 4:14 PM, Tim Romano wrote: > On Mon, Mar 29, 2010 at 4:19 PM, Kevin M. wrote: > >> I have a C/C++ application in which I want to store data from a struct into >> a table (using SQLite 3.6.23) and later retrieve data from the table and >> store it back in the struct. But, I need a general interface for this as >> there are many types of structs used. > > > You could convert the structs to JSON format and store them in a TEXT > field. > There are a number of C++ JSON libraries listed here: http://www.json.org/ > This is probably the best way forward, however, conceptually it is no different from just storing the struct as a blob, something that Igor already suggested. The OP won't be able to retrieve a specific member of the struct directly. He will have to retrieve the entire value, reconstruct the struct in his application, then retrieve the value that he wants. May as well store is as a blob and be done with it. I have done both -- stored a perl data structure as a JSON string, as well as a blob using Storable (a core perl module). Works very well. > Regards > Tim Romano > P.S. Apologies if this reply comes through twice -- I resubscribed under a > new email address but replied under the old address. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Preserving column size
> I'm confused. Do you, or don't you, want to use type names like INT1 or INT2 > to indicate the width in bytes of an integer field? If you do, you can act on > them yourself using sqlite3_column_decltype. If you don't, the question > remains: how do you plan to indicate the width of the field? sqlite3_column_decltype sounds like it may fit the bill! ... especially combined with sqlite3_column_type. Thanks! -- Kevin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Preserving column size
On Mon, Mar 29, 2010 at 02:01:42PM -0700, Kevin M. scratched on the wall: > > Can't you just store the struct as a blob and be done with it? > > No, I can't use a blob because there are other queries where I want > individual columns. Storing to and from a struct is only one part > of the application. Sorry, perhaps I should have specified that too. Then store each field as a BLOB. That will preserve both size and representation. You'll lose some of the format conversions, but that doesn't sound like a big deal in your case. You might want to pick a bit-ordering, however. > > In order to preserve it, you need some way to specify it first. How > > do you plan to do that? > > Again, the data types specified in a CREATE TABLE statement can > serve as a hint. Yes, but your application will need to deal with that. SQLite's column "types" are not really types, their hints to an affinity and storage class. Or you can use a datatype that includes a length, such as a BLOB. > or I have to rework a lot of existing > code (the app uses MySQL now) to work with SQLite's own nuances? This is hardly an SQLite nuance. Although MySQL offers some explicit integer sizes, many databases do not. Database types are not C types. Any assumption otherwise is going to get into trouble. In fact, SQLite matches standard C types more than most databases. If, for whatever reason, your struct/table creation and packing code knows the length of each field, but the unpacking code does not, you need to store that information somewhere. If you want to use a datatype, such as BLOB that stores that info, fine. If you want to use a column type with some type of app-specific encoding, fine. Either way requires you to modify the unpacking code. I don't really see the difference between something that queries a type size and something that queries the column type and does a bit of parsing. You also have the issue that most modern compilers will insert padding, depending on the layout of the struct, but I assume you have some way to deal with that. You're not always going to be able to write a generic "unpack" function, however. Just like the packer, at some point it will likely need to know about the structure it is unpacking. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] More Mac problems - building 3.6.23
Attempting to build 2.6.23 command shell under osx 10.4.11 doesn't work because of unlinked symbol gethostuuid, which seems to be associated with the SQLITE_ENABLE_LOCKING_STYLE option Compiling with SQLITE_ENABLE_LOCKING_STYLE=0 links, and works on native disks, but fails immediately on networked disks with "disk I/O error" from somewhere. Advice? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Berkeley DB adds SQL using SQLite API !!
Hello! On Monday 29 March 2010 22:26:41 D. Richard Hipp wrote: > I can come up with additional reasons why replacing the existing > SQLite backend with TC is not a good idea, but perhaps the above will > suffice. But how about insert/update performance on big indices? When index size is more than cache_size the inserts/updates in SQLite are stopped by fact. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] chidb: A didactic RDBMS (based on SQLite)
Hi everyone, I'd like to share a project we've developed at the University of Chicago, as it may be of interest to some people on this list, specially those involved in higher education: http://people.cs.uchicago.edu/~borja/chidb/ chidb is a didactic relational database management system (RDBMS) designed for teaching how a RDBMS is built internally, from the data organization in files all the way up to the SQL parser and query optimizer. The design of chidb is based on SQLite, with several simplifying assumptions that make it possible to develop a complete chidb implementation over the course of a quarter or semester. In fact, the chidb file format is a subset of SQLite, so well-formed chidb files can be opened and tested with SQLite. We designed chidb last year for the CS department's database course (and will be using it again this year), and have just recently put our code and instructional materials online, which you can find at the above URL. Our experience using chidb in the classroom was very positive and we're hoping that other instructors and students will find it useful or, at least, interesting to peruse. Cheers! -- Borja Sotomayor, University of Chicago Ph.D. Candidate, Department of Computer Science Ryerson 257-C, 1100 East 58th Street, Chicago, IL http://people.cs.uchicago.edu/~borja/ Haizea: http://haizea.cs.uchicago.edu/ "Dis maschine vill run und run!" -- Kurt Gödel (on the Turing Machine) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Preserving column size
Kevin M. wrote:
> I have a C/C++ application in which I want to store data from a struct into a
> table (using SQLite 3.6.23) and later retrieve data from the table and store
> it back in the struct. But, I need a general interface for this as there are
> many types of structs used. So, what I have is a function that accepts a
> pointer to the struct (ptr), the struct size, and a sqlite3_stmt* variable
> for the current row. I then iterate through each column of the row and store
> the value retrieved at ptr's address. Then I increment ptr based on the size
> of the column. Thus, as long as I store and retrieve columns in the same
> order I should be storing the right values in the right variables for a
> particular struct instance.
>
> However, this method breaks down if a store a 16-bit integer value like "99"
> and SQLite stores it internally as an 8-bit value (to save space) and
> subsequently retrieves it and gives me a value of 1 for
> sqlite3_column_bytes(). This causes alignment issues with the variables in
> the struct. So, is there a way I can tell SQLite to preserve the data size
> in a particular column? E.g.:
>
> CREATE TABLE test ( val1 INTEGER, val2 INT2, val3 INT1 ... );
>
> Here val1 is always 4-bytes, val2 is always 2 bytes, and val3 is always 1
> byte.
>
> I'd prefer a way to do this without having to cast every last column in a
> SELECT query to the right size.
>
The layout of a column is an implementation detail and not part of
SQLite's type system. I wouldn't recommend depending on it.
You might want to consider writing a user-defined function to convert a
column row into a BLOB that matches your struct layout. Then you can
just write
SELECT PACK('ihb', val1, val2, val3) FROM test
and memcpy the resulting BLOB into your struct.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Preserving column size
On 3/29/2010 4:19 PM, Kevin M. wrote: > I have a C/C++ application in which I want to store data from a struct into a > table (using SQLite 3.6.23) and later retrieve data from the table and store > it back in the struct. But, I need a general interface for this as there are > many types of structs used. > Couldn't you convert the structs to JSON format and store them in a TEXT field? There are a number of C++ JSON libraries listed here: http://www.json.org/ Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
