Re: [sqlite] SQLite Vs VistaDB - Comparison ???
[EMAIL PROTECTED] wrote: > > 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. When Process A is being killed and OS automatically releases the flocks, will rollback journal be present or not ? I think, in this situation, hot journal will be left when Process A is killed. > Process B comes along and opens the database, see the > inconsistent state, and reports database corruption. > If Process B finds a rollback journal...then...it can use that to rollback database and bring it to consistent state. --- Curiously thinking on this... What if a new SYSTEM-LOCK-MGMT-TABLE is added in SQLite database managed by itself for locking management ? (Surely, it is not an easy task to manage SYSTEM-LOCK-MGMT-TABLE..!!! ) Assuming that, if there is one an added SYSTEM-LOCK-TABLE in SQLite database managed by itself for locking management. Then when Process A wants to modify the database and it flock()s the rows it needs to changes, it should add corresponding info as record in SYSTEM-LOCK-TABLE for that... When somebody sends process A a SIGKILL and it dies, OS will automatically release flocks as process A dies. Process B (any other process accessing database) can find necessary locking related info from SYSTEM-LOCK-TABLE, as well as hot journal and so Process B can bring database back to consistent state. Rohit -- View this message in context: http://www.nabble.com/SQLite-Vs-VistaDB---Comparison-tf1797052.html#a5255158 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] SQLite Vs VistaDB - Comparison ???
> > 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]> Perhaps the way do it is journaling files. I have not study SQLite source codes in detail but i think that before an update or a create or a delete, it must do a search (so only a read) to know which pages and which bytes must be modified. So, Process A wants to modify the database and put on a master table which pages want to modify and where (from byte M to N), Process B checks the master table and if it want access the same info, it gets a lock or busy error, if not, update master table with it own pages and bytes. Process A writes the changes to pages in a journal file and if Process B was able to modify do the same in another journal. Both A and B deletes theirs rows from master table. Then mix both journal files and modify database file. HTH #The Unix Guru's View of Sex unzip ; strip ; touch ; grep ; finger ; mount ; fsck ; more ; yes ; umount ; sleep
RE: [sqlite] SQLite Vs VistaDB - Comparison ???
> -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
Re: [sqlite] SQLite Vs VistaDB - Comparison ???
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
Re: [sqlite] SQLite Vs VistaDB - Comparison ???
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 ???
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. ...
Re: [sqlite] SQLite Vs VistaDB - Comparison ???
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 ???
"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] SQLite Vs VistaDB - Comparison ???
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 ???
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 ???
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 ???
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 ???
[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 ???
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 ???
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] SQLite Vs VistaDB - Comparison ???
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 ???
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
