Author: Jason
Worked in home credit consumer finance Co., Ltd. as DBA. He has been engaged in the development of oracle, mongo, mysql DBA and big data ETL. Have a strong interest in NEWSQL and cloud native distributed database.
Source: original contribution
*It is produced by aikesheng open source community. The original content cannot be used without authorization. For reprint, please contact Xiaobian and indicate the source.
SQLE is an open source SQL audit tool developed by Shanghai aikesheng Information Technology Co., Ltd., which supports SQL audit, index optimization, pre audit, post audit, standardized online process, native MySQL audit and extensible database type.
Official homepage: https://opensource.actionsky....
Official document: Introduction · SQL manual (actiontech. GitHub. IO)
(!!! Make a statement in advance: the secondary development is purely personal technical research and shall not engage in any commercial profit-making activities)
Let's build the secondary development environment of SQLE: SQLE adopts the development mode of separating the front and back platforms.
Backstage: restful API released by go
Front desk: nodeJS + react
Let's start with the first part:
Backstage: restful API released by go
Pre preparation: go language environment, version 1.16
Development environment linux or mac os
It should be emphasized here that the following code of windows will not work: the following errors will be encountered
..\..\..\vendor\github.com\openark\golib\log\log.go:90:19: undefined: syslog.Writer ..\..\..\vendor\github.com\openark\golib\log\log.go:110:22: undefined: syslog.New ..\..\..\vendor\github.com\openark\golib\log\log.go:110:33: undefined: syslog.LOG_ERR
The explanation of GO official website is as follows: the general meaning is that there is no specific implementation of this syslog package in windows environment
This package is not implemented on Windows. As the syslog package is frozen, Windows users are encouraged to use a package outside of the standard library. For background, see https://golang.org/issue/1108. This package is not implemented on Plan 9. This package is not implemented on NaCl (Native Client).
This time, our development environment is built on the linux server.
First, we need to download the complete project from github. (generally, scientific Internet access is required in China)
I downloaded it directly to the linux test environment
TEST mongo@wqdcsrv3066[16:03:03]:/data/jason $ git clone https://github.com/actiontech/sqle.git Cloning into 'sqle'... remote: Enumerating objects: 28097, done. remote: Counting objects: 100% (2998/2998), done. remote: Compressing objects: 100% (1124/1124), done. Receiving objects: 45% (12786/28097), 34.29 MiB | 2.00 MiB/s
After downloading the project, we need to open the IDE on the linux system to import the project: I use Golan here.
Because the IDE is a graphical interface, we need to use VNC viewer on the local windows to connect with the linux development machine:
After connecting to the server, execute the command to start Goland
Next, we need to import the previously downloaded items.
After importing, let's observe the project directory: the package main package started is in the path sqle / CMD / sqld / sqled Go and sqle/cmd/sqld/gen_secret_pass.go these two files
Since we need to open the web service port and connect to the database to start the project, we can create a sqled YML file
server:
sqle_config:
server_port: 10000
#auto_migrate_table: 'AUTO_MIGRATE_TABLE'
#debug_log: 'DEBUG'
log_path: '/data/sqle-main/logs'
#plugin_path: '/data/sqle-main/plugins'
db_config:
mysql_cnf:
mysql_host: '10.67.200.37'
mysql_port: 3306
mysql_user: 'app_sqle'
mysql_password: '*******'
mysql_schema: 'sqle'We need to create corresponding databases and accounts in MySQL:
[root@localhost][performance_schema]> select @@port; +--------+ | @@port | +--------+ | 3306 | +--------+ 1 row in set (0.00 sec) [root@localhost][performance_schema]> CREATE DATABASE IF NOT EXISTS sqle default character set utf8mb4 collate utf8mb4_unicode_ci; Query OK, 1 row affected (3.00 sec) [root@localhost][performance_schema]> create user app_sqle@'%' identified with mysql_native_password by "***********"; Query OK, 0 rows affected (0.01 sec) [root@localhost][performance_schema]> grant all on sqle.* to app_sqle@'%'; Query OK, 0 rows affected (0.00 sec
Next, let's try to start this project:
The package main package started is in the path sqle / CMD / sqld / sqled Go and sqle/cmd/sqld/gen_secret_pass.go these two files
We need to specify our configuration file: - config / data / sqle main / sqle / sqled yml
Let's try go bulild this project:
To view console output:
Finally, we use postman to test the login API: 10.67.200.38:10000/v1/login
So far, our background environment has been built. Next, let's build the foreground environment:
Front desk: nodeJS + react
Again, because the windows system does not support nodejs very well, it is recommended to use MAC OS or Linux system for development.
This time, we install vs code on windows and debug it on Linux remotely.
First, let's install the environment of node js on Linux OS:
Download nodejs: http://nodejs.cn/download/
After downloading, upload it to the linux server, extract it and install it as follows:
TEST mongo@wqdcsrv3066[15:24:08]:/data/jason/nodejs $ xz -d node-v16.14.0-linux-x64.tar.xz TEST mongo@wqdcsrv3066[15:25:38]:/data/jason/nodejs $ tar -xvf node-v16.14.0-linux-x64.tar TEST mongo@wqdcsrv3066[15:27:11]:/data/jason/nodejs $ mv node-v16.14.0-linux-x64 node TEST mongo@wqdcsrv3066[15:28:19]:/data/jason/nodejs/node $ sudo ln -s /data/jason/nodejs/node/bin/node /usr/bin/node TEST mongo@wqdcsrv3066[15:28:27]:/data/jason/nodejs/node $ sudo ln -s /data/jason/nodejs/node/bin/npm /usr/bin/npm TEST mongo@wqdcsrv3066[15:29:03]:/data/jason/nodejs/node $ mkdir node_global TEST mongo@wqdcsrv3066[15:29:10]:/data/jason/nodejs/node $ mkdir node_cache TEST mongo@wqdcsrv3066[15:29:15]:/data/jason/nodejs/node $ npm config set prefix "node_global" TEST mongo@wqdcsrv3066[15:29:21]:/data/jason/nodejs/node $ npm config set cache "node_cache" TEST mongo@wqdcsrv3066[15:29:33]:/data/jason/nodejs/node $ node --version v16.14.0
We need to install YARN package to compile code and type build:
TEST mongo@wqdcsrv3066[16:52:45]:/home/mongo $ npm install -g yarn changed 1 package, and audited 2 packages in 2s found 0 vulnerabilities
At the same time, we also need to configure soft connection for YARN command:
TEST mongo@wqdcsrv3066[17:14:00]:/home/mongo $ sudo ln -s /data/jason/nodejs/node/node_global/bin/yarn /usr/bin/yarn TEST mongo@wqdcsrv3066[17:16:47]:/home/mongo $ yarn version yarn version v1.22.17 ... ...
So far, we have configured nodeJS environment on linux. Next, we will download the code of SQLE UI library from github: https://github.com/actiontech...
TEST mongo@wqdcsrv3066[21:38:21]:/data/jason $ git clone https://github.com/actiontech/sqle-ui.git Cloning into 'sqle-ui'... remote: Enumerating objects: 4982, done. remote: Counting objects: 100% (4982/4982), done. remote: Compressing objects: 100% (1998/1998), done. remote: Total 4982 (delta 2809), reused 4648 (delta 2505), pack-reused 0 Receiving objects: 100% (4982/4982), 2.01 MiB | 89.00 KiB/s, done. Resolving deltas: 100% (2809/2809), done.
Next, let's configure the setting of remote connection to linux in IDE vscode on windows
Install the plug-in Remote Development for remote connection
Icons will also be generated after installation.
Configure server information for remote login:
You need to enter the password to connect to the server:
We can see that the project has been imported successfully:
So far, we have configured the development environment. Next, we try to compile and start the following projects:
Of course, we also need to modify the API publishing address of the background service: we find the file package json
And the file craco config. js
Next, let's download the packages required for the project:
TEST mongo@wqdcsrv3066[21:26:16]:/data/jason/sqle-ui-new/sqle-ui $ yarn install yarn install v1.22.17 [1/5] Validating package.json... [2/5] Resolving packages... [3/5] Fetching packages... [4/5] Linking dependencies... warning " > @testing-library/user-event@12.8.3" has unmet peer dependency "@testing-library/dom@>=7.21.4". warning "craco-less > less-loader@7.3.0" has unmet peer dependency "webpack@^4.0.0 || ^5.0.0". warning " > monaco-editor-webpack-plugin@3.1.0" has unmet peer dependency "webpack@^4.5.0 || 5.x". warning " > monaco-editor-webpack-plugin@3.1.0" has unmet peer dependency "monaco-editor@0.22.x || 0.23.x || 0.24.x". warning " > react-monaco-editor@0.43.0" has incorrect peer dependency "@types/react@^17.x". [5/5] Building fresh packages... Done in 61.65s.
Then we will compile the code:
$ node ./scripts/moveAntdCss.js && node ./scripts/getGitVersion.js $ craco build current mode: ce Creating an optimized production build... Browserslist: caniuse-lite is outdated. Please run: npx browserslist@latest --update-db Why you should do it regularly: https://github.com/browserslist/browserslist#browsers-data-updating Compiled successfully. File sizes after gzip: 588.6 KB build/static/js/OrderDetail.4a59ec36.chunk.js 580.25 KB build/static/js/CreateOrder.8026b0aa.chunk.js 532.43 KB build/static/js/22.c21d2951.chunk.js 61.76 KB build/static/js/DataSource.829fc664.chunk.js 59.74 KB build/static/js/AuditPlan.9f3f1b23.chunk.js 57.64 KB build/static/js/Order.003be5b1.chunk.js 47.66 KB build/static/js/User.9fe363ac.chunk.js 46.36 KB build/static/js/UserGroup.044f6248.chunk.js 45.92 KB build/editor.worker.js 44.41 KB build/static/js/RuleTemplate.86cc7bbb.chunk.js 39.9 KB build/static/js/Role.039a086a.chunk.js 29.54 KB build/static/js/PlanDetail.90341552.chunk.js 20.63 KB build/static/js/main.21b0188c.chunk.js 13.98 KB build/static/js/3.1aa16012.chunk.js 13.98 KB build/static/js/Account.3465705d.chunk.js 13.23 KB build/static/js/1.1bb8d820.chunk.js 10.94 KB build/static/js/System.9ea63f78.chunk.js 9.32 KB build/static/css/CreateOrder.90ee172f.chunk.css 9.32 KB build/static/css/OrderDetail.90ee172f.chunk.css 8.65 KB build/static/js/4.dfda53f3.chunk.js 6.44 KB build/static/js/23.d9ad2202.chunk.js 5.89 KB build/static/js/Login.19923fb4.chunk.js 5.86 KB build/static/js/Rule.828dfc27.chunk.js 5.51 KB build/static/js/0.d9a06cf9.chunk.js 5.13 KB build/static/js/2.68214f76.chunk.js 4.87 KB build/static/js/Home.aa89add8.chunk.js 1.88 KB build/static/js/runtime-main.e8405fb1.js 1.4 KB build/static/js/24.6c710535.chunk.js 982 B build/static/css/22.b58f2af8.chunk.css 819 B build/static/css/main.cdb4f5a9.chunk.css 493 B build/static/css/Login.141bcc4c.chunk.css 313 B build/static/css/AuditPlan.f5209654.chunk.css 250 B build/static/css/RuleTemplate.770a3f55.chunk.css 139 B build/static/css/PlanDetail.b50384ea.chunk.css 96 B build/static/css/Rule.864c09ef.chunk.css The project was built assuming it is hosted at /. You can control this with the homepage field in your package.json. The build folder is ready to be deployed. You may serve it with a static server: yarn global add serve serve -s build Find out more about deployment here: https://cra.link/deployment Done in 79.65s.
After the code is compiled, we try to start the project in the IDE: execute yarn run start
Try accessing SQL from the browser: http://10.67.200.38:3000
Try logging in to admin/admin
So far, the development environment of the front desk has been built!
Generally speaking, this front end and back-end separation of development requires high technical stack and comprehensive ability of open personnel.
In addition, professional knowledge in the field of database should be added, which can not be completed by 1 or 2 people.
Finally, I would like to thank aikesheng company for opening up its own code audit system!
SQLE of aikesheng open source community is a SQL audit tool for database users and managers, which supports multi scenario audit, standardized online process, native MySQL audit and extensible database type.
Acquisition method:
|Type | address|
| --- | --- |
|Version Library| https://github.com/actiontech... |
|Documentation| https://actiontech.github.io/... |
|Release information| https://github.com/actiontech... |
For more technical questions related to SQLE, you can scan the code or directly join the official QQ technical exchange group (637150065) for communication. Welcome to "harass"