src/database/tables.js

   1 const LOGGER = require('@calzoneman/jsli')('database/tables');
   2
   3
   4 export async function initTables() {
   5     const knex = require('../database').getDB().knex;
   6
   7     async function ensureTable(name, structure) {
   8         if (!await knex.schema.hasTable(name)) {
   9             LOGGER.info('Creating table %s', name);
  10             await knex.schema.createTable(name, structure);
  11         }
  12     }
  13
  14     // TODO: consider un-utf8ing columns that are always ASCII
  15     // Leaving for now for backwards compatibility
  16
  17     // TODO: enforce foreign key constraints for tables missing them
  18
  19     await ensureTable('users', t => {
  20         t.charset('utf8');
  21         t.increments('id').notNullable().primary();
  22         t.string('name', 20).notNullable().unique();
  23         t.string('password', 60).notNullable();
  24         t.integer('global_rank').notNullable();
  25         t.string('email', 255);
  26         // UTF8MB4 required for non-BMP Unicode -- Just MySQL things (tm)
  27         t.specificType('profile', 'text character set utf8mb4 not null');
  28         t.string('ip', 39).notNullable();
  29         // Registration time, TODO convert to timestamp
  30         t.bigint('time').notNullable();
  31         t.string('name_dedupe', 20).defaultTo(null);
  32         t.boolean('inactive').defaultTo(false);
  33     });
  34
  35     await ensureTable('channels', t => {
  36         t.charset('utf8');
  37         t.increments('id').notNullable().primary();
  38         t.string('name', 30).notNullable().unique();
  39         t.string('owner', 20).notNullable().index();
  40         // Registration time, TODO convert to timestamp
  41         t.bigInteger('time').notNullable();
  42         t.timestamp('last_loaded').notNullable()
  43                 .defaultTo(knex.raw('0'));
  44         t.timestamp('owner_last_seen').notNullable()
  45                 .defaultTo(knex.raw('0'));
  46     });
  47
  48     await ensureTable('channel_data', t => {
  49         t.charset('utf8');
  50         t.integer('channel_id').notNullable()
  51                 .unsigned()
  52                 .references('id').inTable('channels')
  53                 .onDelete('cascade');
  54         t.string('key', 20).notNullable();
  55         t.specificType('value', 'mediumtext character set utf8mb4 not null');
  56         t.primary(['channel_id', 'key']);
  57     });
  58
  59     await ensureTable('global_bans', t => {
  60         t.charset('utf8');
  61         t.string('ip', 39).notNullable().primary();
  62         t.string('reason', 255).notNullable();
  63     });
  64
  65     await ensureTable('password_reset', t => {
  66         t.charset('utf8');
  67         t.string('ip', 39).notNullable();
  68         t.string('name', 20).notNullable().primary();
  69         t.string('hash', 64).notNullable();
  70         t.string('email', 255).notNullable();
  71         // TODO consider converting to timestamp
  72         t.bigint('expire').notNullable();
  73     });
  74
  75     await ensureTable('user_playlists', t => {
  76         t.charset('utf8');
  77         t.string('user', 20).notNullable();
  78         t.string('name', 255).notNullable();
  79         t.specificType('contents', 'mediumtext character set utf8mb4 not null');
  80         t.integer('count').notNullable();
  81         t.integer('duration').notNullable();
  82         t.primary(['user', 'name']);
  83     });
  84
  85     await ensureTable('aliases', t => {
  86         t.charset('utf8');
  87         t.increments('visit_id').notNullable().primary();
  88         t.string('ip', 39).notNullable().index();
  89         t.string('name', 20).notNullable();
  90         // TODO consider converting to timestamp
  91         t.bigint('time').notNullable();
  92     });
  93
  94     await ensureTable('meta', t => {
  95         t.charset('utf8');
  96         t.string('key', 255).notNullable().primary();
  97         t.text('value').notNullable();
  98     });
  99
 100     await ensureTable('channel_libraries', t => {
 101         t.charset('utf8');
 102         t.string('id', 255).notNullable();
 103         t.specificType('title', 'varchar(255) character set utf8mb4 not null');
 104         t.integer('seconds').notNullable();
 105         t.string('type', 2).notNullable();
 106         t.text('meta').notNullable();
 107         t.string('channel', 30).notNullable();
 108         t.primary(['id', 'channel']);
 109         // TODO replace title index with FTS or elasticsearch or something
 110         t.index(['channel', knex.raw('`title`(227)')], 'channel_libraries_channel_title');
 111     });
 112
 113     await ensureTable('channel_ranks', t => {
 114         t.charset('utf8');
 115         t.string('name', 20).notNullable();
 116         t.integer('rank').notNullable();
 117         t.string('channel', 30).notNullable();
 118         t.primary(['name', 'channel']);
 119     });
 120
 121     await ensureTable('channel_bans', t => {
 122         t.charset('utf8');
 123         t.increments('id').notNullable().primary();
 124         t.string('ip', 39).notNullable();
 125         t.string('name', 20).notNullable();
 126         t.string('bannedby', 20).notNullable();
 127         t.specificType('reason', 'varchar(255) character set utf8mb4 not null');
 128         t.string('channel', 30).notNullable();
 129         t.unique(['name', 'ip', 'channel']);
 130         t.index(['ip', 'channel']);
 131         t.index(['name', 'channel']);
 132     });
 133
 134     await ensureTable('user_deletion_requests', t => {
 135         t.increments('request_id').notNullable().primary();
 136         t.integer('user_id')
 137             .unsigned()
 138             .notNullable()
 139             .references('id').inTable('users')
 140             .onDelete('cascade')
 141             .unique();
 142         t.timestamps(/* useTimestamps */ true, /* defaultToNow */ true);
 143         t.index('created_at');
 144     });
 145
 146     await ensureTable('media_metadata_cache', t => {
 147         // The types of id and type are chosen for compatibility
 148         // with the existing channel_libraries table.
 149         // TODO in the future schema, revisit the ID layout for different media types.
 150         t.charset('utf8');
 151         t.string('id', 255).notNullable();
 152         t.string('type', 2).notNullable();
 153         t.text('metadata').notNullable();
 154         t.timestamps(/* useTimestamps */ true, /* defaultToNow */ true);
 155
 156         t.primary(['type', 'id']);
 157         t.index('updated_at');
 158     });
 159 }