Looking for ejabberd docs?

To access the most up-to-date ejabberd documentation, please visit docs.ejabberd.im »

Database Design of ejabberd

Submitted by day on Fri, 2008-07-04 02:14

Dear all,

I read the database design of ejabberd on mysql:

 CREATE TABLE users ( 
     username varchar(250) PRIMARY KEY, 
     password text NOT NULL 
 ) CHARACTER SET utf8; 
 CREATE TABLE vcard_search ( 
     username varchar(250) NOT NULL, 
     lusername varchar(250) PRIMARY KEY, 
     fn text NOT NULL, 
     lfn varchar(250) NOT NULL, 
     family text NOT NULL, 
     lfamily varchar(250) NOT NULL, 
     given text NOT NULL, 
     lgiven varchar(250) NOT NULL, 
     middle text NOT NULL, 
     lmiddle varchar(250) NOT NULL, 
     nickname text NOT NULL, 
     lnickname varchar(250) NOT NULL, 
     bday text NOT NULL, 
     lbday varchar(250) NOT NULL, 
     ctry text NOT NULL, 
     lctry varchar(250) NOT NULL, 
     locality text NOT NULL, 
     llocality varchar(250) NOT NULL, 
     email text NOT NULL, 
     lemail varchar(250) NOT NULL, 
     orgname text NOT NULL, 
     lorgname varchar(250) NOT NULL, 
     orgunit text NOT NULL, 
     lorgunit varchar(250) NOT NULL 
 ) CHARACTER SET utf8;

I have 2 question I would like someone HELP me.
1. Do anyone know what is the "l" in "lusername" meant?
2. Will I have any problem if I remove the PRIMARY KEY flag in the "lusername"

If you know the answer, please HELP me.
Many thanks!

Best regards,
day

Lowercase

Submitted by mfoss on Tue, 2008-07-08 23:20.

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

Dear badlop, Thanks for your

Submitted by day on Wed, 2008-07-09 08:00.

Dear badlop,

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

Best regards,
day

Maybe it help somebody

Submitted by philipj on Wed, 2008-07-09 15:55.

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

Also... for lusername

Submitted by philipj on Wed, 2008-07-09 16:31.

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....