Skip to content
Snippets Groups Projects
Select Git revision
  • 1560e46ea12488d0e358442daa07daed3baf0b4e
  • main default protected
2 results

database.ts

Blame
  • database.ts 4.16 KiB
    import { CESIUM_PLUS_PROFILE_IMPORT, CESIUM_PLUS_PROFILE_INSERT, CESIUM_PLUS_PROFILE_DELETE } from '../consts'
    import type { IndexRequest } from '../types'
    import { CID } from 'multiformats'
    import pg from 'pg'
    import { kubo } from '../kubo'
    import type { CplusProfile } from '../cesium-plus'
    
    // define form env
    const env = {
      DB_HOST: process.env.DB_HOST || 'localhost',
      DB_PORT: parseInt(process.env.DB_PORT || '0') || 5432,
      DB_USER: process.env.DB_USER || 'postgres',
      DB_PASSWORD: process.env.DB_PASSWORD || 'my_db_password'
    }
    
    // group query and param builder to limit error
    interface QueryBuilder {
      query: string
      paramBuilder: (irCID: CID, ir: IndexRequest, dataCID: CID, data: any) => any[]
    }
    
    // initialize client
    const { Client } = pg
    export const client = new Client({
      host: env.DB_HOST,
      port: env.DB_PORT,
      user: env.DB_USER,
      password: env.DB_PASSWORD
    })
    await client.connect()
    
    // functions
    
    // get key in key/value meta
    async function getKey(key: string): Promise<string | null> {
      const res = await client.query('SELECT value FROM meta WHERE key=$1', [key])
      if (res.rows.length != 0) {
        return res.rows[0].value
      } else {
        return null
      }
    }
    // set key in key/value meta
    async function setKey(key: string, value: string) {
      await client.query(
        'INSERT INTO meta(key, value) VALUES ($1, $2) ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value',
        [key, value]
      )
    }
    
    // latest root cid that was indexed
    export async function getLatestIndexedCID(): Promise<CID | null> {
      const val = await getKey('last_indexed_cid')
      return val ? CID.parse(val) : null
    }
    // set latest root cid
    export async function setLatestIndexedCID(cid: CID) {
      await setKey('last_indexed_cid', cid.toString())
    }
    
    // cesium plus profile query and param builder
    // completely overwrites previous data
    const cesiumPlusProfile: QueryBuilder = {
      query: `INSERT INTO
        profiles(index_request_cid, time, pubkey, data_cid, title, description, avatar, geoloc, city, socials)
        VALUES ($1, $2, $3, $4, $5, $6, $7, point($8, $9), $10, $11)
        ON CONFLICT (pubkey)
        DO UPDATE SET
            index_request_cid = EXCLUDED.index_request_cid,
            time = EXCLUDED.time,
            pubkey = EXCLUDED.pubkey, 
            data_cid = EXCLUDED.data_cid, 
            title = EXCLUDED.title,
            description = EXCLUDED.description,
            avatar = EXCLUDED.avatar,
            geoloc = EXCLUDED.geoloc,
            city = EXCLUDED.city,
            socials = EXCLUDED.socials;
            `,
      paramBuilder: (irCID: CID, ir: IndexRequest, dataCID: CID, data: CplusProfile) => [
        // $1 index_request_cid
        irCID.toString(),
        // $2 time
        new Date(ir.time).toISOString(),
        // $3 pubkey
        ir.pubk,
        // $4 data_cid
        dataCID.toString(),
        // $5 title
        data.title,
        // $6 description
        data.description,
        // $7 avatar
        data.avatar?.toString(),
        // $8 geoloc
        data.geoPoint?.lat,
        // $9
        data.geoPoint?.lon,
        // $10 city
        data.city,
        // $11 socials
        data.socials ? JSON.stringify(data.socials) : undefined
      ]
    }
    
    // insert index request in database
    export async function handleInsertRequest(irCID: CID, ir: IndexRequest) {
      console.log('💾 indexing ' + irCID)
    
      switch (ir.kind.toString()) {
        // insert new format
        case CESIUM_PLUS_PROFILE_INSERT.toString(): {
          const dataCID = ir.data
          if (dataCID == null) {
            console.error('no data')
            return
          }
          const data = (await kubo.dag.get(dataCID)).value
          await client.query(cesiumPlusProfile.query, cesiumPlusProfile.paramBuilder(irCID, ir, dataCID, data))
          break
        }
    
        // insert old import
        case CESIUM_PLUS_PROFILE_IMPORT.toString(): {
          const dataCID = ir.data
          if (dataCID == null) {
            console.error('no data')
            return
          }
          const data = (await kubo.dag.get(dataCID)).value
          await client.query(cesiumPlusProfile.query, cesiumPlusProfile.paramBuilder(irCID, ir, dataCID, data))
          break
        }
    
        // delete
        case CESIUM_PLUS_PROFILE_DELETE.toString(): {
          await client.query(`DELETE FROM profiles WHERE pubkey = $1;`, [ir.pubk])
          break
        }
    
        // unimplemented
        default:
          console.log('🔴 unimplemented kind ' + ir.kind)
          break
      }
    }