TypeScript db.transaction函数代码示例

api.put('/datasets/:datasetId', async (req: Request, res: Response) => {
    const datasetId = expectInt(req.params.datasetId)
    const dataset = await Dataset.findOne({ id: datasetId })
    if (!dataset)
        throw new JsonError(`No dataset by id ${datasetId}`, 404)

    await db.transaction(async t => {
        const newDataset = (req.body as { dataset: any }).dataset
        await t.execute(`UPDATE datasets SET name=?, description=?, isPrivate=?, metadataEditedAt=?, metadataEditedByUserId=? WHERE id=?`, [newDataset.name, newDataset.description||"", newDataset.isPrivate, new Date(), res.locals.user.id, datasetId])

        const tagRows = newDataset.tags.map((tag: any) => [tag.id, datasetId])
        await t.execute(`DELETE FROM dataset_tags WHERE datasetId=?`, [datasetId])
        if (tagRows.length)
            await t.execute(`INSERT INTO dataset_tags (tagId, datasetId) VALUES ?`, [tagRows])

        const source = newDataset.source
        const description = _.omit(source, ['name', 'id'])
        await t.execute(`UPDATE sources SET name=?, description=? WHERE id=?`, [source.name, JSON.stringify(description), source.id])
    })

    // Note: not currently in transaction
    try {
        await syncDatasetToGitRepo(datasetId, { oldDatasetName: dataset.name, commitName: res.locals.user.fullName, commitEmail: res.locals.user.email })
    } catch (err) {
        log.error(err)
        // Continue
    }

    return { success: true }
})

 export async function setTags(postId: number, tagIds: number[]) {
     await db.transaction(async t => {
         const tagRows = tagIds.map(tagId => [tagId, postId])
         await t.execute(`DELETE FROM post_tags WHERE post_id=?`, [postId])
         if (tagRows.length)
             await t.execute(`INSERT INTO post_tags (tag_id, post_id) VALUES ?`, [tagRows])
     })
 }

 static async setTags(datasetId: number, tagIds: number[]) {
     await db.transaction(async t => {
         const tagRows = tagIds.map(tagId => [tagId, datasetId])
         await t.execute(`DELETE FROM dataset_tags WHERE datasetId=?`, [datasetId])
         if (tagRows.length)
             await t.execute(`INSERT INTO dataset_tags (tagId, datasetId) VALUES ?`, [tagRows])
     })
 }

async function importCodebook() {
    const codebookXLS = XLSX.readFile(CODEBOOK_FILE)
    const sheet = codebookXLS.Sheets[codebookXLS.SheetNames[0]]
    const codebookCSV = XLSX.utils.sheet_to_csv(sheet)

    const now = new Date()
    const codebookRows = await parseCSV(codebookCSV)
    const vdemVariables = codebookRows.slice(1).map(row => ({
        indicatorCode: row[0],
        indicatorName: row[1],
        shortDefinition: row[2],
        longDefinition: row[3],
        responses: row[4],
        dataRelease: row[5],
        aggregationMethod: row[6],
        variableSource: row[7].trim()
    }))

    // Need to handle these fussy subset codes separately
    const variablesByCode = _.keyBy(vdemVariables.filter(v => v.shortDefinition), v => v.indicatorCode)
    for (const v of vdemVariables) {
        const orig = variablesByCode[v.indicatorCode]
        if (orig !== v) {
            if (v.indicatorName.toLowerCase().indexOf("executive") !== -1) {
                v.indicatorCode += "_ex"
            } else if (v.indicatorName.toLowerCase().indexOf("legislative") !== -1) {
                v.indicatorCode += "_leg"
            } else {
                throw new Error("Unknown duplicate indicator: " + v.indicatorName)
            }

            v.shortDefinition = orig.shortDefinition
            v.responses = orig.responses
            v.dataRelease = orig.dataRelease
            v.aggregationMethod = orig.aggregationMethod
            v.variableSource = orig.variableSource
        }
    }

    // User responsible for uploading this data
    const userId = (await db.get(`SELECT * FROM users WHERE fullName=?`, ["Jaiden Mispy"])).id

    await db.transaction(async t => {

        const existingDataset = (await t.query("SELECT id FROM datasets WHERE namespace='vdem'"))[0]
        if (existingDataset) {
            await t.execute(`DELETE d FROM data_values AS d JOIN variables AS v ON d.variableId=v.id WHERE v.datasetId=?`, [existingDataset.id])
            await t.execute(`DELETE FROM variables WHERE datasetId=?`, [existingDataset.id])
            await t.execute(`DELETE FROM sources WHERE datasetId=?`, [existingDataset.id])
            await t.execute(`DELETE FROM datasets WHERE id=?`, [existingDataset.id])
        }

        const datasetRow = ['vdem', "V-Dem Dataset Version 8 - V-Dem Institute", "", false, now, now, now, userId, now, userId, userId]
        const result = await t.query("INSERT INTO datasets (namespace, name, description, isPrivate, createdAt, updatedAt, metadataEditedAt, metadataEditedByUserId, dataEditedAt, dataEditedByUserId, createdByUserId) VALUES (?)", [datasetRow])
        const datasetId = result.insertId

        const sourceName = "V-Dem Dataset Version 8 (2018)"

        for (let i = 0; i < vdemVariables.length; i++) {
            const v = vdemVariables[i]

            let additionalInfo = "This variable was imported into the OWID database from Version 8 of the V-Dem Dataset. Here is the original metadata given by the V-Dem Codebook:\n\n"

            additionalInfo += `Indicator Name: ${v.indicatorName}\n\n`
            additionalInfo += `Indicator Code: ${v.indicatorCode}\n\n`
            if (v.shortDefinition)
                additionalInfo += `Short definition: ${v.shortDefinition}\n\n`
            if (v.longDefinition)
                additionalInfo += `Long definition: ${v.longDefinition}\n\n`
            if (v.responses)
                additionalInfo += `Responses: ${v.responses}\n\n`
            if (v.dataRelease)
                additionalInfo += `Data release: ${v.dataRelease}\n\n`
            if (v.aggregationMethod)
                additionalInfo += `Aggregation method: ${v.aggregationMethod}`

            if (v.indicatorCode === "v2exdfcbhs_rec") {
                additionalInfo += "\n| Notes: v2exdfcbhs_rec is a version of v2exdfcbhs, for v2exdfcbhs_rec the answer categories 1 and 2, 3 and 4 has been merged."
                v.indicatorName += " (rec)"
            }

            const sourceDescription = {
                dataPublishedBy: "V-Dem Institute",
                dataPublisherSource: v.variableSource,
                link: findUrlsInText(v.variableSource).join(","),
                additionalInfo: additionalInfo
            }
            const sourceRow = [datasetId, sourceName, now, now, JSON.stringify(sourceDescription)]

            const sourceResult = await t.query("INSERT INTO sources (datasetId, name, createdAt, updatedAt, description) VALUES (?)", [sourceRow])
            const sourceId = sourceResult.insertId

            const variableRow = [datasetId, sourceId, i, v.indicatorName, v.indicatorCode, v.shortDefinition, now, now, JSON.stringify(v), "", "", "", "{}"]
            await t.query("INSERT INTO variables (datasetId, sourceId, columnOrder, name, code, description, createdAt, updatedAt, originalMetadata, unit, coverage, timespan, display) VALUES (?)", [variableRow])
        }
    })
}