Something went wrong. Try again.
Schedule posts and reposts to Bluesky (and other PDSes) with Cloudflare workers. skyscheduler.work
scheduling social-media cloudflare bsky service cloudflare-workers bsky-tool bluesky
Something went wrong. Try again.
4.5 kB · 104 lines
TypeScript
at main
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105import { and, eq, getTableColumns, gt, inArray, isNull, sql } from "drizzle-orm";import flatten from "just-flatten-it";import { mediaFiles, posts, repostCounts, reposts } from "../../db/app.schema";import { users } from "../../db/auth.schema";import { MAX_POSTED_LENGTH } from "../../limits";import type { AllContext, BatchQuery, BatchQueryArray, DBProcessor, R2BucketObject } from "../../types";import { getUserDID } from "../bsky/bskyUser";import { getAllFilesList } from "../r2Query";import { addFileListing, getAllMediaOfUser } from "./file";
/** Maintenance operations **/export const runMaintenanceUpdates = async (c: AllContext) => { const db: DBProcessor = c.get("db"); if (!db) { return; } // Create a posted query that also checks for valid json and content length const postedQuery = db.select({ ...getTableColumns(posts), contentLength: sql<number>`length(${posts.content})`.as("conLength"), isValidJson: sql<number>`cast(length(${posts.embedContent}) as int)`.as("json"), }).from(posts).where(eq(posts.posted, true)).as("postedQuery"); // Then select from those posts that are too long or have too many characters const jsonFix = await db.select({id: postedQuery.uuid}).from(postedQuery).where(gt(postedQuery.isValidJson, 2)); const postTruncation = await db.select({id: postedQuery.uuid, content: postedQuery.content }) .from(postedQuery).where(gt(postedQuery.contentLength, MAX_POSTED_LENGTH));
// Run the invalid json fix if (jsonFix.length > 0) { console.log(`Attempting to clean up/empty old JSON data for ${jsonFix.length} posts`); const invalidJsonFix: string[] = []; jsonFix.forEach(item => { invalidJsonFix.push(item.id)}); await db.update(posts).set({ embedContent: [] }).where(inArray(posts.uuid, invalidJsonFix)); }
// Post truncation if (postTruncation.length > 0) { console.log(`Attempting to clean up post truncation for ${postTruncation.length} posts`); for (const item of postTruncation) { console.log(`Updating post ${item.id}`); await db.update(posts).set({ content: sql`substr(posts.content, 0, ${MAX_POSTED_LENGTH})`}).where(and(eq(posts.uuid, item.id), eq(posts.posted, true))); } }
// push timestamps await db.update(posts).set({updatedAt: sql`CURRENT_TIMESTAMP`}).where(isNull(posts.updatedAt));
// populate existing media table with post data const allBucketFiles: R2BucketObject[] = await getAllFilesList(c); try { for (const bucketFile of allBucketFiles) { await addFileListing(c, bucketFile.name, bucketFile.user, bucketFile.date); } } catch(err: unknown) { console.error("Adding file listings got error " + String(err)); }
const batchedQueries: BatchQueryArray = []; // Flag if the media file has embed data const allUsers = await db.select({id: users.id}).from(users).all(); for (const user of allUsers) { const userMedia = await getAllMediaOfUser(c, user.id); batchedQueries.push(db.update(mediaFiles).set({hasPost: true}) .where(inArray(mediaFiles.fileName, flatten(userMedia)))); }
// clean up post data const allPosts = await db.select({id: posts.uuid, info: posts.repostInfo}).from(posts); for (const post of allPosts) { // Clean up repost counts const countHelper = db.$count(reposts, eq(reposts.uuid, post.id)); batchedQueries.push(db.insert(repostCounts) .values({uuid: post.id, count: countHelper}) .onConflictDoUpdate({target: repostCounts.uuid, set: {count: countHelper}})); }
if (batchedQueries.length > 0) await db.batch(batchedQueries as BatchQuery);};
export const updateSetDIDForAllUsers = async (c: AllContext) => { const db: DBProcessor = c.get("db"); if (!db) { return; } const allUsers = await db.select({id: users.id, handle: users.username}).from(users).where(isNull(users.did)); if (allUsers.length > 0) { console.log("updating handles:\n"); const batchedQueries: BatchQueryArray = []; for (const user of allUsers) { const userDID = await getUserDID(user.handle); if (userDID !== null) { batchedQueries.push(db.update(users).set({did: userDID}).where(eq(users.id, user.id))); console.log(`${user.id} - ${user.handle} = ${userDID}`); } else { console.log(`${user.id} - skipped ${user.handle}, got return of null`); } } if (batchedQueries.length > 0) await db.batch(batchedQueries as BatchQuery); return batchedQueries.length; } return 0;}