RE: [sqlite] Re: Db copy
Dennis, I looked at the .dump feature implementation in shell.c as well as how vacuum is implemented. Based on that an idea will be to iterate through the metainformation on the SQLITE_MASTER table in order to recreate the new Db schema and for each table run SQL statements that will transfer the content from the old db into the new one. However, I was wondering if there is an easier or computationally less expensive way to accomplish the same thing (do it without going through each table) something like "cloning" the B-Tree representing the database. Iulian. -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 29, 2006 7:29 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Re: Db copy Iulian Popescu wrote: >Thank you very much, but this will require to modify the library as far as I >understand. I would rather not do that, is that any other less intrusive >way? > > Iulian, I wasn't suggesting that you modify the library. It is an open source program. I was suggesting that you "appropriate" some of the source that is used to implement functions similar to what you are trying to do, and incorporate it into your application. The functions in shell.c use the library APIs in the same way as your application. Copy the useful stuff out and modify it to do exactly what you need to do in your own application. It seemed like a good place to start to me. HTH Dennis Cote
RE: [sqlite] Re: Db copy
Thank you very much, but this will require to modify the library as far as I understand. I would rather not do that, is that any other less intrusive way? Iulian. -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 29, 2006 6:50 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Re: Db copy Iulian Popescu wrote: >I'm really sorry - I forgot to mention that both databases are in memory. > > > > Iulian, In that case you will have to copy the contents. The easiest way to do that is probably to modify the code the sqlite shell uses to do a database dump. Instead of writing the generated SQL out to a file like the dump command does, execute the generated SQL statements to create the same tables and records in the second attached database. The shell command is implemented in a few functions in shell.c. I believe there is some similar code in the vacuum functions that copy the entire database to a new file without converting everything into SQL text. This might be faster. You can look at the code in vacuum.c HTH Dennis Cote
RE: [sqlite] Re: Db copy
I'm really sorry - I forgot to mention that both databases are in memory. Iulian. -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 29, 2006 5:32 PM To: SQLite Subject: [sqlite] Re: Db copy Iulian Popescu wrote: > Does anybody have an idea what would be the easiest way to completely > copy an attached database to another empty attached database? Making a copy of the underlying file. Igor Tandetnik
[sqlite] Db copy
Hello, Does anybody have an idea what would be the easiest way to completely copy an attached database to another empty attached database? Thank you, Iulian.
RE: [sqlite] Different column names in callback function on Linux vs. Windows
How can I set the pragma values - is this something that can be done at build time or is it only possible at runtime by executing the command (before running any SELECT statements)? Thanks, Iulian. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 22, 2006 4:46 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Different column names in callback function on Linux vs. Windows "Iulian Popescu" <[EMAIL PROTECTED]> writes: > I checked the versions and indeed the one I'm using on Windows is 3.0.8 > whether the one on Linux is 3.1.2. This being said as far as I understand > and please correct me if I'm wrong the two PRAGMA(s) are just commands you > run used to modify the operation of the SQLite library. I haven't invoked > any of them before running the statements I mentioned so I assume the > default behavior was used. How can I find what this is for the particular > versions I'm running? You can find out the current values like this: % sqlite3 :memory: SQLite version 3.2.1 Enter ".help" for instructions sqlite> pragma full_column_names; 0 sqlite> pragma short_column_names; 1 sqlite> but beware that the *meaning* of the pragmas changed over time, so just having the same values doesn't necessarily mean that the same column names will be returned by your queries. (You can try to find out what changes were made when, using the "timeline" on the sqlite.org website. I don't recall when the changes I'm referencing occurred.) For full compatibility in this area, the easiest method is to just ensure that you're using the same version of sqlite on both platforms, and then set the pragma values the same. Derrell
RE: [sqlite] Different column names in callback function on Linux vs. Windows
Hi Derrell, I checked the versions and indeed the one I'm using on Windows is 3.0.8 whether the one on Linux is 3.1.2. This being said as far as I understand and please correct me if I'm wrong the two PRAGMA(s) are just commands you run used to modify the operation of the SQLite library. I haven't invoked any of them before running the statements I mentioned so I assume the default behavior was used. How can I find what this is for the particular versions I'm running? Thanks, Iulian. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 22, 2006 3:20 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Different column names in callback function on Linux vs. Windows "Iulian Popescu" <[EMAIL PROTECTED]> writes: > I'm doing an application port from Windows to Linux and one of the > problems I'm facing is when executing the following statement through a call > to sqlite3_exec(): > > SELECT mytable.'mycolumn' FROM table > > The registered callback function 4th argument (a char**) denoting the column > names contains the string mytable.'mycolumn' on Windows and the string > mycolumn on Linux. Has anyone any idea why would that be? I suspect you're using different versions of sqlite on Windows and Linux, or you have pragma settings set differently on the two OSs. You can verify the version of sqlite with "sqlite3 -version". There have been changes, through the development of sqlite, on what column names to return. IIRC, the meanings of PRAGMA short_column_names and PRAGMA full_column_names have changed a couple of times, and these affect exactly the information that you're having trouble with. You should first ensure that you are running the same version of sqlite on the to OSs. Then ensure that the settings of these two pragmas are the same. With both the version and the pragma settings the same, I believe you should get the same values passed to the callback function, given the same query. Windows and Linux portations built from the same source, so should generate similar results. Derrell
[sqlite] Different column names in callback function on Linux vs. Windows
Hello, I'm doing a port of our application from Windows to Linux and one of the problems I'm facing is when executing the following statement through a call to sqlite3_exec(): SELECT mytable.'mycolumn' FROM table The registered callback function 4th argument (a char**) denoting the column names contains the string mytable.'mycolumn' on Windows and the string mycolumn on Linux. Has anyone any idea why would that be? Thanks, Iulian.
[sqlite] Different kind of db locking behaviour on Unix(Linux) vs. Windows?
Hello, I have the following scenario that fails on Linux and executes fine on Windows XP. Suppose the following SQL statement is executed by calling sqlite3_exec: SELECT mySqlFunction() Inside the body of the mySqlFuntion() the following statement is executed through a call to sqlite3_exec: CREATE TEMP TABLE MyTempTable AS SELECT 'foo' Followed by the statement: DROP TABLE MyTempTable The last statement execution fails on Linux (Fedora Core 4) with the following error "database table is locked" but not on Windows XP. However the following statement runs fine on both systems: DELETE FROM MyTempTable Would somebody know why is that happening? Thanks, Iulian
[sqlite] Different behaviour on Linux vs. Windows?
Hello, I have the following scenario that fails on Linux and executes fine on Windows XP. Suppose the following SQL statement is executed by calling sqlite3_exec: SELECT mySqlFunction() Inside the body of the mySqlFuntion() the following statements are executed through calls to sqlite3_exec: CREATE TEMP TABLE MyTempTable AS SELECT 'foo' Then a: DROP TABLE MyTempTable The last statement execution fails on Linux (Fedora Core 4) with the following error "database table is locked" but not on Windows XP. What would be a reason for that? Thanks, Iulian.
RE: [sqlite] Recursive sqlite3_exec call
I would like to be more specific about the scenario I encountered. Suppose the following SQL statement is executed by calling sqlite3_exec: SELECT mySqlFunction() Inside the body of the mySqlFuntion() the following statements are executed through calls to sqlite3_exec: CREATE TEMP TABLE MyTempTable AS SELECT ... Then a couple of SELECT x, y FROM MyTempTable ... And finally: DROP TABLE MyTempTable The last statement execution fails on Linux (Fedora Core 4) with the following error "database table is locked" but not on Windows XP. What would be a reason for that? Thanks, Iulian. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 14, 2006 5:14 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Recursive sqlite3_exec call "Iulian Popescu" <[EMAIL PROTECTED]> wrote: > Hello, > > > > Is that possible to define a SQL function that calls sqlite3_exec on the > same db handler through which was executed (passed to the function as user > data)? > Yes. But a table being read cannot be written by the recursive call. So you cannot do an UPDATE on the same row you are looking at. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Recursive sqlite3_exec call
Hello, Is that possible to define a SQL function that calls sqlite3_exec on the same db handler through which was executed (passed to the function as user data)? Thanks, Iulian.
RE: [sqlite] Multiple Threads and Transactions
Thank you very much for the answer and I apologize for my misconception. My confusion came from the fact the database handle was used by two different threads and one of them was executing operations inside a transaction. Is that possible to obtain more than one handle to the same in memory instance of the database? Thank you, Iulian. -Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Monday, September 19, 2005 1:44 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Multiple Threads and Transactions On Mon, 2005-09-19 at 13:27 -0400, Iulian Popescu wrote: > Hello, > > > > I'm kind of new to the SQLite. I'm running an instance of the database in > memory. I'm trying to figure out if the transaction isolation is also valid > between threads. To give a concrete example, two threads as part of the same > process share the same database connection. Suppose a transaction is started > in one thread and inserts some rows in a table A. If the same thread does a > SELECT * FROM A it should see the inserted records if my understanding is > correct. What will happen if another thread does a SELECT from the same > table A before the first thread commits the transaction? Will it see the > records inserted by the first thread? Based on the tests I have made it > seems to - weather I expected not. Is that correct or am I doing something > wrong? > > I've noticed if I do the same thing with a database on disk and between > processes, the second process won't see the changes until the first one will > commit the transaction. > Isolation only occurs between separate database handles. If you do two statements against the same database handle, they will not be isolated. I do not understand why you would expect that they would be. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Multiple Threads and Transactions
Hello, I'm kind of new to the SQLite. I'm running an instance of the database in memory. I'm trying to figure out if the transaction isolation is also valid between threads. To give a concrete example, two threads as part of the same process share the same database connection. Suppose a transaction is started in one thread and inserts some rows in a table A. If the same thread does a SELECT * FROM A it should see the inserted records if my understanding is correct. What will happen if another thread does a SELECT from the same table A before the first thread commits the transaction? Will it see the records inserted by the first thread? Based on the tests I have made it seems to - weather I expected not. Is that correct or am I doing something wrong? I've noticed if I do the same thing with a database on disk and between processes, the second process won't see the changes until the first one will commit the transaction. Thank you, Iulian.
RE: [sqlite] quote() function
I'm really sorry D. Hipps. My question was silly - I didn't realize that the
function arguments are also parsed by the SQL engine and therefore they
should be valid SQL strings.
I apologize for the annoyance.
Iulian.
-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
Sent: Monday, March 28, 2005 1:47 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] quote() function
On Mon, 2005-03-28 at 13:27 -0500, Iulian Popescu wrote:
> Is it something wrong with doing a:
>
> SELECT quote('AA'AA')
>
Yes, it is a syntax error. The ' character within a string in SQL is
doubled to quote it - like in Pascal. That's the rules of SQL - I did not
make this stuff up.
The following works:
SELECT quote('AA''AA');
Compare the output against this:
SELECT 'AA''AA';
--
D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] quote() function
Dear D. Hipp,
Thank you very much for getting involved in this discussion. Would you
please give a concrete example of use of quote()?
Is it something wrong with doing a:
SELECT quote('AA'AA')
Thank you very much,
Iulian.
-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
Sent: Monday, March 28, 2005 12:15 PM
To: Christopher Petrilli
Cc: sqlite-users@sqlite.org
Subject: Re: [sqlite] quote() function
On Mon, 2005-03-28 at 12:03 -0500, Christopher Petrilli wrote:
> I suspect it was intended to be used like this:
>
> select quote(columname) from table;
>
Chris's suspicions are correct. quote() is especially useful within
triggers where the trigger generates SQL code that will undo the change the
invoked the trigger. This can be used to generate a general-purpose
undo/redo mechanism in a program that uses sqlite as its primary data
structure.
--
D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] quote() function
How about if the columnname is the AA'AA string? Wouldn't the still generate a syntax error? -Original Message- From: Christopher Petrilli [mailto:[EMAIL PROTECTED] Sent: Monday, March 28, 2005 12:04 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] quote() function On Mon, 28 Mar 2005 11:57:10 -0500, Iulian Popescu <[EMAIL PROTECTED]> wrote: > That doesn't work either - I get the same error. > From the documentation: > > This routine returns a string which is the value of its argument > suitable for inclusion into another SQL statement. Strings are > surrounded by single-quotes with escapes on interior quotes as needed. > BLOBs are encoded as hexadecimal literals. The current implementation > of VACUUM uses this function. The function is also useful when writing > triggers to implement undo/redo functionality. > > Therefore I was expecting that something like: > > SELECT quote(AA'AA) > > would work in the sense that the actual SQL statement executed will be: > > SELECT 'AA''AA' > > but it doesn't seem to. I suspect it was intended to be used like this: select quote(columname) from table; Chris -- | Christopher Petrilli | [EMAIL PROTECTED]
RE: [sqlite] quote() function
That doesn't work either - I get the same error.
>From the documentation:
This routine returns a string which is the value of its argument suitable
for inclusion into another SQL statement. Strings are surrounded by
single-quotes with escapes on interior quotes as needed. BLOBs are encoded
as hexadecimal literals. The current implementation of VACUUM uses this
function. The function is also useful when writing triggers to implement
undo/redo functionality.
Therefore I was expecting that something like:
SELECT quote(AA'AA)
would work in the sense that the actual SQL statement executed will be:
SELECT 'AA''AA'
but it doesn't seem to.
Iulian.
-Original Message-
From: Clay Dowling [mailto:[EMAIL PROTECTED]
Sent: Monday, March 28, 2005 12:01 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] quote() function
Iulian Popescu said:
> Hi,
>
> Would someone please explain me the semantics of this function? I've
> tried:
>
> SELECT quote('AAA'') AS value
>
> And it returns the following error:
>
> Error: near "": syntax error (1)
Try SELECT quote('AAA\'') AS value
Not sure what purpose this function is supposed to server, since you need to
escape the string before passing it to this function. As near as I can tell
this defeats the purpose of the function.
Clay
--
Lazarus Notes from Lazarus Internet Development
http://www.lazarusid.com/notes/ Articles, Reviews and Commentary on web
development
