Re: [sqlite] Searching with like for a specific start letter
Just to complete the thread, I decided for the following: SELECT substr(normalized,1,1) AS letter, COUNT(*) from entry group by letter order by letter; Thank you ALL! 2010/4/26 Alberto Simões <hashas...@gmail.com>: > Hello > > Thank you all for the answers. > > On Mon, Apr 26, 2010 at 12:59 PM, Black, Michael (IS) > <michael.bla...@ngc.com> wrote: >> When you say "running on the fly" do you mean running from an sqlite3 >> command prompt? > > I mean somebody will query it and will be waiting for the answer. > >> Or are you doing this in some other programming language? > > Perl > >> Why in the world would you use a database to do this? > > Probably I am not using a database for this, but for something else, > and I want to add a feature to let users read some statistics. > >> >> >> Hello >> >> I am running on the fly a query to count the number of words starting >> with one of the 26 letters. >> >> I am doing the usual SELECT COUNT(term) from dictionary WHERE normword >> LIKE "a%" (for the 26 letters) >> >> normword is the term normalized without accents and the like >> >> >> Is there any way to make this query faster? It is taking about 10 >> second for 140K entries. >> >> One idea is to add a column named 'letter' and SELECT COUNT(letter) >> from dictionary WHERE letter = 'a'. >> But are there other solutions? >> >> Thanks >> -- >> Alberto Simões >> ___ >> 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 >> >> > > > > -- > Alberto Simões > -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Searching with like for a specific start letter
Hello Thank you all for the answers. On Mon, Apr 26, 2010 at 12:59 PM, Black, Michael (IS) <michael.bla...@ngc.com> wrote: > When you say "running on the fly" do you mean running from an sqlite3 command > prompt? I mean somebody will query it and will be waiting for the answer. > Or are you doing this in some other programming language? Perl > Why in the world would you use a database to do this? Probably I am not using a database for this, but for something else, and I want to add a feature to let users read some statistics. > > > Hello > > I am running on the fly a query to count the number of words starting > with one of the 26 letters. > > I am doing the usual SELECT COUNT(term) from dictionary WHERE normword > LIKE "a%" (for the 26 letters) > > normword is the term normalized without accents and the like > > > Is there any way to make this query faster? It is taking about 10 > second for 140K entries. > > One idea is to add a column named 'letter' and SELECT COUNT(letter) > from dictionary WHERE letter = 'a'. > But are there other solutions? > > Thanks > -- > Alberto Simões > ___ > 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 > > -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Searching with like for a specific start letter
Hello I am running on the fly a query to count the number of words starting with one of the 26 letters. I am doing the usual SELECT COUNT(term) from dictionary WHERE normword LIKE "a%" (for the 26 letters) normword is the term normalized without accents and the like Is there any way to make this query faster? It is taking about 10 second for 140K entries. One idea is to add a column named 'letter' and SELECT COUNT(letter) from dictionary WHERE letter = 'a'. But are there other solutions? Thanks -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backuping SQLite database in VC system
Hello 2010/3/16 Alberto Simões <hashas...@gmail.com>: > Hello > > I was pondering to backup an SQLite database using a version control > system (probably SVN). Of course that if I add the binary file SVN > will diff the entire file. That is not a good option. > > I though on dumping the full database to SQL and put that file into svn. > > I would like to ask: > - any comment on this? is it just stupid? > - if it is not stupid, how can I dump a full sqlite database to a > text file that could be used later to restore the database? Well, .dump might help. My bad :) > Cheers > ambs > -- > Alberto Simões > -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Backuping SQLite database in VC system
Hello I was pondering to backup an SQLite database using a version control system (probably SVN). Of course that if I add the binary file SVN will diff the entire file. That is not a good option. I though on dumping the full database to SQL and put that file into svn. I would like to ask: - any comment on this? is it just stupid? - if it is not stupid, how can I dump a full sqlite database to a text file that could be used later to restore the database? Cheers ambs -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] String Vs Integer Index
On Wed, Feb 10, 2010 at 5:42 PM, Virgilio Fornazin <virgilioforna...@gmail.com> wrote: > I think you should be asking 'How fast is SQLite locating a key in a integer > column index vs a string index'... > > Generally, integer keys are faster in key lookups than string keys, because > comparing a integer value is a > single CMP CPU instruction versus a more-complicated string comparison (that > can be virtually unlimited in size). Yes, I know it should be faster.. I just would like to have an idea of how fast to know how relevant is an indirection table (from string to integer). But probably the best is to try and compare :P > > On Wed, Feb 10, 2010 at 15:38, Simon Slavin <slav...@bigfraud.org> wrote: > >> >> On 10 Feb 2010, at 5:19pm, Alberto Simões wrote: >> >> > I know I can benchmark myself this question, but I am sure somebody >> > did that already. >> > >> > Supose a table with a key that is a string (say, words from 1 to 10 >> > characters) or a table with a key of integers. >> > >> > How different is the efficiency on fetching one record on these tables? >> >> How are you fetching the record ? Do you have a SELECT command that looks >> up the record using a WHERE clause matching a key value ? Is there an index >> on the key column ? >> >> Simon. >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] String Vs Integer Index
Howdy SQLite users, I know I can benchmark myself this question, but I am sure somebody did that already. Supose a table with a key that is a string (say, words from 1 to 10 characters) or a table with a key of integers. How different is the efficiency on fetching one record on these tables? Thanks -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Temporal SQLite database
Hello, Doug Thanks for your message. That helped a lot. ambs On Tue, Sep 15, 2009 at 6:47 PM, Douglas E. Fajardo < dfaja...@beyondtrust.com> wrote: > I'm not sure if you are looking to make a entry unique, or determine the > order in which the entries occurred. In either case, be aware - time can go > *backwards* on a system, especially if it is being syncd to an outside > source such as with NTP. > > Normally the 'jitter' is under a second, but exceptions do occur (including > the one where the Sysadmin changes the system clock!). Note that a backward > "jitter" could (conceivably) result in the same timestamp occurring twice. > Also, depending on the resolution of the clock (which may vary depending on > installation options) it may be possible for two entries to occur at the > same 'time'. As a result, I've sworn off using the time for anything more > than a logging label. > > I've had good success with the auto-increment approach. As for code to > hide it, just set auto-incrementing up on a field in the schema, and there > is no extra code involved. > > *** Doug F. > > > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto: > sqlite-users-boun...@sqlite.org] On Behalf Of Alberto Simões > Sent: Tuesday, September 15, 2009 4:38 AM > To: General Discussion of SQLite Database > Subject: [sqlite] Temporal SQLite database > > Dear SQLite users, > > I am preparing a temporal database. Basically, entries will have a compound > key composed by the real key and some kind of time stamp. This is not > complicated to do, but I would like to know if anybody did something > similar > and if: > - used the number of non-leap seconds since whatever time the system > considers to be the epoch, as time stamp > - used an auto-increment value as the time-stamp, and adding the time > stamp > as an auxiliary column, > - knows of any Perl module that hides this time management. > > Thanks for your ideas. > ambs > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > __ > This email has been scanned by the MessageLabs Email Security System. > For more information please visit http://www.messagelabs.com/email > __ > _______ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Temporal SQLite database
Dear SQLite users, I am preparing a temporal database. Basically, entries will have a compound key composed by the real key and some kind of time stamp. This is not complicated to do, but I would like to know if anybody did something similar and if: - used the number of non-leap seconds since whatever time the system considers to be the epoch, as time stamp - used an auto-increment value as the time-stamp, and adding the time stamp as an auxiliary column, - knows of any Perl module that hides this time management. Thanks for your ideas. ambs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.6.17
On Thu, Sep 3, 2009 at 10:30 PM, Nicolas Williams <nicolas.willi...@sun.com>wrote: > On Thu, Sep 03, 2009 at 03:27:38PM +0100, Simon Slavin wrote: > > On 3 Sep 2009, at 1:38am, P Kishor wrote: > > > well, I think the problem is with the sqlite3 command line tool. > > I agree. I just checked it with OS X 10.6, which comes with SQLite > > version 3.6.12, and got the same problem: you can't type accented > > characters into the sqlite3 tool. So I guess the sqlite3 tool uses a > > version of readline (or whatever) which doesn't do this properly. > > However, the SQLite database system does handle unicode characters > > just fine. > > It's almost certainly readline. I tried SQLite3 on Solaris without > readline and it handles non-ASCII UTF-8 input just fine. > It's all Mac readline fault. Compile a new one, and recompile sqlite with it (check another e-mail mine about how to compile sqlite with a different readline version). It will work :) -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.6.17
Simon, try to compile a gnu version of readline, and then compile sqlite againt it. It should do the trick. All the best, Alberto On Wed, Sep 2, 2009 at 8:27 PM, P Kishor <punk.k...@gmail.com> wrote: > On Wed, Sep 2, 2009 at 10:55 AM, Simon > Slavin<slav...@hearsay.demon.co.uk> wrote: > > > > On 2 Sep 2009, at 2:39pm, Alberto Simões wrote: > > > >> Can you please send me your env? > > > > It works fine for me on Leopard, and I have done no special > > environment setting at all: I use the default Unix settings and the > > default SQLite settings for 10.5. > > > > You might want to check which shell you're using (csh ? bash ?) to > > see if the shell is filtering out your funny characters for you. > > > actually, does not work for me. I am using bash, and while both in > Terminal.app and in iTerm.app, the character encoding is set to > Unicode (UTF-8), none of the accented characters work. When I type > 'café', for example, upon typing Option-e, I get the accent mark, but > when I type e, instead of getting é, I get nothing. It wipes the > accent, and the cursor doesn't move. > > > > > > Simon. > > -- > > http://www.hearsay.demon.co.uk | I'd expect if a computer was involved > > | it all would have been much worse. > >No Buffy for you.|-- John "West" McKenna > >Leave quickly now. -- Anya | THE FRENCH WAS THERE > > > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > Puneet Kishor http://www.punkish.org > Carbon Model http://carbonmodel.org > Charter Member, Open Source Geospatial Foundation http://www.osgeo.org > Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor > Nelson Institute, UW-Madison http://www.nelson.wisc.edu > --- > Assertions are politics; backing up assertions with evidence is science > =========== > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Compiling SQLite with custom readline
Hello I am trying to find out why my SQLite does not accept non ascii characters. So, I compiled readline 6.0 and tried to compile sqlite with it. As you might imagine at first SQLite detects the system (MAC OS) readline library. To use a custom readline I noticed in the configure script: --with-readline-lib specify readline library --with-readline-inc specify readline include paths But I found out these flags not working as one usually expect on a configure script. I was expecting something like: --with-readline-lib=/opt/local/lib --with-readline-inc=/opt/local/include/readline but what the configure script was expecting was something like --with-readline-lib="-L/opt/local/lib -lreadline" --with-readline-inc="-I/opt/local/include/readline" (well, at least it compiled this way) Therefore, this mail is a suggestion of making that two documentation line clearer, probably with an example of what is expected. Oh, by the way, the non-ascii characters are now recognized :) Cheers Alberto -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.6.17
On Wed, Sep 2, 2009 at 2:52 PM, D. Richard Hipp <d...@hwaci.com> wrote:
>
> On Sep 2, 2009, at 9:46 AM, D. Richard Hipp wrote:
> >>>
> >>> It worked for me before. No idea what is happenning.
> >> Can you please send me your env?
> >
> >
>
>
> sqlite-imac:bld drh$ ./sqlite3
> SQLite version 3.6.17
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table t1(x);
> sqlite> insert into t1 values('Alberto Simões ξ € 夷');
> sqlite> select * from t1;
> Alberto Simões ξ € 夷
> sqlite> select hex(x) from t1;
> 416C626572746F2053696DC3B5657320CEBE20E282AC20E5A4B7
> sqlite> sqlite-imac:bld drh$ env
> MANPATH=/usr/share/man:/usr/local/share/man:/usr/X11/man
> TERM_PROGRAM=Apple_Terminal
> TERM=xterm-color
> SHELL=/bin/bash
> TMPDIR=/var/folders/9H/9H+KCJI3F5K1xNlxD8BM7TI/-Tmp-/
> Apple_PubSub_Socket_Render=/tmp/launch-CFpjwW/Render
> TERM_PROGRAM_VERSION=240.2
> USER=drh
> COMMAND_MODE=unix2003
> SSH_AUTH_SOCK=/tmp/launch-FWX7Up/Listeners
> __CF_USER_TEXT_ENCODING=0x1F5:0:0
> PATH=/opt/local/bin:/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/usr/
> X11/bin
> PWD=/Users/drh/sqlite/bld
> LANG=en_US.UTF-8
> SHLVL=1
> HOME=/Users/drh
> LOGNAME=drh
> VISUAL=e
> DISPLAY=/tmp/launch-GNoo4x/:0
> SECURITYSESSIONID=a8c400
> _=/usr/bin/env
> OLDPWD=/Users/drh/sqlite/sqlite
> sqlite-imac:bld drh$
>
Thank you. Nothing easy to detect.
I'll dig a little more on it.
--
Alberto Simões
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.6.17
Hello, drh.
On Wed, Sep 2, 2009 at 2:28 PM, D. Richard Hipp <d...@hwaci.com> wrote:
>
>
>
> Works for me:
>
> sqlite-imac:bld drh$ ./sqlite3
> SQLite version 3.6.17
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table t1(x);
> sqlite> insert into t1 values('Alberto Simões ξ € 夷');
> sqlite> select * from t1;
> Alberto Simões ξ € 夷
> sqlite>
>
> It worked for me before. No idea what is happenning.
Can you please send me your env?
Thank you
Alberto
--
Alberto Simões
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite 3.6.17
Hellows Noticed today (with SQLite 3.6.17) that I can't insert non latin1 character using the sqlite3 shell. I am running under Mac OS Leopard (not yet the white one), and using an unicode terminal. This worked before, but not sure with which version... :-/ Thanks for any hint. Alberto -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Near misses
Hello
2009/6/26 Alberto Simões <hashas...@gmail.com>:
> I am trying to find words in a dictionary stored in sqlite, and trying
> a near miss approach.
> For that I tried an algorithm to create patterns corresponding to
> Levenshtein distance of 1 (edit distance of 1).
> That means, one adition, one remotion or one substitution.
>
> For that, my script receives a word (say, 'car') and generated all
> possible additions and remotions, and substitutions:
>
> Additions: _car c_ar ca_r car_
> Substitutions: _ar c_r ca_
> remotions: ar cr ca
>
> Then, the script constructs an SQL query:
>
> SELECT DISTINCT(word) FROM dict WHERE word = "ar" OR word = "ca" OR
> word LIKE "_car" OR word LIKE "c_r" OR word = "cr" OR word LIKE "_ar"
> OR word LIKE "ca_r" OR word LIKE "c_ar" OR word LIKE "ca_" OR word
> LIKE "car_";
>
> And this SQL quer works... but not as quickly as I need (specially
> because the speed is proportional to the word size).
My current solution is to make all combinations of words: having a
list of letters, cycle them and substitute the underscore by that
letter.
The resulting list is being searched with SELECT word FROM dict
WHERE word IN ('worda','wordb',wordc') and so on.
While for big words this list can be big (it gets above 1000 easily)
the query executes very fast.
Hope this might be helpful for somebody.
Cheers
Alberto
--
Alberto Simões
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Near misses
On Fri, Jun 26, 2009 at 10:00 PM, Jean-Christophe Deschamps<j...@q-e-d.org> wrote: > Hi, > > I'm currently finishing an C extension offering, among other functions, > a "TYPOS" scalar operator which is meant to perform just that, and a > bit more. > > Internally, it applies a Unicode fold() function, a Unicode lower() > function and then computes the Damerau-Levenshtein distance between the > strings. It returns the number of insertions, omissions, change and > transposition (of adjacent letters only). > > If the reference string is 'abcdef', it will return 1 (one typo) for > 'abdef' missing c > 'abcudef' u inserted > 'abzef' c changed into z > 'abdcef' c & d exchanged > > It will also accept a trailing '%' in string2 acting as in LIKE. > > You can use it this way: > > select * from t where typos(col, 'levencht%') <= 2; > > or this way > > select typos(str1, str2) > > The code currently makes use of a couple of Win32 functions, which > should have Un*x equivalent. It runs at really decent speed even if I > didn't fight for optimization. It will obviously outperform any SQL > solution by a large factor. > > I can't promise a very clean version tomorrow but just mail if you're > interested in the C source. You could tailor it to your precise needs > easily. I can't help and test it in the next few days. But I would be happy to test and give some results about it Cheers -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Near misses
On Fri, Jun 26, 2009 at 3:43 PM, Simon Slavin<slav...@hearsay.demon.co.uk> wrote: > > On 26 Jun 2009, at 12:25pm, Alberto Simões wrote: > >> one adition, one remotion or one substitution > > I am always amazed at how well people use English. For your word > 'remotion' you probably mean 'removal' or 'omission'. You have joined > the two possibilities together ! Probably I just tried a word similar to the Portuguese word: remoção ;) > You could write a program to prepare another table in the same > database with your near-misses in. In other words, to take each word > in the dictionary (like 'car') and put entries in this other table for > each near miss you wish to accept: Yep. That is one of my current options. Was just wondering (and thus my mail) about any optimization I could do in my query. Thanks ;) Alberto -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Near misses
Hello On Fri, Jun 26, 2009 at 3:00 PM, Igor Tandetnik<itandet...@mvps.org> wrote: > Alberto Simoes wrote: >> For that, my script receives a word (say, 'car') and generated all >> possible additions and remotions, and substitutions: >> >> Additions: _car c_ar ca_r car_ >> Substitutions: _ar c_r ca_ >> remotions: ar cr ca >> >> Then, the script constructs an SQL query: >> >> SELECT DISTINCT(word) FROM dict WHERE word = "ar" OR word = "ca" OR >> word LIKE "_car" OR word LIKE "c_r" OR word = "cr" OR word LIKE "_ar" >> OR word LIKE "ca_r" OR word LIKE "c_ar" OR word LIKE "ca_" OR word >> LIKE "car_"; >> >> And this SQL quer works... but not as quickly as I need (specially >> because the speed is proportional to the word size). > > I'd try writing a custom function that figures out whether two words are > "close enough" (most of the time, you should be able to declare a > negative by looking at just two first characters), then do > > select word from dict where closeEnough(word, 'car'); Hmms, need to check how to do that. But that would mean call the function to all words in the database (110K atm). > I also don't see why you need DISTINCT. Do you have duplicate words in > dict? Yes, I have. Forgot to explain ;) Thanks -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Near misses
Hello. I am trying to find words in a dictionary stored in sqlite, and trying a near miss approach. For that I tried an algorithm to create patterns corresponding to Levenshtein distance of 1 (edit distance of 1). That means, one adition, one remotion or one substitution. For that, my script receives a word (say, 'car') and generated all possible additions and remotions, and substitutions: Additions: _car c_ar ca_r car_ Substitutions: _ar c_r ca_ remotions: ar cr ca Then, the script constructs an SQL query: SELECT DISTINCT(word) FROM dict WHERE word = "ar" OR word = "ca" OR word LIKE "_car" OR word LIKE "c_r" OR word = "cr" OR word LIKE "_ar" OR word LIKE "ca_r" OR word LIKE "c_ar" OR word LIKE "ca_" OR word LIKE "car_"; And this SQL quer works... but not as quickly as I need (specially because the speed is proportional to the word size). Any hint on how to speed up this thing? THank you Alberto -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite 80x15 badge.
Hello If anyone find it interesting, please use. http://dicionario-aberto.net/sqlite-db.png Also, be free to put it on sqlite.org or something. Cheers Alberto -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fixing a database - solved
Hello On Wed, Apr 22, 2009 at 9:17 PM, Igor Tandetnik <itandet...@mvps.org> wrote: > Alberto Simoes <hashas...@gmail.com> > wrote: >> I am not sure what happened, but I have a database that opens >> correctly with sqlite3 shell, I can select and query it, but I can't >> edit it. I get Disk I/O error. The disk is not full. > > You don't have write permissions to the file, perhaps? It had to be mine stupidity. I was testing as root, and as root I have disk space. As user I don't (root quota). Thanks and sorry for the generated spam. Alberto -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fixing a database
Hello. I am not sure what happened, but I have a database that opens correctly with sqlite3 shell, I can select and query it, but I can't edit it. I get Disk I/O error. The disk is not full. Is there any way to fix the database? I think some kind of flag somewhere in the database has a wrong value. But not sure :) Thank you Alberto -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] disk I/O error...?
Hello. On Sat, Apr 11, 2009 at 3:04 PM, Jim Wilcoxson <pri...@gmail.com> wrote: > You'll get this if you have a database with an active journal > (incomplete transactions) and you don't have write access to the > database. In other words, the database needs a rollback from some > prior operations done under a different userid, but now you don't have > write access to do the rollback. Hmms, these databases where being created and were shut down by other script (kill). It might happen that they have some kind of lock active. I can't find any lock/journal file.Thus, it should probably be inside the database file. Is there anyway to 'clean' a database status? Thank you Alberto > > To me, it should be a permission error instead, to make it clear > what's going on. > > Jim > > On 4/11/09, Alberto Simões <hashas...@gmail.com> wrote: >> Hello >> I am getting disk I/O error with: >> >> [a...@search1 align]$ du -hs T.3.sqlite >> 122M T.3.sqlite >> [a...@search1 align]$ sqlite3 T.3.sqlite >> SQLite version 3.6.6 >> Enter ".help" for instructions >> Enter SQL statements terminated with a ";" >> sqlite> .schema >> CREATE TABLE trigrams (w1,w2,w3,occs); >> CREATE INDEX idxT3w1 ON trigrams (w1); >> sqlite> CREATE INDEX idxT3w2 ON trigrams (w2); >> SQL error: disk I/O error >> sqlite> [a...@search1 align]$ df -h . >> Filesystem Size Used Avail Use% Mounted on >> /dev/sdc1 148G 42G 100G 30% /export3 >> [a...@search1 align]$ >> >> I tried to get I/O errors with other commands (for instance, yes > _) >> but couldn't get any error. >> >> Any hint on what I can check to discover the problem? >> Thank you >> Alberto >> >> -- >> Alberto Simões >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > -- > Software first. Software lasts! > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] disk I/O error...?
Hello I am getting disk I/O error with: [a...@search1 align]$ du -hs T.3.sqlite 122MT.3.sqlite [a...@search1 align]$ sqlite3 T.3.sqlite SQLite version 3.6.6 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .schema CREATE TABLE trigrams (w1,w2,w3,occs); CREATE INDEX idxT3w1 ON trigrams (w1); sqlite> CREATE INDEX idxT3w2 ON trigrams (w2); SQL error: disk I/O error sqlite> [a...@search1 align]$ df -h . FilesystemSize Used Avail Use% Mounted on /dev/sdc1 148G 42G 100G 30% /export3 [a...@search1 align]$ I tried to get I/O errors with other commands (for instance, yes > _) but couldn't get any error. Any hint on what I can check to discover the problem? Thank you Alberto -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ANN - DBD::SQLite version 1.20
I was just wondering, is it a good idea to bundle SQLite in the Perl Module? Wasn't it better to use the system version, if it is available? (well, I think this isn't done as I have it on the system and DBD::SQlite just compiled it...) Cheers Alberto 2009/4/10 Alberto Simões <hashas...@gmail.com>: > Hello. > > Thank you for this new update. Thanks for taking into consideration my > request on collations. Yupy! > > On Thu, Apr 9, 2009 at 6:01 PM, Stefan Evert > <stefan.ev...@collocations.de> wrote: >> Thanks a lot, Darren! >> >> This version also fixes the problems I've been having with segfaults >> in the create_function test that I've been worried about. I've only >> tried version 1.22_02, but according to the change log, the bug was >> fixed before the public release of 1.20. >> >> Best, >> Stefan >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > Alberto Simões > -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ANN - DBD::SQLite version 1.20
Hello. Thank you for this new update. Thanks for taking into consideration my request on collations. Yupy! On Thu, Apr 9, 2009 at 6:01 PM, Stefan Evert <stefan.ev...@collocations.de> wrote: > Thanks a lot, Darren! > > This version also fixes the problems I've been having with segfaults > in the create_function test that I've been worried about. I've only > tried version 1.22_02, but according to the change log, the bug was > fixed before the public release of 1.20. > > Best, > Stefan > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UTF-8 Collation anyone?
Hello I am not yet sure how to write a collation and, worst, how to integrate it with DBD::SQLite. But, does anybody write a UTF-8 collation that I can use as base? Or, yet better, does anyone has experience on using a collation with DBD::SQLite? TIA. -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Random Syntax changed in 3.6.11?
Hello, DRH, On Mon, Mar 2, 2009 at 6:32 PM, D. Richard Hipp <d...@hwaci.com> wrote: > The RANDOM() function takes zero arguments. This has always been the > case. But 3.6.11 has begun to enforce that rule. Prior to 3.6.11, > the error was ignored. Oh. That explains it. Thanks -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Random Syntax changed in 3.6.11?
Hello I am using random as: SELECT word FROM dict ORDER BY RANDOM(term) LIMIT 1; And I am getting: DBD::SQLite::db prepare failed: wrong number of arguments to function RANDOM()(1) at dbdimp.c line 271 at OpenDict.pm line 64. (yes, using it through DBD::SQLite, but I do not think that is the problem). Thanks Alberto -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fuzzy Matching
Hi, Folks. While I do not have any expertise to compare levenshtein with other approaches, my current approach for fuzzy matching is, taking a word w, compute all possible near words with distance n. This is known to generate a big number of near misses, but it is quicker to then search for these words on the database, than to compare each entry in the databse with the word being searched. Well, not sure if I made it clear. With the soundex option that DRH explained, it is possible to compute before hand the soundex for each entry, and store it in a column. Then, it should be easy to grab all words with the same soundex quickly. Cheers ambs On Mon, Jul 7, 2008 at 5:46 AM, Stephen Woodbridge <[EMAIL PROTECTED]> wrote: > OK, thanks for that. I had seen the references to the n-gram stuff and > just started reading about them and not my head is ready to explode! > Between n-grams, bit vectors, Bloom filters, perfect hashes, lots of > academic papers spouting fancy equations and statistics, I'm not sure > this is gelling into any course of action for me. While this technology > seems very cool, I still get the sense that you have to scan each > documents n-gram vector against the query n-gram vector to get a set of > potential documents that might have what I want in them. Or am I missing > something? What? While this type of search is probably faster then some > alternatives, it does not lend itself to the fundamentals of RDBMS or > indexed searches. > > I would appreciate any thoughts on how to proceed with this. > > Levenshtein edit distance is a good way to score the results of a fuzzy > match. I have also used metaphone and double metaphone and soundex in > the past for fuzzy searching. > > -Steve > > Harold Wood wrote: >> I cant go into too much detail because of my current job, but for >> fuzzy matching levenstien isnt very good, you need to try looking >> into ngram matching techniques, it is absolutely awesome in reducing >> over/under matches. >> >> Woody >> >> --- On Sat, 7/5/08, Stephen Woodbridge <[EMAIL PROTECTED]> >> wrote: >> >> From: Stephen Woodbridge <[EMAIL PROTECTED]> Subject: Re: >> [sqlite] Fuzzy Matching To: "General Discussion of SQLite Database" >> <sqlite-users@sqlite.org> Date: Saturday, July 5, 2008, 11:24 PM >> >> Stephen Woodbridge wrote: >>> I would be interested in having something like this also. >>> >>> What I don't understand in your approach is how you compute the >>> (Levenstein) distance during a search. It seems like you have a >>> fixed set of tokens from your document text and these are indexed. >>> Then you have a query token the you want to compare to the index >>> based on some fuzzy distance. Since every query can be different I >>> think you have to compute the distance for every key in the index? >>> that would require doing a full index scan. >>> >>> If there ware a function that you could run a token through that >>> would given you that tokens "location" in some space then you could >>> >> generate a >>> similar "location" for the query token and then use the rtree >> and >>> distance. I'm not aware of any such functions, but my expertise is >> more >>> in GIS the search searching. >> >> Hmmm, that was supposed to say text searching. >> >>> Thoughts? >>> >>> Best, -Steve >>> >>> Martin Pfeifle wrote: >>>> Hi, I think there is nothing available except FTS. Doing a full >>>> table scan and computing for each string the (Levenstein) >>>> distance to the query object is too time consuming. So what I >>>> would like to see is the implementation of a generic metric index >>>> which needs as one parameter a metric distance function. Based on >>>> such a distance function you could then do similarity search on >>>> any objects , e.g. images, strings, etc. One possible index would >>>> be the M-tree (which you can also organize relational as it was >>>> done with the R*-tree). The idea is that you have a hierarchical >>>> index and each node is represented by a database object o and a >>>> covering radius r reflecting the maximal distance of all objects >>>> in that subtree to the object o. If you do a range query now, you >>>> compute the distance of your query object to the object o. If >>>> this distance minus the coverage radius r is bigger than your >>>> query range you can prune that subtree. You can either implement >>>> such a similarity mod
[sqlite] Fuzzy Matching
Hello Although I am quite certain that the answer is that SQLite does not provide any mechanism to help me on this, it doesn't hurt to ask. Who know if anybody have any suggestion. Basically, I am using SQLite for a dictionary, and I want to let the user do fuzzy searches. OK, some simple Levenshtein distance of one or two would do the trick, probably. I imagine that SQLite (given the lite), does not provide any kind of nearmisses search. But probably, somebody here did anything similar in any language? Cheers Alberto -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CREATE INDEX time degradation
On Wed, Jun 18, 2008 at 3:15 PM, Jay A. Kreibich <[EMAIL PROTECTED]> wrote: > On Wed, Jun 18, 2008 at 01:39:34PM +0100, Alberto Sim?es scratched on the > wall: >> [...] > > What is the original ordering of the data? In order to index the > data, it needs to be sorted, and the sort can get expensive due to > page thrashing. In fact it should be random for all columns. >> Can it be caused by a larger db file? > > I suppose, although I'm not sure that's directly related. Index > creation is very sensitive to the number of free pages in the page > cache, but for an index of that size, chances are you're always over. Disk access on that cluster was always a problem. It might be that as well. > Increase the cache size (see PRAGMA commands). Actually, this is > good practice anytime you need to create an index. Thanks Alberto -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] CREATE INDEX time degradation
Hi. I have a simple table w1,w2,w3,w3, occs and I need indexes for columns w1 to w4. Creating them I got: $ time sqlite3 _.db 'CREATE INDEX wi1 ON tet (w1)' real3m48.159s user3m34.739s sys 0m5.590s $ time sqlite3 _.db 'CREATE INDEX wi2 ON tet (w2)' real9m0.107s user3m44.161s sys 0m27.934s $ time sqlite3 _.db 'CREATE INDEX wi3 ON tet (w3)' real15m2.199s user4m8.193s sys 1m5.937s $ time sqlite3 _.db 'CREATE INDEX wi4 ON tet (w4)' real26m57.155s user4m30.153s sys 3m19.778s As these indexes should be completely independent, why this time degradation? Can it be caused by a larger db file? To have an idea, _.db at the end of this process: $ du -hs _.db 1.8G_.db And, if this time degradation is derived from the file size: is there any trick to make it faster? Cheers Alberto -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Importing table
On Tue, Jun 17, 2008 at 6:33 PM, P Kishor <[EMAIL PROTECTED]> wrote: > On 6/17/08, Alberto Simões <[EMAIL PROTECTED]> wrote: >> Hi, Folks >> I am thinking on creating another text file with 18 399 392 INSERT >> commands. >> Would that be the best method? > > Look at the .read command in the sqlite3 command line program. > OK, then INSERTs is the way. Thanks -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Importing table
Hi, Folks I have a text file with 18 399 392 lines. Each line contains five fields. Four are a compound key, the other is just data. What is the best way to import this to sqlite? I am thinking on creating another text file with 18 399 392 INSERT commands. Would that be the best method? (by the way, I am expecting to have bigger tables in the near future) Thanks Alberto -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite Announcements
Hi Is it just me, or SQLite announcements for new releases are not being sent to this list? At least I didn't see 3.5.0, 3.5.1 and 3.5.2 announcements. I am aware of them just because complain about them :) Cheers ambs -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite website
I like (2), but I do not like much the dropdown menus. Cheers On Nov 10, 2007 8:49 AM, James Darpinian <[EMAIL PROTECTED]> wrote: > > I put up 4 variations. Please, everyone, offer your opinions: > > > >(1) http://sqlite.hwaci.com/v1/ No CSS of any kind. > >(2) http://sqlite.hwaci.com/v2/ CSS menus with rounded corners > >(3) http://sqlite.hwaci.com/v3/ CSS menus with square corners > >(4) http://sqlite.hwaci.com/v4/ CSS font specification only > > I like option 2 the best; it looks modern and simple. However, I > don't think the menus are necessary; the toolbar is fine without them. > I decided to try turning the page into a pure CSS layout with no > tables. The result is available at > http://www.cs.hmc.edu/~jdarpini/sqlite.html I tested it in IE6; 3 > hopefully unobtrusive hacks were needed for it to render decently. > > James > > - > To unsubscribe, send email to [EMAIL PROTECTED] > --------- > > -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Disabling tcl bug?
Hi, When trying to install 3.4.2 without tcl (--disable-tcl) it all works ok during compilation, but when trying to install, the install script tries to run the tclsh command. Is this normal? Thank you -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] how do i generate a uniqueidentifier ?
Hi, Chase.
On 7/27/07, Chase <[EMAIL PROTECTED]> wrote:
>
> idfoo bar
> ___
>
> {0109--0010-8000-00AA006D2EA4}"Aaa""Bbb"
>
I can't understand why do you need a big identifier like that, instead
of just a different integer as a key (as said below).
> > {fieldname} INTEGER NOT NULL PRIMARY KEY
Note that the integer will be quite faster.
Cheers
--
Alberto Simões
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Importing a big text file (CSV?)
On 7/19/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: "=?ISO-8859-1?Q?Alberto_Sim=F5es?=" <[EMAIL PROTECTED]> wrote: > Ok, for future reference (drh, please, it would be nice to add this to > the web site) That is why we have wiki (http://www.sqlite.org/cvstrac/wiki) so that you can add things like this yourself. We have a wiki? Nice :) -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite on Mac
Hi On 7/19/07, Ahmed Sulaiman <[EMAIL PROTECTED]> wrote: Hi all, Does SQLite work on Mac, and if yes, is there any Mac enabled version that I could download? While there are fink and darwin ports, I would suggest you to compile it from scratch. It should work well. In my case I just needed to deactivate TCL bindings. Cheers Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Importing a big text file (CSV?)
Ok, for future reference (drh, please, it would be nice to add this to the web site) To import: 3 5 6 3 4 6 CREATE TABLE foo (v1,v2,v3); .separator " " .import "file.dat" foo Cheers Alberto On 7/19/07, Yusuke ITO <[EMAIL PROTECTED]> wrote: Hi, COPY command (like PostgreSQL) http://www.sqlite.org/lang_copy.html COPY tbl_foo (col1, col2, col3) FROM stdin; 2 3 4 4 3 2 5 4 387 5 8 5473 \. -- Yusuke ITO [EMAIL PROTECTED] On Thu, 19 Jul 2007 13:01:53 +0200 "Sylko Zschiedrich" <[EMAIL PROTECTED]> wrote: > We are using precompiled insert statements and bind the parameters. > The inserts were done in a transaction that is committed and reopened every > 1000 iterations. > > Ciao > Sylko > > -Urspr〓gliche Nachricht- > Von: Alberto Sim〓s [mailto:[EMAIL PROTECTED] > Gesendet: Donnerstag, 19. Juli 2007 11:57 > An: sqlite-users@sqlite.org > Betreff: [sqlite] Importing a big text file (CSV?) > > Hi > > I have a file (big file with 16 000 000 lines) with records like > > 2 3 4 > 4 3 2 > 5 4 387 > 5 8 5473 > ... > > and I want to import this to an SQLite table. > Although I can replace all this to INSERT commands very easily, I > would like to ask first if there is any faster method. > > Cheers > Alberto > -- > Alberto Sim〓s > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - --------- To unsubscribe, send email to [EMAIL PROTECTED] - -- Alberto Simões
Re: [sqlite] Importing a big text file (CSV?)
I have a file (big file with 16 000 000 lines) with records like 2 3 4 4 3 2 5 4 387 5 8 5473 ... and I want to import this to an SQLite table. Although I can replace all this to INSERT commands very easily, I would like to ask first if there is any faster method. Meanwhile I found an '.import' command on SQLite, but I can't find a suitable documentation on how it works. -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Importing a big text file (CSV?)
Hi I have a file (big file with 16 000 000 lines) with records like 2 3 4 4 3 2 5 4 387 5 8 5473 ... and I want to import this to an SQLite table. Although I can replace all this to INSERT commands very easily, I would like to ask first if there is any faster method. Cheers Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] mailing list slow?
On 7/18/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Joe Wilson <[EMAIL PROTECTED]> wrote: > I noticed delays of an hour or so in posts hitting the mailing list recently. > Or is it just my mail server? > The server (www.sqlite.org) seems to be doing OK. Load average is 0.13. Nothing unusual in the logs. There are currently 1281 people on the mailing list. The mailing list manager (ezmlm) will send messages to at most 20 people at at time. If each send requires 20 seconds, that means about 1200 seconds to relay a message, or about 20 minutes. Nice math ;) -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Help with order-by query
Ok, my fault. Integers versus string values. Cheers Alberto On 7/15/07, Alberto Simões <[EMAIL PROTECTED]> wrote: Hi I have this simple schema for news: sqlite> .schema CREATE TABLE news (year,month,day,title,text); CREATE INDEX date ON news(year,month,day); And this query results not as expected... sqlite> SELECT * FROM news ORDER BY year DESC,month DESC,day DESC; 2007|7|7|Novo design|... 2007|6|19|10.000 palavras|... 2007|7|15|Actualização das regras de transcrição|... What am I doing wrong? Thank you Kind regards Alberto -- Alberto Simões -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Help with order-by query
Hi I have this simple schema for news: sqlite> .schema CREATE TABLE news (year,month,day,title,text); CREATE INDEX date ON news(year,month,day); And this query results not as expected... sqlite> SELECT * FROM news ORDER BY year DESC,month DESC,day DESC; 2007|7|7|Novo design|... 2007|6|19|10.000 palavras|... 2007|7|15|Actualização das regras de transcrição|... What am I doing wrong? Thank you Kind regards Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] INDEX + SELECT or just SELECT?
Hi I have a big table (16 million records) and I want to perform JUST ONCE an operation to sort by one column: SELECT * FROM tbl ORDER BY i DESC; Now, is it faster to perform just the select, or to do: CREATE INDEX tmp ON tbl (i); SELECT * FROM tbl ORDER BY i DESC; Thank you Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite article, Adobe info
Hi, Tim. Is there any PDF or cleaner HTML we can use for printing? Thank you ;) Alberto On 6/21/07, Tim Anderson <[EMAIL PROTECTED]> wrote: The article I wrote is here: http://technology.guardian.co.uk/weekly/story/0,,2107239,00.html Thanks again to those who gave feedback on SQLite. Another post I think is interesting is from Paul Robertson, who works on SQLite within the Adobe Integrated Runtime (formerly Apollo), responding to a post of mine: http://probertson.com/articles/2007/06/19/air-sql-docs-dont-mention-sqli te-my-response/ Tim - To unsubscribe, send email to [EMAIL PROTECTED] - -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] error in round-function?
On 6/10/07, Olaf Schmidt <[EMAIL PROTECTED]> wrote: > > select round(0.95, 1) > 0.9 > > select round(9.95, 1) > 9.9 > > select round(0.995, 2) > 0.99 > > select round(9.995, 2) > 9.99 > (3.3.17 here) As it should be, hmm. On what OS have you tested? If on windows, was it a GCC-compile or a MS-VC-compile? MacOS X, gcc 4.0.1 Cheers -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] error in round-function?
On 6/10/07, Olaf Schmidt <[EMAIL PROTECTED]> wrote: Hi drh, > http://www.sqlite.org/faq.html#q18 Not so easy I think (I'm well aware of rounding-problems regarding the IEEE-Float-Formats, etc.). Bankers rounding aside at the moment. select round(0.95, 1) 0.9 select round(9.95, 1) 9.9 select round(0.995, 2) 0.99 select round(9.995, 2) 9.99 (3.3.17 here) -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] error in round-function?
Olaf Schmidt <[EMAIL PROTECTED]> wrote: > The integrated round-function works well so far, > especially because it does "bankers rounding". > Tough it seems to have a little bug, wich is probably simple to fix. > > Try... > select round(0.94, 1) -> gives 0.9, wich is correct > select round(0.96, 1) -> gives 1, wich is correct too > > but... > select round(0.95, 1) -> gives 0, wich is not correct of course > Regarding bankers rounding, wich rounds up in case of a > '5' at the appropriate position, the correct result should be 1. > > (tested with 3.3.17) I do not know what are you trying to round. In any case, I just can say that if you are storing a column as a float for money, turn it into a string. This is what I've learned when writing Perl code to deal with money :) Cheers Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Why do you use SQLite? Comments for an article needed
Hi, Tim. We are using SQLite for two main reasons: - no daemon needed: to use RDBMS on a cluster machine is quite annoying. Most clusters administrators does not want more daemons running. - SQLite can be very fast when you tweak some of its basic configuration pragmas, being more than 200% faster than MySQL or Postgres on batch insert operations. Hope this can at least give you some ideas for your article. Good luck Alberto On 6/7/07, Tim Anderson <[EMAIL PROTECTED]> wrote: I'm writing an article about SQLite and I'd love to get some comments from users about why you use it. Performance? Features? Reliability? Cost? Is the open source aspect important? Anything else? For that matter, anything you really don't like about SQLite? You can email me at tim(at)itwriting.com or comment here if you prefer - but to use your quote I'd need at least a full name, what you do and the company you work for Thanks in advance for your help. Tim http://www.itwriting.com/blog - To unsubscribe, send email to [EMAIL PROTECTED] - -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] No space left on device?
Hi What happens when no space is left on device? Does the process die, or does it try to store as much as it can using main memory? I'm asking this because I have a process dying (being killed) because it exauted main memory. Cheers Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: How to sort not binary?
On 5/12/07, Yves Goergen <[EMAIL PROTECTED]> wrote: On 12.05.2007 17:33 CE(S)T, Igor Tandetnik wrote: > Yves Goergen <[EMAIL PROTECTED]> > wrote: >> I'm trying to get my table sorted the way how for example Windows >> Explorer or other file managers are sorting names. Most of all, >> accented characters should not be listed at the end of the list but >> near their non-accented character instead. I can only see the >> built-in collations BINARY and NOCASE. Is there something else? > > You need to install a custom collation. See sqlite3_create_collation[16] I guess that doesn't work when I'm accessing the database through the System.Data.SQLite interface in .NET? I did the same question about ordering, in my case, with Perl DBD::SQLite. As the answer was the same (and I understand why), i did the.. erm... stupid approach: . a column with the word/string or whatever . a column with the string where accented characters are not (accented). Use the first for presentation and query, the second for ordering. Hope this can help Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Compiling SQLite under Mac OS (was: unsupported file format)
On 5/12/07, Alberto Simões <[EMAIL PROTECTED]> wrote: I am compiling *manually* sqlite3 in my Mac OS. Configured without tcl, and with prefix /usr/local. (--disable-tcl --prefix=/usr/local) I get this while compiling: ... gcc -L/sw/lib -I/sw/include -g -O2 -I. -I./src -DNDEBUG -DTHREADSAFE=0 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -c ./src/alter.c -fno-common -DPIC -o .libs/alter.o In file included from ./src/alter.c:17: ./src/sqliteInt.h:516: error: parse error before 'sqlite3_vtab' ./src/sqliteInt.h:516: warning: no semicolon at end of struct or union ./src/sqliteInt.h:528: error: parse error before '}' token ... Solved Removed those -L and -I for fink libraries. Thanks Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Compiling SQLite under Mac OS (was: unsupported file format)
Ok, I didn't explain correctly. This time I am not using fink :) Argh. Not being native english speaker sometimes sucks. Let start again. I am compiling *manually* sqlite3 in my Mac OS. Configured without tcl, and with prefix /usr/local. (--disable-tcl --prefix=/usr/local) I get this while compiling: ... gcc -L/sw/lib -I/sw/include -g -O2 -I. -I./src -DNDEBUG -DTHREADSAFE=0 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -c ./src/alter.c -fno-common -DPIC -o .libs/alter.o In file included from ./src/alter.c:17: ./src/sqliteInt.h:516: error: parse error before 'sqlite3_vtab' ./src/sqliteInt.h:516: warning: no semicolon at end of struct or union ./src/sqliteInt.h:528: error: parse error before '}' token ... Hints, please? Thanks Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Compiling SQLite under Mac OS (was: unsupported file format)
On 5/12/07, P Kishor <[EMAIL PROTECTED]> wrote: On 5/12/07, Alberto Simões <[EMAIL PROTECTED]> wrote: > On 5/11/07, P Kishor <[EMAIL PROTECTED]> wrote: > > > Just download the latest source code and compile a new sqlite3 with > > it. All will be well. > > I am trying to compile SQLite under Mac OS. Normally I use fink but it > includes an old version of SQLite. Don't use fink. I used fink once about a few years ago, and promptly deleted anything to do with it because it (as well as darwinports) was more trouble than worth it. SQLite (and many other software packages) compiles on my Mac (10.4.x) with nary a hiccup. Ok, I didn't explain correctly. This time I am not using fink :) > Configured without tcl, and with prefix /usr/local. > I get this while compiling: > > gcc -L/sw/lib -I/sw/include -g -O2 -I. -I./src -DNDEBUG > -DTHREADSAFE=0 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 > -DSQLITE_OMIT_LOAD_EXTENSION=1 -c ./src/alter.c -fno-common -DPIC -o > .libs/alter.o > In file included from ./src/alter.c:17: > ./src/sqliteInt.h:516: error: parse error before 'sqlite3_vtab' > ./src/sqliteInt.h:516: warning: no semicolon at end of struct or union > ./src/sqliteInt.h:528: error: parse error before '}' token > ... > > > Hints, please? > Thanks > Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Compiling SQLite under Mac OS (was: unsupported file format)
On 5/11/07, P Kishor <[EMAIL PROTECTED]> wrote: Just download the latest source code and compile a new sqlite3 with it. All will be well. I am trying to compile SQLite under Mac OS. Normally I use fink but it includes an old version of SQLite. Configured without tcl, and with prefix /usr/local. I get this while compiling: gcc -L/sw/lib -I/sw/include -g -O2 -I. -I./src -DNDEBUG -DTHREADSAFE=0 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -c ./src/alter.c -fno-common -DPIC -o .libs/alter.o In file included from ./src/alter.c:17: ./src/sqliteInt.h:516: error: parse error before 'sqlite3_vtab' ./src/sqliteInt.h:516: warning: no semicolon at end of struct or union ./src/sqliteInt.h:528: error: parse error before '}' token ... Hints, please? Thanks Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Unsupported file format
Hi, I am using a Mac, and probably doing something weird with fink software and (probably) other installations. The fact is that I create a database using DBD::SQLite, and then: [EMAIL PROTECTED] ProjectoDicionario]$ sqlite3 dic.db SQLite version 3.2.8 Enter ".help" for instructions sqlite> .schema Error: unsupported file format Any hints on what I might be doing wrong? Thank you Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Powered by SQLite image?
On 5/5/07, A. Pagaltzis <[EMAIL PROTECTED]> wrote: Hi Alberto, Maybe contribute both to SQLite? They'd make excellent additions to the art/ directory in the source tarball. (I'm not sure what it would take for Dr. Hipp to include them?) The xcf file can be downloaded from http://dicionario-aberto.net/powered_by_sqlite.xcf Of course I would be very happy if the image(s) is included in the source tarball. Kind Regards Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Powered by SQLite image?
On 5/4/07, Cesar Rodas <[EMAIL PROTECTED]> wrote: Looks great... and this image is public domain? Sure. Be free to use it. Also, I have the xcf file. So if anybody knows how to tweak Gimp, I'll be pleased to send it. Cheers Alberto On 04/05/07, Alberto Simões <[EMAIL PROTECTED]> wrote: > > > > If nobody have one, I'll try to put one up tomorrow :) > > Ok, I had some time today. What do you think of the one shown in: > http://dicionario-aberto.net/bin/dic.pl > > Cheers > Alberto > > -- > Alberto Simões > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > -- Cesar Rodas http://www.cesarodas.com/ http://cesars.users.phpclasses.org/winners.html I won ;) Mobile Phone: 595 961 974165 Phone: 595 21 645590 [EMAIL PROTECTED] [EMAIL PROTECTED] -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Powered by SQLite image?
> If nobody have one, I'll try to put one up tomorrow :) Ok, I had some time today. What do you think of the one shown in: http://dicionario-aberto.net/bin/dic.pl Cheers Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Powered by SQLite image?
On 5/4/07, Griggs, Donald <[EMAIL PROTECTED]> wrote: Regarding: Is there any image/logo with "powered by SQLite"? You know, people with web services (not webservices) that rely on SQLite might like to say that to others :) Well, Alberto, there *was* one, but it was so very tiny, lightweight, and efficient that few could actually see it. ;-) Seriously though, I believe the nice logo at the upper left on page: http://www.sqlite.org/index.html can be used, If nobody have one, I'll try to put one up tomorrow :) Cheers Thanks -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Powered by SQLite image?
Hi Is there any image/logo with "powered by SQLite"? You know, people with web services (not webservices) that rely on SQLite might like to say that to others :) Well, I would. Cheers Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite Performance
On 4/17/07, Alberto Simões <[EMAIL PROTECTED]> wrote: On 4/17/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > "=?ISO-8859-1?Q?Alberto_Sim=F5es?=" <[EMAIL PROTECTED]> wrote: > > > > Consider the following database schema: > > CREATE TABLE tetragrams (word1 INTEGER, word2 INTEGER, word3 INTEGER, > > word4 INTEGER, occs INTEGER, PRIMARY KEY (word1, word2, word3, > > word4)); > > CREATE INDEX tet_b ON tetragrams (word2); > > CREATE INDEX tet_c ON tetragrams (word3); > > CREATE INDEX tet_d ON tetragrams (word4); > > > > The problem is that I want to use: > > > > SELECT * FROM tetragrams WHERE word1=6 ORDER BY occs DESC LIMIT 10; > > > > and it takes.. five minutes and did not give the result yet... > > > > Is there anything I can do to make it speed up CONSIDERABLY? > > CREATE INDEX tet_e ON tetragrams(word1, occs); I tried: CREATE INDEX tet_a ON tetragrams(word1, occs); CREATE INDEX tet_b ON tetragrams(word2, occs); CREATE INDEX tet_c ON tetragrams(word3, occs); CREATE INDEX tet_d ON tetragrams(word4, occs); and PRIMARY KEY(word1,word2,word3,word4) This works fairly well if I need SELECT * from tetragrams WHERE word1 = 'x' ORDER BY occs; The problem is that I need as well SELECT * from tetragrams WHERE word1 = 'x' AND word2 = 'y' ORDER BY occs; and this is QUITE slow. and more (try 16 combinations). That means I need 16 indexes? :-S Just to let you have an idea, this schema: CREATE TABLE tetragrams (word1 INTEGER, word2 INTEGER, word3 INTEGER, word4 INTEGER, occs INTEGER, PRIMARY KEY (word1, word2, word3, word4)); CREATE INDEX tet_a ON tetragrams (word1,occs); CREATE INDEX tet_b ON tetragrams (word2,occs); CREATE INDEX tet_c ON tetragrams (word3,occs); CREATE INDEX tet_d ON tetragrams (word4,occs); is already with 1.8GB of disk :) TIA Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Efficiency and Pragmas
Hi I am using these pragmas for efficiency in data loading (I hope) PRAGMA page_size = 4096; PRAGMA temp_store = MEMORY; PRAGMA cache_size = 100; PRAGMA synchronous = OFF; PRAGMA count_changes = 0; The truth is that adding these pragmas my inserts got quite faster. Now the problem is querying. If I use sqlite3 shell I get answers in one (two) second(s). If I use my program after these pragmas, I get a slow query. Is there any good reason for that? Thanks PS: let me know if more detail on the query or database can be handy for answers. Cheers Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Insert order maintained?
Ok, I'll use ORDER BY. Thanks! Alberto On 4/17/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote: We got bit by this when moving from MSSQL 2000 to MSSQL 2005. MSSQL returned rows by default in PK order and one of our former developers depended on this so when the behavior changed in MSSQL 2005 (which is fine 'cause it wasn't documented or expected behavior) our app broke in unexpected ways. With any db it's always best to specify an ORDER BY if you care about the order. Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 17, 2007 11:18 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Insert order maintained? > I know this is the behavior for MySQL, but not sure about SQLite. I'v heard about some version of mysql that didn't return rows in the same order (but haven't seen it myselt). So unless this behaviour is documented in mysql manual, it's not a good idea to rely on this. Actually I'v seen only 1 database that allways returns rows in random order, but it doesn't mean that other databases guarantee anything. -- Jak bedzie wygladac koniec swiata? >>> Zobacz >>> http://link.interia.pl/f1a38 - To unsubscribe, send email to [EMAIL PROTECTED] --------- -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Insert order maintained?
Hi I would like to know if the order I get the rows from a select (without ORDER BY) is the order by which the values were inserted. I know this is the behavior for MySQL, but not sure about SQLite. THank you Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite Performance
On 4/17/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: "=?ISO-8859-1?Q?Alberto_Sim=F5es?=" <[EMAIL PROTECTED]> wrote: > > Consider the following database schema: > CREATE TABLE tetragrams (word1 INTEGER, word2 INTEGER, word3 INTEGER, > word4 INTEGER, occs INTEGER, PRIMARY KEY (word1, word2, word3, > word4)); > CREATE INDEX tet_b ON tetragrams (word2); > CREATE INDEX tet_c ON tetragrams (word3); > CREATE INDEX tet_d ON tetragrams (word4); > > The problem is that I want to use: > > SELECT * FROM tetragrams WHERE word1=6 ORDER BY occs DESC LIMIT 10; > > and it takes.. five minutes and did not give the result yet... > > Is there anything I can do to make it speed up CONSIDERABLY? CREATE INDEX tet_e ON tetragrams(word1, occs); Hmms, Yes, it works as expected and speeds up to about one second, or less :) Thank you, Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLite Performance
Hi I've found SQLite faster than MySQL and Postgres for small/medium databases. Now I have big ones and I really do not want to change, but I have some performance issues. Consider the following database schema: CREATE TABLE tetragrams (word1 INTEGER, word2 INTEGER, word3 INTEGER, word4 INTEGER, occs INTEGER, PRIMARY KEY (word1, word2, word3, word4)); CREATE INDEX tet_b ON tetragrams (word2); CREATE INDEX tet_c ON tetragrams (word3); CREATE INDEX tet_d ON tetragrams (word4); And the following database size: sqlite> SELECT COUNT(*) from tetragrams; 18397532 Now, a query like SELECT FROM tetragrams WHERE word1 = 6; returns 166579 rows; This query takes some time, but starts as soon as I type the query. The problem is that I want to use: SELECT * FROM tetragrams WHERE word1=6 ORDER BY occs DESC LIMIT 10; and it takes.. five minutes and did not give the result yet... Is there anything I can do to make it speed up CONSIDERABLY? I mean, this is to be used in a CGI and each CGI query will make 11 queries like the one above to the database. Thank you in advance, Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQL and SQLite pronounciation?
On 4/4/07, Joel Cochran <[EMAIL PROTECTED]> wrote: I prefer the "ess cue ell" version. And I can never remmeber that there is only one "el", so I end up saying "ess cue ell light" even though I know its wrong. Join the club, Joel. I do the same, myself. With the only difference that I pronounce the 'S' 'Q' and 'L' letters in Portuguese. Cheers -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sorting with latin1 characters
On 3/31/07, Nuno Lucas <[EMAIL PROTECTED]> wrote: On 3/31/07, Alberto Simões <[EMAIL PROTECTED]> wrote: That is by design. SQLite only knows how to sort ASCII strings. The problem is that almost every language has it's own idea of how to sort, and making SQLite know about all this would take the "lite" from SQLite. Ok, I can understand it. You can create your own COLLATE function and make SQLite use it. Take a look here: http://www.sqlite.org/capi3ref.html#sqlite3_create_collation Anybody knows if there is some Perl way to do that? (using DBD::SQLite it doesn't seem available). Cheers Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Sorting with latin1 characters
Hi I am trying to sort results from a SELECT using ORDER BY. That column includes latin1 characters, and SELECT returns the entries with accented characters at the end of the list. I am using DBD::SQLite, so I am not sure if I tested correctly the change of environment variables to see if sqlite reacts to that changes. Thanks in advance for any help Cheers -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] One big database or various smaller ones?
On 3/26/07, Joe Wilson <[EMAIL PROTECTED]> wrote: --- Alberto Simões <[EMAIL PROTECTED]> wrote: > The tables are related but they are already being created in parallel > (three tables). I was joining them just because it was easier to move. > But given the new, erm, big size, I think I'll maintain them split. > > Joe Wilson idea of attaching the tables seems cool. I'll give it a try. If you're creating the 3 databases in parallel, you would be better off creating 3 connections (each from a seperate thread or process) to populate them in order to more efficiently use the spare cycles of the CPU during I/O waits. Yeah, I am using three different processes in three different machines (using a Cluster) to populate. Use ATTACH from one connection after the databases have been populated to read from all of them. I'll try that. Thanks. -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] One big database or various smaller ones?
On 3/26/07, Eduardo Morras <[EMAIL PROTECTED]> wrote: At 17:22 26/03/2007, you wrote: >Hi > >I have a database with three tables (bigrams, trigrams and >tetragrams). Basically, they are: > TABLE( A, B, Count) IDX(A,B) IDX(B) > TABLE( A, B, C, Count) IDX(A,B,C) IDX(B) IDX(C) > TABLE( A, B, C, D, Count) IDX (A,B,C,D) IDX(B) IDX(C) IDX(D) > >Now, this database is quickly getting around the 2GB of disk space. >My question, about efficiency, is, it better to have: >- one big database, one connection from C code to the database >- three smaller databases, three connection from C code to the database > >Thank you >Alberto The problem with this database is that the three tables are related, no? A,B,C and D are the same data on all tables. If it's not true and tables have separate data, you can easily split in three database files, permiting you concurrent writing and getting faster read times if are on distinct drives. The tables are related but they are already being created in parallel (three tables). I was joining them just because it was easier to move. But given the new, erm, big size, I think I'll maintain them split. Joe Wilson idea of attaching the tables seems cool. I'll give it a try. Thank you, folks. -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] One big database or various smaller ones?
Hi I have a database with three tables (bigrams, trigrams and tetragrams). Basically, they are: TABLE( A, B, Count) IDX(A,B) IDX(B) TABLE( A, B, C, Count) IDX(A,B,C) IDX(B) IDX(C) TABLE( A, B, C, D, Count) IDX (A,B,C,D) IDX(B) IDX(C) IDX(D) Now, this database is quickly getting around the 2GB of disk space. My question, about efficiency, is, it better to have: - one big database, one connection from C code to the database - three smaller databases, three connection from C code to the database Thank you Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Avoiding use of temporary files
> Would it also be acceptable (assuming you want the protection offered > by the temporary tables) to do ... "PRAGMA temp_store = MEMORY" on > the database? > The temp_store pragma only effects the placement of temporary tables. But the rollback journal is not a table. The temp_store pragma has no effect on the placement of rollback journals. Rollback journals always go to disk. I am not sure of the side-effects it would lead to, but I would love to see a "PRAGMA journal = NO". Cheers -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] ANN: SQLcc another open source Database manager
On 3/17/07, Stef Mientki <[EMAIL PROTECTED]> wrote: Jim Dodgen wrote: > Server not found very weird, it's a server directly connected to the European backbone, so please try again. Works here. Cheers -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Last call for bugs...
Heh. I asked for that on a previous mail as well. It would be really nice. Probably it would be the way for more people using it, and thus more bugs being reported about it. Thanks for SQLite! Alberto On 1/26/07, Jason Jobe <[EMAIL PROTECTED]> wrote: How about including using FullTextSearch as a config / make option? FTS Still not working on OSX (for me). -jason On Jan 26, 2007, at 2:33 PM, [EMAIL PROTECTED] wrote: > I plan to release 3.3.12 later today or tomorrow. > If you know about any unreported problems, please > get those bug reports in quickly. Tnx. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > - To unsubscribe, send email to [EMAIL PROTECTED] --------- -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] FTS1
Hi I am reading http://www.sqlite.org/cvstrac/wiki?p=FtsOne about the Full Text Search plugin to SQLite and trying to compile it, and I am wondering why it is not activated using the configure script, or at least why it does not contain a Makefile in its directory. I know it is beta (forever... makes me think of gmail) and that it is not supported, but it would be easier for users to compile it and test it. Just an idea ;) Now, I'll continue learning how to create a .so file ;) Cheers Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Compound index or multiple indexes?
Hi I am using SQLite to store bigrams, trigrams and tetragrams. Let's look for one of the tables: CREATE TABLE trigrams (word1 INTEGER, word2 INTEGER, word3 INTEGER, occs INTEGER, PRIMARY KEY (word1, word2, word3)) My main question is: what's the difference of efficiency between using this index for the compound key, or three different indexes, one for each word? When I ask efficiency, I would like to consider the differences of timings on creating the indexes/inserting data, and the differences of timings on selects by full key. Somebody has timings on this? Kind regards, Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Joining tables in a single file
On 12/29/06, Griggs, Donald <[EMAIL PROTECTED]> wrote: Regarding: " I need something like: cat A.db B.db C.db > full.db # kidding" - Hi Alberto, My first inclination is to consider this a classic use of the ATTACH DATABASE command http://www.sqlite.org/lang_attach.html SQLITE3 A.db >ATTACH 'B.db' as B; >ATTACH 'C.db' as C; >Create tableFromB as select * from B.tablename; >Create tableFromC as select * from C.tablename; Hmms, I think this will do the trick. Thank you :) Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Joining tables in a single file
Hi I am using SQLite to store ngrams from texts (bigrams, trigrams and tetragrams). This is not really important for the question; just imagine I have three tables A (int,int), B (int, int, int) and C (int, int, int, int). As the table keys are full rows and the tables get big, it is not quite efficient com compute bigrams, trigrams and tetragrams at the same time. Given that I have access to a cluster, I split the job in three tasks that can be computed separately on different cluster nodes. One calculates bigrams, another trigrams, and other to calculate tetragrams. So far, everything fine. The problem is that this results in three different files each with a table. I need to join tables in the same file. There are no dependencies between tables, thus you can imagine that I need something like: cat A.db B.db C.db > full.db # kidding I can do an export and import for each table. But I would like to know if there is any faster method. Thank you Kind regards, Alberto -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
