[Devel] database tables

[Mike Williams]

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=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