/**
* Licensed to JumpMind Inc under one or more contributor
* license agreements. See the NOTICE file distributed
* with this work for additional information regarding
* copyright ownership. JumpMind Inc licenses this file
* to you under the GNU General Public License, version 3.0 (GPLv3)
* (the "License"); you may not use this file except in compliance
* with the License.
*
* You should have received a copy of the GNU General Public License,
* version 3.0 (GPLv3) along with this library; if not, see
* <http://www.gnu.org/licenses/>.
*
* Unless required by applicable law or agreed to in writing,
* software distributed under the License is distributed on an
* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
* KIND, either express or implied. See the License for the
* specific language governing permissions and limitations
* under the License.
*/
package org.jumpmind.symmetric;
import java.io.File;
import java.sql.Types;
import java.util.List;
import java.util.Set;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.ObjectUtils;
import org.apache.commons.lang.StringUtils;
import org.jumpmind.db.model.Column;
import org.jumpmind.db.model.Database;
import org.jumpmind.db.model.Table;
import org.jumpmind.db.platform.DatabaseNamesConstants;
import org.jumpmind.db.platform.IDatabasePlatform;
import org.jumpmind.db.sql.DmlStatement;
import org.jumpmind.db.sql.DmlStatement.DmlType;
import org.jumpmind.db.sql.ISqlTemplate;
import org.jumpmind.db.sql.Row;
import org.jumpmind.db.sql.SqlException;
import org.jumpmind.symmetric.io.data.DbExport;
import org.jumpmind.symmetric.io.data.DbExport.Compatible;
import org.jumpmind.symmetric.io.data.DbExport.Format;
import org.jumpmind.symmetric.io.data.DbFill;
import org.jumpmind.symmetric.io.data.DbImport;
import org.jumpmind.symmetric.io.data.writer.ConflictException;
import org.jumpmind.symmetric.service.impl.AbstractServiceTest;
import org.jumpmind.util.FormatUtils;
import org.junit.Assert;
import org.junit.BeforeClass;
import org.junit.Test;
public class DbExportImportTest extends AbstractServiceTest {
private static final String SELECT_FROM_TEST_DB_IMPORT_1_ORDER_BY_ID = "select * from test_db_import_1 order by id";
private static final String TEST_TS_W_TZ = "test_ts_w_tz";
protected static IDatabasePlatform platform;
@BeforeClass
public static void setup() throws Exception {
File f = new File("target/rootdbs");
FileUtils.deleteDirectory(f);
f.mkdir();
AbstractServiceTest.setup();
}
@Test
public void testInsertBigIntIntoOracleIntField() {
if (getPlatform().getName().equals(DatabaseNamesConstants.ORACLE)) {
ISymmetricEngine engine = getSymmetricEngine();
IDatabasePlatform platform = engine.getDatabasePlatform();
Table table = new Table("TEST_ORACLE_INTEGER");
table.addColumn(new Column("A", false, Types.INTEGER, -1, -1));
platform.alterCaseToMatchDatabaseDefaultCase(table);
platform.createTables(true, false, table);
DbImport importer = new DbImport(platform);
importer.setFormat(DbImport.Format.CSV);
importer.importTables("\"A\"\n1149140000100490", table.getName());
Assert.assertEquals(1149140000100490l,
platform.getSqlTemplate().queryForLong("select A from TEST_ORACLE_INTEGER"));
}
}
@Test
public void exportNullTimestampToCsv() throws Exception {
ISymmetricEngine engine = getSymmetricEngine();
IDatabasePlatform platform = engine.getDatabasePlatform();
Table table = new Table("test_null_timestamp");
table.addColumn(new Column("a", false, Types.TIMESTAMP, -1, -1));
table.addColumn(new Column("b", false, Types.TIMESTAMP, -1, -1));
platform.alterCaseToMatchDatabaseDefaultCase(table);
platform.createTables(true, false, table);
platform.getSqlTemplate().update("insert into test_null_timestamp values(null, null)");
DbExport export = new DbExport(platform);
export.setNoCreateInfo(true);
export.setFormat(Format.CSV);
String csv = export.exportTables(new Table[] { table });
Assert.assertEquals("\"A\",\"B\""+System.getProperty("line.separator")+",", csv.trim().toUpperCase());
}
@Test
public void exportTableInAnotherSchemaOnH2() throws Exception {
if (getPlatform().getName().equals(DatabaseNamesConstants.H2)) {
ISymmetricEngine engine = getSymmetricEngine();
ISqlTemplate template = getPlatform().getSqlTemplate();
template.update("CREATE SCHEMA IF NOT EXISTS A");
template.update("CREATE TABLE IF NOT EXISTS A.TEST (ID INT, NOTES VARCHAR(100), PRIMARY KEY (ID))");
template.update("DELETE FROM A.TEST");
template.update("INSERT INTO A.TEST VALUES(1,'test')");
DbExport export = new DbExport(engine.getDatabasePlatform());
export.setSchema("A");
export.setFormat(Format.SQL);
export.setNoCreateInfo(false);
export.setNoData(false);
export.exportTables(new String[] { "TEST" }).toLowerCase();
// TODO validate
}
}
@Test
public void exportTestDatabaseSQL() throws Exception {
ISymmetricEngine engine = getSymmetricEngine();
Table[] tables = engine.getSymmetricDialect().readSymmetricSchemaFromXml().getTables();
DbExport export = new DbExport(engine.getDatabasePlatform());
export.setFormat(Format.SQL);
export.setNoCreateInfo(false);
export.setNoData(true);
export.setSchema(getSymmetricEngine().getSymmetricDialect().getPlatform()
.getDefaultSchema());
export.setCatalog(getSymmetricEngine().getSymmetricDialect().getPlatform()
.getDefaultCatalog());
export.setCompatible(Compatible.H2);
String output = export.exportTables(tables).toLowerCase();
Assert.assertEquals(output, 43, StringUtils.countMatches(output, "create table \"sym_"));
final int EXPECTED_VARCHAR_MAX = engine.getDatabasePlatform().getName().equals(DatabaseNamesConstants.SQLITE) ? 269 : 43;
final String EXPECTED_STRING = "varchar(" + Integer.MAX_VALUE + ")";
Assert.assertEquals("Expected " + EXPECTED_VARCHAR_MAX + " " + EXPECTED_STRING
+ " in the following output: " + output, EXPECTED_VARCHAR_MAX,
StringUtils.countMatches(output, EXPECTED_STRING));
}
@Test
public void exportThenImportXml() throws Exception {
ISymmetricEngine engine = getSymmetricEngine();
IDatabasePlatform platform = engine.getSymmetricDialect().getPlatform();
Database testTables = platform.readDatabaseFromXml("/test-dbimport.xml", true);
Table table = testTables.findTable("test_db_import_1", false);
recreateImportTable();
DbExport export = new DbExport(platform);
export.setFormat(Format.XML);
export.setNoCreateInfo(false);
export.setNoData(true);
export.setSchema(getSymmetricEngine().getSymmetricDialect().getPlatform()
.getDefaultSchema());
export.setCatalog(getSymmetricEngine().getSymmetricDialect().getPlatform()
.getDefaultCatalog());
export.exportTables(new String[] { table.getName() });
// System.out.println(output);
// TODO validate
}
@Test
public void testExportTimestampWithTimeZone() throws Exception {
if (createAndFillTimestampWithTimeZoneTable()) {
ISymmetricEngine engine = getSymmetricEngine();
DbExport export = new DbExport(engine.getDatabasePlatform());
export.setCompatible(Compatible.POSTGRES);
export.setFormat(Format.SQL);
String sql = export.exportTables(new String[] { TEST_TS_W_TZ });
final String EXPECTED_POSTGRES = "insert into \"test_ts_w_tz\"(\"id\", \"tz\") (select 1,cast('1973-06-08 07:00:00.000000 -04:00' as timestamp with time zone) where (select distinct 1 from \"test_ts_w_tz\" where \"id\" = 1) is null);";
Assert.assertTrue("Expected the following sql:\n" + sql + "\n\n to contain:\n"
+ EXPECTED_POSTGRES, sql.contains(EXPECTED_POSTGRES));
export.setCompatible(Compatible.ORACLE);
sql = export.exportTables(new String[] { TEST_TS_W_TZ });
final String EXPECTED_ORACLE = "insert into \"test_ts_w_tz\" (\"id\", \"tz\") values (1,TO_TIMESTAMP_TZ('1973-06-08 07:00:00.000000 -04:00', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'));";
Assert.assertTrue("Expected the following sql:\n" + sql + "\n\n to contain:\n"
+ EXPECTED_ORACLE, sql.contains(EXPECTED_ORACLE));
}
}
protected boolean createAndFillTimestampWithTimeZoneTable() {
ISymmetricEngine engine = getSymmetricEngine();
IDatabasePlatform platform = engine.getDatabasePlatform();
String dbName = platform.getName();
if (dbName.equals(DatabaseNamesConstants.ORACLE)
|| dbName.equals(DatabaseNamesConstants.POSTGRESQL)) {
ISqlTemplate template = engine.getSqlTemplate();
try {
template.update(String.format("drop table \"%s\"", TEST_TS_W_TZ));
} catch (Exception ex) {
}
String createSql = String
.format("create table \"%s\" (\"id\" integer, \"tz\" timestamp with time zone, primary key (\"id\"))",
TEST_TS_W_TZ);
template.update(createSql);
DmlStatement statement = platform.createDmlStatement(DmlType.INSERT,
platform.getTableFromCache(TEST_TS_W_TZ, true), null);
template.update(statement.getSql(), statement.getValueArray(new Object[] { 1,
"1973-06-08 07:00:00.000 -04:00" }, new Object[] { 1 }));
return true;
} else {
return false;
}
}
protected void recreateImportTable() {
ISymmetricEngine engine = getSymmetricEngine();
DbImport reCreateTablesImport = new DbImport(engine.getDatabasePlatform());
reCreateTablesImport.setFormat(DbImport.Format.XML);
reCreateTablesImport.setDropIfExists(true);
reCreateTablesImport.setAlterCaseToMatchDatabaseDefaultCase(true);
reCreateTablesImport.importTables(getClass().getResourceAsStream("/test-dbimport.xml"));
}
protected void assertCountDbImportTableRecords(int expected) {
ISymmetricEngine engine = getSymmetricEngine();
IDatabasePlatform platform = engine.getSymmetricDialect().getPlatform();
Database testTables = platform.readDatabaseFromXml("/test-dbimport.xml", true);
Table table = testTables.findTable("test_db_import_1", false);
Assert.assertEquals(expected, platform.getSqlTemplate().queryForInt("select count(*) from " + table.getName()));
}
@Test
public void importSqlData() throws Exception {
ISymmetricEngine engine = getSymmetricEngine();
recreateImportTable();
assertCountDbImportTableRecords(0);
DbImport importCsv = new DbImport(engine.getDatabasePlatform());
importCsv.setFormat(DbImport.Format.SQL);
importCsv.importTables(getClass().getResourceAsStream("/test-dbimport-1-good.sql"));
assertCountDbImportTableRecords(5);
recreateImportTable();
assertCountDbImportTableRecords(0);
try {
importCsv.importTables(getClass()
.getResourceAsStream("/test-dbimport-1-bad-line-2.sql"));
Assert.fail("Expected a sql exception");
} catch (SqlException ex) {
}
assertCountDbImportTableRecords(0);
importCsv.setCommitRate(1);
importCsv.setForceImport(true);
importCsv.importTables(getClass().getResourceAsStream("/test-dbimport-1-bad-line-2.sql"));
assertCountDbImportTableRecords(4);
}
@Test
public void importSymXmlData() throws Exception {
final String FILE = "/test-dbimport-1-sym_xml-1.xml";
ISymmetricEngine engine = getSymmetricEngine();
recreateImportTable();
assertCountDbImportTableRecords(0);
DbImport importCsv = new DbImport(engine.getDatabasePlatform());
importCsv.setFormat(DbImport.Format.SYM_XML);
importCsv.importTables(getClass().getResourceAsStream(FILE));
assertCountDbImportTableRecords(2);
try {
importCsv.importTables(getClass().getResourceAsStream(FILE));
Assert.fail("Expected a sql exception");
} catch (ConflictException ex) {
}
assertCountDbImportTableRecords(2);
recreateImportTable();
importCsv.setReplaceRows(true);
importCsv.importTables(getClass().getResourceAsStream(FILE));
assertCountDbImportTableRecords(2);
}
@Test
public void importXmlData() throws Exception {
final String FILE = "/test-dbimport-1-xml-1.xml";
ISymmetricEngine engine = getSymmetricEngine();
DbImport importer = new DbImport(engine.getDatabasePlatform());
importer.setFormat(DbImport.Format.XML);
importer.setDropIfExists(true);
importer.setAlterCaseToMatchDatabaseDefaultCase(true);
importer.importTables(getClass().getResourceAsStream(FILE));
assertCountDbImportTableRecords(3);
// table should be dropped so this should work again
importer.importTables(getClass().getResourceAsStream(FILE));
assertCountDbImportTableRecords(3);
}
@Test
public void exportThenImportCsv() throws Exception {
ISymmetricEngine engine = getSymmetricEngine();
IDatabasePlatform platform = engine.getSymmetricDialect().getPlatform();
Database testTables = platform.readDatabaseFromXml("/test-dbimport.xml", true);
Table table = testTables.findTable("test_db_import_1", false);
recreateImportTable();
final int RECORD_COUNT = 100;
DbFill fill = new DbFill(platform);
fill.setRecordCount(RECORD_COUNT);
fill.fillTables(table.getName());
DbExport export = new DbExport(platform);
export.setFormat(Format.CSV);
export.setNoCreateInfo(true);
export.setNoData(false);
String csvOutput = export.exportTables(new String[] { table.getName() });
logger.info(csvOutput);
ISqlTemplate sqlTemplate = platform.getSqlTemplate();
List<Row> rowsBeforeImport = sqlTemplate.query(SELECT_FROM_TEST_DB_IMPORT_1_ORDER_BY_ID);
recreateImportTable();
DbImport importCsv = new DbImport(platform);
importCsv.setFormat(DbImport.Format.CSV);
importCsv.importTables(csvOutput, table.getName());
Assert.assertEquals(RECORD_COUNT, sqlTemplate.queryForInt("select count(*) from " + table.getName()));
compareRows(table, rowsBeforeImport, sqlTemplate.query(SELECT_FROM_TEST_DB_IMPORT_1_ORDER_BY_ID));
// TODO test error
// TODO test replace
// TODO test ignore
// TODO test force
}
@Test
public void exportThenImportCsvWithBackslashes() throws Exception {
ISymmetricEngine engine = getSymmetricEngine();
IDatabasePlatform platform = engine.getSymmetricDialect().getPlatform();
Database testTables = platform.readDatabaseFromXml("/test-dbimport.xml", true);
Table table = testTables.findTable("test_db_import_1", false);
recreateImportTable();
DbImport importCsv = new DbImport(platform);
importCsv.setFormat(DbImport.Format.SQL);
importCsv.importTables(getClass().getResourceAsStream("/test-dbimport-1-backslashes.sql"));
assertCountDbImportTableRecords(1);
DbExport export = new DbExport(platform);
export.setFormat(Format.CSV);
export.setNoCreateInfo(true);
export.setNoData(false);
String csvOutput = export.exportTables(new String[] { table.getName() });
ISqlTemplate sqlTemplate = platform.getSqlTemplate();
List<Row> rowsBeforeImport = sqlTemplate.query(SELECT_FROM_TEST_DB_IMPORT_1_ORDER_BY_ID);
recreateImportTable();
importCsv.setFormat(DbImport.Format.CSV);
importCsv.importTables(csvOutput, table.getName());
compareRows(table, rowsBeforeImport, sqlTemplate.query(SELECT_FROM_TEST_DB_IMPORT_1_ORDER_BY_ID));
}
@Test
public void testExportCsvToDirectory() throws Exception {
ISymmetricEngine engine = getSymmetricEngine();
IDatabasePlatform platform = engine.getSymmetricDialect().getPlatform();
DbImport importXml = new DbImport(platform);
importXml.setFormat(DbImport.Format.XML);
importXml.importTables(getClass().getResourceAsStream("/test-dbexportimport-3-tables.xml"));
File dir = new File("target/test");
FileUtils.deleteDirectory(dir);
Assert.assertFalse(dir.exists());
DbExport exportCsv = new DbExport(platform);
exportCsv.setComments(true);
exportCsv.setFormat(Format.CSV);
exportCsv.setDir(dir.getAbsolutePath());
exportCsv.exportTables(new String[] { "a", "b", "c" });
Assert.assertTrue(dir.exists());
Assert.assertTrue(dir.isDirectory());
File a = new File(dir, platform.getTableFromCache("a", false).getName() + ".csv");
Assert.assertTrue(a.exists());
Assert.assertTrue(a.isFile());
List<String> lines = FileUtils.readLines(a);
Assert.assertEquals(9, lines.size());
Assert.assertEquals("\"id\",\"string_value\"", lines.get(5));
Assert.assertEquals("\"1\",\"This is a test of a\"", lines.get(6));
Assert.assertEquals("\"2\",\"This is a test of a\"", lines.get(7));
File b = new File(dir, platform.getTableFromCache("b", false).getName() + ".csv");
Assert.assertTrue(b.exists());
Assert.assertTrue(b.isFile());
lines = FileUtils.readLines(b);
Assert.assertEquals(10, lines.size());
Assert.assertEquals("\"id\",\"string_value\"", lines.get(5));
Assert.assertEquals("\"1\",\"This is a test of b\"", lines.get(6));
Assert.assertEquals("\"2\",\"This is a test of b\"", lines.get(7));
Assert.assertEquals("\"3\",\"This is line 3 of b\"", lines.get(8));
File c = new File(dir, platform.getTableFromCache("c", false).getName() + ".csv");
Assert.assertTrue(c.exists());
Assert.assertTrue(c.isFile());
lines = FileUtils.readLines(c);
Assert.assertEquals(9, lines.size());
Assert.assertEquals("\"id\",\"string_value\"", lines.get(5));
Assert.assertEquals("\"1\",\"This is a test of c\"", lines.get(6));
Assert.assertEquals("\"2\",\"This is a test of c\"", lines.get(7));
}
protected void compareRows(Table table, List<Row> one, List<Row> two) {
if (one.size() != two.size()) {
Assert.fail("First list had " + one.size() + " and second list had " + two.size());
}
for (int i = 0; i < one.size(); i++) {
Row rOne = one.get(i);
Row rTwo = two.get(i);
Set<String> keys = rOne.keySet();
for (String key : keys) {
Object oOne = rOne.get(key);
Object oTwo = rTwo.get(key);
Column column = table.getColumnWithName(key);
/*
* special comparison for sqlite. the result reports all types
* as text even though the jdbc table metadata reports the types
* as dates
*/
if (column != null && (column.getMappedType().equals("DATE") || column.getMappedType().equals("TIME") || column.getMappedType().equals("TIMESTAMP"))
&& oOne instanceof String && oTwo instanceof String) {
oOne = FormatUtils.parseDate(oOne.toString(), FormatUtils.TIMESTAMP_PATTERNS);
oTwo = FormatUtils.parseDate(oTwo.toString(), FormatUtils.TIMESTAMP_PATTERNS);
}
if (!ObjectUtils.equals(oOne, oTwo)) {
Assert.fail("The " + i + " element was not the same. The column " + key
+ " had a value of " + rOne.get(key) + " for one row and "
+ rTwo.get(key) + " for the other");
}
}
}
}
protected Row findInList(List<Row> rows, String pk, Object pkValue) {
for (Row row : rows) {
Object value = row.get(pk);
if (ObjectUtils.equals(value, pkValue)) {
return row;
}
}
return null;
}
}