Re: [sqlite] Row Level Locking as in InnoDB

[Sylvain Pointeau]

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 11 novembre 2013, Raheel Gupta a écrit :

> @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 write / modify pages IF not locked
> ONLY If writer comes across a locked page wait for it to be released
>
> In this way, multiple threads could do writes. Again I am not an expert but
> from my little understanding this might not remove the leaness. You are the
> expert.
>
> And even I agree that Sqlite must be "lite"
>
>
>
> On Sun, Nov 10, 2013 at 8:39 PM, Simon Slavin 
> >
> wrote:
>
> >
> > 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
> > > the "lite" in SQLite, I would be the most happiest person here :)
> >
> > The main reason you seem to prefer SQLite to other databases is that it's
> > faster.  Adding row-level locking to SQLite would slow it down a lot.
>  As a
> > very simplified explanation, for one SELECT instead of
> >
> > try to lock the database
> > check to see that the lock on the database is yours
> > FOR EACH ROW:
> > figure out where the row's data is
> > read the data
> > unlock the database
> >
> > you have to do
> >
> > FOR EACH ROW:
> > figure out where the row's data is
> > try to lock the row
> > check to see that the lock on the row is yours
> > read the data
> > release the row
> >
> > If your SELECT returns 10 rows you end up doing 50 operations instead of
> > 23.  Which would mean that SQLite was half the speed, and no longer had
> any
> > advantages for you, so you would use something else.
> >
> > Locking is the single hardest thing to get right when writing a DBMS.
> >  SQLite gets a lot of its tininess and speed by implementing the simplest
> > fastest method of locking possible.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org 
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Raheel Gupta]

@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 write / modify pages IF not locked
ONLY If writer comes across a locked page wait for it to be released

In this way, multiple threads could do writes. Again I am not an expert but
from my little understanding this might not remove the leaness. You are the
expert.

And even I agree that Sqlite must be "lite"



On Sun, Nov 10, 2013 at 8:39 PM, Simon Slavin  wrote:

>
> 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
> > the "lite" in SQLite, I would be the most happiest person here :)
>
> The main reason you seem to prefer SQLite to other databases is that it's
> faster.  Adding row-level locking to SQLite would slow it down a lot.  As a
> very simplified explanation, for one SELECT instead of
>
> try to lock the database
> check to see that the lock on the database is yours
> FOR EACH ROW:
> figure out where the row's data is
> read the data
> unlock the database
>
> you have to do
>
> FOR EACH ROW:
> figure out where the row's data is
> try to lock the row
> check to see that the lock on the row is yours
> read the data
> release the row
>
> If your SELECT returns 10 rows you end up doing 50 operations instead of
> 23.  Which would mean that SQLite was half the speed, and no longer had any
> advantages for you, so you would use something else.
>
> Locking is the single hardest thing to get right when writing a DBMS.
>  SQLite gets a lot of its tininess and speed by implementing the simplest
> fastest method of locking possible.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What constitutes one SQL transaction?

[Simon Slavin]

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) prepared statement contains BEGIN
> OR
> * some old prepared statement contained BEGIN and no END has yet occurred
> 
> -AND-
> 
> * the (current) prepared statement doesn't contain END

I'll expand my explanation.  There are two different kinds of SQL commands 
(actually more, but we only care about two here).

Some statements do stuff with the database: read or write it.
Other statements start a transaction (BEGIN) or stop a transaction (END/COMMIT, 
ROLLBACK).

The _prepare, _step, _finalize sequence are used to execute both kinds of 
statement.  You can use _exec() too, but internally that just calls _prepare, 
_step, _finalize itself.

Theoretically you cannot access the database unless you're in a transaction.  
For example, if you were to do a SELECT and hadn't done a BEGIN yet, SQLite 
could return an error message and tell you that you'd forgotten to start a 
transaction.  However, makes programming code very bulky, so instead of doing 
that when SQLite notices you accessing the database outside a transaction it 
helpfully wraps the transaction in a BEGIN and END for you.

So here's your original question:

> For instance, can we safely say that the successive calls
> 
> _prepare_v2()
> _step()
> ...
> _step()
> _finalize()
> 
> always and everywhere constitute one transaction and hence, involve a single 
> -journal file?

Your example would involve a single journal file (== transaction) just for that 
one command if and only if you had not properly started a transaction yourself. 
 For instance, a SELECT or INSERT without a BEGIN before it.  In fact, I think 
it holds for any database access in which you never declare a BEGIN yourself.  
Here are some other possibilities:

BEGIN
SELECT ...
INSERT ...
INSERT ...
END

Here one journal file is created with the BEGIN and lasts until the END.  All 
five things are done while it lasts.

BEGIN EXCLUSIVE
pause for 20 seconds
END

Here you have a journal file which is created and destroyed but the database is 
never accessed during it.  Technically, the BEGIN and END commands are both 
processed using _prepare, _step, _finalize.  So the journal file is still 
created and lasts for both commands even though no database access occurs.

I have handwaved some complications.  Sorry.  Does it help ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Aleksey Tulinov]

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 considered 0 if only 128 decimal 
digits are significant to you.


SELECT produce empty set which is nothing in algebra of sets, max() 
produce scalar value where nothing is undefined if you're looking at 
numbers. Hence max() on empty set produce NULL, which is nothing in SQL.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What constitutes one SQL transaction?

[L. Wood]

> 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 BEGIN and no END has yet occurred

-AND-

* the (current) prepared statement doesn't contain END


Did I get it right?   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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 to update the table.  In fact there's no need to store the 
>> rank values at all.
> 
> There is.
> Consider the fact that the user changes the rank and exit the program
> with the saving. Then he select the option "Reset the league".
> Where can I get the original rank from?

What does 'original' rank depend on ?  The number of points the player had when 
they were added to the league ?  Whatever it is, store that value in the table 
and use that to figure out what rank the player is by counting how many players 
have the same or more points than they do.  It'll be something like

SELECT count(*) FROM league WHERE pointsWhenJoined >= [this player's 
pointsWhenJoined]

You only need to do this when you actually need to know the number.  You don't 
need to keep updating a lot of player's points every time something happens, 
and this saves you a lot of programming and time.

>>> Now, I would expect for the max() function in this case to be
>>> evaluated to 0 and not NULL.
>>> This is plain mathematics: max value of nothing is nothing which
>>> mathematically 0.
>> 
>> No.  Max of nothing is nothing.  And the way you express 'nothing' in SQL is 
>> NULL.  NULL is not 0 or 0.0.  NULL is not the empty string "".  NULL is not 
>> the BLOB x''.  NULL means 'value missing'.
> 
> Well from strictly mathematical point of view maximum or minimum of
> nothing is nothing. And since nothing is 0, than it is zero.

No, nothing is not zero.  Read what I wrote again.  Try this: if I want you to 
be very very quiet do I say to you "I want you to say zero." ?  If you go to a 
bank you don't have an account with and ask "How much money do I have in my 
account ?" do they answer "You have zero dollars in your account, Sir." ?  
Google

difference between nothing and zero

and if it's still not clear, ask someone who teaches maths.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What constitutes one SQL transaction?

[Simon Slavin]

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
> 
> _prepare_v2()
> _step()
> ...
> _step()
> _finalize()
> 
> always and everywhere constitute one transaction and hence, involve a single 
> -journal file?

No.  However, they cannot constitute /more/ than one transaction because they 
area all part of a single SQL operation.

You can create your own transactions using BEGIN.  If you do this, you can put 
any number of operations in that transaction until you use END/COMMIT to end 
it, or a number of other things to abandon it.

If you start a SQLite operation that requires access to the database (SELECT, 
INSERT, DELETE, UPDATE) and have not already started a transaction, SQLite 
helpfully wraps that single operation in a transaction for you.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE and default values

[Simon Slavin]

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,
>  name text,
>  last_changed text default current_date
> )
> 
> That works for INSERTs, but seems not to work for UPDATEs. Is that right? 
> What to do?

CREATE TRIGGER  AFTER UPDATE ON  ...



Not as simple a solution as setting a default, but it should work.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Igor Korot]

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 values at all.

There is.
Consider the fact that the user changes the rank and exit the program
with the saving. Then he select the option "Reset the league".
Where can I get the original rank from?

>
>> Now, I would expect for the max() function in this case to be
>> evaluated to 0 and not NULL.
>> This is plain mathematics: max value of nothing is nothing which
>> mathematically 0.
>
> No.  Max of nothing is nothing.  And the way you express 'nothing' in SQL is 
> NULL.  NULL is not 0 or 0.0.  NULL is not the empty string "".  NULL is not 
> the BLOB x''.  NULL means 'value missing'.
>
> As another contributor to the thread noted, suppose you have a list of any 
> number of values.  You remove them one by one.  At each stage, the maximum of 
> the remaining list can only remain the same or get smaller.  Why should 
> removing the last number somehow /increase/ the maximum of a list ?  Now 
> suppose your list is -18, -41, -23 and you remove them in any order.

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.

>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] What constitutes one SQL transaction?

[L. Wood]

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 transaction and hence, involve a single 
-journal file?   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UPDATE and default values

[Ulrich Goebel]

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 INSERTs, but seems not to work for UPDATEs. Is that 
right? What to do?


Ulrich


--
Ulrich Goebel
Paracelsusstr. 120, 53177 Bonn
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need suggestion for database scheama

[RSmith]

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 for 
table names (files, tracks, albums) because each *row* represents the singular -- a file, a track, an album -- and tables as we 
know are made of rows. But some people find that pedantic, and they're not wrong. --jkl


Firstly, any convention that makes things easier to understand and keep track of complex systems is a worthwhile pursuit and hardly 
pedantic, and very much agreed on the plural use.


Allow me to add to the naming perspective though - while I agree that redundant name-padding is a hindrance rather than a boon to 
simplicity and understanding, it is always (on the programming side at least) wise to prefix variable names with a function 
descriptors. "tbl_users" or "tbl_contacts",  as opposed to just users / contacts, is clearly marked as to where they belong in the 
large hierarchy of objects needed to keep track of, and can be distinguished by one glance from an Index or a Trigger or such.


Further to this, when documenting your work or simply listing and sorting different objects it is very helpful to have the 
readily-available methods of sorting lists have the added benefit of keeping similar objects together in a group - and / or - have 
simple Like / glob / match (or whatever likeness function exists in your system) be able to distinguish one sort of object from the 
other. How much this helps from a pure Database-design point of view is rather debatable, but it should be the very least of a 
hindrance.


Of course, all of the points above goes down the rabbit hole when I see people dutifully prefix variables and then get lazy on the 
descriptors as to end up with things like  "tbl_1",  "tbl_2", "trig_t1",  "tbl_myTable" or "tbl_records".  Words cannot describe





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Combining two databases into one

[Ulrich Goebel]

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 db2.T1
UNION SELECT ... from db3.T1
...

Then it would not be necessary to insert the data from some databases 
into another 'from time to time', but you would have all the data 'just 
when you need it'.

For details see http://www.sqlite.org/lang_select.html#compound

Ulrich


Am 10.11.2013 18:49, schrieb Dave Wellman:

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 single
SQLITE database. We'd still be using multiple tables.



Obviously our application can open multiple databases, select all rows from
T1 in one database and insert them into T1 in another database and continue
doing that until we're complete. I can't see any reason why that shouldn't
technically work and it will probably be fast enough from a performance
perspective.



Is there a 'smart' way of doing this using built-in functaionality of
sqlite? I looked for a 'merge databases' command or something similar but
couldn't find anything.



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 !)?



Cheers,

Dave





Ward Analytics Ltd - information in motion

Tel: +44 (0) 118 9740191

Fax: +44 (0) 118 9740192

www:   http://www.ward-analytics.com



Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR

Registered company number: 3917021 Registered in England and Wales.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



--
Ulrich Goebel
Paracelsusstr. 120, 53177 Bonn
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unicode support in SQLite

[Gert Van Assche]

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 following functions,
>> statements and collations:
>>
>
> I've updated extension, examples and documentation, now it's easier to
> link extension statically. Everything, including new prebuilt binaries, is
> available on BitBucket, changelog is available here:
> https://bitbucket.org/alekseyt/nunicode/src/master/CHANGELOG
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Combining two databases into one

[Simon Slavin]

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 the best way I know of to do it, bearing in mind your entire 
requirements.  I don't see anything else wrong with your proposed procedure.  
Don't forget to DELETE FROM before you insert your new rows.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[John McKown]

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, 2013 at 10: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 values at all.
>
> > Now, I would expect for the max() function in this case to be
> > evaluated to 0 and not NULL.
> > This is plain mathematics: max value of nothing is nothing which
> > mathematically 0.
>
> No.  Max of nothing is nothing.  And the way you express 'nothing' in SQL
> is NULL.  NULL is not 0 or 0.0.  NULL is not the empty string "".  NULL is
> not the BLOB x''.  NULL means 'value missing'.
>
> As another contributor to the thread noted, suppose you have a list of any
> number of values.  You remove them one by one.  At each stage, the maximum
> of the remaining list can only remain the same or get smaller.  Why should
> removing the last number somehow /increase/ the maximum of a list ?  Now
> suppose your list is -18, -41, -23 and you remove them in any order.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
This is clearly another case of too many mad scientists, and not enough
hunchbacks.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] report bugs-update

[Petite Abeille]

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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] report bugs-update

[BULUSLI]

   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 mother tongue isn't English,so)

Version:
  my Windows: SQLite 3.8.1 2013-10-17 12:57:35 
c78be6d786c19073b3a6730dfe3fb1be54f5657a
  my Linux :  SQLite 3.7.15.2 2013-01-09 11:53:05 
c0e09560d26f0a6456be9dd3447f5311eb4f238f
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Combining two databases into one

[Dave Wellman]

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 single
SQLITE database. We'd still be using multiple tables. 

 

Obviously our application can open multiple databases, select all rows from
T1 in one database and insert them into T1 in another database and continue
doing that until we're complete. I can't see any reason why that shouldn't
technically work and it will probably be fast enough from a performance
perspective.

 

Is there a 'smart' way of doing this using built-in functaionality of
sqlite? I looked for a 'merge databases' command or something similar but
couldn't find anything. 

 

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 !)?

 

Cheers,

Dave

 

 

Ward Analytics Ltd - information in motion

Tel: +44 (0) 118 9740191

Fax: +44 (0) 118 9740192

www:   http://www.ward-analytics.com

 

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR

Registered company number: 3917021 Registered in England and Wales.

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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 and not NULL.
> This is plain mathematics: max value of nothing is nothing which
> mathematically 0.

No.  Max of nothing is nothing.  And the way you express 'nothing' in SQL is 
NULL.  NULL is not 0 or 0.0.  NULL is not the empty string "".  NULL is not the 
BLOB x''.  NULL means 'value missing'.

As another contributor to the thread noted, suppose you have a list of any 
number of values.  You remove them one by one.  At each stage, the maximum of 
the remaining list can only remain the same or get smaller.  Why should 
removing the last number somehow /increase/ the maximum of a list ?  Now 
suppose your list is -18, -41, -23 and you remove them in any order.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Igor Korot]

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 choreography.
>
> He's trying to keep each player's rank in his league table.  And he wants the 
> rank column for all players to be updated each time he inserts a new player 
> in the table, so if he inserts a new player who is fourth, all players that 
> were fourth or after fourth move down one rank.

This is what I'm looking for.
Consider the players table data:

id  player_name  rank
1PlayerA  1
2PlayerB   2
3PlayerC  3
4PlayerD  4

Now when the new league is created I specify that only PlayerB and
PlayerC should be in there.
And so I'm writing the following query:

INSERT INTO playersinleague() SELECT * FROM players ORDER BY rank;

Now in playersinleague table I'd like to see following:

idcurrent_rank  original_rank
2 1  1
3  2 2

So that rank in playersinleague table should start from 1 and be
incremented by 1.

>
> Once again I suggest using a TRIGGER is the wrong way to do it:
>
> A) You have to write a trigger for when a player joins a league.
> B) You have to write an equivalent trigger for when a player leaves a league.
> C) You have to write an equivalent trigger for when a player moves position.
> D) That's a lot of checking and operations any time any player does anything.

Not really.
I'm updating the table when the program exit. And I already have this
logic. It does not require to write any triggers.
But because the ranks in the main pool (players table) are not ordered
the same way as they could be added to the league (playersinleague
table) it can be updated with the trigger.

I hope I explained everything and it's now clearer.
Now when the league is created there is no records and, therefore,
max(current_rank) is NULL and so "1 + max(current_rank)" does evaluate
to NULL.

Now, I would expect for the max() function in this case to be
evaluated to 0 and not NULL.
This is plain mathematics: max value of nothing is nothing which
mathematically 0. Which means that I can use this value in
mathematical expressions.

Thank you.

>
> Instead I think it makes more sense just to deduce the ranks from the results 
> of your SELECT, when you select the players in points order.  The player who 
> comes off first is ranked 1.  Not only is it simple but it requires no extra 
> operations, so it speeds up all INSERT/UPDATE/DELETE.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[John McKown]

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. Yes, I know that it can indeed do that work. But if I need
multi-system, multi-user, redundant RDMS, I use PostgreSQL. It is "heavy"
but works well for me. I don't want to replace PostgreSQL with SQLite, in
general. Again, I know that there are massive SQLite DBs out there. But are
they being hit by 100+ users concurrently? I truly don't know.

But if it is up for a vote, I vote "stay lean".


On Sun, Nov 10, 2013 at 9:09 AM, Simon Slavin  wrote:

>
> 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
> > the "lite" in SQLite, I would be the most happiest person here :)
>
> The main reason you seem to prefer SQLite to other databases is that it's
> faster.  Adding row-level locking to SQLite would slow it down a lot.  As a
> very simplified explanation, for one SELECT instead of
>
> try to lock the database
> check to see that the lock on the database is yours
> FOR EACH ROW:
> figure out where the row's data is
> read the data
> unlock the database
>
> you have to do
>
> FOR EACH ROW:
> figure out where the row's data is
> try to lock the row
> check to see that the lock on the row is yours
> read the data
> release the row
>
> If your SELECT returns 10 rows you end up doing 50 operations instead of
> 23.  Which would mean that SQLite was half the speed, and no longer had any
> advantages for you, so you would use something else.
>
> Locking is the single hardest thing to get right when writing a DBMS.
>  SQLite gets a lot of its tininess and speed by implementing the simplest
> fastest method of locking possible.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
This is clearly another case of too many mad scientists, and not enough
hunchbacks.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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 column for all players to be updated each time he inserts a new player in 
the table, so if he inserts a new player who is fourth, all players that were 
fourth or after fourth move down one rank.

Once again I suggest using a TRIGGER is the wrong way to do it:

A) You have to write a trigger for when a player joins a league.
B) You have to write an equivalent trigger for when a player leaves a league.
C) You have to write an equivalent trigger for when a player moves position.
D) That's a lot of checking and operations any time any player does anything.

Instead I think it makes more sense just to deduce the ranks from the results 
of your SELECT, when you select the players in points order.  The player who 
comes off first is ranked 1.  Not only is it simple but it requires no extra 
operations, so it speeds up all INSERT/UPDATE/DELETE.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Simon Slavin]

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
> the "lite" in SQLite, I would be the most happiest person here :)

The main reason you seem to prefer SQLite to other databases is that it's 
faster.  Adding row-level locking to SQLite would slow it down a lot.  As a 
very simplified explanation, for one SELECT instead of

try to lock the database
check to see that the lock on the database is yours
FOR EACH ROW:
figure out where the row's data is
read the data
unlock the database

you have to do

FOR EACH ROW:
figure out where the row's data is
try to lock the row
check to see that the lock on the row is yours
read the data
release the row

If your SELECT returns 10 rows you end up doing 50 operations instead of 23.  
Which would mean that SQLite was half the speed, and no longer had any 
advantages for you, so you would use something else.

Locking is the single hardest thing to get right when writing a DBMS.  SQLite 
gets a lot of its tininess and speed by implementing the simplest fastest 
method of locking possible.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Igor Tandetnik]

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 current_rank IS NULL;
END;


This trigger makes no sense at all. It says: when a row is inserted with 
current_rank of NULL, take the value of current_rank *from that row* 
(which is, of course, NULL), add 1 to it (which produces NULL), and 
write it back to current_rank (which thus remains NULL). The whole thing 
is a very elaborate no-op.


I suspect you want to drop "WHERE id = new.id" part from the subquery.

Also, "original_rank = current_rank" uses the current value of 
current_rank before the UPDATE (that is, NULL), not the new value.



SELECT * FROM playersinleague WHERE id=1 AND playerid=1;
1|1|1|27|42.0|42.0|0|0|||0

I didn't specify current_rank on the INSERT query which means the
field shuold be NULL.


Yes it is. Which is what the SELECT statement shows. Which part is 
surprising?



Also will the UPDATE in trigger do the right thing and correctly
update the original_rank?


That rather depends on what value you deem to be the correct one. You've 
never explained the desired outcome of all this choreography.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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 coalesce() doesn't work, I suggest 
you use ifnull() instead.

Also, I agree with John's analysis: max() of zero items is NULL.  I'm not sure 
what stdev() of zero items is, but fortunately SQLite doesn't have that 
function.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] executing queries on normalized database

[Simon Slavin]

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?

You should not have to write twelve queries in your own programming.  If you 
have set up your FOREIGN KEYs correctly, SQLite should be doing that for you.  
The foreign keys facility should have allowed you to move some of your business 
model from your own programming into SQLite, which should be dealing with it in 
one place in a consistent way.

>  In delete case, do always need to check in parent table whether
> child key refers to any other rows in parent table?

Again, if you have set up your foreign keys correctly, SQLite should be doing 
the checking or deleting for you.  Whether SQLite will prevent you from 
deleting, or delete rows from other tables, depends which of these actions you 
have set up:



Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Howard Chu]

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 code and run it yourself.


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
the "lite" in SQLite, I would be the most happiest person here :)


You seem to enjoy asking a lot of others, without regard to cost.

--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[John McKown]

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 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;




On Sun, Nov 10, 2013 at 2:39 AM, Igor Korot  wrote:

> 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.
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Raheel Gupta]

>> 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 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
the "lite" in SQLite, I would be the most happiest person here :)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] executing queries on normalized database

[dd]

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
child key refers to any other rows in parent table?

Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Igor Korot]

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, ALL,
>
> CREATE TABLE playersinleague(
> id integer
> playerid integer
> ishitter char
> age integer
> value double
> currvalue double
> draft boolean
> isnew char(1)
> current_rank integer
> original_rank integer
> deleted integer
> foreign key(id) references leagues(id)
> foreign key(playerid) references players(playerid)
> );
>
> 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 current_rank IS NULL;
> END;
>
> or do it this way:
>
> DROP TRIGGER playersinleague_insert;
> 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 rowid = new.rowid;
> END;
>
> After that:
>
> INSERT INTO playersinleague(id,playerid,ishitter,age,value,currvalue,dra
> ft,isnew,deleted) SELECT 1, players.playerid, players.ishitter, players.age, 
> pla
> yers.value, players.value, 0, "0", 0 FROM players WHERE (teamid >= 1 AND 
> teamid
> <= 15) OR (teamid >= 16 AND teamid <= 30) ORDER BY rank;
>
> SELECT * FROM playersinleague WHERE id=1 AND playerid=1;
> 1|1|1|27|42.0|42.0|0|0|||0
>
> I didn't specify current_rank on the INSERT query which means the
> field shuold be NULL.
>
> I think I am doing something wrong but I don't see what.
>
> Could someone please help?
> Also will the UPDATE in trigger do the right thing and correctly
> update the original_rank?
>
> Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users