TypeScript typeorm.QueryRunner類代碼示例

 public async up(queryRunner: QueryRunner): Promise<any> {
     // Remove duplicate foreign key
     await queryRunner.query("ALTER TABLE `datasets` DROP FOREIGN KEY `FK_d717ea97450b05d06316d69501a`")
     // Add missing foreign key constraints
     await queryRunner.query("ALTER TABLE `dataset_files` ADD CONSTRAINT `dataset_files_datasetId` FOREIGN KEY (`datasetId`) REFERENCES `datasets`(`id`)")
     await queryRunner.query("ALTER TABLE `sources` ADD CONSTRAINT `sources_datasetId` FOREIGN KEY (`datasetId`) REFERENCES `datasets`(`id`)")
 }

 public async up(queryRunner: QueryRunner): Promise<any> {
     await queryRunner.query("CREATE TABLE `posts` (`id` int NOT NULL AUTO_INCREMENT, `title` text NOT NULL, `slug` text NOT NULL, `type` text NOT NULL, `status` text NOT NULL, `content` longtext NOT NULL, `published_at` datetime NULL, `updated_at` datetime NOT NULL, PRIMARY KEY(`id`)) ENGINE=InnoDB")
     await queryRunner.query("CREATE TABLE `post_tags` (`post_id` int NOT NULL, `tag_id` int NOT NULL, PRIMARY KEY (`post_id`, `tag_id`)) ENGINE=InnoDB")
     await queryRunner.query("ALTER TABLE `post_tags` ADD CONSTRAINT `FK_post_tags_post_id` FOREIGN KEY (`post_id`) REFERENCES `posts`(`id`) ON DELETE CASCADE")
     await queryRunner.query("ALTER TABLE `post_tags` ADD CONSTRAINT `FK_post_tags_tag_id` FOREIGN KEY (`tag_id`) REFERENCES `tags`(`id`) ON DELETE CASCADE")
     await queryRunner.query("update tags t inner join tags p on p.id=t.parentId and p.isBulkImport=1 set t.isBulkImport=1")
 }

    public async up(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query("alter table charts ADD is_indexable BOOLEAN NOT NULL DEFAULT FALSE")

        const chartTags = await queryRunner.query("select ct.chartId, t.parentId from chart_tags ct join tags t on ct.tagId = t.id") as { chartId: number, parentId: number }[]

        for (const ct of chartTags) {
            if (PUBLIC_TAG_PARENT_IDS.includes(ct.parentId)) {
                await queryRunner.query("update charts set is_indexable = ? where id = ?", [true, ct.chartId])
            }
        }
    }

    public async up(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query("CREATE TABLE `chart_revisions` (`id` bigint NOT NULL AUTO_INCREMENT, `chartId` int, `userId` int, `config` json, `createdAt` datetime, `updatedAt` datetime, PRIMARY KEY(`id`)) ENGINE=InnoDB")

        // Create one log for each existing chart
        const charts = await queryRunner.query("SELECT id, config, updatedAt, lastEditedByUserId FROM charts")

        let chartRevisions = []
        for (let i = 0; i < charts.length; i++) {
            const chart = charts[i]
            chartRevisions.push([chart.id, chart.lastEditedByUserId, chart.updatedAt, chart.updatedAt, chart.config])

            if (i % 100 === 0 || i === charts.length-1) {
                await queryRunner.query("INSERT INTO chart_revisions (chartId, userId, createdAt, updatedAt, config) VALUES ?", [chartRevisions])
                chartRevisions = []
            }
        }
    }

    public async up(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query("ALTER TABLE variables CHANGE short_unit shortUnit VARCHAR(255)")

        const variables = await queryRunner.query("SELECT * FROM variables v JOIN datasets d ON d.id=v.datasetId")
        for (const v of variables) {
            if (v.unit || v.shortUnit) {
                const display = JSON.parse(v.display)
                if (v.unit && display.unit === undefined)
                    display.unit = v.unit
                if (v.shortUnit && display.shortUnit === undefined)
                    display.shortUnit = v.shortUnit

                if (JSON.stringify(display) !== v.display)
                    await queryRunner.query("UPDATE variables SET display=? WHERE id=?", [JSON.stringify(display), v.id])
            }
        }
    }

    public async up(queryRunner: QueryRunner): Promise<any> {
        // Split datasets with multiple source infos
        const datasets = await queryRunner.query("select d.* from datasets d join sources s on s.datasetId=d.id where d.namespace='owid' group by d.id having count(s.id) > 1;")

        for (const dataset of datasets) {
            const sources = await queryRunner.query("select * from sources s where s.datasetId=? order by s.id ASC", [dataset.id]) as any[]

            const splitSources = sources.slice(1)

            for (const source of splitSources) {
                const variables = await queryRunner.query("select * from variables v where v.sourceId=?", [source.id])
                if (variables.length) {
                    const row = [`${variables[0].name} (split)`, dataset.namespace, dataset.description, dataset.createdAt, dataset.updatedAt, dataset.dataEditedAt, dataset.dataEditedByUserId, dataset.metadataEditedAt, dataset.metadataEditedByUserId, dataset.createdByUserId, true]
                    const result = await queryRunner.query(`INSERT INTO datasets (name, namespace, description, createdAt, updatedAt, dataEditedAt, dataEditedByUserId, metadataEditedAt, metadataEditedByUserId, createdByUserId, isPrivate) VALUES (?)`, [row])

                    const datasetId = result.insertId

                    await queryRunner.query("update sources set datasetId=? where id=?", [datasetId, source.id])
                    await queryRunner.query("update variables set datasetId=? where sourceId=?", [datasetId, source.id])
                } else {
                    await queryRunner.query("delete from sources where id=?", [source.id])
                }
            }
        }
    }

 public async down(queryRunner: QueryRunner): Promise<any> {
     await queryRunner.query("ALTER TABLE `discord_d_b_server_users_discord_d_b_user` DROP FOREIGN KEY `fk_94d1e5a3251d105fddd937e8252`");
     await queryRunner.query("ALTER TABLE `discord_d_b_server_users_discord_d_b_user` DROP FOREIGN KEY `fk_6caa8ffc1fd9161d73fba4b5c6c`");
     await queryRunner.query("ALTER TABLE `game_time` DROP FOREIGN KEY `fk_8fb82afc6193a996712201d1146`");
     await queryRunner.query("DROP TABLE `discord_d_b_server_users_discord_d_b_user`");
     await queryRunner.query("DROP TABLE `discord_d_b_server`");
     await queryRunner.query("DROP TABLE `discord_d_b_user`");
     await queryRunner.query("DROP TABLE `game_time`");
 }

 public async up(queryRunner: QueryRunner): Promise<any> {
     await queryRunner.query("CREATE TABLE `game_time` (`id` varchar(255) NOT NULL PRIMARY KEY, `secondsPlayed` int(11) NOT NULL, `discordUserId` char(19)) ENGINE=InnoDB");
     await queryRunner.query("CREATE TABLE `discord_d_b_user` (`id` char(19) NOT NULL, `username` varchar(255) NOT NULL, `discriminator` char(4) NOT NULL, PRIMARY KEY(`id`)) ENGINE=InnoDB");
     await queryRunner.query("CREATE TABLE `discord_d_b_server` (`id` char(19) NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY(`id`)) ENGINE=InnoDB");
     await queryRunner.query("CREATE TABLE `discord_d_b_server_users_discord_d_b_user` (`discordDBServerId` char(19) NOT NULL, `discordDBUserId` char(19) NOT NULL, PRIMARY KEY(`discordDBServerId`, `discordDBUserId`)) ENGINE=InnoDB");
     await queryRunner.query("ALTER TABLE `game_time` ADD CONSTRAINT `fk_8fb82afc6193a996712201d1146` FOREIGN KEY (`discordUserId`) REFERENCES `discord_d_b_user`(`id`)");
     await queryRunner.query("ALTER TABLE `discord_d_b_server_users_discord_d_b_user` ADD CONSTRAINT `fk_6caa8ffc1fd9161d73fba4b5c6c` FOREIGN KEY (`discordDBServerId`) REFERENCES `discord_d_b_server`(`id`)");
     await queryRunner.query("ALTER TABLE `discord_d_b_server_users_discord_d_b_user` ADD CONSTRAINT `fk_94d1e5a3251d105fddd937e8252` FOREIGN KEY (`discordDBUserId`) REFERENCES `discord_d_b_user`(`id`)");
 }

    public async up(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query("UPDATE datasets SET dataEditedByUserId=15 WHERE dataEditedByUserId IS NULL")
        await queryRunner.query("UPDATE datasets SET metadataEditedByUserId=15 WHERE metadataEditedByUserId IS NULL")
        await queryRunner.query("UPDATE datasets SET createdByUserId=dataEditedByUserId")

        await queryRunner.query("ALTER TABLE datasets CHANGE dataEditedByUserId dataEditedByUserId INTEGER NOT NULL")
        await queryRunner.query("ALTER TABLE datasets CHANGE metadataEditedByUserId metadataEditedByUserId INTEGER NOT NULL")
        await queryRunner.query("ALTER TABLE datasets CHANGE createdByUserId createdByUserId INTEGER NOT NULL")

        await queryRunner.query("ALTER TABLE `datasets` ADD CONSTRAINT `datasets_createdByUserId` FOREIGN KEY (`createdByUserId`) REFERENCES `users`(`id`)")
    }

    public async up(queryRunner: QueryRunner): Promise<any> {
        const now = new Date()
        await queryRunner.query("ALTER TABLE tags CHANGE categoryId parentId INTEGER DEFAULT NULL;")
        await queryRunner.query("ALTER TABLE tags ADD isBulkImport BOOLEAN NOT NULL DEFAULT FALSE")
        await queryRunner.query("ALTER TABLE tags DROP FOREIGN KEY `dataset_subcategorie_categoryId_cd02a9e3_fk_dataset_c`;")

        for (const category of await queryRunner.query("SELECT * FROM dataset_categories ORDER BY id ASC")) {
            const result = await queryRunner.query("INSERT INTO tags (name, createdAt, updatedAt, isBulkImport) VALUES (?, ?, ?, ?)", [category.name, now, now, category.fetcher_autocreated])

            await queryRunner.query("UPDATE tags SET parentId=? WHERE parentId=?", [result.insertId, category.id])
        }

        await queryRunner.query("ALTER TABLE tags ADD FOREIGN KEY (parentId) REFERENCES tags(id)")
        await queryRunner.query("DROP TABLE dataset_categories")
    }

 public async up(queryRunner: QueryRunner): Promise<any> {
     // If a Swaziland entity exists, we want to map it to Eswatini
     // before renaming Eswatini to Swaziland.
     await queryRunner.query(`
         UPDATE data_values
         SET entityId = (SELECT id FROM entities WHERE name = 'Eswatini')
         WHERE entityId = (SELECT id FROM entities WHERE name = 'Swaziland')
     `)
     await queryRunner.query(`
         DELETE FROM entities
         WHERE name = 'Swaziland'
     `)
     await queryRunner.query(`
         UPDATE entities
         SET name = 'Swaziland'
         WHERE name = 'Eswatini'
     `)
     await queryRunner.query(`
         UPDATE country_name_tool_countrydata
         SET owid_name = 'Swaziland'
         WHERE owid_name = 'Eswatini'
     `)
 }

 public async up(queryRunner: QueryRunner): Promise<any> {
     await queryRunner.query(`
         UPDATE entities
         SET name = 'Eswatini'
         WHERE name = 'Swaziland'
     `)
     await queryRunner.query(`
         UPDATE country_name_tool_countrydata
         SET owid_name = 'Eswatini'
         WHERE owid_name = 'Swaziland'
     `)
     const countrydataIds = await queryRunner.query(`
         SELECT id FROM country_name_tool_countrydata
         WHERE owid_name = 'Eswatini'
         LIMIT 1
     `)
     const eswatiniId = countrydataIds[0].id
     await queryRunner.query(`
         INSERT INTO
             country_name_tool_countryname (country_name, owid_country)
         VALUES
             ('Eswatini', ?)
     `, [eswatiniId])
 }

    public async up(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query("RENAME TABLE dataset_subcategories TO tags")
        await queryRunner.query("CREATE TABLE `dataset_tags` (`datasetId` int NOT NULL, `tagId` int NOT NULL, PRIMARY KEY (`datasetId`, `tagId`)) ENGINE=InnoDB")
        await queryRunner.query("ALTER TABLE `dataset_tags` ADD CONSTRAINT `FK_fa434de5c36953f4efce6b073b3` FOREIGN KEY (`datasetId`) REFERENCES `datasets`(`id`) ON DELETE CASCADE")
        await queryRunner.query("ALTER TABLE `dataset_tags` ADD CONSTRAINT `FK_2e330c9e1074b457d1d238b2dac` FOREIGN KEY (`tagId`) REFERENCES `tags`(`id`) ON DELETE CASCADE")

        for (const dataset of await queryRunner.query("SELECT id, subcategoryId FROM datasets")) {
            await queryRunner.query("INSERT INTO dataset_tags (datasetId, tagId) VALUES (?, ?)", [dataset.id, dataset.subcategoryId])
        }
    }

    public async up(queryRunner: QueryRunner): Promise<any> {
       // await queryRunner.query(`ALTER TABLE "node" RENAME COLUMN "name" TO "title"`);
        await queryRunner.query(`DROP FUNCTION IF EXISTS trigger_change_count_child_after_ins() CASCADE;`);
        await queryRunner.query(`DROP FUNCTION IF EXISTS trigger_change_count_child_after_del() CASCADE;`);

        await queryRunner.query(`CREATE FUNCTION trigger_change_count_child_after_ins()
        RETURNS trigger AS
        $BODY$
        DECLARE
            tmp node%ROWTYPE;
        BEGIN
          SELECT * INTO tmp FROM node WHERE id = NEW.node_id;
        
          tmp.count_child = tmp.count_child + 1;
          
          UPDATE node
          SET count_child = tmp.count_child
          WHERE id = NEW.node_id; 
          
          return NEW;
        END;
        $BODY$
          LANGUAGE plpgsql;`);
        await queryRunner.query(`CREATE TRIGGER change_count_child_after_ins
        AFTER INSERT ON node FOR EACH ROW  
        EXECUTE PROCEDURE trigger_change_count_child_after_ins();`);
        await queryRunner.query(`CREATE FUNCTION trigger_change_count_child_after_del()
        RETURNS trigger AS
        $BODY$
        DECLARE
            tmp node%ROWTYPE;
        BEGIN
          SELECT * INTO tmp FROM node WHERE id = OLD.node_id;
        
          tmp.count_child = tmp.count_child - 1;
          
          UPDATE node
          SET count_child = tmp.count_child
          WHERE id = OLD.node_id; 
        
          return OLD;
        END;
        $BODY$
          LANGUAGE plpgsql;`);
        await queryRunner.query(`CREATE TRIGGER change_count_child_after_del
        AFTER DELETE ON node FOR EACH ROW  
        EXECUTE PROCEDURE trigger_change_count_child_after_del();`);
    }

 public async up(queryRunner: QueryRunner): Promise<any> {
     await queryRunner.query("CREATE INDEX `chartId` ON chart_revisions(chartId)")
 }