From 9aaf9d2466a026494cd81e0a814efdd00638dec6 Mon Sep 17 00:00:00 2001
From: alex <alex@alexloehr.net>
Date: Thu, 15 May 2025 15:06:48 +0000
Subject: [PATCH] adding db queries

---
 lib/db.js |   74 ++++++++++++++++++++++++
 tst.js    |   61 +++++++++++++------
 2 files changed, 115 insertions(+), 20 deletions(-)

diff --git a/lib/db.js b/lib/db.js
new file mode 100644
index 0000000..b70af0e
--- /dev/null
+++ b/lib/db.js
@@ -0,0 +1,74 @@
+const mysql = require("mysql2/promise")
+
+const {host, port, user, database, password} = require("./../settings").db
+
+/////////////////////////////////////////////////////////////////////////
+
+let poolP = initPool()
+
+async function initPool () {
+   return mysql.createPool({
+      host,
+      port,
+      database,
+      user,
+      password,
+   })
+}
+
+/////////////////////////////////////////////////////////////////////////
+
+module.exports = {
+   getUsers,
+   getUserByLogin,
+   getUserByUserId,
+}
+
+/////////////////////////////////////////////////////////////////////////
+
+async function getUsers (limit = 10, offset = 0) {
+   const sel = `usr_id, login, firstname, lastname, gender, email, institution, street, city, zipcode, country, department, active`
+   // TODO user defined fields
+
+   const pool = await poolP
+   const [results, fields] = await pool.query(
+      `SELECT ${sel}
+       FROM ilias.usr_data AS ud
+       LIMIT ${limit}
+       OFFSET ${offset}
+      `
+   )
+   return results
+}
+
+async function getUserByLogin (login) {
+   const sel = `usr_id, login, firstname, lastname, gender, email, institution, street, city, zipcode, country, department, active`
+   // TODO user defined fields
+
+   const pool = await poolP
+   const [results, fields] = await pool.query(
+      `SELECT ${sel}
+       FROM ilias.usr_data AS ud
+       WHERE login = '${login}'`
+   )
+   return results
+}
+
+async function getUserByUserId (userId) {
+   const sel = `usr_id, login, firstname, lastname, gender, email, institution, street, city, zipcode, country, department, active`
+   // TODO user defined fields
+
+   const pool = await poolP
+   const [results, fields] = await pool.query(
+      `SELECT ${sel}
+       FROM ilias.usr_data AS ud
+       WHERE usr_id = '${userId}'`
+   )
+   return results
+}
+
+/////////////////////////////////////////////////////////////////////////
+
+async function promiseDelay (ms) {
+   return new Promise(resolve => setTimeout(resolve, ms))
+}
diff --git a/tst.js b/tst.js
index f1bb561..4ca3a37 100644
--- a/tst.js
+++ b/tst.js
@@ -1,4 +1,5 @@
 const mysql = require("mysql2/promise")
+const db = require("./lib/db")
 
 const {host, port, user, database, password} = require("./settings").db
 
@@ -11,26 +12,46 @@
 
 /////////////////////////////////////////////////////////////////////////
 
-async function run () {
-   // Create the connection to database
-   console.log(111111111111,mysql)
-   const connection = await mysql.createConnection({
-      host,
-      port,
-      database,
-      user,
-      password,
-   })
+async function run() {
+   // const user = await db.getUserByLogin("242424")
+   // console.log("user", user)
+   //
+   // const user2 = await db.getUserByUserId(6)
+   // console.log("user2", user2)
 
-// A simple SELECT query
-   try {
-      const [results, fields] = await connection.query(
-         `SELECT ud.* FROM ilias.usr_data AS ud WHERE login = '242424'`
-      )
+   const users = await db.getUsers()
+   const users2 = users.map(({usr_id,login,firstname,lastname,email,institution}) => ({usr_id,login,firstname,lastname,institution}))
+   console.table(users2)
+}
 
-      console.log(results) // results contains rows returned by server
-      console.log(fields) // fields contains extra meta data about results, if available
-   } catch (err) {
-      console.log(err)
-   }
+// async function run () {
+//    // Create the connection to database
+//    // console.log(111111111111,mysql)
+//    const connection = await mysql.createConnection({
+//       host,
+//       port,
+//       database,
+//       user,
+//       password,
+//    })
+//
+// // A simple SELECT query
+//    try {
+//       const sel = `usr_id, login, firstname, lastname, gender, email, institution, street, city, zipcode, country, department, active`
+//       // const sel = "*"
+//       const [results, fields] = await connection.query(
+//          `SELECT ${sel} FROM ilias.usr_data AS ud WHERE login = '242424'`
+//       )
+//
+//       console.log(results) // results contains rows returned by server
+//       // console.log(fields) // fields contains extra meta data about results, if available
+//    } catch (err) {
+//       console.log(err)
+//    }
+// }
+
+/////////////////////////////////////////////////////////////////////////
+
+async function promiseDelay(ms) {
+    return new Promise(resolve => setTimeout(resolve, ms))
 }

--
Gitblit v1.8.0