SQL Antipatterns - bugs database

-- set this up on web page so I could copy/paste
-- to my android...

-- create database bugs;
-- use bugs;

DROP TABLE IF EXISTS Accounts;
DROP TABLE IF EXISTS BugStatus;
DROP TABLE IF EXISTS Bugs;
DROP TABLE IF EXISTS Comments;
DROP TABLE IF EXISTS Screenshots;
DROP TABLE IF EXISTS Tags;
DROP TABLE IF EXISTS Products;
DROP TABLE IF EXISTS BugsProducts;

#START:create
CREATE TABLE Accounts (
  account_id        SERIAL PRIMARY KEY,
  account_name      VARCHAR(20),
  first_name        VARCHAR(20),
  last_name         VARCHAR(20),
  email             VARCHAR(100),
  password_hash     CHAR(64),
  portrait_image    BLOB,
  hourly_rate       NUMERIC(9,2)
);

CREATE TABLE BugStatus (
  status            VARCHAR(20) PRIMARY KEY
);

CREATE TABLE Bugs (
  bug_id            SERIAL PRIMARY KEY,
  date_reported     DATE NOT NULL,
  summary           VARCHAR(80),
  description       VARCHAR(1000),
  resolution        VARCHAR(1000),
  reported_by       BIGINT UNSIGNED NOT NULL,
  assigned_to       BIGINT UNSIGNED,
  verified_by       BIGINT UNSIGNED,
  status            VARCHAR(20) NOT NULL DEFAULT 'NEW',
  priority          VARCHAR(20),
  hours             NUMERIC(9,2),
  FOREIGN KEY (reported_by) REFERENCES Accounts(account_id),
  FOREIGN KEY (assigned_to) REFERENCES Accounts(account_id),
  FOREIGN KEY (verified_by) REFERENCES Accounts(account_id),
  FOREIGN KEY (status) REFERENCES BugStatus(status)
);

CREATE TABLE Comments (
  comment_id        SERIAL PRIMARY KEY,
  bug_id            BIGINT UNSIGNED NOT NULL,
  author            BIGINT UNSIGNED NOT NULL,
  comment_date      DATETIME NOT NULL,
  comment           TEXT NOT NULL,
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
  FOREIGN KEY (author) REFERENCES Accounts(account_id)
);

CREATE TABLE Screenshots (
  bug_id            BIGINT UNSIGNED NOT NULL,
  image_id          BIGINT UNSIGNED NOT NULL,
  screenshot_image  BLOB,
  caption           VARCHAR(100),
  PRIMARY KEY      (bug_id, image_id),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
);

CREATE TABLE Tags (
  bug_id            BIGINT UNSIGNED NOT NULL,
  tag               VARCHAR(20) NOT NULL,
  PRIMARY KEY      (bug_id, tag),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
);

CREATE TABLE Products (
  product_id        SERIAL PRIMARY KEY,
  product_name      VARCHAR(50)
);

CREATE TABLE BugsProducts(
  bug_id            BIGINT UNSIGNED NOT NULL,
  product_id        BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY      (bug_id, product_id),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);
#END:create

INSERT INTO Accounts () VALUES ();

INSERT INTO Products (product_id, product_name) VALUES
  (1, 'Open RoundFile'),
  (2, 'Visual TurboBuilder'),
  (3, 'ReConsider');

INSERT INTO Bugs (bug_id, summary) VALUES
  (1234, 'crash when I save'),
  (2345, 'increase performance'),
  (3456, 'screen goes blank'),
  (5678, 'unknown conflict between products');

INSERT INTO BugsProducts (bug_id, product_id) VALUES
  (1234, 1),
  (1234, 3),
  (3456, 2),
  (5678, 1),
  (5678, 3);

INSERT INTO Comments (comment_id, bug_id, comment) VALUES
  (6789, 1234, 'It crashes!'),
  (9876, 2345, 'Great idea!');

INSERT INTO Tags () VALUES ();

show_r.ksh

Handy Subversion script. Pass it a revision number and it shows the login comment ~ related
files.

 
xxx::/home/mbxxxxx/apps/datamodules/datamodules/trunk> . _CMD/show_r.ksh 114
------------------------------------------------------------------------
r114 | mbee | 2013-12-02 14:34:30 -0600 (Mon, 02 Dec 2013) | 1 line

null matrix id
------------------------------------------------------------------------ 

... 

    114 mbee             690 Dec 02 14:34 _CMD/process_null_matrix_ids.ksh
    114 mbee            9527 Dec 02 14:34 _CMD/pull_push_null_matrix_id_updates.ksh
    114 mbee            8553 Dec 02 14:34 _CMD/pull_rollback_of_matrix_id_updates.ksh
xxx::/home/mbxxxx/apps/datamodules/datamodules/trunk> cat _CMD/show_r.ksh
svn log -r $1
svn list -v -r $1 --depth=infinity|grep "$1 "

Copy_2_ro_folder.vbs

This copies a powerpivot model from it's source to a Read-Only target. The slicers and other pivot interactions still work the same -- but, users can't alter the sheet (directly).