308717 - Column name "PUBLIC" in SERIES table is an Oracle reserved word

Status: RESOLVED FIXED

(Bugzilla :: Database, enhancement)

Description

[Lance Larsh]

User-Agent:       Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.3) Gecko/20041119
Build Identifier: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.3) Gecko/20041119

The table SERIES has a boolean column named 'PUBLIC' which is a reserved word in
Oracle.  As a result, database creation fails on Oracle.

Although it is possible to have a column named after a reserved word, simply by
quoting all occurences of the column name in SQL.  However, errors would likely
keep reappearing in the future, since anyone not familiar with Oracle may not be
aware of the need to quote that column name.

Best approach seems to be to rename to the column to a safer name, such as
IS_PUBLIC.

Reproducible: Always

Steps to Reproduce:
(NOTE:  Testing obviously requires a database driver for the DB of interest, and
as of this moment the driver for Oracle isn't done yet since this bug blocks it.
 The steps below show how to test the code against any database for which a
driver is available.)
1. Run checksetup.pl on a clean installation.
2. Edit localconfig to set the db_* parameters to point to a fresh db with no
schema created yet.
3. Run checksetup.pl again (this time to create the schema).


Actual Results:  
Oracle returns the following error:
  DBD::Oracle::db do failed: ORA-00904: : invalid identifier (DBD ERROR: error
  possibly near <*> indicator at char 258 in 'CREATE TABLE series (
      series_id       integer NOT NULL PRIMARY KEY,
      creator integer NOT NULL,
      category        integer NOT NULL,
      subcategory     integer NOT NULL,
      name    varchar(64) NOT NULL,
      frequency       integer NOT NULL,
      last_viewed     date,
      query   varchar2(4000) NOT NULL,
      <*>public       integer DEFAULT 0 NOT NULL
  )')


Expected Results:  
The SERIES table should be created without error.

Comment 1

[Max Kanat-Alexander]

Yeah, we've known about this for a little while, as I recall, but it wasn't an
issue until we wanted to support Oracle. :-)

is_public sounds great.

Comment 2

[Lance Larsh]

Attachment: Patch renaming SERIES.PUBLIC to IS_PUBLIC

Comment 3

[Max Kanat-Alexander]

Comment on attachment 196680 [details] [diff] [review]
Patch renaming SERIES.PUBLIC to IS_PUBLIC

>+++ mozilla/webtools/bugzilla/Bugzilla/DB/Schema/Mysql.pm	19 Sep 2005 18:48:55 -0000
>@@ -67,7 +67,7 @@
>     group_group_map => {isbless => 1},
>     user_group_map => {isbless => 1, isderived => 1},
>     products       => {disallownew => 1},
>-    series         => {public => 1},
>+    series         => {is_public => 1},

  I know this is slightly confusing, but read the comment above this area. This
is a historical record, not an accurate representation of present time. So that
needs to stay "public." You can add "is_public" there, if you want to be nice,
as another entry there.

  Perhaps I should make the comment clearer...

Comment 4

[Lance Larsh]

Attachment: Patch without Bugzilla::DB::Schema::Mysql changes

Oops, sorry about that!  It says pretty clearly that that's only for <= 2.19.3,
so not sure how I missed it...	:)

Comment 5

[Max Kanat-Alexander]

Comment on attachment 196701 [details] [diff] [review]
Patch without Bugzilla::DB::Schema::Mysql changes

r=mkanat on inspection, but I don't know enough about New Charts. Gerv?

Comment 6

[Gervase Markham [:gerv]]

Comment on attachment 196701 [details] [diff] [review]
Patch without Bugzilla::DB::Schema::Mysql changes

Assuming he's caught all the instances, I see no problem here.

Gerv

Comment 7

[Max Kanat-Alexander]

Lance: In the future, if possible, please make -p0 patches, instead of -p3
patches. (That is, make them in the mozilla/webtools/bugzilla/ directory itself,
not at the cvs root.)

Thanks for the patch. :-)



Checking in checksetup.pl;
/cvsroot/mozilla/webtools/bugzilla/checksetup.pl,v  <--  checksetup.pl
new revision: 1.437; previous revision: 1.436
done
Checking in Bugzilla/Series.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/Series.pm,v  <--  Series.pm
new revision: 1.11; previous revision: 1.10
done
Checking in Bugzilla/DB/Schema.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Schema.pm,v  <--  Schema.pm
new revision: 1.38; previous revision: 1.37
done