New db migration script - multiplatform, fix mariadb migrations

10 files changed, 195 insertions, 153 deletions

diff --git a/.gitignore b/.gitignore
index 99789b15..a582a2f3 100644
--- a/.gitignore
+++ b/.gitignore
@@ -28,3 +28,7 @@ build.json
 yarn.lock
 
 .yarn/install-state.gz
+
+dbconf.json
+
+migrations.db
diff --git a/scripts/db_migrations.js b/scripts/db_migrations.js
new file mode 100644
index 00000000..df5196b1
--- /dev/null
+++ b/scripts/db_migrations.js
@@ -0,0 +1,80 @@
+#!/usr/bin/node
+const path = require("path");
+const fs = require("fs");
+const { stdout, exit } = require("process");
+const { execIn } = require("./utils.js");
+const { ask } = require("./utils/ask.js");
+
+async function main() {
+    let filename;
+    if(process.argv[2]) filename = process.argv[2];
+    else filename = await ask("Please enter the name of your migration: ");
+    let dbconf;
+    try {
+        dbconf = JSON.parse(fs.readFileSync("dbconf.json"));
+    } catch (e) {
+        console.log("No dbconf.json found!");
+        dbconf = {};
+    }
+
+    if(!dbconf["sqlite"]) 
+        dbconf.sqlite = {
+            conn_str: "migrations.db",
+            migrations_dir: "sqlite",
+            package: "sqlite3"
+        }
+    if(!dbconf["postgres"] && process.env.FC_DB_POSTGRES) {
+        console.log("Found FC_DB_POSTGRES environment variable. Using it!");
+        dbconf.postgres = {
+            conn_str: process.env.FC_DB_POSTGRES,
+            migrations_dir: "postgres",
+            package: "pg"
+        }
+    }
+    if(!dbconf["mariadb"] && process.env.FC_DB_MARIADB){
+        console.log("Found FC_DB_MARIADB environment variable. Using it!");
+        dbconf.mariadb = {
+            conn_str: process.env.FC_DB_MARIADB,
+            migrations_dir: "mariadb",
+            package: "mysql2"
+        }
+    }
+    fs.writeFileSync("dbconf.json", JSON.stringify(dbconf, null, 4));
+
+    //build
+    execIn(`node scripts/build_new.js`, process.cwd(), {stdio: "inherit"});
+
+    if(fs.existsSync(".env") && !fs.existsSync(".env.bak"))
+        fs.renameSync(".env", ".env.bak");
+    Object.keys(dbconf).forEach((db) => {
+        console.log(`Applying migrations for ${db}`);
+        if(!fs.existsSync(path.join("node_modules", dbconf[db].package))) 
+            execIn(`npm i ${dbconf[db].package}`, process.cwd());
+        fs.writeFileSync(
+            `.env`,
+            `DATABASE=${dbconf[db].conn_str}
+    THREADS=1
+    DB_MIGRATE=true
+    DB_VERBOSE=true`
+        );
+        execIn(`node dist/start.js`, process.cwd(), {stdio: "inherit"});
+    });
+
+    Object.keys(dbconf).forEach((db) => {
+        console.log(`Generating new migrations for ${db}`);
+        fs.writeFileSync(
+            `.env`,
+            `DATABASE=${dbconf[db].conn_str}
+    THREADS=1
+    DB_MIGRATE=true
+    DB_VERBOSE=true`
+        );
+        execIn(`node node_modules/typeorm/cli.js migration:generate "src/util/migrations/${db}/${filename}" -d dist/util/util/Database.js -p`, process.cwd(), {stdio: "inherit"});
+    });
+    if(fs.existsSync(".env.bak")) {
+        fs.rmSync(".env");
+        fs.renameSync(".env.bak", ".env");
+    }
+    exit(0);
+}
+main();
\ No newline at end of file
diff --git a/scripts/db_migrations.sh b/scripts/db_migrations.sh
deleted file mode 100755
index 9ec8230a..00000000
--- a/scripts/db_migrations.sh
+++ /dev/null
@@ -1,41 +0,0 @@
-#!/bin/sh
-
-if [ ! -z "$1" ]
-then
-    FILENAME="$1"
-    echo "Using filename: $FILENAME"
-else
-    read -p "Enter migration filename: " FILENAME
-fi
-
-[ -f ".env" ] && (
-    mv .env .env.tmp 2>/dev/null
-    source .env.tmp 2>/dev/null
-)
-npm run build clean logerrors pretty-errors
-
-make_migration() {
-    echo "Creating migrations for $2"
-    mkdir "src/util/migrations/$2" 2>/dev/null
-#    npm run build clean logerrors pretty-errors
-    THREADS=1 DATABASE="$1" DB_MIGRATE=a npm run start:bundle
-    THREADS=1 DATABASE="$1" DB_MIGRATE=a npx typeorm-ts-node-commonjs migration:generate "src/util/migrations/$2/$FILENAME" -d src/util/util/Database.ts -p
-    #npm run build clean logerrors pretty-errors
-    #THREADS=1 DATABASE="$1" DB_MIGRATE=a npm run start:bundle
-}
-
-npm i sqlite3
-make_migration "database.db" "sqlite"
-
-[ -z "$FC_DB_POSTGRES" ] || (
-    npm i pg
-    make_migration "$FC_DB_POSTGRES" "postgres"
-)
-
-[ -z "$FC_DB_MARIADB" ] || (
-    npm i mysql2
-    make_migration "$FC_DB_MARIADB" "mariadb"
-)
-
-[ -f ".env.tmp" ] && mv .env.tmp .env 2>/dev/null
-
diff --git a/scripts/first_setup.js b/scripts/first_setup.js
index d320445d..4ce6e7de 100755
--- a/scripts/first_setup.js
+++ b/scripts/first_setup.js
@@ -2,10 +2,10 @@
 const path = require("path");
 const fs = require("fs");
 const { stdout, exit } = require("process");
-const readline = require("readline");
 const { execIn } = require("./utils.js");
+const { ask } = require("./utils/ask.js");
+
 
-const rl = readline.createInterface({ input: process.stdin, output: process.stdout });
 const data = { env: [], config: { register: {} }, extra_pkgs: [] };
 let rights = [];
 
@@ -128,7 +128,7 @@ async function main() {
 	printTitle("Step 5: extra options");
 
 	if (/y?/i.test(await ask("Use fast BCrypt implementation (requires a compiler) (Y/n): "))) data.extra_pkgs.push("bcrypt");
-	if (/y?/.test(await ask("Enable support for widgets (requires compiler, known to fail on some ARM devices.) (Y/n): ")))
+	if (/y?/i.test(await ask("Enable support for widgets (requires compiler, known to fail on some ARM devices.) (Y/n): ")))
 		data.extra_pkgs.push("canvas");
 
 	printTitle("Step 6: finalizing...");
@@ -201,12 +201,7 @@ async function askRights() {
 	return selectedRights;
 }
 
-async function askRight(right) {
-	let answer = await ask(`${right}: `);
-	if (answer == "y") return true;
-	else if (answer == "n") return false;
-	else return askRight(right);
-}
+
 
 function printTitle(input) {
 	let width = stdout.columns / 2 - 1; //40
@@ -214,15 +209,7 @@ function printTitle(input) {
 	console.log("-".repeat(width - input.length / 2), input, "-".repeat(width - input.length / 2));
 	console.log();
 }
-async function ask(question) {
-	return new Promise((resolve, _reject) => {
-		return rl.question(question, (answer) => {
-			resolve(answer);
-		});
-	}).catch((err) => {
-		console.log(err);
-	});
-}
+
 
 function BitFlag(int) {
 	return 1n << BigInt(int);
diff --git a/scripts/utils/ask.js b/scripts/utils/ask.js
new file mode 100644
index 00000000..cb8a29f6
--- /dev/null
+++ b/scripts/utils/ask.js
@@ -0,0 +1,20 @@
+const readline = require("readline");
+const rl = readline.createInterface({ input: process.stdin, output: process.stdout });
+
+async function ask(question) {
+	return new Promise((resolve, _reject) => {
+		return rl.question(question, (answer) => {
+			resolve(answer);
+		});
+	}).catch((err) => {
+		console.log(err);
+	});
+}
+async function askBool(question) {
+	return /y?/i.test(await ask(question));
+}
+
+module.exports = {
+    ask,
+    askBool
+}
\ No newline at end of file
diff --git a/src/util/migrations/mariadb/1660131942703-apps_nullable_team.ts b/src/util/migrations/mariadb/1660131942703-apps_nullable_team.ts
deleted file mode 100644
index c76d9af4..00000000
--- a/src/util/migrations/mariadb/1660131942703-apps_nullable_team.ts
+++ /dev/null
@@ -1,17 +0,0 @@
-import { MigrationInterface, QueryRunner } from "typeorm";
-
-export class appsNullableTeam1660131942703 implements MigrationInterface {
-	name = "appsNullableTeam1660131942703";
-
-	public async up(queryRunner: QueryRunner): Promise<void> {
-		await queryRunner.query(`
-            DROP INDEX \`IDX_2ce5a55796fe4c2f77ece57a64\` ON \`applications\`
-        `);
-	}
-
-	public async down(queryRunner: QueryRunner): Promise<void> {
-		await queryRunner.query(`
-            CREATE UNIQUE INDEX \`IDX_2ce5a55796fe4c2f77ece57a64\` ON \`applications\` (\`bot_user_id\`)
-        `);
-	}
-}
diff --git a/src/util/migrations/mariadb/1660549252130-fix_nullables.ts b/src/util/migrations/mariadb/1660549252130-fix_nullables.ts
deleted file mode 100644
index d9ea2e77..00000000
--- a/src/util/migrations/mariadb/1660549252130-fix_nullables.ts
+++ /dev/null
@@ -1,29 +0,0 @@
-import { MigrationInterface, QueryRunner } from "typeorm";
-
-export class fixNullables1660549252130 implements MigrationInterface {
-	name = "fixNullables1660549252130";
-
-	public async up(queryRunner: QueryRunner): Promise<void> {
-		await queryRunner.query(`
-            DROP INDEX \`IDX_76ba283779c8441fd5ff819c8c\` ON \`users\`
-        `);
-		await queryRunner.query(`
-            ALTER TABLE \`users\` CHANGE \`bio\` \`bio\` varchar(255) NULL
-        `);
-		await queryRunner.query(`
-            ALTER TABLE \`users\` CHANGE \`mfa_enabled\` \`mfa_enabled\` tinyint NULL
-        `);
-	}
-
-	public async down(queryRunner: QueryRunner): Promise<void> {
-		await queryRunner.query(`
-            ALTER TABLE \`users\` CHANGE \`mfa_enabled\` \`mfa_enabled\` tinyint NOT NULL
-        `);
-		await queryRunner.query(`
-            ALTER TABLE \`users\` CHANGE \`bio\` \`bio\` varchar(255) NOT NULL
-        `);
-		await queryRunner.query(`
-            CREATE UNIQUE INDEX \`IDX_76ba283779c8441fd5ff819c8c\` ON \`users\` (\`settingsId\`)
-        `);
-	}
-}
diff --git a/src/util/migrations/mariadb/1660540527213-sync_migrations.ts b/src/util/migrations/mariadb/1661273147273-test.ts
index 594e4472..4e077a11 100644
--- a/src/util/migrations/mariadb/1660540527213-sync_migrations.ts
+++ b/src/util/migrations/mariadb/1661273147273-test.ts
@@ -1,16 +1,23 @@
 import { MigrationInterface, QueryRunner } from "typeorm";
 
-export class syncMigrations1660540527213 implements MigrationInterface {
-	name = "syncMigrations1660540527213";
+export class test1661273147273 implements MigrationInterface {
+    name = 'test1661273147273'
 
-	public async up(queryRunner: QueryRunner): Promise<void> {
-		await queryRunner.query(`
+    public async up(queryRunner: QueryRunner): Promise<void> {
+        await queryRunner.query(`
             ALTER TABLE \`invites\` DROP FOREIGN KEY \`FK_15c35422032e0b22b4ada95f48f\`
         `);
-		await queryRunner.query(`
-            ALTER TABLE \`users\` CHANGE \`settings\` \`settingsId\` text NOT NULL
+        await queryRunner.query(`
+            DROP INDEX \`IDX_2ce5a55796fe4c2f77ece57a64\` ON \`applications\`
         `);
-		await queryRunner.query(`
+        await queryRunner.query(`
+            CREATE TABLE \`plugin_config\` (
+                \`key\` varchar(255) NOT NULL,
+                \`value\` text NULL,
+                PRIMARY KEY (\`key\`)
+            ) ENGINE = InnoDB
+        `);
+        await queryRunner.query(`
             CREATE TABLE \`user_settings\` (
                 \`id\` varchar(255) NOT NULL,
                 \`afk_timeout\` int NULL,
@@ -47,80 +54,96 @@ export class syncMigrations1660540527213 implements MigrationInterface {
                 PRIMARY KEY (\`id\`)
             ) ENGINE = InnoDB
         `);
-		await queryRunner.query(`
+        await queryRunner.query(`
+            ALTER TABLE \`users\` DROP COLUMN \`settings\`
+        `);
+        await queryRunner.query(`
+            ALTER TABLE \`users\`
+            ADD \`settingsId\` varchar(255) NULL
+        `);
+        await queryRunner.query(`
+            ALTER TABLE \`users\`
+            ADD UNIQUE INDEX \`IDX_76ba283779c8441fd5ff819c8c\` (\`settingsId\`)
+        `);
+        await queryRunner.query(`
             ALTER TABLE \`channels\`
             ADD \`flags\` int NULL
         `);
-		await queryRunner.query(`
+        await queryRunner.query(`
             ALTER TABLE \`channels\`
             ADD \`default_thread_rate_limit_per_user\` int NULL
         `);
-		await queryRunner.query(`
+        await queryRunner.query(`
             ALTER TABLE \`guilds\`
             ADD \`premium_progress_bar_enabled\` tinyint NULL
         `);
-		await queryRunner.query(`
-            ALTER TABLE \`users\` DROP COLUMN \`settingsId\`
+        await queryRunner.query(`
+            ALTER TABLE \`users\` CHANGE \`bio\` \`bio\` varchar(255) NULL
         `);
-		await queryRunner.query(`
-            ALTER TABLE \`users\`
-            ADD \`settingsId\` varchar(255) NULL
+        await queryRunner.query(`
+            ALTER TABLE \`users\` CHANGE \`mfa_enabled\` \`mfa_enabled\` tinyint NULL
         `);
-		await queryRunner.query(`
-            ALTER TABLE \`users\`
-            ADD UNIQUE INDEX \`IDX_76ba283779c8441fd5ff819c8c\` (\`settingsId\`)
-        `);
-		await queryRunner.query(`
+        await queryRunner.query(`
             CREATE UNIQUE INDEX \`REL_76ba283779c8441fd5ff819c8c\` ON \`users\` (\`settingsId\`)
         `);
-		await queryRunner.query(`
+        await queryRunner.query(`
             ALTER TABLE \`users\`
             ADD CONSTRAINT \`FK_76ba283779c8441fd5ff819c8cf\` FOREIGN KEY (\`settingsId\`) REFERENCES \`user_settings\`(\`id\`) ON DELETE NO ACTION ON UPDATE NO ACTION
         `);
-		await queryRunner.query(`
+        await queryRunner.query(`
             ALTER TABLE \`invites\`
             ADD CONSTRAINT \`FK_15c35422032e0b22b4ada95f48f\` FOREIGN KEY (\`inviter_id\`) REFERENCES \`users\`(\`id\`) ON DELETE CASCADE ON UPDATE NO ACTION
         `);
-	}
+    }
 
-	public async down(queryRunner: QueryRunner): Promise<void> {
-		await queryRunner.query(`
+    public async down(queryRunner: QueryRunner): Promise<void> {
+        await queryRunner.query(`
             ALTER TABLE \`invites\` DROP FOREIGN KEY \`FK_15c35422032e0b22b4ada95f48f\`
         `);
-		await queryRunner.query(`
+        await queryRunner.query(`
             ALTER TABLE \`users\` DROP FOREIGN KEY \`FK_76ba283779c8441fd5ff819c8cf\`
         `);
-		await queryRunner.query(`
+        await queryRunner.query(`
             DROP INDEX \`REL_76ba283779c8441fd5ff819c8c\` ON \`users\`
         `);
-		await queryRunner.query(`
-            ALTER TABLE \`users\` DROP INDEX \`IDX_76ba283779c8441fd5ff819c8c\`
+        await queryRunner.query(`
+            ALTER TABLE \`users\` CHANGE \`mfa_enabled\` \`mfa_enabled\` tinyint NOT NULL
         `);
-		await queryRunner.query(`
-            ALTER TABLE \`users\` DROP COLUMN \`settingsId\`
+        await queryRunner.query(`
+            ALTER TABLE \`users\` CHANGE \`bio\` \`bio\` varchar(255) NOT NULL
         `);
-		await queryRunner.query(`
-            ALTER TABLE \`users\`
-            ADD \`settingsId\` text NOT NULL
-        `);
-		await queryRunner.query(`
+        await queryRunner.query(`
             ALTER TABLE \`guilds\` DROP COLUMN \`premium_progress_bar_enabled\`
         `);
-		await queryRunner.query(`
+        await queryRunner.query(`
             ALTER TABLE \`channels\` DROP COLUMN \`default_thread_rate_limit_per_user\`
         `);
-		await queryRunner.query(`
+        await queryRunner.query(`
             ALTER TABLE \`channels\` DROP COLUMN \`flags\`
         `);
-		await queryRunner.query(`
+        await queryRunner.query(`
+            ALTER TABLE \`users\` DROP INDEX \`IDX_76ba283779c8441fd5ff819c8c\`
+        `);
+        await queryRunner.query(`
+            ALTER TABLE \`users\` DROP COLUMN \`settingsId\`
+        `);
+        await queryRunner.query(`
+            ALTER TABLE \`users\`
+            ADD \`settings\` text NOT NULL
+        `);
+        await queryRunner.query(`
             DROP TABLE \`user_settings\`
         `);
-		await queryRunner.query(`
-            ALTER TABLE \`users\` CHANGE \`settingsId\` \`settings\` text NOT NULL
+        await queryRunner.query(`
+            DROP TABLE \`plugin_config\`
+        `);
+        await queryRunner.query(`
+            CREATE UNIQUE INDEX \`IDX_2ce5a55796fe4c2f77ece57a64\` ON \`applications\` (\`bot_user_id\`)
         `);
-		await queryRunner.query(`
+        await queryRunner.query(`
             ALTER TABLE \`invites\`
             ADD CONSTRAINT \`FK_15c35422032e0b22b4ada95f48f\` FOREIGN KEY (\`inviter_id\`) REFERENCES \`users\`(\`id\`) ON DELETE NO ACTION ON UPDATE NO ACTION
         `);
-	}
+    }
+
 }
diff --git a/src/util/migrations/mariadb/1661273179287-test2.ts b/src/util/migrations/mariadb/1661273179287-test2.ts
new file mode 100644
index 00000000..0f77f284
--- /dev/null
+++ b/src/util/migrations/mariadb/1661273179287-test2.ts
@@ -0,0 +1,18 @@
+import { MigrationInterface, QueryRunner } from "typeorm";
+
+export class test21661273179287 implements MigrationInterface {
+    name = 'test21661273179287'
+
+    public async up(queryRunner: QueryRunner): Promise<void> {
+        await queryRunner.query(`
+            DROP INDEX \`IDX_76ba283779c8441fd5ff819c8c\` ON \`users\`
+        `);
+    }
+
+    public async down(queryRunner: QueryRunner): Promise<void> {
+        await queryRunner.query(`
+            CREATE UNIQUE INDEX \`IDX_76ba283779c8441fd5ff819c8c\` ON \`users\` (\`settingsId\`)
+        `);
+    }
+
+}
diff --git a/src/util/util/Database.ts b/src/util/util/Database.ts
index 8509879d..b9f8365e 100644
--- a/src/util/util/Database.ts
+++ b/src/util/util/Database.ts
@@ -87,7 +87,7 @@ function getDataSourceOptions(): DataSourceOptions {
 		//entities: Object.values(Models).filter((x) => x.constructor.name !== "Object" && x.constructor.name !== "Array" && x.constructor.name !== "BigInt" && x).map(x=>x.name),
 		entities: Object.values(Models).filter((x) => x.constructor.name == "Function" && shouldIncludeEntity(x.name)),
 		synchronize: synchronizeInsteadOfMigrations,
-		logging: verboseDb,
+		logging: verboseDb ? "all" : false,
 		cache: {
 			duration: 1000 * 3 // cache all find queries for 3 seconds
 		},
@@ -96,10 +96,7 @@ function getDataSourceOptions(): DataSourceOptions {
 		name: "default",
 		migrations: synchronizeInsteadOfMigrations ? [] : [path.join(__dirname, "..", "migrations", type, "*.js")],
 		migrationsRun: !synchronizeInsteadOfMigrations,
-		//migrationsRun: false,
-		cli: {
-			migrationsDir: `src/migrations/${type}`
-		}
+		applicationName: `Fosscord Server`,
 	} as DataSourceOptions;
 }