SQLiteTools.java [src/java/crlmod/utils] Revision: Date:
/*
* $Id: 1.0+65 SQLiteTools.java 402d39c37049 2021-12-29 od $
*
* This file is part of the Cloud Services Integration Platform (CSIP),
* a Model-as-a-Service framework, API, and application suite.
*
* 2012-2024, OMSLab, Colorado State University.
*
* OMSLab licenses this file to you under the MIT license.
* See the LICENSE file in the project root for more information.
*/
package crlmod.utils;
import csip.api.server.Executable;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
/**
*
* @author LYaege
*/
@Deprecated
public class SQLiteTools {
/**
*
* @param sqlite3 - name of the sqlite3 db file
* @param sqlite2 - name of the sqlite db file
* @return - error
* @throws IOException
* @throws InterruptedException
*
* writes the output of the sqlite3 dump to a file sqlite pulls in the sql
* file to recreate the file in sqlite
*/
private static String recreateDatabase(File sqlite3, File sqlite2, int versionOut) throws IOException, InterruptedException {
if (versionOut == 2) {
// dump sqlite3 to file
Runtime rt = Runtime.getRuntime();
Process p = rt.exec("sqlite3 " + sqlite3.getAbsolutePath() + " .dump");
InputStream stderr = p.getErrorStream();
InputStreamReader isr = new InputStreamReader(stderr);
BufferedReader br = new BufferedReader(isr);
InputStream stout = p.getInputStream();
InputStreamReader stoutr = new InputStreamReader(stout);
BufferedReader br2 = new BufferedReader(stoutr);
String line = null;
String sql = "";
while ((line = br2.readLine()) != null) {
System.out.println(line);
sql += line;
}
File sql_file = new File(sqlite3.getParent() + "/temp.sql");
FileWriter fw = new FileWriter(sql_file);
fw.write(sql);
fw.flush();
fw.close();
// read in file and recreate db
Process pr = rt.exec("sqlite -init " + sql_file.getAbsolutePath() + " " + sqlite2.getAbsolutePath());
PrintWriter pw = new PrintWriter(new OutputStreamWriter(pr.getOutputStream()));
pw.println(".exit");
pw.flush();
System.out.println("exit: " + pr.waitFor());
pw.close();
//Cleanup
stderr.close();
isr.close();
br.close();
stoutr.close();
br2.close();
p.destroy();
pr.destroy();
}
if (versionOut == 3) {
// dump sqlite3 to file
Runtime rt = Runtime.getRuntime();
Process p = rt.exec("sqlite " + sqlite2.getAbsolutePath() + " .dump");
InputStream stderr = p.getErrorStream();
InputStreamReader isr = new InputStreamReader(stderr);
BufferedReader br = new BufferedReader(isr);
InputStream stout = p.getInputStream();
InputStreamReader stoutr = new InputStreamReader(stout);
BufferedReader br2 = new BufferedReader(stoutr);
String line = null;
String sql = "";
while ((line = br2.readLine()) != null) {
System.out.println(line);
sql += line;
}
File sql_file = new File(sqlite2.getParent() + "/temp.sql");
FileWriter fw = new FileWriter(sql_file);
fw.write(sql);
fw.flush();
fw.close();
// read in file and recreate db
Process pr = rt.exec("sqlite3 -init " + sql_file.getAbsolutePath() + " " + sqlite3.getAbsolutePath());
PrintWriter pw = new PrintWriter(new OutputStreamWriter(pr.getOutputStream()));
pw.println(".exit");
pw.flush();
System.out.println("exit: " + pr.waitFor());
pw.close();
//Cleanup
stderr.close();
isr.close();
br.close();
stoutr.close();
br2.close();
p.destroy();
pr.destroy();
}
return null;
}
public static int executeCommand(String command) throws IOException, InterruptedException {
Runtime rt = Runtime.getRuntime();
Process p = rt.exec(command);
InputStream stderr = p.getErrorStream();
InputStreamReader isr = new InputStreamReader(stderr);
BufferedReader br = new BufferedReader(isr);
InputStream stout = p.getInputStream();
InputStreamReader stoutr = new InputStreamReader(stout);
BufferedReader br2 = new BufferedReader(stoutr);
String line = null;
while ((line = br2.readLine()) != null) {
System.out.println(line);
}
while ((line = br.readLine()) != null) {
System.out.println(line);
}
int exitVal = p.waitFor();
//Cleanup
stderr.close();
isr.close();
br.close();
p.destroy();
return exitVal;
}
/**
*
* @param sqlite3 File that points to the input sqlite 3 file. Should
* already contain data.
* @param sqlite2 File that points to the sqlite 2 file. Should be empty
* @param ex_cmd
* @param sqlite_exe
* @param sqlite3_exe
* @throws IOException
* @throws InterruptedException Converts from sqlite 3 database to sqlite 2
* database.
*/
public static String sqlite3To2(File sqlite3, File sqlite2, Executable ex_cmd, File sqlite_exe, File sqlite3_exe) throws IOException, InterruptedException {
if (System.getProperty("os.name").contains("Win")) {
ex_cmd.setArguments("/c", "start", "/b", sqlite3_exe.getAbsolutePath(), sqlite3.getAbsolutePath(), ".dump", ">", sqlite3.getAbsolutePath() + ".sql");
System.out.println("sqlite3 dump process exit code: " + ex_cmd.exec());
ex_cmd.redirectDefaults();
ex_cmd.setArguments("cmd", "/c", "start", "/b", sqlite_exe.getAbsolutePath(), sqlite2.getAbsolutePath(), "<", sqlite3.getAbsolutePath() + ".sql");
System.out.println("sqlite2 creation process exit code: " + ex_cmd.exec());
} else {
String error = recreateDatabase(sqlite3, sqlite2, 2);
if (error != null) {
return error;
}
}
return null;
}
public static void sqlite2To3(File sqlite2, File sqlite3, Executable cmd, File exe_sqlite2, File exe_sqlite3) throws IOException, InterruptedException {
if (System.getProperty("os.name").contains("Win")) {
// //sqlite OLD.DB .dump | sqlite3 NEW.DB
//String command1 = "cmd /c start /b sqlite " + sqlite2.getAbsolutePath() + " .dump | sqlite3 " + sqlite3.getAbsolutePath();
cmd.setArguments("/c", "start", "/b", exe_sqlite2.getAbsolutePath(), sqlite2.getAbsolutePath(), ".dump", "|", exe_sqlite3.getAbsolutePath(), sqlite3.getAbsolutePath());
int result = cmd.exec();
System.out.println("SQLite Conversion 2 -> 3 result: " + result);
} else {
//recreateDatabase(sqlite3, sqlite2, 3);
}
}
}