Line data Source code
1 : import * as dashql from '@ankoh/dashql-core';
2 :
3 1 : export const DEFAULT_DATABASE_NAME = 'hyper';
4 :
5 : /// Column metadata for SQL generation
6 : export interface ColumnMetadata {
7 : name: string;
8 : ordinalPosition: number;
9 : dataType?: string | null;
10 : }
11 :
12 : /// Table metadata for SQL generation
13 : export interface TableMetadata {
14 : tableName: string;
15 : columns: ColumnMetadata[];
16 : }
17 :
18 : /// Schema metadata for SQL generation
19 : export interface SchemaMetadata {
20 : databaseName: string | null;
21 : schemaName: string;
22 : tables: TableMetadata[];
23 : }
24 :
25 1 : export function quoteIdentifier(identifier: string): string {
26 1 : const escaped = identifier.replace(/"/g, '""');
27 1 : return `"${escaped}"`;
28 1 : }
29 :
30 : /// Maps SQL data types to simplified DashQL types.
31 : /// Falls back to VARCHAR for unknown types.
32 1 : export function mapDataType(sqlType: string | null | undefined): string {
33 1 : if (!sqlType) {
34 1 : return 'VARCHAR';
35 : }
36 :
37 1 : const normalized = sqlType.toLowerCase().trim();
38 :
39 : // Integer types
40 1 : if (normalized.includes('int') || normalized.includes('serial') || normalized.includes('bigserial')) {
41 1 : return 'INTEGER';
42 : }
43 :
44 : // Floating point types
45 1 : if (normalized.includes('float') || normalized.includes('double') ||
46 1 : normalized.includes('real') || normalized.includes('numeric') ||
47 1 : normalized.includes('decimal')) {
48 1 : return 'FLOAT';
49 : }
50 :
51 : // Boolean types
52 1 : if (normalized.includes('bool')) {
53 1 : return 'BOOLEAN';
54 : }
55 :
56 : // Date/time types
57 1 : if (normalized.includes('date') && !normalized.includes('time')) {
58 1 : return 'DATE';
59 : }
60 1 : if (normalized.includes('timestamp') || normalized.includes('datetime')) {
61 1 : return 'TIMESTAMP';
62 : }
63 :
64 : // Default to VARCHAR for text and unknown types
65 1 : return 'VARCHAR';
66 1 : }
67 :
68 : /// Generates a qualified table name (database.schema.table).
69 1 : export function generateQualifiedTableName(
70 1 : databaseName: string | null | undefined,
71 1 : schemaName: string,
72 1 : tableName: string
73 1 : ): string {
74 1 : const db = databaseName || DEFAULT_DATABASE_NAME;
75 1 : return `${quoteIdentifier(db)}.${quoteIdentifier(schemaName)}.${quoteIdentifier(tableName)}`;
76 1 : }
77 :
78 : /// Generates a CREATE TABLE SQL statement for a single table.
79 1 : export function generateCreateTableSQL(
80 1 : databaseName: string | null | undefined,
81 1 : schemaName: string | null,
82 1 : tableName: string,
83 1 : columns: ColumnMetadata[]
84 1 : ): string {
85 1 : const qualifiedName = schemaName == null
86 1 : ? quoteIdentifier(tableName)
87 1 : : generateQualifiedTableName(databaseName, schemaName, tableName);
88 :
89 : // Sort columns by ordinal position
90 1 : const sortedColumns = [...columns].sort((a, b) => a.ordinalPosition - b.ordinalPosition);
91 :
92 : // Generate column definitions
93 1 : const columnDefs = sortedColumns.map(col => {
94 1 : const colName = quoteIdentifier(col.name);
95 1 : const colType = mapDataType(col.dataType);
96 1 : return ` ${colName} ${colType}`;
97 1 : }).join(',\n');
98 :
99 1 : return `CREATE TABLE ${qualifiedName} (\n${columnDefs}\n);`;
100 1 : }
101 :
102 : /// Generates CREATE TABLE SQL statements for all tables in a schema.
103 1 : export function generateSchemaSQL(
104 1 : databaseName: string | null | undefined,
105 1 : schemaName: string,
106 1 : tables: Map<string, ColumnMetadata[]>
107 1 : ): string {
108 1 : const sqlStatements: string[] = [];
109 :
110 : // Sort tables by name for consistent output
111 1 : const sortedTableNames = Array.from(tables.keys()).sort();
112 :
113 1 : for (const tableName of sortedTableNames) {
114 1 : const columns = tables.get(tableName)!;
115 1 : const sql = generateCreateTableSQL(databaseName, schemaName, tableName, columns);
116 1 : sqlStatements.push(sql);
117 1 : }
118 :
119 1 : return sqlStatements.join('\n\n');
120 1 : }
121 :
122 : /// Generates unqualified CREATE TABLE statements for all supplied tables.
123 1 : export function generateUnqualifiedSchemaSQL(tables: Map<string, ColumnMetadata[]>): string {
124 1 : return Array.from(tables.keys())
125 1 : .sort()
126 1 : .map(tableName => generateCreateTableSQL(null, null, tableName, tables.get(tableName)!))
127 1 : .join('\n\n');
128 1 : }
129 :
130 1 : export enum CatalogSource {
131 1 : InformationSchema = 0,
132 1 : PgClass = 1,
133 1 : SalesforceMetadataApi = 2,
134 1 : Hyper = 3,
135 1 : Unknown = 4,
136 1 : }
137 :
138 1 : export function generateCatalogScriptHeaderForSource(source: string, updatedAt: Date = new Date()): string {
139 1 : return `-- DashQL Connection Relations.
140 : -- This file is auto-generated and can only be updated through a catalog refresh.
141 : --
142 1 : -- Catalog Source: ${source}
143 1 : -- Last Refresh: ${updatedAt.toISOString()}
144 :
145 : `;
146 1 : }
147 :
148 : /// Generates a SQL comment header for catalog scripts with the update timestamp and method.
149 1 : export function generateCatalogScriptHeader(method: CatalogSource, updatedAt: Date = new Date()): string {
150 1 : let methodStr: string;
151 1 : switch (method) {
152 1 : case CatalogSource.InformationSchema: methodStr = 'SQL information_schema'; break;
153 1 : case CatalogSource.PgClass: methodStr = 'SQL pg_class'; break;
154 1 : case CatalogSource.SalesforceMetadataApi: methodStr = 'Salesforce Connect /ssot/metadata API'; break;
155 1 : case CatalogSource.Hyper: methodStr = 'Hyper attached databases'; break;
156 1 : default: methodStr = '-'; break;
157 1 : }
158 1 : return generateCatalogScriptHeaderForSource(methodStr, updatedAt);
159 1 : }
160 :
161 : /// Generates SQL for multiple schemas.
162 1 : export function generateCatalogSQL(schemas: SchemaMetadata[]): string {
163 1 : const sqlStatements: string[] = [];
164 :
165 1 : for (const schema of schemas) {
166 1 : const tablesMap = new Map<string, ColumnMetadata[]>();
167 1 : for (const table of schema.tables) {
168 1 : tablesMap.set(table.tableName, table.columns);
169 : }
170 :
171 1 : const schemaSQL = generateSchemaSQL(
172 1 : schema.databaseName,
173 1 : schema.schemaName,
174 1 : tablesMap
175 1 : );
176 :
177 1 : if (schemaSQL.length > 0) {
178 1 : sqlStatements.push(schemaSQL);
179 : }
180 1 : }
181 :
182 1 : return sqlStatements.join('\n\n');
183 1 : }
|
