[Devel] database tables

Klaus Darilion klaus.mailinglists at pernau.at
Mon Dec 11 09:22:49 CET 2006


Mike Williams wrote:
> On Friday 08 December 2006 04:34, Bogdan-Andrei Iancu wrote:
> 
> Ok, I've submitted the request. Also, I've made modifications to the 
> mysqldb.sh script, and things seem to be working alright.

Hi!

please always update postgresql.sh too (if it is a common problem).

regards
klaus

> 
> I basically just went through and made sure each table had an id column. I 
> incremented the version by 1 for each table that I had to add one to or 
> modify an existing one. I did not do anything to the serweb tables.
> 
> For table gw_grp I changed grp_id to id, is that correct? For silo, I changed 
> mid to id, same question.
> 
> What do you want me to do with the script?
> 
> Mike
> 
>> Hi Mike,
>>
>> please do submit a request. I have it on my to-do list....but is a paper
>> list...volatile :).
>>
>> yes, you are right - there will be no changes in the openser code. maybe
>> to see if thinks can be optimized by using the ids to reduce the
>> complexity of some queries (but this will follow).
>>
>> regards,
>> bogdan
>>
>> Mike Williams wrote:
>>> All,
>>>
>>> Should I submit a feature request then? I'm willing to do some work. The
>>> good thing about this kind of upgrade is that it doesn't really affect
>>> the rest of the table data at all, and I would guess it doesn't actually
>>> affect the code of openser either (Besides the database creation
>>> scripts).
>>>
>>> Mike
>>>
>>> On Wednesday 06 December 2006 04:32, Bogdan-Andrei Iancu wrote:
>>>> Hi everybody,
>>>>
>>>> couple of ideas from my side on this topic:
>>>>
>>>> 1) I'm not a DB expert, so most of the ideas are based on second hand
>>>> information :)
>>>>
>>>> 2) RDBMS theory (or concepts) suggests that every table should have
>>>> field ID as unique number getting from auto increment sequence (state
>>>> by  Khalukhin Alex <khalukhin at gmail.com>) when dealling with a mysql bug
>>>> related to the primary key size (see
>>>> https://sourceforge.net/tracker/?func=detail&atid=743020&aid=1605410&grou
>>>> p_ id=139143)
>>>>
>>>> 3) I tent to agree with Mike - when is about writing interfaces or
>>>> applications for provisioning the DB, having a one column primary key
>>>> helps a lot - especially in correlating different tables, cross
>>>> references, easy identification of records, etc
>>>>
>>>> 4) it is possible to help also inside openser - if you have an operation
>>>> involving 2 queries (like a select and delete), a smaller amount of
>>>> information is required to be stored (from select) in order to trigger
>>>> the delete.
>>>>
>>>> 5) I had some time ago a discussion with a senior mysql consultant and
>>>> he strongly advised to use auto increment ints as primary keys. I do not
>>>> remember the arguments behind (how the tables is hashed, how the hash is
>>>> balanced, how efficient data is locate, etc), as , again, I'm not to
>>>> much in DB stuff, but I recall the conclusion.
>>>>
>>>> 6) I already started changing some tables to have this kind of PK -
>>>> acc,missed_call table.
>>>>
>>>> any other input (as technical arguments) is welcomed.
>>>>
>>>> regards,
>>>> bogdan
>>>>
>>>> Juha Heinanen wrote:
>>>>> 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
>>>>>
>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> Devel mailing list
>>>>> Devel at openser.org
>>>>> http://openser.org/cgi-bin/mailman/listinfo/devel
>>> _______________________________________________
>>> Devel mailing list
>>> Devel at openser.org
>>> http://openser.org/cgi-bin/mailman/listinfo/devel
> 
> _______________________________________________
> Devel mailing list
> Devel at openser.org
> http://openser.org/cgi-bin/mailman/listinfo/devel


-- 
Klaus Darilion
nic.at




More information about the Devel mailing list