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 <[EMAIL PROTECTED]>) 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@openser.org > >>>http://openser.org/cgi-bin/mailman/listinfo/devel > > > >_______________________________________________ > >Devel mailing list > >Devel@openser.org > >http://openser.org/cgi-bin/mailman/listinfo/devel _______________________________________________ Devel mailing list Devel@openser.org http://openser.org/cgi-bin/mailman/listinfo/devel
