File size: 1,654 Bytes
f64a3bf 416bdd1 f64a3bf 416bdd1 f64a3bf 416bdd1 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | 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;
|