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

Re: [sqlite] Searching with like for a specific start letter

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 > -- >

[sqlite] Searching with like for a specific start letter

ay 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-use

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

[sqlite] Backuping SQLite database in VC system

: - 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

Re: [sqlite] String Vs Integer Index

ble (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, b

[sqlite] String Vs Integer Index

? 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

ssage- > 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, > &

[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

Re: [sqlite] SQLite 3.6.17

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

on.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 > &

[sqlite] Compiling SQLite with custom readline

ntation 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/mai

Re: [sqlite] SQLite 3.6.17

gt; > > > 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('

Re: [sqlite] SQLite 3.6.17

" > 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 -- Al

[sqlite] SQLite 3.6.17

-- 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). > Th

Re: [sqlite] Near misses

ously 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 happ

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 > 'remoti

Re: [sqlite] Near misses

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

ecially 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

Re: [sqlite] Fixing a database - solved

n'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 gen

[sqlite] Fixing a database

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...?

s, 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>

[sqlite] disk I/O error...?

]$ 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.or

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.

Re: [sqlite] ANN - DBD::SQLite version 1.20

ers 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

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. Than

[sqlite] Random Syntax changed in 3.6.11?

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

nce >>>> 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 >>> ___ sqlite-users >>> mailing list sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> ___ sqlite-users mailing >> list sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> ___ sqlite-users mailing >> list sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Fuzzy Matching

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

Re: [sqlite] CREATE INDEX time degradation

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 t

[sqlite] CREATE INDEX time degradation

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

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 bes

[sqlite] Importing table

? (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

Re: [sqlite] SQLite website

btrusive hacks were needed for it to render decently. > > James > > - > 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

Re: [sqlite] how do i generate a uniqueidentifier ?

t; 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 --

Re: [sqlite] Importing a big text file (CSV?)

ac/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

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?)

-- > > > - > 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?)

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

Re: [sqlite] mailing list slow?

age 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

[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 quer

[sqlite] Help with order-by query

7|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?

-- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] SQLite article, Adobe info

/ Tim - To unsubscribe, send email to [EMAIL PROTECTED] - -- Alberto Simões - To unsubscribe, send email to

Re: [sqlite] error in round-function?

? 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?

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?

> > (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

Re: [sqlite] Why do you use SQLite? Comments for an article needed

TECTED] - -- 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

Re: [sqlite] Re: How to sort not binary?

. 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 -DTHRE

Re: [sqlite] Compiling SQLite under Mac OS (was: unsupported file format)

'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

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. > &g

[sqlite] Compiling SQLite under Mac OS (was: unsupported file format)

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

[sqlite] Unsupported file format

ns 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?

onario-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 PROTECTE

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 unsubscr

Re: [sqlite] Powered by SQLite image?

iny, 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 -

[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 -- Albe

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 tetrag

[sqlite] Efficiency and Pragmas

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

Re: [sqlite] Insert order maintained?

ttp://link.interia.pl/f1a38 - To unsubscribe, send email to [EMAIL PROTECTED] --------- -- Alberto Simões - To unsubs

[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

Re: [sqlite] SQLite Performance

ccs 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 u

[sqlite] SQLite Performance

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

Re: [sqlite] SQL and SQLite pronounciation?

elf. 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 thi

[sqlite] Sorting with latin1 characters

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, b

Re: [sqlite] One big database or various smaller ones?

lit. 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?

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

Re: [sqlite] Avoiding use of temporary files

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&

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 -- Alber

Re: [sqlite] Last call for bugs...

o 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

... 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

[sqlite] Compound index or multiple indexes?

, 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

se 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 y

[sqlite] Joining tables in a single file

ach 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] -