Re: [sqlite] sqlite journal file question
This was my initial reading too Paul, but reading the OP post again it could be either - who knows what is meant by syncing. I also think this is the main point for the OP - If by syncing he really means copying the file then Richard's advice, else if he means adding via normal DB connection then the other options. If the DB files are copied, it needs to be copied in perfect version sync, so the program/service filling the DB needs to be informed to wait a moment while copying. I still think for this scenario the backup API would be better. Otherwise, posting SQL to the second DB would require no such trickery, no wait-states and be a lot less IO intensive. On 2/13/2015 10:16 AM, Paul Sanderson wrote: Richard I read that the db on the standby machine is being updated at a record by record level, i.e. not copied in its entirety. In this scenario I can't see the two db files being guaranteed binary compatible. Copying the journal across in this scenario would imo be a mistake. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 13 February 2015 at 02:47, Richard Hipp d...@sqlite.org wrote: On 2/12/15, Mayank Kumar (mayankum) mayan...@cisco.com wrote: Hi All We have two systems which are running in active/standby configuration. The active machine, is actively writing sqlite transactions to a file abcd.db. The standby is syncing the abcd.db file from the active machine on a communication channel and writing the delta records to the abcd.db on the standby machine. Now my question is , lets say the active machine crashes in the middle of the transaction and we have a hot journal created on the active machine. Does it make sense to copy the hot journal to the standby machine, so that the sqlite application on the standby will notice this hot journal and use it to recover any lost transactions Not only does it make sense - it is mandatory if you want to preserve the integrity of the database file. While a transaction is underway, the journal (either the *-journal file or the *-wal file) is part of the state of the database. If you fail to copy it over to another machine and then try to open that database on the other machine, the database will be incomplete and potentially corrupt. The journal files have a well-defined and documented format. They are independent of the byte-order and word-size of the machine they were created on. -- 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-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] sqlite journal file question
Richard I read that the db on the standby machine is being updated at a record by record level, i.e. not copied in its entirety. In this scenario I can't see the two db files being guaranteed binary compatible. Copying the journal across in this scenario would imo be a mistake. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 13 February 2015 at 02:47, Richard Hipp d...@sqlite.org wrote: On 2/12/15, Mayank Kumar (mayankum) mayan...@cisco.com wrote: Hi All We have two systems which are running in active/standby configuration. The active machine, is actively writing sqlite transactions to a file abcd.db. The standby is syncing the abcd.db file from the active machine on a communication channel and writing the delta records to the abcd.db on the standby machine. Now my question is , lets say the active machine crashes in the middle of the transaction and we have a hot journal created on the active machine. Does it make sense to copy the hot journal to the standby machine, so that the sqlite application on the standby will notice this hot journal and use it to recover any lost transactions Not only does it make sense - it is mandatory if you want to preserve the integrity of the database file. While a transaction is underway, the journal (either the *-journal file or the *-wal file) is part of the state of the database. If you fail to copy it over to another machine and then try to open that database on the other machine, the database will be incomplete and potentially corrupt. The journal files have a well-defined and documented format. They are independent of the byte-order and word-size of the machine they were created on. -- 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite journal file question
Hi All We have two systems which are running in active/standby configuration. The active machine, is actively writing sqlite transactions to a file abcd.db. The standby is syncing the abcd.db file from the active machine on a communication channel and writing the delta records to the abcd.db on the standby machine. Now my question is , lets say the active machine crashes in the middle of the transaction and we have a hot journal created on the active machine. Does it make sense to copy the hot journal to the standby machine, so that the sqlite application on the standby will notice this hot journal and use it to recover any lost transactions or the hot journal can only be used on the same db on which it was created. Remember , both the db on active and standby are same(maintained in sync and have same names), so can a hot journal from one db on active machine be used on standby machine to recover lost transactions. We are assuming that somehow the sqlite application on active is not able to use the journal file to recover from it, so as a fallback would copying it to standby make sense at all so that it can be used by the backup db ? Hope by question doesn't sound too absurd:) -Mayank ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite journal file question
I would say no. The journal file stores pages referenced by page no and when replayed will write those pages back to the main DB at the appropriate physical offset. Although the content of your DB's at a logical level may be the same, it is unlikely that they will be exact copies at a binary level so replaying a journal to the wrong DB would almost certaibnly cause corruption. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 12 February 2015 at 21:09, Mayank Kumar (mayankum) mayan...@cisco.com wrote: Hi All We have two systems which are running in active/standby configuration. The active machine, is actively writing sqlite transactions to a file abcd.db. The standby is syncing the abcd.db file from the active machine on a communication channel and writing the delta records to the abcd.db on the standby machine. Now my question is , lets say the active machine crashes in the middle of the transaction and we have a hot journal created on the active machine. Does it make sense to copy the hot journal to the standby machine, so that the sqlite application on the standby will notice this hot journal and use it to recover any lost transactions or the hot journal can only be used on the same db on which it was created. Remember , both the db on active and standby are same(maintained in sync and have same names), so can a hot journal from one db on active machine be used on standby machine to recover lost transactions. We are assuming that somehow the sqlite application on active is not able to use the journal file to recover from it, so as a fallback would copying it to standby make sense at all so that it can be used by the backup db ? Hope by question doesn't sound too absurd:) -Mayank ___ 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] sqlite journal file question
I don't think it can be done, and if it could be done, it would not be wise. The journal is owned and specific to a connection. a hot Journal for connection A on DB 1 cannot ever be used to roll back or affect in any way connection B on DB 2. However, when you say the standby is syncing, I assume from your description it copies the file all the time? having an exact copy of the DB file and accompanying journal might work on a technical level, but how can you be sure you have the exact same moment in time files? The answer is easy: you can't. Especially not when one of the machines went down or hung up. The best way to achieve your scenario (in my opinion, other people might come up with better best ways) is to either have a second connection to the DB which is updating new records to the second DB (which means the second DB will handle its own hot journals and back ups and the like.) with maybe a once-a-day copy of the full DB after possibly a vacuum or other maintenance routines. This all depends on your workload and implementation specifics. Personally I would have System output any and all SQL that it writes to DB 1 to a file or pipe to the sync service/program which will then apply all the exact same SQL to the second DB, that way they are always in sync and they can't both fail. (Ok they /can/ technically, but the odds of that is negligible). This option also prevents re-copying insane amounts of data which are in both DBs already, all the time. Another option is the backup API but if you don't have a relatively good space of time to make copies of the DB (i.e where there are no updates) then it will not work well. On 2/12/2015 11:09 PM, Mayank Kumar (mayankum) wrote: Hi All We have two systems which are running in active/standby configuration. The active machine, is actively writing sqlite transactions to a file abcd.db. The standby is syncing the abcd.db file from the active machine on a communication channel and writing the delta records to the abcd.db on the standby machine. Now my question is , lets say the active machine crashes in the middle of the transaction and we have a hot journal created on the active machine. Does it make sense to copy the hot journal to the standby machine, so that the sqlite application on the standby will notice this hot journal and use it to recover any lost transactions or the hot journal can only be used on the same db on which it was created. Remember , both the db on active and standby are same(maintained in sync and have same names), so can a hot journal from one db on active machine be used on standby machine to recover lost transactions. We are assuming that somehow the sqlite application on active is not able to use the journal file to recover from it, so as a fallback would copying it to standby make sense at all so that it can be used by the backup db ? Hope by question doesn't sound too absurd:) -Mayank ___ 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] sqlite journal file question
On 2/12/15, Mayank Kumar (mayankum) mayan...@cisco.com wrote: Hi All We have two systems which are running in active/standby configuration. The active machine, is actively writing sqlite transactions to a file abcd.db. The standby is syncing the abcd.db file from the active machine on a communication channel and writing the delta records to the abcd.db on the standby machine. Now my question is , lets say the active machine crashes in the middle of the transaction and we have a hot journal created on the active machine. Does it make sense to copy the hot journal to the standby machine, so that the sqlite application on the standby will notice this hot journal and use it to recover any lost transactions Not only does it make sense - it is mandatory if you want to preserve the integrity of the database file. While a transaction is underway, the journal (either the *-journal file or the *-wal file) is part of the state of the database. If you fail to copy it over to another machine and then try to open that database on the other machine, the database will be incomplete and potentially corrupt. The journal files have a well-defined and documented format. They are independent of the byte-order and word-size of the machine they were created on. -- 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] sqlite shell .import drops last cell if too less columns
Hi,
here comes a bug report with fix proposal.
If a CSV file contains a row with missing column, the previous cell will not be
imported.
Tested version: sqlite-shell-win32-x86-3080802.zip,
sqlite-autoconf-3080802.tar.gz
Steps to reproduce:
- A file named data.csv with following content:
a,b,c
d,THIS_ENTRY_WILL_BE_MISSING
f,g,h
- Open shell and execute following commands:
.open db.sqlite
CREATE TABLE data (col1, col2, col3);
.mode csv
.import data.csv data
SELECT * FROM data;
Bug source:
After a missing column was detected (in shell.c line 2890) the bound text
paramter (line 2889) will be overwritten with NULL in line 2895.
Bug fix:
In file shell.c line 2894 change
i++;
to
i+=2;
Corrected source will lock like:
...
do{
int startLine = sCtx.nLine;
for(i=0; inCol; i++){
char *z = xRead(sCtx);
/*
** Did we reach end-of-file before finding any columns?
** If so, stop instead of NULL filling the remaining columns.
*/
if( z==0 i==0 ) break;
/*
** Did we reach end-of-file OR end-of-line before finding any
** columns in ASCII mode? If so, stop instead of NULL filling
** the remaining columns.
*/
if( p-mode==MODE_Ascii (z==0 || z[0]==0) i==0 ) break;
sqlite3_bind_text(pStmt, i+1, z, -1, SQLITE_TRANSIENT);
if( inCol-1 sCtx.cTerm!=sCtx.cColSep ){
fprintf(stderr, %s:%d: expected %d columns but found %d -
filling the rest with NULL\n,
sCtx.zFile, startLine, nCol, i+1);
i+=2; // BUG FIX HERE
while( i=nCol ){ sqlite3_bind_null(pStmt, i); i++; }
}
}
if( sCtx.cTerm==sCtx.cColSep ){
do{
xRead(sCtx);
i++;
}while( sCtx.cTerm==sCtx.cColSep );
fprintf(stderr, %s:%d: expected %d columns but found %d -
extras ignored\n,
sCtx.zFile, startLine, nCol, i);
}
if( i=nCol ){
sqlite3_step(pStmt);
rc = sqlite3_reset(pStmt);
if( rc!=SQLITE_OK ){
fprintf(stderr, %s:%d: INSERT failed: %s\n, sCtx.zFile, startLine,
sqlite3_errmsg(db));
}
}
}while( sCtx.cTerm!=EOF );
...
Udo
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite Virtual Table Example with non-trivial xBestIndex (was: Porting SQLite to another operating system (not supported out of the box))
Having personally written about a dozen virtual table implementations I can confirm that those implementations needing a nontrivial xBestIndex function are all based on building an SQLite interface on substantial proprietary storage subsystems like an in-memory ISAM table (with configurable record and key structure), a Faircom CTree driver (adding configurable record and key structure), a partition provider (allowing storage to be split between several tables of identical structure by configurable record fields), etc. One of the more challenging tasks involved adding a fastbit based index to a variable record length event logfile for an OLTP application. Simple virtual tables require retrieval by rowid (e.g. memory address or file offset) at best. -Ursprüngliche Nachricht- Von: Jay Kreibich [mailto:j...@kreibi.ch] Gesendet: Mittwoch, 11. Februar 2015 04:04 An: Peter Aronson; General Discussion of SQLite Database Betreff: Re: [sqlite] Porting SQLite to another operating system (not supported out of the box) On Feb 10, 2015, at 11:21 AM, Peter Aronson pbaron...@att.net wrote: You could add VFS creation if you ever do a revised edition (along with a virtual table example that actually used xBestIndex and xFilter…) Given that the book is over four years old and covers to the end of SQLIte3 3.6.x, there are a lot of things that would need to go into a revised edition… including a lot more examples of everything, according to reviews. We greatly underestimated the number of SQLite developers that were touching SQL for the first time, and I would have never guessed people would have considered yet another SQL lesson to be so important, given that there are a million books and a bazillion websites on learning SQL basics. You can literally find books on “SQL For Dummies” (Allen Taylor) to “SQL For Smarties” (Joe Celko), and everything in-between. That last book (or books, actually) is awesome, BTW, and the “Advanced SQL Programming” one should be on the shelf of every database programmer doing anything more advanced than an address book. Regardless, if we do a second edition (and at this point that’s an extremely, exceptionally big “if”), VFS is unlikely to make the cut. Consider that out of the thousands of SQLite applications and billions of installed databases, there are likely less than 100 production VFS modules in the whole world. Spending a lot of time and pages, driving up the cost of the book, covering an extremely advanced and obscure topic is a poor trade-off (every page averages about a day to write/edit/prep, and adds about $0.10 to the price of the book). If you need that level of integration and detail, working in the guts of the I/O and locking system, you should likely hand the task to a systems engineer that is familiar with the problem domain and isn’t afraid of looking through a few headers and examples to figure it all out. It’s advanced, custom stuff that is, almost by definition, not textbook work. It is the kind of work that requires digging through nitty-gritty code, documentation, and examples from both SQLite and your environment. This is the kind of thing that’s learned from years of experience, not by reading it in a book. That isn’t meant to be a criticism of the original poster— there is a huge difference between asking if anyone knows where to start looking, and asking for detailed step-by-step instructions. In fact, if we did decide to put some information about VFS modules in a book, it would likely be a discussion of how the structures and APIs fit together, what they’re used for, and the types of things that can be done with them— exactly the kind of info you need to get started, but not much beyond that. After all, what goes in those functions is going to be extremely dependent on the environment the VFS is trying to use. I might say similar things about the xBestIndex() and xFilter() functions. While the APIs and how they are used is a tad confusing, their purpose and function should be reasonably straight forward to someone comfortable with relational data management and design. While the book attempts to cover how the APIs are meant to perform their tasks (and has a six page discussion on their purpose and use), actually writing such a function is extremely dependent on understanding the virtual table being design— and the data in it. I feel it is something that just needs to be done by a skilled engineer, with a lot of detailed knowledge about the problem that’s trying to be solved. Again, there aren’t any real textbook examples here; yes, I could write a contrived example, but if they didn’t understand from a general description, a single specific example is unlikely to help anyone in their specific case. At the end of the day, both functions are an optimizations anyways. You can write a functional virtual table without them, it might just run a tad slower. If you really need
[sqlite] SQLite Toolbox - free Visual Studio extension
I have recently released this free VS addin, also Works with the free VS 2013 Community edition. Blog post: http://erikej.blogspot.dk/2014/08/sqlite-toolbox-40-visual-guide-of.html Channel 9 video: http://channel9.msdn.com/Shows/Visual-Studio-Toolbox/SQL-Server-Compact-and-SQLite-Toolbox Sendt fra Surface ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite give database or disk full
Hello sqlite Users, I am using sqlite on arm-board and getting a database or disk full error (SQLITE_FULL) while using the update command. 1. DB file location is set to some /opt/dbspace/*.db. ( 32 Gb space and 4 GB RAM) 2. Not used compile time option SQLITE_TEMP_STORE so, deafult value 1. 3. I have used pragma journal_mode=WAL. 4. No pragma temp_store. Please help me with below queries a) where is the temporary files location ?? How is it determined if i dont give any storage settings. b) Do i need to set TMPDIR env variable to give another location for temporary files or temp_store_directory ? c) If i set another location for temporary files, how does the performance gets affected ? Thanks in advance regards, Jitendar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite give database or disk full
jitendar kumar wrote: where is the temporary files location ?? 1. temp_directory, if set 2. SQLITE_TMPDR, if set 3. TMPDIR, if set 4. /var/tmp 5. /usr/tmp 6. /tmp Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.8.8 expected in January
On 2015/01/10 15:50, Richard Hipp wrote: Yes, it was a compile-time omission. I have uploaded a new DLL that includes the loadable extension interface. Thank you - it works perfectly for all entries. All other tests worked well too, so no new problems to report from this side. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Android Bindings: how difficult to add LOCALIZED back?
On 9/01/2015 5:00 PM, Dan Kennedy wrote: On 01/08/2015 07:48 AM, Philip Warner wrote: How difficult would it be to add LOCALIZED collation support? I'm guessing that the fact it's not there means it's non-trivial, but I was hoping otherwise... The stumbling block is that the Android implementations use ICU. So to use the Android versions I think we would have to build ICU as a static library as well as SQLite. And ICU is quite large. Thanks for this; I was naively (again) hoping they might have built the unicode stuff as something that was dynamically linkable. I must admit the benefits of a recent build + ability to build custom functions etc is very appealing. btw, do you have an estimate of how big quite large might be? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Android Bindings: how difficult to add LOCALIZED back?
On 01/11/2015 01:55 PM, Philip Warner wrote: On 9/01/2015 5:00 PM, Dan Kennedy wrote: On 01/08/2015 07:48 AM, Philip Warner wrote: How difficult would it be to add LOCALIZED collation support? I'm guessing that the fact it's not there means it's non-trivial, but I was hoping otherwise... The stumbling block is that the Android implementations use ICU. So to use the Android versions I think we would have to build ICU as a static library as well as SQLite. And ICU is quite large. Thanks for this; I was naively (again) hoping they might have built the unicode stuff as something that was dynamically linkable. I must admit the benefits of a recent build + ability to build custom functions etc is very appealing. btw, do you have an estimate of how big quite large might be? Please review the stuff recently added to AndroWish's Tcl SQLite interface on http://www.androwish.org/index.html/info/84d5ed5ae9c24bada8f8b9f9f198306a1e59300a It tries to use whatever ICU infrastructure is already available on the device by using dynamic linking. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.8.8 expected in January
On 1/9/15, RSmith rsm...@rsweb.co.za wrote: The pre-compiled and supplied DLL (sqlite3.dll) seem to be missing an entry point for sqlite3_enable_load_extension - I do not see any mention in the update text about altering or removing this feature so I am assuming this might be a compile-time omission? Yes, it was a compile-time omission. I have uploaded a new DLL that includes the loadable extension interface. -- 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] SQLite 3.8.8 expected in January
The pre-compiled and supplied DLL (sqlite3.dll) seem to be missing an entry point for sqlite3_enable_load_extension - I do not see any mention in the update text about altering or removing this feature so I am assuming this might be a compile-time omission? On 2015/01/09 19:23, Richard Hipp wrote: We hope to release SQLite version 3.8.8 sometime later this month (January). A change-log is available at https://www.sqlite.org/draft/releaselog/current.html Please stress the code in every way you can between now and then and report any problems to this list, or directly to me. Source code snapshots and precompiled Windows DLLs can be found at https://www.sqlite.org/download.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.8.8 expected in January
On 1/9/15, Dominique Devienne ddevie...@gmail.com wrote: On Fri, Jan 9, 2015 at 6:23 PM, Richard Hipp d...@sqlite.org wrote: We hope to release SQLite version 3.8.8 sometime later this month (January). A change-log is available at https://www.sqlite.org/draft/releaselog/current.html Could you please provide more info about stmt_scanstatus()? Perhaps a concrete example might help understand better what this is used for. Also, I suppose the const char* stats returned need to be freed, perhaps with sqlite3_free()? The doc doesn't say. The returned value is a const char*, so, no, it should not be passed to sqlite3_free(). It is difficult to achieve the right balance of brevity and explanation. I have your request for additional explanation, but I need to weigh that against those who prefer brevity. I work on the documentation some more. Perhaps I can find ways of achieving both goals at once. Thank you for the feedback. -- 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] sqlite data source not available
I wish to add a confirmation to this. SQLite installation for 1.0.94.0 did not add the option for System.Data.Sqlite Database File to Choose Data Source dialog when adding ADO.NET Entity Data Model to my application. Using: ~ Visual Studio 2010 ~ VB.NET WinForms Application @ .NET 4.0 ~ Windows 7, x64 Version 1.0.93.0 installed (via download, not NuGet) *did* make this possible. In my case, it was not necessary to modify the app.config document. Version 1.0.93.0 can be downloaded here: http://system.data.sqlite.org/downloads/1.0.93.0/sqlite-netFx40-setup-bundle-x86-2010-1.0.93.0.exe ~ and here ~ http://system.data.sqlite.org/downloads/1.0.93.0/sqlite-netFx40-setup-bundle-x64-2010-1.0.93.0.exe Thanks Joe and Kevin, I missed changing the folder version #. After doing so was able to download .93 and it works! -- View this message in context: http://sqlite.1065341.n5.nabble.com/sqlite-data-source-not-available-tp78521p79925.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
Re: [sqlite] SQLite 3.8.8 expected in January
On Fri, Jan 9, 2015 at 6:23 PM, Richard Hipp d...@sqlite.org wrote: We hope to release SQLite version 3.8.8 sometime later this month (January). A change-log is available at https://www.sqlite.org/draft/releaselog/current.html Could you please provide more info about stmt_scanstatus()? Perhaps a concrete example might help understand better what this is used for. Also, I suppose the const char* stats returned need to be freed, perhaps with sqlite3_free()? The doc doesn't say. Thanks, --DD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite 3.8.8 expected in January
We hope to release SQLite version 3.8.8 sometime later this month (January). A change-log is available at https://www.sqlite.org/draft/releaselog/current.html Please stress the code in every way you can between now and then and report any problems to this list, or directly to me. Source code snapshots and precompiled Windows DLLs can be found at https://www.sqlite.org/download.html -- 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] SQLite Android Bindings: how difficult to add LOCALIZED back?
On 01/08/2015 07:48 AM, Philip Warner wrote: I just saw the SQLite Android Bindings page at http://www.sqlite.org/android/doc/trunk/www/index.wiki but was a little disappointed to read in the details that UNICODE and LOCALIZED are not supported. I'd really like the latest SQLite, and LOCALIZED. How difficult would it be to add LOCALIZED collation support? I'm guessing that the fact it's not there means it's non-trivial, but I was hoping otherwise... The stumbling block is that the Android implementations use ICU. So to use the Android versions I think we would have to build ICU as a static library as well as SQLite. And ICU is quite large. The implementations are in the file sqlite3_android.cpp (part of the Android source tree - should be possible to google it). They look quite self-contained, so if you were willing to build ICU as part of your app and hack around with the code you could probably get them to work without too much trouble. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite problem possible bug
On 1/7/15, The Responsa Project gr.respo...@biu.ac.il wrote:
To Whom it amy concern
I am trying to use SQLITE and the like statement with wildcards and hebrew
when I put in an english string it works correctly, such as
Select * from dbname where colname like '%123%'
I will get all the entries from that column that contain 123 anywhere in the
column.
However if I substitute 123 with hebrew letters - it matches all the
entries, not just the ones containing what I asked for.
If I do not use the wilcards in the like it matches the exact word properly.
In version 3.2.2 of sqlite this worked fine (with wildcards), later versions
it does not. So for example SELECT * from dbname where colname like '%אב%'
will give me all the entries not only the ones matching only אב.
It should work. Here is the test case I used:
CREATE TABLE t1(x TEXT);
INSERT INTO t1(x) VALUES('abc'),('אב'),
('בְּרֵאשִׁ֖ית בָּרָ֣א אֱלֹהִ֑ים אֵ֥ת הַשָּׁמַ֖יִם וְאֵ֥ת הָאָֽרֶץ'),
('וְהָאָ֗רֶץ הָיְתָ֥ה תֹ֙הוּ֙ וָבֹ֔הוּ '),('xyz');
.print --- all ---
SELECT rowid, x FROM t1;
.print --- Using %אב% ---
SELECT rowid, x FROM t1 WHERE x LIKE '%אב%';
The above gives me this output:
--- all ---
1|abc
2|אב
3|בְּרֵאשִׁ֖ית בָּרָ֣א אֱלֹהִ֑ים אֵ֥ת הַשָּׁמַ֖יִם וְאֵ֥ת הָאָֽרֶץ
4|וְהָאָ֗רֶץ הָיְתָ֥ה תֹ֙הוּ֙ וָבֹ֔הוּ
5|xyz
--- Using %אב% ---
2|אב
Which is exactly what you would expect, no?
Perhaps you can give us more details about how you are invoking
SQLite. The problem might be in the interface to your programming
language, not in SQLite itself.
--
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] Sqlite problem possible bug
To Whom it amy concern I am trying to use SQLITE and the like statement with wildcards and hebrew when I put in an english string it works correctly, such as Select * from dbname where colname like '%123%' I will get all the entries from that column that contain 123 anywhere in the column. However if I substitute 123 with hebrew letters - it matches all the entries, not just the ones containing what I asked for. If I do not use the wilcards in the like it matches the exact word properly. In version 3.2.2 of sqlite this worked fine (with wildcards), later versions it does not. So for example SELECT * from dbname where colname like '%אב%' will give me all the entries not only the ones matching only אב. I tried GLOB, which also did not work.? I would like to (and need to) upgrade to the latest version of Sqlite but I cannot because of this issue. Is this a bug? Am I doing something wrong? All help is appreciated, thanks in advance Sincerely,Sharon Gottlieb The Responsa Project Bar-Ilan University Ramat-Gan 52900, ISRAEL Tel: 972-3-5318-411 / Fax: 972-3-5341-850 Email: respo...@mail.biu.ac.il Internet http://responsa.biu.ac.il ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite Android Bindings: how difficult to add LOCALIZED back?
I just saw the SQLite Android Bindings page at http://www.sqlite.org/android/doc/trunk/www/index.wiki but was a little disappointed to read in the details that UNICODE and LOCALIZED are not supported. I'd really like the latest SQLite, and LOCALIZED. How difficult would it be to add LOCALIZED collation support? I'm guessing that the fact it's not there means it's non-trivial, but I was hoping otherwise... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite problem possible bug
On 2015/01/07 12:13, The Responsa Project wrote: To Whom it amy concern I am trying to use SQLITE and the like statement with wildcards and hebrew when I put in an english string it works correctly, such as Select * from dbname where colname like '%123%' I will get all the entries from that column that contain 123 anywhere in the column. However if I substitute 123 with hebrew letters - it matches all the entries, not just the ones containing what I asked for. If I do not use the wilcards in the like it matches the exact word properly. In version 3.2.2 of sqlite this worked fine (with wildcards), later versions it does not. So for example SELECT * from dbname where colname like '%אב%' will give me all the entries not only the ones matching only אב. I tried GLOB, which also did not work.? I would like to (and need to) upgrade to the latest version of Sqlite but I cannot because of this issue. Is this a bug? Am I doing something wrong? Not a bug in the latest version - works fine for me, but I am not sure which other versions you have tested. Are you using the C api directly or going through some wrapper? (It might mess with the UTF8 or whatever encoding you start off with). Is your DB in UTF-8 mode? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite problem possible bug
How about to use dynamic binding? For example, is your SQL(SELECT * from dbname where colname like '%אב%'), use '?' instead of 'אב'. In my guess, 'אב' can have same ASCII code of wildcard(%). Full SQL can be as like as follows. SELECT * from dbname where colname like '%?%' To do this, you need to use sqlite3_bind*** functions in c API. Regards YONGIL. 2015. 1. 7. 오후 9:34에 The Responsa Project gr.respo...@biu.ac.il님이 작성: To Whom it amy concern I am trying to use SQLITE and the like statement with wildcards and hebrew when I put in an english string it works correctly, such as Select * from dbname where colname like '%123%' I will get all the entries from that column that contain 123 anywhere in the column. However if I substitute 123 with hebrew letters - it matches all the entries, not just the ones containing what I asked for. If I do not use the wilcards in the like it matches the exact word properly. In version 3.2.2 of sqlite this worked fine (with wildcards), later versions it does not. So for example SELECT * from dbname where colname like '%אב%' will give me all the entries not only the ones matching only אב. I tried GLOB, which also did not work.? I would like to (and need to) upgrade to the latest version of Sqlite but I cannot because of this issue. Is this a bug? Am I doing something wrong? All help is appreciated, thanks in advance Sincerely,Sharon Gottlieb The Responsa Project Bar-Ilan University Ramat-Gan 52900, ISRAEL Tel: 972-3-5318-411 / Fax: 972-3-5341-850 Email: respo...@mail.biu.ac.il Internet http://responsa.biu.ac.il ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite login password
I want to know how to setting a loging sqlite passwd? When I input: Sqlite mtdb Command, the console will prompt like this: Pls input your password: ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite login password
On 4 Jan 2015, at 12:56am, YAN HONG YE yanhong...@mpsa.com wrote: I want to know how to setting a loging sqlite passwd? When I input: Sqlite mtdb Command, the console will prompt like this: Pls input your password: http://www.sqlite.org/src/doc/trunk/ext/userauth/user-auth.txt It is only available in recent versions of SQLite. Since 2014-10-17. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite login password
On Sat, Jan 3, 2015 at 7:59 PM, Simon Slavin slav...@bigfraud.org wrote: On 4 Jan 2015, at 12:56am, YAN HONG YE yanhong...@mpsa.com wrote: I want to know how to setting a loging sqlite passwd? When I input: Sqlite mtdb Command, the console will prompt like this: Pls input your password: http://www.sqlite.org/src/doc/trunk/ext/userauth/user-auth.txt It is only available in recent versions of SQLite. Since 2014-10-17. (1) The OP says he used the sqlite command (version 2 of SQLite) not sqlite3. (2) SQLite has *never* given a prompt Pls input your password. That message is coming from third-party software. Simon. ___ 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] sqlite login password
On 4 Jan 2015, at 1:00am, Richard Hipp d...@sqlite.org wrote: (1) The OP says he used the sqlite command (version 2 of SQLite) not sqlite3. (2) SQLite has *never* given a prompt Pls input your password. That message is coming from third-party software. I agree that my answer has nothing to do with the situation he described. But it does answer the question he asked. Our problem is that the situation he described could not have taken place. Unless a third party wrote a utility they called 'Sqlite' which would, of course, be bad. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite login password
Maybe the question is How do I make SQLite do this when accessing a database? and the 3 just got dropped inadvertently. If that is the case, as Dr. Hipp said, SQLite has never done it, and I'll add on that it has never done it stock. On the other hand, that link you posted, Dr. Hipp, is rather interesting. Albeit limited functionality (I'd prefer a permissions type system for each user instead of R/O access or full access) as the philosophy of SQLite is to keep it slim, this basic authentication system might be what the OP is running into. On Sat, Jan 3, 2015 at 8:07 PM, Simon Slavin slav...@bigfraud.org wrote: On 4 Jan 2015, at 1:00am, Richard Hipp d...@sqlite.org wrote: (1) The OP says he used the sqlite command (version 2 of SQLite) not sqlite3. (2) SQLite has *never* given a prompt Pls input your password. That message is coming from third-party software. I agree that my answer has nothing to do with the situation he described. But it does answer the question he asked. Our problem is that the situation he described could not have taken place. Unless a third party wrote a utility they called 'Sqlite' which would, of course, be bad. 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
[sqlite] SQLITE wal file size keeps growing
I am writing continuously into a db file which has PRAGMA journal_mode=WAL, PRAGMA journal_size_limit=0. My C++ program has two threads, one reader(queries at 15 sec intervals) and one writer(inserts at 5 sec intervals). Every 3 min I am pausing insertion to run a sqlite3_wal_checkpoint_v2() from the writer thread with the mode parameter as SQLITE_CHECKPOINT_RESTART. To ensure that no active read operations are going on at this point, I set a flag that checkpointing is about to take place and wait for reader to complete (the connection is still open) before running checkpoint. After checkpoint completion I again indicate to readers it is okay to resume querying. sqlite3_wal_checkpoint_v2() returns SQLITE_OK, and pnLog and Ckpt as equal(around 4000), indicating complete wal file has been synced with main db file. So next write should start from beginning according to documentation. However, this does not seem to be happening as the subsequent writes cause the WAL file to grow indefinitely, eventually up to some GBs. I did some searching and found that that readers can cause checkpoint failure due to open transactions. However, the only reader I'm using is ending its transaction before the checkpoint starts. What else could be preventing the WAL file from not growing? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE wal file size keeps growing
Readers do not need long-lasting transactions (if any at all), so I'd rather suspect your writer to be the culprit. Does it use lasting transactions? If so, make it commit the transaction before checkpointing. regards gerd ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE wal file size keeps growing
On 12/19/2014 11:22 AM, Kushagradhi Bhowmik wrote: I am writing continuously into a db file which has PRAGMA journal_mode=WAL, PRAGMA journal_size_limit=0. My C++ program has two threads, one reader(queries at 15 sec intervals) and one writer(inserts at 5 sec intervals). Every 3 min I am pausing insertion to run a sqlite3_wal_checkpoint_v2() from the writer thread with the mode parameter as SQLITE_CHECKPOINT_RESTART. To ensure that no active read operations are going on at this point, I set a flag that checkpointing is about to take place and wait for reader to complete (the connection is still open) before running checkpoint. After checkpoint completion I again indicate to readers it is okay to resume querying. It shouldn't hurt, but you should not have to manage the readers that way. SQLITE_CHECKPOINT_RESTART should wait on readers as required to ensure that the next writer can write into the start of the wal file instead of appending. If SQLITE_CHECKPOINT_RESTART returns SQLITE_OK, the next writer should be able to restart the wal file. If you register an sqlite3_wal_hook() callback it will be invoked to report the size of the wal file after each write transaction. Logging this information along with the checkpoint attempts and return codes might help to shed light on the problem. Dan. sqlite3_wal_checkpoint_v2() returns SQLITE_OK, and pnLog and Ckpt as equal(around 4000), indicating complete wal file has been synced with main db file. So next write should start from beginning according to documentation. However, this does not seem to be happening as the subsequent writes cause the WAL file to grow indefinitely, eventually up to some GBs. I did some searching and found that that readers can cause checkpoint failure due to open transactions. However, the only reader I'm using is ending its transaction before the checkpoint starts. What else could be preventing the WAL file from not growing? ___ 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] sqlite bugreport : unique index causes valid updates to fail
On Mon, 08 Dec 2014 22:01:15 +0700 Dan Kennedy danielk1...@gmail.com wrote: On 12/08/2014 09:55 PM, Nico Williams wrote: Ideally there would be something like DEFERRED foreign key checking for uniqueness constraints... You could hack SQLite to do enforce unique constraints the same way as FKs. When adding an entry to a UNIQUE index b-tree, you check for a duplicate. If one exists, increment a counter. Do the opposite when removing entries - decrement the counter if there are two or more duplicates of the entry you are removing. If your counter is greater than zero at commit time, a UNIQUE constraint has failed. It's not *deferred* constraint checking. It's constraint checking. Best to honor the transaction first. Rather than adding to the syntax, perhaps a pragma could cause updates to happen in a transaction: 1. Create a temporary table to hold the after-image of the updated rows. 2. begin transaction 3 . Delete the rows from the target table. 3. Insert the updated rows from the temporary table. 4. commit 5. drop temporary table. Of course there are more efficient answers available deeper in the update logic, affecting only the partcular columns at the time the constraint is enforced. I guess they all involve deleting the prior set from the index and inserting the new one. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
On Mon, 08 Dec 2014 15:48:41 +0200 RSmith rsm...@rsweb.co.za wrote: UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position = 1; NOT a bug... the moment you SET position to position +1 for the first iteration of the query, it tries to make that entry look like (0,2) and there is of course at this point in time already an entry like (0,2). Yes, that's how SQLite works, or doesn't. Whether or not it's a bug depends on how you define the term. The issue has come up here before: contrary to the SQL standard, SQLite does not support constraint enforcement with transaction semantics. I've never heard of another SQL DBMS that behaves that way. sqlite create table T (t int primary key); sqlite insert into T values (1), (2); sqlite update T set t = t+1; Error: column t is not unique As the OP discovered, the one recourse is to relieve the constraint during the update. Another is to update a temporary table, and then delete insert the rows in a transaction. I would say must implement one's own transaction semantics is, if not a bug, at least a misfeature. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
I just thought of what may be a simpler solution.
I'm assuming that there is a certain limit to the length of the books
(positions can be safely assumed to never exceed say, 100,000)
So what can be done is
update page set position=position + 10 where position='3';
insert into page(book,position) values('1','3');
update page set position=position - 9 where position10;
This will work around the unique contraint and seems simpler than dropping it
everytime you want ot insert a page.
-Original Message-
From: James K. Lowden jklow...@schemamania.org
To: sqlite-users sqlite-users@sqlite.org
Sent: Tue, Dec 9, 2014 10:38 am
Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to
fail
On Mon, 08 Dec 2014 22:01:15 +0700
Dan Kennedy danielk1...@gmail.com wrote:
On 12/08/2014 09:55 PM, Nico Williams wrote:
Ideally there would be something like DEFERRED foreign key checking
for uniqueness constraints...
You could hack SQLite to do enforce unique constraints the same way
as FKs. When adding an entry to a UNIQUE index b-tree, you check for
a duplicate. If one exists, increment a counter. Do the opposite when
removing entries - decrement the counter if there are two or more
duplicates of the entry you are removing. If your counter is greater
than zero at commit time, a UNIQUE constraint has failed.
It's not *deferred* constraint checking. It's constraint checking.
Best to honor the transaction first.
Rather than adding to the syntax, perhaps a pragma could cause updates
to happen in a transaction:
1. Create a temporary table to hold the after-image of the updated
rows.
2. begin transaction
3 . Delete the rows from the target table.
3. Insert the updated rows from the temporary table.
4. commit
5. drop temporary table.
Of course there are more efficient answers available deeper in the
update logic, affecting only the partcular columns at the time the
constraint is enforced. I guess they all involve deleting the
prior set from the index and inserting the new one.
--jkl
___
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] sqlite bugreport : unique index causes valid updates to fail
I just thought of what may be a simpler solution.
I'm assuming that there is a certain limit to the length of the books
(positions can be safely assumed to never exceed say, 100,000)
So what can be done is
update page set position=position + 10 where position='3';
insert into page(book,position) values('1','3');
update page set position=position - 9 where position10;
This will work around the unique contraint and seems simpler than dropping it
everytime you want to insert a page.
-Original Message-
From: Gwendal Roué g...@pierlis.com
To: General Discussion of SQLite Database sqlite-users@sqlite.org
Sent: Mon, Dec 8, 2014 12:07 pm
Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to
fail
Le 8 déc. 2014 à 17:21, Simon Slavin slav...@bigfraud.org a écrit :
Why not an opt-in way to ask for deferred constraint checking. The key here
is only to allow perfectly legit requests to run. With all the due respect to
sqlite implementors and the wonderful design of sqlite.
SQL-99 includes a syntax for deferred checking. We don't need to invent our
own syntax with a PRAGMA. However, it is done when the constraint is defined
rather than being something one can turn on or off. So you would need to think
out whether you wanted row- or transaction-based checking when you define each
constraint in the first place.
Hi Simon,
This topic is fascinating. Googling for SQL-99 deferred checking, I stumbled
upon this page which shows how deferred index maintenance affects Oracle query
plan, and performance :
https://alexanderanokhin.wordpress.com/deferred-index-maintenance/.
I now understand that the strategy for checking index constraints is tied to
their maintenance.
The `UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position =
1` query we are talking about has indeed to perform both. Such an
innocuous-looking request, and it sends us right into the very guts of
relational constraints :-)
Gwendal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [SQLite]Basic query
Hi, I'm about to use sqlite-amalgamation(sqlite3.c) on Visual C++. But although the compiling was successfully finished, even if I set break point on the source code, I can't trace the working line in sqlite3.c correctly. I suspected that the sqlite3.c was optimized, but there is no opiton like that. Always the active line doesn't match to source code in debug mode. Is the code optimized automatically? if so, how can I deactivate it? If anyone knows above phenomenon, then could you please answer me?? Thanks, ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite]Basic query
Shinichiro Yoshioka wrote: I'm about to use sqlite-amalgamation(sqlite3.c) on Visual C++. But although the compiling was successfully finished, even if I set break point on the source code, I can't trace the working line in sqlite3.c correctly. How exactly are you using SQLite in your program? If you are not using the SQLite C API (sqlite3_* functions) directly, it is likely that there is a second copy of the SQLite library inside that other database driver. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite]Basic query
Hi Shinichiro, which Version of VC++ do you use? As far as I know, older versions do not support debugging source files with more than 65535 lines. Also, why do you want to debug into the sqlite.c file? The file sqlite.c is just another source file for your compiler; optimizations would occcur only if you set them in your compiler options. HTH Martin Am 08.12.2014 10:10, schrieb Shinichiro Yoshioka: Hi, I'm about to use sqlite-amalgamation(sqlite3.c) on Visual C++. But although the compiling was successfully finished, even if I set break point on the source code, I can't trace the working line in sqlite3.c correctly. I suspected that the sqlite3.c was optimized, but there is no opiton like that. Always the active line doesn't match to source code in debug mode. Is the code optimized automatically? if so, how can I deactivate it? If anyone knows above phenomenon, then could you please answer me?? Thanks, ___ 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] [SQLite]Basic query
Hi, Clemens and Martin, Thank you for your prompt responses. How exactly are you using SQLite in your program? If you are not using the SQLite C API (sqlite3_* functions) directly, I'm using sqlite APIs for calling from C source code directly. which Version of VC++ do you use? I'm using Visual studio express 2010. why do you want to debug into the sqlite.c file? Since opening database API is failed, I'm trying to figure out the cause. The file sqlite.c is just another source file for your compiler; optimizations would occcur only if you set them in your compiler options. Yeah.. I completely agree with you, but I can't find such setting in VC++ property, So I'm asking this query... Thanks, 2014-12-08 18:14 GMT+09:00 Clemens Ladisch clem...@ladisch.de: Shinichiro Yoshioka wrote: I'm about to use sqlite-amalgamation(sqlite3.c) on Visual C++. But although the compiling was successfully finished, even if I set break point on the source code, I can't trace the working line in sqlite3.c correctly. How exactly are you using SQLite in your program? If you are not using the SQLite C API (sqlite3_* functions) directly, it is likely that there is a second copy of the SQLite library inside that other database driver. Regards, Clemens ___ 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] [SQLite]Basic query
Hi Shinichiro, If opening the database failed with sqlite3_open() != SQLITE_OK, it is probably best to check the return code and error message using sqlite3_errmsg(). It will give a strong hint. Debugging into the sqlite3 code itself never worked for me. Make sure that - the directory the database file redsides in exists - the the user executing the program has the permission to read/write in the directory and the database file This is not really the answer to your question, but you can probably solve your problem without debugging into sqlite. Martin. Am 08.12.2014 10:55, schrieb Shinichiro Yoshioka: Hi, Clemens and Martin, Thank you for your prompt responses. How exactly are you using SQLite in your program? If you are not using the SQLite C API (sqlite3_* functions) directly, I'm using sqlite APIs for calling from C source code directly. which Version of VC++ do you use? I'm using Visual studio express 2010. why do you want to debug into the sqlite.c file? Since opening database API is failed, I'm trying to figure out the cause. The file sqlite.c is just another source file for your compiler; optimizations would occcur only if you set them in your compiler options. Yeah.. I completely agree with you, but I can't find such setting in VC++ property, So I'm asking this query... Thanks, 2014-12-08 18:14 GMT+09:00 Clemens Ladisch clem...@ladisch.de: Shinichiro Yoshioka wrote: I'm about to use sqlite-amalgamation(sqlite3.c) on Visual C++. But although the compiling was successfully finished, even if I set break point on the source code, I can't trace the working line in sqlite3.c correctly. How exactly are you using SQLite in your program? If you are not using the SQLite C API (sqlite3_* functions) directly, it is likely that there is a second copy of the SQLite library inside that other database driver. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite bugreport : unique index causes valid updates to fail
Hi, Unique indexes make some valid update queries fail. Please find below the SQL queries that lead to the unexpected error: -- The `books` and `pages` tables implement a book with several pages. -- Page ordering is implemented via the `position` column in the pages table. -- A unique index makes sure two pages do not share the same position. CREATE TABLE books ( id INT PRIMARY KEY ) CREATE TABLE pages ( book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON UPDATE CASCADE, position INT ) CREATE UNIQUE INDEX pagination ON pages(book_id, position) -- Let's populate the tables with a single book and three pages: INSERT INTO books VALUES (0); INSERT INTO pages VALUES (0,0); INSERT INTO pages VALUES (0,1); INSERT INTO pages VALUES (0,2); -- We want to insert a page between the pages at positions 0 and 1. So we have -- to increment the positions of all pages after page 1. -- Unfortunately, this query yields an error: columns book_id, position are not unique/ UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position = 1; The query should run without any error, since it does not break the unique index. Thank you for considering this issue. Cheers, Gwendal Roué ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué g...@pierlis.com wrote: Hi, Unique indexes make some valid update queries fail. Please find below the SQL queries that lead to the unexpected error: -- The `books` and `pages` tables implement a book with several pages. -- Page ordering is implemented via the `position` column in the pages table. -- A unique index makes sure two pages do not share the same position. CREATE TABLE books ( id INT PRIMARY KEY ) CREATE TABLE pages ( book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON UPDATE CASCADE, position INT ) CREATE UNIQUE INDEX pagination ON pages(book_id, position) -- Let's populate the tables with a single book and three pages: INSERT INTO books VALUES (0); INSERT INTO pages VALUES (0,0); INSERT INTO pages VALUES (0,1); INSERT INTO pages VALUES (0,2); -- We want to insert a page between the pages at positions 0 and 1. So we have -- to increment the positions of all pages after page 1. -- Unfortunately, this query yields an error: columns book_id, position are not unique/ UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position = 1; The query should run without any error, since it does not break the unique index. Uniqueness is checked for each row change, not just at the end of the transaction. Hence, uniqueness might fail, depending on the order in which the individual rows are updated. Thank you for considering this issue. Cheers, Gwendal Roué ___ 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] sqlite bugreport : unique index causes valid updates to fail
Try having your cascade occur before the row is created, updated or deleted. http://www.sqlite.org/lang_createtrigger.html -Original Message- From: Richard Hipp d...@sqlite.org To: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Mon, Dec 8, 2014 8:14 am Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué g...@pierlis.com wrote: Hi, Unique indexes make some valid update queries fail. Please find below the SQL queries that lead to the unexpected error: -- The `books` and `pages` tables implement a book with several pages. -- Page ordering is implemented via the `position` column in the pages table. -- A unique index makes sure two pages do not share the same position. CREATE TABLE books ( id INT PRIMARY KEY ) CREATE TABLE pages ( book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON UPDATE CASCADE, position INT ) CREATE UNIQUE INDEX pagination ON pages(book_id, position) -- Let's populate the tables with a single book and three pages: INSERT INTO books VALUES (0); INSERT INTO pages VALUES (0,0); INSERT INTO pages VALUES (0,1); INSERT INTO pages VALUES (0,2); -- We want to insert a page between the pages at positions 0 and 1. So we have -- to increment the positions of all pages after page 1. -- Unfortunately, this query yields an error: columns book_id, position are not unique/ UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position = 1; The query should run without any error, since it does not break the unique index. Uniqueness is checked for each row change, not just at the end of the transaction. Hence, uniqueness might fail, depending on the order in which the individual rows are updated. Thank you for considering this issue. Cheers, Gwendal Roué ___ 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
Le 8 déc. 2014 à 14:14, Richard Hipp d...@sqlite.org a écrit : On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué g...@pierlis.com wrote: Hi, Unique indexes make some valid update queries fail. Please find below the SQL queries that lead to the unexpected error: -- The `books` and `pages` tables implement a book with several pages. -- Page ordering is implemented via the `position` column in the pages table. -- A unique index makes sure two pages do not share the same position. CREATE TABLE books ( id INT PRIMARY KEY ) CREATE TABLE pages ( book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON UPDATE CASCADE, position INT ) CREATE UNIQUE INDEX pagination ON pages(book_id, position) -- Let's populate the tables with a single book and three pages: INSERT INTO books VALUES (0); INSERT INTO pages VALUES (0,0); INSERT INTO pages VALUES (0,1); INSERT INTO pages VALUES (0,2); -- We want to insert a page between the pages at positions 0 and 1. So we have -- to increment the positions of all pages after page 1. -- Unfortunately, this query yields an error: columns book_id, position are not unique/ UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position = 1; The query should run without any error, since it does not break the unique index. Uniqueness is checked for each row change, not just at the end of the transaction. Hence, uniqueness might fail, depending on the order in which the individual rows are updated. Thank you Richard for your answer. We share the same conclusion. I even tried to decorate the update query with ORDER clauses, in a foolish attempt to reverse the ordering of row updates, and circumvent the issue. Our analysis describes an implementation detail. Still, this behavior can not be considered as normal, and closed as behaves as expected. I still believe that my initial mail is an actual bug report and should be treated as such. I hope it will find an interested ear. I'm unfortunately not familiar enough with the sqlite guts to fix it myself - especially considering the root cause. Messing with relational constraints validation is not an easy task. Regards, Gwendal Roué ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
Thanks J T. Let's give a look. Le 8 déc. 2014 à 14:24, J T drenho...@aol.com a écrit : Try having your cascade occur before the row is created, updated or deleted. http://www.sqlite.org/lang_createtrigger.html -Original Message- From: Richard Hipp d...@sqlite.org To: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Mon, Dec 8, 2014 8:14 am Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué g...@pierlis.com wrote: Hi, Unique indexes make some valid update queries fail. Please find below the SQL queries that lead to the unexpected error: -- The `books` and `pages` tables implement a book with several pages. -- Page ordering is implemented via the `position` column in the pages table. -- A unique index makes sure two pages do not share the same position. CREATE TABLE books ( id INT PRIMARY KEY ) CREATE TABLE pages ( book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON UPDATE CASCADE, position INT ) CREATE UNIQUE INDEX pagination ON pages(book_id, position) -- Let's populate the tables with a single book and three pages: INSERT INTO books VALUES (0); INSERT INTO pages VALUES (0,0); INSERT INTO pages VALUES (0,1); INSERT INTO pages VALUES (0,2); -- We want to insert a page between the pages at positions 0 and 1. So we have -- to increment the positions of all pages after page 1. -- Unfortunately, this query yields an error: columns book_id, position are not unique/ UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position = 1; The query should run without any error, since it does not break the unique index. Uniqueness is checked for each row change, not just at the end of the transaction. Hence, uniqueness might fail, depending on the order in which the individual rows are updated. Thank you for considering this issue. Cheers, Gwendal Roué ___ 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-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] sqlite bugreport : unique index causes valid updates to fail
On 8 Dec 2014, at 1:31pm, Gwendal Roué g...@pierlis.com wrote: We share the same conclusion. I even tried to decorate the update query with ORDER clauses, in a foolish attempt to reverse the ordering of row updates, and circumvent the issue. A way to solve this is to use REAL for page numbers instead of INTEGER. To insert a page between two existing ones, give it a number which is the mean of the two pages you're inserting it between. Every so often you can run a maintenance routine which renumbers all pages to integers. Alternatively, store your pages as a linked list. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
J T, I did provide a sequence of queries that reliably reproduce the issue (see below, from the first CREATE to the last UPDATE). There is no trigger involved, as far as I know. Forgive me but I don't see how I could use your advice. My work around has been to destroy the unique index, and then re-create it after the update. This solution is good enough as my table is not that big, and the pure code path remains intact, with only two inserted statements that are easily described and commented. Gwendal Roué Le 8 déc. 2014 à 14:24, J T drenho...@aol.com a écrit : Try having your cascade occur before the row is created, updated or deleted. http://www.sqlite.org/lang_createtrigger.html -Original Message- From: Richard Hipp d...@sqlite.org To: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Mon, Dec 8, 2014 8:14 am Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué g...@pierlis.com wrote: Hi, Unique indexes make some valid update queries fail. Please find below the SQL queries that lead to the unexpected error: -- The `books` and `pages` tables implement a book with several pages. -- Page ordering is implemented via the `position` column in the pages table. -- A unique index makes sure two pages do not share the same position. CREATE TABLE books ( id INT PRIMARY KEY ) CREATE TABLE pages ( book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON UPDATE CASCADE, position INT ) CREATE UNIQUE INDEX pagination ON pages(book_id, position) -- Let's populate the tables with a single book and three pages: INSERT INTO books VALUES (0); INSERT INTO pages VALUES (0,0); INSERT INTO pages VALUES (0,1); INSERT INTO pages VALUES (0,2); -- We want to insert a page between the pages at positions 0 and 1. So we have -- to increment the positions of all pages after page 1. -- Unfortunately, this query yields an error: columns book_id, position are not unique/ UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position = 1; The query should run without any error, since it does not break the unique index. Uniqueness is checked for each row change, not just at the end of the transaction. Hence, uniqueness might fail, depending on the order in which the individual rows are updated. Thank you for considering this issue. Cheers, Gwendal Roué ___ 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-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] sqlite bugreport : unique index causes valid updates to fail
Le 8 déc. 2014 à 14:39, Simon Slavin slav...@bigfraud.org a écrit : On 8 Dec 2014, at 1:31pm, Gwendal Roué g...@pierlis.com wrote: We share the same conclusion. I even tried to decorate the update query with ORDER clauses, in a foolish attempt to reverse the ordering of row updates, and circumvent the issue. A way to solve this is to use REAL for page numbers instead of INTEGER. To insert a page between two existing ones, give it a number which is the mean of the two pages you're inserting it between. Every so often you can run a maintenance routine which renumbers all pages to integers. Alternatively, store your pages as a linked list. Polluting my database schema around such a bug is not an option for me, as long as I can find a work around that is good enough and leaves my intent intact. The one I chose involves destroying the unique index before running the failing update query, and then recreating it. All I look for is this issue to enter the ticket list of sqlite at http://www.sqlite.org/src/reportlist, so that this fantastic embeddable database gets better. Gwendal Roué ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
Sorry, wasn't focused on what I was looking at. Though, you said you already tried the order by without success which would have been my next suggestion or clarification of my first. As, you should be able to update the rows from the end down to the page that would be after your insertion (update pages set position=position + 1 where book=0 order by position desc.) and then inserting the new page at the desired position. But if that's not working, I have to agree with your opinion of it being a bug. -Original Message- From: Gwendal Roué g...@pierlis.com To: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Mon, Dec 8, 2014 8:40 am Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail J T, I did provide a sequence of queries that reliably reproduce the issue (see below, from the first CREATE to the last UPDATE). There is no trigger involved, as far as I know. Forgive me but I don't see how I could use your advice. My work around has been to destroy the unique index, and then re-create it after the update. This solution is good enough as my table is not that big, and the pure code path remains intact, with only two inserted statements that are easily described and commented. Gwendal Roué Le 8 déc. 2014 à 14:24, J T drenho...@aol.com a écrit : Try having your cascade occur before the row is created, updated or deleted. http://www.sqlite.org/lang_createtrigger.html -Original Message- From: Richard Hipp d...@sqlite.org To: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Mon, Dec 8, 2014 8:14 am Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué g...@pierlis.com wrote: Hi, Unique indexes make some valid update queries fail. Please find below the SQL queries that lead to the unexpected error: -- The `books` and `pages` tables implement a book with several pages. -- Page ordering is implemented via the `position` column in the pages table. -- A unique index makes sure two pages do not share the same position. CREATE TABLE books ( id INT PRIMARY KEY ) CREATE TABLE pages ( book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON UPDATE CASCADE, position INT ) CREATE UNIQUE INDEX pagination ON pages(book_id, position) -- Let's populate the tables with a single book and three pages: INSERT INTO books VALUES (0); INSERT INTO pages VALUES (0,0); INSERT INTO pages VALUES (0,1); INSERT INTO pages VALUES (0,2); -- We want to insert a page between the pages at positions 0 and 1. So we have -- to increment the positions of all pages after page 1. -- Unfortunately, this query yields an error: columns book_id, position are not unique/ UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position = 1; The query should run without any error, since it does not break the unique index. Uniqueness is checked for each row change, not just at the end of the transaction. Hence, uniqueness might fail, depending on the order in which the individual rows are updated. Thank you for considering this issue. Cheers, Gwendal Roué ___ 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
On 2014/12/08 11:55, Gwendal Roué wrote: Hi, Unique indexes make some valid update queries fail. Please find below the SQL queries that lead to the unexpected error: -- The `books` and `pages` tables implement a book with several pages. -- Page ordering is implemented via the `position` column in the pages table. -- A unique index makes sure two pages do not share the same position. CREATE TABLE books ( id INT PRIMARY KEY ) CREATE TABLE pages ( book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON UPDATE CASCADE, position INT ) CREATE UNIQUE INDEX pagination ON pages(book_id, position) -- Let's populate the tables with a single book and three pages: INSERT INTO books VALUES (0); INSERT INTO pages VALUES (0,0); INSERT INTO pages VALUES (0,1); INSERT INTO pages VALUES (0,2); -- We want to insert a page between the pages at positions 0 and 1. So we have -- to increment the positions of all pages after page 1. -- Unfortunately, this query yields an error: columns book_id, position are not unique/ UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position = 1; NOT a bug... the moment you SET position to position +1 for the first iteration of the query, it tries to make that entry look like (0,2) and there is of course at this point in time already an entry like (0,2). Some engines allow you to defer the constraint checking until the end of the transaction (and you can do this for References, though you are cascading which is fine). In SQLite the check is immediate and will fail for the duplication attempted on the first iteration. The fact that the other record will eventually be changed to no longer cause a fail is irrelevant to the engine in a non-deferred checking. Now that we have established it isn't a bug, some methods of working round this exist, like Updating in the reverse order (though this has to be done in code as the UPDATE function cannot be ordered). Also creating a temp table then substituting it after an update (but then you have to recreate the index anyway, so dropping the index and re-making it is better though this can take a long time on really large tables). My favourite is simply running the query twice, once making the values negative, and once more fixing them, like this: UPDATE pages SET position = ((position + 1) * -1) WHERE book_id = 0 AND position = 1; UPDATE pages SET position = abs(position) WHERE book_id = 0 AND position 0; No mess, no fuss, no Unique constraint problem. Cheers, Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
Le 8 déc. 2014 à 14:48, RSmith rsm...@rsweb.co.za a écrit : On 2014/12/08 11:55, Gwendal Roué wrote: Hi, Unique indexes make some valid update queries fail. Please find below the SQL queries that lead to the unexpected error: -- The `books` and `pages` tables implement a book with several pages. -- Page ordering is implemented via the `position` column in the pages table. -- A unique index makes sure two pages do not share the same position. CREATE TABLE books ( id INT PRIMARY KEY ) CREATE TABLE pages ( book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON UPDATE CASCADE, position INT ) CREATE UNIQUE INDEX pagination ON pages(book_id, position) -- Let's populate the tables with a single book and three pages: INSERT INTO books VALUES (0); INSERT INTO pages VALUES (0,0); INSERT INTO pages VALUES (0,1); INSERT INTO pages VALUES (0,2); -- We want to insert a page between the pages at positions 0 and 1. So we have -- to increment the positions of all pages after page 1. -- Unfortunately, this query yields an error: columns book_id, position are not unique/ UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position = 1; NOT a bug... the moment you SET position to position +1 for the first iteration of the query, it tries to make that entry look like (0,2) and there is of course at this point in time already an entry like (0,2). Some engines allow you to defer the constraint checking until the end of the transaction (and you can do this for References, though you are cascading which is fine). In SQLite the check is immediate and will fail for the duplication attempted on the first iteration. The fact that the other record will eventually be changed to no longer cause a fail is irrelevant to the engine in a non-deferred checking. Now that we have established it isn't a bug, I'm new to this mailing list, and I won't try to push my opinion, which is : yes this is a bug, and this bug could be fixed without introducing any regression (since fixing it would cause failing code to suddenly run, and this has never been a compatibility issue). Thank you all for your support and explanations. The root cause has been found, and lies in the constraint checking algorithm of sqlite. I have been able to find a work around that is good enough for me. Now the subject deserves a rest, until, maybe, someday, one sqlite maintainer who his not attached to the constraint-checking algorithm fixes it. Have a nice day, Gwendal Roué ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
On 2014/12/08 15:58, Gwendal Roué wrote: I'm new to this mailing list, and I won't try to push my opinion, which is : yes this is a bug, and this bug could be fixed without introducing any regression (since fixing it would cause failing code to suddenly run, and this has never been a compatibility issue). Thank you all for your support and explanations. The root cause has been found, and lies in the constraint checking algorithm of sqlite. I have been able to find a work around that is good enough for me. Now the subject deserves a rest, until, maybe, someday, one sqlite maintainer who his not attached to the constraint-checking algorithm fixes it. Have a nice day, Gwendal Roué Your new-ness is irrelevant, if you have a worthy argument it deserves being heard. To that end, let me just clarify that nobody was saying the idea of deferring the constraint checking is invalid or ludicrous (at least I had no such intention) and you make a valid point, especially since most other DB engines do work as you suggest - and this will be fixed in SQLite4 I believe, where backward-compatibility is not an issue. The reason I (and others) will say it isn't a bug is because it isn't working different than is intended, or more specifically, than is documented. It works exactly like described - whether you or I agree with that paradigm or not is up to discussion but does not make it a bug as long as it works as described. I hope the work-around you found works great! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
I am like you, Gwendal, in that I don't like that behavior in SQLite; however, not liking it doesn't make it a bug. The constraint-checking algorithm was defined to work exactly the way it's working. When designed, the fact that your type of insert would fail was known and understood. Hence, it cannot be considered a bug. Changing it at this date might be a problem. While unlikely, there is a possibility that code exists out there that takes advantage of that particular design attribute. Then you get into pragmas and options and the like. I don't do any of the development, but I suspect that's a serious pain when there are other features that are more useful to work on. So, in short... not a bug, but a design feature that you don't care for. I'm sure there's a way to make suggestions or requests to change the design. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
On Mon, Dec 8, 2014 at 8:15 AM, Marc L. Allen mlal...@outsitenetworks.com wrote: I am like you, Gwendal, in that I don't like that behavior in SQLite; however, not liking it doesn't make it a bug. On another of my forums, this is called a BAD - Broken, As Designed. As opposed to the normal WAD - Working As Designed. -- The temperature of the aqueous content of an unremittingly ogled culinary vessel will not achieve 100 degrees on the Celsius scale. Maranatha! John McKown ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
Might have another work around. update page set position=position + 1 where designation=(select designation from page where book='1' order by position desc) and then insert your page. Please see if that'll work. I tested it, but your results may differ. -Original Message- From: RSmith rsm...@rsweb.co.za To: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Mon, Dec 8, 2014 9:15 am Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail On 2014/12/08 15:58, Gwendal Roué wrote: I'm new to this mailing list, and I won't try to push my opinion, which is : yes this is a bug, and this bug could be fixed without introducing any regression (since fixing it would cause failing code to suddenly run, and this has never been a compatibility issue). Thank you all for your support and explanations. The root cause has been found, and lies in the constraint checking algorithm of sqlite. I have been able to find a work around that is good enough for me. Now the subject deserves a rest, until, maybe, someday, one sqlite maintainer who his not attached to the constraint-checking algorithm fixes it. Have a nice day, Gwendal Roué Your new-ness is irrelevant, if you have a worthy argument it deserves being heard. To that end, let me just clarify that nobody was saying the idea of deferring the constraint checking is invalid or ludicrous (at least I had no such intention) and you make a valid point, especially since most other DB engines do work as you suggest - and this will be fixed in SQLite4 I believe, where backward-compatibility is not an issue. The reason I (and others) will say it isn't a bug is because it isn't working different than is intended, or more specifically, than is documented. It works exactly like described - whether you or I agree with that paradigm or not is up to discussion but does not make it a bug as long as it works as described. I hope the work-around you found works great! ___ 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] sqlite bugreport : unique index causes valid updates to fail
I'm not sure I'd even consider it broken. SQLite is wonderful. Simply wonderful. Code size and amount of features forced into it impresses me no end. But, it was never intended to run with the big dogs. The fact that, quite often, it can is a tribute to the people that work on it. When making a 'lite' version of something, it's normal to eliminate difficult or intensive features that can be lived without. I think this is one of them. Marc -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John McKown Sent: Monday, December 08, 2014 9:18 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail On Mon, Dec 8, 2014 at 8:15 AM, Marc L. Allen mlal...@outsitenetworks.com wrote: I am like you, Gwendal, in that I don't like that behavior in SQLite; however, not liking it doesn't make it a bug. On another of my forums, this is called a BAD - Broken, As Designed. As opposed to the normal WAD - Working As Designed. -- The temperature of the aqueous content of an unremittingly ogled culinary vessel will not achieve 100 degrees on the Celsius scale. Maranatha! John McKown ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
Cancel that, apparently that only updates the last record... -Original Message- From: John McKown john.archie.mck...@gmail.com To: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Mon, Dec 8, 2014 9:18 am Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail On Mon, Dec 8, 2014 at 8:15 AM, Marc L. Allen mlal...@outsitenetworks.com wrote: I am like you, Gwendal, in that I don't like that behavior in SQLite; however, not liking it doesn't make it a bug. On another of my forums, this is called a BAD - Broken, As Designed. As opposed to the normal WAD - Working As Designed. -- The temperature of the aqueous content of an unremittingly ogled culinary vessel will not achieve 100 degrees on the Celsius scale. Maranatha! John McKown ___ 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] sqlite bugreport : unique index causes valid updates to fail
Le 8 déc. 2014 à 15:18, John McKown john.archie.mck...@gmail.com a écrit : On Mon, Dec 8, 2014 at 8:15 AM, Marc L. Allen mlal...@outsitenetworks.com wrote: I am like you, Gwendal, in that I don't like that behavior in SQLite; however, not liking it doesn't make it a bug. On another of my forums, this is called a BAD - Broken, As Designed. As opposed to the normal WAD - Working As Designed. Thanks RSmith, Marc and John. I can live with this :-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
Doesn't that code risk being broken in a later version that doesn't update in the order provided by the sub-query? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of J T Sent: Monday, December 08, 2014 9:23 AM To: rsm...@rsweb.co.za; sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail Might have another work around. update page set position=position + 1 where designation=(select designation from page where book='1' order by position desc) and then insert your page. Please see if that'll work. I tested it, but your results may differ. -Original Message- From: RSmith rsm...@rsweb.co.za To: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Mon, Dec 8, 2014 9:15 am Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail On 2014/12/08 15:58, Gwendal Roué wrote: I'm new to this mailing list, and I won't try to push my opinion, which is : yes this is a bug, and this bug could be fixed without introducing any regression (since fixing it would cause failing code to suddenly run, and this has never been a compatibility issue). Thank you all for your support and explanations. The root cause has been found, and lies in the constraint checking algorithm of sqlite. I have been able to find a work around that is good enough for me. Now the subject deserves a rest, until, maybe, someday, one sqlite maintainer who his not attached to the constraint-checking algorithm fixes it. Have a nice day, Gwendal Roué Your new-ness is irrelevant, if you have a worthy argument it deserves being heard. To that end, let me just clarify that nobody was saying the idea of deferring the constraint checking is invalid or ludicrous (at least I had no such intention) and you make a valid point, especially since most other DB engines do work as you suggest - and this will be fixed in SQLite4 I believe, where backward-compatibility is not an issue. The reason I (and others) will say it isn't a bug is because it isn't working different than is intended, or more specifically, than is documented. It works exactly like described - whether you or I agree with that paradigm or not is up to discussion but does not make it a bug as long as it works as described. I hope the work-around you found works great! ___ 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 This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
On 8-12-2014 14:58, Gwendal Roué wrote: Le 8 déc. 2014 à 14:48, RSmith rsm...@rsweb.co.za a écrit : On 2014/12/08 11:55, Gwendal Roué wrote: Hi, Unique indexes make some valid update queries fail. Please find below the SQL queries that lead to the unexpected error: -- The `books` and `pages` tables implement a book with several pages. -- Page ordering is implemented via the `position` column in the pages table. -- A unique index makes sure two pages do not share the same position. CREATE TABLE books ( id INT PRIMARY KEY ) CREATE TABLE pages ( book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON UPDATE CASCADE, position INT ) CREATE UNIQUE INDEX pagination ON pages(book_id, position) -- Let's populate the tables with a single book and three pages: INSERT INTO books VALUES (0); INSERT INTO pages VALUES (0,0); INSERT INTO pages VALUES (0,1); INSERT INTO pages VALUES (0,2); -- We want to insert a page between the pages at positions 0 and 1. So we have -- to increment the positions of all pages after page 1. -- Unfortunately, this query yields an error: columns book_id, position are not unique/ UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position = 1; NOT a bug... the moment you SET position to position +1 for the first iteration of the query, it tries to make that entry look like (0,2) and there is of course at this point in time already an entry like (0,2). Some engines allow you to defer the constraint checking until the end of the transaction (and you can do this for References, though you are cascading which is fine). In SQLite the check is immediate and will fail for the duplication attempted on the first iteration. The fact that the other record will eventually be changed to no longer cause a fail is irrelevant to the engine in a non-deferred checking. Now that we have established it isn't a bug, I'm new to this mailing list, and I won't try to push my opinion, which is : yes this is a bug, and this bug could be fixed without introducing any regression (since fixing it would cause failing code to suddenly run, and this has never been a compatibility issue). Thank you all for your support and explanations. The root cause has been found, and lies in the constraint checking algorithm of sqlite. I have been able to find a work around that is good enough for me. Now the subject deserves a rest, until, maybe, someday, one sqlite maintainer who his not attached to the constraint-checking algorithm fixes it. Have a nice day, Gwendal Roué It's not a bug, it's in the manual that SQLite behave this way (https://www.sqlite.org/lang_update.html) Optional LIMIT and ORDER BY Clauses If SQLite is built with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option then the syntax of the UPDATE statement is extended with optional ORDER BY and LIMIT clauses as follows: . The ORDER BY clause on an UPDATE statement is used only to determine which rows fall within the LIMIT. The order in which rows are modified is arbitrary and is *not* influenced by the ORDER BY clause. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
On Mon, Dec 8, 2014 at 8:23 AM, Marc L. Allen mlal...@outsitenetworks.com wrote: I'm not sure I'd even consider it broken. Well, to some on that forum: If it doesn't work the way that _I_ want, then it is ipso-facto broken. And I forgot the grin/ in my message. Sorry. SQLite is wonderful. Simply wonderful. Code size and amount of features forced into it impresses me no end. But, it was never intended to run with the big dogs. The fact that, quite often, it can is a tribute to the people that work on it. I completely agree. I took the source code and copied to my z/OS mainframe operating system. This system is a UNIX branded system. But is very weird. Mainly in that it does not use ASCII or Unicode, but another coding sequence called EBCDIC. Dr. Hipp already had the EBCDIC code in SQLite. And, despite not having access to a z/OS system (as I understand it), the code compiled and ran cleanly on z/OS out of the box. Amazing! When making a 'lite' version of something, it's normal to eliminate difficult or intensive features that can be lived without. I think this is one of them. Again, I agree. The only other RDMS which I have used on the aforementioned system, which was not especially designed for it (DB2), is Derby (pure Java implementation). SQLite is, IMO, much nicer. And it is definitely much less of a hog. Marc -- The temperature of the aqueous content of an unremittingly ogled culinary vessel will not achieve 100 degrees on the Celsius scale. Maranatha! John McKown ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
Ideally there would be something like DEFERRED foreign key checking for uniqueness constraints... You can get something like that by using non-unique indexes (but there would also go your primary keys) and then check that there are no duplicates before you COMMIT. (Doing this reliably would require something like transaction triggers, which IIRC exists in a sessions branch.) Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
On 12/08/2014 09:55 PM, Nico Williams wrote: Ideally there would be something like DEFERRED foreign key checking for uniqueness constraints... You could hack SQLite to do enforce unique constraints the same way as FKs. When adding an entry to a UNIQUE index b-tree, you check for a duplicate. If one exists, increment a counter. Do the opposite when removing entries - decrement the counter if there are two or more duplicates of the entry you are removing. If your counter is greater than zero at commit time, a UNIQUE constraint has failed. I suspect there would be a non-trivial increase in the CPU use of UPDATE statements though. You can get something like that by using non-unique indexes (but there would also go your primary keys) and then check that there are no duplicates before you COMMIT. (Doing this reliably would require something like transaction triggers, which IIRC exists in a sessions branch.) Nico -- ___ 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] sqlite bugreport : unique index causes valid updates to fail
Yes, that would be nice. For example, sqlite already needs explicit opt-in for some of the relational toolkit. I think about PRAGMA foreign_keys = ON. Why not an opt-in way to ask for deferred constraint checking. The key here is only to allow perfectly legit requests to run. With all the due respect to sqlite implementors and the wonderful design of sqlite. Le 8 déc. 2014 à 15:55, Nico Williams n...@cryptonector.com a écrit : Ideally there would be something like DEFERRED foreign key checking for uniqueness constraints... You can get something like that by using non-unique indexes (but there would also go your primary keys) and then check that there are no duplicates before you COMMIT. (Doing this reliably would require something like transaction triggers, which IIRC exists in a sessions branch.) Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Le 8 déc. 2014 à 10:55, Gwendal Roué g...@pierlis.com a écrit : Hi, Unique indexes make some valid update queries fail. Please find below the SQL queries that lead to the unexpected error: -- The `books` and `pages` tables implement a book with several pages. -- Page ordering is implemented via the `position` column in the pages table. -- A unique index makes sure two pages do not share the same position. CREATE TABLE books ( id INT PRIMARY KEY ) CREATE TABLE pages ( book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON UPDATE CASCADE, position INT ) CREATE UNIQUE INDEX pagination ON pages(book_id, position) -- Let's populate the tables with a single book and three pages: INSERT INTO books VALUES (0); INSERT INTO pages VALUES (0,0); INSERT INTO pages VALUES (0,1); INSERT INTO pages VALUES (0,2); -- We want to insert a page between the pages at positions 0 and 1. So we have -- to increment the positions of all pages after page 1. -- Unfortunately, this query yields an error: columns book_id, position are not unique/ UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position = 1; The query should run without any error, since it does not break the unique index. Thank you for considering this issue. Cheers, Gwendal Roué ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
On 8 Dec 2014, at 3:05pm, Gwendal Roué g...@pierlis.com wrote: Why not an opt-in way to ask for deferred constraint checking. The key here is only to allow perfectly legit requests to run. With all the due respect to sqlite implementors and the wonderful design of sqlite. SQL-99 includes a syntax for deferred checking. We don't need to invent our own syntax with a PRAGMA. However, it is done when the constraint is defined rather than being something one can turn on or off. So you would need to think out whether you wanted row- or transaction-based checking when you define each constraint in the first place. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
Le 8 déc. 2014 à 17:21, Simon Slavin slav...@bigfraud.org a écrit : Why not an opt-in way to ask for deferred constraint checking. The key here is only to allow perfectly legit requests to run. With all the due respect to sqlite implementors and the wonderful design of sqlite. SQL-99 includes a syntax for deferred checking. We don't need to invent our own syntax with a PRAGMA. However, it is done when the constraint is defined rather than being something one can turn on or off. So you would need to think out whether you wanted row- or transaction-based checking when you define each constraint in the first place. Hi Simon, This topic is fascinating. Googling for SQL-99 deferred checking, I stumbled upon this page which shows how deferred index maintenance affects Oracle query plan, and performance : https://alexanderanokhin.wordpress.com/deferred-index-maintenance/. I now understand that the strategy for checking index constraints is tied to their maintenance. The `UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position = 1` query we are talking about has indeed to perform both. Such an innocuous-looking request, and it sends us right into the very guts of relational constraints :-) Gwendal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
On Mon, Dec 8, 2014 at 9:01 AM, Dan Kennedy danielk1...@gmail.com wrote: You could hack SQLite to do enforce unique constraints the same way as FKs. When adding an entry to a UNIQUE index b-tree, you check for a duplicate. If one exists, increment a counter. Do the opposite when removing entries - decrement the counter if there are two or more duplicates of the entry you are removing. If your counter is greater than zero at commit time, a UNIQUE constraint has failed. I suspect there would be a non-trivial increase in the CPU use of UPDATE statements though. Well, it'd be an option which, when not used, ought to cost very few additional unlikely branches. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite]Basic query
On Dec 8, 2014 2:10 AM, Shinichiro Yoshioka dekochan...@gmail.com wrote: Hi, I'm about to use sqlite-amalgamation(sqlite3.c) on Visual C++. But although the compiling was successfully finished, even if I set break point on the source code, I can't trace the working line in sqlite3.c correctly. As someone else said, the problem is that the Visual C++ debugger doesn't play nice with files in excess of 64KiB lines. When I had this need a couple years ago, I carefully split the sqlite3.c file into several pieces and compiled them separately. SDR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite]Basic query
On Mon, Dec 8, 2014 at 12:20 PM, Scott Robison sc...@casaderobison.com wrote: On Dec 8, 2014 2:10 AM, Shinichiro Yoshioka dekochan...@gmail.com wrote: Hi, I'm about to use sqlite-amalgamation(sqlite3.c) on Visual C++. But although the compiling was successfully finished, even if I set break point on the source code, I can't trace the working line in sqlite3.c correctly. As someone else said, the problem is that the Visual C++ debugger doesn't play nice with files in excess of 64KiB lines. When I had this need a couple years ago, I carefully split the sqlite3.c file into several pieces and compiled them separately. From the canonical SQLite source code you can type make sqlite3-all.c and it will generate a version of the amalgamation that #includes a handful of separate files (named sqlite3-N.c for N=1,2,3,), each less than 32K lines in size. drh@bella:~/sqlite/bld$ make sqlite3-all.c tclsh /home/drh/sqlite/sqlite/tool/split-sqlite3c.tcl drh@bella:~/sqlite/bld$ wc sqlite3-*.c 32314 165952 1228350 sqlite3-1.c 30892 145495 1098859 sqlite3-2.c 32729 144742 1091870 sqlite3-3.c 32481 150359 1198841 sqlite3-4.c 23259 100070 768733 sqlite3-5.c 32 2371518 sqlite3-all.c 151707 706855 5388171 total Include all these files in your project, but compile against just sqlite3-all.c. -- 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] [SQLite]Basic query
Thank you everyone! I'll try to solve this case with your replies! Thanks, 2014年12月9日火曜日、Richard Hippd...@sqlite.orgさんは書きました: On Mon, Dec 8, 2014 at 12:20 PM, Scott Robison sc...@casaderobison.com javascript:; wrote: On Dec 8, 2014 2:10 AM, Shinichiro Yoshioka dekochan...@gmail.com javascript:; wrote: Hi, I'm about to use sqlite-amalgamation(sqlite3.c) on Visual C++. But although the compiling was successfully finished, even if I set break point on the source code, I can't trace the working line in sqlite3.c correctly. As someone else said, the problem is that the Visual C++ debugger doesn't play nice with files in excess of 64KiB lines. When I had this need a couple years ago, I carefully split the sqlite3.c file into several pieces and compiled them separately. From the canonical SQLite source code you can type make sqlite3-all.c and it will generate a version of the amalgamation that #includes a handful of separate files (named sqlite3-N.c for N=1,2,3,), each less than 32K lines in size. drh@bella:~/sqlite/bld$ make sqlite3-all.c tclsh /home/drh/sqlite/sqlite/tool/split-sqlite3c.tcl drh@bella:~/sqlite/bld$ wc sqlite3-*.c 32314 165952 1228350 sqlite3-1.c 30892 145495 1098859 sqlite3-2.c 32729 144742 1091870 sqlite3-3.c 32481 150359 1198841 sqlite3-4.c 23259 100070 768733 sqlite3-5.c 32 2371518 sqlite3-all.c 151707 706855 5388171 total Include all these files in your project, but compile against just sqlite3-all.c. -- D. Richard Hipp d...@sqlite.org javascript:; ___ sqlite-users mailing list sqlite-users@sqlite.org javascript:; 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] [sqlite-dev] Implementing per-value custom types
On 2014-11-26, 7:53 AM, RSmith wrote:
The fact that inside of SQLite an Integer can be stored in different
ways is simply a code/data/space optimisation for SQLite, it is
transparent to the user and transparent to the SQL - it is in no way
intended as a data-feature or extension of the SQL Language. If you try
to build on top of that (or maybe not on top of it, but in the same way)
more arbitrary sub-types, yes of course it is possible, but it may leave
you in a World of hurt in terms of using the DB as a relational database
system for other normal activities.
This is a documented feature of SQLite and shouldn't be considered an
optimisation or a transparent feature. I've depended on this feature to
implement something very similar to OP where two columns together
described a value: 1 column represented the type, the other a BLOB
representing the value.
SQLite's ultimately untyped storage allowed me to index these types as
best as I could expect it to be done in a superb, efficient manner.
I'd include this feature of SQLite as a killer feature. For sure, if you
needed DB portability, you'd have to have a single type in a column
which could mean one column for each type you'd want to have. Yuck.
Here is an example of how I consumed this variant in C++:
templatetypename T
static
T
getAttribute( Wt::Dbo::Session session,
dbo::id_type repositoryId,
String const attrName )
{
return boost::getT(
findAttribute(session,repositoryId,attrName)
-value
);
}
The C++ magic deserialized the two column value into value. It has
turned out to be immensely useful.
My point again is: it's a documented feature of SQLite, not an optimisation.
Sohail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite-dev] Implementing per-value custom types
That's not true. A 64 bit floating point number and an 64 bit integer can
be represented by the exact same 64 bit pattern, and no-one would suggest
they're the same value. You can have those two differently typed although
identical bit values in the same SQLite column. The data identifying the
representation of those datums is integral to that value and doesn't belong
in a different column as you say. Other SQL based systems also allow
differently represented (typed) values to appear in the same column.
The domain of a column can logically incorporate these different kinds of
values by introducing the concept of subtype. For instance in SQLite there
are something like 6 different representations of integer of between 1 and
64 bits. Each one of those is a different type in the sense of having a
different representation due to the number of bits they use and being
limited to a different set of numbers. A 1 bit integer is a subtype of a 64
bit integer since the set of numbers allowed by the 1 bit integer is {0, 1}
are contained with the set of numbers allowed by 64 bit integers, ie
{0..2^64-1}. If the column has a domain of integer then all these values
are valid since they're all integers. There is no logical or formal reason
why this can't be extended further to allow arbitrary subtypes according to
the user's wishes.
You can have the same 64 bits of data represent 4 different values in
SQLite: integer, double, string and blob (I'm assuming SQLite can store a
blob in 8 bytes). They are not treated as equal by SQLite because they have
different types. There is no reason why we should be limited to those 4
types in SQLite. Many SQL based system allow users to define their own
types. What I'm proposing is just implementing the same thing in SQLite.
There is nothing in the relational model that disallows this. You're
assuming that because columns have a domain or type, then that domain must
have a fixed representation in the database implementation. The relational
model says nothing about how the data is represented or what kind of data
can be stored.
On Tue, Nov 25, 2014 at 5:20 PM, James K. Lowden jklow...@schemamania.org
wrote:
On Tue, 25 Nov 2014 04:41:51 -0800
Darko Volaric li...@darko.org wrote:
I have a need to implement per-value custom typing in SQLite. In my
case I may have the integer value 1234 appear in two rows in the same
column, but they are different in that they have completely different
meanings because they have different types although they have the
same representation in the database.
A column is a type. Two values of the same type *must* represent the
same thing. That's how SQL works.
When you say 1234 may represent two different things in the same
column, what you really mean is that 1234 doesn't stand for the whole
thing, that the column isn't the whole type (as you conceive it).
That's fine; you need another column to discriminate between them, to
capture that whole type. Each column-component of that type is itself
a type, just as a street name is part of a postal address.
As a practical example of what that's true, consider this list:
anything
cat
green
jogging
We can sort that anything column as *strings*, but what if each one is
1234 in the database? Even if they are different values, how do you
compare green to jogging? Which one comes first? How should a join
work?
If that doesn't convince you, please understand I'm not expressing an
opinion. I'm pointing out a basic tenet of the relational model. I
can recommend good references on the subject.
If you represent your things, whatever they are, in the model according
to its rules, you will find you don't need to extend the type system.
Nothing good awaits you if you attempt to extend it without first
understanding it.
HTH.
--jkl
___
sqlite-dev mailing list
sqlite-...@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite-dev] Implementing per-value custom types
Hi Darko,
Firstly, kindly keep this to the sqlite-users forum and not on the dev forum (the devs read this too, the difference being simply
that this one exists to help you, the other one is to discuss development stuff, not to help anyone).
Secondly, you are confusing two things. You are arguing about what /CAN/ be done while James tried to explain what /Should/ be done.
Nobody can argue with the possibility, but from experience we know that you are going down a road that will bite you later.
That said, the best way to achieve what you wish to do is by adding columns that explain the domain of a type on a per-row basis.
The fact that inside of SQLite an Integer can be stored in different ways is simply a code/data/space optimisation for SQLite, it is
transparent to the user and transparent to the SQL - it is in no way intended as a data-feature or extension of the SQL Language. If
you try to build on top of that (or maybe not on top of it, but in the same way) more arbitrary sub-types, yes of course it is
possible, but it may leave you in a World of hurt in terms of using the DB as a relational database system for other normal activities.
In contrast, if this is intended more as an application file-format than RDBMS, sure, make it better but, as others pointed out,
still try to do so in a way that does not require maintaining a software fork. This is good advice and in no way forbidding or
prescribing, just letting you know what will make life easier for you in the long run.
From the tone of your last post (if I am reading correctly) I understand that you have your mind set on finding a way that you have
thought about a lot lying in your bed late at night, you have rolled it around in your head and you just feel this should be
doable and will be so elegant a solution. We all go through that. You probably came here looking for confirmation of your ideas
rather than advice, but many of the people here have already done what you are trying now, this is why they know and this is why
this list is useful.
Y'know, use it, don't use it, etc. :)
On 2014/11/26 13:22, Darko Volaric wrote:
That's not true. A 64 bit floating point number and an 64 bit integer can
be represented by the exact same 64 bit pattern, and no-one would suggest
they're the same value. You can have those two differently typed although
identical bit values in the same SQLite column. The data identifying the
representation of those datums is integral to that value and doesn't belong
in a different column as you say. Other SQL based systems also allow
differently represented (typed) values to appear in the same column.
The domain of a column can logically incorporate these different kinds of
values by introducing the concept of subtype. For instance in SQLite there
are something like 6 different representations of integer of between 1 and
64 bits. Each one of those is a different type in the sense of having a
different representation due to the number of bits they use and being
limited to a different set of numbers. A 1 bit integer is a subtype of a 64
bit integer since the set of numbers allowed by the 1 bit integer is {0, 1}
are contained with the set of numbers allowed by 64 bit integers, ie
{0..2^64-1}. If the column has a domain of integer then all these values
are valid since they're all integers. There is no logical or formal reason
why this can't be extended further to allow arbitrary subtypes according to
the user's wishes.
You can have the same 64 bits of data represent 4 different values in
SQLite: integer, double, string and blob (I'm assuming SQLite can store a
blob in 8 bytes). They are not treated as equal by SQLite because they have
different types. There is no reason why we should be limited to those 4
types in SQLite. Many SQL based system allow users to define their own
types. What I'm proposing is just implementing the same thing in SQLite.
There is nothing in the relational model that disallows this. You're
assuming that because columns have a domain or type, then that domain must
have a fixed representation in the database implementation. The relational
model says nothing about how the data is represented or what kind of data
can be stored.
On Tue, Nov 25, 2014 at 5:20 PM, James K. Lowden jklow...@schemamania.org
wrote:
On Tue, 25 Nov 2014 04:41:51 -0800
Darko Volaric li...@darko.org wrote:
I have a need to implement per-value custom typing in SQLite. In my
case I may have the integer value 1234 appear in two rows in the same
column, but they are different in that they have completely different
meanings because they have different types although they have the
same representation in the database.
A column is a type. Two values of the same type *must* represent the
same thing. That's how SQL works.
When you say 1234 may represent two different things in the same
column, what you really mean is that 1234 doesn't stand for the whole
thing, that the column isn't the whole type (as you
Re: [sqlite] [sqlite-dev] Implementing per-value custom types
The person I replied to cross posted, not I, and I didn't realise this
before I replied to his cross post and the I couldn't change it then, so
maybe take that up with him.
I'm not confusing anything. You, and the other posters, are confusing the
representation of values and the concrete value type with the logical types
declared for column domains. I understand this might be a subtle
distinction to some.
But I don't intend to argue this point any further since I'm merely looking
for advice about how the database engine is implemented, not about how it's
used or how I'm using it.
I'm not looking for confirmation of ideas, on the contrary, people seem to
want to push their own ideas about a database should be used and how I'm
not using it correctly, when that is irrlevent to the issue I'm discussing.
Maybe more focus on the technical facts and less on divining what I think
at night in bed and try framing your arguments based on those technical
facts rather than ad-hominem attacks.
On Wed, Nov 26, 2014 at 4:53 AM, RSmith rsm...@rsweb.co.za wrote:
Hi Darko,
Firstly, kindly keep this to the sqlite-users forum and not on the dev
forum (the devs read this too, the difference being simply that this one
exists to help you, the other one is to discuss development stuff, not to
help anyone).
Secondly, you are confusing two things. You are arguing about what /CAN/
be done while James tried to explain what /Should/ be done. Nobody can
argue with the possibility, but from experience we know that you are going
down a road that will bite you later.
That said, the best way to achieve what you wish to do is by adding
columns that explain the domain of a type on a per-row basis. The fact that
inside of SQLite an Integer can be stored in different ways is simply a
code/data/space optimisation for SQLite, it is transparent to the user and
transparent to the SQL - it is in no way intended as a data-feature or
extension of the SQL Language. If you try to build on top of that (or maybe
not on top of it, but in the same way) more arbitrary sub-types, yes of
course it is possible, but it may leave you in a World of hurt in terms of
using the DB as a relational database system for other normal activities.
In contrast, if this is intended more as an application file-format than
RDBMS, sure, make it better but, as others pointed out, still try to do so
in a way that does not require maintaining a software fork. This is good
advice and in no way forbidding or prescribing, just letting you know what
will make life easier for you in the long run.
From the tone of your last post (if I am reading correctly) I understand
that you have your mind set on finding a way that you have thought about a
lot lying in your bed late at night, you have rolled it around in your head
and you just feel this should be doable and will be so elegant a
solution. We all go through that. You probably came here looking for
confirmation of your ideas rather than advice, but many of the people here
have already done what you are trying now, this is why they know and this
is why this list is useful.
Y'know, use it, don't use it, etc. :)
On 2014/11/26 13:22, Darko Volaric wrote:
That's not true. A 64 bit floating point number and an 64 bit integer can
be represented by the exact same 64 bit pattern, and no-one would suggest
they're the same value. You can have those two differently typed although
identical bit values in the same SQLite column. The data identifying the
representation of those datums is integral to that value and doesn't
belong
in a different column as you say. Other SQL based systems also allow
differently represented (typed) values to appear in the same column.
The domain of a column can logically incorporate these different kinds of
values by introducing the concept of subtype. For instance in SQLite there
are something like 6 different representations of integer of between 1 and
64 bits. Each one of those is a different type in the sense of having a
different representation due to the number of bits they use and being
limited to a different set of numbers. A 1 bit integer is a subtype of a
64
bit integer since the set of numbers allowed by the 1 bit integer is {0,
1}
are contained with the set of numbers allowed by 64 bit integers, ie
{0..2^64-1}. If the column has a domain of integer then all these values
are valid since they're all integers. There is no logical or formal reason
why this can't be extended further to allow arbitrary subtypes according
to
the user's wishes.
You can have the same 64 bits of data represent 4 different values in
SQLite: integer, double, string and blob (I'm assuming SQLite can store a
blob in 8 bytes). They are not treated as equal by SQLite because they
have
different types. There is no reason why we should be limited to those 4
types in SQLite. Many SQL based system allow users to define their own
types. What I'm proposing is just
Re: [sqlite] [sqlite-dev] Implementing per-value custom types
On 2014/11/26 15:58, Darko Volaric wrote: I'm not looking for confirmation of ideas, on the contrary, people seem to want to push their own ideas about a database should be used and how I'm not using it correctly, when that is irrlevent to the issue I'm discussing. Maybe more focus on the technical facts and less on divining what I think at night in bed and try framing your arguments based on those technical facts rather than ad-hominem attacks. I am sincerely sorry if you construed my reply as anything remotely ad hominem - it surely wasn't, and it's not pushing ideas on you, it's giving advice or alternates because your ideas are short-sighted and hard to implement. We are however very nice on here since sharing knowledge is a passion, and that's why we say nice things like we understand how it feels to have ideas and then offer some advice... not because we spend our days divining about your life. You throw everyone's advice back in their faces and are arrogant about it - well, even to that we are still nice and willing to answer the questions - kindly accept it in that spirit. But I don't intend to argue this point any further since I'm merely looking for advice about how the database engine is implemented, not about how it's used or how I'm using it. Ok, sticking to the facts, the database engine is implemented in a way that makes your original suggested options pan out like this: 1 - very easy for the engine, very work intensive for you. 2 - still easy for the engine, although it will lose most RDBMS querying value, and still cumbersome for you (Maybe best to use Virtual tables to implement this), and 3 - impossible without a dedicated fork, and even then very difficult. I wouldn't personally pick any of those, but if those were the only options in life and I had to pick one, knowing SQLite, I'd probably lean more towards option 2 than the others. SQLite is loosely typed, but it is still typed, and the typing mechanism is not open to the API and every one of the hundreds of core functions in SQLite are specifically coded to dance with those few primary types. Adding/Altering it must always be the very last option on any list. Good luck, Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite-dev] Implementing per-value custom types
You wrote: From the tone of your last post (if I am reading correctly) I understand that you have your mind set on finding a way that you have thought about a lot lying in your bed late at night, you have rolled it around in your head and you just feel this should be doable and will be so elegant a solution. That references only me, in a condescending way, without referencing the actual issue. That's ad-hominem. You've made it clear by your posts you don't understand the SQLite issue I'm talking about so I'm not discussing that further, as I have already said. If you want to address anything to do with me, then email me directly at take it off list, so as to stop wasting other people's time. On Wed, Nov 26, 2014 at 8:20 AM, RSmith rsm...@rsweb.co.za wrote: On 2014/11/26 15:58, Darko Volaric wrote: I'm not looking for confirmation of ideas, on the contrary, people seem to want to push their own ideas about a database should be used and how I'm not using it correctly, when that is irrlevent to the issue I'm discussing. Maybe more focus on the technical facts and less on divining what I think at night in bed and try framing your arguments based on those technical facts rather than ad-hominem attacks. I am sincerely sorry if you construed my reply as anything remotely ad hominem - it surely wasn't, and it's not pushing ideas on you, it's giving advice or alternates because your ideas are short-sighted and hard to implement. We are however very nice on here since sharing knowledge is a passion, and that's why we say nice things like we understand how it feels to have ideas and then offer some advice... not because we spend our days divining about your life. You throw everyone's advice back in their faces and are arrogant about it - well, even to that we are still nice and willing to answer the questions - kindly accept it in that spirit. But I don't intend to argue this point any further since I'm merely looking for advice about how the database engine is implemented, not about how it's used or how I'm using it. Ok, sticking to the facts, the database engine is implemented in a way that makes your original suggested options pan out like this: 1 - very easy for the engine, very work intensive for you. 2 - still easy for the engine, although it will lose most RDBMS querying value, and still cumbersome for you (Maybe best to use Virtual tables to implement this), and 3 - impossible without a dedicated fork, and even then very difficult. I wouldn't personally pick any of those, but if those were the only options in life and I had to pick one, knowing SQLite, I'd probably lean more towards option 2 than the others. SQLite is loosely typed, but it is still typed, and the typing mechanism is not open to the API and every one of the hundreds of core functions in SQLite are specifically coded to dance with those few primary types. Adding/Altering it must always be the very last option on any list. Good luck, Ryan ___ 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] [sqlite-dev] Implementing per-value custom types
a type column to go with variant data would probably be best... how many columns do you have that are actually self-described typedata required? could just serialize it to a blob; include type, and the value... kinda hard to select for a value that way... at least if it's a parallel type the value can be a simple representation. variant types can either be handled with a container that contains a type indicator (VB-like) or just kept as a string, and when required to be a value, parsed to see if it can apply... why not just break out separate tables per type that link back to the data row id? For options, started with a value table that was (value_id,int,string,blob), later broke it out to 3 tables (option_id,int) (option_id,string) (option_id,blob) ... I used to create the value_id and store that back in the option map; realized I could just use the map id to get the value instead... THe other type of variable data I ran into was a bank ledger that had a 'operation' field and a value field, and a couple related account IDs.. where the procedure to do with the value was determined variably rather than the data type... If I were to do it again, I'd break out transaction types to separate tables... yes, it complicates queireis requiring joins, but for later general tool use it's easier to cope with. How many applications are really storing variable types of data? Will new applications also write new kinds of data that were previously ununderstood? Will old things still work? On Wed, Nov 26, 2014 at 9:25 AM, Darko Volaric li...@darko.org wrote: You wrote: From the tone of your last post (if I am reading correctly) I understand that you have your mind set on finding a way that you have thought about a lot lying in your bed late at night, you have rolled it around in your head and you just feel this should be doable and will be so elegant a solution. That references only me, in a condescending way, without referencing the actual issue. That's ad-hominem. You've made it clear by your posts you don't understand the SQLite issue I'm talking about so I'm not discussing that further, as I have already said. If you want to address anything to do with me, then email me directly at take it off list, so as to stop wasting other people's time. On Wed, Nov 26, 2014 at 8:20 AM, RSmith rsm...@rsweb.co.za wrote: On 2014/11/26 15:58, Darko Volaric wrote: I'm not looking for confirmation of ideas, on the contrary, people seem to want to push their own ideas about a database should be used and how I'm not using it correctly, when that is irrlevent to the issue I'm discussing. Maybe more focus on the technical facts and less on divining what I think at night in bed and try framing your arguments based on those technical facts rather than ad-hominem attacks. I am sincerely sorry if you construed my reply as anything remotely ad hominem - it surely wasn't, and it's not pushing ideas on you, it's giving advice or alternates because your ideas are short-sighted and hard to implement. We are however very nice on here since sharing knowledge is a passion, and that's why we say nice things like we understand how it feels to have ideas and then offer some advice... not because we spend our days divining about your life. You throw everyone's advice back in their faces and are arrogant about it - well, even to that we are still nice and willing to answer the questions - kindly accept it in that spirit. But I don't intend to argue this point any further since I'm merely looking for advice about how the database engine is implemented, not about how it's used or how I'm using it. Ok, sticking to the facts, the database engine is implemented in a way that makes your original suggested options pan out like this: 1 - very easy for the engine, very work intensive for you. 2 - still easy for the engine, although it will lose most RDBMS querying value, and still cumbersome for you (Maybe best to use Virtual tables to implement this), and 3 - impossible without a dedicated fork, and even then very difficult. I wouldn't personally pick any of those, but if those were the only options in life and I had to pick one, knowing SQLite, I'd probably lean more towards option 2 than the others. SQLite is loosely typed, but it is still typed, and the typing mechanism is not open to the API and every one of the hundreds of core functions in SQLite are specifically coded to dance with those few primary types. Adding/Altering it must always be the very last option on any list. Good luck, Ryan ___ 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] [SQLite] Support for 23,10 Precision number format
On Thu, Nov 13, 2014 at 6:53 PM, RSmith rsm...@rsweb.co.za wrote: By the way, my Oracle friends should intersect here if need be, but I believe the oracle method of /decimal(n,m)/ is simply a representation directive and constraint, there is no native datatype that actually stores or communicates such a value. Oracle stores it internally in a very specific arrangement of bytes and you need to still interpret it in your software. Not so. OCI has native support for Number client side via OCINumber and associated functions to convert to native C types, and can do 128-bit integer arithmetic for example even when the C/C++ native types cannot. And it's a value type, not an opaque type, so you can decode the internal well-known byte rep if you want to even. --DD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite Issue
Hi, I am new for SQLite. I am trying to create database. But its not creating. I have attached the screenshot. Please find and do needful. Thanks Regards Arvind ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite] Support for 23,10 Precision number format
On 14 Nov 2014, at 3:42am, James K. Lowden jklow...@schemamania.org wrote: Simon Slavin slav...@bigfraud.org wrote: I'm not aware of any usable libraries which actually support 23,10 outside the world of physics. http://www.mpfr.org/#free-sw I'm sure you're aware of such things. Hey, you're right. I was thinking about 128-bit stuff. I forgot about arbitrary-precision libraries. Thanks for the correction. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite Issue
On 14 Nov 2014, at 10:40am, ARVIND KUMAR arv...@sblsoftware.com wrote: I am new for SQLite. I am trying to create database. But its not creating. I have attached the screenshot. Please find and do needful. You cannot post screenshots to this mailing list. Most problems with creating a new database are because you failed to specify an appropriate folder/directory for the database file. Please make sure you have write access to the folder you specified. If you are writing your own program which calls the SQLite API, and it is not creating the database, the function will return a value which is not SQLITE_OK. Please tell us what value it is returning. If you are using the SQLite Shell Tool to create your database, and it is not working, please post the error message it generates. If you are using some other program besides the SQLite Shell Tool, then that program is written by a third party, not the SQLite team and the SQLite team can't do anything about your problem. However, if you tell us what program you're using and what error message it shows we /might/ be able to advise you on what to try next. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite Issue
If you're using SQLite3.exe (or equivalent CLI - Command Line Interface) then by default the database id written to memory, not to the disk. Doing something like [ sqlite3.exe test.db3] will create a test.db3 file once you do an actual transaction like creating a table. I THINK even doing a select will make the file as well. If you're writing code in some language, as Simon suggested, ensure that you're not writing to [ :memory: ] or to a directory that the app has create and write permissions. On Fri, Nov 14, 2014 at 5:40 AM, ARVIND KUMAR arv...@sblsoftware.com wrote: Hi, I am new for SQLite. I am trying to create database. But its not creating. I have attached the screenshot. Please find and do needful. Thanks Regards Arvind ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [SQLite] Support for 23,10 Precision number format
Hi, Does any of SQLite data Type support 23,10 precision format for Number? If yes, could you pleas help with right data type or approach to achieve this. If No, then is there something that can be added to SQLite and how quickly? Thanks, Dinesh Navsupe ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite] Support for 23,10 Precision number format
On 13 Nov 2014, at 12:23pm, Dinesh Navsupe dinesh.navs...@gmail.com wrote: Does any of SQLite data Type support 23,10 precision format for Number? If yes, could you pleas help with right data type or approach to achieve this. SQL stores REAL numbers in a REAL field which conforms to 64-bit IEEE 754 (as much as SQL permits). This allows 16 decimal digits of precision. You can store numbers of greater precision than that -- by storing them as strings or BLOBs. You just can't have SQLite do its own maths on them. If No, then is there something that can be added to SQLite and how quickly? It is unlikely that the developer team would be interested in doing this. Given that the source code for SQLite is open, you might want to implement them yourself. However, the test library for having the developer team do this this would probably have to be huge. SQLite4, which is not released yet, uses an 18-digit decimal number with a 3-digit base-10 exponent. It is possible that this might change if you are able to produce a good argument for doing so Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite] Support for 23,10 Precision number format
Data types are 64bit integer (~18 decimal digits) and 64 Bit IEEE Float(11 bit exponent, 52 bit fraction), so no. Store the numbers as TEXT (human readable) or BLOB (e.g. 128Bit binary) and write user-defined functions to manipulate them. -Ursprüngliche Nachricht- Von: Dinesh Navsupe [mailto:dinesh.navs...@gmail.com] Gesendet: Donnerstag, 13. November 2014 13:23 An: sqlite-users@sqlite.org Betreff: [sqlite] [SQLite] Support for 23,10 Precision number format Hi, Does any of SQLite data Type support 23,10 precision format for Number? If yes, could you pleas help with right data type or approach to achieve this. If No, then is there something that can be added to SQLite and how quickly? Thanks, Dinesh Navsupe ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite] Support for 23,10 Precision number format
Hi, My need is 23 decimal digits of precision. We work on complex payout calculation engine where in formula outputs are quite large numbers and clients do not want to round off. We want to use SQLite for local disk data store and calculations. Thanks, Dinesh Navsupe On Thu, Nov 13, 2014 at 6:14 PM, Simon Slavin slav...@bigfraud.org wrote: On 13 Nov 2014, at 12:23pm, Dinesh Navsupe dinesh.navs...@gmail.com wrote: Does any of SQLite data Type support 23,10 precision format for Number? If yes, could you pleas help with right data type or approach to achieve this. SQL stores REAL numbers in a REAL field which conforms to 64-bit IEEE 754 (as much as SQL permits). This allows 16 decimal digits of precision. You can store numbers of greater precision than that -- by storing them as strings or BLOBs. You just can't have SQLite do its own maths on them. If No, then is there something that can be added to SQLite and how quickly? It is unlikely that the developer team would be interested in doing this. Given that the source code for SQLite is open, you might want to implement them yourself. However, the test library for having the developer team do this this would probably have to be huge. SQLite4, which is not released yet, uses an 18-digit decimal number with a 3-digit base-10 exponent. It is possible that this might change if you are able to produce a good argument for doing so 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] [SQLite] Support for 23,10 Precision number format
On 2014/11/13 15:01, Dinesh Navsupe wrote: Hi, My need is 23 decimal digits of precision. We work on complex payout calculation engine where in formula outputs are quite large numbers and clients do not want to round off. I do not think that re-stating your need suffices as a good enough argument. We heard you the first time and understand the need and understand exactly that you are working with numbers in that precision so you would like to be able to store them efficiently. SQLite does not do this in binary terms (it can produce 64 bit Integers/Reals at best in accordance with IEEE as some others already explained). No other Engine does this natively either (meaning that in your code there is no way to exchange that size number with the prepared statement/api/object within a standard C data type other than a string or blob). Using strings is preferable because they will be human readable in DB dumps, but you will need to use a BigInt type library or create your own conversion routines to actually translate those numbers to and from strings for the purposes of communicating it to/from any DB engine, SQLite included. We want to use SQLite for local disk data store and calculations. That's a good decision, but it comes with a bit of work, same as any other chosen RDBMS. As an aside, do you really need that precision? You can accurately state the American National Debt in dollars and cents within 16 digits of precision (That's 18 trillion dollars btw, or 18^12). I can't imagine a payout ever needing to be much higher than that, or by another 8 or so digits more precise? (well, that would allow you to express the entire World's collective national debts in Zimbabwe dollars). 16 digits can very well be represented by a Float in SQLite and transferred to your program in standard 64-bit floats via the API. Above that you will need to make the routines. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite] Support for 23,10 Precision number format
On Thu, Nov 13, 2014 at 2:33 PM, RSmith rsm...@rsweb.co.za wrote: On 2014/11/13 15:01, Dinesh Navsupe wrote: My need is 23 decimal digits of precision. We work on complex payout calculation engine where in formula outputs are quite large numbers and clients do not want to round off. If IEEE double is not good enough, you can use already suggested string or blob based representation, or store the integral part and decimal part in separate integer-typed columns, which have also the advantage to be smaller for small values than larger one (varint format [1]). Or if your exact arithmetic package is based on rational numbers, store numerator/denominator in separate columns similarly. Both remaining human-readable like string, and it might be faster to re-instantiate your custom integer type than parsing a string. FWIW. --DD [1] https://www.sqlite.org/fileformat2.html#varint ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite] Support for 23,10 Precision number format
On 13 Nov 2014, at 1:01pm, Dinesh Navsupe dinesh.navs...@gmail.com wrote: My need is 23 decimal digits of precision. We work on complex payout calculation engine where in formula outputs are quite large numbers and clients do not want to round off. If you're working with floating-point numbers, you will get roundoff [1]. Integer arithmetic really is how big banks work. That way you don't have to test for rounding problems and never get accused of Salami Slicing. If you really need money precision you'll be working in integers representing paise, pennies, halalas, whatever. You'd have asked for 23 digit integers, not 23,10. Since the abandonment of the Lira, no decimal country currency has needed anything more than three places of decimals for manipulation. So for 23 digits of accuracy you seem to have a requirement to manipulate 100,000,000,000,000,000,000 units of currency with perfect accuracy. Even the World Bank Group doesn't need that. And I don't think the total wealth of any country in its own currency requires that many digits. I've worked with international financial organisations and we never did anything that needed 23 digits of precision, and that includes complicated cumulative interest calculations and those unbelievable asset value predictions that require integration and antilogs. In summary, if you need ultimate precision, use integers. If not, use 64-bit IEEE-571 like everyone else does without being sued. If you somehow really need 23,10 maths, then you're going to have to write your own mathematical library anyway, because I'm not aware of any usable libraries which actually support 23,10 outside the world of physics. Simon. [1] This is a little hand-waving but only a little. I'm only mentioning that because I don't want someone in the industry to dig this up and use it against me. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite] Support for 23,10 Precision number format
On Thu, Nov 13, 2014 at 3:38 PM, Simon Slavin slav...@bigfraud.org wrote: On 13 Nov 2014, at 1:01pm, Dinesh Navsupe dinesh.navs...@gmail.com wrote: My need is 23 decimal digits of precision. We work on complex payout calculation engine where in formula outputs are quite large numbers and clients do not want to round off. [...]. So for 23 digits of accuracy you seem to have a requirement to manipulate 100,000,000,000,000,000,000 Assuming he means Oracle's NUMBER(23, 10), and given [1], that's more 9,999,999,999,999.99 i.e. just under 10 trillion max, with 10 decimal digits accuracy, and not 100 million trillion. FWIW. --DD [1] http://www.orafaq.com/wiki/Number ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite] Support for 23,10 Precision number format
You are right Dominique. I mean Oracle's NUMBER(23, 10), and given [1], that's more 9,999,999,999,999.99 Thanks. On Thu, Nov 13, 2014 at 9:14 PM, Dominique Devienne ddevie...@gmail.com wrote: On Thu, Nov 13, 2014 at 3:38 PM, Simon Slavin slav...@bigfraud.org wrote: On 13 Nov 2014, at 1:01pm, Dinesh Navsupe dinesh.navs...@gmail.com wrote: My need is 23 decimal digits of precision. We work on complex payout calculation engine where in formula outputs are quite large numbers and clients do not want to round off. [...]. So for 23 digits of accuracy you seem to have a requirement to manipulate 100,000,000,000,000,000,000 Assuming he means Oracle's NUMBER(23, 10), and given [1], that's more 9,999,999,999,999.99 i.e. just under 10 trillion max, with 10 decimal digits accuracy, and not 100 million trillion. FWIW. --DD [1] http://www.orafaq.com/wiki/Number ___ 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] [SQLite] Support for 23,10 Precision number format
On Thu, Nov 13, 2014 at 4:50 PM, Dinesh Navsupe dinesh.navs...@gmail.com wrote: I mean Oracle's NUMBER(23, 10), and given [1], that's more My need is 23 decimal digits of precision. We work on complex payout calculation engine where in formula outputs are quite large numbers and clients do not want to round off. The first answer to this thread might be helpful (but also probably not what you want to hear): http://sqlite.1065341.n5.nabble.com/How-point-numbers-are-they-stored-in-sqlite-td35739.html -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do. -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite] Support for 23,10 Precision number format
On 13 Nov 2014, at 3:44pm, Dominique Devienne ddevie...@gmail.com wrote: On Thu, Nov 13, 2014 at 3:38 PM, Simon Slavin slav...@bigfraud.org wrote: 100,000,000,000,000,000,000 Assuming he means Oracle's NUMBER(23, 10), and given [1], that's more 9,999,999,999,999.99 i.e. just under 10 trillion max, with 10 decimal digits accuracy, and not 100 million trillion. But he's using the field to store an amount of money in. So why ask for anything with ten places after the decimal point ? No genuine currency requires more than three places. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite] Support for 23,10 Precision number format
On 2014/11/13 19:06, Simon Slavin wrote: On Thu, Nov 13, 2014 at 3:38 PM, Simon Slavin slav...@bigfraud.org wrote: 100,000,000,000,000,000,000 But he's using the field to store an amount of money in. So why ask for anything with ten places after the decimal point ? No genuine currency requires more than three places. He mightn't be storing the actual money, but the working factors and figures used in the formulas seeding the money calculations, conversions, etc. which may have a bit higher accuracy required. Though if this is the case, I would suggest getting back to your original suggestion of saving the money as integer cents (or milli-dollars if you like) and storing the factors and figures as good old 64 bit IEEE floats which will give you up to 15 digits after the decimal point accurately, twice the required accuracy - not to mention the luxury of being able to access both types natively in the api /and/ represent it easily in human-readable format in data dumps. By the way, my Oracle friends should intersect here if need be, but I believe the oracle method of /decimal(n,m)/ is simply a representation directive and constraint, there is no native datatype that actually stores or communicates such a value. Oracle stores it internally in a very specific arrangement of bytes and you need to still interpret it in your software. Nor do I think the Oracle SQL engine would be able to do (without any add-on modules): SELECT (B.Money*B.Factor) FROM BigMoney B; from said table with Money and Factor both as decimal(38,6) each containing 30 decimals - or would it? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite] Support for 23,10 Precision number format
IIRC there was a programmer working for a bank that managed to siphon off the sub-unit fractions that the interest calculating software generated (how much interest is owed for $1 at 0,25% p.a. for 2 days*) onto his own account and temporarily got rich quick. $1 * 0,25% = $25 (interest for 1 year) $25 * 2 / 360 = $0,1389 (interest for 2 days) This is split into 13 cents for the client and nearly 0,9 cents that the bank keeps -Ursprüngliche Nachricht- Von: Simon Slavin [mailto:slav...@bigfraud.org] Gesendet: Donnerstag, 13. November 2014 18:07 An: General Discussion of SQLite Database Betreff: Re: [sqlite] [SQLite] Support for 23,10 Precision number format On 13 Nov 2014, at 3:44pm, Dominique Devienne ddevie...@gmail.com wrote: On Thu, Nov 13, 2014 at 3:38 PM, Simon Slavin slav...@bigfraud.org wrote: 100,000,000,000,000,000,000 Assuming he means Oracle's NUMBER(23, 10), and given [1], that's more 9,999,999,999,999.99 i.e. just under 10 trillion max, with 10 decimal digits accuracy, and not 100 million trillion. But he's using the field to store an amount of money in. So why ask for anything with ten places after the decimal point ? No genuine currency requires more than three places. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite bug report
The following SQL produces an incorrect result with sqlite-3.8.7.1:
CREATE TABLE A(
symbol TEXT,
type TEXT
);
INSERT INTO A VALUES('ABCDEFG','chars');
INSERT INTO A VALUES('1234567890','num');
CREATE TABLE B(
chars TEXT,
num TEXT
);
CREATE TABLE IF NOT EXISTS C AS
SELECT A.symbol AS symbol,A.type,
CASE A.type
WHEN 'chars' THEN A.symbol
WHEN 'num' THEN B.chars
ELSE NULL
END AS chars
FROM A LEFT OUTER JOIN B ON A.type='num' AND B.num=A.symbol;
SELECT * FROM C;
with 3.8.7.1:
sqlite SELECT * FROM C;
ABCDEFG|chars|ABCDEFG
1234567890|num|1234567
with 3.8.6:
sqlite SELECT * FROM C;
ABCDEFG|chars|ABCDEFG
1234567890|num|
--
This message contains confidential information and is intended only for the
individual named. If you are not the named addressee, you should not
disseminate, distribute, alter or copy this e-mail. Please notify the
sender immediately by e-mail if you have received this e-mail by mistake
and delete this e-mail from your system. E-mail transmissions cannot be
guaranteed to be secure or without error as information could be
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The
sender, therefore, does not accept liability for any errors or omissions in
the contents of this message which arise during or as a result of e-mail
transmission. If verification is required, please request a hard-copy
version. This message is provided for information purposes and should not
be construed as a solicitation or offer to buy or sell any securities or
related financial instruments in any jurisdiction.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bug report
This is https://www.sqlite.org/src/info/094d39a4c95ee4 which has been fixed
in trunk and will be fixed in 3.8.7.2.
On Thu, Nov 13, 2014 at 1:05 PM, Hinrichsen, John jhinrich...@c10p.com
wrote:
The following SQL produces an incorrect result with sqlite-3.8.7.1:
CREATE TABLE A(
symbol TEXT,
type TEXT
);
INSERT INTO A VALUES('ABCDEFG','chars');
INSERT INTO A VALUES('1234567890','num');
CREATE TABLE B(
chars TEXT,
num TEXT
);
CREATE TABLE IF NOT EXISTS C AS
SELECT A.symbol AS symbol,A.type,
CASE A.type
WHEN 'chars' THEN A.symbol
WHEN 'num' THEN B.chars
ELSE NULL
END AS chars
FROM A LEFT OUTER JOIN B ON A.type='num' AND B.num=A.symbol;
SELECT * FROM C;
with 3.8.7.1:
sqlite SELECT * FROM C;
ABCDEFG|chars|ABCDEFG
1234567890|num|1234567
with 3.8.6:
sqlite SELECT * FROM C;
ABCDEFG|chars|ABCDEFG
1234567890|num|
--
This message contains confidential information and is intended only for the
individual named. If you are not the named addressee, you should not
disseminate, distribute, alter or copy this e-mail. Please notify the
sender immediately by e-mail if you have received this e-mail by mistake
and delete this e-mail from your system. E-mail transmissions cannot be
guaranteed to be secure or without error as information could be
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The
sender, therefore, does not accept liability for any errors or omissions in
the contents of this message which arise during or as a result of e-mail
transmission. If verification is required, please request a hard-copy
version. This message is provided for information purposes and should not
be construed as a solicitation or offer to buy or sell any securities or
related financial instruments in any jurisdiction.
___
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
