gu.sql2java.SqliteInitializer Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of sql2java-manager Show documentation
sql2java manager class package for accessing database
There is a newer version: 5.2.0
package gu.sql2java;

import static gu.sql2java.Constant.JdbcProperty.*;
import static gu.sql2java.SimpleLog.logString;
import static com.google.common.base.Preconditions.checkState;

import java.io.File;
import java.net.URL;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.List;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.google.common.base.Function;
import com.google.common.base.Joiner;
import com.google.common.collect.Lists;

import gu.sql2java.Constant.JdbcProperty;

/**
 * SQLite initializer
 * @author guyadong
 *
 */
public class SqliteInitializer extends BaseEmbeddedInitializer {

	public SqliteInitializer(File db, URL createSql, boolean runInMemory) {
		super(db, createSql, runInMemory);
	}

	private static String createJDBCUrl(boolean runInMemory, File db) {
		String jdbcurl = null;
		if (runInMemory) {
			jdbcurl = String.format("jdbc:sqlite::memory:");
		} else {
			jdbcurl = String.format("jdbc:sqlite:%s", db.getAbsolutePath());
		}
		return jdbcurl;
	}
	
	@Override
	protected void doInitMemory() {
		String sqlStr = "restore from " + db.getAbsolutePath();
		Managers.getSqlRunner(dbprops.getProperty(JdbcProperty.ALIAS.key)).runSql(sqlStr);
	}
	
	@Override
	protected ScriptRunner normalize(ScriptRunner runner){
		return runner.addClearRegex(Pattern.compile("DEFAULT +CHARSET *= *(.+)",Pattern.CASE_INSENSITIVE))
				.addClearRegex(Pattern.compile("ON +UPDATE +CURRENT_TIMESTAMP",Pattern.CASE_INSENSITIVE))
				.addClearRegex(Pattern.compile("SET +NAMES.*",Pattern.CASE_INSENSITIVE))
				.addReplaceRegex(Pattern.compile("AUTO_INCREMENT",Pattern.CASE_INSENSITIVE),"AUTOINCREMENT")
				/** 替换SQL整数类型(如'INT(11)')为integer */
				.addReplaceRegex(Pattern.compile("int\\s*\\(\\s*\\d+\\s*\\)",Pattern.CASE_INSENSITIVE),"integer")
				.addIgnoreRegex(Pattern.compile("SET +NAMES.*",Pattern.CASE_INSENSITIVE))
				.addIgnoreRegex(Pattern.compile("SET +CHARACTER.*",Pattern.CASE_INSENSITIVE));
	}
	@Override
	protected void doPersist() {
		String sqlStr = "backup to " + db.getAbsolutePath();
		Managers.getSqlRunner(dbprops.getProperty(JdbcProperty.ALIAS.key)).runSql(sqlStr);
	}

	@Override
	protected void writeDbProps(Properties dbprops) {
		dbprops.setProperty(JDBC_URL.key, createJDBCUrl(runInMemory, db));
		dbprops.setProperty(DATASOURCE.key, "SQLITE");
	}

	@Override
	protected void checkExistsDatabse(File db) throws EmbeddedInitException {
		if(db.isFile() && db.canRead() && db.canWrite()){
			return;
		}
		throw new EmbeddedInitException(logString("{} IS NOT a SQLite database", db.getAbsolutePath()));

	}
	
	private List getPrimaryKeys(DatabaseMetaData metadata,String tablename) throws SQLException{
		ResultSet resultSet = metadata.getPrimaryKeys("", "", tablename);
		List pkNames = Lists.newLinkedList();
		while (resultSet.next()) {
			pkNames.add(resultSet.getString("COLUMN_NAME"));	
		}
		checkState(!pkNames.isEmpty(),"NOT FOUND Primary key of table  %s",tablename);
		return pkNames;
	}
	
	@Override
	protected List afterCreateTable(ScriptRunner runner) throws SQLException{
		List executableSqls = runner.getExecutableSqls();
		if(executableSqls.isEmpty()){
			return Collections.emptyList();
		}
		Connection connection = null;
		try {
			connection = Managers.managerInstanceOfAlias(dbprops.getProperty(JdbcProperty.ALIAS.key)).getConnection();
			DatabaseMetaData metadata = connection.getMetaData();			
			List additional = Lists.newLinkedList();
			Pattern tablePattern = Pattern.compile("CREATE\\s+TABLE\\s+((\\w+\\s+)*)([\\w\\.\'\"`]+)\\s*\\((.+)\\)",Pattern.CASE_INSENSITIVE);
			// 为所有表类型为timestamp且更新策略为 ON UPDATE CURRENT_TIMESTAMP的字段创建触发器
			for(String sql:executableSqls){
				Matcher tmacher = tablePattern.matcher(sql);
				while(tmacher.find()){
					String tablename = tmacher.group(3);
					List pks = getPrimaryKeys(metadata,tablename);
					String cols = tmacher.group(4);
					Pattern colPattern = Pattern.compile("([\'\"`])?([\\w\\d]+)\\1\\s+timestamp\\s+DEFAULT(\\s+(?:CURRENT_TIMESTAMP|NULL)\\s+)?ON\\s+UPDATE\\s+CURRENT_TIMESTAMP\\s*(,|$)",Pattern.CASE_INSENSITIVE);
					Matcher cmacher = colPattern.matcher(cols);
					while(cmacher.find()){					
						String timestampColumn = cmacher.group(2);
						String triggerName = "trigger_"+tablename + "_" + timestampColumn;
						String tmpl = 
								"CREATE TRIGGER IF NOT EXISTS ${trigger} "
										+ "AFTER  UPDATE "
										+ "ON ${table} "
										+ "FOR EACH ROW "
										+ "WHEN NEW.${col} <= OLD.${col} "
										+ "BEGIN  "
										+ "	UPDATE ${table} SET ${col}=CURRENT_TIMESTAMP WHERE ${where};"
										+ "END;"; 
						
						String where = Joiner.on(" AND ").join(Lists.transform(pks, new Function(){
							@Override
							public String apply(String input) {
								return "NEW.{}=OLD.{}".replace("{}", input);
							}}));
						
						String triggerSQL = tmpl.replace("${table}", tablename)
								.replace("${col}", timestampColumn)
								.replace("${trigger}", triggerName)
								.replace("${where}", where);
//						SimpleLog.log("create trigger " + triggerName);
//						SimpleLog.log(triggerSQL);
						additional.add(triggerSQL);
					}
				}
			}
			return additional;
		} finally{
			if(null != connection){
				connection.close();
			}
		}
	}
	
	/**
	 * SQLite数据库初始化
	 * @param db 数据文件位置
	 * @param createSql 数据库建表语句(SQL)位置
	 * @param runInMemory 为{@code true}以内存方式运行
	 * @param properties 附加的配置参数
	 * @return {@link SqliteInitializer}实例
	 */
	public static SqliteInitializer init(File db, URL createSql, boolean runInMemory, Properties properties){
		return init(SqliteInitializer.class, db, createSql, runInMemory, properties);
	}
	/**
	 * SQLite数据库初始化
	 * @param db 数据文件位置(File)
	 * @param createSqlURL 数据库建表语句(SQL)位置(URL)
	 * @param runInMemory 为{@code true}以内存方式运行
	 * @param properties 附加的配置参数
	 * @return {@link SqliteInitializer}实例
	 */
	public static SqliteInitializer init(String db, String createSqlURL, boolean runInMemory, Properties properties){
		return init(SqliteInitializer.class, db, createSqlURL, runInMemory, properties);
	}
}