traxxx/migrations/20220327230125_movie_photos.js

const config = require('config');

exports.up = async (knex) => Promise.resolve()
	.then(() => knex.raw(`
		ALTER FUNCTION movies_photos(movie movies) RENAME TO movies_scenes_photos;
		ALTER FUNCTION series_photos(serie series) RENAME TO series_scenes_photos;
	`))
	.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('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.raw(`
		GRANT ALL ON ALL TABLES IN SCHEMA public TO :visitor;
		GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO :visitor;
	`, {
		visitor: knex.raw(config.database.query.user),
	}));

exports.down = async (knex) => knex.raw(`
	DROP TABLE IF EXISTS movies_photos CASCADE;
	DROP TABLE IF EXISTS series_photos CASCADE;

	ALTER FUNCTION movies_scenes_photos(movie movies) RENAME TO movies_photos;
	ALTER FUNCTION series_scenes_photos(serie series) RENAME TO series_photos;
`);