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 klausI 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? MikeHi 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=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 [email protected] http://openser.org/cgi-bin/mailman/listinfo/devel_______________________________________________ Devel mailing list [email protected] http://openser.org/cgi-bin/mailman/listinfo/devel_______________________________________________ Devel mailing list [email protected] http://openser.org/cgi-bin/mailman/listinfo/devel_______________________________________________ Devel mailing list [email protected] http://openser.org/cgi-bin/mailman/listinfo/devel_______________________________________________ Devel mailing list [email protected] http://openser.org/cgi-bin/mailman/listinfo/devel
-- Klaus Darilion nic.at _______________________________________________ Devel mailing list [email protected] http://openser.org/cgi-bin/mailman/listinfo/devel
