Skip to content

Historic: Explorations of the original iesg tracker to datatracker Model

Robert Sparks edited this page May 1, 2023 · 1 revision

PageOutline(2)

Model

The model was partially generated from "python manage.py inspectdb", and then tweaked to add in relationships and appropriate helper functions.

A visualization of the model as of 2007-05-02 14:00PM Pacific is attached to this page and will [hopefully] be periodically regenerated: [/tools/ietfdb/attachment/wiki/Model/models.pdf?format=raw models.pdf]

Changes from existing database

Main changes needed are because:

  • django supports exactly one column for primary keys

Tables with multiple-column primary keys:

CREATE TABLE `ballots` (
  PRIMARY KEY  (`ballot_id`,`ad_id`)
CREATE TABLE `ballots_comment` (
  PRIMARY KEY  (`ballot_id`,`ad_id`)
CREATE TABLE `ballots_discuss` (
  PRIMARY KEY  (`ballot_id`,`ad_id`)
CREATE TABLE `iesg_history` (
  PRIMARY KEY  (`meeting_num`,`area_acronym_id`,`person_or_org_tag`)
CREATE TABLE `roll_call` (
  PRIMARY KEY  (`telechat_id`,`person_or_org_tag`)

Tables with no primary key:

CREATE TABLE `email_addresses` (
CREATE TABLE `id_submission_env` (
CREATE TABLE `meeting_agenda_count` (
CREATE TABLE `not_meeting_groups` (
CREATE TABLE `phone_numbers` (
CREATE TABLE `postal_addresses` (
CREATE TABLE `telechat_dates` (
CREATE TABLE `temp_admins` (

These can be handled with sql like

ALTER TABLE  `ballots` DROP PRIMARY KEY;
ALTER TABLE  `ballots` ADD  `id` INT NOT NULL AUTO_INCREMENT
 PRIMARY KEY FIRST;
ALTER TABLE  `ballots` ADD UNIQUE (`ballot_id` , `ad_id`);

ALTER TABLE  `ballots_comment` DROP PRIMARY KEY;
ALTER TABLE  `ballots_comment` ADD  `id` INT NOT NULL AUTO_INCREMENT
 PRIMARY KEY FIRST;
ALTER TABLE  `ballots_comment` ADD UNIQUE (`ballot_id` , `ad_id`);

ALTER TABLE  `ballots_discuss` DROP PRIMARY KEY;
ALTER TABLE  `ballots_discuss` ADD  `id` INT NOT NULL AUTO_INCREMENT
 PRIMARY KEY FIRST;
ALTER TABLE  `ballots_discuss` ADD UNIQUE (`ballot_id` , `ad_id`);

ALTER TABLE  `iesg_history` DROP PRIMARY KEY;
ALTER TABLE  `iesg_history` ADD  `id` INT NOT NULL AUTO_INCREMENT
 PRIMARY KEY FIRST;

Missing FK lookup values

  • django prefers FK lookup tables to be complete (you can use null=True, and it won't look up the related object automatically, but that doesn't really apply to all situations)
INSERT INTO announced_from VALUES (98, 'IETF Executive Director <[email protected]>', NULL);
INSERT INTO announced_to VALUES (9, 'Unknown', NULL);
INSERT INTO area_status VALUES (3, 'Unknown');

(Reading the code, the announced_from one is intentional, to allow access control of who is permitted to use this value.)

  • The database has many groups with area_director_id that point to a row in area_directors that has been deleted.

Schema Limitations

announcements table loses who the announcement was sent to if announced_to_id = 99

Attachments

Clone this wiki locally