| CREATE TABLE IF NOT EXISTS `YOUR_PROJECT_ID.YOUR_DATASET.tools` ( | |
| tool_id STRING NOT NULL, | |
| slug STRING NOT NULL, | |
| name STRING NOT NULL, | |
| summary STRING NOT NULL, | |
| category STRING NOT NULL, | |
| repo_url STRING NOT NULL, | |
| -- Lowercase owner/repo, used to reject duplicate submissions (not enforced by BigQuery). | |
| repo_key STRING, | |
| website_url STRING, | |
| submitted_by STRING NOT NULL, | |
| -- pending | approved | rejected. New submissions are pending until a moderator approves them. | |
| status STRING, | |
| created_at TIMESTAMP NOT NULL | |
| ) | |
| PARTITION BY DATE(created_at) | |
| CLUSTER BY category, slug; | |
| CREATE TABLE IF NOT EXISTS `YOUR_PROJECT_ID.YOUR_DATASET.tool_ratings` ( | |
| tool_id STRING NOT NULL, | |
| voter_id STRING NOT NULL, | |
| rating INT64 NOT NULL, | |
| created_at TIMESTAMP NOT NULL, | |
| updated_at TIMESTAMP NOT NULL | |
| ) | |
| PARTITION BY DATE(created_at) | |
| CLUSTER BY tool_id, voter_id; | |
| -- --------------------------------------------------------------------------- | |
| -- Migration for an existing deployment (run once, BEFORE deploying this version). | |
| -- BigQuery cannot add NOT NULL columns, so both are nullable; the app treats a | |
| -- NULL status as 'approved' until the backfill has run. | |
| -- --------------------------------------------------------------------------- | |
| -- ALTER TABLE `YOUR_PROJECT_ID.YOUR_DATASET.tools` ADD COLUMN IF NOT EXISTS repo_key STRING; | |
| -- ALTER TABLE `YOUR_PROJECT_ID.YOUR_DATASET.tools` ADD COLUMN IF NOT EXISTS status STRING; | |
| -- UPDATE `YOUR_PROJECT_ID.YOUR_DATASET.tools` | |
| -- SET | |
| -- status = COALESCE(status, 'approved'), | |
| -- repo_key = LOWER(REGEXP_EXTRACT(repo_url, r'^https://github\.com/([^/]+/[^/]+?)(?:\.git)?/?$')) | |
| -- WHERE TRUE; | |