[Devel] database tables

[Mike Williams]

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.

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