Skip to content

Instantly share code, notes, and snippets.

@zwhitchcox

zwhitchcox/create_views.ts

Created July 22, 2023 19:31
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save zwhitchcox/991c92e3a19a60e94c2220e043d1c27f to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save zwhitchcox/991c92e3a19a60e94c2220e043d1c27f to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. zwhitchcox created this gist Jul 22, 2023.
    277 changes: 277 additions & 0 deletions create_views.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,277 @@
    import { format, zonedTimeToUtc } from "date-fns-tz";
    import { Group, Period } from "./types";
    import { prisma } from './db';

    /*
    * This was copied from http://npm.im/strip-indent
    */
    export const stripIndent = (str: string) => {
    const match = str.match(/^[ \t]*(?=\S)/gm)

    if (!match) {
    return str
    }

    const indent = Math.min(
    ...match.map(x => {
    return x.length
    }),
    )
    const re = new RegExp(`^[ \\t]{${indent}}`, 'gm')

    return indent > 0 ? str.replace(re, '') : str
    }

    const timezone = 'America/New_York';

    const createView = async (name: string, query: string, materialized?: boolean) => {
    return await prisma.$executeRawUnsafe<any>(`
    CREATE ${materialized ? `MATERIALIZED` : ''} VIEW "${name}" AS
    ${query}
    ${materialized ? 'WITH DATA;' : ';'}
    `)
    }

    const removeView = async (name: string) => {
    let e1, e2;
    try {
    await prisma.$executeRawUnsafe(`DROP VIEW IF EXISTS "${name}";`);
    } catch (e) {
    e1 = e;
    }
    try {
    await prisma.$executeRawUnsafe(`DROP MATERIALIZED VIEW IF EXISTS "${name}";`);
    } catch (e) {
    e2 = e;
    }
    if (e1 && e2) {
    throw e1;
    }
    }

    const createViewManager = (name: string, query: string) => {
    return {
    create: async (materialized?: boolean) => {
    try {
    return await createView(name, query, materialized);
    } catch (e: any) {
    console.error(name);
    e.message += `\n\n// ${name}\n${query}`;
    throw e;
    }
    },
    remove: async () => {
    return await removeView(name);
    }
    }
    }

    interface QueryProps {
    where?: string | string[];
    groupBy?: string | string[];
    select?: string | string[];
    }

    const normalizeQueryProps = ({ where, groupBy, select }: QueryProps) => {
    if (Array.isArray(groupBy)) {
    groupBy = groupBy.join(', ');
    }
    if (Array.isArray(select)) {
    select = select.join(', ');
    }
    if (Array.isArray(where)) {
    where = where.join(' AND ');
    }
    return { where, groupBy, select };
    }

    const getRatingQuery = (props: QueryProps) => {
    const { where, groupBy, select } = normalizeQueryProps(props);
    return `
    SELECT
    avg("rating") as rating,
    count(*) as count
    ${select ? `, ${select}` : ''}
    FROM
    "Call"
    WHERE
    "rating" IS NOT NULL
    AND "rating" > 0
    AND "agentId" IS NOT NULL
    ${where ? `AND ${where}` : ''}
    GROUP BY
    ${groupBy ? `${groupBy}` : ''}
    `
    }

    const getCallTypeCountQuery = (props: QueryProps) => {
    const { where, groupBy, select } = normalizeQueryProps(props);
    return `
    SELECT
    type as type,
    COUNT(*) as count
    ${select ? `, ${select}` : ''}
    FROM
    "Call"
    WHERE
    direction = 'Inbound'
    ${where ? `AND ${where}` : ''}
    GROUP BY
    type
    ${groupBy ? `, ${groupBy}` : ''}
    `
    }

    const getTagCountQuery = (props: QueryProps) => {
    const { where, groupBy, select } = normalizeQueryProps(props);
    return `
    SELECT
    t.name as tag,
    COUNT(*) as count
    ${select ? `, ${select}` : ''}
    FROM
    "Call" as c
    JOIN
    "_CallToTag" as ct ON ct."A" = c.id
    JOIN
    "Tag" as t ON t.id = ct."B"
    ${where ? `AND ${where}` : ''}
    GROUP BY
    tag
    ${groupBy ? `, ${groupBy}` : ''}
    `;
    }

    const queryGetters = [
    {
    name: 'Rating',
    query: getRatingQuery,
    schema: `
    rating Float
    count BigInt
    `,
    },
    {
    name: 'CallTypeCount',
    query: getCallTypeCountQuery,
    schema: `
    type CallType?
    count BigInt
    `,
    },
    {
    name: 'TagCount',
    query: getTagCountQuery,
    schema: `
    tag String @unique
    count BigInt
    `,
    },
    ]

    const periods = Object.values(Period)
    .map(period => ({
    name: period,
    select: `DATE_TRUNC('${period}', "createdOn" AT TIME ZONE '${timezone}') as ${period.toLowerCase()}`,
    groupBy: period.toLowerCase(),
    schema: `${period.toLowerCase()} DateTime @unique`,
    }));

    const modifiers = [
    {
    name: Group.Agent,
    select: '"agentId"',
    groupBy: '"agentId"',
    schema: ' agentId Int @unique',
    },
    {
    name: Group.Company,
    },
    ]

    const combineProps = (props: any[]) => {
    const combined: any = {};
    for (const prop of props) {
    for (const key in prop) {
    if (combined[key]) {
    combined[key] = [...combined[key], prop[key]];
    } else {
    combined[key] = [prop[key]];
    }
    }
    }
    return combined;
    }

    const queries: { name: string, query: string }[] = []
    export const schemas: string[] = []

    for (const getter of queryGetters) {
    for (const modifier of modifiers) {
    for (const period of periods) {
    const name = `${getter.name}${period.name}${modifier.name}`;
    queries.push({
    name,
    query: getter.query(combineProps([modifier, period])),
    })
    const schema = [getter.schema, modifier.schema, period.schema]
    .filter(Boolean)
    .map(s => s!.trim())
    .map(s => s.replace(/^\s*/gm, ' '))
    .join('\n');

    schemas.push(stripIndent(`
    view ${name} {
    ${schema.trim()}
    }`));
    }
    }
    }
    export const views = queries.map(({ name }) => name);

    const viewManagers = queries.map(({ name, query }) => createViewManager(name, query));

    export async function runQuery(query: string) {
    const results = await prisma.$queryRawUnsafe<any>(query);
    for (const result of results) {
    console.log('result', result);
    }
    }

    export const removeViews = async () => {
    for (const manager of viewManagers) {
    await manager.remove();
    }
    };

    export const createViews = async (materialized?: boolean) => {
    for (const manager of viewManagers) {
    await manager.create(materialized);
    }
    };

    export const refreshViews = async () => {
    for (const view of views) {
    await prisma.$executeRawUnsafe(`REFRESH MATERIALIZED VIEW "${view}"`);
    }
    };

    export const printSchemas = (schemas: string[]) => {
    for (const schema of schemas) {
    console.log(schema);
    }
    }
    export const printQueries = (queries: { name: string, query: string }[]) => {
    for (const { name, query } of queries) {
    console.log(`\n\n// ${name}\n\n${query}`);
    }
    }


    export function getStartOfDayEST() {
    const easternTime = 'America/New_York';
    const now = new Date();
    const startOfDayInEasternTime = format(now, 'yyyy-MM-dd\'T00:00:00.000\'', { timeZone: easternTime });
    return zonedTimeToUtc(startOfDayInEasternTime, easternTime);
    }