11980915260

Committed 22 Nov 2024 10:10PM UTC coverage: 78.492% (-1.7%) from 80.158%

Build # 11980915260

Build Type

push

github

Committed by

web-flow

Commit Message

Switch to a relational database (#637)

* It boots from SQL

* progress on loading playlists

* Use uppercase ID

* Search based on SQL media

* add userID to history entry schema

* stash

* Migrate history, read history

* Typed IDs; move mostly to new schema types

* Migrate authentication model to SQL

* Update unique constraints

* Fix lodash import

* Select the right stuff from users

* Use Object.groupBy

* Use order column for playlist sorting?

The other option is to have a JSON column with IDs on the playlists
table.

* Add linting for JSDoc

* SQL config store

* stash

* Bump kysely

* Different way to store playlist item order

* Opaque -> Tagged

* Port bans

* deps: update better-sqlite3

* Remove mongodb connection code

* Adding playlist items with sql?

* Revert "Remove mongodb connection code"

This reverts commit 8b2ae37e6.

* Make migrations work in sql

* Try with SQLite

* Migrate auth passwords

* Better Date support for SQLite

* Use json_each

* use json_array_length

* SQLite utility functions

* Fix property name in test

* playlist shuffle and cycle with sqlite

* Use a flat list of permissions

* Various test fixes

* Ban test sorta working

* small test fixes

* acl fixes

* some more json sqlite fixes

* serialize active playlist id

* Implement playlist updates with sql

* More JSON fun

* users test fixes

* test fixes for bans and /now

* finish redis connection before changing configs

* User avatar / roles return values

* test ID fix

* Fix playlist item serialization

* implement removing playlist items

* put comment

* Fix issues due to playlist position options

* disable sql query logging

* various sql booth fixes

* Test fixes by moving to new data structure

* Inline the email function

* Fix email test

* This map is a multi map

* fix playlist item filte... (continued)

Run Details

757 of 912 branches covered (83.0%)

Branch coverage included in aggregate %.

2001 of 2791 new or added lines in 52 files covered. (71.69%)

9 existing lines in 7 files now uncovered.

8666 of 11093 relevant lines covered (78.12%)

70.72 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

91.52

/src/utils/sqlite.js

import lodash from 'lodash';
import { sql, OperationNodeTransformer } from 'kysely';

/**
 * @template {unknown[]} T
 * @param {import('kysely').Expression<T>} expr
 * @returns {import('kysely').RawBuilder<{
 *   key: unknown,
 *   value: T[0],
 *   type: string,
 *   atom: T[0],
 *   id: number,
 *   parent: number,
 *   fullkey: string,
 *   path: string,
 * }>}
 */
export function jsonEach(expr) {
  return sql`json_each(${expr})`;
}

/**
 * @template {unknown[]} T
 * @param {import('kysely').Expression<T>} expr
 * @returns {import('kysely').RawBuilder<number>}
 */
export function jsonLength(expr) {
  return sql`json_array_length(${expr})`;
}

/**
 * @param {import('type-fest').Jsonifiable} value
 * @returns {import('kysely').RawBuilder<any>}
 */
export function jsonb(value) {
  return sql`jsonb(${JSON.stringify(value)})`;
}

/**
 * @template {unknown[]} T
 * @param {import('kysely').Expression<T>} expr
 * @returns {import('kysely').RawBuilder<T>}
 */
export function json(expr) {
  return sql`json(${expr})`;
}

/**
 * @template {unknown[]} T
 * @param {import('kysely').Expression<T>} expr
 * @returns {import('kysely').RawBuilder<T>}
 */
export function arrayShuffle(expr) {
  return sql`jsonb(json_array_shuffle(${json(expr)}))`;
}

/**
 * @template {unknown[]} T
 * @param {import('kysely').Expression<T>} expr
 * @returns {import('kysely').RawBuilder<T>}
 */
export function arrayCycle(expr) {
  return sql`
    (CASE ${jsonLength(expr)}
      WHEN 0 THEN (${expr})
      ELSE jsonb_insert(
        jsonb_remove((${expr}), '$[0]'),
        '$[#]',
        (${expr})->>0
      )
    END)
  `;
}

/**
 * @template {unknown} T
 * @param {import('kysely').Expression<T>} expr
 * @returns {import('kysely').RawBuilder<T[]>}
 */
export function jsonGroupArray(expr) {
  return sql`json_group_array(${expr})`;
}

/** @type {import('kysely').RawBuilder<Date>} */
export const now = sql`(strftime('%FT%TZ', 'now'))`;

/** Stringify dates before entering them in the database. */
class SqliteDateTransformer extends OperationNodeTransformer {
  /** @param {import('kysely').ValueNode} node */
  transformValue(node) {
    if (node.value instanceof Date) {
      return { ...node, value: node.value.toISOString() };
    }
    return node;
  }

  /** @param {import('kysely').PrimitiveValueListNode} node */
  transformPrimitiveValueList(node) {
    return {
      ...node,
      values: node.values.map((value) => {
        if (value instanceof Date) {
          return value.toISOString();
        }
        return value;
      }),
    };
  }

  /** @param {import('kysely').ColumnUpdateNode} node */
  transformColumnUpdate(node) {
    /**
     * @param {import('kysely').OperationNode} node
     * @returns {node is import('kysely').ValueNode}
     */
    function isValueNode(node) {
      return node.kind === 'ValueNode';
    }

    if (isValueNode(node.value) && node.value.value instanceof Date) {
      return super.transformColumnUpdate({
        ...node,
        value: /** @type {import('kysely').ValueNode} */ ({
          ...node.value,
          value: node.value.value.toISOString(),
        }),
      });
    }
    return super.transformColumnUpdate(node);
  }
}

export class SqliteDateColumnsPlugin {
  /** @param {string[]} dateColumns */
  constructor(dateColumns) {
    this.dateColumns = new Set(dateColumns);
    this.transformer = new SqliteDateTransformer();
  }

  /** @param {import('kysely').PluginTransformQueryArgs} args */
  transformQuery(args) {
    return this.transformer.transformNode(args.node);
  }

  /** @param {string} col */
  #isDateColumn(col) {
    if (this.dateColumns.has(col)) {
      return true;
    }
    const i = col.lastIndexOf('.');
    return i !== -1 && this.dateColumns.has(col.slice(i));
  }

  /** @param {import('kysely').PluginTransformResultArgs} args */
  async transformResult(args) {
    for (const row of args.result.rows) {
      for (let col in row) { // eslint-disable-line no-restricted-syntax
        if (Object.hasOwn(row, col) && this.#isDateColumn(col)) {
          const value = row[col];
          if (typeof value === 'string') {
            row[col] = new Date(value);
          }
        }
      }
    }
    return args.result;
  }
}

/**
 * @param {string} path
 * @returns {Promise<import('better-sqlite3').Database>}
 */
export async function connect(path) {
  const { default: Database } = await import('better-sqlite3');
  const db = new Database(path ?? 'uwave_local.sqlite');
  db.pragma('journal_mode = WAL');
  db.pragma('foreign_keys = ON');
  db.function('json_array_shuffle', { directOnly: true }, (items) => {
    if (typeof items !== 'string') {
      throw new TypeError('json_array_shuffle(): items must be JSON string');
    }
    const array = JSON.parse(items);
    if (!Array.isArray(array)) {
      throw new TypeError('json_array_shuffle(): items must be JSON array');
    }
    return JSON.stringify(lodash.shuffle(array));
  });
  return db;
}

1	import lodash from 'lodash';	1✔
2	import { sql, OperationNodeTransformer } from 'kysely';	1✔
3		1✔
4	/**	1✔
5	* @template {unknown[]} T	1✔
6	* @param {import('kysely').Expression<T>} expr	1✔
7	* @returns {import('kysely').RawBuilder<{	1✔
8	* key: unknown,	1✔
9	* value: T[0],	1✔
10	* type: string,	1✔
11	* atom: T[0],	1✔
12	* id: number,	1✔
13	* parent: number,	1✔
14	* fullkey: string,	1✔
15	* path: string,	1✔
16	* }>}	1✔
17	*/	1✔
18	export function jsonEach(expr) {	1✔
19	return sql`json_each(${expr})`;	61✔
20	}	61✔
21		1✔
22	/**	1✔
23	* @template {unknown[]} T	1✔
24	* @param {import('kysely').Expression<T>} expr	1✔
25	* @returns {import('kysely').RawBuilder<number>}	1✔
26	*/	1✔
27	export function jsonLength(expr) {	1✔
28	return sql`json_array_length(${expr})`;	123✔
29	}	123✔
30		1✔
31	/**	1✔
32	* @param {import('type-fest').Jsonifiable} value	1✔
33	* @returns {import('kysely').RawBuilder<any>}	1✔
34	*/	1✔
35	export function jsonb(value) {	1✔
36	return sql`jsonb(${JSON.stringify(value)})`;	2,600✔
37	}	2,600✔
38		1✔
39	/**	1✔
40	* @template {unknown[]} T	1✔
41	* @param {import('kysely').Expression<T>} expr	1✔
42	* @returns {import('kysely').RawBuilder<T>}	1✔
43	*/	1✔
44	export function json(expr) {	1✔
45	return sql`json(${expr})`;	1✔
46	}	1✔
47		1✔
48	/**	1✔
49	* @template {unknown[]} T	1✔
50	* @param {import('kysely').Expression<T>} expr	1✔
51	* @returns {import('kysely').RawBuilder<T>}	1✔
52	*/	1✔
53	export function arrayShuffle(expr) {	1✔
54	return sql`jsonb(json_array_shuffle(${json(expr)}))`;	1✔
55	}	1✔
56		1✔
57	/**	1✔
58	* @template {unknown[]} T	1✔
59	* @param {import('kysely').Expression<T>} expr	1✔
60	* @returns {import('kysely').RawBuilder<T>}	1✔
61	*/	1✔
62	export function arrayCycle(expr) {	1✔
63	return sql`	6✔
64	(CASE ${jsonLength(expr)}	6✔
65	WHEN 0 THEN (${expr})	6✔
66	ELSE jsonb_insert(	6✔
67	jsonb_remove((${expr}), '$[0]'),	6✔
68	'$[#]',	6✔
69	(${expr})->>0	6✔
70	)	6✔
71	END)	6✔
72	`;	6✔
73	}	6✔
74		1✔
75	/**	1✔
76	* @template {unknown} T	1✔
77	* @param {import('kysely').Expression<T>} expr	1✔
78	* @returns {import('kysely').RawBuilder<T[]>}	1✔
79	*/	1✔
80	export function jsonGroupArray(expr) {	1✔
81	return sql`json_group_array(${expr})`;	186✔
82	}	186✔
83		1✔
84	/** @type {import('kysely').RawBuilder<Date>} */	1✔
85	export const now = sql`(strftime('%FT%TZ', 'now'))`;	1✔
86		1✔
87	/** Stringify dates before entering them in the database. */	1✔
88	class SqliteDateTransformer extends OperationNodeTransformer {	1✔
89	/** @param {import('kysely').ValueNode} node */	1✔
90	transformValue(node) {	1✔
91	if (node.value instanceof Date) {	13,932!
NEW 92	return { ...node, value: node.value.toISOString() };	×
NEW 93	}	×
94	return node;	13,932✔
95	}	13,932✔
96		1✔
97	/** @param {import('kysely').PrimitiveValueListNode} node */	1✔
98	transformPrimitiveValueList(node) {	1✔
99	return {	1,967✔
100	...node,	1,967✔
101	values: node.values.map((value) => {	1,967✔
102	if (value instanceof Date) {	11,793✔
103	return value.toISOString();	5✔
104	}	5✔
105	return value;	11,788✔
106	}),	1,967✔
107	};	1,967✔
108	}	1,967✔
109		1✔
110	/** @param {import('kysely').ColumnUpdateNode} node */	1✔
111	transformColumnUpdate(node) {	1✔
112	/**	684✔
113	* @param {import('kysely').OperationNode} node	684✔
114	* @returns {node is import('kysely').ValueNode}	684✔
115	*/	684✔
116	function isValueNode(node) {	684✔
117	return node.kind === 'ValueNode';	684✔
118	}	684✔
119		684✔
120	if (isValueNode(node.value) && node.value.value instanceof Date) {	684!
NEW 121	return super.transformColumnUpdate({	×
NEW 122	...node,	×
NEW 123	value: /** @type {import('kysely').ValueNode} */ ({	×
NEW 124	...node.value,	×
NEW 125	value: node.value.value.toISOString(),	×
NEW 126	}),	×
NEW 127	});	×
NEW 128	}	×
129	return super.transformColumnUpdate(node);	684✔
130	}	684✔
131	}	1✔
132		1✔
133	export class SqliteDateColumnsPlugin {	1✔
134	/** @param {string[]} dateColumns */	1✔
135	constructor(dateColumns) {	1✔
136	this.dateColumns = new Set(dateColumns);	92✔
137	this.transformer = new SqliteDateTransformer();	92✔
138	}	92✔
139		1✔
140	/** @param {import('kysely').PluginTransformQueryArgs} args */	1✔
141	transformQuery(args) {	1✔
142	return this.transformer.transformNode(args.node);	6,053✔
143	}	6,053✔
144		1✔
145	/** @param {string} col */	1✔
146	#isDateColumn(col) {	1✔
147	if (this.dateColumns.has(col)) {	20,922✔
148	return true;	3,950✔
149	}	3,950✔
150	const i = col.lastIndexOf('.');	16,972✔
151	return i !== -1 && this.dateColumns.has(col.slice(i));	20,922✔
152	}	20,922✔
153		1✔
154	/** @param {import('kysely').PluginTransformResultArgs} args */	1✔
155	async transformResult(args) {	1✔
156	for (const row of args.result.rows) {	6,053✔
157	for (let col in row) { // eslint-disable-line no-restricted-syntax	2,404✔
158	if (Object.hasOwn(row, col) && this.#isDateColumn(col)) {	20,922✔
159	const value = row[col];	3,950✔
160	if (typeof value === 'string') {	3,950✔
161	row[col] = new Date(value);	3,947✔
162	}	3,947✔
163	}	3,950✔
164	}	20,922✔
165	}	2,404✔
166	return args.result;	6,053✔
167	}	6,053✔
168	}	1✔
169		1✔
170	/**	1✔
171	* @param {string} path	1✔
172	* @returns {Promise<import('better-sqlite3').Database>}	1✔
173	*/	1✔
174	export async function connect(path) {	1✔
175	const { default: Database } = await import('better-sqlite3');	92✔
176	const db = new Database(path ?? 'uwave_local.sqlite');	92!
177	db.pragma('journal_mode = WAL');	92✔
178	db.pragma('foreign_keys = ON');	92✔
179	db.function('json_array_shuffle', { directOnly: true }, (items) => {	92✔
180	if (typeof items !== 'string') {	1!
NEW 181	throw new TypeError('json_array_shuffle(): items must be JSON string');	×
NEW 182	}	×
183	const array = JSON.parse(items);	1✔
184	if (!Array.isArray(array)) {	1!
NEW 185	throw new TypeError('json_array_shuffle(): items must be JSON array');	×
NEW 186	}	×
187	return JSON.stringify(lodash.shuffle(array));	1✔
188	});	92✔
189	return db;	92✔
190	}	92✔

u-wave / core / 11980915260

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous