sql格式化

shanhexi · shanhexi · commit a0a070cf1475 · 2023-08-13T15:22:43.000+08:00
diff --git a/chat2db-client/.vscode/settings.json b/chat2db-client/.vscode/settings.json
@@ -19,52 +19,53 @@
   },
   "git.mergeEditor": false,
   "cSpell.words": [
-    "AZUREAI",
-    "Appstore",
-    "CLICKHOUSE",
-    "Cascader",
-    "Consolas",
-    "DBAI",
-    "Dmaven",
-    "Iconfont",
-    "JDBC",
-    "KEYPAIR",
-    "KINGBASE",
-    "MARIADB",
-    "Mddhhmmss",
-    "Menlo",
-    "OCEANBASE",
-    "OPENAI",
-    "POSTGRESQL",
-    "RESTAI",
-    "SQLSERVER",
-    "Sercurity",
-    "USERANDPASSWORD",
-    "VIEWCOLUMN",
     "ahooks",
     "antd",
+    "Appstore",
+    "AZUREAI",
     "bgcolor",
+    "Cascader",
     "chatgpt",
+    "CLICKHOUSE",
+    "Consolas",
     "datas",
     "datasource",
+    "DBAI",
     "dbhub",
+    "Dmaven",
     "echarts",
     "favicons",
     "findstr",
     "gtag",
+    "Iconfont",
     "indexs",
+    "JDBC",
+    "KEYPAIR",
+    "KINGBASE",
     "lsof",
+    "MARIADB",
+    "Mddhhmmss",
+    "Menlo",
     "netstat",
+    "OCEANBASE",
+    "OPENAI",
     "pgsql",
     "pnpm",
+    "POSTGRESQL",
     "remaininguses",
+    "RESTAI",
     "scrollbar",
+    "Sercurity",
     "sortablejs",
+    "SQLSERVER",
+    "Tigger",
     "ueabe",
     "ueabf",
     "ueac",
     "umijs",
+    "USERANDPASSWORD",
     "uuidv",
+    "VIEWCOLUMN",
     "wireframe"
   ],
   "typescript.tsdk": "/Users/wangjiaqi/Desktop/Chat2DB/chat2db-client/node_modules/typescript/lib"
diff --git a/chat2db-client/src/components/Console/MonacoEditor/index.tsx b/chat2db-client/src/components/Console/MonacoEditor/index.tsx
@@ -149,7 +149,7 @@ function MonacoEditor(props: IProps, ref: ForwardedRef<IExportRefFunction>) {
     if (options?.theme) {
       monaco.editor.setTheme(options.theme);
       return
-    } 
+    }
     monaco.editor.setTheme(appTheme.backgroundColor);
   }, [appTheme.backgroundColor, options?.theme]);
 
@@ -315,12 +315,22 @@ export const appendMonacoValue = (editor: any, text: any, range: IRangeType = 'e
     return;
   }
   switch (range) {
+    // 覆盖所有内容
     case 'cover':
       newRange = model.getFullModelRange();
       break;
+    // 在开头添加内容
     case 'front':
       newRange = new monaco.Range(1, 1, 1, 1);
       break;
+    // 格式化选中区域的sql
+    case 'select':
+      const selection = editor.getSelection();
+      if (selection) {
+        newRange = new monaco.Range(selection.startLineNumber, selection.startColumn, selection.endLineNumber, selection.endColumn);
+      }
+      break;
+    // 在末尾添加内容
     case 'end':
       const lastLine = editor.getModel().getLineCount();
       const lastLineLength = editor.getModel().getLineMaxColumn(lastLine);
diff --git a/chat2db-client/src/components/Console/index.tsx b/chat2db-client/src/components/Console/index.tsx
@@ -5,7 +5,6 @@ import connectToEventSource from '@/utils/eventSource';
 import { Button, Spin, message, Drawer, Modal } from 'antd';
 import ChatInput from './ChatInput';
 import Editor, { IEditorOptions, IExportRefFunction, IRangeType } from './MonacoEditor';
-import { format } from 'sql-formatter';
 import historyServer from '@/service/history';
 import aiServer from '@/service/ai';
 import { v4 as uuidv4 } from 'uuid';
@@ -14,7 +13,7 @@ import Iconfont from '../Iconfont';
 import { IAiConfig, ITreeNode } from '@/typings';
 import { IAIState } from '@/models/ai';
 import Popularize from '@/components/Popularize';
-import { handleLocalStorageSavedConsole, readLocalStorageSavedConsoleText } from '@/utils';
+import { handleLocalStorageSavedConsole, readLocalStorageSavedConsoleText, formatSql } from '@/utils';
 import { chatErrorForKey, chatErrorToLogin } from '@/constants/chat';
 import { AiSqlSourceType } from '@/typings/ai';
 import i18n from '@/i18n';
@@ -162,7 +161,7 @@ function Console(props: IProps) {
   function timingAutoSave() {
     timerRef.current = setInterval(() => {
       handleLocalStorageSavedConsole(executeParams.consoleId!, 'save', editorRef?.current?.getAllContent());
-    }, 5000);
+    }, 500);
   }
 
   const tableListName = useMemo(() => {
@@ -213,7 +212,7 @@ function Console(props: IProps) {
           }
         }, 3000);
       }
-    }catch (e) {
+    } catch (e) {
       setIsLoading(false);
     }
 
@@ -471,17 +470,11 @@ function Console(props: IProps) {
         <Button
           type="text"
           onClick={() => {
-            let formatRes = '';
-            try {
-              const contextTmp = editorRef?.current?.getAllContent();
-              formatRes = format(contextTmp || '');
-            }
-            catch { }
-            if (formatRes) {
-              editorRef?.current?.setValue(formatRes, 'cover');
-            } else {
-              message.error(i18n('common.tips.formatError'))
-            }
+            // 格式化sql
+            const sql = editorRef?.current?.getCurrentSelectContent() || ''
+            formatSql(sql, executeParams.type!).then((res) => {
+              editorRef?.current?.setValue(res, 'select');
+            });
           }}
         >
           {i18n('common.button.format')}
diff --git a/chat2db-client/src/service/sql.ts b/chat2db-client/src/service/sql.ts
@@ -171,9 +171,16 @@ const getProcedureDetail = createRequest<{
   databaseName: string;
   schemaName?: string;
   procedureName: string
-}, {procedureBody: string}>('/api/rdb/procedure/detail', { method: 'get' });
+}, { procedureBody: string }>('/api/rdb/procedure/detail', { method: 'get' });
+
+/** 格式化sql */
+const sqlFormat  = createRequest<{
+  sql: string;
+  dbType: DatabaseTypeCode;
+}, string>('/api/sql/format', { method: 'get' });
 
 export default {
+  sqlFormat,
   getTriggerDetail,
   getProcedureDetail,
   getFunctionDetail,
diff --git a/chat2db-client/src/theme/background/darkDimmed.ts b/chat2db-client/src/theme/background/darkDimmed.ts
@@ -12,7 +12,7 @@ const antdPrimaryColor: IAntdPrimaryColor = {
     colorPrimary: '#3c8618',
   },
   [PrimaryColorType.Golden_Purple]: {
-    colorPrimary: '#7688c9',
+    colorPrimary: '#8276c9',
   },
   [PrimaryColorType.Polar_Blue]: {
     colorPrimary: '#1677ff',
diff --git a/chat2db-client/src/utils/index.ts b/chat2db-client/src/utils/index.ts
@@ -1,7 +1,9 @@
-import { ThemeType, OSType } from '@/constants';
+import { ThemeType, OSType, DatabaseTypeCode } from '@/constants';
 import { ITreeNode } from '@/typings';
 import clipboardCopy from 'copy-to-clipboard';
 import lodash from 'lodash';
+import sqlServer from '@/service/sql';
+import { format } from 'sql-formatter';
 
 export function getOsTheme() {
   return window.matchMedia && window.matchMedia('(prefers-color-scheme: dark)').matches
@@ -230,3 +232,28 @@ export function OSnow(): {
   }
 }
 
+// 格式化sql
+export function formatSql(sql: string, dbType: DatabaseTypeCode) {
+  return new Promise((r: (sql: string) => void, j) => {
+    let formatRes = '';
+    try {
+      formatRes = format(sql || '');
+    }
+    catch { 
+
+    }
+    // 如果格式化失败，直接返回原始sql
+    if (!formatRes) {
+      sqlServer.sqlFormat({
+        sql,
+        dbType,
+      }).then((res) => {
+        formatRes = res;
+        r(formatRes);
+      })
+    } else {
+      r(formatRes);
+    }
+  })
+}
+
