[Devel] database tables
Klaus Darilion
klaus.mailinglists at pernau.at
Tue Dec 12 10:11:01 CET 2006
Do you have CVS access to openser?
regards
klaus
Mike Williams wrote:
> It turns out that I had some time, so I finished up the postgresqldb.sh
> script. Unfortunately, I don't have a Postgres server to test with. Would
> someone mind testing the script? Also, what should I do with these now?
>
> Mike
>
> On Monday 11 December 2006 10:35, Mike Williams wrote:
>> I'll put this on my todo list. I've got final exams the rest of the week,
>> so I'll probably get to it around friday or saturday.
>>
>> Mike
>>
>> On Monday 11 December 2006 03:43, Klaus Darilion wrote:
>>> 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=160541
>>>>>>>> 0& 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
>> _______________________________________________
>> 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