Re: [sqlite] capturing and testing a hot journal
Am 14.07.2014 13:53, schrieb Kees Nuyt: On Mon, 14 Jul 2014 12:09:46 +0100, Simon Slavin slav...@bigfraud.org wrote: On 14 Jul 2014, at 11:19am, Kees Nuyt k.n...@zonnet.nl wrote: On Sun, 13 Jul 2014 18:00:59 +0100, Simon Slavin slav...@bigfraud.org wrote: I had to explain to some users that a database change is not 'safe' until the database is closed. As far as I know, a database change is safe after a successfull COMMIT. Commit also releases locks. That's what the documentation says, and it's a safe way to operate if all your access to the file is via one API. Unfortunately, the drivers for many storage media lie to the operating system and do not flush changes to disk when told to. On a test system running Windows 98, using a C program writing a text file, I was able to prove that doing all the locking and flushing the documentation required still did not properly update the file on disk. However, the file was always updated by a few seconds after the file was closed so I have used that as a yardstick ever since. Aha, I see. Yes, ill-behaving filesystems can do that. The question is whether experiences on Windows 98 are still relevant for rules of thumb in 2014. As most people probably keep the write caches on their hard drives 'ON', yes. If even your hardware lies in the name of performance, you should probably be a little paranoid. So it is not just filesystems, it is hardware too. So if you do a successful commit and the OS and hardware don't lie to you, your change is safe. But there are lies of varying magnitude at work. So if it is really important, wait a few minutes until the OS has surely flushed all its buffers and the HDDs did the same. Michael -- Michael Schlenker Software Architect CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Registering Application IDs?
Hi all, what is needed to have application IDs registered in the magic.txt file, as listed under the #pragma application_id? Any formal process needed, or is an email to someone enough to do it? Oh, and btw. there seems to be some difference between the definition used in the magic file (http://www.sqlite.org/src/artifact?ci=trunkfilename=magic.txt) (signed 32-bit integer) and in the PRAGMA documentation (unsigned 32-bit integer). Is this intended? Thanks, Michael -- Michael Schlenker Software Architect CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf HoltgrefeH Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] executing queries on normalized database
Am 11.11.2013 12:57, schrieb Simon Slavin: On 11 Nov 2013, at 11:08am, dd durga.d...@gmail.com wrote: How do I insert trackname as That's Amore and artistname as Dean Martin with single query in artist and track tables? Sorry, I don't know a way to do that. I hope someone else has a way but it's normal to have your software figure out that you need to create artist/publisher/whatever rows first. C.J. Date offers a way to do it, if I'm remembering right, in his 'Tutorial D' language, (as mentioned in 'Database in Depth'), but it is usually not possible to do it in simple standard SQL. One way to do it in SQL with some upfront preparations, would be to define a special writeable view and arrange a complex INSERT triggers to just insert part of the data into one table and part of the table into a second table. But that kind of defeats the normalization... Might work for your simple structures, but gets pretty ugly if things are more complex. Michael -- Michael Schlenker Software Architect CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] showwal.c to find WAL file differences? (trying to analyze some lost commits)
Hi everyone, I'm trying to track down some lost commits for SQLite 3.7.7.1 via the APSW Python wrapper. Pagesize etc. are all left at defaults for the APSW wrapper. The following probablematic scenario: The sqlite db file and WAL/SHM files are on some automounted linux SAN filesystem (probably NFS beneath it), pretty old SLES 9 system with kernel 2.6.5-7.244-smp and pretty normal mount options for the filesystem. I now have SAN snapshots of two sets of sqlite.db and sqlite.db-wal/shm files from two consecutive days. The only change is in the WAL file, the sqlite.db file is totally unchanged (checked via md5sum and fs timestamps). In the snapshot form day 1 i see a commit, when executing a query. In the snapshot from day 2 the same entry is gone. I'm pretty sure there was no DELETE for the missing object, but want to verify what happend by comparing the WAL files. Now i checked the wal with the tool/showwal.c program from the source distribution and see differences in the frames 50-91 of 288 total frames, so basically in the middle of the file. Now my questions: Is it a sign of file corruption that some frames in the middle of the WAL file are differing between those two snapshots? From the description how the WAL file works, i would assume that only new frames are appended, but old frames are never modified. My current assumption is, that some WAL journal writes were lost or messed up by the filesystem/SAN mount somehow, but i need to verify my assumptions about how WAL works first. Michael -- Michael Schlenker Software Architect CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] showwal.c to find WAL file differences? (trying to analyze some lost commits)
Am 10.01.2013 15:31, schrieb Dan Kennedy: On 01/10/2013 07:11 PM, Michael Schlenker wrote: Hi everyone, [snip] I'm pretty sure there was no DELETE for the missing object, but want to verify what happend by comparing the WAL files. Now i checked the wal with the tool/showwal.c program from the source distribution and see differences in the frames 50-91 of 288 total frames, so basically in the middle of the file. Now my questions: Is it a sign of file corruption that some frames in the middle of the WAL file are differing between those two snapshots? Possibly. But also possibly not. Normally, new frames are appended to the WAL file. However, after a checkpoint occurs (copying the contents of the WAL into the database file), SQLite wraps around and starts writing at the start of the WAL file again. So you can get into a situation where you have a large WAL file on disk with only a small number of valid frames at the start of it. If a crash occurs and SQLite needs to read the WAL file (database recovery), it can tell the difference between the valid frames at the start of the WAL and the trailing garbage using the running checksum embedded in each frame. However, it looks like showwal.c assumes the entire WAL file consists of valid frames (it does not verify the checksums). So, it is possible that on day 1 your WAL file may have contained just 49 valid frames - not the 288 indicated by showwal. And that between day 1 and day 2 42 extra frames were appended to the WAL. Thanks Dan, No, i checked this case. The checksums would explain my observations, e.g. some commits vanishing, as the end of the WAL is skipped/ignored due to checksum mismatch. The checksums are correct for the day 1 log, a SELECT finds pages that are later in the WAL than the last difference (frame 92 in my case). Some trivial grepping/python find() on the strings confirms they are at an offset beyond the last difference. In the day 2 log the checksums start to differ on frame 50 and are the same again on frame 92, which leads to those frames = 92 being ignored. The file header info (salt and checksums) are identical for both files. If i read the code correct, this means no walRestartLog() has happend, as that seems to reset salt1 to a new value. In addition, i would not expect to see 49 identical frames after a walRestartLog(), before new ones are added. So this points to pWal-hdr.mxFrame not being setup correctly on entering sqlite3WalFrames(), which would cause an overwrite of the already written frames without a walRestartLog(). Which points at walIndexTryHdr() reading the maximum sane index from shared memory. And thats probably not working correctly on NFS/Network. So this is a sure way to corrupt the database if the shm code gets an outdated but not corrupted copy of the -shm file from the OS/filesystem and additional writes are done. Ok, so it seems i should implement some really strict asserts/checks so customers do not even try to put those files up on NFS, and just have some pretty error blow up in their face if they try on startup. Thanks. Michael -- Michael Schlenker Software Architect CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VC++ and SQLite
Am 13.11.2012 14:33, schrieb Arbol One: I would also keep this feature, however, in the case of SQLite3 amalgamation, I am really confused. You know how we have to #include the 'stdafx.h' in every declaration file (making it non-portable code), i.e. .c, .cpp, etc., well, I tried doing the same thing with sqlite.c, but VS10 complains about it. You can do that without making it non portable by adding /FI to your compile line: http://msdn.microsoft.com/en-us/library/8c5ztk84%28v=vs.71%29.aspx Try /FI stdafx.h Michael What a nightmare Visual Studio is :( Genius might have limitations, but stupidity is no handicap Eat Kosher -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Drescher Sent: Monday, November 12, 2012 7:44 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] VC++ and SQLite On Mon, Nov 12, 2012 at 1:52 PM, Doug Nebeker pa...@poweradmin.com wrote: You might be surprised at the speed increase you see in compile time if you've got large projects. The time isn't lost to CPU as much, but disk I/O time adds up when hitting many hundreds of small (header) files (even with an SSD). This is why I use PCH. Building some of my projects take a long time even on a 12 threaded processor with multiple SSDs. -- John M. Drescher ___ 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 -- Michael Schlenker Software Architect CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger, syntax question
Am 04.06.2012 19:25, schrieb Wolfgang Meiners: Am 04.06.12 18:59, schrieb Igor Tandetnik: On 6/4/2012 12:33 PM, Wolfgang Meiners wrote: this trigger does work but i think it is not in accordance with the syntax diagram of TRIGGER on http://sqlite.com/lang_createtrigger.html Which part do you feel is in violation of the diagram? I cant find anything about SELECT CASE ... END; in the diagram and i dont understand, how it works. Have a look at: http://sqlite.org/lang_expr.html Michael -- Michael Schlenker Software Architect CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What does The use of the amalgamation is recommended for all applications. mean, precisely?
Am 25.05.2012 14:27, schrieb Pavel Ivanov: With regard to the second issue, several experienced people, including Richard Hipp, have expressed the opinion in more than one occasion that statically linking sqlite avoids some problems that can happen otherwise. OTOH, all people involved in supporting Linux distributions advocate against static inclusion and for use of dynamic libraries all the time so that if some bug or security vulnerability is fixed in SQLite it could be easily updated for everybody by upgrading only one package. I don't know though what they suggest to do if you need to use version of SQLite newer than distribution is currently providing. Well, it reduces the work for the Linux maintainers and some app developers are notoriously slow to fix security bugs upstream. Guess it really depends on the target audience. For some library or other smaller app that is to be distributed with some popular Linux distro or to another environment where someone else is responsible for keeping the environment sane, it might make sense to do dynamic linking to a system library. If the other person that manages the environment fails, your app fails. If the other person does not update the libs you are stuck and can only file bugs and wait. But if you ship a major system, you really really want to keep your environment under control. More work, but saves you from insane bugs due to broken system libs and weird hacks around various system lib issues. If some lib is broken, just patch it and ship a fixed version. We typically run 'ldd' on all our libs and programs and match the external dependencies to an allowed list. Anything not in that list (which is usually tiny, stuff like libc) found in any library is considered a bug in our product and gets patched (usually by either disabling unnecessary dependencies at compile time or by shipping the really needed dependencies in a working version). So, if you can point to someone else (e.g. Linux distro maintainer) to fix issues with broken libs for you, fine. But if you must fix them yourself due to support contracts, you do not want to be forced to depend on those. Michael -- Michael Schlenker Software Architect CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] feed endless data into sqlite, thru a shell script
Am 27.09.2011 23:07, schrieb Patrick Proniewski: On 27 sept. 2011, at 20:18, Gabor Grothendieck wrote: gawk has fflush() On 27 sept. 2011, at 20:29, Roger Andersson wrote: stdbuf? unbuffer? none of them is available out of the box on Mac OS X, or FreeBSD. gawk can be installed, but I'd rather use my while true loop instead of installing gawk. Well 'unbuffer' is a trivial Expect script, and expect IS available on OS X out of the box... http://developer.apple.com/library/mac/#documentation/Darwin/Reference/ManPages/man1/expect.1.html See for the script: http://expect.cvs.sourceforge.net/viewvc/expect/expect/example/unbuffer?revision=5.34view=markup Michael -- Michael Schlenker Software Architect CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] compile warning in tclsql 3.7.5
Am 11.02.2011 22:55, schrieb Paul Moore: paul@paul-suse:~/build/TRUNK/external/build/sqlite-tea-3070500 make gcc -DPACKAGE_NAME=\sqlite\ -DPACKAGE_TARNAME=\sqlite\ -DPACKAGE_VERSION=\3.7.5\ -DPACKAGE_STRING=\sqlite\ 3.7.5\ -DPACKAGE_BUGREPORT=\\ -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1 -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1 -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1 -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DHAVE_LIMITS_H=1 -DHAVE_SYS_PARAM_H=1 -DUSE_THREAD_ALLOC=1 -D_REENTRANT=1 -D_THREAD_SAFE=1 -DTCL_THREADS=1 -DSQLITE_THREADSAFE=1 -DMODULE_SCOPE=extern\ __attribute__\(\(__visibility__\(\hidden\\)\)\) -D_LARGEFILE64_SOURCE=1 -DTCL_WIDE_INT_IS_LONG=1 -DUSE_TCL_STUBS=1 -DSQLITE_ENABLE_FTS3=1 -I./generic -I/home/paul/build/TRUNK/external/build/tcl8.5.8/generic -g -O0 -pipe -O2 -fomit-frame-pointer -Wall -fPIC -c `echo ./generic/tclsqlite3.c` -o tclsqlite3.o./generic/tclsqlite3.c: In function `DbObjCmd': ./generic/tclsqlite3.c:125638: warning: passing arg 3 of pointer to function from incompatible pointer type on 64 bit suse (and redhat) seems to be saying that Tcl_WideInt != sqlite3_int64 Tcl_WideInt ended up being set as 'long' sqlite3_int64 ended up being long long int I assumed those 2 types are the same in 64 bit - but apparently not This define looks suspicious. -DTCL_WIDE_INT_IS_LONG=1 Michael -- Michael Schlenker Software Architect CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] oracle compatibility mode
Am 23.02.2011 03:28, schrieb Phil Oertel: 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. Why is it 'tremendously useful'? If you want an Oracle for testing you can always simply setup an Oracle XE somewhere to have the real syntax and features available without any incomplete 'compatibility mode' that always fails to catch the important little nuances that Oracle does differently. And if it is too slow you can throw money at their Times-Ten product too, which is kinda in memory database. The complexity depends on how shallow the 'compatibility' shall be (e.g. do you want the braindead '' = NULL feature of Oracle, or all of their TO_DATE/TO_CHAR stuff, or Stored Procedures, UPDATE RETURNING, the NLS_LANG crazyness, CREATE SEQUENCE, etc. etc.). In general i would say its not worth the trouble. If you need that kind of portability you should probably use some kind of higher level mapper (for example some ORM like sqlalchemy). Michael -- Michael Schlenker Software Architect CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: Umlaut in database filename
Am 20.12.2010 00:26, schrieb Harald Friedrich: Hello SQLite team, if an umlaut (German mutated vowel with two dots on top äöüÄÖÜß) is present in the database filename, the first access to the database after opening will result in an exception or a runtime error 216. Using the user's AppDataPath unfortunately always includes the username. If this name has an umlaut, SQLite will not work: *C:\Users\Jürgen Möller\AppData\Roaming\ibf\TarV15\medium\Database\target3001.db* What can we do? Use the right encoding for the sqlite_open() call (e.g. UTF-8) or use the sqlite_open16() call which might be easier on Windows. If you use some kind of wrapper other than the C code, file a bug there. You probably passed some ANSI/OEM codepage filename to sqlite_open() which gets messed up. Michael -- Michael Schlenker Software Architect CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] cast from pointer to integer of different size
jagjeet singh nain schrieb: Hi, I was compiling sqlite on 64 bit OS and i got following warning message. Its probably an issue of: http://www.sqlite.org/faq.html#q17 Michael -- Michael Schlenker Software Architect CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] NOT NULL asymetry between CREATE TABLE and ALTER TABLE, why?
Hi all, Have a look at the following short sqlite shell session: SQLite version 3.6.4 Enter .help for instructions Enter SQL statements terminated with a ; sqlite create table foo (a text(5) NOT NULL); sqlite alter table foo add column b text(5) NOT NULL; SQL error: Cannot add a NOT NULL column with default value NULL Is there a reason for this asymetric behaviour of ALTER TABLE and CREATE TABLE? Its a bit dated version, but current version has doc'ed the limitation that for ALTER TABLE still. Michael -- Michael Schlenker Software Architect CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Escaping binary data
Andrew Wood schrieb: I apologise if this is in the documentation but I cant seem to follow it. Is there a function in the C/C++ API which will escape an array of binary data. For example in MySQL there is the following function: mysql_real_escape_string(conn,out,in,length); What Id like to do is take a char array in C, escpape it and drop it into a plain text SQL string which could then be passed to sqlite3_get_table() Sounds like the wrong approach. Use bind variables and a different query API if you need this. See sqlite3_prepare_v2, sqlite3_bind etc. But if you really need it, you can create blob literals easily yourself, its just the hex string in quotes with a x or X prefixed. http://sqlite.org/lang_expr.html Michael -- Michael Schlenker Software Architect CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What languages can include SQLite statically?
Gilles Ganault schrieb: On Fri, 21 May 2010 12:11:39 +0200, Jean-Denis Muys jdm...@kleegroup.com wrote: Well the answer is: any language with an external interface to the C ABI can link to the SQLite compiled C object code Thanks for the explanation. Ideally, the executable should be a simpler language than C that would still be able to include SQLite so that I would end up with a single executable. Apparently, there are two solutions: - use C/C++, and just include sqlite.c + sqlite.h in the project - compile the SQLite source code into OBJ, compile this OBJ with the executable, and somehow call the SQLite functions from the main program. Does someone know of a BASIC-like language (ie. with an easy syntax like Python, Lua, etc.) that can include the SQLite OBJ file? Well, Tcl/Tk is one solution with an easy syntax and good sqlite support. If you used starkits/starpacks you can wrap all those things up into single file executables on many platforms easily too. But Python, Lua etc. have useful SQLite bindings too, so pick whatever language you feel comfortable with, most have a working SQLite binding. Michael -- Michael Schlenker Software Architect CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [server] HTTP + SQLite bundled as single EXE?
Sylvain Pointeau schrieb: option 1: a database server using sqlite behind the scene: - which language / protocole to use ? - which is the security model? (forget .htaccess, this is for apache / webserver) option 2: web server using http - only SELECT statements? - returning JSON or whatever? - which protocole to use then? (there is no normalized protocole to do that) You could take the Tcl WUB webserver, which already implements a SQLQ domain from an sqlite DB and use that. Easy to wrap in single file too. See http://code.google.com/p/wub/source/browse/trunk/Domains/SqlQ.tcl Michael -- Michael Schlenker Software Architect CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Undefined Symbol: Tcl_CreateObjCommand
Dan Kennedy schrieb: On Aug 30, 2009, at 12:08 AM, carlos.tas...@farmerswife.com wrote: Hi Gerry, That's the first thing I tested. I downloaded this file: http://sqlite.org/tclsqlite-3.6.17.so.gz Does anyone else have the same problem? I'm using ActiveTcl 8.5.4. If your using ActiveTcl you could use their build of SQLite and download it via the teacup command. But otherwis this looks weird. Check the downloaded .so with ldd if it references any Tcl Library (like libtcl8.4 or something). It should not, if it had been compiled with STUBS enabled. Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Protect against SQL injection inside of the database?
Fredrik Karlsson schrieb:
Dear list,
Sorry for jumping onto the list mainly to ask a question, but it is an
imporant one, and I have failed to find the answer on Google.
I am developing a prototype of an application in Tcl using sqlite as
the backend database. Now, I know that I will be dealing with quite
naïve users, who will not think that !' and simialar characters are
evil and potentially dangerous in a SQL database context. So, now I
need to make sure that I am taking all the precautions I can to
protect the database from evil / naïve users, and since parts of the
application may be ported to C for speed later, I would prefer as much
of it to happen in the SQL queries themselves, in order to make sure
that the behaviour stays constant when porting.
My currrent strategy is to use a combination of quote() and trim() (as
blank space at the ends of a string is not important in my
application). So, for each string value I get from the user, I do
something similar to
set out [format {select * from X where label == quote(trim(%s)) and
id %d } $myStringValue $compId ]
(Please ignore the Tcl part if you are not familiar with it.. format
is basically (almost) sprintf in a new name )
Your working far too hard. The sqlite Tcl binding already does all thats needed.
This is perfectly safe:
set result [db1 eval {select * from X where label = $myStringValue and id
$compId}]
But you MUST use {} to quote your query and not , so sqlite gets to do the
substitution (or better said convert things to prepared statements and bind
values correctly) and not Tcl.
Michael
--
Michael Schlenker
Software Engineer
CONTACT Software GmbH Tel.: +49 (421) 20153-80
Wiener Straße 1-3 Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/ E-Mail: m...@contact.de
Sitz der Gesellschaft: Bremen
Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Protect against SQL injection inside of the database?
Fredrik Karlsson schrieb:
On Thu, Jul 16, 2009 at 1:20 PM, Michael Schlenkerm...@contact.de wrote:
Your working far too hard. The sqlite Tcl binding already does all thats
needed.
This is perfectly safe:
set result [db1 eval {select * from X where label = $myStringValue and id
$compId}]
But you MUST use {} to quote your query and not , so sqlite gets to do the
substitution (or better said convert things to prepared statements and bind
values correctly) and not Tcl.
Michael
Hi Michael,
Ok, I can see how this would be the easiest solution, but what I am
doing is basically a query builder (maping of comands in a specialized
language to pattern subselects in SQL queries). Since the statements
can be nested in many different ways, I cannot expect to be able to
construct the query and keeping track of variable names to be used in
the final substitution, so that I can make use of the built in binding
feature of sqlite It is much to much hard work.
I don't think so.
Just use an array to store your values and prefix the names with the
identifier of your subpattern. Now when you emit your subpattern via
[format] or some other method just add the appropriate prefixed bind
variables. Should not be too hard.
Instead, I think I need to make each part of the query return a
complete (not to be evaluated further outside of sqlite) SQL query
subselect statement, which is why I think I need to make sure that the
values I insert is safe inside an SQL statement myself.
Or, do you know of a Tcl command to make strings SQL safe? (Sorry
for making this into a Tcl question now..)
Its the wrong way. See the mess you get with mysql_real_escape() in PHP and
you know its wrong.
Michael
--
Michael Schlenker
Software Engineer
CONTACT Software GmbH Tel.: +49 (421) 20153-80
Wiener Straße 1-3 Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/ E-Mail: m...@contact.de
Sitz der Gesellschaft: Bremen
Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Older versions of sqlite3 analyzer?
Hi all, is there any pre-built windows sqlite3_analyzer binary for older SQLite Versions (3.5.9 in particular)? The download page only shows the 3.6.1 compatible one. Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How sqlite will store the data?
Pramoda M. A schrieb: In which order, SQLite will store the data? In any order it likes, it might use the insertion order if thats convenient. I am using SQLite 3.6.5 and which is storing data in some other manner than I am inserting? How to make it to store in the same way as insertion? You usually don't. Simply use ORDER BY when retrieving your data, it was made for that. Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] which tool do they use to generate the SQL Syntax diagrams?
J. King schrieb: On Mon, 20 Apr 2009 06:53:37 -0400, D. Richard Hipp d...@hwaci.com wrote: http://wiki.tcl.tk/21708 It's a shame: I far preferred the BNF: more compact, not to mention you could copy and paste as well as search the text of the syntax itself. I guess it wouldn't be too hard to write a different output plugin for that Tcl code to write BNFs instead of the syntax diagrams, the info must be in the input data for that anyway. So if you want BNFs back, plugin a different backend to that code. Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Any advantage to pre-sorting data before loading and indexing it?
pyt...@bdurham.com schrieb: Is there any advantage to having data pre-sorted (in index order) before loading it into SQLite and indexing it? Or does indexing work best if the index values are randomly ordered at data load time? Random order might help you with some multi-user databases, synthetic primary keys and heavy INSERT load by concurrent users, as it reduces lock contention. As you usually do not have many issues with too much concurrent write access in SQLite (or you might have picked the wrong tool for the job) and lock contention, the downside of pre-sorted loading is not an issue there. If you do pre-sort you might get blindingly fast range scans (at least until you get hit by disc fragmentation again). Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Very slow query execution with SQLite Database
manohar s schrieb: I have a SQLite database which is of size 1.5 GB. The problem that it is taking a lot of time (12 seconds after execution of vacuum) to execute a * SELECT* query. Query : SELECT metric_id, MAX(timestamp_id) AS timestamp_id_max FROM snapshot_master GROUP BY metric_id I do not understand why this query has to take 12 seconds?? I tried to optimize by creating more indexes but no luck. BTW, before execution of vacuum, time taken for Query to execute is aprrox. 17 minutes(1074890649319 nano seconds) I am using sftp_profile for finding out the time. 1) What am I doing wrong here? 2) Is there any problem in the database design? If we might assume your table is 1.5 GB in size and you do that query you have to do a full table scan for it. (unless SQLite can use the PK index if it exists to speed it up). So your probably I/O bound, 1.5 GB in 12 seconds is about 130MB/s, sounds like full read speed of a current harddisk. Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing SQLite on embedded platform with no Tcl
John Efstathiades schrieb: Hello, I am looking into porting the latest SQLite to an embedded platform running a commercial real-time operating system. I'd like to use as much of the existing regression test code as possible to ensure the port is correct but unfortunately the target environment does not have Tcl. Thats rare. Tcl gets ported to really weird places. Unless its really tiny there should be some Tcl port. Which OS are you using? If its vxworks have a look at: http://wiki.tcl.tk/21062 Should I give serious consideration to porting Tcl just to run the regression tests? Might be easier than the work around. Usually Tcl is pretty easy to port as it has very few external dependencies. Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Finding similar strings
htizo schrieb: G'day, Using sqlitev2.8.17 in php5 is there a way to look up a table for similar strings (case insensitive)? i.e. If I have the string BLADEcatcher I want to find blade_catcher or bladecatcher1 or even bladecatcha so say 60% - 70% of characters the same. sqlite 2.8.17 is from the stoneage..., why not use something more recent? There are various options here. Not sure what would work with 2.8.17 though... - use fulltext search support fts3 and hope the tokenizer/stemmer does something useful to your strings - use the soundex() function to calculate soundex values for your stored strings and try to match with the soundex value of your search string - write your own similarity function and use it like soundex() or in a having clause. - explode your stored strings into bi- or trigrams of letters and match those Typical thing to look for is Levenshtein distance, which you might know from PHP http://de3.php.net/levenshtein, n-gram matching http://en.wikipedia.org/wiki/N-gram I would guess that n-gram matching is your best bet... Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Which TCL distrabution for Windows XP for running SQLite tests?
Shane Harrelson schrieb: Sorry for the late reply. I use both the ActiveTCL and the Cygwin distributions for testing on Windows. It should be noted that both versions have limitations that will cause false positives in the test suite. Be on the look out especially for problems with 64-integers and floating point values. I believe the ActiveTCL distribution uses a 3 digit mantissa to display floating point values which throws off the checking in the test suite. That sounds strange. Might be more an issue with Tcl 8.4 vs. Tcl 8.5. Tcl 8.5 has a totally revamped floating point handling, so that there should be no loss of precision when converting a floating point through a string rep anymore (and Tcl got big integer support based on libtommath as a side effect). Cygwin is not really a supported platform for Tcl anymore and the port is old and strange. So, guess you should use the ActiveTcl 8.5 offering, unless DRH recommends something older than Tcl 8.5 (which is the current stable branch while 8.4 is getting its last update soon). Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: [EMAIL PROTECTED] Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [UnicodeEncodeError] Don't know what else to try
Gilles Ganault schrieb: Hello Data that I download from the web seems to be using different code pages at times, and Python doesn't like this. Not sure what this has to do with SQLite... Basically both Python (2.x, 3.0 finally gets sane) encoding handling and HTML/HTTP encoding handling are a little messy. HTML pages have too many ways to specify there encoding and often lie about it. You have encoding settings in the HTTP headers, in the page inside a meta tag or someone could try to put it into an xhtml header ?xml comment. So, basically you make an educated guess. A tool like chardet helps: http://chardet.feedparser.org/ If you have done that use the python codecs module to handle the encoding. If it still blows up complain at the source that creates such mislabeled data. And look at the error='replace' option for pythons encode/decode. And if you want to put data into SQLite, to come back to the topic of this mailing list you could either stuff the raw bytes into a BLOB field or find out the encoding and put it in a normal TEXT field. Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: [EMAIL PROTECTED] Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug? like-search with german umlaut is case-sensitive, should not be
Thomas Mittelstaedt schrieb: Hallo, Just did a search on my database using SELECT * FROM ku2008 where Empfaenger 1 like '%köck%'; and nothing was found. Doing a SELECT * FROM ku2008 where Empfaenger 1 like '%kÖck%'; with the capital umlaut did find the record. The data is utf-8! my sqlite version is 3.5.9 on ubuntu hardy. Documented bug, see the sqlite expressions documentation page which states: http://www.sqlite.org/lang_expr.html (A bug: SQLite only understands upper/lower case for 7-bit Latin characters. Hence the LIKE operator is case sensitive for 8-bit iso8859 characters or UTF-8 characters. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.). But its hard to fix as you would need language information for the data to get the upper/lower thing always correct (just think about the ß - SS anomaly in german). Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: [EMAIL PROTECTED] Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Make Test Failure Without tcl.h
[EMAIL PROTECTED] schrieb: I'm having trouble running make test when I don't have tcl-devel rpm installed (which contains tcl.h). I've tried both with and without --disable-tcl but it seems like the tests don't care that I've disabled tcl. I can't easily change my build machines (ie installing tcl-devel) so I'm wondering how I might get this working. As i understand the setup the Tests are actually Tcl scripts... and for make test the sqlite functions are wrapped as Tcl commands to be excercised (which is a usually trivial excercise with the Tcl C-API). So no 'make test' without Tcl headers. You could rewrite all the Tests in a language that has a foreign function call interface so you could use the sqlite lib without wrapping for doing those tests, e.g. Python+ctype or Tcl+ffidl or any other part of it. But guess thats just a waste of time, those Tcl tests work, are far more portable than any FFI interface you'll find and putting a Tcl on a box (unless your on really small embedded hardware) should not be a problem anywhere. Actually even GCC is tested with Tcl, via DejaGnu/Expect... Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: [EMAIL PROTECTED] Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tcl/SQLite application best practices ???
Rob Sciuk schrieb: Is there an example application which demonstrates good coding technique for both tcl and sqlite to perform C.R.U.D. type operations?? I'm about to develop a small proof of concept using tcl/tk and SQLite, and while I have some ideas about mating the two, I'm sure that there may be some sample applications which have cobbled together a handy database library in an elegant coding style ... Any pointers would be deeply appreciated ... There are a multitude of apps/libs that do such things, but not really a champion, because with Tcl you have some fractioned landscape regarding things like OO and db access currently. (hopefully this will change a bit with Tcl 8.6 which will ship with TDBC for database connections and the new core OO system). I found tcldb quite useful for this, but it depends on [incr Tcl], which isn't my OO system of choice. But it provides nice things already, see the screenshots: http://dqsoftware.sourceforge.net/ Other examples of CRUD operations could surely be extracted from the aolserver codebase, using the ns_ database layer. Other tools to have a look at might be the tcllib tie module and maybe the gridplus gui tools. Maybe also TAO (http://wiki.tcl.tk/tao). It depends a bit on what you try to write and what other constraints you have or are ok to accept. Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: [EMAIL PROTECTED] Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.4 planned for 2008-10-15
D. Richard Hipp schrieb: I suppose that announcements of this kind should really only be posted on the sqlite-dev mailing list, not on sqlite-users. So I will likely reframe from making subsequent pre-release announcements on sqlite- users and use only sqlite-dev for such purposes. If you think this revised policy is out of line with the usual practice on other projects, please let me know. I think its nice to see this on sqlite-users also. If i mostly use/embed SQLite into some project i probably don't really need the extra details added by sqlite-dev, but its nice to get heads up notice about upcoming releases so i can schedule or reschedule needed work (e.g. no reason to do an upgrade to 3.6.3 when knowing 3.6.4 will come just days later.) But if the plans are still rather vague and could change quite easily your probably right that sqlite-dev is the right place. Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: [EMAIL PROTECTED] Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] best language match for SQLite?
Patrick schrieb: I am a beginner to intermediate Python Programmer. I can use SQLite with it just fine but it is my understanding that relational database and object oriented programming our not the perfect marriage. I was just wondering if anyone had an opinion on the most ideal language to use with SQLite? I love Python but I LOVE SQLite, I would learn another language just to use it better-Patrick Depends really on what you wanna do with it. The best language to use with SQLite is of course SQL ;-). Otherwise it does not really matter. Your right that there is a mismatch between the set oriented operations of relational databases and the more item oriented OO world, thats why there are all those ORMs like SQLalchemy, Storm etc. which bridge the gap. But Tcl isn't a bad recommendation anyway. Might feel a bit foreign for your Python tuned mind, but it has a really, good SQLite interface. Bonus: you can help improve the SQLite testsuite easier if you understand Tcl. Michael ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] special characters as column names
Avner Levy schrieb:
Hi,
I have a table with column names like hit/s and runq-sz.
How can I escape characters like the / and - in a select statement?
for example select hit/s, runq-sz from mytable won't work.
Escape the name, for example with , sqlite supports some other escapes for
compat reasons too, but is most widespread for quoting identifiers.
See this example for the Tcl binding:
% package require sqlite3
3.5.7
% sqlite3 db :memory:
% db eval {create table foo (hits/s INTEGER, some-thing TEXT)}
% db eval {select hits/s from foo} a {parray a}
% db eval {insert into foo values (10, 'abc')}
% db eval {select hits/s from foo} a {parray a}
a(*) = hits/s
a(hits/s) = 10
Michael
--
Michael Schlenker
Software Engineer
CONTACT Software GmbH Tel.: +49 (421) 20153-80
Wiener Straße 1-3 Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/ E-Mail: [EMAIL PROTECTED]
Sitz der Gesellschaft: Bremen
Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Diacritics (umlaut) select in SQLite
Igor Tandetnik schrieb: Alexey Pechnikov [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] ? ? ?? Sunday 20 July 2008 21:20:19 Jay A. Kreibich ???(?): The good news is that you can re-implement the LIKE function fairly easily. There have been a number of posts in the past dealing with using external Unicode/I18N libraries to implement a more complete 'LIKE' function. There is unicode extension in /ext/icu of SQLite source code. With ICU extension, LIKE operator may consider Ö and ö to be equal, but is still unlikely to make ö and o equal. You might try to store all your words in one of the decomposition normalization forms (NFD or KNFD would work, see http://unicode.org/reports/tr15/) than your LIKE match should find o and ö, but you might need to apply some other normalization to compose chars again on output. Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: [EMAIL PROTECTED] Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Documentation for the register based VM?
Hi all, I'm currently upgrading some stuff from an sqlite 3.2.7 to the new and shiny 3.5.7 and have some VBDE code in there that i need to port too. As expected the new register based engine does things a bit differently so I'm a little bit lost on how to use some of the stuff. The old code did some stowing away of values via OP_StoreMem/OP_LoadMem and those have been replaced by OP_Copy etc. in the register VM. So how many registers are available in the VM to push away some intermediate results? Maybe i should just RTFS... Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: [EMAIL PROTECTED] Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Scope of sqlite3_changes(), how to reset it?
Hi all,
some questions:
1. Is sqlite3_changes() reset to 0 when a new sqlite3_exec() is started?
2. Does a PRAGMA index_list() manipulate sqlite3_changes() in any way?
3. How can i reset sqlite3_changes() manually if its not done on sqlite3_exec()?
My real problem is i have a misdesigned interface (which i cannot change in
this case) that returns malloc'ed C structs holding a table or integers cast
to pointers when no table is returned.
Something like this (some parts ommitted)
Table* t = 0;
while (sqlite3_step(stmt) == SQLITE_ROW) {
/* build the table struct and copy the rows...
}
sqlite3_finalize(stmt);
if (t) {
return t;
} else {
return (Table*)sqlite3_changes(db);
}
Now i exec an PRAGMA index_list('table_with_no_index'), which returns no
rows, so the t stays 0, but sqlite3_changes() is nonzero.. (1) so i get back
1 stuffed into the pointer. Obviously this blows up if the code expects NULL
for no result...
So i have some ways to get out of the mess:
1. Special case PRAGMAs that return tables, which is ugly.
2. Fix the callers to deal with it, even more ugly.
3. Clear sqlite3_changes() before exec() and hope that PRAGMAs don't modify it.
4. Throw away the broken interface. Would like to, but cannot do it here for
API stability reasons.
Michael
--
Michael Schlenker
Software Engineer
CONTACT Software GmbH Tel.: +49 (421) 20153-80
Wiener Straße 1-3 Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/ E-Mail: [EMAIL PROTECTED]
Sitz der Gesellschaft: Bremen
Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Binding values for IN ?
Dennis Cote schrieb: Michael Schlenker wrote: Or would i have to create a dynamic query with appropriate number of bind variables myself and have to pay the price of reparsing each time? # like this values = [uuid1,uuid2] binders = ,.join(?*len(values)) cursor.execute(SELECT * FROM t WHERE uuid IN (%s) % binders, values) If you are going to prepare the query each time, why not embed the values directly in the query string and avoid all the bind calls? There is no benefit to binding these values unless they are possibly user supplied strings that could be used for an SQL injection attack. If they are generated by your application it would be faster and simpler to skip that step. No, i don't prepare it each time, (the wrapper caches some of the statements internally, via hashes). I now got it working with a pivot table, which worked nice because i have fixed length ids and it looks like this now: buffer = .join((b.bytes for b in uuids)) length = len(blobs) ldb = db.execute( SELECT meta.id, min( CASE WHEN meta.deleted = 1 THEN 'del' WHEN location.location_id = ? THEN 'loc' ELSE 'non' END) as status FROM meta LEFT OUTER JOIN location ON meta.id = location.item_id WHERE meta.id IN -- iterate over binary buffer, each uuid takes -- 16 bytes (SELECT substr(?,(n-1)*16,n*16) FROM pivot WHERE n = ?) GROUP BY meta.id ORDER BY status, (1, buffer, length)) Basically i have to check a larger number (might be around 10.000 or more, but usually far less) of ids against my (incomplete) metadata catalogue to find out if those ids are locally available or need to be fetched from a remote (slow) location. Thanks for the good suggestions. I might have a look at creating a temp table first, but doubt it will be faster. Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: [EMAIL PROTECTED] Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Binding values for IN ?
Dennis Cote schrieb: Michael Schlenker wrote: Basically i have to check a larger number (might be around 10.000 or more, but usually far less) of ids against my (incomplete) metadata catalogue to find out if those ids are locally available or need to be fetched from a remote (slow) location. Since you have to handle up to 10K uuids in the string, your pivot table must have that many rows. To ensure SQLite doesn't waste time doing a full table scan on pivot table you should make column n an integer primary key, or you can simply use a limit clause instead of the comparison. (SELECT substr(?,(n-1)*16,n*16) FROM pivot LIMIT ?) n is an integer primary key..., is LIMIT better than a where condition in that case? Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: [EMAIL PROTECTED] Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Variable substitution (TCL SQLite)
Zbigniew Baniewski schrieb:
On Thu, Jan 17, 2008 at 11:13:59PM -0500, D. Richard Hipp wrote:
The rules of TCL parsing are that text within {...} gets passed into
its command exactly as written with the outermost {...} removed. [..]
In other words, the $columns was *not* expanded by TCL. It got
passed down into SQLite.
Yes, I agree. But the following is not quite clear to me:
SQLite sees the $columns and thinks
you are dealing with an application variable. Just like a ? or a
:abc or @xyz. Sqlite3_prepare() runs and treats the $columns
token as it would any other SQL variable.
So, TCL sees a variable columns, whose contents ($columns) - is
column1, column2, column3.
SQLite sees statement { SELECT $columns FROM some_table }, where $columns is
just a token, which _can be_ an application variable, if TCL confirms
this.
Not really true.
If the part is wrapped in {} then for Tcl the $column is just an ordinary
string with no other meaning than foobar, and NO substitution takes place
before the string is passed to SQLite.
SQLite then interprets the string again, like some Tcl commands do
themselfes and defines the semantics for its argument as:
$name is an application variable if it appears in a place where an
application variable is valid.
This is then prepared as a statement and then values are bound by asking the
surrounding Tcl stackframe for the values of the Variables used.
Your usage fails, because the select list is no valid place to use
application variables, so SQLite does expand it there.
http://sqlite.org/c3ref/bind_blob.html has the details for the C side, only
literals can be replaced with application variables, and column names in the
select list are not really literals.
Michael
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Variable substitution (TCL SQLite)
Zbigniew Baniewski schrieb: On Fri, Jan 18, 2008 at 02:54:40PM +0100, Michael Schlenker wrote: $name is an application variable if it appears in a place where an application variable is valid. [..] Your usage fails, because the select list is no valid place to use application variables, so SQLite does expand it there. How is the definition of the valid place? See the link i provided in my last message, it explains it. Probably drh can provide more details, or just look at the BNF used by the lemon parser, it should contain all the gory details. Michael - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] table list
Ged Murphy schrieb: How can I get a list of all tables programmatically in a given SQLite database? Do a SELECT on the sqlite_master table and pick the info you need. Michael - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQlite in PDA with Windows CE
noel frankinet schrieb: Jorge Rodríguez Pedrianes a écrit : Hello, Anybody Knowk to use sqlite embeden in a PDA??, I saw that i can download a .dll to windows, but can I use this dll in PDA? I saw the documentation but don't find anything about this. Anybody can I help me?? If your happy with using SQLite via Tcl try: http://www.evolane.com/software/etcl/ Michael - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)
Joe Wilson schrieb: --- Trevor Talbot [EMAIL PROTECTED] wrote: On 12/10/07, Robert Wishlaw [EMAIL PROTECTED] wrote: IBM DB2 9.5 select a AS foo from t1 union select b from t1 order by foo SQL0206N FOO is not valid in the context where it is used. SQLSTATE=42703 The problem here is with the inconsistent quoting. PostgreSQL uses the opposite case folding as everyone else, hence the behavior difference. Could someone please post the results of these queries on Oracle, DB2 or SQL Server? If they don't work as is, could you show the syntax (double quoted aliases or otherwise) that would make these queries work on these particular databases? Informix 10: select a, a+b AS c from t1 order by c; a c 2 -998 1 3 select a AS foo from t1 union select b from t1 order by foo; foo -1000 1 2 select a, a+b AS c from t1 UNION ALL select a AS c, c AS a from t1 order by c; a c 2 -998 1 3 1 4 2 5 MS SQL Server 2005 select a, a+b AS c from t1 order by c; a c 2 -998 1 3 select a AS foo from t1 union select b from t1 order by foo; foo -1000 1 2 select a, a+b AS c from t1 UNION ALL select a AS c, c AS a from t1 order by c; a c 2 -998 1 3 1 4 2 5 Oracle 10g SQL SQL -- See if select alias or table column has precedence in ORDER BY SQL select a, a+b AS c from t1 order by c; A C -- -- 2 -998 1 3 SQL SQL -- See if aliases accepted in UNION/ORDER BY combination SQL select a AS foo from t1 union select b from t1 order by foo; FOO -- -1000 1 2 SQL SQL -- See if ambiguous column aliases, UNIONs and ORDER BY work together SQL select a, a+b AS c from t1 UNION ALL select a AS c, c AS a from t1 order by c; A C -- -- 2 -998 1 3 1 4 2 5 Michael - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] INSERT: how to include CR LF symbols in a string constant?
Jevgenijs Rogovs schrieb:
Hello everyone!
Could someone please assist me with the following: how do I insert a string
into an SQLite database that contains a CR or LF character? C-style escapes
(like \r and \n) are not working with SQLite, so how can I do this?
Depends on your sqlite interface.
With Tcl its straight forward:
package require sqlite3
sqlite3 db :memory:
db eval {create table test (a text)}
set text A text\nwith linefeed and carriage return\r\n
db eval {insert into test values ($text)}
db eval {select a from test} row {puts $row(a)}
This just works.
If you have a different binding it might be a little more complex.
Michael
--
Michael Schlenker
Software Engineer
CONTACT Software GmbH Tel.: +49 (421) 20153-80
Wiener Straße 1-3 Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/ E-Mail: [EMAIL PROTECTED]
Sitz der Gesellschaft: Bremen | Geschäftsführer: Karl Heinz Zachries
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Suggests for improving the SQLite website
[EMAIL PROTECTED] schrieb: We are looking at renovating the design of the SQLite website and would love to have suggestions from the community. If you have any ideas on how to improve the SQLite website, please constribute either to the mailing list or directly to me. Among the comments received already are these: (1) It is not clear from the homepage that the software is free. Its stated on the homepage, but maybe it should be in one of the first paragraphs, not at the last bullet in the list. I would reorder the features list quite a bit, the more technical details down (like the byte order thing and some others). (2) Half the page is devoted to talking about bugs in the software. This suggests low quality. (3) The News contains scary words: radical changes. (4) Three releases in as many months suggests the code is not stable. All those are under 'NEWS'. Maybe make the news section smaller and provide 'details' and 'developer details' links. Then the news could be like an 'executive summary' and the real facts would be listed under details and developer details. (5) Move the BigNameUsers to the front page (see http://www.sqlite.org/wiki?p=BigNameUsers) Propaganda isn't bad. Maybe a rotating banner like it is found on many websites that lists one of those with 'used by' for every page visit. (6) Need more eye-candy. Not really. Its simple, clean and nice. One thing that might be worth it, is a bit of cleanup of the menu in the upper right corner, maybe group a bit. I see these main categories there: Code/Binaries: cvs, download, contrib About: support, license, news? Documentation: quick start, faq, syntax, wiki (changes?/news?) Development: bugs, changes, timeline Currently the trackers and the main website are not really integrated, more side by side..., don't know if it would be good to change it. Michael Michael - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to implement xLock()?
Richard Klein schrieb: Joe Wilson wrote: --- Richard Klein [EMAIL PROTECTED] wrote: Joe Wilson wrote: Just implement it and run the test suite. It's easier to respond to a specific problem than a hypothetical. Is the test suite available only in Tcl? Or is there a C version as well? Tcl. But you don't have to run the test suite on the embedded system necessarily. You can simulate it on a PC with the same code. Yes, I was just thinking the same thing. My company develops middleware for set-top boxes. Since we don't want to rewrite our middleware for every different brand of box (each with its own OS), we have specified our own box-independent OS layer, which we call the Porter. The Porter, like SQLite's os_x.c layer, needs to be re-implemented for each different kind of box. I am implementing os_porter.c, which will allow SQLite to run on top of Porter (and therefore on any box we support). Naturally, we have written a version of Porter that runs on Windows, so that we can develop, run, and debug our middleware on an ordinary PC. Since Tcl runs on Windows, I should be able to use your test suite to test my implement- ation of os_porter.c on a PC. It would be nice to be able to run the test suite on the various boxes, but I don't think that will be necessary. Experience has shown that when our middleware behaves differently on a box than on a PC, the difference is almost always due to a bug in the box's Porter implementation, and *not* to a bug in the middleware. If your Porter layer is basically providing POSIX services you might be able to compile Tcl for those boxes natively from the Unix source (maybe needs some autoconf or compat stuff, but in general Tcl is extremly portable and runs on small devices (a modified version runs on most CISCO routers for example). Michael - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: CAST
A. Pagaltzis schrieb: * Samuel R. Neff [EMAIL PROTECTED] [2007-05-30 14:55]: SQLite's typelessness is an asset if you work only with SQLite but in any application that uses multiple database engines of which SQLite is only one supported engine, the non-standard typelessness is something that has to be worked around. Can you give an example of such a case? I work with several different DBMSs, myself, and I have yet to run into trouble with SQLite’s approach. Can you give a reallife example? Start by already having a wrapper that allows type introspection ( e.g. DESCRIBE on oracle and reading the result set to find out what types are returned) such a feature is basically non-portable to SQLite. This works fine if one gets the data direct from a table with declared types, which are introspectable, but it breaks as soon as computed results (aggregates or other) are included. Basically user code fires any odd SQL at the database and expects to get a table structure with data cast correctly to the 'wrapper native' datatypes. Declaring the expected types for the resulting row would work, but if the wrapper only supports the introspectiv result binding and you have some 100k lines of code using the wrapper you have a problem with the sqlite approach. (its a problem with the wrapper APIs/usage, not really with SQLite) Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: [EMAIL PROTECTED] Sitz der Gesellschaft: Bremen | Geschäftsführer: Karl Heinz Zachries Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: CAST
John Elrick schrieb: John Stanton wrote: John Elrick wrote: SNIP Introspection would occur via this mechanism and would even move all introspection for any given system behind a common interface. Just a thought. John Elrick CREATE TABLE already stores the type as its declared type. The user has that available to enforce type restrictions or to direct type conversions. My apologies, I meant any type as in types which are not defined by Sqlite, such as DATE, TIMESTAMP, ADDRESS, LEFT_HANDED_SPANNER, or DEAD_PARROT. Which is what John Stanton explained. 'any type' may be the 'declared type' for create table and is registered in the master table. But those declared types are not used for deriving types in expressions, so if any non trivial expression is used in the query you only get the predefined SQLite types (most often Strings). Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: [EMAIL PROTECTED] Sitz der Gesellschaft: Bremen | Geschäftsführer: Karl Heinz Zachries Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: CAST
Samuel R. Neff schrieb: SQLite's typelessness is an asset if you work only with SQLite but in any application that uses multiple database engines of which SQLite is only one supported engine, the non-standard typelessness is something that has to be worked around. I for one would be in favor of an option to enforce strict typing (compile time option). Quite true for many cases. Strict typing would help in many cases when using or maintaining wrappers, especially concerning the results of expressions like CAST where there is no meaningful defined type. But your wrong about the 'if working with multiple database engines' generalization. Its more of a language feature, e.g. in Tcl you nearly always have typeless interfaces and it works fine, even when accessing multiple databases (oracle, postgres, mysql + sqlite for example). (the typed interfaces break more often, e.g. Windows COM scripting with VARIANTS). So as a compile time option it would surely be a great addition for all those that have to maintain wrappers. Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: [EMAIL PROTECTED] Sitz der Gesellschaft: Bremen | Geschäftsführer: Karl Heinz Zachries Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] another test suite failure under Windows
Dennis Cote schrieb:
After fixing the exclusive2 test and running the quick test with no
errors, I decided to try the complete test suite, alltest.
I am getting failures on test misc7-6.1.x:
misc7-6.1.1... Ok
misc7-6.1.2... Ok
misc7-6.1.3...
Expected: [1]
Got: [0]
misc7-6.1.4... Ok
misc7-6.1.5...
Expected: [1]
Got: [0]
misc7-6.1.6... Ok
After this the testfixture got stuck in busy loop. It was using 99% of
CPU, doing no read or write I/O. According to Task Manager it was doing
Other I/O at a rate of about 6 per second. It was not hung since a
simple Ctrl-C interrupt exited the test.
Looking at the source for this test, I see a hint of what is likely the
problem in the first comment.
#
# The following tests, misc7-6.* test the libraries behaviour when
# it cannot open a file. To force this condition, we use up all the
# file-descriptors before running sqlite. This probably only works
# on unix.
#
proc use_up_files {} {
set ret [list]
catch {
while 1 { lappend ret [open test.db] }
}
return $ret
}
proc do_fileopen_test {prefix sql} {
set fd_list [use_up_files]
set ::go 1
set ::n 1
set ::sql $sql
while {$::go} {
catch {db close}
do_test ${prefix}.${::n} {
set rc [catch {
sqlite db test.db
db eval $::sql
} msg]
if {$rc == 0} {set ::go 0}
expr {$rc == 0 || ($rc == 1 [string first unable $msg]==0)}
} 1
close [lindex $fd_list 0]
set fd_list [lrange $fd_list 1 end]
incr ::n
}
foreach fd $fd_list {
close $fd
}
db close
}
execsql { CREATE TABLE abc(a PRIMARY KEY, b, c); }
db close
do_fileopen_test misc7-6.1 {
BEGIN;
INSERT INTO abc VALUES(1, 2, 3);
INSERT INTO abc VALUES(2, 3, 4);
INSERT INTO abc SELECT a+2, b, c FROM abc;
COMMIT;
}
It seems like the use_up_files command must work sometimes, since
several iterations of the test worked (i.e. 6.1.1 and 6.1.2). I'm not
sure what to make of the sequence of test failures and successes before
it finally hangs.
Since the comment indicates that this test is designed for a unix OS,
shouldn't it be testing for that OS before running? I think it should be
skipped much like the tests that depend upon the SQLITE_MEMDEBUG being
defined.
Does anyone with better TCL knowledge have any recommendations for
either making these tests work under Windows, or detecting the OS so the
tests can be skipped when not running under that OS?
Skipping it would simply be something like:
if {$::tcl_platform(platform) != windows} {
# code for non-windows
}
or inverted:
if {$::tcl_platform(platform) == unix} {
# code for unix platforms
}
If you need more specific platform details, take a look at:
http://wiki.tcl.tk/tcl_platform
It lists values for tcl_platform for various OS.
Michael
--
Michael Schlenker
Software Engineer
CONTACT Software GmbH Tel.: +49 (421) 20153-80
Wiener Straße 1-3 Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/ E-Mail: [EMAIL PROTECTED]
Sitz der Gesellschaft: Bremen | Geschäftsführer: Karl Heinz Zachries
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Can i store DER encoded Certificate data in sqlite
samrat saha schrieb: Dear All, I am trying to store certificate related DER encoded data using sqlite database?? But what kind of datatype should i use in sqlite to store and retrive DER encoded data?? Is there anyway to store the DER encoded certificate related fileds in sqlite?? ASN.1 DER encoding is binary data, so use a BLOB. Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: [EMAIL PROTECTED] Sitz der Gesellschaft: Bremen | Geschäftsführer: Karl Heinz Zachries Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Soft search in database
Henrik Ræder schrieb: Hi (First post - hope it's an appropriate place) I've been implementing a database of a few MB of text (indexing magazines) in SQLite, and so far have found it to work really well. Now my boss, who has a wonderfully creative mind, asks me to implement a full-text search function which is not the usual simplistic 'found' / 'not found', but more Google-style where a graded list of results is returned. For example, in a search for MP3 Player, results with the phrases next to each other would get a high rating, as would records with a high occurance of the keywords. This falls outside the usual scope of SQL, but would still seem a relatively common problem to tackle. Any ideas (pointers) how to tackle this? You have come to the right place. Take a closer look at: http://www.sqlite.org/cvstrac/wiki?p=FullTextIndex Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: [EMAIL PROTECTED] Sitz der Gesellschaft: Bremen | Geschäftsführer: Karl Heinz Zachries Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Table Info Pragmas as Virtual Table?
Hi all, for some uses its nice to query the PRAGMA and the sqlite_master table with a single query instead of multiple PRAGMA statements. Could the Pragmas to query the database schema be converted to virtual tables? (that would enable joins with sqlite_master and other nice uses to get all schema info for a table with a single statement). Or did some already do something like that? Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: [EMAIL PROTECTED] Sitz der Gesellschaft: Bremen | Geschäftsführer: Karl Heinz Zachries Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite / tcl syntax help
Jeff Godfrey schrieb:
Hi All,
I'm trying to determine the datatype of a given column using Tcl and the
following code snippet...
set dataType [$db onecolumn select typeof($colName) from $table]
Read about the Tcl substitution rules (
http://www.tcl.tk/man/tcl8.4/TclCmd/Tcl.htm#M11 , [4] and [5])
especially and SQLites quoting rules...
set dataType [$db onecolumn {select typeof($colName) from $table}]
should do what you want. Note the {} instead of the , which prevent
early substitution, so sqlite can use the $colName as a bind variable.
Michael
--
Michael Schlenker
Software Engineer
CONTACT Software GmbH Tel.: +49 (421) 20153-80
Wiener Straße 1-3 Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/ E-Mail: [EMAIL PROTECTED]
Sitz der Gesellschaft: Bremen | Geschäftsführer: Karl Heinz Zachries
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] sqlite / tcl syntax help
Jeff Godfrey schrieb:
- Original Message - From: Michael Schlenker [EMAIL PROTECTED]
set dataType [$db onecolumn {select typeof($colName) from $table}]
should do what you want. Note the {} instead of the , which prevent
early substitution, so sqlite can use the $colName as a bind variable.
Michael
Hi Michael,
Yeah, I agree that makes sense from a Tcl standpoint. In fact, that's
exactly what I started with. Unfortunately, it doesn't work as expected
and just returns the following:
Error: near $table: syntax error
So, the curly-braces are preventing the Tcl parser from substituting the
vars (as they should), but then SQLite doesn't substitute the $table var
for some reason. I guess I'm a bit unclear on exactly *what* SQLite is
capable of substituting with regard to Tcl variables. It only seems
capable of substituting variables in certain cases, or certain
locations, or ??? I'm not sure.
Looks like it does not allow variable substitution for identifiers, just
for literal values. The docs for db eval state it.
As a workaround try this:
set datatype [db eval onecolumn \
[format {select typeof($colName) from %s} $table]]
--
Michael Schlenker
Software Engineer
CONTACT Software GmbH Tel.: +49 (421) 20153-80
Wiener Straße 1-3 Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/ E-Mail: [EMAIL PROTECTED]
Sitz der Gesellschaft: Bremen | Geschäftsführer: Karl Heinz Zachries
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] PRIMARY KEY / NOT NULL enforcement
Hi all, i noticed the note in the documentation that the implicit NOT NULL constraint for PRIMARY KEYs is currently not enforced by SQLite and wondered if there is an explicit way to set it? I expected specifying the NOT NULL explicitly would work and enforce it, but it does not: CREATE TABLE foo (a INTEGER NOT NULL, b TEXT, PRIMARY KEY(a) ); Inserting a NULL into column a works fine, this does not raise an error. INSERT INTO foo VALUES (NULL,'bar'); Is the only workaround creating a trigger that calls RAISE(FAIL,...)? Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: [EMAIL PROTECTED] Sitz der Gesellschaft: Bremen | Geschäftsführer: Karl Heinz Zachries Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Triggers+callbacks = GUI?
Wesley W. Terpstra schrieb: I intend to write a GUI application backed by SQL. Several of the windows display status that would best be represented as a database view. What I've been thinking about is how to update the GUI when the view changes. [snip] Thus, a window simply provides the VIEW definition and insert/update/delete callbacks. Some support code creates the view and insert()s the current contents. Then it hooks the triggers invoking the methods to catch future updates. On widget death, the view and triggers are dropped. Triggers only react on update/delete/insert operations, which are not generally available for a arbitrary view. But if you slightly change your api, to provide a view definition and a list of tables/columns to watch you can do it, just create the triggers on the tables not on the view and take the appropriate action in your callbacks. Michael - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite / php - Using REGEXP
Cecilia VIGNY schrieb: Hi, I'm developing a Php program which uses a SQLite database. I want to execute an SQL request : select * from dicsupp WHERE supp regexp 'test'; It doesn't work... What's wrong ? Thank you ! ;) There could be all sorts of things wrong. Your SQL code looks correct so far (but using regexp with a constant pattern like 'test' is a bit inefficient). Do you get any error message? How do you execute your statement? Michael - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] how can i speed up inserts ?
Dennis Cote schrieb: ohadp wrote: holy smokes that pragma just made my several hundred inserts take half a second compared to 30 seconds earlier. thanks! Yes, but now your database can be corrupted by a power failure or OS crash. Wrapping several hundred inserts in a transaction will be nearly as fast and completely safe. What happens if one uses the pragma and transactions to do bulk inserts, would that be efficient or simple useless premature optimization? I my scenario i create test databases from scratch by bulk inserts, so if things blow up there it doesn't hurt because i have to scrap everything anyway in that case. Michael - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to specify collating sequences in an expression.
[EMAIL PROTECTED] schrieb:
SQLite has supported collating sequences since version 3.0.0.
A collating sequence is really a specification on how comparison
operators work on strings.
You can have arbitrary collating sequences in SQLite. But for
the sake of this discussion, lets focus on just two: BINARY
which is the default collating sequence, and NOCASE which does
case insensitive comparisons (on US-ASCII).
Consider this example table:
CREATE TABLE t1(
x TEXT COLLATE BINARY,
y TEXT COLLATE NOCASE
);
INSERT INTO t1 VALUES('hello','Hello');
This query returns one row because comparisons against column y
ignore case:
SELECT * FROM t1 WHERE y='HELLO';
This query returns no rows because comparisons against column x
take case into account.
SELECT * FROM t1 WHERE x='HELLO';
When comparing two columns, the column on the left determines which
collating sequence to use. In the first of the following two queries,
the column on the left uses NOCASE so one row is returned. But in
the second query, the columns are reversed and the left-most column
uses BINARY. As a result, no rows are returned from the second
query:
SELECT * FROM t1 WHERE y=x;
SELECT * FROM t1 WHERE x=y;
This last point seems a little goofy, but SQLite is documented as
working that way and the situation comes up so very rarely that nobody
has yet complained.
The problem with all of the above is that the collating sequence
on a column is specified when the column is declared and cannot
be changed. What many people would like to have is some way to
specify a different collating sequence to override the default
collating sequence for a single comparison. The question is, what
should the syntax be. Here are some ideas:
SELECT * FROM t1 WHERE x='HELLO' COLLATE NOCASE;
Here the = operator has some added syntax at the end. There some
parsing abiguities with this approach, but they can be dealt with
just as they are with the EXCEPT clause on a LIKE operator. The
comparison operator syntax would be like this:
expr operator expr [COLLATE id]
Another idea is to use CAST:
SELECT * FROM t1 WHERE CAST(x AS TEXT COLLATE NOCASE)='HELLO';
Or perhaps
SELECT * FROM t1 WHERE CASE(x COLLATE NOCASE)='HELLO';
A third idea is to invent entirely new syntax, perhaps like this:
SELECT * FROM t1 WHERE COLLATE NOCASE(x='HELLO')
Please note that while I have used the = operator in all of the
examples above, everything applies equally to !=, , =, , and =.
Questions for SQLite community members:
(1) Is there some standard SQL syntax that I do not know about
for doing this kind of thing?
(2) How do other SQL engines do this kind of thing?
Microsoft SQL Server does this either on the database level, by
specifying the collation to use for the whole database, or at the column
level with an alter table statement or during table creation.
See:
http://msdn2.microsoft.com/en-us/library/aa258237(SQL.80).aspx
Michael
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Does julianday work according to the manual?
info schrieb:
Hi,
The manual states that the function julianday returns the number of days
since noon in Greenwich on November 24, 4714 B.C. That would imply that the
statement:
Select julianday('-4714-11-24 12:00:00');
Should return 0.0. But it doesn't, it returns -365.0
Does this mean, that the manual should say since noon in Greenwich on
November 24, 4713 B.C.? Or am I missing something?
Wikipedia also disagrees with the 24. Nov of 4714 B.C. as start of
julian days...
http://en.wikipedia.org/wiki/Julian_day
Michael
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
