sqlite dbmail

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

sqlite dbmail

DTK-2
Hello,

I don't know if this is even news to you probably not, dbmail ships
now with an sqlite back end option.

I really like the idea of using sqlite as the backend but I am trying
to get them to make a modification so I took it on my self to do the
research for it to make it easier on them if at all possible.

Here I go:
The sqlite backend right now works the same way as mysql and the pgsql
backend. Meaning all data in one db.
What I would like is to have a sqlite db file per user.
This would have many advantages like easy backup on a per user base.
No reader/writer lock concerns
Better security since each user would have access only to there own db.
Faster data access since data is stored in small chunks.
etc etc.

The challenge:
There is data like email aliases which need to be accessed by the MTA.
It wouldn't be a good idea to open each users db to check the aliases
for each email that arrives. So what we would need is a common db
which mirrors one table from all the user databases.
The dbmail guys suggest to use ldap, witch is already in existence and
could probably be easy modified to do this. However I don't like the
idea of having one more component specially ldap. So how could this be
done with sqlite only is the question.

Thanks

Demi
Reply | Threaded
Open this post in threaded view
|

Re: sqlite dbmail

D. Richard Hipp
DK <[hidden email]> wrote:
> There is data like email aliases which need to be accessed by the MTA.
> It wouldn't be a good idea to open each users db to check the aliases
> for each email that arrives. So what we would need is a common db
> which mirrors one table from all the user databases.

Have one database containing email aliases and/or other shared
information and then one email per user.  Then use the ATTACH
command to join them together at runtime.  See

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

--
D. Richard Hipp   <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: sqlite dbmail

DTK-2
On 6/23/06, [hidden email] <[hidden email]> wrote:

> DK <[hidden email]> wrote:
> > There is data like email aliases which need to be accessed by the MTA.
> > It wouldn't be a good idea to open each users db to check the aliases
> > for each email that arrives. So what we would need is a common db
> > which mirrors one table from all the user databases.
>
> Have one database containing email aliases and/or other shared
> information and then one email per user.  Then use the ATTACH
> command to join them together at runtime.  See
>
>   http://www.sqlite.org/lang_attach.html
>
> --
> D. Richard Hipp   <[hidden email]>
>

Thanks Richard,
But it says: "There is a compile-time limit of 10 attached database
files" If this means max db 10 then this is no good we will have more
then 10 users.

Demi
Reply | Threaded
Open this post in threaded view
|

Re: sqlite dbmail

D. Richard Hipp
DK <[hidden email]> wrote:

> On 6/23/06, [hidden email] <[hidden email]> wrote:
> > DK <[hidden email]> wrote:
> > > There is data like email aliases which need to be accessed by the MTA.
> > > It wouldn't be a good idea to open each users db to check the aliases
> > > for each email that arrives. So what we would need is a common db
> > > which mirrors one table from all the user databases.
> >
> > Have one database containing email aliases and/or other shared
> > information and then one email per user.  Then use the ATTACH
> > command to join them together at runtime.  See
> >
> >   http://www.sqlite.org/lang_attach.html
> >
> > --
> > D. Richard Hipp   <[hidden email]>
> >
>
> Thanks Richard,
> But it says: "There is a compile-time limit of 10 attached database
> files" If this means max db 10 then this is no good we will have more
> then 10 users.
>

You are probably not serving all your users all at once.  Just
ATTACH and DETACH user databases on an as-needed basis.
--
D. Richard Hipp   <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: sqlite dbmail

DTK-2
On 6/23/06, [hidden email] <[hidden email]> wrote:

> DK <[hidden email]> wrote:
> > On 6/23/06, [hidden email] <[hidden email]> wrote:
> > > DK <[hidden email]> wrote:
> > > > There is data like email aliases which need to be accessed by the MTA.
> > > > It wouldn't be a good idea to open each users db to check the aliases
> > > > for each email that arrives. So what we would need is a common db
> > > > which mirrors one table from all the user databases.
> > >
> > > Have one database containing email aliases and/or other shared
> > > information and then one email per user.  Then use the ATTACH
> > > command to join them together at runtime.  See
> > >
> > >   http://www.sqlite.org/lang_attach.html
> > >
> > > --
> > > D. Richard Hipp   <[hidden email]>
> > >
> >
> > Thanks Richard,
> > But it says: "There is a compile-time limit of 10 attached database
> > files" If this means max db 10 then this is no good we will have more
> > then 10 users.
> >
>
> You are probably not serving all your users all at once.  Just
> ATTACH and DETACH user databases on an as-needed basis.
> --
> D. Richard Hipp   <[hidden email]>
>
>

Well postfix when mail arrives checks if the to field of the email
matches up with any users aliases. One user can have many aliases as
in [hidden email], [hidden email], [hidden email]
but also [hidden email], [hidden email],
[hidden email] these are all aliases which belong to one user
one mail box. Postfix must check this table field for every user.
Right now we have a table with two fields userid and alias. Postfix
checks the alias field to know if that users exist then if the alias
exists postfix accepts that email sends it over to dbmail, dbmail
matches the alias field to the userid and delivers the mail.

Demi
Reply | Threaded
Open this post in threaded view
|

Re: sqlite dbmail

Dennis Cote
DK wrote:

>
> Well postfix when mail arrives checks if the to field of the email
> matches up with any users aliases. One user can have many aliases as
> in [hidden email], [hidden email], [hidden email]
> but also [hidden email], [hidden email],
> [hidden email] these are all aliases which belong to one user
> one mail box. Postfix must check this table field for every user.
> Right now we have a table with two fields userid and alias. Postfix
> checks the alias field to know if that users exist then if the alias
> exists postfix accepts that email sends it over to dbmail, dbmail
> matches the alias field to the userid and delivers the mail.
>
Demi,

You should store all your user alias information in a table in the
common database. Postfix will only use the common database. For each
user connection dbmail will open one user database and attach the common
database. When it is done it will close that user database (which closes
the attached common database as well). Your dbmail will only have one
attached database at any point in time, the common database.

Dennis Cote
Reply | Threaded
Open this post in threaded view
|

Re: sqlite dbmail

DTK-2
On 6/23/06, Dennis Cote <[hidden email]> wrote:

> DK wrote:
> >
> > Well postfix when mail arrives checks if the to field of the email
> > matches up with any users aliases. One user can have many aliases as
> > in [hidden email], [hidden email], [hidden email]
> > but also [hidden email], [hidden email],
> > [hidden email] these are all aliases which belong to one user
> > one mail box. Postfix must check this table field for every user.
> > Right now we have a table with two fields userid and alias. Postfix
> > checks the alias field to know if that users exist then if the alias
> > exists postfix accepts that email sends it over to dbmail, dbmail
> > matches the alias field to the userid and delivers the mail.
> >
> Demi,
>
> You should store all your user alias information in a table in the
> common database. Postfix will only use the common database. For each
> user connection dbmail will open one user database and attach the common
> database. When it is done it will close that user database (which closes
> the attached common database as well). Your dbmail will only have one
> attached database at any point in time, the common database.
>
> Dennis Cote
>

Good one Dennis.

Thank you

Demi
Reply | Threaded
Open this post in threaded view
|

Embedded Database Functions

John Stanton-3
In reply to this post by DTK-2
There has been discussion about extending Sqlite to have more functions,
but the risk is creating Sqlite-Bloat and losing the most endearing
feature of the product, its light weight and simplicity.

Here is an interesting and thought provoking discussion on the general
subject.  A good case is made for more extensive use of a data-specific
language, specifically PL/SQL.

It would be a major restructure of Sqlite, but it would be intriguing to
see the VDBE virtual machine extended to have the features to execute a
subset of PL/SQL and to have the Sqlite compiler also compile the PL/SQL
dialect into virtual machine code.  Then executables could be stored in
the database, functions could be added and with ingenious design the
footprint could be smaller by splitting off the compiler and having as
many internal functions as possible realized in the dense virtual
machine code.  It is not a new concept, having been the principle behind
Java and many other similar projects, but it is one where the ideal
solution is yet to emerge.

By using an efficient virtual machine with a dense target code
performance should be good and be frugal on resources on embedded
devices.  The virtual machine code is processor independent, making
distribution of modules very straight forward.

http://www.viacognis.com/devicesql/ArticleDataCentricSoftware.pdf


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Embedded Database Functions

Eduardo Morras
At 03:41 22/12/2006, you wrote:

>There has been discussion about extending Sqlite to have more
>functions, but the risk is creating Sqlite-Bloat and losing the most
>endearing feature of the product, its light weight and simplicity.
>
>Here is an interesting and thought provoking discussion on the
>general subject.  A good case is made for more extensive use of a
>data-specific language, specifically PL/SQL.
>
>It would be a major restructure of Sqlite, but it would be
>intriguing to see the VDBE virtual machine extended to have the
>features to execute a subset of PL/SQL and to have the Sqlite
>compiler also compile the PL/SQL dialect into virtual machine
>code.  Then executables could be stored in the database, functions
>could be added and with ingenious design the footprint could be
>smaller by splitting off the compiler and having as many internal
>functions as possible realized in the dense virtual machine
>code.  It is not a new concept, having been the principle behind
>Java and many other similar projects, but it is one where the ideal
>solution is yet to emerge.
>
>By using an efficient virtual machine with a dense target code
>performance should be good and be frugal on resources on embedded
>devices.  The virtual machine code is processor independent, making
>distribution of modules very straight forward.
>
>http://www.viacognis.com/devicesql/ArticleDataCentricSoftware.pdf

Sounds very interesting, and after reading it i think there no
embedded database like sqlite that has PL/SQL, only Oracle and
similar sizes. I think it's a very good idea and i can help in
develop it, but don't know much about the VDBE code. I think adding
control structures to SQLite SQL will not make it heavier, perhaps
20-40 Kb of optional code in parser/vdbe. Optimization will be another headache

But the question is always the same, Who?. Read this message from Dr.
Richard Hipp on 23/10/2006 with subject "Re: [sqlite] Extra functions
- New Project?"



-----------------------------------------------------------------------------------------
Useful Acronymous : FAQ = Frequently 'Answered' Questions  


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Embedded Database Functions

Roger Binns
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Eduardo Morras wrote:
| but don't know much about the VDBE code.

The opcodes and operation (stacks etc) are all well documented:

~ http://www.sqlite.org/opcode.html

All the necessary opcodes are already present in that you can do checks
(ge, eq, le etc), conditionals (if) and program flow (goto, gosub, return).

A simple sanity check of your idea is to have some PL/SQL (or whatever
language of your choice) and hand compile it into VDBE.  You'll soon get
a feel for whether the existing opcodes work well, or if you will need
something completely different.  Going from hand compilation to machine
compilation is then a simple matter of programming :-)

If you are unfamiliar with how to write a compiler then almost any
textbook out there will do.  They usually have a first section going to
extraordinary length on grammars, automata, parsing and lexing etc.  The
good news is you can just use Lemon which is another fine drh product
and used for the SQL grammar in SQLite.  Pretty much every book and
tutorial then shows how to emit code for a simple stack machine.
Luckily enough, that is exactly what vdbe is.  Later chapters will be on
generating error messages, optimizations, and dealing with the fun low
level things in modern CPUs, all of which you can ignore.

There is even a starting grammar for you:

~  http://www.antlr.org/grammar/1107752678378/PLSQLGrammar.g

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFjj0GmOOfHg372QQRAiAaAJwINxMkhdyUFKKu/fT3HGkEcjquOACeO06x
WY7L3Zhh8h2YbU60oMzNC1Q=
=qKyu
-----END PGP SIGNATURE-----

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Embedded Database Functions

John Stanton-3
A compiler for a subset of PL/SQL would not be too arduous a project,
and a code generator into VDBE would, as you suggest, give an insight
into what extra opcodes would be desirable to produce dense PL/SQ.
Obviously the SQL component and optimization is already handled.

I can see that the major obstacle to such a concept would be the
expectation among users of complete compatibility with Oracle so that
complete Oracle systems could be migrated to Sqlite.

The benefit would be having platform independent executables which could
be stored in the database, a major advantage.

Roger Binns wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Eduardo Morras wrote:
> | but don't know much about the VDBE code.
>
> The opcodes and operation (stacks etc) are all well documented:
>
> ~ http://www.sqlite.org/opcode.html
>
> All the necessary opcodes are already present in that you can do checks
> (ge, eq, le etc), conditionals (if) and program flow (goto, gosub, return).
>
> A simple sanity check of your idea is to have some PL/SQL (or whatever
> language of your choice) and hand compile it into VDBE.  You'll soon get
> a feel for whether the existing opcodes work well, or if you will need
> something completely different.  Going from hand compilation to machine
> compilation is then a simple matter of programming :-)
>
> If you are unfamiliar with how to write a compiler then almost any
> textbook out there will do.  They usually have a first section going to
> extraordinary length on grammars, automata, parsing and lexing etc.  The
> good news is you can just use Lemon which is another fine drh product
> and used for the SQL grammar in SQLite.  Pretty much every book and
> tutorial then shows how to emit code for a simple stack machine.
> Luckily enough, that is exactly what vdbe is.  Later chapters will be on
> generating error messages, optimizations, and dealing with the fun low
> level things in modern CPUs, all of which you can ignore.
>
> There is even a starting grammar for you:
>
> ~  http://www.antlr.org/grammar/1107752678378/PLSQLGrammar.g
>
> Roger
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFFjj0GmOOfHg372QQRAiAaAJwINxMkhdyUFKKu/fT3HGkEcjquOACeO06x
> WY7L3Zhh8h2YbU60oMzNC1Q=
> =qKyu
> -----END PGP SIGNATURE-----
>
> -----------------------------------------------------------------------------
>
> To unsubscribe, send email to [hidden email]
> -----------------------------------------------------------------------------
>
>


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Embedded Database Functions

Andrew Piskorski
On Sun, Dec 24, 2006 at 09:35:01AM -0600, John Stanton wrote:

> >There is even a starting grammar for you:
> >  http://www.antlr.org/grammar/1107752678378/PLSQLGrammar.g

> A compiler for a subset of PL/SQL would not be too arduous a project,

If what you want is something like PL/SQL, it might be both easier and
better to retarget PostgreSQL's PL/pgSQL for SQLite opcodes, rather
than starting from scratch with your own Oracle PL/SQL style language.
(And PL/pgSQL's BSD license is compatible with SQLite's.)

--
Andrew Piskorski <[hidden email]>
http://www.piskorski.com/

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Embedded Database Functions

developir@yahoo.com
--- Andrew Piskorski <[hidden email]> wrote:
> If what you want is something like PL/SQL, it might be both easier and
> better to retarget PostgreSQL's PL/pgSQL for SQLite opcodes, rather
> than starting from scratch with your own Oracle PL/SQL style language.
> (And PL/pgSQL's BSD license is compatible with SQLite's.)

It may be compatible, but it is not public domain.

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com 

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Embedded Database Functions

John Stanton-3
In reply to this post by Andrew Piskorski
Andrew Piskorski wrote:

> On Sun, Dec 24, 2006 at 09:35:01AM -0600, John Stanton wrote:
>
>
>>>There is even a starting grammar for you:
>>> http://www.antlr.org/grammar/1107752678378/PLSQLGrammar.g
>
>
>>A compiler for a subset of PL/SQL would not be too arduous a project,
>
>
> If what you want is something like PL/SQL, it might be both easier and
> better to retarget PostgreSQL's PL/pgSQL for SQLite opcodes, rather
> than starting from scratch with your own Oracle PL/SQL style language.
> (And PL/pgSQL's BSD license is compatible with SQLite's.)
>
That is a rational suggestion, since Sqlite and PostgreSQL make up an
open source broad solution for data storage.  PostgreSQL picks up where
Sqlite leaves off so compatibility with that would be more likely to fit
in with user's plans.

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Embedded Database Functions

Eduardo Morras
In reply to this post by Andrew Piskorski
At 17:00 24/12/2006, you wrote:

>On Sun, Dec 24, 2006 at 09:35:01AM -0600, John Stanton wrote:
>
> > >There is even a starting grammar for you:
> > >  http://www.antlr.org/grammar/1107752678378/PLSQLGrammar.g
>
> > A compiler for a subset of PL/SQL would not be too arduous a project,
>
>If what you want is something like PL/SQL, it might be both easier and
>better to retarget PostgreSQL's PL/pgSQL for SQLite opcodes, rather
>than starting from scratch with your own Oracle PL/SQL style language.
>(And PL/pgSQL's BSD license is compatible with SQLite's.)
>
>--
>Andrew Piskorski <[hidden email]>
>http://www.piskorski.com/

Don't know how much size has PL/SQL implementation in PostgreSQL, i
think use it twice if SQLite (200-300KB) grows-up to 500KB or more by
adding PostgreSQL code. I doubt that Postgre implementation is done
with lemon, which is the lalr(1) parser used by SQLite, so some
incompatibilities may/will occur, that means glue code or twice code
(lemon+pgsql pl/sql parser) and make work harder and non-maintenable
in future.

------------------------------------------------------------
Alien.org contacted...    waiting for Ripley


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Embedded Database Functions

John Stanton-3
The way I would envisage a PL/SQL type capability existing in Sqlite is
by adding the grammar to the existing parser and adding the code
generation to the existing code generator.  The existing VDBE engine
might need to be slightly extended.

Apparently a version of Sqlite which uses stored VDBE code already
exists so that is not an uncharted route.

Eduardo Morras wrote:

> At 17:00 24/12/2006, you wrote:
>
>> On Sun, Dec 24, 2006 at 09:35:01AM -0600, John Stanton wrote:
>>
>> > >There is even a starting grammar for you:
>> > >  http://www.antlr.org/grammar/1107752678378/PLSQLGrammar.g
>>
>> > A compiler for a subset of PL/SQL would not be too arduous a project,
>>
>> If what you want is something like PL/SQL, it might be both easier and
>> better to retarget PostgreSQL's PL/pgSQL for SQLite opcodes, rather
>> than starting from scratch with your own Oracle PL/SQL style language.
>> (And PL/pgSQL's BSD license is compatible with SQLite's.)
>>
>> --
>> Andrew Piskorski <[hidden email]>
>> http://www.piskorski.com/
>
>
> Don't know how much size has PL/SQL implementation in PostgreSQL, i
> think use it twice if SQLite (200-300KB) grows-up to 500KB or more by
> adding PostgreSQL code. I doubt that Postgre implementation is done with
> lemon, which is the lalr(1) parser used by SQLite, so some
> incompatibilities may/will occur, that means glue code or twice code
> (lemon+pgsql pl/sql parser) and make work harder and non-maintenable in
> future.
>
> ------------------------------------------------------------
> Alien.org contacted...    waiting for Ripley
>
> -----------------------------------------------------------------------------
>
> To unsubscribe, send email to [hidden email]
> -----------------------------------------------------------------------------
>
>


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------