Spaces:
Sleeping
Sleeping
File size: 5,134 Bytes
6f1c297 | 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 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 | import { pool } from './client.js';
const migrations = [
{
name: '001_create_scores_table',
sql: `
CREATE TABLE IF NOT EXISTS scores (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
title VARCHAR(255),
source_url TEXT,
page_count INTEGER DEFAULT 0,
data JSONB DEFAULT '{}',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_scores_created_at ON scores(created_at DESC);
`,
},
{
name: '002_create_tasks_table',
sql: `
CREATE TYPE task_status AS ENUM ('pending', 'running', 'completed', 'failed');
CREATE TYPE task_type AS ENUM ('predict_page', 'predict_all', 'predict_custom');
CREATE TABLE IF NOT EXISTS tasks (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
score_id UUID REFERENCES scores(id) ON DELETE CASCADE,
type task_type NOT NULL,
status task_status DEFAULT 'pending',
progress INTEGER DEFAULT 0,
current_step VARCHAR(100),
result JSONB,
error TEXT,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
started_at TIMESTAMP WITH TIME ZONE,
completed_at TIMESTAMP WITH TIME ZONE
);
CREATE INDEX IF NOT EXISTS idx_tasks_score_id ON tasks(score_id);
CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(status);
`,
},
{
name: '003_create_pages_table',
sql: `
CREATE TABLE IF NOT EXISTS pages (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
score_id UUID REFERENCES scores(id) ON DELETE CASCADE,
page_index INTEGER NOT NULL,
image_path TEXT,
width INTEGER,
height INTEGER,
data JSONB DEFAULT '{}',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
UNIQUE(score_id, page_index)
);
CREATE INDEX IF NOT EXISTS idx_pages_score_id ON pages(score_id);
`,
},
{
name: '004_create_music_sets_table',
sql: `
CREATE TABLE IF NOT EXISTS music_sets (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(255),
type VARCHAR(20) DEFAULT 'stave',
content JSONB DEFAULT '{}',
status VARCHAR(50) DEFAULT 'draft',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_music_sets_updated_at ON music_sets(updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_music_sets_name ON music_sets(name);
`,
},
{
name: '005_create_tags_table',
sql: `
CREATE TABLE IF NOT EXISTS tags (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(100) NOT NULL UNIQUE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS music_set_tags (
music_set_id UUID REFERENCES music_sets(id) ON DELETE CASCADE,
tag_id UUID REFERENCES tags(id) ON DELETE CASCADE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
PRIMARY KEY (music_set_id, tag_id)
);
CREATE INDEX IF NOT EXISTS idx_music_set_tags_music_set_id ON music_set_tags(music_set_id);
CREATE INDEX IF NOT EXISTS idx_music_set_tags_tag_id ON music_set_tags(tag_id);
`,
},
{
name: '006_create_solution_cache_table',
sql: `
CREATE TABLE IF NOT EXISTS solution_cache (
name VARCHAR(64) PRIMARY KEY,
value JSONB NOT NULL,
priority DOUBLE PRECISION,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
`,
},
{
name: '007_create_issue_measures_table',
sql: `
CREATE TABLE IF NOT EXISTS issue_measures (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
score_id VARCHAR(255) NOT NULL,
measure_index INTEGER NOT NULL,
measure JSONB NOT NULL,
status INTEGER NOT NULL DEFAULT 1,
by_user BOOLEAN DEFAULT false,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_issue_measures_score_id ON issue_measures(score_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_issue_measures_score_measure
ON issue_measures(score_id, measure_index) WHERE status > 0;
`,
},
{
name: '008_add_annotator_to_issue_measures',
sql: `
ALTER TABLE issue_measures ADD COLUMN IF NOT EXISTS annotator VARCHAR(100);
`,
},
];
async function runMigrations() {
console.log('Running migrations...');
// Create migrations tracking table
await pool.query(`
CREATE TABLE IF NOT EXISTS migrations (
name VARCHAR(255) PRIMARY KEY,
executed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
)
`);
for (const migration of migrations) {
const { rows } = await pool.query('SELECT name FROM migrations WHERE name = $1', [migration.name]);
if (rows.length === 0) {
console.log(`Running migration: ${migration.name}`);
await pool.query(migration.sql);
await pool.query('INSERT INTO migrations (name) VALUES ($1)', [migration.name]);
console.log(`Completed: ${migration.name}`);
} else {
console.log(`Skipping: ${migration.name} (already executed)`);
}
}
console.log('All migrations completed.');
}
runMigrations()
.then(() => process.exit(0))
.catch((err) => {
console.error('Migration failed:', err);
process.exit(1);
});
|