/*
* Jampa
* Copyright (C) 2008-2009 J. Devauchelle and contributors.
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the GNU General Public License
* version 3 as published by the Free Software Foundation.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*/
package org.jampa.controllers.core;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.jampa.controllers.Controller;
import org.jampa.logging.Log;
import org.jampa.utils.Constants;
import org.jampa.utils.SystemUtils;
import org.jaudiotagger.audio.AudioFile;
import org.jaudiotagger.audio.AudioFileIO;
import org.jaudiotagger.audio.AudioHeader;
import org.jaudiotagger.tag.Tag;
/**
* HSQL Controller. Used to interact with the database.
* @author jdevauchelle
*
*/
public class HSQLController {
private Connection _connection;
private int _libraryRecordCount;
private boolean _boLibraryTableReseted = false;
private boolean _boMetadataTableReseted = false;
private static final String SQL_CREATE_LIBRARY_TABLE = "CREATE TABLE LIBRARY (PATH VARCHAR, ARTIST VARCHAR, ALBUM VARCHAR, TITLE VARCHAR, TRACKNUMBER VARCHAR, TRACKLENGTH INT, GENRE VARCHAR, YEAR VARCHAR, COMMENT VARCHAR);"; //$NON-NLS-1$
private static final String SQL_LIBRARY_COUNT = "SELECT COUNT(*) FROM LIBRARY"; //$NON-NLS-1$
private static final String SQL_LIBRARY_SELECT_ALL = "SELECT ARTIST, ALBUM, TITLE, GENRE, TRACKNUMBER, TRACKLENGTH, COMMENT, PATH FROM LIBRARY ORDER BY ARTIST, ALBUM, TITLE"; //$NON-NLS-1$
private static final String SQL_CREATE_STATISTICS_TABLE = "CREATE TABLE STATISTICS (AUTOINC BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) PRIMARY KEY, ID VARCHAR, TYPE VARCHAR, DISPLAY VARCHAR, ADDITIONAL VARCHAR, VALUE BIGINT);"; //$NON-NLS-1$
private static final String SQL_STATISTICS_COUNT = "SELECT COUNT(*) FROM STATISTICS"; //$NON-NLS-1$
private static final String SQL_STATISTICS_SELECT_ALL = "SELECT * FROM STATISTICS"; //$NON-NLS-1$
private static final String SQL_CREATE_METADATA_TABLE = "CREATE TABLE DB_METADATA (KEY VARCHAR, VALUE VARCHAR);"; //$NON-NLS-1$
private static final String SQL_METADATA_COUNT = "SELECT COUNT(*) FROM DB_METADATA"; //$NON-NLS-1$
private static final String DATABASE_DRIVER = "org.hsqldb.jdbcDriver"; //$NON-NLS-1$
private static final String DATABASE_NAME = "librarydb"; //$NON-NLS-1$
private static final String DATABASE_CONNECTION_PREFIX = "jdbc:hsqldb:file:"; //$NON-NLS-1$
public HSQLController() {
try {
_connection = getConnection();
} catch (Exception e) {
Log.getInstance(HSQLController.class).fatal("Unable to get connection to database : " + e.getMessage()); //$NON-NLS-1$
}
if (!isLibraryTableExist()) {
createDataStructure(SQL_CREATE_LIBRARY_TABLE);
_boLibraryTableReseted = true;
}
if (!isStatisticsTableExist())
createDataStructure(SQL_CREATE_STATISTICS_TABLE);
if (!isMetadataTableExists()) {
createDataStructure(SQL_CREATE_METADATA_TABLE);
executeQuery("INSERT INTO DB_METADATA (KEY, VALUE) VALUES ('VERSION', '" + Constants.APP_VERSION + "')"); //$NON-NLS-1$ //$NON-NLS-2$
_boMetadataTableReseted = true;
}
String databaseVersion = getDatabaseVersion();
Log.getInstance(HSQLController.class).debug("Database version: " + databaseVersion); //$NON-NLS-1$
// If database versions do not match, or METADATA table just created,
// its an update, we need to update database.
if ((!databaseVersion.equals(Constants.APP_VERSION)) ||
(_boMetadataTableReseted)) {
updateDatabase(databaseVersion, Constants.APP_VERSION);
}
Log.getInstance(HSQLController.class).debug("HSQLController initialized."); //$NON-NLS-1$
}
private Connection getConnection() throws Exception {
Class.forName(DATABASE_DRIVER);
String url = DATABASE_CONNECTION_PREFIX + SystemUtils.databaseDirectory + DATABASE_NAME;
Connection result = DriverManager.getConnection(url, "sa", ""); //$NON-NLS-1$ //$NON-NLS-2$
result.setAutoCommit(true);
return result;
}
private void executeQuery(String query) {
try {
Statement statement = _connection.createStatement();
statement.executeQuery(query);
statement.close();
} catch (Exception e) {
Log.getInstance(HSQLController.class).warn("HSQL : Problem while executing query: " + query + " (" + e.toString() + ")."); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
}
}
/**
* Update database version.
* @param dbVersion
* @param appVersion
*/
private void updateDatabase(String dbVersion, String appVersion) {
Log.getInstance(HSQLController.class).info("Incorect database version, updating: " + dbVersion + " --> " + appVersion + "."); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
// Put the correct version in METADATA table.
executeQuery("UPDATE DB_METADATA SET VALUE = '" + Constants.APP_VERSION + "' WHERE KEY = 'VERSION'"); //$NON-NLS-1$ //$NON-NLS-2$
if (!_boLibraryTableReseted) {
// Update LIBRARY table: drop it and re-create it :)
doDropLibraryTable();
createDataStructure(SQL_CREATE_LIBRARY_TABLE);
_boLibraryTableReseted = true;
}
}
private void createDataStructure(String query) {
try {
Log.getInstance(HSQLController.class).debug("Creating table: " + query); //$NON-NLS-1$
Statement statement = _connection.createStatement();
statement.executeQuery(query);
statement.executeQuery("COMMIT"); //$NON-NLS-1$
statement.close();
} catch (Exception e) {
Log.getInstance(HSQLController.class).warn("HSQL : Problem creating table: " + e.toString()); //$NON-NLS-1$
}
}
private boolean isLibraryTableExist() {
Statement statement;
try {
statement = _connection.createStatement();
ResultSet result = statement.executeQuery(SQL_LIBRARY_COUNT);
result.next();
_libraryRecordCount = result.getInt(1);
Log.getInstance(HSQLController.class).debug("Connected to LIBRARY table. Records number : " + _libraryRecordCount); //$NON-NLS-1$
statement.close();
return true;
} catch (SQLException e) {
Log.getInstance(HSQLController.class).debug("HSQLDB : Table LIBRARY not found."); //$NON-NLS-1$
return false;
} catch (Exception e) {
Log.getInstance(HSQLController.class).warn("Problem connecting to HSQLDB : " + e.toString()); //$NON-NLS-1$
return false;
}
}
private boolean isStatisticsTableExist() {
Statement statement;
try {
statement = _connection.createStatement();
ResultSet result = statement.executeQuery(SQL_STATISTICS_COUNT);
result.next();
int nbResults = result.getInt(1);
Log.getInstance(HSQLController.class).debug("Connected to STATISTICS table. Records number : " + nbResults); //$NON-NLS-1$
statement.close();
return true;
} catch (SQLException e) {
Log.getInstance(HSQLController.class).debug("HSQLDB : Table STATISTICS not found."); //$NON-NLS-1$
return false;
} catch (Exception e) {
Log.getInstance(HSQLController.class).warn("Problem connecting to HSQLDB : " + e.toString()); //$NON-NLS-1$
return false;
}
}
private boolean isMetadataTableExists() {
Statement statement;
try {
statement = _connection.createStatement();
ResultSet result = statement.executeQuery(SQL_METADATA_COUNT);
result.next();
int nbResults = result.getInt(1);
Log.getInstance(HSQLController.class).debug("Connected to DB_METADATA table. Records number : " + nbResults); //$NON-NLS-1$
statement.close();
return true;
} catch (SQLException e) {
Log.getInstance(HSQLController.class).debug("HSQLDB : Table DB_METADATA not found."); //$NON-NLS-1$
return false;
} catch (Exception e) {
Log.getInstance(HSQLController.class).warn("Problem connecting to HSQLDB : " + e.toString()); //$NON-NLS-1$
return false;
}
}
public void insertFileToLibrary(String fileName) {
String title = ""; //$NON-NLS-1$
String artist = ""; //$NON-NLS-1$
String album = ""; //$NON-NLS-1$
String trackNumber = ""; //$NON-NLS-1$
int trackLength = 0;
String genre = ""; //$NON-NLS-1$
String year = ""; //$NON-NLS-1$
String comment = ""; //$NON-NLS-1$
try {
AudioFile f = AudioFileIO.read(new File(fileName));
AudioHeader ah = f.getAudioHeader();
Tag tag = f.getTag();
try {
title = tag.getFirstTitle().replaceAll("'", "''"); //$NON-NLS-1$ //$NON-NLS-2$
} catch (Exception e) {
Log.getInstance(PlaylistController.class).warn("Error while reading title property : " + fileName + " (" + e.toString() +")."); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
}
try {
artist = tag.getFirstArtist().replaceAll("'", "''"); //$NON-NLS-1$ //$NON-NLS-2$
} catch (Exception e) {
Log.getInstance(PlaylistController.class).warn("Error while reading artist property : " + fileName + " (" + e.toString() +")."); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
}
try {
album = tag.getFirstAlbum().replaceAll("'", "''"); //$NON-NLS-1$ //$NON-NLS-2$
} catch (Exception e) {
Log.getInstance(PlaylistController.class).warn("Error while reading album property : " + fileName + " (" + e.toString() +")."); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
}
try {
trackNumber = tag.getFirstTrack().replaceAll("'", "''"); //$NON-NLS-1$ //$NON-NLS-2$
} catch (Exception e) {
Log.getInstance(PlaylistController.class).warn("Error while reading trackNumber property : " + fileName + " (" + e.toString() +")."); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
}
try {
trackLength = ah.getTrackLength();
} catch (Exception e) {
Log.getInstance(PlaylistController.class).warn("Error while reading trackLength property : " + fileName + " (" + e.toString() +")."); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
}
try {
genre = tag.getFirstGenre().replaceAll("'", "''"); //$NON-NLS-1$ //$NON-NLS-2$
} catch (Exception e) {
Log.getInstance(PlaylistController.class).warn("Error while reading genre property : " + fileName + " (" + e.toString() +")."); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
}
try {
year = tag.getFirstYear().replaceAll("'", "''"); //$NON-NLS-1$ //$NON-NLS-2$
} catch (Exception e) {
Log.getInstance(PlaylistController.class).warn("Error while reading year property : " + fileName + " (" + e.toString() +")."); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
}
try {
comment = tag.getFirstComment().replaceAll("'", "''"); //$NON-NLS-1$ //$NON-NLS-2$
} catch (Exception e) {
Log.getInstance(PlaylistController.class).warn("Error while reading comment property : " + fileName + " (" + e.toString() +")."); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
}
} catch (Exception e) {
Log.getInstance(PlaylistController.class).warn("Error while reading file properties : " + fileName + " (" + e.toString() +")."); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
} finally {
if ((title == null) ||
(title.isEmpty())) {
title = fileName.substring(fileName.lastIndexOf(SystemUtils.fileSeparator) + 1).replaceAll("'", "''"); //$NON-NLS-1$ //$NON-NLS-2$
}
}
try {
Statement statement = _connection.createStatement();
statement.executeUpdate("INSERT INTO LIBRARY (PATH, ARTIST, ALBUM, TITLE, TRACKNUMBER, TRACKLENGTH, GENRE, YEAR, COMMENT) VALUES ('" + //$NON-NLS-1$
fileName.replaceAll("'", "''") + "', '" + //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
artist + "', '" + //$NON-NLS-1$
album + "', '" + //$NON-NLS-1$
title + "', '" + //$NON-NLS-1$
trackNumber + "', " + //$NON-NLS-1$
Integer.toString(trackLength) + ", '" + //$NON-NLS-1$
genre + "', '" + //$NON-NLS-1$
year + "', '" + //$NON-NLS-1$
comment + "')"); //$NON-NLS-1$
statement.close();
setLibraryRecordCount(++_libraryRecordCount);
} catch (Exception e) {
Log.getInstance(HSQLController.class).error(e.getMessage());
}
}
public List<String> updateLibraryItem(String fileName,
boolean updateTrackNumber, String trackNumber,
boolean updateTitle, String title,
boolean updateArtist, String artist,
boolean updateAlbum, String album,
boolean updateGenre, String genre,
boolean updateYear, String year,
boolean updateComment, String comment) {
List<String> result = new ArrayList<String>();
// First update the library
try {
Statement statement = _connection.createStatement();
String query = "UPDATE LIBRARY SET "; //$NON-NLS-1$
if (updateTrackNumber) {
query += "TRACKNUMBER='" + trackNumber.replaceAll("'", "''") + "'" + (updateTitle || updateArtist || updateAlbum || updateGenre || updateYear || updateComment ? "," : ""); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$
}
if (updateTitle) {
query += "TITLE='" + title.replaceAll("'", "''") + "'" + (updateArtist || updateAlbum || updateGenre || updateYear || updateComment ? "," : ""); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$
}
if (updateArtist) {
query += "ARTIST='" + artist.replaceAll("'", "''") + "'" + (updateAlbum || updateGenre || updateYear || updateComment ? "," : ""); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$
}
if (updateAlbum) {
query += "ALBUM='" + album.replaceAll("'", "''") + "'" + (updateGenre || updateYear || updateComment ? "," : ""); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$
}
if (updateGenre) {
query += "GENRE='" + genre.replaceAll("'", "''") + "'" + (updateYear || updateComment ? "," : ""); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$
}
if (updateYear) {
query += "YEAR='" + year.replaceAll("'", "''") + "'" + (updateComment ? "," : ""); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
}
if (updateComment) {
query += "COMMENT='" + comment.replaceAll("'", "''") + "'"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
}
query += " WHERE PATH='" + fileName.replaceAll("'", "''") + "'"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
statement.executeUpdate(query);
statement.close();
} catch (Exception e) {
result.add(e.getMessage() + " (" + e.toString() + ")"); //$NON-NLS-1$ //$NON-NLS-2$
Log.getInstance(HSQLController.class).warn(e.getMessage());
}
// Then update the file.
try {
AudioFile file;
Tag tag;
file = AudioFileIO.read(new File(fileName));
tag = file.getTag();
if (updateTrackNumber) {
try {
tag.setTrack(trackNumber);
} catch (Exception e) {
String message = "Error while writting tracknumber: " + e.getMessage() + " (" + e.toString() + ")"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
result.add(message);
Log.getInstance(HSQLController.class).warn(message);
}
}
if (updateTitle) {
try {
tag.setTitle(title);
} catch (Exception e) {
String message = "Error while writting title: " + e.getMessage() + " (" + e.toString() + ")"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
result.add(message);
Log.getInstance(HSQLController.class).warn(message);
}
}
if (updateArtist) {
try {
tag.setArtist(artist);
} catch (Exception e) {
String message = "Error while writting artist: " + e.getMessage() + " (" + e.toString() + ")"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
result.add(message);
Log.getInstance(HSQLController.class).warn(message);
}
}
if (updateAlbum) {
try {
tag.setAlbum(album);
} catch (Exception e) {
String message = "Error while writting album: " + e.getMessage() + " (" + e.toString() + ")"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
result.add(message);
Log.getInstance(HSQLController.class).warn(message);
}
}
if (updateGenre) {
try {
tag.setGenre(genre);
} catch (Exception e) {
String message = "Error while writting genre: " + e.getMessage() + " (" + e.toString() + ")"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
result.add(message);
Log.getInstance(HSQLController.class).warn(message);
}
}
if (updateYear) {
try {
tag.setYear(year);
} catch (Exception e) {
String message = "Error while writting year: " + e.getMessage() + " (" + e.toString() + ")"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
result.add(message);
Log.getInstance(HSQLController.class).warn(message);
}
}
if (updateComment) {
try {
tag.setComment(comment);
} catch (Exception e) {
String message = "Error while writting comment: " + e.getMessage() + " (" + e.toString() + ")"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
result.add(message);
Log.getInstance(HSQLController.class).warn(message);
}
}
file.commit();
} catch (Exception e) {
result.add(e.getMessage() + " (" + e.toString() + ")"); //$NON-NLS-1$ //$NON-NLS-2$
Log.getInstance(HSQLController.class).warn(e.getMessage());
}
return result;
}
private void doDropLibraryTable() {
try {
Statement statement = _connection.createStatement();
statement.executeQuery("DROP TABLE LIBRARY"); //$NON-NLS-1$
statement.executeQuery("COMMIT"); //$NON-NLS-1$
statement.close();
} catch (Exception e) {
Log.getInstance(HSQLController.class).error(e.getMessage());
}
}
public void doResetLibraryDatabase() {
try {
Statement statement = _connection.createStatement();
statement.executeQuery("DELETE FROM LIBRARY"); //$NON-NLS-1$
statement.executeQuery("COMMIT"); //$NON-NLS-1$
statement.close();
setLibraryRecordCount(0);
} catch (Exception e) {
Log.getInstance(HSQLController.class).error(e.getMessage());
}
}
public void doCommitDatabase() {
try {
Statement statement = _connection.createStatement();
statement.executeQuery("COMMIT"); //$NON-NLS-1$
statement.close();
} catch (Exception e) {
Log.getInstance(HSQLController.class).error(e.getMessage());
}
}
public String getData() {
try {
Statement statement = _connection.createStatement();
ResultSet result = statement.executeQuery(SQL_LIBRARY_SELECT_ALL);
StringBuilder sb = new StringBuilder();
sb.append("ARTIST ## ALBUM ## TITLE ## TRACKNUMBER ## PATH\n"); //$NON-NLS-1$
while(result.next()) {
sb.append(result.getString("ARTIST") + " ## " + result.getString("ALBUM") + " ## " + result.getString("TITLE") + " ## " + result.getString("TRACKNUMBER") + " ## "+ result.getString("PATH") + "\n"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ //$NON-NLS-8$ //$NON-NLS-9$ //$NON-NLS-10$
}
statement.close();
return sb.toString();
} catch (Exception e) {
Log.getInstance(HSQLController.class).warn("HSQL : Problem creating table : " + e.toString()); //$NON-NLS-1$
return new String("Problems with HSQL data."); //$NON-NLS-1$
}
}
public ResultSet getLibraryData() {
try {
Statement statement = _connection.createStatement();
ResultSet result = statement.executeQuery(SQL_LIBRARY_SELECT_ALL);
statement.close();
return result;
} catch (Exception e) {
Log.getInstance(HSQLController.class).warn("HSQL : Problem reading library : " + e.toString()); //$NON-NLS-1$
return null;
}
}
public ResultSet getLibraryItemData(String fileName) {
try {
Statement statement = _connection.createStatement();
ResultSet result = statement.executeQuery("SELECT * FROM LIBRARY WHERE PATH = '" + fileName.replaceAll("'", "''") + "'"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
statement.close();
return result;
} catch (Exception e) {
Log.getInstance(HSQLController.class).warn("HSQL : Problem reading item : " + fileName + " (" + e.toString() + ")."); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
return null;
}
}
public ResultSet executeSelectionQuery(String query) {
try {
Statement statement = _connection.createStatement();
ResultSet result = statement.executeQuery(query);
statement.close();
return result;
} catch (Exception e) {
Log.getInstance(HSQLController.class).warn("HSQL : Problem executing query : " + query); //$NON-NLS-1$
return null;
}
}
public boolean doesLibraryItemExists(String fileName) {
try {
Statement statement = _connection.createStatement();
ResultSet result = statement.executeQuery("SELECT COUNT(*) FROM LIBRARY WHERE PATH = '" + fileName.replaceAll("'", "''") + "'"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
result.next();
statement.close();
return result.getInt(1) > 0 ? true : false;
} catch (Exception e) {
Log.getInstance(HSQLController.class).warn("HSQL : Problem reading item : " + fileName + " (" + e.toString() + ")."); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
return false;
}
}
public void deleteLibraryItem(String fileName) {
try {
Statement statement = _connection.createStatement();
statement.executeQuery("DELETE FROM LIBRARY WHERE PATH = '" + fileName.replaceAll("'", "''") + "'"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
statement.close();
setLibraryRecordCount(--_libraryRecordCount);
} catch (Exception e) {
Log.getInstance(HSQLController.class).warn("HSQL : Problem deleting item : " + fileName + " (" + e.toString() + ")."); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
}
}
public ResultSet getStatisticsData() {
try {
Statement statement = _connection.createStatement();
ResultSet result = statement.executeQuery(SQL_STATISTICS_SELECT_ALL);
statement.close();
return result;
} catch (Exception e) {
Log.getInstance(HSQLController.class).warn("HSQL : Problem reading statistics : " + e.toString()); //$NON-NLS-1$
return null;
}
}
public void resetStatisticsTable() {
try {
Statement statement = _connection.createStatement();
statement.executeQuery("DELETE FROM STATISTICS"); //$NON-NLS-1$
statement.close();
statement = _connection.createStatement();
statement.executeQuery("ALTER TABLE STATISTICS ALTER COLUMN AUTOINC RESTART WITH 1;"); //$NON-NLS-1$
statement.close();
doCommitDatabase();
} catch (Exception e) {
Log.getInstance(HSQLController.class).warn("HSQL : Problem reseting statistics : " + e.toString()); //$NON-NLS-1$
}
}
public void writeStatisticItem(String id, String type, String display, String additionalValue, int value) {
try {
String query = "INSERT INTO STATISTICS (AUTOINC, ID, TYPE, DISPLAY, ADDITIONAL, VALUE) VALUES (null, '" + //$NON-NLS-1$
id.replaceAll("'", "''") + "', '" + //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
type.replaceAll("'", "''") + "', '" + //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
display.replaceAll("'", "''") + "', '" + //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
additionalValue.replaceAll("'", "''") + "', " + //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
Integer.toString(value) + ")"; //$NON-NLS-1$
Statement statement = _connection.createStatement();
statement.executeUpdate(query);
statement.close();
} catch (Exception e) {
Log.getInstance(HSQLController.class).warn("HSQL : Problem writing statistics : " + e.toString()); //$NON-NLS-1$
}
}
public void closeDatabase() {
try {
Statement statement = _connection.createStatement();
statement.executeQuery("SHUTDOWN"); //$NON-NLS-1$
statement.close();
_connection.close();
} catch (Exception e) {
Log.getInstance(HSQLController.class).warn("Error closing database."); //$NON-NLS-1$
}
}
public int getLibraryRecordCount() {
return _libraryRecordCount;
}
public void setLibraryRecordCount(int recordCount) {
_libraryRecordCount = recordCount;
Controller.getInstance().getStatisticsController().setLibraryRecordCount(_libraryRecordCount);
}
private String getDatabaseVersion() {
try {
Statement statement = _connection.createStatement();
ResultSet rs = statement.executeQuery("SELECT VALUE FROM DB_METADATA WHERE KEY='VERSION'"); //$NON-NLS-1$
statement.close();
if (rs.next())
return rs.getString("VALUE"); //$NON-NLS-1$
else
return ""; //$NON-NLS-1$
} catch (Exception e) {
Log.getInstance(HSQLController.class).warn("Error getting database version."); //$NON-NLS-1$
return ""; //$NON-NLS-1$
}
}
public boolean getLibraryTableReseted() {
return _boLibraryTableReseted;
}
}