En estos últimos días estuve sufriendo con una base de datos desconocida para mi, su nombre HyperSQL o también llamada HSQLDB. La base de datos funciona bien, sin embargo no cuenta con una interfaz gráfica muy potente, además de que mis conocimientos sobre esta tecnología son bastante limitados.
Por diversas causas adicionales a las anteriores, me vi en la necesidad de migrar esta base de datos a MySQL. Cuando lo hube conseguido pensé que quizá haya otros quienes tengan esta misma necesidad y ese es el motivo de mi publicación.
 |
| Interfaz gráfica HyperSQL |
Requisitos
1. HyperSQL funcionando
2. Editor de código Java. En mi caso "Eclipse IDE for Java Developers".
3. Agregar la referencia al archivo "hsqldb.jar"
Nota Importante: Este código funciona únicamente con los siguientes tipos de datos HSQLDB:
NUMERIC
CHARACTER VARYING
BIGINT
TIMESTAMP
BOOLEAN
SMALLINT
INTEGER
CHARACTER LARGE OBJECT
BINARY LARGE OBJECT
Sin mas que decir, les dejo el código Java:
/*
* Developer: tyrodeveloper
*
*/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.io.FileWriter;
public class Scripts {
public static void main(String args[]){
try{
System.out.printf("Procesando...\n");
DateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
String currentSchema = "";
String fileName = dateFormat.format(new Date());
FileWriter writer = new FileWriter("D:\\script-"+ fileName +".sql", true);
Connection con = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost:9001/sinba", "SA", "");
Statement stmt = con.createStatement();
//Script schemas
String sql = "SELECT TABLE_SCHEMA, COUNT(*) AS TOTAL_TABLAS "
+ "FROM INFORMATION_SCHEMA.TABLES "
+ "WHERE TABLE_TYPE = 'BASE TABLE' "
+ "GROUP BY TABLE_SCHEMA "
+ "ORDER BY TABLE_SCHEMA";
ResultSet rs = stmt.executeQuery(sql);
writer.append("/* CREAR ESQUEMAS */\r\n");
while (rs.next()) {
String table_schema = rs.getString("TABLE_SCHEMA");
int total_tablas = rs.getInt("TOTAL_TABLAS");
writer.append("CREATE SCHEMA IF NOT EXISTS `"+ table_schema + "`;/* TABLAS: "+ total_tablas +" */\r\n");
}
rs.close();
//Script tables
sql = "SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE "
+ "FROM INFORMATION_SCHEMA.TABLES "
+ "WHERE TABLE_TYPE = 'BASE TABLE' "
+ "ORDER BY TABLE_SCHEMA, TABLE_NAME";
rs = stmt.executeQuery(sql);
writer.append("/* CREAR TABLAS */\r\n");
while (rs.next()) {
String table_schema = rs.getString("TABLE_SCHEMA");
String table_name = rs.getString("TABLE_NAME");
if(currentSchema!=table_schema)
{
writer.append("USE `"+ table_schema + "`;\r\n");
}
writer.append(ScriptTable(table_schema , table_name) + "\r\n");
currentSchema = rs.getString("TABLE_SCHEMA");
}
rs.close();
//Script Primary keys
sql = "SELECT ' ALTER TABLE `' + TABLE_SCHEM + '`.`' + TABLE_NAME + '` ADD CONSTRAINT `' + PK_NAME + '` PRIMARY KEY (`' + COLUMN_NAME + '`);' AS SQL_TEXT "
+ "FROM INFORMATION_SCHEMA.SYSTEM_PRIMARYKEYS "
+ "WHERE KEY_SEQ = 1 "
+ "ORDER BY TABLE_SCHEM ";
rs = stmt.executeQuery(sql);
writer.append("/* CREAR PRIMARY KEYS */\r\n");
while (rs.next()) {
String sql_text = rs.getString("SQL_TEXT");
writer.append(sql_text+ "\r\n");
}
rs.close();
//Script foreign keys
sql = "SELECT 'ALTER TABLE `' + FKTABLE_SCHEM + '`.`' + FKTABLE_NAME + '` ADD CONSTRAINT `' + FK_NAME + '` FOREIGN KEY (`' + FKCOLUMN_NAME + '`) REFERENCES `' + PKTABLE_SCHEM + '`.`' + PKTABLE_NAME + '`(`' + PKCOLUMN_NAME + '`);' AS SQL_TEXT "
+ "FROM INFORMATION_SCHEMA.SYSTEM_CROSSREFERENCE "
+ "WHERE KEY_SEQ = 1 "
+ "ORDER BY FKTABLE_SCHEM ";
rs = stmt.executeQuery(sql);
writer.append("/* CREAR FOREIGN KEYS */\r\n");
while (rs.next()) {
String sql_text = rs.getString("SQL_TEXT");
writer.append(sql_text+ "\r\n");
}
rs.close();
//Close connection
con.close();
writer.close();
System.out.printf("Terminado.");
}
catch (Exception ex) {
System.out.printf("Error: %s", ex.getMessage());
}
}
public static String ScriptTable(String schemaName,String tableName ){
try{
StringBuilder sbTable = new StringBuilder();
Connection con = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost:9001/sinba", "SA", "");
Statement stmt = con.createStatement();
String sql ="SELECT COLUMN_NAME,DTD_IDENTIFIER, IS_NULLABLE,DATA_TYPE, "
+ "DECLARED_NUMERIC_PRECISION, DECLARED_NUMERIC_SCALE "
+ "FROM INFORMATION_SCHEMA.COLUMNS "
+ "WHERE TABLE_NAME='"+ tableName +"' "
+ "AND TABLE_SCHEMA ='"+ schemaName +"' ORDER BY ORDINAL_POSITION";
ResultSet rs = stmt.executeQuery(sql);
sbTable.append("CREATE TABLE "+ (schemaName == ""?"": ("`" + schemaName + "`.")) + "`" +tableName +"`\r\n(");
while (rs.next()) {
String column_name = rs.getString("COLUMN_NAME").intern();
String column_spec = "";
String data_type = rs.getString("DATA_TYPE").intern();
String dtd_identifier = rs.getString("DTD_IDENTIFIER").intern();
String is_nullable = rs.getString("IS_NULLABLE").intern();
int numeric_precision = 0;
if(rs.getString("DECLARED_NUMERIC_PRECISION")!= null)
numeric_precision= rs.getInt("DECLARED_NUMERIC_PRECISION");
int numeric_scale = 0;
if(rs.getString("DECLARED_NUMERIC_SCALE")!=null)
numeric_scale = rs.getInt("DECLARED_NUMERIC_SCALE");
switch(data_type){
case "TIMESTAMP":
column_spec ="\r\n\t`" + column_name + "` " + dtd_identifier + (is_nullable == "YES" ? " NULL" : " NOT NULL" ) + (data_type=="TIMESTAMP"?" DEFAULT CURRENT_TIMESTAMP":"") + ",";
break;
case "CHARACTER VARYING" :
column_spec ="\r\n\t`" + column_name + "` " + dtd_identifier + (is_nullable == "YES" ? " NULL" : " NOT NULL" ) + ",";
break;
case "NUMERIC":
if(numeric_precision==1){
column_spec ="\r\n\t`"+ column_name +"` NUMERIC(1)" + (is_nullable == "YES" ? " NULL" : " NOT NULL" ) + ",";
}
else{
if(numeric_scale==0){column_spec ="\r\n\t`" + column_name + "` INT"+ (is_nullable == "YES" ? " NULL" : " NOT NULL" ) + ",";}
else{column_spec ="\r\n\t`"+ column_name +"` DECIMAL("+ numeric_precision +","+ numeric_scale +")" + (is_nullable == "YES" ? " NULL" : " NOT NULL" ) + ",";}
}
break;
case "INTEGER" :
column_spec ="\r\n\t`" + column_name + "` INT" + (is_nullable == "YES" ? " NULL" : " NOT NULL" ) + ",";
break;
case "BINARY LARGE OBJECT" :
column_spec ="\r\n\t`" + column_name + "` LONGBLOB" + (is_nullable == "YES" ? " NULL" : " NOT NULL" ) + ",";
break;
case "CHARACTER LARGE OBJECT" :
column_spec ="\r\n\t`" + column_name + "` LONGBLOB" + (is_nullable == "YES" ? " NULL" : " NOT NULL" ) + ",";
break;
case "BIGINT" :
column_spec ="\r\n\t`" + column_name + "` BIGINT" + (is_nullable == "YES" ? " NULL" : " NOT NULL" ) + ",";
break;
case "BOOLEAN" :
column_spec ="\r\n\t`" + column_name + "` BIT" + (is_nullable == "YES" ? " NULL" : " NOT NULL" ) + ",";
break;
default:
column_spec ="\r\n\t`" + column_name + "` " + dtd_identifier + (is_nullable == "YES" ? " NULL" : " NOT NULL" ) + ",";
break;
}
sbTable.append(column_spec);
}
rs.close();
con.close();
sbTable.replace(sbTable.length()-1, sbTable.length(), "");
sbTable.append("\r\n);");
return sbTable.toString();
}
catch (Exception ex) {
System.out.printf("Error ScriptTable: %s", ex.getMessage());
return "/*Error to create table :"+ tableName +" */\r\n";
}
}
}
No hay comentarios:
Publicar un comentario