Re: [sqlite] Query problem
On Wed, Jan 23, 2008 at 3:23 PM, James Dennett <[EMAIL PROTECTED]> wrote: > The POSIX/Single Unix Spec documentation for fnmatch might be a good > source, but I agree with the idea that SQLite should just document what > it does rather than assuming that there's a universal standard for > globbing. Seems to me that GLOB is a poor substitute for REGEXP. At the shell level, I think an argument can be made that it is more concise, but I don't think that really holds for this use. Rather than extending GLOB towards a full REGEXP implementation, maybe it would be more reasonable to find (or write) a REGEXP implementation which was slight enough to be bundled into the SQLite core? No, I'm not volunteering :-). I think it would be pretty reasonable for SQLite to strictly match the TCL glob command's operation, for obvious reasons. I'd probably miss not being able to say [^x], but, *shrug*, what are you going to do? I could also see matching the glob provided by some other popular scripting language which might have SQLite embedded, say Python or Perl. -scott - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Foreign Constraint Triggers Across Attached Databases
I have been trying to implement the paradigm of using Triggers to emulate referential integrity, for example cascading updates and deletes between two database tables. This works when the two database tables are in “main” but when I try to create the triggers between database tables in attached database tables, the create doesn’t work. Tried several iterations and couldn’t come up with the proper SQL syntax to do this. Is there a way to do this, add referential integrity triggers with database tables in attached databases? Even better, if/when is SQLite going to support built-in referential integrity using foreign key constraints in the SQL when creating the tables? Can it support referential integrity with attached database tables? Example use case: “main” containing an “AccountTable”, and a daily transactional table “-MM-DD.db3” that will be attached to “main” that contains a TransactionTable that has a “foreign key relation” to the “AccountTable” by having an account primary key as a foreign key in the transaction table. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite omit using index
"Alexander Batyrshin" <[EMAIL PROTECTED]> wrote:
> Hello All,
> I've found that SQLite-3.5.4 doesnt use index in this situation:
>
> sqlite> create table t1 (id int primary key, val int);
> sqlite> create table t2 (id unique, val int primary key);
> sqlite> explain query plan update t1 set val = (select t2.val from t2
> where t1.id = t2.id);
> 0|0|TABLE t1
> 0|0|TABLE t2
>
> In this case, SQLite should takes value from t2 via unique id INDEX,
> but it doesn't
>
The t2.id field has no datatype specified. That means it has
an affinity of NONE. (See http://www.sqlite.org/datatypes3.html
paragraph 2.1 bullet 3.) That means that if you insert a string
into t2.id it goes in as a string:
INSERT INTO t2(id) VALUES('123');
SELECT typeof(id) FROM t2 WHERE rowid=last_insert_rowid();
--> answer "text"
Or if you insert an integer, it goes in as an integer:
INSERT INTO t2(id) VALUES(123);
SELECT typeof(id) FROM t2 WHERE rowid=last_insert_rowid();
--> answer "integer"
But the t1.id column to which you are comparing t2.id has
an affinity of INTEGER. (paragraph 2.1 bullet 1.) That means
if you insert a string it is converted into an integer if it
looks like an integer.
INSERT INTO t1(id) VALUES('123');
SELECT typeof(id) FROM t1 WHERE rowid=last_insert_rowid();
--> answer "integer"
Now, the index on t2(id) also uses NO-affinity because the
affinity of the column is NONE. So the index stores separate
entries in separate places for '123' and 123. But the value
you are comparing against is always an integer, because it is
coming out of t1.id which has integer affinity. So if you
look up the entry using just the integer value 123, you will
miss the '123' entry. That is unacceptable. Hence, you cannot
use a value with INTEGER-affinity as the key to an index
with NO-affinity.
Hence the index on t2.id cannot be used to speed the search.
You can get the index to work by saying:
create table t1(id int primary key, val int);
create table t2(id INT unique, val int primary key);
Note the added INT in the definition of t2.id, thus
giving it integer affinity. You'll still be able to store
text in t2.id if you want to, but if that text looks like
an integer, it is converted into an integer.
Please also not that INT PRIMARY KEY is not the same
thing as INTEGER PRIMARY KEY. You probably want
to use INTEGER PRIMARY KEY in this context, not what
you have - but that is a whole other issue.
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] order by issue?
Ken wrote: I'm not sure if this a bug or if working as intended: the order by b,a seems to cause B to use asc.. Instead of the desc as written in the order by statement. create table tst (a integer, b integer ); sqlite> insert into tst values(1,1); sqlite> insert into tst values(1,2); sqlite> insert into tst values(1,3); sqlite> insert into tst values(2,3); sqlite> insert into tst values(2,2); sqlite> insert into tst values(2,1); sqlite> select * from tst order by b, a desc; a|b 2|1 1|1 2|2 1|2 2|3 1|3 But that's not what you wrote... You told it to order a desc, and b by the default (which is asc). Which is exactly what the output shows. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] order by issue?
Ken wrote: the order by b,a seems to cause B to use asc.. Instead of the desc as written in the order by statement. Ken, You would need to say order by b desc, a desc to get the behaviour you expect. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] order by issue?
Unless I don't know SQL, the results look correct. Anyone chime in to correct me. Your query: select * from tst order by b, a desc; Is really: select * from tst order by b ASC, a desc; So, if you look at the rows from top to bottom, you'll see that for each value of 'b' in ascending order, you have the 'a' values in descending order. Lee ___ -Original Message- From: Ken [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 23, 2008 5:10 PM To: sqlite Subject: [sqlite] order by issue? I'm not sure if this a bug or if working as intended: the order by b,a seems to cause B to use asc.. Instead of the desc as written in the order by statement. create table tst (a integer, b integer ); sqlite> insert into tst values(1,1); sqlite> insert into tst values(1,2); sqlite> insert into tst values(1,3); sqlite> insert into tst values(2,3); sqlite> insert into tst values(2,2); sqlite> insert into tst values(2,1); sqlite> select * from tst order by b, a desc; a|b 2|1 1|1 2|2 1|2 2|3 1|3 - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] order by issue?
> -Original Message- > From: Ken [mailto:[EMAIL PROTECTED] > Sent: Wednesday, January 23, 2008 4:10 PM > To: sqlite > Subject: [sqlite] order by issue? > > I'm not sure if this a bug or if working as intended: > > the order by b,a seems to cause B to use asc.. Instead of the desc as > written in the order by statement. > > create table tst (a integer, b integer ); > sqlite> insert into tst values(1,1); > sqlite> insert into tst values(1,2); > sqlite> insert into tst values(1,3); > sqlite> insert into tst values(2,3); > sqlite> insert into tst values(2,2); > sqlite> insert into tst values(2,1); > sqlite> select * from tst order by b, a desc; > a|b > 2|1 > 1|1 > 2|2 > 1|2 > 2|3 > 1|3 Could be that I'm too tired again, but that looks to me like you asked for order primarily by b (with the default, ascending, order) and secondarily by a (with inverted/descending order). select * from tst order by b desc, a desc; might be what you were wanting? -- James - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Query problem
James Dennett wrote: The POSIX/Single Unix Spec documentation for fnmatch might be a good source, but I agree with the idea that SQLite should just document what it does rather than assuming that there's a universal standard for globbing. I think the most direct documentation I have found so far is from the Linux glob man page at http://www.kernel.org/doc/man-pages/online/pages/man7/glob.7.html Linux uses ! for inversion only (so SQLite would still have to add that and continue to support the ^ inversion character for backwards compatability) and a backslash to escape the special characters where needed. It also specifically states that the string between the square brackets can't be empty, so an error should be warranted in this case. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] order by issue?
I'm not sure if this a bug or if working as intended: the order by b,a seems to cause B to use asc.. Instead of the desc as written in the order by statement. create table tst (a integer, b integer ); sqlite> insert into tst values(1,1); sqlite> insert into tst values(1,2); sqlite> insert into tst values(1,3); sqlite> insert into tst values(2,3); sqlite> insert into tst values(2,2); sqlite> insert into tst values(2,1); sqlite> select * from tst order by b, a desc; a|b 2|1 1|1 2|2 1|2 2|3 1|3
[sqlite] sqlite 3.5.2 for 32 bits and 64 bits
Hi All, I already had the sqlite library for 32 bits as libsqlite3.so.0.86 and now I want to build the sqlite library for 64bits for 64bits machine. Can someone help me with the information how to build the sqlite library which is used for 64bits machine. Thanks in advance for your help. JP Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping
RE: [sqlite] Query problem
> -Original Message- > From: Dennis Cote [mailto:[EMAIL PROTECTED] > Sent: Wednesday, January 23, 2008 3:08 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Query problem > > James Dennett wrote: > > > > Square brackets don't "escape" thing that way: [[] is a character class > > containing only the character '['. [][], however, is a character class > > containing two characters. The special rule is that the first character > > after the opening '[' is part of the class even if it's a ']' or a '-'. > > > > > James, > > I don't think it is that simple. > > What happens if, as in the OP, the character set is simply []? Is this > an empty character set, or is a set containing a ] but missing the > terminal ]? That's not a valid specification of a character class. > According to your special rule above, it would be the latter, in which > case it should generate some kind of error message reporting the > unterminated character set. That would be appropriate; the alternative (which bash appears to use) is to take it as two literal characters. > If you say it is supposed to be greedy and include all characters it can > until the terminal ] before examining the set of characters it contains, > then this would be an empty character set. No, because there *is* no terminal ]. (The first character after the opening '[' is *never* the end of the character class: that's exactly the special rule.) > What does an empty set match? Nothing, but you'd have to specify exclusion of every character. > Normally, a set matches any of the contained characters, but an empty > set can't match any character, so any pattern containing the empty set > would always fail. So, is an empty set a special case, that matches the > literal characters [] instead? That's not an empty character class; it's not a character class at all. The POSIX/Single Unix Spec documentation for fnmatch might be a good source, but I agree with the idea that SQLite should just document what it does rather than assuming that there's a universal standard for globbing. -- James - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Query problem
James Dennett wrote: Square brackets don't "escape" thing that way: [[] is a character class containing only the character '['. [][], however, is a character class containing two characters. The special rule is that the first character after the opening '[' is part of the class even if it's a ']' or a '-'. James, I don't think it is that simple. What happens if, as in the OP, the character set is simply []? Is this an empty character set, or is a set containing a ] but missing the terminal ]? According to your special rule above, it would be the latter, in which case it should generate some kind of error message reporting the unterminated character set. If you say it is supposed to be greedy and include all characters it can until the terminal ] before examining the set of characters it contains, then this would be an empty character set. What does an empty set match? Normally, a set matches any of the contained characters, but an empty set can't match any character, so any pattern containing the empty set would always fail. So, is an empty set a special case, that matches the literal characters [] instead? None of this is really clear and unambiguous from any of the documentation I have seen so far. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance degradation after 3.3.17 -> 3.5.4 upgrade
"Alexander Batyrshin" <[EMAIL PROTECTED]> wrote: > On Jan 21, 2008 12:58 AM, Alexander Batyrshin <[EMAIL PROTECTED]> wrote: > > On Jan 20, 2008 11:32 PM, <[EMAIL PROTECTED]> wrote: > > > "Alexander Batyrshin" <[EMAIL PROTECTED]> wrote: > > > > Hello everyone. > > > > I've discover performance degradation due to update 3.3.17 -> 3.5.4. > > > > This SQL query work very slowly: > > > > > > > > DELETE FROM > > > > population_stamp > > > > WHERE > > > > town_id IN ( > > > > SELECT DISTINCT town_id FROM population_stamp > > > > EXCEPT > > > > SELECT id FROM town > > > > ); > > > > > > > > > > I'll bet it will go a lot faster in both 3.3.17 and 3.5.4 if > > > you omit the DISTINCT operator. EXCEPT implies DISTINCT anyway, > > > so you are not losing anything. But SQLite does not optimize > > > out redundant DISTINCTs so it is computing the DISTINCT twice. > > > > It's faster only for 3.5.4, but still slowly for 3.3.17 (time in > > seconds: us - user, sy - system) > > 3.5.4 +DISTINCT: 5.474us 0.287sy > > 3.5.4 -DISTINCT: 3.397us 0.259sy > > 3.3.17 -DISTINCT: 4.129us 0.228sy > > 3.3.17 +DISTINCT: 2.959us 0.180sy > > > > These is timing example for my small testing database. With real > > database difference will be more visible. > > D. Richard Hipp, do you have any ideas why 3.3.17 version with > DISTINCT works faster that new 3.5.4 without DISTINCT? In my test case > difference is greater that 0.5 second. > I have not researched it. Somewhere along the line, somebody sent me a patch that caused DISTINCT to be converted into a GROUP BY and processed that way. The claim was this made the processing faster in most cases. Perhaps you have discovered the case where it actually slows things down. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Query problem
[EMAIL PROTECTED] wrote:
Experiments using bash indicate that either ^ or ! is accepted
as the negation of a character set. Hence,
ls -d [^tu]*
ls -d [!tu]*
both return the same thing - a list of all files and directories
in the current directory whose names do not begin with "t" or "u".
SQLite only supports ^, not !. I wonder if this is something I
should change? It would not be much trouble to get GLOB to support
both, must like the globber in bash.
Anybody have an old Bourne shell around? An authentic C-shell?
What do they do?
Richard,
I found the following info in a Jedit appendix.
| |
*
|?| matches any one character
*
|*| matches any number of characters
*
|{!/|glob|/}| Matches anything that does /not/ match /|glob|/
*
|{/|a|/,/|b|/,/|c|/}| matches any one of /|a|/, /|b|/ or /|c|/
*
|[/|abc|/]| matches any character in the set /|a|/, /|b|/ or /|c|/
*
|[^/|abc|/]| matches any character not in the set /|a|/, /|b|/
or /|c|/
*
|[/|a-z|/]| matches any character in the range /|a|/ to /|z|/,
inclusive. A leading or trailing dash will be interpreted literally
I noticed that SQLite doesn't implement any of the curly brace grouping
of globs. It also shows the use of ^ for inversion with a character set,
and ! for inversion of a complete glob.
The following is from the TCL documentation:
The /pattern/ arguments may contain any of the following special
characters:
*?*
Matches any single character.
***
Matches any sequence of zero or more characters.
*[*/chars/*]*
Matches any single character in /chars/. If /chars/ contains a
sequence of the form /a/*-*/b/ then any character between /a/ and
/b/ (inclusive) will match.
*\*/x/
Matches the character /x/.
*{*/a/*,*/b/*,*/.../}
Matches any of the strings /a/, /b/, etc.
This doesn't mention inversion at all, but it does say a backslash can
be used to escape a character.
And the following is from a the documentation of a glob compiler class.
* *** - Matches zero or more instances of any character. If the
STAR_CANNOT_MATCH_NULL_MASK option is used, *** matches one or
more instances of any character.
* *?* - Matches one instance of any character. If the
QUESTION_MATCHES_ZERO_OR_ONE_MASK option is used, *?* matches
zero or one instances of any character.
* *[...]* - Matches any of characters enclosed by the brackets. *
* * and *?* lose their special meanings within a character
class. Additionaly if the first character following the opening
bracket is a *!* or a *^*, then any character not in the
character class is matched. A *-* between two characters can be
used to denote a range. A *-* at the beginning or end of the
character class matches itself rather than referring to a range.
A *]* immediately following the opening *[* matches itself
rather than indicating the end of the character class, otherwise
it must be escaped with a backslash to refer to itself.
* *\* - A backslash matches itself in most situations. But when a
special character such as a *** follows it, a backslash /
escapes / the character, indicating that the special chracter
should be interpreted as a normal character instead of its
special meaning.
* All other characters match themselves.
This class explicitly mentions using either ^ or ! to invert a character
set. It also allows backslash escapes for special characters. It says *
and ? loose their special status in a character set, so it isn't really
an escape.
The following is from the Apple's documentation
*?* Matches any single character.
*** Matches any sequence of zero or more characters.
*[*_chars_*]* Matches any single character in _chars_. If _chars_
contains a
sequence of the form _a_*-*_b_ then any character between
_a_ and _b_
(inclusive) will match.
*\*_x_Matches the character _x_.
*{*_a_*,*_b_*,*_..._} Matches any of the strings _a_, _b_, etc.
And finally, from the GNU bash documentation:
3.5.8.1 Pattern Matching
Any character that appears in a pattern, other than the special
pattern characters described below, matches itself. The nul character
may not occur in a pattern. A backslash escapes the following
character; the escaping backslash is discarded when matching. The
special pattern characters must be quoted if they are to be matched
literally.
The special pattern characters have the following meanings:
|*|
Matches any string, including the null string.
|?|
Matches any single character.
|[...]|
Matches any one of the enclosed characters. A pair of characters
separated by a hyphen denotes a range expression; any character
that sorts between those two characters, inclusive, using the
current
RE: [sqlite] Query problem
> -Original Message- > From: Dennis Cote [mailto:[EMAIL PROTECTED] > Sent: Wednesday, January 23, 2008 2:22 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Query problem > > [EMAIL PROTECTED] wrote: > > > > You > > can escape characters using [..]. To match a * anywhere in a string, > > for example: > > > > x GLOB '*[*]*' > > > > The [..] pattern must contain at least one internal character. So > > to match a "]" you can use the pattern > > > > x GLOB '*[]]*' > > > So to match the OP's original string he would need to use > '*1[[][]]1.txt' as his pattern? > > With each of the square brackets to be matched escaped by a pair of > enclosing square brackets. Square brackets don't "escape" thing that way: [[] is a character class containing only the character '['. [][], however, is a character class containing two characters. The special rule is that the first character after the opening '[' is part of the class even if it's a ']' or a '-'. -- James - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Query problem
[EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: This appears to be slightly different than normal *nix globbing since SQLite uses '^' rather than '!' for the set inversion (if my reading of the source is correct). GLOB is suppose to exactly mimic Unix, except that SQLite does not break pattern matching at / boundaries the way the shell does. So if the previous statement is true, it is a bug. Experiments using bash indicate that either ^ or ! is accepted as the negation of a character set. Hence, ls -d [^tu]* ls -d [!tu]* both return the same thing - a list of all files and directories in the current directory whose names do not begin with "t" or "u". SQLite only supports ^, not !. I wonder if this is something I should change? It would not be much trouble to get GLOB to support both, must like the globber in bash. Anybody have an old Bourne shell around? An authentic C-shell? What do they do? -- D. Richard Hipp <[EMAIL PROTECTED]> Both Korn and Bourne shells behave differently for the examples but in the same way. ls -d [^tu]* behaves like your example ls -d [!tu]* lists all directories. C Shell does not recognize the 2nd example. First one behaves as above. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to truncate the hour fraction
sqlite> select datetime('1201561222', 'unixepoch');
2008-01-28 23:00:22
OK, so now it's clear your values are Unix times.
sqlite> select strftime('%s', date('1201561222', 'unixepoch'));
1201478400
Effectively strips the time portion of your time value
sqlite> select datetime('1201478400', 'unixepoch');
2008-01-28 00:00:00
Proves the result is what you asked for.
Good luck!
-Clark
- Original Message
From: Joanne Pham <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, January 23, 2008 2:02:09 PM
Subject: Re: [sqlite] How to truncate the hour fraction
Any
help
for
this
question
please!
I
would
like
to
convert
from
t1
to
t2.
and
my
table
is
store
t2.
t1:
201561222
->
2008-01-28
15:00:22
and
I
want
t2
?
->
2008-01-28
00:00:00
Thanks
JP
-
Original
Message
From:
Joanne
Pham
<[EMAIL PROTECTED]>
To:
sqlite-users@sqlite.org
Sent:
Wednesday,
January
23,
2008
10:44:07
AM
Subject:
Re:
[sqlite]
How
to
truncate
the
hour
fraction
Thank
Ken,
But
this
is
not
what
I
want.
I
would
like
to
truncate
the
hour.
So
t1:
201561222
->
2008-01-28
15:00:22
and
I
want
t2
?
->
2008-01-28
00:00:00
So
I
want
to
find
out
t2?
which
has
no
hour
number.
Thanks
JP
-
Original
Message
From:
Ken
<[EMAIL PROTECTED]>
To:
sqlite-users@sqlite.org
Sent:
Wednesday,
January
23,
2008
10:35:03
AM
Subject:
Re:
[sqlite]
How
to
truncate
the
hour
fraction
assuming
your
time
is
in
seconds.
try
t1:
201561222
->
2008-01-28
15:00:22
and
you
want
t2:
1201561200
->
2008-01-28
15:00:00
t1
=
t2
-
(t2
mod
3600)
Joanne
Pham
<[EMAIL PROTECTED]>
wrote:
Hi
All,
I
have
a
hour
table
which
has
the
startTime
are
stored
at
GMT
time.
The
startTime
of
this
hour
table
has
the
values
below:
Hour
table
---
StartTime
equivalent
with
the
time
format
1201561200
->
2008-01-28
15:00:00
120159
->
2008-01-28
23:00:00
120159
->
2008-01-28
23:00:00
120159
->
2008-01-28
23:00:00
I
want
to
aggregate
the
data
from
hour
table
and
store
them
in
weekly
table
and
startTime
should
not
have
any
hour
fraction.
Is
there
any
function
to
truncate
all
the
hour
fraction.
Thanks,
JP
Never
miss
a
thing.
Make
Yahoo
your
home
page.
http://www.yahoo.com/r/hs
Be
a
better
friend,
newshound,
and
know-it-all
with
Yahoo!
Mobile.
Try
it
now.
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
Never
miss
a
thing.
Make
Yahoo
your
home
page.
http://www.yahoo.com/r/hs
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Query problem
[EMAIL PROTECTED] wrote: You can escape characters using [..]. To match a * anywhere in a string, for example: x GLOB '*[*]*' The [..] pattern must contain at least one internal character. So to match a "]" you can use the pattern x GLOB '*[]]*' So to match the OP's original string he would need to use '*1[[][]]1.txt' as his pattern? With each of the square brackets to be matched escaped by a pair of enclosing square brackets. I haven't had much luck finding a detailed spec for the GLOB pattern language. Most descriptions only show the basics. Does anyone know of a good reference? Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to truncate the hour fraction
Joanne Pham wrote: Any help for this question please! I would like to convert from t1 to t2. and my table is store t2. t1: 201561222 -> 2008-01-28 15:00:22 and I want t2 ?-> 2008-01-28 00:00:00 I think your numbers got cutoff... you mean 1201561222 right? Anyway try this: sqlite> SELECT date(1201561222 - (1201561222 % 86400),'unixepoch','localtime'); 2008-01-27 -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance degradation after 3.3.17 -> 3.5.4 upgrade
On Jan 21, 2008 12:58 AM, Alexander Batyrshin <[EMAIL PROTECTED]> wrote: > On Jan 20, 2008 11:32 PM, <[EMAIL PROTECTED]> wrote: > > "Alexander Batyrshin" <[EMAIL PROTECTED]> wrote: > > > Hello everyone. > > > I've discover performance degradation due to update 3.3.17 -> 3.5.4. > > > This SQL query work very slowly: > > > > > > DELETE FROM > > > population_stamp > > > WHERE > > > town_id IN ( > > > SELECT DISTINCT town_id FROM population_stamp > > > EXCEPT > > > SELECT id FROM town > > > ); > > > > > > > I'll bet it will go a lot faster in both 3.3.17 and 3.5.4 if > > you omit the DISTINCT operator. EXCEPT implies DISTINCT anyway, > > so you are not losing anything. But SQLite does not optimize > > out redundant DISTINCTs so it is computing the DISTINCT twice. > > It's faster only for 3.5.4, but still slowly for 3.3.17 (time in > seconds: us - user, sy - system) > 3.5.4 +DISTINCT: 5.474us 0.287sy > 3.5.4 -DISTINCT: 3.397us 0.259sy > 3.3.17 -DISTINCT: 4.129us 0.228sy > 3.3.17 +DISTINCT: 2.959us 0.180sy > > These is timing example for my small testing database. With real > database difference will be more visible. D. Richard Hipp, do you have any ideas why 3.3.17 version with DISTINCT works faster that new 3.5.4 without DISTINCT? In my test case difference is greater that 0.5 second. -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to truncate the hour fraction
Any help for this question please! I would like to convert from t1 to t2. and my table is store t2. t1: 201561222 -> 2008-01-28 15:00:22 and I want t2 ?-> 2008-01-28 00:00:00 Thanks JP - Original Message From: Joanne Pham <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Wednesday, January 23, 2008 10:44:07 AM Subject: Re: [sqlite] How to truncate the hour fraction Thank Ken, But this is not what I want. I would like to truncate the hour. So t1: 201561222 -> 2008-01-28 15:00:22 and I want t2 ?-> 2008-01-28 00:00:00 So I want to find out t2? which has no hour number. Thanks JP - Original Message From: Ken <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Wednesday, January 23, 2008 10:35:03 AM Subject: Re: [sqlite] How to truncate the hour fraction assuming your time is in seconds. try t1: 201561222 -> 2008-01-28 15:00:22 and you want t2: 1201561200 -> 2008-01-28 15:00:00 t1 = t2 - (t2 mod 3600) Joanne Pham <[EMAIL PROTECTED]> wrote: Hi All, I have a hour table which has the startTime are stored at GMT time. The startTime of this hour table has the values below: Hour table --- StartTimeequivalent with the time format 1201561200 -> 2008-01-28 15:00:00 120159 -> 2008-01-28 23:00:00 120159 -> 2008-01-28 23:00:00 120159 -> 2008-01-28 23:00:00 I want to aggregate the data from hour table and store them in weekly table and startTime should not have any hour fraction. Is there any function to truncate all the hour fraction. Thanks, JP Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs
RE: [sqlite] Query problem
DRH wrote: > > Experiments using bash indicate that either ^ or ! is accepted > as the negation of a character set. Hence, > > ls -d [^tu]* > ls -d [!tu]* > > both return the same thing - a list of all files and directories > in the current directory whose names do not begin with "t" or "u". > > SQLite only supports ^, not !. I wonder if this is something I > should change? It would not be much trouble to get GLOB to support > both, must like the globber in bash. > > Anybody have an old Bourne shell around? An authentic C-shell? > What do they do? C shell on Solaris 9 gives an error on echo [!c]* as it considers the !c to be an event specification. Tcsh the same. Ksh treats echo [^c]* the same as echo c* but does "the right thing" with echo [!c]* bash treats the two the same (as all names starting with a character other than lower-case 'c'). -- James - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Query problem
[EMAIL PROTECTED] wrote: > > > This appears to be slightly different than normal *nix globbing since > > SQLite uses '^' rather than '!' for the set inversion (if my reading of > > the source is correct). > > GLOB is suppose to exactly mimic Unix, except that SQLite does not > break pattern matching at / boundaries the way the shell does. > So if the previous statement is true, it is a bug. > Experiments using bash indicate that either ^ or ! is accepted as the negation of a character set. Hence, ls -d [^tu]* ls -d [!tu]* both return the same thing - a list of all files and directories in the current directory whose names do not begin with "t" or "u". SQLite only supports ^, not !. I wonder if this is something I should change? It would not be much trouble to get GLOB to support both, must like the globber in bash. Anybody have an old Bourne shell around? An authentic C-shell? What do they do? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Journal Files
"Mark Riehl" <[EMAIL PROTECTED]> wrote: > I'm working with version 3.5.2 under Linux. I've got a database that > is being shared between two processes and I'm running into issues with > the journal file that doesn't go away. When that happens, one process > appears to have the lock on the database and the other process is > essentially locked out. > > A few questions for you: > > 1. Is there a way to look at the contents of the journal file so that > I know which insert statements are currently queued up? This way, I > can try and work backwards from there and see what the issue is. If you are storing text data, I guess you could use "strings" too see bits and pieces of it. But it won't be in any recognizable order. > > 2. Is there a way to determine the current database state (unlocked, > shared, pending, reserved, exclusive) ? > http://www.sqlite.org/c3ref/c_fcntl_lockstate.html For additional background information one what SQLite does with locking and journal files, please see http://www.sqlite.org/atomiccommit.html http://www.sqlite.org/lockingv3.html -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] .import NULL
On Jan 23, 2008 7:54 AM, Dennis Cote <[EMAIL PROTECTED]> wrote: > Robert Wishlaw wrote: > > When .import parses an empty field in the csv file, that is , a comma > > immediately followed by another comma, is the cell, in the database, > > that corresponds to the empty field a NULL cell? > > > Robert, > > No, it inserts a text field containing an empty string. Thank you Dennis, this is what I needed to know. > > > All the fields inserted by .import are strings. If they are inserted > into columns with numeric affinity they are converted to numeric values > when stored into the database. The column was created "AS REAL". > > > > The reason I ask is that when I query the column with > > > > SELECT COUNT(*) FROM table WHERE temp ISNULL; > > > > 0 is returned but there are 5 empty cells in that column in the csv > file. > > > > > This confirms that you don't have any NULL values in that column. My expected value of 5 is returned from the query SELECT COUNT(*) FROM table WHERE temp = ""; or SELECT COUNT(*) FROM table WHERE temp <> 0 AND NOT temp; > > > > Also when I query > > > > SELECT temp FROM table WHERE temp > 0; > > > > the empty cells are returned as well as the cells containing > > temperatures over 0. > > > > > > > This is because you are comparing a numeric value to a string. It's an > empty string, but a string none the less. All strings are greater than > any numeric value (see section 3 Comparison Expressions at > http://www.sqlite.org/datatype3.html for the details of how SQLite > compares values) so in these rows, the empty string is greater than the > literal numeric value of 0. Thank you again for your concise yet comprehensive explanation of the consequences of using .import on an empty field in a .csv file. Robert Wishlaw > > > HTH > Dennis Cote > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
[sqlite] Journal Files
I'm working with version 3.5.2 under Linux. I've got a database that is being shared between two processes and I'm running into issues with the journal file that doesn't go away. When that happens, one process appears to have the lock on the database and the other process is essentially locked out. A few questions for you: 1. Is there a way to look at the contents of the journal file so that I know which insert statements are currently queued up? This way, I can try and work backwards from there and see what the issue is. 2. Is there a way to determine the current database state (unlocked, shared, pending, reserved, exclusive) ? Thanks, Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to truncate the hour fraction
Joanne Pham wrote:
Thank Ken,
But this is not what I want. I would like to truncate the hour.
So
t1: 201561222 -> 2008-01-28 15:00:22
and I want
t2 ? -> 2008-01-28 00:00:00
So I want to find out t2? which has no hour number.
So you just want the date (i.e. No time at all)? Seems like you have
two options. You can mod against 86400 (number of seconds in a day),
or you can just use the date(DateField) option, to only output the date.
sqlite> SELECT date('2007-01-19 12:54:32');
2007-01-19
sqlite> SELECT date('120159','unixepoch','localtime');
2008-01-28
Lots of good docs here:
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
--
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] How to truncate the hour fraction
Thank Ken, But this is not what I want. I would like to truncate the hour. So t1: 201561222 -> 2008-01-28 15:00:22 and I want t2 ? -> 2008-01-28 00:00:00 So I want to find out t2? which has no hour number. Thanks JP - Original Message From: Ken <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Wednesday, January 23, 2008 10:35:03 AM Subject: Re: [sqlite] How to truncate the hour fraction assuming your time is in seconds. try t1: 201561222 -> 2008-01-28 15:00:22 and you want t2: 1201561200 -> 2008-01-28 15:00:00 t1 = t2 - (t2 mod 3600) Joanne Pham <[EMAIL PROTECTED]> wrote: Hi All, I have a hour table which has the startTime are stored at GMT time. The startTime of this hour table has the values below: Hour table --- StartTimeequivalent with the time format 1201561200 -> 2008-01-28 15:00:00 120159 -> 2008-01-28 23:00:00 120159 -> 2008-01-28 23:00:00 120159 -> 2008-01-28 23:00:00 I want to aggregate the data from hour table and store them in weekly table and startTime should not have any hour fraction. Is there any function to truncate all the hour fraction. Thanks, JP Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
Re: [sqlite] How to truncate the hour fraction
assuming your time is in seconds. try t1: 201561222 -> 2008-01-28 15:00:22 and you want t2: 1201561200 -> 2008-01-28 15:00:00 t1 = t2 - (t2 mod 3600) Joanne Pham <[EMAIL PROTECTED]> wrote: Hi All, I have a hour table which has the startTime are stored at GMT time. The startTime of this hour table has the values below: Hour table --- StartTimeequivalent with the time format 1201561200 -> 2008-01-28 15:00:00 120159 -> 2008-01-28 23:00:00 120159 -> 2008-01-28 23:00:00 120159 -> 2008-01-28 23:00:00 I want to aggregate the data from hour table and store them in weekly table and startTime should not have any hour fraction. Is there any function to truncate all the hour fraction. Thanks, JP Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs
[sqlite] How to truncate the hour fraction
Hi All, I have a hour table which has the startTime are stored at GMT time. The startTime of this hour table has the values below: Hour table --- StartTimeequivalent with the time format 1201561200 -> 2008-01-28 15:00:00 120159 -> 2008-01-28 23:00:00 120159 -> 2008-01-28 23:00:00 120159 -> 2008-01-28 23:00:00 I want to aggregate the data from hour table and store them in weekly table and startTime should not have any hour fraction. Is there any function to truncate all the hour fraction. Thanks, JP Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs
Re: [sqlite] Query problem
Dennis Cote <[EMAIL PROTECTED]> wrote: >> > SQLite seems to do the following: > > The glob syntax supports the following patterns: > ? - matches any single character > * - matches zero or more characters > [seq] - matches any single character in seq > [!seq] - matches any single character not in seq > > seq is one or more characters, such as abc. You may specify character > ranges using a dash. For example, a-z0-9 specifies all of the characters > in the English alphabet and the decimal digits 0 through 9. > > This appears to be slightly different than normal *nix globbing since > SQLite uses '^' rather than '!' for the set inversion (if my reading of > the source is correct). GLOB is suppose to exactly mimic Unix, except that SQLite does not break pattern matching at / boundaries the way the shell does. So if the previous statement is true, it is a bug. > > It is not clear how you should escape these characters if you need to > match them literally. It may not be possible, since these characters are > not allowed in filenames and hence wouldn't need to be matched by *nix > commands. Unix allows *any* characters in filenames except \000 and /. You can escape characters using [..]. To match a * anywhere in a string, for example: x GLOB '*[*]*' The [..] pattern must contain at least one internal character. So to match a "]" you can use the pattern x GLOB '*[]]*' -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] May one software write to the SQLite database while a other read the same SQLite database ?
Thanks for the quick answers. Pierre8r - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: May one software write to the SQLite database while a other read the same SQLite database ?
Pierre8r <[EMAIL PROTECTED]> wrote: One SQLite database on my PC. Two softwares. May one software write to the SQLite database while a other read the same SQLite database ? Not at the exact same time. The database file is essentially protected by multiple-readers-single-writer lock. But the two can take turns. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] May one software write to the SQLite database while a other read the same SQLite database ?
Pierre8r <[EMAIL PROTECTED]> wrote: > Hello, > > One SQLite database on my PC. > Two softwares. > May one software write to the SQLite database while a other read the > same SQLite database ? > Your programs cannot be reading and writing at exactly the same instant in time. But both programs can have the database open for reading and writing. While one program is writing, the other is blocked from reading. But the write normally only takes a few dozen milliseconds. Surely your reader can wait that long. The waiting is handled for you automatically if you set sqlite3_busy_timeout() -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Query problem
Yasir Nisar wrote:
Hi,
Hope you will find this mail in the best of your health.
SELECT * FROM BackupTable,BackupItemTable,BackItUpPathTable WHERE lower(BackItUpPathTable.WinName) GLOB lower("*1[]1.txt*") AND BackupItemTable.BKItemSize > -1 AND BackupTable.BackupNo = BackupItemTable.BackupNo AND BackupItemTable.PathID = BackItUpPathTable.PathID ;
Above mentioned is the query which returns nothing. Problem is with "[" (1[]1.txt). Would you kindly tell me whether "[" is reserved for something or not?
Best Regards,
Yasir Nisar
Yes, the '[' character is used to mark the beginning of a set of
characters to match at that position in the string.
SQLite seems to do the following:
The glob syntax supports the following patterns:
? - matches any single character
* - matches zero or more characters
[seq] - matches any single character in seq
[!seq] - matches any single character not in seq
seq is one or more characters, such as abc. You may specify character
ranges using a dash. For example, a-z0-9 specifies all of the characters
in the English alphabet and the decimal digits 0 through 9.
This appears to be slightly different than normal *nix globbing since
SQLite uses '^' rather than '!' for the set inversion (if my reading of
the source is correct).
It is not clear how you should escape these characters if you need to
match them literally. It may not be possible, since these characters are
not allowed in filenames and hence wouldn't need to be matched by *nix
commands.
You can always trace through the source of the patternCompare function
in SQLite's source file func.c for more details.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] May one software write to the SQLite database while a other read the same SQLite database ?
Hello, One SQLite database on my PC. Two softwares. May one software write to the SQLite database while a other read the same SQLite database ? Thanks, Pierre8r - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] .import NULL
Robert Wishlaw wrote: When .import parses an empty field in the csv file, that is , a comma immediately followed by another comma, is the cell, in the database, that corresponds to the empty field a NULL cell? Robert, No, it inserts a text field containing an empty string. All the fields inserted by .import are strings. If they are inserted into columns with numeric affinity they are converted to numeric values when stored into the database. The reason I ask is that when I query the column with SELECT COUNT(*) FROM table WHERE temp ISNULL; 0 is returned but there are 5 empty cells in that column in the csv file. This confirms that you don't have any NULL values in that column. Also when I query SELECT temp FROM table WHERE temp > 0; the empty cells are returned as well as the cells containing temperatures over 0. This is because you are comparing a numeric value to a string. It's an empty string, but a string none the less. All strings are greater than any numeric value (see section 3 Comparison Expressions at http://www.sqlite.org/datatype3.html for the details of how SQLite compares values) so in these rows, the empty string is greater than the literal numeric value of 0. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Strange error "Incomplete SQL"
Ciao, a tutti! il giorno lunedì 21 gennaio 2008 parlavamo "Re: [sqlite] Strange error "Incomplete SQL"" > Do you really need to use version 2.8.17? it's just the default version present in opensuse, I'm just a noob of sqlite (it's the first time I used it) and I'm just "playing" with the cli interface to know it better... mmm... I know it's a bit OT, but... I need to use a db that allow 2 process (2 differents programs, in effect) to access the same data, one in read only and one in write only. What is the better way to assure that the reading process it's always "up to date"? does it make sense to upgrade to 3.x? (specifically about this problem) -- They that can give up liberty to obtain a little temporary safety deserve neither liberty nor safety. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Query problem
You need single quotes for text literals.
On Jan 23, 2008 6:15 AM, Yasir Nisar <[EMAIL PROTECTED]> wrote:
> SELECT * FROM BackupTable,BackupItemTable,BackItUpPathTable WHERE
> lower(BackItUpPathTable.WinName) GLOB lower("*1[]1.txt*") AND
> BackupItemTable.BKItemSize > -1 AND BackupTable.BackupNo =
> BackupItemTable.BackupNo AND BackupItemTable.PathID =
> BackItUpPathTable.PathID ;
>
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite
Cthulhu Bucks!
http://www.cthulhubucks.com
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Multiple databases
"Mina R Waheeb" <[EMAIL PROTECTED]> wrote: > Hi, >I have few questions regarding the limitation of multiple databases with > SQLite. > > I have a large number of SQLite DB files with the same structure i > need to query them all (looking for speed), I have tried ATTACH method > and its working fine but there is a limitation on the number of > attached files > > in sqlitelimit.h > /* > ** The maximum number of attached databases. This must be at least 2 > ** in order to support the main database file (0) and the file used to > ** hold temporary tables (1). And it must be less than 32 because > ** we use a bitmask of databases with a u32 in places (for example > ** the Parse.cookieMask field). > */ > > - Is there is any way or patch to increase the number of attached > files more than 32? You could change the unsigned 32-bit integer used for the mask into an unsigned 64-bit integer. This will be tricky, though, as a lot of things will need to change. > > - is SQLite open FD to each attached database or open it on request? Actually, 3 file descriptors per attached database. > > - Once the SQLite library loaded in memory, How much the new > connection cost of the system resources mainly in the memory? That depends on the size of the database schema. > > - If the schema is standard (Will not change), Is there is anyway just > switch between the FD to avoid reparse on startup? sorry maybe this is > a stupid question but i dunno much about SQLite internals. > No. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Query problem
Hi,
Hope you will find this mail in the best of your health.
SELECT * FROM BackupTable,BackupItemTable,BackItUpPathTable WHERE
lower(BackItUpPathTable.WinName) GLOB lower("*1[]1.txt*") AND
BackupItemTable.BKItemSize > -1 AND BackupTable.BackupNo =
BackupItemTable.BackupNo AND BackupItemTable.PathID = BackItUpPathTable.PathID ;
Above mentioned is the query which returns nothing. Problem is with "["
(1[]1.txt). Would you kindly tell me whether "[" is reserved for something or
not?
Best Regards,
Yasir Nisar
-
Looking for last minute shopping deals? Find them fast with Yahoo! Search.
