Re: [sqlite] Re: Transactions on a shared database connection
On 11/6/07, John Firebaugh <[EMAIL PROTECTED]> wrote: > > You're confusing threads with the context of the connection. When you > > call sqlite3_open() you get a single connection to a db. That > > connection can then be used across threads, but it is for all intents > > and purposes a single line of communication with a database (using it > > twice at the same time doesn't somehow multiply that relationship). > > Thank you for attempting to clarify this for me. You are saying that the > transaction state is part of the connection context, whereas I was > expecting it to be a per-thread-per-connection state. > What else, besides transaction state, is part of the connection context > and shared with shared connections? I know of the page cache, of which > sharing is desired. Anything else to be aware of? To put things another way: there is no per-thread behavior in SQLite. Everything works on a per-connection basis. (The only new thing is that SQLite is more threadsafe than it was before.) - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Transactions on a shared database connection
Richard Klein wrote: John Firebaugh wrote: I take it this mean that if, say, thread A begins a transaction, and modifies the database, then thread B modifies the database, then thread A commits the transactions, B's modifications are also committed? Nope. Once thread A begins a transaction, no other thread (or process) can modify the database until thread A commits. (That's what "atomic transaction" means!) In the above scenario, thread B will likely get a SQLITE_BUSY when he tries to UPDATE. He should sleep for awhile and try again. - Richard Klein Oops, didn't read the fine print: Threads A and B share a connection. I don't know what would happen in that case. - Richard - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Transactions on a shared database connection
> John Firebaugh wrote: > > I take it this mean that if, say, thread A begins a transaction, and > > modifies the database, then thread B modifies the database, > then thread > > A commits the transactions, B's modifications are also committed? > > Nope. Once thread A begins a transaction, no other thread > (or process) > can modify the database until thread A commits. (That's what "atomic > transaction" means!) > > In the above scenario, thread B will likely get a SQLITE_BUSY when he > tries to UPDATE. He should sleep for awhile and try again. To clarify, that's the behavior in the case where each thread has its own database connection. But I'm referring to the case where the threads are sharing a single connection. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Transactions on a shared database connection
Richard Klein <[EMAIL PROTECTED]> wrote: John Firebaugh wrote: I take it this mean that if, say, thread A begins a transaction, and modifies the database, then thread B modifies the database, then thread A commits the transactions, B's modifications are also committed? Nope. Once thread A begins a transaction, no other thread (or process) can modify the database until thread A commits. (That's what "atomic transaction" means!) Not true. Another thread in the same process can modify the database via the same connection. The two just can't do that simultaneously. In the above scenario, thread B will likely get a SQLITE_BUSY when he tries to UPDATE. He should sleep for awhile and try again. That would happen if thread B had its own, separate connection, but not when the two threads are working with the same connection. As far as SQLite is concerned, there's no difference between a single thread making two changes to the database within a single transaction, or two threads each making one change. Transaction control is per connection, not per thread. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Transactions on a shared database connection
John Firebaugh wrote: I take it this mean that if, say, thread A begins a transaction, and modifies the database, then thread B modifies the database, then thread A commits the transactions, B's modifications are also committed? Nope. Once thread A begins a transaction, no other thread (or process) can modify the database until thread A commits. (That's what "atomic transaction" means!) In the above scenario, thread B will likely get a SQLITE_BUSY when he tries to UPDATE. He should sleep for awhile and try again. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Transactions on a shared database connection
A transaction state is dependent upon the lock state, and the lock is at database level because the database is the resource being shared by user initiated connections. Locks are held by individual connections and those connections can be at process or thread level. The Sqlite locks are process level file locks and are less efficient when invoked by threads because of the mutexes and extra logic required. In essence a transaction is a journal file and the transaction lock is a lock on that file. There can be only one journal current for each database file. Sqlite does have some sophistications which increase concurrency. For example it is possible to read a database while a journal file is being built, but not when it is being committed or if you specify an exclusive transaction. Finally, Sqlite is "lite" and designed towards embedded use rather than enterprise implementations. If you need the features of Oracle, then use it but if you don't Sqlite's small footprint and minimalist nature can be highly beneficial. John Firebaugh wrote: You're confusing threads with the context of the connection. When you call sqlite3_open() you get a single connection to a db. That connection can then be used across threads, but it is for all intents and purposes a single line of communication with a database (using it twice at the same time doesn't somehow multiply that relationship). Thank you for attempting to clarify this for me. You are saying that the transaction state is part of the connection context, whereas I was expecting it to be a per-thread-per-connection state. I had thought, based on the documentation, that there was little or no functional difference between: A) Two threads, each of which has its own database connection B) Two threads, sharing a single database connection Note that your example of two threads attempting to update the same record at the same time is consistent with this theory -- in either case SQLITE_BUSY (or similar) is likely to occur on one of the threads. In my case, I was also expecting to get SQLITE_BUSY (or similar) with the second transaction, rather than an error. (I'm well aquainted with the SQLITE_BUSY behavior and application-level synchronization techniques.) What else, besides transaction state, is part of the connection context and shared with shared connections? I know of the page cache, of which sharing is desired. Anything else to be aware of? - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Transactions on a shared database connection
John Firebaugh wrote: I take it this mean that if, say, thread A begins a transaction, and modifies the database, then thread B modifies the database, then thread A commits the transactions, B's modifications are also committed? Correct. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Transactions on a shared database connection
> Note that your example of two threads attempting to update the same > record at the same time is consistent with this theory -- in > either case SQLITE_BUSY (or similar) is likely to occur on one of the > threads. I see now that I am also likely wrong about this. In the shared connection case, the modifications will simply be serialized. Correct? I take it this mean that if, say, thread A begins a transaction, and modifies the database, then thread B modifies the database, then thread A commits the transactions, B's modifications are also committed? - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Transactions on a shared database connection
> You're confusing threads with the context of the connection. When you > call sqlite3_open() you get a single connection to a db. That > connection can then be used across threads, but it is for all intents > and purposes a single line of communication with a database (using it > twice at the same time doesn't somehow multiply that relationship). Thank you for attempting to clarify this for me. You are saying that the transaction state is part of the connection context, whereas I was expecting it to be a per-thread-per-connection state. I had thought, based on the documentation, that there was little or no functional difference between: A) Two threads, each of which has its own database connection B) Two threads, sharing a single database connection Note that your example of two threads attempting to update the same record at the same time is consistent with this theory -- in either case SQLITE_BUSY (or similar) is likely to occur on one of the threads. In my case, I was also expecting to get SQLITE_BUSY (or similar) with the second transaction, rather than an error. (I'm well aquainted with the SQLITE_BUSY behavior and application-level synchronization techniques.) What else, besides transaction state, is part of the connection context and shared with shared connections? I know of the page cache, of which sharing is desired. Anything else to be aware of? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proposed sqlite3_initialize() interface
> This is still just an idea. If you think that adding a new > required sqlite3_initialize() interface would cause serious > hardship for your use of SQLite, please speak up now. It requires changing and recompiling all applications linking to it. This is a bit annoying for distributions. Debian would probably have to ship an sqlite3 and sqlite4 (?) package until the transition is done. I think the list of affected functions you posted is overly pessimistic; most of these functions probably do not need a fully initialized library. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: Transactions on a shared database connection
John Firebaugh wrote: No. You are still required to synchronize access, so only one thread uses the connection at any given time. If you don't, it will likely lead to database corruption. I belive you are mistaken. This refers to low level synchronization, not the application level synchronization needed to serialize access to an Sqlite database. You still need to observe the SQLITE_BUSY checks or use some other form of synchronization such as mutexes, rwlocks etc. An Sqlite database id a single file. If you think of it that way and treat it as you would sharing a file between users or threads everything becomes intuitive. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Transactions on a shared database connection
On Nov 6, 2007 7:45 PM, John Firebaugh <[EMAIL PROTECTED]> wrote: > "Restrictions on the use of the same database connection by multiple > threads have been dropped. It is now safe for multiple threads to use > the same database connection at the same time." This suggests to me that > operations that can be accomplished using per-thread database > connections (e.g. a transaction on each thread) can now be accomplished > with a single database connection. You're confusing threads with the context of the connection. When you call sqlite3_open() you get a single connection to a db. That connection can then be used across threads, but it is for all intents and purposes a single line of communication with a database (using it twice at the same time doesn't somehow multiply that relationship). > Obviously two threads can issue commands that change the database at the > same time, But not all actions can succeed at the same time. For example, two threads trying to update the same record at the same time. One query will likely result in SQLITE_BUSY (or similar). -- - stephan beal http://wanderinghorse.net/home/stephan/ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Re: Transactions on a shared database connection
John Firebaugh wrote: No. You are still required to synchronize access, so only one thread uses the connection at any given time. If you don't, it will likely lead to database corruption. I belive you are mistaken. http://www.mail-archive.com/[EMAIL PROTECTED]/msg28089.html From the same thread: http://www.mail-archive.com/sqlite-users@sqlite.org/msg28099.html But with SQLite 3.5, access to each database connection is serialized. So yes, you don't need to syncrhonize access yourself, but SQLite engine will do it for you. For example, if one thread runs an UPDATE statement on a connection, another thread attempting to use the same connection will block until UPDATE finishes. At no point will you have two threads writing to the database simultaneously. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Transactions on a shared database connection
John Firebaugh wrote: I would have expected the "no transaction nesting" restriction to be enforced per-thread, not per-connection. And what made you believe so? How is supporting nested transactions in multiple threads any easier than supporting them in a single thread? I have no idea; the main attraction of SQLite is it frees me from having to worry about such difficulties. From a _user's_ perspective, rather than an implementer's perspective, it seems a reasonable assumption: "Restrictions on the use of the same database connection by multiple threads have been dropped. It is now safe for multiple threads to use the same database connection at the same time." Ok, that means that something that used to only work for a single thread now also works for multiple threads. How does this lead you to assume that a feature that did _not_, and still does not, work in a single thread, would suddenly start working in multiple threads? Obviously two threads can issue commands that change the database at the same time No. You are still required to synchronize access, so only one thread uses the connection at any given time. If you don't, it will likely lead to database corruption. Pretty much the only benefit of sharing a connection between threads is reduced memory footprint due to the fact that memory cache is also shared: if you have two separate connections, and each has recently retrieved the same page from database file, you would have two copies of this page in memory, but if you have one connection you only have one copy. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Transactions on a shared database connection
> > I would have expected the "no transaction > > nesting" restriction to be enforced per-thread, not per-connection. > > And what made you believe so? How is supporting nested transactions in > multiple threads any easier than supporting them in a single thread? I have no idea; the main attraction of SQLite is it frees me from having to worry about such difficulties. From a _user's_ perspective, rather than an implementer's perspective, it seems a reasonable assumption: "Restrictions on the use of the same database connection by multiple threads have been dropped. It is now safe for multiple threads to use the same database connection at the same time." This suggests to me that operations that can be accomplished using per-thread database connections (e.g. a transaction on each thread) can now be accomplished with a single database connection. Obviously two threads can issue commands that change the database at the same time, which in the absence of a "begin transaction" statement, still uses an (autocommitted) transaction. So from that perspective it also seems reasonable to expect explicit transactions to work just as well. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Transactions on a shared database connection
John Firebaugh wrote: With SQLite 3.5.1, when a connection is shared between two threads, if the second thread attempts to begin a transaction while a transaction is in progress on the first thread, sqlite3_exec( "begin transaction" ) returns SQLITE_ERROR with a status message "cannot start a transaction within a transaction". Is this expected behavior? Yes. This has nothing to do with threads - you can't do this on a single thread either. SQLite doesn't support nested transactions. I would have expected the "no transaction nesting" restriction to be enforced per-thread, not per-connection. And what made you believe so? How is supporting nested transactions in multiple threads any easier than supporting them in a single thread? Anyway, if you want this behavior, why don't you just have each thread open its own connection? Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Transactions on a shared database connection
With SQLite 3.5.1, when a connection is shared between two threads, if the second thread attempts to begin a transaction while a transaction is in progress on the first thread, sqlite3_exec( "begin transaction" ) returns SQLITE_ERROR with a status message "cannot start a transaction within a transaction". Is this expected behavior? I would have expected the "no transaction nesting" restriction to be enforced per-thread, not per-connection. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UTF-8 BLOB
It works in a Linux xterm. There's probably some UTF or codepage issue with the Windows console. Try using another command-line shell. --- Robert Wishlaw <[EMAIL PROTECTED]> wrote: > Using sqlite 3.5.2 on Windows XP, I have a textblob.csv file > > 192,C0,À0,À0 > 193,C1,Á0,Á0 > 254,FE,þ0,þ0 > 255,FF,ÿ0,ÿ0 > > which I have imported into a new database > > sqlite3 textblob.db < textblob.sql > > via a file named textblob.sql > > .separator , > create Table textblob(dec INTEGER, hex BLOB, txt TEXT, blb BLOB); > .import 'textblob.csv' textblob > > When I run the query > > SELECT dec, hex FROM textblob WHERE blb = 'À0'; > > there is no result. Likewise > > SELECT dec, hex FROM textblob WHERE blb = "À0"; > > does not work. > > SELECT dec, hex FROM textblob WHERE hex = "C0"; > > returns > > 192|C0 > > Any idea how to get the BLOB data? Or is the problem that the BLOB > data is not there because .import is filtering out invalid UTF-8? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How use "uSQLiteServer" wrapper with Visual Basic .NET ???
This is good advice from Sam, but there is another question. Why do you need a server and how do you intend to interface to it? Why does the server need to support Sqlite? Samuel R. Neff wrote: uSQLiteServer provides it's own network protocol implementation and it's own API so using is nothing like using SQLite itself or any other SQLite wrapper. If you feel you need to use uSQLiteServer then you'll need to either use the C code provided for it's client API, convert it to VB.NET, or write your own implementation based on the specs on their website. The download package does have a "vbclient" folder but the code in it does not appear to compile (I don't have VB6 so I can't say for sure). However, my suggestion would be not to use uSQLiteServer. If you really need a server based database, then use something designed for it like MSSQL or PostgreSQL. Alternatively, there are other network wrappers for SQLite which internalize the network layer and provide the same C API that SQLite provides which should allow for easier integration--google SQLite server. 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: paulito santana [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 06, 2007 3:36 AM To: sqlite-users@sqlite.org Subject: [sqlite] How use "uSQLiteServer" wrapper with Visual Basic .NET ??? Hello friends, i use this code to work with a "SQLite" database and works good : ... What changes i must to do, for use the "uSQLiteServer" wrapper ??? Please help me !! Thanks, Paulito - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How use "uSQLiteServer" wrapper with Visual Basic .NET ???
uSQLiteServer provides it's own network protocol implementation and it's own API so using is nothing like using SQLite itself or any other SQLite wrapper. If you feel you need to use uSQLiteServer then you'll need to either use the C code provided for it's client API, convert it to VB.NET, or write your own implementation based on the specs on their website. The download package does have a "vbclient" folder but the code in it does not appear to compile (I don't have VB6 so I can't say for sure). However, my suggestion would be not to use uSQLiteServer. If you really need a server based database, then use something designed for it like MSSQL or PostgreSQL. Alternatively, there are other network wrappers for SQLite which internalize the network layer and provide the same C API that SQLite provides which should allow for easier integration--google SQLite server. 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: paulito santana [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 06, 2007 3:36 AM To: sqlite-users@sqlite.org Subject: [sqlite] How use "uSQLiteServer" wrapper with Visual Basic .NET ??? Hello friends, i use this code to work with a "SQLite" database and works good : ... What changes i must to do, for use the "uSQLiteServer" wrapper ??? Please help me !! Thanks, Paulito - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite Rename table
@ Kishor:
thank you for the idea:
although it wasn't perfectly what I needed (I have to write a generic method
to rename a table, so I don't know the structure of the table before) it
took me the right idea and lastly I used the following sql:
create table new as select * from old
It still doesn't allow to recreate the exact structure of the prevoius table
(eg indexes and keys) but it should be useful for my needs.
:-)
@John: thanks a lot for your suggest :-)
in a future release of our opensource project probably we will translate our
database support class (mysql, postgres and sqlite) using PDO in order to
use sqlite3
Regards
Valerio
2007/11/6, John Stanton <[EMAIL PROTECTED]>:
>
> Sqlite3 is supported by PHP using PDO.
>
> Valerio Bontempi wrote:
> > Hi Kees,
> >
> > thanks for your solution, it is a very interesting solution.
> > But I need to rename a table using sql from php.
> > (this is also the reason for my need of sqlite and not sqlite3, not
> > supported yet by php)
> >
> > Thanks a lot
> >
> > Valerio
> >
> > 2007/11/5, Kees Nuyt <[EMAIL PROTECTED]>:
> >>
> >> Hi Valerio,
> >>
> >> On Mon, 5 Nov 2007 18:51:20 +0100, "Valerio Bontempi"
> >> <[EMAIL PROTECTED]> wrote:
> >>
> >>> Hi All,
> >>>
> >>> I'm new in your mailing list.
> >>> I need to use sqlite and not sqlite3 because of php still support only
> >> the
> >>> first version.
> >>> I read that sqlite3 support table renaming with common sql syntax
> >>> alter table table1 rename to table2
> >>>
> >>> but, although it works fine on sqlite3, it doesn't work on sqlite
> >>>
> >>> Can anyone confirm that table renaming isn't supported in sqlite first
> >>> version?
> >>> Moreover, if so, is there another way to do it?
> >>>
> >>> Thanks and regards
> >>>
> >>> Valerio Bontempi
> >> If the name of your table plus a leading and trailing space
> >> doesn't exist anywhere in your data, you could do :
> >>
> >> Windows:
> >> sqlite old_db .dump |
> >> awk "{sub(/ oldtable /,\" newtable \");print}" |
> >> sqlite new_db
> >>
> >> Unix:
> >> sqlite old_db .dump |
> >> awk '{sub(/ oldtable /," newtable ");print}' |
> >> sqlite new_db
> >>
> >> (all on one line, but I added linefeeds after every pipe
> >> character for clarity)
> >>
> >> Untested, parenthesis in positions where i assume spaces might
> >> cause some problems, but you get the idea.
> >>
> >> HTH
> >> --
> >> ( Kees Nuyt
> >> )
> >> c[_]
> >>
> >>
> >>
> -
> >> To unsubscribe, send email to [EMAIL PROTECTED]
> >>
> >>
> -
> >>
> >>
> >
> >
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>
--
*
Valerio Bontempi
Blog: http://mithland.wordpress.com/
*
RE: [sqlite] using "+" for avoiding using indexes in "group by"
Hi. > You can't. > > Why are you trying to avoid the use of indices? Most people > like it when their indices make an ORDER BY go faster. And > why are you worried about duplicate data in a transient > table, as long as you are getting the correct answer? > Thanks for reply. Just in real query I has one constraint, usable for other index, and this index is are better. With using this index need process only 2000 records and sort it, and using other index need process 100 records and test it for constraint. And I can't change the structure of indexes, becose table is a virtual table from other data source. So, I will try better calculate cost in my xBestIndex. WBR, Alexandr Orefkov. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite Rename table
Sqlite3 is supported by PHP using PDO.
Valerio Bontempi wrote:
Hi Kees,
thanks for your solution, it is a very interesting solution.
But I need to rename a table using sql from php.
(this is also the reason for my need of sqlite and not sqlite3, not
supported yet by php)
Thanks a lot
Valerio
2007/11/5, Kees Nuyt <[EMAIL PROTECTED]>:
Hi Valerio,
On Mon, 5 Nov 2007 18:51:20 +0100, "Valerio Bontempi"
<[EMAIL PROTECTED]> wrote:
Hi All,
I'm new in your mailing list.
I need to use sqlite and not sqlite3 because of php still support only
the
first version.
I read that sqlite3 support table renaming with common sql syntax
alter table table1 rename to table2
but, although it works fine on sqlite3, it doesn't work on sqlite
Can anyone confirm that table renaming isn't supported in sqlite first
version?
Moreover, if so, is there another way to do it?
Thanks and regards
Valerio Bontempi
If the name of your table plus a leading and trailing space
doesn't exist anywhere in your data, you could do :
Windows:
sqlite old_db .dump |
awk "{sub(/ oldtable /,\" newtable \");print}" |
sqlite new_db
Unix:
sqlite old_db .dump |
awk '{sub(/ oldtable /," newtable ");print}' |
sqlite new_db
(all on one line, but I added linefeeds after every pipe
character for clarity)
Untested, parenthesis in positions where i assume spaces might
cause some problems, but you get the idea.
HTH
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] using "+" for avoiding using indexes in "group by"
"orefkov" <[EMAIL PROTECTED]> wrote: > Hi All. > > When I used "+" for avoiding using indexes in "order by" list, > this cause a duplicate columns in ephemeral "sort" table. > > Example: > > "select col1, col2, sum(col3) from ttab group by +col1, col2" > > In this case, ephemeral table contain 5 columns, where column 0 and 3 are > duplicate: > > 25 Column 0 0 # ttab.col1 > 26 Column 0 1 # ttab.col2 > 27 Sequence 1 0 > 28 Column 0 0 # ttab.col1 > 29 Column 0 2 # ttab.col3 > 30 MakeRecord 5 0 > 31 IdxInsert 1 0 > > How I can force SQLite don't use index on grouping, and don't add duplicate > column in ephemeral table? > You can't. Why are you trying to avoid the use of indices? Most people like it when their indices make an ORDER BY go faster. And why are you worried about duplicate data in a transient table, as long as you are getting the correct answer? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] using "+" for avoiding using indexes in "group by"
Hi All. When I used "+" for avoiding using indexes in "order by" list, this cause a duplicate columns in ephemeral "sort" table. Example: "select col1, col2, sum(col3) from ttab group by +col1, col2" In this case, ephemeral table contain 5 columns, where column 0 and 3 are duplicate: 25 Column 0 0 # ttab.col1 26 Column 0 1 # ttab.col2 27 Sequence 1 0 28 Column 0 0 # ttab.col1 29 Column 0 2 # ttab.col3 30 MakeRecord 5 0 31 IdxInsert 1 0 How I can force SQLite don't use index on grouping, and don't add duplicate column in ephemeral table? WBR, Alexandr Orefkov. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Any roadmap for sqlite?
Halton Huo <[EMAIL PROTECTED]> wrote: > Hi there, > > As title, just curious whether have plan for later version, for example > 3.4.x? > There are no plans to continue 3.4.x. 3.5.2 is the current release. What is there in 3.4.x that 3.5.2 does not do better? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] UTF-8 BLOB
Using sqlite 3.5.2 on Windows XP, I have a textblob.csv file 192,C0,À0,À0 193,C1,Á0,Á0 254,FE,þ0,þ0 255,FF,ÿ0,ÿ0 which I have imported into a new database sqlite3 textblob.db < textblob.sql via a file named textblob.sql .separator , create Table textblob(dec INTEGER, hex BLOB, txt TEXT, blb BLOB); .import 'textblob.csv' textblob When I run the query SELECT dec, hex FROM textblob WHERE blb = 'À0'; there is no result. Likewise SELECT dec, hex FROM textblob WHERE blb = "À0"; does not work. SELECT dec, hex FROM textblob WHERE hex = "C0"; returns 192|C0 Any idea how to get the BLOB data? Or is the problem that the BLOB data is not there because .import is filtering out invalid UTF-8? Robert Wishlaw - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Lemon not reducing
For those reading this thread, I could solve my problme by using ragel (http://www.cs.queensu.ca/~thurston/ragel/). You can define leaving actions but also 'any change' actions. It was also easier to include in a C++ project then lemon/flex. If my grammar becomes more complicated, I heard it is possible to make a good collaboration between ragel and lemon (ragel is then used as a tokenizer). 2007/10/25, Gaspard Bucher <[EMAIL PROTECTED]>: > > I do not understand why lemon waits for one more token when it has > > enough information to reduce > ... > > > > >>> I don't think you can. Why do you want to? Why not just go > > >>> ahead and send it the next token? > > >>> > > >> Most people find a way around this problem using white-space. This > > >> could be a solution but then my grammar will be filled with > > >> "white-space | nothing" rules and I thought Lemon could reduce when > > >> there is no other way out of the current stack as it is more elegant. > > >> > > >> > > LA(LR) is the answer - just drop Your's tokens as their arrive and give > > a chance for the parser to be LALR, not LR or SLR :) > > > > mak > > > Lookahead improves what we can do with a grammar, but when there is no > ambiguity, it should resolve without waiting for the next token. If > your grammar is so simple it's an SLR, why not treat it so and ease > the creation of grammars without needing to add "separator" tokens > just so there is a lookahead (most languages use ";" or white space > for that purpose). > > My grammar works with all "whitespace or nothing" rules, but I have to > be careful to avoid conflicts. When I write it without the whitespace > stuff (eaten in the tokenizer), I have no conflict. Simple example: > main ::= commands. > commands ::= . > commands ::= commands ws command. > command ::= variable EQUAL value. > variable ::= ws IDENTIFIER ws. > value::= ws NUMBER ws. > ws ::= . > ws ::= WHITE. > > 2 parsing conflicts. > > Whithout whitespace stuff: > main ::= commands. > commands ::= . > commands ::= commands command. > command ::= variable EQUAL value. > variable ::= IDENTIFIER. > value::= NUMBER. > > no conflicts, easier and cleaner. > > I know how to fix the first grammar, but you have to think of the > actual succession to avoid two "ws" from coming next to the other, > this thinking is not really related to the grammar from my point of > view. > > Gaspard > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How use 'uSQLiteServer' wrapper with Visual Basic .NET ???
Sorry, I don't use .NET and couldn't tell you.
I am sure somebody will.
RBS
> Hello RBS,
> thanks for your answer. I'am a newbie in "SQLite".
> I need a "SQlite" server and i read that the "uSQLite" can do this, and
> i'am
> try to work with this. But i need something more in VB .NET ?
> Or you know other server application ?? Please help me !
>
>
> Regards,
> Paulito
>
> 2007/11/6, [EMAIL PROTECTED]
> <[EMAIL PROTECTED]>:
>>
>> What wrapper are you using now that works good and what is the
>> "uSQLiteServer" wrapper?
>>
>> RBS
>>
>> > Hello friends,
>> > i use this code to work with a "SQLite" database and works good :
>> >
>> > Dim cn As New SQLiteConnection("Data Source=C:\sqlite-3_5_1\tst.db")
>> > Dim dr As SQLiteDataReader
>> > Dim cmd As New SQLiteCommand()
>> > cmd.CommandText = "Select * from CFG_USER"
>> > cmd.Connection = cn
>> > cn.Open()
>> > dr = cmd.ExecuteReader()
>> >
>> > While (dr.Read())
>> >MsgBox(dr.GetValue(0))
>> > End While
>> >
>> > dr.Close()
>> > cn.Close(
>> >
>> > What changes i must to do, for use the "uSQLiteServer" wrapper ???
>> Please
>> > help me !!
>> >
>> > Thanks,
>> > Paulito
>> >
>> >
>> > **
>> >
>>
>>
>>
>>
>>
>> -
>> To unsubscribe, send email to [EMAIL PROTECTED]
>>
>> -
>>
>>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] How use 'uSQLiteServer' wrapper with Visual Basic .NET ???
Hello RBS,
thanks for your answer. I'am a newbie in "SQLite".
I need a "SQlite" server and i read that the "uSQLite" can do this, and i'am
try to work with this. But i need something more in VB .NET ?
Or you know other server application ?? Please help me !
Regards,
Paulito
2007/11/6, [EMAIL PROTECTED] <[EMAIL PROTECTED]>:
>
> What wrapper are you using now that works good and what is the
> "uSQLiteServer" wrapper?
>
> RBS
>
> > Hello friends,
> > i use this code to work with a "SQLite" database and works good :
> >
> > Dim cn As New SQLiteConnection("Data Source=C:\sqlite-3_5_1\tst.db")
> > Dim dr As SQLiteDataReader
> > Dim cmd As New SQLiteCommand()
> > cmd.CommandText = "Select * from CFG_USER"
> > cmd.Connection = cn
> > cn.Open()
> > dr = cmd.ExecuteReader()
> >
> > While (dr.Read())
> >MsgBox(dr.GetValue(0))
> > End While
> >
> > dr.Close()
> > cn.Close(
> >
> > What changes i must to do, for use the "uSQLiteServer" wrapper ???
> Please
> > help me !!
> >
> > Thanks,
> > Paulito
> >
> >
> > **
> >
>
>
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>
Re: [sqlite] Sqlite Rename table
On 11/6/07, Valerio Bontempi <[EMAIL PROTECTED]> wrote: > Hi Kees, > > thanks for your solution, it is a very interesting solution. > But I need to rename a table using sql from php. > (this is also the reason for my need of sqlite and not sqlite3, not > supported yet by php) > > Thanks a lot > > Valerio > Use the SQL suggested below in your PHP program. Don't worry about sqlite3 (that is all I have). Just change that to sqlite, and it should work. Lucknow:~/Data/punkish punkish$ sqlite3 foo SQLite version 3.4.2 Enter ".help" for instructions sqlite> CREATE TABLE foo (a, b); sqlite> .s CREATE TABLE foo (a, b); sqlite> INSERT INTO foo VALUES (1, 'blah'); sqlite> INSERT INTO foo VALUES (2, 'booh'); sqlite> SELECT * FROM foo; 1|blah 2|booh sqlite> BEGIN TRANSACTION; sqlite> CREATE TEMPORARY TABLE foo_backup (a, b); sqlite> INSERT INTO foo_backup SELECT a, b FROM foo; sqlite> DROP TABLE foo; sqlite> CREATE TABLE bar (a, b); sqlite> INSERT INTO bar SELECT a, b FROM foo_backup; sqlite> DROP TABLE foo_backup; sqlite> COMMIT; sqlite> SELECT * FROM bar; 1|blah 2|booh sqlite> .s CREATE TABLE bar (a, b); sqlite> .q - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How use 'uSQLiteServer' wrapper with Visual Basic .NET ???
What wrapper are you using now that works good and what is the
"uSQLiteServer" wrapper?
RBS
> Hello friends,
> i use this code to work with a "SQLite" database and works good :
>
> Dim cn As New SQLiteConnection("Data Source=C:\sqlite-3_5_1\tst.db")
> Dim dr As SQLiteDataReader
> Dim cmd As New SQLiteCommand()
> cmd.CommandText = "Select * from CFG_USER"
> cmd.Connection = cn
> cn.Open()
> dr = cmd.ExecuteReader()
>
> While (dr.Read())
>MsgBox(dr.GetValue(0))
> End While
>
> dr.Close()
> cn.Close(
>
> What changes i must to do, for use the "uSQLiteServer" wrapper ??? Please
> help me !!
>
> Thanks,
> Paulito
>
>
> **
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Sqlite Rename table
Hi Kees,
thanks for your solution, it is a very interesting solution.
But I need to rename a table using sql from php.
(this is also the reason for my need of sqlite and not sqlite3, not
supported yet by php)
Thanks a lot
Valerio
2007/11/5, Kees Nuyt <[EMAIL PROTECTED]>:
>
>
> Hi Valerio,
>
> On Mon, 5 Nov 2007 18:51:20 +0100, "Valerio Bontempi"
> <[EMAIL PROTECTED]> wrote:
>
> >Hi All,
> >
> >I'm new in your mailing list.
> >I need to use sqlite and not sqlite3 because of php still support only
> the
> >first version.
> >I read that sqlite3 support table renaming with common sql syntax
> >alter table table1 rename to table2
> >
> >but, although it works fine on sqlite3, it doesn't work on sqlite
> >
> >Can anyone confirm that table renaming isn't supported in sqlite first
> >version?
> >Moreover, if so, is there another way to do it?
> >
> >Thanks and regards
> >
> >Valerio Bontempi
>
> If the name of your table plus a leading and trailing space
> doesn't exist anywhere in your data, you could do :
>
> Windows:
> sqlite old_db .dump |
> awk "{sub(/ oldtable /,\" newtable \");print}" |
> sqlite new_db
>
> Unix:
> sqlite old_db .dump |
> awk '{sub(/ oldtable /," newtable ");print}' |
> sqlite new_db
>
> (all on one line, but I added linefeeds after every pipe
> character for clarity)
>
> Untested, parenthesis in positions where i assume spaces might
> cause some problems, but you get the idea.
>
> HTH
> --
> ( Kees Nuyt
> )
> c[_]
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>
--
*
Valerio Bontempi
Blog: http://mithland.wordpress.com/
*
[sqlite] How use "uSQLiteServer" wrapper with Visual Basic .NET ???
Hello friends,
i use this code to work with a "SQLite" database and works good :
Dim cn As New SQLiteConnection("Data Source=C:\sqlite-3_5_1\tst.db")
Dim dr As SQLiteDataReader
Dim cmd As New SQLiteCommand()
cmd.CommandText = "Select * from CFG_USER"
cmd.Connection = cn
cn.Open()
dr = cmd.ExecuteReader()
While (dr.Read())
MsgBox(dr.GetValue(0))
End While
dr.Close()
cn.Close(
What changes i must to do, for use the "uSQLiteServer" wrapper ??? Please
help me !!
Thanks,
Paulito
**
