11365461971

Committed 16 Oct 2024 12:18PM UTC coverage: 31.475% (-0.2%) from 31.693%

Build # 11365461971

Build Type

push

github

Committed by

web-flow

Commit Message

Merge pull request #9861 from ForumMagnum/simplify-migration-workflow

Simplify migration workflow

Run Details

5012 of 41789 branches covered (11.99%)

Branch coverage included in aggregate %.

9 of 16 new or added lines in 3 files covered. (56.25%)

9 existing lines in 3 files now uncovered.

29430 of 67636 relevant lines covered (43.51%)

174.34 hits per line

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

14.57

/packages/lesswrong/server/scripts/generateSQLSchema.ts

import { format as sqlFormatter } from 'sql-formatter';
import { Globals } from "../vulcan-lib";
import { getAllCollections, isValidCollectionName } from "../../lib/vulcan-lib/getCollection";
import Table from "@/server/sql/Table";
import CreateTableQuery from "@/server/sql/CreateTableQuery";
import { writeFileSync } from 'node:fs'
import path from 'path';
import { PostgresFunction, postgresFunctions } from '../postgresFunctions';
import { PostgresExtension, postgresExtensions } from '../postgresExtensions';
import CreateExtensionQuery from '@/server/sql/CreateExtensionQuery';
import CreateIndexQuery from '@/server/sql/CreateIndexQuery';
import { sqlInterpolateArgs } from '@/server/sql/Type';
import { CustomPgIndex, expectedCustomPgIndexes } from '../../lib/collectionIndexUtils';
import { PostgresView, getAllPostgresViews } from '../postgresView';
import TableIndex from '@/server/sql/TableIndex';

const ROOT_PATH = path.join(__dirname, "../../../");
const acceptedSchemePath = (rootPath: string) => path.join(rootPath, "schema/accepted_schema.sql");

const schemaFileHeaderTemplate = `-- GENERATED FILE
-- Do not edit this file directly. Instead, start a server and run "yarn generate"
-- as described in the README. This file should nevertheless be checked in to version control.

`

declare global {
  type SchemaDependency =
    {type: "extension", name: PostgresExtension} |
    {type: "collection", name: CollectionNameString} |
    {type: "function", name: string} |
    {type: "view", name: string};
}

abstract class Node {
  public dependencies: SchemaDependency[] = [];

  addDependency(dependency: SchemaDependency) {
    this.dependencies.push(dependency);
  }

  abstract getName(): string;
  abstract getQuery(): {compile(): {sql: string, args: any[]}};

  getHeader(): string {
    const type = this.constructor.name.replace(/(_|Node)/g, "");
    return `-- ${type} "${this.getName()}"`;
  }

  getSource(): string {
    const {sql, args} = this.getQuery().compile();
    return sqlInterpolateArgs(sql, args).trim();
  }

  getAnnotatedSource(): string {
    const source = this.getSource();
    const hasSemi = source[source.length - 1] === ";";
    return `${this.getHeader()}\n${source}${hasSemi ? "" : ";"}\n`;
  }
}

class ExtensionNode extends Node {
  constructor(private extension: PostgresExtension) {
    super();
  }

  getName() {
    return this.extension;
  }

  getQuery() {
    return new CreateExtensionQuery(this.extension);
  }
}

class TableNode extends Node {
  constructor(private table: Table<DbObject>) {
    super();
  }

  getName() {
    return this.table.getName();
  }

  getQuery() {
    return new CreateTableQuery(this.table);
  }
}

class IndexNode extends Node {
  constructor(
    private table: Table<DbObject>,
    private index: TableIndex<DbObject>,
  ) {
    super();
    this.addDependency({
      type: "collection",
      name: table.getName() as CollectionNameString,
    });
  }

  getName() {
    return this.index.getName();
  }

  getQuery() {
    return new CreateIndexQuery({ table: this.table, index: this.index, ifNotExists: true, allowConcurrent: false });
  }
}

class CustomIndexNode extends Node {
  private static nameRegex = /^\s*CREATE\s+(UNIQUE\s+)?INDEX\s+(CONCURRENTLY\s+)?(IF\s+NOT\s+EXISTS\s+)?"?([a-zA-Z0-9_]+)/i;
  private static targetRegex = /.*ON\s+(public\.)?"([A-Za-z0-9_]+)"/i;
  private static concurrentRegex = /\s+CONCURRENTLY\s+/gi;

  private name: string;

  constructor(private index: CustomPgIndex) {
    super();
    const {source, options} = index;
    const name = source.match(CustomIndexNode.nameRegex)?.[4];
    if (!name) {
      throw new Error(`Can't parse name for custom index: ${source}`);
    }
    this.name = name;

    const target = source.match(CustomIndexNode.targetRegex)?.[2];
    if (!target) {
      throw new Error(`Can't parse target for custom index "${name}"`);
    }
    const dependency: SchemaDependency = isValidCollectionName(target)
      ? {type: "collection", name: target}
      : {type: "view", name: target};
    this.addDependency(dependency);

    this.dependencies = this.dependencies.concat(options?.dependencies ?? []);
  }

  getName() {
    return this.name;
  }

  getQuery() {
    return {
      compile: () => ({
        sql: this.index.source.trim().replace(CustomIndexNode.concurrentRegex, " "),
        args: [],
      }),
    };
  }
}

class FunctionNode extends Node {
  private static nameRegex = /^\s*CREATE\s+(OR\s+REPLACE\s+)?FUNCTION\s+([a-zA-Z0-9_]+)/i;

  private name: string;

  constructor(private func: PostgresFunction) {
    super();
    const name = func.source.match(FunctionNode.nameRegex)?.[2];
    if (!name) {
      throw new Error(`Can't find name for function: ${func.source}`);
    }
    const overload = func.overload ? `_${func.overload}` : "";
    this.name = name + overload;
    this.dependencies = this.dependencies.concat(func.dependencies ?? []);
  }

  getName() {
    return this.name;
  }

  getQuery() {
    return {
      compile: () => ({
        sql: this.func.source.trim(),
        args: [],
      }),
    };
  }
}

class ViewNode extends Node {
  constructor(private view: PostgresView) {
    super();
  }

  getName() {
    return this.view.getName();
  }

  getQuery() {
    return {
      compile: () => ({
        sql: this.view.getCreateViewQuery().trim(),
        args: [],
      }),
    };
  }
}

class Graph {
  private nodes: Record<string, Node> = {};

  addNode(node: Node) {
    const name = node.getName();
    if (this.nodes[name]) {
      throw new Error(`Duplicate node names: "${name}"`);
    }
    this.nodes[name] = node;
  }

  addNodes(nodes: Node[]) {
    for (const node of nodes) {
      this.addNode(node);
    }
  }

  linearize(): Node[] {
    const stack: Node[] = [];
    const unvisited = new Set<string>(Object.keys(this.nodes));

    const depthFirstSearch = (nodeName: string, parents: string[]) => {
      if (parents.includes(nodeName)) {
        throw new Error(`Dependency cycle detected for ${nodeName} via ${parents}`);
      }

      const node = this.nodes[nodeName];
      if (!node) {
        throw new Error(`Invalid node: "${nodeName}"`);
      }

      if (!unvisited.has(nodeName)) {
        return;
      }
      unvisited.delete(nodeName);

      for (const dependency of node.dependencies) {
        depthFirstSearch(dependency.name, [...parents, nodeName]);
      }

      stack.push(node);
    }

    unvisited.forEach((node) => depthFirstSearch(node, []));

    return stack;
  }
}

const buildSchemaSQL = () => {
  const graph = new Graph();
  graph.addNodes(postgresExtensions.map((e) => new ExtensionNode(e)));
  graph.addNodes(getAllCollections().flatMap((collection) => {
    const table = Table.fromCollection(collection);
    const indexes: Node[] = table.getRequestedIndexes().map((i) => new IndexNode(table, i));
    return indexes.concat(new TableNode(table));
  }));
  graph.addNodes(expectedCustomPgIndexes.map((i) => new CustomIndexNode(i)));
  graph.addNodes(postgresFunctions.map((f) => new FunctionNode(f)));
  graph.addNodes(getAllPostgresViews().flatMap((view) => {
    const indexQueries = view.getCreateIndexQueries();
    const indexes: Node[] = indexQueries.map((source) => new CustomIndexNode({source}));
    return indexes.concat(new ViewNode(view));
  }));
  const nodes = graph.linearize();
  const rawSchema = nodes.map((n) => n.getAnnotatedSource()).join("\n");
  const schemaFileContents = sqlFormatter(rawSchema, {
    language: "postgresql",
    linesBetweenQueries: 1,
    tabWidth: 2,
    useTabs: false,
    keywordCase: "upper",
    dataTypeCase: "upper",
    functionCase: "upper",
    identifierCase: "preserve",
    logicalOperatorNewline: "after",
    paramTypes: {
      positional: false,
      numbered: [],
      named: [],
      quoted: [],
      custom: [],
    },
  });
  return schemaFileHeaderTemplate + schemaFileContents + "\n";
}

export const generateSQLSchema = (rootPath = ROOT_PATH) => {
  const sqlSchema = buildSchemaSQL();
  writeFileSync(acceptedSchemePath(rootPath), sqlSchema);
}

Globals.generateSQLSchema = generateSQLSchema;

1	import { format as sqlFormatter } from 'sql-formatter';	54✔
2	import { Globals } from "../vulcan-lib";	18✔
3	import { getAllCollections, isValidCollectionName } from "../../lib/vulcan-lib/getCollection";	18✔
4	import Table from "@/server/sql/Table";	18✔
5	import CreateTableQuery from "@/server/sql/CreateTableQuery";	18✔
6	import { writeFileSync } from 'node:fs'	18✔
7	import path from 'path';	18✔
8	import { PostgresFunction, postgresFunctions } from '../postgresFunctions';	18✔
9	import { PostgresExtension, postgresExtensions } from '../postgresExtensions';	18✔
10	import CreateExtensionQuery from '@/server/sql/CreateExtensionQuery';	18✔
11	import CreateIndexQuery from '@/server/sql/CreateIndexQuery';	18✔
12	import { sqlInterpolateArgs } from '@/server/sql/Type';	18✔
13	import { CustomPgIndex, expectedCustomPgIndexes } from '../../lib/collectionIndexUtils';	18✔
14	import { PostgresView, getAllPostgresViews } from '../postgresView';	18✔
15	import TableIndex from '@/server/sql/TableIndex';
16
17	const ROOT_PATH = path.join(__dirname, "../../../");	18✔
18	const acceptedSchemePath = (rootPath: string) => path.join(rootPath, "schema/accepted_schema.sql");	18✔
19
20	const schemaFileHeaderTemplate = `-- GENERATED FILE	18✔
21	-- Do not edit this file directly. Instead, start a server and run "yarn generate"
22	-- as described in the README. This file should nevertheless be checked in to version control.
23
24	`
25
26	declare global {
27	type SchemaDependency =
28	{type: "extension", name: PostgresExtension} \|
29	{type: "collection", name: CollectionNameString} \|
30	{type: "function", name: string} \|
31	{type: "view", name: string};
32	}
33
34	abstract class Node {
35	public dependencies: SchemaDependency[] = [];	×
36
37	addDependency(dependency: SchemaDependency) {
38	this.dependencies.push(dependency);	×
39	}
40
41	abstract getName(): string;
42	abstract getQuery(): {compile(): {sql: string, args: any[]}};
43
44	getHeader(): string {
45	const type = this.constructor.name.replace(/(_\|Node)/g, "");	×
NEW 46	return `-- ${type} "${this.getName()}"`;	×
47	}
48
49	getSource(): string {
50	const {sql, args} = this.getQuery().compile();	×
51	return sqlInterpolateArgs(sql, args).trim();	×
52	}
53
54	getAnnotatedSource(): string {
55	const source = this.getSource();	×
56	const hasSemi = source[source.length - 1] === ";";	×
57	return `${this.getHeader()}\n${source}${hasSemi ? "" : ";"}\n`;	×
58	}
59	}
60
61	class ExtensionNode extends Node {
62	constructor(private extension: PostgresExtension) {	×
63	super();	×
64	}
65
66	getName() {
67	return this.extension;	×
68	}
69
70	getQuery() {
71	return new CreateExtensionQuery(this.extension);	×
72	}
73	}
74
75	class TableNode extends Node {
76	constructor(private table: Table<DbObject>) {	×
77	super();	×
78	}
79
80	getName() {
81	return this.table.getName();	×
82	}
83
84	getQuery() {
85	return new CreateTableQuery(this.table);	×
86	}
87	}
88
89	class IndexNode extends Node {
90	constructor(
91	private table: Table<DbObject>,	×
92	private index: TableIndex<DbObject>,	×
93	) {
94	super();	×
95	this.addDependency({	×
96	type: "collection",
97	name: table.getName() as CollectionNameString,
98	});
99	}
100
101	getName() {
102	return this.index.getName();	×
103	}
104
105	getQuery() {
106	return new CreateIndexQuery({ table: this.table, index: this.index, ifNotExists: true, allowConcurrent: false });	×
107	}
108	}
109
110	class CustomIndexNode extends Node {	18✔
111	private static nameRegex = /^\s*CREATE\s+(UNIQUE\s+)?INDEX\s+(CONCURRENTLY\s+)?(IF\s+NOT\s+EXISTS\s+)?"?([a-zA-Z0-9_]+)/i;
112	private static targetRegex = /.*ON\s+(public\.)?"([A-Za-z0-9_]+)"/i;
113	private static concurrentRegex = /\s+CONCURRENTLY\s+/gi;
114
115	private name: string;
116
117	constructor(private index: CustomPgIndex) {	×
118	super();	×
119	const {source, options} = index;	×
120	const name = source.match(CustomIndexNode.nameRegex)?.[4];	×
121	if (!name) {	×
122	throw new Error(`Can't parse name for custom index: ${source}`);	×
123	}
124	this.name = name;	×
125
126	const target = source.match(CustomIndexNode.targetRegex)?.[2];	×
127	if (!target) {	×
128	throw new Error(`Can't parse target for custom index "${name}"`);	×
129	}
130	const dependency: SchemaDependency = isValidCollectionName(target)	×
131	? {type: "collection", name: target}	×
132	: {type: "view", name: target};
133	this.addDependency(dependency);	×
134
135	this.dependencies = this.dependencies.concat(options?.dependencies ?? []);	×
136	}
137
138	getName() {
139	return this.name;	×
140	}
141
142	getQuery() {
143	return {	×
144	compile: () => ({	×
145	sql: this.index.source.trim().replace(CustomIndexNode.concurrentRegex, " "),
146	args: [],
147	}),
148	};
149	}
150	}
151
152	class FunctionNode extends Node {	18✔
153	private static nameRegex = /^\s*CREATE\s+(OR\s+REPLACE\s+)?FUNCTION\s+([a-zA-Z0-9_]+)/i;
154
155	private name: string;
156
157	constructor(private func: PostgresFunction) {	×
158	super();	×
159	const name = func.source.match(FunctionNode.nameRegex)?.[2];	×
160	if (!name) {	×
161	throw new Error(`Can't find name for function: ${func.source}`);	×
162	}
163	const overload = func.overload ? `_${func.overload}` : "";	×
164	this.name = name + overload;	×
165	this.dependencies = this.dependencies.concat(func.dependencies ?? []);	×
166	}
167
168	getName() {
169	return this.name;	×
170	}
171
172	getQuery() {
173	return {	×
174	compile: () => ({	×
175	sql: this.func.source.trim(),
176	args: [],
177	}),
178	};
179	}
180	}
181
182	class ViewNode extends Node {
183	constructor(private view: PostgresView) {	×
184	super();	×
185	}
186
187	getName() {
188	return this.view.getName();	×
189	}
190
191	getQuery() {
192	return {	×
193	compile: () => ({	×
194	sql: this.view.getCreateViewQuery().trim(),
195	args: [],
196	}),
197	};
198	}
199	}
200
201	class Graph {
202	private nodes: Record<string, Node> = {};	×
203
204	addNode(node: Node) {
205	const name = node.getName();	×
206	if (this.nodes[name]) {	×
207	throw new Error(`Duplicate node names: "${name}"`);	×
208	}
209	this.nodes[name] = node;	×
210	}
211
212	addNodes(nodes: Node[]) {
213	for (const node of nodes) {	×
214	this.addNode(node);	×
215	}
216	}
217
218	linearize(): Node[] {
219	const stack: Node[] = [];	×
220	const unvisited = new Set<string>(Object.keys(this.nodes));	×
221
222	const depthFirstSearch = (nodeName: string, parents: string[]) => {	×
223	if (parents.includes(nodeName)) {	×
224	throw new Error(`Dependency cycle detected for ${nodeName} via ${parents}`);	×
225	}
226
227	const node = this.nodes[nodeName];	×
228	if (!node) {	×
229	throw new Error(`Invalid node: "${nodeName}"`);	×
230	}
231
232	if (!unvisited.has(nodeName)) {	×
233	return;	×
234	}
235	unvisited.delete(nodeName);	×
236
237	for (const dependency of node.dependencies) {	×
238	depthFirstSearch(dependency.name, [...parents, nodeName]);	×
239	}
240
241	stack.push(node);	×
242	}
243
244	unvisited.forEach((node) => depthFirstSearch(node, []));	×
245
246	return stack;	×
247	}
248	}
249
250	const buildSchemaSQL = () => {	18✔
251	const graph = new Graph();	×
252	graph.addNodes(postgresExtensions.map((e) => new ExtensionNode(e)));	×
253	graph.addNodes(getAllCollections().flatMap((collection) => {	×
NEW 254	const table = Table.fromCollection(collection);	×
255	const indexes: Node[] = table.getRequestedIndexes().map((i) => new IndexNode(table, i));	×
256	return indexes.concat(new TableNode(table));	×
257	}));
258	graph.addNodes(expectedCustomPgIndexes.map((i) => new CustomIndexNode(i)));	×
259	graph.addNodes(postgresFunctions.map((f) => new FunctionNode(f)));	×
260	graph.addNodes(getAllPostgresViews().flatMap((view) => {	×
261	const indexQueries = view.getCreateIndexQueries();	×
262	const indexes: Node[] = indexQueries.map((source) => new CustomIndexNode({source}));	×
263	return indexes.concat(new ViewNode(view));	×
264	}));
265	const nodes = graph.linearize();	×
266	const rawSchema = nodes.map((n) => n.getAnnotatedSource()).join("\n");	×
267	const schemaFileContents = sqlFormatter(rawSchema, {	×
268	language: "postgresql",
269	linesBetweenQueries: 1,
270	tabWidth: 2,
271	useTabs: false,
272	keywordCase: "upper",
273	dataTypeCase: "upper",
274	functionCase: "upper",
275	identifierCase: "preserve",
276	logicalOperatorNewline: "after",
277	paramTypes: {
278	positional: false,
279	numbered: [],
280	named: [],
281	quoted: [],
282	custom: [],
283	},
284	});
NEW 285	return schemaFileHeaderTemplate + schemaFileContents + "\n";	×
286	}
287
288	export const generateSQLSchema = (rootPath = ROOT_PATH) => {	18!
NEW 289	const sqlSchema = buildSchemaSQL();	×
NEW 290	writeFileSync(acceptedSchemePath(rootPath), sqlSchema);	×
291	}
292
293	Globals.generateSQLSchema = generateSQLSchema;	18✔

ForumMagnum / ForumMagnum / 11365461971

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