Re: [sqlite] The next release of SQLite....
On Dec 4, 2009, at 7:28 AM, John Brooks wrote: > In the new fts3.html, the statement: > > "each FTS3 table has a 'rowid' column that behaves like an INTEGER > PRIMARY KEY, except that values remain unchanged if the database is > rebuilt using the VACUUM command." > > is incorrect; an INTEGER PRIMARY KEY column *does* remain unchanged > during VACUUM, in contrast to a usual ROWID. It is unclear here if the > FTS3 rowid does or does not change during vacuum. Changed to: ", each FTS3 table has a "rowid" column. The rowid of an FTS3 table behaves in the same way as the rowid column of an ordinary SQLite table, except that the values stored in the rowid column of an FTS3 table remain unchanged if the database is rebuilt using the VACUUM command." Dan. > > - John Brooks > > On Thu, Dec 3, 2009 at 4:15 PM, Nicolas Williams > wrote: >> Use the glob operator. >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The next release of SQLite....
Is somewhere a list of which sites of the documentation has changed? It would be a little difficult to search for differences. with best wishes - Artur - > BTW, this is why I added docid as an alias for rowid for fts3 > (rowid-versus-VACUUM was the initial reason for revving fts2 to fts3). > Saying "rowid is just like rowid in other tables, except different" > seemed more confusing than it was worth. > > Though since I'm not writing the new documentation, I'm more than > happy to be ignored :-). > > -scott > > > On Thu, Dec 3, 2009 at 4:28 PM, John Brooks > wrote: >> In the new fts3.html, the statement: >> >> "each FTS3 table has a 'rowid' column that behaves like an INTEGER >> PRIMARY KEY, except that values remain unchanged if the database is >> rebuilt using the VACUUM command." >> >> is incorrect; an INTEGER PRIMARY KEY column *does* remain unchanged >> during VACUUM, in contrast to a usual ROWID. It is unclear here if the >> FTS3 rowid does or does not change during vacuum. >> >> - John Brooks >> >> On Thu, Dec 3, 2009 at 4:15 PM, Nicolas Williams >> wrote: >>> Use the glob operator. >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > Artur Reilin sqlite.yuedream.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "foreign key mismatch" error
On Dec 4, 2009, at 12:31 PM, Paul Shaffer wrote: > Below are 3 tables. When I delete a row in Item with related rows in > Item_attribute I get the dreaded "foreign key mismatch" error. I've > read > through the documentation and don't see what I'm doing wrong. There are no FK mismatches in the three table definitions here. Maybe the problem is in a different part of the schema. Any other FK constraints reference table [Item]? Any ON DELETE triggers on table [Item]? > sqlite v 1.6.20 > > CREATE TABLE [Item_attribute] ( >"ItemID"integer NOT NULL, >"AttributeID"integer NOT NULL, >PRIMARY KEY ([ItemID], [AttributeID]) > , >FOREIGN KEY ([AttributeID]) >REFERENCES [Attribute]([AttributeID]) ON DELETE CASCADE, >FOREIGN KEY ([ItemID]) >REFERENCES [Item]([ItemID]) ON DELETE CASCADE > ) > > > CREATE TABLE [Item] ( >"ItemID"integer PRIMARY KEY AUTOINCREMENT NOT NULL, >"ItemName"varchar(255) NOT NULL COLLATE NOCASE DEFAULT '' > ) > > CREATE TABLE [Attribute] ( >"AttributeID"integer PRIMARY KEY AUTOINCREMENT NOT NULL, >"Name"varchar(255) NOT NULL COLLATE NOCASE DEFAULT '' > > ) > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] "foreign key mismatch" error
Below are 3 tables. When I delete a row in Item with related rows in Item_attribute I get the dreaded "foreign key mismatch" error. I've read through the documentation and don't see what I'm doing wrong. sqlite v 1.6.20 CREATE TABLE [Item_attribute] ( "ItemID"integer NOT NULL, "AttributeID"integer NOT NULL, PRIMARY KEY ([ItemID], [AttributeID]) , FOREIGN KEY ([AttributeID]) REFERENCES [Attribute]([AttributeID]) ON DELETE CASCADE, FOREIGN KEY ([ItemID]) REFERENCES [Item]([ItemID]) ON DELETE CASCADE ) CREATE TABLE [Item] ( "ItemID"integer PRIMARY KEY AUTOINCREMENT NOT NULL, "ItemName"varchar(255) NOT NULL COLLATE NOCASE DEFAULT '' ) CREATE TABLE [Attribute] ( "AttributeID"integer PRIMARY KEY AUTOINCREMENT NOT NULL, "Name"varchar(255) NOT NULL COLLATE NOCASE DEFAULT '' ) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [bug+patch] Old lemon bug reintroduced
It seems my previous message has not gone through, so this is a repeat
post.
A recent change to the lemon parser generator has reintroduced an old bug.
The old bug report can be found in the debian bug tracking system:
http://bugs.debian.org/233690
The old bug was fixed in CVS checkin 1249:
http://www.sqlite.org/cvstrac/chngview?cn=1249
However, the bug was re-introduced in checkin 27d8e684db:
http://www.sqlite.org/src/info/27d8e684db
Here is a demonstration of the bug with a small grammar, and a patch
that, at least for me now, appears to fix the bug in the same way as
the CVS checkin mentioned above.
$fossil status
repository: /home/vincent/fossil/sqlite
local-root: /home/vincent/src/sqlite/
server-code: 3efcb091a6d5857b29c9fa3385fee9ee4e8f866f
checkout: 2f42f91fe65b0b21671936013df08037091f0cc6 2009-11-20 18:48:36
UTC
parent: cae949ce971ca216e0f8880b2f93866619fa05be 2009-11-20 17:23:13
UTC
tags: trunk
$cat x.y
start ::= S0.
start ::= S1.
start ::= S2.
start ::= n0.
n0 ::= n4 T0.
n4 ::= .
n4 ::= n4 T4.
%include{
#include
#include
#include "x.h"
void *ParseAlloc(void*(*)(size_t));
void ParseTrace(FILE*,char*);
void Parse(void*,int,void*);
void ParseFree(void*,void(*)(void*));
int main(int argc, char *argv[])
{
void *parser = ParseAlloc(&malloc);
ParseTrace(stderr, "x: ");
Parse(parser, T0, NULL);
Parse(parser, 0, NULL);
ParseFree(parser, &free);
return 0;
}
}
$gcc -o tool/lemon tool/lemon.c
$tool/lemon -s x.y
Parser statistics: 6 terminals, 4 nonterminals, 7 rules
8 states, 0 parser table entries, 0 conflicts
$gcc -o x x.c
$./x
x: Input T0
x: Shift 7
x: Stack: T0
x: Input $
x: Reduce [n0 ::= n4 T0].
x: x.c:524: yy_find_reduce_action: Assertion `stateno<=(0)' failed.
Aborted
$cat crash.patch
Index: tool/lemon.c
===
fossil diff /home/vincent/src/sqlite/tool/lemon.c
--- tool/lemon.c
+++ tool/lemon.c
@@ -518,11 +518,11 @@
** offset is found. In the worst case, we fall out of the loop when
** i reaches p->nAction, which means we append the new transaction set.
**
** i is the index in p->aAction[] where p->mnLookahead is inserted.
*/
- for(i=p->nAction-1; i>=0; i--){
+ for(i=p->nAction+p->mnLookahead-1; i>=0; i--){
/* First look for an existing action table entry that can be reused */
if( p->aAction[i].lookahead==p->mnLookahead ){
if( p->aAction[i].action!=p->mnAction ) continue;
for(j=0; jnLookahead; j++){
k = p->aLookahead[j].lookahead - p->mnLookahead + i;
@@ -541,11 +541,11 @@
}
}
}
if( i<0 ){
/* If no reusable entry is found, look for an empty slot */
-for(i=0; inAction; i++){
+for(i=0; inAction+p->mnLookahead; i++){
if( p->aAction[i].lookahead<0 ){
for(j=0; jnLookahead; j++){
k = p->aLookahead[j].lookahead - p->mnLookahead + i;
if( k<0 ) break;
if( p->aAction[k].lookahead>=0 ) break;
$patch -p0 | "If you're flamed in a group you
Re: [sqlite] Enable fts?
2009/11/19 P Kishor > On Wed, Nov 18, 2009 at 4:28 PM, Grzegorz Wierzchowski > wrote: > > Wednesday 18 of November 2009 09:39:27 Trung Nguyen Duc napisał(a): > >> Hi all, > >> How can I enable fts1 or fts2 modules? > >> Do I need any changes in configure.ac or Makefile.in? > >> ___ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > It seems to be driven by SQLITE_ENABLE_FTS1 (...FTS2, ...FTS3) macro > > definitions. > > > > Try ./configure CFLAGS=-DSQLITE_ENABLE_FTS1 > > > > > I haven't checked, but I think the current source tree comes only with > FTS3 which has superseded FTS1 and FTS2 for a while now. Any > particular reason you want to use the older versions of FTS and not > the latest? > > -- > Puneet Kishor > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > Sorry for my belate replying. Thank both of you, I've added macro to enable fts module but only fts3 is enabled successfully, building sqlite with two others is failed. The reason of this fail maybe is the way of building sqlite. I've checked that there are two modes to build sqlite, amalgamation and non-amalgamation. The amalgamation mode will creates an sqlite3.c containing all other sqlite src files(include fts3 src files) exception fts1 and ft2 src files to compile. The non-amalgamation mode compiles src files of sqlite exclusively extension modules including all fts modules. I want to build sqlite3 with older fts because I've seen many test files for both of fts1 and fts2 and just want to run them :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting unique entries from one table with multiplecolumns forming a unique key
Rich Rattanni wrote:
> Awesome! Thank you. Can I ask a follow up question? "from foo,
> bar"... is this behaving like a join?
Yes it does. You can also phrase the same query with an explicit join:
select bar.* from bar join foo
on (foo.col2 = bar.col2 and foo.col3 = bar.col3);
> Second, I came up with my own solution:
>
> select * from foo
> where col1 || col2 in (select col1 || col2 from bar)
Note that, with this condition, a pair ('AB', 'C') would match ('A', 'BC').
> This also works, but from an efficiency standpoint I assume this is
> horrible since it would basically be a series of string compare
> operations. However, if the number of rows in each table is VERY
> LARGE (lets say 50,000) would my solution maybe outperform the first
> (on the surface seems like n^2 vs n*S where S is concat string length
> (which will always be < 50)).
How did you arrive at the conclusion that this last query is better than
O(n^2)? Forget about concatenation: a simple query like
select * from foo where col1 in (select col1 from bar);
is n^2 unless an index can be used.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 is blocked by transaction when wanting to close the sqlite3 *
PS:
I used the signal to close the connection to database. Just like:
int main ()
{
...
signal (SIGUSR2, (void*)sig_handler);
}
void sys_sig_handler(int sig)
{
...
switch(sig)
{
case SIGUSR2:
...
sqlite3_close (db);
...
}
...
}
liubin liu wrote:
>
> I do the job as you say, but the problem is still here.
>
> There are several processes who write the database. And the place is not
> same each time that the problem happens.
>
> How could I know the reason exactly?
>
>
>
> Pavel Ivanov-2 wrote:
>>
>>> Because there are several process who use the database. I have another
>>> question:Could I close the database of other process in main process?
>>
>> Just use your favorite IPC mechanism and write your application so
>> that main process sends message to other process and when other
>> process receives it then it closes its database connection...
>>
>> Pavel
>>
>> On Sun, Nov 29, 2009 at 7:46 PM, liubin liu <7101...@sina.com> wrote:
>>>
>>> Thank you!
>>>
>>> I'm sorry for not showing clearly the environment is embedded linux on
>>> arm
>>> board.
>>>
>>> Because there are several process who use the database. I have another
>>> question:Could I close the database of other process in main process?
>>>
>>>
>>> Nick Shaw-3 wrote:
By "other process" do you mean a separate DLL or similar? You can't
free memory allocated in a DLL from an application, even when that
application has the DLL loaded - Windows will complain. This could be
what's happening.
Could you instead write the database close call within this other
process, and call it from the main process when you shut down?
Nick.
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of liubin liu
Sent: 26 November 2009 02:29
To: sqlite-users@sqlite.org
Subject: [sqlite] sqlite3 is blocked by transaction when wanting to
close the sqlite3 *
My application includes a main process and some other processes. I open
the
database in other process, but at end I will close the database in main
process.
The problem happens while I close the database. The main process is
blocked.
And I could see the journal file is still there, so I guess there are
still
some transactions.
How resolve the problem?
Thanks in advance!
--
View this message in context:
http://old.nabble.com/sqlite3-is-blocked-by-transaction-when-wanting-to-
close-the-sqlite3-*-tp26523551p26523551.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>> --
>>> View this message in context:
>>> http://old.nabble.com/sqlite3-is-blocked-by-transaction-when-closing-the-sqlite3-*-tp26523551p26568098.html
>>> Sent from the SQLite mailing list archive at Nabble.com.
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
>
--
View this message in context:
http://old.nabble.com/sqlite3-is-blocked-by-transaction-when-closing-the-sqlite3-*-tp26523551p26635743.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 is blocked by transaction when wanting to close the sqlite3 *
I do the job as you say, but the problem is still here. There are several processes who write the database. And the place is not same each time that the problem happens. How could I know the reason exactly? Pavel Ivanov-2 wrote: > >> Because there are several process who use the database. I have another >> question:Could I close the database of other process in main process? > > Just use your favorite IPC mechanism and write your application so > that main process sends message to other process and when other > process receives it then it closes its database connection... > > Pavel > > On Sun, Nov 29, 2009 at 7:46 PM, liubin liu <7101...@sina.com> wrote: >> >> Thank you! >> >> I'm sorry for not showing clearly the environment is embedded linux on >> arm >> board. >> >> Because there are several process who use the database. I have another >> question:Could I close the database of other process in main process? >> >> >> Nick Shaw-3 wrote: >>> >>> By "other process" do you mean a separate DLL or similar? You can't >>> free memory allocated in a DLL from an application, even when that >>> application has the DLL loaded - Windows will complain. This could be >>> what's happening. >>> >>> Could you instead write the database close call within this other >>> process, and call it from the main process when you shut down? >>> >>> Nick. >>> >>> -Original Message- >>> From: sqlite-users-boun...@sqlite.org >>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of liubin liu >>> Sent: 26 November 2009 02:29 >>> To: sqlite-users@sqlite.org >>> Subject: [sqlite] sqlite3 is blocked by transaction when wanting to >>> close the sqlite3 * >>> >>> >>> My application includes a main process and some other processes. I open >>> the >>> database in other process, but at end I will close the database in main >>> process. >>> >>> The problem happens while I close the database. The main process is >>> blocked. >>> And I could see the journal file is still there, so I guess there are >>> still >>> some transactions. >>> >>> How resolve the problem? >>> >>> Thanks in advance! >>> -- >>> View this message in context: >>> http://old.nabble.com/sqlite3-is-blocked-by-transaction-when-wanting-to- >>> close-the-sqlite3-*-tp26523551p26523551.html >>> Sent from the SQLite mailing list archive at Nabble.com. >>> >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >> >> -- >> View this message in context: >> http://old.nabble.com/sqlite3-is-blocked-by-transaction-when-closing-the-sqlite3-*-tp26523551p26568098.html >> Sent from the SQLite mailing list archive at Nabble.com. >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/sqlite3-is-blocked-by-transaction-when-closing-the-sqlite3-*-tp26523551p26635742.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The next release of SQLite....
BTW, this is why I added docid as an alias for rowid for fts3 (rowid-versus-VACUUM was the initial reason for revving fts2 to fts3). Saying "rowid is just like rowid in other tables, except different" seemed more confusing than it was worth. Though since I'm not writing the new documentation, I'm more than happy to be ignored :-). -scott On Thu, Dec 3, 2009 at 4:28 PM, John Brooks wrote: > In the new fts3.html, the statement: > > "each FTS3 table has a 'rowid' column that behaves like an INTEGER > PRIMARY KEY, except that values remain unchanged if the database is > rebuilt using the VACUUM command." > > is incorrect; an INTEGER PRIMARY KEY column *does* remain unchanged > during VACUUM, in contrast to a usual ROWID. It is unclear here if the > FTS3 rowid does or does not change during vacuum. > > - John Brooks > > On Thu, Dec 3, 2009 at 4:15 PM, Nicolas Williams > wrote: >> Use the glob operator. >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The next release of SQLite....
In the new fts3.html, the statement: "each FTS3 table has a 'rowid' column that behaves like an INTEGER PRIMARY KEY, except that values remain unchanged if the database is rebuilt using the VACUUM command." is incorrect; an INTEGER PRIMARY KEY column *does* remain unchanged during VACUUM, in contrast to a usual ROWID. It is unclear here if the FTS3 rowid does or does not change during vacuum. - John Brooks On Thu, Dec 3, 2009 at 4:15 PM, Nicolas Williams wrote: > Use the glob operator. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The next release of SQLite....
Use the glob operator. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiples natural left joins problem
2009/12/3 Yuzem : > > . . . > Mine is a little more complex actually: > CREATE TABLE movies(id INTEGER,year INTEGER,rating INTEGER,votes > INTEGER,runtime INTEGER,iconWidth INTEGER,iconHeight INTEGER,iconAlpha > INTEGER,iconModified INTEGER,title TEXT,type TEXT,plot TEXT,color > TEXT,unique(id)) > > CREATE TABLE files(id INTEGER,modified INTEGER,size INTEGER,files > TEXT,unique(files)) > > CREATE TABLE icons_movies(icon_width integer,icon_height integer,icon_alpha > integer,icon_modified integer,id text,unique(id)) > > Movies with no files will not display the icons. Don't know why. Maybe > because id is text in icons and integer in the others? But it joins, just, > not as expected... > So I change: sqlite> drop table tag; sqlite> create table tag( id text, tag text ); sqlite> insert into tag( id, tag ) values( '1', 'tag1' ); sqlite> insert into tag( id, tag ) values( '2', 'tag2' ); sqlite> insert into tag( id, tag ) values( '3', 'tag3' ); sqlite> insert into tag( id, tag ) values( '4', 'tag4' ); sqlite> sqlite> SELECT id,title,rating,tag FROM (movies natural left join user) natural left join tag; id|title|rating|tag 1|movie1|rating1|tag1 2|movie2|rating2|tag2 3|movie3||tag3 text v integer appears to make no difference What query are you trying with the above tables, and how do the results differ from what you expect? > > SimonDavies wrote: Simon Slavin actually... >> >> You need to read up on the different types of JOIN that SQL allows. No >> reason to use a LEFT JOIN if you want a RIGHT JOIN, an INNER JOIN or a >> CROSS JOIN. >> > It says that right join is not supported and inner and cross join don't give > the expected result, they duplicate movies if there are more than one tag > for every movie. Regards, Simon (Davies) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The next release of SQLite....
Jay, That's a reasonable perspective, and indeed I have asked Adobe for the feature. But another not unreasonable position is this: to prevent fragmentation, enhancements that have general applicability are made to the core rather than to individual implementations. The more implementations diverge from the core, the less portable the database becomes. The raw functions I requested would be useful features for the database to have. Once you have a raw LIKE operator, one that tests codepoint by codepoint, it doesn't matter so much what direction the SQLite architects might take in the future in terms of how they decide to handle normalisation of Unicode composed characters (.e.g is a + combining diaresis LIKE a-umlaut?). One can always rely upon the raw string function; not so with functions that incorporate higher-order Unicode awareness. Tim Romano Jay A. Kreibich wrote: > If you want changes to Adobe's implementation, you should probably be > talking to Adobe. > >-j > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting unique entries from one table with multiple columns forming a unique key
Understood. Thank you for discussing this with me. Your help is greatly appreciated. -- Rich On Thu, Dec 3, 2009 at 5:03 PM, Pavel Ivanov wrote: >> Can I ask a follow up question? "from foo, >> bar"... is this behaving like a join? I saw this before and didn't >> quite understand it, I just realized it permuted all rows of the each >> table together (which sounds like a join with no conditions). > > Yes, it is join. What you seem to talk about is a cartesian product > and this would be it if it wasn't WHERE clause. With WHERE clause it > is inner join - SQLite gets smaller of two tables (foo and bar) and > for each row in it scans another table for rows satisfying WHERE > condition. And this behavior is the same as for your query if your foo > table is smaller than bar. But in case if you create index on (col2, > col3) in biggest of the tables foo or bar then my query will work an > order of magnitude faster than yours. > > > Pavel > > On Thu, Dec 3, 2009 at 4:54 PM, Rich Rattanni wrote: >> Awesome! Thank you. Can I ask a follow up question? "from foo, >> bar"... is this behaving like a join? I saw this before and didn't >> quite understand it, I just realized it permuted all rows of the each >> table together (which sounds like a join with no conditions). >> >> Second, I came up with my own solution: >> >> select * from foo >> where col1 || col2 in (select col1 || col2 from bar) >> >> This also works, but from an efficiency standpoint I assume this is >> horrible since it would basically be a series of string compare >> operations. However, if the number of rows in each table is VERY >> LARGE (lets say 50,000) would my solution maybe outperform the first >> (on the surface seems like n^2 vs n*S where S is concat string length >> (which will always be < 50)). >> >> On Thu, Dec 3, 2009 at 4:39 PM, Pavel Ivanov wrote: >>> Maybe >>> >>> select distinct bar.* from foo, bar >>> where foo.col2 = bar.col2 and foo.col3 = bar.col3 >>> >>> It's not clear from your requirements written below whether you need >>> 'distinct' here or not but I've added it just in case... >>> >>> >>> Pavel >>> >>> On Thu, Dec 3, 2009 at 4:34 PM, Rich Rattanni wrote: Suppose I have the following two tables foo: 10 | A | A 20 | B | B 30 | C | C and bar: 1 | A | X 2 | B | C 3 | A | A 4 | C | A 5 | B | B I want to select all the rows in table bar where the second and third column match an entry found in foo (that is to say, I want my result to be 3 | A | A and 5 | B | B. My attempt of: select * from bar where col2 in (select col2 from foo where bar.col2 = col2) and col3 in (select col3 from foo where bar.col3 = col3) Does not work, and I understand that is should not (it returns 2 | B | C, 3 | A | A, 4 | C | A, 5 | B | B). Could someone give me a hand? -- Rich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The next release of SQLite....
On Thu, Dec 03, 2009 at 11:16:43PM +0100, Jean-Christophe Deschamps scratched
on the wall:
>
>> > Does Adobe actually filter out statements similar to:
>> >
>> >select sqlite3_load_extension('mylibrary', 'entrypoint');
>>
>>
>> It is much more likely they simply do not call the C function
>> sqlite3_enable_load_extension( ) either on purpose, or just as an
>> oversight.
>
> I don't know this product but those are two distinct things in my view.
They are distinct, but _loadable_ extensions are off by default.
All types of loadable extensions must be explicitly enabled via that
API call for security reasons. The sqlite3 shell does this.
If extensions are not explicitly enabled, neither the C API call
sqlite3_load_extension(), nor the SQL call load_extension() will work.
> Another possibility is that they have included only part of SQLite in
> their compiled product
Or, simply forgotten to enable selected function. Maybe on accident,
maybe on purpose. If they're intercepting and canceling PRAGMA
commands, they likely did it on purpose.
Regardless, it is an application issue, not an SQLite one.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The next release of SQLite....
> > Does Adobe actually filter out statements similar to:
> >
> >select sqlite3_load_extension('mylibrary', 'entrypoint');
>
>
> It is much more likely they simply do not call the C function
> sqlite3_enable_load_extension( ) either on purpose, or just as an
> oversight.
I don't know this product but those are two distinct things in my view.
A layer (whatever it is) may offer a specific interface to load an
extension (using SQLite API) - a know example is SQLite CLI with its
.load command.
If the user issues a SELECT statement similar to above, then does Adobe
code really dissect it, rewrite it internally and invoke its own
lobotomized interpretation of what was inside the statement? If true
then their tool is best avoided!
Another possibility is that they have included only part of SQLite in
their compiled product and left out selected functions. Looks like dumb.
Perhaps could Tim try things and report what actually happens to be the
case.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting unique entries from one table with multiple columns forming a unique key
> Can I ask a follow up question? "from foo, > bar"... is this behaving like a join? I saw this before and didn't > quite understand it, I just realized it permuted all rows of the each > table together (which sounds like a join with no conditions). Yes, it is join. What you seem to talk about is a cartesian product and this would be it if it wasn't WHERE clause. With WHERE clause it is inner join - SQLite gets smaller of two tables (foo and bar) and for each row in it scans another table for rows satisfying WHERE condition. And this behavior is the same as for your query if your foo table is smaller than bar. But in case if you create index on (col2, col3) in biggest of the tables foo or bar then my query will work an order of magnitude faster than yours. Pavel On Thu, Dec 3, 2009 at 4:54 PM, Rich Rattanni wrote: > Awesome! Thank you. Can I ask a follow up question? "from foo, > bar"... is this behaving like a join? I saw this before and didn't > quite understand it, I just realized it permuted all rows of the each > table together (which sounds like a join with no conditions). > > Second, I came up with my own solution: > > select * from foo > where col1 || col2 in (select col1 || col2 from bar) > > This also works, but from an efficiency standpoint I assume this is > horrible since it would basically be a series of string compare > operations. However, if the number of rows in each table is VERY > LARGE (lets say 50,000) would my solution maybe outperform the first > (on the surface seems like n^2 vs n*S where S is concat string length > (which will always be < 50)). > > On Thu, Dec 3, 2009 at 4:39 PM, Pavel Ivanov wrote: >> Maybe >> >> select distinct bar.* from foo, bar >> where foo.col2 = bar.col2 and foo.col3 = bar.col3 >> >> It's not clear from your requirements written below whether you need >> 'distinct' here or not but I've added it just in case... >> >> >> Pavel >> >> On Thu, Dec 3, 2009 at 4:34 PM, Rich Rattanni wrote: >>> Suppose I have the following two tables >>> >>> foo: >>> 10 | A | A >>> 20 | B | B >>> 30 | C | C >>> >>> and >>> >>> bar: >>> 1 | A | X >>> 2 | B | C >>> 3 | A | A >>> 4 | C | A >>> 5 | B | B >>> >>> >>> I want to select all the rows in table bar where the second and third >>> column match an entry found in foo (that is to say, I want my result >>> to be 3 | A | A and 5 | B | B. >>> >>> My attempt of: >>> select * from bar >>> where col2 in (select col2 from foo where bar.col2 = col2) >>> and col3 in (select col3 from foo where bar.col3 = col3) >>> >>> Does not work, and I understand that is should not (it returns 2 | B | >>> C, 3 | A | A, 4 | C | A, 5 | B | B). >>> >>> Could someone give me a hand? >>> >>> -- >>> Rich >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [bug+patch] Old lemon bug reintroduced
It seems my previous messages have not gone through, so this is a
repeat post.
A recent change to the lemon parser generator has reintroduced an old bug.
The old bug report can be found in the debian bug tracking system:
http://bugs.debian.org/233690
The old bug was fixed in CVS checkin 1249:
http://www.sqlite.org/cvstrac/chngview?cn=1249
However, the bug was re-introduced in checkin 27d8e684db:
http://www.sqlite.org/src/info/27d8e684db
Here is a demonstration of the bug with a small grammar, and a patch
that, at least for me now, appears to fix the bug in the same way as
the CVS checkin mentioned above.
$fossil status
repository: /home/vincent/fossil/sqlite
local-root: /home/vincent/src/sqlite/
server-code: 3efcb091a6d5857b29c9fa3385fee9ee4e8f866f
checkout: 2f42f91fe65b0b21671936013df08037091f0cc6 2009-11-20 18:48:36
UTC
parent: cae949ce971ca216e0f8880b2f93866619fa05be 2009-11-20 17:23:13
UTC
tags: trunk
$cat x.y
start ::= S0.
start ::= S1.
start ::= S2.
start ::= n0.
n0 ::= n4 T0.
n4 ::= .
n4 ::= n4 T4.
%include{
#include
#include
#include "x.h"
void *ParseAlloc(void*(*)(size_t));
void ParseTrace(FILE*,char*);
void Parse(void*,int,void*);
void ParseFree(void*,void(*)(void*));
int main(int argc, char *argv[])
{
void *parser = ParseAlloc(&malloc);
ParseTrace(stderr, "x: ");
Parse(parser, T0, NULL);
Parse(parser, 0, NULL);
ParseFree(parser, &free);
return 0;
}
}
$gcc -o tool/lemon tool/lemon.c
$tool/lemon -s x.y
Parser statistics: 6 terminals, 4 nonterminals, 7 rules
8 states, 0 parser table entries, 0 conflicts
$gcc -o x x.c
$./x
x: Input T0
x: Shift 7
x: Stack: T0
x: Input $
x: Reduce [n0 ::= n4 T0].
x: x.c:524: yy_find_reduce_action: Assertion `stateno<=(0)' failed.
Aborted
$cat crash.patch
Index: tool/lemon.c
===
fossil diff /home/vincent/src/sqlite/tool/lemon.c
--- tool/lemon.c
+++ tool/lemon.c
@@ -518,11 +518,11 @@
** offset is found. In the worst case, we fall out of the loop when
** i reaches p->nAction, which means we append the new transaction set.
**
** i is the index in p->aAction[] where p->mnLookahead is inserted.
*/
- for(i=p->nAction-1; i>=0; i--){
+ for(i=p->nAction+p->mnLookahead-1; i>=0; i--){
/* First look for an existing action table entry that can be reused */
if( p->aAction[i].lookahead==p->mnLookahead ){
if( p->aAction[i].action!=p->mnAction ) continue;
for(j=0; jnLookahead; j++){
k = p->aLookahead[j].lookahead - p->mnLookahead + i;
@@ -541,11 +541,11 @@
}
}
}
if( i<0 ){
/* If no reusable entry is found, look for an empty slot */
-for(i=0; inAction; i++){
+for(i=0; inAction+p->mnLookahead; i++){
if( p->aAction[i].lookahead<0 ){
for(j=0; jnLookahead; j++){
k = p->aLookahead[j].lookahead - p->mnLookahead + i;
if( k<0 ) break;
if( p->aAction[k].lookahead>=0 ) break;
$patch -p0 | "If you're flamed in a group you
Re: [sqlite] The next release of SQLite....
On Thu, Dec 03, 2009 at 10:07:07PM +0100, Jean-Christophe Deschamps scratched
on the wall:
> Does Adobe actually filter out statements similar to:
>
>select sqlite3_load_extension('mylibrary', 'entrypoint');
It is much more likely they simply do not call the C function
sqlite3_enable_load_extension( ) either on purpose, or just as an
oversight.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting unique entries from one table with multiple columns forming a unique key
Awesome! Thank you. Can I ask a follow up question? "from foo, bar"... is this behaving like a join? I saw this before and didn't quite understand it, I just realized it permuted all rows of the each table together (which sounds like a join with no conditions). Second, I came up with my own solution: select * from foo where col1 || col2 in (select col1 || col2 from bar) This also works, but from an efficiency standpoint I assume this is horrible since it would basically be a series of string compare operations. However, if the number of rows in each table is VERY LARGE (lets say 50,000) would my solution maybe outperform the first (on the surface seems like n^2 vs n*S where S is concat string length (which will always be < 50)). On Thu, Dec 3, 2009 at 4:39 PM, Pavel Ivanov wrote: > Maybe > > select distinct bar.* from foo, bar > where foo.col2 = bar.col2 and foo.col3 = bar.col3 > > It's not clear from your requirements written below whether you need > 'distinct' here or not but I've added it just in case... > > > Pavel > > On Thu, Dec 3, 2009 at 4:34 PM, Rich Rattanni wrote: >> Suppose I have the following two tables >> >> foo: >> 10 | A | A >> 20 | B | B >> 30 | C | C >> >> and >> >> bar: >> 1 | A | X >> 2 | B | C >> 3 | A | A >> 4 | C | A >> 5 | B | B >> >> >> I want to select all the rows in table bar where the second and third >> column match an entry found in foo (that is to say, I want my result >> to be 3 | A | A and 5 | B | B. >> >> My attempt of: >> select * from bar >> where col2 in (select col2 from foo where bar.col2 = col2) >> and col3 in (select col3 from foo where bar.col3 = col3) >> >> Does not work, and I understand that is should not (it returns 2 | B | >> C, 3 | A | A, 4 | C | A, 5 | B | B). >> >> Could someone give me a hand? >> >> -- >> Rich >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The next release of SQLite....
On Thu, Dec 3, 2009 at 10:26 AM, Tim Romano wrote: > The alternative, NOCASE collation, also does not get me out of the > woods. For some reason in Adobe (and in Mozilla) the index is not used > on LIKE clauses when the column in question has NOCASE collation, though > SQLite3.EXE does use the index in its query plan on the same query. > These consortium members might be overriding the LIKE function or > compiling statements not with _V2 or doing something else that prevents > the optimization. I don't know. > Mozilla does override the LIKE function because we need to be able to handle Unicode, which the default implementation does not do. The implementation is here: http://mxr.mozilla.org/mozilla-central/source/storage/src/mozStorageSQLFunctions.cpp#408 And we'd happily accept patches to fix this issue. Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting unique entries from one table with multiple columns forming a unique key
Maybe select distinct bar.* from foo, bar where foo.col2 = bar.col2 and foo.col3 = bar.col3 It's not clear from your requirements written below whether you need 'distinct' here or not but I've added it just in case... Pavel On Thu, Dec 3, 2009 at 4:34 PM, Rich Rattanni wrote: > Suppose I have the following two tables > > foo: > 10 | A | A > 20 | B | B > 30 | C | C > > and > > bar: > 1 | A | X > 2 | B | C > 3 | A | A > 4 | C | A > 5 | B | B > > > I want to select all the rows in table bar where the second and third > column match an entry found in foo (that is to say, I want my result > to be 3 | A | A and 5 | B | B. > > My attempt of: > select * from bar > where col2 in (select col2 from foo where bar.col2 = col2) > and col3 in (select col3 from foo where bar.col3 = col3) > > Does not work, and I understand that is should not (it returns 2 | B | > C, 3 | A | A, 4 | C | A, 5 | B | B). > > Could someone give me a hand? > > -- > Rich > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Selecting unique entries from one table with multiple columns forming a unique key
Suppose I have the following two tables foo: 10 | A | A 20 | B | B 30 | C | C and bar: 1 | A | X 2 | B | C 3 | A | A 4 | C | A 5 | B | B I want to select all the rows in table bar where the second and third column match an entry found in foo (that is to say, I want my result to be 3 | A | A and 5 | B | B. My attempt of: select * from bar where col2 in (select col2 from foo where bar.col2 = col2) and col3 in (select col3 from foo where bar.col3 = col3) Does not work, and I understand that is should not (it returns 2 | B | C, 3 | A | A, 4 | C | A, 5 | B | B). Could someone give me a hand? -- Rich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The next release of SQLite....
Hi Tim,
> ... where myTextColumnUsingDefaultBinaryCollation like 'foo%'
Did you try
... where myTextColumnUsingDefaultBinaryCollation glob 'foo*'
GLOB is hardcoded as case-sensitive and more likely a candidate to
using index. Just check it.
>2. In Adobe, one is not able to load a user-defined function as an
>extension. I need a *raw* codepoint-by-codepoint reversal of a text
>string, which makes no attempt to distinguish between Unicode combining
>characters and base-characters, similar to what can be done in Oracle
>and SQLServer.
Does Adobe actually filter out statements similar to:
select sqlite3_load_extension('mylibrary', 'entrypoint');
If they do, then they really have something against _you_ ;-)
If they don't (which is what I would wild guess) then I've offered you
something that should perform per your requirements. BTW I've a
revise, expanded version available with (portable) a NUMERIC Unicode
sort (lexicagraphic sort of variable-size text numbers sucks).
>B. Create another LIKE that is *always* case-sensitive. RAWLIKE or SLIKE
>or whatever. It does a simple codepoint-by-codepoint test and doesn't
>have any special intelligence for ASCII/LATIN.
That's GLOB, already in the box and free.
>RAW STRING REVERSAL
>This is a simple function and the other major players have it. It seems
>to me that it should be easy to implement. Concerns about combining
>characters and base characters and higher order Unicode intelligence
>could be saved for a UREVERSE() function, one which preserves Unicode
>composed characters.
If you need I can make a limited verion of my extension for you with
only the functions you need.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The next release of SQLite....
>Last minute comments on the pending release of SQLite 3.6.21 are >welcomed. Thank you for your continued efforts. Can you consider making sqlite3_auto_extension and sqlite3_reset_auto_extension available into the API structure so that they can both be invoked from within an extension without bringing dependancy on sqlite3.dll/so For reasons that would require lengthy explanations, loading extensions is a vital feature but it is unduly limited as it stands today. Making the above inclusion would help a _lot_ in some situations, really. There are related severe issues with loading extensions thru SQL, but this would need more than a last minute easy change. I can elaborate on both subjects, as I've been banging my bones on these questions ad nauseam. TIA. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The next release of SQLite....
On Thu, Dec 03, 2009 at 01:26:40PM -0500, Tim Romano scratched on the wall: > I have some ideas on how these two limiting features of Adobe's > implementation of SQLite might be addressed. I don't know how feasible > they might be given the existing codebase because I am not a C coder. If you want changes to Adobe's implementation, you should probably be talking to Adobe. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Optimization
On 3 Dec 2009, at 6:26pm, mr_orange wrote: > I am trying to optimize the speed of my SQLite transactions. The goal is to > beat query and insertion times that we have with MS SQL. I guess there are 2 > main issues: > > 1) The query times are faster when I do simple select statements on a large > amount of data. I start to run into trouble when I get into more complex > join statement that involve date conversion functions. I've read that > replacing joins with "WHERE" statements that involve consideration of the > ordering of tables in FROM statement can help with speed. However, I am a > little skeptical since I think that this consideration was already made in > the current implementation. Regard the matching parameters of 'JOIN' as the same as 'WHERE'. In other words, to make them happen fastest, JOIN on indexed columns. If you are going to join on the results of a calculation (e.g. date conversion function) store the result of the calculation in its own column and include that column in an appropriate index. Remember: the majority of effort SQL puts into a SELECT instruction is all to do with the WHERE clause (and in your case, the JOIN, which is another WHERE clause). Understand how those work and you'll understand what's taking all the time. > 2) I've tried ramping up the "cache_size" (from 2000 to 1), but I don't > see any effect, even with simple select statements. Does this seem strange? Forget that sort of tuning. You're going to get your improvements by understanding how indexes are used. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The next release of SQLite....
Since the consortium members are prominently listed on those pages, I hope it is not inappropriate if my comments refer to a couple of them. Nothing disparaging. As I am in the process of discovering (and so subject to revision/correction), there are a number of capabilities in SQLite that are disabled or not enabled in consortium member Adobe's implementation. LoadableExtensions don't seem to be available, and PRAGMA is not. Adobe has good reasons for this, I'm sure, but these limitations leave my application rather high and dry on two counts: 1. Without PRAGMA, I cannot set the case-sensitivity of the LIKE operator and so cannot get use of index optimization on queries like this: ... where myTextColumnUsingDefaultBinaryCollation like 'foo%' The alternative, NOCASE collation, also does not get me out of the woods. For some reason in Adobe (and in Mozilla) the index is not used on LIKE clauses when the column in question has NOCASE collation, though SQLite3.EXE does use the index in its query plan on the same query. These consortium members might be overriding the LIKE function or compiling statements not with _V2 or doing something else that prevents the optimization. I don't know. This has a major impact on performance. Queries that take < 100ms in SQLite3.EXE take 40-50 seconds in Adobe (and Mozilla) because of the full-table-scan. 2. In Adobe, one is not able to load a user-defined function as an extension. I need a *raw* codepoint-by-codepoint reversal of a text string, which makes no attempt to distinguish between Unicode combining characters and base-characters, similar to what can be done in Oracle and SQLServer. I have some ideas on how these two limiting features of Adobe's implementation of SQLite might be addressed. I don't know how feasible they might be given the existing codebase because I am not a C coder. INDEX-OPTIMIZATION WITH LIKE A. Make the existing LIKE operator able to recognize the column COLLATION and adapt its case-sensitivity to that COLLATION. B. Create another LIKE that is *always* case-sensitive. RAWLIKE or SLIKE or whatever. It does a simple codepoint-by-codepoint test and doesn't have any special intelligence for ASCII/LATIN. RAW STRING REVERSAL This is a simple function and the other major players have it. It seems to me that it should be easy to implement. Concerns about combining characters and base characters and higher order Unicode intelligence could be saved for a UREVERSE() function, one which preserves Unicode composed characters. Thanks for considering. Regards Tim Romano D. Richard Hipp wrote: > Last minute comments on the pending release of SQLite 3.6.21 are > welcomed. Visit > > http://www.sqlite.org/draft/index.html > http://www.sqlite.org/draft/releaselog/3_6_21.html > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite Optimization
I am trying to optimize the speed of my SQLite transactions. The goal is to beat query and insertion times that we have with MS SQL. I guess there are 2 main issues: 1) The query times are faster when I do simple select statements on a large amount of data. I start to run into trouble when I get into more complex join statement that involve date conversion functions. I've read that replacing joins with "WHERE" statements that involve consideration of the ordering of tables in FROM statement can help with speed. However, I am a little skeptical since I think that this consideration was already made in the current implementation. 2) I've tried ramping up the "cache_size" (from 2000 to 1), but I don't see any effect, even with simple select statements. Does this seem strange? I've consulted the following site: http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html#pragma-cache_size SQLite Optimization for advice on how to optimize SQLite. Does anyone know of any other resources on the topic of optimizing SQLite? Any advice welcome, thanks. -- View this message in context: http://old.nabble.com/SQLite-Optimization-tp26630742p26630742.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiples natural left joins problem
SimonDavies wrote: > > sqlite> create table movies( id integer primary key, title text ); > sqlite> insert into movies( title ) values( 'movie1' ); > sqlite> insert into movies( title ) values( 'movie2' ); > sqlite> insert into movies( title ) values( 'movie3' ); > sqlite> > sqlite> create table user( id integer primary key, rating text ); > sqlite> insert into user( rating ) values( 'rating1' ); > sqlite> insert into user( rating ) values( 'rating2' ); > sqlite> > sqlite> create table tag( id integer primary key, tag text ); > sqlite> insert into tag( tag ) values( 'tag1' ); > sqlite> insert into tag( tag ) values( 'tag2' ); > sqlite> insert into tag( tag ) values( 'tag3' ); > sqlite> > sqlite> SELECT a.id,a.title,a.rating,tag.tag FROM (movies natural left > join user > ) as a natural left join tag; > 1|movie1|rating1|tag1 > 2|movie2|rating2|tag2 > 3|movie3||tag3 > sqlite> > > Regards, > Simon > Mine is a little more complex actually: CREATE TABLE movies(id INTEGER,year INTEGER,rating INTEGER,votes INTEGER,runtime INTEGER,iconWidth INTEGER,iconHeight INTEGER,iconAlpha INTEGER,iconModified INTEGER,title TEXT,type TEXT,plot TEXT,color TEXT,unique(id)) CREATE TABLE files(id INTEGER,modified INTEGER,size INTEGER,files TEXT,unique(files)) CREATE TABLE icons_movies(icon_width integer,icon_height integer,icon_alpha integer,icon_modified integer,id text,unique(id)) Movies with no files will not display the icons. Don't know why. Maybe because id is text in icons and integer in the others? But it joins, just, not as expected... SimonDavies wrote: > > You need to read up on the different types of JOIN that SQL allows. No > reason to use a LEFT JOIN if you want a RIGHT JOIN, an INNER JOIN or a > CROSS JOIN. > It says that right join is not supported and inner and cross join don't give the expected result, they duplicate movies if there are more than one tag for every movie. -- View this message in context: http://old.nabble.com/Multiples-natural-left-joins-problem-tp26627140p26630530.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The next release of SQLite....
On Thu, Dec 3, 2009 at 12:01 PM, D. Richard Hipp wrote: > Last minute comments on the pending release of SQLite 3.6.21 are > welcomed. Visit > > http://www.sqlite.org/draft/index.html > http://www.sqlite.org/draft/releaselog/3_6_21.html > with regards to the newly reworked FTS3 and its new docs (see http://www.sqlite.org/draft/fts3.html)... bravo! What can I say, with tears of joy in my eyes. My one compelling reason (besides other compelling reasons) to continue using SQLite got more compelling. -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === Sent from Madison, Wisconsin, United States ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiples natural left joins problem
2009/12/3 Simon Davies : > 2009/12/3 Yuzem : >>> SELECT a.id,a.title,a.rating,tag.tag FROM (movies natural left join >>> user) as a natural left join tag; >>> >> It doesn't work, it says: no such column a.id >> If I use movies.id I have the same problem as before: movies with no rating >> don't get any tags. >> > > This is what I see: > > C:\>sqlite3 yuzem.db > SQLite version 3.4.2 > Enter ".help" for instructions . . . > sqlite> SELECT a.id,a.title,a.rating,tag.tag FROM (movies natural left join > user > ) as a natural left join tag; > 1|movie1|rating1|tag1 > 2|movie2|rating2|tag2 > 3|movie3||tag3 > sqlite> > > Regards, > Simon > Simpler still: SELECT id,title,rating,tag FROM (movies natural left join user) natural left join tag; Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Virtual Tables & Transactions
Documentation error and/or bug: http://sqlite.org/vtab.html#xsync "This method is only invoked after call to the xBegin method..." Not true (3.6.20). An xSync/xCommit pair is given after the initial xCreate call. I'm not sure if that is intentional or not. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiples natural left joins problem
On 3 Dec 2009, at 5:30pm, Yuzem wrote: > SimonDavies wrote: >> >> SELECT a.id,a.title,a.rating,tag.tag FROM (movies natural left join >> user) as a natural left join tag; >> > It doesn't work, it says: no such column a.id > If I use movies.id I have the same problem as before: movies with no rating > don't get any tags. You need to read up on the different types of JOIN that SQL allows. No reason to use a LEFT JOIN if you want a RIGHT JOIN, an INNER JOIN or a CROSS JOIN. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiples natural left joins problem
>> select movies.id, title, rating, tag >> from movies left join user on movies.id = user.id >> left join tag on movies.id = tag.id; >> > Ok, this works and it is fast but isn't any way to make this a little > simpler because the real query involves more tables and more columns and it > becomes a mess. I tried with using(id) but it is the same problem than with > natural left join. No, you cannot simplify this. Maybe you better make trigger on movies so that every time new row inserted relative rows in user and tag are created with empty rating and tag value. After that you won't have to deal with left joins and with simple "natural join" you won't have such problem... Pavel On Thu, Dec 3, 2009 at 12:30 PM, Yuzem wrote: > > Thanks both for the replies. > > Pavel Ivanov-2 wrote: >> >> If using "natural left join" is not a requirement for you then this >> works as you expect it: >> select movies.id, title, rating, tag >> from movies left join user on movies.id = user.id >> left join tag on movies.id = tag.id; >> > Ok, this works and it is fast but isn't any way to make this a little > simpler because the real query involves more tables and more columns and it > becomes a mess. I tried with using(id) but it is the same problem than with > natural left join. > > > SimonDavies wrote: >> >> SELECT a.id,a.title,a.rating,tag.tag FROM (movies natural left join >> user) as a natural left join tag; >> > It doesn't work, it says: no such column a.id > If I use movies.id I have the same problem as before: movies with no rating > don't get any tags. > > -- > View this message in context: > http://old.nabble.com/Multiples-natural-left-joins-problem-tp26627140p26629866.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiples natural left joins problem
2009/12/3 Yuzem : > > Thanks both for the replies. . . . . > SimonDavies wrote: >> >> SELECT a.id,a.title,a.rating,tag.tag FROM (movies natural left join >> user) as a natural left join tag; >> > It doesn't work, it says: no such column a.id > If I use movies.id I have the same problem as before: movies with no rating > don't get any tags. > This is what I see: C:\>sqlite3 yuzem.db SQLite version 3.4.2 Enter ".help" for instructions sqlite> sqlite> sqlite> create table movies( id integer primary key, title text ); sqlite> insert into movies( title ) values( 'movie1' ); sqlite> insert into movies( title ) values( 'movie2' ); sqlite> insert into movies( title ) values( 'movie3' ); sqlite> sqlite> create table user( id integer primary key, rating text ); sqlite> insert into user( rating ) values( 'rating1' ); sqlite> insert into user( rating ) values( 'rating2' ); sqlite> sqlite> create table tag( id integer primary key, tag text ); sqlite> insert into tag( tag ) values( 'tag1' ); sqlite> insert into tag( tag ) values( 'tag2' ); sqlite> insert into tag( tag ) values( 'tag3' ); sqlite> sqlite> SELECT a.id,a.title,a.rating,tag.tag FROM (movies natural left join user ) as a natural left join tag; 1|movie1|rating1|tag1 2|movie2|rating2|tag2 3|movie3||tag3 sqlite> Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiples natural left joins problem
Thanks both for the replies. Pavel Ivanov-2 wrote: > > If using "natural left join" is not a requirement for you then this > works as you expect it: > select movies.id, title, rating, tag > from movies left join user on movies.id = user.id > left join tag on movies.id = tag.id; > Ok, this works and it is fast but isn't any way to make this a little simpler because the real query involves more tables and more columns and it becomes a mess. I tried with using(id) but it is the same problem than with natural left join. SimonDavies wrote: > > SELECT a.id,a.title,a.rating,tag.tag FROM (movies natural left join > user) as a natural left join tag; > It doesn't work, it says: no such column a.id If I use movies.id I have the same problem as before: movies with no rating don't get any tags. -- View this message in context: http://old.nabble.com/Multiples-natural-left-joins-problem-tp26627140p26629866.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Convert Access sql to SQLite sql
"Pavel Ivanov" schrieb im Newsbeitrag news:f3d9d2130912020440r1777d1ado6d9938755f80...@mail.gmail.com... > Your Access query doesn't have good equivalent in SQLite. Your options are: > 1. Execute SELECT separately and then for each row in the result issue > an UPDATE with necessary values (UPDATE can be prepared and you can > just bind all values). > > 2. Insert results of SELECT into some temporary table and then issue > one UPDATE statement like this: > > UPDATE EvAtemp SET > EvAtemp.EstimateDate = (select Date from TempTable tt where tt.Code = > EvAtemp.Code and tt.Function = EvAtemp.Function), > EvAtemp.CodeEst = (select Code from TempTable tt where tt.Code = > EvAtemp.Code and tt.Function = EvAtemp.Function), > ... > > This will work much-much slower than first approach. > > 3. Modify your SELECT so that it joins with EvAtemp too to get from > there rowid of the row which should be updated, insert results into > temporary table and issue UPDATE like this: > > UPDATE EvAtemp SET > EvAtemp.EstimateDate = (select Date from TempTable tt where > tt.EvAtempROWID = EvAtemp.rowid), > EvAtemp.CodeEst = (select Code from TempTable tt where tt. > EvAtempROWID = EvAtemp.rowid), > ... > > This will work faster than 2nd approach but I believe it's still > slower than the 1st. Yep, good hints - and when googling, one can find also messages which suggest an: 4. Insert Or Replace Into ... Select ... approach. But doing so would (although working) involve a Delete/Recreate instead of an Update of records in many cases. What I fiddled out in the meantime is something, which maybe could be included into the core possibly, since it involves "stuff which is already there" in SQLite. A working Demo-Source is posted here (using the VB-language): http://groups.google.de/group/microsoft.public.vb.database/msg/20bc947f3d5bdea4 In short again... 5. Alternative to an Update Tbl Set From (Select ...) As Qry Where Cond - define a (temporary) View for all columns of [Tbl], which are involed in the Update-Stmt above (the ones defined in the Set-list, as well as the ones, used within the final "Update-Where-Condition" - define a second (temporary) View for the SubSelect [Qry], so that we are able later on, to rearrange the Column-Order in a way, that it matches with the order of the Columns in the first created view above (to meet the "Set-Pairs" and eventual compair-pairs of the Where-Condition). - define an Instead Of Trigger for the first View... assuming the first view was named [v_Tbl] having columns A,B,C: Create Temp Trigger tr_upd_Tbl Instead Of Insert On v_Tbl Begin Update Tbl Set A = new.A, B = new.B Where C = new.C; End; ...note, that the new.xxx-names in the expressions can (should) match the lefthandside naming directly, which eases programmatic construction of that trigger-definition. - finally the "triggering": Insert Into v_Tbl Select From v_Qry ...if we take care in the needed fieldlist-Def for the SubSelect- View [v_Qry], that the "Set-, and expression-pair righthandsides" match with the Column-Order, defined for [v_Tbl], then we can execute the whole thing - and the performance is not bad, already tested that... - oh, and dont forget the "cleanup-drops" for the temporary views and the temporary trigger. Don't know, if one does work "out of specification", if an Update is performed inside an "Instead Of Insert" trigger, but the above steps perform really good + it can be "formalized" nicely, to construct the statements also programmatically (maybe at some "PreProcessor-level", at least in the wrappers, if something like that is not planned to integrate into the core-engine). Olaf Schmidt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] The next release of SQLite....
Last minute comments on the pending release of SQLite 3.6.21 are welcomed. Visit http://www.sqlite.org/draft/index.html http://www.sqlite.org/draft/releaselog/3_6_21.html D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Pragma not allowed in SQL error
Tim Romano wrote: > Thanks, Simon. I've tried "PRAGMA encoding" too but Adobe complains > whenever it sees PRAGMA. > Tim Romano > According to the Adobe documentation, PRAGMA statements are not supported by their actionscript/flash database engine. http://livedocs.adobe.com/flash/9.0/ActionScriptLangRefV3/localDatabaseSQLSupport.html#unsupportedSQL ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Pragma not allowed in SQL error
Thanks, Simon. I've tried "PRAGMA encoding" too but Adobe complains whenever it sees PRAGMA. Tim Romano Simon Slavin wrote: > On 3 Dec 2009, at 2:51pm, Tim Romano wrote: > > >> The statement I'm issuing to the database >> via Adobe's libraries is >> >> PRAGMA case_sensitive_like =1 >> >> But I take it from your answer that SQLite has no problem with this and >> that it is Adobe who are preventing my PRAGMA directive from being executed? >> > > Certainly that statement is not a problem for SQLite. I don't think the > precise placing of 'space' characters matters. I don't know anything about > the Adobe library but it seems to me that if something is stopping it working > it must be the Adobe thing. > > Please try using the same library to do this command: > > PRAGMA encoding > > since this PRAGMA command includes no underlines and no spaces. If this one > fails then the Adobe tool appears to have a problem with PRAGMA commands. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiples natural left joins problem
2009/12/3 Pavel Ivanov : > If using "natural left join" is not a requirement for you then this > works as you expect it: > > select movies.id, title, rating, tag > from movies left join user on movies.id = user.id > left join tag on movies.id = tag.id; > Or: SELECT a.id,a.title,a.rating,tag.tag FROM (movies natural left join user) as a natural left join tag; Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Pragma not allowed in SQL error
On 3 Dec 2009, at 2:51pm, Tim Romano wrote: > The statement I'm issuing to the database > via Adobe's libraries is > > PRAGMA case_sensitive_like =1 > > But I take it from your answer that SQLite has no problem with this and > that it is Adobe who are preventing my PRAGMA directive from being executed? Certainly that statement is not a problem for SQLite. I don't think the precise placing of 'space' characters matters. I don't know anything about the Adobe library but it seems to me that if something is stopping it working it must be the Adobe thing. Please try using the same library to do this command: PRAGMA encoding since this PRAGMA command includes no underlines and no spaces. If this one fails then the Adobe tool appears to have a problem with PRAGMA commands. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiples natural left joins problem
If using "natural left join" is not a requirement for you then this works as you expect it: select movies.id, title, rating, tag from movies left join user on movies.id = user.id left join tag on movies.id = tag.id; Pavel On Thu, Dec 3, 2009 at 9:49 AM, Yuzem wrote: > > Lets say I have the following tables: > movies: id,title > user: id,rating > tag: id,tag > > I want to left join all three tables: > SELECT movies.id,movies.title,user.rating,tag.tag > FROM movies natural left join user natural left join tag > > The problem with this besides having to specify the tables (instead of > doing: select id,title,rating,tag) is that if a movie doesn't have any > rating it will not retrieve any tag neither. The table tag is joined to the > table user not to the main table movies > > The following works as expected but it is terribly slow: > SELECT id,title,rating,tag > FROM movies natural left join (movies natural left join user) natural left > join (movies natural left join tag) > > Is there any way to get the last result with an acceptable speed? > -- > View this message in context: > http://old.nabble.com/Multiples-natural-left-joins-problem-tp26627140p26627140.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Pragma not allowed in SQL error
Actually, that syntax in my question was just me being sloppy when asking the question. Sorry. The statement I'm issuing to the database via Adobe's libraries is PRAGMA case_sensitive_like =1 But I take it from your answer that SQLite has no problem with this and that it is Adobe who are preventing my PRAGMA directive from being executed? Tim Tim Romano wrote: > I was trying to do > > PRAGMA set case_sensitive_like =1 > > in Adobe Flash Builder Beta 4 and got the following error: > > Error #2044: Unhandled SQLErrorEvent:. errorID=3115, operation=execute , > message=Error #3115: SQL Error. , details=PRAGMA is not allowed in SQL. > > Is this an Adobe error? Or are they passing through a SQLite error? > > Thanks > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > No virus found in this incoming message. > Checked by AVG - www.avg.com > Version: 8.5.426 / Virus Database: 270.14.91/2541 - Release Date: 12/02/09 > 19:43:00 > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Pragma not allowed in SQL error
>PRAGMA set case_sensitive_like =1 This should be: PRAGMA case_sensitive_like = 1 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiples natural left joins problem
Lets say I have the following tables: movies: id,title user: id,rating tag: id,tag I want to left join all three tables: SELECT movies.id,movies.title,user.rating,tag.tag FROM movies natural left join user natural left join tag The problem with this besides having to specify the tables (instead of doing: select id,title,rating,tag) is that if a movie doesn't have any rating it will not retrieve any tag neither. The table tag is joined to the table user not to the main table movies The following works as expected but it is terribly slow: SELECT id,title,rating,tag FROM movies natural left join (movies natural left join user) natural left join (movies natural left join tag) Is there any way to get the last result with an acceptable speed? -- View this message in context: http://old.nabble.com/Multiples-natural-left-joins-problem-tp26627140p26627140.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Pragma not allowed in SQL error
On 3 Dec 2009, at 2:42pm, Tim Romano wrote: > I was trying to do > > PRAGMA set case_sensitive_like =1 Wrong syntax: http://www.sqlite.org/pragma.html Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Pragma not allowed in SQL error
I was trying to do PRAGMA set case_sensitive_like =1 in Adobe Flash Builder Beta 4 and got the following error: Error #2044: Unhandled SQLErrorEvent:. errorID=3115, operation=execute , message=Error #3115: SQL Error. , details=PRAGMA is not allowed in SQL. Is this an Adobe error? Or are they passing through a SQLite error? Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to deal with non 'normilize-able' tables
Hi CityDev,
Thanks for getting back to me on this and sorry for not getting back
before. I thought it had dropped off the bottom of the forum.
Yeah ... it's a pain in the backside. I'd prefer to keep everything
normalised, but as I said, it isn't going to be possible. Thanks for
the suggestions.
I'm a bit miffed because I'm of all the additional queries I'll have
to write on the fly to query these quirky tables. Nevermind.
ALJ
On Nov 25, 5:44 pm, CityDev wrote:
> What you are saying is you are holding information about items which have
> different characteristics. To represent these as relations you would have a
> product entity then you would have an attribute entity that would be like
> (product_id,attribute_id,attribute_name,attribute_value) eg:
>
> screwdriver1210, 1, handle, wood
> screwdriver1210, 2, point, crossdrive
> screwdriver1210, 3, weight, 180
> hammer0899, 1,weight, 3.35
> hammer0899, 2 head, steel
> etc
>
> You then join from product to this table and pick up the listed features.
> You can of course use attribute_ids that are standardised eg 56 is always
> weight etc
>
> That's one way. Alternatively if there aren't too many different attributes
> you can collapse ('denormalise') these into a bunch of fields in the product
> table. I would recommend leaving your model normalised until you are forced
> to compromise.
>
> --
> View this message in
> context:http://old.nabble.com/How-to-deal-with-non-%27normilize-able%27-table...
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-us...@sqlite.orghttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ANN: SQLite Data Wizard 9.12 released
Hi! SQL Maestro Group announces the release of SQLite Data Wizard 9.12, a powerful Windows GUI solution for SQLite data management. SQLite Data Wizard provides you with a number of easy-to-use wizards to convert any ADO-compatible database to the SQLite database, import data into SQLite tables, export data from tables, views and queries to most popular file formats as well as generate data-driven ASP.NET pages for your SQLite database. The new version is immediately available for download at http://www.sqlmaestro.com/products/sqlite/datawizard/ New features = 1. Data Import: now you can import data from XML, Microsoft Office Excel 2007 and Microsoft Office Access 2007 file formats along with Microsoft Office Excel 97-2003, Microsoft Office Access, CSV, DBF, and Text files supported in the previous versions. Also starting with this version you can import Text and CSV data files stored in different encodings. 2. Data Import: now it is possible to empty target tables as well as execute custom SQL scripts before and after the import. 3. Data Import: the wizard has been completely redesigned to improve the look and feel and increase the usability. 4. Data Export: support for Microsoft Office Excel 2007, Microsoft Office Word 2007, OpenDocument Spreadsheed, and OpenDocument Text file formats has been implemented. Also now it is possible to select the result file encoding. This wizard also has been completely redesigned. 5. Datapump: the conversion rules for fields and indexes become more intelligent, so starting with this version most of source databases from well-known DBMS can be transferred even with default settings (or with a small customization). Also the speed of data transfer has been significantly increased. 6. An Italian localization is now included into the installation package. In addition to this, several bugs have been fixed and some other minor improvements and corrections have been made. Full press-release is available at: http://www.sqlmaestro.com/news/company/data_wizard_family_updated_to_version_9_12/ Background information: --- SQL Maestro Group offers complete database admin, development and management tools for MySQL, SQL Server, PostgreSQL, Oracle, DB2, SQLite, SQL Anywhere, Firebird and MaxDB providing the highest performance, scalability and reliability to meet the requirements of today's database applications. Sincerely yours, The SQL Maestro Group Team http://www.sqlmaestro.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ODBC Problem with SQLite
Hello, I suppose you don't see nothing but sth like #Gelöscht (german) in your fields. The solution should be to use "Primary Keys" ODBC-ERROR: YES --- CREATE TABLE test_01( a INTEGER, b TEXT ); ODBC-ERROR: NO -- CREATE TABLE test_02( a INTEGER PRIMARY KEY, b TEXT ); I already sent a mail to the developer greetings oliver > -Ursprüngliche Nachricht- > Von: "rriera" > Gesendet: 03.12.09 13:00:00 > An: sqlite-users@sqlite.org > Betreff: [sqlite] ODBC Problem with SQLite > > Hi, > > I installed the ODBC 2.8.17/3.6.20 SQLite driver package and I did the set > up, but when I want to open a SQLite database with MS Access, Access opens > the DB but without all the data. Do you had this problem some time? Or know > any solution? > > Thanks, > > Roger > > -- > View this message in context: > http://old.nabble.com/ODBC-Problem-with-SQLite-tp26623595p26623595.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ Preisknaller: WEB.DE DSL Flatrate für nur 16,99 Euro/mtl.! http://produkte.web.de/go/02/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ODBC Problem with SQLite
Hi, I installed the ODBC 2.8.17/3.6.20 SQLite driver package and I did the set up, but when I want to open a SQLite database with MS Access, Access opens the DB but without all the data. Do you had this problem some time? Or know any solution? Thanks, Roger -- View this message in context: http://old.nabble.com/ODBC-Problem-with-SQLite-tp26623595p26623595.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ANN: SQLite components for D2009, D2010
A candidate release version of the open-source SQLite components for Delphi 2009/2010 has been released and is available on www.aducom.com albert drent ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] fts3b-4.9 fails - FTS3 docid updates now possible with latest version?
On 02.12.2009 20:00, Dan Kennedy wrote: > On Dec 2, 2009, at 3:07 PM, Ralf Junker wrote: > >> * Is it now intentionally possible to update FTS3 DOCIDs? > > It is. The failing sqlite test case has been replaced. Thanks you, Dan! I had already noticed! Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error message from sqlite3_tokenizer_module.xCreate?
On 01.12.2009 09:01, Dan Kennedy wrote:
>> Monday 30 of November 2009 12:29:10 Ralf Junker napisał(a):
>> I am passing various arguments to sqlite3_tokenizer_module.xCreate.
>> In case they are invalid, I would like to return an explaining
>> error message in addition to SQLITE_ERROR. I did not find a way to
>> do this. Is it at all possible?
> I don't think it is possible at the moment. Unfortunately.
Dan, thanks for the confirmation.
Related to this, I noticed check-in [620a8a2b38] which avoids using an
uninitialized variable for the error message issued when
sqlite3_tokenizer_module.xCreate returns an error. The error message is now:
pzErr = sqlite3_mprintf("unknown tokenizer");
I believe that the message is misleading because the tokenizer is not
unknown. The error is that a registered tokenizer can not be created.
Personally, I would like to see an error message giving the proper
reason and the name of the tokenizer:
pzErr = sqlite3_mprintf(
"cannot create tokenizer %s");
Also giving the tokenizer arguments is naturally helpful for
debugging and end-user support:
pzErr = sqlite3_mprintf(
"cannot create tokenizer %s with arguments %s");
Ralf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
