org.lyra.Database
Class Interface

java.lang.Object
  org.lyra.Database.Interface

public class Interface
extends java.lang.Object

The database interface exists to implement additional functionality and to provide a certain speed increase by caching the music database.

Version:

1.0.5, %Revision, 174%, %LastChangedDate, 27/10/12 3:33PM%

Author:

Martin Foster

Nested Class Summary

private class

Interface.myConnections For some reason it is difficult to properly separate preparation and query results running without running into trouble.

Field Summary

private static java.lang.String	CLASS
private java.sql.Connection	Conn
private static java.lang.String	CR
private static java.lang.String	HOST
private static java.lang.String	LANG
private Ledger	Logger
private java.lang.String	myQuery
private java.sql.PreparedStatement	myUpdates

Constructor Summary

Interface(Ledger logger)
Standard constructor for this class.

Method Summary

private boolean	Attach(java.io.File db, java.lang.String name) Certain elements of the system require that other elements be attached to the primary database.
boolean	attachHost(java.io.File db) Attach a new host element to the database.
boolean	attachLang(java.io.File db) Attach a new language element to the database.
boolean	Connect(java.io.File path) Will conduct the initial connection and logging to the SQLite database store.
void	debugDatabase() When logging levels are set to CONFIG a significant amount of information will be handed to the user about the database and how it operates.
private void	Detach(java.lang.String name) The DETACH command will permit the separation of a database from the main.
void	detachHost() Detaches the currently connected host database from the main entry.
void	detachLang() Detaches the currently connected language databases from the main entry.
void	Disconnect() Will cleanly close the database connection in order to avoid any potential file locks that could be introduced otherwise.
void	getColumn(java.util.List<java.lang.String> list, java.lang.String query, java.lang.String... parameters) An extension of the Query method.
java.lang.Integer	getInt(java.lang.String query, java.lang.String... parameters) Will return a single element if so required.
java.sql.DatabaseMetaData	getMetaDeta() Direct pass through to retrieve the MetaData from the database elements themselves.
void	getProperties(java.util.Map<java.lang.String,java.lang.String> prop, java.lang.String query) Generates a Map based on the rows returned.
java.util.Map<java.lang.String,java.lang.String>	getRow(java.lang.String query, java.lang.String... parameters) This method is useful for a multiple column result.
void	getRows(java.util.List<java.util.Map<java.lang.String,java.lang.String>> list, java.lang.String query, java.lang.String... parameters) This method is useful for a multiple column result.
java.lang.String[][]	getRows(java.lang.String rowQuery, java.lang.String mainQuery, java.lang.String... parameters) A query which will return multiple rows must make use of this mechanism since the getColumn variant only returns a singular column with one or more rows of data.
java.util.Map<java.lang.String,java.util.Map<java.lang.String,java.lang.String>>	getSet(java.lang.String query, java.lang.String... parameters) A query which is meant to return a large data set back as a complex data structure containing a Map within a Map.
java.lang.String	getString(java.lang.String query, java.lang.String... parameters) Will return a single element if so required.
Interface.myConnections	Query(java.lang.String query, java.lang.String... parameters) One of the more critical components of this Class is the ability to abstract the SQL Query process.
private void	QueryDebug(java.lang.String method, java.lang.String query, java.lang.String... parameters) Debugging capability for the query string handler.
boolean	updateCommence() Rarely used method which allows for a myriad of queries to be added into a singular transaction in order to prevent out of dependency updates.
boolean	updateComplete() Cleans up the update process created by multiple row updates setting up the process for another pass if necessary.
boolean	updateMultipleRows(java.lang.String query, java.lang.String... parameters) Provides the ability to update and create elements in the database while providing an audit table.
boolean	updateSingleRows(java.lang.String query, java.lang.String... parameters) Provides the ability to update and create elements in the database while providing an audit table.
void	Vacuum() The VACUUM command which cleans up the database structure and can shrink up used space considerably.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

Logger

private Ledger Logger

Conn

private java.sql.Connection Conn

myUpdates

private java.sql.PreparedStatement myUpdates

myQuery

private java.lang.String myQuery

CR

private static final java.lang.String CR

CLASS

private static final java.lang.String CLASS

See Also:

Constant Field Values

HOST

private static final java.lang.String HOST

See Also:

Constant Field Values

LANG

private static final java.lang.String LANG

See Also:

Constant Field Values

Constructor Detail

Interface

public Interface(Ledger logger)

Standard constructor for this class. This will create a database element if possible. If this fails during the Connect phase then the Class calling this store should not continue with execution.

Parameters:

logger - Logging capability.

Method Detail

Connect

public boolean Connect(java.io.File path)

Will conduct the initial connection and logging to the SQLite database store. If there are problems this component will return false and the program can terminate gracefully.

Parameters:

path - File containing path to the database.

Returns:

boolean True/False statement about successful connection.

Disconnect

public void Disconnect()

Will cleanly close the database connection in order to avoid any potential file locks that could be introduced otherwise.

debugDatabase

public void debugDatabase()

When logging levels are set to CONFIG a significant amount of information will be handed to the user about the database and how it operates.

The first round of information pulled from the database will be SQLite Meta Data. This can be especially useful to determine exactly what you are connected to.

getURL	The connection string used by the JDBC driver. Useful to find out if the paths are correct.
getUserName	The user name of the database connection. Normally this will never apply to a SQLite database.
getDatabaseProductName	Database name. This will normally always appear as SQLite
getDatabaseProductVersion	The version of the database file which is supported. Normally 3.6.4.
getDriverName	This should indicate SQLiteJDBC as the code only employs this driver.
getDriverVersion	Normally you would get a version number. However with the JDBC driver used you will either get native to indicate native drivers are used or java to indicate the driver is using slower drivers for compatibility

Information about the SQLite PRAGMA system is also included here. Some of the features may not be supported due to version issues. If that is the case the NOT SUPPORTED descriptor will be appended instead of the proper database value. The current PRAGMA types searched for are as follows:

PRAGMA auto_vacuum	Query or set the auto-vacuum status in the database.
PRAGMA cache_size	Query or change the suggested maximum number of database disk pages that SQLite will hold in memory at once per open database file.
PRAGMA case_sensitive_like	The default behaviour of the LIKE operator is to ignore case for ASCII characters. Hence, by default 'a' LIKE 'A' is true.
PRAGMA count_changes	Query or change the count-changes flag.
PRAGMA default_cache_size	This pragma queries or sets the suggested maximum number of pages of disk cache that will be allocated per open database file.
PRAGMA empty_result_callbacks	Query or change the empty-result-callbacks flag.
PRAGMA encoding	In first form, if the main database has already been created, then this pragma returns the text encoding used by the main database, one of "UTF-8", "UTF-16le" (little-endian UTF-16 encoding) or "UTF-16be" (big-endian UTF-16 encoding).
PRAGMA foreign_keys	Query, set, or clear the enforcement of foreign key constraints.
PRAGMA full_column_names	Query or change the full_column_names flag.
PRAGMA fullfsync	Query or change the fullfsync flag.
PRAGMA journal_mode	This pragma queries or sets the journal mode for databases associated with the current database connection.
PRAGMA journal_size_limit	If a database connection is operating in either "exclusive mode" (PRAGMA locking_mode=exclusive) or "persistent journal mode" (PRAGMA journal_mode=persist) then under certain circumstances after committing a transaction the journal file may remain in the file-system.
PRAGMA legacy_file_format	This pragma sets or queries the value of the legacy_file_format flag. When this flag is on, new SQLite databases are created in a file format that is readable and writable by all versions of SQLite going back to 3.0.0. When the flag is off, new databases are created using the latest file format which might not be readable or writable by versions of SQLite prior to 3.3.0.
PRAGMA locking_mode	This pragma sets or queries the database connection locking-mode. The locking-mode is either NORMAL or EXCLUSIVE.
PRAGMA page_size	Query or set the page size of the database.
PRAGMA max_page_count	Query or set the maximum number of pages in the database file.
PRAGMA read_uncommitted	Query, set, or clear READ UNCOMMITTED isolation.
PRAGMA recursive_triggers	Query, set, or clear the recursive trigger capability.
PRAGMA reverse_unordered_selects	When enabled, this PRAGMA causes SELECT statements without a an ORDER BY clause to emit their results in the reverse order of what they normally would.
PRAGMA short_column_names	Query or change the short-column-names flag.
PRAGMA synchronous	Query or change the setting of the "synchronous" flag.
PRAGMA temp_store	Query or change the setting of the "temp_store" parameter.
PRAGMA temp_store_directory	Query or change the setting of the "temp_store_directory" - the directory where files used for storing temporary tables and indices are kept.

Additionally a complete list of all tables will also be displayed. This can be a great asset when determining if tables have been added or removed.

Query

public Interface.myConnections Query(java.lang.String query,
                                     java.lang.String... parameters)

One of the more critical components of this Class is the ability to abstract the SQL Query process.

Parameters:

query - A string defining the queries

parameters - Elements of string which contain the elements

Returns:

myConnections

QueryDebug

private void QueryDebug(java.lang.String method,
                        java.lang.String query,
                        java.lang.String... parameters)

Debugging capability for the query string handler. Used to format the query text as an ability to run the query independently if need be.

Parameters:

query - A string defining the queries

parameters - Elements of string which contain the elements

getRow

public java.util.Map<java.lang.String,java.lang.String> getRow(java.lang.String query,
                                                               java.lang.String... parameters)

This method is useful for a multiple column result. It will determine the column names and then use those to create a hash map.

Parameters:

query - String query to generate.

parameters - Array of parameters to append to the query.

Returns:

HashMap results.

getRows

public void getRows(java.util.List<java.util.Map<java.lang.String,java.lang.String>> list,
                    java.lang.String query,
                    java.lang.String... parameters)

This method is useful for a multiple column result. It will determine the column names and then use those to create a hash map and additionally throw it into a list.

Parameters:

list - List containing all of the results.

query - String query to generate.

parameters - Array of parameters to append to the query.

getRows

public java.lang.String[][] getRows(java.lang.String rowQuery,
                                    java.lang.String mainQuery,
                                    java.lang.String... parameters)

A query which will return multiple rows must make use of this mechanism since the getColumn variant only returns a singular column with one or more rows of data.

Parameters:

rowQuery - A variant of the main query which will return a count of rows to return.

mainQuery - The main query which will return all rows requested.

parameters - Parameters necessary to run both the Row and Main query.

Returns:

A multi-level array with all of the results.

getSet

public java.util.Map<java.lang.String,java.util.Map<java.lang.String,java.lang.String>> getSet(java.lang.String query,
                                                                                               java.lang.String... parameters)

A query which is meant to return a large data set back as a complex data structure containing a Map within a Map. Which is primarily used for quick lookups when confirming changes to the database.

Note: The first row must be unique else the map will override.

Parameters:

query - String query to generate.

parameters - Array of parameters to append to the query.

Returns:

Map data type containing all results.

getColumn

public void getColumn(java.util.List<java.lang.String> list,
                      java.lang.String query,
                      java.lang.String... parameters)

An extension of the Query method. This one will take a List as a parameter and fill it in accordingly based on the Query generated.

Parameters:

list - ArrayList to populate.

query - Query to execute on the Database.

parameters - Array of strings to pass on as parameters.

getInt

public java.lang.Integer getInt(java.lang.String query,
                                java.lang.String... parameters)

Will return a single element if so required. Since it can always return a null set, this will also ensure that a value is always returned as necessary.

Parameters:

query - Query to execute on Database.

parameters - Array of strings containing parameters to pass on.

Returns:

Integer value as requested.

getString

public java.lang.String getString(java.lang.String query,
                                  java.lang.String... parameters)

Will return a single element if so required. Since it can always return a null set, this will also ensure that a value is always returned as necessary.

Parameters:

query - Query to execute on Database.

parameters - Array of strings containing parameters to pass on.

Returns:

Integer value as requested.

getProperties

public void getProperties(java.util.Map<java.lang.String,java.lang.String> prop,
                          java.lang.String query)

Generates a Map based on the rows returned. As the name implies this is mostly used for properties such as language elements and global settings.

Parameters:

prop - Property Map to store the results.

query - Query string containing elements to run.

getMetaDeta

public java.sql.DatabaseMetaData getMetaDeta()
                                      throws java.sql.SQLException

Direct pass through to retrieve the MetaData from the database elements themselves.

Returns:

DatabaseMetaData instance.

Throws:

java.sql.SQLException

updateMultipleRows

public boolean updateMultipleRows(java.lang.String query,
                                  java.lang.String... parameters)

Provides the ability to update and create elements in the database while providing an audit table. This variant will remain active in order to speed up multiple row updates to the same table.

Parameters:

query - A string defining the queries

parameters - Elements of string which contain the elements

Returns:

Boolean value which indicates success.

updateSingleRows

public boolean updateSingleRows(java.lang.String query,
                                java.lang.String... parameters)

Provides the ability to update and create elements in the database while providing an audit table. A single row will neatly clean up after itself.

Parameters:

query - A string defining the queries

parameters - Elements of string which contain the elements

Returns:

Boolean value which indicates success in operation.

Attach

private boolean Attach(java.io.File db,
                       java.lang.String name)

Certain elements of the system require that other elements be attached to the primary database. This permits the language and site elements to be cached without duplicating settings and configuration elements at every startup.

Parameters:

db - File element containing location of database to attach.

name - String containing the identifier to use for table lookup.

Returns:

Success of the connection.

attachHost

public boolean attachHost(java.io.File db)

Attach a new host element to the database. This is a pass-through for the main Attach method.

Parameters:

db - Path to the database.

Returns:

Boolean indicating success of connection

attachLang

public boolean attachLang(java.io.File db)

Attach a new language element to the database. This is a pass-through for the main Attach method.

Parameters:

db - Path to the database.

Returns:

Boolean indicating success of attachment.

Detach

private void Detach(java.lang.String name)

The DETACH command will permit the separation of a database from the main. This can permit the changing of hosts without leaving the application.

Parameters:

name - String containing name of database to detach from.

detachHost

public void detachHost()

Detaches the currently connected host database from the main entry. Permitting the addition of a new entry,

detachLang

public void detachLang()

Detaches the currently connected language databases from the main entry. Unlike host, this method will rarely be used.

updateCommence

public boolean updateCommence()

Rarely used method which allows for a myriad of queries to be added into a singular transaction in order to prevent out of dependency updates.

Returns:

Boolean indicating success of the operation.

updateComplete

public boolean updateComplete()

Cleans up the update process created by multiple row updates setting up the process for another pass if necessary.

Returns:

Boolean indicating success of the operation.

Vacuum

public void Vacuum()

The VACUUM command which cleans up the database structure and can shrink up used space considerably.