/** Deletes record with id 1000 from the Country resource. */
private static void doDeleteWithPk() throws SqlResourceException {
System.out.println("Delete with PK");
// Get the resource object
final SqlResource sqlResource = Factory.getSqlResource("Country");
// Create the delete request
final List<NameValuePair> resIds = new ArrayList<NameValuePair>(1);
final List<NameValuePair> params = null;
resIds.add(new NameValuePair("country_id", "1000", Operator.Equals));
final List<List<NameValuePair>> childrenParams = null;
final RequestLogger requestLogger = Factory.getRequestLogger();
final Request request =
Factory.getRequest(
Request.Type.DELETE,
sqlResource.getName(),
resIds,
params,
childrenParams,
requestLogger);
// Execute the delete request
final int rowsAffected = sqlResource.write(request);
System.out.println("\t" + requestLogger.getSql());
System.out.println("\tdeleted " + rowsAffected + " row(s)\n");
}
@Test
public void testGetTables_SingleTableAliased() throws SqlResourceException {
final SqlResource sqlResource = Factory.getSqlResource("SingleTableAliased");
assertEquals(1, sqlResource.getTables().size());
final TableMetaData table = sqlResource.getTables().get(getQualifiedTableName("film"));
assertNotNull(table);
assertEquals("sakila", table.getDatabaseName());
assertEquals("film", table.getTableName());
assertEquals("movie", table.getTableAlias());
// Pks
assertEquals(1, table.getPrimaryKeys().size());
assertEquals("film_id", table.getPrimaryKeys().get(0).getColumnName());
// Columns
assertEquals(3, table.getColumns().size());
AssertionHelper.assertColumnMetaData(
table.getColumns(), 1, true, "sakila", "film", "film_id", "id", Types.SMALLINT);
AssertionHelper.assertColumnMetaData(
table.getColumns(), 2, false, "sakila", "film", "title", "title", Types.VARCHAR);
AssertionHelper.assertColumnMetaData(
table.getColumns(),
3,
false,
"sakila",
"film",
"release_year",
"year",
(getDatabaseType() == DatabaseType.PostgreSql) ? Types.INTEGER : Types.DATE);
}
/** Updates record with country name Wohoo! in the Country resource. */
private static void doUpdateWithParam() throws SqlResourceException {
System.out.println("Update with Param");
// Get the resource object
final SqlResource sqlResource = Factory.getSqlResource("Country");
// Create the record
final List<NameValuePair> params = new ArrayList<NameValuePair>(3);
// Create the delete request
final List<NameValuePair> resIds = new ArrayList<NameValuePair>(1);
resIds.add(new NameValuePair("country", "Test2"));
params.add(new NameValuePair("country", "Test1"));
params.add(new NameValuePair("last_update", "2012-02-18 10:00:10"));
final List<List<NameValuePair>> childrenParams = null;
final RequestLogger requestLogger = Factory.getRequestLogger();
final Request request =
Factory.getRequest(
Request.Type.UPDATE,
sqlResource.getName(),
resIds,
params,
childrenParams,
requestLogger);
// Execute the update request
final int rowsAffected = sqlResource.write(request);
System.out.println("\t" + requestLogger.getSql());
System.out.println("\tupdated " + rowsAffected + " row(s)\n");
}
@Test
public void testGetTables_SingleTable() throws SqlResourceException {
final SqlResource sqlResource = Factory.getSqlResource("SingleTable");
assertEquals(1, sqlResource.getTables().size());
final TableMetaData table = sqlResource.getTables().get(getQualifiedTableName("actor"));
assertNotNull(table);
assertEquals("sakila", table.getDatabaseName());
assertEquals("actor", table.getTableName());
// Pks
assertEquals(1, table.getPrimaryKeys().size());
AssertionHelper.assertColumnMetaData(
table.getPrimaryKeys().get(0),
1,
true,
"sakila",
"actor",
"actor_id",
"id",
Types.SMALLINT);
assertEquals(3, table.getColumns().size());
AssertionHelper.assertColumnMetaData(
table.getColumns(), 1, true, "sakila", "actor", "actor_id", "id", Types.SMALLINT);
AssertionHelper.assertColumnMetaData(
table.getColumns(), 2, false, "sakila", "actor", "first_name", "first_name", Types.VARCHAR);
AssertionHelper.assertColumnMetaData(
table.getColumns(), 3, false, "sakila", "actor", "last_name", "surname", Types.VARCHAR);
}
/** One-level recursive method to serialize hierarchical results. */
private void serializeReadRowsHierarchical(
final SqlResource sqlResource,
final List<Map<String, Object>> rows,
final StringBuilder body,
final int level) {
String tableAlias = sqlResource.getMetaData().getParent().getRowAlias();
if (level == 2) {
tableAlias = sqlResource.getMetaData().getChild().getRowAlias();
}
for (final Map<String, Object> row : rows) {
boolean hasChildren = false;
// Start element
if (level == 1) {
body.append("\n\t<");
} else {
body.append("\n\t\t<");
}
body.append(tableAlias);
// Do attribute columns
for (final String columnLabel : row.keySet()) {
final Object value = row.get(columnLabel);
if (!(value instanceof List<?>)) {
addAttribute(body, columnLabel, value);
}
}
// Do embedded child object columns
for (final String columnLabel : row.keySet()) {
final Object value = row.get(columnLabel);
if (value instanceof List<?> && ((List<?>) value).size() > 0) {
hasChildren = true;
body.append(">");
@SuppressWarnings("unchecked")
final List<Map<String, Object>> childRows = (List<Map<String, Object>>) value;
serializeReadRowsHierarchical(sqlResource, childRows, body, 2);
}
}
// Close element
if (level == 1) {
if (hasChildren) {
// Enclose the children with the parent end element
body.append("\n\t</");
body.append(tableAlias);
body.append(">");
} else {
// Close the parent
body.append(" />");
}
} else { // level == 2
// Close the child
body.append(" />");
}
}
}
/** One-level recursive method to serialize hierarchical results. */
private void serializeWriteRows(
final SqlResource sqlResource,
final List<Set<ResponseValue>> rows,
final StringBuilder body,
final int level) {
String tableAlias = sqlResource.getMetaData().getParent().getRowAlias();
if (level == 2) {
tableAlias = sqlResource.getMetaData().getChild().getRowAlias();
}
for (final Set<ResponseValue> row : rows) {
boolean hasChildren = false;
// Start element
if (level == 1) {
body.append("\n\t<");
} else {
body.append("\n\t\t<");
}
body.append(tableAlias);
// Do attribute columns
for (final ResponseValue value : row) {
if (!(value.getValue() instanceof List<?>)) {
addAttribute(body, value.getName(), value.getValue());
}
}
// Do embedded child object columns
for (final ResponseValue value : row) {
if (value.getValue() instanceof List<?> && ((List<?>) value.getValue()).size() > 0) {
hasChildren = true;
body.append(">"); // cap the parent element
@SuppressWarnings("unchecked")
final List<Set<ResponseValue>> childRows = (List<Set<ResponseValue>>) value.getValue();
serializeWriteRows(sqlResource, childRows, body, 2);
}
}
// Close element
if (level == 1) {
if (hasChildren) {
// Enclose the children with the parent end element
body.append("\n\t</");
body.append(tableAlias);
body.append(">");
} else {
// Close the parent
body.append(" />");
}
} else { // level == 2
// Close the child
body.append(" />");
}
}
}
/**
* Converts flat select results to an XML document.
*
* @param sqlResource SQL resource
* @param resultSet results
* @return XML doc
*/
@Override
public String serializeReadFlat(final SqlResource sqlResource, final ResultSet resultSet)
throws SQLException {
StringBuilder body = null;
while (resultSet.next()) {
if (body == null) {
body = new StringBuilder(1000);
}
body.append("\n\t<");
body.append(sqlResource.getMetaData().getParent().getRowAlias());
for (final ColumnMetaData column : sqlResource.getMetaData().getAllReadColumns()) {
if (!column.isNonqueriedForeignKey()) {
addAttribute(body, column.getColumnLabel(), column.getResultByNumber(resultSet));
}
}
body.append(" />");
}
return completeDoc(DocType.Read, null, body);
}
@Test
public void testGetTables_SingleTableSub() throws SqlResourceException {
SqlResource sqlResource = Factory.getSqlResource("sub.SingleTable");
assertEquals(1, sqlResource.getTables().size());
TableMetaData table = sqlResource.getTables().get(getQualifiedTableName("film"));
assertNotNull(table);
assertEquals("sakila", table.getDatabaseName());
assertEquals("film", table.getTableName());
assertEquals("pelicula", table.getTableAlias());
sqlResource = Factory.getSqlResource("sub.sub.SingleTable");
assertEquals(1, sqlResource.getTables().size());
table = sqlResource.getTables().get(getQualifiedTableName("language"));
assertNotNull(table);
assertEquals("sakila", table.getDatabaseName());
assertEquals("language", table.getTableName());
assertEquals("language", table.getTableAlias());
}
@Test
public void testGetTables_MultiPK() throws SqlResourceException {
final SqlResource sqlResource = Factory.getSqlResource("SingleTable_MultiPK");
assertEquals(1, sqlResource.getTables().size());
final TableMetaData table = sqlResource.getTables().get(getQualifiedTableName("film_actor"));
assertNotNull(table);
assertEquals("sakila", table.getDatabaseName());
assertEquals("film_actor", table.getTableName());
// Pks
assertEquals(2, table.getPrimaryKeys().size());
assertEquals("actor_id", table.getPrimaryKeys().get(0).getColumnName());
assertEquals("film_id", table.getPrimaryKeys().get(1).getColumnName());
// Columns
assertEquals(2, table.getColumns().size());
AssertionHelper.assertColumnMetaData(
table.getColumns(), 1, true, "sakila", "film_actor", "actor_id", "actorId", Types.SMALLINT);
AssertionHelper.assertColumnMetaData(
table.getColumns(), 2, true, "sakila", "film_actor", "film_id", "film_id", Types.SMALLINT);
}
/** Creates record in the Country resource. */
private static void doInsert(final List<NameValuePair> params) throws SqlResourceException {
// Get the resource object
final SqlResource sqlResource = Factory.getSqlResource("Country");
// Create the request
final List<NameValuePair> resIds = null;
final List<List<NameValuePair>> childrenParams = null;
final RequestLogger requestLogger = Factory.getRequestLogger();
final Request request =
Factory.getRequest(
Request.Type.INSERT,
sqlResource.getName(),
resIds,
params,
childrenParams,
requestLogger);
// Execute the insert request
final int rowsAffected = sqlResource.write(request);
System.out.println("\t" + requestLogger.getSql());
System.out.println("\tinserted " + rowsAffected + " row(s)");
}
/** Deletes records with country name like W% from the Country resource. */
private static void doDeleteWithWildcard() throws SqlResourceException {
System.out.println("Delete with wildcard");
// Get the resource object
final SqlResource sqlResource = Factory.getSqlResource("Country");
// Create the record
final List<NameValuePair> params = new ArrayList<NameValuePair>(3);
params.add(new NameValuePair("country_id", "1000"));
params.add(new NameValuePair("country", "Test1"));
doInsert(params);
params.clear();
params.add(new NameValuePair("country_id", "1001"));
params.add(new NameValuePair("country", "Test2"));
doInsert(params);
// Create the delete request
final List<NameValuePair> resIds = null;
params.clear();
params.add(new NameValuePair("country", "Test%"));
final List<List<NameValuePair>> childrenParams = null;
final RequestLogger requestLogger = Factory.getRequestLogger();
final Request request =
Factory.getRequest(
Request.Type.DELETE,
sqlResource.getName(),
resIds,
params,
childrenParams,
requestLogger);
// Execute the delete request
final int rowsAffected = sqlResource.write(request);
System.out.println("\t" + requestLogger.getSql());
System.out.println("\tdeleted " + rowsAffected + " row(s)\n");
}
@Test
public void testGetTables_HierManyToManyExt() throws SqlResourceException {
final SqlResource sqlResource = Factory.getSqlResource("HierManyToManyExt");
SqlResourceMetaData metaData = ((SqlResourceImpl) sqlResource).getMetaData();
assertTrue(sqlResource.isHierarchical());
assertEquals(5, sqlResource.getTables().size());
// Parent table
TableMetaData table = sqlResource.getParentTable();
assertNotNull(table);
assertNotNull(sqlResource.getTables().get(getQualifiedTableName("actor")));
assertEquals(getQualifiedTableName("actor"), table.getQualifiedTableName());
assertEquals(TableRole.Parent, table.getTableRole());
// Parent primary keys
assertEquals(1, table.getPrimaryKeys().size());
AssertionHelper.assertColumnMetaData(
table.getPrimaryKeys().get(0),
1,
true,
"sakila",
"actor",
"actor_id",
"actor_id",
Types.SMALLINT);
// Parent columns
assertEquals(3, table.getColumns().size());
AssertionHelper.assertColumnMetaData(
table.getColumns(), 1, true, "sakila", "actor", "actor_id", "actor_id", Types.SMALLINT);
AssertionHelper.assertColumnMetaData(
table.getColumns(), 2, false, "sakila", "actor", "first_name", "first_name", Types.VARCHAR);
AssertionHelper.assertColumnMetaData(
table.getColumns(), 3, false, "sakila", "actor", "last_name", "last_name", Types.VARCHAR);
// Parent extension
table = sqlResource.getTables().get(getQualifiedTableName("actor_genre"));
assertNotNull(table);
assertEquals(TableRole.ParentExtension, table.getTableRole());
// Parent extension primary keys
assertEquals(0, table.getPrimaryKeys().size());
// Parent extension columns
assertEquals(3, table.getColumns().size());
AssertionHelper.assertColumnMetaData(
table.getColumns(),
4,
false,
"sakila",
"actor_genre",
"actor_genre_id",
"actor_genre_id",
Types.SMALLINT);
AssertionHelper.assertColumnMetaData(
table.getColumns(),
0,
false,
"sakila",
"actor_genre",
"actor_id",
"actor_id",
Types.SMALLINT);
AssertionHelper.assertColumnMetaData(
table.getColumns(), 5, false, "sakila", "actor_genre", "name", "name", Types.VARCHAR);
// Child table
table = sqlResource.getChildTable();
assertNotNull(table);
assertNotNull(sqlResource.getTables().get(getQualifiedTableName("film")));
assertEquals(getQualifiedTableName("film"), table.getQualifiedTableName());
assertEquals(TableRole.Child, table.getTableRole());
// Child primary keys
AssertionHelper.assertColumnMetaData(
table.getPrimaryKeys().get(0),
6,
true,
"sakila",
"film",
"film_id",
"film_id",
Types.SMALLINT);
// Child columns
assertEquals(3, table.getColumns().size());
AssertionHelper.assertColumnMetaData(
table.getColumns(), 6, true, "sakila", "film", "film_id", "film_id", Types.SMALLINT);
AssertionHelper.assertColumnMetaData(
table.getColumns(), 7, false, "sakila", "film", "title", "title", Types.VARCHAR);
AssertionHelper.assertColumnMetaData(
table.getColumns(),
8,
false,
"sakila",
"film",
"release_year",
"year",
(getDatabaseType() == DatabaseType.PostgreSql) ? Types.INTEGER : Types.DATE);
// Child extension
table = sqlResource.getTables().get(getQualifiedTableName("film_rating"));
assertNotNull(table);
assertEquals(TableRole.ChildExtension, table.getTableRole());
// Child extension primary keys
assertEquals(1, table.getPrimaryKeys().size());
AssertionHelper.assertColumnMetaData(
table.getColumns(),
9,
true,
"sakila",
"film_rating",
"film_rating_id",
"film_rating_id",
Types.SMALLINT);
// Child extension columns
assertEquals(3, table.getColumns().size());
AssertionHelper.assertColumnMetaData(
table.getColumns(),
9,
true,
"sakila",
"film_rating",
"film_rating_id",
"film_rating_id",
Types.SMALLINT);
AssertionHelper.assertColumnMetaData(
table.getColumns(),
0,
false,
"sakila",
"film_rating",
"film_id",
"film_id",
Types.SMALLINT);
AssertionHelper.assertColumnMetaData(
table.getColumns(), 10, false, "sakila", "film_rating", "stars", "stars", Types.SMALLINT);
// Join table
table = metaData.getJoin();
assertNotNull(table);
assertNotNull(sqlResource.getTables().get(getQualifiedTableName("film_actor")));
assertEquals(getQualifiedTableName("film_actor"), table.getQualifiedTableName());
assertEquals(TableRole.Join, table.getTableRole());
assertEquals(3, table.getColumns().size());
AssertionHelper.assertColumnMetaData(
table.getColumns(), 0, false, "sakila", "film_actor", "film_id", "film_id", Types.SMALLINT);
AssertionHelper.assertColumnMetaData(
table.getColumns(),
0,
false,
"sakila",
"film_actor",
"actor_id",
"actor_id",
Types.SMALLINT);
AssertionHelper.assertColumnMetaData(
table.getColumns(),
0,
false,
"sakila",
"film_actor",
"last_update",
"last_update",
(getDatabaseType() == DatabaseType.PostgreSql) ? Types.NULL : Types.TIMESTAMP);
// Special read column lists
assertEquals(10, metaData.getAllReadColumns().size());
assertEquals("actor_id", metaData.getAllReadColumns().get(0).getColumnLabel());
assertEquals("first_name", metaData.getAllReadColumns().get(1).getColumnLabel());
assertEquals("last_name", metaData.getAllReadColumns().get(2).getColumnLabel());
assertEquals("actor_genre_id", metaData.getAllReadColumns().get(3).getColumnLabel());
assertEquals("name", metaData.getAllReadColumns().get(4).getColumnLabel());
assertEquals("film_id", metaData.getAllReadColumns().get(5).getColumnLabel());
assertEquals("title", metaData.getAllReadColumns().get(6).getColumnLabel());
assertEquals("year", metaData.getAllReadColumns().get(7).getColumnLabel());
assertEquals("film_rating_id", metaData.getAllReadColumns().get(8).getColumnLabel());
assertEquals("stars", metaData.getAllReadColumns().get(9).getColumnLabel());
assertEquals(5, metaData.getParentReadColumns().size());
assertEquals("actor_id", metaData.getParentReadColumns().get(0).getColumnLabel());
assertEquals("first_name", metaData.getParentReadColumns().get(1).getColumnLabel());
assertEquals("last_name", metaData.getParentReadColumns().get(2).getColumnLabel());
assertEquals("actor_genre_id", metaData.getParentReadColumns().get(3).getColumnLabel());
assertEquals("name", metaData.getParentReadColumns().get(4).getColumnLabel());
assertEquals(5, metaData.getChildReadColumns().size());
assertEquals("film_id", metaData.getChildReadColumns().get(0).getColumnLabel());
assertEquals("title", metaData.getChildReadColumns().get(1).getColumnLabel());
assertEquals("year", metaData.getChildReadColumns().get(2).getColumnLabel());
assertEquals("film_rating_id", metaData.getChildReadColumns().get(3).getColumnLabel());
assertEquals("stars", metaData.getChildReadColumns().get(4).getColumnLabel());
}
@Test
public void testGetTables_HierManyToMany() throws SqlResourceException {
final SqlResource sqlResource = Factory.getSqlResource("HierManyToMany");
assertEquals(3, sqlResource.getTables().size());
TableMetaData table = sqlResource.getTables().get(getQualifiedTableName("actor"));
assertNotNull(table);
assertEquals("sakila", table.getDatabaseName());
assertEquals("actor", table.getTableName());
// Pks
assertEquals(1, table.getPrimaryKeys().size());
assertEquals("actor_id", table.getPrimaryKeys().get(0).getColumnName());
// Columns
assertEquals(3, table.getColumns().size());
AssertionHelper.assertColumnMetaData(
table.getColumns(), 1, true, "sakila", "actor", "actor_id", "actor_id", Types.SMALLINT);
AssertionHelper.assertColumnMetaData(
table.getColumns(), 2, false, "sakila", "actor", "first_name", "first_name", Types.VARCHAR);
AssertionHelper.assertColumnMetaData(
table.getColumns(), 3, false, "sakila", "actor", "last_name", "last_name", Types.VARCHAR);
// Child table
table = sqlResource.getTables().get(getQualifiedTableName("film"));
assertNotNull(table);
assertEquals("sakila", table.getDatabaseName());
assertEquals("film", table.getTableName());
// Pks
assertEquals(1, table.getPrimaryKeys().size());
assertEquals("film_id", table.getPrimaryKeys().get(0).getColumnName());
// Columns
assertEquals(3, table.getColumns().size());
AssertionHelper.assertColumnMetaData(
table.getColumns(), 4, true, "sakila", "film", "film_id", "film_id", Types.SMALLINT);
AssertionHelper.assertColumnMetaData(
table.getColumns(), 5, false, "sakila", "film", "title", "title", Types.VARCHAR);
AssertionHelper.assertColumnMetaData(
table.getColumns(),
6,
false,
"sakila",
"film",
"release_year",
"year",
(getDatabaseType() == DatabaseType.PostgreSql) ? Types.INTEGER : Types.DATE);
// // Join table
// table = sqlResource.getTables().get(getQualifiedTableName("film_actor"));
// assertNotNull(table);
// assertEquals("sakila", table.getDatabaseName());
// assertEquals("film_actor", table.getTableName());
//
// // Pks
// assertEquals(2, table.getPrimaryKeys().size());
// assertEquals("actor_id", table.getPrimaryKeys().get(0).getColumnName());
// assertEquals("film_id", table.getPrimaryKeys().get(1).getColumnName());
//
// // Columns
// assertEquals(2, table.getColumns().size());
// AssertionHelper.assertColumnMetaData(table.getColumns(), 7, true, "sakila", "film_actor",
// "film_id",
// "film_id", Types.SMALLINT);
// AssertionHelper.assertColumnMetaData(table.getColumns(), 0, true, "sakila", "film_actor",
// "actor_id",
// "actor_id", "smallint(5) unsigned");
// assertTrue(table.getColumns().get("actor_id").isNonqueriedForeignKey());
}
@Test
public void testGetTables_FlatManyToOne() throws SqlResourceException {
final SqlResource sqlResource = Factory.getSqlResource("FlatManyToOne");
// Parent table
assertEquals(2, sqlResource.getTables().size());
TableMetaData table = sqlResource.getTables().get(getQualifiedTableName("film"));
assertNotNull(table);
assertEquals("sakila", table.getDatabaseName());
assertEquals("film", table.getTableName());
assertEquals(1, table.getPrimaryKeys().size());
// Primary keys
AssertionHelper.assertColumnMetaData(
table.getPrimaryKeys().get(0),
1,
true,
"sakila",
"film",
"film_id",
"film_id",
Types.SMALLINT);
// Columns
assertEquals(3, table.getColumns().size());
AssertionHelper.assertColumnMetaData(
table.getColumns(), 1, true, "sakila", "film", "film_id", "film_id", Types.SMALLINT);
AssertionHelper.assertColumnMetaData(
table.getColumns(), 2, false, "sakila", "film", "title", "title", Types.VARCHAR);
AssertionHelper.assertColumnMetaData(
table.getColumns(),
3,
false,
"sakila",
"film",
"release_year",
"year",
(getDatabaseType() == DatabaseType.PostgreSql) ? Types.INTEGER : Types.DATE);
// Child table
table = sqlResource.getTables().get(getQualifiedTableName("language"));
assertNotNull(table);
assertEquals("sakila", table.getDatabaseName());
assertEquals("language", table.getTableName());
// Pks
assertEquals(1, table.getPrimaryKeys().size());
AssertionHelper.assertColumnMetaData(
table.getPrimaryKeys().get(0),
4,
true,
"sakila",
"language",
"language_id",
"language_id",
Types.SMALLINT);
// Columns
assertEquals(2, table.getColumns().size());
AssertionHelper.assertColumnMetaData(
table.getColumns(),
4,
true,
"sakila",
"language",
"language_id",
"language_id",
Types.SMALLINT);
AssertionHelper.assertColumnMetaData(
table.getColumns(), 5, false, "sakila", "language", "name", "name", Types.VARCHAR);
}
