Re: [sqlite] Sqlite inside MySQL as one of the pluggable "storage mechanisms" ?
Jim C. Nasby wrote: On Sun, Apr 23, 2006 at 07:30:21AM +1000, John Stanton wrote: I have actually done that and it works well for a particular class of applications, ones with a relatively small number of simultaneous users. For large numbers we switch to PostgreSQL The basic architecture of Sqlite, and why it is "Lite", is that it uses a single file and file locks for synchronization. That is very well adapted to myriads of applications, but does not make it a competitor to DB2, Oracle or PostgreSQL in the enterprise area. The single file makes it a dream to maintain, but there is a price involved for that simplicity. Dr Hipp put it succinctly when he pointed out that Sqlite is a replacement for fopen(), not Oracle. In our server we do not rely on the file locks in Sqlite but instead serialize the transactions using the mutex/condition/event capabilities which become available in such a framework. As for your idea of pluggable storage methods, I consider that a better approach for an embedded tool like Sqlite is to embed the other functionality alongside it. Both would be linked into the specific application. For example the people who are eager to have more BLOB features could do well to have a second file which just stores BLOBs in their chosen style and links to Sqlite via a key. On a semi-related note, Sean Chittenden (of FreeBSD fame) created an API from PostgreSQL into memcached (http://pgfoundry.org/projects/pgmemcache/), mainly to allow the database to invalidate objects from memcached that had been updated. I think it would be very interesting to see something similar that would allow using SQLite from within PostgreSQL, since there's a few applications that are difficult to get good performance out of with PostgreSQL. Website session tables that are constantly updated are one example. Food for thought.
Re: [sqlite] Sqlite inside MySQL as one of the pluggable "storage mechanisms" ?
On Sun, Apr 23, 2006 at 07:30:21AM +1000, John Stanton wrote: > I have actually done that and it works well for a particular class of > applications, ones with a relatively small number of simultaneous users. > For large numbers we switch to PostgreSQL The basic architecture of > Sqlite, and why it is "Lite", is that it uses a single file and file > locks for synchronization. That is very well adapted to myriads of > applications, but does not make it a competitor to DB2, Oracle or > PostgreSQL in the enterprise area. The single file makes it a dream to > maintain, but there is a price involved for that simplicity. > > Dr Hipp put it succinctly when he pointed out that Sqlite is a > replacement for fopen(), not Oracle. > > In our server we do not rely on the file locks in Sqlite but instead > serialize the transactions using the mutex/condition/event capabilities > which become available in such a framework. > > As for your idea of pluggable storage methods, I consider that a better > approach for an embedded tool like Sqlite is to embed the other > functionality alongside it. Both would be linked into the specific > application. For example the people who are eager to have more BLOB > features could do well to have a second file which just stores BLOBs in > their chosen style and links to Sqlite via a key. On a semi-related note, Sean Chittenden (of FreeBSD fame) created an API from PostgreSQL into memcached (http://pgfoundry.org/projects/pgmemcache/), mainly to allow the database to invalidate objects from memcached that had been updated. I think it would be very interesting to see something similar that would allow using SQLite from within PostgreSQL, since there's a few applications that are difficult to get good performance out of with PostgreSQL. Website session tables that are constantly updated are one example. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] Sqlite inside MySQL as one of the pluggable "storage mechanisms" ?
I have actually done that and it works well for a particular class of applications, ones with a relatively small number of simultaneous users. For large numbers we switch to PostgreSQL The basic architecture of Sqlite, and why it is "Lite", is that it uses a single file and file locks for synchronization. That is very well adapted to myriads of applications, but does not make it a competitor to DB2, Oracle or PostgreSQL in the enterprise area. The single file makes it a dream to maintain, but there is a price involved for that simplicity. Dr Hipp put it succinctly when he pointed out that Sqlite is a replacement for fopen(), not Oracle. In our server we do not rely on the file locks in Sqlite but instead serialize the transactions using the mutex/condition/event capabilities which become available in such a framework. As for your idea of pluggable storage methods, I consider that a better approach for an embedded tool like Sqlite is to embed the other functionality alongside it. Both would be linked into the specific application. For example the people who are eager to have more BLOB features could do well to have a second file which just stores BLOBs in their chosen style and links to Sqlite via a key. JS Russell Leighton wrote: I was afraid of that...it would be cool if someone created a sqlite server which handled the networking and serialization...I would take a crack at it myself but right now I don't have time. Dan Kennedy wrote: I had a musing while reading: http://news.com.com/2100-7344_3-6063599.html?part=rss=6063599=news where it reminded me of one of MySQL's features: MySQL's database is built so that it can use a range of different storage mechanisms, tuned for different purposes, such as transactions or indexing large amounts of text. "Rather than have one perfect engine, it's better to have a pluggable architecture," Urlocker said. "The idea is you can mix and match within a single application because data will be used in different ways." Perhaps adding the appropriate bindings to MySQL to register sqlite as a storage mechanism would allow the use of Sqlite in a network environment with out bloating the core Sqlite code? Maybe the above makes no sense, but I find the notion appealing that Sqlite could remain as tight little sql engine but could use the "network server" of MySQL should ever such a thing be needed. Would that be possible? It's possible. But the mysql interface is looking for something more like the sqlite btree layer - you would be effectively bypassing the sqlite schema layer entirely and just using sqlite as a simple key-value database (like berkeley db). I think it would be extremely tricky to make the sqlite schema visible to the mysql client. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] Sqlite inside MySQL as one of the pluggable "storage mechanisms" ?
I was afraid of that...it would be cool if someone created a sqlite server which handled the networking and serialization...I would take a crack at it myself but right now I don't have time. Dan Kennedy wrote: I had a musing while reading: http://news.com.com/2100-7344_3-6063599.html?part=rss=6063599=news where it reminded me of one of MySQL's features: MySQL's database is built so that it can use a range of different storage mechanisms, tuned for different purposes, such as transactions or indexing large amounts of text. "Rather than have one perfect engine, it's better to have a pluggable architecture," Urlocker said. "The idea is you can mix and match within a single application because data will be used in different ways." Perhaps adding the appropriate bindings to MySQL to register sqlite as a storage mechanism would allow the use of Sqlite in a network environment with out bloating the core Sqlite code? Maybe the above makes no sense, but I find the notion appealing that Sqlite could remain as tight little sql engine but could use the "network server" of MySQL should ever such a thing be needed. Would that be possible? It's possible. But the mysql interface is looking for something more like the sqlite btree layer - you would be effectively bypassing the sqlite schema layer entirely and just using sqlite as a simple key-value database (like berkeley db). I think it would be extremely tricky to make the sqlite schema visible to the mysql client. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] Sqlite inside MySQL as one of the pluggable "storage mechanisms" ?
> > I had a musing while reading: > > > http://news.com.com/2100-7344_3-6063599.html?part=rss=6063599=news > > > where it reminded me of one of MySQL's features: > > MySQL's database is built so that it can use a range of different > storage mechanisms, tuned for different purposes, such as > transactions or indexing large amounts of text. > > "Rather than have one perfect engine, it's better to have a > pluggable architecture," Urlocker said. "The idea is you can mix and > match within a single application because data will be used in > different ways." > > > Perhaps adding the appropriate bindings to MySQL to register sqlite as a > storage mechanism would > allow the use of Sqlite in a network environment with out bloating the > core Sqlite code? > > Maybe the above makes no sense, but I find the notion appealing that > Sqlite could remain as tight little sql engine but could use the > "network server" > of MySQL should ever such a thing be needed. > > Would that be possible? It's possible. But the mysql interface is looking for something more like the sqlite btree layer - you would be effectively bypassing the sqlite schema layer entirely and just using sqlite as a simple key-value database (like berkeley db). I think it would be extremely tricky to make the sqlite schema visible to the mysql client. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[sqlite] Sqlite inside MySQL as one of the pluggable "storage mechanisms" ?
I had a musing while reading: http://news.com.com/2100-7344_3-6063599.html?part=rss=6063599=news where it reminded me of one of MySQL's features: MySQL's database is built so that it can use a range of different storage mechanisms, tuned for different purposes, such as transactions or indexing large amounts of text. "Rather than have one perfect engine, it's better to have a pluggable architecture," Urlocker said. "The idea is you can mix and match within a single application because data will be used in different ways." Perhaps adding the appropriate bindings to MySQL to register sqlite as a storage mechanism would allow the use of Sqlite in a network environment with out bloating the core Sqlite code? Maybe the above makes no sense, but I find the notion appealing that Sqlite could remain as tight little sql engine but could use the "network server" of MySQL should ever such a thing be needed. Would that be possible?
