GeezerVison is showing github.com — Databases/JavaScript/db.js at master · HowProgrammingWorks/Databases

Expand file tree

db.js

Copy path

More file actions

Latest commit

History

176 lines (158 loc) · 4.04 KB

db.js

Copy path

File metadata and controls

176 lines (158 loc) · 4.04 KB

Raw

Copy raw file

Download raw file

Open symbols panel

Edit and raw actions

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

'use strict';

const { Pool } = require('pg');

const where = (conditions) => {

let clause = '';

const args = [];

let i = 1;

for (const key in conditions) {

let value = conditions[key];

let condition;

if (typeof value === 'number') {

condition = `${key} = $${i}`;

} else if (typeof value === 'string') {

if (value.startsWith('>=')) {

condition = `${key} >= $${i}`;

value = value.substring(2);

} else if (value.startsWith('<=')) {

condition = `${key} <= $${i}`;

value = value.substring(2);

} else if (value.startsWith('<>')) {

condition = `${key} <> $${i}`;

value = value.substring(2);

} else if (value.startsWith('>')) {

condition = `${key} > $${i}`;

value = value.substring(1);

} else if (value.startsWith('<')) {

condition = `${key} < $${i}`;

value = value.substring(1);

} else if (value.includes('*') || value.includes('?')) {

value = value.replace(/\*/g, '%').replace(/\?/g, '_');

condition = `${key} LIKE $${i}`;

} else {

condition = `${key} = $${i}`;

}

i++;

args.push(value);

clause = clause ? `${clause} AND ${condition}` : condition;

}

return { clause, args };

};

const MODE_ROWS = 0;

const MODE_VALUE = 1;

const MODE_ROW = 2;

const MODE_COL = 3;

const MODE_COUNT = 4;

class Cursor {

constructor(database, table) {

this.database = database;

this.table = table;

this.cols = null;

this.rows = null;

this.rowCount = 0;

this.ready = false;

this.mode = MODE_ROWS;

this.whereClause = undefined;

this.columns = ['*'];

this.args = [];

this.orderBy = undefined;

}

resolve(result) {

const { rows, fields, rowCount } = result;

this.rows = rows;

this.cols = fields;

this.rowCount = rowCount;

}

where(conditions) {

const { clause, args } = where(conditions);

this.whereClause = clause;

this.args = args;

return this;

}

fields(list) {

this.columns = list;

return this;

}

value() {

this.mode = MODE_VALUE;

return this;

}

row() {

this.mode = MODE_ROW;

return this;

}

col(name) {

this.mode = MODE_COL;

this.columnName = name;

return this;

}

count() {

this.mode = MODE_COUNT;

return this;

}

order(name) {

this.orderBy = name;

return this;

}

then(callback) {

// TODO: store callback to pool

const { mode, table, columns, args } = this;

const { whereClause, orderBy, columnName } = this;

const fields = columns.join(', ');

let sql = `SELECT ${fields} FROM ${table}`;

if (whereClause) sql += ` WHERE ${whereClause}`;

if (orderBy) sql += ` ORDER BY ${orderBy}`;

this.database.query(sql, args, (err, res) => {

this.resolve(res);

const { rows, cols } = this;

if (mode === MODE_VALUE) {

const col = cols[0];

const row = rows[0];

callback(row[col.name]);

} else if (mode === MODE_ROW) {

callback(rows[0]);

} else if (mode === MODE_COL) {

const col = [];

for (const row of rows) {

col.push(row[columnName]);

}

callback(col);

} else if (mode === MODE_COUNT) {

callback(this.rowCount);

} else {

callback(rows);

}

});

return this;

}

class Database {

constructor(config, logger) {

this.pool = new Pool(config);

this.config = config;

this.logger = logger;

}

query(sql, values, callback) {

if (typeof values === 'function') {

callback = values;

values = [];

}

const startTime = new Date().getTime();

console.log({ sql, values });

this.pool.query(sql, values, (err, res) => {

const endTime = new Date().getTime();

const executionTime = endTime - startTime;

console.log(`Execution time: ${executionTime}`);

callback?.(err, res);

});

}

select(table) {

return new Cursor(this, table);

}

close() {

this.pool.end();

}

module.exports = {

open: (config, logger) => new Database(config, logger),

};