Re: [sqlite] Re: converting a mysql database
Dave Dyer wrote: > > I looked into this, and the actual problem is windows returning > a "access denied" error code when trying to recreate the journal > file immediately after deleting it. I can't find any documentation > that says create might fail for this reason, but filemon (from > systeminternals.com) says the status is "delete pending" in this > situation. So I think this is a genuine windows glitch, which > might be related to my having a fast, dual-core processor. > > In any case, it seems to be possible to paper over the problem > by waiting a few milliseconds and retrying the create. I have had weird problems in the past on a Windows platform with the deletion and re-creation of files. I have never been able to figure out what happened. I am not familiar with inner workings of SQLite but I can imagine that this will not be a problem anymore when you use a transaction instead of individual inserts. Regards, Arjen
Re: [sqlite] Unsupported SQL feature
> What are these constructs suppose to do? x all (select y from t where ...) is equivalent to not exists (select y from t where not (x y) and ...) Any and some are synonyms. x any (select y from t where ...) is equivalent to exists (select y from t where x y and ...) Any can be confusing because of its ambiguity in the English language. If I say 'Is x greater than any y in t?', almost everyone will say the answer is yes if x is greater than one or more y-values in t -- but if I say 'x is greater than any y in t', you may interpret that to mean that x is greater than every y-value in t. This has lead any and all to be deprecated for new code in some circles. Regards
Re: [sqlite] Unsupported SQL feature
On Sun, 06 Nov 2005 15:09:34 -0500, [EMAIL PROTECTED] wrote: > Alexander Kozlovsky <[EMAIL PROTECTED]> wrote: > > I just notice, SQLite don't support this type of queries: > > > > > > select * from t1 where a >= ALL (select b from t2) > > > > > > Of course, queries with ALL, ANY and SOME clauses may be reformulated, > > but this is part of SQL standard. > > What are these constructs suppose to do? [from the sql 1992 standard] 8.7 Function Specify a quantified comparison. Format ::= ::= | ::= ALL ::= SOME | ANY Syntax Rules 1) The shall be of the same degree as the result of the . 2) The data types of the values of the shall be respectively comparable to those of the columns of the . 3) The collating sequence for each pair of respective values in the is determined in the same manner as described in Subclause 8.2, "". Access Rules None. General Rules 1) Let R be the result of the and let T be the result of the . 2) The result of "R T" is derived by the application of the implied "R RT" to every row RT in T: Case: a) If T is empty or if the implied is true for every row RT in T, then "R T" is true. b) If the implied is false for at least one row RT in T, then "R T" is false. c) If the implied is true for at least one row RT in T, then "R T" is true. d) If T is empty or if the implied is false for every row RT in T, then "R T" is false. e) If "R T" is neither true nor false, then it is unknown. +---+-+ : Klint Gore: "Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+
Re: [sqlite] Unsupported SQL feature
Alexander Kozlovsky <[EMAIL PROTECTED]> wrote: > Hi! > > I just notice, SQLite don't support this type of queries: > > > select * from t1 where a >= ALL (select b from t2) > > > Of course, queries with ALL, ANY and SOME clauses may be reformulated, > but this is part of SQL standard. > > I use SQLite for teach SQL to students, and will be very happy > if such feature appears in next version of SQLite > What are these constructs suppose to do? -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Re: Re: Re: Request for comment: Proposed SQLite API changes
Eduardo wrote: You seem to think that the problem occurs when schema changes right in the middle of sqlite3_prepare call. This is not the case. The problem is that a prepared statement may sit around for a long time before it is actually used in a sqlite3_step call. At this point the engine may find out that the schema now differs from what it was when the statement was prepared, so the internal data structures are no longer valid. How do you propose to handle this situation, other than re-prepare? Would you prefer disabling any and all schema modifications for as long as there exists a single prepared statement? Instead of re-prepare the affected transaction/s, don't allow any transaction prepare if other is changing the schema. Sorry for being dense, but I don't understand what you are saying. What do you mean by "prepare a transaction"? Transactions are not prepared, statements are. sqlite3_prepare is not part of a transaction, does not initiate a transaction, and is not affected by any ongoing transaction in any way, shape or form. The first call to sqlite3_step on a prepared statement initiates the transaction (assuming there is no explicitly started transaction in place on the database handle). SQLITE_SCHEMA error arises when another process or thread changes the schema between sqlite3_prepare (or sqlite3_reset) and sqlite3_step. If your usage pattern is something like begin transaction prepare step step ... finalize commit then you don't need to worry about SQLITE_SCHEMA under the existing engine. The whole issue becomes moot. Of course you are losing one of the benefits of prepared statements - time saving due to elimination of unnecessary parsing and execution planning. Igor Tandetnik
Re: [sqlite] Re: Re: Request for comment: Proposed SQLite API changes
Well, the write was an example. So, a lock_schema wouldn't do the re-prepare, in some cases reparse, the other threaded transactions. I don't quite follow how exactly this is supposed to help. Are you saying that there should be some kind of a lock from the moment the statement is prepared to the moment it is finalized, and no schema change should occur while this lock is held? Not exactly, to allow a statement change the schema, it must have exclusive access to the schema. Not all statements change the schema. I don't think this would be desirable - I beleve many applications now prepare certain often-used queries up front when the database handle is opened, and keep prepared statements around until it's time to close the database. Think of them as poor man's stored procedures. But this pre-prepared statements are also affected by the schema change, no? I don't use pre-prepared statements and didn't thought about them. You seem to think that the problem occurs when schema changes right in the middle of sqlite3_prepare call. This is not the case. The problem is that a prepared statement may sit around for a long time before it is actually used in a sqlite3_step call. At this point the engine may find out that the schema now differs from what it was when the statement was prepared, so the internal data structures are no longer valid. How do you propose to handle this situation, other than re-prepare? Would you prefer disabling any and all schema modifications for as long as there exists a single prepared statement? Instead of re-prepare the affected transaction/s, don't allow any transaction prepare if other is changing the schema. Perhaps my idea is a bit distorsionated by the use i do of sqlite and don't have a general point of view. Must add that doing this way you don't need to modify the API. Must add that doing it the way Dr. Hipp proposed doesn't modify the API either. It simply makes one error code obsolete - the engine never reports it where it could be reported before. Fully backward compatible. Oppsss i was thinking about the SQLITE_ERROR in sqlite3_step change. Sorry. ** This document represent my ideas. They are original from me. It's forbidden think the same than me, without previous payment. If you agree me, PAY. If you don't do so and divulge my ideas in any media, i'll send you my lawyers... sit lawyer SIT!!
[sqlite] Re: Re: Request for comment: Proposed SQLite API changes
Eduardo wrote: At 14:27 06/11/2005, you wrote: Eduardo <[EMAIL PROTECTED]> wrote: Isn't better lock the database while a transaction that can make a SQLITE_SCHEMA error, as is done with writes? A change in database is always a change. Also that way you don't waste time in rerunning the affected transactions. It is indeed locked as you describe. The problem is as follows: 1. One db handle performs sqlite3_prepare. This does not in itself start a transaction, but the resulting data structure (the prepared statement) relies on details of the schema at the time of prepare. E.g. "select * " query captures the list of columns when the statement is prepared. 2. Another db handle performs a transaction that modifies the schema. 3. sqlite3_step is called on the statement prepared at #1. It is at this point that the schema modification is discovered. Currently, SQLite reports SQLITE_SCHEMA error in this situation. The proprosal is for the statement to keep the text of the query, so the engine can re-prepare the satement and try to step again, transparently to the caller. 4. Once the first sqlite3_step succeeds, an implicit transaction is started (I assume there are no explicit transactions in effect), so the schema can no longer change unexpectedly. Well, the write was an example. So, a lock_schema wouldn't do the work at the prepare phase? The schema begins locked and when a transaction needs to do a change, sends a signal to gain exclusive, unlock, make the changes and lock it again. Don't know how many cpu cycles can this take but in a heavy scenario it may be less than re-prepare, in some cases reparse, the other threaded transactions. I don't quite follow how exactly this is supposed to help. Are you saying that there should be some kind of a lock from the moment the statement is prepared to the moment it is finalized, and no schema change should occur while this lock is held? I don't think this would be desirable - I beleve many applications now prepare certain often-used queries up front when the database handle is opened, and keep prepared statements around until it's time to close the database. Think of them as poor man's stored procedures. You seem to think that the problem occurs when schema changes right in the middle of sqlite3_prepare call. This is not the case. The problem is that a prepared statement may sit around for a long time before it is actually used in a sqlite3_step call. At this point the engine may find out that the schema now differs from what it was when the statement was prepared, so the internal data structures are no longer valid. How do you propose to handle this situation, other than re-prepare? Would you prefer disabling any and all schema modifications for as long as there exists a single prepared statement? Must add that doing this way you don't need to modify the API. Must add that doing it the way Dr. Hipp proposed doesn't modify the API either. It simply makes one error code obsolete - the engine never reports it where it could be reported before. Fully backward compatible. Igor Tandetnik
Re: [sqlite] Re: Request for comment: Proposed SQLite API changes
Eduardo <[EMAIL PROTECTED]> wrote: Isn't better lock the database while a transaction that can make a SQLITE_SCHEMA error, as is done with writes? A change in database is first sqlite3_step succeeds, an implicit transaction is started (I assume there are no explicit transactions in effect), so the schema can no longer change unexpectedly. Well, the write was an example. So, a lock_schema wouldn't do the work at the prepare phase? The schema begins locked and when a transaction needs to do a change, sends a signal to gain exclusive, unlock, make the changes and lock it again. Don't know how many cpu cycles can this take but in a heavy scenario it may be less than re-prepare, in some cases reparse, the other threaded transactions. Must add that doing this way you don't need to modify the API. #The Unix Guru's View of Sex unzip ; strip ; touch ; grep ; finger ; mount ; fsck ; more ; yes ; umount ; sleep
Re: [sqlite] Re: Request for comment: Proposed SQLite API changes
At 14:27 06/11/2005, you wrote: Eduardo <[EMAIL PROTECTED]> wrote: Isn't better lock the database while a transaction that can make a SQLITE_SCHEMA error, as is done with writes? A change in database is always a change. Also that way you don't waste time in rerunning the affected transactions. It is indeed locked as you describe. The problem is as follows: 1. One db handle performs sqlite3_prepare. This does not in itself start a transaction, but the resulting data structure (the prepared statement) relies on details of the schema at the time of prepare. E.g. "select * " query captures the list of columns when the statement is prepared. 2. Another db handle performs a transaction that modifies the schema. 3. sqlite3_step is called on the statement prepared at #1. It is at this point that the schema modification is discovered. Currently, SQLite reports SQLITE_SCHEMA error in this situation. The proprosal is for the statement to keep the text of the query, so the engine can re-prepare the satement and try to step again, transparently to the caller. 4. Once the first sqlite3_step succeeds, an implicit transaction is started (I assume there are no explicit transactions in effect), so the schema can no longer change unexpectedly. Well, the write was an example. So, a lock_schema wouldn't do the work at the prepare phase? The schema begins locked and when a transaction needs to do a change, sends a signal to gain exclusive, unlock, make the changes and lock it again. Don't know how many cpu cycles can this take but in a heavy scenario it may be less than re-prepare, in some cases reparse, the other threaded transactions. Antivirus. Warning: User detected. Please, move away from computer or you will be eliminated. Thanks
Re: [sqlite] Fwd: column name as a value in a tuple - headache!
On 11/6/05, Puneet Kishor <[EMAIL PROTECTED]> wrote: > > On Nov 6, 2005, at 8:19 AM, Bjørn Eikeland wrote: > > > Two columns, 'rr' and 'slag', 'rr' contains precipitation and 'slag' > > contains the type of precipitation as a two letter code. RR (capital) > > is the code for rain, but any and all select statemens includeing > > WHERE slag="RR" fails as it thinks I'm comparing 'slag' to the column > > 'rr'.. > > use single quotes > > WHERE slag = 'RR' > > -- > Puneet Kishor > > Single quotes it is then! Thanks!
Re: [sqlite] Fwd: column name as a value in a tuple - headache!
On Nov 6, 2005, at 8:19 AM, Bjørn Eikeland wrote: Two columns, 'rr' and 'slag', 'rr' contains precipitation and 'slag' contains the type of precipitation as a two letter code. RR (capital) is the code for rain, but any and all select statemens includeing WHERE slag="RR" fails as it thinks I'm comparing 'slag' to the column 'rr'.. use single quotes WHERE slag = 'RR' -- Puneet Kishor
Re: [sqlite] Fwd: column name as a value in a tuple - headache!
Bjørn Eikeland a écrit : I've been using sqlite to ease making statistical graphs from metrological data and have encountered a strange problem. Two columns, 'rr' and 'slag', 'rr' contains precipitation and 'slag' contains the type of precipitation as a two letter code. RR (capital) is the code for rain, but any and all select statemens includeing WHERE slag="RR" fails as it thinks I'm comparing 'slag' to the column 'rr'.. Is this a feature or a bug? I'm not really sure where to look for similar cases or what to call it. It seems it could have its uses, but then one should also be able to force not to behave like that. Hi Bjørn , Have you tried to use single quotes instead of double quotes ? varchar values have to be encapsulated wihtin single quotes... HTH, -- Guillaume MAISON - [EMAIL PROTECTED] 83, Cours Victor Hugo 47000 AGEN Tél : 05 53 87 91 48 - Fax : 05 53 68 73 50 e-mail : [EMAIL PROTECTED] - Web : http://nauteus.com
[sqlite] Fwd: column name as a value in a tuple - headache!
I've been using sqlite to ease making statistical graphs from
metrological data and have encountered a strange problem.
Two columns, 'rr' and 'slag', 'rr' contains precipitation and 'slag'
contains the type of precipitation as a two letter code. RR (capital)
is the code for rain, but any and all select statemens includeing
WHERE slag="RR" fails as it thinks I'm comparing 'slag' to the column
'rr'..
Is this a feature or a bug? I'm not really sure where to look for
similar cases or what to call it. It seems it could have its uses, but
then one should also be able to force not to behave like that.
More generic example:
BEGIN TRANSACTION;
DROP TABLE test;
CREATE TABLE test(
colATEXT,
colBTEXT);
INSERT INTO test VALUES("a0", "b0");
INSERT INTO test VALUES("a1", "b1");
INSERT INTO test VALUES("a2", "b2");
INSERT INTO test VALUES("a3", "colb");
INSERT INTO test VALUES("a4", "b3");
INSERT INTO test VALUES("a5", "cola");
COMMIT;
SELECT test.* FROM test where test.colB="colb";
SELECT test.* FROM test where test.colB="cola";
I would expect each of the two queries to return a single row,
however, the first query returns 5 rows, and the last no rows.
mvh,
Bjørn
[sqlite] Re: Request for comment: Proposed SQLite API changes
Eduardo <[EMAIL PROTECTED]> wrote: Isn't better lock the database while a transaction that can make a SQLITE_SCHEMA error, as is done with writes? A change in database is always a change. Also that way you don't waste time in rerunning the affected transactions. It is indeed locked as you describe. The problem is as follows: 1. One db handle performs sqlite3_prepare. This does not in itself start a transaction, but the resulting data structure (the prepared statement) relies on details of the schema at the time of prepare. E.g. "select * " query captures the list of columns when the statement is prepared. 2. Another db handle performs a transaction that modifies the schema. 3. sqlite3_step is called on the statement prepared at #1. It is at this point that the schema modification is discovered. Currently, SQLite reports SQLITE_SCHEMA error in this situation. The proprosal is for the statement to keep the text of the query, so the engine can re-prepare the satement and try to step again, transparently to the caller. 4. Once the first sqlite3_step succeeds, an implicit transaction is started (I assume there are no explicit transactions in effect), so the schema can no longer change unexpectedly. Igor Tandetnik
