[Devel] database tables

Klaus Darilion klaus.mailinglists at pernau.at
Mon Dec 11 09:43:12 CET 2006


one more:

for postgresql we have the auto increment defined as:

AUTO_INCREMENT="SERIAL PRIMARY KEY"

regards
klaus

Klaus Darilion wrote:
> 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