[sqlite] porting sqlite3 to embeded os-----lock question
I am porting sqlite3 to the embeded os,such as threadx,nucleus,ect.. I am writing the file such as os_threadx.c,os_nucleus.c according to the os_win.c,os_unix.c. I have read the os_win.c and find that there is a switcher OS_WINCE in the struct winFile. Is this mean the windows platform don't need the function such as share memory(CreateFileMappingW,MapViewOfFile) inside the OS_WINCE swither? whether I should realize the share memory lock function in the embeded os? I have find there is not a direct way similar to the windows share memory and the interface funcitons. It seems difficult to simulate the share memory funciton and it's lock function in my embeded os. Does it mean I must realize it. or the porting will fail. another question: There is also a little difficult to realize the sqlite3WinThreadSpecificData function to get the thread information, Is this also must realize ? thanks a lot. allen.zhang
[sqlite] A suggestion
As a suggestion, and even in the risk to abuse of Mr Hipp's patience. Would it be possible to include in the command-line program (sqlite3.exe) the ability to edit, an repeat at least the five or six last commands, as in Linux?. Is to say with up-arrow and down-arrow. I believe it would be too helpful. A.J.Millan ZATOR Systems - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] perfomance degradation for expr foo = X or bar =X
Hello All, Im using SQLite-3.3.17. My table is: CREATE TABLE town_log ( id integer NOT NULL PRIMARY KEY AUTOINCREMENT, town_id int, stamp_id int, old_player_id int, new_player_id int, type int ); CREATE INDEX tl_np_id on town_log(new_player_id); CREATE INDEX tl_op_id on town_log(old_player_id); CREATE INDEX tl_st_id on town_log(stamp_id); CREATE INDEX tl_tw_id on town_log(town_id); CREATE INDEX tl_type on town_log(type); And I'm trying to execute this query: SELECT type, stamp_id, old_player_id, new_player_id FROM town_log WHERE old_player_id = $ID OR new_player_id = $ID ORDER BY stamp_id DESC; This query works really slowly and i don't know why :/ For example, the same result by another QUERY work much faster! SELECT type, stamp_id, old_player_id, new_player_id FROM town_log WHERE old_player_id = $ID UNION SELECT type, stamp_id, old_player_id, new_player_id FROM town_log WHERE new_player_id = %d ORDER BY stamp_id DESC; Timing by http://katastrophos.net/andre/blog/2007/01/04/sqlite-simple-timing-profiler-patch/ shows this times: 1st query: 0.250 s. 2st query: 0.000 s. PS. ANALYZE do not help to solve this problem. I think problem(?) in query optimizer. -- Biomechanical Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: perfomance degradation for expr foo = X or bar =X
Im simplify environment: CREATE TABLE tbl( id integer NOT NULL PRIMARY KEY AUTOINCREMENT, n1 int, n2 int ); CREATE INDEX idx1 on tbl(n1); CREATE INDEX idx2 on tbl(n2); sqlite select count(*) from tbl; 63026 1 query: SELECT id, n1, n2 FROM tbl WHERE n1 = $I OR n2 = $I ORDER BY id DESC; 2 query: SELECT id, n1, n2 FROM tbl WHERE n1 = $I UNION SELECT id, n1, n2 FROM tbl WHERE n2 = $I ORDER BY id DESC; Timing: 1: 0.080 s. 2: 0.000 s. Still big degradation. -- Biomechanical Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] A suggestion
On 9-mei-2007, at 11:06, A.J.Millan wrote: As a suggestion, and even in the risk to abuse of Mr Hipp's patience. Would it be possible to include in the command-line program (sqlite3.exe) the ability to edit, an repeat at least the five or six last commands, as in Linux?. Is to say with up-arrow and down-arrow. I believe it would be too helpful. Arrow up and down work fine for me, I'm not sure what your actual request is then? Cheers, Peter. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] perfomance degradation for expr foo = X or bar =X
On 9-mei-2007, at 11:28, bash wrote: SELECT type, stamp_id, old_player_id, new_player_id FROM town_log WHERE old_player_id = $ID OR new_player_id = $ID ORDER BY stamp_id DESC; This query works really slowly and i don't know why :/ For example, the same result by another QUERY work much faster! SELECT type, stamp_id, old_player_id, new_player_id FROM town_log WHERE old_player_id = $ID UNION SELECT type, stamp_id, old_player_id, new_player_id FROM town_log WHERE new_player_id = %d ORDER BY stamp_id DESC; Hello Humanoid, UNION performing much better than an equivalent query with OR is a known 'limitation' in many database systems, including MySQL and SQLite. Page 52 of http://www.sqlite.org/php2004/slides-all.html explicitly recommends to use UNION here. Cheers, Peter. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: perfomance degradation for expr foo = X or bar =X
On Wed, May 09, 2007 at 14:03:54 +0400, bash wrote: Im simplify environment: CREATE TABLE tbl( id integer NOT NULL PRIMARY KEY AUTOINCREMENT, n1 int, n2 int ); CREATE INDEX idx1 on tbl(n1); CREATE INDEX idx2 on tbl(n2); sqlite select count(*) from tbl; 63026 1 query: SELECT id, n1, n2 FROM tbl WHERE n1 = $I OR n2 = $I ORDER BY id DESC; 2 query: SELECT id, n1, n2 FROM tbl WHERE n1 = $I UNION SELECT id, n1, n2 FROM tbl WHERE n2 = $I ORDER BY id DESC; Timing: 1: 0.080 s. 2: 0.000 s. From http://www.sqlite.org/optoverview.html section 6.0: Each table in the FROM clause of a query can use at most one index... So the first query can't benefit from both idx1 and idx2. You may use EXPLAIN QUERY PLAN SELECT ... to see what indexes will be used. -- Tomash Brechko - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] A suggestion
In old versions it work... But in new versions (3.* I think) its not working! On Wed, 2007-05-09 at 12:11 +0200, Peter van Dijk wrote: On 9-mei-2007, at 11:06, A.J.Millan wrote: As a suggestion, and even in the risk to abuse of Mr Hipp's patience. Would it be possible to include in the command-line program (sqlite3.exe) the ability to edit, an repeat at least the five or six last commands, as in Linux?. Is to say with up-arrow and down-arrow. I believe it would be too helpful. Arrow up and down work fine for me, I'm not sure what your actual request is then? Cheers, Peter. - To unsubscribe, send email to [EMAIL PROTECTED] - __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] A suggestion
Sorry!! Although there are two other boxes around; one with XP and the other with Vista, for test purposes, actually I still use a third, with my old Windows 98SE and a utility, DOSKEY.COM, who emulates the Linux behavior in the Windows shell (who natively does not do that), but when I use it with sqlite3.exe that ability is lost. By the way, after this response, I have been looking in the XP box and there are a doskey.exe, but does not work in that way, but when try with sqlite3 in it, that works fine!! Perhaps that would make me jump to that system despite my reluctance :-) A.J.Millan ZATOR Systems - Original Message - From: Peter van Dijk [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Wednesday, May 09, 2007 12:11 PM Subject: Re: [sqlite] A suggestion On 9-mei-2007, at 11:06, A.J.Millan wrote: As a suggestion, and even in the risk to abuse of Mr Hipp's patience. Would it be possible to include in the command-line program (sqlite3.exe) the ability to edit, an repeat at least the five or six last commands, as in Linux?. Is to say with up-arrow and down-arrow. I believe it would be too helpful. Arrow up and down work fine for me, I'm not sure what your actual request is then? Cheers, Peter. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] perfomance degradation for expr foo = X or bar =X
On Wed, 9 May 2007 12:23:14 +0200 Peter van Dijk [EMAIL PROTECTED] wrote: On 9-mei-2007, at 11:28, bash wrote: SELECT type, stamp_id, old_player_id, new_player_id FROM town_log WHERE old_player_id = $ID OR new_player_id = $ID ORDER BY stamp_id DESC; This query works really slowly and i don't know why :/ For example, the same result by another QUERY work much faster! SELECT type, stamp_id, old_player_id, new_player_id FROM town_log WHERE old_player_id = $ID UNION SELECT type, stamp_id, old_player_id, new_player_id FROM town_log WHERE new_player_id = %d ORDER BY stamp_id DESC; Hello Humanoid, UNION performing much better than an equivalent query with OR is a known 'limitation' in many database systems, including MySQL and SQLite. Page 52 of http://www.sqlite.org/php2004/slides-all.html explicitly recommends to use UNION here. Cheers, Peter. Thanks a lot! I don't know that information :) Moreover this papers is really interesting. But i don't understand about indexes this: http://www.sqlite.org/php2004/slides-all.html Page 54 of 63 For example: CREATE TABLE ex1( id INTEGER PRIMARY KEY, x , y ); CREATE INDEX idx1 ON ex1(x); CREATE INDEX idx2 ON ex1(y); SELECT * FROM ex1 WHERE x'abc' AND y'abc'; In this form only one indexes will be used, why not both? For example by idx1 we can get set1 of ROWIDs which is satisfy x 'abc' term, by idx2 we can get set2 of ROWIDs which is satisfy y'abc' term and then just union set1 with set2. So next we should just extracts rows with ROWIDS in this union -- Biomechanical Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: perfomance degradation for expr foo = X or bar =X
On Wed, 9 May 2007 14:24:27 +0400 Tomash Brechko [EMAIL PROTECTED] wrote: On Wed, May 09, 2007 at 14:03:54 +0400, bash wrote: Im simplify environment: CREATE TABLE tbl( id integer NOT NULL PRIMARY KEY AUTOINCREMENT, n1 int, n2 int ); CREATE INDEX idx1 on tbl(n1); CREATE INDEX idx2 on tbl(n2); sqlite select count(*) from tbl; 63026 1 query: SELECT id, n1, n2 FROM tbl WHERE n1 = $I OR n2 = $I ORDER BY id DESC; 2 query: SELECT id, n1, n2 FROM tbl WHERE n1 = $I UNION SELECT id, n1, n2 FROM tbl WHERE n2 = $I ORDER BY id DESC; Timing: 1: 0.080 s. 2: 0.000 s. From http://www.sqlite.org/optoverview.html section 6.0: Each table in the FROM clause of a query can use at most one index... So the first query can't benefit from both idx1 and idx2. You may use EXPLAIN QUERY PLAN SELECT ... to see what indexes will be used. If i understand right from previous post by Peter there will be not used any indexes because of OR. -- Biomechanical Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Update callback and REPLACE
Hi all, I have a question about an update callback. It's supposed to be called for every row that's inserted, updated and deleted and it works fine. Now there's a little thing calles INSERT OR UPDATE (and its friends) which removes conflicting rows before the INSERT. The thing is, the callback gets called for the row inserted, but NOT for the row deleted. Is this expected behaviour? I'm depending on the callback to notify me of *all* changes and this one missing row is not something I like :) -KS - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] perfomance degradation for expr foo = X or bar =X
On Wed, May 09, 2007 at 16:34:57 +0400, bash wrote: On Wed, 9 May 2007 14:24:27 +0400 Tomash Brechko [EMAIL PROTECTED] wrote: From http://www.sqlite.org/optoverview.html section 6.0: Each table in the FROM clause of a query can use at most one index... So the first query can't benefit from both idx1 and idx2. You may use EXPLAIN QUERY PLAN SELECT ... to see what indexes will be used. If i understand right from previous post by Peter there will be not used any indexes because of OR. I think those slides are a bit outdated. On the same http://www.sqlite.org/optoverview.html page the section 3.0: The OR optimization says that the query from the slide 52 SELECT * FROM ex4 WHERE x=5 OR x=7; will be rewritten as SELECT * FROM ex4 WHERE x IN (5, 7); and IN can use indexes. But x=5 OR y=7 (i.e. conditions on _different_ columns) can't be rewritten that way---exactly your situation. On Wed, May 09, 2007 at 16:32:34 +0400, bash wrote: SELECT * FROM ex1 WHERE x'abc' AND y'abc'; In this form only one indexes will be used, why not both? One index per table rule. At first glance it seems like SQLite could use at least one index for x=5 OR y=7 case too, but there is no point in that, as the other part of the OR would require full table scan anyway. And for the solution with the UNION, sqlite explain query plan ... SELECT id, n1, n2 ... FROM tbl ... WHERE n1 = $I ... UNION ... SELECT id, n1, n2 ... FROM tbl ... WHERE n2 = $I ... ORDER BY id DESC; 0|0|TABLE tbl WITH INDEX idx1 0|0|TABLE tbl WITH INDEX idx2 both indexes are used. -- Tomash Brechko - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] perfomance degradation for expr foo = X or bar =X
On Wed, 9 May 2007 17:29:29 +0400 Tomash Brechko [EMAIL PROTECTED] wrote: On Wed, May 09, 2007 at 16:32:34 +0400, bash wrote: SELECT * FROM ex1 WHERE x'abc' AND y'abc'; In this form only one indexes will be used, why not both? One index per table rule. At first glance it seems like SQLite could use at least one index for x=5 OR y=7 case too, but there is no point in that, as the other part of the OR would require full table scan anyway. Why full table scan? :/ SQLite can takes set (1) of rowid by ex(x) index for X=5. Then takes another set (2) of rowid by ex(y) for Y=7. Then SQLite need only to union this two set (1) and (2). Final SQLite should returns rows where rowid in (set1 union set2). And for the solution with the UNION, sqlite explain query plan ... SELECT id, n1, n2 ... FROM tbl ... WHERE n1 = $I ... UNION ... SELECT id, n1, n2 ... FROM tbl ... WHERE n2 = $I ... ORDER BY id DESC; 0|0|TABLE tbl WITH INDEX idx1 0|0|TABLE tbl WITH INDEX idx2 Yep... Im using now this construction. -- Biomechanical Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] perfomance degradation for expr foo = X or bar =X
Tomash Brechko [EMAIL PROTECTED] wrote: sqlite explain query plan ... SELECT id, n1, n2 ... FROM tbl ... WHERE n1 = $I ... UNION ... SELECT id, n1, n2 ... FROM tbl ... WHERE n2 = $I ... ORDER BY id DESC; 0|0|TABLE tbl WITH INDEX idx1 0|0|TABLE tbl WITH INDEX idx2 Correct. Notice, however, that the UNION is not strictly equivalent to the original query. The UNION query above gives the same results as: SELECT DISTINCT id, n1, n2 FROM tbl WHERE n1=$I OR n2=$I; Perhaps the added DISTINCT will make no difference in the output. If so, then the UNION is the best way to go. But if DISTINCT will combine records that you do not want to be combined, then you might consider rewriting the query as follows: SELECT id, n1, n2 FROM tbl WHERE rowid in ( SELECT rowid FROM tbl WHERE n1=$I UNION SELECT rowid FROM tbl WHERE n2=$I ) Note that some client/server database engines (ex: PostgreSQL and I think also firebird) will automatically rewrite the original query into something logically similar to my second example above. But the query optimizer in SQLite does not attempt to be quite that clever. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] perfomance degradation for expr foo = X or bar =X
On Wed, May 09, 2007 at 17:45:52 +0400, bash wrote: One index per table rule. At first glance it seems like SQLite could use at least one index for x=5 OR y=7 case too, but there is no point in that, as the other part of the OR would require full table scan anyway. Why full table scan? :/ SQLite can takes set (1) of rowid by ex(x) index for X=5. Then takes another set (2) of rowid by ex(y) for Y=7. Then SQLite need only to union this two set (1) and (2). Final SQLite should returns rows where rowid in (set1 union set2). You should read it the following way: SQLite can't use two indexes per table, and using only one index is pointless, hence no index is used at all. So your question is actually why SQLite uses at most one index per table?. My guess is that the benefits are out-weighted by the implementation complexity. -- Tomash Brechko - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] perfomance degradation for expr foo = X or bar =X
On Wed, 9 May 2007 18:13:07 +0400 Tomash Brechko [EMAIL PROTECTED] wrote: On Wed, May 09, 2007 at 17:45:52 +0400, bash wrote: One index per table rule. At first glance it seems like SQLite could use at least one index for x=5 OR y=7 case too, but there is no point in that, as the other part of the OR would require full table scan anyway. Why full table scan? :/ SQLite can takes set (1) of rowid by ex(x) index for X=5. Then takes another set (2) of rowid by ex(y) for Y=7. Then SQLite need only to union this two set (1) and (2). Final SQLite should returns rows where rowid in (set1 union set2). You should read it the following way: SQLite can't use two indexes per table, and using only one index is pointless, hence no index is used at all. So your question is actually why SQLite uses at most one index per table?. My guess is that the benefits are out-weighted by the implementation complexity. Oh... so this is implementation limitation. Im currently thinking about this table: CREATE TABLE map ( x int, y int, name char ); CREATE INDEX map_x ON map(x); CREATE INDEX map_y ON map(y); And query for it will be something like this (circle): SELECT name FROM map WHERE (point_x - x)^2 + (point_y -y)^2 R^2; How SQLite will works? Is there any benefit in indexes? -- Biomechanical Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] perfomance degradation for expr foo = X or bar =X
bash [EMAIL PROTECTED] wrote: Oh... so this is implementation limitation. Im currently thinking about this table: CREATE TABLE map ( x int, y int, name char ); CREATE INDEX map_x ON map(x); CREATE INDEX map_y ON map(y); And query for it will be something like this (circle): SELECT name FROM map WHERE (point_x - x)^2 + (point_y -y)^2 R^2; How SQLite will works? Is there any benefit in indexes? You need an R-Tree index to do something like this. The public-domain version of SQLite only supports B-Tree indices. So, no, indices are not going to help you here. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] perfomance degradation for expr foo = X or bar =X
I wonder if it would be beneficial to add an additional where clause which can prefilter the data so you only need to perform the full calculation on a subset of records. I haven't done the math, but let's supposed that point_x is 10 and that for any result of your long calculation to be true, then x must be between 5 and 15, then you can use the where clause WHERE X BETWEEN 5 AND 15 AND (point_x - x)^2 + (point_y -y)^2 R^2; If you can make this type of pre-calculation and get a proper range for X then it can reduce the number of records that need to be checked. I'm assuming SQLite will use an index on X for BETWEEN (I don't know for sure). Also you should do testing to be sure this query really is faster in practice--I'm only theorizing here. :-) HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: bash [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 09, 2007 10:33 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] perfomance degradation for expr foo = X or bar =X On Wed, 9 May 2007 18:13:07 +0400 Tomash Brechko [EMAIL PROTECTED] wrote: Im currently thinking about this table: CREATE TABLE map ( x int, y int, name char ); CREATE INDEX map_x ON map(x); CREATE INDEX map_y ON map(y); And query for it will be something like this (circle): SELECT name FROM map WHERE (point_x - x)^2 + (point_y -y)^2 R^2; How SQLite will works? Is there any benefit in indexes? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Conditional table select
Vitali Lovich wrote: The solution I came up with is: SELECT coalesce( (SELECT field1 FROM tbl1 WHERE key = $key), (SELECT field1 FROM tbl2 WHERE key = $key), (SELECT field1 FROM tbl3 WHERE key = $key)) , field2 FROM tbl1 WHERE key = $key; However, if coalesce works the way I think it does, then it'll do early evaluation and stop at the first non null parameter. Vitali, Coalesce does not work the way you suggest. It is an SQL function and like all functions in SQLite it is passed all its arguments during the call. So SQLite has to evaluate all the select expressions to generate the arguments before calling coalesce. The coalesce function simply returns the first non null argument. You could combine the tables like this: select field1 from ( select 1 as tbl, field1 from tbl1 where key = :key and field1 not null union select 2 as tbl, field1 from tbl2 where key = :key and field1 not null union select 3 as tbl, field1 from tbl3 where key = :key and field1 not null ) order by tbl limit 1 Or you could use a case expression to execute the lookups only if needed, but at the expense of repeating the succesful lookup twice. select case when (select field1 from tbl1 where key = :key) not null then (select field1 from tbl1 where key = :key) else case when (select field1 from tbl2 where key = :key) not null then (select field1 from tbl2 where key = :key) else case when (select field1 from tbl3 where key = :key) not null then (select field1 from tbl3 where key = :key) else null end end end as field1; This could be optimized somewhat if SQLite supported the WITH clause for named subexpressions. This would allow the lookup queries to be executed only once to build an internal temporary table. If the optimizer is smart enough, it could delay the execution of the temp table creation until the temp table is referenced. This would do the lookups only once, and only if needed. with tbl1_f1 (field1) as (select field1 from tbl1 where key = :key) tbl2_f1 (field1) as (select field1 from tbl2 where key = :key) tbl3_f1 (field1) as (select field1 from tbl3 where key = :key) select case when (select field1 from tbl1_f1) not null then (select field1 from tbl1_f1) else case when (select field1 from tbl2_f1) not null then (select field1 from tbl2_f1) else case when (select field1 from tbl3_f1) not null then (select field1 from tbl3_f1) else null end end end as field1; The question that I have to ask is why is the data separated into these three tables? Your life would be a whole lot easier if you combined the three tables into one with an additional column that indicated the source of the original data. Instead of this: create table tbl1 (key primary key, field1); create table tbl2 (key primary key, field1); create table tbl3 (key primary key, field1); You could do this: create table tbl_all (tbl, key, field1, primary key(tbl, key)); insert into tbl_all select 1 as tbl, key, field1 from tbl1 union select 2 as tbl, key, field1 from tbl2 union select 3 as tbl, key, field1 from tbl3 Then your query becomes select field1 from tbl_all where key = :key and field1 not null order by tbl limit 1 If you don't want to create an actual table with the combined data you could get much the same effect using a view. create view view_all as select 1 as tbl, key, field1 from tbl1 union select 2 as tbl, key, field1 from tbl2 union select 3 as tbl, key, field1 from tbl3 select field1 from view_all where key = :key and field1 not null order by tbl limit 1 The drawback to this approach is that it will actually build the entire table defined by the view as a temporary table before executing a query that references the view. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] perfomance degradation for expr foo = X or bar =X
[EMAIL PROTECTED] wrote:Note that some client/server database engines (ex: PostgreSQL and I think also firebird) will automatically rewrite the original query into something logically similar to my second example above. But the query optimizer in SQLite does not attempt to be quite that clever. -- D. Richard Hipp I think that is a good thing that sqlite isn't that clever! I've seen too many cases of advanced optimizers such as Oracle totally mess up the execution plans for complex query operations. There are simply too many choices for the optimizer to pick the best plan. Ken
Re: [sqlite] perfomance degradation for expr foo = X or bar =X
On Wed, 09 May 2007 14:45:33 + [EMAIL PROTECTED] wrote: bash [EMAIL PROTECTED] wrote: Oh... so this is implementation limitation. Im currently thinking about this table: CREATE TABLE map ( x int, y int, name char ); CREATE INDEX map_x ON map(x); CREATE INDEX map_y ON map(y); And query for it will be something like this (circle): SELECT name FROM map WHERE (point_x - x)^2 + (point_y -y)^2 R^2; How SQLite will works? Is there any benefit in indexes? You need an R-Tree index to do something like this. The public-domain version of SQLite only supports B-Tree indices. So, no, indices are not going to help you here. Thanks for advice. -- Biomechanical Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] perfomance degradation for expr foo = X or bar =X
On Wed, 9 May 2007 11:08:26 -0400 Samuel R. Neff [EMAIL PROTECTED] wrote: I wonder if it would be beneficial to add an additional where clause which can prefilter the data so you only need to perform the full calculation on a subset of records. I haven't done the math, but let's supposed that point_x is 10 and that for any result of your long calculation to be true, then x must be between 5 and 15, then you can use the where clause WHERE X BETWEEN 5 AND 15 AND (point_x - x)^2 + (point_y -y)^2 R^2; Yes... it should help a little :) -- Biomechanical Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] perfomance degradation for expr foo = X or bar =X
bash wrote: Why full table scan? :/ SQLite can takes set (1) of rowid by ex(x) index for X=5. Then takes another set (2) of rowid by ex(y) for Y=7. Then SQLite need only to union this two set (1) and (2). Final SQLite should returns rows where rowid in (set1 union set2). I think you mean intersection where you have used union. SQLite won't optimize the query this way, but you can do it manually. Instead of select * from ex4 where x = 5 or y = 7; You can do this select * from ex4 where rowid in ( select rowid from ex4 where x = 5 intersect select rowid from ex4 where y = 7 ); The intersect operation allows each of the sub-selects to be executed using an independent index, and the outer select uses the implicit index on the rowid. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] perfomance degradation for expr foo = X or bar =X
Dennis Cote [EMAIL PROTECTED] wrote: Why full table scan? :/ SQLite can takes set (1) of rowid by ex(x) index for X=5. Then takes another set (2) of rowid by ex(y) for Y=7. Then SQLite need only to union this two set (1) and (2). Final SQLite should returns rows where rowid in (set1 union set2). I think you mean intersection where you have used union. SQLite won't optimize the query this way, but you can do it manually. Instead of select * from ex4 where x = 5 or y = 7; You can do this select * from ex4 where rowid in ( select rowid from ex4 where x = 5 intersect select rowid from ex4 where y = 7 ); The intersect operation allows each of the sub-selects to be executed using an independent index, and the outer select uses the implicit index on the rowid. INTERSECT would give you x=5 AND y=7. For x=5 OR y=7 you want UNION. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] perfomance degradation for expr foo = X or bar =X
On Wed, May 09, 2007 at 14:45:33 +, [EMAIL PROTECTED] wrote: You need an R-Tree index to do something like this. The public-domain version of SQLite only supports B-Tree indices. So, no, indices are not going to help you here. Alternatively to R-tree index, you may simply partition the space into NxM cells, with, say, left and bottom border belonging to the cell itself (while right and upper borders belonging to the right and upper cells as their left and bottom borders respectively), and enumerate these cells row-by-row like 10|11|12|13|14 ---+--+--+--+--- 5| 6| 7| 8| 9 ---+--+--+--+--- 0| 1| 2| 3| 4 This way every point belongs to exactly one cell. Then you create CREATE TABLE map ( x INTEGER, y INTEGER, name TEXT, cell_no INTEGER ); CREATE INDEX map_cell_no ON map (cell_no); When inserting a point, you compute its cell_no (something like cell_no(x, y) = y / cell_height * cells_in_row + x / cell_width; ). When doing a region query, you compute a set of cell numbers that intersect with a query window, accumulate them in a (memory) table selected_cells, and then do SELECT map.* FROM mem.selected_cells sc CROSS JOIN map ON sc.cell_no = map.cell_no; Better yet to compute two sets: those cells that reside completely within the query window, and those that intersect window border. Points from the latter cells should be filtered further. Reasonable cell dimensions based on typical query window size and points distribution will give quite reasonable performance. -- Tomash Brechko - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sql stack using sqlite
Id like to get your ideas on implementing a stack using sql tables. table a, contains references to b table b contains refernce to a table c contains delete entries for A (but b must also be purged!) My processing forces me to load all of a,b and c. There may be cases where table C indicates a complete deletion for table A. As a special case there is another table D that indicates a complete delete. In this instance I can delete by another unique Id that is contained in all of the tables but ommitted for brevity. create table a ( id integer, ref integer, cnt integer ); create table b ( id integer, ref integer, val text ); create table c ( id integer, cnt integer ); insert into a values (1,32,5); insert into b values (11,32,'first data item'); insert into c values (1,5) ; insert into a values (1,33,5); insert into b values (11,33,'second data item'); insert into c values (1,5) ; insert into a values (1,34,5); insert into b values (11,34,'third data item'); After processing, Id like to be left with the following: a ( 1, 34,5) b (11, 34, 'third data item') This is easily implemented in a memory stack. but I'm not sure how to implement using sql. thanks for any ideas. Ken
Re: [sqlite] perfomance degradation for expr foo = X or bar =X
On Wed, 9 May 2007 21:00:46 +0400 Tomash Brechko [EMAIL PROTECTED] wrote: On Wed, May 09, 2007 at 14:45:33 +, [EMAIL PROTECTED] wrote: You need an R-Tree index to do something like this. The public-domain version of SQLite only supports B-Tree indices. So, no, indices are not going to help you here. Alternatively to R-tree index, you may simply partition the space into NxM cells, with, say, left and bottom border belonging to the cell itself (while right and upper borders belonging to the right and upper cells as their left and bottom borders respectively), and enumerate these cells row-by-row like 10|11|12|13|14 ---+--+--+--+--- 5| 6| 7| 8| 9 ---+--+--+--+--- 0| 1| 2| 3| 4 This way every point belongs to exactly one cell. Then you create CREATE TABLE map ( x INTEGER, y INTEGER, name TEXT, cell_no INTEGER ); CREATE INDEX map_cell_no ON map (cell_no); When inserting a point, you compute its cell_no (something like cell_no(x, y) = y / cell_height * cells_in_row + x / cell_width; ). When doing a region query, you compute a set of cell numbers that intersect with a query window, accumulate them in a (memory) table selected_cells, and then do SELECT map.* FROM mem.selected_cells sc CROSS JOIN map ON sc.cell_no = map.cell_no; Better yet to compute two sets: those cells that reside completely within the query window, and those that intersect window border. Points from the latter cells should be filtered further. Reasonable cell dimensions based on typical query window size and points distribution will give quite reasonable performance. Interesting idea. I'll try to test it. Dimension for my map is 800x800 and number of points 50,000+. -- Biomechanical Artificial Sabotage Humanoid - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] perfomance degradation for expr foo = X or bar =X
[EMAIL PROTECTED] wrote: INTERSECT would give you x=5 AND y=7. For x=5 OR y=7 you want UNION. Oops, yes of course. I was thinking of the higher level problem with circles where intersection could be used to find a small subset of the table that would then be scanned to locate the points in the circle using the exact distance calculation. select * from ex4 where rowid in ( select rowid from ex4 where x between :x_center - :radius and :x_center + :radius intersect select rowid from ex4 where y between :y_center - :radius and :y_center + :radius ) This will find points in a circumscribed square around the center of the circle. The distance calculation would eliminate those points outside the circle (i.e. the points in the corners of the squares). Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Variable substitution for table names
Hi all, I have a complex query that I want to run often and store the results to a temporary table. I'd like to compile a statement that looks something like: CREATE TEMP TABLE ? AS SELECT * FROM data WHERE value = ?; so that I can have multiple threads accessing the results of the query in various temp tables simultaneously. The above gives me a parse error on the first ? for the temp table name. Is this a bug, or something that SQLite can't support? I have also tried compiling a simple query SELECT * FROM ?; which also gives a parse error. -Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Variable substitution for table names
Jeff Hamilton [EMAIL PROTECTED] wrote: I have a complex query that I want to run often and store the results to a temporary table. I'd like to compile a statement that looks something like: CREATE TEMP TABLE ? AS SELECT * FROM data WHERE value = ?; so that I can have multiple threads accessing the results of the query in various temp tables simultaneously. The above gives me a parse error on the first ? for the temp table name. Is this a bug, or something that SQLite can't support? Something that SQLite can't support. A parameter is only allowed where an expression can appear. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Variable substitution for table names
Jeff Hamilton [EMAIL PROTECTED] wrote: Hi all, I have a complex query that I want to run often and store the results to a temporary table. I'd like to compile a statement that looks something like: CREATE TEMP TABLE ? AS SELECT * FROM data WHERE value = ?; so that I can have multiple threads accessing the results of the query in various temp tables simultaneously. The above gives me a parse error on the first ? for the temp table name. Is this a bug, or something that SQLite can't support? I have also tried compiling a simple query SELECT * FROM ?; which also gives a parse error. A host parameter (a.k.a. ?) can only be used in places where it is legal to put a literal value. You cannot use it as a place holder for program structure. Changing a table in a query can result it radically different compiled code. (Use EXPLAIN to see this.) It is not feasible to recompile the SQL statement in response to a bind(). -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] adding a default ESCAPE character to LIKE
Sorry if this isn't the proper channel, but I couldn't figure out where to submit a patch. See below for a patch to src/func.c that alters LIKE such that it has a default escape character of '\'. That is, LIKE behaves by default as if ESCAPE '\' were appended. Both MySQL and PostgreSQL behave this way. The current default behavior (no escape) can be done by using ESCAPE ''. --Jeff Mitchell I have never submitted anything to SQLite before, so here is my copyright statement. The author or authors of this code dedicate any and all copyright interest in this code to the public domain. We make this dedication for the benefit of the public at large and to the detriment of our heirs and successors. We intend this dedication to be an overt act of relinquishment in perpetuity of all present and future rights this code under copyright law. 520c520 int escape = 0; --- int escape = sqlite3ReadUtf8(\\); /* default to '\' */ 522c522,523 /* The escape character string must consist of a single UTF-8 character. --- /* The escape character string must be either the empty string (disabling ** escaping) or a single UTF-8 character. 526c527 if( sqlite3utf8CharLen((char*)zEsc, -1)!=1 ){ --- if( sqlite3utf8CharLen((char*)zEsc, -1) 1 ){ 528c529 ESCAPE expression must be a single character, -1); --- ESCAPE expression must be the empty string or a single character, -1); - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] A suggestion
That program does have the capability, but may not be implemented that way on Windows. Why not make the change yourself? A.J.Millan wrote: As a suggestion, and even in the risk to abuse of Mr Hipp's patience. Would it be possible to include in the command-line program (sqlite3.exe) the ability to edit, an repeat at least the five or six last commands, as in Linux?. Is to say with up-arrow and down-arrow. I believe it would be too helpful. A.J.Millan ZATOR Systems - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] A suggestion
On Wed, 9 May 2007, John Stanton wrote: That program does have the capability, but may not be implemented that way on Windows. Why not make the change yourself? A.J.Millan wrote: As a suggestion, and even in the risk to abuse of Mr Hipp's patience. Would it be possible to include in the command-line program (sqlite3.exe) the ability to edit, an repeat at least the five or six last commands, as in Linux?. Is to say with up-arrow and down-arrow. I believe it would be too helpful. That, I believe, is a function of the shell, not the application using it. For example, the command history available in bash is not present -- at least, was not present -- in sh. Command history, IIRC, was a feature of the csh, and bash (the Bourne Again Shell) took the best features of sh, csh, and the Textronics version tcsh. Years ago, about a decade, when I had PC DOS 7.0 installed along with linux, I discovered that the DOS shell allowed tab completions. I don't recall if it had the history recall, too. Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Help wiith SQL - first row of each group
* Ed Pasma [EMAIL PROTECTED] [2007-05-07 10:28]: This solution may is tricky but has occasoinaly helped me. It is written here dedicated for the example data. For real data the leftpadding should likely be increased to the content of the sorting key. Also the result may need to be converted to the expected data type, it has now become text. SELECT g, SUBSTR (MAX (SUBSTR (' ' || p, -2, 2) || v), 3, 1) v FROM t GROUP BY g ; Heh, very cool. A Guttman-Rosler transform [1] in SQL. [1] http://www.sysarch.com/Perl/sort_paper.html Regards, -- Aristotle Pagaltzis // http://plasmasturm.org/ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] The need for sqlite3_encode_binary and sqlite3_decode_binary
It would appear that by using bind variables instead of '%Q' in the SQL string, the need for sqlite3_encode_binary and sqlite3_decode_binary is eliminated. Is that indeed the case? --andy
[sqlite] ATTACH and sqlite3_open()
Can one attach multiple database files using C API? The only example I've seen is CLI-based (using ATTACH). The Owens book seems to hint that it's possible to do the same trick using sqlite3_open()... Quote Pgs. 206-207: This is more of a connection handle than a database handle since it is possible to attach multiple databases to a single connection. However, this connection still represents exactly one transaction context regardless of how many databases are attached. But no example is given. Here's mine: sqlite3 *db; if (sqlite3_open(flash.db, db)) { fprintf(stderr, Can't open the database in the Flash file system\n); exit(2); } else if (sqlite3_open(ram.db, db)) { fprintf(stderr, Can't open the database in the RAM-disk file system\n); sqlite3_close(db); exit(2); } Should this work? If so, is sqlite3_close() smart enough to handle this situation? If not, how does sqlite3 (CLI) manage this feat? Thanks. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] ATTACH and sqlite3_open()
From the comments around the attach function, you'd have to execute a SQL statement attach database x as y KEY z. I assume that 'key Z' is for the encrypting version of SQLite3 distributed by drh. /* ** An SQL user-function registered to do the work of an ATTACH statement. The ** three arguments to the function come directly from an attach statement: ** ** ATTACH DATABASE x AS y KEY z ** ** SELECT sqlite_attach(x, y, z) ** ** If the optional KEY z syntax is omitted, an SQL NULL is passed as the ** third argument. */ --a On 5/9/07, Jon Scully [EMAIL PROTECTED] wrote: Can one attach multiple database files using C API? The only example I've seen is CLI-based (using ATTACH). The Owens book seems to hint that it's possible to do the same trick using sqlite3_open()... Quote Pgs. 206-207: This is more of a connection handle than a database handle since it is possible to attach multiple databases to a single connection. However, this connection still represents exactly one transaction context regardless of how many databases are attached. But no example is given. Here's mine: sqlite3 *db; if (sqlite3_open(flash.db, db)) { fprintf(stderr, Can't open the database in the Flash file system\n); exit(2); } else if (sqlite3_open(ram.db, db)) { fprintf(stderr, Can't open the database in the RAM-disk file system\n); sqlite3_close(db); exit(2); } Should this work? If so, is sqlite3_close() smart enough to handle this situation? If not, how does sqlite3 (CLI) manage this feat? Thanks. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] ATTACH and sqlite3_open()
On further inspection of your code fragment, it appears you aren't really using (extra) attached databases, but merely specifying an alternative file to use if the first file is not available. Calling sqlite3_close(...) will do the right thing, by closing the actual database that succeeded in opening. --andy On 5/9/07, Jon Scully [EMAIL PROTECTED] wrote: Can one attach multiple database files using C API? The only example I've seen is CLI-based (using ATTACH). The Owens book seems to hint that it's possible to do the same trick using sqlite3_open()... Quote Pgs. 206-207: This is more of a connection handle than a database handle since it is possible to attach multiple databases to a single connection. However, this connection still represents exactly one transaction context regardless of how many databases are attached. But no example is given. Here's mine: sqlite3 *db; if (sqlite3_open(flash.db, db)) { fprintf(stderr, Can't open the database in the Flash file system\n); exit(2); } else if (sqlite3_open(ram.db, db)) { fprintf(stderr, Can't open the database in the RAM-disk file system\n); sqlite3_close(db); exit(2); } Should this work? If so, is sqlite3_close() smart enough to handle this situation? If not, how does sqlite3 (CLI) manage this feat? Thanks. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] ATTACH and sqlite3_open()
Simpler than that. I merely want to attach to two databases (files). flash.db contains a set of tables that hold non-volatile data; ram.db contains a set of tables that is re-built (volatile) on re-boot -- but offers fast, read-only access. No table-name overlaps, of course. I want to access both sets of tables, seamlessly, as I should be able to do using ATTACH, at the command prompt, but do so using the C API. Just wondering how others do this (Using ATTACH? Using sqlite3_open()? Obviously I haven't looked very far into the sqlite3_open() code to see how it's put together, etc.). Thanks for the prompt reply. On 5/9/07, Andrew Finkenstadt [EMAIL PROTECTED] wrote: On further inspection of your code fragment, it appears you aren't really using (extra) attached databases, but merely specifying an alternative file to use if the first file is not available. Calling sqlite3_close(...) will do the right thing, by closing the actual database that succeeded in opening. --andy - To unsubscribe, send email to [EMAIL PROTECTED] -
AW: [sqlite] ATTACH and sqlite3_open()
Just call sqlite3_exec with the proper ATTACH as you would on the command line. (Of course you could also do a prepare/step/finalize, but for ATTACH sqlite3_exec is enough.) Example: sqlite3 *db = NULL; /* ... */ sqlite3_exec(db, ATTACH DATABASE 'filename' AS dbname, NULL, NULL, errmsg); Using SELECT sqlite_attach('filename','dbname',NULL) should also work - haven't tried it though. This should have the benefit that the arguments can be bound and the attach statement can be prepared. I'm not certain this is possible with the ATTACH syntax. I'll try it soon though. Mike -Ursprüngliche Nachricht- Von: Jon Scully [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 10. Mai 2007 01:18 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] ATTACH and sqlite3_open() Simpler than that. I merely want to attach to two databases (files). flash.db contains a set of tables that hold non-volatile data; ram.db contains a set of tables that is re-built (volatile) on re-boot -- but offers fast, read-only access. No table-name overlaps, of course. I want to access both sets of tables, seamlessly, as I should be able to do using ATTACH, at the command prompt, but do so using the C API. Just wondering how others do this (Using ATTACH? Using sqlite3_open()? Obviously I haven't looked very far into the sqlite3_open() code to see how it's put together, etc.). Thanks for the prompt reply. On 5/9/07, Andrew Finkenstadt [EMAIL PROTECTED] wrote: On further inspection of your code fragment, it appears you aren't really using (extra) attached databases, but merely specifying an alternative file to use if the first file is not available. Calling sqlite3_close(...) will do the right thing, by closing the actual database that succeeded in opening. --andy - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Longest real SQL statement
I'm looking for an upper bound on how big legitimate SQL statements handed to SQLite get to be. I'm not interested in contrived examples. I want to see really big SQL statements that are actually used in real programs. Big can be defined in several ways: * Number of bytes of text in the SQL statement. * Number of tokens in the SQL statement * Number of result columns in a SELECT * Number of terms in an expression If you are using really big SQL statements, please tell me about them. I'd like to see the actual SQL text if possible. But if your use is proprietary, please at least tell me how big your query is in bytes or tokens or columns or expression terms. Thanks. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Problem with SQLite Pecl extension for PHP4
I've gone through a few tutorials and can't seem to get this right. I've had my web host install the PECL extension and the following is what is in my phpinfo(); sqlite SQLite support enabled PECL Module version 1.0.3 $Id: sqlite.c,v 1.62.2.25 2004/07/10 12:25:33 wez Exp $ SQLite Library 2.8.14 SQLite Encoding iso8859 Directive Local Value Master Value sqlite.assoc_case 0 0 *** This is the PHP code I'm using: ?php $db = sqlite_open(/home/xxx/data/xxx.db); ? *** This is the error I'm getting: Warning: sqlite_open() [function.sqlite-open]: file is encrypted or is not a database in /home/xxx/public_html/test.php on line 140 *** What am I doing wrong? I can run queries all day long using the command line sqlite3 but I can't seem to get PHP to read the stinkin database! Any help would be GREATLY appreciated! Brandon Eley [EMAIL PROTECTED]
[sqlite] Re: Longest real SQL statement
I've recently been storing images in Sqlite datbases, and for various reasons don't want to use the normal blob mechanisms. Consequently, a 10mb JPG image file would be encoded as slightly larger than 10mb sqlite insert xx command. The size of the commands required by this method is limited only by the size of images I think is reasonable to store as pseudo-blobs. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Longest real SQL statement
Dave Dyer [EMAIL PROTECTED] wrote: I've recently been storing images in Sqlite datbases, and for various reasons don't want to use the normal blob mechanisms. Consequently, a 10mb JPG image file would be encoded as slightly larger than 10mb sqlite insert xx command. The use of sqlite3_bind_blob() for this is MUCH faster, and surely requires less code. May I ask for more detail on why you chose to generate SQL statements with huge blob literals? -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] A suggestion
Rich Shepard wrote: On Wed, 9 May 2007, John Stanton wrote: That program does have the capability, but may not be implemented that way on Windows. Why not make the change yourself? A.J.Millan wrote: As a suggestion, and even in the risk to abuse of Mr Hipp's patience. Would it be possible to include in the command-line program (sqlite3.exe) the ability to edit, an repeat at least the five or six last commands, as in Linux?. Is to say with up-arrow and down-arrow. I believe it would be too helpful. That, I believe, is a function of the shell, not the application using it. For example, the command history available in bash is not present -- at least, was not present -- in sh. Command history, IIRC, was a feature of the csh, and bash (the Bourne Again Shell) took the best features of sh, csh, and the Textronics version tcsh. Years ago, about a decade, when I had PC DOS 7.0 installed along with linux, I discovered that the DOS shell allowed tab completions. I don't recall if it had the history recall, too. Rich It would be a function of readline. I have not looked closely but I would imagine that to implement or emulate readline on Windows would achieve the function. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] ATTACH and sqlite3_open()
That worked as hoped. (Used your sqlite3_exec() version.) Thanks. On 5/9/07, Michael Ruck [EMAIL PROTECTED] wrote: Just call sqlite3_exec with the proper ATTACH as you would on the command line. (Of course you could also do a prepare/step/finalize, but for ATTACH sqlite3_exec is enough.) Example: sqlite3 *db = NULL; /* ... */ sqlite3_exec(db, ATTACH DATABASE 'filename' AS dbname, NULL, NULL, errmsg); Using SELECT sqlite_attach('filename','dbname',NULL) should also work - haven't tried it though. This should have the benefit that the arguments can be bound and the attach statement can be prepared. I'm not certain this is possible with the ATTACH syntax. I'll try it soon though. Mike -Ursprüngliche Nachricht- Von: Jon Scully [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 10. Mai 2007 01:18 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] ATTACH and sqlite3_open() Simpler than that. I merely want to attach to two databases (files). flash.db contains a set of tables that hold non-volatile data; ram.db contains a set of tables that is re-built (volatile) on re-boot -- but offers fast, read-only access. No table-name overlaps, of course. I want to access both sets of tables, seamlessly, as I should be able to do using ATTACH, at the command prompt, but do so using the C API. Just wondering how others do this (Using ATTACH? Using sqlite3_open()? Obviously I haven't looked very far into the sqlite3_open() code to see how it's put together, etc.). Thanks for the prompt reply. On 5/9/07, Andrew Finkenstadt [EMAIL PROTECTED] wrote: On further inspection of your code fragment, it appears you aren't really using (extra) attached databases, but merely specifying an alternative file to use if the first file is not available. Calling sqlite3_close(...) will do the right thing, by closing the actual database that succeeded in opening. --andy - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem with SQLite Pecl extension for PHP4
What user is httpd running as? Enter: ps aux | grep -v grep | grep httpd Does that user (typically 'nobody') have permission to access the file at your: /home/xxx/data/xxx.db ? On 5/9/07, Brandon Eley [EMAIL PROTECTED] wrote: I've gone through a few tutorials and can't seem to get this right. I've had my web host install the PECL extension and the following is what is in my phpinfo(); sqlite SQLite support enabled PECL Module version 1.0.3 $Id: sqlite.c,v 1.62.2.25 2004/07/10 12:25:33 wez Exp $ SQLite Library 2.8.14 SQLite Encoding iso8859 Directive Local Value Master Value sqlite.assoc_case 0 0 *** This is the PHP code I'm using: ?php $db = sqlite_open(/home/xxx/data/xxx.db); ? *** This is the error I'm getting: Warning: sqlite_open() [function.sqlite-open]: file is encrypted or is not a database in /home/xxx/public_html/test.php on line 140 *** What am I doing wrong? I can run queries all day long using the command line sqlite3 but I can't seem to get PHP to read the stinkin database! Any help would be GREATLY appreciated! Brandon Eley [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite3_step error code confusion
Ive been getting SQLITE_MISUSE errors after calling sqlite3_step one more time than required. Im using sqlite3_prepare_v2 so im expecting detailed error messages, but when i call sqlite3_errmsg all i seem to get is not an error. So my question is this. What is an error ? It seems a little confusing becuase there is a SQLITE_ERROR code, and also a number of other detailed codes which i assume represent forms of errors. The old version of sqlite3_step used to return SQLITE_ERROR, and then you would call sqlite3_reset to get the detailed error code. This implies that the other codes are in fact errors. Am i correct in assuming that SQLITE_MISUSE is a form of error ? Is there some subset of the error codes which work with sqlite3_errmsg ? I would have expected the error message that i got for SQLITE_MISUSE to be library used incorectly or some string other than not an error. Emerson - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem with SQLite Pecl extension for PHP4
There was a change of file structure with version 3.x.x and this is described in the documentation under Version 3 Overview. You are attempting to use Sqlite3 on a version 2.8.x database and the formats are not compatible. On Wed, 9 May 2007 19:42:06 -0400, Brandon Eley wrote: I've gone through a few tutorials and can't seem to get this right. I've had my web host install the PECL extension and the following is what is in my phpinfo(); sqlite SQLite support enabled PECL Module version1.0.3 $Id: sqlite.c,v 1.62.2.25 2004/07/10 12:25:33 wez Exp $ SQLite Library 2.8.14 SQLite Encodingiso8859 Directive Local Value Master Value sqlite.assoc_case 0 0 *** This is the PHP code I'm using: ?php $db = sqlite_open(/home/xxx/data/xxx.db); ? *** This is the error I'm getting: Warning: sqlite_open() [function.sqlite-open]: file is encrypted or is not a database in /home/xxx/public_html/test.php on line 140 *** What am I doing wrong? I can run queries all day long using the command line sqlite3 but I can't seem to get PHP to read the stinkin database! Any help would be GREATLY appreciated! Brandon Eley [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -