This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
Subject: Re: DB independence in 2.10? From: Kevin Brannen <kevin.brannen@springbow.com> Date: Mon, 27 Nov 2000 17:59:27 +0000 Dan Mosedale wrote: > > kbrannen@gte.net (Kevin Brannen) writes: ... > > I took a few short-cuts: > > > > * I changed the bit fields to INT, meaning there can be only 32 of "whatever" > > just now. I liked the separate table idea by one of the Daves, but didn't > > want to go there just now; but I could. > > It does seem like the separate table is the way to go, long term. But > for the short term, why did you change from BIGINT to INT? This has > the problem that it will actually break existing installations that > use > 32 groups. Because there is no such thing as a BIGINT in ANSI SQL, at least not according to my book. It is my understanding that INT only *promises* 32 bits. I could test with NUMBER(8) and see if that continues to work [or maybe do CHAR(8)]. Note: I did leave all the Mysql types intact for Mysql, so those that have the requirement for more than 32 thingies, need only to stay on Mysql--at least until the separate table work is done. > > > * I changed the enum fields to varchar(20) and just store the string. Didn't > > feel like going to the trouble of normalizing those into separate tables. > > I'd be interested in hearing what the other folks who have done > similar porting work have done related to this issue. I wonder what > effect on performance this change will have, as well as what effect > normalizing would have. That's an interesting question. I strongly suspect that the specific answer is DB dependent. Overall, I'd expect it to be about equal (give or take a few milliseconds). I base that on the logic of: Just use the string versus change the input string to the internal INT representation and compare for validity sort of thing; all IMHO of course. :-) > > > * I had to create an if/then/else section at the top for DB > > dependent stuff to go into vars (for the data types), which drive > > the rest of the code. (For nicety sake, I left the Mysql version to > > be all the myriad of data types; but the other DBs would be only the > > standard ANSI SQL types.) > > MySQL actually does support a bunch of more standard types; see > <http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Other-vendor_column_types> Yes but... Let's say you want to allow a field to hold 100K of chars. What datattype do you use? Mysql: mediumtext Informix: text Oracle: clob others: ??? Or maybe 4M for an attachment? Mysql: longblob Informix: byte Oracle: longraw others: ??? That's what my table is for. As a side issue, I had to add several extra DB convenience functions, along the lines of SendSQL(). Because some of the data are "blobs", they must be insert'd with bind parameters. Once those convenience functions exist, the code is easily changed. ... > > I've proved a point to myself that it could be done. Should I go on or just > > toss the code and consider it an education on Bugzilla? > > I would love to see this get integrated into the 2.x Bugzilla tree. > 3.0 is still on the drawing board; coding has not yet started. So > we'll be living in a 2.x world for a while yet. OK. I see you have a "mozilla.org" address, so I guess I can assume official blessing... :-) When I get close to done, I'll file a "bug" to log the work against. > > > p.s. Doing this was instructive in that I found some SQL that is wrong (e.g. > > where Mysql didn't complain about a "not null" column without a default not > > being given a value in an insert; I think I found about 5-10 of these). > > If you can file a bug on me with patches for these, I'll work to get > it checked into the main tree. > > Dan > -- Will do. I'll diff my against the the original to get that info. ===