public static void logInit(boolean force) throws IOException {
if (!IsLogInitialized || force) {
LogManager.getLogManager()
.readConfiguration(
LibRt.class.getClassLoader().getResourceAsStream("cfg.logging.properties"));
String logFile = System.getProperty("CfgLog");
if (logFile != null) {
LogManager.getLogManager().readConfiguration(new FileInputStream(logFile));
}
try {
LogLvlMax = Integer.parseInt(System.getProperty("LogLvlMax", LogLvlMax + ""));
} catch (Exception ex) {
}
; // XXX: mostrar mensaje?
}
}
/* uses badsource and badsink */
public void bad() throws Throwable {
String data;
/* get system property user.home */
/* POTENTIAL FLAW: Read data from a system property */
data = System.getProperty("user.home");
Connection dbConnection = null;
try {
dbConnection = IO.getDBConnection();
/* POTENTIAL FLAW: Set the catalog name with the value of data
* allowing a nonexistent catalog name or unauthorized access to a portion of the DB */
dbConnection.setCatalog(data);
} catch (SQLException exceptSql) {
IO.logger.log(Level.WARNING, "Error getting database connection", exceptSql);
} finally {
try {
if (dbConnection != null) {
dbConnection.close();
}
} catch (SQLException exceptSql) {
IO.logger.log(Level.WARNING, "Error closing Connection", exceptSql);
}
}
}
private void saveWorkbook(Workbook resultWorkbook, String fileName) throws IOException {
String saveResultsProp = System.getProperty("saveResults");
if ("true".equalsIgnoreCase(saveResultsProp)) {
OutputStream os = new BufferedOutputStream(new FileOutputStream(fileName));
resultWorkbook.write(os);
os.flush();
os.close();
}
}
public static void main(String args[]) throws SQLException {
String url = "jdbc:oracle:oci8:@";
try {
String url1 = System.getProperty("JDBC_URL");
if (url1 != null) url = url1;
} catch (Exception e) {
// If there is any security exception, ignore it
// and use the default
}
// Create a OracleDataSource instance and set properties
OracleDataSource ods = new OracleDataSource();
ods.setUser("hr");
ods.setPassword("hr");
ods.setURL(url);
// Connect to the database
Connection conn = ods.getConnection();
Statement stmt = conn.createStatement();
stmt.execute("delete from departments where department_id > 2000");
// Default batch value set to 50 for all prepared statements
// belonging to this connection.
((OracleConnection) conn).setDefaultExecuteBatch(50);
PreparedStatement ps = conn.prepareStatement("insert into departments values (?, ?, ?, ?)");
ps.setInt(1, 2010);
ps.setString(2, "Import");
ps.setInt(3, 114);
ps.setInt(4, 1700);
// this execute does not actually happen at this point
System.out.println(ps.executeUpdate());
ps.setInt(1, 2020);
ps.setString(2, "Export");
ps.setInt(3, 145);
ps.setInt(4, 2500);
// this execute does not actually happen at this point
int rows = ps.executeUpdate();
System.out.println("Number of rows updated before calling sendBatch: " + rows);
// Execution of both previously batched executes will happen
// at this point. The number of rows updated will be
// returned by sendBatch.
rows = ((OraclePreparedStatement) ps).sendBatch();
System.out.println("Number of rows updated by calling sendBatch: " + rows);
ps.close();
conn.close();
}
// Fireup tomcat and register this servlet
public static void main(String[] args) throws LifecycleException, SQLException {
Tomcat tomcat = new Tomcat();
tomcat.setPort(8080);
File base = new File(System.getProperty("java.io.tmpdir"));
Context rootCtx = tomcat.addContext("/", base.getAbsolutePath());
Tomcat.addServlet(rootCtx, "log", new LogService());
rootCtx.addServletMapping("/*", "log");
tomcat.start();
tomcat.getServer().await();
}
public AnySQLDriver() {
try {
DriverManager.registerDriver(this);
} catch (SQLException sqle) {
}
String cfgfile = System.getProperty("db.config");
VariableTable vt = new VariableTable();
if (cfgfile != null) {
vt.loadFile(cfgfile);
DBPhysicalManager.loadDBConfig(vt);
DBLogicalManager.loadDBConfig(vt);
DBPhysicalManager.markupAll();
}
}
public void callData() {
workingDir = System.getProperty("user.dir");
String fileName =
workingDir
+ File.separator
+ "data"
+ File.separator
+ "log"
+ File.separator
+ "karaf.log";
WatchedDir = workingDir + File.separator + "data" + File.separator + "log";
try {
LOG.info("log enabled ...");
Thread.sleep(10000);
infile = new File(theNewestFile.toString());
LOG.info("karaf file ..." + theNewestFile);
Thread.sleep(1000);
LOG.info("parsing karaf file ...");
Thread.sleep(9000);
Scanner scanner = new Scanner(infile);
while (scanner.hasNext()) {
line = scanner.nextLine();
if (line.contains("DEBUG") && line.contains("from")) {
String phrase1 = line;
String delims = "[,]+";
String[] tokens = phrase1.split(delims);
String phrase2 = line;
String delims2 = "[|]+";
String[] tokens2 = phrase2.split(delims2);
time = tokens[0];
attempt = tokens2[5];
String phrase3 = line;
String[] parts = phrase3.split(" ");
srcIP = parts[parts.length - 1];
usecpluginAAAstore.addData(time, srcIP, attempt);
LOG.info("Information stored in datastore is" + time + " " + srcIP + " " + attempt);
}
}
PublishNotif publishNotif = new PublishNotif();
publishNotif.setdataBroker(dataBroker);
publishNotif.Notify();
} catch (Exception e) {
e.printStackTrace();
}
}
// Logs a new entry in the library RSS file
public static synchronized void addRSSEntry(
String title, String link, String description, File rssFile) {
// File rssFile=new File("nofile.xml");
try {
System.out.println("Looking for RSS file: " + rssFile.getCanonicalPath());
if (rssFile.exists()) {
SAXReader reader = new SAXReader();
Document document = reader.read(rssFile);
Element root = document.getRootElement();
Element channel = root.element("channel");
List items = channel.elements("item");
int numItems = items.size();
items = null;
if (numItems > 9) {
Element removeThisItem = channel.element("item");
channel.remove(removeThisItem);
}
Element newItem = channel.addElement("item");
Element newTitle = newItem.addElement("title");
Element newLink = newItem.addElement("link");
Element newDescription = newItem.addElement("description");
newTitle.setText(title);
newDescription.setText(description);
newLink.setText(link);
Element pubDate = channel.element("pubDate");
pubDate.setText((new java.util.Date()).toString());
// now save changes
FileWriter mywriter = new FileWriter(rssFile);
OutputFormat format = OutputFormat.createPrettyPrint();
format.setLineSeparator(System.getProperty("line.separator"));
XMLWriter writer = new XMLWriter(mywriter, format);
writer.write(document);
writer.close();
}
} catch (IOException ioe) {
System.out.println("ERROR: Could not find the RSS file.");
ioe.printStackTrace();
} catch (DocumentException de) {
System.out.println("ERROR: Could not read the RSS file.");
de.printStackTrace();
} catch (Exception e) {
System.out.println("Unknown exception trying to add an entry to the RSS file.");
e.printStackTrace();
}
}
private void configureExportDatabaseConnection() throws java.sql.SQLException {
String dbString = System.getProperty("CONFDB");
if (dbString == null
|| dbString.equals("")
|| dbString.indexOf('/') == -1
|| dbString.indexOf('@') == -1)
throw new java.sql.SQLException("No valid $CONFDB variable found : can not connect!");
String user = dbString.substring(0, dbString.indexOf('/'));
String password = dbString.substring(dbString.indexOf('/') + 1, dbString.indexOf('@'));
String url = dbString.substring(dbString.indexOf('@') + 1, dbString.length());
url = "jdbc:oracle:thin:@" + url;
c.setUser(user);
c.setUrl(url);
c.setPassword(password);
}
public static void main(String[] args) throws Exception {
System.out.println(System.getProperty("java.library.path"));
Class.forName("org.sqlite.JDBC");
Connection conn =
DriverManager.getConnection(
"jdbc:sqlite:D:\\MyProject\\java\\beaj\\src\\test\\Northwind.db");
Statement stat = conn.createStatement();
ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
// ImportProducts(stat);
ImportCategories(stat);
// ImportUser(stat);
// ImportRole(stat);
// ImportAuth(stat);ImportRoleAuth(stat);
// ImportUserRole(stat);
conn.close();
}
public static SQLException toSQLException(String msg, String sqlState, Throwable t) {
if (t instanceof SQLException) {
if (Debug.DEBUG && Debug.TRACE == Debug.TRACE_MAX && logger.isLoggable(MLevel.FINER)) {
SQLException s = (SQLException) t;
StringBuffer tmp = new StringBuffer(255);
tmp.append("Attempted to convert SQLException to SQLException. Leaving it alone.");
tmp.append(" [SQLState: ");
tmp.append(s.getSQLState());
tmp.append("; errorCode: ");
tmp.append(s.getErrorCode());
tmp.append(']');
if (msg != null) tmp.append(" Ignoring suggested message: '" + msg + "'.");
logger.log(MLevel.FINER, tmp.toString(), t);
SQLException s2 = s;
while ((s2 = s2.getNextException()) != null)
logger.log(MLevel.FINER, "Nested SQLException or SQLWarning: ", s2);
}
return (SQLException) t;
} else {
if (Debug.DEBUG) {
// t.printStackTrace();
if (logger.isLoggable(MLevel.FINE))
logger.log(MLevel.FINE, "Converting Throwable to SQLException...", t);
}
if (msg == null)
msg = "An SQLException was provoked by the following failure: " + t.toString();
if (VersionUtils.isAtLeastJavaVersion14()) {
SQLException out = new SQLException(msg);
out.initCause(t);
return out;
} else
return new SQLException(
msg
+ System.getProperty("line.separator")
+ "[Cause: "
+ ThrowableUtils.extractStackTrace(t)
+ ']',
sqlState);
}
}
public static void log(String depbugstatement) {
SimpleLayout layout = new SimpleLayout();
FileAppender appender = null;
try {
String userDir = System.getProperty("user.dir");
// FileInputStream fis = null;
if (userDir.indexOf(":") != -1) {
appender = new FileAppender(layout, "..\\server\\default\\log\\caIMAGE.log", false);
} else {
appender = new FileAppender(layout, "../log/caIMAGE.log", false);
}
} catch (Exception e) {
e.printStackTrace();
}
logger.addAppender(appender);
logger.setLevel((Level) Level.DEBUG);
logger.debug(depbugstatement);
}
public void ejecutarReporte(String codigo) {
try {
cn = Conexion.realizarConexion();
String master = System.getProperty("user.dir") + "\\src\\reportes\\reporteEmpleado.jasper";
System.out.println("master: " + master);
if (master == null) {
showMessageDialog(null, "No se encontro el archivo", "Error", 0);
System.exit(2);
}
JasperReport masterReport = null;
try {
masterReport = (JasperReport) JRLoader.loadObject(master);
} catch (JRException ex) {
showMessageDialog(null, "MasterReport:" + ex.getMessage(), "Error", 0);
System.exit(3);
}
Map parametro = new HashMap();
parametro.put("chr_emplcodigo", codigo);
JasperPrint jasperPrint = JasperFillManager.fillReport(masterReport, parametro, cn);
JasperViewer jviewer = new JasperViewer(jasperPrint, false);
jviewer.setTitle("Reporte");
jviewer.setVisible(true);
} catch (ClassNotFoundException ex) {
showMessageDialog(null, ex.getMessage(), "Error", 0);
} catch (SQLException ex) {
showMessageDialog(null, ex.getMessage(), "Error", 0);
} catch (Exception ex) {
showMessageDialog(null, ex.getMessage(), "Error", 0);
} finally {
try {
cn.close();
} catch (Exception ex) {
showMessageDialog(null, ex.getMessage(), "Error", 0);
}
}
}
private String getDatabaseStructureInfo() {
ResultSet schemaRs = null;
ResultSet catalogRs = null;
String nl = System.getProperty("line.separator");
StringBuffer sb = new StringBuffer(nl);
// Let's give the user some feedback. The exception
// is probably related to incorrect schema configuration.
sb.append("Configured schema:").append(getSchema()).append(nl);
sb.append("Configured catalog:").append(getCatalog()).append(nl);
try {
schemaRs = getMetaData().getSchemas();
sb.append("Available schemas:").append(nl);
while (schemaRs.next()) {
sb.append(" ").append(schemaRs.getString("TABLE_SCHEM")).append(nl);
}
} catch (SQLException e2) {
GLogger.warn("Couldn't get schemas", e2);
sb.append(" ?? Couldn't get schemas ??").append(nl);
} finally {
dbHelper.close(schemaRs, null);
}
try {
catalogRs = getMetaData().getCatalogs();
sb.append("Available catalogs:").append(nl);
while (catalogRs.next()) {
sb.append(" ").append(catalogRs.getString("TABLE_CAT")).append(nl);
}
} catch (SQLException e2) {
GLogger.warn("Couldn't get catalogs", e2);
sb.append(" ?? Couldn't get catalogs ??").append(nl);
} finally {
dbHelper.close(catalogRs, null);
}
return sb.toString();
}
public File getTheNewestFile() {
workingDir = System.getProperty("user.dir");
String fileName =
workingDir
+ File.separator
+ "data"
+ File.separator
+ "log"
+ File.separator
+ "karaf.log";
WatchedDir = workingDir + File.separator + "data" + File.separator + "log";
try {
LOG.info("log enabled ...");
File dir = new File(WatchedDir);
Thread.sleep(10000);
files = dir.listFiles();
Arrays.sort(files, LastModifiedFileComparator.LASTMODIFIED_COMPARATOR);
for (int i = 0; i < files.length; i++) {
file = files[i];
karafFiles += file.getName();
}
LOG.info("karaf files:" + karafFiles);
Arrays.sort(files, LastModifiedFileComparator.LASTMODIFIED_REVERSE);
for (int i = 0; i < files.length; i++) {
file = files[i];
RevOrder_ofFiles += file.getName();
}
LOG.info("list of karaf File in reverse order" + " " + RevOrder_ofFiles);
theNewestFile = files[0];
LOG.info("Latest karaf File" + theNewestFile);
} catch (Exception e) {
e.printStackTrace();
}
return theNewestFile;
}
/**
* Takes a vector full of property lists and generates a report.
*
* @param args Command line arguments. args[0] should be the config filename.
*/
public static void main(String[] args) {
// Load the database properties from properties file
Properties properties = new Properties();
// Load config file
String configFile = null;
if (args.length > 0) configFile = args[0];
try {
if (configFile == null) {
System.out.println("Database config file not set.");
return;
} else properties.load(new FileInputStream(configFile));
} catch (IOException e) {
System.out.println("Error opening config file.");
}
String url = properties.getProperty("databaseUrl");
String username = properties.getProperty("username");
String password = properties.getProperty("password");
String dir = System.getProperty("user.dir"); // Current working directory
Connection con = null;
// Try to open file containing javac output
String output = "";
try {
BufferedReader outputReader = new BufferedReader(new FileReader(dir + "/CompileOut.txt"));
while (outputReader.ready()) output += outputReader.readLine() + '\n';
// Close file
outputReader.close();
} catch (FileNotFoundException e) {
System.out.println("Error opening compilation output file.");
return;
} catch (IOException e) {
System.out.println("I/O Exception Occured.");
return;
}
boolean hasDriver = false;
// Create class for the driver
try {
Class.forName("com.mysql.jdbc.Driver");
hasDriver = true;
} catch (Exception e) {
System.out.println("Failed to load MySQL JDBC driver class.");
}
// Create connection to database if the driver was found
if (hasDriver) {
try {
con = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
System.out.println("Couldn't get connection!");
}
}
// Check that a connection was made
if (con != null) {
long userEventId = -1;
// Store results from the report into the database
try {
BufferedReader rd =
new BufferedReader(
new FileReader(dir + "/userId.txt")); // Read userId.txt to get userId
String userId = rd.readLine(); // Store userId from text file
rd.close();
// Insert the report into the table and get the auto_increment id for it
Statement stmt = con.createStatement();
stmt.executeUpdate("INSERT INTO userEvents (userId) VALUES ('" + userId + "')");
ResultSet result = stmt.getGeneratedKeys();
result.next();
userEventId = result.getLong(1);
// Close the statement
stmt.close();
// Prepare statement for adding the compilation error to the userEvent
PreparedStatement compErrorPrepStmt =
con.prepareStatement(
"INSERT INTO userEventCompilationErrors(userEventId, output) VALUES (?, ?)");
// Insert userEventId and docletId into the database
compErrorPrepStmt.setLong(1, userEventId);
compErrorPrepStmt.setString(2, output);
compErrorPrepStmt.executeUpdate();
// Close the prepare statements
compErrorPrepStmt.close();
} catch (Exception e) {
System.out.println("Exception Occurred");
System.out.println(e);
}
// Store the java files for the report
try {
// Prepare statement for storing files
PreparedStatement filePrepStmt =
con.prepareStatement(
"INSERT INTO files(userEventId, filename, contents) VALUES ("
+ userEventId
+ ", ?, ?)");
// Get the list of files from source.txt
BufferedReader rd =
new BufferedReader(
new FileReader(dir + "/source.txt")); // Read userId.txt to get userId
while (rd.ready()) {
String filename = rd.readLine(); // Store userId from text file
// Remove the "src/" from the beginning to get the real file name
String realname = filename.substring(4);
filePrepStmt.setString(1, realname);
// Read in the contents of the files
String contents = "";
File javaFile = new File(dir + "/" + filename);
int length = (int) javaFile.length();
// Add parameter for file contents to the prepared statement and execute it
filePrepStmt.setCharacterStream(2, new BufferedReader(new FileReader(javaFile)), length);
filePrepStmt.executeUpdate();
}
rd.close();
} catch (IOException e) {
System.err.println("I/O Exception Occured.");
} catch (SQLException e) {
System.err.println("SQL Exception Occured.");
}
}
}
@Override
public String getRequestsGroupByProductSentInLastMonth() throws SQLException {
Map<String, Integer> aMap = Maps.newLinkedHashMap();
final List<String> patterns = Lists.newArrayList(System.getProperty("PRODUCTS").split(","));
for (String aPattern : patterns) {
aMap.put(aPattern, 0);
}
aMap.put(UNKNOWN, 0);
Connection conn = RbDb.getConnection();
Statement statement = null;
ResultSet rs = null;
try {
statement = conn.createStatement();
rs =
statement.executeQuery(
"SELECT summary FROM reviews_reviewrequest "
+ "WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= time_added "
+ "ORDER BY time_added");
String summary = null;
boolean found = false;
while (rs.next()) {
summary = rs.getString(1);
found = false;
for (int i = 0; i < patterns.size(); ++i) {
String key = patterns.get(i);
if (summary.contains(key + "-")) {
aMap.put(key, aMap.get(key) + 1);
found = true;
break;
}
}
if (!found) {
aMap.put(UNKNOWN, aMap.get(UNKNOWN) + 1);
}
}
} finally {
if (rs != null) {
rs.close();
}
if (statement != null) {
statement.close();
}
if (conn != null) {
conn.close();
}
}
List<Pair<String, Integer>> aList = Lists.newArrayList();
for (Map.Entry<String, Integer> entry : aMap.entrySet()) {
aList.add(new Pair<>(entry.getKey(), entry.getValue()));
}
return new GsonBuilder().create().toJson(aList);
}
public final class Database {
private static final String PATH_TO_SQL_SCRIPT =
System.getProperty("user.dir") + "/DocumentTermsDump.sql";
private static final BasicDataSource dataSource = new BasicDataSource();
public static Connection getConnection(Configuration config) throws SQLException {
String dbtype = config.getString("DB_TYPE");
switch (dbtype) {
case "mysql":
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
}
dataSource.setUrl(
"jdbc:"
+ dbtype
+ "://"
+ config.getString("DB_HOSTNAME")
+ "/"
+ config.getString("DB_NAME")
+ "?autoReconnect=true&useSSL=false&rewriteBatchedStatements=true");
dataSource.setUsername(config.getString("DB_USER"));
dataSource.setPassword(config.getString("DB_PASS"));
return dataSource.getConnection();
}
private Database() {}
public static boolean checkForTables(Configuration config) {
String sqlCheck = "SHOW TABLES";
try (Connection connection = Database.getConnection(config);
PreparedStatement preparedStatement = connection.prepareStatement(sqlCheck);
ResultSet res = preparedStatement.executeQuery()) {
res.last();
if (res.getRow() != 3) {
return false;
}
} catch (SQLException e) {
e.printStackTrace();
return false;
}
return true;
}
public static boolean initDatabase(Configuration config) {
try (Connection connection = Database.getConnection(config);
Statement statement = connection.createStatement()) {
Scanner s = new Scanner(FileUtil.getInputStreamFrom(PATH_TO_SQL_SCRIPT));
s.useDelimiter("(;(\r)?\n)|(--\n)");
while (s.hasNext()) {
String line = s.next();
if (line.startsWith("/*!") && line.endsWith("*/")) {
int i = line.indexOf(' ');
line = line.substring(i + 1, line.length() - " */".length());
}
if (line.trim().length() > 0) {
statement.execute(line);
}
}
} catch (SQLException | FileNotFoundException ex) {
ex.printStackTrace();
return false;
}
return true;
}
}
public CrossVersionResultsStore() {
this(new File(System.getProperty("user.home"), ".gradle-performance-test-data/results"));
}
public void bad() throws Throwable {
String data;
if (IO.staticReturnsTrueOrFalse()) {
/* get system property user.home */
/* POTENTIAL FLAW: Read data from a system property */
data = System.getProperty("user.home");
} else {
/* FIX: Use a hardcoded string */
data = "foo";
}
if (IO.staticReturnsTrueOrFalse()) {
if (data != null) {
String names[] = data.split("-");
int successCount = 0;
Connection dbConnection = null;
Statement sqlStatement = null;
try {
dbConnection = IO.getDBConnection();
sqlStatement = dbConnection.createStatement();
for (int i = 0; i < names.length; i++) {
/* POTENTIAL FLAW: data concatenated into SQL statement used in executeBatch(), which could result in SQL Injection */
sqlStatement.addBatch(
"update users set hitcount=hitcount+1 where name='" + names[i] + "'");
}
int resultsArray[] = sqlStatement.executeBatch();
for (int i = 0; i < names.length; i++) {
if (resultsArray[i] > 0) {
successCount++;
}
}
IO.writeLine("Succeeded in " + successCount + " out of " + names.length + " queries.");
} catch (SQLException exceptSql) {
IO.logger.log(Level.WARNING, "Error getting database connection", exceptSql);
} finally {
try {
if (sqlStatement != null) {
sqlStatement.close();
}
} catch (SQLException exceptSql) {
IO.logger.log(Level.WARNING, "Error closing Statament", exceptSql);
}
try {
if (dbConnection != null) {
dbConnection.close();
}
} catch (SQLException exceptSql) {
IO.logger.log(Level.WARNING, "Error closing Connection", exceptSql);
}
}
}
} else {
if (data != null) {
String names[] = data.split("-");
int successCount = 0;
Connection dbConnection = null;
PreparedStatement sqlStatement = null;
try {
/* FIX: Use prepared statement and executeBatch (properly) */
dbConnection = IO.getDBConnection();
sqlStatement =
dbConnection.prepareStatement("update users set hitcount=hitcount+1 where name=?");
for (int i = 0; i < names.length; i++) {
sqlStatement.setString(1, names[i]);
sqlStatement.addBatch();
}
int resultsArray[] = sqlStatement.executeBatch();
for (int i = 0; i < names.length; i++) {
if (resultsArray[i] > 0) {
successCount++;
}
}
IO.writeLine("Succeeded in " + successCount + " out of " + names.length + " queries.");
} catch (SQLException exceptSql) {
IO.logger.log(Level.WARNING, "Error getting database connection", exceptSql);
} finally {
try {
if (sqlStatement != null) {
sqlStatement.close();
}
} catch (SQLException exceptSql) {
IO.logger.log(Level.WARNING, "Error closing PreparedStatement", exceptSql);
}
try {
if (dbConnection != null) {
dbConnection.close();
}
} catch (SQLException exceptSql) {
IO.logger.log(Level.WARNING, "Error closing Connection", exceptSql);
}
}
}
}
}
public class LibRt {
public static String CfgEncodingDflt =
"ISO-8859-1"; // U: si no especificamos, un archivo a string se lee con este encoding
public static final Logger logger = Logger.getLogger("rt");
public static final String EOL = "\n";
public static final int BUFF_SZ = 8192;
public static java.util.Hashtable state =
new java.util
.Hashtable(); // U: accesible desde javascript para mantener estado entre llamadas AUNQUE
// los scripts se ejecuten de cero en cada thread
public static boolean IsLogInitialized = false;
public static void logInit(boolean force) throws IOException {
if (!IsLogInitialized || force) {
LogManager.getLogManager()
.readConfiguration(
LibRt.class.getClassLoader().getResourceAsStream("cfg.logging.properties"));
String logFile = System.getProperty("CfgLog");
if (logFile != null) {
LogManager.getLogManager().readConfiguration(new FileInputStream(logFile));
}
try {
LogLvlMax = Integer.parseInt(System.getProperty("LogLvlMax", LogLvlMax + ""));
} catch (Exception ex) {
}
; // XXX: mostrar mensaje?
}
}
// Primitiva de log
public static int LogLvlMax = 9; // DFLT
public static void logm(String t, int lvl, String msg, Object o) {
// System.out.println("LOG:" + t + ":" + lvl + ":" + msg + ":" + (o != null ? ser_json(o) :
// ""));
if (lvl <= LogLvlMax) {
logger.info(t + ":" + lvl + ":" + msg + ":" + (o != null ? ser_json(o) : ""));
}
}
public static void logmex(String t, int lvl, String msg, Object o, Exception ex) {
StringBuilder sb = new StringBuilder();
StackTraceElement se[] = ex.getStackTrace();
for (int i = 0; i < se.length; i++) {
sb.append(se[i].getMethodName() + "@" + se[i].getFileName() + "@" + se[i].getLineNumber());
sb.append(" > ");
}
logm(t, lvl, msg + " EXCEPTION " + ex + " " + sb.toString(), o);
}
// ***************************************************************************
// S: enc/base64
public static String enc_base64(String data) throws UnsupportedEncodingException {
return enc_base64(data.getBytes("UTF-8"));
}
public static String enc_base64(byte[] data) throws UnsupportedEncodingException {
return DatatypeConverter.printBase64Binary(data);
}
public static String enc_base64_r_str(String encoded) throws UnsupportedEncodingException {
return new String(enc_base64_r(encoded), "UTF-8");
}
public static byte[] enc_base64_r(String encoded) throws UnsupportedEncodingException {
return DatatypeConverter.parseBase64Binary(encoded);
}
// ***************************************************************************
// S: ser/json
static final Gson gson = new Gson(); // A: encapsulamos
// XXX: mejor usar Jackson? cual usan otros proyectos de EM? Por que?
public static String ser_json(Object o) {
String s = "";
try {
s = gson.toJson(o);
} catch (Exception ex) {
s = o + "";
}
return s;
}
// ***************************************************************************
// S: ser/csv
public static int serRsCsvToFile(
ResultSet resultset, String[] columnNames, String path, int maxRows, String separator) {
Writer writer;
try {
writer = fileWriter(path);
} catch (UnsupportedEncodingException e) {
logm("ERR", 0, "FILE WRITER CSV OUTPUT OPEN unsupported encondig", path);
return -1;
} catch (FileNotFoundException e) {
logm("ERR", 0, "FILE WRITER CSV OUTPUT OPEN file not found", path);
return -1;
} catch (IOException e) {
logm("ERR", 0, "FILE WRITER CSV OUTPUT OPEN file not found", path);
return -1;
}
// A: writer escribe en el archivo especificado
return serRsCsvToWriter(resultset, columnNames, writer, maxRows, separator);
}
public static int serDiccCsvToWriter(String str, String path, int maxRows, String separator) {
Writer writer;
try {
writer = fileWriterAppend(path, false, null);
logm("DBG", 9, "ABRE WRITER", null);
} catch (UnsupportedEncodingException e) {
logm("ERR", 0, "FILE WRITER CSV OUTPUT OPEN unsupported encondig", path);
return -1;
} catch (FileNotFoundException e) {
logm("ERR", 0, "FILE WRITER CSV OUTPUT OPEN file not found", path);
return -1;
} catch (IOException e) {
logm("ERR", 0, "FILE WRITER CSV OUTPUT OPEN file not found", path);
return -1;
}
// A: writer escribe en el archivo especificado
return serDiccCsvToWriter(str, writer, maxRows, separator);
}
public static String[] dbRsColumnNames(ResultSet resultset) throws SQLException {
java.util.Vector<String> rv = new java.util.Vector<String>();
if (resultset != null) {
logm("DBG", 9, "ColumnCount=0 Calculando cantidad columnas desde metadata", null);
ResultSetMetaData md = resultset.getMetaData();
int columnCount = md.getColumnCount();
logm("DBG", 9, "ColumnCount", columnCount);
for (int i = 1; i <= columnCount; i++) {
rv.add(md.getColumnName(i).toLowerCase());
}
}
String[] r = rv.toArray(new String[0]);
logm("DBG", 9, "DB dbRsColumnNames", r);
return r;
}
public static int serRsCsvToWriter(
ResultSet resultset, String[] columnNames, Writer writer, int maxRows, String separator) {
int counter = 0;
if (resultset != null) {
if (columnNames == null) {
try {
logm("NFO", 9, "CSV titulos, obteniendo desde metadata", null);
columnNames = dbRsColumnNames(resultset);
} catch (SQLException e) {
logmex("ERR", 1, "CSV titulos, obteniendo desde metadata", null, e);
return -1;
}
}
logm("DBG", 7, "CSV titulos", columnNames);
// A: columnCount tiene el valor especificado o el default
int columnCount = columnNames.length;
// Itero el resultset escribiendo el output
try {
// XXX:OPCION escape separator si aparece en un valor?
logm("DBG", 4, "ESCRIBIENDO ARCHIVO", resultset);
for (int i = 0; i < columnCount - 1; i++) {
logm("DBG", 9, "ESCRIBE COL: ", columnNames[i]);
writer.write(columnNames[i]);
writer.write(separator);
}
writer.write(columnNames[columnCount - 1]);
writer.write(EOL);
logm("DBG", 4, "SE ESCRIBIO LINEA DE COLUMNAS", null);
logm("DBG", 4, "COUNTER", counter);
logm("DBG", 4, "MAXROWS", maxRows);
// A: escribi los nombres de las columnas
boolean hasNext = resultset.next();
logm("DBG", 4, "NEXT", hasNext);
while ((counter < maxRows || maxRows < 0) && hasNext) {
logm("DBG", 4, "Escribiendo fila :", counter);
String buf;
for (int i = 1; i < columnCount; i++) {
if ((buf = resultset.getString(i)) != null) {
writer.write(buf);
}
logm("DBG", 9, "STR", buf);
writer.write(separator);
}
if ((buf = resultset.getString(columnCount)) != null) {
writer.write(buf);
}
logm("DBG", 9, "STR", buf);
writer.write(EOL);
counter++;
// XXX:loguear un cartelito ej. cada 1000
hasNext = resultset.next();
}
logm("DBG", 2, "termino de escribir lineas", null);
} catch (SQLException s) {
logmex("ERR", 0, "DB leyendo resultset para CSV", null, s);
return -1;
} catch (IOException e) {
logmex("ERR", 0, "FILE WRITER CSV OUTPUT writing", null, e);
return -1;
}
} else {
logm("NFO", 3, "DB FILE CSV RESULTSET IS NULL, was expected?", null);
}
try {
writer.close();
} catch (IOException e) {
logmex("ERR", 0, "FILE WRITER CSV OUTPUT closing", null, e);
return -1;
}
return counter;
}
@SuppressWarnings("unchecked")
public static int serDiccCsvToWriter(String csv, Writer writer, int maxRows, String separator) {
int counter = 0;
if (csv != null) {
try {
writer.write(csv);
writer.write(EOL);
} catch (IOException e) {
logmex("ERR", 0, "FILE WRITER CSV OUTPUT writing", null, e);
return -1;
}
} else {
logm("NFO", 3, "DB FILE CSV RESULTSET IS NULL, was expected?", null);
}
return counter;
}
public static int serDiccGroupByToWriter(
ResultSet rs,
Writer writer,
int maxRows,
String idPor,
String[] idAcumulados,
String campoAcumuladoNombre) {
int rowsCount = 0;
try {
ArrayList<String> acumulado = null;
String idActual = null;
StringBuilder reg = null;
reg = new StringBuilder();
String value = "";
if (rs != null) {
ResultSetMetaData rsm = rs.getMetaData();
int countCol = rsm.getColumnCount();
String name = "";
for (int i = 1; i <= countCol; i++) {
name = rsm.getColumnName(i);
reg.append(name.toLowerCase()).append("\t");
}
reg.append(campoAcumuladoNombre);
writer.write(reg.toString() + EOL);
while (rs.next()) {
if (idActual == null) {
reg = new StringBuilder();
acumulado = new ArrayList<String>();
idActual = rs.getString(idPor);
for (int i = 1; i <= countCol; i++) {
reg.append(rs.getString(i)).append("\t");
}
for (String id : idAcumulados) {
value = rs.getString(id);
if (!rs.wasNull()) {
acumulado.add(rs.getString(id));
}
}
} else {
if (idActual.equals(rs.getString(idPor))) {
for (String id : idAcumulados) {
value = rs.getString(id);
if (!rs.wasNull()) {
acumulado.add(rs.getString(id));
}
}
} else {
if (acumulado.size() > 0) {
for (String str : acumulado) {
reg.append(str).append(",");
}
reg.deleteCharAt(reg.length() - 1);
}
reg.append(EOL);
writer.write(reg.toString());
rowsCount++;
if (maxRows == rowsCount) {
break;
}
idActual = rs.getString(idPor);
reg = new StringBuilder();
acumulado = new ArrayList<String>();
for (int i = 1; i <= countCol; i++) {
reg.append(rs.getString(i)).append("\t");
}
for (String id : idAcumulados) {
value = rs.getString(id);
if (!rs.wasNull()) {
acumulado.add(rs.getString(id));
}
}
}
}
}
if (acumulado.size() > 0) {
for (String str : acumulado) {
reg.append(str).append(",");
}
reg.deleteCharAt(reg.length() - 1);
}
reg.append(EOL);
writer.write(reg.toString());
rowsCount++;
}
} catch (SQLException e) {
logm("ERR", 1, "Error al escribir registros", e);
} catch (IOException e) {
logm("ERR", 1, "Error al escribir registros", e);
}
return rowsCount;
}
public static void closeWriterAppend(Writer writer) {
try {
writer.close();
} catch (IOException e) {
logmex("ERR", 0, "FILE WRITER CSV OUTPUT closing", null, e);
}
}
// ***************************************************************************
// S: db
public static int dbRsColumnCount(ResultSet rs) throws SQLException {
return rs.getMetaData().getColumnCount();
}
// ***************************************************************************
// S: digest
public static MessageDigest digestCalc(String algorithm) throws NoSuchAlgorithmException {
return MessageDigest.getInstance(algorithm);
}
public static String digestHexStr(MessageDigest digestCalc) {
return (new HexBinaryAdapter()).marshal(digestCalc.digest());
}
public static String digest(String data, String algorithm) throws NoSuchAlgorithmException {
MessageDigest calc = digestCalc(algorithm);
calc.update(data.getBytes());
return digestHexStr(calc);
}
// ***************************************************************************
// S: file
public static Writer fileWriter(String path)
throws UnsupportedEncodingException, FileNotFoundException, IOException {
return fileWriter(path, false, null);
}
public static Writer fileWriter(String path, boolean zip, MessageDigest digestCalc)
throws UnsupportedEncodingException, FileNotFoundException, IOException {
return streamWriter(new FileOutputStream(path), zip, digestCalc);
}
public static Writer fileWriterAppend(String path, boolean zip, MessageDigest digestCalc)
throws UnsupportedEncodingException, FileNotFoundException, IOException {
return streamWriter(new FileOutputStream(path, true), zip, digestCalc);
}
public static OutputStream streamForOutput_file(
String path, boolean zip, MessageDigest digestCalc)
throws UnsupportedEncodingException, FileNotFoundException, IOException {
return streamForOutput(new FileOutputStream(path), zip, digestCalc);
}
public static OutputStream streamForOutput(OutputStream os, boolean zip, MessageDigest digestCalc)
throws UnsupportedEncodingException, FileNotFoundException, IOException {
OutputStream dos = digestCalc != null ? new DigestOutputStream(os, digestCalc) : os;
OutputStream zos = zip ? new GZIPOutputStream(dos) : dos;
return zos;
}
public static Writer streamWriter(OutputStream os, boolean zip, MessageDigest digestCalc)
throws UnsupportedEncodingException, FileNotFoundException, IOException {
return new BufferedWriter(
new OutputStreamWriter(streamForOutput(os, zip, digestCalc), "utf-8"));
}
public static InputStream streamForInput_file(String path, boolean zip, MessageDigest digestCalc)
throws UnsupportedEncodingException, FileNotFoundException, IOException {
return streamForInput(new FileInputStream(path), zip, digestCalc);
}
public static Reader fileReader(String path, boolean zip, MessageDigest digestCalc)
throws UnsupportedEncodingException, FileNotFoundException, IOException {
return streamReader(new FileInputStream(path), zip, digestCalc);
}
public static InputStream streamForInput(InputStream fis, boolean zip, MessageDigest digestCalc)
throws UnsupportedEncodingException, FileNotFoundException, IOException {
InputStream dis = digestCalc != null ? new DigestInputStream(fis, digestCalc) : fis;
InputStream zis = zip ? new GZIPInputStream(dis) : dis;
return zis;
}
public static Reader streamReader(InputStream fis, boolean zip, MessageDigest digestCalc)
throws UnsupportedEncodingException, FileNotFoundException, IOException {
return new BufferedReader(
new InputStreamReader(streamForInput(fis, zip, digestCalc))); // , "utf-8"));
}
public static int pipe_stream(InputStream is, OutputStream os, boolean wantsKeepOpen)
throws IOException { // U: copia de un stream al otro
int cnt = 0;
int n;
byte[] buffer = new byte[BUFF_SZ];
while ((n = is.read(buffer)) > -1) {
cnt += n;
os.write(buffer, 0, n);
}
if (!wantsKeepOpen) {
is.close();
os.close();
}
return cnt;
}
public static String get_stream(Reader isr) throws IOException {
char[] buffer = new char[BUFF_SZ];
StringBuilder out = new StringBuilder();
logm("DBG", 9, "STREAM GET", isr + "");
for (; ; ) {
int rsz = isr.read(buffer, 0, buffer.length);
logm("DBG", 9, "STREAM GET READ", rsz);
if (rsz < 0) break;
out.append(buffer, 0, rsz);
}
String s = out.toString();
logm("DBG", 9, "STREAM GET RESULT", s);
return s;
}
public static String get_stream(InputStream is) throws IOException {
return get_stream(is, CfgEncodingDflt);
}
public static String get_stream(InputStream is, String encoding) throws IOException {
if (encoding == null) {
encoding = CfgEncodingDflt;
}
byte[] buffer = new byte[BUFF_SZ];
StringBuilder out = new StringBuilder();
logm("DBG", 9, "STREAM GET", is + "");
for (; ; ) {
int rsz = is.read(buffer, 0, buffer.length);
logm("DBG", 9, "STREAM GET READ", rsz);
if (rsz < 0) break;
out.append(new String(buffer, 0, rsz, encoding));
}
String s = out.toString();
logm("DBG", 9, "STREAM GET RESULT", s);
return s;
}
public static void set_stream(OutputStream os, String data, String encoding) throws IOException {
os.write(data.getBytes(encoding));
os.close();
}
public static void set_stream(Writer os, String data) throws IOException {
os.write(data);
os.close();
}
public static String get_file(
String path, boolean gzip, MessageDigest digestCalc, String encoding)
throws UnsupportedEncodingException, IOException {
try {
return get_stream(streamForInput_file(path, gzip, digestCalc));
} catch (FileNotFoundException ex) {
return "";
}
}
public static String get_resource(String path) throws IOException {
java.io.InputStream srcs = null;
try {
srcs = LibRt.class.getResourceAsStream(path);
return get_stream(srcs);
} catch (IOException ex) {
LibRt.logmex("DBG", 9, "RT GET_RESOURCE", path, ex);
throw (ex);
}
}
public static String get_resourceOrFile(
String path, boolean gzip, MessageDigest digestCalc, String encoding)
throws UnsupportedEncodingException, IOException {
try {
return get_resource(path);
} catch (IOException ex) {
return get_file(path, gzip, digestCalc, encoding);
}
}
public static void set_file(
String path, String data, boolean gzip, MessageDigest digestCalc, String encoding)
throws UnsupportedEncodingException, IOException {
set_stream(streamForOutput_file(path, gzip, digestCalc), data, CfgEncodingDflt);
}
public static String[] get_filelist(String path, boolean nodirs, boolean nofiles) {
File folder = new File(path);
if (folder.isDirectory()) {
File[] listOfFiles = folder.listFiles();
java.util.Vector<String> r = new java.util.Vector<String>();
for (int i = 0; listOfFiles != null && i < listOfFiles.length; i++) {
if ((listOfFiles[i].isFile() && !nofiles) || (listOfFiles[i].isDirectory() && !nodirs)) {
r.add(listOfFiles[i].getName());
}
}
return r.toArray(new String[0]);
} else {
return null; // A: no existe o no es directorio
}
}
public static FileLock lock_file(String path) throws IOException, FileNotFoundException {
RandomAccessFile file = new RandomAccessFile(path, "rw");
FileChannel fileChannel = file.getChannel();
return fileChannel.tryLock();
}
public static boolean unlock_file(FileLock fileLock) throws IOException, FileNotFoundException {
if (fileLock != null) {
fileLock.release();
return true;
}
return false;
}
public static String temp_filePath(String namePattern, String ext)
throws IOException, FileNotFoundException {
File temp = File.createTempFile("temp-file-name", ".tmp");
return temp.getAbsolutePath();
}
// ***************************************************************************
// S File: scp = copiar sobre ssh
public static void set_file_scp(
String srcFilePath,
String dstUrl,
String dstFilePath,
String dstUser,
String dstPass,
String keyFilePath)
throws SshException {
// SEE:
// http://code.google.com/p/securechannelfacade/source/browse/trunk/src/main/java/org/rev6/scf/SshConnection.java?r=12
SshConnection sshCx = null;
String[] nameAndPort = dstUrl.split(":");
int dstPort = nameAndPort.length > 1 ? Integer.parseInt(nameAndPort[1]) : 22;
try {
if (keyFilePath == null) {
sshCx = new SshConnection(nameAndPort[0], dstUser, dstPass);
logm(
"DBG",
7,
"NET SSH CONNECT TO {HOST: '"
+ nameAndPort[0]
+ "', PORT:"
+ dstPort
+ ", USER:'******', auth: '"
+ "pass"
+ "'}",
null);
} else {
sshCx = new SshConnection(nameAndPort[0], dstUser, new File(keyFilePath));
logm(
"DBG",
7,
"NET SSH CONNECT TO {HOST: '"
+ nameAndPort[0]
+ "', PORT:"
+ dstPort
+ ", USER:'******', auth: 'key', keyfile: '"
+ keyFilePath
+ "'}",
null);
}
sshCx.setPort(dstPort);
sshCx.connect();
ScpFile scpFile = new ScpFile(new File(srcFilePath), dstFilePath);
sshCx.executeTask(new ScpUpload(scpFile));
} finally {
if (sshCx != null) {
sshCx.disconnect();
}
}
}
public static void set_file_scp_pass(
String srcFilePath, String dstUrl, String dstFilePath, String dstUser, String dstPass)
throws SshException {
set_file_scp(srcFilePath, dstUrl, dstFilePath, dstUser, dstPass, null);
}
public static void set_file_scp_key(
String srcFilePath, String dstUrl, String dstFilePath, String dstUser, String keyFilePath)
throws SshException {
set_file_scp(srcFilePath, dstUrl, dstFilePath, dstUser, null, keyFilePath);
}
// ***************************************************************************
// S: net/http
public static HttpURLConnection httpCx(String url, String method, String usr, String pass)
throws MalformedURLException, IOException, ProtocolException {
// NB:https requiere HAVA_HOME para encontrar los certificados!
URL obj = new URL(url);
HttpURLConnection cx = (HttpURLConnection) obj.openConnection();
cx.setRequestMethod(method);
if (usr != "") {
String basicAuth = "Basic " + new String(enc_base64(usr + ":" + pass));
cx.setRequestProperty("Authorization", basicAuth);
}
// A: parameters set
return cx;
}
public static void httpWrite(HttpURLConnection cx, String data)
throws IOException, ProtocolException {
cx.setDoOutput(true);
DataOutputStream wr = new DataOutputStream(cx.getOutputStream());
wr.writeBytes(data);
wr.flush();
wr.close();
}
public static String httpRead(HttpURLConnection cx)
throws UnsupportedEncodingException, IOException {
// int responseCode = con.getResponseCode();
return get_stream(cx.getInputStream());
}
public static String get_http(String url, String method, String data, String usr, String pass)
throws IOException, ProtocolException, UnsupportedEncodingException {
HttpURLConnection cx = httpCx(url, method, usr, pass);
if (data != null) {
httpWrite(cx, data);
}
return httpRead(cx);
}
// ****************************************************************************
// S: Javascript
public static LibRtJs JsImpl =
System.getProperty("jsImpl", "RHINO").equals("JDK") ? new LibRtJsJdk() : new LibRtJsRhino();
public static LibRtJs jsEval(String js, String srcDesc, LibRtJs jsEnv, String[] args)
throws Exception {
logm("DBG", 9, "RT JS EVAL START PATH=" + srcDesc + " SRC", js);
return JsImpl.jsEval(js, srcDesc, jsEnv, args);
}
public static Object jsCall(String funName, LibRtJs jsEnv, Object[] args)
throws IOException, Exception {
logm("DBG", 5, "RT JS CALL TRY", funName);
return JsImpl.jsCall(funName, jsEnv, args);
}
public static LibRtJs jsLoad(String path, LibRtJs jsEnv, String[] args)
throws IOException, Exception {
logm("DBG", 5, "RT JS LOAD TRY", path);
return JsImpl.jsLoad(path, jsEnv, args);
}
public static LibRtJs jsLoadAlone(
String path,
Hashtable<String, Object>
env) { // U: ejecutar un script en un contexto separado (no comparte variables salvo las
// que le pasemos en el hashtable)
return JsImpl.jsLoadAlone(path, env);
}
// ****************************************************************************
// S: paths
public static String runtimePath()
throws java.net.URISyntaxException,
java.net.MalformedURLException { // U: para cargar librerias y assets, etc.
// SEE: http://stackoverflow.com/questions/320542/how-to-get-the-path-of-a-running-jar-file
URL url = LibRt.class.getResource(LibRt.class.getSimpleName() + ".class");
logm("DBG", 1, "RT runtimePath URL", url);
URL urlOk = (url.getProtocol() == "jar" ? new URL(url.getPath()) : url);
logm("DBG", 1, "RT runtimePath URL OK", urlOk);
return new File(urlOk.toURI()).getParentFile().getPath();
}
// ****************************************************************************
// S: main
public static void init() throws Exception {
logInit(false);
}
public static void main(String[] args) throws Exception {
init();
String mainPath = args.length > 0 ? args[0] : "0inicio.js";
try {
jsLoad("librt.js", null, args);
jsLoad(mainPath, null, args);
} catch (Exception ex) {
ex.printStackTrace();
logmex("ERR", 1, "RT RUNNING SCRIPTS", null, ex);
}
}
private static Class<?> getClassFromJar(String pathToJar, String pkg, String classToGet)
throws IOException, ClassNotFoundException, SecurityException, InstantiationException,
IllegalAccessException, NoSuchMethodException, IllegalArgumentException,
InvocationTargetException {
JarFile jarFile = new JarFile(pathToJar);
Enumeration e = jarFile.entries();
URL[] urls = {new URL("jar:file:" + pathToJar + "!/")};
ClassLoader cl = URLClassLoader.newInstance(urls);
Class<?> c = Class.forName(pkg + "." + classToGet, true, cl);
return c;
}
public static void executeMethodClass(
String pathToJar,
String pkg,
String classToGet,
String methodName,
String pathToFile,
long logIdSyncMin,
long logIdSyncMax)
throws IOException, ClassNotFoundException, SecurityException, InstantiationException,
IllegalAccessException, NoSuchMethodException, IllegalArgumentException,
InvocationTargetException {
Class<?> c = getClassFromJar(pathToJar, pkg, classToGet);
Method method = c.getDeclaredMethod(methodName, String.class, long.class, long.class);
method.invoke(null, pathToFile, logIdSyncMin, logIdSyncMax);
}
}
public static void main(String[] args) throws Exception {
String method = "main";
int location = 1000;
int argsCount = args.length;
String sourceType = "";
String sourceServer = "";
String sourceInstance = "";
int sourcePort = 0;
String sourceDatabase = "";
String sourceSchema = "";
String sourceTable = "";
String refreshType = "";
String appendColumnName = "";
int appendColumnMax = 0;
String gpDatabase = "";
int gpPort = 0;
int queueId = 0;
int connectionId = 0;
String selectSQL = "";
// this is an external table that a user defines
if (argsCount == 4) {
gpDatabase = args[0];
gpPort = Integer.parseInt(args[1]);
connectionId = Integer.parseInt(args[2]);
selectSQL = args[3];
}
// this is an extrenal table Outsourcer defines
else if (argsCount == 13) {
sourceType = args[0];
sourceServer = args[1];
sourceInstance = args[2];
sourcePort = Integer.parseInt(args[3]);
sourceDatabase = args[4];
sourceSchema = args[5];
sourceTable = args[6];
refreshType = args[7];
appendColumnName = args[8];
appendColumnMax = Integer.parseInt(args[9]);
gpDatabase = args[10];
gpPort = Integer.parseInt(args[11]);
queueId = Integer.parseInt(args[12]);
}
String gpServer = "localhost";
String gpUserName = System.getProperty("user.name");
String sourceUser = "";
String sourcePass = "";
Connection gpConn = null;
try {
location = 3000;
gpConn = CommonDB.connectGP(gpServer, gpPort, gpDatabase, gpUserName);
if (argsCount == 13) {
location = 3100;
executeOS(
sourceType,
sourceServer,
sourceInstance,
sourcePort,
sourceDatabase,
sourceSchema,
sourceTable,
refreshType,
appendColumnName,
appendColumnMax,
gpConn,
queueId);
} else if (argsCount == 4) {
location = 3200;
executeExt(gpConn, connectionId, selectSQL);
}
location = 4000;
gpConn.close();
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
} finally {
if (gpConn != null) gpConn.close();
}
}
/**
* Checks the <code>os.name</code> system property to see if it starts with "windows".
*
* @return <code>true</code> if <code>os.name</code> starts with "windows", else <code>false
* </code>.
*/
public static boolean isWindowsOS() {
return System.getProperty("os.name").toLowerCase().startsWith("windows");
}
// sqlbob@users 20020401 - patch 1.7.0 by sqlbob (RMP) - enhancements
// sqlbob@users 20020401 - patch 537501 by ulrivo - command line arguments
// sqlbob@users 20020407 - patch 1.7.0 - reengineering
public class DatabaseManager extends Applet implements ActionListener, WindowListener, KeyListener {
static final String NL = System.getProperty("line.separator");
static final int iMaxRecent = 24;
Connection cConn;
DatabaseMetaData dMeta;
Statement sStatement;
Menu mRecent;
String sRecent[];
int iRecent;
TextArea txtCommand;
Button butExecute;
Tree tTree;
Panel pResult;
long lTime;
int iResult; // 0: grid; 1: text
Grid gResult;
TextArea txtResult;
boolean bHelp;
Frame fMain;
Image imgEmpty;
static boolean bMustExit;
// (ulrivo): variables set by arguments from the commandline
static String defDriver = "org.hsqldb.jdbcDriver";
static String defURL = "jdbc:hsqldb:.";
static String defUser = "******";
static String defPassword = "";
static String defScript;
static String defDirectory;
/**
* Method declaration
*
* @param c
*/
void connect(Connection c) {
if (c == null) {
return;
}
if (cConn != null) {
try {
cConn.close();
} catch (SQLException e) {
}
}
cConn = c;
try {
dMeta = cConn.getMetaData();
sStatement = cConn.createStatement();
refreshTree();
} catch (SQLException e) {
e.printStackTrace();
}
}
/** Method declaration */
public void init() {
DatabaseManager m = new DatabaseManager();
m.main();
try {
m.connect(ConnectionDialog.createConnection(defDriver, defURL, defUser, defPassword));
m.insertTestData();
m.refreshTree();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* Method declaration
*
* @param arg
*/
public static void main(String arg[]) {
System.getProperties().put("sun.java2d.noddraw", "true");
// (ulrivo): read all arguments from the command line
String lowerArg;
boolean autoConnect = false;
for (int i = 0; i < arg.length; i++) {
lowerArg = arg[i].toLowerCase();
i++;
if (i == arg.length) {
showUsage();
return;
}
if (lowerArg.equals("-driver")) {
defDriver = arg[i];
autoConnect = true;
} else if (lowerArg.equals("-url")) {
defURL = arg[i];
autoConnect = true;
} else if (lowerArg.equals("-user")) {
defUser = arg[i];
autoConnect = true;
} else if (lowerArg.equals("-password")) {
defPassword = arg[i];
autoConnect = true;
} else if (lowerArg.equals("-dir")) {
defDirectory = arg[i];
} else if (lowerArg.equals("-script")) {
defScript = arg[i];
} else {
showUsage();
return;
}
}
bMustExit = true;
DatabaseManager m = new DatabaseManager();
m.main();
Connection c = null;
try {
if (autoConnect) {
c =
ConnectionDialog.createConnection(
defDriver, defURL,
defUser, defPassword);
} else {
c = ConnectionDialog.createConnection(m.fMain, "Connect");
}
} catch (Exception e) {
e.printStackTrace();
}
if (c == null) {
return;
}
m.connect(c);
}
private static void showUsage() {
System.out.println(
"Usage: java DatabaseManager [-options]\n"
+ "where options include:\n"
+ " -driver <classname> jdbc driver class\n"
+ " -url <name> jdbc url\n"
+ " -user <name> username used for connection\n"
+ " -password <password> password for this user\n"
+ " -dir <path> default directory\n"
+ " -script <file> reads from script file\n");
}
/** Method declaration */
void insertTestData() {
try {
DatabaseManagerCommon.createTestTables(sStatement);
refreshTree();
txtCommand.setText(DatabaseManagerCommon.createTestData(sStatement));
refreshTree();
for (int i = 0; i < DatabaseManagerCommon.testDataSql.length; i++) {
addToRecent(DatabaseManagerCommon.testDataSql[i]);
}
execute();
} catch (SQLException e) {
e.printStackTrace();
}
}
/** Method declaration */
void main() {
fMain = new Frame("HSQL Database Manager");
imgEmpty = createImage(new MemoryImageSource(2, 2, new int[4 * 4], 2, 2));
fMain.setIconImage(imgEmpty);
fMain.addWindowListener(this);
MenuBar bar = new MenuBar();
// used shortcuts: CERGTSIUDOLM
String fitems[] = {
"-Connect...", "--", "-Open Script...", "-Save Script...", "-Save Result...", "--", "-Exit"
};
addMenu(bar, "File", fitems);
String vitems[] = {
"RRefresh Tree", "--", "GResults in Grid", "TResults in Text",
"--", "1Shrink Tree", "2Enlarge Tree", "3Shrink Command",
"4Enlarge Command"
};
addMenu(bar, "View", vitems);
String sitems[] = {
"SSELECT",
"IINSERT",
"UUPDATE",
"DDELETE",
"--",
"-CREATE TABLE",
"-DROP TABLE",
"-CREATE INDEX",
"-DROP INDEX",
"--",
"-CHECKPOINT",
"-SCRIPT",
"-SET",
"-SHUTDOWN",
"--",
"-Test Script"
};
addMenu(bar, "Command", sitems);
Menu recent = new Menu("Recent");
mRecent = new Menu("Recent");
bar.add(mRecent);
String soptions[] = {
"-AutoCommit on",
"-AutoCommit off",
"OCommit",
"LRollback",
"--",
"-Disable MaxRows",
"-Set MaxRows to 100",
"--",
"-Logging on",
"-Logging off",
"--",
"-Insert test data"
};
addMenu(bar, "Options", soptions);
/* NB - 26052002 Restore is not implemented yet in the transfer tool */
String stools[] = {"-Dump", /*"-Restore",*/ "-Transfer"};
addMenu(bar, "Tools", stools);
fMain.setMenuBar(bar);
fMain.setSize(640, 480);
fMain.add("Center", this);
initGUI();
sRecent = new String[iMaxRecent];
Dimension d = Toolkit.getDefaultToolkit().getScreenSize();
Dimension size = fMain.getSize();
// (ulrivo): full size on screen with less than 640 width
if (d.width >= 640) {
fMain.setLocation((d.width - size.width) / 2, (d.height - size.height) / 2);
} else {
fMain.setLocation(0, 0);
fMain.setSize(d);
}
fMain.show();
// (ulrivo): load query from command line
if (defScript != null) {
if (defDirectory != null) {
defScript = defDirectory + File.separator + defScript;
}
txtCommand.setText(DatabaseManagerCommon.readFile(defScript));
}
txtCommand.requestFocus();
}
/**
* Method declaration
*
* @param b
* @param name
* @param items
*/
void addMenu(MenuBar b, String name, String items[]) {
Menu menu = new Menu(name);
addMenuItems(menu, items);
b.add(menu);
}
/**
* Method declaration
*
* @param f
* @param m
*/
void addMenuItems(Menu f, String m[]) {
for (int i = 0; i < m.length; i++) {
MenuItem item = new MenuItem(m[i].substring(1));
char c = m[i].charAt(0);
if (c != '-') {
item.setShortcut(new MenuShortcut(c));
}
item.addActionListener(this);
f.add(item);
}
}
/**
* Method declaration
*
* @param k
*/
public void keyPressed(KeyEvent k) {}
/**
* Method declaration
*
* @param k
*/
public void keyReleased(KeyEvent k) {}
/**
* Method declaration
*
* @param k
*/
public void keyTyped(KeyEvent k) {
if (k.getKeyChar() == '\n' && k.isControlDown()) {
k.consume();
execute();
}
}
/**
* Method declaration
*
* @param ev
*/
public void actionPerformed(ActionEvent ev) {
String s = ev.getActionCommand();
if (s == null) {
if (ev.getSource() instanceof MenuItem) {
MenuItem i;
s = ((MenuItem) ev.getSource()).getLabel();
}
}
if (s.equals("Execute")) {
execute();
} else if (s.equals("Exit")) {
windowClosing(null);
} else if (s.equals("Transfer")) {
Transfer.work(null);
} else if (s.equals("Dump")) {
Transfer.work(new String[] {"-d"});
/* NB - 26052002 Restore is not implemented yet in the transfer tool */
/*
} else if (s.equals("Restore")) {
Transfer.work(new String[]{"-r"});
*/
} else if (s.equals("Logging on")) {
jdbcSystem.setLogToSystem(true);
} else if (s.equals("Logging off")) {
jdbcSystem.setLogToSystem(false);
} else if (s.equals("Refresh Tree")) {
refreshTree();
} else if (s.startsWith("#")) {
int i = Integer.parseInt(s.substring(1));
txtCommand.setText(sRecent[i]);
} else if (s.equals("Connect...")) {
connect(ConnectionDialog.createConnection(fMain, "Connect"));
refreshTree();
} else if (s.equals("Results in Grid")) {
iResult = 0;
pResult.removeAll();
pResult.add("Center", gResult);
pResult.doLayout();
} else if (s.equals("Open Script...")) {
FileDialog f = new FileDialog(fMain, "Open Script", FileDialog.LOAD);
// (ulrivo): set default directory if set from command line
if (defDirectory != null) {
f.setDirectory(defDirectory);
}
f.show();
String file = f.getFile();
if (file != null) {
txtCommand.setText(DatabaseManagerCommon.readFile(f.getDirectory() + file));
}
} else if (s.equals("Save Script...")) {
FileDialog f = new FileDialog(fMain, "Save Script", FileDialog.SAVE);
// (ulrivo): set default directory if set from command line
if (defDirectory != null) {
f.setDirectory(defDirectory);
}
f.show();
String file = f.getFile();
if (file != null) {
DatabaseManagerCommon.writeFile(f.getDirectory() + file, txtCommand.getText());
}
} else if (s.equals("Save Result...")) {
FileDialog f = new FileDialog(fMain, "Save Result", FileDialog.SAVE);
// (ulrivo): set default directory if set from command line
if (defDirectory != null) {
f.setDirectory(defDirectory);
}
f.show();
String file = f.getFile();
if (file != null) {
showResultInText();
DatabaseManagerCommon.writeFile(f.getDirectory() + file, txtResult.getText());
}
} else if (s.equals("Results in Text")) {
iResult = 1;
pResult.removeAll();
pResult.add("Center", txtResult);
pResult.doLayout();
showResultInText();
} else if (s.equals("AutoCommit on")) {
try {
cConn.setAutoCommit(true);
} catch (SQLException e) {
}
} else if (s.equals("AutoCommit off")) {
try {
cConn.setAutoCommit(false);
} catch (SQLException e) {
}
} else if (s.equals("Enlarge Tree")) {
Dimension d = tTree.getMinimumSize();
d.width += 20;
tTree.setMinimumSize(d);
fMain.pack();
} else if (s.equals("Shrink Tree")) {
Dimension d = tTree.getMinimumSize();
d.width -= 20;
if (d.width >= 0) {
tTree.setMinimumSize(d);
}
fMain.pack();
} else if (s.equals("Enlarge Command")) {
txtCommand.setRows(txtCommand.getRows() + 1);
fMain.pack();
} else if (s.equals("Shrink Command")) {
int i = txtCommand.getRows() - 1;
txtCommand.setRows(i < 1 ? 1 : i);
fMain.pack();
} else if (s.equals("Commit")) {
try {
cConn.commit();
} catch (SQLException e) {
}
} else if (s.equals("Insert test data")) {
insertTestData();
} else if (s.equals("Rollback")) {
try {
cConn.rollback();
} catch (SQLException e) {
}
} else if (s.equals("Disable MaxRows")) {
try {
sStatement.setMaxRows(0);
} catch (SQLException e) {
}
} else if (s.equals("Set MaxRows to 100")) {
try {
sStatement.setMaxRows(100);
} catch (SQLException e) {
}
} else if (s.equals("SELECT")) {
showHelp(DatabaseManagerCommon.selectHelp);
} else if (s.equals("INSERT")) {
showHelp(DatabaseManagerCommon.insertHelp);
} else if (s.equals("UPDATE")) {
showHelp(DatabaseManagerCommon.updateHelp);
} else if (s.equals("DELETE")) {
showHelp(DatabaseManagerCommon.deleteHelp);
} else if (s.equals("CREATE TABLE")) {
showHelp(DatabaseManagerCommon.createTableHelp);
} else if (s.equals("DROP TABLE")) {
showHelp(DatabaseManagerCommon.dropTableHelp);
} else if (s.equals("CREATE INDEX")) {
showHelp(DatabaseManagerCommon.createIndexHelp);
} else if (s.equals("DROP INDEX")) {
showHelp(DatabaseManagerCommon.dropIndexHelp);
} else if (s.equals("CHECKPOINT")) {
showHelp(DatabaseManagerCommon.checkpointHelp);
} else if (s.equals("SCRIPT")) {
showHelp(DatabaseManagerCommon.scriptHelp);
} else if (s.equals("SHUTDOWN")) {
showHelp(DatabaseManagerCommon.shutdownHelp);
} else if (s.equals("SET")) {
showHelp(DatabaseManagerCommon.setHelp);
} else if (s.equals("Test Script")) {
showHelp(DatabaseManagerCommon.testHelp);
}
}
/**
* Method declaration
*
* @param s
* @param help
*/
void showHelp(String help[]) {
txtCommand.setText(help[0]);
txtResult.setText(help[1]);
bHelp = true;
pResult.removeAll();
pResult.add("Center", txtResult);
pResult.doLayout();
txtCommand.requestFocus();
txtCommand.setCaretPosition(help[0].length());
}
/**
* Method declaration
*
* @param e
*/
public void windowActivated(WindowEvent e) {}
/**
* Method declaration
*
* @param e
*/
public void windowDeactivated(WindowEvent e) {}
/**
* Method declaration
*
* @param e
*/
public void windowClosed(WindowEvent e) {}
/**
* Method declaration
*
* @param ev
*/
public void windowClosing(WindowEvent ev) {
try {
cConn.close();
} catch (Exception e) {
}
fMain.dispose();
if (bMustExit) {
System.exit(0);
}
}
/**
* Method declaration
*
* @param e
*/
public void windowDeiconified(WindowEvent e) {}
/**
* Method declaration
*
* @param e
*/
public void windowIconified(WindowEvent e) {}
/**
* Method declaration
*
* @param e
*/
public void windowOpened(WindowEvent e) {}
/** Method declaration */
void execute() {
String sCmd = txtCommand.getText();
if (sCmd.startsWith("-->>>TEST<<<--")) {
testPerformance();
return;
}
String g[] = new String[1];
try {
lTime = System.currentTimeMillis();
sStatement.execute(sCmd);
int r = sStatement.getUpdateCount();
if (r == -1) {
formatResultSet(sStatement.getResultSet());
} else {
g[0] = "update count";
gResult.setHead(g);
g[0] = String.valueOf(r);
gResult.addRow(g);
}
lTime = System.currentTimeMillis() - lTime;
addToRecent(txtCommand.getText());
} catch (SQLException e) {
lTime = System.currentTimeMillis() - lTime;
g[0] = "SQL Error";
gResult.setHead(g);
String s = e.getMessage();
s += " / Error Code: " + e.getErrorCode();
s += " / State: " + e.getSQLState();
g[0] = s;
gResult.addRow(g);
}
updateResult();
System.gc();
}
/** Method declaration */
void updateResult() {
if (iResult == 0) {
// in case 'help' has removed the grid
if (bHelp) {
pResult.removeAll();
pResult.add("Center", gResult);
pResult.doLayout();
bHelp = false;
}
gResult.update();
gResult.repaint();
} else {
showResultInText();
}
txtCommand.selectAll();
txtCommand.requestFocus();
}
/**
* Method declaration
*
* @param r
*/
void formatResultSet(ResultSet r) {
if (r == null) {
String g[] = new String[1];
g[0] = "Result";
gResult.setHead(g);
g[0] = "(empty)";
gResult.addRow(g);
return;
}
try {
ResultSetMetaData m = r.getMetaData();
int col = m.getColumnCount();
String h[] = new String[col];
for (int i = 1; i <= col; i++) {
h[i - 1] = m.getColumnLabel(i);
}
gResult.setHead(h);
while (r.next()) {
for (int i = 1; i <= col; i++) {
h[i - 1] = r.getString(i);
if (r.wasNull()) {
h[i - 1] = "(null)";
}
}
gResult.addRow(h);
}
r.close();
} catch (SQLException e) {
}
}
/** Method declaration */
void testPerformance() {
String all = txtCommand.getText();
StringBuffer b = new StringBuffer();
long total = 0;
for (int i = 0; i < all.length(); i++) {
char c = all.charAt(i);
if (c != '\n') {
b.append(c);
}
}
all = b.toString();
String g[] = new String[4];
g[0] = "ms";
g[1] = "count";
g[2] = "sql";
g[3] = "error";
gResult.setHead(g);
int max = 1;
lTime = System.currentTimeMillis() - lTime;
while (!all.equals("")) {
int i = all.indexOf(';');
String sql;
if (i != -1) {
sql = all.substring(0, i);
all = all.substring(i + 1);
} else {
sql = all;
all = "";
}
if (sql.startsWith("--#")) {
max = Integer.parseInt(sql.substring(3));
continue;
} else if (sql.startsWith("--")) {
continue;
}
g[2] = sql;
long l = 0;
try {
l = DatabaseManagerCommon.testStatement(sStatement, sql, max);
total += l;
g[0] = String.valueOf(l);
g[1] = String.valueOf(max);
g[3] = "";
} catch (SQLException e) {
g[0] = g[1] = "n/a";
g[3] = e.toString();
}
gResult.addRow(g);
System.out.println(l + " ms : " + sql);
}
g[0] = "" + total;
g[1] = "total";
g[2] = "";
gResult.addRow(g);
lTime = System.currentTimeMillis() - lTime;
updateResult();
}
/** Method declaration */
void showResultInText() {
String col[] = gResult.getHead();
int width = col.length;
int size[] = new int[width];
Vector data = gResult.getData();
String row[];
int height = data.size();
for (int i = 0; i < width; i++) {
size[i] = col[i].length();
}
for (int i = 0; i < height; i++) {
row = (String[]) data.elementAt(i);
for (int j = 0; j < width; j++) {
int l = row[j].length();
if (l > size[j]) {
size[j] = l;
}
}
}
StringBuffer b = new StringBuffer();
for (int i = 0; i < width; i++) {
b.append(col[i]);
for (int l = col[i].length(); l <= size[i]; l++) {
b.append(' ');
}
}
b.append(NL);
for (int i = 0; i < width; i++) {
for (int l = 0; l < size[i]; l++) {
b.append('-');
}
b.append(' ');
}
b.append(NL);
for (int i = 0; i < height; i++) {
row = (String[]) data.elementAt(i);
for (int j = 0; j < width; j++) {
b.append(row[j]);
for (int l = row[j].length(); l <= size[j]; l++) {
b.append(' ');
}
}
b.append(NL);
}
b.append(NL + height + " row(s) in " + lTime + " ms");
txtResult.setText(b.toString());
}
/**
* Method declaration
*
* @param s
*/
private void addToRecent(String s) {
for (int i = 0; i < iMaxRecent; i++) {
if (s.equals(sRecent[i])) {
return;
}
}
if (sRecent[iRecent] != null) {
mRecent.remove(iRecent);
}
sRecent[iRecent] = s;
if (s.length() > 43) {
s = s.substring(0, 40) + "...";
}
MenuItem item = new MenuItem(s);
item.setActionCommand("#" + iRecent);
item.addActionListener(this);
mRecent.insert(item, iRecent);
iRecent = (iRecent + 1) % iMaxRecent;
}
/** Method declaration */
private void initGUI() {
Panel pQuery = new Panel();
Panel pCommand = new Panel();
pResult = new Panel();
pQuery.setLayout(new BorderLayout());
pCommand.setLayout(new BorderLayout());
pResult.setLayout(new BorderLayout());
Font fFont = new Font("Dialog", Font.PLAIN, 12);
txtCommand = new TextArea(5, 40);
txtCommand.addKeyListener(this);
txtResult = new TextArea(20, 40);
txtCommand.setFont(fFont);
txtResult.setFont(new Font("Courier", Font.PLAIN, 12));
butExecute = new Button("Execute");
butExecute.addActionListener(this);
pCommand.add("East", butExecute);
pCommand.add("Center", txtCommand);
gResult = new Grid();
setLayout(new BorderLayout());
pResult.add("Center", gResult);
pQuery.add("North", pCommand);
pQuery.add("Center", pResult);
fMain.add("Center", pQuery);
tTree = new Tree();
// (ulrivo): screen with less than 640 width
Dimension d = Toolkit.getDefaultToolkit().getScreenSize();
if (d.width >= 640) {
tTree.setMinimumSize(new Dimension(200, 100));
} else {
tTree.setMinimumSize(new Dimension(80, 100));
}
gResult.setMinimumSize(new Dimension(200, 300));
fMain.add("West", tTree);
doLayout();
fMain.pack();
}
/** Method declaration */
private void refreshTree() {
tTree.removeAll();
try {
int color_table = Color.yellow.getRGB();
int color_column = Color.orange.getRGB();
int color_index = Color.red.getRGB();
tTree.addRow("", dMeta.getURL(), "-", 0);
String usertables[] = {"TABLE"};
ResultSet result = dMeta.getTables(null, null, null, usertables);
Vector tables = new Vector();
// sqlbob@users Added remarks.
Vector remarks = new Vector();
while (result.next()) {
tables.addElement(result.getString(3));
remarks.addElement(result.getString(5));
}
result.close();
for (int i = 0; i < tables.size(); i++) {
String name = (String) tables.elementAt(i);
String key = "tab-" + name + "-";
tTree.addRow(key, name, "+", color_table);
// sqlbob@users Added remarks.
String remark = (String) remarks.elementAt(i);
if ((remark != null) && !remark.trim().equals("")) {
tTree.addRow(key + "r", " " + remark);
}
ResultSet col = dMeta.getColumns(null, null, name, null);
while (col.next()) {
String c = col.getString(4);
String k1 = key + "col-" + c + "-";
tTree.addRow(k1, c, "+", color_column);
String type = col.getString(6);
tTree.addRow(k1 + "t", "Type: " + type);
boolean nullable = col.getInt(11) != DatabaseMetaData.columnNoNulls;
tTree.addRow(k1 + "n", "Nullable: " + nullable);
}
col.close();
tTree.addRow(key + "ind", "Indices", "+", 0);
ResultSet ind = dMeta.getIndexInfo(null, null, name, false, false);
String oldiname = null;
while (ind.next()) {
boolean nonunique = ind.getBoolean(4);
String iname = ind.getString(6);
String k2 = key + "ind-" + iname + "-";
if ((oldiname == null || !oldiname.equals(iname))) {
tTree.addRow(k2, iname, "+", color_index);
tTree.addRow(k2 + "u", "Unique: " + !nonunique);
oldiname = iname;
}
String c = ind.getString(9);
tTree.addRow(k2 + "c-" + c + "-", c);
}
ind.close();
}
tTree.addRow("p", "Properties", "+", 0);
tTree.addRow("pu", "User: "******"pr", "ReadOnly: " + cConn.isReadOnly());
tTree.addRow("pa", "AutoCommit: " + cConn.getAutoCommit());
tTree.addRow("pd", "Driver: " + dMeta.getDriverName());
tTree.addRow("pp", "Product: " + dMeta.getDatabaseProductName());
tTree.addRow("pv", "Version: " + dMeta.getDatabaseProductVersion());
} catch (SQLException e) {
tTree.addRow("", "Error getting metadata:", "-", 0);
tTree.addRow("-", e.getMessage());
tTree.addRow("-", e.getSQLState());
}
tTree.update();
}
}
public class MyLittleDatabase {
private static Connection connection;
private static final String DB_PATH = System.getProperty("user.home") + "/" + "scrollsdb.db";
private static MyLittleDatabase instance;
static {
try {
Class.forName("org.sqlite.JDBC");
} catch (ClassNotFoundException e) {
System.err.println("error while loading the JDBC-driver");
e.printStackTrace();
}
}
public static MyLittleDatabase getInstance() {
if (instance == null) {
instance = new MyLittleDatabase();
}
return instance;
}
private MyLittleDatabase() {
this.initDBConnection();
}
private void initDBConnection() {
try {
if (connection != null) return;
System.out.println("Creating Connection to Database...");
connection = DriverManager.getConnection("jdbc:sqlite:" + DB_PATH);
if (!connection.isClosed()) System.out.println("...Connection established");
} catch (SQLException e) {
throw new RuntimeException(e);
}
Runtime.getRuntime()
.addShutdownHook(
new Thread() {
public void run() {
try {
if (!connection.isClosed() && connection != null) {
connection.close();
if (connection.isClosed()) System.out.println("Connection to Database closed");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
});
String sql =
"create table if not exists players (name string, gold int, shards int, admin int, featuretype int, spectate int, trade int, challenge int, rating int, head int, body int, leg int, armback int, armfront int, idoltype string, idol1 int, idol2 int, idol3 int, idol4 int, idol5 int);";
try {
Statement stmt = connection.createStatement();
stmt.executeUpdate(sql);
stmt.close();
sql = "CREATE INDEX IF NOT EXISTS nameidx ON players (name);";
stmt = connection.createStatement();
stmt.executeUpdate(sql);
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
sql = "create table if not exists cards ( typeId int, owner int, tradeable int, level int);";
try {
Statement stmt = connection.createStatement();
stmt.executeUpdate(sql);
stmt.close();
sql = "CREATE INDEX IF NOT EXISTS cardowneridx ON cards (owner);";
stmt = connection.createStatement();
stmt.executeUpdate(sql);
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
sql =
"create table if not exists decks ( name string, owner int, resources string, valid int, timestamp int, cardids string);";
try {
Statement stmt = connection.createStatement();
stmt.executeUpdate(sql);
stmt.close();
sql = "CREATE INDEX IF NOT EXISTS deckowneridx ON decks (owner);";
stmt = connection.createStatement();
stmt.executeUpdate(sql);
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public synchronized long getFreeIDPlayer() {
// dont use that!
String sql = "SELECT rowid FROM players;";
long i = -1;
try {
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(sql);
long max = 1;
while (rs.next()) {
long m = rs.getLong(1);
if (max <= m) max = m + 1;
}
i = max;
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return i;
}
public long insertPlayer(String name) {
long id = -1;
String sql =
"INSERT INTO players ( name, gold, shards, admin , featuretype , spectate , trade , challenge , rating , head, body, leg, armback, armfront, idoltype, idol1, idol2, idol3, idol4, idol5) "
+ "VALUES ( '"
+ name
+ "', 2000, 0, 0, 0, 1, 1, 1, 1000, 37, 11, 40, 1, 17, 'DEFAULT', 2, 2, 2, 2, 2);";
try {
connection.setAutoCommit(false); // Starts transaction.
Statement stmt = connection.createStatement();
stmt.executeUpdate(sql);
ResultSet rs = stmt.executeQuery("SELECT last_insert_rowid()");
rs.next();
id = rs.getLong(1);
stmt.close();
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
}
if (id != -1) {
addDefaultDecks(id);
}
return id;
}
private void addDefaultDecks(long playerid) {
ArrayList<String> toadd = new ArrayList<String>();
String deck1 =
"{\"deck\":\"Growth Preconstructed\",\"author\":\"Mojang\",\"types\":[56,91,40,100,115,44,100,45,44,49,94,84,89,38,78,75,49,22,42,156,53,156,88,88,84,63,63,18,89,18,17,40,21,21,22,65,91,40,17,26,26,16,16,75,41,115,13,53,114,13]}";
toadd.add(deck1);
deck1 =
"{\"deck\":\"Energy Preconstructed\",\"author\":\"Mojang\",\"types\":[152,27,152,151,143,39,77,2,151,87,96,96,98,98,139,96,141,98,141,141,2,139,130,80,80,24,107,35,68,82,77,24,149,27,118,83,107,24,35,68,1,67,149,67,150,83,87,66,66,32]}";
toadd.add(deck1);
deck1 =
"{\"deck\":\"Order Preconstructed\",\"author\":\"Mojang\",\"types\":[97,124,95,20,125,73,125,127,124,71,86,123,126,54,123,122,120,64,50,50,37,74,97,105,71,122,86,73,126,36,120,99,99,128,128,23,101,95,61,52,124,15,121,52,48,123,127,50,20,23]}";
toadd.add(deck1);
deck1 =
"{\"deck\":\"Decay Preconstructed\",\"author\":\"Mojang\",\"types\":[3,3,59,69,69,102,102,162,163,163,164,165,165,166,170,170,174,175,176,176,176,179,179,179,180,184,184,185,186,187,191,191,191,192,192,192,194,195,195,198,198,198,200,200,201,201,202,202,239,239]}";
toadd.add(deck1);
deck1 =
"{\"deck\":\"EnergySet1and2\",\"author\":\"Mojang\",\"types\":[24,27,35,39,47,66,68,70,79,80,83,85,87,90,96,98,108,110,118,140,141,142,143,147,148,149,150,151,152,153]}";
toadd.add(deck1);
deck1 =
"{\"deck\":\"GrowthSet1and2\",\"author\":\"Mojang\",\"types\":[4,13,16,17,18,29,34,38,40,41,43,44,49,56,60,63,76,84,88,89,91,94,100,112,113,114,115,129,154,189]}";
toadd.add(deck1);
deck1 =
"{\"deck\":\"OrderSet1and2\",\"author\":\"Mojang\",\"types\":[15,19,20,23,36,37,48,52,54,55,57,61,71,73,86,93,97,99,101,103,109,111,119,120,124,125,126,127,128,158]}";
toadd.add(deck1);
deck1 =
"{\"deck\":\"DecaySet1and2\",\"author\":\"Mojang\",\"types\":[59,69,102,131,137,159,162,164,165,168,169,170,171,172,174,176,177,180,181,182,184,185,190,191,194,195,197,198,199,202]}";
toadd.add(deck1);
deck1 =
"{\"deck\":\"Set3\",\"author\":\"Mojang\",\"types\":[1,2,25,32,67,77,81,82,107,130,138,139,145,146,160,21,22,26,30,33,42,45,53,58,65,75,78,104,117,156,50,51,62,64,74,92,95,105,116,121,122,123,155,157,193,3,161,163,166,173,175,179,183,186,187,188,192,196,200,201]}";
toadd.add(deck1);
deck1 =
"{\"deck\":\"Set4\",\"author\":\"Mojang\",\"types\":[135,203,208,211,212,213,214,215,216,217,218,219,221,222,257,134,178,206,209,228,229,230,231,232,234,237,238,240,254,133,204,205,207,210,223,224,225,226,227,235,236,241,256,258,239,242,243,244,245,246,247,248,249,250,251,252,253,255,259]}";
toadd.add(deck1);
deck1 =
"{\"deck\":\"Set5\",\"author\":\"Mojang\",\"types\":[261,289,290,291,292,293,294,295,296,297,302,303,304,307,310,263,273,274,275,280,298,305,306,308,311,312,313,314,315,316,260,264,265,266,267,268,269,270,271,272,276,278,279,309,319,262,277,281,282,283,284,285,286,287,288,299,300,301,317,318]}";
toadd.add(deck1);
deck1 =
"{\"deck\":\"Set6\",\"author\":\"Mojang\",\"types\":[332,342,343,344,345,349,350,351,352,353,362,380,381,382,540,322,339,340,341,347,363,364,365,366,367,368,369,379,383,385,320,333,337,338,346,354,355,356,357,358,359,360,361,384,541,321,334,335,336,348,370,371,372,373,374,375,376,377,378,542]}";
toadd.add(deck1);
// test area targeting!
// deck1 =
// "{\"deck\":\"testing\",\"author\":\"Mojang\",\"types\":[25,246,244,244,245,343,343,343,343,343,343,343]}";
// toadd.add(deck1);
for (String s : toadd) {
addDeckANDCards(s, playerid);
}
}
public void addDeck(String deckname, long playerid, String cardids) {
// TODO: insert correct ressources :D
Long l = System.currentTimeMillis() / 1000L;
String sql =
"INSERT OR IGNORE INTO decks ( name , owner , resources , valid , timestamp , cardids ) "
+ "VALUES ('"
+ deckname
+ "', "
+ playerid
+ ", 'GROWTH', 1,"
+ l.intValue()
+ ",'"
+ cardids
+ "');";
sql +=
" UPDATE decks SET resources = 'GROWTH', valid = 1, timestamp = "
+ l.intValue()
+ ", cardids = '"
+ cardids
+ "'";
sql += " WHERE name ='" + deckname + "' AND owner = " + playerid;
System.out.println(sql);
Statement stmt;
try {
connection.setAutoCommit(false); // Starts transaction.
stmt = connection.createStatement();
stmt.executeUpdate(sql);
stmt.close();
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
private void addDeckANDCards(String deckstring, long playerid) {
JSONObject jo = new JSONObject(deckstring);
String deckname = jo.getString("deck");
JSONArray arrr = jo.getJSONArray("types");
String deckids = "";
ArrayList<Integer> toadd = new ArrayList<Integer>();
int j = 0;
Statement stmt;
try {
connection.setAutoCommit(false); // ONLY FOR addNewCardNoClose
stmt = connection.createStatement(); // ONLY FOR addNewCardNoClose
for (int i = 0; i < arrr.length(); i++) {
int typeid = arrr.getInt(i);
/*
toadd.add(typeid);
j++;
if(j>=100 || i == arrr.length()-1 )
{
ArrayList<Integer> cardids = addNewCards(toadd, playerid);
for(int iii : cardids)
{
if(!deckids.equals(""))deckids += ",";
deckids += iii +"";
System.out.print(iii);
System.out.print(",");
}
toadd.clear();
j=0;
}*/
if (!deckids.equals("")) deckids += ",";
deckids += addNewCardNoClose(typeid, playerid, stmt) + "";
}
stmt.close(); // ONLY FOR addNewCardNoClose
connection.commit(); // ONLY FOR addNewCardNoClose
} catch (SQLException e2) {
// TODO Auto-generated catch block
e2.printStackTrace();
}
// now we have to add the deck
Long l = System.currentTimeMillis() / 1000L;
String sql =
"INSERT INTO decks ( name , owner , resources , valid , timestamp , cardids ) "
+ "VALUES ('"
+ deckname
+ "', "
+ playerid
+ ", 'GROWTH', 1,"
+ l.intValue()
+ ",'"
+ deckids
+ "');";
int deckid = -1;
try {
connection.setAutoCommit(false); // Starts transaction.
stmt = connection.createStatement();
stmt.executeUpdate(sql);
/*ResultSet rs = stmt.executeQuery("SELECT last_insert_rowid()");
rs.next();
deckid = rs.getInt(1);*/
stmt.close();
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
public long addNewCardNoClose(int typeid, long owner, Statement stmt) {
long id = -1;
String sql =
"INSERT INTO cards ( typeId , owner , tradeable , level ) "
+ "VALUES ("
+ typeid
+ ", "
+ owner
+ ", 0, 0);";
try {
stmt.executeUpdate(sql);
ResultSet rs = stmt.executeQuery("SELECT last_insert_rowid()");
rs.next();
id = rs.getLong(1);
} catch (SQLException e) {
e.printStackTrace();
}
return id;
}
public long addNewCard(int typeid, int owner) {
long id = -1;
String sql =
"INSERT INTO cards ( typeId , owner , tradeable , level ) "
+ "VALUES ("
+ typeid
+ ", "
+ owner
+ ", 0, 0);";
try {
/*Statement stmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
stmt.executeUpdate(sql);
ResultSet rs = stmt.getGeneratedKeys();
rs.next();
id = rs.getInt(1);
stmt.close();*/
connection.setAutoCommit(false); // Starts transaction.
Statement stmt = connection.createStatement();
stmt.executeUpdate(sql);
ResultSet rs = stmt.executeQuery("SELECT last_insert_rowid()");
rs.next();
id = rs.getLong(1);
stmt.close();
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
}
return id;
}
public ArrayList<Long> addNewCards(ArrayList<Integer> typeids, int owner) {
// doesnt work properly...
long id = -1;
String sql = "INSERT INTO cards ( typeId , owner , tradeable , level ) VALUES ";
boolean added = false;
for (Integer iii : typeids) {
if (added) sql += ",";
sql += "(" + iii + ", " + owner + ", 0, 0)";
added = true;
}
System.out.println(sql);
ArrayList<Long> cardids = new ArrayList<Long>();
try {
/*Statement stmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
stmt.executeUpdate(sql);
ResultSet rs = stmt.getGeneratedKeys();
rs.next();
id = rs.getInt(1);
stmt.close();*/
connection.setAutoCommit(false); // Starts transaction.
Statement stmt = connection.createStatement();
stmt.executeUpdate(sql);
ResultSet rs = stmt.executeQuery("SELECT last_insert_rowid()");
while (rs.next()) {
id = rs.getLong(1);
cardids.add(id);
}
stmt.close();
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
}
return cardids;
}
public Player getPlayerbyName(String name) {
Player p = new Player();
Statement stmt;
boolean added = false;
try {
stmt = connection.createStatement();
String sql = "SELECT rowid,* FROM players Where name= '" + name + "' ;";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println("in db" + rs.getLong(1) + " " + rs.getString(2));
added = true;
p.profileId = rs.getLong(1);
p.name = rs.getString(2);
p.gold = rs.getInt(3);
p.shards = rs.getInt(4);
p.admin = rs.getInt(5);
p.featureType = rs.getInt(6);
p.spectatePermission = rs.getInt(7);
p.acceptTrades = rs.getInt(8);
p.acceptChallenges = rs.getInt(9);
p.rating = rs.getInt(10);
p.head = rs.getInt(11);
p.body = rs.getInt(12);
p.leg = rs.getInt(13);
p.armBack = rs.getInt(14);
p.armFront = rs.getInt(15);
p.idolType = rs.getString(16);
p.idol1 = rs.getInt(17);
p.idol2 = rs.getInt(18);
p.idol3 = rs.getInt(19);
p.idol4 = rs.getInt(20);
p.idol5 = rs.getInt(21);
break;
}
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if (added == false) {
long id = insertPlayer(name);
p.name = name;
p.profileId = id;
System.out.println("new player was added: " + id);
}
return p;
}
public ArrayList<Deck> getDecksFromPlayerID(long id) {
ArrayList<Deck> ds = new ArrayList<Deck>();
String retval = "";
Statement stmt;
try {
stmt = connection.createStatement();
String sql = "SELECT rowid,* FROM decks Where owner= " + id + ";";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
Deck d = new Deck();
d.deckname = rs.getString(2);
d.playerowner = rs.getLong(3);
d.ressis = rs.getString(4);
d.valid = rs.getInt(5);
d.timestamp = rs.getLong(6);
String cs = rs.getString(7);
for (String c : cs.split(",")) {
try {
d.cardIds.add(Integer.parseInt(c));
} catch (NumberFormatException nfe) {
}
}
ds.add(d);
}
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return ds;
}
public ArrayList<SmallCard> getCardsFromPlayerID(long id) {
ArrayList<SmallCard> ds = new ArrayList<SmallCard>();
String retval = "";
Statement stmt;
try {
stmt = connection.createStatement();
String sql = "SELECT rowid,* FROM cards Where owner= " + id + ";";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
SmallCard d = new SmallCard();
// ( typeId int, owner int, tradeable int, level int)
d.cardid = rs.getLong(1);
d.typeid = rs.getInt(2);
d.owner = rs.getLong(3);
d.tradeable = rs.getInt(4);
d.level = rs.getInt(5);
ds.add(d);
}
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return ds;
}
public ArrayList<Minion> getDeckFromPlayer(String deckname, long playerid, boolean iswhite) {
ArrayList<Minion> deck = new ArrayList<Minion>();
Statement stmt;
ArrayList<SmallCard> cards = new ArrayList<SmallCard>();
try {
stmt = connection.createStatement();
String sql =
"SELECT * FROM decks Where owner= " + playerid + " AND name = '" + deckname + "';";
ResultSet rs = stmt.executeQuery(sql);
String cs = "";
if (rs.next()) {
cs = rs.getString(6);
}
String inlist = "(";
boolean added = false;
for (String c : cs.split(",")) {
if (added) inlist += ",";
inlist += c;
added = true;
}
inlist += ")";
sql = "SELECT rowid,* FROM cards Where owner= " + playerid + " AND rowid IN " + inlist + ";";
rs = stmt.executeQuery(sql);
while (rs.next()) {
SmallCard d = new SmallCard();
// ( typeId int, owner int, tradeable int, level int)
d.cardid = rs.getLong(1);
d.typeid = rs.getInt(2);
d.owner = rs.getLong(3);
d.tradeable = rs.getInt(4);
d.level = rs.getInt(5);
cards.add(d);
}
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
CardDB cdb = CardDB.getInstance();
UColor colw = UColor.black;
if (iswhite) colw = UColor.white;
for (SmallCard sc : cards) {
Minion m = new Minion(cdb.cardId2Card.get(sc.typeid), sc.cardid, colw);
m.lvl = sc.level;
deck.add(m);
}
return deck;
}
// String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
}
//
// --------------------------------------------------------------------------------------------------------------------
// Class: RFFieldSQLDatabase
// Description: RFFieldSQLDatabase interfaces SQL Database, supplies methods to access
// data and passes JSON to/from calling classes.
//
// --------------------------------------------------------------------------------------------------------------------
public class RFFieldSQLDatabase {
// Define class members
private Connection conn = null; // MySql Database connection
private static final String APPLICATION_NAME = "ECEN689Project1"; // Set the Application Name
// Directory to store user credentials for this
// application.
private static final java.io.File DATA_STORE_DIR =
new java.io.File(System.getProperty("user.dir"), ".store/ecen689project1");
private static FileDataStoreFactory
DATA_STORE_FACTORY; // Global instance of the {@link FileDataStoreFactory}.
private static HttpTransport HTTP_TRANSPORT; // Global instance of the HTTP transport
// Global instance of the JSON factory.
private static final JsonFactory JSON_FACTORY = JacksonFactory.getDefaultInstance();
private static Fusiontables FusionTables; // Global instance of the Fusion Tables
// -----------------------------------------------------------------------------------------------------------------
// Create new instances of HTTP_TRANSPORT
static //
{ // and DATA_STORE_FACTORY
try //
{ //
HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
DATA_STORE_FACTORY = new FileDataStoreFactory(DATA_STORE_DIR);
} //
catch (Throwable t) //
{ //
t.printStackTrace(); //
System.exit(1); //
} //
} //
// ----------------------------------------------------------------------------------------------------------------
// Method: authorize
// Inputs: none
// Outputs: Credentials
// Description: Creates an authorized credential object, will throw IO exception
// ----------------------------------------------------------------------------------------------------------------
private static Credential authorize() throws IOException {
// Load client secrets (from JSON file)
InputStream in = Main.class.getResourceAsStream("/client_secret_ecen689project1.json");
GoogleClientSecrets clientSecrets =
GoogleClientSecrets.load(JSON_FACTORY, new InputStreamReader(in));
// Build flow and trigger user authorization request
GoogleAuthorizationCodeFlow flow =
new GoogleAuthorizationCodeFlow.Builder(
HTTP_TRANSPORT,
JSON_FACTORY,
clientSecrets,
Collections.singleton(FusiontablesScopes.FUSIONTABLES))
.setDataStoreFactory(DATA_STORE_FACTORY)
.build();
// Authorize and get the credential
Credential credential =
new AuthorizationCodeInstalledApp(flow, new LocalServerReceiver()).authorize("user");
System.out.println("Credentials saved to " + DATA_STORE_DIR.getAbsolutePath());
return credential; // Return the credential
}
// ----------------------------------------------------------------------------------------------------------------
// Method: getConnection
// Inputs: database URL, username, password
// Outputs: connection to DB
// Description: Connects to MySql database
// ----------------------------------------------------------------------------------------------------------------
private static Connection getConnection(String dbURL, String user, String password)
throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver"); // Setup for the MySql JDBC Driver
Properties props = new Properties(); // Build the properties
props.put("user", user);
props.put("password", password);
props.put("autoReconnect", "true"); // Enabled auto-reconnection
return DriverManager.getConnection(dbURL, props); // Return the connection to the database
}
// ----------------------------------------------------------------------------------------------------------------
// Method: Class Construction
// Inputs: none
// Outputs: none
// Description: Class Construction
//
// ----------------------------------------------------------------------------------------------------------------
public RFFieldSQLDatabase() {}
// ----------------------------------------------------------------------------------------------------------------
// Method: ConnectToDatabase
// Inputs: none
// Outputs: Success = TRUE / Failure = FALSE
// Description: Establishes a connection to the database
// ----------------------------------------------------------------------------------------------------------------
public boolean ConnectToDatabase(String host_address, boolean fusion_tables) {
boolean status; // Return status (success / failure)
if (fusion_tables == true) // Only create a connection to FT if the
{ // flag is set to true
try {
// Google Fusion Tables Authorization
Credential credential = authorize(); // Client authorization
// Set up global FusionTables instance
FusionTables =
new Fusiontables.Builder(HTTP_TRANSPORT, JSON_FACTORY, credential)
.setApplicationName(APPLICATION_NAME)
.build();
} //
catch (IOException e) // Print out exception messages
{ //
System.err.println(e.getMessage()); //
} //
catch (Throwable t) //
{ //
t.printStackTrace(); //
} //
}
try // Try and connect to the MySQL Database
{ // Use ECEN_RF_Field Database
System.out.println("Attempting Connection"); // Print out connection success
// conn = getConnection("jdbc:mysql://69.5.199.164/ECEN_RF_Fields", "ecen689",
// "ecen689$2016");
// conn = getConnection("jdbc:mysql://10.202.102.173/ECEN_RF_Fields", "ecen689",
// "ecen689$2016");
conn =
getConnection(
"jdbc:mysql://" + host_address + "/ECEN_RF_Fields", "ecen689", "ecen689$2016");
System.out.println("Opened database successfully"); // Print out connection success
status = true; // Success
} //
catch (Exception e) // Exception processing:
{ //
System.err.println(
"ConnectToDatabase: " + e.getMessage()); // Print the exception data and exit
status = false; // Failure
} //
return status; // Return status
}
// ----------------------------------------------------------------------------------------------------------------
// Method: DisconnectDatabase
// Inputs: none
// Outputs: Success = TRUE / Failure = FALSE
// Description: Disconnects the connection to the database
// ----------------------------------------------------------------------------------------------------------------
public boolean DisconnectDatabase() {
boolean status; // Return status (success / failure)
try // Try and connect to the MySQL Database
{ //
if (conn != null) conn.close(); // Close the connection if defined
System.out.println("Database connection closed"); // Print out status
status = true; // Success
} //
catch (Exception e) // Exception processing:
{ // Print the exception data and exit
System.err.println("DisconnectDatabase: " + e.getMessage());
status = false; // Failure
} //
return status; // Return status
}
// ----------------------------------------------------------------------------------------------------------------
// Method: GetTableId
// Inputs: none
// Outputs: none
// Description: Get Table ID from Table Nam
// ----------------------------------------------------------------------------------------------------------------
private static String GetTableId(String tableName) throws IOException {
String tid = null; // Set default return
System.out.println("Get Table ID: " + tableName); // Print header
// Get the table list
Fusiontables.Table.List listTables = FusionTables.table().list();
TableList tablelist = listTables.execute(); //
// If there are no tables, print that info
if (tablelist.getItems() == null || tablelist.getItems().isEmpty()) { //
System.out.println("No tables found!"); //
} //
else // Else, loop through tables, find match
{ // If it matches then save the table ID
for (Table table : tablelist.getItems()) //
{ //
if (table.getName().equals(tableName)) tid = table.getTableId();
} // Printout the results of that
System.out.println(tableName + " - tableId: " + tid); //
} //
return tid; // Return the table ID
}
// ----------------------------------------------------------------------------------------------------------------
// Method: AddNewEntry
// Inputs: RF Data Entry (JSON)
// Outputs: Success = TRUE / Failure = FALSE
// Description: Insert new data to table (executes SQL command)
// ----------------------------------------------------------------------------------------------------------------
public boolean AddNewEntry(String json, boolean fusion_tables) {
boolean status; // Return status (success / failure)
try // Try to get JSON, and save data to database
{ //
Gson gson = new GsonBuilder().create(); // Create Gson builder
RFData RFMember = gson.fromJson(json, RFData.class); // Convert from JSON to RFData
if (RFMember.XbeeID != -1) // If not default then save data
{
// Print debug information to port
System.out.println(
"Insert New RF Data into Table - XbeeID: "
+ RFMember.XbeeID
+ ", RSSI: "
+ RFMember.RSSI);
SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String sql_string; // Build up SQL string
sql_string = "INSERT INTO RF_Fields ("; // Insert SQL statement, Table: RF_Fields
sql_string += "intXbeeID,"; // Field: intXbeeID
sql_string += "intDeviceID,"; // Field: intDeviceID
sql_string += "fltRSSI,"; // Field: fltRSSI
sql_string += "fltLatitude,"; // Field: fltLatitude
sql_string += "fltLongitude,"; // Field: fltLongitude
sql_string += "fltYaw,"; // Field: fltYaw
sql_string += "fltPitch,"; // Field: fltPitch
sql_string += "fltRoll,"; // Field: fltRoll
sql_string += "dtSampleDate) "; // Field: dtSampleDate
sql_string += "VALUES ("; // Values indetifier
sql_string += RFMember.XbeeID + ","; // Value: XbeeID
sql_string += RFMember.DeviceID + ","; // Value: DeviceID
sql_string += RFMember.RSSI + ","; // Value: RSSI
sql_string += RFMember.Latitude + ","; // Value: Latitude
sql_string += RFMember.Longitude + ","; // Value: Longitude
sql_string += RFMember.Yaw + ","; // Value: Yaw
sql_string += RFMember.Pitch + ","; // Value: Pitch
sql_string += RFMember.Roll + ","; // Value: Roll
sql_string += "'" + ft.format(RFMember.SampleDate) + "')";
System.out.println("SQL: " + sql_string); // Debug print the SQL statement
//
Statement stmt = conn.createStatement(); // Build SQL statement
stmt.execute(sql_string); // Execute the SQL statement
stmt.close(); // Close the statement
status = true; // Success
if (fusion_tables) // Only add to the fusion tables if the flag
{ // is set
// Add to fusion table
String tableId = GetTableId("RF Field Data"); // Get the "Customer Data" Table
sql_string = "INSERT INTO " + tableId + " ("; // Insert SQL statement, Table: Table ID
sql_string += "XbeeID,"; // Field: intXbeeID
sql_string += "DeviceID,"; // Field: intDeviceID
sql_string += "RSSI,"; // Field: fltRSSI
sql_string += "Location,"; // Field: fltLatitude
sql_string += "Longitude,"; // Field: fltLongitude
sql_string += "Yaw,"; // Field: fltYaw
sql_string += "Pitch,"; // Field: fltPitch
sql_string += "Roll,"; // Field: fltRoll
sql_string += "SampleDate) "; // Field: dtSampleDate
sql_string += "VALUES ("; // Values indetifier
sql_string += RFMember.XbeeID + ","; // Value: XbeeID
sql_string += RFMember.DeviceID + ","; // Value: DeviceID
sql_string += RFMember.RSSI + ","; // Value: RSSI
sql_string += RFMember.Latitude + ","; // Value: Latitude
sql_string += RFMember.Longitude + ","; // Value: Longitude
sql_string += RFMember.Yaw + ","; // Value: Yaw
sql_string += RFMember.Pitch + ","; // Value: Pitch
sql_string += RFMember.Roll + ","; // Value: Roll
sql_string += "'" + ft.format(RFMember.SampleDate) + "')";
Sql sql = FusionTables.query().sql(sql_string); // Build Fusion Query
// Try and execute the SQL command
try //
{ //
sql.executeAndDownloadTo(System.out); // Execute command, stream to the system.out
} //
catch (IllegalArgumentException e) //
{ //
} //
}
} //
else //
{ //
System.err.println("AddNewEntry: Invalid JSON data"); // Print the exception data and exit
status = false; // Failure, invalid JSON or data
}
} //
catch (Exception e) // Exception processing:
{ //
System.err.println("AddNewEntry: " + e.getMessage()); // Print the exception data and exit
status = false; // Failure
} //
return status; // Return status
}
// ----------------------------------------------------------------------------------------------------------------
// Method: ReadRecords
// Inputs: Result Set and Array List
// Outputs: (by ref) Array List
// Description: Read the Records, Fill Array List with JSON data
// ----------------------------------------------------------------------------------------------------------------
public void ReadRecords(ResultSet rs, ArrayList records) {
Gson gson = new GsonBuilder().create(); // Create Gson builder
try // Try to get JSON, and save data to database
{ //
while (rs.next()) // Loop through all the returned records, until EOF
{ //
RFData RFMember = new RFData(); // Create new RF data
// Capture the data from the record set
RFMember.SampleNumber = rs.getInt("intSampleNum"); // Get sample #
RFMember.XbeeID = rs.getInt("intXbeeID"); // Get Xbee ID
RFMember.DeviceID = rs.getInt("intDeviceID"); // Get Device ID
RFMember.RSSI = rs.getFloat("fltRSSI"); // Get RSSI
RFMember.Latitude = rs.getFloat("fltLatitude"); // Get Latitude
RFMember.Longitude = rs.getFloat("fltLongitude"); // Get Longitude
RFMember.Yaw = rs.getFloat("fltYaw"); // Get Yaw
RFMember.Pitch = rs.getFloat("fltPitch"); // Get Pitch
RFMember.Roll = rs.getFloat("fltRoll"); // Get Roll
RFMember.SampleDate = rs.getTimestamp("dtSampleDate"); // Get Sample Date
// Print debug information to port
SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
System.out.println(
"Got Record: # "
+ RFMember.SampleNumber
+ " - XbeeID: "
+ RFMember.XbeeID
+ ", RSSI: "
+ RFMember.RSSI
+ " Date/Time: "
+ ft.format(RFMember.SampleDate));
String json_record = gson.toJson(RFMember); // Get JSON data of this record
records.add(json_record); // Add the JSON string to the record
}
} catch (Exception e) {
System.err.println("ReadRecords: " + e.getMessage()); // Print the exception data and exit
}
}
// ----------------------------------------------------------------------------------------------------------------
// Method: ListDataByEntryID
// Inputs: SampleNum
// Outputs: RF Data Entry (JSON)
// Description: Get RF Data Entry
// ----------------------------------------------------------------------------------------------------------------
public String ListDataByEntryID(int SampleNum) {
String json = ""; // Return JSON entry
try // Try to get JSON, and save data to database
{ //
// Print debug information to port
System.out.println("Select RF Data from Table - Sample #: " + SampleNum);
String sql_string; // Build up SQL string
sql_string = "SELECT "; // Select SQL statement
sql_string += "intSampleNum,"; // Field: intSampleNum
sql_string += "intXbeeID,"; // Field: intXbeeID
sql_string += "intDeviceID,"; // Field: intDeviceID
sql_string += "fltRSSI,"; // Field: fltRSSI
sql_string += "fltLatitude,"; // Field: fltLatitude
sql_string += "fltLongitude,"; // Field: fltLongitude
sql_string += "fltYaw,"; // Field: fltYaw
sql_string += "fltPitch,"; // Field: fltPitch
sql_string += "fltRoll,"; // Field: fltRoll
sql_string += "dtSampleDate "; // Field: dtSampleDate
sql_string += "FROM RF_Fields "; // Table: RF_Fields
sql_string += "WHERE "; // Where statement
sql_string += "intSampleNum = "; // Field on Where and condition
sql_string += SampleNum; // Condition value
System.out.println("SQL: " + sql_string); // Debug print the SQL statement
Statement stmt = conn.createStatement(); // Build SQL statement
ResultSet rs = stmt.executeQuery(sql_string); // Execute the SQL statement as a query
while (rs.next()) // Loop through all the returned records, until EOF
{ // However, there should only be one return record
Gson gson = new GsonBuilder().create(); // Create Gson builder
RFData RFMember = new RFData(); // Create new RF data
// Capture the data from the record set
RFMember.SampleNumber = rs.getInt("intSampleNum"); // Get sample #
RFMember.XbeeID = rs.getInt("intXbeeID"); // Get Xbee ID
RFMember.DeviceID = rs.getInt("intDeviceID"); // Get Device ID
RFMember.RSSI = rs.getFloat("fltRSSI"); // Get RSSI
RFMember.Latitude = rs.getFloat("fltLatitude"); // Get Latitude
RFMember.Longitude = rs.getFloat("fltLongitude"); // Get Longitude
RFMember.Yaw = rs.getFloat("fltYaw"); // Get Yaw
RFMember.Pitch = rs.getFloat("fltPitch"); // Get Pitch
RFMember.Roll = rs.getFloat("fltRoll"); // Get Roll
RFMember.SampleDate = rs.getTimestamp("dtSampleDate"); // Get Sample Date
// Print debug information to port
SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
System.out.println(
"Got Record: # "
+ RFMember.SampleNumber
+ " - XbeeID: "
+ RFMember.XbeeID
+ ", RSSI: "
+ RFMember.RSSI
+ " Date/Time: "
+ ft.format(RFMember.SampleDate));
json = gson.toJson(RFMember); // Get JSON data from this record
}
rs.close(); // Close the record set
stmt.close(); // Close the statement
if (json == "") //
{ //
System.err.println("No Record Found!"); // Print the fact that no record was found
}
} //
catch (Exception e) // Exception processing:
{ //
System.err.println(
"ListDataByEntryID: " + e.getMessage()); // Print the exception data and exit
} //
return json; // Return JSON string
}
// ----------------------------------------------------------------------------------------------------------------
// Method: ListDataByDevice
// Inputs: DeviceID
// Outputs: RF Data Entry (JSON) Collection
// Description: Get RF Data Entries
// ----------------------------------------------------------------------------------------------------------------
public String ListDataByDevice(int DeviceID) {
String json = ""; // Return JSON entry
try // Try to get JSON, and save data to database
{ //
// Print debug information to port
Gson gson = new GsonBuilder().create(); // Create Gson builder
System.out.println("Select RF Data from Table - Device ID: " + DeviceID);
String sql_string; // Build up SQL string
sql_string = "SELECT "; // Select SQL statement
sql_string += "intSampleNum,"; // Field: intSampleNum
sql_string += "intXbeeID,"; // Field: intXbeeID
sql_string += "intDeviceID,"; // Field: intDeviceID
sql_string += "fltRSSI,"; // Field: fltRSSI
sql_string += "fltLatitude,"; // Field: fltLatitude
sql_string += "fltLongitude,"; // Field: fltLongitude
sql_string += "fltYaw,"; // Field: fltYaw
sql_string += "fltPitch,"; // Field: fltPitch
sql_string += "fltRoll,"; // Field: fltRoll
sql_string += "dtSampleDate "; // Field: dtSampleDate
sql_string += "FROM RF_Fields "; // Table: RF_Fields
sql_string += "WHERE "; // Where statement
sql_string += "intDeviceID = "; // Field on Where and condition
sql_string += DeviceID; // Condition value
System.out.println("SQL: " + sql_string); // Debug print the SQL statement
Statement stmt = conn.createStatement(); // Build SQL statement
ResultSet rs = stmt.executeQuery(sql_string); // Execute the SQL statement as a query
ArrayList records = new ArrayList(); // Build the Array List
ReadRecords(rs, records); // Read the records
json = gson.toJson(records); // Get JSON data from this record
rs.close(); // Close the record set
stmt.close(); // Close the statement
if (records.isEmpty() == true) // If empty then print out no records found
{ //
System.err.println("No Records Found!"); // Print the fact that no record was found
} //
} //
catch (Exception e) // Exception processing:
{ //
System.err.println(
"ListDataByDevice: " + e.getMessage()); // Print the exception data and exit
} //
return json; // Return JSON string
}
// ----------------------------------------------------------------------------------------------------------------
// Method: ListDataByRSSI
// Inputs: RSSI, GTE
// Outputs: RF Data Entry (JSON) Collection
// Description: Get RF Data Entries
// ----------------------------------------------------------------------------------------------------------------
public String ListDataByRSSI(float RSSI, boolean GTE) {
String json = ""; // Return JSON entry
try // Try to get JSON, and save data to database
{ //
Gson gson = new GsonBuilder().create(); // Create Gson builder
// Print debug information to port
if (GTE) System.out.println("Select RF Data from Table - RSSI >= " + RSSI);
else System.out.println("Select RF Data from Table - RSSI < " + RSSI);
String sql_string; // Build up SQL string
sql_string = "SELECT "; // Select SQL statement
sql_string += "intSampleNum,"; // Field: intSampleNum
sql_string += "intXbeeID,"; // Field: intXbeeID
sql_string += "intDeviceID,"; // Field: intDeviceID
sql_string += "fltRSSI,"; // Field: fltRSSI
sql_string += "fltLatitude,"; // Field: fltLatitude
sql_string += "fltLongitude,"; // Field: fltLongitude
sql_string += "fltYaw,"; // Field: fltYaw
sql_string += "fltPitch,"; // Field: fltPitch
sql_string += "fltRoll,"; // Field: fltRoll
sql_string += "dtSampleDate "; // Field: dtSampleDate
sql_string += "FROM RF_Fields "; // Table: RF_Fields
sql_string += "WHERE "; // Where statement
if (GTE) // Change condition based upon direction (GTE)
sql_string += "fltRSSI >= "; // Field on Where and condition
else sql_string += "fltRSSI < "; // Field on Where and condition
sql_string += RSSI; // Condition value
System.out.println("SQL: " + sql_string); // Debug print the SQL statement
Statement stmt = conn.createStatement(); // Build SQL statement
ResultSet rs = stmt.executeQuery(sql_string); // Execute the SQL statement as a query
ArrayList records = new ArrayList(); // Build the Array List
ReadRecords(rs, records); // Read the records
json = gson.toJson(records); // Get JSON data from this record
rs.close(); // Close the record set
stmt.close(); // Close the statement
if (records.isEmpty() == true) // If empty then print out no records found
{ //
System.err.println("No Records Found!"); // Print the fact that no record was found
} //
} //
catch (Exception e) // Exception processing:
{ //
System.err.println("ListDataByRSSI: " + e.getMessage()); // Print the exception data and exit
} //
return json; // Return JSON string
}
// ----------------------------------------------------------------------------------------------------------------
// Method: ListDataByGeoArea
// Inputs: Start Latitude, Start Longitude, End Latitude, End Longitude (create
// rectangle)
// Outputs: RF Data Entry (JSON) Collection
// Description: Get RF Data Entries
// ----------------------------------------------------------------------------------------------------------------
public String ListDataByGeoArea(
float StartLatitude, float StartLongitude, float EndLatitude, float EndLongitude) {
String json = ""; // Return JSON entry
try // Try to get JSON, and save data to database
{ //
Gson gson = new GsonBuilder().create(); // Create Gson builder
// Print debug information to port
System.out.println("Select RF Data from Table - GeoLocation");
String sql_string; // Build up SQL string
sql_string = "SELECT "; // Select SQL statement
sql_string += "intSampleNum,"; // Field: intSampleNum
sql_string += "intXbeeID,"; // Field: intXbeeID
sql_string += "intDeviceID,"; // Field: intDeviceID
sql_string += "fltRSSI,"; // Field: fltRSSI
sql_string += "fltLatitude,"; // Field: fltLatitude
sql_string += "fltLongitude,"; // Field: fltLongitude
sql_string += "fltYaw,"; // Field: fltYaw
sql_string += "fltPitch,"; // Field: fltPitch
sql_string += "fltRoll,"; // Field: fltRoll
sql_string += "dtSampleDate "; // Field: dtSampleDate
sql_string += "FROM RF_Fields "; // Table: RF_Fields
// If, 0,0,0,0 return all records
if ((StartLatitude != 0) | (StartLongitude != 0) | (EndLatitude != 0) | (EndLongitude != 0)) {
sql_string += "WHERE ("; // Where statement
sql_string += "fltLatitude >= " + StartLatitude + " AND "; // Field on Where and condition
sql_string += "fltLatitude >= " + StartLongitude + " AND "; // Field on Where and condition
sql_string += "fltLatitude <= " + EndLatitude + " AND "; // Field on Where and condition
sql_string += "fltLongitude <= " + EndLongitude + ")"; // Field on Where and condition
}
System.out.println("SQL: " + sql_string); // Debug print the SQL statement
Statement stmt = conn.createStatement(); // Build SQL statement
ResultSet rs = stmt.executeQuery(sql_string); // Execute the SQL statement as a query
ArrayList records = new ArrayList(); // Build the Array List
ReadRecords(rs, records); // Read the records
json = gson.toJson(records); // Get JSON data from this record
rs.close(); // Close the record set
stmt.close(); // Close the statement
if (records.isEmpty() == true) // If empty then print out no records found
{ //
System.err.println("No Records Found!"); // Print the fact that no record was found
} //
} //
catch (Exception e) // Exception processing:
{ //
System.err.println(
"ListDataByGeoArea: " + e.getMessage()); // Print the exception data and exit
} //
return json; // Return JSON string
}
}
public static void main(String args[]) throws Exception {
if ((System.getProperty("java.vm.name") != null)
&& System.getProperty("java.vm.name").equals("J9")) jvm = jvm.getJvm("j9_13");
else jvm = jvm.getJvm("currentjvm"); // ensure compatibility
vCmd = jvm.getCommandLine();
try {
bos = new BufferedOutputStream(System.out, 1024);
System.out.println("Start testProperties to test property priority");
/**
* ********************************************************** Test port setting priorty
* **********************************************************
*/
// derby.drda.portNumber set in derby.properties to 1528
System.out.println("Testing derby.properties Port 1528 ");
Properties derbyProperties = new Properties();
derbyProperties.put("derby.drda.portNumber", "1528");
FileOutputStream propFile = new FileOutputStream("derby.properties");
derbyProperties.store(propFile, "testing derby.properties");
propFile.close();
// test start no parameters - Pickup 1528 from derby.properties
derbyServerCmd("start", null);
waitForStart("1528", 60000);
System.out.println("Successfully Connected");
// shutdown - also picks up from derby.properties
derbyServerCmd("shutdown", null);
System.out.println("Testing System properties Port 1529 ");
// test start with system property. Overrides derby.properties
derbyServerCmd("start", "-Dderby.drda.portNumber=1529");
waitForStart("1529", 60000);
System.out.println("Successfully Connected");
// shutdown - also picks up from System Properties
derbyServerCmd("shutdown", "1529");
System.out.println("Testing command line option. Port 1530");
derbyServerCmd("start", "1530");
waitForStart("1530", 60000);
System.out.println("Successfully Connected");
// shutdown - with command line option
derbyServerCmd("shutdown", "1530");
/**
* ******************************************************************** Test start server
* specifying system properties without values
* *******************************************************************
*/
System.out.println("Testing start server by specifying system properties without values");
System.out.println("First shutdown server started on default port by the test harness");
// Shutdown the server started by test
derbyServerCmd("shutdown", "1527");
execCmdDumpResults(startServerCmd, false);
waitForStart("1527", 60000);
// check that default properties are used
listProperties("1527");
// Test trace and logconnections commands
execCmdDumpResults(cmdTraceOn, true);
execCmdDumpResults(cmdLogconnectionsOn, true);
listProperties("1527");
execCmdDumpResults(cmdTraceOff, true);
listProperties("1527");
derbyServerCmd("shutdown", "1527");
// Test error conditions in command-line
execCmdDumpResults(cmdWithoutArgs, true);
execCmdDumpResults(cmdUnknown, true);
execCmdDumpResults(cmdWithWrongArgNum, true);
System.out.println("End test");
bos.close();
} catch (Exception e) {
e.printStackTrace();
// If something went wrong,
// make sure all these servers are shutdown
try {
derbyServerCmd("shutdown", "1527");
} catch (Exception se) {
}
try {
derbyServerCmd("shutdown", "1528");
} catch (Exception se) {
}
try {
derbyServerCmd("shutdown", "1529");
} catch (Exception se) {
}
try {
derbyServerCmd("shutdown", "1530");
} catch (Exception se) {
}
} finally {
try {
File fileToDelete = new File("derby.properties");
fileToDelete.delete();
} catch (Exception e) {
e.printStackTrace();
}
}
}
static void initJDBC() {
try {
// load the ini file
Properties ini = new Properties();
ini.load(new FileInputStream(System.getProperty("prop")));
// display the values we need
System.out.println("driver=" + ini.getProperty("driver"));
System.out.println("conn=" + ini.getProperty("conn"));
System.out.println("user="******"user"));
System.out.println("password=******");
name = ini.getProperty("name");
// Register jdbcDriver
Class.forName(ini.getProperty("driver"));
// make connection
conn =
DriverManager.getConnection(
ini.getProperty("conn"), ini.getProperty("user"), ini.getProperty("password"));
conn.setAutoCommit(false);
// Create Statement
stmt = conn.createStatement();
distPrepStmt =
conn.prepareStatement(
"INSERT INTO district "
+ " (d_id, d_w_id, d_ytd, d_tax, d_next_o_id, d_name, d_street_1, d_street_2, d_city, d_state, d_zip) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
itemPrepStmt =
conn.prepareStatement(
"INSERT INTO item "
+ " (i_id, i_name, i_price, i_data, i_im_id) "
+ "VALUES (?, ?, ?, ?, ?)");
custPrepStmt =
conn.prepareStatement(
"INSERT INTO customer "
+ " (c_id, c_d_id, c_w_id, "
+ "c_discount, c_credit, c_last, c_first, c_credit_lim, "
+ "c_balance, c_ytd_payment, c_payment_cnt, c_delivery_cnt, "
+ "c_street_1, c_street_2, c_city, c_state, c_zip, "
+ "c_phone, c_since, c_middle, c_data) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
histPrepStmt =
conn.prepareStatement(
"INSERT INTO history "
+ " (h_c_id, h_c_d_id, h_c_w_id, "
+ "h_d_id, h_w_id, "
+ "h_date, h_amount, h_data) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
ordrPrepStmt =
conn.prepareStatement(
"INSERT INTO oorder "
+ " (o_id, o_w_id, o_d_id, o_c_id, "
+ "o_carrier_id, o_ol_cnt, o_all_local, o_entry_d) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
orlnPrepStmt =
conn.prepareStatement(
"INSERT INTO order_line "
+ " (ol_w_id, ol_d_id, ol_o_id, "
+ "ol_number, ol_i_id, ol_delivery_d, "
+ "ol_amount, ol_supply_w_id, ol_quantity, ol_dist_info) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
nworPrepStmt =
conn.prepareStatement(
"INSERT INTO new_order " + " (no_w_id, no_d_id, no_o_id) " + "VALUES (?, ?, ?)");
stckPrepStmt =
conn.prepareStatement(
"INSERT INTO stock "
+ " (s_i_id, s_w_id, s_quantity, s_ytd, s_order_cnt, s_remote_cnt, s_data, "
+ "s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, "
+ "s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
whsePrepStmt =
conn.prepareStatement(
"INSERT INTO warehouse "
+ " (w_id, w_ytd, w_tax, w_name, w_street_1, w_street_2, w_city, w_state, w_zip) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
} catch (SQLException se) {
System.out.println(se.getMessage());
transRollback();
} catch (Exception e) {
e.printStackTrace();
transRollback();
} // end try
} // end initJDBC()
private void createFile() {
fileName = System.getProperty("user.dir") + System.getProperty("file.separator") + fileName;
f = new File(fileName);
}
/**
* Swing Tool for manageing a JDBC database.
*
* <p>
*
* <pre>
* Usage: java DatabaseManagerSwing [-options]
* where options include:
* -driver <classname> jdbc driver class
* -url <name> jdbc url
* -user <name> username used for connection
* -password <password> password for this user
* -dir <path> default directory
* -script <file> reads from script file
* </pre>
*
* @version 1.7.0
*/
public class DatabaseManagerSwing extends JApplet
implements ActionListener, WindowListener, KeyListener {
static final String NL = System.getProperty("line.separator");
static int iMaxRecent = 24;
Connection cConn;
DatabaseMetaData dMeta;
Statement sStatement;
JMenu mRecent;
String sRecent[];
int iRecent;
JTextArea txtCommand;
JScrollPane txtCommandScroll;
JButton butExecute;
JTree tTree;
JScrollPane tScrollPane;
DefaultTreeModel treeModel;
DefaultMutableTreeNode rootNode;
JPanel pResult;
long lTime;
int iResult; // 0: grid; 1: text
GridSwing gResult;
JTable gResultTable;
JScrollPane gScrollPane;
JTextArea txtResult;
JScrollPane txtResultScroll;
JSplitPane nsSplitPane; // Contains query over results
JSplitPane ewSplitPane; // Contains tree beside nsSplitPane
boolean bHelp;
JFrame fMain;
static boolean bMustExit;
String ifHuge = "";
JToolBar jtoolbar;
// (ulrivo): variables set by arguments from the commandline
static String defDriver = "org.hsqldb.jdbcDriver";
static String defURL = "jdbc:hsqldb:.";
static String defUser = "******";
static String defPassword = "";
static String defScript;
static String defDirectory;
public void init() {
DatabaseManagerSwing m = new DatabaseManagerSwing();
m.main();
try {
m.connect(ConnectionDialogSwing.createConnection(defDriver, defURL, defUser, defPassword));
m.insertTestData();
m.refreshTree();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String arg[]) {
System.getProperties().put("sun.java2d.noddraw", "true");
// (ulrivo): read all arguments from the command line
String lowerArg;
boolean autoConnect = false;
for (int i = 0; i < arg.length; i++) {
lowerArg = arg[i].toLowerCase();
i++;
bMustExit = true;
if (lowerArg.equals("-driver")) {
defDriver = arg[i];
autoConnect = true;
} else if (lowerArg.equals("-url")) {
defURL = arg[i];
autoConnect = true;
} else if (lowerArg.equals("-user")) {
defUser = arg[i];
autoConnect = true;
} else if (lowerArg.equals("-password")) {
defPassword = arg[i];
autoConnect = true;
} else if (lowerArg.equals("-dir")) {
defDirectory = arg[i];
} else if (lowerArg.equals("-script")) {
defScript = arg[i];
} else if (lowerArg.equals("-noexit")) {
bMustExit = false;
i--;
} else {
showUsage();
return;
}
}
DatabaseManagerSwing m = new DatabaseManagerSwing();
m.main();
Connection c = null;
try {
if (autoConnect) {
c = ConnectionDialogSwing.createConnection(defDriver, defURL, defUser, defPassword);
} else {
c = ConnectionDialogSwing.createConnection(m.fMain, "Connect");
}
} catch (Exception e) {
e.printStackTrace();
}
if (c == null) {
return;
}
m.connect(c);
}
private void connect(Connection c) {
if (c == null) {
return;
}
if (cConn != null) {
try {
cConn.close();
} catch (SQLException e) {
}
}
cConn = c;
try {
dMeta = cConn.getMetaData();
sStatement = cConn.createStatement();
refreshTree();
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void showUsage() {
System.out.println(
"Usage: java DatabaseManagerSwing [-options]\n"
+ "where options include:\n"
+ " -driver <classname> jdbc driver class\n"
+ " -url <name> jdbc url\n"
+ " -user <name> username used for connection\n"
+ " -password <password> password for this user\n"
+ " -dir <path> default directory\n"
+ " -script <file> reads from script file\n"
+ " -noexit do not call system.exit()");
}
private void insertTestData() {
try {
DatabaseManagerCommon.createTestTables(sStatement);
refreshTree();
txtCommand.setText(DatabaseManagerCommon.createTestData(sStatement));
refreshTree();
for (int i = 0; i < DatabaseManagerCommon.testDataSql.length; i++) {
addToRecent(DatabaseManagerCommon.testDataSql[i]);
}
execute();
} catch (SQLException e) {
e.printStackTrace();
}
}
void main() {
CommonSwing.setDefaultColor();
fMain = new JFrame("HSQL Database Manager");
// (ulrivo): An actual icon.
fMain.getContentPane().add(createToolBar(), "North");
fMain.setIconImage(CommonSwing.getIcon());
fMain.addWindowListener(this);
JMenuBar bar = new JMenuBar();
// used shortcuts: CERGTSIUDOLM
String fitems[] = {
"-Connect...", "--", "-Open Script...", "-Save Script...", "-Save Result...", "--", "-Exit"
};
addMenu(bar, "File", fitems);
String vitems[] = {"RRefresh Tree", "--", "GResults in Grid", "TResults in Text"};
addMenu(bar, "View", vitems);
String sitems[] = {
"SSELECT",
"IINSERT",
"UUPDATE",
"DDELETE",
"---",
"-CREATE TABLE",
"-DROP TABLE",
"-CREATE INDEX",
"-DROP INDEX",
"--",
"-CHECKPOINT",
"-SCRIPT",
"-SET",
"-SHUTDOWN",
"--",
"-Test Script"
};
addMenu(bar, "Command", sitems);
mRecent = new JMenu("Recent");
bar.add(mRecent);
String soptions[] = {
"-AutoCommit on",
"-AutoCommit off",
"OCommit",
"LRollback",
"--",
"-Disable MaxRows",
"-Set MaxRows to 100",
"--",
"-Logging on",
"-Logging off",
"--",
"-Insert test data"
};
addMenu(bar, "Options", soptions);
String stools[] = {"-Dump", "-Restore", "-Transfer"};
addMenu(bar, "Tools", stools);
fMain.setJMenuBar(bar);
initGUI();
sRecent = new String[iMaxRecent];
Dimension d = Toolkit.getDefaultToolkit().getScreenSize();
Dimension size = fMain.getSize();
// (ulrivo): full size on screen with less than 640 width
if (d.width >= 640) {
fMain.setLocation((d.width - size.width) / 2, (d.height - size.height) / 2);
} else {
fMain.setLocation(0, 0);
fMain.setSize(d);
}
fMain.show();
// (ulrivo): load query from command line
if (defScript != null) {
if (defDirectory != null) {
defScript = defDirectory + File.separator + defScript;
}
// if insert stmet is thousands of records...skip showing it
// as text. Too huge.
StringBuffer buf = new StringBuffer();
ifHuge = DatabaseManagerCommon.readFile(defScript);
if (4096 <= ifHuge.length()) {
buf.append("This huge file cannot be edited. Please execute\n");
txtCommand.setText(buf.toString());
} else {
txtCommand.setText(ifHuge);
}
}
txtCommand.requestFocus();
}
private void addMenu(JMenuBar b, String name, String items[]) {
JMenu menu = new JMenu(name);
addMenuItems(menu, items);
b.add(menu);
}
private void addMenuItems(JMenu f, String m[]) {
Dimension d = Toolkit.getDefaultToolkit().getScreenSize();
for (int i = 0; i < m.length; i++) {
if (m[i].equals("--")) {
f.addSeparator();
} else if (m[i].equals("---")) {
// (ulrivo): full size on screen with less than 640 width
if (d.width >= 640) {
f.addSeparator();
} else {
return;
}
} else {
JMenuItem item = new JMenuItem(m[i].substring(1));
char c = m[i].charAt(0);
if (c != '-') {
item.setMnemonic(c);
}
item.addActionListener(this);
f.add(item);
}
}
}
public void keyPressed(KeyEvent k) {}
public void keyReleased(KeyEvent k) {}
public void keyTyped(KeyEvent k) {
if (k.getKeyChar() == '\n' && k.isControlDown()) {
k.consume();
execute();
}
}
public void actionPerformed(ActionEvent ev) {
String s = ev.getActionCommand();
if (s == null) {
if (ev.getSource() instanceof JMenuItem) {
JMenuItem i;
s = ((JMenuItem) ev.getSource()).getText();
}
}
/*
// button replace by toolbar
if (s.equals("Execute")) {
execute();
} else
*/
if (s.equals("Exit")) {
windowClosing(null);
} else if (s.equals("Transfer")) {
Transfer.work(null);
} else if (s.equals("Dump")) {
Transfer.work(new String[] {"-d"});
} else if (s.equals("Restore")) {
Transfer.work(new String[] {"-r"});
} else if (s.equals("Logging on")) {
javaSystem.setLogToSystem(true);
} else if (s.equals("Logging off")) {
javaSystem.setLogToSystem(false);
} else if (s.equals("Refresh Tree")) {
refreshTree();
} else if (s.startsWith("#")) {
int i = Integer.parseInt(s.substring(1));
txtCommand.setText(sRecent[i]);
} else if (s.equals("Connect...")) {
connect(ConnectionDialogSwing.createConnection(fMain, "Connect"));
refreshTree();
} else if (s.equals("Results in Grid")) {
iResult = 0;
pResult.removeAll();
pResult.add(gScrollPane, BorderLayout.CENTER);
pResult.doLayout();
gResult.fireTableChanged(null);
pResult.repaint();
} else if (s.equals("Open Script...")) {
JFileChooser f = new JFileChooser(".");
f.setDialogTitle("Open Script...");
// (ulrivo): set default directory if set from command line
if (defDirectory != null) {
f.setCurrentDirectory(new File(defDirectory));
}
int option = f.showOpenDialog(fMain);
if (option == JFileChooser.APPROVE_OPTION) {
File file = f.getSelectedFile();
if (file != null) {
StringBuffer buf = new StringBuffer();
ifHuge = DatabaseManagerCommon.readFile(file.getAbsolutePath());
if (4096 <= ifHuge.length()) {
buf.append("This huge file cannot be edited. Please execute\n");
txtCommand.setText(buf.toString());
} else {
txtCommand.setText(ifHuge);
}
}
}
} else if (s.equals("Save Script...")) {
JFileChooser f = new JFileChooser(".");
f.setDialogTitle("Save Script");
// (ulrivo): set default directory if set from command line
if (defDirectory != null) {
f.setCurrentDirectory(new File(defDirectory));
}
int option = f.showSaveDialog(fMain);
if (option == JFileChooser.APPROVE_OPTION) {
File file = f.getSelectedFile();
if (file != null) {
DatabaseManagerCommon.writeFile(file.getAbsolutePath(), txtCommand.getText());
}
}
} else if (s.equals("Save Result...")) {
JFileChooser f = new JFileChooser(".");
f.setDialogTitle("Save Result...");
// (ulrivo): set default directory if set from command line
if (defDirectory != null) {
f.setCurrentDirectory(new File(defDirectory));
}
int option = f.showSaveDialog(fMain);
if (option == JFileChooser.APPROVE_OPTION) {
File file = f.getSelectedFile();
if (file != null) {
showResultInText();
DatabaseManagerCommon.writeFile(file.getAbsolutePath(), txtResult.getText());
}
}
} else if (s.equals("Results in Text")) {
iResult = 1;
pResult.removeAll();
pResult.add(txtResultScroll, BorderLayout.CENTER);
pResult.doLayout();
showResultInText();
pResult.repaint();
} else if (s.equals("AutoCommit on")) {
try {
cConn.setAutoCommit(true);
} catch (SQLException e) {
}
} else if (s.equals("AutoCommit off")) {
try {
cConn.setAutoCommit(false);
} catch (SQLException e) {
}
} else if (s.equals("Commit")) {
try {
cConn.commit();
} catch (SQLException e) {
}
} else if (s.equals("Insert test data")) {
insertTestData();
} else if (s.equals("Rollback")) {
try {
cConn.rollback();
} catch (SQLException e) {
}
} else if (s.equals("Disable MaxRows")) {
try {
sStatement.setMaxRows(0);
} catch (SQLException e) {
}
} else if (s.equals("Set MaxRows to 100")) {
try {
sStatement.setMaxRows(100);
} catch (SQLException e) {
}
} else if (s.equals("SELECT")) {
showHelp(DatabaseManagerCommon.selectHelp);
} else if (s.equals("INSERT")) {
showHelp(DatabaseManagerCommon.insertHelp);
} else if (s.equals("UPDATE")) {
showHelp(DatabaseManagerCommon.updateHelp);
} else if (s.equals("DELETE")) {
showHelp(DatabaseManagerCommon.deleteHelp);
} else if (s.equals("CREATE TABLE")) {
showHelp(DatabaseManagerCommon.createTableHelp);
} else if (s.equals("DROP TABLE")) {
showHelp(DatabaseManagerCommon.dropTableHelp);
} else if (s.equals("CREATE INDEX")) {
showHelp(DatabaseManagerCommon.createIndexHelp);
} else if (s.equals("DROP INDEX")) {
showHelp(DatabaseManagerCommon.dropIndexHelp);
} else if (s.equals("CHECKPOINT")) {
showHelp(DatabaseManagerCommon.checkpointHelp);
} else if (s.equals("SCRIPT")) {
showHelp(DatabaseManagerCommon.scriptHelp);
} else if (s.equals("SHUTDOWN")) {
showHelp(DatabaseManagerCommon.shutdownHelp);
} else if (s.equals("SET")) {
showHelp(DatabaseManagerCommon.setHelp);
} else if (s.equals("Test Script")) {
showHelp(DatabaseManagerCommon.testHelp);
}
}
private void showHelp(String help[]) {
txtCommand.setText(help[0]);
bHelp = true;
pResult.removeAll();
pResult.add(txtResultScroll, BorderLayout.CENTER);
pResult.doLayout();
txtResult.setText(help[1]);
pResult.repaint();
txtCommand.requestFocus();
txtCommand.setCaretPosition(help[0].length());
}
public void windowActivated(WindowEvent e) {}
public void windowDeactivated(WindowEvent e) {}
public void windowClosed(WindowEvent e) {}
public void windowDeiconified(WindowEvent e) {}
public void windowIconified(WindowEvent e) {}
public void windowOpened(WindowEvent e) {}
public void windowClosing(WindowEvent ev) {
try {
cConn.close();
} catch (Exception e) {
}
fMain.dispose();
if (bMustExit) {
System.exit(0);
}
}
private void clear() {
ifHuge = "";
txtCommand.setText(ifHuge);
}
private void execute() {
gResult.clear();
String sCmd = null;
if (4096 <= ifHuge.length()) {
sCmd = ifHuge;
} else {
sCmd = txtCommand.getText();
}
if (sCmd.startsWith("-->>>TEST<<<--")) {
testPerformance();
return;
}
String g[] = new String[1];
try {
lTime = System.currentTimeMillis();
sStatement.execute(sCmd);
int r = sStatement.getUpdateCount();
if (r == -1) {
formatResultSet(sStatement.getResultSet());
} else {
g[0] = "update count";
gResult.setHead(g);
g[0] = "" + r;
gResult.addRow(g);
}
lTime = System.currentTimeMillis() - lTime;
addToRecent(txtCommand.getText());
gResult.fireTableChanged(null);
} catch (SQLException e) {
lTime = System.currentTimeMillis() - lTime;
g[0] = "SQL Error";
gResult.setHead(g);
String s = e.getMessage();
s += " / Error Code: " + e.getErrorCode();
s += " / State: " + e.getSQLState();
g[0] = s;
gResult.addRow(g);
gResult.fireTableChanged(null);
}
updateResult();
System.gc();
}
private void updateResult() {
if (iResult == 0) {
// in case 'help' has removed the grid
if (bHelp) {
pResult.removeAll();
pResult.add(gScrollPane, BorderLayout.CENTER);
pResult.doLayout();
gResult.fireTableChanged(null);
pResult.repaint();
bHelp = false;
}
} else {
showResultInText();
}
txtCommand.selectAll();
txtCommand.requestFocus();
}
private void formatResultSet(ResultSet r) {
if (r == null) {
String g[] = new String[1];
g[0] = "Result";
gResult.setHead(g);
g[0] = "(empty)";
gResult.addRow(g);
return;
}
try {
ResultSetMetaData m = r.getMetaData();
int col = m.getColumnCount();
String h[] = new String[col];
for (int i = 1; i <= col; i++) {
h[i - 1] = m.getColumnLabel(i);
}
gResult.setHead(h);
while (r.next()) {
for (int i = 1; i <= col; i++) {
h[i - 1] = r.getString(i);
if (r.wasNull()) {
h[i - 1] = "(null)";
}
}
gResult.addRow(h);
}
r.close();
} catch (SQLException e) {
}
}
private void testPerformance() {
String all = txtCommand.getText();
StringBuffer b = new StringBuffer();
long total = 0;
for (int i = 0; i < all.length(); i++) {
char c = all.charAt(i);
if (c != '\n') {
b.append(c);
}
}
all = b.toString();
String g[] = new String[4];
g[0] = "ms";
g[1] = "count";
g[2] = "sql";
g[3] = "error";
gResult.setHead(g);
int max = 1;
lTime = System.currentTimeMillis() - lTime;
while (!all.equals("")) {
int i = all.indexOf(';');
String sql;
if (i != -1) {
sql = all.substring(0, i);
all = all.substring(i + 1);
} else {
sql = all;
all = "";
}
if (sql.startsWith("--#")) {
max = Integer.parseInt(sql.substring(3));
continue;
} else if (sql.startsWith("--")) {
continue;
}
g[2] = sql;
long l = 0;
try {
l = DatabaseManagerCommon.testStatement(sStatement, sql, max);
total += l;
g[0] = "" + l;
g[1] = "" + max;
g[3] = "";
} catch (SQLException e) {
g[0] = g[1] = "n/a";
g[3] = e.toString();
}
gResult.addRow(g);
System.out.println(l + " ms : " + sql);
}
g[0] = "" + total;
g[1] = "total";
g[2] = "";
gResult.addRow(g);
lTime = System.currentTimeMillis() - lTime;
updateResult();
}
/** Method declaration */
private void showResultInText() {
String col[] = gResult.getHead();
int width = col.length;
int size[] = new int[width];
Vector data = gResult.getData();
String row[];
int height = data.size();
for (int i = 0; i < width; i++) {
size[i] = col[i].length();
}
for (int i = 0; i < height; i++) {
row = (String[]) data.elementAt(i);
for (int j = 0; j < width; j++) {
int l = row[j].length();
if (l > size[j]) {
size[j] = l;
}
}
}
StringBuffer b = new StringBuffer();
for (int i = 0; i < width; i++) {
b.append(col[i]);
for (int l = col[i].length(); l <= size[i]; l++) {
b.append(' ');
}
}
b.append(NL);
for (int i = 0; i < width; i++) {
for (int l = 0; l < size[i]; l++) {
b.append('-');
}
b.append(' ');
}
b.append(NL);
for (int i = 0; i < height; i++) {
row = (String[]) data.elementAt(i);
for (int j = 0; j < width; j++) {
b.append(row[j]);
for (int l = row[j].length(); l <= size[j]; l++) {
b.append(' ');
}
}
b.append(NL);
}
b.append(NL + height + " row(s) in " + lTime + " ms");
txtResult.setText(b.toString());
}
private void addToRecent(String s) {
for (int i = 0; i < iMaxRecent; i++) {
if (s.equals(sRecent[i])) {
return;
}
}
if (sRecent[iRecent] != null) {
mRecent.remove(iRecent);
}
sRecent[iRecent] = s;
if (s.length() > 43) {
s = s.substring(0, 40) + "...";
}
JMenuItem item = new JMenuItem(s);
item.setActionCommand("#" + iRecent);
item.addActionListener(this);
mRecent.insert(item, iRecent);
iRecent = (iRecent + 1) % iMaxRecent;
}
private void initGUI() {
JPanel pCommand = new JPanel();
pResult = new JPanel();
nsSplitPane = new JSplitPane(JSplitPane.VERTICAL_SPLIT, pCommand, pResult);
pCommand.setLayout(new BorderLayout());
pResult.setLayout(new BorderLayout());
Font fFont = new Font("Dialog", Font.PLAIN, 12);
txtCommand = new JTextArea(5, 40);
txtCommand.setMargin(new Insets(5, 5, 5, 5));
txtCommand.addKeyListener(this);
txtCommandScroll = new JScrollPane(txtCommand);
txtResult = new JTextArea(20, 40);
txtResult.setMargin(new Insets(5, 5, 5, 5));
txtResultScroll = new JScrollPane(txtResult);
txtCommand.setFont(fFont);
txtResult.setFont(new Font("Courier", Font.PLAIN, 12));
/*
// button replaced by toolbar
butExecute = new JButton("Execute");
butExecute.addActionListener(this);
pCommand.add(butExecute, BorderLayout.EAST);
*/
pCommand.add(txtCommandScroll, BorderLayout.CENTER);
gResult = new GridSwing();
gResultTable = new JTable(gResult);
gScrollPane = new JScrollPane(gResultTable);
// getContentPane().setLayout(new BorderLayout());
pResult.add(gScrollPane, BorderLayout.CENTER);
// Set up the tree
rootNode = new DefaultMutableTreeNode("Connection");
treeModel = new DefaultTreeModel(rootNode);
tTree = new JTree(treeModel);
tScrollPane = new JScrollPane(tTree);
tScrollPane.setPreferredSize(new Dimension(120, 400));
tScrollPane.setMinimumSize(new Dimension(70, 100));
txtCommandScroll.setPreferredSize(new Dimension(360, 100));
txtCommandScroll.setMinimumSize(new Dimension(180, 100));
gScrollPane.setPreferredSize(new Dimension(460, 300));
ewSplitPane = new JSplitPane(JSplitPane.HORIZONTAL_SPLIT, tScrollPane, nsSplitPane);
fMain.getContentPane().add(ewSplitPane, BorderLayout.CENTER);
doLayout();
fMain.pack();
}
/* Simple tree node factory method - set's parent and user object.
*/
private DefaultMutableTreeNode makeNode(Object userObject, MutableTreeNode parent) {
DefaultMutableTreeNode node = new DefaultMutableTreeNode(userObject);
if (parent != null) {
treeModel.insertNodeInto(node, parent, parent.getChildCount());
}
return node;
}
/* Clear all existing nodes from the tree model and rebuild from scratch.
*/
protected void refreshTree() {
DefaultMutableTreeNode propertiesNode;
DefaultMutableTreeNode leaf;
// First clear the existing tree by simply enumerating
// over the root node's children and removing them one by one.
while (treeModel.getChildCount(rootNode) > 0) {
DefaultMutableTreeNode child = (DefaultMutableTreeNode) treeModel.getChild(rootNode, 0);
treeModel.removeNodeFromParent(child);
child.removeAllChildren();
child.removeFromParent();
}
treeModel.nodeStructureChanged(rootNode);
treeModel.reload();
tScrollPane.repaint();
// Now rebuild the tree below its root
try {
// Start by naming the root node from its URL:
rootNode.setUserObject(dMeta.getURL());
// get metadata about user tables by building a vector of table names
String usertables[] = {"TABLE", "GLOBAL TEMPORARY", "VIEW"};
ResultSet result = dMeta.getTables(null, null, null, usertables);
Vector tables = new Vector();
// sqlbob@users Added remarks.
Vector remarks = new Vector();
while (result.next()) {
tables.addElement(result.getString(3));
remarks.addElement(result.getString(5));
}
result.close();
// For each table, build a tree node with interesting info
for (int i = 0; i < tables.size(); i++) {
String name = (String) tables.elementAt(i);
DefaultMutableTreeNode tableNode = makeNode(name, rootNode);
ResultSet col = dMeta.getColumns(null, null, name, null);
// sqlbob@users Added remarks.
String remark = (String) remarks.elementAt(i);
if ((remark != null) && !remark.trim().equals("")) {
makeNode(remark, tableNode);
}
// With a child for each column containing pertinent attributes
while (col.next()) {
String c = col.getString(4);
DefaultMutableTreeNode columnNode = makeNode(c, tableNode);
String type = col.getString(6);
makeNode("Type: " + type, columnNode);
boolean nullable = col.getInt(11) != DatabaseMetaData.columnNoNulls;
makeNode("Nullable: " + nullable, columnNode);
}
col.close();
DefaultMutableTreeNode indexesNode = makeNode("Indices", tableNode);
ResultSet ind = dMeta.getIndexInfo(null, null, name, false, false);
String oldiname = null;
// A child node to contain each index - and its attributes
while (ind.next()) {
DefaultMutableTreeNode indexNode = null;
boolean nonunique = ind.getBoolean(4);
String iname = ind.getString(6);
if ((oldiname == null || !oldiname.equals(iname))) {
indexNode = makeNode(iname, indexesNode);
makeNode("Unique: " + !nonunique, indexNode);
oldiname = iname;
}
// And the ordered column list for index components
makeNode(ind.getString(9), indexNode);
}
ind.close();
}
// Finally - a little additional metadata on this connection
propertiesNode = makeNode("Properties", rootNode);
makeNode("User: "******"ReadOnly: " + cConn.isReadOnly(), propertiesNode);
makeNode("AutoCommit: " + cConn.getAutoCommit(), propertiesNode);
makeNode("Driver: " + dMeta.getDriverName(), propertiesNode);
makeNode("Product: " + dMeta.getDatabaseProductName(), propertiesNode);
makeNode("Version: " + dMeta.getDatabaseProductVersion(), propertiesNode);
} catch (SQLException se) {
propertiesNode = makeNode("Error getting metadata:", rootNode);
makeNode(se.getMessage(), propertiesNode);
makeNode(se.getSQLState(), propertiesNode);
}
treeModel.nodeStructureChanged(rootNode);
treeModel.reload();
tScrollPane.repaint();
}
protected JToolBar createToolBar() {
JToolBar jtoolbar = new JToolBar();
jtoolbar.putClientProperty("JToolBar.isRollover", Boolean.TRUE);
// ---------------------------------------
JButton jbuttonClear = new JButton("Clear SQL Statement");
jbuttonClear.setToolTipText("Clear SQL Statement");
jbuttonClear.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent actionevent) {
clear();
}
});
// ---------------------------------------
JButton jbuttonExecute = new JButton("Execute SQL Statement");
jbuttonExecute.setToolTipText("Execute SQL Statement");
jbuttonExecute.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent actionevent) {
execute();
}
});
jtoolbar.addSeparator();
jtoolbar.add(jbuttonClear);
jtoolbar.addSeparator();
jtoolbar.add(jbuttonExecute);
jtoolbar.addSeparator();
jbuttonClear.setAlignmentY(0.5F);
jbuttonClear.setAlignmentX(0.5F);
jbuttonExecute.setAlignmentY(0.5F);
jbuttonExecute.setAlignmentX(0.5F);
return jtoolbar;
}
}
