2226 lines
60 KiB
JavaScript
Executable File
2226 lines
60 KiB
JavaScript
Executable File
const config = require('config');
|
|
|
|
exports.up = (knex) => Promise.resolve()
|
|
.then(() => knex.schema.createTable('countries', (table) => {
|
|
table.text('alpha2', 2)
|
|
.unique()
|
|
.primary();
|
|
|
|
table.text('alpha3', 3)
|
|
.unique();
|
|
|
|
table.text('name')
|
|
.notNullable();
|
|
|
|
table.text('alias');
|
|
|
|
table.integer('code', 3);
|
|
table.text('nationality');
|
|
|
|
table.integer('priority', 2)
|
|
.defaultTo(0);
|
|
}))
|
|
.then(() => knex.schema.createTable('entities_types', (table) => {
|
|
table.text('type')
|
|
.primary();
|
|
}))
|
|
.then(() => { // eslint-disable-line arrow-body-style
|
|
// allow vim fold
|
|
return knex('entities_types').insert([
|
|
{ type: 'network' },
|
|
{ type: 'channel' },
|
|
{ type: 'studio' },
|
|
{ type: 'info' },
|
|
]);
|
|
})
|
|
.then(() => knex.schema.createTable('entities', (table) => {
|
|
table.increments('id', 12);
|
|
|
|
table.integer('parent_id', 12)
|
|
.references('id')
|
|
.inTable('entities')
|
|
.index();
|
|
|
|
table.text('name');
|
|
table.text('slug', 32);
|
|
|
|
table.text('type')
|
|
.notNullable()
|
|
.references('type')
|
|
.inTable('entities_types')
|
|
.defaultTo('channel');
|
|
|
|
table.unique(['slug', 'type']);
|
|
|
|
table.specificType('alias', 'text[]');
|
|
|
|
table.text('url');
|
|
table.text('description');
|
|
table.json('parameters');
|
|
|
|
table.integer('priority', 3)
|
|
.defaultTo(0);
|
|
|
|
table.boolean('independent')
|
|
.defaultTo(false);
|
|
|
|
table.boolean('showcased');
|
|
table.boolean('visible')
|
|
.defaultTo(true);
|
|
|
|
table.boolean('has_logo')
|
|
.defaultTo(true);
|
|
|
|
table.datetime('created_at')
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.schema.createTable('media', (table) => {
|
|
table.text('id', 21)
|
|
.primary();
|
|
|
|
table.text('path');
|
|
table.text('thumbnail');
|
|
table.text('lazy');
|
|
table.integer('index');
|
|
table.text('mime');
|
|
|
|
table.boolean('is_s3')
|
|
.defaultTo(false);
|
|
|
|
table.text('hash');
|
|
|
|
table.bigInteger('size', 12);
|
|
table.integer('quality', 6);
|
|
table.integer('width', 6);
|
|
table.integer('height', 6);
|
|
table.boolean('is_vr');
|
|
table.float('entropy');
|
|
table.float('sharpness');
|
|
|
|
table.text('source', 2100);
|
|
table.text('source_page', 2100);
|
|
|
|
table.text('scraper', 32);
|
|
table.text('credit', 100);
|
|
|
|
table.integer('entity_id')
|
|
.references('id')
|
|
.inTable('entities')
|
|
.onDelete('cascade');
|
|
|
|
table.date('date');
|
|
table.enum('date_precision', ['year', 'month', 'week', 'day', 'hour', 'minute', 'second'])
|
|
.defaultTo('year');
|
|
|
|
table.text('comment');
|
|
table.text('group');
|
|
|
|
table.boolean('is_sfw')
|
|
.defaultTo(false);
|
|
|
|
table.unique('hash');
|
|
table.unique('source');
|
|
|
|
table.datetime('created_at')
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => { // eslint-disable-line arrow-body-style
|
|
// allow vim fold
|
|
return knex.raw(`
|
|
CREATE FUNCTION get_random_sfw_media_id() RETURNS varchar AS $$
|
|
SELECT id FROM media
|
|
WHERE is_sfw = true
|
|
ORDER BY random()
|
|
LIMIT 1;
|
|
$$ LANGUAGE sql STABLE;
|
|
`);
|
|
})
|
|
.then(() => knex.schema.alterTable('media', (table) => {
|
|
table.text('sfw_media_id', 21)
|
|
.references('id')
|
|
.inTable('media')
|
|
.defaultTo(knex.raw('get_random_sfw_media_id()'));
|
|
}))
|
|
.then(() => knex.schema.createTable('tags_groups', (table) => {
|
|
table.increments('id', 12);
|
|
|
|
table.text('name', 32);
|
|
table.text('description');
|
|
|
|
table.text('slug', 32)
|
|
.unique();
|
|
|
|
table.datetime('created_at')
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.schema.createTable('tags', (table) => {
|
|
table.increments('id', 12);
|
|
table.text('name');
|
|
|
|
table.text('description');
|
|
|
|
table.integer('priority', 2)
|
|
.defaultTo(0);
|
|
|
|
table.boolean('filter')
|
|
.defaultTo(false);
|
|
|
|
table.boolean('secondary')
|
|
.defaultTo(false);
|
|
|
|
table.integer('group_id', 12)
|
|
.references('id')
|
|
.inTable('tags_groups');
|
|
|
|
table.integer('alias_for', 12)
|
|
.references('id')
|
|
.inTable('tags');
|
|
|
|
table.text('slug', 32)
|
|
.unique();
|
|
|
|
table.datetime('created_at')
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.schema.createTable('tags_posters', (table) => {
|
|
table.integer('tag_id', 12)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('tags');
|
|
|
|
table.text('media_id', 21)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('media');
|
|
|
|
table.unique('tag_id');
|
|
}))
|
|
.then(() => knex.schema.createTable('tags_photos', (table) => {
|
|
table.integer('tag_id', 12)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('tags');
|
|
|
|
table.text('media_id', 21)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('media');
|
|
|
|
table.unique(['tag_id', 'media_id']);
|
|
}))
|
|
.then(() => knex.schema.createTable('entities_tags', (table) => {
|
|
table.integer('tag_id', 12)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('tags');
|
|
|
|
table.integer('entity_id', 12)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('entities');
|
|
|
|
table.boolean('inherit')
|
|
.defaultTo(false);
|
|
|
|
table.unique(['tag_id', 'entity_id']);
|
|
}))
|
|
.then(() => knex.schema.createTable('entities_social', (table) => {
|
|
table.increments('id', 16);
|
|
|
|
table.text('url');
|
|
table.text('platform');
|
|
|
|
table.integer('entity_id', 12)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('entities');
|
|
|
|
table.unique(['url', 'entity_id']);
|
|
|
|
table.datetime('created_at')
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.schema.createTable('batches', (table) => {
|
|
table.increments('id', 12);
|
|
table.text('comment');
|
|
|
|
table.datetime('created_at')
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.schema.createTable('actors', (table) => {
|
|
table.increments('id', 12);
|
|
|
|
table.text('name')
|
|
.notNullable();
|
|
|
|
table.text('slug', 32)
|
|
.notNullable();
|
|
|
|
table.integer('entity_id', 12)
|
|
.references('id')
|
|
.inTable('entities');
|
|
|
|
table.text('entry_id');
|
|
|
|
table.integer('alias_for', 12)
|
|
.references('id')
|
|
.inTable('actors');
|
|
|
|
table.text('real_name');
|
|
|
|
table.date('date_of_birth');
|
|
table.date('date_of_death');
|
|
table.integer('age', 3);
|
|
|
|
table.text('gender', 18);
|
|
table.text('orientation');
|
|
table.text('description');
|
|
|
|
table.text('birth_city');
|
|
table.text('birth_state');
|
|
table.text('birth_country_alpha2', 2)
|
|
.references('alpha2')
|
|
.inTable('countries');
|
|
|
|
table.text('residence_city');
|
|
table.text('residence_state');
|
|
table.text('residence_country_alpha2', 2)
|
|
.references('alpha2')
|
|
.inTable('countries');
|
|
|
|
table.text('ethnicity');
|
|
|
|
table.text('cup', 4);
|
|
table.integer('bust', 3);
|
|
table.integer('waist', 3);
|
|
table.integer('hip', 3);
|
|
table.boolean('natural_boobs');
|
|
|
|
table.integer('penis_length', 3);
|
|
table.integer('penis_girth', 3);
|
|
table.boolean('circumcised');
|
|
|
|
table.integer('height', 3);
|
|
table.integer('weight', 3);
|
|
table.text('eyes');
|
|
table.text('hair_color');
|
|
table.text('hair_length');
|
|
|
|
table.boolean('has_tattoos');
|
|
table.boolean('has_piercings');
|
|
table.text('piercings');
|
|
table.text('tattoos');
|
|
|
|
table.text('avatar_media_id', 21)
|
|
.references('id')
|
|
.inTable('media');
|
|
|
|
table.integer('batch_id', 12)
|
|
.references('id')
|
|
.inTable('batches')
|
|
.onDelete('cascade');
|
|
|
|
table.datetime('updated_at')
|
|
.defaultTo(knex.fn.now());
|
|
|
|
table.datetime('created_at')
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.schema.createTable('actors_profiles', (table) => {
|
|
table.increments('id', 12);
|
|
|
|
table.integer('actor_id', 12)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('actors')
|
|
.onDelete('cascade');
|
|
|
|
table.integer('entity_id', 12)
|
|
.references('id')
|
|
.inTable('entities');
|
|
|
|
table.unique(['actor_id', 'entity_id']);
|
|
table.integer('priority', 4)
|
|
.defaultTo(1);
|
|
|
|
table.text('url');
|
|
|
|
table.text('real_name');
|
|
table.text('gender', 18);
|
|
table.text('orientation');
|
|
|
|
table.date('date_of_birth');
|
|
table.date('date_of_death');
|
|
table.integer('age', 3);
|
|
|
|
table.text('description');
|
|
table.text('description_hash');
|
|
|
|
table.text('birth_city');
|
|
table.text('birth_state');
|
|
table.text('birth_country_alpha2', 2)
|
|
.references('alpha2')
|
|
.inTable('countries');
|
|
|
|
table.text('residence_city');
|
|
table.text('residence_state');
|
|
table.text('residence_country_alpha2', 2)
|
|
.references('alpha2')
|
|
.inTable('countries');
|
|
|
|
table.text('ethnicity');
|
|
|
|
table.text('cup', 4);
|
|
table.integer('bust', 3);
|
|
table.integer('waist', 3);
|
|
table.integer('hip', 3);
|
|
table.boolean('natural_boobs');
|
|
|
|
table.integer('penis_length', 3);
|
|
table.integer('penis_girth', 3);
|
|
table.boolean('circumcised');
|
|
|
|
table.integer('height', 3);
|
|
table.integer('weight', 3);
|
|
table.text('eyes');
|
|
table.text('hair_color');
|
|
table.text('hair_length');
|
|
|
|
table.boolean('has_tattoos');
|
|
table.boolean('has_piercings');
|
|
table.text('piercings');
|
|
table.text('tattoos');
|
|
|
|
table.text('avatar_media_id', 21)
|
|
.references('id')
|
|
.inTable('media');
|
|
|
|
table.datetime('scraped_at');
|
|
table.boolean('scrape_success');
|
|
|
|
table.datetime('updated_at')
|
|
.defaultTo(knex.fn.now());
|
|
|
|
table.datetime('created_at')
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.schema.createTable('body', (table) => {
|
|
table.text('slug', 20)
|
|
.primary();
|
|
|
|
table.text('name');
|
|
}))
|
|
.then(() => { // eslint-disable-line arrow-body-style
|
|
// allow vim fold
|
|
return knex('body').insert([
|
|
// head
|
|
{ slug: 'head', name: 'head' },
|
|
{ slug: 'face', name: 'face' },
|
|
{ slug: 'scalp', name: 'scalp' },
|
|
{ slug: 'forehead', name: 'forehead' },
|
|
{ slug: 'temple', name: 'temple' },
|
|
{ slug: 'cheek', name: 'cheek' },
|
|
{ slug: 'jaw', name: 'jaw' },
|
|
{ slug: 'chin', name: 'chin' },
|
|
{ slug: 'neck', name: 'neck' },
|
|
{ slug: 'throat', name: 'throat' },
|
|
// eyes
|
|
{ slug: 'eyelid', name: 'eyelid' },
|
|
{ slug: 'eyeball', name: 'eyeball' },
|
|
{ slug: 'eyebrow', name: 'eyebrow' },
|
|
// mouth
|
|
{ slug: 'tongue', name: 'tongue' },
|
|
{ slug: 'lip', name: 'lip' },
|
|
{ slug: 'upper-lip', name: 'upper lip' },
|
|
{ slug: 'lower-lip', name: 'lower lip' },
|
|
{ slug: 'inner-lip', name: 'inner lip' },
|
|
{ slug: 'inner-lower-lip', name: 'inner lower lip' },
|
|
{ slug: 'inner-upper-lip', name: 'inner upper lip' },
|
|
{ slug: 'philtrum', name: 'philtrum' },
|
|
{ slug: 'above-lip', name: 'above lip' },
|
|
{ slug: 'below-lip', name: 'below lip' },
|
|
// nose
|
|
{ slug: 'nose', name: 'nose' },
|
|
{ slug: 'third-eye', name: 'third eye' },
|
|
{ slug: 'bridge', name: 'bridge' },
|
|
{ slug: 'nostril', name: 'nostril' },
|
|
{ slug: 'septum', name: 'septum' },
|
|
{ slug: 'septril', name: 'septril' },
|
|
// ear
|
|
{ slug: 'ear', name: 'ear' },
|
|
{ slug: 'earlobe', name: 'earlobe' },
|
|
{ slug: 'helix', name: 'helix' },
|
|
{ slug: 'tragus', name: 'tragus' },
|
|
{ slug: 'conch', name: 'conch' },
|
|
{ slug: 'rook', name: 'rook' },
|
|
{ slug: 'behind-ear', name: 'behind ear' },
|
|
// arms
|
|
{ slug: 'arm', name: 'arm' },
|
|
{ slug: 'upper-arm', name: 'upper arm' },
|
|
{ slug: 'forearm', name: 'forearm' },
|
|
{ slug: 'elbow', name: 'elbow' },
|
|
{ slug: 'inner-elbow', name: 'inner elbow' },
|
|
{ slug: 'outer-elbow', name: 'outer elbow' },
|
|
// hands
|
|
{ slug: 'hand', name: 'hand' },
|
|
{ slug: 'fingers', name: 'fingers' },
|
|
{ slug: 'knuckles', name: 'knuckles' },
|
|
{ slug: 'thumb', name: 'thumb' },
|
|
{ slug: 'index-finger', name: 'index finger' },
|
|
{ slug: 'middle-finger', name: 'middle finger' },
|
|
{ slug: 'ring-finger', name: 'ring finger' },
|
|
{ slug: 'pinky', name: 'pinky' },
|
|
{ slug: 'back-of-hand', name: 'back of hand' },
|
|
{ slug: 'inner-wrist', name: 'inner wrist' },
|
|
{ slug: 'outer-wrist', name: 'outer wrist' },
|
|
// torso
|
|
{ slug: 'shoulder', name: 'shoulder' },
|
|
{ slug: 'collarbone', name: 'collarbone' },
|
|
{ slug: 'chest', name: 'chest' },
|
|
{ slug: 'rib-cage', name: 'rib cage' },
|
|
{ slug: 'breastbone', name: 'breastbone' },
|
|
{ slug: 'underboob', name: 'underboob' },
|
|
{ slug: 'sideboob', name: 'sideboob' },
|
|
{ slug: 'boob', name: 'boob' },
|
|
{ slug: 'nipple', name: 'nipple' },
|
|
{ slug: 'abdomen', name: 'abdomen' },
|
|
{ slug: 'navel', name: 'navel' },
|
|
{ slug: 'pelvis', name: 'pelvis' },
|
|
// back
|
|
{ slug: 'back', name: 'back' },
|
|
{ slug: 'upper-back', name: 'upper back' },
|
|
{ slug: 'middle-back', name: 'lower back' },
|
|
{ slug: 'lower-back', name: 'lower back' },
|
|
{ slug: 'spine', name: 'spine' },
|
|
// bottom
|
|
{ slug: 'butt', name: 'butt' },
|
|
{ slug: 'hip', name: 'hip' },
|
|
{ slug: 'anus', name: 'anus' },
|
|
// genitals
|
|
{ slug: 'pubic-mound', name: 'pubic mound' },
|
|
{ slug: 'vagina', name: 'vagina' },
|
|
{ slug: 'outer-labia', name: 'outer labia' },
|
|
{ slug: 'inner-labia', name: 'inner labia' },
|
|
{ slug: 'clitoris', name: 'clitoris' },
|
|
{ slug: 'penis', name: 'penis' },
|
|
{ slug: 'glans', name: 'glans' },
|
|
{ slug: 'foreskin', name: 'foreskin' },
|
|
{ slug: 'shaft', name: 'shaft' },
|
|
{ slug: 'scrotum', name: 'scrotum' },
|
|
// legs
|
|
{ slug: 'leg', name: 'leg' },
|
|
{ slug: 'groin', name: 'groin' },
|
|
{ slug: 'upper-leg', name: 'upper leg' },
|
|
{ slug: 'thigh', name: 'thigh' },
|
|
{ slug: 'lower-leg', name: 'lower leg' },
|
|
{ slug: 'shin', name: 'shin' },
|
|
{ slug: 'calf', name: 'calf' },
|
|
{ slug: 'knee', name: 'knee' },
|
|
{ slug: 'inner-knee', name: 'inner knee' },
|
|
// feet
|
|
{ slug: 'inner-ankle', name: 'inner ankle' },
|
|
{ slug: 'outer-ankle', name: 'outer ankle' },
|
|
{ slug: 'foot', name: 'foot' },
|
|
{ slug: 'toes', name: 'toes' },
|
|
{ slug: 'big-toe', name: 'big toe' },
|
|
{ slug: 'index-toe', name: 'index toe' },
|
|
{ slug: 'middle-toe', name: 'middle toe' },
|
|
{ slug: 'fourth-toe', name: 'fourth toe' },
|
|
{ slug: 'little-toe', name: 'little toe' },
|
|
]);
|
|
})
|
|
.then(() => knex.schema.createTable('actors_tattoos', (table) => {
|
|
table.increments('id');
|
|
|
|
table.integer('actor_id', 12)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('actors')
|
|
.onDelete('cascade');
|
|
|
|
table.text('body_slug', 20)
|
|
.references('slug')
|
|
.inTable('body');
|
|
|
|
table.enum('side', ['left', 'right', 'center', 'both']);
|
|
|
|
table.text('description');
|
|
|
|
table.datetime('created_at')
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.schema.createTable('actors_piercings', (table) => {
|
|
table.increments('id');
|
|
|
|
table.integer('actor_id', 12)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('actors')
|
|
.onDelete('cascade');
|
|
|
|
table.text('body_slug', 20)
|
|
.references('slug')
|
|
.inTable('body');
|
|
|
|
table.enum('side', ['left', 'right', 'center', 'both']);
|
|
|
|
table.text('description');
|
|
|
|
table.datetime('created_at')
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.schema.createTable('actors_avatars', (table) => {
|
|
table.integer('profile_id', 12)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('actors_profiles')
|
|
.onDelete('cascade');
|
|
|
|
table.text('media_id', 21)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('media');
|
|
|
|
table.unique('profile_id');
|
|
}))
|
|
.then(() => knex.schema.createTable('actors_photos', (table) => {
|
|
table.integer('actor_id', 12)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('actors')
|
|
.onDelete('cascade');
|
|
|
|
table.text('media_id', 21)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('media');
|
|
|
|
table.unique(['actor_id', 'media_id']);
|
|
}))
|
|
.then(() => knex.schema.createTable('actors_social', (table) => {
|
|
table.increments('id', 16);
|
|
|
|
table.text('url');
|
|
table.text('platform');
|
|
|
|
table.integer('actor_id', 12)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('actors');
|
|
|
|
table.unique(['url', 'actor_id']);
|
|
|
|
table.datetime('created_at')
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.schema.createTable('releases', (table) => {
|
|
table.increments('id', 16);
|
|
|
|
table.integer('entity_id', 12)
|
|
.references('id')
|
|
.inTable('entities')
|
|
.notNullable();
|
|
|
|
table.integer('studio_id', 12)
|
|
.references('id')
|
|
.inTable('entities');
|
|
|
|
table.text('shoot_id');
|
|
table.text('entry_id');
|
|
|
|
table.unique(['entity_id', 'entry_id']);
|
|
|
|
table.text('url', 1000);
|
|
table.text('title');
|
|
table.text('slug');
|
|
|
|
table.timestamp('date');
|
|
table.index('date');
|
|
|
|
table.date('production_date');
|
|
|
|
table.text('production_location');
|
|
table.text('production_city');
|
|
table.text('production_state');
|
|
table.text('production_country_alpha2', 2)
|
|
.references('alpha2')
|
|
.inTable('countries');
|
|
|
|
table.enum('date_precision', ['year', 'month', 'week', 'day', 'hour', 'minute', 'second'])
|
|
.defaultTo('day');
|
|
|
|
table.text('description');
|
|
|
|
table.integer('duration')
|
|
.unsigned();
|
|
|
|
table.specificType('qualities', 'text[]');
|
|
|
|
table.boolean('deep');
|
|
table.text('deep_url', 1000);
|
|
|
|
table.text('comment');
|
|
|
|
table.integer('created_batch_id', 12)
|
|
.references('id')
|
|
.inTable('batches')
|
|
.onDelete('cascade');
|
|
|
|
table.integer('updated_batch_id', 12)
|
|
.references('id')
|
|
.inTable('batches')
|
|
.onDelete('cascade');
|
|
|
|
table.datetime('created_at')
|
|
.notNullable()
|
|
.defaultTo(knex.fn.now());
|
|
|
|
table.datetime('updated_at')
|
|
.notNullable()
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.schema.createTable('releases_actors', (table) => {
|
|
table.integer('release_id', 16)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('releases')
|
|
.onDelete('cascade');
|
|
|
|
table.integer('actor_id', 12)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('actors')
|
|
.onDelete('cascade');
|
|
|
|
table.integer('alias_id', 12)
|
|
.references('id')
|
|
.inTable('actors')
|
|
.onDelete('cascade');
|
|
|
|
table.unique(['release_id', 'actor_id']);
|
|
|
|
table.datetime('created_at')
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.schema.createTable('releases_directors', (table) => {
|
|
table.integer('release_id', 16)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('releases')
|
|
.onDelete('cascade');
|
|
|
|
table.integer('director_id', 12)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('actors')
|
|
.onDelete('cascade');
|
|
|
|
table.integer('alias_id', 12)
|
|
.references('id')
|
|
.inTable('actors')
|
|
.onDelete('cascade');
|
|
|
|
table.unique(['release_id', 'director_id']);
|
|
|
|
table.datetime('created_at')
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.schema.createTable('releases_posters', (table) => {
|
|
table.integer('release_id', 16)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('releases')
|
|
.onDelete('cascade');
|
|
|
|
table.text('media_id', 21)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('media');
|
|
|
|
table.unique('release_id');
|
|
}))
|
|
.then(() => knex.schema.createTable('releases_covers', (table) => {
|
|
table.integer('release_id', 16)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('releases')
|
|
.onDelete('cascade');
|
|
|
|
table.text('media_id', 21)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('media');
|
|
|
|
table.unique(['release_id', 'media_id']);
|
|
}))
|
|
.then(() => knex.schema.createTable('releases_trailers', (table) => {
|
|
table.integer('release_id', 16)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('releases')
|
|
.onDelete('cascade');
|
|
|
|
table.text('media_id', 21)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('media');
|
|
|
|
table.unique('release_id');
|
|
}))
|
|
.then(() => knex.schema.createTable('releases_teasers', (table) => {
|
|
table.integer('release_id', 16)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('releases')
|
|
.onDelete('cascade');
|
|
|
|
table.text('media_id', 21)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('media');
|
|
|
|
table.unique('release_id');
|
|
}))
|
|
.then(() => knex.schema.createTable('releases_photos', (table) => {
|
|
table.integer('release_id', 16)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('releases')
|
|
.onDelete('cascade');
|
|
|
|
table.text('media_id', 21)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('media');
|
|
|
|
table.unique(['release_id', 'media_id']);
|
|
}))
|
|
.then(() => knex.schema.createTable('releases_tags', (table) => {
|
|
table.integer('tag_id', 12)
|
|
.references('id')
|
|
.inTable('tags');
|
|
|
|
table.integer('release_id', 16)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('releases')
|
|
.onDelete('cascade');
|
|
|
|
table.text('original_tag');
|
|
|
|
table.unique(['tag_id', 'release_id']);
|
|
table.index('tag_id');
|
|
table.index('release_id');
|
|
}))
|
|
.then(() => knex.schema.createTable('releases_search', (table) => {
|
|
table.integer('release_id', 16)
|
|
.references('id')
|
|
.inTable('releases')
|
|
.onDelete('cascade');
|
|
}))
|
|
.then(() => knex.schema.createTable('movies', (table) => {
|
|
table.increments('id', 16);
|
|
|
|
table.integer('entity_id', 12)
|
|
.references('id')
|
|
.inTable('entities')
|
|
.notNullable();
|
|
|
|
table.integer('studio_id', 12)
|
|
.references('id')
|
|
.inTable('entities');
|
|
|
|
table.text('entry_id');
|
|
table.unique(['entity_id', 'entry_id']);
|
|
|
|
table.text('url', 1000);
|
|
table.text('title');
|
|
table.text('slug');
|
|
|
|
table.timestamp('date');
|
|
table.index('date');
|
|
|
|
table.enum('date_precision', ['year', 'month', 'week', 'day', 'hour', 'minute', 'second'])
|
|
.defaultTo('day');
|
|
|
|
table.text('description');
|
|
|
|
table.boolean('deep');
|
|
table.text('deep_url', 1000);
|
|
|
|
table.text('comment');
|
|
|
|
table.integer('created_batch_id', 12)
|
|
.references('id')
|
|
.inTable('batches')
|
|
.onDelete('cascade');
|
|
|
|
table.integer('updated_batch_id', 12)
|
|
.references('id')
|
|
.inTable('batches')
|
|
.onDelete('cascade');
|
|
|
|
table.datetime('created_at')
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.schema.createTable('movies_scenes', (table) => {
|
|
table.integer('movie_id', 16)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('movies')
|
|
.onDelete('cascade');
|
|
|
|
table.integer('scene_id', 16)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('releases')
|
|
.onDelete('cascade');
|
|
|
|
table.unique(['movie_id', 'scene_id']);
|
|
|
|
table.datetime('created_at')
|
|
.defaultTo(knex.fn.now());
|
|
|
|
table.index('scene_id');
|
|
}))
|
|
.then(() => knex.schema.createTable('movies_covers', (table) => {
|
|
table.integer('movie_id', 16)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('movies')
|
|
.onDelete('cascade');
|
|
|
|
table.text('media_id', 21)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('media');
|
|
|
|
table.unique(['movie_id', 'media_id']);
|
|
}))
|
|
.then(() => knex.schema.createTable('movies_trailers', (table) => {
|
|
table.integer('movie_id', 16)
|
|
.unique()
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('movies')
|
|
.onDelete('cascade');
|
|
|
|
table.text('media_id', 21)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('media');
|
|
}))
|
|
.then(() => knex.schema.createTable('movies_posters', (table) => {
|
|
table.integer('movie_id', 16)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('movies')
|
|
.onDelete('cascade');
|
|
|
|
table.text('media_id', 21)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('media')
|
|
.onDelete('cascade');
|
|
|
|
table.unique('movie_id');
|
|
}))
|
|
.then(() => knex.schema.createTable('movies_photos', (table) => {
|
|
table.integer('movie_id', 16)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('movies')
|
|
.onDelete('cascade');
|
|
|
|
table.text('media_id', 21)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('media');
|
|
|
|
table.unique(['movie_id', 'media_id']);
|
|
}))
|
|
.then(() => knex.schema.createTable('movies_search', (table) => {
|
|
table.integer('movie_id', 16)
|
|
.references('id')
|
|
.inTable('movies')
|
|
.onDelete('cascade');
|
|
}))
|
|
.then(() => knex.schema.createTable('series', (table) => {
|
|
table.increments('id', 16);
|
|
|
|
table.integer('entity_id', 12)
|
|
.references('id')
|
|
.inTable('entities')
|
|
.notNullable();
|
|
|
|
table.integer('studio_id', 12)
|
|
.references('id')
|
|
.inTable('entities');
|
|
|
|
table.text('entry_id');
|
|
table.unique(['entity_id', 'entry_id']);
|
|
|
|
table.text('url', 1000);
|
|
table.text('title');
|
|
table.text('slug');
|
|
|
|
table.timestamp('date');
|
|
table.index('date');
|
|
|
|
table.enum('date_precision', ['year', 'month', 'week', 'day', 'hour', 'minute', 'second'])
|
|
.defaultTo('day');
|
|
|
|
table.text('description');
|
|
|
|
table.boolean('deep');
|
|
table.text('deep_url', 1000);
|
|
|
|
table.text('comment');
|
|
|
|
table.integer('created_batch_id', 12)
|
|
.references('id')
|
|
.inTable('batches')
|
|
.onDelete('cascade');
|
|
|
|
table.integer('updated_batch_id', 12)
|
|
.references('id')
|
|
.inTable('batches')
|
|
.onDelete('cascade');
|
|
|
|
table.datetime('created_at')
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.schema.createTable('series_scenes', (table) => {
|
|
table.integer('serie_id', 16)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('series')
|
|
.onDelete('cascade');
|
|
|
|
table.integer('scene_id', 16)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('releases')
|
|
.onDelete('cascade');
|
|
|
|
table.unique(['serie_id', 'scene_id']);
|
|
|
|
table.datetime('created_at')
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.schema.createTable('series_trailers', (table) => {
|
|
table.integer('serie_id', 16)
|
|
.unique()
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('series')
|
|
.onDelete('cascade');
|
|
|
|
table.text('media_id', 21)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('media');
|
|
}))
|
|
.then(() => knex.schema.createTable('series_posters', (table) => {
|
|
table.integer('serie_id', 16)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('series')
|
|
.onDelete('cascade');
|
|
|
|
table.text('media_id', 21)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('media')
|
|
.onDelete('cascade');
|
|
|
|
table.unique('serie_id');
|
|
}))
|
|
.then(() => knex.schema.createTable('series_covers', (table) => {
|
|
table.integer('serie_id', 16)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('series')
|
|
.onDelete('cascade');
|
|
|
|
table.text('media_id', 21)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('media');
|
|
|
|
table.unique(['serie_id', 'media_id']);
|
|
}))
|
|
.then(() => knex.schema.createTable('series_photos', (table) => {
|
|
table.integer('serie_id', 16)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('series')
|
|
.onDelete('cascade');
|
|
|
|
table.text('media_id', 21)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('media');
|
|
|
|
table.unique(['serie_id', 'media_id']);
|
|
}))
|
|
.then(() => knex.schema.createTable('series_search', (table) => {
|
|
table.integer('serie_id', 16)
|
|
.references('id')
|
|
.inTable('series')
|
|
.onDelete('cascade');
|
|
}))
|
|
.then(() => knex.schema.createTable('chapters', (table) => {
|
|
table.increments('id', 16);
|
|
|
|
table.integer('release_id', 12)
|
|
.references('id')
|
|
.inTable('releases')
|
|
.notNullable()
|
|
.onDelete('cascade');
|
|
|
|
table.integer('index');
|
|
table.unique(['release_id', 'index']);
|
|
|
|
table.integer('time')
|
|
.unsigned();
|
|
|
|
table.integer('duration')
|
|
.unsigned();
|
|
|
|
table.text('title');
|
|
table.text('description');
|
|
|
|
table.integer('created_batch_id', 12)
|
|
.references('id')
|
|
.inTable('batches')
|
|
.onDelete('cascade');
|
|
|
|
table.integer('updated_batch_id', 12)
|
|
.references('id')
|
|
.inTable('batches')
|
|
.onDelete('cascade');
|
|
|
|
table.datetime('created_at')
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.schema.createTable('chapters_posters', (table) => {
|
|
table.integer('chapter_id', 16)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('chapters')
|
|
.onDelete('cascade');
|
|
|
|
table.text('media_id', 21)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('media');
|
|
|
|
table.unique('chapter_id');
|
|
}))
|
|
.then(() => knex.schema.createTable('chapters_photos', (table) => {
|
|
table.integer('chapter_id', 16)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('chapters')
|
|
.onDelete('cascade');
|
|
|
|
table.text('media_id', 21)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('media');
|
|
|
|
table.unique(['chapter_id', 'media_id']);
|
|
}))
|
|
.then(() => knex.schema.createTable('chapters_tags', (table) => {
|
|
table.integer('tag_id', 12)
|
|
.references('id')
|
|
.inTable('tags')
|
|
.onDelete('cascade');
|
|
|
|
table.integer('chapter_id', 16)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('chapters')
|
|
.onDelete('cascade');
|
|
|
|
table.text('original_tag');
|
|
|
|
table.unique(['tag_id', 'chapter_id']);
|
|
}))
|
|
.then(() => knex.schema.createTable('users_roles', (table) => {
|
|
table.string('role')
|
|
.primary();
|
|
|
|
table.json('abilities');
|
|
}))
|
|
.then(() => knex('users_roles').insert([
|
|
{
|
|
role: 'admin',
|
|
abilities: JSON.stringify([ // serialization necessary to avoid array being interpreted as a PG array
|
|
{ subject: 'scene', action: 'create' },
|
|
{ subject: 'scene', action: 'update' },
|
|
{ subject: 'scene', action: 'delete' },
|
|
{ subject: 'actor', action: 'create' },
|
|
{ subject: 'actor', action: 'update' },
|
|
{ subject: 'actor', action: 'delete' },
|
|
]),
|
|
},
|
|
{
|
|
role: 'editor',
|
|
abilities: JSON.stringify([ // serialization necessary to avoid array being interpreted as a PG array
|
|
{ subject: 'scene', action: 'update' },
|
|
{ subject: 'actor', action: 'update' },
|
|
]),
|
|
},
|
|
{
|
|
role: 'user',
|
|
},
|
|
]))
|
|
.then(() => knex.schema.createTable('users', (table) => {
|
|
table.increments('id');
|
|
|
|
table.text('username')
|
|
.unique()
|
|
.notNullable();
|
|
|
|
table.text('email')
|
|
.unique()
|
|
.notNullable();
|
|
|
|
table.text('password')
|
|
.notNullable();
|
|
|
|
table.string('role')
|
|
.references('role')
|
|
.inTable('users_roles')
|
|
.defaultTo('user')
|
|
.notNullable();
|
|
|
|
table.json('abilities');
|
|
|
|
table.boolean('email_verified')
|
|
.notNullable()
|
|
.defaultTo(false);
|
|
|
|
table.boolean('identity_verified')
|
|
.notNullable()
|
|
.defaultTo(false);
|
|
|
|
table.datetime('created_at')
|
|
.notNullable()
|
|
.defaultTo(knex.fn.now());
|
|
|
|
table.datetime('last_login');
|
|
}))
|
|
.then(() => knex.schema.createTable('stashes', (table) => {
|
|
table.increments('id');
|
|
|
|
table.integer('user_id')
|
|
.references('id')
|
|
.inTable('users')
|
|
.onDelete('cascade');
|
|
|
|
table.string('name')
|
|
.notNullable();
|
|
|
|
table.string('slug')
|
|
.notNullable();
|
|
|
|
table.boolean('public')
|
|
.notNullable()
|
|
.defaultTo(false);
|
|
|
|
table.boolean('primary')
|
|
.notNullable()
|
|
.defaultTo(false);
|
|
|
|
table.datetime('created_at')
|
|
.notNullable()
|
|
.defaultTo(knex.fn.now());
|
|
|
|
table.unique(['user_id', 'slug']);
|
|
}))
|
|
.then(() => knex.raw(`
|
|
CREATE UNIQUE INDEX unique_primary ON stashes (user_id, "primary") WHERE ("primary" = TRUE);
|
|
`))
|
|
.then(() => knex.schema.createTable('stashes_scenes', (table) => {
|
|
table.integer('stash_id')
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('stashes')
|
|
.onDelete('cascade');
|
|
|
|
table.integer('scene_id')
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('releases')
|
|
.onDelete('cascade');
|
|
|
|
table.unique(['stash_id', 'scene_id']);
|
|
|
|
table.string('comment');
|
|
|
|
table.datetime('created_at')
|
|
.notNullable()
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.schema.createTable('stashes_movies', (table) => {
|
|
table.integer('stash_id')
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('stashes')
|
|
.onDelete('cascade');
|
|
|
|
table.integer('movie_id')
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('movies')
|
|
.onDelete('cascade');
|
|
|
|
table.unique(['stash_id', 'movie_id']);
|
|
|
|
table.string('comment');
|
|
|
|
table.datetime('created_at')
|
|
.notNullable()
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.schema.createTable('stashes_actors', (table) => {
|
|
table.integer('stash_id')
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('stashes')
|
|
.onDelete('cascade');
|
|
|
|
table.integer('actor_id')
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('actors')
|
|
.onDelete('cascade');
|
|
|
|
table.unique(['stash_id', 'actor_id']);
|
|
|
|
table.string('comment');
|
|
|
|
table.datetime('created_at')
|
|
.notNullable()
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.schema.createTable('stashes_series', (table) => {
|
|
table.integer('stash_id')
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('stashes')
|
|
.onDelete('cascade');
|
|
|
|
table.integer('serie_id')
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('series')
|
|
.onDelete('cascade');
|
|
|
|
table.unique(['stash_id', 'serie_id']);
|
|
|
|
table.string('comment');
|
|
|
|
table.datetime('created_at')
|
|
.notNullable()
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.schema.createTable('alerts', (table) => {
|
|
table.increments('id');
|
|
|
|
table.integer('user_id')
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('users')
|
|
.onDelete('cascade');
|
|
|
|
table.boolean('notify')
|
|
.defaultTo(false);
|
|
|
|
table.boolean('email')
|
|
.defaultTo(false);
|
|
|
|
table.datetime('created_at')
|
|
.notNullable()
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.schema.createTable('alerts_scenes', (table) => {
|
|
table.increments('id');
|
|
|
|
table.integer('alert_id')
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('alerts')
|
|
.onDelete('cascade');
|
|
|
|
table.integer('scene_id')
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('releases')
|
|
.onDelete('cascade');
|
|
|
|
table.unique(['alert_id', 'scene_id']);
|
|
}))
|
|
.then(() => knex.schema.createTable('alerts_actors', (table) => {
|
|
table.increments('id');
|
|
|
|
table.integer('alert_id')
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('alerts')
|
|
.onDelete('cascade');
|
|
|
|
table.integer('actor_id')
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('actors')
|
|
.onDelete('cascade');
|
|
|
|
table.unique(['alert_id', 'actor_id']);
|
|
}))
|
|
.then(() => knex.schema.createTable('alerts_tags', (table) => {
|
|
table.increments('id');
|
|
|
|
table.integer('alert_id')
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('alerts')
|
|
.onDelete('cascade');
|
|
|
|
table.integer('tag_id')
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('tags')
|
|
.onDelete('cascade');
|
|
|
|
table.unique(['alert_id', 'tag_id']);
|
|
}))
|
|
.then(() => knex.schema.createTable('alerts_entities', (table) => {
|
|
table.increments('id');
|
|
|
|
table.integer('alert_id')
|
|
.notNullable()
|
|
.unique()
|
|
.references('id')
|
|
.inTable('alerts')
|
|
.onDelete('cascade');
|
|
|
|
table.integer('entity_id')
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('entities')
|
|
.onDelete('cascade');
|
|
}))
|
|
.then(() => knex.schema.createTable('alerts_stashes', (table) => {
|
|
table.increments('id');
|
|
|
|
table.integer('alert_id')
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('alerts')
|
|
.onDelete('cascade');
|
|
|
|
table.integer('stash_id')
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('stashes')
|
|
.onDelete('cascade');
|
|
|
|
table.unique(['alert_id', 'stash_id']);
|
|
}))
|
|
.then(() => knex.schema.createTable('notifications', (table) => {
|
|
table.increments('id');
|
|
|
|
table.integer('user_id')
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('users')
|
|
.onDelete('cascade');
|
|
|
|
table.integer('scene_id')
|
|
.references('id')
|
|
.inTable('releases')
|
|
.onDelete('cascade');
|
|
|
|
table.integer('alert_id')
|
|
.references('id')
|
|
.inTable('alerts')
|
|
.onDelete('set null');
|
|
|
|
table.boolean('seen')
|
|
.notNullable()
|
|
.defaultTo(false);
|
|
|
|
table.datetime('created_at')
|
|
.notNullable()
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.schema.createTable('affiliates', (table) => {
|
|
table.string('id')
|
|
.primary()
|
|
.unique()
|
|
.notNullable();
|
|
|
|
table.integer('entity_id', 12)
|
|
.references('id')
|
|
.inTable('entities');
|
|
|
|
table.text('url');
|
|
table.text('parameters');
|
|
|
|
table.unique(['entity_id', 'url']);
|
|
table.unique(['entity_id', 'parameters']);
|
|
|
|
table.text('comment');
|
|
|
|
table.datetime('created_at')
|
|
.notNullable()
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.schema.createTable('banners', (table) => {
|
|
table.string('id')
|
|
.primary()
|
|
.unique()
|
|
.notNullable();
|
|
|
|
table.string('type')
|
|
.defaultTo('jpg');
|
|
|
|
table.integer('width')
|
|
.notNullable();
|
|
|
|
table.integer('height')
|
|
.notNullable();
|
|
|
|
table.integer('entity_id', 12)
|
|
.references('id')
|
|
.inTable('entities');
|
|
|
|
table.text('comment');
|
|
|
|
table.datetime('created_at')
|
|
.notNullable()
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.raw(`
|
|
ALTER TABLE banners ADD COLUMN ratio numeric GENERATED ALWAYS AS (ROUND(width::decimal/ height::decimal, 2)) STORED;
|
|
`))
|
|
.then(() => knex.schema.createTable('banners_tags', (table) => {
|
|
table.increments('id');
|
|
|
|
table.string('banner_id')
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('banners');
|
|
|
|
table.integer('tag_id')
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('tags');
|
|
|
|
table.unique(['banner_id', 'tag_id']);
|
|
|
|
table.datetime('created_at')
|
|
.notNullable()
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.schema.createTable('campaigns', (table) => {
|
|
table.increments('id');
|
|
|
|
table.integer('entity_id', 12)
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('entities');
|
|
|
|
table.text('affiliate_id')
|
|
.references('id')
|
|
.inTable('affiliates');
|
|
|
|
table.text('url');
|
|
|
|
table.string('banner_id')
|
|
.references('id')
|
|
.inTable('banners');
|
|
|
|
table.text('comment');
|
|
|
|
table.datetime('created_at')
|
|
.notNullable()
|
|
.defaultTo(knex.fn.now());
|
|
}))
|
|
.then(() => knex.schema.createTable('random_campaign', (table) => {
|
|
table.integer('id')
|
|
.notNullable()
|
|
.references('id')
|
|
.inTable('campaigns');
|
|
|
|
table.text('banner_id')
|
|
.references('id')
|
|
.inTable('banners');
|
|
|
|
table.text('url');
|
|
|
|
table.integer('entity_id')
|
|
.references('id')
|
|
.inTable('entities');
|
|
|
|
table.string('affiliate_id')
|
|
.references('id')
|
|
.inTable('affiliates');
|
|
|
|
table.integer('parent_id')
|
|
.references('id')
|
|
.inTable('entities');
|
|
}))
|
|
// SEARCH AND SORT
|
|
.then(() => { // eslint-disable-line arrow-body-style
|
|
// allow vim fold
|
|
return knex.raw(`
|
|
ALTER TABLE releases_search ADD COLUMN document tsvector;
|
|
ALTER TABLE movies_search ADD COLUMN document tsvector;
|
|
ALTER TABLE series_search ADD COLUMN document tsvector;
|
|
|
|
/* allow scenes without dates to be mixed inbetween scenes with dates */
|
|
ALTER TABLE releases
|
|
ADD COLUMN effective_date timestamptz
|
|
GENERATED ALWAYS AS (COALESCE(date, created_at)) STORED;
|
|
|
|
ALTER TABLE movies
|
|
ADD COLUMN effective_date timestamptz
|
|
GENERATED ALWAYS AS (COALESCE(date, created_at)) STORED;
|
|
`);
|
|
})
|
|
// INDEXES
|
|
.then(() => { // eslint-disable-line arrow-body-style
|
|
// allow vim fold
|
|
return knex.raw(`
|
|
CREATE UNIQUE INDEX unique_actor_slugs_network ON actors (slug, entity_id, entry_id);
|
|
CREATE UNIQUE INDEX unique_actor_slugs ON actors (slug) WHERE entity_id IS NULL;
|
|
|
|
CREATE UNIQUE INDEX unique_entity_campaigns_banner_url ON campaigns (entity_id, url, banner_id) WHERE affiliate_id IS NULL;
|
|
CREATE UNIQUE INDEX unique_entity_campaigns_url ON campaigns (entity_id, url) WHERE banner_id IS NULL AND affiliate_id IS NULL;
|
|
CREATE UNIQUE INDEX unique_entity_campaigns_banner_affiliate ON campaigns (entity_id, affiliate_id, banner_id) WHERE url IS NULL;
|
|
CREATE UNIQUE INDEX unique_entity_campaigns_affiliate ON campaigns (entity_id, affiliate_id) WHERE banner_id IS NULL AND url IS NULL;
|
|
|
|
CREATE UNIQUE INDEX releases_search_unique ON releases_search (release_id);
|
|
CREATE UNIQUE INDEX movies_search_unique ON movies_search (movie_id);
|
|
CREATE INDEX releases_search_index ON releases_search USING GIN (document);
|
|
CREATE INDEX movies_search_index ON movies_search USING GIN (document);
|
|
CREATE UNIQUE INDEX series_search_unique ON series_search (serie_id);
|
|
CREATE INDEX series_search_index ON series_search USING GIN (document);
|
|
`);
|
|
})
|
|
// FUNCTIONS
|
|
.then(() => { // eslint-disable-line arrow-body-style
|
|
// allow vim fold
|
|
return knex.raw(`
|
|
CREATE EXTENSION IF NOT EXISTS pg_trgm;
|
|
|
|
CREATE FUNCTION current_user_id() RETURNS INTEGER AS $$
|
|
/* if the user ID is undefined, the adapter will pass it as a string, which cannot be cast as NULL by ::integer */
|
|
SELECT NULLIF(current_setting('user.id', true), '')::integer;
|
|
$$ LANGUAGE SQL STABLE;
|
|
|
|
/* We need both the release entries and their search ranking, and PostGraphile does not seem to allow virtual foreign keys on function results.
|
|
* Using a table as a proxy for the search results allows us to get both a reference to the releases table, and the ranking.
|
|
* A composite type does not seem to be compatible with PostGraphile's @sortable, and a view does not allow for many native constraints */
|
|
CREATE TABLE releases_search_results (release_id integer, rank real, FOREIGN KEY (release_id) REFERENCES releases (id));
|
|
CREATE TABLE movies_search_results (movie_id integer, rank real, FOREIGN KEY (movie_id) REFERENCES movies (id));
|
|
|
|
CREATE FUNCTION search_releases(query text) RETURNS SETOF releases_search_results AS $$
|
|
SELECT results.release_id, ts_rank(results.document::tsvector, curate_search_query(query)) as rank
|
|
FROM (
|
|
SELECT releases_search.release_id, document
|
|
FROM releases_search
|
|
WHERE document::tsvector @@ curate_search_query(query)
|
|
) AS results
|
|
ORDER BY rank DESC;
|
|
$$ LANGUAGE SQL STABLE;
|
|
|
|
CREATE FUNCTION search_movies(query text) RETURNS SETOF movies_search_results AS $$
|
|
SELECT movies.id, ranks.rank FROM (
|
|
SELECT
|
|
movies_search.movie_id,
|
|
ts_rank(movies_search.document, to_tsquery('english', array_to_string(array(SELECT * FROM regexp_matches(query, '[A-Za-zÀ-ÖØ-öø-ÿ0-9]+', 'g')), '|'))) AS rank
|
|
FROM movies_search
|
|
) ranks
|
|
LEFT JOIN movies ON movies.id = ranks.movie_id
|
|
WHERE ranks.rank > 0
|
|
ORDER BY ranks.rank DESC;
|
|
$$ LANGUAGE SQL STABLE;
|
|
|
|
CREATE FUNCTION search_entities(search text) RETURNS SETOF entities AS $$
|
|
SELECT * FROM entities
|
|
WHERE
|
|
name ILIKE ('%' || TRIM(search) || '%') OR
|
|
slug ILIKE ('%' || TRIM(search) || '%') OR
|
|
array_to_string(alias, '') ILIKE ('%' || TRIM(search) || '%') OR
|
|
replace(array_to_string(alias, ''), ' ', '') ILIKE ('%' || TRIM(search) || '%') OR
|
|
url ILIKE ('%' || search || '%')
|
|
ORDER BY similarity(entities.name, search) DESC, name ASC
|
|
$$ LANGUAGE SQL STABLE;
|
|
|
|
CREATE FUNCTION search_actors(query text, min_length smallint DEFAULT 2) RETURNS SETOF actors AS $$
|
|
SELECT * FROM actors
|
|
WHERE query IS NULL
|
|
OR length(query) >= min_length
|
|
AND CASE
|
|
WHEN length(query) > 1 THEN name ILIKE ('%' || TRIM(query) || '%')
|
|
WHEN length(query) = 1 THEN name ILIKE (TRIM(query) || '%')
|
|
ELSE true
|
|
END
|
|
$$ LANGUAGE SQL STABLE;
|
|
|
|
CREATE FUNCTION search_tags(query text, min_length smallint DEFAULT 2) RETURNS SETOF tags AS $$
|
|
SELECT aliases.* FROM tags
|
|
LEFT JOIN tags AS aliases ON aliases.id = tags.alias_for OR (tags.alias_for IS NULL AND aliases.id = tags.id)
|
|
WHERE length(query) >= min_length
|
|
AND tags.name ILIKE ('%' || TRIM(query) || '%')
|
|
GROUP BY aliases.id
|
|
ORDER BY similarity(aliases.slug, query) DESC, slug ASC
|
|
$$ LANGUAGE SQL STABLE;
|
|
|
|
CREATE FUNCTION actors_tags(actor actors, selectable_tags text[]) RETURNS SETOF tags AS $$
|
|
SELECT tags.*
|
|
FROM releases_actors
|
|
LEFT JOIN
|
|
releases_tags ON releases_tags.release_id = releases_actors.release_id
|
|
LEFT JOIN
|
|
tags ON tags.id = releases_tags.tag_id
|
|
WHERE
|
|
releases_actors.actor_id = actor.id
|
|
AND
|
|
CASE WHEN array_length(selectable_tags, 1) IS NOT NULL
|
|
THEN tags.slug = ANY(selectable_tags)
|
|
ELSE true
|
|
END
|
|
GROUP BY tags.id
|
|
ORDER BY tags.name;
|
|
$$ LANGUAGE SQL STABLE;
|
|
|
|
CREATE FUNCTION actors_channels(actor actors) RETURNS SETOF entities AS $$
|
|
SELECT entities.*
|
|
FROM releases_actors
|
|
LEFT JOIN releases ON releases.id = releases_actors.release_id
|
|
LEFT JOIN entities ON entities.id = releases.entity_id
|
|
WHERE releases_actors.actor_id = actor.id
|
|
GROUP BY entities.id;
|
|
$$ LANGUAGE SQL STABLE;
|
|
|
|
CREATE FUNCTION actors_actors(actor actors) RETURNS SETOF actors AS $$
|
|
SELECT actors.*
|
|
FROM releases_actors
|
|
LEFT JOIN releases_actors AS associated_actors ON associated_actors.release_id = releases_actors.release_id
|
|
LEFT JOIN actors ON actors.id = associated_actors.actor_id
|
|
WHERE releases_actors.actor_id = actor.id
|
|
AND NOT actors.id = actor.id
|
|
GROUP BY actors.id
|
|
ORDER BY actors.name;
|
|
$$ LANGUAGE SQL STABLE;
|
|
|
|
CREATE FUNCTION entities_scenes(entity entities) RETURNS SETOF releases AS $$
|
|
WITH RECURSIVE children AS (
|
|
SELECT entities.id
|
|
FROM entities
|
|
WHERE entities.id = entity.id
|
|
|
|
UNION ALL
|
|
|
|
SELECT entities.id
|
|
FROM entities
|
|
INNER JOIN children ON children.id = entities.parent_id
|
|
)
|
|
|
|
SELECT releases FROM releases
|
|
INNER JOIN children ON children.id = releases.entity_id
|
|
|
|
UNION
|
|
|
|
SELECT releases FROM releases
|
|
INNER JOIN children ON children.id = releases.studio_id;
|
|
$$ LANGUAGE SQL STABLE;
|
|
|
|
CREATE OR REPLACE FUNCTION entities_scene_total(entity entities) RETURNS bigint AS $$
|
|
SELECT COUNT(id)
|
|
FROM releases
|
|
WHERE releases.entity_id = entity.id;
|
|
$$ LANGUAGE SQL STABLE;
|
|
|
|
CREATE FUNCTION entities_scene_tags(entity entities, selectable_tags text[]) RETURNS SETOF tags AS $$
|
|
SELECT tags.*
|
|
FROM releases
|
|
LEFT JOIN
|
|
releases_tags ON releases_tags.release_id = releases.id
|
|
LEFT JOIN
|
|
tags ON tags.id = releases_tags.tag_id
|
|
WHERE
|
|
releases.entity_id = entity.id
|
|
AND
|
|
CASE WHEN array_length(selectable_tags, 1) IS NOT NULL
|
|
THEN tags.slug = ANY(selectable_tags)
|
|
ELSE true
|
|
END
|
|
GROUP BY tags.id
|
|
ORDER BY tags.name;
|
|
$$ LANGUAGE SQL STABLE;
|
|
|
|
/* GraphQL/Postgraphile 'every' applies to the data, will only include scenes for which every assigned tag is selected,
|
|
instead of what we want; scenes with every selected tag, but possibly also some others */
|
|
CREATE FUNCTION actors_scenes(actor actors, selected_tags text[], mode text DEFAULT 'all') RETURNS SETOF releases AS $$
|
|
SELECT releases.*
|
|
FROM releases
|
|
LEFT JOIN
|
|
releases_actors ON releases_actors.release_id = releases.id
|
|
LEFT JOIN
|
|
releases_tags ON releases_tags.release_id = releases.id
|
|
LEFT JOIN
|
|
tags ON tags.id = releases_tags.tag_id
|
|
WHERE releases_actors.actor_id = actor.id
|
|
AND CASE
|
|
/* match at least one of the selected tags */
|
|
WHEN mode = 'any'
|
|
AND array_length(selected_tags, 1) > 0
|
|
THEN tags.slug = ANY(selected_tags)
|
|
ELSE true
|
|
END
|
|
GROUP BY releases.id
|
|
HAVING CASE
|
|
/* match all of the selected tags */
|
|
WHEN mode = 'all'
|
|
AND array_length(selected_tags, 1) > 0
|
|
THEN COUNT(
|
|
CASE WHEN tags.slug = ANY(selected_tags)
|
|
THEN true
|
|
END
|
|
) = array_length(selected_tags, 1)
|
|
ELSE true
|
|
END;
|
|
$$ LANGUAGE SQL STABLE;
|
|
|
|
CREATE FUNCTION tags_scenes(tag tags, selected_tags text[], mode text DEFAULT 'all') RETURNS SETOF releases AS $$
|
|
SELECT releases.*
|
|
FROM releases
|
|
LEFT JOIN
|
|
releases_actors ON releases_actors.release_id = releases.id
|
|
LEFT JOIN
|
|
releases_tags ON releases_tags.release_id = releases.id
|
|
LEFT JOIN
|
|
tags ON tags.id = releases_tags.tag_id
|
|
WHERE releases_tags.tag_id = tag.id
|
|
GROUP BY releases.id;
|
|
$$ LANGUAGE SQL STABLE;
|
|
|
|
CREATE FUNCTION movies_actors(movie movies) RETURNS SETOF actors AS $$
|
|
SELECT actors.*
|
|
FROM movies_scenes
|
|
LEFT JOIN
|
|
releases ON releases.id = movies_scenes.scene_id
|
|
LEFT JOIN
|
|
releases_actors ON releases_actors.release_id = releases.id
|
|
LEFT JOIN
|
|
actors ON actors.id = releases_actors.actor_id
|
|
WHERE movies_scenes.movie_id = movie.id
|
|
AND actors.id IS NOT NULL
|
|
GROUP BY actors.id
|
|
ORDER BY actors.name, actors.gender
|
|
$$ LANGUAGE SQL STABLE;
|
|
|
|
CREATE FUNCTION movies_tags(movie movies) RETURNS SETOF tags AS $$
|
|
SELECT tags.*
|
|
FROM movies_scenes
|
|
LEFT JOIN
|
|
releases ON releases.id = movies_scenes.scene_id
|
|
LEFT JOIN
|
|
releases_tags ON releases_tags.release_id = releases.id
|
|
LEFT JOIN
|
|
tags ON tags.id = releases_tags.tag_id
|
|
WHERE movies_scenes.movie_id = movie.id
|
|
AND tags.id IS NOT NULL
|
|
GROUP BY tags.id
|
|
ORDER BY tags.priority DESC
|
|
$$ LANGUAGE SQL STABLE;
|
|
|
|
CREATE FUNCTION movies_scenes_photos(movie movies) RETURNS SETOF media AS $$
|
|
SELECT media.*
|
|
FROM movies_scenes
|
|
LEFT JOIN
|
|
releases ON releases.id = movies_scenes.scene_id
|
|
INNER JOIN
|
|
releases_photos ON releases_photos.release_id = releases.id
|
|
LEFT JOIN
|
|
media ON media.id = releases_photos.media_id
|
|
WHERE movies_scenes.movie_id = movie.id
|
|
GROUP BY media.id
|
|
ORDER BY media.index ASC
|
|
$$ LANGUAGE SQL STABLE;
|
|
|
|
CREATE FUNCTION releases_is_new(release releases) RETURNS boolean AS $$
|
|
SELECT EXISTS(SELECT true WHERE (SELECT id FROM batches ORDER BY created_at DESC LIMIT 1) = release.created_batch_id);
|
|
$$ LANGUAGE sql STABLE;
|
|
|
|
CREATE FUNCTION series_actors(serie series) RETURNS SETOF actors AS $$
|
|
SELECT actors.*
|
|
FROM series_scenes
|
|
LEFT JOIN
|
|
releases ON releases.id = series_scenes.scene_id
|
|
LEFT JOIN
|
|
releases_actors ON releases_actors.release_id = releases.id
|
|
LEFT JOIN
|
|
actors ON actors.id = releases_actors.actor_id
|
|
WHERE series_scenes.serie_id = serie.id
|
|
AND actors.id IS NOT NULL
|
|
GROUP BY actors.id
|
|
ORDER BY actors.name, actors.gender
|
|
$$ LANGUAGE SQL STABLE;
|
|
|
|
CREATE FUNCTION series_tags(serie series) RETURNS SETOF tags AS $$
|
|
SELECT tags.*
|
|
FROM series_scenes
|
|
LEFT JOIN
|
|
releases ON releases.id = series_scenes.scene_id
|
|
LEFT JOIN
|
|
releases_tags ON releases_tags.release_id = releases.id
|
|
LEFT JOIN
|
|
tags ON tags.id = releases_tags.tag_id
|
|
WHERE series_scenes.serie_id = serie.id
|
|
AND tags.id IS NOT NULL
|
|
GROUP BY tags.id
|
|
ORDER BY tags.priority DESC
|
|
$$ LANGUAGE SQL STABLE;
|
|
|
|
CREATE FUNCTION series_scenes_photos(serie series) RETURNS SETOF media AS $$
|
|
SELECT media.*
|
|
FROM series_scenes
|
|
LEFT JOIN
|
|
releases ON releases.id = series_scenes.scene_id
|
|
INNER JOIN
|
|
releases_photos ON releases_photos.release_id = releases.id
|
|
LEFT JOIN
|
|
media ON media.id = releases_photos.media_id
|
|
WHERE series_scenes.serie_id = serie.id
|
|
GROUP BY media.id
|
|
ORDER BY media.index ASC
|
|
$$ LANGUAGE SQL STABLE;
|
|
|
|
CREATE FUNCTION get_random_campaign(min_ratio decimal default 0, max_ratio decimal default 1000.0) RETURNS random_campaign AS $$
|
|
SELECT * FROM (
|
|
SELECT DISTINCT ON (CASE WHEN parent_id IS NOT NULL THEN parent_id ELSE entity_id END)
|
|
id, banner_id, url, entity_id, affiliate_id, parent_id
|
|
FROM (
|
|
SELECT
|
|
campaigns.*, entities.parent_id as parent_id
|
|
FROM campaigns
|
|
LEFT JOIN entities ON entities.id = campaigns.entity_id
|
|
LEFT JOIN banners ON banners.id = campaigns.banner_id
|
|
WHERE banner_id IS NOT NULL
|
|
AND ratio >= min_ratio
|
|
AND ratio <= max_ratio
|
|
ORDER BY RANDOM()
|
|
) random_campaigns
|
|
) random_banners
|
|
ORDER BY RANDOM()
|
|
LIMIT 1;
|
|
$$ LANGUAGE SQL STABLE;
|
|
`);
|
|
})
|
|
// VIEWS AND COMMENTS
|
|
.then(() => { // eslint-disable-line arrow-body-style
|
|
// allow vim fold
|
|
return knex.raw(`
|
|
CREATE MATERIALIZED VIEW releases_not_showcased AS (
|
|
SELECT releases.id AS release_id FROM releases
|
|
LEFT JOIN entities AS channels ON channels.id = releases.entity_id
|
|
LEFT JOIN entities AS studios ON studios.id = releases.studio_id
|
|
LEFT JOIN entities AS networks ON networks.id = channels.parent_id
|
|
WHERE (studios.showcased = false)
|
|
OR (channels.showcased = false AND studios.showcased IS NOT true)
|
|
OR (networks.showcased = false AND channels.showcased IS NOT true AND studios.showcased IS NOT true)
|
|
);
|
|
|
|
CREATE UNIQUE INDEX ON releases_not_showcased (release_id);
|
|
COMMENT ON MATERIALIZED VIEW releases_not_showcased IS E'@foreignKey (release_id) references releases (id)';
|
|
|
|
COMMENT ON COLUMN users.password IS E'@omit';
|
|
COMMENT ON COLUMN users.email IS E'@omit';
|
|
COMMENT ON COLUMN users.email_verified IS E'@omit';
|
|
COMMENT ON COLUMN users.abilities IS E'@omit';
|
|
|
|
COMMENT ON COLUMN actors.height IS E'@omit read,update,create,delete,all,many';
|
|
COMMENT ON COLUMN actors.weight IS E'@omit read,update,create,delete,all,many';
|
|
COMMENT ON COLUMN actors.penis_length IS E'@omit read,update,create,delete,all,many';
|
|
COMMENT ON COLUMN actors.penis_girth IS E'@omit read,update,create,delete,all,many';
|
|
|
|
COMMENT ON FUNCTION entities_scenes IS E'@sortable';
|
|
|
|
COMMENT ON FUNCTION actors_tags IS E'@sortable';
|
|
COMMENT ON FUNCTION actors_channels IS E'@sortable';
|
|
COMMENT ON FUNCTION actors_actors IS E'@sortable';
|
|
COMMENT ON FUNCTION actors_scenes IS E'@sortable';
|
|
COMMENT ON FUNCTION tags_scenes IS E'@sortable';
|
|
|
|
COMMENT ON FUNCTION search_releases IS E'@sortable';
|
|
COMMENT ON FUNCTION search_entities IS E'@sortable';
|
|
COMMENT ON FUNCTION search_actors IS E'@sortable';
|
|
COMMENT ON FUNCTION search_movies IS E'@sortable';
|
|
COMMENT ON FUNCTION search_tags IS E'@sortable';
|
|
`);
|
|
})
|
|
// POLICIES
|
|
.then(() => { // eslint-disable-line arrow-body-style
|
|
// allow vim fold
|
|
return knex.raw(`
|
|
GRANT ALL ON ALL TABLES IN SCHEMA public TO :visitor;
|
|
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO :visitor;
|
|
|
|
REVOKE ALL ON users FROM :visitor;
|
|
GRANT SELECT (id, username, role, identity_verified, created_at) ON users TO :visitor;
|
|
|
|
ALTER TABLE stashes ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE stashes_scenes ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE stashes_movies ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE stashes_actors ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE stashes_series ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY stashes_policy_select ON stashes FOR SELECT USING (stashes.public OR stashes.user_id = current_user_id());
|
|
CREATE POLICY stashes_policy_update ON stashes FOR UPDATE USING (stashes.public OR stashes.user_id = current_user_id());
|
|
CREATE POLICY stashes_policy_delete ON stashes FOR DELETE USING (stashes.public OR stashes.user_id = current_user_id());
|
|
CREATE POLICY stashes_policy_insert ON stashes FOR INSERT WITH CHECK (true);
|
|
|
|
CREATE POLICY stashes_policy ON stashes_scenes
|
|
USING (EXISTS (
|
|
SELECT *
|
|
FROM stashes
|
|
WHERE stashes.id = stashes_scenes.stash_id
|
|
AND (stashes.user_id = current_user_id() OR stashes.public)
|
|
));
|
|
|
|
CREATE POLICY stashes_policy ON stashes_movies
|
|
USING (EXISTS (
|
|
SELECT *
|
|
FROM stashes
|
|
WHERE stashes.id = stashes_movies.stash_id
|
|
AND (stashes.user_id = current_user_id() OR stashes.public)
|
|
));
|
|
|
|
CREATE POLICY stashes_policy ON stashes_actors
|
|
USING (EXISTS (
|
|
SELECT *
|
|
FROM stashes
|
|
WHERE stashes.id = stashes_actors.stash_id
|
|
AND (stashes.user_id = current_user_id() OR stashes.public)
|
|
));
|
|
|
|
CREATE POLICY stashes_policy ON stashes_series
|
|
USING (EXISTS (
|
|
SELECT *
|
|
FROM stashes
|
|
WHERE stashes.id = stashes_series.stash_id
|
|
AND (stashes.user_id = current_user_id() OR stashes.public)
|
|
));
|
|
|
|
ALTER TABLE alerts ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE alerts_tags ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE alerts_scenes ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE alerts_actors ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE alerts_entities ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE alerts_stashes ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY alerts_policy_select ON alerts FOR SELECT USING (alerts.user_id = current_user_id());
|
|
CREATE POLICY alerts_policy_update ON alerts FOR UPDATE USING (alerts.user_id = current_user_id());
|
|
CREATE POLICY alerts_policy_delete ON alerts FOR DELETE USING (alerts.user_id = current_user_id());
|
|
CREATE POLICY alerts_policy_insert ON alerts FOR INSERT WITH CHECK (true);
|
|
|
|
CREATE POLICY alerts_policy ON alerts_scenes
|
|
USING (EXISTS (
|
|
SELECT *
|
|
FROM alerts
|
|
WHERE alerts.id = alerts_scenes.alert_id
|
|
AND alerts.user_id = current_user_id()
|
|
));
|
|
|
|
CREATE POLICY alerts_policy ON alerts_actors
|
|
USING (EXISTS (
|
|
SELECT *
|
|
FROM alerts
|
|
WHERE alerts.id = alerts_actors.alert_id
|
|
AND alerts.user_id = current_user_id()
|
|
));
|
|
|
|
CREATE POLICY alerts_policy ON alerts_entities
|
|
USING (EXISTS (
|
|
SELECT *
|
|
FROM alerts
|
|
WHERE alerts.id = alerts_entities.alert_id
|
|
AND alerts.user_id = current_user_id()
|
|
));
|
|
|
|
CREATE POLICY alerts_policy ON alerts_tags
|
|
USING (EXISTS (
|
|
SELECT *
|
|
FROM alerts
|
|
WHERE alerts.id = alerts_tags.alert_id
|
|
AND alerts.user_id = current_user_id()
|
|
));
|
|
|
|
CREATE POLICY alerts_policy ON alerts_stashes
|
|
USING (EXISTS (
|
|
SELECT *
|
|
FROM alerts
|
|
WHERE alerts.id = alerts_stashes.alert_id
|
|
AND alerts.user_id = current_user_id()
|
|
));
|
|
|
|
ALTER TABLE notifications ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY notifications_policy_select ON notifications FOR SELECT USING (notifications.user_id = current_user_id());
|
|
CREATE POLICY notifications_policy_update ON notifications FOR UPDATE USING (notifications.user_id = current_user_id());
|
|
CREATE POLICY notifications_policy_delete ON notifications FOR DELETE USING (notifications.user_id = current_user_id());
|
|
CREATE POLICY notifications_policy_insert ON notifications FOR INSERT WITH CHECK (true);
|
|
|
|
ALTER TABLE releases_photos ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY releases_photos_select ON releases_photos FOR SELECT USING (current_user_id() IS NOT NULL);
|
|
|
|
ALTER TABLE releases_trailers ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY releases_trailers_select ON releases_trailers FOR SELECT USING (current_user_id() IS NOT NULL);
|
|
`, {
|
|
visitor: knex.raw(config.database.query.user),
|
|
});
|
|
});
|
|
|
|
exports.down = (knex) => { // eslint-disable-line arrow-body-style
|
|
// allow vim fold
|
|
return knex.raw(`
|
|
DROP TABLE IF EXISTS releases_actors CASCADE;
|
|
DROP TABLE IF EXISTS releases_movies CASCADE;
|
|
DROP TABLE IF EXISTS releases_directors CASCADE;
|
|
DROP TABLE IF EXISTS releases_posters CASCADE;
|
|
DROP TABLE IF EXISTS releases_photos CASCADE;
|
|
DROP TABLE IF EXISTS releases_covers CASCADE;
|
|
DROP TABLE IF EXISTS releases_trailers CASCADE;
|
|
DROP TABLE IF EXISTS releases_teasers CASCADE;
|
|
DROP TABLE IF EXISTS releases_tags CASCADE;
|
|
DROP TABLE IF EXISTS releases_search CASCADE;
|
|
DROP TABLE IF EXISTS movies_search CASCADE;
|
|
|
|
DROP TABLE IF EXISTS movies_scenes CASCADE;
|
|
DROP TABLE IF EXISTS movies_covers CASCADE;
|
|
DROP TABLE IF EXISTS movies_posters CASCADE;
|
|
DROP TABLE IF EXISTS movies_photos CASCADE;
|
|
DROP TABLE IF EXISTS movies_trailers CASCADE;
|
|
|
|
DROP TABLE IF EXISTS stashes_series CASCADE;
|
|
DROP TABLE IF EXISTS series_scenes CASCADE;
|
|
DROP TABLE IF EXISTS series_trailers CASCADE;
|
|
DROP TABLE IF EXISTS series_posters CASCADE;
|
|
DROP TABLE IF EXISTS series_covers CASCADE;
|
|
DROP TABLE IF EXISTS series_photos CASCADE;
|
|
DROP TABLE IF EXISTS series_search CASCADE;
|
|
|
|
DROP TABLE IF EXISTS clips_tags CASCADE;
|
|
DROP TABLE IF EXISTS clips_posters CASCADE;
|
|
DROP TABLE IF EXISTS clips_photos CASCADE;
|
|
|
|
DROP TABLE IF EXISTS chapters_tags CASCADE;
|
|
DROP TABLE IF EXISTS chapters_posters CASCADE;
|
|
DROP TABLE IF EXISTS chapters_photos CASCADE;
|
|
|
|
DROP TABLE IF EXISTS banners_tags CASCADE;
|
|
DROP TABLE IF EXISTS banners CASCADE;
|
|
DROP TABLE IF EXISTS campaigns CASCADE;
|
|
DROP TABLE IF EXISTS random_campaign CASCADE;
|
|
DROP TABLE IF EXISTS affiliates CASCADE;
|
|
|
|
DROP TABLE IF EXISTS batches CASCADE;
|
|
|
|
DROP TABLE IF EXISTS actors_avatars CASCADE;
|
|
DROP TABLE IF EXISTS actors_photos CASCADE;
|
|
DROP TABLE IF EXISTS actors_social CASCADE;
|
|
DROP TABLE IF EXISTS actors_profiles CASCADE;
|
|
DROP TABLE IF EXISTS actors_tattoos CASCADE;
|
|
DROP TABLE IF EXISTS actors_piercings CASCADE;
|
|
DROP TABLE IF EXISTS body CASCADE;
|
|
|
|
DROP TABLE IF EXISTS entities_tags CASCADE;
|
|
DROP TABLE IF EXISTS entities_social CASCADE;
|
|
|
|
DROP TABLE IF EXISTS sites_tags CASCADE;
|
|
DROP TABLE IF EXISTS sites_social CASCADE;
|
|
DROP TABLE IF EXISTS networks_social CASCADE;
|
|
DROP TABLE IF EXISTS tags_posters CASCADE;
|
|
DROP TABLE IF EXISTS tags_photos CASCADE;
|
|
DROP TABLE IF EXISTS movies CASCADE;
|
|
DROP TABLE IF EXISTS clips CASCADE;
|
|
DROP TABLE IF EXISTS chapters CASCADE;
|
|
DROP TABLE IF EXISTS series CASCADE;
|
|
DROP TABLE IF EXISTS releases CASCADE;
|
|
DROP TABLE IF EXISTS actors CASCADE;
|
|
DROP TABLE IF EXISTS tags CASCADE;
|
|
DROP TABLE IF EXISTS tags_groups CASCADE;
|
|
DROP TABLE IF EXISTS social CASCADE;
|
|
DROP TABLE IF EXISTS sites CASCADE;
|
|
DROP TABLE IF EXISTS studios CASCADE;
|
|
DROP TABLE IF EXISTS media_sfw CASCADE;
|
|
DROP TABLE IF EXISTS media CASCADE;
|
|
DROP TABLE IF EXISTS countries CASCADE;
|
|
DROP TABLE IF EXISTS networks CASCADE;
|
|
|
|
DROP TABLE IF EXISTS entities_types CASCADE;
|
|
DROP TABLE IF EXISTS entities CASCADE;
|
|
|
|
DROP TABLE IF EXISTS notifications CASCADE;
|
|
|
|
DROP TABLE IF EXISTS stashes_scenes CASCADE;
|
|
DROP TABLE IF EXISTS stashes_movies CASCADE;
|
|
DROP TABLE IF EXISTS stashes_actors CASCADE;
|
|
DROP TABLE IF EXISTS stashes CASCADE;
|
|
|
|
DROP TABLE IF EXISTS alerts_scenes CASCADE;
|
|
DROP TABLE IF EXISTS alerts_actors CASCADE;
|
|
DROP TABLE IF EXISTS alerts_tags CASCADE;
|
|
DROP TABLE IF EXISTS alerts_entities CASCADE;
|
|
DROP TABLE IF EXISTS alerts_stashes CASCADE;
|
|
DROP TABLE IF EXISTS alerts CASCADE;
|
|
|
|
DROP TABLE IF EXISTS users CASCADE;
|
|
DROP TABLE IF EXISTS users_roles CASCADE;
|
|
|
|
DROP FUNCTION IF EXISTS search_releases;
|
|
DROP FUNCTION IF EXISTS search_sites;
|
|
DROP FUNCTION IF EXISTS search_entities;
|
|
DROP FUNCTION IF EXISTS search_actors;
|
|
DROP FUNCTION IF EXISTS search_movies;
|
|
DROP FUNCTION IF EXISTS search_tags;
|
|
DROP FUNCTION IF EXISTS get_random_sfw_media_id;
|
|
|
|
DROP FUNCTION IF EXISTS releases_is_new;
|
|
DROP FUNCTION IF EXISTS entities_scenes;
|
|
DROP FUNCTION IF EXISTS entities_scene_total;
|
|
DROP FUNCTION IF EXISTS entities_scene_tags;
|
|
DROP FUNCTION IF EXISTS actors_tags;
|
|
DROP FUNCTION IF EXISTS actors_channels;
|
|
DROP FUNCTION IF EXISTS actors_actors;
|
|
DROP FUNCTION IF EXISTS actors_scenes;
|
|
|
|
DROP FUNCTION IF EXISTS movies_actors;
|
|
DROP FUNCTION IF EXISTS movies_tags;
|
|
DROP FUNCTION IF EXISTS movies_scenes_photos;
|
|
|
|
DROP FUNCTION IF EXISTS series_actors;
|
|
DROP FUNCTION IF EXISTS series_tags;
|
|
DROP FUNCTION IF EXISTS series_scenes_photos;
|
|
|
|
DROP POLICY IF EXISTS stashes_policy ON stashes;
|
|
DROP POLICY IF EXISTS stashes_policy ON stashes_scenes;
|
|
DROP POLICY IF EXISTS stashes_policy ON stashes_actors;
|
|
|
|
DROP FUNCTION IF EXISTS current_user_id;
|
|
DROP FUNCTION IF EXISTS get_random_campaign;
|
|
|
|
DROP TABLE IF EXISTS releases_search_results;
|
|
DROP TABLE IF EXISTS movies_search_results;
|
|
`);
|
|
};
|