Shulou(Shulou.com)06/01 Report--

The R & D process of the financial industry is inseparable from the platform support to realize the automation coordination and control of the R & D process. The quality control of the code can be automated by integrating Sonar, Checkstyle, etc., but the quality control of SQL is in the automation process for a long time. Now, through the Jenkins plug-in and JVM probe developed by SQM, the automation capability of SQL quality control is assembled into the DevOps pipeline, which completely fills this gap. If necessary, it can be matched with SQM's OpenAPI to achieve more powerful capabilities.

DevOps and Financial Enterprise R & D

DevOps has become an increasingly mainstream set of practices and cultural values in IT R & D that help companies shorten software release cycles, improve software quality, and safely and quickly obtain product development feedback. In China, major financial enterprises began to practice DevOps concept very early, and some banks have achieved good results, such as China Merchants Bank, Ping An Bank, Minsheng Bank, CITIC Bank, etc.

Research organizations have found that automation is a key factor in making organizational differences in DevOps processes and is a key point in DevOps. Product/project planning and design, plan tracking, iterative development, continuous delivery of these different stages have good methodological support, such as story map, Scrum framework, Kanban method, etc., but those who standardize the global process, automation, visualization, key processes and node control in place, parallel development process collaborative management in place, to build a complete DevOps pipeline enterprises can get the most benefits.

As a result, over the past few years, organizations have been building their own DevOps platforms, complete DevOps pipelines. The author summed it up and roughly divided it into two schools, open source or self-research.

Open source genre, continuous delivery pipeline based on open source tool chain, enabling rapid build of DevOps capabilities from 0 to 1.

Self-developed schools, the core of which is mostly the encapsulation, extension, enhancement of the open source tool chain, simplifying the difficulty of use, and achieving DevOps goals. Due to factors such as security, stability, autonomy and control, most financial enterprises take the path of self-research or cooperative research and development of DevOps platform.

No matter which genre, quality control management of code is a key link, and automation can be well realized by integrating Sonar, Checkstyle, etc.

However, SQL quality control still stays in the state of no detection or manual detection, low efficiency, large difference in quality standards, no automation, and no integration with DevOps, resulting in great risks.

The challenges and value of SQL QC

Over the years, we have found that there are many factors that affect business performance, continuity, and stability, but they can be summarized into six categories, two of which are related to SQL.

In modern enterprise R & D, other factors have better methods and tools to control, but SQL quality problems have not been well solved, business outbreak of various SQL performance and security problems, it is necessary to be able to control SQL quality, before it affects the business must be found and eliminated.

After years of exploration in SQL quality control direction, we found that although SQL quality problems exist in the whole life cycle of application software, from development, testing, release online, production and operation, SQL quality problems are everywhere, but the effect of SQL quality control is better.

SQL quality control effect in development and testing stage is far greater than SQL quality control effect in production and operation stage, and pre-solution is fundamental. At the same time, by integrating SQL quality control into tools, automation, and DevOps processes by scenario, quality control objectives can be efficiently achieved. SQM-SQL quality control platform came into being.

Two engines, SQL parsing engine + expert engine

Covers the entire application lifecycle: development, testing, launch, production

Built-in expert experience rules + syntax tree feature values, text regular matching flexible custom rule capabilities, comprehensive coverage of user SQL specifications

Applications built in various development languages

Supports Oracle, MySQL, DB2, Microsoft SQL Server, PostgreSQL, OceanBase

Jenkins plug-ins, JVM plug-ins, integration with DevOps, automated SQL QC

Powerful OpenAPI for quick integration of SQM capabilities with other user systems

Support more than ten scenarios of SQL quality control solutions of different scales and different R & D modes. The best one is the one that suits.

Intelligent optimization, able to give accurate optimization suggestions after comprehensive analysis of SQL, such as creating indexes, etc.

SQL QC in DevOps

SQL quality control in DevOps should solve problems comprehensively from three dimensions: process system construction, management and control means construction and personnel ability improvement.

SQL Quality Control is advanced to the development testing phase. SQL quality control is introduced before the development is delivered to the test, which improves the collaboration efficiency between teams, reduces the cost increase caused by rework, and introduces automatic SQL quality review capability in the test process, covering all aspects.

Improvement of personnel capacity and quality control. Personnel capacity can be improved through training, professional coaching, SQL code standardization, etc. Quality control, with the help of professional tools as the starting point for control, for extremely complex problems can be optimized with expert support.

A typical DevOps process can be viewed as a two-tiered process. The first level master process is the overall DevOps process, including "task management, code management, build and release, and other follow-ups."

The two-level process of "Build & Release" consists of multiple steps: source code control, code review, build, test, and deployment. Under the secondary process, there are many "operations", such as "code inspection", including normative inspection, safety inspection and other inspections.

The Jenkins plug-ins and JVM plug-ins developed by SQM support rapid assembly into DevOps pipelines, and automatically perform SQL quality checks in the code inspection process. In the testing phase, SQL quality checks are automated.

process schematic

Jenkins+ SQM Plug-in actual operation diagram

Case Analysis and Discussion

bank X

Solution: Jenkins plugin +JVM plugin +OpenAPI, phased implementation

SQM is integrated with the development process to provide SQL audit services during development testing. Deal with most SQL quality issues in the development phase, and advance operation and maintenance work to the development phase, greatly saving costs and improving development efficiency.

1) SQM platform implementation and DevOps convergence

2) SQM provides SQL script audit function (real-time audit)

3) Complex SQL can be handled by DBA through SQM platform

4) Provide SQL exemption processing flow

bank Y

Solution: Job +OpenAPI+ Platform

In order to achieve SQL quality control goals, the bank combines its own situation, starting from SQL quality control points, continuously practices iteration, and uses SQM audit tools to cover development environment and test environment for SQL audit and monitoring.

SQM project officially launched in 2016. SQM has played an important role in the bank's R & D system from the initial research implementation to the subsequent iterative improvement.

As of mid-2019, the SQM platform of this bank has more than 3,000 users, more than 400 configured projects (subsystems), more than 500 test libraries monitored, nearly 5,000 work orders in total, about 20 work orders created every day, and 80% completion rate of work orders processed on the same day. Automated SQL audit reduces the pressure of testing and operation and maintenance, greatly reduces the manual workload, and unifies the development specification process. Through continuous exploration, The workflow summarized best practices greatly reduces serious problems caused by SQL performance after the application is put into production, helping the bank to build a data middle platform, and the integration of DevOps development and operation and maintenance promotes the unified archiving, classification and tracking of project performance problems, forming a relatively complete processing closed loop, and providing quantitative indicator data for project team development quality evaluation.

bank Z

Solution: OpenAPI+ Library Capture + Process Reengineering

The expected goals of this line for SQL auditing are as follows:

1) Prevent problematic SQL from flowing into the production environment

2) SQL detection before project launch, review and rectify problematic SQL

3) Design closed-loop audit process to ensure that each problem SQL is rectified

After sorting out the development test process, the bank decided to integrate SQM review and development process for process reengineering.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.