[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