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&group_ 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
