Enhancements for SQLite

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Enhancements for SQLite

joxeankoret



Hi list!

       I'm developing an open source project (a Data Access Grid) that
uses internally the SQLite library as the storage method and I'm
interested in your opinion about 4 questions:

1) SQLite can't deal with raw devices. Should be hard to patch the
source to deal with raw devices?

2) SQLite doesn't have support for views envolving multiple databases.

In example:

sqlite> attach 'test.db' as db2;
sqlite> create table db2.t1 (c1);
sqlite> create view v1 as select * from main.t1 union select * from
db2.t1;

I have a patch that works for me but the engine have problems when doing
a VACUUM if the other involved databases are detacheds and the unique
solution is to re-ATTACH the other involved databases.

I tried also to do the same with triggers (triggers that involves
multiple databases) but it appears to be hard. What can I do?

Attached goes patches to make working views envolving multiple
databases.

3) I want to write an stored procedure engine (such as Pl/PgSQL, Pl/SQL,
etc...). Is anyone working on something like this?

4) I have an idea about what can be done to get working a page level
locking to replace the database level locking. Currently SQLite has only
support for database level locking. My idea should works on POSIX based
systems. Why not use mmap, munmap, mprotect, mlock, etc... system calls?

Regards,
Joxean Koret

attach.c.patch (110 bytes) Download Attachment
sqliteInt.h.patch (488 bytes) Download Attachment
signature.asc (196 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Enhancements for SQLite

Jay Sprenkle
On 9/27/05, Joxean Koret <[hidden email]> wrote:
>
> Hi list!
>
> I'm developing an open source project (a Data Access Grid) that
> uses internally the SQLite library as the storage method and I'm
> interested in your opinion about 4 questions:
>
> 1) SQLite can't deal with raw devices. Should be hard to patch the
> source to deal with raw devices?



You'll have to write your own file system code (with locking).
Reply | Threaded
Open this post in threaded view
|

Re: Enhancements for SQLite

Christopher Petrilli
On 9/27/05, Jay Sprenkle <[hidden email]> wrote:
> > I'm developing an open source project (a Data Access Grid) that
> > uses internally the SQLite library as the storage method and I'm
> > interested in your opinion about 4 questions:
> >
> > 1) SQLite can't deal with raw devices. Should be hard to patch the
> > source to deal with raw devices?
>
> You'll have to write your own file system code (with locking).

Seriously though, there's no reason for this.  It's not going to help
on performance unless you also add all sorts of multi-spindle
management code.  Having worked a lot with Oracle on raw-devices, it's
only beneficial when you can throw lots of different spindles at it.

Chris
--
| Christopher Petrilli
| [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Enhancements for SQLite

D. Richard Hipp
In reply to this post by joxeankoret
On Tue, 2005-09-27 at 13:40 +0200, Joxean Koret wrote:
> Why not use mmap, munmap, mprotect, mlock, etc... system calls?
>

You ever tried to mmap a 10GiB database file into the memory
of processor with a 4GiB address space?
--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Enhancements for SQLite

Jay Sprenkle
In reply to this post by Christopher Petrilli
On 9/27/05, Christopher Petrilli <[hidden email]> wrote:

>
> On 9/27/05, Jay Sprenkle <[hidden email]> wrote:
> > > I'm developing an open source project (a Data Access Grid) that
> > > uses internally the SQLite library as the storage method and I'm
> > > interested in your opinion about 4 questions:
> > >
> > > 1) SQLite can't deal with raw devices. Should be hard to patch the
> > > source to deal with raw devices?
> >
> > You'll have to write your own file system code (with locking).
>
> Seriously though, there's no reason for this. It's not going to help
> on performance unless you also add all sorts of multi-spindle
> management code. Having worked a lot with Oracle on raw-devices, it's
> only beneficial when you can throw lots of different spindles at it.



It might be good if you're implementing on many operating systems that
don't all do file system locking the same way or even at all...