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 > > > 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 <[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 > > >>>>>> 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 > > _______________________________________________ > 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
