TypeScript QueryRunner.query方法代碼示例

 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")
    }