From e238eab45f1f5b6f3dbf40ddff59b9db804ffb4f Mon Sep 17 00:00:00 2001 From: Ran Guin Date: Mon, 19 Jul 2021 12:19:18 -0700 Subject: update migrations --- db/migrations/20210612181508_init_referrals.js | 21 +++++++ .../20210612192804_init_waiting_list.js.js | 19 ++++++ db/migrations/20210612203333_schema_diff_merges.js | 39 +++++++++++++ .../20210625232223_add_invoice_id_to_payments.js | 15 +++-- db/migrations/20210713174217_add_admin_to_users.js | 13 +++-- db/migrations/20210714123456_add_fks_merge.js | 38 ++++++++++++ db/migrations/s2/user_adjustments.js | 67 ++++++++++++++++++++++ 7 files changed, 202 insertions(+), 10 deletions(-) create mode 100644 db/migrations/20210612181508_init_referrals.js create mode 100644 db/migrations/20210612192804_init_waiting_list.js.js create mode 100644 db/migrations/20210612203333_schema_diff_merges.js create mode 100644 db/migrations/20210714123456_add_fks_merge.js create mode 100644 db/migrations/s2/user_adjustments.js (limited to 'db') diff --git a/db/migrations/20210612181508_init_referrals.js b/db/migrations/20210612181508_init_referrals.js new file mode 100644 index 00000000..875d167f --- /dev/null +++ b/db/migrations/20210612181508_init_referrals.js @@ -0,0 +1,21 @@ +exports.up = function(knex) { + return knex.schema.createTable('referrals', function(t) { + t.increments('id').unsigned().primary(); + t.integer('user_id').nullable(); + t.foreign('user_id').references('users.id'); + + t.integer('sponsor_id').notNull(); + t.foreign('sponsor_id').references('users.id'); + + t.string('token').notNull(); + t.string('expiry').nullable(); + t.enum('status', ['available', 'used', 'expired', 'cancelled']).notNull().defaultTo('available'); + + t.timestamps(); + }) + }; + + exports.down = function(knex) { + return knex.schema.dropTable('referrals'); + }; + \ No newline at end of file diff --git a/db/migrations/20210612192804_init_waiting_list.js.js b/db/migrations/20210612192804_init_waiting_list.js.js new file mode 100644 index 00000000..65d3eb42 --- /dev/null +++ b/db/migrations/20210612192804_init_waiting_list.js.js @@ -0,0 +1,19 @@ +exports.up = function(knex) { + return knex.schema.createTable('waiting_list', function(t) { + t.increments('id').unsigned().primary(); + + t.string('email').notNull(); + t.string('phone').notNull(); + t.enu('status', ['pending', 'activated', 'expired', 'cancelled']).notNull(); + + t.integer('user_id').nullable(); + t.foreign('user_id').references('users.id'); + t.text('notes').nullable(); + + t.timestamps(); + }) +} + +exports.down = function(knex) { + return knex.schema.dropTable('waiting_list'); +} \ No newline at end of file diff --git a/db/migrations/20210612203333_schema_diff_merges.js b/db/migrations/20210612203333_schema_diff_merges.js new file mode 100644 index 00000000..a853f449 --- /dev/null +++ b/db/migrations/20210612203333_schema_diff_merges.js @@ -0,0 +1,39 @@ +exports.up = function(knex) { + return Promise.all([ + knex.schema.dropTableIfExists('reset'), + knex.schema.dropTableIfExists('naughty'), + knex.schema.dropTableIfExists('cheaters'), + + knex.raw("UPDATE accounts set createdAt = '2020-01-01' where createdAt < '2020-01-01'"), + knex.raw("UPDATE accounts set updatedAt = '2020-01-01' where updatedAt < '2020-01-01'"), + knex.raw("ALTER TABLE accounts modify `contract` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL"), + + knex.schema.alterTable('orders', function(t) { + t.dropColumn('a1'); + t.dropColumn('a2'); + }), + + knex.schema.alterTable("users", function(t) { + t.string('authyId').nullable(); + t.dropColumn('symbol'); + }), + + knex.raw("ALTER TABLE users MODIFY `subscriptions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL"), + + ]) +} + +exports.down = async function(knex) { + return Promise.all([ + knex.schema.table("orders", function(t) { + t.integer("a1"); + t.integer("a2") + }), + + knex.schema.table("users", function(t) { + t.string("symbol"); + t.dropColumn("unit"); + t.dropColumn("authyId"); + }), + ]) +} \ No newline at end of file diff --git a/db/migrations/20210625232223_add_invoice_id_to_payments.js b/db/migrations/20210625232223_add_invoice_id_to_payments.js index 46e2a82e..d8f70a6f 100644 --- a/db/migrations/20210625232223_add_invoice_id_to_payments.js +++ b/db/migrations/20210625232223_add_invoice_id_to_payments.js @@ -1,10 +1,13 @@ -exports.up = async knex => - knex.schema.table("payments", function(table) { +exports.up = async function (knex) { + return knex.schema.table("payments", function(table) { table.integer("invoice_id"); table.foreign("invoice_id").references("invoices.id"); - }); + }) +} -exports.down = async knex => - knex.schema.table("payments", function(table) { +exports.down = async function (knex) { + return knex.schema.table("payments", function(table) { + table.dropForeign("invoice_id"); table.dropColumn("invoice_id"); - }); + }) +} diff --git a/db/migrations/20210713174217_add_admin_to_users.js b/db/migrations/20210713174217_add_admin_to_users.js index c8e009b1..05b1bbc9 100644 --- a/db/migrations/20210713174217_add_admin_to_users.js +++ b/db/migrations/20210713174217_add_admin_to_users.js @@ -1,11 +1,16 @@ exports.up = async function(knex) { - return knex.schema.table("users", function(table) { - table.boolean("admin"); + return knex.schema.table("users", function(t) { + t.boolean("admin"); + t.string('email'); + t.string('phone'); + }); }; exports.down = async function(knex) { - return knex.schema.table("users", function(table) { - table.dropColumn("admin"); + return knex.schema.table("users", function(t) { + t.dropColumn("admin"); + t.dropColumn('email') + t.dropColumn('phone') }); }; diff --git a/db/migrations/20210714123456_add_fks_merge.js b/db/migrations/20210714123456_add_fks_merge.js new file mode 100644 index 00000000..a13ea5c9 --- /dev/null +++ b/db/migrations/20210714123456_add_fks_merge.js @@ -0,0 +1,38 @@ +exports.up = function(knex) { + return Promise.all([ + + knex.schema.alterTable("invoices", function(t) { + t.foreign('user_id').references('users.id') + t.foreign('account_id').references('accounts.id') + }), + + knex.schema.alterTable("payments", function(t) { + t.foreign('user_id').references('users.id') + t.foreign('account_id').references('accounts.id') + }), + + knex.schema.alterTable("orders", function(t) { + t.foreign('user_id').references('users.id') + t.foreign('a1_id').references('accounts.id') + t.foreign('a2_id').references('accounts.id') + }), + + knex.schema.alterTable("deposits", function(t) { + t.foreign('user_id').references('users.id') + }), + + knex.schema.alterTable("withdrawals", function(t) { + t.foreign('user_id').references('users.id') + }), + ]) +} + +exports.down = function(knex) { + return Promise.all([ + knex.schema.table("users", function(t) { + t.dropColumn("email"); + t.dropColumn("phone"); + t.dropColumn("admin"); + }) + ]) +} \ No newline at end of file diff --git a/db/migrations/s2/user_adjustments.js b/db/migrations/s2/user_adjustments.js new file mode 100644 index 00000000..010bbc76 --- /dev/null +++ b/db/migrations/s2/user_adjustments.js @@ -0,0 +1,67 @@ + +exports.up = function(knex) { + return Promise.all([ + + // move security fields to separate t (potentially different access permissions) + + knex.schema.createTable('user_security', function (t) { + t.increments('id').unsigned().primary(); + t.int('user_id').notNull(); + t.foreign('user_id').references('users.user_id'); + + t.boolean('twofa').notNull().defaultTo(false); + t.string('pin').notNull(); + t.string('otp_secret').nullable(); + t.string('seed').nullable(); + }), + + knex.raw("INSERT INTO user_security (user_id, twofa, pin, otp_secret, seed) SELECT users.id, twofa, pin, otpsecret, seed FROM users"), + + // move user-customizeable fields to separate t (potentially different access permissions) + + knex.schema.createTable('user_preferences', function (t) { + t.increments('id').unsigned().primary(); + t.int('user_id').notNull(); + t.foreign('user_id').references('users.user_id'); + + t.enum('default_unit', ['SAT', 'BTC', 'CAD']).notNull().defaultTo('SAT'); + t.enum('default_currency', ['CAD', 'USD']).notNull().defaultTo('CAD'); + t.enum('default_fiat', ['CAD', 'USD']).notNull().defaultTo('CAD'); + t.set('show_currencies', ['CAD', 'USD']).notNull().defaultTo('CAD'); + }), + + knex.raw("INSERT INTO user_preferences (user_id, default_unit, default_currency, default_fiat, show_currencies) SELECT users.id, unit, currency, fiat, currencies FROM users"), + + // remove deprecated fields + + knex.schema.table('users', function (t) { + t.string('uuid'); + t.enum('access', ['Anonymous', 'Member', 'Admin']).defaultTo('Anonymous'); + + t.dropColumn('account_id'); + t.dropColumn('unit'); + t.dropColumn('fiat'); + t.dropColumn('currency'); + t.dropColumn('currencies'); + }) + ]) + +}; + +exports.down = function(knex) { + return Promise.all([ + knex.schema.table('users', function (t) { + t.dropColumn('uuid'); + t.dropColumn('access'); + + t.int('account_id'); + t.string('unit'); + t.string('fiat'); + t.string('currency'); + t.string('currencies'); + }), + + knex.schema.dropTable('user_preferences'), + knex.schema.dropTable('user_security'), + ]); +}; -- cgit v1.2.3