[Devel] db schema status, questions

[Klaus Darilion]

Henning Westerholt wrote:
> Hello all,
> 
> during my work with the database schemas i've noticed and fixed some issues 
> with the current schema:
> 
> - addition of a version field to pdt table
Then the pdt module should also check the version number.

> - change uri: uri_user field length from 50 to 64 (standard user data length)
Is this a global #define or is it hardcoded in every table definition?

> - change sip_trace: callid and :status from 254 to 255, and sip_trace: 
>   traced_user from 128 to 255 (standard hf and uri length)
> - acc: sip_code from CHAR to VARCHAR changed (CHAR not available in the 
>   schema scripts atm)
> - grp:grp group name from 50 to 64 increased (std id length)
> - trusted:src_ip from 39 to 50 increased
> - speeddial: new_uri from 192 to 255 increased, lname and fname from 128 to 64  
>   decreased (std user name length)
> - usr_preferences: avp_val_len from 128 to 255 increased (to make this  
>   consistent to domainpolicy), :last_modified from TIMESTAMP to DATETIME 
>   changed (TIMESTAMP not available in schema scripts atm, DATETIME more safe 
>   in different mysql versions)
> - subscriber: lname, fname and email fields to 64 increased
> - presentity: domain from 124 to 128 increased

There is also mismatch between column types, maybe you can take a look 
at this too.

   Mysql:
presentity.body is a "text"
xcap_xml.xcap   is a "text"

   Postgresql:
presentity.body is a "bytea"
xcap_xml.xcap   is a "text"

Also reported at http://www.mail-archive.com/devel@openser.org/msg06554.html

> - xcap_xml: user from 66 to 64 decreased
> - pua:watcher_uri and pres_user increased from 128 to 255 (std. URI length)
> 
> In my opinion it's more resonable to have some standard length for datatypes, 
> what do you think about this changes?
sounds reasonable

> At the moment the IDs for the tables are either signed or unsigned. Should i 
> change this to only one type?
I think unsigned sounds better for an ID, but AFAIK postgresql does not 
support unsigned integer. The "serial" is some kind of unsigned integer.
http://www.postgresql.org/docs/7.4/interactive/datatype.html#DATATYPE-NUMERIC

regards
klaus