package org.h2.command.dml;
import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.Set;
import org.h2.api.ErrorCode;
import org.h2.command.CommandInterface;
import org.h2.constraint.Constraint;
import org.h2.engine.Comment;
import org.h2.engine.Constants;
import org.h2.engine.Database;
import org.h2.engine.DbObject;
import org.h2.engine.Domain;
import org.h2.engine.Right;
import org.h2.engine.Role;
import org.h2.engine.Session;
import org.h2.engine.Setting;
import org.h2.engine.SysProperties;
import org.h2.engine.User;
import org.h2.engine.UserAggregate;
import org.h2.expression.Expression;
import org.h2.expression.ExpressionColumn;
import org.h2.index.Cursor;
import org.h2.index.Index;
import org.h2.message.DbException;
import org.h2.result.LocalResult;
import org.h2.result.ResultInterface;
import org.h2.result.Row;
import org.h2.schema.Constant;
import org.h2.schema.Schema;
import org.h2.schema.SchemaObject;
import org.h2.schema.Sequence;
import org.h2.schema.TriggerObject;
import org.h2.table.Column;
import org.h2.table.PlanItem;
import org.h2.table.Table;
import org.h2.table.TableType;
import org.h2.util.IOUtils;
import org.h2.util.MathUtils;
import org.h2.util.StringUtils;
import org.h2.util.Utils;
import org.h2.value.Value;
import org.h2.value.ValueString;
public class ScriptCommand extends ScriptBase {
private Charset charset = StandardCharsets.UTF_8;
private Set<String> schemaNames;
private Collection<Table> tables;
private boolean passwords;
private boolean data;
private boolean settings;
private boolean drop;
private boolean simple;
private boolean withColumns;
private LocalResult result;
private String lineSeparatorString;
private byte[] lineSeparator;
private byte[] buffer;
private boolean tempLobTableCreated;
private int nextLobId;
private int lobBlockSize = Constants.IO_BUFFER_SIZE;
public ScriptCommand(Session session) {
super(session);
}
@Override
public boolean isQuery() {
return true;
}
public void setSchemaNames(Set<String> schemaNames) {
this.schemaNames = schemaNames;
}
public void setTables(Collection<Table> tables) {
this.tables = tables;
}
public void setData(boolean data) {
this.data = data;
}
public void setPasswords(boolean passwords) {
this.passwords = passwords;
}
public void setSettings(boolean settings) {
this.settings = settings;
}
public void setLobBlockSize(long blockSize) {
this.lobBlockSize = MathUtils.convertLongToInt(blockSize);
}
public void setDrop(boolean drop) {
this.drop = drop;
}
@Override
public ResultInterface queryMeta() {
LocalResult r = createResult();
r.done();
return r;
}
private LocalResult createResult() {
Database db = session.getDatabase();
return db.getResultFactory().create(session,
new Expression[] { new ExpressionColumn(db, new Column("SCRIPT", Value.STRING)) }, 1);
}
@Override
public ResultInterface query(int maxrows) {
session.getUser().checkAdmin();
reset();
Database db = session.getDatabase();
if (schemaNames != null) {
for (String schemaName : schemaNames) {
Schema schema = db.findSchema(schemaName);
if (schema == null) {
throw DbException.get(ErrorCode.SCHEMA_NOT_FOUND_1,
schemaName);
}
}
}
try {
result = createResult();
deleteStore();
openOutput();
if (out != null) {
buffer = new byte[Constants.IO_BUFFER_SIZE];
}
if (settings) {
for (Setting setting : db.getAllSettings()) {
if (setting.getName().equals(SetTypes.getTypeName(
SetTypes.CREATE_BUILD))) {
continue;
}
add(setting.getCreateSQL(), false);
}
}
if (out != null) {
add("", true);
}
for (User user : db.getAllUsers()) {
add(user.getCreateSQL(passwords), false);
}
for (Role role : db.getAllRoles()) {
add(role.getCreateSQL(true), false);
}
for (Schema schema : db.getAllSchemas()) {
if (excludeSchema(schema)) {
continue;
}
add(schema.getCreateSQL(), false);
}
for (Domain datatype : db.getAllDomains()) {
if (drop) {
add(datatype.getDropSQL(), false);
}
add(datatype.getCreateSQL(), false);
}
for (SchemaObject obj : db.getAllSchemaObjects(
DbObject.CONSTANT)) {
if (excludeSchema(obj.getSchema())) {
continue;
}
Constant constant = (Constant) obj;
add(constant.getCreateSQL(), false);
}
final ArrayList<Table> tables = db.getAllTablesAndViews(false);
Collections.sort(tables, new Comparator<Table>() {
@Override
public int compare(Table t1, Table t2) {
return t1.getId() - t2.getId();
}
});
for (Table table : tables) {
if (excludeSchema(table.getSchema())) {
continue;
}
if (excludeTable(table)) {
continue;
}
if (table.isHidden()) {
continue;
}
table.lock(session, false, false);
String sql = table.getCreateSQL();
if (sql == null) {
continue;
}
if (drop) {
add(table.getDropSQL(), false);
}
}
for (SchemaObject obj : db.getAllSchemaObjects(
DbObject.FUNCTION_ALIAS)) {
if (excludeSchema(obj.getSchema())) {
continue;
}
if (drop) {
add(obj.getDropSQL(), false);
}
add(obj.getCreateSQL(), false);
}
for (UserAggregate agg : db.getAllAggregates()) {
if (drop) {
add(agg.getDropSQL(), false);
}
add(agg.getCreateSQL(), false);
}
for (SchemaObject obj : db.getAllSchemaObjects(
DbObject.SEQUENCE)) {
if (excludeSchema(obj.getSchema())) {
continue;
}
Sequence sequence = (Sequence) obj;
if (drop) {
add(sequence.getDropSQL(), false);
}
add(sequence.getCreateSQL(), false);
}
int count = 0;
for (Table table : tables) {
if (excludeSchema(table.getSchema())) {
continue;
}
if (excludeTable(table)) {
continue;
}
if (table.isHidden()) {
continue;
}
table.lock(session, false, false);
String createTableSql = table.getCreateSQL();
if (createTableSql == null) {
continue;
}
final TableType tableType = table.getTableType();
add(createTableSql, false);
final ArrayList<Constraint> constraints = table.getConstraints();
if (constraints != null) {
for (Constraint constraint : constraints) {
if (Constraint.Type.PRIMARY_KEY == constraint.getConstraintType()) {
add(constraint.getCreateSQLWithoutIndexes(), false);
}
}
}
if (TableType.TABLE == tableType) {
if (table.canGetRowCount()) {
StringBuilder builder = new StringBuilder("-- ").append(table.getRowCountApproximation())
.append(" +/- SELECT COUNT(*) FROM ");
table.getSQL(builder, false);
add(builder.toString(), false);
}
if (data) {
count = generateInsertValues(count, table);
}
}
final ArrayList<Index> indexes = table.getIndexes();
for (int j = 0; indexes != null && j < indexes.size(); j++) {
Index index = indexes.get(j);
if (!index.getIndexType().getBelongsToConstraint()) {
add(index.getCreateSQL(), false);
}
}
}
if (tempLobTableCreated) {
add("DROP TABLE IF EXISTS SYSTEM_LOB_STREAM", true);
add("CALL SYSTEM_COMBINE_BLOB(-1)", true);
add("DROP ALIAS IF EXISTS SYSTEM_COMBINE_CLOB", true);
add("DROP ALIAS IF EXISTS SYSTEM_COMBINE_BLOB", true);
tempLobTableCreated = false;
}
final ArrayList<SchemaObject> constraints = db.getAllSchemaObjects(
DbObject.CONSTRAINT);
Collections.sort(constraints, null);
for (SchemaObject obj : constraints) {
if (excludeSchema(obj.getSchema())) {
continue;
}
Constraint constraint = (Constraint) obj;
if (excludeTable(constraint.getTable())) {
continue;
}
if (constraint.getTable().isHidden()) {
continue;
}
if (Constraint.Type.PRIMARY_KEY != constraint.getConstraintType()) {
add(constraint.getCreateSQLWithoutIndexes(), false);
}
}
for (SchemaObject obj : db.getAllSchemaObjects(DbObject.TRIGGER)) {
if (excludeSchema(obj.getSchema())) {
continue;
}
TriggerObject trigger = (TriggerObject) obj;
if (excludeTable(trigger.getTable())) {
continue;
}
add(trigger.getCreateSQL(), false);
}
for (Right right : db.getAllRights()) {
DbObject object = right.getGrantedObject();
if (object != null) {
if (object instanceof Schema) {
if (excludeSchema((Schema) object)) {
continue;
}
} else if (object instanceof Table) {
Table table = (Table) object;
if (excludeSchema(table.getSchema())) {
continue;
}
if (excludeTable(table)) {
continue;
}
}
}
add(right.getCreateSQL(), false);
}
for (Comment comment : db.getAllComments()) {
add(comment.getCreateSQL(), false);
}
if (out != null) {
out.close();
}
} catch (IOException e) {
throw DbException.convertIOException(e, getFileName());
} finally {
closeIO();
}
result.done();
LocalResult r = result;
reset();
return r;
}
private int generateInsertValues(int count, Table table) throws IOException {
PlanItem plan = table.getBestPlanItem(session, null, null, -1, null, null);
Index index = plan.getIndex();
Cursor cursor = index.find(session, null, null);
Column[] columns = table.getColumns();
StringBuilder builder = new StringBuilder("INSERT INTO ");
table.getSQL(builder, true);
if (withColumns) {
builder.append('(');
Column.writeColumns(builder, columns, true);
builder.append(')');
}
builder.append(" VALUES");
if (!simple) {
builder.append('\n');
}
builder.append('(');
String ins = builder.toString();
builder = null;
while (cursor.next()) {
Row row = cursor.get();
if (builder == null) {
builder = new StringBuilder(ins);
} else {
builder.append(",\n(");
}
for (int j = 0; j < row.getColumnCount(); j++) {
if (j > 0) {
builder.append(", ");
}
Value v = row.getValue(j);
if (v.getType().getPrecision() > lobBlockSize) {
int id;
if (v.getValueType() == Value.CLOB) {
id = writeLobStream(v);
builder.append("SYSTEM_COMBINE_CLOB(").append(id).append(')');
} else if (v.getValueType() == Value.BLOB) {
id = writeLobStream(v);
builder.append("SYSTEM_COMBINE_BLOB(").append(id).append(')');
} else {
v.getSQL(builder);
}
} else {
v.getSQL(builder);
}
}
builder.append(')');
count++;
if ((count & 127) == 0) {
checkCanceled();
}
if (simple || builder.length() > Constants.IO_BUFFER_SIZE) {
add(builder.toString(), true);
builder = null;
}
}
if (builder != null) {
add(builder.toString(), true);
}
return count;
}
private int writeLobStream(Value v) throws IOException {
if (!tempLobTableCreated) {
add("CREATE TABLE IF NOT EXISTS SYSTEM_LOB_STREAM" +
"(ID INT NOT NULL, PART INT NOT NULL, " +
"CDATA VARCHAR, BDATA BINARY)",
true);
add("CREATE PRIMARY KEY SYSTEM_LOB_STREAM_PRIMARY_KEY " +
"ON SYSTEM_LOB_STREAM(ID, PART)", true);
add("CREATE ALIAS IF NOT EXISTS " + "SYSTEM_COMBINE_CLOB FOR \"" +
this.getClass().getName() + ".combineClob\"", true);
add("CREATE ALIAS IF NOT EXISTS " + "SYSTEM_COMBINE_BLOB FOR \"" +
this.getClass().getName() + ".combineBlob\"", true);
tempLobTableCreated = true;
}
int id = nextLobId++;
switch (v.getValueType()) {
case Value.BLOB: {
byte[] bytes = new byte[lobBlockSize];
try (InputStream input = v.getInputStream()) {
for (int i = 0;; i++) {
StringBuilder buff = new StringBuilder(lobBlockSize * 2);
buff.append("INSERT INTO SYSTEM_LOB_STREAM VALUES(").append(id)
.append(", ").append(i).append(", NULL, '");
int len = IOUtils.readFully(input, bytes, lobBlockSize);
if (len <= 0) {
break;
}
StringUtils.convertBytesToHex(buff, bytes, len).append("')");
String sql = buff.toString();
add(sql, true);
}
}
break;
}
case Value.CLOB: {
char[] chars = new char[lobBlockSize];
try (Reader reader = v.getReader()) {
for (int i = 0;; i++) {
StringBuilder buff = new StringBuilder(lobBlockSize * 2);
buff.append("INSERT INTO SYSTEM_LOB_STREAM VALUES(").append(id).append(", ").append(i)
.append(", ");
int len = IOUtils.readFully(reader, chars, lobBlockSize);
if (len == 0) {
break;
}
StringUtils.quoteStringSQL(buff, new String(chars, 0, len)).
append(", NULL)");
String sql = buff.toString();
add(sql, true);
}
}
break;
}
default:
DbException.throwInternalError("type:" + v.getValueType());
}
return id;
}
public static InputStream combineBlob(Connection conn, int id)
throws SQLException {
if (id < 0) {
return null;
}
final ResultSet rs = getLobStream(conn, "BDATA", id);
return new InputStream() {
private InputStream current;
private boolean closed;
@Override
public int read() throws IOException {
while (true) {
try {
if (current == null) {
if (closed) {
return -1;
}
if (!rs.next()) {
close();
return -1;
}
current = rs.getBinaryStream(1);
current = new BufferedInputStream(current);
}
int x = current.read();
if (x >= 0) {
return x;
}
current = null;
} catch (SQLException e) {
throw DbException.convertToIOException(e);
}
}
}
@Override
public void close() throws IOException {
if (closed) {
return;
}
closed = true;
try {
rs.close();
} catch (SQLException e) {
throw DbException.convertToIOException(e);
}
}
};
}
public static Reader combineClob(Connection conn, int id) throws SQLException {
if (id < 0) {
return null;
}
final ResultSet rs = getLobStream(conn, "CDATA", id);
return new Reader() {
private Reader current;
private boolean closed;
@Override
public int read() throws IOException {
while (true) {
try {
if (current == null) {
if (closed) {
return -1;
}
if (!rs.next()) {
close();
return -1;
}
current = rs.getCharacterStream(1);
current = new BufferedReader(current);
}
int x = current.read();
if (x >= 0) {
return x;
}
current = null;
} catch (SQLException e) {
throw DbException.convertToIOException(e);
}
}
}
@Override
public void close() throws IOException {
if (closed) {
return;
}
closed = true;
try {
rs.close();
} catch (SQLException e) {
throw DbException.convertToIOException(e);
}
}
@Override
public int read(char[] buffer, int off, int len) throws IOException {
if (len == 0) {
return 0;
}
int c = read();
if (c == -1) {
return -1;
}
buffer[off] = (char) c;
int i = 1;
for (; i < len; i++) {
c = read();
if (c == -1) {
break;
}
buffer[off + i] = (char) c;
}
return i;
}
};
}
private static ResultSet getLobStream(Connection conn, String column, int id)
throws SQLException {
PreparedStatement prep = conn.prepareStatement("SELECT " + column +
" FROM SYSTEM_LOB_STREAM WHERE ID=? ORDER BY PART");
prep.setInt(1, id);
return prep.executeQuery();
}
private void reset() {
result = null;
buffer = null;
lineSeparatorString = SysProperties.LINE_SEPARATOR;
lineSeparator = lineSeparatorString.getBytes(charset);
}
private boolean excludeSchema(Schema schema) {
if (schemaNames != null && !schemaNames.contains(schema.getName())) {
return true;
}
if (tables != null) {
for (Table table : schema.getAllTablesAndViews()) {
if (tables.contains(table)) {
return false;
}
}
return true;
}
return false;
}
private boolean excludeTable(Table table) {
return tables != null && !tables.contains(table);
}
private void add(String s, boolean insert) throws IOException {
if (s == null) {
return;
}
if (lineSeparator.length > 1 || lineSeparator[0] != '\n') {
s = StringUtils.replaceAll(s, "\n", lineSeparatorString);
}
s += ";";
if (out != null) {
byte[] buff = s.getBytes(charset);
int len = MathUtils.roundUpInt(buff.length +
lineSeparator.length, Constants.FILE_BLOCK_SIZE);
buffer = Utils.copy(buff, buffer);
if (len > buffer.length) {
buffer = new byte[len];
}
System.arraycopy(buff, 0, buffer, 0, buff.length);
for (int i = buff.length; i < len - lineSeparator.length; i++) {
buffer[i] = ' ';
}
for (int j = 0, i = len - lineSeparator.length; i < len; i++, j++) {
buffer[i] = lineSeparator[j];
}
out.write(buffer, 0, len);
if (!insert) {
Value[] row = { ValueString.get(s) };
result.addRow(row);
}
} else {
Value[] row = { ValueString.get(s) };
result.addRow(row);
}
}
public void setSimple(boolean simple) {
this.simple = simple;
}
public void setWithColumns(boolean withColumns) {
this.withColumns = withColumns;
}
public void setCharset(Charset charset) {
this.charset = charset;
}
@Override
public int getType() {
return CommandInterface.SCRIPT;
}
}
ScriptCommand
charset: Charset
