konnosDashboard
PgSQLCompletionProvider.ts231 lines · main
1import type { RefObject } from 'react'
2
3import BackwardIterator from './BackwardIterator'
4import type { DatabaseFunction } from '@/data/database-functions/database-functions-query'
5import type { Schema } from '@/data/database/schemas-query'
6import type { TableColumn } from '@/data/database/table-columns-query'
7
8// [Joshen] Needs to be fixed
9
10export default function getPgsqlCompletionProvider(monaco: any, pgInfoRef: RefObject<any>) {
11 return {
12 triggerCharacters: [' ', '.', '"'],
13 provideCompletionItems: function (model: any, position: any, context: any) {
14 try {
15 // position.column should minus 2 as it returns 2 for first char
16 // position.lineNumber should minus 1
17 let iterator = new BackwardIterator(model, position.column - 2, position.lineNumber - 1)
18
19 if (context.triggerCharacter === '"') {
20 return startingQuoteScenarioSuggestions(monaco, pgInfoRef, iterator)
21 } else if (context.triggerCharacter === '.') {
22 return dotScenarioSuggestions(monaco, pgInfoRef, iterator)
23 } else {
24 return defaultScenarioSuggestions(monaco, pgInfoRef)
25 }
26 } catch (_) {
27 // any error, returns empty suggestion
28 return { suggestions: [] }
29 }
30 },
31 }
32}
33
34function startingQuoteScenarioSuggestions(monaco: any, pgInfoRef: RefObject<any>, iterator: any) {
35 const items: any[] = []
36
37 let startingQuotedIdent = iterator.isFowardDQuote()
38 if (!startingQuotedIdent) return { suggestions: items }
39
40 iterator.next() // get passed the starting quote
41 if (iterator.isNextPeriod()) {
42 // probably a field - get the ident
43 let ident = iterator.readIdent()
44 let isQuotedIdent = false
45 if (ident.match(/^\".*?\"$/)) {
46 isQuotedIdent = true
47 ident = fixQuotedIdent(ident)
48 }
49 let table = pgInfoRef.current.tableColumns.find((tbl: TableColumn) => {
50 return (
51 (isQuotedIdent && tbl.tablename === ident) ||
52 (!isQuotedIdent && tbl.tablename.toLocaleLowerCase() == ident.toLocaleLowerCase())
53 )
54 })
55
56 if (!table) return { suggestions: items }
57 table.columns.forEach((field: any) => {
58 items.push({
59 label: field.attname,
60 kind: monaco.languages.CompletionItemKind.Property,
61 detail: field.data_type,
62 insertText: field.attname,
63 })
64 })
65 } else {
66 // probably a table - list the tables
67 pgInfoRef.current.tableColumns.forEach((table: TableColumn) => {
68 items.push({
69 label: table.tablename,
70 kind: monaco.languages.CompletionItemKind.Class,
71 insertText: table.tablename,
72 })
73 })
74 }
75
76 return { suggestions: items }
77}
78
79function dotScenarioSuggestions(monaco: any, pgInfoRef: RefObject<any>, iterator: any) {
80 const items: any[] = []
81
82 let idents = readIdents(iterator, 3)
83 let pos = 0
84
85 let schema = pgInfoRef.current.schemas.find((sch: Schema) => {
86 const _ident = idents && idents.length > pos ? idents[pos] : {}
87 return (
88 (_ident.isQuoted && sch.name === _ident.name) ||
89 (!_ident.isQuoted && sch.name?.toLocaleLowerCase() == _ident.name?.toLocaleLowerCase())
90 )
91 })
92
93 if (!schema) {
94 schema = pgInfoRef.current.schemas.find((sch: Schema) => {
95 return sch.name == 'public'
96 })
97 } else {
98 pos++
99 }
100
101 if (idents.length == pos) {
102 pgInfoRef.current.tableColumns.forEach((tbl: TableColumn) => {
103 if (tbl.schemaname != schema.name) {
104 return
105 }
106 items.push({
107 label: tbl.tablename,
108 kind: monaco.languages.CompletionItemKind.Class,
109 detail: tbl.schemaname !== 'public' ? tbl.schemaname : null,
110 insertText: formatInsertText(tbl.tablename),
111 })
112 })
113 return { suggestions: items }
114 }
115
116 let table = pgInfoRef.current.tableColumns.find((tbl: TableColumn) => {
117 const _ident = idents && idents.length > pos ? idents[pos] : {}
118 return (
119 (tbl.schemaname == schema.name && _ident.isQuoted && tbl.tablename === _ident.name) ||
120 (!_ident.isQuoted && tbl.tablename?.toLocaleLowerCase() == _ident.name?.toLocaleLowerCase())
121 )
122 })
123
124 if (table) {
125 table.columns.forEach((field: any) => {
126 items.push({
127 label: field.attname,
128 kind: monaco.languages.CompletionItemKind.Property,
129 detail: field.data_type,
130 insertText: formatInsertText(field.attname),
131 })
132 })
133 }
134
135 return { suggestions: items }
136}
137
138function defaultScenarioSuggestions(monaco: any, pgInfoRef: RefObject<any>) {
139 const items: any = []
140
141 if (pgInfoRef.current.keywords?.length > 0) {
142 pgInfoRef.current.keywords.forEach((x: string) => {
143 items.push({
144 label: x,
145 kind: monaco.languages.CompletionItemKind.Keyword,
146 insertText: x,
147 })
148 })
149 }
150
151 if (pgInfoRef.current.schemas?.length > 0) {
152 pgInfoRef.current.schemas.forEach((x: Schema) => {
153 items.push({
154 label: x.name,
155 kind: monaco.languages.CompletionItemKind.Keyword,
156 insertText: x.name,
157 })
158 })
159 }
160
161 if (pgInfoRef.current.tableColumns?.length > 0) {
162 pgInfoRef.current.tableColumns.forEach((x: TableColumn) => {
163 const insertText = x.schemaname == 'public' ? x.tablename : x.schemaname + '.' + x.tablename
164 items.push({
165 label: x.tablename,
166 detail: x.schemaname !== 'public' ? x.schemaname : null,
167 kind: x.is_table
168 ? monaco.languages.CompletionItemKind.Class
169 : monaco.languages.CompletionItemKind.Interface,
170 insertText: formatInsertText(insertText),
171 })
172 x.columns.forEach((field: any) => {
173 if (!field) return
174
175 let foundItem = items.find(
176 (i: any) =>
177 i.label === field?.attname &&
178 i.kind === monaco.languages.CompletionItemKind.Field &&
179 i.detail === field?.data_type
180 )
181 if (foundItem) {
182 foundItem.tables.push(x.tablename)
183 foundItem.tables.sort()
184 foundItem.documentation = foundItem.tables.join(', ')
185 } else {
186 items.push({
187 label: field.attname,
188 kind: monaco.languages.CompletionItemKind.Field,
189 detail: field.data_type,
190 documentation: x.tablename,
191 tables: [x.tablename],
192 insertText: formatInsertText(field.attname),
193 })
194 }
195 })
196 })
197 }
198
199 if (pgInfoRef.current.functions?.length > 0) {
200 pgInfoRef.current.functions.forEach((x: DatabaseFunction) => {
201 items.push({
202 label: x.name,
203 kind: monaco.languages.CompletionItemKind.Function,
204 detail: x.return_type,
205 insertText: x.name,
206 })
207 })
208 }
209
210 return { suggestions: items }
211}
212
213function fixQuotedIdent(str: string) {
214 return str.replace(/^\"/, '').replace(/\"$/, '').replace(/\"\"/, '"')
215}
216
217function readIdents(iterator: any, maxlvl: number) {
218 return iterator.readIdents(maxlvl).map((name: string) => {
219 let isQuoted = false
220 if (name.match(/^\".*?\"$/)) {
221 isQuoted = true
222 name = fixQuotedIdent(name)
223 }
224 return { isQuoted: isQuoted, name: name }
225 })
226}
227
228function formatInsertText(value: string) {
229 const hasUpperCase = !(value == value.toLowerCase())
230 return hasUpperCase ? `"${value}"` : value
231}