[sqlite] Problem with VACUUM feature
Hi All, I am using sqlite-amalgamation-3_7_3.zip source in my project. I tested VACUUM command on a DB file which has lot of holes(fragmentation caused by deletion of random records ) but the source file size does not change. Instead sqlite applies the vaccum command and writes data into new temporary file prefixed by "etilqs_". Input:MyDb.db with size 23KB (has lot of empty pages caused due to delete operation) *Expected OutPut: after applying Vacuum command, should be MyDb.db with reduced file size of 13KB.* *Actual output: MyDb.db remains size 23KB(size not changes from original) and creates temporary file etilqs_Hm4RUi6JPXcMZ17 whose data is same as MyDb.db but the size is reduced to 13KB* I applied the VACUUM command on MyDb.db using sqlite3.exe(shell based commands interpreter) and it applies to the MyDb.db whose size beccomes 13KB after the command completion. ? I have ported sqlite-amalgamation-3_7_3.zip on some X platform using WINCE configuration. Everything else seem to work fine except this strange behaviour of VACUUM feature. Is this a bug or i am doing something wrong? Why sqlite writing data of MyDb.db in to temporary file and applying VACUUM on temporary file instead of original file? Please let me know if any of you come across such scenario and have solution for this issue. Thanks a lot. ~Sudha ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected
On February 15, 2011, Black, Michael (IS) wrote: > I'll give you another failure point that most people never see or think of. > > I used to manage numerous Linux systems with RAID-5. One time I had a > drive fail, the spare kicked in, and then during the rebuild a 2nd drive > failed...hosing the RAID (i.e. two failed disks). > > The problem was...normal disk access/backup only scanned the in-use blocks. > The RAID resync scanned the entire disk which had never been done. I think this is one reason mdraid on linux by default will run a resync regularly. > After that I put in a utility that did a nightly "dd if=/dev/md0 > >/dev/null" job to force a scan of the entire disk set. > > This is one reason why they invented RAID6. > > There's just so many ways to fail...sigh... > > > Michael D. Black > Senior Scientist > NG Information Systems > Advanced Analytics Directorate > > > > > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Simon Slavin [slav...@bigfraud.org] Sent: Monday, February 14, > 2011 5:04 PM > To: General Discussion of SQLite Database > Subject: EXT :Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected > > On 14 Feb 2011, at 8:50pm, Black, Michael (IS) wrote: > > And if you want to talk about data reliability...BACK UP YOUR DATA. > > And keep the backups off-site. And once a year try to actually restore one > of them. I've earned /lots/ of money from companies with backup systems > that claimed to work fine but were either faulty, or being used to backup > to media with low-level corruption. > > If there was any money in it I'd write a book, but there isn't. Companies > prefer to do it wrong and then hire a consultant after it fails. > > On 14 Feb 2011, at 9:13pm, Jim Wilcoxson wrote: > > I think what would be useful is for drives to have 3 settings: > > > > a) the drive can do whatever it wants to optimize performance > > b) the drive can reorder writes, but not across a sync (ACI, but no D) > > c) the drive has to respect all syncs (ACID) > > > > If the drive mgfr wants to make a) the default, that's fine, but there > > should be an easy way to request the other 2 from an application. > > Users are not usually sophisticated enough to know when it's okay to > > cache writes or not okay. For my use of SQLite, b) would probably be > > fine, but a) apparently is not since it corrupts databases. > > This is a job for someone who wants to contact lots of manufacturers and > ask if they provide models of their drives which conform. Some do, > because they want to sell their drives for use in servers. Often these > drives are physically identical to their mass-market drives, but they have > fewer bad blocks, and have jumpers in a different place or different > firmware. > > > Michael mentioned doing backups and forgetting about all of this, but > > many people are using USB drives to store their backups. So there is > > a bit of a Catch-22 here. Almost all modern-day filesystems are going > > to depend on some kind of journalling to prevent corrupted file > > systems, and as far I as know, journalling filesystems depend on syncs > > to maintain FS consistency. > > If the data is backed up when SQLite has the files closed, things are fine. > The really hard part of this comes when you're working with systems that > must be live at all times. Which is why you'll never see a bank use > SQLite to maintain its live transaction system. The big DBMSs have > systems inside themselves that allow the backing-up of an active > ever-changing database. > > 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 -- Thomas Fjellstrom tfjellst...@strangesoft.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] oracle compatibility mode
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/22/2011 07:17 PM, Phil Oertel wrote: > Sorry for being unclear, I'm referring to the ability to emulate > oracle-specific features and syntax, like ROWNUM for example. What else? ROWNUM seems spectacularly useless! You should be able to use OFFSET/LIMIT to get the same effect. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEUEARECAAYFAk1kiM8ACgkQmOOfHg372QRNGgCYwmNSsuv/Wlx8g2jto0HEbk3g jACePLS3PQdxUGl/5LY7qujePXLpv0Y= =wV+/ -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VFS
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/22/2011 02:39 PM, Robert Hairgrove wrote: > On Tue, 2011-02-22 at 08:46 -0800, Roger Binns wrote: >> What you think you are seeing is not happening. The documentation is >> correct. > > OK ... but what about that which the GDB debugger is seeing? There are two possibilities: 1 - The SQLite code deliberately (against documented behaviour) goes around copying VFS structures, and sneaks in behind the scenes to zero out some members all the while craftily hiding how this is done so that searching the source code won't find it. It also hides the previous values so that extension loading still works. 2 - When layers of third party code are compiled, linked, some static, some dynamic, and combined in a process, you and/or gdb get confused. For Linux the only way the default VFS ends up with zero for the dl functions is if it is compiled with SQLITE_OMIT_LOAD_EXTENSION (which incidentally has often been the default for many maintainers). Other things I have seen are multiple copies of SQLite getting loaded into the same process. This is especially an issue on Mac where CoreData picks up the system copy. The copies all coexist but will confuse you. I have been caught out by shared library unloading and then reloading. Because Linux uses ASLR the reload happens at a different address but any registrations made during the first load point to the wrong addresses. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1khwwACgkQmOOfHg372QRvkACgvIRRBiQV3k9suHC38EhYEKQ8 UmsAnRmXUyam/B2FfiTiS3/cEGyLv/rH =1gl3 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] apostrophes in strings...
The suggestion apparently derives from comments in attach.c For example: http://gears.googlecode.com/svn/trunk/third_party/sqlite_google/src/attach.c /* ** An SQL user-function registered to do the work of an ATTACH statement. The ** three arguments to the function come directly from an attach statement: ** ** ATTACH DATABASE x AS y KEY z ** ** SELECT sqlite_attach(x, y, z) ** ** If the optional "KEY z" syntax is omitted, an SQL NULL is passed as the ** third argument. */ -- -- -- --ΞΞ-- ô¿ô¬ K e V i N /¯\ On Tue, Feb 22, 2011 at 9:35 PM, Sam Carleton wrote: > y KEY z > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] oracle compatibility mode
Sorry for being unclear, I'm referring to the ability to emulate oracle-specific features and syntax, like ROWNUM for example. On Feb 22, 2011 6:44 PM, "Pavel Ivanov" wrote: ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] oracle compatibility mode
Could you please explain what is "Oracle compatibility mode"? And how can anyone make an attempt to use it for SQLite if SQLite doesn't have such feature? Pavel On Tue, Feb 22, 2011 at 9:28 PM, Phil Oertel wrote: > Hi sqliters, > > After a recent failed attempt to use SQLite as an in-memory fake Oracle for > some of my tests, I'm curious whether anyone has attempted an Oracle > compatibility mode for SQLite. H2 and others have this tremendously useful > feature, but there doesn't seem to be anything available for those not > running on a JVM. > > Has anyone attempted to build such a feature for SQLite? Has there been any > assessment of the difficulty or major blocking issues? Most importantly, is > this a feature that the core team wants at all? > > Thanks in advance for any information, > Phil > ___ > 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] apostrophes in strings...
On Mon, Feb 21, 2011 at 9:42 AM, Sam Carleton wrote: > On Sun, Feb 20, 2011 at 4:11 PM, Scott Hess wrote: > >> You can also convert: >> ATTACH DATABASE x AS y KEY z >> to: >> SELECT sqlite_attach(x, y, z) >> where the parameters can be turned into bind arguments. Then embedded >> quotes won't be an issue. >> > I am looking for some documentation on this notation, which I cannot find. The reason is the KEY part, I don't know what that is and I am not currenting use that part, so I am looking to figure out how to drop it. Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] oracle compatibility mode
Hi sqliters, After a recent failed attempt to use SQLite as an in-memory fake Oracle for some of my tests, I'm curious whether anyone has attempted an Oracle compatibility mode for SQLite. H2 and others have this tremendously useful feature, but there doesn't seem to be anything available for those not running on a JVM. Has anyone attempted to build such a feature for SQLite? Has there been any assessment of the difficulty or major blocking issues? Most importantly, is this a feature that the core team wants at all? Thanks in advance for any information, Phil ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VFS
On Tue, 2011-02-22 at 08:46 -0800, Roger Binns wrote:
> On 02/22/2011 05:29 AM, Robert Hairgrove wrote:
> > I'm trying to understand how the VFS implementation works.
>
> What you think you are seeing is not happening. The documentation is correct.
OK ... but what about that which the GDB debugger is seeing?
> > However, if I open a database and inspect the VFS contained in the sqlite3*,
>
> That should just point to the VFS used. There is no copying or modification.
Or so says the documentation...
Maybe some more details will help here:
The OS is Linux Ubuntu 10.04 LTS, AKA "Lucid Lynx" running on a Dell
Inspiron 1420N notebook (and Dell supplies the drivers etc. for this
distro of Linux). GCC version is 4.4.3.
SQLite version is 3.6.19 (I know it's not the latest and greatest, but
this is what is supplied together with the Qt sources... these are
version 4.7.1 -- which is pretty much up-to-date, but not cutting edge).
I compiled the Qt libraries from source myself and built SQLite
statically into Qt, as opposed to a plug-in. The SQLite sources are all
in the amalgamation file "sqlite.c".
I created a little console project in QtCreator to test the SQLite VFS
functionality. I had to add "sqlite3.c" to the project because I was
getting linker errors when trying to call some of the SQLite API
functions, such as "sqlite3_vfs_find" ... (hmmm...seems that these
symbols aren't exported from the Qt shared libraries).
Obviously, SOMETHING is resetting these pointers. Here is a snippet from
the test code I wrote. It should compile and run OK if you have a newer
version of Qt installed on your system. Here is my Qt .pro file:
// my Qt project file: ===
QT += core sql
INCLUDEPATH += \
{wherever the Qt sources are...}/src/3rdparty/sqlite
SOURCES += main.cpp \
{wherever the Qt sources are...}/src/3rdparty/sqlite/sqlite3.c
HEADERS += \
{wherever the Qt sources are...}/src/3rdparty/sqlite/sqlite3.h
// end of Qt project file ===
And here is the source code of my test app in "main.cpp":
// BEGIN CODE:
#include
#include
#include
#include
void showVFS(const sqlite3_vfs * const &);
int main(int argc, char *argv[])
{
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE","default_db");
sqlite3 * pSqliteDb = 0;
//
// Using an on-disk database seems to give the exact same results:
// db.setDatabaseName("/home/bob/code/SQLite_Test_VFS/test.db");
//
db.setDatabaseName(":memory:");
if (db.open()) {
//---
// Some of the following lines of code are copied and
// pasted from the Qt docs:
//---
QVariant v = db.driver()->handle();
if (v.isValid() && qstrcmp(v.typeName(), "sqlite3*")==0) {
// v.data() returns a pointer to the handle...
//
// Actually, it seems to return either int
// or void* ... (see below):
//
sqlite3 *pSqliteDb = *static_cast(v.data());
if (pSqliteDb) {
//
// Interesting that using static_cast<> does not work below;
// probably because QSqlDriver::handle() returns void* (???)
// although the Qt docs AND the sources say differently:
//
sqlite3_vfs * pvfs = *reinterpret_cast(pSqliteDb);
//
// The line below gives a VFS with non-NULL values
// for the struct members under discussion:
//
// sqlite3_vfs * pvfs = sqlite3_vfs_find(0);
//
if (pvfs) {
showVFS(pvfs);
}
} else {
std::cout << "No VFS handle!" << std::endl;
}
}
}
return 0;
}
void showVFS(const sqlite3_vfs * const &pVfs)
{
if (pVfs->zName) {
std::cout << "=\n";
std::cout << "Name of VFS:\t" << pVfs->zName << std::endl;
#ifdef SQLITE_OMIT_LOAD_EXTENSION
std::cout << "SQLITE_OMIT_LOAD_EXTENSION = true" << std::endl;
#endif
std::cout << "=\n"
Re: [sqlite] Asymmetric keys encryption
On Tue, Feb 22, 2011 at 9:07 AM, Philip Graham Willoughby wrote: > > On 22 Feb 2011, at 15:41, Max Vlasov wrote: > > The obvious solution is public-key cryptography. The question is about > > different ways how it could be implemented with sqlite. The requirement for > > this system is that it should operate in two modes: > > - insert-only when no reading operation is used. This mode uses public key > > to store the data > > - full-mode when the private key is supplied and any operation is possible. > > It might work, but it wouldn't be quick. Public-key cryptography is very > slow. There are benchmarks on this page > (http://www.cryptopp.com/benchmarks.html) but most of what you need to know > is in the choice of scale: AES and other shared key systems are in > cycles-per-byte and RSA/friends are in megacycles-per-operation. The simple answer to 'public-key' cryptography is very slow', so to not encrypt the complete text. Instead you generate a random key for one of the good [and fast] symmetric encryption implementations, encrypt the complete text with that, and encrypt only the symmetric key using public-key encryption. [.. snip .. snip ] -- [another] Phil ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite GUI comparison
Hi Tom, if you do not have a command line tool in one of the software package you can do two things: 1) try to create an FTS or RTree table. It will fail if the extensions are not supported. 2) create a database having all to be tested extension and then issue a SELECT * statement on the table In both cases you will get an error message. Hartwig Am 22.02.2011 um 00:16 schrieb BareFeetWare: > On 22/02/2011, at 4:31 AM, skywind mailing lists wrote: > >> "Supports SQLite extension" would be an accurate feature description. And in >> the cell (value) I suggest to put - if supported - FTS2, FTS3, RTree etc., >> otherwise a "-". A yes or no is insufficient because some support RTree but >> not FTS and vice versa. > > OK, that sounds good. I'll probably use "no" or "none" if no extension is > supported. > > Can anyone please tell me what should go in this cell for any SQLite GUI app > they know (ie either FTS2, FTS3, RTree or none). Or how can I easily test > this feature? > > Thanks, > Tom > BareFeetWare > > -- > Comparison of SQLite GUI tools: > http://www.barefeetware.com/sqlite/compare/?ml > > ___ > 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] sqlite3_busy_handler
I dont' know the details of the busy handler. Not clear to me that it should sequentialize the requests. Perhaps you're better off just using a flag that you could check between your commit;begin so that if there's a request in the queue you go process it before continuing. commit; if item_in_queue go_process begin; Did you eliminate the idea of using WAL mode? Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Frank Chang [frank_chan...@hotmail.com] Sent: Tuesday, February 22, 2011 2:44 PM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] sqlite3_busy_handler I wanted to thank Michael D. Black and Simon Slavin for replying to my question. I was wondering how long the sqlite_busy_handler should sleep for before SQLite tries to access the datbase again. Our chief engineer was wondering whether the writing function could set an event when the write finished. The purpose of this event would be to notify the sqlite_busy_handler that the write finished so that the sqlite_busy_handler could use something like WaitForSingleObject before returning from the callback function. Thank you. ___ 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] Asymmetric keys encryption
On Tue, Feb 22, 2011 at 7:07 PM, Philip Graham Willoughby < phil.willoug...@strawberrycat.com> wrote: > On 22 Feb 2011, at 15:41, Max Vlasov wrote: > > The obvious solution is public-key cryptography. The question is about > > different ways how it could be implemented with sqlite. The requirement > for > > this system is that it should operate in two modes: > > - insert-only when no reading operation is used. This mode uses public > key > > to store the data > > - full-mode when the private key is supplied and any operation is > possible. > > It might work, but it wouldn't be quick. Public-key cryptography is very > slow. There are benchmarks on this page ( > http://www.cryptopp.com/benchmarks.html) but most of what you need to know > is in the choice of scale: AES and other shared key systems are in > cycles-per-byte and RSA/friends are in megacycles-per-operation. > > Who are you trying to hide the data from? If it's someone with access to > the account running the program on the machine on which it's running they > could fairly easily trap the appropriate entry point in the SQLite code > using the platforms debugging facilities and log all the data you're trying > to write before it's written. > Phil, as an example, it might be a software that tracks user activity on the machine, which process was activated, how much time, which caption was active and later some software can calculate some statistics based on this. Users of such software at least would be willing to hide this big base that contain all their recent activity from occasional observer of thief . To implement this with general encryption means that he or she would enter the key when the program starts (=when the OS starts). It may be tedious. Public key cryptography would help to keep the user from entering the password every time while also keeping the data safe from decrypting. Another example is a note-taking program that doesn't require to enter the password if you just want to write something and not read. It's like general rsa but you're writing a message not to someone, but to "secret yourself" :) I suppose users would wonder why such feature is useful in the first place. A couple of cases: - You're getting used to getting something written fast (save time usually spend on entering the password). - You're partly safe from internal and external unsafe environment (someone stays behind you or your program is executed on another computer where a keylogging program can be active). There might be other cases... Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_busy_handler
I wanted to thank Michael D. Black and Simon Slavin for replying to my question. I was wondering how long the sqlite_busy_handler should sleep for before SQLite tries to access the datbase again. Our chief engineer was wondering whether the writing function could set an event when the write finished. The purpose of this event would be to notify the sqlite_busy_handler that the write finished so that the sqlite_busy_handler could use something like WaitForSingleObject before returning from the callback function. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite GUI comparison
On Tue, 22 Feb 2011 10:16:20 +1100, BareFeetWare wrote: >On 22/02/2011, at 4:31 AM, skywind mailing lists wrote: > >> "Supports SQLite extension" would be an accurate feature description. And in >> the cell (value) I suggest to put - if supported - FTS2, FTS3, RTree etc., >> otherwise a "-". A yes or no is insufficient because some support RTree but >> not FTS and vice versa. > >OK, that sounds good. I'll probably use "no" or "none" if no extension is >supported. > >Can anyone please tell me what should go in this cell for any > SQLite GUI app they know (ie either FTS2, FTS3, RTree or none). > Or how can I easily test this feature? Perhaps http://www.sqlite.org/pragma.html#pragma_compile_options sqlite3 command line tool: sqlite> select sqlite_version(); 3.6.23.1 sqlite> PRAGMA compile_options; ENABLE_FTS3 ENABLE_RTREE TEMP_STORE=1 THREADSAFE=1 >Thanks, >Tom >BareFeetWare -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Asymmetric keys encryption
Public-key encryption is not designed as a method to encrypt data, it is meant as a means to prove a digital signature and to prevent man in the middle attacks. Web servers do use public keys but only to encrypt the symmetric key that is used to encrypt the actual data traffic. You will want to look at more of an AES based solution for this. On Feb 22, 2011, at 10:41 AM, Max Vlasov wrote: > Hi, > > recently I was thinking about a system when logs about something are written > encrypted without interaction with the user, but for reading the contents > one would need the key. > > The obvious solution is public-key cryptography. The question is about > different ways how it could be implemented with sqlite. The requirement for > this system is that it should operate in two modes: > - insert-only when no reading operation is used. This mode uses public key > to store the data > - full-mode when the private key is supplied and any operation is possible. > > Random thoughts... VFS layer is not an option since due to virtual approach > to the data stored, sqlite sometimes will need to read data so we losing > "insert-only" mode. The easiest way seemed to be by using a blob field > encoded and decoded on the user side. But in this case one loses the > flexibility of sqlite (for example querying against particular fields). > Another option is virtual tables that is probably can solve the task but > since I never implemented one, I'm not sure. > > The questions: > - Are there other ways you see in implementing this? > - Are virtual tables is flexible enough to fit the requirements for > insert-mode? In other word, if the only operation is INSERT and no indexes > are defined, can one be sure there will be no reading request for a > particular table? Probably it's not possible if we remember how important > rowid is, but I'm ready at least to make rowid public and not encrypted. > - Is there something I > > Thanks in advance, > > Max Vlasov > ___ > 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] VFS
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/22/2011 05:29 AM, Robert Hairgrove wrote: > I'm trying to understand how the VFS implementation works. What you think you are seeing is not happening. The documentation is correct. > However, if I open a database and inspect the VFS contained in the sqlite3*, That should just point to the VFS used. There is no copying or modification. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1j6FkACgkQmOOfHg372QTR8gCgsX+dYtQFkeYRZDskwKoQRXdc eGUAoJqhFJYdkOp40Usuujws3KSFMT2m =kYzg -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Asymmetric keys encryption
On 22 Feb 2011, at 15:41, Max Vlasov wrote: > The obvious solution is public-key cryptography. The question is about > different ways how it could be implemented with sqlite. The requirement for > this system is that it should operate in two modes: > - insert-only when no reading operation is used. This mode uses public key > to store the data > - full-mode when the private key is supplied and any operation is possible. It might work, but it wouldn't be quick. Public-key cryptography is very slow. There are benchmarks on this page (http://www.cryptopp.com/benchmarks.html) but most of what you need to know is in the choice of scale: AES and other shared key systems are in cycles-per-byte and RSA/friends are in megacycles-per-operation. Who are you trying to hide the data from? If it's someone with access to the account running the program on the machine on which it's running they could fairly easily trap the appropriate entry point in the SQLite code using the platforms debugging facilities and log all the data you're trying to write before it's written. If it's someone less determined/knowledgeable than that then a simple xor-all-bytes-with-0x42-obfuscation for input and output would work just as well, be considerably faster, and could be implemented in a VFS plugin. Best Regards, Phil Willoughby -- Managing Director, StrawberryCat Limited StrawberryCat Limited is registered in England and Wales with Company No. 7234809. The registered office address of StrawberryCat Limited is: 107 Morgan Le Fay Drive Eastleigh SO53 4JH ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Asymmetric keys encryption
Hi, recently I was thinking about a system when logs about something are written encrypted without interaction with the user, but for reading the contents one would need the key. The obvious solution is public-key cryptography. The question is about different ways how it could be implemented with sqlite. The requirement for this system is that it should operate in two modes: - insert-only when no reading operation is used. This mode uses public key to store the data - full-mode when the private key is supplied and any operation is possible. Random thoughts... VFS layer is not an option since due to virtual approach to the data stored, sqlite sometimes will need to read data so we losing "insert-only" mode. The easiest way seemed to be by using a blob field encoded and decoded on the user side. But in this case one loses the flexibility of sqlite (for example querying against particular fields). Another option is virtual tables that is probably can solve the task but since I never implemented one, I'm not sure. The questions: - Are there other ways you see in implementing this? - Are virtual tables is flexible enough to fit the requirements for insert-mode? In other word, if the only operation is INSERT and no indexes are defined, can one be sure there will be no reading request for a particular table? Probably it's not possible if we remember how important rowid is, but I'm ready at least to make rowid public and not encrypted. - Is there something I Thanks in advance, Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Auto-grow setting?
I don't know what's the best value for chunk size. I'm not even sure that it's useful to set it to any value at all. So let your test results guide you. The only thought I have is the chunk size should be a multiple of page size (don't know if SQLite's code rounds up to such multiple internally). Pavel On Tue, Feb 22, 2011 at 10:14 AM, Sven L wrote: > > What I meant is this: > Database size = 1 MB. When opening connection, set chunk to ~100 kB. > Database size = 100 MB. When opening connection, set chunk to ~10 MB. > Database size = 1 GB. When opening connection, set chunk to ~100 MB. > > I guess SQLITE_FCNTL_CHUNK_SIZE should be a 2^n value, so this gives me this > algorithm: > > unsigned int v = databaseSizeBytes / 10; > // Round up to the next highest power of 2 (well-known bit trick ;) > --v; > v |= v >> 1; > v |= v >> 2; > v |= v >> 4; > v |= v >> 8; > v |= v >> 16; > ++v; > > // A minimum of 32 kB is desirable? > chunkSize = max(32768, v); > > > Thoughts? > > >> From: paiva...@gmail.com >> Date: Tue, 22 Feb 2011 10:01:03 -0500 >> Subject: Re: [sqlite] Auto-grow setting? >> To: sqlite-users@sqlite.org >> CC: larvpo...@hotmail.se >> >> Please reply to the list, not to me only. >> >> It's impossible to set chunk size to percentage of the database size, >> you can only set a constant value. >> >> >> Pavel >> >> On Tue, Feb 22, 2011 at 9:13 AM, Sven L wrote: >> > Thanks a lot! :D >> > >> > What do you think of setting the chunk size to approximately 10% of the >> > database file size? Or is it better to use a constant? >> > >> >> From: paiva...@gmail.com >> >> Date: Tue, 22 Feb 2011 08:30:54 -0500 >> >> Subject: Re: [sqlite] Auto-grow setting? >> >> To: sqlite-users@sqlite.org >> >> CC: larvpo...@hotmail.se >> >> >> >> Is SQLITE_FCNTL_CHUNK_SIZE what you are looking for? See more >> >> information about it here: >> >> http://www.sqlite.org/c3ref/c_fcntl_chunk_size.html. Notice that this >> >> feature appeared only in recent version of SQLite, so if you have some >> >> earlier version you won't be able to control it and SQLite will >> >> grow/shrink database page-by-page (maximum page size is 32Kb). >> >> >> >> >> >> Pavel >> >> >> >> On Tue, Feb 22, 2011 at 7:28 AM, Sven L wrote: >> >> > >> >> > Can't seem to find a setting to control how the database file grows when >> >> > full. Is there such a setting? >> >> > It looks like the file increases by some < 100 kB when it is full. I >> >> > want to change this to around 10 MB (or even more) to avoid file >> >> > fragmentation. >> >> > >> >> > Any ideas? >> >> > >> >> > 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Auto-grow setting?
What I meant is this: Database size = 1 MB. When opening connection, set chunk to ~100 kB. Database size = 100 MB. When opening connection, set chunk to ~10 MB. Database size = 1 GB. When opening connection, set chunk to ~100 MB. I guess SQLITE_FCNTL_CHUNK_SIZE should be a 2^n value, so this gives me this algorithm: unsigned int v = databaseSizeBytes / 10; // Round up to the next highest power of 2 (well-known bit trick ;) --v; v |= v >> 1; v |= v >> 2; v |= v >> 4; v |= v >> 8; v |= v >> 16; ++v; // A minimum of 32 kB is desirable? chunkSize = max(32768, v); Thoughts? > From: paiva...@gmail.com > Date: Tue, 22 Feb 2011 10:01:03 -0500 > Subject: Re: [sqlite] Auto-grow setting? > To: sqlite-users@sqlite.org > CC: larvpo...@hotmail.se > > Please reply to the list, not to me only. > > It's impossible to set chunk size to percentage of the database size, > you can only set a constant value. > > > Pavel > > On Tue, Feb 22, 2011 at 9:13 AM, Sven L wrote: > > Thanks a lot! :D > > > > What do you think of setting the chunk size to approximately 10% of the > > database file size? Or is it better to use a constant? > > > >> From: paiva...@gmail.com > >> Date: Tue, 22 Feb 2011 08:30:54 -0500 > >> Subject: Re: [sqlite] Auto-grow setting? > >> To: sqlite-users@sqlite.org > >> CC: larvpo...@hotmail.se > >> > >> Is SQLITE_FCNTL_CHUNK_SIZE what you are looking for? See more > >> information about it here: > >> http://www.sqlite.org/c3ref/c_fcntl_chunk_size.html. Notice that this > >> feature appeared only in recent version of SQLite, so if you have some > >> earlier version you won't be able to control it and SQLite will > >> grow/shrink database page-by-page (maximum page size is 32Kb). > >> > >> > >> Pavel > >> > >> On Tue, Feb 22, 2011 at 7:28 AM, Sven L wrote: > >> > > >> > Can't seem to find a setting to control how the database file grows when > >> > full. Is there such a setting? > >> > It looks like the file increases by some < 100 kB when it is full. I > >> > want to change this to around 10 MB (or even more) to avoid file > >> > fragmentation. > >> > > >> > Any ideas? > >> > > >> > 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] SELECT (string field) returns part of contents, then asterisks
On Tue, Feb 22, 2011 at 04:03:22PM +0100, Haldrup Office wrote: > Hello list, > > I'm in the process of writing a little interface tool for notes and > adress databases from an iPad. > > Using MS Word VBA and SQLite3_StdCall.dll. > My query looks quite simply put: > SELECT ROWID,creation_date,title FROM Note > > and it runs fine and returns w/o problems. > > When I iterate through it, though, and I try to read a long note (saved > in field 'title') I happen to get some 2700 characters back plus a > sequence of asterisks. Probably a limitation of your "MS Word VBA and SQLite3_StdCall.dll" > > Am I missing something here? > > Thank you for your efforts, > > /T > > -- > Med venlig hilsen, > > Thomas Besand > > > ___ > 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] SELECT (string field) returns part of contents, then asterisks
On 22 Feb 2011, at 3:03pm, Haldrup Office wrote: > Using MS Word VBA and SQLite3_StdCall.dll. > My query looks quite simply put: > SELECT ROWID,creation_date,title FROM Note > > and it runs fine and returns w/o problems. > > When I iterate through it, though, and I try to read a long note (saved > in field 'title') I happen to get some 2700 characters back plus a > sequence of asterisks. Use the command-line tool to look at the same data: http://www.sqlite.org/sqlite.html See if your problem is with the data in the file or the method you're using to access it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Auto-grow setting?
Please reply to the list, not to me only. It's impossible to set chunk size to percentage of the database size, you can only set a constant value. Pavel On Tue, Feb 22, 2011 at 9:13 AM, Sven L wrote: > Thanks a lot! :D > > What do you think of setting the chunk size to approximately 10% of the > database file size? Or is it better to use a constant? > >> From: paiva...@gmail.com >> Date: Tue, 22 Feb 2011 08:30:54 -0500 >> Subject: Re: [sqlite] Auto-grow setting? >> To: sqlite-users@sqlite.org >> CC: larvpo...@hotmail.se >> >> Is SQLITE_FCNTL_CHUNK_SIZE what you are looking for? See more >> information about it here: >> http://www.sqlite.org/c3ref/c_fcntl_chunk_size.html. Notice that this >> feature appeared only in recent version of SQLite, so if you have some >> earlier version you won't be able to control it and SQLite will >> grow/shrink database page-by-page (maximum page size is 32Kb). >> >> >> Pavel >> >> On Tue, Feb 22, 2011 at 7:28 AM, Sven L wrote: >> > >> > Can't seem to find a setting to control how the database file grows when >> > full. Is there such a setting? >> > It looks like the file increases by some < 100 kB when it is full. I >> > want to change this to around 10 MB (or even more) to avoid file >> > fragmentation. >> > >> > Any ideas? >> > >> > 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
[sqlite] SELECT (string field) returns part of contents, then asterisks
Hello list, I'm in the process of writing a little interface tool for notes and adress databases from an iPad. Using MS Word VBA and SQLite3_StdCall.dll. My query looks quite simply put: SELECT ROWID,creation_date,title FROM Note and it runs fine and returns w/o problems. When I iterate through it, though, and I try to read a long note (saved in field 'title') I happen to get some 2700 characters back plus a sequence of asterisks. Am I missing something here? Thank you for your efforts, /T -- Med venlig hilsen, Thomas Besand ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite-dev] what's the wrong with my update function
sqlite3_step can be called several times if your statement returns
some rows (like select statement). In this case each call of
sqlite3_step except last one will return SQLITE_ROW. Last call will
return SQLITE_DONE. And in case of any error sqlite3_step will return
SQLITE_ERROR or some extended error code (depending on whether you use
default SQLite configuration or you also called
sqlite3_extended_result_codes,
http://www.sqlite.org/c3ref/extended_result_codes.html).
insert/update/delete statements do not return any rows, so
sqlite3_step won't ever return SQLITE_ROW for them. It will always be
SQLITE_DONE if statement has been executed successfully or
SQLITE_ERROR (or extended error code) if statement failed.
One more note: reply to the whole list please, not to me only.
Pavel
On Tue, Feb 22, 2011 at 8:34 AM, Ali Habib wrote:
> Hi,
> sorry for the wrong sending , my problem is that No update happened , I
> also I read about sql_step , but I couldn't understand how to apply it ,
> they said you should run it several times
> Best regards
>
> On Tue, Feb 22, 2011 at 3:24 PM, Pavel Ivanov wrote:
>>
>> There are several problems:
>> 1) You wrote to the wrong list. sqlite-dev is for those who develop
>> SQLite, sqlite-users is for those who develop using SQLite.
>> 2) You didn't say what problem you have with that piece of code.
>> 3) You didn't call sqlite3_step() after sqlite3_bind_text() to
>> actually execute your update statement.
>>
>>
>> Pavel
>>
>> On Tue, Feb 22, 2011 at 6:43 AM, Ali Habib
>> wrote:
>> > Hi all,
>> > I want to update database that exists in the user iphone , I use the
>> > information in uitextview (animalDesciption ) to update using the
>> > following
>> >
>> > -(IBAction)UpadateData:(id)sender{
>> >
>> > sqlite3 *database;
>> >
>> > // Setup some globals
>> >
>> > NSString *databaseName = @"test.sql";
>> >
>> > // Get the path to the documents directory and append the databaseName
>> >
>> > NSArray *documentPaths =
>> > NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,
>> > NSUserDomainMask,
>> > YES);
>> >
>> > NSString *documentsDir = [documentPaths objectAtIndex:0];
>> >
>> > NSString * databasePath = [documentsDir
>> > stringByAppendingPathComponent:databaseName];
>> >
>> > [databasePath retain];
>> >
>> > sqlite3_stmt *compiledStatement;
>> >
>> > if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) {
>> >
>> > const char *sqlStatement = "Update animals set description = ? WHERE
>> > name=
>> > ?";
>> >
>> > //sqlite3_prepare_v2(database, sqlStatement, 1,&compiledStatement, NULL)
>> > ;
>> >
>> > if(sqlite3_prepare_v2(database, sqlStatement , -1, &compiledStatement,
>> > NULL)== SQLITE_OK) {
>> >
>> > sqlite3_bind_text(compiledStatement, 1, [ self.animalDesciption.text
>> > UTF8String] , -1, SQLITE_TRANSIENT);
>> >
>> > sqlite3_bind_text(compiledStatement, 2, [ AnimalName UTF8String], -1,
>> > SQLITE_TRANSIENT);
>> >
>> > sqlite3_reset(compiledStatement);
>> >
>> > }
>> >
>> > sqlite3_finalize(compiledStatement);
>> >
>> > sqlite3_close(database);
>> >
>> > }
>> >
>> >
>> > }
>> >
>> > any suggestion, how to fix that please
>> >
>> > Best regards
>> >
>> > ___
>> > 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] Auto-grow setting?
Is SQLITE_FCNTL_CHUNK_SIZE what you are looking for? See more information about it here: http://www.sqlite.org/c3ref/c_fcntl_chunk_size.html. Notice that this feature appeared only in recent version of SQLite, so if you have some earlier version you won't be able to control it and SQLite will grow/shrink database page-by-page (maximum page size is 32Kb). Pavel On Tue, Feb 22, 2011 at 7:28 AM, Sven L wrote: > > Can't seem to find a setting to control how the database file grows when > full. Is there such a setting? > It looks like the file increases by some < 100 kB when it is full. I want to > change this to around 10 MB (or even more) to avoid file fragmentation. > > Any ideas? > > 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
[sqlite] VFS
I'm trying to understand how the VFS implementation works. If I fetch the default VFS with sqlite3_vfs_find(NULL), these members: xDlOpen xDlError xDlSym xDlClose all have non-NULL values. However, if I open a database and inspect the VFS contained in the sqlite3*, these four members are all NULL. Which function resets them, and why? I have no extensions loaded, but SQLITE_OMIT_EXTENSIONS is also not defined. Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite-dev] what's the wrong with my update function
There are several problems:
1) You wrote to the wrong list. sqlite-dev is for those who develop
SQLite, sqlite-users is for those who develop using SQLite.
2) You didn't say what problem you have with that piece of code.
3) You didn't call sqlite3_step() after sqlite3_bind_text() to
actually execute your update statement.
Pavel
On Tue, Feb 22, 2011 at 6:43 AM, Ali Habib wrote:
> Hi all,
> I want to update database that exists in the user iphone , I use the
> information in uitextview (animalDesciption ) to update using the following
>
> -(IBAction)UpadateData:(id)sender{
>
> sqlite3 *database;
>
> // Setup some globals
>
> NSString *databaseName = @"test.sql";
>
> // Get the path to the documents directory and append the databaseName
>
> NSArray *documentPaths =
> NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask,
> YES);
>
> NSString *documentsDir = [documentPaths objectAtIndex:0];
>
> NSString * databasePath = [documentsDir
> stringByAppendingPathComponent:databaseName];
>
> [databasePath retain];
>
> sqlite3_stmt *compiledStatement;
>
> if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) {
>
> const char *sqlStatement = "Update animals set description = ? WHERE name=
> ?";
>
> //sqlite3_prepare_v2(database, sqlStatement, 1,&compiledStatement, NULL) ;
>
> if(sqlite3_prepare_v2(database, sqlStatement , -1, &compiledStatement,
> NULL)== SQLITE_OK) {
>
> sqlite3_bind_text(compiledStatement, 1, [ self.animalDesciption.text
> UTF8String] , -1, SQLITE_TRANSIENT);
>
> sqlite3_bind_text(compiledStatement, 2, [ AnimalName UTF8String], -1,
> SQLITE_TRANSIENT);
>
> sqlite3_reset(compiledStatement);
>
> }
>
> sqlite3_finalize(compiledStatement);
>
> sqlite3_close(database);
>
> }
>
>
> }
>
> any suggestion, how to fix that please
>
> Best regards
>
> ___
> 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 3.7.5 : same query written using NOT IN and EXCEPT does not give out same results
On Tue, Feb 22, 2011 at 5:22 AM, Benoit Mortgat wrote: > Hello, > > I have come across a strange behaviour of SQLite 3.7.5. > > The following query: > > SELECT DISTINCT COALESCE(a.xxx, b.yyy) value > FROM tbl1 a > LEFT OUTER JOIN tbl2 b >ON a.zzz = b.ttt > EXCEPT > SELECT DISTINCT ggg value > FROM tbl3; > > will not return any results (which seems to be correct). > > However, when I write it this way: > > SELECT DISTINCT COALESCE(a.xxx, b.yyy) value > FROM tbl1 a > LEFT OUTER JOIN tbl2 b >ON a.zzz = b.ttt > WHERE value NOT IN ( >SELECT DISTINCT ggg > FROM tbl3 > ); > The query is really more like this: SELECT DISTINCT COALESCE(a.xxx, b.value) value FROM tbl1 a LEFT OUTER JOIN tbl2 b ON a.zzz = b.ttt WHERE value NOT IN ( SELECT DISTINCT ggg FROM tbl3 ); The "value" on the left-hand side of the NOT IN operator is ambiguous: Does it refer to the first column of output or to the "value" column of the "b" table? SQLite chooses the latter. You can fix it in a couple of ways: SELECT DISTINCT COALESCE(a.xxx, b.value) valueX FROM tbl1 a LEFT OUTER JOIN tbl2 b ON a.zzz = b.ttt WHERE valueX NOT IN ( SELECT DISTINCT ggg FROM tbl3 ); Or SELECT DISTINCT COALESCE(a.xxx, b.value) value FROM tbl1 a LEFT OUTER JOIN tbl2 b ON a.zzz = b.ttt WHERE COALESCE(a.xxx, b.value) NOT IN ( SELECT DISTINCT ggg FROM tbl3 ); > > I get results (which are wrong). > > I could send a samble database with full query to a developer if > needed in order to reproduce that. > Is this known bug? > > Thank you, > Benoit > ___ > 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] Auto-grow setting?
Can't seem to find a setting to control how the database file grows when full. Is there such a setting? It looks like the file increases by some < 100 kB when it is full. I want to change this to around 10 MB (or even more) to avoid file fragmentation. Any ideas? Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.5 : same query written using NOT IN and EXCEPT does not give out same results
On Tue, Feb 22, 2011 at 5:22 AM, Benoit Mortgat wrote: > I could send a samble database with full query to a developer if > needed in order to reproduce that. > Please do send the sample database and the full queries. -- 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 3.7.5 : same query written using NOT IN and EXCEPT does not give out same results
Hello, I have come across a strange behaviour of SQLite 3.7.5. The following query: SELECT DISTINCT COALESCE(a.xxx, b.yyy) value FROM tbl1 a LEFT OUTER JOIN tbl2 b ON a.zzz = b.ttt EXCEPT SELECT DISTINCT ggg value FROM tbl3; will not return any results (which seems to be correct). However, when I write it this way: SELECT DISTINCT COALESCE(a.xxx, b.yyy) value FROM tbl1 a LEFT OUTER JOIN tbl2 b ON a.zzz = b.ttt WHERE value NOT IN ( SELECT DISTINCT ggg FROM tbl3 ); I get results (which are wrong). I could send a samble database with full query to a developer if needed in order to reproduce that. Is this known bug? Thank you, Benoit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
