/// <summary>
/// Creates and opens a database.
/// </summary>
/// <param name="name">Database name.</param>
/// <returns>Created database, or null if cannot be created.</returns>
public override Database CreateDatabase(string name)
{
Database database = null;
try
{
database = new Database(name, DEFAULT_DATABASE_OPTION_NEW, DEFAULT_DATABASE_OPTION_COMPRESS);
SQLiteConnection connection = new SQLiteConnection(
"Data Source=" + Path.Combine(GetBasePath(),name) +
";Version=" + DEFAULT_DATABASE_VERSION +
";New=" + DEFAULT_DATABASE_OPTION_NEW +
";Compress=" + DEFAULT_DATABASE_OPTION_COMPRESS + ";");
connection.Open();
StoreConnection(name, connection);
StoreDatabase(name, database);
}
catch (Exception e)
{
#if DEBUG
Console.WriteLine("Exception creating database: " + e.Message);
#endif
database = null;
}
return database;
}
/// <summary>
/// Creates and opens a database.
/// </summary>
/// <param name="name">Database name.</param>
/// <returns>Created database, or null if cannot be created.</returns>
public override Database CreateDatabase(string name)
{
Database database = null;
try
{
database = new Database(name, DEFAULT_DATABASE_OPTION_NEW, DEFAULT_DATABASE_OPTION_COMPRESS);
string dbPath = Path.Combine(GetBasePath(), name);
// Checks if a file already exists, if not creates the file.
bool exists = File.Exists (dbPath);
if (!exists)
{
SqliteConnection.CreateFile (dbPath);
}
SqliteConnection connection = new SqliteConnection(
"Data Source=" + dbPath +
",version=" + DEFAULT_DATABASE_VERSION);
connection.Open();
StoreConnection(name, connection);
StoreDatabase(name, database);
}
catch (Exception e)
{
SystemLogger.Log(SystemLogger.Module.PLATFORM, "Exception creating database.", e);
database = null;
}
return database;
}
/// <summary>
/// Executes SQL statement, on the given database.
/// </summary>
/// <param name="db">Database reference.</param>
/// <param name="statement">Statement to be executed.</param>
/// <param name="statementParams">Replacement parameters to be applied, by order, on the statement string.</param>
/// <returns></returns>
public override bool ExecuteSQLStatement(Database db, string statement, string[] statementParams)
{
bool result = false;
if (db != null)
{
DbConnection connection = GetConnection(db.Name);
if (connection != null)
{
string SQL = statement;
try
{
if (statementParams != null)
{
SQL = String.Format(SQL, statementParams);
}
SQLiteDataAdapter adapter = new SQLiteDataAdapter(SQL, (SQLiteConnection)connection);
adapter.Fill(new DataSet());
result = true;
}
catch (Exception e)
{
#if DEBUG
Console.WriteLine("Exception replacement strings on statement: " + e.Message);
#endif
}
}
else
{
#if DEBUG
Console.WriteLine("SQLiteConnection not found for database name: " + db.Name);
#endif
}
}
else
{
#if DEBUG
Console.WriteLine("Provided database is null");
#endif
}
return result;
}
/// <summary>
/// Creates a table inside the given database, using the provided columns definitions.
/// </summary>
/// <param name="db">Database reference.</param>
/// <param name="tableName">New table name.</param>
/// <param name="columnDefinitions">Columns definitions (sqlite syntax).</param>
/// <returns>True if successful table creation.</returns>
public bool CreateTable(Database db, string tableName, string[] columnDefinitions)
{
bool result = false;
if (db != null) {
try {
DbConnection connection = GetConnection (db.Name);
if (connection != null) {
StringBuilder buffer = new StringBuilder ();
string columnDefs = "";
foreach (string columnDef in columnDefinitions) {
buffer.Append ((buffer.Length > 0 ? "," + columnDef : columnDef));
}
if (buffer.Length > 0) {
columnDefs = "(" + buffer.ToString () + ")";
}
string SQL = "CREATE TABLE " + tableName + columnDefs;
SystemLogger.Log (SystemLogger.Module .CORE, "SQL to execute: " + SQL);
DbCommand command = connection.CreateCommand ();
command.CommandText = SQL;
command.CommandType = CommandType.Text;
command.ExecuteNonQuery ();
result = true;
} else {
SystemLogger.Log (SystemLogger.Module .CORE, "SQLiteConnection not found for database name: " + db.Name);
}
} catch (Exception e) {
SystemLogger.Log (SystemLogger.Module .CORE, "Exception creating table[" + tableName + "] on database[" + db.Name + "]", e);
}
} else {
SystemLogger.Log (SystemLogger.Module .CORE, "Provided database is null");
}
return result;
}
/// <summary>
/// Adds the given database to the dictionary.
/// </summary>
/// <param name="key">The key to apply when adding to dictionary for the given database.</param>
/// <param name="connection">Database to be added.</param>
protected void StoreDatabase(string key, Database database)
{
if (Databases == null) {
Databases = new Dictionary<string, Database> ();
}
Databases [key] = database;
}
/// <summary>
/// Returns a list of table names for the given database.
/// </summary>
/// <param name="db">
/// A <see cref="Database"/>
/// </param>
/// <returns>
/// A <see cref="System.String[]"/>
/// </returns>
public string[] GetTableNames(Database db)
{
List<string> list = new List<string> ();
if (db != null) {
DbConnection connection = GetConnection (db.Name);
if (connection != null) {
DataTable dt = connection.GetSchema ("TABLES");
foreach (DataRow row in dt.Rows) {
foreach (DataColumn col in dt.Columns) {
//Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
if (col.ColumnName == "TABLE_NAME") {
list.Add (row [col].ToString ());
}
}
}
} else {
SystemLogger.Log (SystemLogger.Module.PLATFORM,
"SQLiteConnection not found for database name: " + db.Name);
}
} else {
SystemLogger.Log (SystemLogger.Module.PLATFORM, "Provided database is null");
}
return list.ToArray ();
}
/// <summary>
/// Checks whether a table (given its name) exists on the given database.
/// </summary>
/// <param name="db">
/// A <see cref="Database"/>
/// </param>
/// <param name="tableName">
/// A <see cref="System.String"/>
/// </param>
/// <returns>
/// A <see cref="System.Boolean"/>
/// </returns>
public bool Exists(Database db, string tableName)
{
bool exists = false;
if (db != null) {
DbConnection connection = GetConnection (db.Name);
if (connection != null) {
DataTable dt = connection.GetSchema ("TABLES", new string[] {
null,
null,
tableName,
null
});
if (dt.Rows.Count > 0) {
exists = true;
}
} else {
SystemLogger.Log (SystemLogger.Module.PLATFORM,
"SQLiteConnection not found for database name: " + db.Name);
}
} else {
SystemLogger.Log (SystemLogger.Module.PLATFORM, "Provided database is null");
}
return exists;
}
/// <summary>
/// Checks whether the given database exists or not.
/// </summary>
/// <param name="db">Database to be checked.</param>
/// <returns>True if database exists.</returns>
public bool Exists(Database db)
{
return ExistsDatabase (db.Name);
}
public abstract bool ExecuteSQLTransaction(Database db, string[] statements, bool rollbackFlag);
/// <summary>
/// Executes a SQL Query that should return data.
/// </summary>
/// <param name="db">Database reference.</param>
/// <param name="queryText">Query to be executed.</param>
/// <param name="queryParams">Values to be replaced on query string.</param>
/// <returns>Result set.</returns>
public override IResultSet ExecuteSQLQuery(Database db, string queryText, string[] queryParams)
{
IResultSet resultSet = null;
if (db != null)
{
DbConnection connection = GetConnection(db.Name);
if (connection != null)
{
string queryString = queryText;
try
{
if (queryParams != null)
{
queryString = String.Format(queryString, queryParams);
}
DataSet ds = new DataSet();
SQLiteDataAdapter adapter = new SQLiteDataAdapter(queryString, (SQLiteConnection)connection);
adapter.Fill(ds);
resultSet = new ResultSet(ds);
}
catch (Exception e)
{
#if DEBUG
Console.WriteLine("Exception replacement strings on statement: " + e.Message);
#endif
}
}
else
{
#if DEBUG
Console.WriteLine("SQLiteConnection not found for database name: " + db.Name);
#endif
}
}
else
{
#if DEBUG
Console.WriteLine("Provided database is null");
#endif
}
return resultSet;
}
public abstract IResultSet ExecuteSQLQuery(Database db, string queryText, string[] queryParams);
/// <summary>
/// Executes a SQL Query that should return data (without query replacement values).
/// </summary>
/// <param name="db">Database reference.</param>
/// <param name="queryText">Query to be executed.</param>
/// <returns>Result set.</returns>
public IResultSet ExecuteSQLQuery(Database db, string queryText)
{
return ExecuteSQLQuery (db, queryText, null);
}
/// <summary>
/// Deletes table - given the table name - on the given database.
/// </summary>
/// <param name="db">Database reference.</param>
/// <param name="tableName">Table name.</param>
/// <returns>True on successful deletion.</returns>
public bool DeleteTable(Database db, string tableName)
{
bool result = false;
if (db != null) {
try {
DbConnection connection = GetConnection (db.Name);
if (connection != null) {
string SQL = "DROP TABLE " + tableName;
SystemLogger.Log (SystemLogger.Module .CORE, "SQL to execute: " + SQL);
DbCommand command = connection.CreateCommand ();
command.CommandText = SQL;
command.CommandType = CommandType.Text;
command.ExecuteNonQuery ();
result = true;
} else {
SystemLogger.Log (SystemLogger.Module .CORE, "SQLiteConnection not found for database name: " + db.Name);
}
} catch (Exception e) {
SystemLogger.Log (SystemLogger.Module .CORE, "Exception deleting table[" + tableName + "] on database[" + db.Name + "]", e);
}
} else {
SystemLogger.Log (SystemLogger.Module .CORE, "Provided database is null");
}
return result;
}
/// <summary>
/// Deletes the given database.
/// </summary>
/// <param name="db">Database reference to be deleted.</param>
/// <returns>True on successful deletion.</returns>
public bool DeleteDatabase(Database db)
{
bool result = false;
if (db != null) {
DbConnection connection = GetConnection (db.Name);
if (connection != null) {
connection.Close ();
RemoveConnection (db.Name);
RemoveDatabase (db.Name);
DeleteDatabaseFile (db.Name);
connection = null;
result = true;
}
}
return result;
}
/// <summary>
/// Executing SQL Statement
/// (using Mono/monotouch apis for SqliteConnection and SqliteDataAdapter classes).
/// </summary>
/// <param name="db">
/// A <see cref="Database"/>
/// </param>
/// <param name="statement">
/// A <see cref="System.String"/>
/// </param>
/// <param name="statementParams">
/// A <see cref="System.String[]"/>
/// </param>
/// <returns>
/// A <see cref="System.Boolean"/>
/// </returns>
public override bool ExecuteSQLStatement(Database db, string statement, string[] statementParams)
{
bool result = false;
if (db != null)
{
DbConnection connection = GetConnection(db.Name);
if (connection != null)
{
string SQL = statement;
try
{
if (statementParams != null)
{
SQL = String.Format(SQL, statementParams);
}
SqliteDataAdapter adapter = new SqliteDataAdapter(SQL, (SqliteConnection)connection);
adapter.Fill(new DataSet());
result = true;
}
catch (Exception e)
{
SystemLogger.Log(SystemLogger.Module.PLATFORM, "Exception replacement strings on statement.", e);
}
}
else
{
SystemLogger.Log(SystemLogger.Module.PLATFORM, "SQLiteConnection not found for database name: " + db.Name);
}
}
else
{
SystemLogger.Log(SystemLogger.Module.PLATFORM, "Provided database is null");
}
return result;
}
/// <summary>
/// Executing SQL Transaction
/// (using Mono/monotouch apis for SqliteConnection, SqliteTransaction and SqliteCommand classes).
/// </summary>
/// <param name="db">
/// A <see cref="Database"/>
/// </param>
/// <param name="statements">
/// A <see cref="System.String[]"/>
/// </param>
/// <param name="rollbackFlag">
/// A <see cref="System.Boolean"/>
/// </param>
/// <returns>
/// A <see cref="System.Boolean"/>
/// </returns>
public override bool ExecuteSQLTransaction(Database db, string[] statements, bool rollbackFlag)
{
bool result = false;
if (db != null)
{
SqliteConnection connection = (SqliteConnection)GetConnection(db.Name);
if (connection != null)
{
// Start a local transaction.
SqliteTransaction transaction = connection.BeginTransaction();
// Enlist a command in the current transaction.
SqliteCommand command = connection.CreateCommand();
command.Transaction = transaction;
try
{
// Execute separate commands.
foreach (string statement in statements)
{
command.CommandText = statement;
command.ExecuteNonQuery();
}
// Commit the transaction.
transaction.Commit();
result = true;
}
catch (Exception ex)
{
// Handle the exception if the transaction fails to commit.
SystemLogger.Log(SystemLogger.Module.PLATFORM, "SQL Transaction fails to commit.", ex);
try
{
if (rollbackFlag)
{
// Attempt to roll back the transaction.
transaction.Rollback();
}
else
{
// No rollback specified.
SystemLogger.Log(SystemLogger.Module.PLATFORM, "No rollback specified.");
// Commit the transaction (sucess statements, previous to exception, are committed)
transaction.Commit();
}
}
catch (Exception exRollback)
{
// Throws an InvalidOperationException if the connection
// is closed or the transaction has already been rolled
// back on the server.
SystemLogger.Log(SystemLogger.Module.PLATFORM, "Error when doing Rollback on SQL Transaction.", exRollback);
}
}
}
else
{
SystemLogger.Log(SystemLogger.Module.PLATFORM, "SQLiteConnection not found for database name: " + db.Name);
}
}
else
{
SystemLogger.Log(SystemLogger.Module.PLATFORM, "Provided database is null");
}
return result;
}
/// <summary>
/// Executes an SQL statements without parameter replacements.
/// </summary>
/// <param name="db">Database reference.</param>
/// <param name="statement">Statement to be executed.</param>
/// <returns>True on successful execution.</returns>
public bool ExecuteSQLStatement(Database db, string statement)
{
return ExecuteSQLStatement (db, statement, null);
}
/// <summary>
///
/// </summary>
/// <param name="db">
/// A <see cref="Database"/>
/// </param>
/// <param name="queryText">
/// A <see cref="System.String"/>
/// </param>
/// <param name="queryParams">
/// A <see cref="System.String[]"/>
/// </param>
/// <returns>
/// A <see cref="IResultSet"/>
/// </returns>
public override IResultSet ExecuteSQLQuery(Database db, string queryText, string[] queryParams)
{
IResultSet resultSet = null;
if (db != null)
{
DbConnection connection = GetConnection(db.Name);
if (connection != null)
{
string queryString = queryText;
try
{
if (queryParams != null)
{
queryString = String.Format(queryString, queryParams);
}
DataSet ds = new DataSet();
SqliteDataAdapter adapter = new SqliteDataAdapter(queryString, (SqliteConnection)connection);
adapter.Fill(ds);
resultSet = new ResultSet(ds);
}
catch (Exception e)
{
SystemLogger.Log(SystemLogger.Module.PLATFORM, "Exception replacement strings on statement.", e);
}
}
else
{
SystemLogger.Log(SystemLogger.Module.PLATFORM, "SQLiteConnection not found for database name: " + db.Name);
}
}
else
{
SystemLogger.Log(SystemLogger.Module.PLATFORM, "Provided database is null");
}
return resultSet;
}
public abstract bool ExecuteSQLStatement(Database db, string statement, string[] statementParams);
