[Devel] database tables

[Juha Heinanen]

Mike Williams writes:

 > I think it would be a good idea to change all of the database tables created 
 > by OpenSER to having the primary key be an unsigned auto_incremented int 
 > named 'id'. The keys that are used now should become unique keys. 

mike,

i have not seen much use for auto-increment id keys in implementing an
openser management system.  if there is no really good use case, an
extra key just adds to table size.

 > 2. Consistency. Some tables are using there own id names, when it would be 
 > better to have just one standard one. Then, everyone would know that the 
 > column id was an autoincremented unique int id for that table. As of now, in 
 > some tables I find it hard to understand what the id names actually
 > mean. For instance, what does 'grp_id' mean in the table gw_grp? 
 > Is it a unique id, or 
 > is it refering to the id of the 'grp' table? I would have to look it up to 
 > find out. With 'id' there would be no ambiguity.

grp_id of gw_grp table is NOT an auto-increment unique key.  from README
file:

   Each gateway belongs to a gateway group either alone or among
   other gateways. All gateways in a group share the same
   priority.
   ...
   Table lcr contains prefix of user part of Request-URI, From
   URI, gateway group id, and priority.
   ...
   In addition to gw and lcr tables there is third table gw_grp
   that is used to associate names with gateway group ids.

 > 3. Greatly simplifies manual database work. Let's consider the lcr table:
 > 
 > CREATE TABLE lcr (
 >   prefix varchar(16) NOT NULL,
 >   from_uri varchar(128) DEFAULT NULL,
 >   grp_id INT UNSIGNED NOT NULL,
 >   priority TINYINT UNSIGNED NOT NULL,
 >   KEY (prefix),
 >   KEY (from_uri),
 >   KEY (grp_id)
 > ) $TABLE_TYPE;
 > 
 > As it is now, it would take a statement like this:
 > 
 > DELETE FROM lcr WHERE prefix='A', from_uri='B', grp_id='C', priority='D'; 
 > 
 > Just to delete one record. With a unique id, it becomes:
 > 
 > DELETE FROM lcr WHERE id=X; 

i don't consider this a big deal when the query is created automatically
by management system.

in summary, although i don't see any urgent need, i would not oppose
adding a an auto-increment key to tables that tend to have only a small
number of rows.  but if there are tables that can be big and that
currently don't have such a key, i would carefully consider if it really
needed.

-- juha