ejabberd - Comments for "Database Design of ejabberd"

Also... for lusername

philipj — Wed, 09 Jul 2008 16:31:57 +0000

Maybe I again missed the point... but for me it looks quite strange

family text NOT NULL,
lfamily varchar(250) NOT NULL

So, I wonder, If lfamily is just same, as family, but in lowercase... why they have a different types (varchar upto 250, and text)?

Text types can be very big (much larger than 250 chars), and have some differencies against varchars...

Also... for me is strange, why there is necessity in "same field, but in lowercase"... ?

What is need in "SELECT lusername", if we can do just "SELECT LOWER(username)"

AFAIK, "LOWER" is even SQL-standard string function....

Maybe it help somebody

philipj — Wed, 09 Jul 2008 15:55:34 +0000

As far as I know, Primary Key on Mysql on char field actually do following:

1. If there is a record, for example... "Philip" :) You can't store same value in that field at another record, or change (UPDATE) another record to "Philip". Database will show an error. It's usefull if You want to be absolutely shure, that records (in that column) are unique on DB-level.
There is no way to enter to different records two similar values at Primary Key field.

Some info -

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key that matches an existing row.

A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). A table can have only one PRIMARY KEY.

If a PRIMARY KEY or UNIQUE index consists of only one column that has an integer type, you can also refer to the column as _rowid in SELECT statements

2. If You use statements like

SELECT f1, f2 FROM table1 WHERE prikeyfield="Philip"
SELECT f1, f2 FROM table1 WHERE prikeyfield="Phil%"
SELECT f1, f2 FROM table1 INNER JOIN table2 ON prikeyfield=table2keyfield

It will be MUCH faster if prikeyfield declared as Primary Key.

So... If these two reasons is important for You, You shold use it... if no - You can omit declare Primary Key... AFAIK

Dear badlop, Thanks for your

day — Wed, 09 Jul 2008 08:00:04 +0000

Dear badlop,

Thanks for your reply.
Let me see see if it works.

Best regards,
day

Lowercase

mfoss — Tue, 08 Jul 2008 23:20:19 +0000

day wrote:

1. Do anyone know what is the "l" in "lusername" meant?

Looking at the source code, lfamily is the lowercase version of family. So, if family = "MtCan", then i bet lfamily = "mtcan". The same applies to all the other L+variable.

day wrote:

2. Will I have any problem if I remove the PRIMARY KEY flag in the "lusername"

I think that was added for some good reason. If there weren't a reason, it wouldn't be there, right? You can remove and try, but later don't complain if something breaks :P