Re: [sqlite] Row Level Locking as in InnoDB

Hello, If I would have one wish, it would not be the row level locking but the merge syntax, so usefulf to update, insert or update in 1 command (no insert or replace is not an equivalent), and in general it would be good to implement the sql 2003. Just a wish. Best regards, Sylvain Le lundi

Re: [sqlite] Row Level Locking as in InnoDB

@simon I guess a Row level locking could be difficult but a Page Level locking could be not that difficult. ATM "db level locking" : If DB locked throw busy error In not locked lock db, let the writer do its thing For Page level locking (I think you could allow something like) : Let writer

Re: [sqlite] What constitutes one SQL transaction?

On 10 Nov 2013, at 11:30pm, L. Wood wrote: >> Simon Slavin wrote: >> No. > > So by "no", you mean that the only other alternative is that the C calls I > mentioned can constitute ZERO transactions, and this case happens only if > > * the (current)

Re: [sqlite] What is wrong with this trigger?

On 11/11/2013 12:36 AM, Igor Korot wrote: Well from strictly mathematical point of view maximum or minimum of nothing is nothing. And since nothing is 0, than it is zero. Thank you. max() can't simply pull 0 out of the air, for it 0 is the same random number as 1e-129 which might be also

Re: [sqlite] What constitutes one SQL transaction?

> Simon Slavin wrote: > No. So by "no", you mean that the only other alternative is that the C calls I mentioned can constitute ZERO transactions, and this case happens only if * the (current) prepared statement contains BEGIN OR * some old prepared statement contained

Re: [sqlite] What is wrong with this trigger?

On 10 Nov 2013, at 10:36pm, Igor Korot wrote: > On Sun, Nov 10, 2013 at 8:44 AM, Simon Slavin wrote: > >> On 10 Nov 2013, at 4:26pm, Igor Korot wrote: >> >>> I'm updating the table when the program exit. >> >> There is not need

Re: [sqlite] What constitutes one SQL transaction?

On 10 Nov 2013, at 10:30pm, L. Wood wrote: > SQLite creates a -journal file for each single transaction. But what exactly > is "one transaction" and when does it happen in terms of the C functions? > > For instance, can we safely say that the successive calls > >

Re: [sqlite] UPDATE and default values

On 10 Nov 2013, at 9:03pm, Ulrich Goebel wrote: > I want to have a column 'last_changed' in a table, which shows the date of > the last change (inser or update) of the row. I though the default value in > the CREATE TABLE was a good idea: > > CREATE TABLE ( > id integer,

Re: [sqlite] What is wrong with this trigger?

Simon, On Sun, Nov 10, 2013 at 8:44 AM, Simon Slavin wrote: > > On 10 Nov 2013, at 4:26pm, Igor Korot wrote: > >> I'm updating the table when the program exit. > > There is not need to update the table. In fact there's no need to store the > rank

[sqlite] What constitutes one SQL transaction?

SQLite creates a -journal file for each single transaction. But what exactly is "one transaction" and when does it happen in terms of the C functions? For instance, can we safely say that the successive calls _prepare_v2() _step() ... _step() _finalize() always and everywhere constitute one

[sqlite] UPDATE and default values

Hallo, I want to have a column 'last_changed' in a table, which shows the date of the last change (inser or update) of the row. I though the default value in the CREATE TABLE was a good idea: CREATE TABLE ( id integer, name text, last_changed text default current_date ) That works for

Re: [sqlite] Need suggestion for database scheama

On 2013/11/08 05:47, James K. Lowden wrote: Not that you asked, but I also suggest you consider dropping the "Tbl" from the table names. Noting that at table is a table in its name is like calling every file "data". It makes it harder to read and conveys no information. I myself prefer plurals

Re: [sqlite] Combining two databases into one

May be there is a simple way depending of what means 'combine all the data'. If that means just _see_ all that data (without the oportunity to manipulate), there could be defined a VIEW which does a compound SELECT like CREATE VIEW combined_all as SELECT ... from db1.T1 UNION SELECT ... from

Re: [sqlite] Unicode support in SQLite

Very nice! Thanks for sharing, Aleksey. 2013/11/9 Aleksey Tulinov > On 11/04/2013 11:50 AM, Aleksey Tulinov wrote: > > Hey, > > > As you can see, this is truly full Unicode collation and case mapping >> with untailored special casing. Extension provides the

Re: [sqlite] Combining two databases into one

On 10 Nov 2013, at 5:49pm, Dave Wellman wrote: > Ah! I may have just found the answer. If I've got databases db1 and db2 > attached, can I use something like 'insert into db1.t1 select * from > db2.t1;' (assuming that the 't1' definitions are the same !)? That's

Re: [sqlite] What is wrong with this trigger?

If Igor "must have a number" for the "max", even when there are no rows, then I'm certain that the coalesce() function is exactly what he needs to use. coalesce(max(id),0)) will give the maximum "id" value of the result set or 0 if there are no results in the result set (NULL). On Sun, Nov 10,

Re: [sqlite] report bugs-update

On Nov 10, 2013, at 1:51 AM, BULUSLI wrote: > hello Sir,I don't Know this isn't a bug http://www.sqlite.org/compile.html#enable_update_delete_limit ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] report bugs-update

hello Sir,I don't Know this isn't a bug In my windows machine I used "update test set id=0 limit 0,1" and In my linux machine use"update test set id=0 limit 0,1" answer is different. In windows,he is tell me"Error: near "limit": syntax error" But in linux is right! So this is aBug? (my

[sqlite] Combining two databases into one

Hi, I'm looking at a use case for SQLITE within one of our applications. One potential scenario would be for multiple, asynchronous processes to build their own database. Each one would be populating a different table. At some point it would be 'really useful' to combine all the data into a

Re: [sqlite] What is wrong with this trigger?

On 10 Nov 2013, at 4:26pm, Igor Korot wrote: > I'm updating the table when the program exit. There is not need to update the table. In fact there's no need to store the rank values at all. > Now, I would expect for the max() function in this case to be > evaluated to 0

Re: [sqlite] What is wrong with this trigger?

Simon et al, On Sun, Nov 10, 2013 at 7:18 AM, Simon Slavin wrote: > > On 10 Nov 2013, at 3:05pm, Igor Tandetnik wrote: > >> That rather depends on what value you deem to be the correct one. You've >> never explained the desired outcome of all this

Re: [sqlite] Row Level Locking as in InnoDB

Just a personal observation from the peanut gallery (my uninformed opinion). I like SQLite pretty much as is. When I use it, I want reliable (ACID), fast, and SQL compliant. I use SQLite more like an "embeded" or "single user" SQL engine. I don't use it for a really hairy data base application.

Re: [sqlite] What is wrong with this trigger?

On 10 Nov 2013, at 3:05pm, Igor Tandetnik wrote: > That rather depends on what value you deem to be the correct one. You've > never explained the desired outcome of all this choreography. He's trying to keep each player's rank in his league table. And he wants the rank

Re: [sqlite] Row Level Locking as in InnoDB

On 10 Nov 2013, at 12:05pm, Raheel Gupta wrote: >>> I can't think of any other single feature that would remove the "lite" > > I am not a database expert. If you say so, it must be the case. > But if there is a way to implement concurrent writers in SQLite maintaining >

Re: [sqlite] What is wrong with this trigger?

On 11/10/2013 12:54 AM, Igor Korot wrote: CREATE TRIGGER playersinleague_insert AFTER INSERT on playersinleague BEGIN UPDATE playersinleague SET current_rank = 1+ (select max( current_rank ) from playersinleague WHERE id = new.id), original_rank = current_rank WHERE id = new.id AND

Re: [sqlite] What is wrong with this trigger?

On 10 Nov 2013, at 12:34pm, John McKown wrote: > If you need a particular "default" value instead of a NULL, use the > coalesce() function. > > select coalesce(max(current_rank),0) FROM playersinleague WHERE id = 1; Purely for clarity's sake, and not to say

Re: [sqlite] executing queries on normalized database

On 10 Nov 2013, at 10:54am, dd wrote: > I have two tables in my database. > > After applying normalization, there are twelve tables with foreign > key support. > > For insert/delete operations, it has to execute twelve queries > instead of two. Is it recommended way?

Re: [sqlite] Row Level Locking as in InnoDB

Raheel Gupta wrote: Look at the performance difference between BDB and SQLite3 here http://symas.com/mdb/microbench/#sec1 I did, and I really cant comment on that. The results are of 2012 and its almost 2013. You should update the page with a newer result set. Or you could just download the

Re: [sqlite] What is wrong with this trigger?

I would expect NULL (empty string) as the result from a max (or any other summary) query which has "no rows". Any particular value wouldn't make too much sense to me. Why any particular value? 0 doesn't really make sense. Well no more or less sense than -1 or even -10372 (picked "at random"). If

Re: [sqlite] Row Level Locking as in InnoDB

>> Look at the performance difference between BDB and SQLite3 here http://symas.com/mdb/microbench/#sec1 I did, and I really cant comment on that. The results are of 2012 and its almost 2013. You should update the page with a newer result set. >> I can't think of any other single feature that

[sqlite] executing queries on normalized database

Hi, I have two tables in my database. After applying normalization, there are twelve tables with foreign key support. For insert/delete operations, it has to execute twelve queries instead of two. Is it recommended way? In delete case, do always need to check in parent table whether

Re: [sqlite] What is wrong with this trigger?

I just tried to do: SELECT max(current_rank) FROM playersinleague WHERE id = 1; and I got an empty string and not 0. Is this a bug? Should max(field) return 0 if there is no records that satisfy criteria? Thank you. On Sat, Nov 9, 2013 at 9:54 PM, Igor Korot wrote: > Hi,