com.github.chengyuxing.sql.XQLFileManager Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of rabbit-sql Show documentation
Show all versions of rabbit-sql Show documentation
Light wrapper of JDBC, support ddl, dml, query, plsql/procedure/function, transaction and manage sql
file.
package com.github.chengyuxing.sql;
import com.github.chengyuxing.common.io.FileResource;
import com.github.chengyuxing.common.script.lexer.FlowControlLexer;
import com.github.chengyuxing.common.script.parser.FlowControlParser;
import com.github.chengyuxing.common.script.exception.ScriptSyntaxException;
import com.github.chengyuxing.common.script.expression.IPipe;
import com.github.chengyuxing.common.tuple.Pair;
import com.github.chengyuxing.common.utils.ReflectUtil;
import com.github.chengyuxing.common.utils.StringUtil;
import com.github.chengyuxing.sql.exceptions.DuplicateException;
import com.github.chengyuxing.sql.support.TemplateFormatter;
import com.github.chengyuxing.sql.utils.SqlGenerator;
import com.github.chengyuxing.sql.utils.SqlHighlighter;
import com.github.chengyuxing.sql.utils.SqlUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.UncheckedIOException;
import java.lang.reflect.InvocationTargetException;
import java.net.URISyntaxException;
import java.nio.charset.Charset;
import java.util.*;
import java.util.concurrent.locks.ReentrantLock;
import java.util.function.BiConsumer;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import static com.github.chengyuxing.common.utils.StringUtil.NEW_LINE;
import static com.github.chengyuxing.common.utils.StringUtil.containsAnyIgnoreCase;
/**
* Dynamic SQL parse file manager
* Use standard sql block annotation ({@code /**}{@code /}), line annotation ({@code --}),
* named parameter ({@code :name}) and string template variable ({@code ${template}}) to
* extends SQL file standard syntax with special content format, brings more features to
* SQL file and follow the strict SQL file syntax.
* File type supports: {@code .xql}, {@code .sql}, suffix {@code .xql} means this file is
* {@code XQLFileManager} default file type.
* File path support {@link FileResource URI and classpath}.
* Notice: Rabbit-SQL IDEA Plugin only support detect {@code .xql} file.
* File content structure
* {@code key-value} format, key is sql name, value is sql statement, e.g.
*
*
* /*[sqlName1]*/
* select * from test.region where
* -- #if :id != blank
* id = :id
* -- #fi
* ${order}
* ;
*
* /*[sqlNameN]*/
* select * from test.user;
*
* /*{order}*/
* order by id desc;
* ...
*
*
*
* {@linkplain DynamicSqlParser Dynamic sql script} write in line annotation where starts with {@code --},
* check example following class path file: {@code template.xql}.
*
Invoke method {@link #get(String, Map)} to enjoy the dynamic sql!
*
* @see FlowControlParser
*/
public class XQLFileManager extends XQLFileManagerConfig implements AutoCloseable {
private static final Logger log = LoggerFactory.getLogger(XQLFileManager.class);
public static final Pattern NAME_PATTERN = Pattern.compile("/\\*\\s*\\[\\s*(?\\S+)\\s*]\\s*\\*/");
public static final Pattern PART_PATTERN = Pattern.compile("/\\*\\s*\\{\\s*(?\\S+)\\s*}\\s*\\*/");
public static final String SQL_DESC_START = "/*#";
public static final String XQL_DESC_QUOTE = "@@@";
public static final String PROPERTIES = "xql-file-manager.properties";
public static final String YML = "xql-file-manager.yml";
/**
* Template ({@code ${key}}) formatter.
* Default implementation: {@link SqlUtil#parseValue(Object, boolean) parseValue(value, boolean)}
*/
private TemplateFormatter templateFormatter = SqlUtil::parseValue;
private final ClassLoader classLoader = this.getClass().getClassLoader();
private final ReentrantLock lock = new ReentrantLock();
private final Map resources = new LinkedHashMap<>();
private volatile boolean initialized;
/**
* Constructs a new XQLFileManager.
* If classpath exists files：
*
* xql-file-manager.ymlxql-file-manager.properties
* load {@code .yml} first otherwise {@code .properties}.
*
* @see XQLFileManagerConfig
*/
public XQLFileManager() {
FileResource resource = new FileResource(YML);
if (resource.exists()) {
loadYaml(resource);
return;
}
resource = new FileResource(PROPERTIES);
if (resource.exists()) {
loadProperties(resource);
}
}
/**
* Constructs a new XQLFileManager with config location.
*
* @param configLocation {@link FileResource config file}: supports {@code .yml} and {@code .properties}
* @see XQLFileManagerConfig
*/
public XQLFileManager(String configLocation) {
super(configLocation);
}
/**
* Constructs a new XQLFileManager with xql file manager config.
*
* @param config xql file manager config
*/
public XQLFileManager(XQLFileManagerConfig config) {
config.copyStateTo(this);
}
/**
* Add a sql file.
*
* @param alias file alias
* @param fileName file path name
*/
public void add(String alias, String fileName) {
files.put(alias, fileName);
}
/**
* Add a sql file with default alias(file name without extension).
*
* @param fileName file path name
* @return file alias
*/
public String add(String fileName) {
String alias = FileResource.getFileName(fileName, false);
add(alias, fileName);
return alias;
}
/**
* Remove a sql file with associated sql resource.
*
* @param alias file alias
*/
public void remove(String alias) {
lock.lock();
try {
files.remove(alias);
resources.remove(alias);
} finally {
lock.unlock();
}
}
/**
* Parse sql file to structured resource.
*
* @param alias file alias
* @param filename file name
* @param fileResource file resource
* @return structured resource
* @throws IOException if file not exists
* @throws DuplicateException if duplicate sql fragment name found in same sql file
* @throws URISyntaxException if file uri syntax error
*/
public Resource parse(String alias, String filename, FileResource fileResource) throws IOException, URISyntaxException {
Map entry = new LinkedHashMap<>();
StringJoiner xqlDesc = new StringJoiner(NEW_LINE);
try (BufferedReader reader = fileResource.getBufferedReader(Charset.forName(charset))) {
String blockName = null;
List descriptionBuffer = new ArrayList<>();
List sqlBodyBuffer = new ArrayList<>();
String line;
while ((line = reader.readLine()) != null) {
String trimLine = line.trim();
if (trimLine.isEmpty()) {
continue;
}
Matcher m_name = NAME_PATTERN.matcher(trimLine);
if (m_name.matches()) {
blockName = m_name.group("name");
if (entry.containsKey(blockName)) {
throw new DuplicateException("same sql fragment name: '" + blockName + "' in " + filename);
}
continue;
}
Matcher m_part = PART_PATTERN.matcher(trimLine);
if (m_part.matches()) {
blockName = "${" + m_part.group("part") + "}";
if (entry.containsKey(blockName)) {
throw new DuplicateException("same sql template name: '" + blockName + "' in " + filename);
}
continue;
}
if (trimLine.startsWith("/*")) {
// /*#...#*/
if (trimLine.startsWith(SQL_DESC_START)) {
if (trimLine.endsWith("*/")) {
String description = trimLine.substring(3, trimLine.length() - 2);
if (description.endsWith("#")) {
description = description.substring(0, description.length() - 1);
}
if (!description.trim().isEmpty()) {
descriptionBuffer.add(description);
}
continue;
}
String descriptionStart = trimLine.substring(3);
if (!descriptionStart.trim().isEmpty()) {
descriptionBuffer.add(descriptionStart);
}
String descLine;
while ((descLine = reader.readLine()) != null) {
if (descLine.trim().endsWith("*/")) {
String descriptionEnd = descLine.substring(0, descLine.lastIndexOf("*/"));
if (descriptionEnd.endsWith("#")) {
descriptionEnd = descriptionEnd.substring(0, descriptionEnd.length() - 1);
}
if (!descriptionEnd.trim().isEmpty()) {
descriptionBuffer.add(descriptionEnd);
}
break;
}
descriptionBuffer.add(descLine);
}
continue;
}
// @@@
// ...
// @@@
if (Objects.isNull(blockName)) {
if (trimLine.endsWith("*/")) {
continue;
}
String a;
descBlock:
while ((a = reader.readLine()) != null) {
String ta = a.trim();
if (ta.endsWith("*/")) {
break;
}
if (ta.equals(XQL_DESC_QUOTE)) {
String b;
while ((b = reader.readLine()) != null) {
String tb = b.trim();
if (tb.equals(XQL_DESC_QUOTE)) {
break;
}
if (tb.endsWith("*/")) {
break descBlock;
}
xqlDesc.add(tb);
}
}
}
}
}
if (Objects.nonNull(blockName) && !blockName.isEmpty()) {
sqlBodyBuffer.add(line);
if (trimLine.endsWith(delimiter)) {
String sql = String.join(NEW_LINE, sqlBodyBuffer);
sql = sql.substring(0, sql.lastIndexOf(delimiter)).trim();
String desc = String.join(NEW_LINE, descriptionBuffer);
entry.put(blockName, scanSql(alias, filename, blockName, sql, desc));
blockName = "";
sqlBodyBuffer.clear();
descriptionBuffer.clear();
}
}
}
// if last part of sql is not ends with delimiter symbol
if (!StringUtil.isEmpty(blockName)) {
String lastSql = String.join(NEW_LINE, sqlBodyBuffer);
String lastDesc = String.join(NEW_LINE, descriptionBuffer);
entry.put(blockName, scanSql(alias, filename, blockName, lastSql, lastDesc));
}
}
if (!entry.isEmpty()) {
mergeSqlTemplate(entry);
}
Resource resource = new Resource(alias, filename);
resource.setEntry(Collections.unmodifiableMap(entry));
resource.setLastModified(fileResource.getLastModified());
resource.setDescription(xqlDesc.toString().trim());
return resource;
}
/**
* Scan sql object.
*
* @param alias alias
* @param filename sql file name
* @param blockName sql fragment name
* @param sql sql content
* @param desc sql description
* @return sql object
*/
protected Sql scanSql(String alias, String filename, String blockName, String sql, String desc) {
try {
newDynamicSqlParser(sql).verify();
} catch (ScriptSyntaxException e) {
throw new ScriptSyntaxException("File: " + filename + " -> '" + blockName + "' dynamic sql script syntax error.", e);
}
Sql sqlObj = new Sql(sql);
sqlObj.setDescription(desc);
log.debug("scan {} to get sql({}) [{}.{}]：{}", filename, delimiter, alias, blockName, SqlHighlighter.highlightIfAnsiCapable(sql));
return sqlObj;
}
/**
* Merge and reuse sql template into sql fragment.
*
* @param sqlResource sql resource
*/
protected void mergeSqlTemplate(Map sqlResource) {
Map templates = new HashMap<>();
for (Map.Entry e : sqlResource.entrySet()) {
String k = e.getKey();
if (k.startsWith("${")) {
String template = fixTemplate(e.getValue().getContent());
templates.put(k.substring(2, k.length() - 1), template);
}
}
if (templates.isEmpty() && constants.isEmpty()) {
return;
}
for (Map.Entry e : sqlResource.entrySet()) {
Sql sql = e.getValue();
String sqlContent = sql.getContent();
if (sqlContent.contains("${")) {
sqlContent = SqlUtil.formatSql(sqlContent, templates, templateFormatter);
sqlContent = SqlUtil.formatSql(sqlContent, constants, templateFormatter);
// remove empty line.
sql.setContent(StringUtil.removeEmptyLine(sqlContent));
}
}
}
/**
* In case line annotation in template occurs error,
* e.g.
* sql statement:
*
* select * from test.user where ${cnd} order by id
*
* {cnd}
*
*
* -- #if :id <> blank
* id = :id
* -- #fi
*
*
* result:
*
*
* select * from test.user where
* -- #if :id <> blank
* id = :id
* -- #fi
* order by id
*
*
*
* @param template template
* @return safe template
*/
protected String fixTemplate(String template) {
String newTemplate = template;
if (newTemplate.trim().startsWith("--")) {
newTemplate = NEW_LINE + newTemplate;
}
int lastLN = newTemplate.lastIndexOf(NEW_LINE);
if (lastLN != -1) {
String lastLine = newTemplate.substring(lastLN);
if (lastLine.trim().startsWith("--")) {
newTemplate += NEW_LINE;
}
}
return newTemplate;
}
/**
* Load all sql files and parse to structured resources.
*
* @throws UncheckedIOException if file not exists or read error
* @throws RuntimeException if uri syntax error
*/
protected void loadResources() {
try {
// In case method copyStateTo invoked, files are updated but resources not,
// It's necessary to remove non-associated dirty resources.
resources.entrySet().removeIf(e -> !files.containsKey(e.getKey()));
// Reload and parse all sql file.
for (Map.Entry e : files.entrySet()) {
String alias = e.getKey();
String filename = e.getValue();
FileResource fr = new FileResource(filename);
if (fr.exists()) {
String ext = fr.getFilenameExtension();
if (ext != null && (ext.equals("sql") || ext.equals("xql"))) {
if (resources.containsKey(alias)) {
Resource resource = resources.get(alias);
long oldLastModified = resource.getLastModified();
long lastModified = fr.getLastModified();
if (oldLastModified > 0 && oldLastModified != lastModified) {
resources.put(alias, parse(alias, filename, fr));
log.debug("reload modified sql file: {}", filename);
}
} else {
resources.put(alias, parse(alias, filename, fr));
}
}
} else {
throw new FileNotFoundException("sql file '" + filename + "' of name '" + alias + "' not found!");
}
}
} catch (IOException e) {
throw new UncheckedIOException("load sql file error.", e);
} catch (URISyntaxException e) {
throw new RuntimeException("sql file uri syntax error.", e);
}
}
/**
* Load custom pipes.
*/
protected void loadPipes() {
if (pipes.isEmpty()) return;
if (pipes.keySet().equals(pipeInstances.keySet())) {
return;
}
try {
for (Map.Entry entry : pipes.entrySet()) {
pipeInstances.put(entry.getKey(), (IPipe) ReflectUtil.getInstance(classLoader.loadClass(entry.getValue())));
}
} catch (ClassNotFoundException | InstantiationException | IllegalAccessException |
InvocationTargetException | NoSuchMethodException e) {
throw new RuntimeException("init pipe error.", e);
}
if (log.isDebugEnabled()) {
if (!pipeInstances.isEmpty())
log.debug("loaded pipes {}", pipeInstances);
}
}
/**
* Initialing XQL file manager.
*
* @throws UncheckedIOException if file not exists or read error
* @throws RuntimeException if sql uri syntax error or load pipes error
* @throws DuplicateException if duplicate sql fragment name found in same sql file
*/
public void init() {
lock.lock();
try {
loading = true;
loadResources();
loadPipes();
} finally {
loading = false;
initialized = true;
lock.unlock();
}
}
/**
* Foreach all sql resource.
*
* @param consumer (alias, resource) -> void
*/
public void foreach(BiConsumer consumer) {
resources.forEach(consumer);
}
/**
* Get all sql fragment name.
*
* @return sql fragment names set
*/
public Set names() {
Set names = new HashSet<>();
foreach((k, r) -> r.getEntry().keySet().forEach(n -> names.add(k + "." + n)));
return names;
}
/**
* Get sql fragment count.
*
* @return sql fragment count
*/
public int size() {
int i = 0;
for (Resource resource : resources.values()) {
i += resource.getEntry().size();
}
return i;
}
/**
* Check resources contains sql fragment or not.
*
* @param name sql reference name ({@code .})
* @return true if exists or false
*/
public boolean contains(String name) {
try {
getSqlObject(name);
return true;
} catch (NoSuchElementException e) {
log.warn(e.getMessage());
return false;
}
}
/**
* Get sql resource.
*
* @param alias sql file alias
* @return sql resource
*/
public Resource getResource(String alias) {
return resources.get(alias);
}
/**
* Get all sql resources.
*
* @return unmodifiable sql resources
*/
public Map getResources() {
return Collections.unmodifiableMap(resources);
}
/**
* Get a sql object.
*
* @param name sql reference name ({@code .})
* @return sql object
* @throws NoSuchElementException if sql fragment name not exists
* @throws IllegalArgumentException if sql reference name format error
*/
public Sql getSqlObject(String name) {
int dotIdx = name.lastIndexOf(".");
if (dotIdx < 1) {
throw new IllegalArgumentException("Invalid sql reference name, please follow . format.");
}
String alias = name.substring(0, dotIdx);
if (resources.containsKey(alias)) {
Map e = getResource(alias).getEntry();
String sqlName = name.substring(dotIdx + 1);
if (e.containsKey(sqlName)) {
return e.get(sqlName);
}
}
throw new NoSuchElementException(String.format("no SQL named [%s] was found.", name));
}
/**
* Get a sql fragment.
*
* @param name sql reference name ({@code .})
* @return sql fragment
* @throws NoSuchElementException if sql fragment name not exists
* @throws IllegalArgumentException if sql reference name format error
*/
public String get(String name) {
String sql = getSqlObject(name).getContent();
return SqlUtil.trimEnd(sql);
}
/**
* Get and calc a dynamic sql.
*
* @param name sql name ({@code .})
* @param args dynamic sql script expression args
* @return parsed sql and extra args calculated by {@code #for} expression if exists
* @throws NoSuchElementException if sql fragment name not exists
* @throws ScriptSyntaxException dynamic sql script syntax error
* @see DynamicSqlParser
*/
public Pair> get(String name, Map args) {
try {
return parseDynamicSql(get(name), args);
} catch (Exception e) {
throw new ScriptSyntaxException("an error occurred when getting dynamic sql of name: " + name, e);
}
}
/**
* Parse dynamic sql.
*
* @param sql dynamic sql
* @param args dynamic sql script expression args
* @return parsed sql and extra args calculated by {@code #for} expression if exists
*/
public Pair> parseDynamicSql(String sql, Map args) {
if (!containsAnyIgnoreCase(sql, FlowControlLexer.KEYWORDS)) {
return Pair.of(sql, Collections.emptyMap());
}
Map myArgs = new HashMap<>();
if (Objects.nonNull(args)) {
myArgs.putAll(args);
}
myArgs.put("_parameter", args);
myArgs.put("_databaseId", databaseId);
DynamicSqlParser parser = newDynamicSqlParser(sql);
String parsedSql = parser.parse(myArgs);
String fixedSql = SqlUtil.repairSyntaxError(parsedSql);
return Pair.of(fixedSql, parser.getForContextVars());
}
/**
* Get a constant.
*
* @param key constant name
* @return constant value
*/
public Object getConstant(String key) {
return constants.get(key);
}
/**
* Check XQL file manager is initialized or not.
*
* @return true if initialized or false
*/
public boolean isInitialized() {
return initialized;
}
/**
* Cleanup XQL file manager.
*/
@Override
public void close() {
lock.lock();
try {
files.clear();
resources.clear();
pipes.clear();
pipeInstances.clear();
constants.clear();
} finally {
lock.unlock();
}
}
/**
* Create a new dynamic sql parser.
*
* @param sql sql
* @return dynamic sql parser
*/
public DynamicSqlParser newDynamicSqlParser(String sql) {
return new DynamicSqlParser(sql);
}
public TemplateFormatter getTemplateFormatter() {
return templateFormatter;
}
public void setTemplateFormatter(TemplateFormatter templateFormatter) {
this.templateFormatter = templateFormatter;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (!(o instanceof XQLFileManager)) return false;
if (!super.equals(o)) return false;
XQLFileManager that = (XQLFileManager) o;
return getResources().equals(that.getResources());
}
@Override
public int hashCode() {
int result = super.hashCode();
result = 31 * result + getResources().hashCode();
return result;
}
/**
* Dynamic sql parser.
*/
public class DynamicSqlParser extends FlowControlParser {
public static final String FOR_VARS_KEY = "_for";
public static final String VAR_PREFIX = FOR_VARS_KEY + '.';
public DynamicSqlParser(String sql) {
super(sql);
}
@Override
protected Map> getPipes() {
return getPipeInstances();
}
/**
* Cleanup annotation in for loop and create indexed arg for special format named arg, e.g.
* Mock data {@code users} and {@code forIndex: 0}:
*
* ["CYX", "jack", "Mike"]
*
* for loop:
*
*
* -- #for user,idx of :users delimiter ', '
* :user
* -- #done
*
*
* result:
*
*
* :_for.user_0_0,
* :_for.user_0_1,
* :_for.user_0_2,
*
*
*
* @param forIndex each for loop auto index
* @param varIndex for var auto index
* @param varName for var name, e.g. {@code }
* @param idxName for index name, e.g. {@code }
* @param body content in for loop
* @param args each for loop args (index and value) which created by for expression
* @return formatted content
*/
@Override
protected String forLoopBodyFormatter(int forIndex, int varIndex, String varName, String idxName, String body, Map args) {
String formatted = body;
if (body.contains("${")) {
formatted = SqlUtil.formatSql(body, args, templateFormatter);
}
if (formatted.contains(namedParamPrefix + varName) || formatted.contains(namedParamPrefix + idxName)) {
StringBuilder sb = new StringBuilder();
Pattern p = new SqlGenerator(namedParamPrefix).getNamedParamPattern();
Matcher m = p.matcher(formatted);
int lastMatchEnd = 0;
while (m.find()) {
sb.append(formatted, lastMatchEnd, m.start());
lastMatchEnd = m.end();
String name = m.group(1);
if (Objects.isNull(name)) {
sb.append(m.group());
continue;
}
if (name.equals(varName) || name.equals(idxName)) {
sb.append(namedParamPrefix)
.append(VAR_PREFIX)
.append(forVarKey(name, forIndex, varIndex));
continue;
}
// -- #for item of :data | kv
// ${item.key} = :item.value
//-- #done
// --------------------------
// name: item.value
// varName: item
if (name.startsWith(varName + '.')) {
String suffix = name.substring(varName.length());
sb.append(namedParamPrefix)
.append(VAR_PREFIX)
.append(forVarKey(varName, forIndex, varIndex))
.append(suffix);
continue;
}
sb.append(m.group());
}
sb.append(formatted.substring(lastMatchEnd));
formatted = sb.toString();
}
return formatted;
}
/**
* Trim line annotation for detect dynamic sql script expression.
*
* @param line current line
* @return script expression or other line
*/
@Override
protected String trimExpressionLine(String line) {
String lt = line.trim();
if (lt.startsWith("--")) {
return lt.substring(2);
}
return line;
}
}
/**
* Sql object.
*/
public static final class Sql {
private String content;
private String description = "";
public Sql(String content) {
this.content = content;
}
void setContent(String content) {
this.content = content;
}
public String getContent() {
return content;
}
public String getDescription() {
return description;
}
void setDescription(String description) {
this.description = description;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (!(o instanceof Sql)) return false;
Sql sql = (Sql) o;
return getContent().equals(sql.getContent()) && getDescription().equals(sql.getDescription());
}
@Override
public int hashCode() {
int result = getContent().hashCode();
result = 31 * result + getDescription().hashCode();
return result;
}
}
/**
* Sql file resource.
*/
public static final class Resource {
private final String alias;
private final String filename;
private long lastModified = -1;
private String description;
private Map entry;
public Resource(String alias, String filename) {
this.alias = alias;
this.filename = filename;
this.entry = Collections.emptyMap();
}
public String getAlias() {
return alias;
}
public String getFilename() {
return filename;
}
public long getLastModified() {
return lastModified;
}
void setLastModified(long lastModified) {
this.lastModified = lastModified;
}
public String getDescription() {
return description;
}
void setDescription(String description) {
this.description = description;
}
public Map getEntry() {
return entry;
}
void setEntry(Map entry) {
if (Objects.nonNull(entry))
this.entry = entry;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (!(o instanceof Resource)) return false;
Resource resource = (Resource) o;
return getLastModified() == resource.getLastModified() && Objects.equals(getAlias(), resource.getAlias()) && Objects.equals(getFilename(), resource.getFilename()) && Objects.equals(getDescription(), resource.getDescription()) && getEntry().equals(resource.getEntry());
}
@Override
public int hashCode() {
int result = Objects.hashCode(getAlias());
result = 31 * result + Objects.hashCode(getFilename());
result = 31 * result + Long.hashCode(getLastModified());
result = 31 * result + Objects.hashCode(getDescription());
result = 31 * result + getEntry().hashCode();
return result;
}
}
}