Re: [sqlite] Bug in SQLite's TCL module
2012/4/12, Alexey Pechnikov : > May be used "onecolumn" function instead of "eval". The "eval" function > returns empty value of single record and so the result is "{}". "Empty list", I suppose? -- regards, Zbigniew ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite's TCL module
2012/4/12, Richard Hipp : > On Thu, Apr 12, 2012 at 2:06 PM, Zbigniew wrote: > >> At the attempt to get a non-existing value, for example: >> >> set x [dbcomm eval {SELECT max(somecolumn) FROM sometable}] >> > > The correct way to do this would be one or other other of the following: > >set x [lindex [dbcomm eval {SELECT max(somecolumn) FROM sometable}] > >set x [dbcomm onecolumn eval {SELECT max(somecolumn) FROM sometable}] > > In the second example, it is traditional to abbreviate "onecolumn" to > simply "one". Thanks, "onecolumn" work for me. Do you mean, that "eval" will always return a list, although my SELECT clause wants just single value? -- regards, Zbigniew ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug in SQLite's TCL module
At the attempt to get a non-existing value, for example: set x [dbcomm eval {SELECT max(somecolumn) FROM sometable}] The returned value of $x will be {} - and no, not "empty", but exactly these two characters. Easy to reproduce. -- Zbigniew ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Transcoding?
Reading the contents of the blog http://milky.manishsinha.net/2009/03/30/sqlite-with-c/ tried to follow the tips to access SQLite database from C, but there's a problem: actually database should keep UTF-8 encoded data. No problem, when one's using LATIN1 - but I tried LATIN2 strings, and they were inserted just "as they were". Not sure: did I miss something in SQLite docs (any transcoding function available?) - or one has to transcode all the strings before insertion on his own, e.g. using iconv()? Surely someone met the problem before... maybe some code example? -- regards, Z. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] toupper/tolower
I'm sorry: totally missed `The "function" method' :( So, it seems, that the problem's solved. :) -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] toupper/tolower
On Sun, Jun 21, 2009 at 08:34:23PM -0400, Igor Tandetnik wrote: > http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/README.txt Yes, I've found libraries made by someone, and ready to use. Unfortunately, they are of several MB size... :( No, I would to keep the application "compact", and because of this, the better way - if possible - would be suggested completion of TCL API. Even that I'm aware, that such external method will be slower. -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] toupper/tolower
On Sun, Jun 21, 2009 at 05:12:18PM -0700, Cory Nelson wrote: > Unfortunately, no. A correct toupper/tolower would require locale > support and passing in a string pointer, not a char. Even a wide > character can't store all the information needed to make something > upper/lower. OK, maybe different way then: There is - in SQLite's TCL API - a "collate" method: #v+ This method registers new text collating sequences. There are two arguments: the name of the collating sequence and the name of a TCL procedure that implements a comparison function for the collating sequence. For example, the following code implements a collating sequence called "NOCASE" that sorts in text order without regard to case: proc nocase_compare {a b} { return [string compare [string tolower $a] [string tolower $b]] } db collate NOCASE nocase_compare #v- Perhaps could be possible to add to that API something like external "tolower/toupper" methods, that could be registered from within TCL? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] toupper/tolower
In the docs there is a remark: #v+ upper(X) Return a copy of input string X converted to all upper-case letters. The implementation of this function uses the C library routine toupper() which means it may not work correctly on non-ASCII UTF-8 strings. #v- And yes - alas - upper/lower are unable to do it properly for characters "outside ASCII". But I've found some more info: http://www.warpspeed.com.au/cgi-bin/inf2html.cmd?..%5Chtml%5Cbook%5CToolkt40%5CXPG4REF.INF+297 #v+ Note: toupper and tolower can only be used for single-byte characters. towupper and towlower should be used for case conversion of wide characters that are equivalent to both single-byte and double-byte characters. #v- It looks, like the problem could be easily solved just by replacing "toupper/tolower" occurences with "towupper/towlower". Am I right? If so - perhaps could be such change introduced in new version? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.12
> The TEA configure script is correct but the sqlite-3.6.12.tar.gz > configure script is messed up. See > http://www.sqlite.org/cvstrac/chngview?cn=6419 > and remember that vapier==Mike Frysinger. I'll try to fix the > tarball soon. I made a try to build TCL-module using the TEA package. There is one doubtful thing: - version 3.6.11 has libtclsqlite3.so of 47486 bytes size, while: - 3.6.12 539796 (!) Is it OK? Of course, the previous version has been built from that now "unsupported" tarball. The size of present 3.6.12 TCL-module is given _after_ "stripping". -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.12
Sorry, checked it again, and noticed now, that this kind of tarball seems to be kinda abandoned: :( "The Makefile and configure script in this tarball are not supported" -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.12
On Tue, Mar 31, 2009 at 07:42:49PM -0400, D. Richard Hipp wrote: > I just checked the sqlite-3_6_12-tea.tar.gz tarball on the website, > and it contains no "3.6.11" text. Only "3.6.12". I think you have > somehow obtained the wrong configure script. I meant sqlite-3.6.12.tar.gz package. Is it possible to just use the Makefile from sqlite-3_6_12-tea.tar.gz with sources provided by sqlite-3.6.12.tar.gz ? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.12
The "configure" script included in tarball has entries, like f.e.: #! /bin/sh # Guess values for system-dependent variables and create Makefiles. # Generated by GNU Autoconf 2.63 for sqlite 3.6.11. # [..] # Identity of this package. PACKAGE_NAME='sqlite' PACKAGE_TARNAME='sqlite' PACKAGE_VERSION='3.6.11' PACKAGE_STRING='sqlite 3.6.11' PACKAGE_BUGREPORT='' [..] \`configure' configures sqlite 3.6.11 to adapt to many kinds of systems. [..] if test -n "$ac_init_help"; then case $ac_init_help in short | recursive ) echo "Configuration of sqlite 3.6.11:";; esac cat <<\_ACEOF [..] sqlite configure 3.6.11 generated by GNU Autoconf 2.63 [..] ...and so on... (a few more) Could the above have negative influence? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.12
On Tue, Mar 31, 2009 at 07:15:57PM -0400, D. Richard Hipp wrote: > I don't understand that. I can grep in the package for 3.6.11 and I > get no hits: > > grep 3.6.11 * */* > > On the other hand, if I grep from 3.6.12, I get lots of hits. So I do > not know where the package gets the idea that it is providing version > 3.6.11. #v+ # grep -i "3.6.11" tclsqlite.o # Binary file tclsqlite.o matches # grep -i "3.6.12" tclsqlite.o # #v- But OK - I'll try to check it once again, and I'll make another compilation. Perhaps I missed something(?) -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.12
On Tue, Mar 31, 2009 at 09:41:30AM -0400, D. Richard Hipp wrote: > SQLite version 3.6.12 is now available on the SQLite website ...and once again TCL module is flawed: #v+ % package require sqlite3 attempt to provide package sqlite3 3.6.12 failed: package sqlite3 3.6.11 % provided instead #v- -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.11
It seems to me, that at 1st time, it did "relink" using former version of library (3.6.10) - and that was probably that versioning problem - but at the second time, when I made a compilation after I've removed the earlier version, it was unable to relink it (no library: the former one has been deleted before new compilation, the present one not yet installed). Is it right? If so - how should it be fixed? (BTW: gcc version 4.3.3) -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.11
And another problem - that I didn't met before: #v+ [..] echo 'package ifneeded sqlite3 3.6.11 [list load /Programs/SQLite/3.6.11/lib/tcl8.5/libtclsqlite3.so sqlite3]' > pkgIndex.tcl /System/Links/Executables/install -c -d /Programs/SQLite/3.6.11/lib/tcl8.5 ./libtool --mode=install /System/Links/Executables/install -c libtclsqlite3.la /Programs/SQLite/3.6.11/lib/tcl8.5 libtool: install: warning: relinking `libtclsqlite3.la' libtool: install: (cd /Files/Compile/Sources/sqlite-3.6.11; /bin/sh /Files/Compile/Sources/sqlite-3.6.11/libtool --mode=relink gcc -O2 -march=i686 -fomit-frame-pointer -pipe -DSQLITE_OS_UNIX=1 -I. -I./src -D_HAVE_SQLITE_CONFIG_H -DNDEBUG -I/Programs/Tcl/8.5.6/include -DSQLITE_THREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -o libtclsqlite3.la tclsqlite.lo libsqlite3.la -L/Programs/Tcl/8.5.6/lib -ltclstub8.5 -lpthread -rpath /Programs/SQLite/3.6.11/lib/tcl8.5 -version-info 8:6:8 -avoid-version ) libtool: relink: gcc -shared .libs/tclsqlite.o -Wl,-rpath -Wl,/Programs/SQLite/3.6.11/lib -L/Programs/SQLite/3.6.11/lib -lsqlite3 -L/Programs/Tcl/8.5.6/lib -ltclstub8.5 -lpthread -march=i686 -Wl,-soname -Wl,libtclsqlite3.so -o .libs/libtclsqlite3.so /System/Links/Executables/ld: cannot find -lsqlite3 collect2: ld returned 1 exit status libtool: install: error: relink `libtclsqlite3.la' with the above command before installing it make: *** [tcl_install] Błąd 1 #v- What's wrong? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.11
Hmmm... maybe I did something wrong, but it seems to me rather, there's a minor bug: #v+ z...@trurl~$ rlwrap tclsh % package require sqlite3 attempt to provide package sqlite3 3.6.11 failed: package sqlite3 3.6.10 % provided instead % #v- #v+ r...@trurl~$ strings libtclsqlite3.so | grep 3.6 /Programs/SQLite/3.6.11/lib 3.6.10 #v- How could I fix it? Where's held the release number "3.6.10"? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.11
On Tue, Feb 17, 2009 at 07:28:27PM -0500, D. Richard Hipp wrote: > Version 3.6.11 adds support for a new live-backup API which enables > applications to make backup copies of SQLite databases It's great, that backup and restore have been added to the TCL interface as well. :) -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Copy database from memory... [resolved]
Sorry for noise: the problem was in quite other place. -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Copy database from memory to disk - sometimes works, sometimes not
Reading the paper http://www.tcl.tk/community/tcl2004/Papers/D.RichardHipp/drh.html I'm trying to use the ability to work using "in-memory-database". But there is a problem: sometimes the data doesn't get transferred from memory back onto disk. The procedure, that I wrote, is fairly simple: #v+ # ...dblock & dbcomm are both active... dblock eval {SELECT name FROM sqlite_master WHERE type='table'} { dblock eval "DELETE FROM $name" } dblock close dbcomm eval {ATTACH $fullPath2dbase AS app} dbcomm eval {SELECT name FROM sqlite_master WHERE type='table'} { dbcomm eval "INSERT INTO app.$name SELECT * FROM $name" } dbcomm eval {DETACH app} dbcomm close #v- The problem is, that the above _in most cases_ works, but _just sometimes_ it isn't working - and because of this it's difficult to trace. Any ideas, what can be the reason? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Faster (can be shallow) "integrity_check"?
On Fri, Oct 31, 2008 at 06:43:29PM +0700, Dan wrote: > You could use "PRAGMA quick_check", which is similar to integrity_check > but runs more quickly. Yes, it's much faster - but still much too time-consuming to make it default (more than 10 additional seconds on ~300 MB database file). > Note that "PRAGMA quick_check" is currently an undocumented experimental > feature. It has been present for almost a year though. But will it be present for steady from now on? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Faster (can be shallow) "integrity_check"?
I would to make automatic test of the database integrity each time before accessing, just to avoid the problems with broken files, which are resulting in error message: "database disk image is malformed". Unfortunately, "PRAGMA integrity_check" takes very long time. Does there exist any possibility to make a much shorter test, for just very obvious cases, like incomplete database file (when f.e. there has been taken just part of the database file during downloading)? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite & ISO8859-x characters (Linux, C)
Wow, a whole lot of material... thanks. Starting to read it all... -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite & ISO8859-x characters (Linux, C)
How one should handle this? SQLite has UTF-8 by default. This makes a bit of conversion necessary before INSERTion (and the opposite after SELECTion, and so on...) - am I right? Or perhaps it can be notified at C-level about client-encoding - and is able to convert on its own? What C-function (Linux) could be considered as most convenient? Perhaps there's a doc with explanation (in the context of SQLite-usage)? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speeding up the simplest queries
On Mon, Sep 22, 2008 at 09:42:56PM +0100, Seun Osewa wrote: > You need FTS3: > http://www.sqlite.org/cvstrac/wiki?p=FtsUsage > It comes with the command line version of SQLite 3.6 You mean: it won't work using SQLite's module for TCL? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] The old bug strikes back
On Mon, Sep 22, 2008 at 08:03:49AM -0400, D. Richard Hipp wrote: > Version 3.6.3 fixes several bugs in version 3.6.2, most notably the > problem with DISTINCT. Just tried to compile and link against TCL 8.5.4 - unfortunately, after the compilation: #v+ % package require sqlite3 couldn't load file "/usr/lib/tcl8.4/sqlite3/libtclsqlite3.so": % /usr/lib/tcl8.4/sqlite3/libtclsqlite3.so: undefined symbol: sqlite3StrICmp #v- The problem was there before: http://www.mail-archive.com/sqlite-users@sqlite.org/msg32800.html And yes, renaming all "sqlite3StrICmp" occurences to "strcasecmp" (file tclsqlite.c) fixed the problem (I guess). -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speeding up the simplest queries
On Sun, Sep 21, 2008 at 07:50:23PM -0400, Igor Tandetnik wrote: > > although not always I want > > to fetch all that data. Sometimes I would just to count it. > > Don't call sqlite3_column_*. Just call sqlite3_step in a loop. But > again, if you want to get a count of records, you are unlikely to be > able to do any better than a statement using count(). I've got a feeling, you know very good SQLite's internals. How do you think: is it technically possible to implement much faster searching routine for all the LIKE queries? I'm asking, because I've got no idea presently, whether (or not) the limit is just the storage ("flat database file"). So, perhaps supposed different one's own procedure has to do about the same, as the built-in, and it'll take about the same time? I mean: perhaps different approach to the subject is just not possible just because of the limits forced by the storage? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speeding up the simplest queries
On Sun, Sep 21, 2008 at 07:07:33PM -0500, Jay A. Kreibich wrote: > It sounds like you need to take a more general approach to speeding > up your queries. If you've not yet looked at building appropriate > indexes, that seems like a good place to start. Right, proper indexing gives significant "boost" (for "sharp" conditions). I'm afraid, one has just to wait in the case of all the LIKE-s, unfortunately. -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speeding up the simplest queries
On Mon, Sep 22, 2008 at 12:17:44PM +1000, BareFeet wrote: > select exists (select 1 from MyTable where condition) > > which will return a boolean result, and stop scanning the table after > the first match is found. Yes, thanks - that's right: it is partial solution indeed. "Partial" - because in the case of non-existence it has to scan the table to the end anyway. But still it's better than "count(*)". -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speeding up the simplest queries
On Sun, Sep 21, 2008 at 05:22:46PM -0500, Jay A. Kreibich wrote: > count(*) is an odd one... In most database systems it is extremely > fast, but in SQLite it tends to be rather slow. I forgot the important thing: usually I was using count(*) just to detect the presence of any record meeting given condition. Is it possible to make it fast _not_ using count(*)? I need just a "boolean result" of 1/0 (yes, there is at least one / there aren't any). -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speeding up the simplest queries
On Sun, Sep 21, 2008 at 05:22:46PM -0500, Jay A. Kreibich wrote: > count(*) is an odd one... In most database systems it is extremely > fast, but in SQLite it tends to be rather slow. > [..] > If you search the archives, you'll find many discussions on the best > way to create a system table that keeps track of the number of rows > in each table via triggers. The problem is, that it's not only about global number of records - I would to have a possibility to quickly count number of records found by conditional queries like: "select count(*) from table where ". Partially it can be solved by moving the task to the application (fetch, then count list size), although not always I want to fetch all that data. Sometimes I would just to count it. It wasn't a problem, when I was counting several thousands of records - but, as I can see, it will be inconvenient in the case of the larger table. -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Speeding up the simplest queries
I've created a test database with almost one million records (about 20 fields each). I noticed, that the simple query like "select count(*) from table" takes about 10 seconds (the database file is of about 300 MB size). I'm wondering: is it the limit - or is it still possible to reduce the response time? I mean the simplest queries here, mostly: "select * from", "...where something=''", "...where something like '%that%'. Yes, I know: indexing. Unfortunately, indexing won't have any effect on "count(*)". But of course, indexing tips are welcome too (for SELECT ...). Found a page http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html - but perhaps someone gathered some more tips? Maybe latest versions of SQLite (the page is from 2003) are giving some more options for tuning? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VACUUM trouble
OK, the mystery has been solved: didn't notice, that disk just reached 100% usage... :D -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VACUUM trouble
On Sun, Sep 21, 2008 at 12:26:32AM +0100, [EMAIL PROTECTED] wrote: > You know what disk IO error means, right? Run scandisk No, the disk is OK. > and try dumping the database and restoring it to a new file. Yes, I'm aware, that one can just create the database anew - but I was asking, why VACUUM is giving up. No, it's not hardware-related trouble. Meanwhile I've found something related: http://www.mail-archive.com/sqlite-users@sqlite.org/msg24905.html Is it filesystem-related problem? Is it possible, that SQLite just "doesn't like" Reiserfs? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] VACUUM trouble
It seems, it's not possible to VACUUM a bit larger file? I tried to VACUUM a database file of 25 MB size, containing 75000 records (about 20 fields each). Unfortunately, each VACUUM attempt is interrupted with message: SQL error: disk I/O error No VACUUM allowed for such size? Linux 2.6.26, SQLite 3.6.1, 256 MB RAM -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recommended (Windows/Linux) SQLite utilities
On Wed, Apr 30, 2008 at 12:40:54AM -0400, [EMAIL PROTECTED] wrote: > Looking for recommendations for 3rd party SQLite utilities for browsing, > maintaining, importing/exporting and repairing SQLite database files. You can try "SQLite Studio" http://sqlitestudio.one.pl/ -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite & TCL: A "SELECT" from one-column table
On Sat, May 03, 2008 at 03:07:00PM -0400, D. Richard Hipp wrote: > > dbcomm eval {CREATE TABLE something( a_string VARCHAR(20) )} > > dbcomm eval {INSERT INTO something VALUES ('Version V8.5')} > > > > Now, if you want to retrieve the value: > > > > tclsh8.5 [~/tmp/tcltk]dbcomm eval "SELECT a_string FROM something" > > Version V8.5 > > > > Why so much parentheses? A list, nested in a list, nested in a list? > > But why? > > I get just {Version V8.5} when I try this. OK, too much trust into the script functionality - I should check it out interactively for sure. There was an UPDATE in the script (in between), done following way: dbcomm eval {CREATE TABLE something( a_string VARCHAR(20) )} dbcomm eval {INSERT INTO something VALUES ('Version V8.5')} set a [dbcomm eval "SELECT a_string FROM something"] dbcomm eval {UPDATE something SET a_string=$a} Of course, should have been: set a [lindex [dbcomm eval "SELECT a_string FROM something"] 0] -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite & TCL: A "SELECT" from one-column table
On Sat, May 03, 2008 at 09:52:06PM +0200, Zbigniew Baniewski wrote: > False alarm; sorry. A database has been improperly restored from ASCII dump. Cannot reproduce. :( Restored it - before, and now - quite common way: sqlite3 dbase.db http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite & TCL: A "SELECT" from one-column table
On Sat, May 03, 2008 at 03:07:00PM -0400, D. Richard Hipp wrote: > > Why so much parentheses? A list, nested in a list, nested in a list? > > But why? > > I get just {Version V8.5} when I try this. False alarm; sorry. A database has been improperly restored from ASCII dump. -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite & TCL: A "SELECT" from one-column table
Take a look at following example: dbcomm eval {CREATE TABLE something( a_string VARCHAR(20) )} dbcomm eval {INSERT INTO something VALUES ('Version V8.5')} Now, if you want to retrieve the value: tclsh8.5 [~/tmp/tcltk]dbcomm eval "SELECT a_string FROM something" Version V8.5 Why so much parentheses? A list, nested in a list, nested in a list? But why? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.5.7 & TCL: "undefined symbol: sqlite3StrICmp"
On Fri, Apr 04, 2008 at 08:48:53AM -0400, D. Richard Hipp wrote: > This has never been a problem for the prebuilt binaries on > the website. Neither this wasn't any problem for earlier sources (including 3.5.6). > Anyway, you can fix the problem by either using the > precompiled binaries, or downloading the latest from > CVS. Thanks, the patch seems to be working. Binaries are good solution for Windows (and perhaps for Mac too) - but for Linux rarely it is the case. You know: "dependencies" - especially, when somebody prefers "stable" releases, thus having older libraries, than the ones used for pre-built binaries. -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.5.7 & TCL: "undefined symbol: sqlite3StrICmp"
On Thu, Apr 03, 2008 at 09:54:15PM -0500, John Stanton wrote: > Install TCL Another one? What for? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 3.5.7 & TCL: "undefined symbol: sqlite3StrICmp"
I'm sorry to confirm the problem described at http://tinyurl.com/29wc8x #v+ $ tclsh8.5 % package require sqlite3 couldn't load file "/usr/lib/sqlite3/libtclsqlite3.so.0": /usr/lib/sqlite3/libtclsqlite3.so.0: undefined symbol: sqlite3StrICmp #v- Does there exist any cure? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TCL & tester.tcl question
On Fri, Mar 28, 2008 at 01:35:01PM -0700, Noah Hart wrote: I'm not an expert (yet... ;) - but I'm doing some TCL. > sqlite3 db ./test.db > set ::DB [sqlite3_connection_pointer db] > > My question is: Why is the last line not "set ::DB db" It seems, that there's an attempt to assign to global variable "DB" some kind of pointer (which is string value, actually), returned by some function. > What does sqlite3_connection_pointer do? Look for the definition of that function - because it's function, which is using "db" as its parameter, and "[sqlite3_connection_pointer db]" means: "value returned by function sqlite3_connection_pointer, which has processed db". > Since this is not a tcl verb, or defined by sqlite3, where does this get > defined? Somewhere else in tester.tcl? In any other script, "source"-d by tester.tcl? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Conversion string -> date
On Fri, Mar 28, 2008 at 02:39:23PM -0400, Igor Tandetnik wrote: > date() function produces a string. You are trying to subtract a number > from a string, at which point SQLite converts the string to number > ('2008-03-28' becomes 2008) and performs the subtraction (2008 - 14 == > 1994). I don't know the conversion routines details - but shouldn't be more proper to convert to "Julian Day" first, and then to make a subtraction (when "date involved" has been detected), and - finally - to convert back to "Gregorian Day"? Actually... yes, one can live without that. -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Faulty "date" function
On Fri, Mar 28, 2008 at 11:37:10AM -0700, Scott Baker wrote: > I think what you want is date math: > > sqlite> SELECT date('now','-14 days'); > 2008-03-14 Yes, I wanted date 2 weeks ago. I made a comparison using PostgreSQL, where it returns the expected date value, and my guess was, that it should have been working exactly the same way, as the "official syntax" (above), when I typed "SELECT date('now') - 14" - if it does any date-math anyway, instead of reporting error. > Since '2008-03-28' is a string, and you're trying to subtract from that > it converts it to a integer. '2008-03-28' converts to 2008 as an integer. H... yes, actually every output of SQLite is a string - but the conversion - if there's an attempt to convert a value - IMHO could be working a bit smarter way. -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Faulty "date" function
#v+ SQLite version 3.5.6 Enter ".help" for instructions sqlite> select date('now'); 2008-03-28 sqlite> select date('now')-14; 1994 sqlite> #v- Of course, proper answer should be: "2008-03-14" - shouldn't it? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New crashes with SQLite 3.5.7
I noticed other things while trying to compile under OpenBSD. First: there is a warning: #v+ *** Warning: linker path does not have real file for library -ltclstub85. *** I have the capability to make that library automatically link in when *** you link to this library. But I can only do this if you have a *** shared version of the library, which you do not appear to have *** because I did check the linker path looking for a file starting *** with libtclstub85 and none of the candidates passed a file format test *** using a regex pattern. Last file checked: /usr/src/tcl8.5.1/unix/libtclstub85.a *** The inter-library dependencies that have been dropped here will be *** automatically added whenever a program is linked with this library *** or is declared to -dlopen it. #v- (this warning I noticed with earlier SQLite versions as well) But OK. Just copied the library to libtclsqlite3.so, and after "make install" tried to use TCL package: #v+ [EMAIL PROTECTED] tclsh % package require sqlite3 tclsh:/usr/local/lib/tcl8.5/sqlite3/libtclsqlite3.so: undefined symbol 'sqlite3S trICmp' 3.5.7 % % Segmentation fault (core dumped) [EMAIL PROTECTED] #v- And again: "undefined symbol" is specific to 3.5.7 - but the "core dumped" message (always, when leaving tclsh) have been noticed with earlier SQLite versions already. Why such problems? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] can't find libsqlite3.so.0 on RHEL
On Thu, Mar 13, 2008 at 02:55:20PM -0500, P Kishor wrote: > right, but for most other programs (and, in fact, as I understand it, > this is the normal behavior for GNU's autoconf), the default behavior > is to put everything that matters under /usr/local... that is, > binaries under /usr/local/bin, libraries under /usr/local/lib, and so > on, *unless* specified otherwise. No, it depends on the distribution maintainers intentions. In most cases "/usr/local" is the hierarchy *especially* for the software installed by user, when he's *not* installing it from distribution packages (just like you were installing SQLite from sources). But in a consequence there's even not always /usr/local/bin in your command PATH; sometimes you have to add it in your .bashrc "manually". But not always: it's rather about OpenBSD, what you wrote above, that it has "everything that matters under /usr/local" - while f.e. NetBSD has it splitted into /usr/pkg (software installed from pkgsrc) and /usr/local (all the other software) - which is better solution, IMHO. More clean and tidy. There's no fixed "once for always & all" rule. -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] can't find libsqlite3.so.0 on RHEL
On Thu, Mar 13, 2008 at 01:03:01PM -0500, P Kishor wrote: > why is it the I had to do the above? Why is LD_LIBRARY_PATH not set > automatically? And, since it wasn't set, how come other programs were > working fine (assuming that other programs in the /usr/local/bin space > were looking for libs in /usr/local/lib as well > > And, what can I do to make LD_LIBRARY_PATH permanent? You don't have to. If you run "./configure --help" before any compilation, it'll show you its switches. For example: #v+ [..] Installation directories: --prefix=PREFIX install architecture-independent files in PREFIX [/usr/local] [..] --libdir=DIR object code libraries [EPREFIX/lib] ...and some "influential environment variables" as well: LDFLAGS linker flags, e.g. -L if you have libraries in a nonstandard directory LIBSlibraries to pass to the linker, e.g. -l #v- So, you can set it all *before*. -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Improvment suggestion for "BOOLEAN"-type fields
On Thu, Mar 13, 2008 at 05:59:54PM +, Brad Stiles wrote: > > Taking into consideration a declared close relativity between SQLite and > > TCL, I would to suggest an improvement in boolean-type fields treatment. > > In my opinion, field of that type should be treated equally, when it does > > contain a values: "f", "false", 0, "no" - and, respectively: "t", "true", > > 1, "yes". > > My understanding is that SQLite provides an extension mechanism for > exactly this sort of thing, does it not? Can you be more specific? Of course, there can be something which I overlooked. > Besides which, where do you stop? "Present", "Absent", "on", "off", > "here", "there", ".t.", ".f.", "da", "nein"? What about non-zero values > generally representing true? Just look at the quoting from my previous post... what's there? - something about TCL - 2 pairs (4 values each) mentioned My understanding is, that TCL uses just 2 pairs (3 values each) for "binary-sort" variables (and not "niet", "nein", "off"...) - does it not? Additional "f" / "t" can be added for PHP. So: no worries... ;) -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Improvment suggestion for "BOOLEAN"-type fields
Taking into consideration a declared close relativity between SQLite and TCL, I would to suggest an improvement in boolean-type fields treatment. In my opinion, field of that type should be treated equally, when it does contain a values: "f", "false", 0, "no" - and, respectively: "t", "true", 1, "yes". You know, what I mean: if "checkIt" field contains value, which can be described as FALSE, a clause: "... WHERE checkIt='0'" should return "true" independent of the fact, which one of the four strings it does contain ("f", "false", 0, "no"). Of course, only in the case, when "checkIt" has been declared as "BOOLEAN", and not as "TEXT" for example. What do you think? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Beginners bad luck
On Sun, Mar 09, 2008 at 03:51:23PM +0300, Aharon (Rony) Shapira wrote: > I created a simple table named "books" in "library.db". > After entering "select * from books" I saw the data that I put into it. > > When trying to access it through php: > $file = "library.db"; > // open database file > > $handle = sqlite_open($db) or die("Could not open database"); Perhaps try to point full path to database file, then use something like: $handle = sqlite_open("$path$file", 0666, $error) -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Impossible to "catch locked state" (SQLite 3.5.6 & TCL)
On Sun, Mar 09, 2008 at 01:41:24AM +, [EMAIL PROTECTED] wrote: > The error is not occurring until later, the first time you > try to access the database using the dbcomm object. Yes, right - opening itself isn't any access yet. -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Impossible to "catch locked state" (SQLite 3.5.6 & TCL)
I'm afraid, I've discovered a bug: there's no possibility to "catch" the error "database is locked". Even, when one's trying something like: #v+ if { [catch {sqlite3 dbcomm $fullPathToDatabaseFile} err] } { puts $err } #v- There'll be no $err output, because - as it seems - "catch" won't return a value > 0. There'll be following lines printed on the console instead: database is locked while evaluating {source ./main.tcl} -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] which is faster, PHP or SQLite?
On Mon, Feb 18, 2008 at 08:33:49AM -0800, Scott Baker wrote: > The less database hits you have to do, the faster your code will be. > Getting all the data into a PHP data structure should be the way to go. But, if one really is "loading all the data into memory at once" (just "SELECT * FROM xyz") - where are, actually, any benefits from using SQL database engine? Using plain file you can have about the same: open/read_all/close... done. -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.5.5 on OpenBSD running on HPPA (HP9000) make test
On Fri, Feb 01, 2008 at 11:46:06AM -0500, Rob Sciuk wrote: > Actually, upon inspection, that may be an artifact of an earlier attempt > to run the tests as root, the "chocolate" directory was, in fact owned by > root. I'm re-running the tests, having removed that directory, and I'll > repost the results upon completion ... Hallo, sent a question to OpenBSD list, before I noticed, you're user of both OpenBSD and SQLite. Perhaps could you help me a little with my SQLite problem: I've compiled lately SQLite 3.5.6 and TCL/Tk 8.5.1 - everything seem to be working OK, with one annoying exception: everytime, when I'm leaving tclsh, when SQLite module has been loaded before ("package require sqlite3") there is an error message: "Segmentation fault (core dumped)". Do you know the origin of the problem - and (even better) the cure? Both SQLite and TCL/Tk were compiled directly from the sources (_not_ from OpenBSD's "ports"), OpenBSD 4.2/i386. TCL/Tk itself seems to be working just fine (didn't notice any problems, as of yet). Exactly the same issue I had earlier, trying SQLite 3.4.2 with TCL/Tk 8.5.0. -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serious problem: lower/upper malfunction
It's OK, found a README... :] -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serious problem: lower/upper malfunction
On Mon, Feb 11, 2008 at 02:23:54PM +0100, Z.B. wrote: > > There is an icu extension to make it work with UTF-8/UTF-16 as > > far as I know. > > Is it a special extension for SQLite? Perhaps someone could tell me, how one is supposed to use an extension http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/icu.c Yes, took that file - how to integrate that file with SQLite 3.5.4, to make lower/upper/LIKE properly working? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Serious problem: lower/upper malfunction
#v+ SQLite version 3.5.4 Enter ".help" for instructions sqlite> select lower(A); SQL error: no such column: A sqlite> select lower('A'); a sqlite> select lower('Ą'); Ą sqlite> select lower('ŻŹĆ'); ŻŹĆ sqlite> select upper('ążźć'); ążźć sqlite> select upper('asdf'); ASDF #v- As one can see, the lower/upper functions aren't working at all for 8-bit non iso8859-1 characters (as I understand, this is the origin of a bug in "LIKE", mentioned on http://www.sqlite.org/lang_expr.html#like ? ). It's a serious problem for every language other than english. Any schedule for a fix? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] "Parameter suggestion" for Makefile...
I tried to install newest sqlite3 on OpenBSD 4.2 - unfortunately, when using sqlite3 module for TCL, immediately after exiting tclsh, there's always "core dump" occurence. It seems, that sqlite needs some patching by OpenBSD port maintainers. But it wasn't a big problem, there is binary package version for OpenBSD provided, not that old (3.4.1) anyway. But that's not the point: my suggestion would be to add "deinstall" target into Makefile - especially for such occasion, to give the possibility to make clean-up automatically. -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: To VACUUM - or not to VACUUM?
On Mon, Jan 21, 2008 at 10:43:39AM -0500, Igor Tandetnik wrote: > Now, if you expect the amount of data to grow in the future, it is fine > to leave free pages in. They will be reused as necessary. In other > words, you don't need to VACUUM if you are happy with your file > retaining the high water mark size (the size reflecting the largest > amount of data it ever held). Thanks, that's I was afraid of: that there's something subtle, I'm missing. -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: To VACUUM - or not to VACUUM?
On Mon, Jan 21, 2008 at 09:59:08AM -0500, Igor Tandetnik wrote: > The point of VACUUM is to reclaim pages freed by deleting records (or by > updating with less data, e.g. by removing large BLOBs). If you don't > modify the database, there's no reason to VACUUM it. > > And of course, if you really want to, you can detach a database, open it > again directly, and vaccuum it on that new connection. OK, perhaps I wrote not enough: http://www.tcl.tk/community/tcl2004/Papers/D.RichardHipp/drh.html In the paper above I've found a ready-to-use recipe, which I'm using since, because is very practical. First I'm copying the data from "physical file" into "memory database", then I'm operating in memory - and, at the end of work, all the memory contents is written back into the file. So, I'm deleting the file contents first: SELECT name FROM sqlite_master WHERE type='table' DELETE FROM $name ...then attaching "physical" file... ATTACH /full/path/to/dbase AS app ...and then putting the "memory database" contents into file: SELECT name FROM sqlite_master WHERE type='table' INSERT INTO app.$name SELECT * FROM $name ...detaching it in the end: DETACH app So, back to my question: do I need VACUUM at all? -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: To VACUUM - or not to VACUUM?
On Mon, Jan 21, 2008 at 09:59:08AM -0500, Igor Tandetnik wrote: > DETACH doesn't do any writing, it just disassociates a given connection > from a paricular DB file. I don't understand what you mean by "real" > database. Is there any other kind? Perhaps I had false idea on what's going on, when working with attached database. If I properly understood, in such case all the operations are taking place directly in memory, and when DETACH-ing such database, only then the "in-memory" database contents is written to physical database file (if you don't like "real"), the one on the disk. If it's working the way described above - it seems to me, that the physical file is always entirely overwritten with the memory contents (when detaching). So, it's never fragmented. And in conclusion: there's no need for VACUUM. But I'm not sure, about the above... is it really working the way, I'm supposing - or I'm wrong about it? -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] To VACUUM - or not to VACUUM?
In the docs there is a remark: "VACUUM only works on the main database. It is not possible to VACUUM an attached database file". I'm not sure: if one works with "attached" database, making a "dbase-shot" of its contents to real database file, when ending the work - does there exist any need to perform VACUUM? If I'm correct - attached database, when DETACH-ed, will be saved into real database file "from the beginning to end", thus no fragmentation there at all? -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite character comparisons
On Fri, Jan 18, 2008 at 11:04:09PM +, Evans, Mark (Tandem) wrote: > Think of NULL as "value is unknown". > > With zero length blob/text, value is known: 0-length blob/text OK, I will :) -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite character comparisons
On Fri, Jan 18, 2008 at 02:13:51PM -0800, Darren Duncan wrote: > Don't read too much into that statement; I'm was not raising the > auto-trim thing. Yes, yes - I know... "Roma locuta"... -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite character comparisons
On Fri, Jan 18, 2008 at 12:32:36PM -0800, Darren Duncan wrote: > If trailing spaces were supposed to be insignificant for an equality test, > then it should not be possible to define a string value containing > trailing spaces at all. Yes, yes: quite right... the above reminds me something... ;) -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Variable substitution (TCL & SQLite)
On Fri, Jan 18, 2008 at 04:41:12PM +, Simon Davies wrote: > Thus the results are from executing the SQL > SELECT 'column1, column2, column3' FROM some_table; > which I believe tallies with the results you see. Thanks: it's probably the best picture, what is exactly going on there. -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Variable substitution (TCL & SQLite)
On Fri, Jan 18, 2008 at 02:54:40PM +0100, Michael Schlenker wrote: > 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. Yes, as I wrote already: I agree. > 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. > [..] > 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"? -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Variable substitution (TCL & SQLite)
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. > After the statement is prepared. TCL asks the statement: "What > application variables do you have, and what are their names?" > The statement tells TCL that it has a variable named "$columns". > TCL says "I have a variable by that name", and so then TCL > then calls sqlite3_bind_text() to stick the value of the $columns > TCL variable into the SQLite variable. TCL then calls sqlite3_step() > to run the statement. ...and now the contents of $columns (SQL variable) in the statement above, has been replaced with the contents of $columns (TCL variable) - because the variable names are "compatible". So - that was my assumption - we've got now: { SELECT column1, column2, column3 FROM some_table } ...which seems to be quite legal SQL statement. It seems, there's something I'm still missing(?). -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Variable substitution (TCL & SQLite)
I'm choosing desired column names dynamically, then store all the names in one variable, something like this... set columns "column1, column2, column3" The names are chosen in much more complicated way, but the above is just a variable contents example. I'm trying then to fetch the data like this: set data [dbcomm eval {SELECT $columns FROM some_table}] ...but it doesn't work. It returns that column names, not the data from the table. When I replace $columns with just the column names separated by colons - I mean: directly with $columns contents - there's no problem anymore. Not sure: the variable substitution won't work the way presented above? What should I change? Currently I made a temporary fix, fetching just all (*), then selecting the data I need - but I don't like it: I'm fetching more, than I needed, and there's an additional "cleaning" loop, which is slowing down the entire procedure. -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite character comparisons
On Thu, Jan 17, 2008 at 05:25:30PM -0500, Fowler, Jeff wrote: > By the way.. I found this snippet. If I read it right, it seems that > IGNORING trailing spaces during string comparisons is ANSI standard. I'm not sure. I was always avoiding such problem by "trim"-ming everything to be inserted; either before, or during insertion (directly in SQL query). There is remark in Postgres docs, that it does follow SQL-92 - so probably the above is a recommendation rather than a rule. In the Postgres docs I've found: #v+ The notations varchar(n) and char(n) are aliases for character varying(n) and character(n) [..] Values of type character are physically padded with spaces to the specified width n, and are stored and displayed that way. However, the padding spaces are treated as semantically insignificant. Trailing spaces are disregarded when comparing two values of type character, and they will be removed when converting a character value to one of the other string types. Note that trailing spaces are semantically significant in character varying and text values. #v- So, you can just use "character" type, to have what you need. But I'm still talking about Postgres ;) - and you were asking about SQLite. I'm trimming it all anyway... ;) -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite character comparisons
On Thu, Jan 17, 2008 at 10:43:20AM -0500, Fowler, Jeff wrote: > I've used SQL Server for over 15 years, Oracle off & on when I have no > choice, but SQLite for a couple weeks. I've just learned (today) that > SQLite respects trailing spaces when comparing two character fields. > I.e. 'SQLITE' <> 'SQLITE ' > > Is this behavior intentional? Neither SQL Server nor Oracle do this. > Just curious as to why it works this way. PostgreSQL sees the strings that way too: mydbase=> select 'str' = 'str'; ?column? -- t (1 row) mydbase=> select 'str' = 'str '; ?column? -- f (1 row) Those are different strings, anyway. Can't recall now, does there any setting to change this - but you can easily find out at their website. -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite --> PostGres
On Fri, Jan 11, 2008 at 01:49:41PM -0600, P Kishor wrote: > > If aliases were defined for PostGres then why not for MySQL, Oracle, ^^^ ?? > > MSSQL, Firebird, VistaDB, SAP/DB, DB/2, and on and on. > Good point. Not so good. The proper question would be: "why _yes_"? Can't understand this popular "contra" in the style: "if we would add this feature, then why not a hundreds of others, if aliases for Postgres - then..." see above. There are many possibilities available, so there's just a need to make conscious choice. Who said, that when aliases were defined for Postgres, it means, that similar _must_(?) then be defined for "MySQL, Oracle, MSSQL, Firebird, VistaDB, SAP/DB, DB/2, and on and on." as well? If there were real benefits from implementing such aliases for Postgres, would there be any point in avoiding this - just being scared, that "soon we'll have do the same for MySQL etc."? There'll be no need (neither compulsion) for this, if "aliases for MySQL, Firebird etc." won't mean any gain. So, the answer for the question "why not" would be in such case: "...because no profits there". No, it's not voice neither "pro", nor "contra" of the proposal (didn't write, that there are profits in Postgres' case), I read the concluding response from dr. Hipp already, where is a _real_ argument "it can break compatibility". -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: "always-trim" - feature suggestion
On Wed, Jan 09, 2008 at 08:27:13PM +0100, Aristotle Pagaltzis wrote: > Yes, actually, almost all requested and many implemented features > are by definition bloat. It depends on definition, I guess... ;) -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] "always-trim" - feature suggestion
On Wed, Jan 09, 2008 at 07:16:27PM +, [EMAIL PROTECTED] wrote: > Puneet also speaks for me on this matter. I'm glad, dr. Hipp, you haven't found my answer as offensive or abusive - of course, as I wrote, it was just a proposal, an idea - not any "demanding". But to know your opinion about this - I had to express my own first. It's all. Thanks for all your efforts, which, of course, are greatly appreciated - what I would do on this list otherwise? -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] "Can't we all just get along?" [Was: RE: [sqlite] "always-trim" - feature suggestion]]
On Wed, Jan 09, 2008 at 11:12:42AM -0800, James Dennett wrote: > To take an example (and I apologize for it being from your message, but > that's a convenient place): when you write "I ended the discussion > *yesterday* already", it's easy for me to take that as being rude > because it implies that you have the power to unilaterally terminate a > discussion on this list. Now, I think that you really meant that you > stated yesterday that *you* did not need any further discussion, and I > don't really believe that you intended to tell others that they are not > permitted to continue the discussion if they wish to do so. However, if > I were of a mind to look for "rudeness", I could find it even where none > was intended. Sorry, perhaps I should write: "from my side..." - or something like this. I meant just: "the answer was `no', and I accepted this - and there was no need to `beat that horse' any further", as someone politely wrote. > Our goals here are the same -- we want SQLite to continue to be a fine > database within its niche, and to improve. It's natural that there are > disagreements on what constitutes "improvement", and even that there > will be tensions as the forces behind those disagreements are resolved. > Let's not waste time debating perceived insults on the list? Thanks, James and Puneet; it wasn't my intention to offend anybody, perhaps partially my (not so well, still working on this) english is to blame, but I'm not native speaker - consider this, reading any of my posts -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] "always-trim" - feature suggestion
On Wed, Jan 09, 2008 at 07:15:52PM +0100, Kees Nuyt wrote: > It's a culture thing. In Eastern Europe this is the normal way > of reasoning, and isn't considered rude (my girlfriend is > Latvian from Russian parents, so I have some experience with > this kind of culture shock). I'm afraid, I don't understand. Am I expected here to agree with everyone, because I'll be seen as "rude" otherwise? That's too bad - but it's not my way to change my mind under pressure. Everyone here can have his/her own opinion - and so can I. And my opinion can differ from the opinion of the others' (and vice versa). Ending the thread (I hope), I want to repeat: I wrote *yesterday*: "OK, no problem". Everyone can check out this lists archive. The devs - especially "the Highest One" - answered "no", and it's enough for me. But my opinion about the proposed feature stays. So what? It's mine. Perhaps really - after ending my current work - I'll write that patch on my own. Fred, Ken and all the others' (even that mentioned "poor ***") can have different opinions, of their own. I didn't deny it - as (almost) all the others are denying my right to have my own opinion. I can't understand all that people tryin' to start a flamewar here *after* I ended the discussion *yesterday* already. Are they bored, and looking for some doubtful "fun"? But why exactly here? -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] "always-trim" - feature suggestion
On Wed, Jan 09, 2008 at 12:04:54PM -0600, Fred Williams wrote: > range beginning with MySQL and ending with Oracle. I do not beat a dead > horse trying to get the entire SQLite world and Dr. Hipp in particular > to "see it MY way." Give it up and look for a more feature rich DB and > leave us poor dumb Bas--rds using SQLite alone! I gave it up yesterday already. What's your point, exactly? -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] "always-trim" - feature suggestion
On Wed, Jan 09, 2008 at 08:57:18AM -0800, Ken wrote: > After reading your response to DRH, I think your being argumentative, > disrespectful and just plain rude. Thanks, I love you too. -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] "always-trim" - feature suggestion
On Wed, Jan 09, 2008 at 11:25:01AM -0500, Rob Sciuk wrote: > You know, I believe that an "embedded" SQL has a philosophy which is > inherently minimalist. ...yes, I know. > Your request specifically goes against the > philosophy of what SQLite was designed to be. DRH is working hard to > protect an ideal which has appealed to millions, and continues to do so, Of course, I appreciate work of dr. Hipp > and adding bloat will not contribute to its future success. Of course, any feature, which *you* aren't especially fond of, you can describe as "bloat". Even the most useful feature (which is useful FOR ME) - can be "bloat" for you. And vice versa. No, I'm not using *all*available* features of SQLite. Are they "bloat"? Answer yourself. I'm not quite sure, what is the point in continuing this thread, when *yesterday* already I responded to the denial sent by Darren Duncan, that it's OK, and I can understand, that the devs didn't share my point of view. If it's going to convince me, that I don't need what I need - it's pointless, because I know my own needs much better. Otherwise I've got no idea, where should it going to, when I already wrote "OK, no problem". -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] "always-trim" - feature suggestion
On Wed, Jan 09, 2008 at 12:51:16PM +, [EMAIL PROTECTED] wrote: > > Why not have a possibility to make it default > > behaviour of the SQL-engine itself, just by > > using one "pragma"? > > > > 1. It'll make my code shorter. > > But it makes the SQLite core code larger. Why should the the > SQLite core be enlarged for the convenience of a single user. It's rather hard to say, if really just "single". SQLite, as I understood, has many users; just three of them were "against", until this time. Who knows, which of the existing features are used by how many users? Yesterday one of them wrote, that (if I properly understood) he appreciates nothing more, than "job of storage". > > 2. It'll make my life easier. ;) > > But it makes my life harder. [..] Probably a bit - but it was your own choice of such way of life, anyway. ;) > > 3. It'll make the inserting operation faster, than using separate trim-s for > >every value, at SQL level. > > No it won't. The string has to be trimmed either way. Doing > it explicitly or magically in the background does not change the > amount of work that has to be done. The work does not go away > just because you cannot see it. I don't know SQLite internals - but I was supposing, that trimming inserted strings "by default", without looking at the SQL sequence first ("does there exist a `trim' for current string? Yes, so we're stripping spaces..., or perhaps not?"), should make that operation faster. Perhaps I was wrong, not knowing, as I wrote, the internals. > > 4. It can be, as I wrote, additional safety, f.e. if I forgot to set trim > >anywhere in the application. > > It might also introduce bugs, if for some reason you ever decide > that you really want a space at the beginning of some field. But I'm ready to take the risk. > > 5. In some simpler cases I could even omit entry check knowing, that strings > >will be trimmed by SQLite anyway. > > See #2 ... > > 6. It's a feature "in the spirit" of the one, which allows to insert strings > >containing single quotes, without a need to escape them first (very > >convenient! :) > > You can easily write your own sqlite3_bind_trimmedtext() interface > to accomplish this. The sqlite3_bind_trimmedtext() would first trim > spaces from both ends of the input string, then pass the result > through to sqlite3_bind_text(). No changes to the SQLite core are > needed to accomplish this. Thanks, perhaps it's a way for me to have this. Although, pay attention, you just wrote, that "it's easy and doesn't need significant changes". > > 7. It won't hurt anybody; as I wrote, it would be an option. But I'm pretty > >sure, many can (and will) appreciate that. Never seen that in any other > >database server (or engine). > > No, it would hurt others. Everybody that uses SQLite would have to > pay the penalty of a larger executable and a more complicated code > base. True, the change would be small and would not by itself be > a big deal. But hundreds of such changes would quickly balloon the > size of the SQLite library and make it so complex that we would no > longer be able to maintain it effectively. I fully agree. But my suggestion was about just one change (and very small, as you agreed) - not about hundreds. Besides: (almost) every introduced feature makes the code more complex. Should the development of the applications be stopped then? OK, as I wrote already, it was just a proposal. The others may suggest this or that - so I made use out of my freedom to make the same. -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: "always-trim" - feature suggestion
On Wed, Jan 09, 2008 at 12:01:05PM +0100, Aristotle Pagaltzis wrote: > No, it doesn???t. And the next tiny feature like yours will not > cause a mess either. And the next one after that won???t cause a > mess either. Now keep addding tiny cannot-cause-a-mess features > for two years and the result *will* be a massive mess. Keep your flamewar just to yourself, will you? Thank you. -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: "always-trim" - feature suggestion
On Mon, Jan 07, 2008 at 03:29:21PM -0800, Darren Duncan wrote: > The job of a DB isn't just storage of your data, but also to ensure > that the data it stores is always conformant to the types and > restrictions or business rules that you tell it applies to the data, > so that the data can be trusted. > > It makes a lot of sense for a DBMS to be smart and to do a lot of the > data-centric work itself. Yes, _I_ agree, that it's not just "job of storage". Just for storage purposes a plain file (or .dbf) can be quite enough in most cases. > However, that doesn't mean that the DBMS > has to have a large laundry-list of built-in functions, but more that > it provides the means for users to define the types and business > rules that it wants the DBMS to enforce for data, and routines for > data processing tasks. [..] > I don't see that auto-strip is a good candidate for that list ... not > enough of a case for that being generally useful, or too many cases > for why it would be a mis-feature whose use should not get the > encouragement from it being built-in. OK, I understand. It was just a proposal, anyway. -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: "always-trim" - feature suggestion
On Mon, Jan 07, 2008 at 10:41:35AM -0800, Ken wrote: > In the spirit of the conversation the OP wants the DB to strip out white > space. I contend that this is the applications responsibility either as > DRH suggested by using proper sql expressions. Or by using a trigger. Or > better yet by implementing inside his application if possible adding code > to strip white space. I'm not sure, whether did you notice, that I wrote about the same - and my feature suggestion was: "because it's so common operation, repeated again and again - perhaps the database server/engine could do it"? > Triggers are another route, but I recommend letting the DB do its job of > storage. [..] Forgive me for being contrary: you just wrote, that most of core functions listed on http://www.sqlite.org/lang_expr.html seems to be useless. They aren't related to "DB's job of storage" at all, anyway. -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] "always-trim" - feature suggestion
On Mon, Jan 07, 2008 at 11:37:25AM -0500, P Kishor wrote: > As someone said, the most bugfree code is the one that didn't have to > be written. While that is true from our perspective (let the db take > care of it), it is also true from the perspective of the makers of the > db (let the user take care of it). Yes: and from the perspective of the makers, perhaps this doesn't have to look that bad: it's just using some C-function to strip every string-value directly before insertion... I don't expect, that this can cause a mess. Of course, it's just a suggestion. -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] "always-trim" - feature suggestion
On Mon, Jan 07, 2008 at 03:59:52PM +, [EMAIL PROTECTED] wrote: > If you want to trim whitespace on insert, why not just say so: > >INSERT INTO table VALUES(trim(?),trim(?),trim(?)); > > Instead of: > >INSERT INTO table VALUES(?,?,?); Yes, yes - quite right. And exactly because of this I "invented" a feature I'm suggesting now. In my practice, *always* I wanted to insert values stripped out of spaces. So, when I know, that now and in the future I want always to have these strings stripped out of spaces, why not have a possibility to make it default behaviour of the SQL-engine itself, just by using one "pragma"? 1. It'll make my code shorter. 2. It'll make my life easier. ;) 3. It'll make the inserting operation faster, than using separate trim-s for every value, at SQL level. 4. It can be, as I wrote, additional safety, f.e. if I forgot to set trim anywhere in the application. 5. In some simpler cases I could even omit entry check knowing, that strings will be trimmed by SQLite anyway. 6. It's a feature "in the spirit" of the one, which allows to insert strings containing single quotes, without a need to escape them first (very convenient! :) 7. It won't hurt anybody; as I wrote, it would be an option. But I'm pretty sure, many can (and will) appreciate that. Never seen that in any other database server (or engine). -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] "always-trim" - feature suggestion
On Sun, Jan 06, 2008 at 07:39:55PM -0800, Darren Duncan wrote: > I think that this would be a horrible thing if it were the default > behaviour. A database needs to by default store and retrieve data > pristine , so that people get out what they put in, not something > else. And when the people - just by their intention - deliberately want to strip *every* stored string? Wouldn't be this much smarter done such way? > Or if you really have to have the pragma, it needs to be off by default. Yes, that's what I'm suggesting: to add an option, which could change default, just "for those about to trimming". It could (perhaps even should) be "off" by default. -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] "always-trim" - feature suggestion
I think, that it sometimes could be useful as secondary protection: a feature (perhaps another "pragma"?), which will cause stripping the spaces from beginning and end of every inserted string. But perhaps even not just only as "secondary"? Yes, usually it's done at application level; I was wondering lately, why not "from the other end"? Seems to not be that difficult to implement. In fact, almost always we want to insert into database the strings with no spaces at beginning, neither at the end - so perhaps adding a possibility to set such behaviour (using "pragma") as "default" seems to be logical? What do you think? -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Tcl: "sharing a variable" using SQLite - could be possible?
Reading the paper http://www.tcl.tk/community/tcl2004/Papers/D.RichardHipp/drh.html - especially the paragraph "SQLite Extensions Written In Tcl" - I was wondering, if could be possible to arrange something like this: Suppose, we have one SQLite database with several connections from different clients; a shared database. Knowing about SQLite's close relations to Tcl I'm wondering: could be possible to write some kind of trigger or extension in Tcl (or anything like this), which could be used to change the contents of variable, which will be "shared" by all the currently connected Tcl applications, utilizing the database? By "shared variable" I mean, that its contents can be changed by any of the users, and it can be "seen" without any polling (no "selects" neither any other SQL commands periodically executed to "refresh"); it should be seen just like any other variable in Tcl application, to - for example - set a "trace" (Tcl's "trace", of course) on such variable, allowing to trigger some kind of action. Of course, the above is just idea, not the exact expectation; one can think about some kind of trigger, which will change some variables seen by every connected client individually (but of the same name for every client) - and in the end the result will be about the same. But two conditions should be fullfilled (if possible): - It shouldn't need any special setup from client's side (or very minimal?). I would just have, let's say, a variable $messageFromSQLite available in the app's global namespace, immediately after successful connection to a database. A variable, which can be then "traced". - Any change to that variable's contents should be reflected in the "corresponding" variables of the other clients. That's why I'm writing about "sharing" - and I don't want any "polling". The "polling" is just obvious: sharing a database (doing "SELECT" and so on) is "polling" anyway, right? And I'm wondering, if database could be "active" side, not just "waiting for query". Could be something like this possible at all? -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: :Using sqlite3_progress_handler for GUI application
On Tue, Dec 18, 2007 at 02:10:02PM -0600, John Stanton wrote: > It won't be ignored. If you have an index ordered in the sequence > specified by the ORDER BY Sqlite will use that order instead of having > to assemble the result set and sort it. To say it precisely: although it won't be ignored - but (in the case mentioned above) it won't be used anyway, and won't spoil the speed gain, given by index, right? I'm asking, because tries with new database I prefer to make without indexing, and add indexes later. So I was wondering, should I then check the code again, to remove some "ORDER BY"-s, or can I skip it. -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: :Using sqlite3_progress_handler for GUI application
One more thing: suppose, I created an index for the column "name". Should I then take care to avoid ending the clauses with "ORDER BY name" - or just it doesn't matter, and it'll be automatically ignored? -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite]:Using sqlite3_progress_handler for GUI application
On Tue, Dec 18, 2007 at 12:24:25PM +, Simon Davies wrote: > Prob shd be http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor One question - using the example mentioned there: If we've created an index: CREATE INDEX example1 ON tracks(singer, title); So, it'll make the query, like below, much faster: SELECT title FROM tracks WHERE singer='Madonna' AND title<:firsttitle ORDER BY title DESC LIMIT 5; ...but I understand, that when I'll try to add in the query a field not covered by index "example1", like this: SELECT title FROM tracks WHERE singer='Madonna' AND title<:firsttitle AND year_ed > 1985; ...so, then I'm losing every profit from having "example1" index, right? Or perhaps "not quite every", and some speedup still remains - just because _some_ columns are covered by example1 anyway? -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Compiling Problem With SQLite 3.5.4
On Sat, Dec 15, 2007 at 12:48:44PM -0500, Robert L Cochran wrote: > I did a poor job of explaining this issue. GCC builds of 3.5.4 seem to > fail. I've sent Richard a bunch of log files comparing source code > builds of versions 3.5.3 and 3.5.4 showing what I believe are symptoms > of the alleged failure. If anyone wants copies of the same set of files, > feel free to contact me. Just out of curiosity made a compilation of 3.5.4: apart of some warnings (below) no errors at all. cp: uwaga: plik źródłowy `./src/btree.h' pojawił się więcej niż raz cp: uwaga: plik źródłowy `./src/hash.h' pojawił się więcej niż raz cp: uwaga: plik źródłowy `./src/sqliteInt.h' pojawił się więcej niż raz cp: uwaga: plik źródłowy `./src/vdbe.h' pojawił się więcej niż raz cp: uwaga: plik źródłowy `./ext/fts1/fts1.h' pojawił się więcej niż raz cp: uwaga: plik źródłowy `./ext/fts1/fts1_hash.h' pojawił się więcej niż raz cp: uwaga: plik źródłowy `./ext/fts1/fts1_tokenizer.h' pojawił się więcej niż raz (it means: "cp: warning: source file `..' appears more than once") Compiled on: gcc version 4.1.2 20061115 (prerelease) (Debian 4.1.1-21) Linux with kernel 2.6.22 Pentium II One additional comment: Tcl's 8.5 "package require" isn't as tollerant about version marks, as in 8.4.x line it used to. So, the proper pgIndex.tcl contents should contain the subversion digit as well, instead of just "3.5" - I mean: package ifneeded sqlite3 3.5.4 [list load /usr/local/lib/tcl8.5/sqlite3/libtclsqlite3.so sqlite3] -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Should the next release be 3.5.4 or 3.6.0?
3.5.4 -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem installing TCL bindings
On Sat, Dec 08, 2007 at 08:14:41PM +0530, yahalome wrote: > Just mkdir /usr/share/tcl8.4/sqlite3 [..] Perhaps it'll be not enough, when he got such error message: > tclsh > % load ./tclsqlite-3.5.3.so Sqlite3 > couldn't load file "./tclsqlite-3.5.3.so": ./tclsqlite-3.5.3.so: undefined > symbol: sqlite3StrICmp It looks for a libraries mismatch to me. It's very likely, that he cannot proceed without compilation from the sources on his own. -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Any advantages of "varchar()" over "text"?
On Fri, Dec 07, 2007 at 06:05:35PM -0600, P Kishor wrote: > don't know about other db, but with the ones I have worked, there is > no such thing as unlimited text width field. Oracle maxes out VARCHAR > at 4000 or 8000 (for NVARCHAR).. Of course, I realize, that "unlimited" is just theory. Perhaps I should write: "with no formal limit set" rather. So, I was just wondering, whether (or not) there is a noticeable difference when operating on - let's say - VARCHAR(255), rather than on just TEXT. Never made such measurements by myself. -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Any advantages of "varchar()" over "text"?
On Fri, Dec 07, 2007 at 05:43:49PM -0600, P Kishor wrote: > I believe it used to... fixed width (CHAR) was quicker than VARCHAR > while the latter was more space efficient, obviously. But he didn't compare variable, but limited width (VARCHAR()) - with variable unlimited width fields (TEXT)? -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Any advantages of "varchar()" over "text"?
On Fri, Dec 07, 2007 at 04:50:19PM -0600, P Kishor wrote: > I may be wrong, but my understanding is that other than INTEGER > PRIMARY KEY, SQLite doesn't give a rat's batuti about what you define Perhaps it's related to "closed relations" between SQLite and Tcl. Well, it's even more comfortable then. Less things to take care of. -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -