internal FileStore(FileInfo storeFile) { StoreFile = storeFile; OpenConnection(storeFile, out connection); SetupDatabase(); select = connection.CreateCommand(); select.CommandText = "SELECT data FROM store WHERE key = ? AND size = ? AND time = ?"; select.Parameters.Add(selectKey = select.CreateParameter()); selectKey.DbType = DbType.String; select.Parameters.Add(selectSize = select.CreateParameter()); selectSize.DbType = DbType.Int64; select.Parameters.Add(selectTime = select.CreateParameter()); selectTime.DbType = DbType.Int64; selectCover = connection.CreateCommand(); selectCover.CommandText = "SELECT cover FROM store WHERE key = ? AND size = ? AND time = ?"; selectCover.Parameters.Add(selectCoverKey = select.CreateParameter()); selectCoverKey.DbType = DbType.String; selectCover.Parameters.Add(selectCoverSize = select.CreateParameter()); selectCoverSize.DbType = DbType.Int64; selectCover.Parameters.Add(selectCoverTime = select.CreateParameter()); selectCoverTime.DbType = DbType.Int64; insert = connection.CreateCommand(); insert.CommandText = "INSERT OR REPLACE INTO store " + "VALUES(@key, @size, @time, @data, COALESCE(@cover, (SELECT cover FROM store WHERE key = @key)))"; insert.Parameters.Add(insertKey = select.CreateParameter()); insertKey.DbType = DbType.String; insertKey.ParameterName = "@key"; insert.Parameters.Add(insertSize = select.CreateParameter()); insertSize.DbType = DbType.Int64; insertSize.ParameterName = "@size"; insert.Parameters.Add(insertTime = select.CreateParameter()); insertTime.DbType = DbType.Int64; insertTime.ParameterName = "@time"; insert.Parameters.Add(insertData = select.CreateParameter()); insertData.DbType = DbType.Binary; insertData.ParameterName = "@data"; insert.Parameters.Add(insertCover = select.CreateParameter()); insertCover.DbType = DbType.Binary; insertCover.ParameterName = "@cover"; remove = connection.CreateCommand(); remove.CommandText = "DELETE FROM store WHERE key = @key"; remove.Parameters.Add(removeKey = remove.CreateParameter()); removeKey.DbType = DbType.String; removeKey.ParameterName = "@key"; randomSample = connection.CreateCommand(); randomSample.CommandText = "SELECT key FROM store ORDER BY RANDOM() LIMIT 500"; InfoFormat("FileStore at {0} is ready", storeFile.FullName); vacuumer.Add(connection); }
internal FileStore(FileInfo storeFile) { StoreFile = storeFile; connection = Sqlite.GetDatabaseConnection(storeFile); using (var transaction = connection.BeginTransaction()) { using (var pragma = connection.CreateCommand()) { pragma.CommandText = "PRAGMA journal_mode = MEMORY"; pragma.ExecuteNonQuery(); pragma.CommandText = "PRAGMA temp_store = MEMORY"; pragma.ExecuteNonQuery(); pragma.CommandText = "PRAGMA synchonous = OFF"; pragma.ExecuteNonQuery(); } using (var create = connection.CreateCommand()) { create.CommandText = "CREATE TABLE IF NOT EXISTS store (key TEXT PRIMARY KEY ON CONFLICT REPLACE, size INT, time INT, data BINARY, cover BINARY)"; create.ExecuteNonQuery(); } transaction.Commit(); } select = connection.CreateCommand(); select.CommandText = "SELECT data FROM store WHERE key = ? AND size = ? AND time = ?"; select.Parameters.Add(selectKey = select.CreateParameter()); selectKey.DbType = DbType.String; select.Parameters.Add(selectSize = select.CreateParameter()); selectSize.DbType = DbType.Int64; select.Parameters.Add(selectTime = select.CreateParameter()); selectTime.DbType = DbType.Int64; selectCover = connection.CreateCommand(); selectCover.CommandText = "SELECT cover FROM store WHERE key = ? AND size = ? AND time = ?"; selectCover.Parameters.Add(selectCoverKey = select.CreateParameter()); selectCoverKey.DbType = DbType.String; selectCover.Parameters.Add(selectCoverSize = select.CreateParameter()); selectCoverSize.DbType = DbType.Int64; selectCover.Parameters.Add(selectCoverTime = select.CreateParameter()); selectCoverTime.DbType = DbType.Int64; insert = connection.CreateCommand(); insert.CommandText = "INSERT OR REPLACE INTO store VALUES(?,?,?,?,?)"; insert.Parameters.Add(insertKey = select.CreateParameter()); insertKey.DbType = DbType.String; insert.Parameters.Add(insertSize = select.CreateParameter()); insertSize.DbType = DbType.Int64; insert.Parameters.Add(insertTime = select.CreateParameter()); insertTime.DbType = DbType.Int64; insert.Parameters.Add(insertData = select.CreateParameter()); insertData.DbType = DbType.Binary; insert.Parameters.Add(insertCover = select.CreateParameter()); insertCover.DbType = DbType.Binary; InfoFormat("FileStore at {0} is ready", storeFile.FullName); vacuumer.Add(connection); }
internal FileStore(FileInfo storeFile) { StoreFile = storeFile; OpenConnection(storeFile, out connection); SetupDatabase(); select = connection.CreateCommand(); select.CommandText = "SELECT data FROM store WHERE key = ? AND size = ? AND time = ?"; select.Parameters.Add(selectKey = select.CreateParameter()); selectKey.DbType = DbType.String; select.Parameters.Add(selectSize = select.CreateParameter()); selectSize.DbType = DbType.Int64; select.Parameters.Add(selectTime = select.CreateParameter()); selectTime.DbType = DbType.Int64; selectCover = connection.CreateCommand(); selectCover.CommandText = "SELECT cover FROM store WHERE key = ? AND size = ? AND time = ?"; selectCover.Parameters.Add(selectCoverKey = select.CreateParameter()); selectCoverKey.DbType = DbType.String; selectCover.Parameters.Add(selectCoverSize = select.CreateParameter()); selectCoverSize.DbType = DbType.Int64; selectCover.Parameters.Add(selectCoverTime = select.CreateParameter()); selectCoverTime.DbType = DbType.Int64; insert = connection.CreateCommand(); insert.CommandText = "INSERT OR REPLACE INTO store VALUES(?,?,?,?,?)"; insert.Parameters.Add(insertKey = select.CreateParameter()); insertKey.DbType = DbType.String; insert.Parameters.Add(insertSize = select.CreateParameter()); insertSize.DbType = DbType.Int64; insert.Parameters.Add(insertTime = select.CreateParameter()); insertTime.DbType = DbType.Int64; insert.Parameters.Add(insertData = select.CreateParameter()); insertData.DbType = DbType.Binary; insert.Parameters.Add(insertCover = select.CreateParameter()); insertCover.DbType = DbType.Binary; InfoFormat("FileStore at {0} is ready", storeFile.FullName); vacuumer.Add(connection); }