| /* |
| Copyright 2020 The Chromium OS Authors. All rights reserved. |
| Use of this source code is governed by a BSD-style license that can be |
| found in the LICENSE file. |
| |
| Module containing script to initialize database table schemas. |
| */ |
| |
| /* |
| This script should ONLY be ran when the CloudSQL database is being created. |
| This initializes the schema tables and columns that are stored in cloudsql. |
| |
| To run this script use the following cmd: |
| `mysql -u linux_patches_robot -p --host 127.0.0.1 -p linuxdb < initialize_sql_tables.sql` |
| */ |
| |
| |
| /* linux_upstream table stores metadata about linux upstream commits */ |
| CREATE TABLE IF NOT EXISTS linux_upstream ( |
| sha VARCHAR(40), |
| description TEXT NOT NULL, |
| patch_id CHAR(40) NOT NULL, |
| PRIMARY KEY (sha) |
| ); |
| CREATE INDEX patch_id ON linux_upstream (patch_id); |
| |
| |
| /* upstream_fixes table stores metadata about bugfix patches in linux upstream */ |
| CREATE TABLE IF NOT EXISTS upstream_fixes ( |
| upstream_sha VARCHAR(40), |
| fixedby_upstream_sha VARCHAR(40), |
| FOREIGN KEY (upstream_sha) REFERENCES linux_upstream(sha), |
| FOREIGN KEY (fixedby_upstream_sha) REFERENCES linux_upstream(sha), |
| PRIMARY KEY (upstream_sha, fixedby_upstream_sha) |
| ); |
| CREATE INDEX upstream_sha ON upstream_fixes (upstream_sha); |
| |
| |
| /* linux_stable table stores metadata about linux stable commits */ |
| CREATE TABLE IF NOT EXISTS linux_stable ( |
| sha VARCHAR(40), |
| branch VARCHAR(5) NOT NULL, |
| upstream_sha VARCHAR(40) NOT NULL, |
| patch_id CHAR(40) NOT NULL, |
| description TEXT NOT NULL, |
| FOREIGN KEY (upstream_sha) REFERENCES linux_upstream(sha), |
| PRIMARY KEY (sha) |
| ); |
| CREATE INDEX patch_id ON linux_stable (patch_id); |
| |
| |
| /* |
| linux_chrome table stores metadata about linux chrome commits |
| |
| Cannot put foreign key on upstream_sha since it may contain SHA's from |
| maintainer trees which haven't been merged into upstream yet. |
| */ |
| CREATE TABLE IF NOT EXISTS linux_chrome ( |
| sha VARCHAR(40), |
| branch VARCHAR(5) NOT NULL, |
| upstream_sha VARCHAR(40), |
| patch_id CHAR(40) NOT NULL, |
| description TEXT NOT NULL, |
| PRIMARY KEY (sha) |
| ); |
| CREATE INDEX upstream_sha ON linux_chrome (upstream_sha); |
| CREATE INDEX patch_id ON linux_chrome (patch_id); |
| |
| |
| /* |
| previous_fetch table stores data about the last parsed commit on each repo branch |
| |
| Possibility for date to see history of latest fetches. |
| */ |
| CREATE TABLE IF NOT EXISTS previous_fetch ( |
| linux ENUM('linux_stable', 'linux_chrome', 'linux_upstream'), |
| branch VARCHAR(20), |
| sha_tip VARCHAR(40) NOT NULL, |
| PRIMARY KEY (linux, branch) |
| ); |
| |
| |
| /* stable_fixes table stores metadata about missing patches in linux_stable */ |
| CREATE TABLE IF NOT EXISTS stable_fixes ( |
| kernel_sha VARCHAR(40), /*stable sha*/ |
| fixedby_upstream_sha VARCHAR(40), |
| branch VARCHAR(5) NOT NULL, |
| entry_time DATETIME NOT NULL, |
| close_time DATETIME, |
| fix_change_id CHAR(41), |
| initial_status ENUM('OPEN', 'MERGED', 'CONFLICT') NOT NULL, |
| status ENUM('OPEN', 'MERGED', 'ABANDONED', 'CONFLICT') NOT NULL, |
| reason VARCHAR(120), |
| FOREIGN KEY (kernel_sha) REFERENCES linux_stable(sha), |
| FOREIGN KEY (fixedby_upstream_sha) REFERENCES linux_upstream(sha), |
| PRIMARY KEY (kernel_sha, fixedby_upstream_sha) |
| ); |
| |
| |
| /* chrome_fixes table stores metadata about missing patches in linux_chrome */ |
| CREATE TABLE IF NOT EXISTS chrome_fixes ( |
| kernel_sha VARCHAR(40), /*chrome sha*/ |
| fixedby_upstream_sha VARCHAR(40), |
| branch VARCHAR(5) NOT NULL, |
| entry_time DATETIME NOT NULL, |
| close_time DATETIME, |
| fix_change_id CHAR(41), |
| initial_status ENUM('OPEN', 'MERGED', 'CONFLICT') NOT NULL, |
| status ENUM('OPEN', 'MERGED', 'ABANDONED', 'CONFLICT') NOT NULL, |
| reason VARCHAR(120), |
| FOREIGN KEY (kernel_sha) REFERENCES linux_chrome(sha), |
| FOREIGN KEY (fixedby_upstream_sha) REFERENCES linux_upstream(sha), |
| PRIMARY KEY (kernel_sha, fixedby_upstream_sha) |
| ); |