myheats

db.cjs (10276B)

---

 const postgres = require('postgres');
 let scoreSubscription = null
 
 // Keep track of connected websocket clients
 // https://javascript.info/websocket
 const clients = new Set();
 
 require('dotenv').config({
   // Configure common config files and modes
   // - https://www.npmjs.com/package/dotenv
   // - https://vitejs.dev/guide/env-and-mode
   path: [
     '.env.local',
     '.env.development', '.env.development.local',
     '.env.test',        '.env.test.local',
     '.env.production',  '.env.production.local',
     '.env'
   ]
 });
 
 // Use psql environment variables
 // https://github.com/porsager/postgres?tab=readme-ov-file#environmental-variables
 sql = postgres({
   publications: [
     'myheats_realtime',
   ]
 })
 
 async function invalidateToken(token) {
   try {
     const users = await sql`
       update public.judges set
         token = null,
         expires_at = null
       where token = ${token}
     `
     return users
   } catch (error) {
     console.error('Error occurred in invalidateToken:', error);
     throw error
   }
 }
 
 async function lookupToken(token) {
   try {
     const users = await sql`
       select * from public.judges where
       token = ${token}
     `
     return users
   } catch (error) {
     console.error('Error occurred in lookupToken:', error);
     throw error
   }
 }
 
 async function saveToken(id, token, exp) {
   try {
     const users = await sql`
       update public.judges set
         token = ${token},
         expires_at = ${exp}
       where id = ${id}
     `
     return users
   } catch (error) {
     console.error('Error occurred in saveToken:', error);
     throw error
   }
 }
 
 // Request a signed magic link
 // https://clerk.com/blog/magic-links#building-a-magic-link-system
 async function getUser(email) {
   try {
     const users = await sql`
       select
         id,
         firstname,
         lastname,
         token,
         email
       from public.judges
       where email = ${email}
     `
     return users
   } catch (error) {
     console.error('Error occurred in getUser:', error);
     throw error
   }
 }
 
 async function allHeats() {
   try {
     const heats = await sql`
       select * from heats
     `
     return heats
   } catch (error) {
     console.error('Error occurred in allHeats:', error);
     throw error
   }
 }
 
 async function getHeat(heatId) {
   try {
     const heat = await sql`
       select * from heats
       where id = ${heatId}
     `
     return heat
   } catch (error) {
     console.error('Error occurred in getHeat:', error);
     throw error
   }
 }
 
 async function allAthletes() {
   try {
     const athletes = await sql`
       select * from athletes
     `
     return athletes
   } catch (error) {
     console.error('Error occurred in allHeats:', error);
     throw error
   }
 }
 
 async function addAthleteToHeat(athlete, heat) {
   try {
     const startlist = await sql`
       insert into startlist (
         athlete,
         heat
       )
       values (
         ${athlete},
         ${heat}
       )
       returning *
     `
     return startlist
   } catch (error) {
     console.error('Error occurred in addAthleteToHeat:', error);
     throw error
   }
 }
 
 async function addAthlete(nr, firstname, lastname, birthday, school) {
   try {
     const athlete = await sql`
       insert into athletes (
         nr,
         firstname,
         lastname,
         birthday,
         school
       )
       values (
         ${nr},
         ${firstname},
         ${lastname},
         ${birthday},
         ${school}
       )
       returning *
     `
     return athlete
   } catch (error) {
     console.error('Error occurred in addAthlete:', error);
     throw error
   }
 }
 
 async function removeAthlete(id) {
   try {
     const athlete = await sql`
       delete from athletes where id = ${id}
       returning *
     `
     return athlete
   } catch (error) {
     console.error('Error occurred in removeAthlete:', error);
     throw error
   }
 }
 
 async function removeAthleteFromHeat(startlistId) {
   try {
     const startlist = await sql`
       delete from startlist
       where id = ${startlistId}
       returning *
     `
     return startlist
   } catch (error) {
     console.error('Error occurred in removeAthleteFromHeat:', error);
     throw error
   }
 }
 
 async function newHeat(name, heatLocation, plannedStart) {
   try {
     const heat = await sql`
       insert into heats (
         name,
         location,
         planned_start
       )
       values (
         ${name},
         ${heatLocation},
         ${plannedStart}
       )
       returning *
     `
     return heat
   } catch (error) {
     console.error('Error occurred in newHeat:', error);
     throw error
   }
 }
 
 async function removeHeat(heatId) {
   try {
     const heat = await sql`
       delete from heats where id = ${heatId}
       returning *
     `
     return heat
   } catch (error) {
     console.error('Error occurred in removeHeat:', error);
     throw error
   }
 }
 
 async function distinctStartlist(heatIds) {
   try {
     const startlist = await sql`
       select
         id,
         athlete,
         nr,
         firstname,
         lastname,
         birthday,
         school
       from distinct_startlist(${heatIds}) as athlete
     `
     return startlist
   } catch (error) {
     console.error('Error occurred in distinctStartlist:', error);
     throw error
   }
 }
 
 async function startlistWithAthletes(heatId) {
   try {
     // left outer join, to fetch heats with no athletes
     // https://www.postgresql.org/docs/current/tutorial-join.html
     const startlist = await sql`
       select
         s.id as startlist_id,
         s.heat,
         s.athlete,
         a.id as athlete_id,
         a.nr,
         a.firstname,
         a.lastname,
         a.birthday,
         a.school
       from startlist as s
       left outer join athletes as a
       on s.athlete = a.id
       where s.heat = ${heatId}
     `
     return startlist
   } catch (error) {
     console.error('Error occurred in startlistWithAthletes:', error);
     throw error
   }
 }
 
 async function scoresForHeatAndAthlete(heat, athlete) {
   try {
     const score = await sql`
       select
         id,
         athlete,
         judge,
         score
       from scores where heat = ${heat} and athlete = ${athlete}
     `
     return score
   } catch (error) {
     console.error('Error occurred in scoresForHeatAndAthlete:', error);
     throw error
   }
 }
 
 async function scoreSummaryForHeatAndAthlete(heat, athlete) {
   try {
     const summary = await sql`
       select score_summary
       from score_summary where heat_id = ${heat} and athlete_id = ${athlete}
     `
     return summary
   } catch (error) {
     console.error('Error occurred in scoreSummaryForHeatAndAthlete:', error);
     throw error
   }
 }
 
 async function getScore(heat, athlete, judge) {
   try {
     const scores = await sql`
       select * from scores
       where heat = ${heat} and athlete = ${athlete} and judge = ${judge}
     `
     return scores
   } catch (error) {
     console.error('Error occurred in getScore:', error);
     throw error
   }
 }
 
 // "upsert" score (https://www.postgresql.org/docs/current/sql-insert.html)
 // For ON CONFLICT DO UPDATE, a conflict_target must be provided.
 async function setScore(heat, athlete, judge, score) {
   try {
     const scores = await sql`
       insert into public.scores as s (athlete, judge, score, heat)
         values (${athlete}, ${judge}, ${score}, ${heat})
       on conflict (athlete, judge, heat) do update
       set score = ${score}
       where s.heat = ${heat} and s.athlete = ${athlete} and s.judge = ${judge}
       returning *
     `
     return scores
   } catch (error) {
     console.error('Error occurred in setScore:', error);
     throw error
   }
 }
 
 async function getSetting(name) {
   try {
     const setting = await sql`
       select * from settings
       where name = ${name}
     `
     return setting
   } catch (error) {
     console.error('Error occurred in getSetting:', error);
     throw error
   }
 }
 
 async function allSettings() {
   try {
     const settings = await sql`
       select * from settings
     `
     return settings
   } catch (error) {
     console.error('Error occurred in allSettings:', error);
     throw error
   }
 }
 
 async function removeSetting(name) {
   try {
     const setting = await sql`
       delete from settings where name = ${name}
       returning *
     `
     return setting
   } catch (error) {
     console.error('Error occurred in removeSetting:', error);
     throw error
   }
 }
 
 // "upsert" setting
 async function updateSetting(name, value) {
   try {
     const setting = await sql`
       insert into public.settings as s (name, value)
         values (${name}, ${value})
       on conflict (name) do update
       set value = ${value}
       where s.name = ${name}
       returning *
     `
     return setting
   } catch (error) {
     console.error('Error occurred in updateSetting:', error);
     throw error
   }
 }
 
 function removeClient(sock) {
   console.log("- Client removed")
   clients.delete(sock)
 }
 
 function addClient(sock) {
   console.log("+ Client added")
   clients.add(sock)
 }
 
 // add client to subscription
 async function watchScores(sock) {
   if (scoreSubscription !== null) {
     // we are already subscribed to this publication
     return scoreSubscription
   }
 
   // subscribe to score publication
   try {
     scoreSubscription = await sql.subscribe(
       '*:scores',
       (row, { command, relation, key, old }) => {
         // distributed score updates to all connected clients
         for(let c of clients) {
           c.send(JSON.stringify({
             "message": "Received new scores",
             "data": row,
           }))
         }
       },
       () => {
         // callback on initial connect and potential reconnects
         console.log("~ Start watching scores, created scoreSubscription")
       }
     )
     return scoreSubscription
   } catch (error) {
     console.error('Error occurred in watchScores:', error);
     throw error
   }
 }
 
 module.exports = {
   sql,
   invalidateToken,
   lookupToken,
   saveToken,
   getUser,
   allHeats,
   getHeat,
   allAthletes,
   newHeat,
   removeHeat,
   distinctStartlist,
   startlistWithAthletes,
   scoresForHeatAndAthlete,
   scoreSummaryForHeatAndAthlete,
   getScore,
   setScore,
   watchScores,
   removeClient,
   addClient,
   addAthleteToHeat,
   addAthlete,
   removeAthlete,
   removeAthleteFromHeat,
   getSetting,
   allSettings,
   updateSetting,
   removeSetting,
 }