TypeScript db.query函数代码示例

api.get('/countries.json', async (req: Request, res: Response) => {
    let rows = []

    const input = req.query.input
    const output = req.query.output

    if (input === CountryNameFormat.NonStandardCountryName) {
        const outputColumn = CountryDefByKey[output].column_name

        rows = await db.query(`
            SELECT country_name as input, ${outputColumn} as output
            FROM country_name_tool_countryname ccn
            LEFT JOIN country_name_tool_countrydata ccd on ccn.owid_country = ccd.id
            LEFT JOIN country_name_tool_continent con on con.id = ccd.continent`)
    } else {
        const inputColumn = CountryDefByKey[input].column_name
        const outputColumn = CountryDefByKey[output].column_name

        rows = await db.query(
            `SELECT ${inputColumn} as input, ${outputColumn} as output
            FROM country_name_tool_countrydata ccd
            LEFT JOIN country_name_tool_continent con on con.id = ccd.continent`)
    }

    return {
        countries: rows
    }
})

export async function getIndexableCharts(): Promise<ChartItemWithTags[]> {
    const chartItems = await db.query(`SELECT id, config->>"$.slug" AS slug, config->>"$.title" AS title FROM charts WHERE publishedAt IS NOT NULL`)

    const chartTags = await db.query(`
        SELECT ct.chartId, ct.tagId, t.name as tagName, t.parentId as tagParentId FROM chart_tags ct
        JOIN charts c ON c.id=ct.chartId
        JOIN tags t ON t.id=ct.tagId
    `)

    for (const c of chartItems) {
        c.tags = []
    }

    const chartsById = _.keyBy(chartItems, c => c.id)

    for (const ct of chartTags) {
        // XXX hardcoded filtering to public parent tags
        if ([1515, 1507, 1513, 1504, 1502, 1509, 1506, 1501, 1514, 1511, 1500, 1503, 1505, 1508, 1512, 1510].indexOf(ct.tagParentId) === -1)
            continue

        const c = chartsById[ct.chartId]
        if (c)
            c.tags.push({ id: ct.tagId, name: ct.tagName })
    }

    return chartItems
}

export async function getVariableData(variableIds: number[]): Promise<any> {
    const data: any = { variables: {}, entityKey: {} }

    const variableQuery = db.query(`
        SELECT v.*, v.shortUnit, d.name as datasetName, d.id as datasetId, s.id as s_id, s.name as s_name, s.description as s_description FROM variables as v
            JOIN datasets as d ON v.datasetId = d.id
            JOIN sources as s on v.sourceId = s.id
            WHERE v.id IN (?)
    `, [variableIds])

    const dataQuery = db.query(`
            SELECT value, year, variableId as variableId, entities.id as entityId,
            entities.name as entityName, entities.code as entityCode
            FROM data_values
            LEFT JOIN entities ON data_values.entityId = entities.id
            WHERE data_values.variableId IN (?)
            ORDER BY variableId ASC, year ASC
    `, [variableIds])

    const variables = await variableQuery

    for (const row of variables) {
        row.display = JSON.parse(row.display)
        const sourceDescription = JSON.parse(row.s_description); delete row.s_description
        row.source = {
            id: row.s_id,
            name: row.s_name,
            dataPublishedBy: sourceDescription.dataPublishedBy || "",
            dataPublisherSource: sourceDescription.dataPublisherSource || "",
            link: sourceDescription.link || "",
            retrievedData: sourceDescription.retrievedData || "",
            additionalInfo: sourceDescription.additionalInfo || ""
        }
        data.variables[row.id] = _.extend({
            years: [],
            entities: [],
            values: []
        }, row)
    }

    const results = await dataQuery

    for (const row of results) {
        const variable = data.variables[row.variableId]
        variable.years.push(row.year)
        variable.entities.push(row.entityId)

        const asNumber = parseFloat(row.value)
        if (!isNaN(asNumber))
            variable.values.push(asNumber)
        else
            variable.values.push(row.value)

        if (data.entityKey[row.entityId] === undefined) {
            data.entityKey[row.entityId] = { name: row.entityName, code: row.entityCode }
        }
    }

    return data
}

api.get('/importData.json', async req => {
    // Get all datasets from the importable namespace to match against
    const datasets = await db.query(`SELECT id, name FROM datasets WHERE namespace='owid' ORDER BY name ASC`)

    // Get a unique list of all entities in the database (probably this won't scale indefinitely)
    const existingEntities = (await db.query(`SELECT name FROM entities`)).map((e: any) => e.name)

    return { datasets: datasets, existingEntities: existingEntities }
})

async function dataExport() {
    await db.connect()

    const slugs = (await fs.readFile("/Users/mispy/tmp/urls.txt", "utf8")).split("\n").filter(s => s.trim())
    const slugToId = await Chart.mapSlugsToIds()
    const idsToGet = slugs.map(slug => slugToId[slug])

    const variables = await db.query("SELECT v.name, v.id FROM variables v JOIN chart_dimensions cd ON cd.variableId=v.id WHERE cd.chartId IN (?)", [idsToGet])
    const variableIds = variables.map((v: any) => v.id)
    const stream = fs.createWriteStream("/Users/mispy/tmp/sdgs.csv")

    // From dataset CSV export
    const csvHeader = ["Entity", "Year"]
    for (const variable of variables) {
        csvHeader.push(variable.name)
    }

    const columnIndexByVariableId: {[key: number]: number} = {}
    for (const variable of variables) {
        columnIndexByVariableId[variable.id] = csvHeader.indexOf(variable.name)
    }

    stream.write(csvRow(csvHeader))

    const data = await db.query(`
        SELECT e.name AS entity, dv.year, dv.value, dv.variableId FROM data_values dv
        JOIN variables v ON v.id=dv.variableId
        JOIN entities e ON dv.entityId=e.id
        WHERE v.id IN (?)
        ORDER BY e.name ASC, dv.year ASC, dv.variableId ASC`, [variableIds])

    let row: string[] = []
    for (const datum of data) {
        if (datum.entity !== row[0] || datum.year !== row[1]) {
            // New row
            if (row.length) {
                stream.write(csvRow(row))
            }
            row = [datum.entity, datum.year]
            for (const variable of variables) {
                row.push("")
            }
        }

        row[columnIndexByVariableId[datum.variableId]] = datum.value
    }

    // Final row
    stream.write(csvRow(row))

    stream.end()

    await db.end()
}

export async function mapSlugsToIds(): Promise<{ [slug: string]: number }> {
    const redirects = await db.query(`SELECT chart_id, slug FROM chart_slug_redirects`)
    const rows = await db.query(`SELECT id, JSON_UNQUOTE(JSON_EXTRACT(config, "$.slug")) AS slug FROM charts`)

    const slugToId: {[slug: string]: number} = {}
    for (const row of redirects) {
        slugToId[row.slug] = row.chart_id
    }
    for (const row of rows) {
        slugToId[row.slug] = row.id
    }
    return slugToId
}

    // Return object representing datapackage.json for this dataset
    async toDatapackage(): Promise<any> {
        // XXX
        const sources = await Source.find({ datasetId: this.id })
        const variables = await db.table(Variable.table).where({ datasetId: this.id }) as Variable.Row[]
        const tags = await db.query(`SELECT t.id, t.name FROM dataset_tags dt JOIN tags t ON t.id=dt.tagId WHERE dt.datasetId=?`, [this.id])

        const initialFields = [
            { name: "Entity", type: "string" },
            { name: "Year", type: "year" }
        ]

        const dataPackage = {
            name: this.name,
            title: this.name,
            id: this.id,
            description: (sources[0] && sources[0].description && sources[0].description.additionalInfo)||"",
            sources: sources.map(s => s.toDatapackage()),
            owidTags: tags.map((t: any) => t.name),
            resources: [{
                path: `${this.name}.csv`,
                schema: {
                    fields: initialFields.concat(variables.map(v => ({
                        name: v.name,
                        type: "any",
                        description: v.description,
                        owidDisplaySettings: v.display
                    })))
                }
            }]
        }

        return dataPackage
    }