SQL development environment | secondary audit

Posted by verbalmike on Fri, 25 Feb 2022 07:05:53 +0100

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_port: 10000
    #auto_migrate_table: 'AUTO_MIGRATE_TABLE'
    #debug_log: 'DEBUG'
    log_path: '/data/sqle-main/logs'
    #plugin_path: '/data/sqle-main/plugins'
      mysql_host: ''
      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:

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
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

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
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,
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:
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:
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:

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"