54 lines
2.1 KiB
JavaScript
54 lines
2.1 KiB
JavaScript
|
/**
|
||
|
|
* Create tables for Scryfall Tagger community tags (oracle + art).
|
||
|
|
* Tags join to cards via oracle_id (oracle tags) or illustration_id (art tags).
|
||
|
|
*/
|
||
|
|
export const shorthands = undefined;
|
||
|
|
|
||
|
|
export const up = (pgm) => {
|
||
|
|
pgm.createTable('tags', {
|
||
|
|
id: { type: 'uuid', primaryKey: true },
|
||
|
|
slug: { type: 'varchar(255)', notNull: true },
|
||
|
|
label: { type: 'varchar(255)', notNull: true },
|
||
|
|
type: { type: 'varchar(20)', notNull: true, comment: 'oracle or illustration' },
|
||
|
|
description: { type: 'text' },
|
||
|
|
parent_ids: { type: 'jsonb' },
|
||
|
|
child_ids: { type: 'jsonb' },
|
||
|
|
aliases: { type: 'jsonb' },
|
||
|
|
created_at: { type: 'timestamp', default: pgm.func('CURRENT_TIMESTAMP') },
|
||
|
|
updated_at: { type: 'timestamp', default: pgm.func('CURRENT_TIMESTAMP') },
|
||
|
|
});
|
||
|
|
|
||
|
|
pgm.createIndex('tags', 'type');
|
||
|
|
pgm.createIndex('tags', 'slug');
|
||
|
|
pgm.createIndex('tags', 'label');
|
||
|
|
|
||
|
|
pgm.createTable('card_tags', {
|
||
|
|
id: { type: 'serial', primaryKey: true },
|
||
|
|
tag_id: { type: 'uuid', notNull: true, references: 'tags(id)', onDelete: 'CASCADE' },
|
||
|
|
card_id: { type: 'integer', references: 'cards(id)', onDelete: 'CASCADE' },
|
||
|
|
oracle_id: { type: 'varchar(36)', comment: 'For oracle tags — matches cards.oracle_id' },
|
||
|
|
illustration_id: { type: 'varchar(36)', comment: 'For art tags — matches cards.illustration_id' },
|
||
|
|
weight: { type: 'varchar(20)', default: 'median' },
|
||
|
|
annotation: { type: 'text' },
|
||
|
|
created_at: { type: 'timestamp', default: pgm.func('CURRENT_TIMESTAMP') },
|
||
|
|
});
|
||
|
|
|
||
|
|
pgm.createIndex('card_tags', 'tag_id');
|
||
|
|
pgm.createIndex('card_tags', 'card_id');
|
||
|
|
pgm.createIndex('card_tags', 'oracle_id');
|
||
|
|
pgm.createIndex('card_tags', 'illustration_id');
|
||
|
|
pgm.addConstraint('card_tags', 'card_tags_unique_tag_oracle', {
|
||
|
|
unique: ['tag_id', 'oracle_id'],
|
||
|
|
where: 'oracle_id IS NOT NULL',
|
||
|
|
});
|
||
|
|
pgm.addConstraint('card_tags', 'card_tags_unique_tag_illustration', {
|
||
|
|
unique: ['tag_id', 'illustration_id'],
|
||
|
|
where: 'illustration_id IS NOT NULL',
|
||
|
|
});
|
||
|
|
};
|
||
|
|
|
||
|
|
export const down = (pgm) => {
|
||
|
|
pgm.dropTable('card_tags');
|
||
|
|
pgm.dropTable('tags');
|
||
|
|
};
|