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

99.5

/src/migrations/002-sql.cjs

'use strict';

const { sql } = require('kysely');

const now = sql`(strftime('%FT%TZ', 'now'))`;
const emptyArray = sql`(jsonb('[]'))`;

/**
 * @param {import('umzug').MigrationParams<import('../Uwave').default>} params
 */
async function up({ context: uw }) {
  const { db } = uw;

  await db.schema.createTable('configuration')
    .addColumn('name', 'text', (col) => col.primaryKey())
    .addColumn('value', 'jsonb')
    .execute();

  await db.schema.createTable('media')
    .addColumn('id', 'uuid', (col) => col.primaryKey())
    .addColumn('source_type', 'text', (col) => col.notNull())
    .addColumn('source_id', 'text', (col) => col.notNull())
    .addColumn('source_data', 'jsonb')
    .addColumn('artist', 'text', (col) => col.notNull())
    .addColumn('title', 'text', (col) => col.notNull())
    .addColumn('duration', 'integer', (col) => col.notNull())
    .addColumn('thumbnail', 'text', (col) => col.notNull())
    .addColumn('created_at', 'timestamp', (col) => col.notNull().defaultTo(now))
    .addColumn('updated_at', 'timestamp', (col) => col.notNull().defaultTo(now))
    .addUniqueConstraint('media_source_key', ['source_type', 'source_id'])
    .execute();

  await db.schema.createTable('users')
    .addColumn('id', 'uuid', (col) => col.primaryKey())
    .addColumn('username', 'text', (col) => col.notNull().unique())
    .addColumn('email', 'text')
    .addColumn('password', 'text')
    .addColumn('slug', 'text', (col) => col.notNull().unique())
    .addColumn('avatar', 'text')
    .addColumn('pending_activation', 'boolean', (col) => col.defaultTo(null))
    .addColumn('created_at', 'timestamp', (col) => col.notNull().defaultTo(now))
    .addColumn('updated_at', 'timestamp', (col) => col.notNull().defaultTo(now))
    .addUniqueConstraint('user_email', ['email'])
    .execute();

  await db.schema.createTable('roles')
    .addColumn('id', 'text', (col) => col.primaryKey())
    .addColumn('permissions', 'jsonb', (col) => col.notNull())
    .execute();

  await db.schema.createTable('user_roles')
    .addColumn('userID', 'uuid', (col) => col.references('users.id'))
    .addColumn('role', 'text', (col) => col.references('roles.id'))
    .addUniqueConstraint('unique_user_role', ['userID', 'role'])
    .execute();

  await db.schema.createTable('bans')
    .addColumn('user_id', 'uuid', (col) => col.primaryKey().references('users.id'))
    .addColumn('moderator_id', 'uuid', (col) => col.notNull().references('users.id'))
    .addColumn('expires_at', 'timestamp')
    .addColumn('reason', 'text')
    .addColumn('created_at', 'timestamp', (col) => col.notNull().defaultTo(now))
    .addColumn('updated_at', 'timestamp', (col) => col.notNull().defaultTo(now))
    .execute();

  await db.schema.createTable('mutes')
    .addColumn('user_id', 'uuid', (col) => col.notNull().references('users.id'))
    .addColumn('moderator_id', 'uuid', (col) => col.notNull().references('users.id'))
    .addColumn('expires_at', 'timestamp', (col) => col.notNull())
    .addColumn('created_at', 'timestamp', (col) => col.notNull().defaultTo(now))
    .addColumn('updated_at', 'timestamp', (col) => col.notNull().defaultTo(now))
    .execute();

  await db.schema.createTable('auth_services')
    .addColumn('user_id', 'uuid', (col) => col.notNull().references('users.id'))
    .addColumn('service', 'text', (col) => col.notNull())
    .addColumn('service_id', 'text', (col) => col.notNull())
    .addColumn('service_avatar', 'text')
    .addColumn('created_at', 'timestamp', (col) => col.notNull().defaultTo(now))
    .addColumn('updated_at', 'timestamp', (col) => col.notNull().defaultTo(now))
    .addUniqueConstraint('user_auth_service', ['user_id', 'service'])
    .addUniqueConstraint('auth_service', ['service', 'service_id'])
    .execute();

  await db.schema.createTable('playlists')
    .addColumn('id', 'uuid', (col) => col.primaryKey())
    .addColumn('name', 'text', (col) => col.notNull())
    .addColumn('user_id', 'uuid', (col) => col.notNull().references('users.id'))
    .addColumn('created_at', 'timestamp', (col) => col.notNull().defaultTo(now))
    .addColumn('updated_at', 'timestamp', (col) => col.notNull().defaultTo(now))
    .addColumn('items', 'jsonb', (col) => col.notNull().defaultTo(emptyArray))
    .execute();

  await db.schema.createTable('playlist_items')
    .addColumn('id', 'uuid', (col) => col.primaryKey())
    .addColumn('playlist_id', 'uuid', (col) => col.notNull().references('playlists.id'))
    .addColumn('media_id', 'uuid', (col) => col.notNull().references('media.id'))
    .addColumn('artist', 'text', (col) => col.notNull())
    .addColumn('title', 'text', (col) => col.notNull())
    .addColumn('start', 'integer', (col) => col.notNull())
    .addColumn('end', 'integer', (col) => col.notNull())
    .addColumn('created_at', 'timestamp', (col) => col.notNull().defaultTo(now))
    .addColumn('updated_at', 'timestamp', (col) => col.notNull().defaultTo(now))
    .execute();

  await db.schema.createTable('history_entries')
    .addColumn('id', 'uuid', (col) => col.primaryKey())
    .addColumn('user_id', 'uuid', (col) => col.notNull().references('users.id'))
    .addColumn('media_id', 'uuid', (col) => col.notNull().references('media.id'))
    .addColumn('artist', 'text', (col) => col.notNull())
    .addColumn('title', 'text', (col) => col.notNull())
    .addColumn('start', 'integer', (col) => col.notNull())
    .addColumn('end', 'integer', (col) => col.notNull())
    .addColumn('source_data', 'jsonb')
    .addColumn('created_at', 'timestamp', (col) => col.notNull().defaultTo(now))
    .execute();

  await db.schema.createTable('feedback')
    .addColumn('history_entry_id', 'uuid', (col) => col.notNull().references('historyEntries.id'))
    .addColumn('user_id', 'uuid', (col) => col.notNull().references('users.id'))
    .addColumn('vote', 'integer', (col) => col.defaultTo(0))
    .addColumn('favorite', 'integer', (col) => col.defaultTo(0))
    .addUniqueConstraint('one_vote_per_user', ['history_entry_id', 'user_id'])
    .execute();

  await db.schema.alterTable('users')
    .addColumn('active_playlist_id', 'uuid', (col) => col.references('playlists.id'))
    .execute();
}

/**
 * @param {import('umzug').MigrationParams<import('../Uwave').default>} params
 */
async function down() {}

module.exports = { up, down };

1	'use strict';	1✔
2		1✔
3	const { sql } = require('kysely');	1✔
4		1✔
5	const now = sql`(strftime('%FT%TZ', 'now'))`;	1✔
6	const emptyArray = sql`(jsonb('[]'))`;	1✔
7		1✔
8	/**	1✔
9	* @param {import('umzug').MigrationParams<import('../Uwave').default>} params	1✔
10	*/	1✔
11	async function up({ context: uw }) {	92✔
12	const { db } = uw;	92✔
13		92✔
14	await db.schema.createTable('configuration')	92✔
15	.addColumn('name', 'text', (col) => col.primaryKey())	92✔
16	.addColumn('value', 'jsonb')	92✔
17	.execute();	92✔
18		92✔
19	await db.schema.createTable('media')	92✔
20	.addColumn('id', 'uuid', (col) => col.primaryKey())	92✔
21	.addColumn('source_type', 'text', (col) => col.notNull())	92✔
22	.addColumn('source_id', 'text', (col) => col.notNull())	92✔
23	.addColumn('source_data', 'jsonb')	92✔
24	.addColumn('artist', 'text', (col) => col.notNull())	92✔
25	.addColumn('title', 'text', (col) => col.notNull())	92✔
26	.addColumn('duration', 'integer', (col) => col.notNull())	92✔
27	.addColumn('thumbnail', 'text', (col) => col.notNull())	92✔
28	.addColumn('created_at', 'timestamp', (col) => col.notNull().defaultTo(now))	92✔
29	.addColumn('updated_at', 'timestamp', (col) => col.notNull().defaultTo(now))	92✔
30	.addUniqueConstraint('media_source_key', ['source_type', 'source_id'])	92✔
31	.execute();	92✔
32		92✔
33	await db.schema.createTable('users')	92✔
34	.addColumn('id', 'uuid', (col) => col.primaryKey())	92✔
35	.addColumn('username', 'text', (col) => col.notNull().unique())	92✔
36	.addColumn('email', 'text')	92✔
37	.addColumn('password', 'text')	92✔
38	.addColumn('slug', 'text', (col) => col.notNull().unique())	92✔
39	.addColumn('avatar', 'text')	92✔
40	.addColumn('pending_activation', 'boolean', (col) => col.defaultTo(null))	92✔
41	.addColumn('created_at', 'timestamp', (col) => col.notNull().defaultTo(now))	92✔
42	.addColumn('updated_at', 'timestamp', (col) => col.notNull().defaultTo(now))	92✔
43	.addUniqueConstraint('user_email', ['email'])	92✔
44	.execute();	92✔
45		92✔
46	await db.schema.createTable('roles')	92✔
47	.addColumn('id', 'text', (col) => col.primaryKey())	92✔
48	.addColumn('permissions', 'jsonb', (col) => col.notNull())	92✔
49	.execute();	92✔
50		92✔
51	await db.schema.createTable('user_roles')	92✔
52	.addColumn('userID', 'uuid', (col) => col.references('users.id'))	92✔
53	.addColumn('role', 'text', (col) => col.references('roles.id'))	92✔
54	.addUniqueConstraint('unique_user_role', ['userID', 'role'])	92✔
55	.execute();	92✔
56		92✔
57	await db.schema.createTable('bans')	92✔
58	.addColumn('user_id', 'uuid', (col) => col.primaryKey().references('users.id'))	92✔
59	.addColumn('moderator_id', 'uuid', (col) => col.notNull().references('users.id'))	92✔
60	.addColumn('expires_at', 'timestamp')	92✔
61	.addColumn('reason', 'text')	92✔
62	.addColumn('created_at', 'timestamp', (col) => col.notNull().defaultTo(now))	92✔
63	.addColumn('updated_at', 'timestamp', (col) => col.notNull().defaultTo(now))	92✔
64	.execute();	92✔
65		92✔
66	await db.schema.createTable('mutes')	92✔
67	.addColumn('user_id', 'uuid', (col) => col.notNull().references('users.id'))	92✔
68	.addColumn('moderator_id', 'uuid', (col) => col.notNull().references('users.id'))	92✔
69	.addColumn('expires_at', 'timestamp', (col) => col.notNull())	92✔
70	.addColumn('created_at', 'timestamp', (col) => col.notNull().defaultTo(now))	92✔
71	.addColumn('updated_at', 'timestamp', (col) => col.notNull().defaultTo(now))	92✔
72	.execute();	92✔
73		92✔
74	await db.schema.createTable('auth_services')	92✔
75	.addColumn('user_id', 'uuid', (col) => col.notNull().references('users.id'))	92✔
76	.addColumn('service', 'text', (col) => col.notNull())	92✔
77	.addColumn('service_id', 'text', (col) => col.notNull())	92✔
78	.addColumn('service_avatar', 'text')	92✔
79	.addColumn('created_at', 'timestamp', (col) => col.notNull().defaultTo(now))	92✔
80	.addColumn('updated_at', 'timestamp', (col) => col.notNull().defaultTo(now))	92✔
81	.addUniqueConstraint('user_auth_service', ['user_id', 'service'])	92✔
82	.addUniqueConstraint('auth_service', ['service', 'service_id'])	92✔
83	.execute();	92✔
84		92✔
85	await db.schema.createTable('playlists')	92✔
86	.addColumn('id', 'uuid', (col) => col.primaryKey())	92✔
87	.addColumn('name', 'text', (col) => col.notNull())	92✔
88	.addColumn('user_id', 'uuid', (col) => col.notNull().references('users.id'))	92✔
89	.addColumn('created_at', 'timestamp', (col) => col.notNull().defaultTo(now))	92✔
90	.addColumn('updated_at', 'timestamp', (col) => col.notNull().defaultTo(now))	92✔
91	.addColumn('items', 'jsonb', (col) => col.notNull().defaultTo(emptyArray))	92✔
92	.execute();	92✔
93		92✔
94	await db.schema.createTable('playlist_items')	92✔
95	.addColumn('id', 'uuid', (col) => col.primaryKey())	92✔
96	.addColumn('playlist_id', 'uuid', (col) => col.notNull().references('playlists.id'))	92✔
97	.addColumn('media_id', 'uuid', (col) => col.notNull().references('media.id'))	92✔
98	.addColumn('artist', 'text', (col) => col.notNull())	92✔
99	.addColumn('title', 'text', (col) => col.notNull())	92✔
100	.addColumn('start', 'integer', (col) => col.notNull())	92✔
101	.addColumn('end', 'integer', (col) => col.notNull())	92✔
102	.addColumn('created_at', 'timestamp', (col) => col.notNull().defaultTo(now))	92✔
103	.addColumn('updated_at', 'timestamp', (col) => col.notNull().defaultTo(now))	92✔
104	.execute();	92✔
105		92✔
106	await db.schema.createTable('history_entries')	92✔
107	.addColumn('id', 'uuid', (col) => col.primaryKey())	92✔
108	.addColumn('user_id', 'uuid', (col) => col.notNull().references('users.id'))	92✔
109	.addColumn('media_id', 'uuid', (col) => col.notNull().references('media.id'))	92✔
110	.addColumn('artist', 'text', (col) => col.notNull())	92✔
111	.addColumn('title', 'text', (col) => col.notNull())	92✔
112	.addColumn('start', 'integer', (col) => col.notNull())	92✔
113	.addColumn('end', 'integer', (col) => col.notNull())	92✔
114	.addColumn('source_data', 'jsonb')	92✔
115	.addColumn('created_at', 'timestamp', (col) => col.notNull().defaultTo(now))	92✔
116	.execute();	92✔
117		92✔
118	await db.schema.createTable('feedback')	92✔
119	.addColumn('history_entry_id', 'uuid', (col) => col.notNull().references('historyEntries.id'))	92✔
120	.addColumn('user_id', 'uuid', (col) => col.notNull().references('users.id'))	92✔
121	.addColumn('vote', 'integer', (col) => col.defaultTo(0))	92✔
122	.addColumn('favorite', 'integer', (col) => col.defaultTo(0))	92✔
123	.addUniqueConstraint('one_vote_per_user', ['history_entry_id', 'user_id'])	92✔
124	.execute();	92✔
125		92✔
126	await db.schema.alterTable('users')	92✔
127	.addColumn('active_playlist_id', 'uuid', (col) => col.references('playlists.id'))	92✔
128	.execute();	92✔
129	}	92✔
130		1✔
131	/**	1✔
132	* @param {import('umzug').MigrationParams<import('../Uwave').default>} params	1✔
133	*/	1✔
NEW 134	async function down() {}	×
135		1✔
136	module.exports = { up, down };	1✔

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