[sqlite] Two instances of app access same db, one instance hangs or killed

[RohitPatel9999]

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= *
Multiple instances of application can be started at the same time *
* For example two instances of application are running *
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= An
Win32 Application runs on Windows 98/XP/2000
It uses SQLite 3.3.5 compiled with -DTHREADSAFE=1 
App Creates/opens two SQLite databases one by one. (DB files are not on
network)
Checks in each database if necessary tables exist, if not creates necessary
tables in each database. 
Then in loop, application accepts user commands/requests and processes
User screens allow to Add/View/Modify/Delete records.
User screens allow to get some lists/records based on queries and
calculations.
Application uses db1 and db2 as and when needed.
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
* Multiple instances of application can be started at the same time
*
* For example two instances of application are running *
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Two instances of app are running. While each app is inside loop, if one app
instance hangs or killed or user terminates abruptly (Presses
Ctrl+Alt+Delete and kills the app), each database file will be in a
consistant state (SQLite is crash prrof and power-failure proof. Might leave
some rollback journal).

Questions:
1. If app instance is executing some transaction, so it might have locked
database(s) and if that app instance is being killed or hanged, will that
database will remain in locked state or lock will be released ?

2. Pointers to opened databases (db1, db2) in killed app will be consuming
resources, what will happen to them ?

2. How safe it is to continue using those databases by other app instance(s)
?

3. What precautions must be taken care of ? (to avoid database corruption,
to maintain consistant database and for better performance and to avoid any
deadlock)



/* app code snippent just to describe the logic */
int rc;
sqlite3* db1; 
sqlite3* db2; 
rc = sqlite3_open("C:\file1.db", &db1); 
if(rc) {
  printf("Cannot open file1.db: %s\n", sqlite3_errmsg(db1)); 
  exit(1); 
}
rc = sqlite3_open("C:\file2.db", &db2); 
if(rc) {
  printf("Cannot open file2.db: %s\n", sqlite3_errmsg(db2)); 
  exit(1); 
}
...
/* code to check if necessary tables exist in db1, if not create tables,
indexes etc.*/
/* code to check if necessary tables exist in db2, if not create tables,
indexes etc.*/
...
bool bExit = false;
do {  /* loop for processing user commands */
  ...
  /* User screens allow to Add/View/Modify/Delete records */
  /* User screens allow to get some lists/records based on queries and
calculations */
  /* Uses db1 and db2 as necessary */
  ...
  if(...user chose to exit...)
  bExit = true;
} while (bExit != true);
sqlite3_close(db1);
sqlite3_close(db2);

--
View this message in context: 
http://www.nabble.com/Two-instances-of-app-access-same-db%2C-one-instance-hangs-or-killed-t1797037.html#a4897129
Sent from the SQLite forum at Nabble.com.

[sqlite] SQLite Vs VistaDB - Comparison ???

[RohitPatel9999]

Hello

I was just comparing embedded SQL database engines (SQLite Vs VistaDB) for
my knowledge. Itmight be of help for someone. 

In case, someone might be interested to know and/or add more feature
comparisons.

SQLite
HomePage : www.sqlite.org
More Features : www.sqlite.org, www.sqlite.org/docs.html,
www.sqlite.org/faq.html
SQLite is a free open-source embedded SQL database engine (for multiple
platforms)
Cost? : Free Version (with no encryption feature, No password protection)
Encryption?: Encryption extension is Commercial, need to purchase license
Platforms? : For multiple platforms
Source Code Available? : Yes, for free version (No Encryption)
Size? : Small 250 KB footprint 
Single-file database format, database files can be freely shared between
machines with different byte orders. 
Multiple threads/processes can have the same database open at the same time
Supports concurrancy 
Transactions are atomic, consistent, isolated, and durable (ACID) even after
system crashes and power failures
Zero-configuration - no setup or administration needed.
SQL? : SQLite Implements most of SQL92 (standard SQL language). But does
omit some features and added a few features of its own. 
CSV and text file import and export
UTF-8, UTF-16 Support
Supports C/C++, PHP, Pearl, tcl, Python and many other languages through C
or through own wrappers, .NET port available, WindowsCE port available.
More Features : www.sqlite.org, www.sqlite.org/docs.html,
www.sqlite.org/faq.html

VistaDB  
HomePage : www.vistadb.com
More Features : www.vistadb.com/features.asp
VistaDB is a commercial embedded SQL database engine (only for .NET and
Win32)
Cost? : Need to purchase license, then Royalty free distribution 
Encryption?: Secure Blowfish encryption and password protection 
Platforms? : For Different Window Versions
Source Code Available? : No
Size? : Small 500KB footprint for Embedded Editions
Single-file database format 
Single and Multi-User support 
Supports concurrancy 
SQL? : Easy-to-use full featured RDBMS, provides industry support for SQL-92
and powerful Direct Data Access
Fast performance 
No deadlocks - Snapshot Isolation level Transaction Processing 
SureCommit
Row and Table-level locking 
Automatic Storage Recycling 
XML Import and Export 
Automatic data synchronization 
In-memory databases and tables 
Write-behind data caching 
International support 
Supports C#, VB.NET, Delphi, C++Builder, VB and classic ASP 
More Features : www.vistadb.com/features.asp


Please put forward your views, ideas, thoughts, comparisons (if any) ??? I
might have missed many points of comparison/similarity.


Rohit

--
View this message in context: 
http://www.nabble.com/SQLite-Vs-VistaDB---Comparison-t1797052.html#a4897159
Sent from the SQLite forum at Nabble.com.

Re: [sqlite] SQLite Vs VistaDB - Comparison ???

[René Tegel]

RohitPatel schreef:

Please put forward your views, ideas, thoughts, comparisons (if any) ??? I
might have missed many points of comparison/similarity.
  
At the risk of playing the devils advocate, if your target is ms windows 
(seen your interest for vistadb), i found MS-Access a very reasonable 
flat-file database. It may lack fancy features like encrytion, but has 
it advantages as well
Pro's: any windows client has the driver installed (no need to install 
office), accessable by odbc, reasonable sql (very much like mssql 
server), reasonable fast, able to be used as website-backend (!), allows 
simultanious users to certain amount (max. 5 recommended by MS), and 
flat-file which easifies back-ups and distribution. License not needed 
since it is licensed when using windows. Good indexes.
Cons: platform dependant. Sometimes bit weird SQL dialect (as anything 
from MS i guess). When not properly designed may take some time porting 
an database and/or application. Size of datafile may unproportionally 
grows to the amount of data. Unknown behaviour (to me) on hard crashes 
but probably repairable.


You may also want to investigate embedded MySQL.
Pros: full-blown mysql engine. Very configurable. Excellent 
multi-threading support. Cross-platform.
Cons: poorly documented (the embedded part (does and don'ts)), probably 
still buggy (4.1 was), may need license when shipped with 
commercial/closed source software. Possible crashed tables if the main 
application crashes. Probably you are better off seperating client and 
server (=traditional setup).


To be honest, for what sqlite was designed i think there is no serious 
alternative, sqlite is the best imho for embedded usage, and does not 
suffer platform or language dependancy. Small con: query parser may have 
trouble optimizing indices.


regards,

Rene

Re: [sqlite] Row Locking

[RohitPatel9999]

FoxPro has Row Level locking. There must be some way with which it must have
been implemented.

Just curious...how MS would have implemented row level locking ???

Rohit
--
View this message in context: 
http://www.nabble.com/Row-Locking-t69657.html#a4898759
Sent from the SQLite forum at Nabble.com.

Re: [sqlite] Locking

[RohitPatel9999]

FoxPro supports row level locking. Ofcourse FoxPro creates one file for each
table. There must be some way to implement row level locking. Probable by
locking region in a file or somehow.

Just thinking curiously...how MS could have implemented row level locking in
FoxPro.

Rohit

--
View this message in context: 
http://www.nabble.com/Locking-t799702.html#a4899211
Sent from the SQLite forum at Nabble.com.

Re: [sqlite] SQLite Vs VistaDB - Comparison ???

[Mikey C]

MS Access (MDB files) use the Jet engine.  Not every PC has the correct
drivers, since jet has changed many times as Access evolved from version 2.0
thru 95, XP and 2003.

Access is NOT ACID compliant, is limited in maximum database size, is
limited to 255 connections.

http://www.somacon.com/p369.php

However, JET's biggest gain over SQLite is it supports table and row level
locking.  If D. Hipp were to implement a fine grained locking mechanism in
SQLite, we'd be onto a winner.

Please implement table and row level locking. :-)
--
View this message in context: 
http://www.nabble.com/SQLite-Vs-VistaDB---Comparison-t1797052.html#a4899327
Sent from the SQLite forum at Nabble.com.

Re: [sqlite] SQLite Vs VistaDB - Comparison ???

[Jay Sprenkle]

On 6/16/06, René Tegel <[EMAIL PROTECTED]> wrote:

i found MS-Access a very reasonable
flat-file database. It may lack fancy features like encrytion, but has
it advantages as well
Pro's: any windows client has the driver installed (no need to install
office), accessable by odbc, reasonable sql (very much like mssql
server), reasonable fast, able to be used as website-backend (!),


I can't recommend Access files.

I've repeatedly seen file corruption in multiuser applications
using Microsoft's Access. No user written code ever touched
the database but we still suffered corruption problems.

Having program data accessable, and thus changable, by
the user without my application to control that access has
proven to be bad in many of my installations. If the user messes
with it I end up fixing the mess. In general I've found almost none
of them able or willing to use a database or report
writer.

It's not portable to anything else but windows.

It costs money to buy the development tools.


I've found more drawbacks than advantages.


--
--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

Re: [sqlite] accessing sqlite files directly via http

[Jay Sprenkle]

On 6/15/06, John Stanton <[EMAIL PROTECTED]> wrote:

I have implemented just such a system as an RPC.  It accesses an HTTP
server using CGI and returns the table or view requested in XML.


How do you ensure non malicious code is sent to RPC?

--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

Re: [sqlite] SQLite Vs VistaDB - Comparison ???

[drh]

Mikey C <[EMAIL PROTECTED]> wrote:
> 
> Please implement table and row level locking. :-)

People commonly believe that doing so must be easy.  I
certainly get a lot of requests for it from people who
think they know how.  But in fact, row-level locking
is extraordinarily difficult.  To my knowledge, nobody 
has yet come up with a way to do it unless you:

  *  Add a server process to coordinate access.

  *  Accept that the database might be corrupted if
 an application crashes while writing.

  *  Make writing to the database very, very slow.

  *  Double the size of the database file.

If you think you know a way to implement row-level
locking that does not impose one of the above
limitations, then please tell me and I will look
into the matter.

--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] SQLite Vs VistaDB - Comparison ???

[Bogusław Brandys]

[EMAIL PROTECTED] wrote:

Mikey C <[EMAIL PROTECTED]> wrote:

Please implement table and row level locking. :-)


People commonly believe that doing so must be easy.  I
certainly get a lot of requests for it from people who
think they know how.  But in fact, row-level locking
is extraordinarily difficult.  To my knowledge, nobody 
has yet come up with a way to do it unless you:


  *  Add a server process to coordinate access.

  *  Accept that the database might be corrupted if
 an application crashes while writing.

  *  Make writing to the database very, very slow.

  *  Double the size of the database file.

If you think you know a way to implement row-level
locking that does not impose one of the above
limitations, then please tell me and I will look
into the matter.



I'm not an expert but let me propose such solution:
Multi- Generational Architecture like Interbase/Firebird can cope with 
all except the last point (double size of database file) - however in 
the last case periodical vaccum could shrink database.
There is also no need to have server process - each instance of sqlite 
library could be a server.


Shared lock manager could be required or simply each instance of sqlite 
library could have separate one.
Shared lock manager is tricky idea (maybe it could be for example dumb 
manager in each sqlite library with shared memory pool and if one 
instance terminate another one could detect it and play that role?)


In fact that is as I fairy know how it's implemented in Firebird Classic 
Server (where each server process has separate lock manager I suppose)

This classic server processes  are spawn by xinetd deamon.
I see sqlite in very similar manner : sqlite library is attached to each 
spawned process which uses it.



Regards
Boguslaw Brandys

Re: [sqlite] SQLite Vs VistaDB - Comparison ???

[Clay Dowling]

Bogus³aw Brandys said:

> In fact that is as I fairy know how it's implemented in Firebird Classic
> Server (where each server process has separate lock manager I suppose)
> This classic server processes  are spawn by xinetd deamon.
> I see sqlite in very similar manner : sqlite library is attached to each
> spawned process which uses it.

You've just proposed changing SQLite from an embedded database to a server
database.  The fact that it would be a self-launching server doesn't
really change that.  It completely kills its value to me.  If I wanted a
server process running I'd use PostgreSQL and get the associated benefits
to boot.

Clay
-- 
Simple Content Management
http://www.ceamus.com

Re: [sqlite] SQLite Vs VistaDB - Comparison ???

[Christian Smith]

Bogus�aw Brandys uttered:


[EMAIL PROTECTED] wrote:

Mikey C <[EMAIL PROTECTED]> wrote:

Please implement table and row level locking. :-)


People commonly believe that doing so must be easy.  I
certainly get a lot of requests for it from people who
think they know how.  But in fact, row-level locking
is extraordinarily difficult.  To my knowledge, nobody has yet come up with 
a way to do it unless you:


  *  Add a server process to coordinate access.

  *  Accept that the database might be corrupted if
 an application crashes while writing.

  *  Make writing to the database very, very slow.

  *  Double the size of the database file.

If you think you know a way to implement row-level
locking that does not impose one of the above
limitations, then please tell me and I will look
into the matter.



I'm not an expert but let me propose such solution:
Multi- Generational Architecture like Interbase/Firebird can cope with all 
except the last point (double size of database file) - however in the last 
case periodical vaccum could shrink database.
There is also no need to have server process - each instance of sqlite 
library could be a server.


Shared lock manager could be required or simply each instance of sqlite 
library could have separate one.
Shared lock manager is tricky idea (maybe it could be for example dumb 
manager in each sqlite library with shared memory pool and if one instance 
terminate another one could detect it and play that role?)


In fact that is as I fairy know how it's implemented in Firebird Classic 
Server (where each server process has separate lock manager I suppose)

This classic server processes  are spawn by xinetd deamon.
I see sqlite in very similar manner : sqlite library is attached to each 
spawned process which uses it.



In order to communicate with the other lock managers, all instances of the 
SQLite library would have to be on the same box.


If you want MVCC without process communication (as not all processes would 
be on the same box) you'd need each row update to be synchronous and 
synced, which would be slower than what we have now.


The locking protocol could maybe be changed to allow locking at the table 
level, but such a change would be incompatible with the current locking 
protocol. And how do you manage multiple rollback journals for multiple 
writers? A sort of table level locking is already possible anyway using 
attached databases.


I can't see this being a feasible project.



Regards
Boguslaw Brandys




Christian

--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

Re: [sqlite] SQLite Vs VistaDB - Comparison ???

[Mikey C]

Okay I know very little about these things, but the fact that Access/JET MDB
files are serverless (it's just a bunch of Windows dll's) in the same way as
SQLite, and that JET implements row and table level locking means I guess it
is possible.

If it meant losing ACID compliance, then no, forget about it, but if it
meant much bigger database files, then no problem, as long as the row level
locking could be turned on or off at compile time (i.e. those who don't care
about row level locking, but do care about file size can compile without
it).

So if it can be implemented by storing a lock record for every row that is
about to be updated in a new system table, then why not?

Of course row level locking will make updates slower, but you can't have
fine grained locking and ultimate performance.

As I say, if it could be implemented knowing that:

1. Performance will be slower.
2. Database size will be bigger.
3. Row level locking can be compiled in or out.

Then I think the majority of users would want the benefit of increased write
concurrency, even at the expense of speed or database file size.


--
View this message in context: 
http://www.nabble.com/SQLite-Vs-VistaDB---Comparison-t1797052.html#a4902745
Sent from the SQLite forum at Nabble.com.

Re: [sqlite] SQLite Vs VistaDB - Comparison ???

[Jay Sprenkle]

On 6/16/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

Mikey C <[EMAIL PROTECTED]> wrote:
>
> Please implement table and row level locking. :-)


If you think you know a way to implement row-level
locking that does not impose one of the above
limitations, then please tell me and I will look
into the matter.


Out of curiosity why won't flock() work?
flock() allows locking an area within a file.
I know there are problems with locking on files accessed on a
network. I also recall when we used locking across an NFS
network with Sun workstations it was very slow. Aquiring locks
to a LONG time.

Re: [sqlite] SQLite Vs VistaDB - Comparison ???

[drh]

"Jay Sprenkle" <[EMAIL PROTECTED]> wrote:
> On 6/16/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > Mikey C <[EMAIL PROTECTED]> wrote:
> > >
> > > Please implement table and row level locking. :-)
> >
> >
> > If you think you know a way to implement row-level
> > locking that does not impose one of the above
> > limitations, then please tell me and I will look
> > into the matter.
> 
> Out of curiosity why won't flock() work?

Process A wants to modify the database, so it flock()s
the rows it needs to changes and starts changing them.
But half way in the middle of the change, somebody sends
process A a SIGKILL and it dies.  The OS automatically
releases the flocks as process A dies, leaving the
database half-way updated and in an inconsistent state,
with no locks.

Process B comes along and opens the database, see the
inconsistent state, and reports database corruption.

--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Locking

[Dennis Jenkins]

RohitPatel wrote:
> FoxPro supports row level locking. Ofcourse FoxPro creates one file for each
> table. There must be some way to implement row level locking. Probable by
> locking region in a file or somehow.
>
> Just thinking curiously...how MS could have implemented row level locking in
> FoxPro.
>   

FoxPro (DBase-III file structures) is a piece of shit that corrupts
all the time.  NEVER run FoxPro on a novell network with Win95 clients
(ok, so this was last decade).  FoxPro does region locking and has NO
JOURNAL.  "Rollbacks" are done by the client by replacing the changed
rows.  If the client crashes with a table (or table set) partially
modified, then the table is left inconsistent.  FoxPro is not ACID.

At my previous employer I wrote some C code to repair broken foxpro
tables.  (I  myself did not write foxpro code).  Our main database at
that time was 5 large tables, each about 256M in size.  All sitting on a
single Novell share.  Every few days a win95 or win98 client (100
clients, all using IPX, novell v3 on HUBS (not switches)) would send a
junk packet to the server. (or it sent a good packet that arrived
mangled).  From my analysis of the corrupted database file, it seems
that the client intended to send a "seek (somewhere far down in the
file)" and then "write".  But what actually happened is that the Novell
server seeked to file offset 0 (btw, the metadata for the table) and
dutifully wrote out the data record.  So instead of the DBF (foxpro
table) file having the proper header that defines the record layout for
the table, it contained junk.  The FoxPro guys had no way to fix this
except to try to restore from a backup and re-run tens of thousands of
"transactions".  So one day this happened and the CIO was freaking out. 
I pulled up a file format spec for the DBF file and fixed it using a hex
editor.  Later that day I wrote a tool to repair the damaged header.  A
fun hack, but a horrible situation and horrible technology.

The moral of the story is that you should never trust a database
system that does region lock of flat files sitting on a server IF the
server will release the lock when the client dies AND the client does
not leave behind some sort of useful journal.  Maybe even this statement
is too liberal.  I'm sure that someone in this group can make a better
argument for never using region locking.

Moral #2 is that creative use of hex editors in front of the CIO can
help your career.  Unless he's a total spaz and doesn't understand what
you are doing.

Re: [sqlite] SQLite Vs VistaDB - Comparison ???

[Bogusław Brandys]

Christian Smith wrote:

Bogus�aw Brandys uttered:


[EMAIL PROTECTED] wrote:

Mikey C <[EMAIL PROTECTED]> wrote:

Please implement table and row level locking. :-)


People commonly believe that doing so must be easy.  I
certainly get a lot of requests for it from people who
think they know how.  But in fact, row-level locking
is extraordinarily difficult.  To my knowledge, nobody has yet come 
up with a way to do it unless you:


  *  Add a server process to coordinate access.

  *  Accept that the database might be corrupted if
 an application crashes while writing.

  *  Make writing to the database very, very slow.

  *  Double the size of the database file.

If you think you know a way to implement row-level
locking that does not impose one of the above
limitations, then please tell me and I will look
into the matter.



I'm not an expert but let me propose such solution:
Multi- Generational Architecture like Interbase/Firebird can cope with 
all except the last point (double size of database file) - however in 
the last case periodical vaccum could shrink database.
There is also no need to have server process - each instance of sqlite 
library could be a server.


Shared lock manager could be required or simply each instance of 
sqlite library could have separate one.
Shared lock manager is tricky idea (maybe it could be for example dumb 
manager in each sqlite library with shared memory pool and if one 
instance terminate another one could detect it and play that role?)


In fact that is as I fairy know how it's implemented in Firebird 
Classic Server (where each server process has separate lock manager I 
suppose)

This classic server processes  are spawn by xinetd deamon.
I see sqlite in very similar manner : sqlite library is attached to 
each spawned process which uses it.



In order to communicate with the other lock managers, all instances of 
the SQLite library would have to be on the same box.


Or share the same lock data for example within sqlite database special 
table (internal like sqlite_master) In that case problem is to serialize 
access to lock data ,but we are talking about MG architecture where 
pessimistic locks are rare.




If you want MVCC without process communication (as not all processes 
would be on the same box) you'd need each row update to be synchronous 
and synced, which would be slower than what we have now.


Here I don't quite understand.I thought that MG architecture use 
transaction manager to manage transactions. There is not need to sync 
row update because each row has many record versions (and old committed 
are not removed until vacuum for example) each one with transaction ID 
and stamp
Problem: need to serialize transaction manager if working from 
concurrent computers on the same database

Problem: without vacuum there is more and more garbage inside database


The locking protocol could maybe be changed to allow locking at the 
table level, but such a change would be incompatible with the current 
locking protocol. And how do you manage multiple rollback journals for 
multiple writers? A sort of table level locking is already possible 
anyway using attached databases.


This is all about locking (pessimistic) not about MG architecture.


I can't see this being a feasible project.


Hey! As I stated I'm not an expert. ;-)

Regards
Boguslaw Brandys

RE: [sqlite] SQLite Vs VistaDB - Comparison ???

[Fred Williams]

I suggest we don't pick Access/Jet MDB as our shining example of
SQLite's future

Borland's old, dead, and gone Paradox was Access' main reason to come
into existence.  Inspire of outliving Paradox, only because of marketing
reasons, Access has never been able to leapfrog or even measure up to
old Paradox's last few gasps, IMHO.

This periodic "need" for vast "improvements" always seems to end up
proposing breaking the whole reason SQLite exists and why it addresses
its chosen segment of the market so wonderfully.

If someone needs table or row level locking in a multi-user environment,
selecting SQLite is like trying to take that old '57 fuel injected,
ultra lite Corvette and use it for a fully tricked out hearse.  Sounds
to me like a job for "Monster Garage" rather than CVS SQLite :-)

Fred

> -Original Message-
> From: Mikey C [mailto:[EMAIL PROTECTED]
> Sent: Friday, June 16, 2006 10:39 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] SQLite Vs VistaDB - Comparison ???
>
>
>
> Okay I know very little about these things, but the fact that
> Access/JET MDB
> files are serverless (it's just a bunch of Windows dll's) in
> the same way as
> SQLite, and that JET implements row and table level locking
> means I guess it
> is possible.
...

[sqlite] sqlite too slow for me?

[Péter Szabó]

Dear SQLite Developers,

I am seeking help for optimizing my SQLite SQL query, which seems to
be running unreasonably slow. The query is:

 SELECT col2 FROM t WHERE col1='foobar' AND
   col4='foobarfoobarfoobarfoob';

My schema is the following:

 CREATE TABLE t (
   col1 text NOT NULL,
   col2 integer NOT NULL,
   col3 integer NOT NULL,
   col4 text NOT NULL,
   col5 text NOT NULL,
   PRIMARY KEY(col1, col2, col3, col4, col5),
   UNIQUE(col1, col4, col5),
   UNIQUE(col4, col5, col1),
   UNIQUE(col5, col4, col1),
 );

Some statistics:

 SELECT COUNT(*) FROM t;
 355113

 SELECT COUNT(*) FROM t WHERE col1='foobar';
 355113

 SELECT COUNT(*) FROM t WHERE col1='foobar' AND
   col4='foobarfoobarfoobarfoob';
 96

 SELECT COUNT(DISTINCT col5) FROM t WHERE col1='foobar' AND
   col4='foobarfoobarfoobarfoob';
 96

All the above operations returning 96 are unmeasurably fast, i.e. they
return their answer immediately.

This is also instant, possibly because SQLite is using UNIQUE(col1,col4,col5):

 SELECT col5 FROM t WHERE col1='foobar' AND
   col4='foobarfoobarfoobarfoob';

However, this is very slow in SQLite 3.3.5:

 SELECT col2 FROM t WHERE col1='foobar' AND
   col4='foobarfoobarfoobarfoob';

It takes 25 seconds to return all the 96 rows on my PC (Celeron 2400
MHz, 512 MB of RAM, Linux). This is way too much for me. I was
expecting an instant answer.

I've also run these queries on the same data, using a MySQL server
version 4.1.4 with InnoDB tables, and all them, including the `SELECT
col2 ...' were unmeasurably
fast! (This is not because MySQL caches queries or results -- I've
restarted the MySQL server between each query.)

Is it possible to speed up the `SELECT col2 ...' query in SQLite? What
should I do?

Thanks,

Péter Szabó
free software consultant
Free Software Institute, Hungary

Re: [sqlite] Duplicate records

[Adam]

In message <[EMAIL PROTECTED]>,
Jay Sprenkle wrote:

> > Hi All,
> >
> > I'm creating a database which will look a little like:
> > create table main (f1 TEXT, f2 TEXT, f3 TEXT, t1 TEXT, t2 TEXT, tn TEXT);
> >
> > f1, f2 and f3 will always be present. Any or all of the ts might be NULL.
> >
> > I'd like to prevent entries where all of the fields are the same being
> > created. How can I do this. I've tried experimenting with UNIQUE KEYs but
> > haven't been successful - perhaps because of the NULLs?
> 
> You might try adding NOT NULL to your column constraints and
> a default to an empty string in the create table.
> I thought UNIQUE implied NOT NULL but I might be
> wrong.

Thanks a lot - I think I've got it working now:

  create table main (f1 TEXT not null default '~', f2 TEXT not null default
  '~', f3 TEXT not null default '~', t1 TEXT not null default '~', t2 TEXT
  not null default '~', tn TEXT not null default '~');
  
...gives me an error when I try to insert two similar rows :-) Now I'll just
have to work out how to sensibly deal with the error in my C app.

Cheers,
Adam
  
-- 
Adam Richardson
Carpe Diem

Re: [sqlite] sqlite too slow for me?

[Kurt Welgehausen]

"P?ter Szab?" <[EMAIL PROTECTED]> wrote:

> Dear SQLite Developers,
>
> I am seeking help for optimizing my SQLite SQL query, which seems to
> be running unreasonably slow. The query is:
>
>   SELECT col2 FROM t WHERE col1='foobar' AND
> col4='foobarfoobarfoobarfoob';
>
> My schema is the following:
>
>   CREATE TABLE t (
> col1 text NOT NULL,
> col2 integer NOT NULL,
> col3 integer NOT NULL,
> col4 text NOT NULL,
> col5 text NOT NULL,
> PRIMARY KEY(col1, col2, col3, col4, col5),
> UNIQUE(col1, col4, col5),
> UNIQUE(col4, col5, col1),
> UNIQUE(col5, col4, col1),
>   );

Just a guess: try

   SELECT +col2 FROM t ...


Regards

Re: [sqlite] SQLite Vs VistaDB - Comparison ???

[Andrew Piskorski]

On Fri, Jun 16, 2006 at 12:35:33PM -0400, [EMAIL PROTECTED] wrote:

> > Out of curiosity why won't flock() work?
> 
> Process A wants to modify the database, so it flock()s
> the rows it needs to changes and starts changing them.
> But half way in the middle of the change, somebody sends
> process A a SIGKILL and it dies.  The OS automatically
> releases the flocks as process A dies, leaving the
> database half-way updated and in an inconsistent state,
> with no locks.
> 
> Process B comes along and opens the database, see the
> inconsistent state, and reports database corruption.

Would it, at least in principle, be feasible to have Process B then
take a lock (hm, which lock?), notice somehow that A's transaction
failed without either committing or rolling back, read the rollback
journal written earlier by Process A, and rollback A's half-done work?
What in practice makes that not a good idea?

Would using a non-overwriting MVCC storage layer a la PostgreSQL (but
still using client SQLite processes only, no client/server
arrangement) make any of the above easier or better?

Note, I'm not suggesting that you should implement anything like this
in SQLite, I'm just curious in general...

-- 
Andrew Piskorski <[EMAIL PROTECTED]>
http://www.piskorski.com/

Re: [sqlite] SQLite Vs VistaDB - Comparison ???

[Doug Currie]

Friday, June 16, 2006, 5:32:32 PM, Andrew Piskorski wrote:

> Would using a non-overwriting MVCC storage layer a la PostgreSQL (but
> still using client SQLite processes only, no client/server
> arrangement) make any of the above easier or better?

See http://www.sqlite.org/cvstrac/wiki?p=BlueSky the shadow pager.

> Note, I'm not suggesting that you should implement anything like this
> in SQLite...

Me neither. ;-)

e

-- 
Doug Currie
Londonderry, NH

[sqlite] WHERE clause syntax error

[James W. Walker]

Let's say I have two tables A and B, each of which has an integer ID 
column.  I want to find ID values that occur in A but not B.  The 
first query I came up with was:


SELECT ID FROM A WHERE NOT EXISTS (SELECT * FROM B WHERE B.ID = A.ID);

but I get a syntax error.  My next try,

SELECT ID FROM A WHERE ID NOT IN (SELECT ID FROM B);

works and is probably more efficient, but I'm just curious what's 
wrong with the first one.

--
  James W. Walker, ScriptPerfection Enterprises, Inc.
  

RE: [sqlite] SQLite Vs VistaDB - Comparison ???

[Robert Simpson]

> -Original Message-
> From: RohitPatel [mailto:[EMAIL PROTECTED] 
> Sent: Friday, June 16, 2006 1:58 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] SQLite Vs VistaDB - Comparison ???

Getting back on track ...

> VistaDB  
> HomePage : www.vistadb.com
> More Features : www.vistadb.com/features.asp
> VistaDB is a commercial embedded SQL database engine (only 
> for .NET and
> Win32)

Only supports Win32 desktop.  No 64-bit available, no CE support either.
OleDB support is missing some features, and the .NET 2.0 provider is a
recompile of their 1.1 provider and has almost no support for any of the
ADO.NET 2.0 features.

> Encryption?: Secure Blowfish encryption and password protection 

There are several of us providing free SQLite implementations with built-in
encryption.

> Platforms? : For Different Window Versions

Again, only 32-bit desktops.  There is a CE provider that lets you connect
to a desktop database over the wire, but there's no embedded version of
VistaDB for CE.

> Fast performance 

Not.  VistaDb is slower than Access/JET in nearly every test I ran, from
bulk inserts to simple indexed joins to multi-table joins.  Access trounced
VistaDb in every category -- and SQLite trounced Access in every category.

> International support 
> Supports C#, VB.NET, Delphi, C++Builder, VB and classic ASP 

There've been lots of complaints about VistaDb's international support.  It
has no unicode or UTF8/16 support.

> More Features : www.vistadb.com/features.asp
> 
> 
> Please put forward your views, ideas, thoughts, comparisons 
> (if any) ??? I
> might have missed many points of comparison/similarity.

VistaDB's database size is also massive, but its one area that it beat Jet
in my tests:

http://sqlite.phxsoftware.com/forums/622/ShowPost.aspx

Robert