From ccfa3e0279cdc2e6ce0f98b25a6c158c0811efaa Mon Sep 17 00:00:00 2001 From: Ran Guin Date: Wed, 28 Jul 2021 15:59:13 -0700 Subject: add deletion of users with names longer than 32 chars.. and accounts referencing these (or non-existent) users --- db/migrations/20210714123455_data_fixes_merge.js | 5 +++++ 1 file changed, 5 insertions(+) (limited to 'db') diff --git a/db/migrations/20210714123455_data_fixes_merge.js b/db/migrations/20210714123455_data_fixes_merge.js index 70740ce8..d6ba3a3a 100644 --- a/db/migrations/20210714123455_data_fixes_merge.js +++ b/db/migrations/20210714123455_data_fixes_merge.js @@ -7,6 +7,11 @@ exports.up = function(knex) { knex.raw("alter table orders drop rate"), knex.raw("alter table orders add rate double"), knex.raw("update orders set rate = v1/v2"), + + knex.raw('update accounts left join users on accounts.user_id = users.id set user_id = null where length(username) > 32') + knex.raw('update accounts left join users on accounts.user_id = users.id set user_id = null where users.id is null') + knex.raw('delete from accounts where users.id is null'), + knex.raw('delete from users where length(username) > 32') ]) } -- cgit v1.2.3 From ed8f354f6d101416607b1caabf8c13c5663e62f2 Mon Sep 17 00:00:00 2001 From: Ran Guin Date: Wed, 28 Jul 2021 16:25:07 -0700 Subject: tweak user_id reference --- db/migrations/20210612181508_init_referrals.js | 43 ++++++++++++---------- .../20210612192804_init_waiting_list.js.js | 27 ++++++++------ db/migrations/20210714123455_data_fixes_merge.js | 6 +-- 3 files changed, 42 insertions(+), 34 deletions(-) (limited to 'db') diff --git a/db/migrations/20210612181508_init_referrals.js b/db/migrations/20210612181508_init_referrals.js index 875d167f..f9107e15 100644 --- a/db/migrations/20210612181508_init_referrals.js +++ b/db/migrations/20210612181508_init_referrals.js @@ -1,21 +1,24 @@ 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 + return Promise.all([ + knex.schema.dropTableIfExists('referrals'), + + 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'); +}; diff --git a/db/migrations/20210612192804_init_waiting_list.js.js b/db/migrations/20210612192804_init_waiting_list.js.js index 65d3eb42..67762ff1 100644 --- a/db/migrations/20210612192804_init_waiting_list.js.js +++ b/db/migrations/20210612192804_init_waiting_list.js.js @@ -1,17 +1,22 @@ exports.up = function(knex) { - return knex.schema.createTable('waiting_list', function(t) { - t.increments('id').unsigned().primary(); + + return Promise.all([ + knex.schema.dropTableIfExists('waiting_list'), + + 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.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.integer('user_id').nullable(); + t.foreign('user_id').references('users.id'); + t.text('notes').nullable(); - t.timestamps(); - }) + t.timestamps(); + }) + ]) } exports.down = function(knex) { diff --git a/db/migrations/20210714123455_data_fixes_merge.js b/db/migrations/20210714123455_data_fixes_merge.js index d6ba3a3a..eb873554 100644 --- a/db/migrations/20210714123455_data_fixes_merge.js +++ b/db/migrations/20210714123455_data_fixes_merge.js @@ -8,9 +8,9 @@ exports.up = function(knex) { knex.raw("alter table orders add rate double"), knex.raw("update orders set rate = v1/v2"), - knex.raw('update accounts left join users on accounts.user_id = users.id set user_id = null where length(username) > 32') - knex.raw('update accounts left join users on accounts.user_id = users.id set user_id = null where users.id is null') - knex.raw('delete from accounts where users.id is null'), + knex.raw('update accounts left join users on accounts.user_id = users.id set user_id = null where length(username) > 32'), + knex.raw('update accounts left join users on accounts.user_id = users.id set user_id = null where users.id is null'), + knex.raw('delete from accounts where user_id is null'), knex.raw('delete from users where length(username) > 32') ]) } -- cgit v1.2.3 From ad89c433db5be30bf4b28afe15e9c660d0ad17c0 Mon Sep 17 00:00:00 2001 From: Ran Guin Date: Wed, 28 Jul 2021 16:43:36 -0700 Subject: replaced update.sql with migration --- db/init/update.sql | 123 ----------------------------------------------------- 1 file changed, 123 deletions(-) delete mode 100644 db/init/update.sql (limited to 'db') diff --git a/db/init/update.sql b/db/init/update.sql deleted file mode 100644 index 9412c1ee..00000000 --- a/db/init/update.sql +++ /dev/null @@ -1,123 +0,0 @@ - -DROP TABLE IF EXISTS `waiting_list`; -CREATE TABLE `waiting_list` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `email` varchar(255) DEFAULT NULL, - `phone` varchar(255) DEFAULT NULL, - `created_at` datetime DEFAULT NULL, - `updated_at` datetime DEFAULT NULL, - `status` enum('pending','contacted','registered', 'cancelled') DEFAULT NULL, - `notes` varchar(255), - `user_id` int(11), - KEY `user` (`user_id`), - FOREIGN KEY (`user_id`) REFERENCES `users` (`id`), - PRIMARY KEY (`id`) -) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1; - -DROP TABLE IF EXISTS `referrals`; -CREATE TABLE `referrals` ( - `id` int(10) unsigned NOT NULL AUTO_INCREMENT, - `token` varchar(255) DEFAULT NULL, - `expiry` date DEFAULT NULL, - `status` enum('available', 'used', 'expired', 'cancelled') DEFAULT NULL, - `sponsor_id` int(11) NOT NULL, - `user_id` int(11) DEFAULT NULL, - `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, - `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (`id`), - UNIQUE KEY `token` (`token`), - CONSTRAINT `sponsor` FOREIGN KEY (sponsor_id) REFERENCES users (`id`), - CONSTRAINT `sponsored` FOREIGN KEY (`user_id`) REFERENCES users (`id`) -) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1; - -ALTER TABLE invoices ADD CONSTRAINT `invoice_user` FOREIGN KEY (user_id) REFERENCES users (id); -ALTER TABLE invoices ADD CONSTRAINT `invoice_account` FOREIGN KEY (account_id) REFERENCES accounts (id); - -ALTER TABLE orders ADD CONSTRAINT `order_user` FOREIGN KEY (user_id) REFERENCES users (id); - -ALTER TABLE payments ADD CONSTRAINT `payment_user` FOREIGN KEY (user_id) REFERENCES users (id); -ALTER TABLE payments ADD CONSTRAINT `payment_account` FOREIGN KEY (account_id) REFERENCES accounts (id); - -ALTER TABLE deposits ADD CONSTRAINT `deposit_user` FOREIGN KEY (user_id) REFERENCES users (id); - -ALTER TABLE withdrawals ADD CONSTRAINT `withdrawal_user` FOREIGN KEY (user_id) REFERENCES users (id); - -ALTER TABLE accounts ADD CONSTRAINT `account_user` FOREIGN KEY (user_id) REFERENCES users (id); -ALTER TABLE users ADD CONSTRAINT `user_account` FOREIGN KEY (account_id) REFERENCES accounts (id); - -ALTER TABLE users ADD email varchar(255); -ALTER TABLE users ADD phone varchar(255); -ALTER TABLE users ADD admin tinyint default false; - -DROP TABLE IF EXISTS `reset`; -DROP TABLE IF EXISTS `naughty`; -DROP TABLE IF EXISTS `cheaters`; - --- --- Table structure for table `migrations` --- - -DROP TABLE IF EXISTS `migrations`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `migrations` ( - `id` int(10) unsigned NOT NULL AUTO_INCREMENT, - `name` varchar(255) DEFAULT NULL, - `batch` int(11) DEFAULT NULL, - `migration_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), - PRIMARY KEY (`id`) -) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4; -/*!40101 SET character_set_client = @saved_cs_client */; - --- --- Table structure for table `migrations_lock` --- - -DROP TABLE IF EXISTS `migrations_lock`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `migrations_lock` ( - `index` int(10) unsigned NOT NULL AUTO_INCREMENT, - `is_locked` int(11) DEFAULT NULL, - PRIMARY KEY (`index`) -) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4; -/*!40101 SET character_set_client = @saved_cs_client */; - - --- --- Table structure for table `urls` --- - -DROP TABLE IF EXISTS `urls`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `urls` ( - `hash` varchar(255) DEFAULT NULL, - `data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL, - UNIQUE KEY `urls_hash_unique` (`hash`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -/*!40101 SET character_set_client = @saved_cs_client */; - ---- ---- New field changes... ---- - -UPDATE accounts set createdAt = '2020-01-01' where createdAt < '2020-01-01'; -UPDATE accounts set updatedAt = '2020-01-01' where createdAt < '2020-01-01'; -ALTER TABLE accounts modify `contract` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL; - -ALTER TABLE invoices modify `tip` double NOT NULL DEFAULT 0; -ALTER TABLE invoices ADD `webhook` text DEFAULT NULL; - -ALTER TABLE payments ADD `invoice_id` int(11) DEFAULT NULL; -ALTER TABLE payments modify `tip` double NOT NULL DEFAULT 0; - -ALTER TABLE orders DROP a1; -ALTER TABLE orders DROP a2; - -ALTER TABLE users MODIFY `unit` varchar(255) DEFAULT NULL; -ALTER TABLE users ADD `authyId` varchar(255) DEFAULT NULL; -ALTER TABLE users MODIFY `subscriptions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL; -ALTER TABLE users DROP symbol; - - -- cgit v1.2.3