Small knowledge, big challenge! This article is part of the “Programmer’s Essentials
This article also participated in the “Digitalstar Project” to win a creative gift package and creative incentive money
Today I am free to adjust the code of database backup I wrote before.
1. Problems in work The database development process is generally to first create a table structure in Power Design (because pd other departments need to see), and then copy the generated DDL table construction sentences and execute them in the database, and then create a table. There are some problems with this workflow.
1. It is not easy to modify, interrupting the focus of code development. If I wanted to modify the table during development, I would modify the table structure directly in the database through Navicat, adding, deleting, and normally synchronizing the table to PD. This process interrupted my focus on developing the code, so we needed to get rid of it.
2, easy to forget, there will be a psychological burden Modify data is the basic operation, if in the process of developing functional changes frequently, to synchronize pd, would have a burden, to develop such circumstances would have forgotten, in the case of functional development, seldom go to completion pd again, basically a incomplete pd significance is not big. After all, I don’t want to be asked how the structure of the table is every time, and I don’t want to waste my breath, there will be no maintenance, no good feeling.
3, game version update frequently, unable to roll back our database In the most busy time, the game is basically a new version for two weeks, each version will be accompanied by some form of change, although our game code will have version record, but the data table structure hasn’t been a good backup, such circumstances cause a database table structure is very difficult to roll back, So you need to find a way to back up the database.
When you have a problem like this, you want to solve it. Solving problems improves productivity (time to paddle) and reduces the chance of making mistakes (you don’t want to take the blame). The following is the tool I wrote to organize data, using screw, I added the backup of the database table structure, download code, simple to change the configuration can run directly, take to thank you.
Point of optimization:
1. Add a date to the generated file as the version number
Alter table structure add new drop table statement
3. Added SQL backup for building tables
The following code is copied directly into the editor after modifying the database connection information, can be run directly
package screw;
import cn.smallbun.screw.core.Configuration;
import cn.smallbun.screw.core.engine.EngineConfig;
import cn.smallbun.screw.core.engine.EngineFileType;
import cn.smallbun.screw.core.engine.EngineTemplateType;
import cn.smallbun.screw.core.execute.DocumentationExecute;
import cn.smallbun.screw.core.process.ProcessConfig;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.nio.charset.StandardCharsets;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class App {
public static void main(String[] ags) throws IOException, SQLException {
String dbName = "fate";
HikariConfig config = new HikariConfig();
config.setDriverClassName("com.mysql.cj.jdbc.Driver");
config.setJdbcUrl("JDBC: mysql: / / 127.0.0.1:3306 /" + dbName +" ?serverTimezone=UTC");
config.setUsername("root");
config.setPassword("root");
config.addDataSourceProperty("useInformationSchema"."true");
config.setMinimumIdle(2);
config.setMaximumPoolSize(5);
DataSource ds = new HikariDataSource(config);
String userDir = System.getProperty("user.dir") + "\\src\\test\\java\\com\\pdool\\";
System.out.println(userDir);
SimpleDateFormat dataFormat = new SimpleDateFormat("yyyyMMdd");
String versionStr = dataFormat.format(new Date());
List<String> ignoreTable = new ArrayList<>();
List<String> ignorePrefix = new ArrayList<>();
List<String> ignoreSuffix = new ArrayList<>();
ignoreSuffix.add("_test");
ignoreSuffix.add("test");
for (int i = 0; i < 10; i++) {
ignoreSuffix.add(String.valueOf(i));
}
createHtml(ds, userDir, versionStr, ignoreTable, ignorePrefix, ignoreSuffix);
createSql(dbName, ds, userDir, versionStr, ignoreTable, ignorePrefix, ignoreSuffix);
}
public static void createHtml(DataSource dataSource, String userDir, String versionStr, List<String> ignoreTable, List<String> ignorePrefix, List<String> ignoreSuffix) {
EngineConfig engineConfig = EngineConfig.builder()
.fileOutputDir(userDir)
.openOutputDir(false)
.fileType(EngineFileType.HTML)
.produceType(EngineTemplateType.freemarker)
.build();
ProcessConfig processConfig = ProcessConfig.builder()
.ignoreTableName(ignoreTable)
.ignoreTablePrefix(ignorePrefix)
.ignoreTableSuffix(ignoreSuffix)
.build();
Configuration config = Configuration.builder()
.version(versionStr)
.description("Database Document")
.dataSource(dataSource)
.engineConfig(engineConfig)
.produceConfig(processConfig).build();
new DocumentationExecute(config).execute();
}
public static void createSql(String dbName, DataSource dataSource, String userDir, String versionStr, List<String> ignoreTable, List<String> ignorePrefix, List<String> ignoreSuffix) throws IOException, SQLException {
Statement tmt = null;
PreparedStatement pstmt = null;
List<String> createSqlList = new ArrayList<>();
String sql = "select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = '"+dbName+"' and TABLE_TYPE = 'BASE TABLE'";
tmt = dataSource.getConnection().createStatement();
pstmt = dataSource.getConnection().prepareStatement(sql);
ResultSet res = tmt.executeQuery(sql);
while (res.next()) {
String tableName = res.getString(1);
if (tableName.contains("`")) {
continue;
}
if (ignoreTable.contains(tableName)) {
continue;
}
boolean isContinue = false;
for (String prefix : ignorePrefix) {
if (tableName.startsWith(prefix)) {
isContinue = true;
break; }}if (isContinue) {
continue;
}
for (String suffix : ignoreSuffix) {
if (tableName.startsWith(suffix)) {
isContinue = true;
break; }}if (isContinue) {
continue;
}
ResultSet rs = pstmt.executeQuery("show create Table `" + tableName + "`");
while (rs.next()) {
createSqlList.add("DROP TABLE IF EXISTS '" + tableName + "'");
createSqlList.add(rs.getString(2));
}
}
String head = "-- database construction clause \r\n";
head += "-- db:" + dbName + " version: " + versionStr + "\r\n";
String collect = String.join("; \r\n", createSqlList);
collect = head + collect + ";";
string2file(collect, userDir + dbName + "_" + versionStr + ".sql");
}
public static void string2file(String collect, String dirStr) throws IOException {
System.out.println("File Address"+ dirStr);
OutputStreamWriter osw = null;
try {
osw = new OutputStreamWriter(new FileOutputStream(new File(dirStr)), StandardCharsets.UTF_8);
osw.write(collect);
osw.flush();
} finally {
if(osw ! =null) { osw.close(); }}}}Copy the code3. The files generated as a result of the operation are shown below
File address: The console also has a print file address
Fate_20210304. SQL: Database name fate, generation date is 20210304, content is a table construction clause. Mainly used to correspond with the code, restore the database.
Fate_ database document __20210304.html: : Database name fate, generated date is 20210304, the content is HTML, mainly used for communication to other departments.
conclusion
I had written the tool in the company, but unfortunately I couldn’t get it out of the company Intranet, so I rewrote it again and spent a lot of time. It was quite late and I was ready for bed.
PS: Original is not easy, a thumbs up