728203 - Create bugscache database table for tbpl

Status: RESOLVED FIXED

(Data & BI Services Team :: DB: MySQL, task)

Description

[Arpad Borsos [:Swatinem]]

Bug 718632 adds a real bug cache to tbpl to massively improve creating annotated summaries.

Please create the following table in staging and production for that:

CREATE TABLE IF NOT EXISTS `bugscache` (
  `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
  `filename` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `json` text COLLATE utf8_unicode_ci NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE INDEX bugscache_filename_idx (filename)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Comment 1

[Sheeri Cabral [:sheeri]]

Any reason that the id is not an unsigned int?  With an auto-increment value, it's never going to be negative, so might as well use INT UNSIGNED instead of just integer.

Comment 2

[Arpad Borsos [:Swatinem]]

(In reply to Sheeri Cabral [:sheeri] from comment #1)
> Any reason that the id is not an unsigned int?  With an auto-increment
> value, it's never going to be negative, so might as well use INT UNSIGNED
> instead of just integer.

Out of convention because we use the same for all other id fields. I usually use unsigned, must have slipped past me when we discussed the initial db schema

Comment 3

[Sheeri Cabral [:sheeri]]

OK, so would you like me to make this table, but with an INT UNSIGNED, then?

Comment 4

[Arpad Borsos [:Swatinem]]

However you wish ;)

Comment 5

[Sheeri Cabral [:sheeri]]

Sorry this took so long, with the holiday weekend and all:

Done on stage:

mysql> use tbpl_allizom_org
Database changed
mysql> CREATE TABLE IF NOT EXISTS `bugscache` (
    ->   `id` INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->   `filename` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    ->   `json` text COLLATE utf8_unicode_ci NOT NULL,
    ->   `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ->   UNIQUE INDEX bugscache_filename_idx (filename)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.09 sec)

Done on production:
mysql> use tbpl_mozilla_org
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE IF NOT EXISTS `bugscache` (
    ->   `id` INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->   `filename` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    ->   `json` text COLLATE utf8_unicode_ci NOT NULL,
    ->   `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ->   UNIQUE INDEX bugscache_filename_idx (filename)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.18 sec)