On 3/25/07, Florent Daigni?re (NextGen$) <nextgens at freenetproject.org> wrote:
> * 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.
I don't think its messy. isvalid makes the design clear and is cheap
and works even if no invalidreason is set. VARCHAR is needed to allow
variable size subjects and owners. msgdatetime is a BIGINT because
this is a long! An int is not enough. Recipient is the recipient
identity of an encrypted message (one of yours). from/signature is not
externalized for performance reasons (I tested both implementations).
And the signature is kept in preparation of new functionality, like
sending others messages with their signature within an message.
But hey, this is not the problem :) I think an relational DB is
great, I have no performance problems and no DB design problems at
all!
My problem is that the McKoi DB seems to cause problems when the JVM
running Frost crashes. Some users report corrupted databases. Thats
why I look for alternatives! I would love to stay on a RDBMS instead
to use an ODBMS, but I found no more open source RDBMS in pure java.
Thanks for the hints, I now will look at derby and jakarta. And I will
investigate if an ODBMS is an alternative.
>
> > "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-----
> > >
> > >
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFGBudcU/Z/dHFfxtcRArUTAJ0WvZTGGvDTv2vJ2NyMErV6J71L5ACfW8p/
> SBC01ftbsG1ZdeTn5b23o5k=
> =mKjE
> -----END PGP SIGNATURE-----
>
>
