TypeScript wpdb.query函数代码示例

async function undoPermalinks() {
    const rows = await wpdb.query(`SELECT post_id, meta_value FROM wp_postmeta WHERE meta_key='custom_permalink'`)

    for (const row of rows) {
        await wpdb.query(`UPDATE wp_posts SET post_name = ? WHERE ID=?`, [row.meta_value.replace(/\/+$/g, "").replace(/\//g, "__").replace(/--/g, "__"), row.post_id])
    }

    wpdb.end()
}

export async function syncPostToGrapher(postId: number): Promise<string|undefined> {
    const rows = await wpdb.query("SELECT * FROM wp_posts WHERE ID = ? AND post_status != 'trash'", [postId])

    const matchingRows = await db.table(Post.table).where({ id: postId })
    const existsInGrapher = !!matchingRows.length

    const wpPost = rows[0]
    const postRow = wpPost ? {
        id: wpPost.ID,
        title: wpPost.post_title,
        slug: wpPost.post_name.replace(/__/g, '/'),
        type: wpPost.post_type,
        status: wpPost.post_status,
        content: wpPost.post_content,
        published_at: wpPost.post_date_gmt === "0000-00-00 00:00:00" ? null : wpPost.post_date_gmt,
        updated_at: wpPost.post_modified_gmt === "0000-00-00 00:00:00" ? "1970-01-01 00:00:00" : wpPost.post_modified_gmt    
    } as Post.Row : undefined

    await db.knex().transaction(async t => {
        if (!postRow && existsInGrapher) {
            // Delete from grapher
            await t.table(Post.table).where({ 'id': postId }).delete()
        } else if (postRow && !existsInGrapher) {
            await t.table(Post.table).insert(postRow)
        } else if (postRow && existsInGrapher) {
            await t.table(Post.table).where('id', '=', postRow.id).update(postRow)
        }
    })

    const newPost = (await Post.select('slug').from(db.table(Post.table).where({ id: postId })))[0]
    return newPost ? newPost.slug : undefined
}

async function getTitles() {
    await wpdb.connect()

    const pageRows = await wpdb.query(`
        SELECT post_title FROM wp_posts AS posts
        WHERE (posts.post_type='page' OR posts.post_type='post') AND posts.post_status='publish' ORDER BY post_title DESC
    `)

    await wpdb.end()

    return pageRows.map(row => asciify(row.post_title))
}

export async function syncPostTagsToGrapher() {
    const categoriesByPostId = await wpdb.getCategoriesByPostId()

    const tagsByPostId = await wpdb.getTagsByPostId()
    const postRows = await wpdb.query("select * from wp_posts where (post_type='page' or post_type='post') AND post_status != 'trash'")

    for (const post of postRows) {
        const tags = tagsByPostId.get(post.ID) || []
        let tagNames = tags.map(t => decodeHTML(t)).concat([post.post_title])
        const matchingTags = await Tag.select('id', 'name', 'isBulkImport').from(
            db.knex().from(Tag.table).whereIn('name', tagNames).andWhere({ isBulkImport: false })
        )
        let tagIds = matchingTags.map(t => t.id)
        if (matchingTags.map(t => t.name).includes(post.post_title)) {
            tagIds.push(1640)
        }
        await Post.setTags(post.ID, _.uniq(tagIds))
    }
}

async function main() {
    try {
        const categoriesByPostId = await wpdb.getCategoriesByPostId()

        const tagsByPostId = await wpdb.getTagsByPostId()
        const postRows = await wpdb.query("select * from wp_posts where (post_type='page' or post_type='post') AND post_status != 'trash'")
    
        for (const post of postRows) {
            const categories = categoriesByPostId.get(post.ID)||[]

            let tagNames = categories.map(t => decodeHTML(t))

            const matchingTags = await Tag.select('id', 'name', 'isBulkImport').from(
                db.table(Tag.table).whereIn('name', tagNames).andWhere({ isBulkImport: false })
            )

            const existingTags = await Tag.select('id').from(
                db.table(Tag.table)
                    .join('post_tags', { 'post_tags.tag_id': 'tags.id' })
                    .where({ 'post_tags.post_id': post.ID })
            )

            const tagIds = matchingTags.map(t => t.id).concat(existingTags.map(t => t.id))
            // if (matchingTags.map(t => t.name).includes(post.post_title)) {
            //     tagIds.push(1640)
            // }


            // const matchingTags = await Tag.select('id', 'name', 'isBulkImport').from(
            //     db.knex().from(Tag.table).whereIn('name', tagNames).andWhere({ isBulkImport: false })
            // )
            // let tagIds = matchingTags.map(t => t.id)
            // if (matchingTags.map(t => t.name).includes(post.post_title)) {
            //     tagIds.push(1640)
            // }
            await Post.setTags(post.ID, _.uniq(tagIds))
        }
    } finally {
        await wpdb.end()
        await db.end()
    }
}

export async function syncPostsToGrapher() {
    const rows = await wpdb.query("SELECT * FROM wp_posts WHERE (post_type='page' OR post_type='post') AND post_status != 'trash'")

    const doesExistInWordpress = _.keyBy(rows, 'ID')
    const existsInGrapher = await Post.select('id').from(db.knex().from(Post.table))
    const doesExistInGrapher = _.keyBy(existsInGrapher, 'id')
 
    const categoriesByPostId = await wpdb.getCategoriesByPostId()

    const toDelete = existsInGrapher.filter(p => !doesExistInWordpress[p.id]).map(p => p.id)
    const toInsert = rows.map((post: any) => {
        return {
            id: post.ID,
            title: post.post_title,
            slug: post.post_name.replace(/__/g, '/'),
            type: post.post_type,
            status: post.post_status,
            content: post.post_content,
            published_at: post.post_date_gmt === "0000-00-00 00:00:00" ? null : post.post_date_gmt,
            updated_at: post.post_modified_gmt === "0000-00-00 00:00:00" ? "1970-01-01 00:00:00" : post.post_modified_gmt    
        }
    }) as Post.Row[]

    await db.knex().transaction(async t => {
        if (toDelete.length) {
            await t.whereIn('id', toDelete).delete().from(Post.table)
        }

        for (const row of toInsert) {
            if (doesExistInGrapher[row.id])
                await t.update(row).where('id', '=', row.id).into(Post.table)
            else
                await t.insert(row).into(Post.table)
        }
    })
}

async function indexToAlgolia() {
    const client = algoliasearch(ALGOLIA_ID, ALGOLIA_SECRET_KEY)
    const finalIndex = await client.initIndex('pages')
    const tmpIndex = await client.initIndex('pages_tmp')

    // Copy to a temporary index which we will then update
    // This is so we can do idempotent reindexing
    await client.copyIndex(finalIndex.indexName, tmpIndex.indexName, [
        'settings',
        'synonyms',
        'rules'
    ])

    const rows = await wpdb.query(`SELECT * FROM wp_posts WHERE (post_type='post' OR post_type='page') AND post_status='publish'`)

    const records = []

    for (const country of countries) {
        records.push({
            objectID: country.slug,
            type: 'country',
            slug: country.slug,
            title: country.name,
            content: `All available indicators for ${country.name}.`
        })
    }

    for (const row of rows) {
        const rawPost = await wpdb.getFullPost(row)
        const post = await formatPost(rawPost, { footnotes: false })
        const postText = htmlToPlaintext(post.html)
        const chunks = chunkParagraphs(postText, 1000)

        const tags = await getPostTags(post.id)
        const postType = getPostType(post, tags)

        let importance = 0
        if (postType === 'entry')
            importance = 3
        else if (postType === 'explainer')
            importance = 2
        else if (postType === 'fact')
            importance = 1

        let i = 0
        for (const c of chunks) {
            records.push({
                objectID: `${row.ID}-c${i}`,
                postId: post.id,
                type: postType,
                slug: post.slug,
                title: post.title,
                excerpt: post.excerpt,
                authors: post.authors,
                date: post.date,
                modifiedDate: post.modifiedDate,
                content: c,
                _tags: tags.map(t => t.name),
                importance: importance
            })
            i += 1
        }
    }


    for (let i = 0; i < records.length; i += 1000) {
        await tmpIndex.saveObjects(records.slice(i, i+1000))
    }
    await client.moveIndex(tmpIndex.indexName, finalIndex.indexName);

    await wpdb.end()
    await db.end()
}