Re: [sqlite] SQL Statement Help(selecting days).

[Jean-Christophe Deschamps]

>On Wed, May 18, 2011 at 4:10 PM, Petite Abeille
> wrote:
> > On May 18, 2011, at 10:50 PM, Danilo Cicerone wrote:
> >> How can I simulate a
> >> calendar table(maybe using the strftime funtion)?
> >
> > Well, you have two broad options:
> >
> > (1) materialize the calendar as a table
> > (2) virtualize the calendar as a generator
> >
> > The first option is brutal, but simple. E.g. create a table with, 
> say, all the julian date from 2101 to 21001231 (2451544 to 
> 2488433 JD, about 36,889 records for a century worth of date)
> >
> > The second option is a bit more involved, but you could have a 
> virtual calendar table that generate the relevant date span on demand:
> >
> > http://www.sqlite.org/vtab.html
> >
> > Unfortunately, there is no direct way to generate rows in SQLite as 
> there is, for example, in Oracle or such:
>
>I have a virtual table that allows you to split strings and count
>numbers, which could be used as a row generator.  I really want to
>polish it off and even, some day, add syntactic sugar (calling this
>"table functions"), but lack for time.  Would it help if I posted this
>somewhere?
>
>Nico

A vtable for that may be a bit of a caterpillar unless you look for top 
efficiency or large ranges and, yes, SQLite perfectly allows such range 
of values to be created within SQLite's SQL.

I regularly use such constructs to keep generating (reasonably small) 
sequences or date ranges entirely within SQLite:


CREATE TABLE "Dates" (
   "jDate" INTEGER PRIMARY KEY,
   "sDate" CHAR);

CREATE TABLE "Sequence" (
   "N" INTEGER PRIMARY KEY AUTOINCREMENT);

CREATE TABLE "Counts" (
   "jStartDate" INTEGER,
   "Start" INTEGER DEFAULT (0),
   "Counter" INTEGER DEFAULT (0));

CREATE TRIGGER "trUpdCount"
AFTER UPDATE OF "Counter"
ON "Counts"
WHEN new.counter > 0
BEGIN
  insert or replace into sequence (N) values ((select start + 
counter from counts));
  insert or replace into dates (jdate, sdate) values ((select 
jstartdate + counter from counts), date((select jstartdate + counter 
from counts)));
  update counts set counter = new.counter - 1;
END;

To use, set first a Julian day start date and/or a start integer. Then 
update counter to specify how many dates and/or numbers you want 
created in tables.
Empty data tables between runs (that can be automated with more triggers).

Since we use recursive triggers, set recursive_triggers pragma 
beforehand if not yet done.  This scheme can be adapted to your actual 
needs, merge data tables, use other types, etc.

Don't request large counts as the recursion limit may bite you. As the 
doc says:
The depth of recursion for triggers has a hard upper limit set by the 
SQLITE_MAX_TRIGGER_DEPTH 
compile-time option and a run-time limit set by 
sqlite3_limit(db,SQLITE_LIMIT_TRIGGER_DEPTH,...).
 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Caveat in parsing create table statement

[Jean-Christophe Deschamps]

Dear list,

Is there a way to make SQLite accept this kind of constraint:

CREATE TABLE tab (
   id INTEGER NOT NULL,
   data INTEGER,
   CHECK(data = 0 or not exists (select 1 from tab where id = data)));

This toy exemple doesn't mean much as it is and the actual situation is 
a bit more involved.  Anyway, the crux of it is that the table name 
doesn't yet exist when the parser looks at the constraint, hence SQLite 
issues a "no such table: tab" error.

The docs say that a check table constraint can be any expression but 
this is clearly not the complete picture.

I tend to think that this statement should be accepted, but I'm in no 
way an expert in ISO SQL.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE return codes for insert/delete/update/select

[Jean-Christophe Deschamps]

>I agree with what you stated but it would have been more clearer if 
>the result of the update statement was a "RECORD NOT FOUND" return 
>value since it did not find any that met the query's criteria. How can 
>you say that the UPDATE was successful when the record you were 
>looking for does not even exist in the DB?

You overlook that SQL is in essence a set-theoric language.  You tell 
it to perform this os that change (update) over the set of record which 
match this or that criterion.
That the concerned subset is populated or not doesn't change the 
successful outcome of the operation.

Like in math: "return the set of primes integers that are integer 
squares" gives the empty set, without any error.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

[Jean-Christophe Deschamps]

Without a view (but with a trigger) and certainly open to improvement 
(9 is the
MAX_ENTRIES parameter):

CREATE TABLE "log" (
   "id" INTEGER NOT NULL PRIMARY KEY ON CONFLICT REPLACE AUTOINCREMENT,
   "seq" INTEGER CONSTRAINT "ix1Seq" UNIQUE ON CONFLICT REPLACE,
   "data" CHAR);

CREATE TRIGGER "trInsLog"
AFTER INSERT
ON "log"
BEGIN
  update log set seq = id % 9 where id=(select max(id) from log);
END;

insert into log (data) values ('a');
insert into log (data) values ('b');
insert into log (data) values ('c');
insert into log (data) values ('d');
insert into log (data) values ('e');
insert into log (data) values ('f');
insert into log (data) values ('g');
insert into log (data) values ('h');
insert into log (data) values ('i');
insert into log (data) values ('j');
insert into log (data) values ('k');
insert into log (data) values ('l');
insert into log (data) values ('m');
insert into log (data) values ('o');
insert into log (data) values ('p');
insert into log (data) values ('q');
insert into log (data) values ('r');
insert into log (data) values ('s');
insert into log (data) values ('t');
insert into log (data) values ('u');
insert into log (data) values ('v');
insert into log (data) values ('w');
insert into log (data) values ('x');
insert into log (data) values ('y');
insert into log (data) values ('z');
insert into log (data) values ('Here you are!');


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Determining how many columns were returned in a query

[Jean-Christophe Deschamps]

>I happen to have a code path such that the select statement can return 
>1, 3
>or 5 columns.  I know I could go based on count, but if I could do it by
>name that would be safer.  I had not considered the point that multiple
>columns could have the same name, though, so I fully understand why such a
>function does not exist.

Also beware that the names returned by sqlite3_column_name are in fact 
the aliases used (if any) by the select statement, thus users can foil 
you without bribing anyone nor subverting the engine.

Say you have a table T with (col_A, sigma), you can get the following:

select max(col_A) as sigma, total(sigma) / count(*) as col_A from T;

names returned by sqlite3_column_name will be 'sigma' and 'col_A'.
Gotcha!

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Determining how many columns were returned in a query

[Jean-Christophe Deschamps]

>How about:
>
>SELECT count() FROM ();

You can do that (and variations) but this is a completely distinct 
statement.

I meant that there is no possibility to recover the row count of a 
result set before it goes to completion (by iterating step), just 
because the SQLite engine has no idea itself.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Determining how many columns were returned in a query

[Jean-Christophe Deschamps]

>How does one go about finding out how many rows a query returns?

This is the number of time sqlite3_step can be called successfully 
until it returns SQLITE_DONE.

>Is there a way to find out the id of a particular column?

AFAICT column don't have ids.  You can read column names or alias using 
sqlite3_column_name[16].

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Issue 608 in sqlite-manager: select date('2011-04-29', quote(-3) || 'day'); shows null after upgrading of firefox to 3.6.17

[Jean-Christophe Deschamps]

Change that into:

select date('2011-04-29', quote(-3) || ' day');

(note the space before day).
Looks like a parsing change.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request: Fuzzy searching

[Jean-Christophe Deschamps]

I apologize if double-post, the first one didn't make it to the list.
-

Hi,

>Hi all, I am interested in seeing "fuzzy searching" in SQLite, for
>lack of a better term. This type of search would return more results
>than LIKE currently does today. The search would return matches based
>on expanded criteria, each one may be considered a separate RFE for
>LIKE or for another specialized function (FLIKE, maybe).
>
>1) Case insensitivity

Yes

>2) ASCII-equivalent searching

Yes

>3) Diacritic-elimination searching

Yes

>4) Punctuation-elimination searching

Not out of the box.


I've written a small SQLite extension for dealing with Unicode text: 
unifuzz.  It contains, among other [I believe] useful functions, an 
unaccent() scalar function which will do right that: map accented 
characters to their basic form (unaccented) equivalent.  It also 
handles a few special cases like the German Eszet 'ß' and a number of 
ligatures.  All Unicode functions rely on Unicode v5.1 tries internal 
to the extension.

You'll also find a set of Unicode-aware set of functions: upper, lower, 
title, proper.

There are as well a set of Unicode-aware locale-independant collations: 
nocase and names and a collation usefull to sort numerically strings 
with numeric prefix (built-in collations will sort lexicographically, 
which is a pain in this case).  Being able to deal with text as 
locale-INdependant was a need for me as I manipulate data from 
customers in more that 27 countries now.  Of course it's far from 
perfect since doing things "perfectly" means first selecting a single 
locale, which would ruin operations applied to text from another 
country/language.  The numeric-prefix collation recognizes all known 
zero_to_nine representations and maps them all to "our" 0-9.

All this is detailed at length in headers/comments of the code.

Finally, there is a fuzzy search function which works internally with 
unaccented versions of the strings supplied.  It returns the 
Damerau-Levenshtein distance between its two arguments, and is 
(uncreatively) named 'typos'.

There is one limitation with this extension: the collation functions 
rely on invoking a Windows call (from kernel32.dll), which makes them 
currently non-portable across systems outside the Windows world.  The 
extension can probably be easily built without collation for compiling 
on non-Windows systems.  I didn't have the need yet to build for 64 
bits but I don't believe this should pose unduly complex issues.  It 
should also be quite easy to port the offending function to unix like OSes.

Full source is included and contains detailed explanations: read 
them!  Last note: all this comes without guaranty of any kind.  I still 
consider these extensions as beta, but I've been using them daily on 
our production base for months, like several other users.  Of course, 
it can't pretend be as perfect as ICU, but it's way smaller (170Kb vs 
many Mb) and much, much faster.  Aother advantage is that it doesn't 
require that you select a specific locale to register collations.  Not 
only does this simplify the code but it's also a prerequisite when you 
need to deal with data from several languages at once.

The source contains detailed explanations.  The binaries are x86 32bit 
ready for use.

If you have any question or would like to obtain a download link, feel 
free to drop me a mail.  I also would appreciate it if you can report 
how this extension behave w.r.t. your language(s).

Cheers,

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to Use an Apostrophe in a Text Field?

[Jean-Christophe Deschamps]

>True. I will get rid of the habit of using double quotes for string 
>literals.
>Thanks for information. But most of the databases support this non 
>standard
>behavior.


Yeah ... until things break under your feet due to a new version not 
sticking to the "non-standard" behavior anymore or parser unable to 
raise ambiguity in the way you expect in unexpected corner case.  I 
hate it when such simple details get an opportunity to cause premium havoc.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to Use an Apostrophe in a Text Field?

[Jean-Christophe Deschamps]

>  The apostrophes are escaped by apostrophes.

Correct.  http://www.sqlite.org/faq.html#q14

>  One more way you can do.
>
>insert into  () values ("*Goin' Down
> >> the Road Feelin' Bad*");
>
>It is double quotes before and after *. Similarly double quotes will 
>be escaped by one more double quote

Don't do that: it can reveal a pitfall.
It's not SQL even if SQLite does its best to interpret it without 
issuing an error.

Double quotes should be reserved to enclose database, table and column 
names, not string literals. SQLite also accepts square brackets as 
well: "My Table" is the same as [My Table].

Only use single quotes (apostrophes) for string literals.

A statement like:
delete from "my table" where column = "column";
is prone to disapoint you!
http://www.sqlite.org/faq.html#q24

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert help

[Jean-Christophe Deschamps]

>My date column is set when the program starts and i do not want it to
>change.

How is this date column set in the database without inserting anything?

>   So I have my  with  and two columns  and
>.   I have say 5 values (1 2 3 4 5) that I wanted inserted
>into mytable where the date is equal to date that was preset my 
>starting the
>program.

Preset, how?  I guess you have this date stored in some variable 
somewhere.  Just use it to fill the date column in the each new row.

>   So a select of my table would look like this:
>select * from mytable where date='2011/04/18 21:35:33';
>2011/04/18 21:35:33|1
>2011/04/18 21:35:33|2
>2011/04/18 21:35:33|3
>2011/04/18 21:35:33|4
>2011/04/18 21:35:33|5

So you need to perform as many inserts as values you have to insert:
insert into mytable (date, value) values ('2011/04/18 21:35:33', 1);
insert into mytable (date, value) values ('2011/04/18 21:35:33', 2);
insert into mytable (date, value) values ('2011/04/18 21:35:33', 3);
insert into mytable (date, value) values ('2011/04/18 21:35:33', 4);
insert into mytable (date, value) values ('2011/04/18 21:35:33', 5);

If you have really _many_ inserts to perform, wrap the lot in a 
transaction to speed up the process:
begin;
   
commit;


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert help

[Jean-Christophe Deschamps]

>Newbie here.   i'm trying to insert multiple values into a table by a 
>certain
>date and when I use where clause it fails.  This is my code "insert 
>into db
>(table) values ('value') where date = 'date range'". Thanks for any help.

There is no where clause in insert statements, it wouldn't make sense.
Your insert should look like:

insert into mytable (datecolumn) values 
(litteral_date_in_the_format_you_choose);

See http://www.sqlite.org/lang_insert.html

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trouble inserting null value from txt file

[Jean-Christophe Deschamps]

>It's impossible when you use standard sqlite3 command line utility. In
>your particular case you have 2 options: either write your own
>application that will recognize some kind of value as null and insert
>it instead of plain text, or you can use temporary table like this:

Another way is to use Pavel's suggestion to reserve some rare arbitrary 
string and use an insert trigger to convert the value into actual SQL null.

BTW, beware that triggers can slow down insert rate seriously.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] read full txt file in one record

[Jean-Christophe Deschamps]

>3. edit the database file with a hex editor, replacing "~~" with "0D 0A".

That seems pretty dangerous!  Rather update the table using the 
standard replace() function.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite bug? -- inconsistent time searches

[Jean-Christophe Deschamps]

>It's a pain in the arse that one has to pay for copies of the 
>ISOs.  What do they think we're paying them for ?

I also find this a perverse effect totally contrary to their mission or 
at least its spirit: produce good standards for public use.  Without 
free access to reference up-to-date documents, too many "invent" 
something or rely on obsolete or incomplete information grabbed off the 
web.

Try locating reference documents about the format of PDF files, various 
versions...  And PDF is supposed to be a "free" and "open" 
specification.  My ass!

That's a shame!

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Jean-Christophe Deschamps]

>This page has a lot of info about
>Decimal Number support, including
>a set of libraries:
>
>http://speleotrove.com/decimal/

Yes!  IBM and Intel are two of the big names having done significant 
work in this direction and made research and/or results publicly available.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Jean-Christophe Deschamps]

>There are many cases where people are doing calculations or using 
>numbers expecting them to retain all digits.  This would allow the BCD 
>type to be used for that if they really need it.

Currency conversions (rarely exact!) or tax (or margin) calculations 
come to mind as very common uses requiring a bit more than plus and 
minus operations with extended integers support and expected to be 
available at the SQL level along with correct rounding.

It's true that embedding anything like full GNU GMP would make "lite" a 
joke, but a decent subset and something like decimal(n,p) type would 
certainly be beneficial for a large number of SLite users.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[Jean-Christophe Deschamps]

At 18:46 23/03/2011, you wrote:

>Current US national debt is 16 digits.

Nothing less?  That's where the bug lies.

OK, OK, I'm out ;-)


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sorting of Korean Language Characters

[Jean-Christophe Deschamps]

At 09:43 23/03/2011, you wrote:

>I am working on one application which require sorting for Korean Language.
>
>The Korean Characters sort by Jamo(Hangul Jamo) ie based on KSX1001
>character code.
>
>Does sqlite3 or any other package support this type of sorting ?
>If not , then any clue to carry out this type of sorting ?

I would be surprised if ICU wouldn't work for Korean.

Did you get my mail yesterday?  If ICU doesn't fit your needs, I 
believe you can build your Hangul Jamo tries similar to one of the 
tries used in the extension I mailed you and write a very simple nd 
efficient collation based on it.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Passing key as string in system.data.sqlite

[Jean-Christophe Deschamps]

Hi Dear list,

I'm looking into system.data.sqlite and I've been asking myself 
questions about  the most portable encoding of a text passphrase to 
sqlite_[re]key.

It seems to me that the only portable way is to pass an UTF-8 string 
since it's the only encoding invariant wrt byte order and character 
encoding.

I'd like to have confirmation by experienced users of this "almost 
twin" library.

Of course passing a raw string of binary bytes with no notion of 
encoding makes the issue vanish.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

[Jean-Christophe Deschamps]

>But what I postulate is that you can't physically write *the same* record
>over and over more than 90 times per second on a 5400 rpm drive, 
>unless the
>drive, OS, or filesystem implements something like wear-leveling, 
>where the
>physical location of sectors is constantly changing.

It's still possible if the firmware lies about physical sync and, 
having noticed that you repeatedly overwrite the same LBA, just 
postpones writes in its queue and only actually flushes the last one.

By precisely monitoring input power levels, the drive firmware knows 
how much it can write before its internal power reserves drop below 
unreliable levels and it also knows what's left in its write queue, 
accounting for every internal parameter.  So it's still possible that 
some drives give you a much higher rate than our simple-minded theory 
predicts.

Given the low cost of computing power now embarked in most devices, 
such behavior is quite possible.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

[Jean-Christophe Deschamps]

>So if the drive is 5400 rpm, 227 is much more than 5400/60=90 and even if
>it's 7200 (manufacturers sometimes upgrade drives inside portable hd 
>without
>prior notice), it's still twice as much as 7200/60=120.

5400/60, 7200/60 ... those values rely on the assumption that 
successive LBAs are mapped to successive physical sectors (512 or 4K, 
whatever) on the same face of the same plater.  Is it obvious that all 
today's typical stock drives actually implement only that simple old 
scheme and not an untold mix of various interleaving techniques?

Yet that wouldn't explain a two- or threefold difference between 
internal SATA and USB.  That sounds surprising.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.7.6 testing

[Jean-Christophe Deschamps]

>Your goals make a lot of sense.  However I think you can do my second
>suggestion.  Compile with STAT2 code included by default, but make the
>analyze command only build stat1 by default.
>
>This will result in no change in default behaviour, but means that anyone
>wanting to use stat2 can easily do so.  (The vast majority of SQLite
>non-embedded device deployments do not compile SQLite themselves and are
>arguably in the "business applications" demographic.)
>
>If I build a database right now and mail it off to another random SQLite
>user and even if I compile with stat2 and run analyze, then it is unlikely
>they will be able to make use of the stat2 information.
>
>You can make ANALYZE take a numerical argument or a word such as FULL or
>QUICK to explicitly choose which flavour is used.

Roger, you're right once again!

That setup would be a must for many, even possibly most, users with no 
(apparent) downside.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger to filter out characters

[Jean-Christophe Deschamps]

>Could you please show me how to write the condition (to filter out
>characters)?

Use the strfilter function found in extension-functions.c downloadable 
from http://www.sqlite.org/contrib/

Once built and loaded, you can use a trigger similar to

create trigger if not exists trFilterAB after insert on mytable
begin
update mytable set columnB = strfilter(new.columnA, '0123456789*#+pw') 
where rowid = new.rowid;
end;




@venkat,

I guess the OP needs to filter out characters not in the prescribed 
set, not sort out if the data is a 1-character string in this set. 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] slow select from table with data blob

[Jean-Christophe Deschamps]

>It seemed strange that a simple "select * from table" that I was doing 
>was so slow. The table contained about 20 columns (fields) and 300 
>rows. The select took about 1.5 seconds. (using SQLite Expert).

Does the run time settle at 1.5 s after a few runs or is that a 
first-run time ?

As an aside to what others have answered I'd like to point out another 
potential source of slowdown.

I too am using SQLite Expert (Pro version).  This is a truly excellent 
SQLite DB manager.  But don't forget that it will do its best to 
"display" the BLOB column the best it can.  Depending on the options 
you've set the program may have to perform extra work to produce the 
result of your query.  You may want to test various settings likely to 
affect blob display (Show images, Thumbs tracking, Cell auto height 
[?]) to speed up display somehow.  It may be that the column order has 
some impact on the speed of the grid component used for display.

Of course Bogdan [SQLite Expert author] is in the best place to comment.

Now you should also ascertain the good usage of cache size and either 
use the CLI or write a simple program (in whatever language) to time 
the same query and result fetch but without any display.  Time several 
runs to have the caches filled.  That will be fairly close to the time 
SQLite actually uses for processing the statement.  Anything 
significantly departing from that reference time is in the realm of 
Expert and probably its grid component.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] REGEXP parameter order

[Jean-Christophe Deschamps]

Hi Igor,

>I'm not quite sure what you are talking about, but see the 
>documentation of like() and glob() functions here:

You're right about LIKE and GLOB.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] REGEXP parameter order

[Jean-Christophe Deschamps]

> > Sorry for elementary questions but when implementing a REGEXP function
> > (I'm using PCRE from pcre.org) I need to know in which order the two
> > arguments (target, pattern) will be passed to the function.
> >
> > I also would like to retain the last pattern used in compiled form
> > (connection-wise).  Is the use of sqlite3_get_auxdata possible in this
> > context as it is in overriding LIKE functions, for instance?
> >
>
>REGEXP works just like LIKE and GLOB.

Thanks Richard for this confirmation.

Digging further I found mention of this only in the VTAB description, 
but may be a mention or link to the infix argument reversal should 
appear in the LIKE, GLOB, REGEXP, MATCH description, as implementing 
simple extension functions doesn't imply immersion in VTAB stuff (IMVHO).

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] REGEXP parameter order

[Jean-Christophe Deschamps]

Hi all,

Sorry for elementary questions but when implementing a REGEXP function 
(I'm using PCRE from pcre.org) I need to know in which order the two 
arguments (target, pattern) will be passed to the function.

I also would like to retain the last pattern used in compiled form 
(connection-wise).  Is the use of sqlite3_get_auxdata possible in this 
context as it is in overriding LIKE functions, for instance?

Thank you.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to put the database in cache without waiting the system do it by himself ?

[Jean-Christophe Deschamps]

>when i just launch th application, at the beginning the query can take
>around fews seconds... but after some time (10 - 20 minutes), it's take
>only few ms !
>
>so i guess it's because the windows cache in memory the database file ?
>
>so how to speed up this time to make windows cache more fastly in memory
>the database file ?

Read the file!

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Propose minor incompatible API change

[Jean-Christophe Deschamps]

>An end user (think: your mom) wants to upgrade her smartphone to the 
>latest
>OS release.  That new OS release includes the latest shared library for
>SQLite.  But in so doing, some percentage of the apps she has downloaded
>cease to work.  Sure, the problem really is that the apps were incorrectly
>coded.  But does your mom really care about that?  They worked before.  Do
>we really want thousand, perhaps millions, of moms screaming that SQLite
>broke their phone when they upgraded?

Yes if the choice is the only one left, then I for one want zillions of 
users complaining worldwide!  Software liability has to find its way 
someday.  It has become too easy for major software vendors to release 
crap without care and consider that the next release will fix the most 
crying bugs/misfeatures.

Now if it has been that common and easy to download and install a new 
whole OS, then how difficult is it to scan the smartphone at the same 
time for broken applications versions and push in fixed ones?  Again, 
all those companies have plenty of resource for planing such a move and 
the incentive for the phone maker is to maintain a good reputation vs 
competition, for the OS maker to show that his OS can be kept up to 
date and still working fine and for the app supplier that he is careful 
and responsive to his customers.

Taking up the responsability for this bug and changing SQLite for 
coping with incompetence will only open the avenue that every time a 
significant widespread application will prove buggy, then the author 
will be tempted to ask you to put in a specific workaround.

I remind a post by Roger Binns (I believe) regarding the history of 
SMB/Samba and the awfull number of such "workarounds" that have cropped 
into it (to overcome bugs in protocols, in implementations and possibly 
in common big name applications) to such an extent that the whole stuff 
has become unmanageable and unreliable, which seems to be one of the 
main reasons why for instance SQLite can't be used over a network reliably.

Again, and since installing a new OS version into smartphones has been 
that easy, why don't OS makers force download of a revised OS version 
including a specific SQLite version suited to the park?

Even if I agree and understand that the proposed change has minor or 
zero implications to most non-smartphone users, I still advocate 
against making it part of the main core, where it doesn't belong, just 
for the reasons invoked.

I sincerely doubt even Cisco would succeed in forcing a change in the 
IPv6 protocols to work around a bug in some of their routers.

Please read me well: I really don't care which corner case behavior is 
the best  in the future.  I trust SQlite dev team to make the right 
choice(s) just like they have done already so many times.  I also don't 
care as the library I'm maintaining (AutoIt UDF) works fine with both 
old and new versions and so do my own applications.  What makes me 
answer to your poll is that the principle of changing a core component 
due to applicative bugs opens the route to endless negociations about 
which applicative bug should be worked around at SQLite level, which is 
completely backwards.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Propose minor incompatible API change

[Jean-Christophe Deschamps]

>This is, technically, a compatibility break.  On the other hand, there
>appear to be vast numbers of smartphone applications that currently depend
>on undefined behavior and will suddenly stop working if we don't make this
>change.

I understand the proposed change will have no incidence for correctly 
written application but as a principle, I really can't agree with the 
idea of changing an engine core for the benefit of poorly written apps 
disseminated in the wild.

My rationale is that either it's technically or economically feasible 
for the offending applications' developpers to change their code to use 
the library correctly (and that doesn't seem to be the case), OR very 
simply avoid upgrading to the new SQLite versions.  I don't get what 
good reason they had to switch to new SQLite versions.  Any basic app 
testing would have revealed incompatibility before they upgrade to 
newer SQLite, so they knew about the issue.

It makes full sense that if a third-party library evolves, which 
changes break your own bad code, then you keep on using the old version 
without asking the library author to take extra steps backward to cope 
with your own incompetence.  That's specially true when the said 
library is free beer.

Since that market share (smartphones) managed to upgrade the 
smartphones in use with the new SQLite version, how can we understand 
that they can't upgrade their apps as well using a conforming API use?

Now if for some weird reason they can change SQLite lib but can't 
upgrade the offending apps in the field, they can still either 
downgrade to the latest SQLite version which works for them or (and 
they all have enough resource for doing so) fork their own private 
SQLite source from the current version and apply any change they 
want/need, without bragging over the rest of the community.

All this doesn't make any sense to me. 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fill blank fields

[Jean-Christophe Deschamps]

>thanks Simon and Igor for your help on this a few weeks ago, but I 
>need an
>addition to
>
>UPDATE Aircraft SET CN = '*' where CN = '' or CN is null;
>
>what I need to do is replace blank fields in a specific row, sort of
>a double where where statement as in:
>
>UPDATE Aircraft SET CN = '*' where CN = '' or CN is null where 
>(primary key)
>is xyz
>
>What I'm trying to do is update a database, but the users also have an
>automated update system
>that apparently if it finds any blank fields in the row, updates the 
>whole
>row, thereby overwriting
>what I've replaced.  I thought if I could at least replace the blank 
>fields
>with something, it should
>prevent the overwriting.  I don't know which fields are blank, every user
>will be different, so the
>plan was to update the fields I want to, insert text/symbol into the 
>blank
>fields and leave the others
>alone.  I realize that because I don't know which fields are 
>empty/blank I'm
>going to have to create
>a statement for every column I'm not updating to, there are only 10 
>columns
>altogether and I'll
>typically be updating to at least 5 of them.

You should be able to acheive what you want (keep as many columns 
non-empty and non-blank) even while users cause rows update which don't 
follow your rules.

Create an AFTER UPDATE OF (colA, colB, ... ) WHEN new.colA = '' OR 
new.colA IS NULL OR new.colB = '' OR new.colB IS NULL OR ... trigger 
where you can force some value in the columns of your choice.  It will 
surely slightly slow down insert rate, but that doesn't seem to be a 
blocking reason in your case. 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Rounding Strategy

[Jean-Christophe Deschamps]

Scott,

>SQLite handles rounding by running the value through the internal
>printf with the appropriate precision.  As best I can tell the
>internal printf adds half a unit at the appropriate position, then
>truncates.

Yep, truncation is a way to do it but doesn't meet layman expectations 
in many (most?) cases.  Rounding is another beast entirely.

>   Since the 3.05 isn't precisely represented (with format
>%.16f, I get "3.0498"), adding .05 doesn't do much to
>improve the situation.

Due to truncation, OK. If round() were using some "round to rearest, 
ties away from zero" setting (IEEE754r) it would always work.  But that 
isn't easy to implement portably AFAICT.

>Definitely be careful when using floating-point.  This kind of thing
>is why there are so many number types in different SQL
>implementations.

... and so many settings in IEE hardware/software.  I really look 
forward Intel decimal FP being mainstream, specially in business-likely 
code like a DB engine.  But until such baby finds its way in hardware, 
I guess we're stuck with this pesky binary thing and slow / imprecise 
conversions back and forth and 1/3 + 1/3 + 1/3 != 1.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Rounding Strategy

[Jean-Christophe Deschamps]

> > select round(3.05, 1)
>3.0
>
>Is this expected behavior for SQLite?
>The documentation isn't specific on the rounding strategy that is used.
>
>My personal expectation was that this would round to 3.1.

You _expect_ that 3.05 will represent exactly as 3.05 in IEEE, but it 
that the case?  Should the nearest FP representation be 
3.048 then the round() function would work fine, which is 
likely.

In such case, add half a unit at the position below rounding 
position.  Half a unit is guaranteed to have an exact representation 
under IEEE (at least in my old time), being a power of 2.

[Binary] FP can be utterly misleading.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] delete from t1 where not in (null)

[Jean-Christophe Deschamps]

Hi,

SQLite sounds pretty reasonnable to me:


>select "select distinct favicon_id from moz_places";
>select distinct favicon_id from moz_places;

Returns NULL

>select "deleting: standard method...";
>delete from moz_favicons where id not in (select distinct favicon_id
>from moz_places); -- here is the alleged bug?

Are any id row(s) in moz_favicons = NULL ?   No, hence I don't any 
surprise here.

Rows in moz_favicons are:

id  url datamime_type   expiration
1   http://fedoraproject.org/static/images/favicon.ico  (BLOB) 
image/png   1293223842352252
2   http://slashdot.org/favicon.ico (BLOB)  image/png   1293221318471851

No, there are no NULLs in IDs.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL query on sort order

[Jean-Christophe Deschamps]

Harish,

>We have a problem with a sql query.
>In a table, a column called "name" contains character data that may 
>include
>alpha, numeric and special characters. It is required to sort in such 
>a way
>that names starting with alpha characters are listed first, then numerals
>and finally special characters.
>For example, the column contains data: [Bhaskar, 5th user, anand, ##, 
>Anand,
>bhaskar ].
>When lsited, it should appear as [Anand, anand, Bhaskar, bhaskar, 5th 
>user,
>##].

I just mailed you an extension for SQLite offering the collation you need.

Hope it will fit your bill. 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] cost of sqlite3_open

[Jean-Christophe Deschamps]

>It's not contradictory. I say that "real cost" of sqlite3_open is
>parsing the schema. Igor says that this cost is actually deferred from
>inside sqlite3_open call to the first "substantive" sqlite3_step call.
>So you will have to pay this price anyway, just profiler output would
>be somewhat confusing.

I agree that the price has to be paid somewhere in time.  The issue I 
raised was about the precise moment in was occuringin time, nothing 
else.  Nothing really important anyway.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] cost of sqlite3_open

[Jean-Christophe Deschamps]

Pavel,

> > 1) How "expensive" is a call to sqlite3_open. Does a call to 
> sqlite3_enable_shared_cache make it "cheaper"?
>
>Its cost depends on the size of your schema as it gets parsed during
>open.


Isn't this contradictory with an answer by Igor made in a recent thread?

>Subject: Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs
>
>
>Nick Shaw  wrote:
> > Is there a reason that sqlite3_open() will happily "open" a non-sqlite
> > file, returning SQLITE_OK, instead of returning SQLITE_NOTADB, which
> > would seem a more obvious return value?
>
>SQLite doesn't actually touch the file until the first "substantive" 
>statement is executed on the connection. This allows one to set 
>various PRAGMAs that can only be set before the database is created.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE 3.7.3 bug report (shell) - output in column mode does not align UTF8-strings correctly

[Jean-Christophe Deschamps]

At 14:26 26/11/2010, you wrote:

>N.b., there is a severe bug (pointers calculated based on truncated 
>16-bit
>values above plane-0) in a popular Unicode-properties SQLite extension.
>The extension only attempts covering a few high-plane characters—if 
>memory
>serves, three of thhem in array 198; but with the high-bits snipped 
>off, I
>rather doubt those will be what is actually affected.  I attempted
>contacting the author about the bug last year when I discovered it, but
>was unable to find a private contact method on a brief glance through 
>the
>author’s site.  Perhaps the bug has been fixed by now; I never checked
>back; anyone who intelligently investigates compiler warnings would 
>not be
>bitten anyway.  I write off the whole episode as a victory for spammers.

I believe you refer to Ioannis code.  I found this 16-bit truncation 
and decided to expand that trie to 32-bit in order to support those 
characters correctly.  As I had many several distinct needs (still 
highly related to Unicode) I decided to rewrite most of the code and 
expand it in a number of directions.  Anyone interested can contact me 
so I post the source.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reducing time to create indexes

[Jean-Christophe Deschamps]

>Simon, I'll try that and see what difference itmakes

That or build a :memory: DB, populate it, build indices and then only 
back it up to disk using the backup API.  That requires you have enough 
memory available, but should be really fast if you have.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Confused by import of NULL values from CSV file

[Jean-Christophe Deschamps]

>I import a CSV flat file into a table (using sqliteman as GUI).  The 
>flat file has NULL values as simply no data between two delimiters (I 
>use 'tab' as Delimiter).
>
>The resulting table does not treat 'no data' between 2 delimiters as 
>NULL.  When I run CHECK constraints etc. on it it behaves as if the 
>'no data' was some value.  For example in a query it will show an 
>empty value but not as NULL (I can see this as SQLiteman has 
>highlights showing NULL fields).
>
>How can I set up Sqlite so it will read 'no data' between two 
>delimiters as NULL on file import?  Thanks

I believe it isn't as much an issue that's up to SQLite itself to 
manage, but rather the import tool you use.

While Igor is right to point out that NULLs were never mentionned in 
any .CSV RFC/document, it is still possible for an import tool to adopt 
a [private] convention, like:

"col_1","col_2","col_3"
"abc",123,"def"
,456,
"ghi",,"jkl"

being imported as

abc|123|def
null|456|null
ghi|null|jkl

but this requires a contract between the .csv producer[s] and the 
importer, as well as the use of text delimiters in order to 
differentiate an empty string from a null.

 From this point of view, SQLite has little control over that.  You can 
probably modify your copy of the CLI to adopt this convention, or write 
your own importer, which isn't hard if you don't try to make it 
universal but instead highly proprietary.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE: sorting

[Jean-Christophe Deschamps]

Look in your mailbox.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragma foreign_keys not persistent?

[Jean-Christophe Deschamps]

>As far as I can tell, turning on foreign_keys for a database is not 
>persistent. It only seems to be valid for that connection/session. So 
>this means I have to prefix every command that I send to the database 
>with a command to turn on foreign_keys and I therefore can't issue 
>one-off commands, but they must be part of a session. Is this correct?
>
>This worries me since any ad-hoc use of the database that does not 
>implicitly turn on foreign keys is likely to mess up the data integrity.
>
>Wouldn't it be better to have the foreign_keys setting persistent 
>between sessions, so that once it's turned on, it stays turned on for 
>all future sessions (unless explicitly turned off)?
>
>Thanks,
>Tom
>BareFeetWare
>
>  --
>Comparison of SQLite GUI tools:
>http://www.barefeetware.com/sqlite/compare/?ml

Very true, and the danger is even bigger when using SQLite third-party 
managers as not all of them make that pragma a persistent setting 
accross runs.

This setting is a meta-data of the database and really should go along 
with it for _any_ use.  The same applies to recursive_triggers pragma, 
albeit possibly of less common use in the wild.

If preserving backwards compatibility is seen as an absolute 
requirement, it would be easy to introduce a couple of new and simple 
"persistent_foreign_keys" and "persistent_recursive_triggers" pragmas.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA database_list: insert into table?

[Jean-Christophe Deschamps]

>But Roger, the  "layer sitting in front of SQLite"  is a programming
>environment which provides its own (black-box) connectivity to SQLite, and
>it isn't going to be calling any DLL into which one will have injected 
>a UDF
>library in the manner you have laid out, and it's not going to let the
>developer load an extension either.   That's what I've been trying to make
>clear.

I second that.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ON conditions vs. WHERE conditions

[Jean-Christophe Deschamps]

> > What is the rationale about placing complex conditions in the ON part
> > of an inner join rather than in an WHERE clause?
>
>Except for outer joins, the difference is purely stylistic. They are 
>functionally equivalent. In fact, SQLite internally rewrites the 
>former to the latter, before generating the query plan.
>
>For outer joins (of which SQLite only supports LEFT JOIN), the 
>distinction is significant.

That was my impression and consistent with my real-world findings (wall 
clock made).  Just wanted to avoid a possible pitfall.

Outer is another beast.

Thanks Igor.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ON conditions vs. WHERE conditions

[Jean-Christophe Deschamps]

What is the rationale about placing complex conditions in the ON part 
of an inner join rather than in an WHERE clause?

I understand that the ON clause will limit the temporary table 
processed by a subsequent WHERE clause, while a larger table will be 
filtered by the WHERE part if no selective ON clause is present.
But with a complex condition (20+ sub-conditions on various part of 
joined tables) requiring full tables scan, aren't both solutions 
essentially equivalent?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error in sum function

[Jean-Christophe Deschamps]

Ricardo,

>I have typeof(basket)=real in all records  ...
>I just see now that all data are float numbers in the text file that was
>used for inserting rows, and did not notice this because SqliteExpert
>show only ints !

SQLite Expert (which I use extensively in the Pro version) enforces 
displaying types as declared.  So if you declare your column as INTEGER 
(or INT, ...) then it will display integers regardless of the actual 
individual data type using common conversions.  This is a side effect 
of the Delphi grid component that Bogdan uses.

I see that as a unevitable drawback of SQLite flexibility about 
types.  A generic tool like Expert needs to stick to some set of rules 
about types, which isn't the case for an ad hoc application, where you 
have full lattitude to manage varied types inside a column and apply 
complex processing/display rules, possibly far beyond what SQL would allow.


--
       Jean-Christophe Deschamps
eMail: <mailto:j...@q-e-d.org>j...@q-e-d.org
SnailsTo:   1308, route du Vicot
   40230 Saint Jean de Marsacq
 France
GSM: +33 (0)6 15 10 19 29
Home:+33 (0)5 58 77 71 79

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reg: In Memory Database Using SQLite

[Jean-Christophe Deschamps]

>I'm aware that SQLite offers the functionality of in-memory databases, 
>but I
>don't know how to read the data and send it over the wire to the server or
>how to push the data into the in-memory database of the server.

The backup API included in SQLite offers the facility to backup/restore 
(in fact = copy) disk-based or memory databases to/from disk or 
memory.  So you can launch a backup operation all in one shunk to 
create a memory DB, work on it and then backup it back to the 
server.  I use this everyday and it works like a charm.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CREATE TABLE work arounds?

[Jean-Christophe Deschamps]

>I am parsing fields on the fly and then creating tables, unfortunately 
>one of the fields is "Order" and is a "special word" in SQL as is not 
>allowed. Is there a way around this instead of intercepting with perl 
>s'/Order/Orders/g'

Can you wrap every column name inside double quotes or square brackets?
"Order"
[Order]

Both will work, but you'll have to use this syntax in your commands and 
queries.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] copy data from one db to another

[Jean-Christophe Deschamps]

>What's the best way to copy data from one db to another?
>
>Given 2 databases with identical schemas, one full of data and the
>other empty, the brute force way would be to perform selects on the
>source db, then for each row, perform an insert into the destination
>db.  Is there a more efficient way?

The easiest is either to simply copy the file as Igor suggested or use 
the backup API (very easy too).


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to search for asterix character?

[Jean-Christophe Deschamps]

>I use vb or in SQLlite Expert the * does not seem to work
> >From my testing * doesn't seem to work in the likeif I use '%33' it 
> returns
>everything with test33if I use '33%' it returns everything with 
>33testif I
>use '%33%' it returns everything with 33test, test33 which is the same as
>*

I can't vouch for VB, but SQLite Expert --which I use extensively-- 
definitely handles asterisks '*' in litterals correctly.

What do you mean with "which is the same as *".  Asterisk has no 
special meaning for LIKE.  You must be confusing with GLOB.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to search for asterix character?

[Jean-Christophe Deschamps]

>How do I search for the asterix character *  ??
>
>This doesn't work:
>select field1 from table1 where field1 like '%FH%*%'
>as the * character here seems to be ignored.
>
>Using the latest version of SQLite.

You're doing it right.  Either you use a wrapper that messes with * in 
litterals or there is actually no matching row.  Try using the 
command-line tool to better diagnose what's going on here.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] network access problem

[Jean-Christophe Deschamps]

>Running in sqlite application in Virtual Box, attempt to open a 
>database with sqlite from a shared network folder 
>\\Vboxsvr\testdata  however the open16 and openv2 (with read 
>only)  both fail --- rc = 14.  File opens file if moved locally to 
>hard drive.
>
>using latest version of sqlite3 3.6.23.1
>
>this seems to be a bug in the open code of sqlite when accessing a 
>file across a network.

I don't believe so: I use several bases which I open with MS short UNC 
syntax 200 times a day without any problem ever (using vanilla 3.6.23.1).

Your issue is elsewhere.  Make sure the account you use has enough 
rights to the destination folder and check you can access it with, for 
instance, a innocent application like a hex editor or a third-party 
SQLite manager.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] what languages

[Jean-Christophe Deschamps]

>I use sqlite from within Autoit V3 (Autoit is a windows-oriented 
>basic-like language)

AutoIt, while a scripting language can be seen and used as a RAD 
platform.  It enjoys good support, up to date SQLite embedding and 
executables produced can include any file your application needs, like 
a DB, help files.  In case you need no-installation executables and 
mono-threaded general purpose platform for Windows with easy to lear 
Basic-like language and a huge library, this can be a very good choice.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Loading Sqlite3 DB into memory

[Jean-Christophe Deschamps]

>Is there any way to load a sqlite3 db from a location in memory? 
>Ideally, I'd like to have a memory pointer (eg, something provided via 
>malloc), which I could then use as the handle sqlite3 uses to load the 
>database.
>
>The reason I'm trying to do this: I have an encrypted database file 
>that I will copy into memory, decrypt, and then process. I would like 
>to do this entirely in memory (no unencrypted files on the HD) to 
>reduce the likelihood of casual users snooping in the db file.

You can use the backup API to make a copy of the disk DB to a :memory: 
DB and work on this one.  You won't have any memory housekeeping to do: 
everything is handled by SQLite.  Drawback or advantage, depending on 
your precise context: you can't share your memory DB with other processes.

As I understand it, you won't be using the disk DB by itself, so you 
can "backup" the baby in one shot as there is no concurrency in your 
situation.
This can be made to work in only a handful of simple calls to the 
SQLite API.

Another possibility is use some RAMdisk driver for your OS and copy the 
disk file there, but then be aware that it might then be easier for 
curious people to dissect your DB.  To overcome this, you'll need to 
wipe the RAMdisk file as well, adding more complexity to your code.

I'd favor the backup way, much simpler.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] recursive select in sqlite

[Jean-Christophe Deschamps]

>z> i wonder is there RECURSIVE select function in sqlite? the background
>z> for the question are: create table objects (id INTEGER PRIMARY KEY
>z> AUTOINCREMENT, name text unique) create table tree(id int, child_id
>z> int, PRIMARY KEY(id, child_id)) i want to draw the whole tree, is 
>there
>z> good solution for the function?
>
>If you want to store a tree structure in relational tables, then you 
>could
>read up on things like this:
>
>http://articles.sitepoint.com/article/hierarchical-data-database
>
>The model on the second page is the one to go for.

Just adding a note to this good advice: depending on your application 
current and future needs, you may find it useful to store a "level" 
column, with for instance, 0 being the root, 1 the first level branches 
or leaves, aso.

If ever you have usage for it, it will simplify many queries a great 
lot while eating only little space.  As an example, you can then build 
a hierarchical SQL view of the tree or subtree, displaying it exactly 
as the display_tree() PHP function shown on this page, all with simple SQL.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select intersecting intervals

[Jean-Christophe Deschamps]

>The Minimal-Perfect-Hash-INTERSECTION-OF-VECTORS approach might benefit
>queries against tables having several million rows. What I'm wondering 
>(and
>lack the C skills to find out for myself) is whether SQLite's underlying
>algorithms for INTERSECT could be optimized with a minimal perfect hash
>approach. The algorithms would decide which vector of ids is the better
>candidate to be used for the MPH and which is the better candidate to be
>iterated, and then iterate over the one vector of ids, testing for the
>existence of each id in the MPH using the optimized Exists() function
>supplied by the mph library for the particular type of mph being used.
>
>The question, in scenarios where the vectors contain many items, is 
>whether
>the overhead of creating the MPH and testing for existence is 
>significantly
>less than the overhead of doing whatever INTERSECT is doing now when it
>intersects vectors of ids.  You have a ready-made acronym to advertise the
>speed if it turns out to be faster: MPH.  ;-)

Ooops, for some reason the end of my post went deleted.  I was adding 
that the simplest naive approch using only one index can be magnitude 
faster, depending on how selective the conditions are, for a given set 
content.  I believe the key is "know your data" for the same reason 
you'll craft a regexp differently depending on what you know about the 
target text.  Histogram can help this.

Good MPH pun!


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

[Jean-Christophe Deschamps]

> > In my low-concurrency, familly-business context, I have no problem at
> > all setting 3 hours timeout using the built-in function, when the
> > slowest transaction may only take less than 5 minutes.
>
>With this condition as a 4th one in your list and with 5th one stating
>that you have less than 30 slowest transactions in 3 hours you have a
>very low possibility of having SQLITE_BUSY in the application. It
>still is not 0 (your computer could be heavy loaded with something
>else, memory consumed almost to the limit and swap is slowing down
>everything significantly) but it should be too low to think about.
>
>You need additional conditions about concurrency because SQLite
>doesn't guarantee fairness executing concurrent transactions with busy
>handler involved. In the message linked by Michael poster clearly says
>that he observed some transactions starting to wait early and finally
>executed later than other transactions which started to wait much
>later. So you have to think what is the worst case when some
>transaction started to wait and another transactions are keep coming
>and coming and finally 3 hours are gone but there was no chance for
>the first transaction to obtain necessary lock. Think about
>possibility of such "luck" and whether you're comfortable in ignoring
>it.

You're right pointing out boldly that such chaining of "new" 
transactions can keep an older one from executing in the alloted time 
frame.

I didn't mention that explicitely as I'm really in utterly low 
concurrency context and that condition would require an army of familly 
monkeys typing to have the slightliest odd of occurring.  But it 
shouldn't be completely ignored in the general case.

Perhaps SQLite should have a no-nonsense approach to this issue and 
introduce some determinism, even in a primitive way.  I find it worst 
than unfair, as the waiting process can do absolutely nothing against 
the situation where it is "ignored".

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

[Jean-Christophe Deschamps]

Let me take a reality check for the case of my own usage.  If I 
guarantee that the following conditions are all true:

All R^n (Read-Read-...-Read) atomic operations are enclosed in BEGIN 
transactions.
All W^n (Write-Write-...-Write) and RMW (Read-Modify-Write) atomic 
operations are enclosed in BEGIN IMMEDIATE transactions.
All connections setup a sqlite3_timeout() much longer than any 
transaction in the "system", including background backups.

Then am I entitled to say that no deadlock will ever occur (barring 
catastrophic condition like hardware failure, memory shortage, corrupt 
DB and such)?

In my low-concurrency, familly-business context, I have no problem at 
all setting 3 hours timeout using the built-in function, when the 
slowest transaction may only take less than 5 minutes.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select intersecting intervals

[Jean-Christophe Deschamps]

>
>I would first create an INTEGER primary key and then place an index on 
>name,
>another on i_from, and another on i_to, and then see if the approach below
>has any benefit.
>
>When I tried this with a geo-queryit was actually slower than the standard
>select, and I'm curious if that's always going to be the case. It will 
>come
>down to how efficient the INTERSECT of the vectors of integers is. Each
>vector will have been the result of an index-scan.  If INTERSECT were
>optimized (perhaps with a minimal perfect hash function
>http://cmph.sourceforge.net/index.html) this approach might be useful.


All three following queries use only simple indexes (PK, name, lo, hi).

Query#1:
select * from tst where lo < 345678
 intersect
select * from tst where hi > 123456
 intersect
select * from tst where name = 'aaelj';

Query#2
select * from tst
 join (
 select rowid from tst where lo < 345678
 intersect
 select rowid from tst where hi > 123456
 ) as interval
 on tst.rowid = interval.rowid and name = 'aaelj';

Query#3
select * from tst
 join (
 select rowid from tst where lo < 345678
 intersect
 select rowid from tst where hi > 123456
 intersect
 select rowid from tst where name = 'aaelj'
 ) as interval
 on tst.rowid = interval.rowid;

On a 200K-row test table with random data, queries #2 and #3 were 
essentially identical while #1 was twice slower (moving too much data 
around, uselessly).


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT question (computing day of week the using strftime() function)

[Jean-Christophe Deschamps]

>I need to find out how many specific weekdays (e.g., how many Sundays) 
>I have in any given range of dates.
>My problem: How to use the COUNT function in combination with the 
>strftime() function.
>
>$ sqlite3 test.db3
>SQLite version 3.6.20
>sqlite> create table test (date VARCHAR(20), money INTEGER);
>sqlite> INSERT INTO "test" VALUES('2007-07-20', 1000);
>sqlite> INSERT INTO "test" VALUES('2007-07-21', 2100);
>sqlite> INSERT INTO "test" VALUES('2007-07-22', 2200);

That will do:
select count(*) from test where date between '2007-07-20' and 
'2007-07-22' and strftime('%w', date) = '0';

This will select and count, not compute anything.

___
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

[Jean-Christophe Deschamps]

Tim,

>But did I say that  GLOB uses an index if it has been overloaded?  No.  I
>wrote that if LIKE has been overloaded, queries that contain LIKE 
>won't use
>the index.  Typically, GLOB won't have been overridden too just 
>because LIKE
>has been overridden: the rationale for overriding the LIKE operator 
>does not
>apply equally to GLOB, and it would make little sense to override GLOB 
>in a
>manner that vitiates its raison d'être. You are conflating these two
>functions ("... if LIKE/GLOB has been overridden... overloads LIKE/GLOB")
>but in important respects they are dissimilar.

I agree it is possible to overload LIKE and GLOB independantly but I 
don't see a practical situation where overloading only one of them 
would be desirable.

For instance, if some extension overloads LIKE to support ICU, it would 
be logical and consistent to overload GLOB with the same 
function.  Given that the two entries differ only by a parameter, 
enjoying Unicode support in LIKE and not in GLOB (or vice-versa) would 
be a bit strange.

Should one have a need to keep the native functions untouched, there is 
the easy possibility to call the new versions with new names (e.g. 
LIKEU, GLOBU) even if that makes the SQL less standard.

In short: possible yes, likely not much.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Exclusive transactions over network

[Jean-Christophe Deschamps]

Hi gurus,

I'm aware of the limitations that generally preclude using SQLite over 
a network.
Anyway do you think that doing so with every read or write operation 
wrapped inside an explicit exclusive transaction can be a safe way to 
run a DB for a group of 10 people under low load (typically 2Kb read or 
100b writes per user per minute)?
Schema will be very simple and queries / inserts as well.  Speed is not 
a real concern.

So do you believe DB corruption can still occur in this context, 
knowing that the use will be for a very limited time (2-3 weeks) and 
low volume (~50K rows)?

Using one of the available client/server wrappers is not a suitable option.
This is targeted at Windows, XP or later.

Do you have a better idea to make the thing more robust, even at 
additional cost in concurrency and/or speed.

___
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

[Jean-Christophe Deschamps]

Tim,

>Queries using GLOB do use the index on the column in question (i.e.
>optimization is attempted)
>Queries using LIKE do not use that index if the LIKE operator has been
>overridden.

Sorry but GLOB doesn't use an index either if LIKE/GLOB has been 
overloaded.  This is consistent with the docs and the output of Explain 
query plan for both variants when an extension is active and overloads 
LIKE/GLOB.

Things can be different with a custom built of SQLite, where native 
LIKE/GLOB itself has been modified.  With custom code, all bets are off.

___
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

[Jean-Christophe Deschamps]

At 14:31 26/04/2010, you wrote:

>If the implementation of SQLite you are using overrides the LIKE operator
>(as more than a few do), then SQLite will not make use of an index on the
>column in question. Use the GLOB operator instead.

I doubt it.  GLOB is absolutely nothing more or less than an invokation 
of the same code for LIKE but with slightly different 
parameters.  Except if people have made a completely differing version, 
departing from the architecture of the standard SQLite code (and there 
is little reason to, if any) AND have made LIKE and GLOB two completely 
distinct functions, there shouldn't be any significant difference in 
running time (for equivalent queries, of course).

Also if ever LIKE is overloaded, then GLOB gets excluded from standard 
optimization, except large changes in SQLite code.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Restriction of updating unique column

[Jean-Christophe Deschamps]

> > Contrary to what occurs in other engines, SQLite seems to
> > assert constraints at insert/delete time, ignoring the fact that
> > (insert or delete) trigger will increment or decrement the upper part
> > of the tree interval on HI and LO keys (in the case of a nested tree).
>
>This /should/ be handled properly if you make appropriate use of the 
>BEFORE or AFTER words in your TRIGGERs.  Can you verify whether you 
>are doing so in this case ?

Yes, in the case of nested trees, we need to update LO first on delete, 
but HI first on insert.  The following raise a constraint error, which 
disappears when Unique constraints are removed.  More precisely, the 
constraint is raise on HI being "not unique" on delete, while LO is 
deemed "non unique" on insert.  Removing both constraints works and 
shows that if the constraints were checked after the relevant trigger 
action, nothing would be in violation.

CREATE TABLE "Tree" (
   "lo" INTEGER NOT NULL,
   "hi" INTEGER NOT NULL,
   "value" TEXT DEFAULT '');

CREATE TRIGGER "tgDelNode"
AFTER DELETE
ON "Tree"
FOR EACH ROW
BEGIN
  delete from tree where lo > old.lo and hi < old.hi;
  update tree set lo = lo - (old.hi - old.lo + 1) where lo >= 
old.lo;
  update tree set hi = hi - (old.hi - old.lo + 1) where hi >= old.lo;
END;

CREATE TRIGGER "tgInsLeaf"
BEFORE INSERT
ON "Tree"
FOR EACH ROW
BEGIN
  update tree set hi = hi + 2 where hi >= new.lo;
  update tree set lo = lo + 2 where lo >= new.lo;
END;

CREATE UNIQUE INDEX [ixHi] ON [Tree] ([hi]);

CREATE UNIQUE INDEX [ixLo] ON [Tree] ([lo]);

To make this ready to work, insert a root element as
insert into Tree values (1, 2, 'root');

then insert some nodes:
insert into tree values (2, 3, 'node A');
insert into tree values (4, 5, 'node B');
insert into tree values (6, 7, 'node C');
then some leaves:
insert into tree values (3, 4, 'leaf 1');
insert into tree values (5, 6, 'leaf 2');
insert into tree values (9,10, 'leaf a');



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Restriction of updating unique column

[Jean-Christophe Deschamps]

>I found the the restriction of updating unique column in ver3.6.21.
>and same problem is reported in follwoing mail
>
>Marc-Andre Gosselin wrote:
>date: Thu, 16 Jun 2005
>title: "[sqlite] Update unique column"
> >
> > I discovered a behavior in SQLite 2.8.16 that doesn't conform to 
> the SQL
> > standard, here's an example :
> >
> > CREATE TABLE tbUpdateUnique (a INTEGER PRIMARY KEY, b INTEGER UNIQUE, c
> > VARCHAR(100));
> > INSERT INTO tbUpdateUnique VALUES('', 1, "Test 1");
> > INSERT INTO tbUpdateUnique VALUES('', 2, "Test 2");
> > INSERT INTO tbUpdateUnique VALUES('', 3, "Test 3");
> >
> > Now when I try the following update, I get a constraint error :
> >
> > UPDATE tbUpdateUnique SET b = b + 1 WHERE b >= 2;
> >
> > In the SQL Standard and NIST SQL test suite they say than an update
> > should be considered atomic, and verify unique constraints only 
> after the operation has
> > updated all rows. From what I experienced with SQLite, constraints 
> are verified
> > after each row has been updated, resulting in a constraint error. I 
> also tried
> > these with no success :
> >
> > BEGIN TRANSACTION;
> > UPDATE tbUpdateUnique SET b = b + 1 WHERE b >= 2;
> > COMMIT TRANSACTION;
> >
> > and
> >
> > UPDATE tbUpdateUnique SET b = b + 1 WHERE a IN (SELECT a FROM 
> tbUpdateUnique
> > WHERE b >= 2 ORDER BY b DESC);
> >
>
># full mail can be seen in archieve log at
># http://www.mail-archive.com/sqlite-users@sqlite.org/msg08597.html
>
>Will this restriction be fixed in near feature?
>
>I also know the workaround to use temporary value and update twice.
>(example is shown is
>http://www.mail-archive.com/sqlite-users@sqlite.org/msg50894.html)
>but I don't want to use this workaround if possible
>because it is not gut feeling and less performance.

I also have been in the situation where a unique constraint gets raised 
early: while inserting items in a nested tree (using integral LO-HI 
intervals) you have to completely abandon unique constraints on the LO 
and HI keys.  Contrary to what occurs in other engines, SQLite seems to 
assert constraints at insert/delete time, ignoring the fact that 
(insert or delete) trigger will increment or decrement the upper part 
of the tree interval on HI and LO keys (in the case of a nested tree).

At least, there should be a pragma or clause similar to the "deferred" 
clause available for foreign keys, causing uniqueness/existence/custom 
constraints to be checked only after FK (=trigger) action.  With such 
feature available, we wouldn't have to sacrifice integrity constraints 
on critical columns.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUBSTR overload and ALTER TABLE

[Jean-Christophe Deschamps]

>We just experienced the hard way that overloading certain built-in SQL
>function can interfere with core SQL commands if the overloaded function
>behaves differently from the built-in function.
>
>Not surprising, after looking at the sources:
>
>* ALTER TABLE - alter.c uses SUBSTR and LIKE.
>* VACUUM  - vacuum.c uses SUBSTR, LIKE, and QUOTE.
>* Possible others?
>
>Would it be possible that the SQLite core always uses the built-in
>functions instead of the overloaded ones? Or should overloading be
>disabled for "core critical" SQL functions?
>
>If not, maybe those "critical" functions could be mentioned in the
>documentation to warn developers that strange things might happen if
>they change their behavior?
>
>IMHO, LIKE is especially critical since it is a likely candidate for
>overriding to add UNICODE LIKE to applications.

Your finding is scary.  At first glance my Unicode-twisted LIKE won't 
mess up things, but my LIKE works with unaccented versions of strings, 
so all bets are off w.r.t. usage in database integrity critical 
operations...

IMVHO, the core definitely should call an internal _...@_like_@_ aliased 
to the original core version.  The same for your other discovers, of 
course.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with sqlite providing different programs different data

[Jean-Christophe Deschamps]

>Both of these tools show a version of the database that is different
>from what i see in the command line and they are equivalent in their
>discrepancies (they are different from the command line but the same
>as each other).
>
>So heres the basica scenario:
>
>1. i update my database with the code i wrote
>2. those changes are reflected in the command line
>3. those changes are not reflected in Lita/SEP
>
>1. I update information in Lita/SEP on that same database file
>2. those changes are reflected in Lita/SEP
>3. those changes are not reflected in the command line

I use SEP heavily and never had such issue.  I don't use Lita so I 
can't comment on it.
Sorry to ask the obvious: have you double-checked you are using the 
same physical file in the CLI and in SEP?

Only times when SEP doesn't reflect actual DB content is when you have 
the Data or View open and have other processes change the 
table/view.  Obviously you have to hit Refresh to see the changes 
appear.  Beside that, I don't see how that can happen.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite template files

[Jean-Christophe Deschamps]

Hi Tom,

>BTW- if you haven't done so already; it may be of use to the user to add
>extensions: VirtualText and Jean-Christophe Deschamps has an extension
>for fuzzy search for example.

There is no problem.  Alexey put it on his website under the extension 
for Unicode folder.  Please drop me a mail if you're interested, as the 
code as it is currently compiles for Windows only, but I'm willing to 
work to port to whatever system.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] All ancestors / descendents of a vertex in sqlite?

[Jean-Christophe Deschamps]

>I have a (small) directed graph which I would be able to fins all
>ancestors or descendents of a certain vertex (transitive closure?).
>So, using this graph:
>
>CREATE TABLE levels_levels (parent_id INTEGER ,child_id INTEGER,
>UNIQUE(parent_id, child_id));
>INSERT INTO "levels_levels" VALUES(6,7);
>INSERT INTO "levels_levels" VALUES(6,8);
>INSERT INTO "levels_levels" VALUES(8,9);
>INSERT INTO "levels_levels" VALUES(7,10);
>INSERT INTO "levels_levels" VALUES(9,10);
>
>which would look like this:
>
>  6
>7 8
>|  9
>10
>
>I would like to make it possible to find 10 as a descendent of 8 and 6
>as a parent of 9 (for instance).
>
>I have found a couple of procedural solutions using procedural calls
>in sql server or postgresql, but is there a solution that I could get
>into sqlite?
>
>The graph will never be very big, updating efficiency is not an
>important factor. It will be queried a lot though, so search
>efficiency is important.

Re-implement your levels_levels table as a couple of twin trees 
(father, mother) using integer intervals (lookup nested tree).  All 
common queries into that can then be made very efficiently using a 
single SQL statement.  Inserts (and updates but are there many in 
genealogy?) will definitely take longer, but your tree probably doesn't 
see too many new leaves each second!




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Result_* functions

[Jean-Christophe Deschamps]

Is it allowable/safe to invoke more than once any sqlite3_result_*() 
function?  In other terms, subsequent invokation of any result function 
will it harmlessly override a previous one?

As in:

init...
// once for all, post null return in anticipation for the various cases 
where
// parameters or arguments would fail to make sense
sqlite3_result_null();

if (dumb case #1) return;
if (dumb case #2) return;
if (dumb case #3) return;
...
if (dumb case #N) return;

// perform requested action on known-valid input

// override null result by some valid result
sqlite3_result_int(...);




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unicode command line bug in Windows version of sqlite3 with patch

[Jean-Christophe Deschamps]

>We currently use sqlite 3.6.23. We have a big problem with characters with
>accents or other special characters in path to database file, for 
>example in
>Czech Windows XP the "Application Data" folder is translated to "Data
>aplikací" so if the accented 'í' is in path the sqlite3.exe writes that it
>is unable to open file in this path.

A much better solution is to use a MSYS terminal (installed by MinGW), 
so you have UTF-8 command-line and data entry/display without 
conversion.  No need to "patch" anything.

The culprit here isn't the command-line utility, but the WinDOS usage 
of old charset.

>In attachment you will find diff patch which works for us. But because
>SQLite3 source code is really big we can't be sure that this fix is 
>correct
>and doesn't introduce any side effects.

If you change input encoding and use your code page, then it's likely 
you'll going to do the same with data, which is plain wrong: SQLite 
needs UTF-8 (or UTF-16) data, not ANSI.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] regular expressions

[Jean-Christophe Deschamps]

>is anybody aware of a possibility to do s.th. like
>select * from table where field like '[A|a]%'

Unless non-standard compile option and provided you don't issue
 PRAGMA case_sensitive_like = 1;
LIKE is case-insensitive, so LIKE 'A%' is the same as LIKE 'a%'

SQLite offers another filtering function: GLOB, which is case-sensitive 
and can accept '*', '?' wildcards (instead of LIKE' % and _) and a 
regexp-style character subset for selection '[a-z]' or exclusion '[^0-9]'.

select 'abcd123' glob '*[Cc]?[1-9]2*';
1




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] if exist

[Jean-Christophe Deschamps]

>Yours returns 1 or 0. Mine returns length if found, otherwise 0.

That's true but the subject title led me to believe that the OP 
intended to have a 0 vs. nonzero return for not-exists vs exists condition.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] if exist

[Jean-Christophe Deschamps]

>Andrea Galeazzi wrote:
> > I've got a table T made up of only two fields: INT id (PRIMARY KEY) and
> > INT length.
> > I need a statement in order to yield 0 when the key doesn't exist.
>
>Well, "select 0;" fits your spec (you never said what should be 
>returned when the key does exist). I would hazard a guess that you 
>meant something like this:
>
>select coalesce((select length from T where id=?), 0);

Sorry to ask but isn't the following exactly the same, but even easier 
to read?

select exists(select rowid from T where id=?);




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Column types "safe" changes

[Jean-Christophe Deschamps]

>Why not just
>
> update tbl set col1 = col1;
>
>or perhaps
>
> update tbl set col1 = cast(col1 as text);
>
>I'm not sure the former will actually change anything, but the latter 
>should.

Yes my untold question was merely if simpler col = col way could be 
simply ignored.  You're right about the cast, it should work, thanks.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Column types "safe" changes

[Jean-Christophe Deschamps]

>And, of course, you'll have a table where some rows (old ones) contain 
>integers and others (new ones) contain text. SQLite is fine with this, 
>but your client software might not be prepared to deal with it. Some 
>comparisons might behave in surprising ways.

I imagine that in such case, the best bet is to run a "refresh all" 
procedure, updating each row identically, just to have the new affinity 
take effect.


Beside the obvious update with every changed column mentionned,

 update tbl set col1 = (select col1 from tbl y where y.rowid = 
tbl.rowid),
...
coln = (select coln from tbl y where y.rowid = 
tbl.rowid);

do you think of a easier way?



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feasability of a Range function

[Jean-Christophe Deschamps]

Hi Alexey,


>1. See internal sqlite instarray interface:
>http://sqlite.mobigroup.ru/src/finfo?name=src/test_intarray.c
>http://sqlite.mobigroup.ru/src/finfo?name=src/test_intarray.h
>http://sqlite.mobigroup.ru/src/finfo?name=test/intarray.test
>
>Note: http://sqlite.mobigroup.ru include official SQLite trunk
>branch but anonymous autorization is not required.
>
>2. See my Tablefunc extension here:
>http://sqlite.mobigroup.ru/src/dir?name=ext/tablefunc

Warm thanks to the pointer.  That will prove helpful for other purpose 
as well (testing, as I believe this is why you wrote it).

Cheers,



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feasability of a Range function

[Jean-Christophe Deschamps]

>Wouldn't it make more sense for "i in 1..5" to expand to "i >= 1 and i 
><= 5"?
>
>Then it would also work for ordered types that aren't ordinal, such as 
>rationals
>and strings and blobs and dates etc, and it would work for very large 
>ranges,
>since there's no conceptual need to generate all the individual values.
>
>Of course, you'd want to support all 4 variants: 1..5, 1..^5, 1^..5, 
>1^..^5,
>where a ^ means exclude that endpoint and its absence means include.
>
>This is more flexible than SQL's BETWEEN, which I believe only covers 
>one of
>those 4 options.

That's getting into generic interval support.  I find this interesting 
even if its really much more ambitious than my simple-minded (and 
highly optional) initial need/question.

I might dig further in this direction someday.  Good point.

Thanks Darren.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to execute an ATTACH DATABASE?

[Jean-Christophe Deschamps]

>ATTACH DATABASE ?1 as sysDB

AFAIK you can't use parameter binding for anything else than litteral 
values.

It makes sense since it would be impossible for the parser and 
optimizer to evaluate and produce run-time code for a statement without 
knowing beforehand which database or column the statement actually 
refers to. 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feasability of a Range function

[Jean-Christophe Deschamps]

>Ah. You want table-valued functions, like this:
>
>http://msdn.microsoft.com/en-us/library/ms191165.aspx

Thanks Igor, that's what I had in mind.


>In any case, SQLite doesn't support table-valued functions. The 
>closest thing to it is a virtual table:

OK, got it, but this is a bit of heavy engine for such a marginal use.
I'll do the simple way for now.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feasability of a Range function

[Jean-Christophe Deschamps]

>Why not just
>
>select some_scalar_function(i) where i between 1 and 5;

That's because we then get
No such column: i.

That was not very important.  I would have the use for such possibility 
but I can live without.  My question was just curiosity about whether 
something along the line could work, without any materialized table 
holding successive integers in the needed range.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Feasability of a Range function

[Jean-Christophe Deschamps]

I'm trying to determine if a Range(from, to) function can be made as an 
extension function.

Its effect would be to expand, for instance, range(1, 5) into (1, 2, 3, 
4, 5) for use in constructs similar to
  select some_scalar_function(i) where i in range(1, 5);
without having to build a table holding integers 1..5

I don't see how that's possible, but maybe someone with better internal 
knowledge can advise.

Thank you.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] why is underscore like dash?

[Jean-Christophe Deschamps]

>sqlite> select * from test where text like '_';

Underscore '_' is LIKE wildcard for any single character, percent '%' 
matches any substring.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

[Jean-Christophe Deschamps]

>I haven't been able to think of how it would preclude using the index,
>but I suspect it's more a matter of needing a similar-but-different
>codepath to optimize for the NOT case, rather than a simple "invert
>this" codepath relying on the existing case.  Which is really just
>another way of stating Jay's point, perhaps.

I understand.  Perhaps I'm biaised by using functional languages where 
such operators as NOT in our case are applied as deep as possible in 
the process of any evaluation.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

[Jean-Christophe Deschamps]

>NULL = 12345 is NULL, NOT NULL is NULL, so subset N is not part of NOT
>(col = 12345).

You're right of course!  (and I was even saying about nulls treated apart)

But, in your view, that the set can be non-contiguous for 
negative/negated conditions would it explain that current code can't 
make use of index?



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

[Jean-Christophe Deschamps]

>   So indexes are not used for NOT conditions, as NOT conditions
>   generally require a full scan, regardless.  Yes, it is a simple
>   reverse of a binary test, but the reverse of a specific indexed
>   lookup of a known value is a table scan to gather all the unknown
>   values.

Jay,

I understand how your argument goes, but I still have difficulty buying 
it.  Why not negate the condition itself and then only proceed?

Under any condition K, a table T is the union of the subset X of 
elements that match the condition K and the subset Y of elements that 
don't.  In other words, Y is the subset whose elements match [not 
K].  In SQL there is of course the special case for nulls.

You're saying that selecting all rows in T that match K is OK for 
indexing, while selecting rows in T which match the negated condition 
K' = not K can only be done with a full scan.

That is true iff the complementation is done afterwards or on the fly 
(full scan).  My point is that NOT seems to be acting as a 
complementation operator: select X then full scan and check every row 
if it belongs to X or not.

I'd rather see the negated condition K' (= not K) as the condition used 
for indexed selection, again when using the 'complementary' condition 
makes sense.

Pavel modified simple example works well here:

condition  " ... >  3" OK for index search
condition  " ... <= 3" OK for index search
condition  "NOT  ... >  3" only full scan: why?

I still believe that it's possible that many simple conditions be 
negated and used negated for index search.

Now, that it would easy to implement in the current SQLite code is 
another matter entirely and I never pretended it could be done within 
minutes.

The actual reason for the way NOT works as for now may be due to the 
fact that negating a condition may cause the resulting set to be in 
fact itself the union of two subsets.
Say the "where" condition K is "col = 12345".  We can see the index 
split into not less than four subsets:
   o) N = {rows with col is null}
   o) X = {rows with col < 12345}
   o) Y = {rows with col = 12345}
   o) Z = {rows with col > 12345}

For the "positive" condition K, the resulset is Y.
For the negated condition K' (col <> 12345) the resulset is N u X u Z, 
made of two (or three ?) distinct blocks of indexed rowids.  A possible 
explanation is that the current code may be unable to cope with 
situations where the resultset is not index-wise contiguous.


The OP request can be written in a number of ways to make use of the 
index, even:
select count(*) from mybigtable where mytextcolumn < '' or 
mytextcolumn >= '';
so the question is not if SQLite can process equivalent count/querries 
efficiently or not.  I nonetheless think "is not null" is much clearer 
than the above workaround. 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

[Jean-Christophe Deschamps]

>maybe NOT is implemented the same way as any other
>function and so it cannot be optimized using index.

That's possible, but other logical operators don't exhibit the same 
bahavior and will not prevent the use of indexes.  That NOT is not 
being handled at the same _logical_ level than AND and OR is a bit 
disappointing.


>Better example will be "NOT int_val < 3" versus "int_val >= 3".

You're right, that just got "out of the keyboard" by itself ;-)


>No, I didn't mean that. Apparently we have different understanding of
>words "full values". :)

OK then I follow you, but that only needs loading / searching some 
O(log N) pages from the b-tree, which hold actual column entries (full 
values).





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

[Jean-Christophe Deschamps]

>I totally disagree with you. Let's say you have 1,000,000 rows and 100
>of them contain NULL. In this situation selecting NOT NULL will select
>almost all rows which means that using index in this case doesn't give
>any performance boost. So here using full scan for NOT NULL condition
>is better and for NULL condition using index is better. Everything is
>completely legitimate.

The number of rows concerned either way is not the point.  Also the 
NULL condition could be almost anything else.  Situations where the 1M 
and 100 figures are reversed would invalidate your point, so?

select * from T where col1 like 'abc%' and col2 between 3 and 18;
uses the index on T

select * from T where col1 NOT like 'abc%' and col2 between 1 and 24;
doesn't, due to the use of NOT.

That in some particular situation using an index could be better or 
worse is again not the point.  The point was that NOT  is 
simply reversing the issue of a binary test, in fine, and that seems 
essentially independant of the use of an index for determining .


>BTW, when SQLite uses index on "text" field it needs to load full
>values of "text" field anyway.

Do you mean that
 select * from T where rowid = 1;
needs loading the _entire_ index when T has rowids in 1..1000 ?



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

[Jean-Christophe Deschamps]

> > "SELECT count(*) WHERE NOT text IS NULL"
> >
> > requires that the complete text column is loaded. With a stored LOB
> > this results in crazy performance.
>
>How did you find that? What do you mean by "requires loading of the
>whole text column"? It pretty much can require even loading of text
>columns that shouldn't be counted at all just because one database
>page is the minimum storage entity loaded from disk.

Hi Pavel,

I believe the OP was intrigued/upset by the fact that

 SELECT count(*) WHERE NOT text IS NULL;
or (equivalent)
 SELECT count(*) WHERE text IS NOT NULL

does not use an index in the text column, while

 SELECT count(*) WHERE text IS NULL

does use the index.

I've shown a (trivial) way to achieve the same 'not null' count using 
the index.

Anyway, it seems the OP has a point in saying that it would be nice 
--and I would say 'natural'-- to have the optimizer enhanced to handle 
"NOT " as efficiently as it handles ", provided 
such enhancement can be done with only little changes.

The optimizer is smart enough to handle multiple conditions connected 
by AND and OR and use index for every condition (when they are 
available, of course), but it reverts to full scan for any NOT 
, whatever condition is (simple or complex).

I'm certainly not in a position to dictate how should the optimizer 
should evolve but, as a mere user, I feel that situation a little less 
than satisfactory. 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

[Jean-Christophe Deschamps]

>It is driving me crazy. I'm working on a web spider where a table 
>holds the downloaded
>webpage. It seems that a select
>
>"SELECT count(*) WHERE NOT text IS NULL"
>
>requires that the complete text column is loaded. With a stored LOB
>this results in crazy performance.
>
>Is this optimized in later versions of SQLite (i'm using a 3.4.x which is
>about 2 years old).


select (select count(*) from mybigtable) - (select count(*) from 
mybigtable where mytextcolumn is null) as "Non-NULL record count";


This uses only indexes, but explain query plan gives no answer on the 
first select.  Anyway, workaround like this to check that the second 
part actually uses your index (Adapt to minimum rowid you're using if 
ever you force negative rowids in).

explain query plan select (select count(*) from mybigtable where 
rowid > 0) - (select count(*) from mybigtable where mytextcolumn is 
null) as "Non-NULL record count";
TABLE mybigtable USING PRIMARY KEY
TABLE mybigtable USING idxMyTextColumn


BTW, what is "irreal" in SQLite?  Its cost or its support?



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Diagrams and ROLLBACK TO

[Jean-Christophe Deschamps]

Forget the previous post as it's probably wrong.  I was misinterpreting 
the ROLLBACK TO discussion.  In fact I was led to believe there were 
two forms: "rollback to" and "rollback to ".  This is not 
the case.

But then, I still have hard time understanding this part:

"Instead of cancelling the transaction, the ROLLBACK TO command 
restarts the transaction again at the beginning. All intervening 
SAVEPOINTs are cancelled, however."

Say we have the following events:

begin
insert
update
savepoint A
update
savepoint B
insert
delete
savepoint C
update
rollback to B

I would expect the last statement to:
cancel the whole transaction  (contrary to what's said)
then re-apply changes:

begin
insert
update
savepoint A ???
update

Is savepoint A considered an "intervening" savepoint?



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Diagrams and ROLLBACK TO

[Jean-Christophe Deschamps]

The diagrams in the following pages of the documentation don't allow 
for the "ROLLBACK TO" construct: lang_savepoint.html and 
lang_transaction.html

lang_savepoint.html also contains a 'tranaction' typo.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nesting Read/Write within Transaction?

[Jean-Christophe Deschamps]

>It doesn't matter if you are in a transaction or not, changing
>rows while inside a fetch loop on the same table may lead
>to problems.

Sorry I was talking of another process doing asynchronous writes.  I 
didn't understand the warning was towards a single rogue process.

Of course letting the left hand cut down with scisors the letter that 
the right hand is writing denotes a serious problem with the brain, not 
with the paper.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nesting Read/Write within Transaction?

[Jean-Christophe Deschamps]

>The only thing that can bite you is if
>you are in process of fetching rows from some select statement and you
>update row that was just fetched or update/insert rows that would have
>been fetched later by the select statement.

As I understand it, simply wrapping every batch operation (Read, Write 
or Read_Modify_Write) inside an IMMEDIATE transaction is all that's 
needed to prevent such bad things from happening, or am I misguided?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-process read_uncommited equivalent?

[Jean-Christophe Deschamps]

Be aware that the backup process will need to restart from zero after 
each write!

Now, if you can setup some kind of IPC between your two processes, then 
you could have the update process update the disk base and send 
identical data to the reader process, so the latter can update a memory 
copy.  The two processe can run as fast as possible without much 
interference, as memory updates are very fast.  The 50Mb is reasonable 
size for memory and would need loading only once at startup.

For this to work, you have to take steps to insert identical fields 
including rowids, no randomblob or local timestamps.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Shocked by long updates of the text database, 1000 speed improvement after small modification to the code.

[Jean-Christophe Deschamps]

>I will try to do something to that extent using timer and character 
>counter.
>I hoped that I could update the text stored in the database character 
>by character as fast as  they come from the keyboard driver. 
>Unfortunately updates noticeably slow down the display of typed 
>characters.

You can probably use a memory database to store on the fly data with 
low overhead (the update time being very short) and simply update your 
main disk-based base from time to time.

If your application can't afford loosing key hit, even in the event of 
power loss, you can still have a regular OS flat file to record them, 
in parallel or not with your memory base.  But you may still loose some 
at power loss...  (What the heck?)

As the saying goes: at least with 360 systems, memory was memory.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Searching for a LIKE '[0-9]%' equivalence in sqlite

[Jean-Christophe Deschamps]

>To to get columns that begin with a number range or any number, I 
>would use
>in SQL server:
>
>SELECT * FROM mytable WHERE myfield LIKE '[0-9]%'
>
>This obviously doesn't work in sqlite and I have been searching for an
>equivalence.

Try this:

SELECT * FROM mytable WHERE myfield glob '[0-9]*'



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users