Re: [sqlite] SQLite version 3.6.18 scheduled for Monday

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

D. Richard Hipp wrote:
> There are multiple reasons for changing from CVS to Fossil.

The reasons listed are true for pretty much any of the current popular
distributed source control systems.  They have various minor under the hood
differences (eg if they record the existence of directories), as well as
interaction styles (eg read about git and the index).  So far Fossil is
lacking a means of converting back out into some other system.  Tailor is a
good tool for doing this for other systems - it replays the changes from one
repository to another - http://progetti.arstecnica.it/tailor

Not mentioned is the unique functionality of Fossil providing a bug tracker
and wiki as part of the repository.  There are projects to add wiki's and
bug trackers to other version control systems, but they are problematic
because the original system design never anticipated the wiki and bug
tracker functionality.  It is generally considered best developer practise
for a bug/feature tracker ticket to exist corresponding to each source
change, which Fossil makes *very* easy to do, as well as document background
in the wiki.   This is the very good reason to use Fossil instead of the
other DVCS systems.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkqodxAACgkQmOOfHg372QTBegCg0f8kZj4aky9m/QRKWhoRI+Hx
8eUAnAznE8xE+wYngZnGwi4VoBE18PAz
=/bZL
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] One more SQL statement question

[Dennis Volodomanov]

> The not-in subselect could be expensive, depending on table size. If
> it turns out to be expensive, you might consider reference counting.

Thank you for the hint. It's done on an integer column with an index, so 
hopefully won't be too bad. Further tests will show though of course.

   Dennis

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

Re: [sqlite] One more SQL statement question

[Jim Showalter]

The not-in subselect could be expensive, depending on table size. If 
it turns out to be expensive, you might consider reference counting.

- Original Message - 
From: "Dennis Volodomanov" 
To: "General Discussion of SQLite Database" 
Sent: Wednesday, September 09, 2009 5:17 PM
Subject: Re: [sqlite] One more SQL statement question


>> sqlite> create trigger UpdateData after update on tablea begin
>>...> delete from tableb where id=old.refb and id not in ( select
>> refb from tablea );
>
> Yep, that's exactly what I need.
>
> Thank you!
>
>   Dennis
>
> ___
> 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] server process gives better concurrency - why?

[Iker Arizmendi]

Simon Slavin wrote:
> On 10 Sep 2009, at 12:02am, Iker Arizmendi wrote:
> 
>> The assumption being that a lock facility that can handle these issues
>> is needed by any concurrency scheme (MVCC, shadow pages, etc) and so  
>> can
>> be thought about independently. Does the ability of a client-server  
>> DB to
>> support multiple writers follow solely from the fact that it  
>> centralizes
>> the lock bookkeeping and that it can easily detect death of its  
>> children?
> 
> 'Death of children' is an issue in some DBMS systems (like SQLite  
> currently is), but not others.  In particular, if you are using MVCC  
> correctly, death of children hardly matters at all.  All it means is  
> that the DBMS engine will be using a little extra memory (and no extra  
> processing power) until it decides that a child which hasn't done  
> anything for an hour is probably not going to come back to life.
> 
> Read up on MVCC.  Read up on how to support ACID in a multiuser  
> concurrent system.  Neither allow a client to explicitly lock  
> anything.  Locks are handled by low-level processes inside the engine  
> and last fractions of a second.
> 
> Simon.

Even with MVCC write locks need to be kept somewhere so it does not
address the problems that are resolved by a persistent server process.
In any event, (I think) SQLite uses write ahead logging and infers
the death of a writer by the presence of the rollback journal so
this is the scenario of interest.

Iker

-- 
Iker Arizmendi
AT Labs - Research
Speech and Image Processing Lab
e: i...@research.att.com
w: http://research.att.com
p: 973-360-8516

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

Re: [sqlite] One more SQL statement question

[Dennis Volodomanov]

> sqlite> create trigger UpdateData after update on tablea begin
>...> delete from tableb where id=old.refb and id not in ( select
> refb from tablea );

Yep, that's exactly what I need.

Thank you!

   Dennis

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

Re: [sqlite] server process gives better concurrency - why?

[Iker Arizmendi]

Cory Nelson wrote:
> 
> Right now sqlite works over a network via NFS and the like -- if you
> wanted to keep it an embedded library (with no server), you'd have to
> drop support for that.
> 
> PS. please stop top posting!
> 
> 

It looks like any move away from file based locks suffers from this
drawback. Perhaps the locking scheme could be offered as a compile
time option or left to the application as a pragma at the cost of
making things a bit less "zero-conf".

Iker

-- 
Iker Arizmendi
AT Labs - Research
Speech and Image Processing Lab

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

Re: [sqlite] server process gives better concurrency - why?

[Simon Slavin]

On 10 Sep 2009, at 12:02am, Iker Arizmendi wrote:

> The assumption being that a lock facility that can handle these issues
> is needed by any concurrency scheme (MVCC, shadow pages, etc) and so  
> can
> be thought about independently. Does the ability of a client-server  
> DB to
> support multiple writers follow solely from the fact that it  
> centralizes
> the lock bookkeeping and that it can easily detect death of its  
> children?

'Death of children' is an issue in some DBMS systems (like SQLite  
currently is), but not others.  In particular, if you are using MVCC  
correctly, death of children hardly matters at all.  All it means is  
that the DBMS engine will be using a little extra memory (and no extra  
processing power) until it decides that a child which hasn't done  
anything for an hour is probably not going to come back to life.

Read up on MVCC.  Read up on how to support ACID in a multiuser  
concurrent system.  Neither allow a client to explicitly lock  
anything.  Locks are handled by low-level processes inside the engine  
and last fractions of a second.

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

Re: [sqlite] One more SQL statement question

[Simon Davies]

2009/9/10 Dennis Volodomanov :
> Hello,
>
> I have one more SQL query question - the people on this list have been very 
> helpful in the past, so thank you!
>
> I'm trying to create a trigger that would delete unreferenced rows from a 
> table.
>
> Let's say the schema is like this:
>
> TABLEA ( ID INTEGER PRIMARY KEY, REFB INTEGER );
> TABLEB ( ID INTEGER PRIMARY KEY, DATA );
>
> And let's say the data is like this:
>
> TABLEA:
> 1|1
>
> TABLEB:
> 1|dataA
> 2|dataB
>
> Now, I update the record in TABLEA and change REFB to be REFB=2
>
> I've got the following trigger, which doesn't work:
>
> CREATE TRIGGER UpdateData AFTER UPDATE ON TABLEA
> BEGIN
>        DELETE FROM TABLEB WHERE TABLEB.ID IN ( SELECT TABLEB.ID FROM TABLEB 
> LEFT JOIN TABLEA ON TABLEA.REFB=TABLEB.ID WHERE TABLEB.ID=OLD.REFB AND 
> TABLEB.ID NOT IN ( TABLEA.REFB ) );
> END
>
> If I run just the SELECT statement within the brackets above (supplying 1 
> instead of OLD.REFB of course), it seems to produce the correct row number 
> (1), but if I run the complete DELETE statement, nothing happens.

This is simpler and appears to do what you want:


sqlite> create table TABLEA ( ID INTEGER PRIMARY KEY, REFB INTEGER );
sqlite> create table TABLEB ( ID INTEGER PRIMARY KEY, DATA );
sqlite> insert into tableb values( 1, 'data1' );
sqlite> insert into tableb values( 2, 'data2' );
sqlite> insert into tablea values( 1, 1 );
sqlite>
sqlite> select * from tableb;
1|data1
2|data2
sqlite>
sqlite> select * from tablea;
1|1
sqlite>
sqlite> create trigger UpdateData after update on tablea begin
   ...> delete from tableb where id=old.refb and id not in ( select
refb from tablea );
   ...> end;
sqlite>
sqlite>
sqlite> update tablea set refb=2;
sqlite>
sqlite>
sqlite> select * from tableb;
2|data2
sqlite>


>
> Thank you in advance,
>
>   Dennis

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

Re: [sqlite] One more SQL statement question

[Dennis Volodomanov]

> This statement makes no sense to me. Why not simply
> 
> delete from TABLEB where ID=old.REFB and ID != new.REFB;

But entries in TABLEB may be used by other entries in TABLEA and I don't want 
them deleted if they're used by at least one entry in TABLEA.

Thanks,

   Dennis

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

Re: [sqlite] How long should Insert take?

[Rod Dav4is]

Well, That certainly did the trick. Much obliged!
Rate now > 2000 inserts/second including transaction processing and 3390 
inserts.
-R.

Igor Tandetnik wrote:
> Rod Dav4is  wrote:
>   
>> *Insert rate*: 7/sec.
>> 
>
> Run batches of inserts within a single transaction. It's not the insert 
> itself that's slow, it's the process of committing a transaction. By not 
> starting an explicit transaction, you commit an implict one on every 
> statement.
>
> Igor Tandetnik 
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   

-- 
Regards, Rod Dav4is / P.O. Box 118 / Hyde Park, NY 12538 / USA
Genealogy, et Cetera: http://freepages.rootsweb.ancestry.com/~dav4is/
538 ancestral & collateral families, mostly 17°-19° century 
New England & European roots. Total population: 136,000+
Annex: http://www.gencircles.com/users/dav4is/
email: dav...@yahoo.com
A Democrat, a Republican and a giraffe walk into a bar. The 
bartender looks up from his want ads and says, "What is this, a joke?"
-unknown

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

Re: [sqlite] One more SQL statement question

[Igor Tandetnik]

Dennis Volodomanov 
wrote:
> I'm trying to create a trigger that would delete unreferenced rows
> from a table.
>
> Let's say the schema is like this:
>
> TABLEA ( ID INTEGER PRIMARY KEY, REFB INTEGER );
> TABLEB ( ID INTEGER PRIMARY KEY, DATA );
>
> And let's say the data is like this:
>
> TABLEA:
> 1|1
>
> TABLEB:
> 1|dataA
> 2|dataB
>
> Now, I update the record in TABLEA and change REFB to be REFB=2
>
> I've got the following trigger, which doesn't work:
>
> CREATE TRIGGER UpdateData AFTER UPDATE ON TABLEA
> BEGIN
> DELETE FROM TABLEB WHERE TABLEB.ID IN ( SELECT TABLEB.ID FROM TABLEB
> LEFT JOIN TABLEA ON TABLEA.REFB=TABLEB.ID WHERE TABLEB.ID=OLD.REFB
> AND TABLEB.ID NOT IN ( TABLEA.REFB ) );
> END

This statement makes no sense to me. Why not simply

delete from TABLEB where ID=old.REFB and ID != new.REFB;

Igor Tandetnik 



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

Re: [sqlite] How long should Insert take?

[Rod Dav4is]

Thanks. I'll give it a try.

Igor Tandetnik wrote:
> Rod Dav4is  wrote:
>   
>> *Insert rate*: 7/sec.
>> 
>
> Run batches of inserts within a single transaction. It's not the insert 
> itself that's slow, it's the process of committing a transaction. By not 
> starting an explicit transaction, you commit an implict one on every 
> statement.
>
> Igor Tandetnik 
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   

-- 
Regards, Rod Dav4is / P.O. Box 118 / Hyde Park, NY 12538 / USA
Genealogy, et Cetera: http://freepages.rootsweb.ancestry.com/~dav4is/
538 ancestral & collateral families, mostly 17°-19° century 
New England & European roots. Total population: 136,000+
Annex: http://www.gencircles.com/users/dav4is/
email: dav...@yahoo.com
A Democrat, a Republican and a giraffe walk into a bar. The 
bartender looks up from his want ads and says, "What is this, a joke?"
-unknown

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

Re: [sqlite] I'm surprised what the author of web2py on web2py mailing list just said about sqlite

[Dave Toll]

>
>I apologize and I conclude my statement is not correct.
>
>I have experienced this problem once (out of disk space and sqlite
>database corruption) but I made the mistake of confusing correlation
>with causality. Except in that occasion I never had any problem with
>sqlite and I do use it on production. On my web site (web2py.com) I
>only run sqlite.
>
>Massimo
>
>


I wonder whether Massimo was referring to the possibility that a DB can
get into an unrecoverable state when you reach the disk space limit -
there's no room to create a rollback journal so you can't delete any
records or vacuum. Obviously this is only a concern for systems with
limited disk space.

It would be great to see a disk quota management system in a future
version of SQLite - or at least a way to associate sqlite3_file objects
with the DB connections that created them. I'm trying to implement a
quota system at the VFS level for an embedded platform, but it is
proving tricky to manage temp files...

Cheers,
Dave.

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

[sqlite] One more SQL statement question

[Dennis Volodomanov]

Hello,

I have one more SQL query question - the people on this list have been very 
helpful in the past, so thank you!

I'm trying to create a trigger that would delete unreferenced rows from a table.

Let's say the schema is like this:

TABLEA ( ID INTEGER PRIMARY KEY, REFB INTEGER );
TABLEB ( ID INTEGER PRIMARY KEY, DATA );

And let's say the data is like this:

TABLEA:
1|1

TABLEB:
1|dataA
2|dataB

Now, I update the record in TABLEA and change REFB to be REFB=2

I've got the following trigger, which doesn't work:

CREATE TRIGGER UpdateData AFTER UPDATE ON TABLEA
BEGIN
DELETE FROM TABLEB WHERE TABLEB.ID IN ( SELECT TABLEB.ID FROM TABLEB 
LEFT JOIN TABLEA ON TABLEA.REFB=TABLEB.ID WHERE TABLEB.ID=OLD.REFB AND 
TABLEB.ID NOT IN ( TABLEA.REFB ) );
END

If I run just the SELECT statement within the brackets above (supplying 1 
instead of OLD.REFB of course), it seems to produce the correct row number (1), 
but if I run the complete DELETE statement, nothing happens.

Thank you in advance,

   Dennis

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

Re: [sqlite] How long should Insert take?

[Jay A. Kreibich]

On Wed, Sep 09, 2009 at 06:59:18PM -0400, Rod Dav4is scratched on the wall:

> *Insert rate*: 7/sec.

  http://www.sqlite.org/faq.html#q19



-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How long should Insert take?

[Griggs, Donald]

Hi Rod,

Regarding: *Insert rate*: 7/sec.

You'll want to wrap your INSERT loop in a transaction, i.e., BEGIN /
COMMIT.

Without a transaction, each INSERT has to wait for multiple disk
revolutions.

http://www.sqlite.org/lang_transaction.html

If you have a huge number of inserts, you may want to get fancy and
start a new transaction every several thousand.

I think you'll find a marked difference.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How long should Insert take?

[Igor Tandetnik]

Rod Dav4is  wrote:
> *Insert rate*: 7/sec.

Run batches of inserts within a single transaction. It's not the insert 
itself that's slow, it's the process of committing a transaction. By not 
starting an explicit transaction, you commit an implict one on every 
statement.

Igor Tandetnik 



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

Re: [sqlite] server process gives better concurrency - why?

[Iker Arizmendi]

I'm not planning to develop a new SQL engine, nor am I proposing any
changes to SQLite. I'm only looking for some pointers regarding the
relevant issues in order to study them more closely. If something useful
were to come as a result of doing so then I might be in a position to
actually flesh out a proposal.

Because SQLite is a complex piece of code (as others have pointed out)
I found it helpful to focus on just the following items:

   1) maintaining shared lock state
   2) efficiently detecting failed database processes

The assumption being that a lock facility that can handle these issues
is needed by any concurrency scheme (MVCC, shadow pages, etc) and so can
be thought about independently. Does the ability of a client-server DB to
support multiple writers follow solely from the fact that it centralizes
the lock bookkeeping and that it can easily detect death of its children?
If so, then I think one could get a similar result using a mmap'ed file
and futexes among unrelated processes. This is the area I thought worth
looking into provided that (1) and (2) really are at the heart of the
matter. If not, course corrections are appreciated :)

Regards,
Iker

Pavel Ivanov wrote:
>> I'm hopeful that it's possible to devise a scheme that will let SQLite 
>> support
>> multiple readers and writers while completely preserving all of its current
>> benefits
> 
> So the answer to my question is yes, you want to develop a new SQL engine...
> Then first of all you have to describe one thing: how will you
> implement ACID properties and most probably MVCC? What kind of OS and
> programming tools will you use for this? How exactly you'll make sure
> that multiple writers don't write to the same place in database and
> readers don't read inconsistent data while writers are writing?
> 
> As you explain your concepts in these topics we will be happy to
> discuss it with you and help you make a great SQL engine.
> 
> BTW, you can search list archives and find what DRH said about why
> ACID cannot be implemented along with fine-grained locks using just
> file system locking as SQLite does now.
> 
> Pavel
> 
> On Wed, Sep 9, 2009 at 4:09 PM, Iker Arizmendi  wrote:
>> I'm hopeful that it's possible to devise a scheme that will let SQLite 
>> support
>> multiple readers and writers while completely preserving all of its current
>> benefits (eg, serverless, efficient, zero-conf, simple API, small footprint).
>> To that end, I'm trying to understand some of the "sub problems" that get in
>> the way of multiple writers in order to take a stab at working them out. I 
>> can
>> see that the overall problem is nontrivial and an acceptable solution has not
>> been found. But this only proves that the problem is hard, not that it's
>> impossible (*).
>>
>> Iker
>>
>> (*) Of course, if folks have actually shown that solving this problem
>> amounts to squaring the circle then that's another story.
>>
>> Pavel Ivanov wrote:
>>> I just keep wondering: do you want to write some new database engine
>>> based on SQLite so that it will heed all these caveats? Otherwise this
>>> discussion is useless because all these features are not implementable
>>> on top of SQLite and are way nontrivial to implement inside SQLite...
>>>
>>> Pavel
>> --
>> Iker Arizmendi
>> AT Labs - Research
>> Speech and Image Processing Lab
>>
>> ___
>> 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
> 


-- 
Iker Arizmendi
AT Labs - Research
Speech and Image Processing Lab
e: i...@research.att.com
w: http://research.att.com
p: 973-360-8516

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

[sqlite] How long should Insert take?

[Rod Dav4is]

The environment:

Compaq Presario CQ60 w/Pentium dual core 2GHz+2GHz, 2GB RAM
Windows Vista Home Premium w/SP2, fully updated & patched
SQLite/3 (3.6.17)
My db 17.5Mb (small-medium size, I would say)
The table: CREATE TABLE INDI (ref text , indi text)
No indices or other constraints on INDI
Data to insert (typical) "DAVI37" ,  "@I1@"

Insertion code (Rexx, RexxSQL, error checking code omitted)

SetINDI: Procedure Expose D.
SQLCA.=;Call SQLCommand ,'Delete from INDI'
say 'Begin SetINDI' time('R')
say 'count=' D.0INDI.0
Do f_i=1 to D.0INDI.0
 IF f_I//100 = 0 Then say f_i time('R')
 f_indi=D.0INDI.f_i
 f_refn=Strip(D.0INDI.f_indi.1Refn,'Trailing','*')
 f_CMD='Insert into INDI values (' quote(f_refn) ',' quote(f_indi) ')'
 SQLCA.=;Call SQLCommand , f_CMD
 End

*Insert rate*: 7/sec.

This seems rather slow to me. I expected something perhaps an order of 
magnitude faster.
The time is not spent in the Rexx overhead. Commenting out the SQL 
Insert statement the loop runs in an eyeblink.

Am I expecting too much? I think that SQLite2 did this much faster.

-R.

-- 
Regards, Rod Dav4is / P.O. Box 118 / Hyde Park, NY 12538 / USA
Genealogy, et Cetera: http://freepages.rootsweb.ancestry.com/~dav4is/
538 ancestral & collateral families, mostly 17°-19° century 
New England & European roots. Total population: 136,000+
Annex: http://www.gencircles.com/users/dav4is/
email: dav...@yahoo.com
A Democrat, a Republican and a giraffe walk into a bar. The 
bartender looks up from his want ads and says, "What is this, a joke?"
-unknown

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

Re: [sqlite] I'm surprised what the author of web2py on web2py mailing list just said about sqlite

[Stef Mientki]

the answer of Massimo (the web2py author):


I apologize and I conclude my statement is not correct.

I have experienced this problem once (out of disk space and sqlite
database corruption) but I made the mistake of confusing correlation
with causality. Except in that occasion I never had any problem with
sqlite and I do use it on production. On my web site (web2py.com) I
only run sqlite.

Massimo



Now I'm happy again ;-)
Stef
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I'm surprised what the author of web2py on web2py mailing list just said about sqlite

[Stef Mientki]

D. Richard Hipp wrote:
> On Sep 9, 2009, at 5:35 PM, Stef Mientki wrote:
>   
>> 
>> The worse thing about sqlite is that if the file grows and you run out
>> of memory, the file gets corrupted and you lose data.
>> 
>> 
>
> The web2py author seems to be misinformed.  Perhaps you could direct  
> him to the description of SQLite's Anomaly and OOM testing at 
> http://www.sqlite.org/testing.html 
>   and if that explanation does not suffice, to the description of why  
> SQLite is proof against such database corruption in 
> http://www.sqlite.org/atomiccommit.html
>
>
>   
done, keep you informed.

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

Re: [sqlite] I'm surprised what the author of web2py on web2py mailing list just said about sqlite

[D. Richard Hipp]

On Sep 9, 2009, at 5:35 PM, Stef Mientki wrote:
>
> 
> The worse thing about sqlite is that if the file grows and you run out
> of memory, the file gets corrupted and you lose data.
> 

The web2py author seems to be misinformed.  Perhaps you could direct  
him to the description of SQLite's Anomaly and OOM testing at 
http://www.sqlite.org/testing.html 
  and if that explanation does not suffice, to the description of why  
SQLite is proof against such database corruption in 
http://www.sqlite.org/atomiccommit.html


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

D. Richard Hipp
d...@hwaci.com



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

[sqlite] I'm surprised what the author of web2py on web2py mailing list just said about sqlite

[Stef Mientki]

The worse thing about sqlite is that if the file grows and you run out
of memory, the file gets corrupted and you lose data.


cheers,
Stef

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

Re: [sqlite] server process gives better concurrency - why?

[Simon Slavin]

On 9 Sep 2009, at 9:09pm, Iker Arizmendi wrote:

> I'm hopeful that it's possible to devise a scheme that will let  
> SQLite support
> multiple readers and writers while completely preserving all of its  
> current
> benefits (eg, serverless, efficient, zero-conf, simple API, small  
> footprint).

Sorry but I don't think it is.  The amount of code you'd have to add  
to SQLite to do concurrency correctly and fast is probably about as  
much code as there already is.  It's not a small addon to the existing  
system.

> To that end, I'm trying to understand some of the "sub problems"  
> that get in
> the way of multiple writers in order to take a stab at working them  
> out. I can
> see that the overall problem is nontrivial and an acceptable  
> solution has not
> been found. But this only proves that the problem is hard, not that  
> it's
> impossible (*).

I would recommend books with titles like 'DBMS Systems' or 'Principles  
of Multiuser Database Design' or 'Client/Server Database System  
Architecture'.  Or you might want to sign up for a university course  
or something.  This stuff is hard.

One reason SQLite is small, fast, portable is that it does not try to  
do these things.  And the core parts of SQLite might not be easy to  
adapt to concurrency.  It might be simpler to start from the ground up  
and not support any legacy SQLite facilities.

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

Re: [sqlite] server process gives better concurrency - why?

[Cory Nelson]

On Wed, Sep 9, 2009 at 1:09 PM, Iker Arizmendi  wrote:
> I'm hopeful that it's possible to devise a scheme that will let SQLite support
> multiple readers and writers while completely preserving all of its current
> benefits (eg, serverless, efficient, zero-conf, simple API, small footprint).
> To that end, I'm trying to understand some of the "sub problems" that get in
> the way of multiple writers in order to take a stab at working them out. I can
> see that the overall problem is nontrivial and an acceptable solution has not
> been found. But this only proves that the problem is hard, not that it's
> impossible (*).
>
> Iker
>
> (*) Of course, if folks have actually shown that solving this problem
>     amounts to squaring the circle then that's another story.

Right now sqlite works over a network via NFS and the like -- if you
wanted to keep it an embedded library (with no server), you'd have to
drop support for that.

PS. please stop top posting!


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

Re: [sqlite] sqlite3_exec fails on arm

[Shane Harrelson]

I wasn't able to duplicate this with my ARM system.   Can you provide
more details?  or was the issue DRH pointed out above your problem?

On Wed, Sep 9, 2009 at 7:02 AM, gprand  wrote:
>
> Hi,
>
> I can confirm this behavoir. Now, after 2 days debugging and unsuccessful
> searching around I have found your posting.  It drives me crazy, but I can
> not locate the problem. Exactly the same code works, compiled for X86, and
> fails, compiled for ARM. All machine depending configuration is omitted.
>
> My poor debugging results are:
> * 100 Bytes be read from the file.
> * SQLite attempts to parse the sql text
> * SQLite creates sqlite_master table
> * Now it attempts to parse the sql statement again
> * Now SQLite crashes in ARM mode.
>
> In X86 mode 1024 bytes be read and all works fine.
> Why?
>
> Gottfried
>
> Sorry for poor english.
>
>>Hi,
>>I am using sqlite3.6.17 on arm_v7 . sqlite3_Exec fails giving the following
>>error sqlite3_exec  malformed database schema error : unrecognized token
> "'".
>>I am able to read the database manually using select commands. Executing
> the
>>same sqlite3_exec statements on x86 machine on the same db file does not
>>give any error . Any help on this issue will be welcome,
> --
> View this message in context: 
> http://www.nabble.com/sqlite3_exec-fails-on-arm-tp25293839p25362643.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> 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] server process gives better concurrency - why?

[Pavel Ivanov]

> I'm hopeful that it's possible to devise a scheme that will let SQLite support
> multiple readers and writers while completely preserving all of its current
> benefits

So the answer to my question is yes, you want to develop a new SQL engine...
Then first of all you have to describe one thing: how will you
implement ACID properties and most probably MVCC? What kind of OS and
programming tools will you use for this? How exactly you'll make sure
that multiple writers don't write to the same place in database and
readers don't read inconsistent data while writers are writing?

As you explain your concepts in these topics we will be happy to
discuss it with you and help you make a great SQL engine.

BTW, you can search list archives and find what DRH said about why
ACID cannot be implemented along with fine-grained locks using just
file system locking as SQLite does now.

Pavel

On Wed, Sep 9, 2009 at 4:09 PM, Iker Arizmendi  wrote:
> I'm hopeful that it's possible to devise a scheme that will let SQLite support
> multiple readers and writers while completely preserving all of its current
> benefits (eg, serverless, efficient, zero-conf, simple API, small footprint).
> To that end, I'm trying to understand some of the "sub problems" that get in
> the way of multiple writers in order to take a stab at working them out. I can
> see that the overall problem is nontrivial and an acceptable solution has not
> been found. But this only proves that the problem is hard, not that it's
> impossible (*).
>
> Iker
>
> (*) Of course, if folks have actually shown that solving this problem
>     amounts to squaring the circle then that's another story.
>
> Pavel Ivanov wrote:
>>
>> I just keep wondering: do you want to write some new database engine
>> based on SQLite so that it will heed all these caveats? Otherwise this
>> discussion is useless because all these features are not implementable
>> on top of SQLite and are way nontrivial to implement inside SQLite...
>>
>> Pavel
>
> --
> Iker Arizmendi
> AT Labs - Research
> Speech and Image Processing Lab
>
> ___
> 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] how to get "One to many" in 1 row of a table ?

[Simon Slavin]

On 9 Sep 2009, at 9:15pm, Stef Mientki wrote:

> 1 type of car can (and will) be owned by many persons.

Oh, so these are not specific cars, these are descriptions of cars.   
Yes, in this case you need now what is asked for in the subject-header  
"one to many" but "many to many".  And the correct way to do this is  
to have a table between 'person' and 'vehicle': the one you called  
'bind'.  What you suggested is a fine design and probably what you want.

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

Re: [sqlite] how to get "One to many" in 1 row of a table ?

[Stef Mientki]

Shaun Seckman (Firaxis) wrote:
> It seems like the more ideal solution would be to create a third table for 
> ownership that contains a person ID and a vehicle ID.
thanks Shaun,
but that's what I (i'm not a database expert) call the Bind table.
I read some articles about normalization,
and I think I got with this database somewhere to the 5-order?

cheers,
Stef


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

Re: [sqlite] how to get "One to many" in 1 row of a table ?

[Stef Mientki]

Simon Slavin wrote:
> On 9 Sep 2009, at 5:15pm, Stef Mientki wrote:
>
>   
>> The situation is like this (simplified):
>>
>> I've a table with persons: name, address, phone, etc.
>> I've another table with vehicles: car_or_bike, brand, type, build- 
>> year, etc.
>> The 2 tables are bounded by bind table: person_ID, vehicle_ID
>> 
>
> Well you cannot have one vehicle owned by more than one person.  So  
> you do not need your 'bind' table at all.  You just need to add a  
> column to the vehicle table which contains the person_ID.
>
>   
Sorry Simon,
I expressed myself not well enough:
1 type of car can (and will) be owned by many persons.
But that always the problem with simplifications.
The real problem is about psychological / medical tests and questionnaires,
where a patient for each test scores on a number of scales.
As all scales are of the same type of data, they are all put in one table.


>> I know that every person has 1 car and 1 bike
>> 
>
> Do you mean exactly one car ?  Or up to but no more than one car ?  Or  
> at least one car ?
>
>   
so yes exactly one car, or in reality 1 score on every scale.
>> I want to show up a table, with 3 columns:
>>  persons.name  vehicle.brand[car]
>> vehicle.brand[bike]
>>
>>
>> I can get the result in the following way:
>>
>> 1. create view Cars: persons join vehicle, where car_or_bike = car
>> 2. create view Bikes: persons join vehicle, where car_or_bike = bike
>> (and change the relevant colum names)
>> 3. join the above view 1 and 2
>>
>> The first problem is that the above approach is quiet slow.
>> Both starting tables (persons and vehicles are also views),
>> and maybe it would be faster to create (temporary) tables ?
>>
>> The second problem is that in the real situation
>> I'm not converting 1 column  to 2 columns,
>> but it can be as large as 1 column to 10 columns.
>>
>> btw I'm running SQLite from Python, so maybe I should do some data
>> handling outside SQL (in Python) ?
>> 
>
> Yes, you are trying to get SQL to do your programming work for you.   
> Just do two SELECT commands in Python: one to find the car for the  
> person, and another to find the bike for the person.  No need to  
> create any temporary tables, create any views, do any joins, or keep a  
> huge table with all the results in.  Much smaller and faster.
>   
Yes but that's not the goal,
the goal is to create a very easy environment,
where even a doctor or nurse can ask any question to the system.

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

Re: [sqlite] SQLite version 3.6.18 scheduled for Monday

[Pavel Ivanov]

> So what are we missing.  Can you suggest better ways of organizing the
> documentation above?

A bit of suggestion: it would be great if
http://www.sqlite.org/doc_keyword_crossref.html was accessible from
http://www.sqlite.org/docs.html as a link say after "FAQ" with a text
like "All documentation index" or "All documentation reference". I've
read documentation of SQLite a lot and followed a lot of links but
never met this cross-reference so far. Also the fact that googling
"crossreference site:sqlite.org" doesn't give link to this page
suggests that there's no way to come to cross-reference from any place
on the site.

Pavel

On Wed, Sep 9, 2009 at 3:54 PM, D. Richard Hipp  wrote:
>
> On Sep 9, 2009, at 1:46 PM, John Loveys wrote:
>>
>> I do find the documentation rather awkward.  I have seen (and used)
>> links to
>> stuff I cannot find from the start page.  Maybe doxygen?  I write a
>> lot of C
>> and find it invaluable.
>>
>
>
> Suggestions on how to improve the documentation are always welcomed.
> However, I don't consider "doxygen" an improvement.  If you want to
> try to prove me wrong about the previous sentence, I promise to listen.
>
> Are you looking for these links:
>
>     http://www.sqlite.org/c3ref/intro.html
>     http://www.sqlite.org/lang.html
>
> There are extensive hyperlinks from the above to just about everything
> you might want to know about SQLite.  Note also the "Common Links"
> section on the homepage (http://www.sqlite.org/index.html)   The two
> links above are "C/C++ Interface Spec" and "SQL Syntax".  On the menu
> bar under "Documentation" you can find a list of documents:
>
>     http://www.sqlite.org/docs.html
>
> Need an index?  There is a listing of hyperlinks together with their
> labels here:
>
>    http://www.sqlite.org/doc_keyword_crossref.html
>
> So what are we missing.  Can you suggest better ways of organizing the
> documentation above?
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> 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] server process gives better concurrency - why?

[Iker Arizmendi]

I'm hopeful that it's possible to devise a scheme that will let SQLite support
multiple readers and writers while completely preserving all of its current
benefits (eg, serverless, efficient, zero-conf, simple API, small footprint).
To that end, I'm trying to understand some of the "sub problems" that get in
the way of multiple writers in order to take a stab at working them out. I can
see that the overall problem is nontrivial and an acceptable solution has not
been found. But this only proves that the problem is hard, not that it's
impossible (*).

Iker

(*) Of course, if folks have actually shown that solving this problem
 amounts to squaring the circle then that's another story.

Pavel Ivanov wrote:
> 
> I just keep wondering: do you want to write some new database engine
> based on SQLite so that it will heed all these caveats? Otherwise this
> discussion is useless because all these features are not implementable
> on top of SQLite and are way nontrivial to implement inside SQLite...
> 
> Pavel

-- 
Iker Arizmendi
AT Labs - Research
Speech and Image Processing Lab

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

Re: [sqlite] how to get "One to many" in 1 row of a table ?

[Simon Slavin]

On 9 Sep 2009, at 6:58pm, Shaun Seckman (Firaxis) wrote:

> It seems like the more ideal solution would be to create a third  
> table for ownership that contains a person ID and a vehicle ID.

I thought that a third table was what you proposed in the first  
place.  I was telling you you didn't need one.

>  This will result in more tables (significantly more if you're  
> trying to migrate object oriented data into a relational model) but  
> is that really a bad thing?

Yes.  Every extra table is an additional join, view, or temporary  
table.  In addition it's one more thing that cannot be indexed.  For  
instance, suppose you want to look up all the cars belonging to Person  
#47.  If you have the person number in the vehicle table you can do

SELECT * FROM vehicles WHERE owner = 47 and type = 'car'

and you can make an index on (owner, type) that means the SELECT  
command can go straight there, so it will be super-fast.  Now try  
doing the same thing if you spin your ownership relation off into a  
separate table.  First you have to do a JOIN, then it's impossible to  
create an index, so SQL will have to do searching and sorting to find  
the right records.

> I managed to migrate similar data using this tactic and the biggest  
> issue I've had with it is in the generation of long SQL statements  
> that contain many inner joins (I really wish there was some syntax  
> sugar for automatically joining all foreign keys).  This can become  
> tricky if performance is a priority as I found myself needing many  
> unique SQL statements

Absolutely.  Joins are necessary in many situations.  But joins are  
what makes things like this slow, and the SELECT commands long and  
confusing.  You want to minimise anything that involves joins, views,  
virtual tables, etc..

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

Re: [sqlite] how to get "One to many" in 1 row of a table ?

[Stef Mientki]

Igor Tandetnik wrote:
> Stef Mientki  wrote:
>   
>> I'm just an amateur  struggling with databases,
>> so forgive if  I ask  a question every database user should know.
>>
>> The situation is like this (simplified):
>>
>> I've a table with persons: name, address, phone, etc.
>> I've another table with vehicles: car_or_bike, brand, type,
>> build-year, etc. The 2 tables are bounded by bind table: person_ID,
>> vehicle_ID
>>
>> I know that every person has 1 car and 1 bike
>>
>> I want to show up a table, with 3 columns:
>>  persons.name  vehicle.brand[car]
>> vehicle.brand[bike]
>> 
>
> select p.name, v1.brand, v2.brand
> from persons p join bind b1 on (p.person_ID = b1.person_ID)
> join vechicles v1 on (b1.vehicle_ID = v1.vehicle_ID and 
> v1.car_or_bike = 'car')
> join bind b2 on (p.person_ID = b2.person_ID)
> join vechicles v2 on (b2.vehicle_ID = v2.vehicle_ID and 
> v2.car_or_bike = 'bike');
>
> Igor Tandetnik 
>
>   
thanks Igor,

That works very good and fast, and still very readable code.
I just tried it with 1 to 5 columns on the same database
and it's much faster then mine 1 to 2 columns.
I tried some code quiet similar to yours, but got errors,
now I see an essental part is to use aliases for the tables used more 
than once.

thanks!

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

Re: [sqlite] SQLite version 3.6.18 scheduled for Monday

[D. Richard Hipp]

On Sep 9, 2009, at 2:54 PM, Shaun Seckman (Firaxis) wrote:

> I'm sold :)  Do you know if there are any MS Visual Studio or MS
> Explorer integrations?

Nope.  Sorry.  We mostly use Linux and Mac here.  Fossil does work on  
windows - it took quite a bit of effort to get the "fossil ui" and  
"fossil server" commands working there.  But other than providing the  
same functionality provided on all other platforms, we do not have any  
special windows integration.

>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of D. Richard Hipp
> Sent: Wednesday, September 09, 2009 2:29 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite version 3.6.18 scheduled for Monday
>
>
> On Sep 9, 2009, at 2:04 PM, Shaun Seckman (Firaxis) wrote:
>
>> It's always a pleasure to have a new version of one of my favorite
>> tools.  I noticed in the draft that you migrated the project to
>> Fossil.
>> I am curious, how was your experience with the migration?  Did it go
>> smoothly or were there many kinks to work out.  Also, what was your
>> primary motivation for migrating in the first place?
>>
>
> The transfer of version history from CVS to Fossil went relatively
> smoothly.  There were, in fact, some anomalies in the CVS revision
> history of SQLite back in the 1.x days and so I had to develop work-
> arounds for those anomalies.  But otherwise everything went smoothly.
>
> SQLite is the largest (in terms of history) project that we are aware
> of that is using Fossil, so this was a big step for Fossil.  Very
> early on, the SQLite repository revealed an inefficiency in the
> implementation of the synchronization wire protocol, which we quickly
> fixed (http://www.fossil-scm.org/fossil/vinfo/7646ee13e) but other
> than that, no problems have yet arisen.  Fossil is working out very
> well.  We were pleasantly surprised to find that the Fossil repository
> was an order of magnitude smaller than the original CVS repository.
> The entire 9.25-year revision history of SQLite can be cloned with
> only about 13MB of network traffic.
>
> There are multiple reasons for changing from CVS to Fossil.
>
> (1) The whole point of writing fossil to begin with is so that we
> could keep tighter configuration control of SQLite.  Some customers
> require this.  Others simply want it.  As an example of the new
> capabilities that Fossil provides us, see the new sqlite3_sourceid()  
> C/
> C++ interface and the new sqlite_source_id() SQL functions:
>
> http://www.sqlite.org/draft/c3ref/libversion.html
> http://www.sqlite.org/draft/lang_corefunc.html#sqlite_source_id
>
> (2) Fossil is a distributed VCS which enables us to work off-line.  We
> are also much less concerned about backups now, since every clone of
> the repository is a full backup.  And we can easily make read/write
> mirrors of the repository:
>
>  http://www.sqlite.org/src/timeline  (in Dallas, TX)
>  http://www2.sqlite.org/src/timeline  (in Atlanta, GA)
>
> (3) With Fossil, it is much much easier to go back and create branches
> to fix bugs in archaic versions of SQLite, in order to support those
> companies who actually pay us.  (example:
> http://www.sqlite.org/src/vinfo/715cecb8c7)
>
> (4) Having the canonical SQLite sources in Fossil makes it easier to
> create and maintain a private branch of SQLite.  See
> http://www.sqlite.org/privatebranch.html
>  for further discussion.
>
> (5) In our experience, Fossil works better than CVS over slow and/or
> unreliable networks.  And, Fossil allows users behind restrictive
> firewalls to get direct access to the code - something that was never
> possible with CVS.
>
> (6) "fossil diff" is way faster than "cvs diff" (since there is no
> network traffic with "fossil diff").
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> 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

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] SQLite version 3.6.18 scheduled for Monday

[D. Richard Hipp]

On Sep 9, 2009, at 1:46 PM, John Loveys wrote:
>
> I do find the documentation rather awkward.  I have seen (and used)  
> links to
> stuff I cannot find from the start page.  Maybe doxygen?  I write a  
> lot of C
> and find it invaluable.
>


Suggestions on how to improve the documentation are always welcomed.   
However, I don't consider "doxygen" an improvement.  If you want to  
try to prove me wrong about the previous sentence, I promise to listen.

Are you looking for these links:

 http://www.sqlite.org/c3ref/intro.html
 http://www.sqlite.org/lang.html

There are extensive hyperlinks from the above to just about everything  
you might want to know about SQLite.  Note also the "Common Links"  
section on the homepage (http://www.sqlite.org/index.html)   The two  
links above are "C/C++ Interface Spec" and "SQL Syntax".  On the menu  
bar under "Documentation" you can find a list of documents:

 http://www.sqlite.org/docs.html

Need an index?  There is a listing of hyperlinks together with their  
labels here:

http://www.sqlite.org/doc_keyword_crossref.html

So what are we missing.  Can you suggest better ways of organizing the  
documentation above?

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] SQLite version 3.6.18 scheduled for Monday

[Shaun Seckman (Firaxis)]

I'm sold :)  Do you know if there are any MS Visual Studio or MS
Explorer integrations?  

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of D. Richard Hipp
Sent: Wednesday, September 09, 2009 2:29 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite version 3.6.18 scheduled for Monday


On Sep 9, 2009, at 2:04 PM, Shaun Seckman (Firaxis) wrote:

> It's always a pleasure to have a new version of one of my favorite
> tools.  I noticed in the draft that you migrated the project to  
> Fossil.
> I am curious, how was your experience with the migration?  Did it go
> smoothly or were there many kinks to work out.  Also, what was your
> primary motivation for migrating in the first place?
>

The transfer of version history from CVS to Fossil went relatively  
smoothly.  There were, in fact, some anomalies in the CVS revision  
history of SQLite back in the 1.x days and so I had to develop work- 
arounds for those anomalies.  But otherwise everything went smoothly.

SQLite is the largest (in terms of history) project that we are aware  
of that is using Fossil, so this was a big step for Fossil.  Very  
early on, the SQLite repository revealed an inefficiency in the  
implementation of the synchronization wire protocol, which we quickly  
fixed (http://www.fossil-scm.org/fossil/vinfo/7646ee13e) but other  
than that, no problems have yet arisen.  Fossil is working out very  
well.  We were pleasantly surprised to find that the Fossil repository  
was an order of magnitude smaller than the original CVS repository.   
The entire 9.25-year revision history of SQLite can be cloned with  
only about 13MB of network traffic.

There are multiple reasons for changing from CVS to Fossil.

(1) The whole point of writing fossil to begin with is so that we  
could keep tighter configuration control of SQLite.  Some customers  
require this.  Others simply want it.  As an example of the new  
capabilities that Fossil provides us, see the new sqlite3_sourceid() C/ 
C++ interface and the new sqlite_source_id() SQL functions:

 http://www.sqlite.org/draft/c3ref/libversion.html
 http://www.sqlite.org/draft/lang_corefunc.html#sqlite_source_id

(2) Fossil is a distributed VCS which enables us to work off-line.  We  
are also much less concerned about backups now, since every clone of  
the repository is a full backup.  And we can easily make read/write  
mirrors of the repository:

  http://www.sqlite.org/src/timeline  (in Dallas, TX)
  http://www2.sqlite.org/src/timeline  (in Atlanta, GA)

(3) With Fossil, it is much much easier to go back and create branches  
to fix bugs in archaic versions of SQLite, in order to support those  
companies who actually pay us.  (example:
http://www.sqlite.org/src/vinfo/715cecb8c7)

(4) Having the canonical SQLite sources in Fossil makes it easier to  
create and maintain a private branch of SQLite.  See
http://www.sqlite.org/privatebranch.html 
  for further discussion.

(5) In our experience, Fossil works better than CVS over slow and/or  
unreliable networks.  And, Fossil allows users behind restrictive  
firewalls to get direct access to the code - something that was never  
possible with CVS.

(6) "fossil diff" is way faster than "cvs diff" (since there is no  
network traffic with "fossil diff").

D. Richard Hipp
d...@hwaci.com



___
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] server process gives better concurrency - why?

[P Kishor]

On Wed, Sep 9, 2009 at 1:39 PM, Pavel Ivanov  wrote:
>> But I imagine
>> there are other issues as well - these are the issues I'd like to get
>> a bead on.
>
> I just keep wondering: do you want to write some new database engine
> based on SQLite so that it will heed all these caveats? Otherwise this
> discussion is useless because all these features are not implementable
> on top of SQLite and are way nontrivial to implement inside SQLite...
>

well said. This thread is like asking,

"What are the reasons SQLite is not like Oracle or PostGres?"

"Well, because... if it were then it wouldn't be zero conf, fit on a
floppy, and cost less than a popsicle"

"But, why is it not like PostGres or Oracle? Can't we make it so?"

"Given a few years of development, yes, we might, but then it would be
like Oracle and PostGres, and what would be the point of that? Just go
get Oracle or PostGres right now."

"Yes, but wouldn't it be nice to make SQLite like Oracle and PostGres?"


> Pavel
>
> On Wed, Sep 9, 2009 at 1:44 PM, Iker Arizmendi  wrote:
>> Proposals for techniques like MVCC and shadow paging have been made
>> on this list before and they appeared feasible (IIUC) without a server
>> process. The problems with the serverless approach arise once multiple
>> writers are introduced. For example, efficiently detecting crashed
>> writers and initiating recovery. I think this problem may be soluble,
>> at least non-portably, with robust futexes or mutexes. But I imagine
>> there are other issues as well - these are the issues I'd like to get
>> a bead on.
>>
>> Iker
>>
>> Ken wrote:
>>> The key to increased concurrency is MVCC. Without MVCC concurrency is 
>>> limited to page locking, table locking etc.
>>>
>>> Google MVCC...
>>>
>>> --- On Tue, 9/8/09, Iker Arizmendi  wrote:
>>>
 From: Iker Arizmendi 
 Subject: Re: [sqlite] server process gives better concurrency - why?
 To: sqlite-users@sqlite.org
 Date: Tuesday, September 8, 2009, 9:34 PM
 The question is whether a
 client-server design is /necessary/ to
 efficiently implement higher concurrency. It appears to be
 easier
 to do so with a client-server model, but is such a model
 required?
 Are there functions performed by a server process that
 cannot be
 carried out at all without it?

 Iker

 Simon Slavin wrote:
  > If SQLite was to be
  > designed to handle multiple processes 'properly', it
 would have to be
  > rewritten as a client/server system.
  >
  > This would, of course, kill all the advantages of
 SQLite: it could no
  > longer be tiny, fast, and ultra-portable.  So it
 would be a bad design
  > choice for SQLite (bowing, of course, to DRH's right
 to do whatever he
  > pleases with it).
  >
  > This is why I get uneasy when I see posts here that
 suggest spinning
  > off threads especially to deal with locking issues,
 or do other things
  > that solve concurrency or latency problems.
 Often you find that
  > making such a change in your program just leads to
 one of the threads
  > immediately being blocked by another, defeating the
 point of threading
  > in the first place.  Software has to be designed
 around what is
  > possible with the tools you're using, not around some
 mythical idea of
  > the perfect generic SQL engine.
  >
  > Simon.

 --
 Iker Arizmendi
 AT Labs - Research
 Speech and Image Processing Lab
 e: i...@research.att.com
 w: http://research.att.com
 p: 973-360-8516

 ___
 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
>>>
>>
>>
>> --
>> Iker Arizmendi
>> AT Labs - Research
>> Speech and Image Processing Lab
>> e: i...@research.att.com
>> w: http://research.att.com
>> p: 973-360-8516
>>
>> ___
>> 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
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with 

Re: [sqlite] server process gives better concurrency - why?

[Pavel Ivanov]

> But I imagine
> there are other issues as well - these are the issues I'd like to get
> a bead on.

I just keep wondering: do you want to write some new database engine
based on SQLite so that it will heed all these caveats? Otherwise this
discussion is useless because all these features are not implementable
on top of SQLite and are way nontrivial to implement inside SQLite...

Pavel

On Wed, Sep 9, 2009 at 1:44 PM, Iker Arizmendi  wrote:
> Proposals for techniques like MVCC and shadow paging have been made
> on this list before and they appeared feasible (IIUC) without a server
> process. The problems with the serverless approach arise once multiple
> writers are introduced. For example, efficiently detecting crashed
> writers and initiating recovery. I think this problem may be soluble,
> at least non-portably, with robust futexes or mutexes. But I imagine
> there are other issues as well - these are the issues I'd like to get
> a bead on.
>
> Iker
>
> Ken wrote:
>> The key to increased concurrency is MVCC. Without MVCC concurrency is 
>> limited to page locking, table locking etc.
>>
>> Google MVCC...
>>
>> --- On Tue, 9/8/09, Iker Arizmendi  wrote:
>>
>>> From: Iker Arizmendi 
>>> Subject: Re: [sqlite] server process gives better concurrency - why?
>>> To: sqlite-users@sqlite.org
>>> Date: Tuesday, September 8, 2009, 9:34 PM
>>> The question is whether a
>>> client-server design is /necessary/ to
>>> efficiently implement higher concurrency. It appears to be
>>> easier
>>> to do so with a client-server model, but is such a model
>>> required?
>>> Are there functions performed by a server process that
>>> cannot be
>>> carried out at all without it?
>>>
>>> Iker
>>>
>>> Simon Slavin wrote:
>>>  > If SQLite was to be
>>>  > designed to handle multiple processes 'properly', it
>>> would have to be
>>>  > rewritten as a client/server system.
>>>  >
>>>  > This would, of course, kill all the advantages of
>>> SQLite: it could no
>>>  > longer be tiny, fast, and ultra-portable.  So it
>>> would be a bad design
>>>  > choice for SQLite (bowing, of course, to DRH's right
>>> to do whatever he
>>>  > pleases with it).
>>>  >
>>>  > This is why I get uneasy when I see posts here that
>>> suggest spinning
>>>  > off threads especially to deal with locking issues,
>>> or do other things
>>>  > that solve concurrency or latency problems.
>>> Often you find that
>>>  > making such a change in your program just leads to
>>> one of the threads
>>>  > immediately being blocked by another, defeating the
>>> point of threading
>>>  > in the first place.  Software has to be designed
>>> around what is
>>>  > possible with the tools you're using, not around some
>>> mythical idea of
>>>  > the perfect generic SQL engine.
>>>  >
>>>  > Simon.
>>>
>>> --
>>> Iker Arizmendi
>>> AT Labs - Research
>>> Speech and Image Processing Lab
>>> e: i...@research.att.com
>>> w: http://research.att.com
>>> p: 973-360-8516
>>>
>>> ___
>>> 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
>>
>
>
> --
> Iker Arizmendi
> AT Labs - Research
> Speech and Image Processing Lab
> e: i...@research.att.com
> w: http://research.att.com
> p: 973-360-8516
>
> ___
> 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] SQLite version 3.6.18 scheduled for Monday

[D. Richard Hipp]

On Sep 9, 2009, at 2:04 PM, Shaun Seckman (Firaxis) wrote:

> It's always a pleasure to have a new version of one of my favorite
> tools.  I noticed in the draft that you migrated the project to  
> Fossil.
> I am curious, how was your experience with the migration?  Did it go
> smoothly or were there many kinks to work out.  Also, what was your
> primary motivation for migrating in the first place?
>

The transfer of version history from CVS to Fossil went relatively  
smoothly.  There were, in fact, some anomalies in the CVS revision  
history of SQLite back in the 1.x days and so I had to develop work- 
arounds for those anomalies.  But otherwise everything went smoothly.

SQLite is the largest (in terms of history) project that we are aware  
of that is using Fossil, so this was a big step for Fossil.  Very  
early on, the SQLite repository revealed an inefficiency in the  
implementation of the synchronization wire protocol, which we quickly  
fixed (http://www.fossil-scm.org/fossil/vinfo/7646ee13e) but other  
than that, no problems have yet arisen.  Fossil is working out very  
well.  We were pleasantly surprised to find that the Fossil repository  
was an order of magnitude smaller than the original CVS repository.   
The entire 9.25-year revision history of SQLite can be cloned with  
only about 13MB of network traffic.

There are multiple reasons for changing from CVS to Fossil.

(1) The whole point of writing fossil to begin with is so that we  
could keep tighter configuration control of SQLite.  Some customers  
require this.  Others simply want it.  As an example of the new  
capabilities that Fossil provides us, see the new sqlite3_sourceid() C/ 
C++ interface and the new sqlite_source_id() SQL functions:

 http://www.sqlite.org/draft/c3ref/libversion.html
 http://www.sqlite.org/draft/lang_corefunc.html#sqlite_source_id

(2) Fossil is a distributed VCS which enables us to work off-line.  We  
are also much less concerned about backups now, since every clone of  
the repository is a full backup.  And we can easily make read/write  
mirrors of the repository:

  http://www.sqlite.org/src/timeline  (in Dallas, TX)
  http://www2.sqlite.org/src/timeline  (in Atlanta, GA)

(3) With Fossil, it is much much easier to go back and create branches  
to fix bugs in archaic versions of SQLite, in order to support those  
companies who actually pay us.  (example:  
http://www.sqlite.org/src/vinfo/715cecb8c7)

(4) Having the canonical SQLite sources in Fossil makes it easier to  
create and maintain a private branch of SQLite.  See 
http://www.sqlite.org/privatebranch.html 
  for further discussion.

(5) In our experience, Fossil works better than CVS over slow and/or  
unreliable networks.  And, Fossil allows users behind restrictive  
firewalls to get direct access to the code - something that was never  
possible with CVS.

(6) "fossil diff" is way faster than "cvs diff" (since there is no  
network traffic with "fossil diff").

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] SQLite version 3.6.18 scheduled for Monday

[Shaun Seckman (Firaxis)]

It's always a pleasure to have a new version of one of my favorite
tools.  I noticed in the draft that you migrated the project to Fossil.
I am curious, how was your experience with the migration?  Did it go
smoothly or were there many kinks to work out.  Also, what was your
primary motivation for migrating in the first place?


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of D. Richard Hipp
Sent: Wednesday, September 09, 2009 11:55 AM
To: General Discussion of SQLite Database
Subject: [sqlite] SQLite version 3.6.18 scheduled for Monday

We are on schedule to release SQLite version 3.6.18 on Monday,  
2009-09-14.  For a preview, see

   http://www.sqlite.org/draft/index.html

D. Richard Hipp
d...@hwaci.com



___
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] how to get "One to many" in 1 row of a table ?

[Shaun Seckman (Firaxis)]

It seems like the more ideal solution would be to create a third table for 
ownership that contains a person ID and a vehicle ID.  This will result in more 
tables (significantly more if you're trying to migrate object oriented data 
into a relational model) but is that really a bad thing?

I managed to migrate similar data using this tactic and the biggest issue I've 
had with it is in the generation of long SQL statements that contain many inner 
joins (I really wish there was some syntax sugar for automatically joining all 
foreign keys).  This can become tricky if performance is a priority as I found 
myself needing many unique SQL statements.

-Shaun

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of P Kishor
Sent: Wednesday, September 09, 2009 1:35 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] how to get "One to many" in 1 row of a table ?

On Wed, Sep 9, 2009 at 12:32 PM, Simon Slavin
 wrote:
>
> On 9 Sep 2009, at 5:15pm, Stef Mientki wrote:
>
>> The situation is like this (simplified):
>>
>> I've a table with persons: name, address, phone, etc.
>> I've another table with vehicles: car_or_bike, brand, type, build-
>> year, etc.
>> The 2 tables are bounded by bind table: person_ID, vehicle_ID
>
> Well you cannot have one vehicle owned by more than one person.  So
> you do not need your 'bind' table at all.  You just need to add a
> column to the vehicle table which contains the person_ID.


huh! even though my Saturn is a clunker, it is owned by my wife and I,
and until recently, was also owned partially by the credit union via
their auto loan...

..


-- 
Puneet Kishor
___
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] SQLite version 3.6.18 scheduled for Monday

[John Loveys]

Thanks D Hipp

Your software is marvelous, as is the help on this list.  I had to rework
one delete statement, for reasons I still don't get, but otherwise awfully
good stuff.  I made it work another way.  I worked with some of the people
who invented DB2.  Was a hell of a lot of fun.

I especially enjoy the silly and mindless debates, but they happen on every
list.  I try to stay out of those...

I do find the documentation rather awkward.  I have seen (and used) links to
stuff I cannot find from the start page.  Maybe doxygen?  I write a lot of C
and find it invaluable.

Not much point to this post, other than well deserved praise.  I have
embedded 3.x.y in the field (on mountaintops, mostly), so I'll not be
upgrading unless there is something really significant.  I should be as good
as SQL, I figure.  But I think all thank you for saving us from rolling our
own DB.

All raise a glass...

Cheers
/J

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of D. Richard Hipp
Sent: Wednesday, September 09, 2009 12:55 PM
To: General Discussion of SQLite Database
Subject: [sqlite] SQLite version 3.6.18 scheduled for Monday

We are on schedule to release SQLite version 3.6.18 on Monday,  
2009-09-14.  For a preview, see

   http://www.sqlite.org/draft/index.html

D. Richard Hipp
d...@hwaci.com



___
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] how to get "One to many" in 1 row of a table ?

[Igor Tandetnik]

Stef Mientki  wrote:
> I'm just an amateur  struggling with databases,
> so forgive if  I ask  a question every database user should know.
>
> The situation is like this (simplified):
>
> I've a table with persons: name, address, phone, etc.
> I've another table with vehicles: car_or_bike, brand, type,
> build-year, etc. The 2 tables are bounded by bind table: person_ID,
> vehicle_ID
>
> I know that every person has 1 car and 1 bike
>
> I want to show up a table, with 3 columns:
>  persons.name  vehicle.brand[car]
> vehicle.brand[bike]

select p.name, v1.brand, v2.brand
from persons p join bind b1 on (p.person_ID = b1.person_ID)
join vechicles v1 on (b1.vehicle_ID = v1.vehicle_ID and 
v1.car_or_bike = 'car')
join bind b2 on (p.person_ID = b2.person_ID)
join vechicles v2 on (b2.vehicle_ID = v2.vehicle_ID and 
v2.car_or_bike = 'bike');

Igor Tandetnik 



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

Re: [sqlite] server process gives better concurrency - why?

[Iker Arizmendi]

Proposals for techniques like MVCC and shadow paging have been made
on this list before and they appeared feasible (IIUC) without a server
process. The problems with the serverless approach arise once multiple
writers are introduced. For example, efficiently detecting crashed
writers and initiating recovery. I think this problem may be soluble,
at least non-portably, with robust futexes or mutexes. But I imagine
there are other issues as well - these are the issues I'd like to get
a bead on.

Iker

Ken wrote:
> The key to increased concurrency is MVCC. Without MVCC concurrency is limited 
> to page locking, table locking etc. 
> 
> Google MVCC...
> 
> --- On Tue, 9/8/09, Iker Arizmendi  wrote:
> 
>> From: Iker Arizmendi 
>> Subject: Re: [sqlite] server process gives better concurrency - why?
>> To: sqlite-users@sqlite.org
>> Date: Tuesday, September 8, 2009, 9:34 PM
>> The question is whether a
>> client-server design is /necessary/ to
>> efficiently implement higher concurrency. It appears to be
>> easier
>> to do so with a client-server model, but is such a model
>> required?
>> Are there functions performed by a server process that
>> cannot be
>> carried out at all without it?
>>
>> Iker
>>
>> Simon Slavin wrote:
>>  > If SQLite was to be
>>  > designed to handle multiple processes 'properly', it
>> would have to be
>>  > rewritten as a client/server system.
>>  >
>>  > This would, of course, kill all the advantages of
>> SQLite: it could no
>>  > longer be tiny, fast, and ultra-portable.  So it
>> would be a bad design
>>  > choice for SQLite (bowing, of course, to DRH's right
>> to do whatever he
>>  > pleases with it).
>>  >
>>  > This is why I get uneasy when I see posts here that
>> suggest spinning
>>  > off threads especially to deal with locking issues,
>> or do other things
>>  > that solve concurrency or latency problems. 
>> Often you find that
>>  > making such a change in your program just leads to
>> one of the threads
>>  > immediately being blocked by another, defeating the
>> point of threading
>>  > in the first place.  Software has to be designed
>> around what is
>>  > possible with the tools you're using, not around some
>> mythical idea of
>>  > the perfect generic SQL engine.
>>  >
>>  > Simon.
>>
>> -- 
>> Iker Arizmendi
>> AT Labs - Research
>> Speech and Image Processing Lab
>> e: i...@research.att.com
>> w: http://research.att.com
>> p: 973-360-8516
>>
>> ___
>> 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
> 


-- 
Iker Arizmendi
AT Labs - Research
Speech and Image Processing Lab
e: i...@research.att.com
w: http://research.att.com
p: 973-360-8516

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

Re: [sqlite] how to get "One to many" in 1 row of a table ?

[P Kishor]

On Wed, Sep 9, 2009 at 12:32 PM, Simon Slavin
 wrote:
>
> On 9 Sep 2009, at 5:15pm, Stef Mientki wrote:
>
>> The situation is like this (simplified):
>>
>> I've a table with persons: name, address, phone, etc.
>> I've another table with vehicles: car_or_bike, brand, type, build-
>> year, etc.
>> The 2 tables are bounded by bind table: person_ID, vehicle_ID
>
> Well you cannot have one vehicle owned by more than one person.  So
> you do not need your 'bind' table at all.  You just need to add a
> column to the vehicle table which contains the person_ID.


huh! even though my Saturn is a clunker, it is owned by my wife and I,
and until recently, was also owned partially by the credit union via
their auto loan...

..


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

Re: [sqlite] how to get "One to many" in 1 row of a table ?

[Simon Slavin]

On 9 Sep 2009, at 5:15pm, Stef Mientki wrote:

> The situation is like this (simplified):
>
> I've a table with persons: name, address, phone, etc.
> I've another table with vehicles: car_or_bike, brand, type, build- 
> year, etc.
> The 2 tables are bounded by bind table: person_ID, vehicle_ID

Well you cannot have one vehicle owned by more than one person.  So  
you do not need your 'bind' table at all.  You just need to add a  
column to the vehicle table which contains the person_ID.

> I know that every person has 1 car and 1 bike

Do you mean exactly one car ?  Or up to but no more than one car ?  Or  
at least one car ?

> I want to show up a table, with 3 columns:
>  persons.name  vehicle.brand[car]
> vehicle.brand[bike]
>
>
> I can get the result in the following way:
>
> 1. create view Cars: persons join vehicle, where car_or_bike = car
> 2. create view Bikes: persons join vehicle, where car_or_bike = bike
> (and change the relevant colum names)
> 3. join the above view 1 and 2
>
> The first problem is that the above approach is quiet slow.
> Both starting tables (persons and vehicles are also views),
> and maybe it would be faster to create (temporary) tables ?
>
> The second problem is that in the real situation
> I'm not converting 1 column  to 2 columns,
> but it can be as large as 1 column to 10 columns.
>
> btw I'm running SQLite from Python, so maybe I should do some data
> handling outside SQL (in Python) ?

Yes, you are trying to get SQL to do your programming work for you.   
Just do two SELECT commands in Python: one to find the car for the  
person, and another to find the bike for the person.  No need to  
create any temporary tables, create any views, do any joins, or keep a  
huge table with all the results in.  Much smaller and faster.

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

Re: [sqlite] server process gives better concurrency - why?

[Ken]

The key to increased concurrency is MVCC. Without MVCC concurrency is limited 
to page locking, table locking etc. 

Google MVCC...

--- On Tue, 9/8/09, Iker Arizmendi  wrote:

> From: Iker Arizmendi 
> Subject: Re: [sqlite] server process gives better concurrency - why?
> To: sqlite-users@sqlite.org
> Date: Tuesday, September 8, 2009, 9:34 PM
> The question is whether a
> client-server design is /necessary/ to
> efficiently implement higher concurrency. It appears to be
> easier
> to do so with a client-server model, but is such a model
> required?
> Are there functions performed by a server process that
> cannot be
> carried out at all without it?
> 
> Iker
> 
> Simon Slavin wrote:
>  > If SQLite was to be
>  > designed to handle multiple processes 'properly', it
> would have to be
>  > rewritten as a client/server system.
>  >
>  > This would, of course, kill all the advantages of
> SQLite: it could no
>  > longer be tiny, fast, and ultra-portable.  So it
> would be a bad design
>  > choice for SQLite (bowing, of course, to DRH's right
> to do whatever he
>  > pleases with it).
>  >
>  > This is why I get uneasy when I see posts here that
> suggest spinning
>  > off threads especially to deal with locking issues,
> or do other things
>  > that solve concurrency or latency problems. 
> Often you find that
>  > making such a change in your program just leads to
> one of the threads
>  > immediately being blocked by another, defeating the
> point of threading
>  > in the first place.  Software has to be designed
> around what is
>  > possible with the tools you're using, not around some
> mythical idea of
>  > the perfect generic SQL engine.
>  >
>  > Simon.
> 
> -- 
> Iker Arizmendi
> AT Labs - Research
> Speech and Image Processing Lab
> e: i...@research.att.com
> w: http://research.att.com
> p: 973-360-8516
> 
> ___
> 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] how to get "One to many" in 1 row of a table ?

[Stef Mientki]

hello,

I'm just an amateur  struggling with databases,
so forgive if  I ask  a question every database user should know.

The situation is like this (simplified):

I've a table with persons: name, address, phone, etc.
I've another table with vehicles: car_or_bike, brand, type, build-year, etc.
The 2 tables are bounded by bind table: person_ID, vehicle_ID

I know that every person has 1 car and 1 bike

I want to show up a table, with 3 columns:
  persons.name  vehicle.brand[car]
vehicle.brand[bike]


I can get the result in the following way:

1. create view Cars: persons join vehicle, where car_or_bike = car
2. create view Bikes: persons join vehicle, where car_or_bike = bike 
(and change the relevant colum names)
3. join the above view 1 and 2

The first problem is that the above approach is quiet slow.
Both starting tables (persons and vehicles are also views),
and maybe it would be faster to create (temporary) tables ?

The second problem is that in the real situation
I'm not converting 1 column  to 2 columns,
but it can be as large as 1 column to 10 columns.

btw I'm running SQLite from Python, so maybe I should do some data 
handling outside SQL (in Python) ?

Any suggestions to make it faster and /or more structural will be 
greatly appreciated.

thanks,
Stef Mientki


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

[sqlite] SQLite version 3.6.18 scheduled for Monday

[D . Richard Hipp]

We are on schedule to release SQLite version 3.6.18 on Monday,  
2009-09-14.  For a preview, see

   http://www.sqlite.org/draft/index.html

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Column MetaData

[Jay A. Kreibich]

On Wed, Sep 09, 2009 at 07:33:31AM -0700, Sebastian Bermudez scratched on the 
wall:
> ok. i'm develop... an PHP-WEB Sqlite TUNNEL

  Because apparently the world doesn't have enough wheels?

>select * from tableA 
>  

> how can i to know the "base table name" of a column for make the
> insert statement in PHP without parsing the SQL QUERY.

  You can't.  There is no reason to assume a result column even
  has a base table.

> I assume:
> 1) Every query with only one table in Where clause is updatable.

  Hardly.  You need a lot more restrictions than that.  Consider
  a query like:

SELECT count(*), descri FROM tableA GROUP BY descri;

> I have Sqlite 2x.

  SQLite 2.x is five years old.  You're not likely to get a lot of
  help, as nearly everyone on the mailing list moved to 3.x a long long
  time ago.  They're different enough to be considered two different
  products.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Importing data into SQLite - text files are not really portable

[P Kishor]

On Wed, Sep 9, 2009 at 10:33 AM, Jim Showalter  wrote:
> Microsoft also supports an XML standard for import/export. Whenever
> possible, it should be used instead of CSV. It's not the
> line-terminators that are the problem--the problem is CSV itself.
> That's why Microsoft upgraded to supporting XML. They only support CSV
> for backward-compatibility reasons.

with reference to our problem here, how does exporting data to XML
help if one wants to then import it into sqlite3? As far as I can
tell, sqlite3 doesn't support importing XML, or does it?


>
> - Original Message -
> From: "Jean-Denis Muys" 
> To: "General Discussion of SQLite Database" 
> Sent: Wednesday, September 09, 2009 6:09 AM
> Subject: Re: [sqlite] Importing data into SQLite - text files are not
> really portable
>
>
>>
>> On 9/8/09 22:24 , various people wrote:
>>
>>> Unfortunately, the 3 main families of small computer operating
>>> systems
>>> have 3 different definitions of what a text file is...
>>>
>>> DOS/Windows (PC): lines are terminated with CR+LF
>>> Unix: lines are terminated with LF
>>> Macintosh: lines are terminated with CR
>> [...]
>>> FYI: Mac excel does not separate rows with \r, but inserts a ^M
>>> instead.
>> [...]
>>> From: "Kavita Raghunathan" 
>>>
>>> Yes, this works. Must have been my original csv file.
>>> I was using mac based excel and I'll now try using the windows
>>> based
>>> excel.
>>
>> For the record, the Mac has not been using CR line terminations for
>> many
>> years now (2001).
>>
>> Microsoft, in its not very high wisdom, proposes in the latest Mac
>> version
>> of Excel, 4 export formats that might correspond, with useless
>> names:
>>
>> 1- the main format at the top of the menu is named "Comma Separated
>> Values
>> (.csv)"
>> It's a comma -separated, CR-terminated format, with characters
>> encoded in
>> MacRoman ( http://en.wikipedia.org/wiki/Mac_OS_Roman).
>> As both line-termination and character-encoding conventions have
>> stopped
>> being used for many years, it's despicable from Micros__t to
>> continue to
>> name it as they do, and to propose it as the main choice.
>>
>> 2- secondary format named "Tab delimited text (.txt)"
>> Same as 1-, except the separator is now a tab character.
>> Totally useless format
>>
>> 3- secondary format named "Windows Comma-separated (.csv)"
>> Line terminations: CR-LF (Windows convention)
>> Character encoding: CP1502 (Windows extension to ISO-8859-1)
>> This is the most useful format, as it's likely to work for
>> interoperability
>> with the Windows world.
>>
>> 4- secondary format named "MS-Dos Comma-separated (.csv)"
>> Line terminations: CR
>> Character encoding: CP850 ( http://en.wikipedia.org/wiki/CP850)
>> A totally obsolete variant.
>>
>> Notably missing would be for example the *current* version: line
>> terminated
>> with LF and UTF8 encoding.
>>
>> I will stop there. Ranting on MS is bad for my nerves.
>>
>> Jean-Denis
>>
>>
>>
>>
>> ___
>> 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
>



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

Re: [sqlite] Importing data into SQLite - text files are not really portable

[Jim Showalter]

Microsoft also supports an XML standard for import/export. Whenever 
possible, it should be used instead of CSV. It's not the 
line-terminators that are the problem--the problem is CSV itself. 
That's why Microsoft upgraded to supporting XML. They only support CSV 
for backward-compatibility reasons.

- Original Message - 
From: "Jean-Denis Muys" 
To: "General Discussion of SQLite Database" 
Sent: Wednesday, September 09, 2009 6:09 AM
Subject: Re: [sqlite] Importing data into SQLite - text files are not 
really portable


>
> On 9/8/09 22:24 , various people wrote:
>
>> Unfortunately, the 3 main families of small computer operating 
>> systems
>> have 3 different definitions of what a text file is...
>>
>> DOS/Windows (PC): lines are terminated with CR+LF
>> Unix: lines are terminated with LF
>> Macintosh: lines are terminated with CR
> [...]
>> FYI: Mac excel does not separate rows with \r, but inserts a ^M 
>> instead.
> [...]
>> From: "Kavita Raghunathan" 
>>
>> Yes, this works. Must have been my original csv file.
>> I was using mac based excel and I'll now try using the windows 
>> based
>> excel.
>
> For the record, the Mac has not been using CR line terminations for 
> many
> years now (2001).
>
> Microsoft, in its not very high wisdom, proposes in the latest Mac 
> version
> of Excel, 4 export formats that might correspond, with useless 
> names:
>
> 1- the main format at the top of the menu is named "Comma Separated 
> Values
> (.csv)"
> It's a comma -separated, CR-terminated format, with characters 
> encoded in
> MacRoman ( http://en.wikipedia.org/wiki/Mac_OS_Roman).
> As both line-termination and character-encoding conventions have 
> stopped
> being used for many years, it's despicable from Micros__t to 
> continue to
> name it as they do, and to propose it as the main choice.
>
> 2- secondary format named "Tab delimited text (.txt)"
> Same as 1-, except the separator is now a tab character.
> Totally useless format
>
> 3- secondary format named "Windows Comma-separated (.csv)"
> Line terminations: CR-LF (Windows convention)
> Character encoding: CP1502 (Windows extension to ISO-8859-1)
> This is the most useful format, as it's likely to work for 
> interoperability
> with the Windows world.
>
> 4- secondary format named "MS-Dos Comma-separated (.csv)"
> Line terminations: CR
> Character encoding: CP850 ( http://en.wikipedia.org/wiki/CP850)
> A totally obsolete variant.
>
> Notably missing would be for example the *current* version: line 
> terminated
> with LF and UTF8 encoding.
>
> I will stop there. Ranting on MS is bad for my nerves.
>
> Jean-Denis
>
>
>
>
> ___
> 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] Column MetaData

[Sebastian Bermudez]

ok. i'm develop... an PHP-WEB Sqlite TUNNEL that PHP get a request, process 
it and send results to client (desktop app)
In the client side, i have my Own Ado Like Data Access components
my Question is:
my cliente send a query "select * from tableA"
the PHP exec the query and return an XML like:

   select * from tableA 
  
  
  
  
  
  row data here !
  


then the client modifies the resultset... (add new rows, delete rows, update 
rows)...
When i post the changes... i have antother xml ..
   select * from tableA 
 
how can i to know the "base table name" of a column for make the insert 
statement in PHP without parsing the SQL QUERY.

I assume:
1) Every query with only one table in Where clause is updatable.
2) View isn't updatable.
3) Query with more than one table in where is not updatable.

I have Sqlite 2x.

__
Correo Yahoo!
Espacio para todos tus mensajes, antivirus y antispam ¡gratis! 
¡Abrí tu cuenta ya! - http://correo.yahoo.com.ar
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ANN: AnySQL Maestro 9.8 released

[SQL Maestro Group]

Hi!

SQL Maestro Group announces the release of AnySQL Maestro 9.8, a powerful
tool for managing any database engine accessible via ODBC driver or OLE DB
provider (SQLite, MySQL, SQL Server, Oracle, Access, etc). The new version
is immediately available at
http://www.sqlmaestro.com/products/anysql/maestro/

This version features major improvements in Visual Query Builder, Data
Export and Data Import tools, significantly updated Data Grid and a lot of
other useful things. Full press-release (with explaining screenshots) is
available at:
http://www.sqlmaestro.com/news/company/anysql_maestro_9_8_released/

AnySQL Maestro comes in two editions: Freeware and Professional. The feature
matrix can be found at
http://www.sqlmaestro.com/products/anysql/maestro/free_vs_pro/

AnySQL Maestro 9.8 has been successfully tested with the SQLite ODBC driver
by Christian Werner (http://www.ch-werner.de/sqliteodbc/) and all the latest
SQLite versions.

Background information:

SQL Maestro Group is engaged in developing complete database admin and
management tools for MySQL, Oracle, MS SQL Server, DB2, PostgreSQL, SQL
Anywhere, SQLite, Firebird and MaxDB providing the highest performance,
scalability and reliability to meet the requirements of today's database
applications.

Sincerely yours,
The SQL Maestro Group Team
http://www.sqlmaestro.com

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

Re: [sqlite] Importing data into SQLite - text files are not really portable

[Jean-Denis Muys]

On 9/8/09 22:24 , various people wrote:

> Unfortunately, the 3 main families of small computer operating systems
> have 3 different definitions of what a text file is...
> 
> DOS/Windows (PC): lines are terminated with CR+LF
> Unix: lines are terminated with LF
> Macintosh: lines are terminated with CR
[...] 
> FYI: Mac excel does not separate rows with \r, but inserts a ^M instead.
[...]
> From: "Kavita Raghunathan" 
> 
> Yes, this works. Must have been my original csv file.
> I was using mac based excel and I'll now try using the windows based
> excel.

For the record, the Mac has not been using CR line terminations for many
years now (2001).

Microsoft, in its not very high wisdom, proposes in the latest Mac version
of Excel, 4 export formats that might correspond, with useless names:

1- the main format at the top of the menu is named "Comma Separated Values
(.csv)"
It's a comma -separated, CR-terminated format, with characters encoded in
MacRoman ( http://en.wikipedia.org/wiki/Mac_OS_Roman).
As both line-termination and character-encoding conventions have stopped
being used for many years, it's despicable from Micros__t to continue to
name it as they do, and to propose it as the main choice.

2- secondary format named "Tab delimited text (.txt)"
Same as 1-, except the separator is now a tab character.
Totally useless format

3- secondary format named "Windows Comma-separated (.csv)"
Line terminations: CR-LF (Windows convention)
Character encoding: CP1502 (Windows extension to ISO-8859-1)
This is the most useful format, as it's likely to work for interoperability
with the Windows world.

4- secondary format named "MS-Dos Comma-separated (.csv)"
Line terminations: CR
Character encoding: CP850 ( http://en.wikipedia.org/wiki/CP850)
A totally obsolete variant.

Notably missing would be for example the *current* version: line terminated
with LF and UTF8 encoding.

I will stop there. Ranting on MS is bad for my nerves.

Jean-Denis




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

[sqlite] Your Opinions About This Article

[Chris]

A Toronto Globe and Mail columnist writes about

Vacuum Places Improved (tested v1) for Firefox

Free Windows/Mac/Linux Firefox profile cleaner by Luca Niccoli

http://www.theglobeandmail.com/news/technology/the-everything-utility/article1279326/

Buried in the article he says:

"The places database powers Firefox's auto-complete address bar, giving
it full access to all the sites you've been to recently and the ones
you've bookmarked. But this new ability comes at a price: the more
robust SQLite database format suffers from slow performance without
routine maintenance, and Firefox doesn't perform this maintenance
itself. You could do it manually by figuring out the SQLite command
needed, but why bother when a Firefox add-on can do the job for you?"


This seems like 'damned with faint praise' to me.
Should I let it pass or is a comment on the article justified?


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

Re: [sqlite] server process gives better concurrency - why?

[Iker Arizmendi]

The benefits are the same as they would be for any application that
wants to use SQLite instead of a traditional database: trivial install
and configuration, simple administration, etc. Were SQLite to support
higher concurrency then these benefits could be extended to a wider
variety of applications. The point is try to identify the technical
issues that have stood in the way thus far and distilling them to
something simple that can be explored and hacked on without diving
too deeply into SQLite internals.

Iker

Igor Tandetnik wrote:
> 
> A better question may be - what's the benefit? What's the point of the 
> exercise? What's the advantage over the traditional server architecture?
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


-- 
Iker Arizmendi
AT Labs - Research
Speech and Image Processing Lab
e: i...@research.att.com
w: http://research.att.com
p: 973-360-8516

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

Re: [sqlite] Sqlite uses a lot of memory during a delete operation

[P Kishor]

On Tue, Sep 8, 2009 at 10:48 PM, Benjamin Rutt  wrote:
> On Mon, Sep 7, 2009 at 12:28 PM, P Kishor  wrote:
>
>> Find out if the DELETEion is chewing up the memory or the SELECTion. Try
>>
>> SELECT * FROM old_conns WHERE "end" >= strftime('%s', 'now', '-7 days');
>>
>> If the above is quick, you can simply create a new table with that,
>> and then drop the old table.
>>
>> CREATE TABLE new_conns AS SELECT * FROM old_conns WHERE "end" >=
>> strftime('%s', 'now', '-7 days');
>>
>> DROP TABLE old_conns;
>>
>
> When I do the select as you suggested, the process remains contained to a
> small amount of memory, which is good, but the result set from the select is
> huge.  As I wrote originally, about 50% of the table would be deleted by my
> delete, so about 50% of the table would be selected were I to use your
> select.  So yes, I could create a tmp table and insert into it, add the
> missing index, drop the old table, and rename the old to the new.


Well, I would recreate indexes as the last step in the process, after
I have dropped the old table. Indexes are probably significant in
size.

> This
> would work, but seems to me is quite an expensive hack.  It would also
> require 200% of the original table space on disk in the worst case, and may
> necessitate an extra vacuum operation after the fact to conserve disk space
> (the original db file is 8GB so I suspect it would double in size to 16GB in
> the worst case).  So it is a workaround, but not a cheap one.

Yes, but I am not sure if you actually tried the entire process and
measured the space and time tradeoffs. From your statements above, it
seems you haven't yet tried it since you write, "I could create a tmp
table..."

I would be curious to see if you get a decent space time tradeoff or
not. It may or may not be worth it.


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



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

Re: [sqlite] sqlite3_exec fails on arm

[gprand]

Hi,

I can confirm this behavoir. Now, after 2 days debugging and unsuccessful
searching around I have found your posting.  It drives me crazy, but I can
not locate the problem. Exactly the same code works, compiled for X86, and
fails, compiled for ARM. All machine depending configuration is omitted.

My poor debugging results are:
* 100 Bytes be read from the file.
* SQLite attempts to parse the sql text
* SQLite creates sqlite_master table
* Now it attempts to parse the sql statement again
* Now SQLite crashes in ARM mode.

In X86 mode 1024 bytes be read and all works fine.
Why?

Gottfried

Sorry for poor english.

>Hi,
>I am using sqlite3.6.17 on arm_v7 . sqlite3_Exec fails giving the following
>error sqlite3_exec  malformed database schema error : unrecognized token
"'".
>I am able to read the database manually using select commands. Executing
the
>same sqlite3_exec statements on x86 machine on the same db file does not
>give any error . Any help on this issue will be welcome,
-- 
View this message in context: 
http://www.nabble.com/sqlite3_exec-fails-on-arm-tp25293839p25362643.html
Sent from the SQLite mailing list archive at Nabble.com.

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