On 3/25/07, Florent Daigni?re (NextGen$) <nextgens at freenetproject.org> wrote:
> * bbackde at googlemail.com <bbackde at googlemail.com> [2007-03-25 21:01:06]:
>
> > I know that Berkeley is'nt relational. But according to some
> > informations I read in the berkeley FAQ you could implement the same
> > behavior by implementing some of the scan stuff by yourself...
>
> I really do think that frost should use a relational database.
> How are you currently storing messages ?
>
> What I would have done is a table where you store:
>
> idMsg | idSender | idBoard | subject | idPrevMsg | content | tstamp - if
> needed | isNew | isStared | is Marked
>
> an other table for senders :
>
> idSender | nick | key | whatever
>
> an other table for boards :
>
> idBoard | name | pubKey | secKey
>
> and that's all (assuming you drop filetransfert capabilities)...
>
> With that schema and a relationnal database, thread reconstruction ought
> to be *really* faster than your current code... And you would be able to
> do nice stuffs like "Filter new Messages" easily
Its more complex in the real world. We have a table for incoming
messages, and for sent, unsent and archived messages. And external
tables for the message content (performance!).
The DDL for the message table looks like this:
"CREATE TABLE IF NOT EXISTS "+getMessageTableName()+" ("+
"primkey BIGINT NOT NULL,"+
"messageid VARCHAR,"+
"inreplyto VARCHAR,"+
"isvalid BOOLEAN,"+
"invalidreason VARCHAR,"+
"msgdatetime BIGINT NOT NULL,"+
"msgindex INT NOT NULL,"+
"board INT NOT NULL,"+
"fromname VARCHAR,"+
"subject VARCHAR,"+
"recipient VARCHAR,"+
"signature VARCHAR,"+
"signaturestatus INT,"+
"publickey VARCHAR,"+
"isdeleted BOOLEAN,"+
"isnew BOOLEAN,"+
"isreplied BOOLEAN,"+
"isjunk BOOLEAN,"+
"isflagged BOOLEAN,"+
"isstarred BOOLEAN,"+
"hasfileattachment BOOLEAN,"+
"hasboardattachment BOOLEAN,"+
"idlinepos INT,"+
"idlinelen INT,"+
"CONSTRAINT "+getPrimKeyConstraintName()+" PRIMARY KEY (primkey),"+
"CONSTRAINT "+getUniqueMsgIdConstraintName()+"
UNIQUE(messageid),"+ // multiple null allowed
getBoardConstraint()+ // only for messages , not for sent messages
getUniqueMsgConstraint()+ // only for messages and sent messages
")";
The BOARDS, IDENTITIES and OWN_IDENTITIES table look nearly similar to
what you wrote.
>
> > Before I decided to use McKoi I also tried hsqldb (used by thaw) but
> > it was much slower than McKoi, especially with COUNT queries and some
> > other queries often used by Frost. According to the hsqldb forums they
> > know about this issue, but there is still no solution.
>
> May I ask why do you need count queries for ?
e.g. for the statistics dialog. But hsqldb was slow in different areas
too, currently I don't remember what areas...but all tests pointed to
McKoi.
>
> >
> > I didn't found any other relational java database that runs in the
> > same JVM as frost. Hence I came to the point to think about to use
> > berkeley and to implement the 'relational' part by myself. I need no
> > joins so I think this could be possible. What I need are queries that
> > have more than one condition (AND/OR).
>
> on BDB the basic operations are like a hashtable:
> push(key, data)
> pop(key)
> isEmpty(key)
>
> Then there might be willcards on keys, but that's all.
The FAQ entry made me believe that I could implement the SQL-like
behaviour by myself. Could'nt I implement the records scans over
indices? I think this would be it.
>
> >
> > On 3/25/07, Florent Daigni?re (NextGen$) <nextgens at freenetproject.org>
> > wrote:
> > >* bbackde at googlemail.com <bbackde at googlemail.com> [2007-03-25
> > >19:41:27]:
> > >
> > >> I currently check if the berkeley db could be a replacement for McKoi
> > >> db which is used by Frost. I'm not too happy with McKoi.
> > >>
> > >> But I still don't get the concept of berkeley db. I need queries like:
> > >> give me the messages (aka. db keys) of all messages that are:
> > >> - new (isnew=true)
> > >> - valid (isvalid=true)
> > >> - not older than X days (msgdate >= oldestDate)
> > >>
> > >> berkeley seems to access all stored object by key only (?). Do I have
> > >> to implement the 'table scan' by myself? How can I learn about this,
> > >> where are more complex examples (transition from sql to berkeley db)?
> > >>
> > >
> > >BDB isn't a relational database.
> > >
> > >> If someone can help and assist me that would be such great :)
> > >
> > >You probably don't want to use BDB but a relational one. Have you ever
> > >considered using the same as thaw ?
> > >
> > >NextGen$
> > >
> > >-----BEGIN PGP SIGNATURE-----
> > >Version: GnuPG v1.4.6 (GNU/Linux)
> > >
> > >iD8DBQFGBsVFU/Z/dHFfxtcRAi1cAJ9Cd9c1Npy4drDb3xDcChoix91xSgCfS91e
> > >ahgU4sunO1/1XFFrTSJEPWQ=
> > >=Jo9u
> > >-----END PGP SIGNATURE-----
> > >
> > >_______________________________________________
> > >Devl mailing list
> > >Devl at freenetproject.org
> > >http://emu.freenetproject.org/cgi-bin/mailman/listinfo/devl
> > >
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFGBs/eU/Z/dHFfxtcRAuO2AJ42G3YPtYbTBuGYfhk0T/tmwzL6GACgoyNO
> S0Sp+acdi6KDAZLA9HAEji4=
> =ktUb
> -----END PGP SIGNATURE-----
>
>
