[Devel] database tables
Mike Williams
mike at mikebwilliams.com
Mon Dec 11 22:50:39 CET 2006
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 <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=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 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
>
> _______________________________________________
> Devel mailing list
> Devel at openser.org
> http://openser.org/cgi-bin/mailman/listinfo/devel
More information about the Devel
mailing list