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

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

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

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

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:

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

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,

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

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

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

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

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

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

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

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

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

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

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

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 >

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

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

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

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

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

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

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

[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

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

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

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

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

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

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 >

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

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,

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

[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

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.

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

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

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

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

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

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

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

[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

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.

[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

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

[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

[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

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

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