[sqlite] 3.5.4 to 3.7.13 and back(!)
Hi, Did the on-disk file-format of SQLite databases change between version 3.5.4 and the latest (3.7.13)? Will I be able to back to 3.5.4 without any special conversions? Thanks, Folkert van Heusden De informatie verzonden met dit emailbericht is uitsluitend bestemd voor de geadresseerde. Gebruik van deze informatie door anderen dan de geadresseerde is verboden. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is niet toegestaan. Afzender staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden email, noch voor tijdige ontvangst daarvan. Afzender attendeert erop dat de vertrouwelijkheid van informatie verzonden per email niet gewaarborgd is. Minder printen is beter voor het milieu. - VolkerWessels Telecom is de handelsnaam van Volker Wessels Telecom bv - KvK Gooi-, Eem- en Flevoland 24321042- The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. Sender is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Please note that the confidentiality of e-mail communication is not warranted. Less printing is better for the environment -VolkerWessels Telecom is the tradingname of Volker Wessels Telecom bv - Chamber of Commerce for the district Gooi-, Eem- en Flevoland 24321042- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Quoting id versus 'id' in query
On 11/06/2012 21:48, Larry Brasfield wrote: On June 11, rick wrote: Something I noticed today: sqlite select * FROM words WHERE word = id; nothing sqlite select * FROM words WHERE word = 'id'; 13556|id sqlite .schema CREATE TABLE words ( id integer primary key, wordvarchar(64) ); Yes, it can be explained: http://www.sqlite.org/lang_keywords.html says: 'keyword' A keyword in single quotes is a string literal. keyword A keyword in double-quotes is an identifier So, id is interpreted as a column name, not as the string literal 'id', unless the value in the double quotes is not an identifier (column name). I think this is quite nasty behaviour. Hope it prevents someone making the same mistake! You should take up the nastiness issue with the authors of the SQL-92 standard and its successors. Supporting the convention you just noticed is the least surprising behavior for those who have learned SQL as defined, and hence is not generally regarded as a nasty feature. Well, I'm not entirely a casual user, but I don't know the SQL-92 standard or it's successors. I'm an engineer, and sqlite for me is a tool. Above query was used a long time in a MySQL DB, where it works as (I) expected. I just hope to warn others against naively using double quotes. Something, by the way, that MySQL does as I expect, although probably not exactly according to '(Second Informal Review Draft) ISO/IEC 9075:1992, Database Language SQL- July 30, 1992'. If it's so easy to make mistakes like this the standard could be considered a poor one. Good luck. hm. rick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
Hi, I am new to SQLITE Would be using Microsoft VS 2008 2010 Is it possible to safely use multiple threads in a process with each thread making its own connection to the same database rc = sqlite3_open(file::memory:?cache=shared, db); ie. does sqlite implement an alternative to f_lock especially for :memory: databases. Many thanks Nick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.5.4 to 3.7.13 and back(!)
On Tue, Jun 12, 2012 at 3:54 AM, Heusden, F. van folkert.van.heus...@mcom-nl.com wrote: Hi, Did the on-disk file-format of SQLite databases change between version 3.5.4 and the latest (3.7.13)? Will I be able to back to 3.5.4 without any special conversions? The SQLite file format http://www.sqlite.org/fileformat2.html is unchanged since 3.5.4, except for the WAL-mode enhancement of 3.7.0. So as long as you avoid using WAL-mode, your database will still be readable and writable by version 3.5.4. And even if you do use WAL-mode, if you will just change back to DELETE mode before handing off to 3.5.4, your database will still be readable and writable. Thanks, Folkert van Heusden De informatie verzonden met dit emailbericht is uitsluitend bestemd voor de geadresseerde. Gebruik van deze informatie door anderen dan de geadresseerde is verboden. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is niet toegestaan. Afzender staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden email, noch voor tijdige ontvangst daarvan. Afzender attendeert erop dat de vertrouwelijkheid van informatie verzonden per email niet gewaarborgd is. Minder printen is beter voor het milieu. - VolkerWessels Telecom is de handelsnaam van Volker Wessels Telecom bv - KvK Gooi-, Eem- en Flevoland 24321042- The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. Sender is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Please note that the confidentiality of e-mail communication is not warranted. Less printing is better for the environment -VolkerWessels Telecom is the tradingname of Volker Wessels Telecom bv - Chamber of Commerce for the district Gooi-, Eem- en Flevoland 24321042- ___ 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] (no subject)
On 06/12/2012 05:41 PM, Nicholas Thompson wrote: Hi, I am new to SQLITE Would be using Microsoft VS 2008 2010 Is it possible to safely use multiple threads in a process with each thread making its own connection to the same database rc = sqlite3_open(file::memory:?cache=shared,db); Should be safe, yes. Assuming you are using the library in either multi-thread or serialized (the default) mode. http://www.sqlite.org/threadsafe.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Specifying a nullable column
Hi Kevin, I understand the 'NOT NULL' column constraint and the syntax diagram and text describe it quite well. What isn't described is whether or not specifying simply 'NULL' is truly supported. For example, the following create table statement is accepted by SQLite and appears to behave as expected (column 'Id' is not null and column 'Name' is nullable): create table ( Id integer not null, Name text null ); I would like to know if 'NULL' is meant to be a valid column constraint or not. It seems to work, but the documentation doesn't mention it explicitly. Thanks, -Paul On 11-Jun-2012 15:35, Kevin Benson wrote: On Mon, Jun 11, 2012 at 10:35 AM, Paul Medynskipmedyn...@rim.com wrote: Hi folks, I notice that the syntax diagram for 'create table' shows the 'column-constraint' definition as requiring 'null' to always be preceded by 'not'. I don't see any definition that supports just 'null'. However, when using SQLite3 3.7.3 or 3.7.9, I can create a table and specify any column as Footype null, and it works as expected allowing the column to contain null values. Is the syntax diagram simply out of date, or am I doing something that appears to work, but will bite me in the end? :) http://www.sqlite.org/draft/lang_createtable.html A CREATE TABLE command specifies the following attributes of the new table: - - - - - A default value or expression for each column in the table. - - - A set of SQL constraints for each table. SQLite supports UNIQUE, NOT NULL, CHECK and FOREIGN KEY constraints. A table created using CREATE TABLE AS has no PRIMARY KEY and no constraints of any kind. The default value of each column is NULL. -- -- -- --Ô¿Ô-- K e V i N ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Paul Medynski BlackBerry Messenger Group Senior Software Developer Research In Motion pmedyn...@rim.com50 Innovation Drive Phone: +1 902 982 6134 Bedford, NS Fax:+1 902 482 4380 B4B 0G4 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Specifying a nullable column
On Tue, Jun 12, 2012 at 7:04 AM, Paul Medynski pmedyn...@rim.com wrote: Hi Kevin, I understand the 'NOT NULL' column constraint and the syntax diagram and text describe it quite well. What isn't described is whether or not specifying simply 'NULL' is truly supported. For example, the following create table statement is accepted by SQLite and appears to behave as expected (column 'Id' is not null and column 'Name' is nullable): create table ( Id integer not null, Name text null ); I would like to know if 'NULL' is meant to be a valid column constraint or not. It seems to work, but the documentation doesn't mention it explicitly. The NULL constraint is parsed and then ignored. http://www.sqlite.org/src/artifact/f29df90bd3a?ln=299 Thanks, -Paul On 11-Jun-2012 15:35, Kevin Benson wrote: On Mon, Jun 11, 2012 at 10:35 AM, Paul Medynskipmedyn...@rim.com wrote: Hi folks, I notice that the syntax diagram for 'create table' shows the 'column-constraint' definition as requiring 'null' to always be preceded by 'not'. I don't see any definition that supports just 'null'. However, when using SQLite3 3.7.3 or 3.7.9, I can create a table and specify any column as Footype null, and it works as expected allowing the column to contain null values. Is the syntax diagram simply out of date, or am I doing something that appears to work, but will bite me in the end? :) http://www.sqlite.org/draft/**lang_createtable.htmlhttp://www.sqlite.org/draft/lang_createtable.html A CREATE TABLE command specifies the following attributes of the new table: - - - - - A default value or expression for each column in the table. - - - A set of SQL constraints for each table. SQLite supports UNIQUE, NOT NULL, CHECK and FOREIGN KEY constraints. A table created using CREATE TABLE AS has no PRIMARY KEY and no constraints of any kind. The default value of each column is NULL. -- -- -- --Ô¿Ô-- K e V i N __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- --**-- Paul Medynski BlackBerry Messenger Group Senior Software Developer Research In Motion pmedyn...@rim.com50 Innovation Drive Phone: +1 902 982 6134 Bedford, NS Fax:+1 902 482 4380 B4B 0G4 __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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] Specifying a nullable column
On 12-Jun-2012 08:08, Richard Hipp wrote: On Tue, Jun 12, 2012 at 7:04 AM, Paul Medynskipmedyn...@rim.com wrote: Hi Kevin, I understand the 'NOT NULL' column constraint and the syntax diagram and text describe it quite well. What isn't described is whether or not specifying simply 'NULL' is truly supported. For example, the following create table statement is accepted by SQLite and appears to behave as expected (column 'Id' is not null and column 'Name' is nullable): create table ( Id integer not null, Name text null ); I would like to know if 'NULL' is meant to be a valid column constraint or not. It seems to work, but the documentation doesn't mention it explicitly. The NULL constraint is parsed and then ignored. http://www.sqlite.org/src/artifact/f29df90bd3a?ln=299 Perfect! Thanks Richard. -Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Quoting id versus 'id' in query
On 12 Jun 2012, at 9:52am, rick jansm...@xs4all.nl wrote: Well, I'm not entirely a casual user, but I don't know the SQL-92 standard or it's successors. I'm an engineer, and sqlite for me is a tool. Above query was used a long time in a MySQL DB, where it works as (I) expected. SQL (note that I'm talking about the same SQL-92 standard Larry referred to) does not use the same convention of double-quoting text strings that you are used to from other programming languages. It's something you need to get used to when using SQL no matter which SQL engine you use. MySQL's use of double quotes is something special for MySQL, not normal for SQL. We used to have to deal with different string quoting rules for FORTRAN, COBOL, ALGOL, BASIC, PASCAL, and LISP. Now almost everything uses non-directional double-quotes. Kids these days have it easy. And get off my lawn. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Features of SQLite question
I dont' see the difference between a a single database and 2 in your example. I would claim the delete is most certainly not unintended. If you had just one database a user could come in one hour later and delete the record you updated. I realize in the pure sense this may not be desirable in some situations. But you're talking right now about how you keep two users in sync which is a totally different story. And, of course, you NEVER really delete a record...you just tag it as deleted. So I still maintain -- he who updates last wins -- regardless of 1, or N databases. I would bet that 1/10th or 1/100th timing with appropriately time-synced computers would work just fine. Trying to sync two databases without transactions is pretty hopeless and fraught with problems. Unless you have a master who always wins. You can tag a record as changed and the slave can update if the master has not changed, otherwise the master wins. The master then replicates to all slaves. As for connectivity each system just ensures that it not process any transactions beyond the most recent time received from the other system. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org] Sent: Monday, June 11, 2012 5:31 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Features of SQLite question On 11 Jun 2012, at 11:11pm, Black, Michael (IS) michael.bla...@ngc.com wrote: Isn't it true that semi-accurate (sub-second) time-tagged transactions will generally keep 2 databases in sync? I've done that before with considerable success. You don't sync the fields...you sync the transactions. The presumption is that later transactions win regardless of intervening updates. Your idea, if expressed in terms of SQL, is that you do not keep copies of the records in the databases, instead you keep copies of all the INSERT, UPDATE and DELETE commands executed. To synchronise two databases you just make sure all the commands are executed on all copies of the database. It's a great idea. Except it doesn't work unless the commands are executed in the same order and there's no way to do that starting with one or other copy which has been changed since the last sync. So you need a third copy of the databases: the database as it was the last time two copies were synchronised. And this needs to be stored either centrally or with each copy of the database, which means there's no magic simple synchronisation algorithm. And then you get into intent. Suppose you have this row of data: {name: Mike Smith, town: London} on your database. Since the last synchronisation one user of the database executes the command UPDATE contacts SET town='Hendon' WHERE town='London' And an hour later the user of the other copy executes the command DELETE FROM contacts WHERE town='Hendon' Before the two were synchonised, the row for Mike Smith still exists in both copies. Both users were happy with this, even though they didn't know they had different towns for Mike. But after the two are synchonised and all transactions are played back in log order, Mike Smith has somehow disappeared from both copies. That's not synchronisation, that's deleting data we wanted to keep ! That's disastrous ! Only problem is when times are too close (beyond your time-sync resolution) which requires human intervention...though in a user-driven system that should be nigh on to impossible to create. For the sake of discussion you can pretend that your log stores times to Unix epoch precision. Even with that, as you can see, there are still problems. I do realize the complexity of keeping two database in syncbut transaction systems have been around for decades. Depends on your application. Yet it's still an unsolved problem. I can (maybe should) write some text layout out this problem and some solutions which work in some situations but there's still no general solution that I know of. Simon. ___ 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] Quoting id versus 'id' in query
That's the nice thing about standards...there are so many to choose from...:-( Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
Nicholas Thompson nick.john.thomp...@googlemail.com wrote: Is it possible to safely use multiple threads in a process with each thread making its own connection to the same database rc = sqlite3_open(file::memory:?cache=shared, db); Every time you connect to a :memory: database, a new in-memory database is created. There ain't no such thing as two connections to the same memory database. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On Tue, Jun 12, 2012 at 8:32 AM, Igor Tandetnik itandet...@mvps.org wrote: Nicholas Thompson nick.john.thomp...@googlemail.com wrote: Is it possible to safely use multiple threads in a process with each thread making its own connection to the same database rc = sqlite3_open(file::memory:?cache=shared, db); Every time you connect to a :memory: database, a new in-memory database is created. There ain't no such thing as two connections to the same memory database. Igor, This comment will soon become out-of-date. See http://www.sqlite.org/draft/releaselog/3_7_13.html. Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS4 Questions
There was a somehow recent change in the snippets algorithm, it used to return snippets from a number of columns in a single row if there were multiple matches, separated with Now, it will try to find a fragment of the row that contains ALL search terms, using snippets from the other columns only if such a fragment (which contains all terms) can't be found. For the second question, all the terms saved on a fts table are tokenized before going into the index - and so are your search terms. The default simple tokenizer will strip punctuation, special characters, etc. If you need to store and search such terms, you can create your own tokenizer and attach it to your fts table. More info on both topics can be found on the docs : http://www.sqlite.org/fts3.html -- View this message in context: http://sqlite.1065341.n5.nabble.com/FTS4-Questions-tp62323p62343.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] Problem with SQL error: unrecognized token: !!
Hello List, I'm quite new to SQLite. I try to convert from a MySQL DB to SQLite. I've already done several changes to the MySQL dump but now I'm a bit stuck. If I try to insert a row containing two subsequent exclamation marks !!, I get the error `SQL error: unrecognized token: !!`. I haven't found useful information while searching the net so I try to get a answer this way. Could you tell me what I'm doing wrong and what is the meaning of !! in text in SQLite. As a reference here is the INSERT that fails as described: sqlite INSERT INTO 'gb' VALUES ('Die Alex','http://','Heisann Jungs! \n\nIch drück euch alle meine Daumen für's finale!! Ihr schafft das!! \n\n','193.216.173.35','2007-11-05 20:00:20',NULL); ... ; ... ; ... ' ... ; SQL error: unrecognized token: !! sqlite TIA Greets Alex ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with SQL error: unrecognized token: !!
On 6/12/2012 2:45 PM, Alexander Koeppe wrote: sqlite INSERT INTO 'gb' VALUES ('Die Alex','http://','Heisann Jungs! \n\nIch drück euch alle meine Daumen für' This aprostrophe terminates the string literal. The rest is part of the SQL statement, and of course it's nonsense. Make it für''s (that's two apostrohes in a row, not a double quote) -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with SQL error: unrecognized token: !!
On 13 Jun 2012, at 1:45am, Igor Tandetnik itandet...@mvps.org wrote: On 6/12/2012 2:45 PM, Alexander Koeppe wrote: sqlite INSERT INTO 'gb' VALUES ('Die Alex','http://','Heisann Jungs! \n\nIch drück euch alle meine Daumen für' This aprostrophe terminates the string literal. The rest is part of the SQL statement, and of course it's nonsense. Make it für''s (that's two apostrohes in a row, not a double quote) Also the single quotes around 'gb' are wrong. Either leave them out altogether, or use double quotes instead. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with SQL error: unrecognized token: !!
Am 13.06.2012 02:45, schrieb Igor Tandetnik: On 6/12/2012 2:45 PM, Alexander Koeppe wrote: sqlite INSERT INTO 'gb' VALUES ('Die Alex','http://','Heisann Jungs! \n\nIch drück euch alle meine Daumen für' This aprostrophe terminates the string literal. The rest is part of the SQL statement, and of course it's nonsense. Make it für''s (that's two apostrohes in a row, not a double quote) Oh damn, I've overlooked this. Thanks that was the reason. I though I did replaced them already, but my expression might have missed a few. Now everything went through. Thanks. Greets Alex ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] rekey
hello, when I use sqlite3_key to crypt my sqlite database my.db and the key is abc, and when I use sqlitespy to open the database, then sqlitespy require me to input the key, when I input abc to it,but show me couldn't open the database, I don't know why, these following function is the source: 1: j = sqlite3_key(db, argv[1], 2); 2: j = sqlite3_rekey(db,NULL, 0 ); 3: int sqlite3_key(sqlite3 *db, const void *zKey, int nKey) { /* The key is only set for the main database, not the temp database */ return sqlite3CodecAttach(db, 0, zKey, nKey); } 4: int sqlite3CodecAttach(sqlite3* db, int nDb, const void* zKey, int nKey) { /* Attach a key to a database. */ Codec* codec = (Codec*) sqlite3_malloc(sizeof(Codec)); CodecInit(codec); /* No key specified, could mean either use the main db's encryption or no encryption */ if (zKey == NULL || nKey = 0) { /* No key specified */ if (nDb != 0 nKey 0) { Codec* mainCodec = (Codec*) mySqlite3PagerGetCodec(sqlite3BtreePager(db-aDb[0].pBt)); /* Attached database, therefore use the key of main database, if main database is encrypted */ if (mainCodec != NULL CodecIsEncrypted(mainCodec)) { CodecCopy(codec, mainCodec); CodecSetBtree(codec, db-aDb[nDb].pBt); #if (SQLITE_VERSION_NUMBER = 3006016) mySqlite3PagerSetCodec(sqlite3BtreePager(db-aDb[nDb].pBt), sqlite3Codec, sqlite3CodecSizeChange, sqlite3CodecFree, codec); #else #if (SQLITE_VERSION_NUMBER = 3003014) sqlite3PagerSetCodec(sqlite3BtreePager(db-aDb[nDb].pBt), sqlite3Codec, codec); #else sqlite3pager_set_codec(sqlite3BtreePager(db-aDb[nDb].pBt), sqlite3Codec, codec); #endif db-aDb[nDb].pAux = codec; db-aDb[nDb].xFreeAux = sqlite3CodecFree; #endif } else { CodecSetIsEncrypted(codec, 0); sqlite3_free(codec); } } } else { /* Key specified, setup encryption key for database */ CodecSetIsEncrypted(codec, 1); CodecSetHasReadKey(codec, 1); CodecSetHasWriteKey(codec, 1); CodecGenerateReadKey(codec, (char*) zKey, nKey); CodecCopyKey(codec, 1); CodecSetBtree(codec, db-aDb[nDb].pBt); #if (SQLITE_VERSION_NUMBER = 3006016) mySqlite3PagerSetCodec(sqlite3BtreePager(db-aDb[nDb].pBt), sqlite3Codec, sqlite3CodecSizeChange, sqlite3CodecFree, codec); #else #if (SQLITE_VERSION_NUMBER = 3003014) sqlite3PagerSetCodec(sqlite3BtreePager(db-aDb[nDb].pBt), sqlite3Codec, codec); #else sqlite3pager_set_codec(sqlite3BtreePager(db-aDb[nDb].pBt), sqlite3Codec, codec); #endif db-aDb[nDb].pAux = codec; db-aDb[nDb].xFreeAux = sqlite3CodecFree; #endif } return SQLITE_OK; } ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rekey
On 13 Jun 2012, at 2:27am, YAN HONG YE yanhong...@mpsa.com wrote: hello, when I use sqlite3_key to crypt my sqlite database my.db and the key is abc, and when I use sqlitespy to open the database, then sqlitespy require me to input the key, when I input abc to it,but show me couldn't open the database, If you use key 'abc' with your own application, can your own application open the database and see the data you put in it earlier ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] HTML5 database commands as a JavaScript-SQLite bridge
So for some time I've been searching for a general-purpose SQLite bridge for javascript. When I need this I'm generally keeping the database on a computer which is also a web server, so I just use a PHP file which I've written to act as a bridge: make JavaScript execute an HTTP operation to POST a request to the PHP file, the PHP file executes the command in the request, and if the command returns rows it returns them as its contents encoded as JSON. It all works efficiently enough for my purposes. But I can't use this at home because I don't want to run a general-purpose web server on my home computer. HTML5 includes database commands which everyone has implemented by using SQLite calls which is both a tremendous compliment to the SQLite team and a problem because the HTML5 team don't want to build SQLite into the spec. However, the databases in HTML5 persist as belonging to particular web sites. You can't specify an arbitrary file on disk to open, the browser maintains the databases as attached to a particular site. This is done on purpose, to prevent what could be a disastrous security problem allowing people who run web sites to read the contents of SQLite databases on your computer. Exactly the same mechanism is used to prevent web browsers from opening arbitrary text files on your hard disk, which would be a similar security disaster. So we have an excellent JavaScript-SQLite bridge but it's intentionally prevented from accessing SQLite databases you keep on your computer. So it's useful only if you only ever need to access the database inside a specific browser. But then I thought … well, since it turns out that everyone implements this using SQLite, what would happen if I did an ATTACH ? And on that note I'll bid you goodnight. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users