public static void Backup(string sourceConnectionString, string appDataDirectory)
{
var now = DateTime.UtcNow;
var backupDirectory = Path.Combine(appDataDirectory, "Backup");
if (!Directory.Exists(backupDirectory))
Directory.CreateDirectory(backupDirectory);
if (HasBackupForToday(now, backupDirectory)) return;
var backupFile = "{0}.sqlite".F(now.ToString("yyyy-MM-dd_hh-mm-ss"));
backupFile = Path.Combine(backupDirectory, backupFile);
Logger.I("Database backup to {0}", backupFile);
string backupConnectionString = "data source={0}".F(backupFile);
using (var backupConnection = new SQLiteConnection(backupConnectionString))
using (var sourceConnection = new SQLiteConnection(sourceConnectionString))
{
backupConnection.Open();
sourceConnection.Open();
sourceConnection.BackupDatabase(backupConnection, "main", "main", -1, null, 0);
}
DeleteOldBackups(backupDirectory, 5);
}
/// <summary>
/// Builds database and run SQL.
/// </summary>
private static void BuildAndRunSQL()
{
if (_dataSet != null
&& _dataSet.Tables.Count > 0
&& _dataSet.Tables[0].Columns.Count > 0)
{
var columns = _dataSet.Tables[0].Columns.Count;
var rows = _dataSet.Tables[0].Rows.Count;
var columnNames = new List<string>(columns);
var tableName = SanitizeString(_options.TableName);
for (var i = 0; i < columns; i++)
{
var oldName = _dataSet.Tables[0].Columns[i].ColumnName;
var newName = SanitizeString(oldName);
if (_options.Verbose && oldName != newName)
{
WriteInfo(string.Format("Column {0} renamed to {1}", oldName, newName));
}
columnNames.Add(newName);
}
using (var connection = new SQLiteConnection("data source=" + _options.SaveTo))
{
connection.Open();
using (var cmdCreateTable = new SQLiteCommand(connection))
{
cmdCreateTable.CommandText = "CREATE TABLE IF NOT EXISTS "
+ SanitizeString(_options.TableName)
+ " ("
+ string.Join(" TEXT,", columnNames.ToArray())
+ " TEXT);";
cmdCreateTable.ExecuteNonQuery();
}
using (var cmdInsertValues = new SQLiteCommand(connection))
{
var transaction = connection.BeginTransaction();
cmdInsertValues.Transaction = transaction;
var sbQuery = new StringBuilder();
for (var i = 1; i < rows; i++)
{
sbQuery.AppendLine("INSERT INTO " + tableName + " VALUES (");
for (var j = 0; j < columns; j++)
{
sbQuery.Append("'" + _dataSet.Tables[0].Rows[i].ItemArray[j].ToString().SafeReplace("'", "''") + "'");
if (j < columns - 1)
{
sbQuery.Append(",");
}
}
sbQuery.Append(");");
}
cmdInsertValues.CommandText = sbQuery.ToString();
cmdInsertValues.ExecuteNonQuery();
transaction.Commit();
}
if (_options.Verbose)
{
WriteInfo(string.Format("Data loaded in {0} milliseconds", _stopwatch.ElapsedMilliseconds));
}
if (!string.IsNullOrWhiteSpace(_options.SQL))
{
_stopwatch.Restart();
using (var command = new SQLiteCommand(connection))
{
command.CommandText = _options.SQL;
Console.WriteLine(command.ExecuteScalar());
}
if (_options.Verbose)
{
WriteInfo(string.Format("Queries run in {0} milliseconds", _stopwatch.ElapsedMilliseconds));
}
}
if (_options.Console)
{
var appPath = Path.GetDirectoryName(Process.GetCurrentProcess().MainModule.FileName);
var sqliteExecutable = appPath + "\\sqlite3.exe";
if (File.Exists(sqliteExecutable))
{
string dbPath;
if (_options.SaveTo == ":memory:")
{
var tempPath = appPath + "\\temp.db";
if (File.Exists(tempPath))
{
File.Delete(tempPath);
}
var tempConnection = new SQLiteConnection("data source=" + tempPath);
tempConnection.Open();
connection.BackupDatabase(tempConnection, "main", "main", -1, null, 0);
dbPath = tempPath;
}
else
{
dbPath = _options.SaveTo;
}
var sqliteProcess = new Process
{
StartInfo =
{
FileName = sqliteExecutable,
Arguments = dbPath
}
};
sqliteProcess.StartInfo.UseShellExecute = false;
sqliteProcess.StartInfo.RedirectStandardOutput = true;
sqliteProcess.OutputDataReceived += (sender, args) => Console.WriteLine(args.Data);
sqliteProcess.Start();
sqliteProcess.BeginOutputReadLine();
sqliteProcess.WaitForExit();
}
else
{
ExitFailure("An error occured launching sqlite3 shell");
}
}
}
}
else
{
ExitFailure("Source File is empty");
}
}
private void Backup()
{
try
{
using (var src = new SQLiteConnection(SrcZip))
using (var dst = new SQLiteConnection(DstZip))
using (DisposeWatch.Start(e => Console.WriteLine($"Backup Completed in {e.TotalMilliseconds} ms")))
{
src.Open();
dst.Open();
Console.WriteLine("Start Copy");
src.BackupDatabase(dst, "main", "main", -1, null, 0);
Console.WriteLine("End Copy");
}
}
catch (Exception ex)
{
Console.WriteLine();
Console.WriteLine();
Console.WriteLine();
Console.WriteLine("Couldn't copy");
Console.WriteLine(ex.Message);
}
}
private void SaveTo(SQLiteConnection source, SQLiteConnection destination) {
bool saving = source.DataSource == "";
Stopwatch sw = new Stopwatch();
sw.Start();
source.BackupDatabase(destination, "main", "main", 1, this.SQLiteBackupCallback, 10);
sw.Stop();
Logger.Log("Database " + ((saving) ? "backed up" : "loaded") + " in " + sw.Elapsed);
return;
/*SQLiteCommand cmdSource = new SQLiteCommand(source);
cmdSource.CommandText = "Select * FROM sqlite_master WHERE type=='table'";
SQLiteDataReader masterReader = cmdSource.ExecuteReader();
while (masterReader.Read()) {
//sqlite_sequence,
NameValueCollection nvc = masterReader.GetValues();
if (nvc["name"] != "sqlite_sequence") {
Stopwatch s = new Stopwatch();
s.Start();
SQLiteCommand cmdDest = new SQLiteCommand(destination);
cmdDest.CommandText = "DROP TABLE IF EXISTS " + nvc["name"];
cmdDest.ExecuteNonQuery();
cmdDest.CommandText = nvc["sql"];
cmdDest.ExecuteNonQuery();
SQLiteCommand cmdSrc = new SQLiteCommand(source);
cmdSrc.CommandText = "SELECT * FROM " + nvc["name"] + " ORDER BY _ROWID_";
SQLiteDataReader dataReader = cmdSrc.ExecuteReader();
while (dataReader.Read()) {
NameValueCollection nvcRow = dataReader.GetValues();
string insert = "INSERT INTO " + nvc["name"] + " VALUES (";
bool valid = false;
for (int i = 0; i < nvcRow.Keys.Count; i++) {
insert += "\"" + nvcRow[i] + "\"";
if (i + 1 != nvcRow.Keys.Count)
insert += ", ";
if (nvcRow[i] != "") valid = true;
}
if (valid) {
cmdDest.CommandText = insert + ")";
cmdDest.ExecuteNonQuery();
}
}
s.Stop();
Logger.Log("Table " + nvc["name"] + ((saving)?" saved (":" loaded (") + s.Elapsed + ")");
}
}*/
}
// -------------------------------------------------------------------
// ----------------------- DATABASE FUNCTIONS ------------------------
// -------------------------------------------------------------------
private void AllTablesToMemory() {
// first get the FILE DB to memory
using (var source = new SQLiteConnection(_connectionpath))
using (var destination = new SQLiteConnection("Data Source=:memory:")) {
source.Open();
destination.Open();
source.BackupDatabase(destination, "main", "main", -1, null, 0);
// get all the Table Names
string query = "SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY 1";
DataTable tablenames = readTable(query, destination);
List<string> names = new List<string>();
foreach (DataRow row in tablenames.Rows) { names.Add(row.ItemArray[0].ToString()); }
// get all the Table Data
foreach (var name in names){
DataTable tmpdata = readTable("SELECT * FROM " + name, destination);
_data.Add(name, tmpdata);
}
}
}
//CreateParams backup of db
internal static void BackupDB()
{
originalDatabase = new SQLiteConnection(SQLiteConnectionString);
backupDatabase = new SQLiteConnection(backupDB);
originalDatabase.Open();
backupDatabase.Open();
originalDatabase.BackupDatabase(backupDatabase, "main", "main", -1, null, -1);
originalDatabase.Close();
backupDatabase.Close();
}
/// <summary>
/// Create a backup of database
/// </summary>
/// <param name="connection">Connection to database which we want to backup</param>
/// <param name="backupName">Name of the new database</param>
/// <returns>true if successful; false otherwise</returns>
private bool BackupDatabase(SQLiteConnection connection, string backupName)
{
try
{
string dataSource = Path.Combine(
Path.GetDirectoryName(LibraryConfiguration.Instance.StoragePath) ?? String.Empty, backupName);
var connectionStringBuilder = new SQLiteConnectionStringBuilder()
{
DataSource = dataSource,
Version = 3,
//Set page size to NTFS cluster size = 4096 bytes
PageSize = 4096,
CacheSize = 10000,
JournalMode = SQLiteJournalModeEnum.Wal,
Pooling = true,
ForeignKeys = true,
LegacyFormat = false,
FailIfMissing = false
};
using (var backupConnection = new SQLiteConnection(connectionStringBuilder.ToString()))
{
backupConnection.Open();
connection.BackupDatabase(backupConnection, "main", "main", -1, null, -1);
}
return true;
}
catch (Exception ex)
{
_log.Error("Unable to backup database.", ex);
return false;
}
}
/// <summary>备份数据库
/// </summary>
/// <param name="isFileToMemory">是否是文件数据库备份到内存数据库；
/// isFileToMemory为true指的是从文件数据库导入到当前内存数据库；
/// isFileToMemory为false指的是从当前内存数据库导出到文件数据库。
/// </param>
private void BackupDatabase(bool isFileToMemory)
{
using (SQLiteConnection dbfileConnection = GetCon(false))
{
memoryConn = GetCon(true);
//如果连接是关闭状态就打开
if (dbfileConnection.State == ConnectionState.Closed)
{
dbfileConnection.Open();
}
if (memoryConn.State == ConnectionState.Closed)
{
memoryConn.Open();
}
if (isFileToMemory)
{
dbfileConnection.BackupDatabase(memoryConn, "main", "main", -1, null, 0);
//memoryConn = dbmemConnection;
useInMemory = true;
}
else
{
memoryConn.BackupDatabase(dbfileConnection, "main", "main", -1, null, 0);
}
}
}
public override void Initialize(String conString)
{
if (InMemory)
{
try
{
SQLiteConnection sourceConnection = new SQLiteConnection(conString);
SQLiteConnection destinationConnection = new SQLiteConnection("Data Source=:memory:");
sourceConnection.Open();
destinationConnection.Open();
sourceConnection.BackupDatabase(destinationConnection, "main", "main", -1, Callback, 0);
sourceConnection.Close();
destinationConnection.Close();
Connection = destinationConnection;
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
}
}
else
{
Connection = new SQLiteConnection(conString);
}
}
async static void BuildDatabase(string fileName, IEnumerable<Entry> entries)
{
try
{
ResourceExtractor.ExtractResourceToFile("awagame.sqlite.dll", "SQLite.Interop.dll");
}
catch
{
Console.WriteLine("[ERROR] Can not write SQLite library.");
}
try
{
File.Delete(fileName);
}
catch (IOException)
{
}
finally
{
SQLiteConnection.CreateFile(fileName);
}
SQLiteConnection database = new SQLiteConnection("Data Source=:memory:;Version=3;"); //use a memory database for speed purposes
SQLiteConnection disk = new SQLiteConnection("Data Source=" + fileName + ";Version=3;");
database.Open();
using (var sqlCommand = new SQLiteCommand(@"CREATE TABLE IF NOT EXISTS roms(
gamename TEXT,
romname TEXT,
size TEXT,
crc TEXT,
md5 TEXT,
sha1 TEXT PRIMARY KEY,
romID TEXT,
datName TEXT,
datSource TEXT,
datDate TEXT
)", database))
{
sqlCommand.ExecuteNonQuery();
}
foreach (Entry gameEntry in entries)
{
using (var sqlCommand = new SQLiteCommand(@"INSERT OR IGNORE INTO roms VALUES(
@gamename,
@romname,
@size,
@crc,
@md5,
@sha1,
@romID,
@datName,
@datSource,
@datDate)", database))
{
sqlCommand.Parameters.AddWithValue("@gamename", gameEntry.GameName);
sqlCommand.Parameters.AddWithValue("@romname", gameEntry.RomFileName);
sqlCommand.Parameters.AddWithValue("@size", gameEntry.RomSize);
sqlCommand.Parameters.AddWithValue("@crc", gameEntry.HashCRC32);
sqlCommand.Parameters.AddWithValue("@md5", gameEntry.HashMD5);
sqlCommand.Parameters.AddWithValue("@sha1", gameEntry.HashSHA1);
sqlCommand.Parameters.AddWithValue("@romID", gameEntry.OpenVGDB_RomID);
sqlCommand.Parameters.AddWithValue("@datName", gameEntry.DatName);
sqlCommand.Parameters.AddWithValue("@datSource", gameEntry.DatSource);
sqlCommand.Parameters.AddWithValue("@datDate", gameEntry.DatDate);
await sqlCommand.ExecuteNonQueryAsync();
if (Program.Verbose)
{
await Console.Out.WriteLineAsync(String.Format("[INFO] Added ROM record {0} with game record {1} (SHA {2})",
gameEntry.RomFileName, gameEntry.GameName, gameEntry.HashSHA1));
}
}
}
disk.Open();
Console.WriteLine("[INFO] Saving Database to " + fileName);
try
{
database.BackupDatabase(disk, "main", "main", -1, null, 0);
}
catch
{
Console.WriteLine("[ERROR] Could not save SQLite file (File in use?).");
}
Console.WriteLine("Saved to " + fileName);
disk.Close();
database.Close();
}
