Forum OpenACS Q&A: Bug in bboard_q_and_a_categories

Collapse
1: Bug in bboard_q_and_a_categories
Posted by Roberto Mello on 05/03/00 06:09 PM
Why do we have bboard_q_and_a_categories defined as: 
create table bboard_q_and_a_categories (
        topic_id        varchar(100) not null references
bboard_topics, 
        category        varchar(200) not null
);
Needless to say, this breaks categorization because bboard_topics is defined as: 
create table bboard_topics (
        topic_id        integer not null primary key,
I was going to commit the change but thought of asking first so I was sure this wasn't done for a reason.
Collapse
2: Response to Bug in bboard_q_and_a_categories (response to 1)
Posted by Naveen Agnihotri on 05/04/00 06:39 AM
In closedACS, the definition is like: 
create table bboard_q_and_a_categories (
        topic_id        not null references bboard_topics,      
        category        varchar(200) not null
);

If  bboard_q_and_a_categories  is currently defined the way you describe, shouldn't the referential integrity of 7.0 have caught that?

Collapse
3: Response to Bug in bboard_q_and_a_categories (response to 1)
Posted by Roberto Mello on 05/04/00 02:56 PM
PostgreSQL does catch it. That's exactly why it breaks categorization. I just asked to make sure this wasn't done for a reason. Most likely it was a merging error or someone's fingers just slipped through "varchar(100)".
Collapse
4: Response to Bug in bboard_q_and_a_categories (response to 1)
Posted by Don Baccus on 05/04/00 02:58 PM
Yes, referential integrity should've caught it, if not, would you please tell me, Roberto?

As to why it slipped in, bulletin boards used to be referenced by the topic itself, i.e. the name of the forum.  Integer keys are considerably more efficient, so in 3.2 topic_id was introduced.  It looks to me that someone spaced when porting the datamodel, changing "topic" to "topic_id" and simply forgetting to change the type.

The new bboard module for 4.0 will also use integer keys for the messages.  If you look at site_wide_category_map, you'll see that the general categorization stuff uses a varchar key, simply to accomodate bboard's non-integer key.  All that will speed up when this can be changed to integer, too.

Collapse
5: Response to Bug in bboard_q_and_a_categories (response to 1)
Posted by Roberto Mello on 05/04/00 03:12 PM
Oh, you mean PostgreSQL should have caught it while loading the data model ? If that's what you have in mind, then it didn't catch it.

For testing, I created a db called foo and loaded a subset of bboard_topics and bboard_q_and_a_categories (with the varchar) and it loaded fine. I am running PostgreSQL beta 2 though, which is kind of old, so this may have been fixed, but it'd be worth to check for this in a newer release of PG. 

[nsadmin@fslc sql]$ psql -f foobar.sql foo
psql:foobar.sql:9: NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'bboard_topics_pkey' for table 'bboard_topics'
psql:foobar.sql:9: NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'bboard_topics_topic_key' for table 'bboard_topics'
CREATE
psql:foobar.sql:17: NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE

foo=# d bboard_topics
           Table "bboard_topics"
  Attribute  |     Type     |   Modifier
-------------+--------------+---------------
 topic_id    | integer      | not null
 topic       | varchar(200) | not null
 read_access | varchar(16)  | default 'any'
Indices: bboard_topics_pkey,
         bboard_topics_topic_key
Constraint: (((read_access = 'any'::"varchar") OR (read_access = 'public'::"varchar")) OR (read_access = 'group'::"varchar"))
foo=# d bboard_q_and_a_categories
  Table "bboard_q_and_a_categories"
 Attribute |     Type     | Modifier
-----------+--------------+----------
 topic_id  | varchar(100) | not null
 category  | varchar(200) | not null