* bbackde at googlemail.com <bbackde at googlemail.com> [2007-03-25 22:22:45]:
> 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:
>
You can't have good performances with that because you are using
VARCHARs... Btw, what's the point of having both isvalid and
invalidreason ? can't you assume the boolean is false if the varchar is
NULL ? msgdatetime ought to be stored on a shorter field, what's
recipient ? can't "from" "signatures" and other fields like that be
externalized ? btw, why do you keep the signature if you update the
field signaturestatus ?
Your database template seems messy to me. I think you should consider
optimizing it before considering switching to an other provider.
> "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.
Hmm, how often is that thingy called ? can't you store/increase a
counter each time you insert a new message in the DB insteed of
recomputing every thing ?
I hope this helps.
NextGen$
>
> >
> >>
> >> 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-----
> >
> >
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: Digital signature
URL:
<https://emu.freenetproject.org/pipermail/devl/attachments/20070325/e397781e/attachment.pgp>
