public override bool Delete(SyncJob job) { SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, DATABASE_NAME), false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, DATABASE_NAME))); } SqliteParameterCollection paramList = new SqliteParameterCollection(); string cmdText = "DELETE FROM " + SyncSource.DATASOURCE_INFO_TABLE + " WHERE " + SyncSource.SOURCE_ID + " = @sid;"; paramList.Add(new SqliteParameter("@sid", System.Data.DbType.String) { Value = job.SyncSource.ID }); cmdText += "DELETE FROM " + SYNCJOB_TABLE + " WHERE " + COL_SYNCJOB_ID + " = @pid"; paramList.Add(new SqliteParameter("@pid", System.Data.DbType.String) { Value = job.ID }); db.ExecuteNonQuery(cmdText, paramList); } return(true); }
public override bool Update(SyncJob job) { if (this.SyncJobExists(job.Name, job.ID)) { throw new SyncJobNameExistException(String.Format(m_ResourceManager.GetString("err_syncjobCreated"), job.Name)); } SQLiteSyncSourceProvider provider = (SQLiteSyncSourceProvider)SyncClient.GetSyncSourceProvider(job.IntermediaryStorage.Path); if (provider.GetSyncSourceCount() > 2) { throw new SyncSourcesNumberExceededException(m_ResourceManager.GetString("err_onlyTwoSyncSourceFolders")); } // Update a profile requires update 2 tables at the same time, // If one update on a table fails, the total update action must fail too. string updateProfileText = "UPDATE " + SYNCJOB_TABLE + " SET " + COL_METADATA_SOURCE_LOCATION + " = @mdSource, " + COL_SYNCJOB_NAME + " = @name WHERE " + COL_SYNCJOB_ID + " = @id;"; SqliteParameterCollection paramList = new SqliteParameterCollection(); // Add parameters for 1st Update statement paramList.Add(new SqliteParameter("@mdSource", System.Data.DbType.String) { Value = job.IntermediaryStorage.Path }); paramList.Add(new SqliteParameter("@name", System.Data.DbType.String) { Value = job.Name }); paramList.Add(new SqliteParameter("@id", System.Data.DbType.String) { Value = job.ID }); SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, DATABASE_NAME), false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, DATABASE_NAME))); } SqliteTransaction transaction = (SqliteTransaction)con.BeginTransaction(); try { SQLiteSyncSourceProvider.Update(job.SyncSource, con); db.ExecuteNonQuery(updateProfileText, paramList); transaction.Commit(); return(true); } catch (Exception) { transaction.Rollback(); throw; } } }
/// <summary> /// Create default schema of File metadata table and folder metadata table /// No transactional feature /// </summary> public override void CreateSchema() { SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME), false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); } string cmdText = "CREATE TABLE IF NOT EXISTS " + Configuration.TBL_METADATA + " ( " + Configuration.COL_SOURCE_ID + " TEXT, " + Configuration.COL_RELATIVE_PATH + " TEXT, " + Configuration.COL_HASH_CODE + " TEXT, " + Configuration.COL_LAST_MODIFIED_TIME + " DATETIME, " + Configuration.COL_NTFS_ID1 + " INT, " + Configuration.COL_NTFS_ID2 + " INT," + "FOREIGN KEY (" + Configuration.COL_SOURCE_ID + ") REFERENCES " + Configuration.TBL_DATASOURCE_INFO + "(" + Configuration.COL_SOURCE_ID + ")" + "PRIMARY KEY (" + Configuration.COL_SOURCE_ID + "," + Configuration.COL_RELATIVE_PATH + ")" + ")"; db.ExecuteNonQuery(cmdText, false); } }
public bool SyncJobExists(string jobName, string id) { SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME), false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); } // TODO: Change sql to SELECT COUNT(*)? string cmdText = "SELECT * FROM " + SYNCJOB_TABLE + " WHERE " + COL_SYNCJOB_NAME + " = @profileName AND " + COL_SYNCJOB_ID + " <> @id"; SqliteParameterCollection paramList = new SqliteParameterCollection(); paramList.Add(new SqliteParameter("@profileName", System.Data.DbType.String) { Value = jobName }); paramList.Add(new SqliteParameter("@id", System.Data.DbType.String) { Value = id }); bool found = false; db.ExecuteReader(cmdText, paramList, reader => { found = true; return; } ); return(found); } }
/// <summary> /// Get number of sync sources in intermediate storage /// Maximum numbers of sync sources can be added is 2 /// </summary> /// <returns></returns> public override int GetSyncSourceCount() { try { SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME), false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); } string cmdText = "SELECT COUNT (DISTINCT " + Configuration.COL_SOURCE_ID + ") AS num" + " FROM " + Configuration.TBL_DATASOURCE_INFO; return(Convert.ToInt32(db.ExecuteScalar(cmdText, null))); } } catch (Exception) { // Possible exception during first-run when DataSourceInfo Table not created yet. // Log error? return(-1); } }
public override FileMetaData LoadFileMetadata(string currId, SourceOption option) { string opt = (option == SourceOption.SOURCE_ID_NOT_EQUALS) ? " <> " : " = "; var mData = new FileMetaData(currId, RootPath); var db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME), false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); } string cmdText = string.Format("SELECT * FROM {0} WHERE {1}{2} @sourceId", Configuration.TBL_METADATA, Configuration.COL_SOURCE_ID, opt); var paramList = new SqliteParameterCollection { new SqliteParameter("@sourceId", DbType.String) { Value = currId } }; db.ExecuteReader(cmdText, paramList, reader => mData.MetaDataItems.Add(new FileMetaDataItem( (string)reader[Configuration.COL_SOURCE_ID], (string)reader[Configuration.COL_RELATIVE_PATH], (string)reader[Configuration.COL_HASH_CODE], (DateTime)reader[Configuration.COL_LAST_MODIFIED_TIME], Convert.ToUInt32(reader[Configuration.COL_NTFS_ID1]), Convert.ToUInt32(reader[Configuration.COL_NTFS_ID2])))); } return(mData); }
/// <summary> /// Load all the sync sources objects /// </summary> /// <returns></returns> public override IList <SyncSource> LoadAll() { IList <SyncSource> syncSources = new List <SyncSource>(); SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME), false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); } using (SqliteCommand cmd = con.CreateCommand()) { cmd.CommandText = "SELECT * FROM " + Configuration.TBL_DATASOURCE_INFO; using (SqliteDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { syncSources.Add(new SyncSource((string)reader[Configuration.COL_SOURCE_ID], (string)reader[Configuration.COL_SOURCE_ABSOLUTE_PATH])); } } } } return(syncSources); }
public override bool SourceExist(string sourceId) { try { SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME), false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); } using (SqliteCommand cmd = con.CreateCommand()) { cmd.CommandText = "SELECT * FROM " + Configuration.TBL_DATASOURCE_INFO + " WHERE " + Configuration.COL_SOURCE_ID + " = @sourceId"; cmd.Parameters.Add(new SqliteParameter( "@sourceId", DbType.String) { Value = sourceId }); using (SqliteDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { return(true); } } } } } catch (Exception) { return(false); } return(false); }
public override bool Delete(IList <SyncAction> actions) { SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME), false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); } string cmdText = "DELETE FROM " + Configuration.TBL_ACTION + " WHERE " + Configuration.COL_ACTION_ID + " = @id"; SqliteParameterCollection paramList = new SqliteParameterCollection(); foreach (SyncAction action in actions) { paramList.Clear(); paramList.Add(new SqliteParameter("@id", DbType.Int32) { Value = action.ActionId }); db.ExecuteNonQuery(cmdText, paramList); } } return(true); }
/// <summary> /// Update details of a sync source /// </summary> /// <param name="source"></param> /// <returns></returns> public override bool Update(SyncSource source) { SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME), false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); } string cmdText = "UPDATE " + Configuration.TBL_DATASOURCE_INFO + " SET " + Configuration.COL_SOURCE_ABSOLUTE_PATH + " = @path WHERE " + Configuration.COL_SOURCE_ID + " = @id"; SqliteParameterCollection paramList = new SqliteParameterCollection { new SqliteParameter("@id", DbType.String) { Value = source.ID }, new SqliteParameter("@path", DbType.String) { Value = source.Path } }; db.ExecuteNonQuery(cmdText, false); } return(true); }
/// <summary> /// Add Folder metadata into datatabse /// Add is atomic action /// </summary> /// <param name="folders"></param> /// <returns></returns> public bool Add(IList<FolderMetadataItem> folders) { var db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); var trasaction = (SqliteTransaction)con.BeginTransaction(); try { string cmdText = string.Format("INSERT INTO {0}( {1},{2},{3})VALUES (@source_id , @relative_path, @isEmpty)", Configuration.TLB_FOLDERMETADATA, Configuration.COL_SOURCE_ID, Configuration.COL_FOLDER_RELATIVE_PATH, Configuration.COL_IS_FOLDER_EMPTY); foreach (FolderMetadataItem item in folders) { var paramList = new SqliteParameterCollection { new SqliteParameter("@source_id", DbType.String) {Value = item.SourceId}, new SqliteParameter("@relative_path", DbType.String) {Value = item.RelativePath}, new SqliteParameter("@isEmpty", DbType.Int32){Value = item.IsEmpty} }; db.ExecuteNonQuery(cmdText, paramList); } trasaction.Commit(); return true; } catch (Exception) { trasaction.Rollback(); return false; } } }
public override bool Delete(string sourceID, SourceOption option) { string opt = (option == SourceOption.SOURCE_ID_NOT_EQUALS) ? " <> " : " = "; SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME), false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); } string cmdText = "DELETE FROM " + Configuration.TBL_ACTION + " WHERE " + Configuration.COL_CHANGE_IN + " " + opt + " @id"; SqliteParameterCollection paramList = new SqliteParameterCollection(); paramList.Add(new SqliteParameter("@id", System.Data.DbType.Int32) { Value = sourceID }); db.ExecuteNonQuery(cmdText, paramList); } return(true); }
/// <summary> /// Update meta data including file metadata and folder metadata /// </summary> /// <param name="oldMetadata"></param> /// <param name="newMetada"></param> /// <returns></returns> public override bool Update(Metadata oldMetadata, Metadata newMetada) { var db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME), false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); } var trasaction = (SqliteTransaction)con.BeginTransaction(); // TODO: make 3 actions atomic try { if (!UpdateFileMetadata(oldMetadata.FileMetadata, newMetada.FileMetadata, con)) { throw new Exception(); } if (!UpdateFolderMetadata(oldMetadata.FolderMetadata, newMetada.FolderMetadata, con)) { throw new Exception(); } trasaction.Commit(); } catch (Exception) { trasaction.Rollback(); return(false); } } return(true); }
public override bool UpdateFileMetadata(FileMetaData oldMetadata, FileMetaData newMetadata) { //Sort list of metadata items before search using linq oldMetadata.MetaDataItems.ToList().Sort(new FileMetaDataItemComparer()); newMetadata.MetaDataItems.ToList().Sort(new FileMetaDataItemComparer()); var mdComparer = new FileMetaDataComparer(oldMetadata, newMetadata); var db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME), false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); } var transaction = (SqliteTransaction)con.BeginTransaction(); try { this.Add(mdComparer.RightOnly.ToList(), con); this.Delete(mdComparer.LeftOnly.ToList(), con); this.Update(mdComparer.BothModified.ToList(), con); transaction.Commit(); } catch (Exception) { transaction.Rollback(); return(false); } } return(true); }
public override FolderMetadata LoadFolderMetadata(string currId, SourceOption option) { string opt = (option == SourceOption.SOURCE_ID_NOT_EQUALS) ? " <> " : " = "; var folderMetadata = new FolderMetadata(currId, this.RootPath); var db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME), false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); } string cmdText = "SELECT * FROM " + Configuration.TLB_FOLDERMETADATA + " WHERE " + Configuration.COL_SOURCE_ID + opt + " @sourceId"; var paramList = new SqliteParameterCollection { new SqliteParameter("@sourceId", DbType.String) { Value = currId } }; db.ExecuteReader(cmdText, paramList, reader => folderMetadata.FolderMetadataItems.Add( new FolderMetadataItem( (string)reader[Configuration.COL_SOURCE_ID], (string)reader[Configuration.COL_FOLDER_RELATIVE_PATH], (int)reader[Configuration.COL_IS_FOLDER_EMPTY]))); } return(folderMetadata); }
public static void CreateDataStore(string pathToJobFolder, SyncSource syncSource, IntermediaryStorage metaDataSource) { if (!Directory.Exists(pathToJobFolder)) Directory.CreateDirectory(pathToJobFolder); if (!Directory.Exists(metaDataSource.Path)) Directory.CreateDirectory(metaDataSource.Path); SqliteConnection con1 = null; SqliteConnection con2 = null; SqliteTransaction transaction1 = null; SqliteTransaction transaction2 = null; try { SQLiteAccess dbAccess1 = new SQLiteAccess(Path.Combine(pathToJobFolder, Configuration.DATABASE_NAME),true); SQLiteAccess dbAccess2 = new SQLiteAccess(Path.Combine (metaDataSource.Path, Configuration.DATABASE_NAME),true); con1 = dbAccess1.NewSQLiteConnection(); con2 = dbAccess2.NewSQLiteConnection(); if (con1 == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(pathToJobFolder, Configuration.DATABASE_NAME))); if (con2 == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(metaDataSource.Path, Configuration.DATABASE_NAME))); transaction2 = (SqliteTransaction)con2.BeginTransaction(); transaction1 = (SqliteTransaction)con1.BeginTransaction(); //Create schema for source info table in job folder SQLiteSyncSourceProvider.CreateSchema(con1); //Create schema for profile table in job folder SQLiteSyncJobManager.CreateSchema(con1); //create schema for source info table in intermediate storage folder SQLiteSyncSourceProvider.CreateSchema(con2); //create schema for metadata table in intermediate storage folder SQLiteMetaDataProvider mdProvider = (SQLiteMetaDataProvider)SyncClient.GetMetaDataProvider(metaDataSource.Path, Configuration.DATABASE_NAME); mdProvider.CreateSchema(con2); //create schema for action table in intermediate storage folder SQLiteSyncActionsProvider actionProvider = (SQLiteSyncActionsProvider)SyncClient.GetSyncActionsProvider(metaDataSource.Path); actionProvider.CreateSchema(con2); transaction2.Commit(); transaction1.Commit(); } catch (Exception ex) { Console.WriteLine(ex.Message); if (transaction2 != null) transaction2.Rollback(); if (transaction1 != null) transaction1.Rollback(); throw new DatabaseException(m_ResourceManager.GetString("err_databaseException")); } finally { if (con1 != null) con1.Dispose(); if (con2 != null) con2.Dispose(); } }
public override IList <SyncAction> Load(string sourceID, SourceOption option) { string opt = (option == SourceOption.SOURCE_ID_NOT_EQUALS) ? " <> " : " = "; IList <SyncAction> actions = new List <SyncAction>(); SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME), false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); } string cmdText = "SELECT * FROM " + Configuration.TBL_ACTION + " WHERE " + Configuration.COL_CHANGE_IN + opt + " @sourceId"; SqliteParameterCollection paramList = new SqliteParameterCollection(); paramList.Add(new SqliteParameter("@sourceId", System.Data.DbType.String) { Value = sourceID }); db.ExecuteReader(cmdText, paramList, reader => { ChangeType actionType = (ChangeType)reader[Configuration.COL_ACTION_TYPE]; if (actionType == ChangeType.DELETED) { DeleteAction delAction = new DeleteAction( (int)reader[Configuration.COL_ACTION_ID], (string)reader[Configuration.COL_CHANGE_IN], (string)reader[Configuration.COL_OLD_RELATIVE_PATH], (string)reader[Configuration.COL_OLD_HASH]); actions.Add(delAction); } else if (actionType == ChangeType.NEWLY_CREATED) { CreateAction createAction = new CreateAction( (int)reader[Configuration.COL_ACTION_ID], (string)reader[Configuration.COL_CHANGE_IN], (string)reader[Configuration.COL_NEW_RELATIVE_PATH], (string)reader[Configuration.COL_NEW_HASH]); actions.Add(createAction); } else if (actionType == ChangeType.RENAMED) { RenameAction renameAction = new RenameAction( (int)reader[Configuration.COL_ACTION_ID], (string)reader[Configuration.COL_CHANGE_IN], (string)reader[Configuration.COL_NEW_RELATIVE_PATH], (string)reader[Configuration.COL_OLD_RELATIVE_PATH], (string)reader[Configuration.COL_OLD_HASH]); actions.Add(renameAction); } } ); } return(actions); }
/// <summary> /// Copy a file from sync folder and update action table /// </summary> /// <param name="action"></param> /// <param name="profile"></param> public static void CopyToSyncFolderAndUpdateActionTable(SyncAction action, SyncJob job) { if (!Directory.Exists(job.SyncSource.Path)) { throw new SyncSourceException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), job.SyncSource.Path)); } if (!Directory.Exists(job.IntermediaryStorage.Path)) { throw new SyncSourceException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), job.IntermediaryStorage.Path)); } // TODO: atomic.... string absolutePathInIntermediateStorage = job.IntermediaryStorage.DirtyFolderPath + action.RelativeFilePath; string absolutePathInSyncSource = job.SyncSource.Path + action.RelativeFilePath; SQLiteAccess dbAccess = new SQLiteAccess(Path.Combine(job.IntermediaryStorage.Path, Configuration.DATABASE_NAME), true); SqliteConnection con = dbAccess.NewSQLiteConnection(); if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(job.IntermediaryStorage.Path, Configuration.DATABASE_NAME))); } SqliteTransaction trasaction = (SqliteTransaction)con.BeginTransaction(); try { SQLiteSyncActionsProvider actProvider = (SQLiteSyncActionsProvider)SyncClient.GetSyncActionsProvider(job.IntermediaryStorage.Path); actProvider.Delete(action, con); if (!Files.FileUtils.Copy(absolutePathInIntermediateStorage, absolutePathInSyncSource, true)) { throw new Exception(String.Format(m_ResourceManager.GetString("err_cannotCopyFile"), absolutePathInIntermediateStorage)); } trasaction.Commit(); Files.FileUtils.DeleteFileAndFolderIfEmpty(job.IntermediaryStorage.DirtyFolderPath, absolutePathInIntermediateStorage, true); } catch (OutOfDiskSpaceException) { trasaction.Rollback(); throw; } catch (Exception) { trasaction.Rollback(); throw; } finally { if (con != null) { con.Dispose(); } } }
public override bool Update(IList <FileMetaDataItem> items) { SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME), false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); } SqliteTransaction trasaction = (SqliteTransaction)con.BeginTransaction(); try { foreach (FileMetaDataItem item in items) { string cmdText = "UPDATE " + Configuration.TBL_METADATA + " SET " + Configuration.COL_HASH_CODE + " = @hash, " + Configuration.COL_LAST_MODIFIED_TIME + " = @lmf" + " WHERE " + Configuration.COL_RELATIVE_PATH + " = @rel AND " + Configuration.COL_SOURCE_ID + " = @sourceId"; SqliteParameterCollection paramList = new SqliteParameterCollection(); paramList.Add(new SqliteParameter("@hash", DbType.String) { Value = item.HashCode }); paramList.Add(new SqliteParameter("@lmf", DbType.DateTime) { Value = item.LastModifiedTime }); paramList.Add(new SqliteParameter("@rel", DbType.String) { Value = item.RelativePath }); paramList.Add(new SqliteParameter("@sourceId", DbType.String) { Value = item.SourceId }); db.ExecuteNonQuery(cmdText, false); } trasaction.Commit(); } catch (Exception) { trasaction.Rollback(); return(false); } } return(true); }
/// <summary> /// Rename a file in sync source folder and update action table /// </summary> /// <param name="action"></param> /// <param name="profile"></param> /// <exception cref="System.ComponentModel.Win32Exception"></exception> public static void RenameInSyncFolderAndUpdateActionTable(RenameAction action, SyncJob job) { if (!Directory.Exists(job.SyncSource.Path)) { throw new SyncSourceException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), job.SyncSource.Path)); } if (!Directory.Exists(job.IntermediaryStorage.Path)) { throw new SyncSourceException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), job.IntermediaryStorage.Path)); } string oldAbsolutePathInSyncSource = job.SyncSource.Path + action.PreviousRelativeFilePath; string newAbsolutePathInSyncSource = job.SyncSource.Path + action.RelativeFilePath; SQLiteAccess access = new SQLiteAccess(Path.Combine(job.IntermediaryStorage.Path, Configuration.DATABASE_NAME), true); SqliteConnection con = access.NewSQLiteConnection(); if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(job.IntermediaryStorage.Path, Configuration.DATABASE_NAME))); } SqliteTransaction transaction = (SqliteTransaction)con.BeginTransaction(); try { SyncActionsProvider actProvider = SyncClient.GetSyncActionsProvider(job.IntermediaryStorage.Path); actProvider.Delete(action); if (File.Exists(oldAbsolutePathInSyncSource) && !Files.FileUtils.Move(oldAbsolutePathInSyncSource, newAbsolutePathInSyncSource, true)) { throw new Exception(String.Format(m_ResourceManager.GetString("err_cannotRenameFile"), oldAbsolutePathInSyncSource)); } transaction.Commit(); } catch (Exception) { transaction.Rollback(); throw; } finally { if (con != null) { con.Dispose(); } } }
public override IList <SyncJob> LoadAllJobs() { IList <SyncJob> jobs = new List <SyncJob>(); // Note: The SQL depends on other tables as well which might not be created. // So empty SyncJob list should be returned if there is an exception try { SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME), false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); } using (SqliteCommand cmd = con.CreateCommand()) { cmd.CommandText = "SELECT * FROM " + SYNCJOB_TABLE + " p, " + DATASOURCE_INFO_TABLE + " d WHERE p" + "." + COL_SYNC_SOURCE_ID + " = d" + "." + COL_SOURCE_ID; using (SqliteDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { SyncSource source = new SyncSource((string)reader[COL_SYNC_SOURCE_ID], (string)reader[COL_SOURCE_ABS_PATH]); IntermediaryStorage mdSource = new IntermediaryStorage((string)reader[COL_METADATA_SOURCE_LOCATION]); SyncJob p = new SyncJob((string)reader[COL_SYNCJOB_ID], (string)reader[COL_SYNCJOB_NAME], source, mdSource); jobs.Add(p); } } } } } catch (Exception ex) { Console.WriteLine(ex.Message); // Log error? } return(jobs); }
private bool InsertRenameAction(RenameAction renameAction) { SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME), false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); } string cmdText = "INSERT INTO " + Configuration.TBL_ACTION + " ( " + Configuration.COL_CHANGE_IN + "," + Configuration.COL_ACTION_TYPE + "," + Configuration.COL_OLD_RELATIVE_PATH + "," + Configuration.COL_NEW_RELATIVE_PATH + "," + Configuration.COL_OLD_HASH + ") VALUES (@changeIn, @action, @oldPath, @newPath, @oldHash)"; SqliteParameterCollection paramList = new SqliteParameterCollection(); paramList.Add(new SqliteParameter("@changeIn", DbType.String) { Value = renameAction.SourceID }); paramList.Add(new SqliteParameter("@action", DbType.Int32) { Value = renameAction.ChangeType }); paramList.Add(new SqliteParameter("@oldPath", DbType.String) { Value = renameAction.PreviousRelativeFilePath }); paramList.Add(new SqliteParameter("@newPath", DbType.String) { Value = renameAction.RelativeFilePath }); paramList.Add(new SqliteParameter("@oldHash", DbType.String) { Value = renameAction.FileHash }); db.ExecuteNonQuery(cmdText, paramList); } return(true); }
/// <summary> /// Renames conflicted file so that RenameAction can be executed /// </summary> /// <param name="renameAction">Rename action that cannot be executed due to conflict.</param> public static void ConflictRenameAndUpdateActionTable(RenameAction action, SyncJob job, bool keepConflictedFile) { if (!Directory.Exists(job.SyncSource.Path)) throw new SyncSourceException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), job.SyncSource.Path)); if (!Directory.Exists(job.IntermediaryStorage.Path)) throw new SyncSourceException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), job.IntermediaryStorage.Path)); string absPathInIStorage = job.IntermediaryStorage.DirtyFolderPath + action.RelativeFilePath; string absPathInSyncSource = job.SyncSource.Path + action.RelativeFilePath; string absOldPathInSyncSource = job.SyncSource.Path + action.PreviousRelativeFilePath; SQLiteAccess access = new SQLiteAccess(Path.Combine(job.IntermediaryStorage.Path, Configuration.DATABASE_NAME), true); SqliteConnection con = access.NewSQLiteConnection(); if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(job.IntermediaryStorage.Path, Configuration.DATABASE_NAME))); SqliteTransaction trasaction = (SqliteTransaction)con.BeginTransaction(); try { SQLiteSyncActionsProvider actProvider = (SQLiteSyncActionsProvider)SyncClient.GetSyncActionsProvider(job.IntermediaryStorage.Path); actProvider.Delete(action, con); if (!keepConflictedFile) Files.FileUtils.Delete(absPathInSyncSource, true); else Files.FileUtils.DuplicateRename(absPathInSyncSource, absPathInSyncSource); if (!Files.FileUtils.Move(absOldPathInSyncSource, absPathInSyncSource, true)) throw new Exception(String.Format(m_ResourceManager.GetString("err_cannotRenameFile"), absPathInIStorage)); trasaction.Commit(); Files.FileUtils.DeleteFileAndFolderIfEmpty(job.IntermediaryStorage.DirtyFolderPath, absPathInIStorage, true); } catch (Exception) { trasaction.Rollback(); throw; } finally { if (con != null) con.Dispose(); } }
/// <summary> /// Delete a list of file metadata items /// Actomic /// </summary> /// <param name="items"></param> /// <returns></returns> public override bool Delete(IList <FileMetaDataItem> items) { // All deletions are atomic const string cmdText = "DELETE FROM " + Configuration.TBL_METADATA + " WHERE " + Configuration.COL_SOURCE_ID + " = @sourceId AND " + Configuration.COL_RELATIVE_PATH + " = @path"; var dbAccess = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME), false); using (SqliteConnection con = dbAccess.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); } var transaction = (SqliteTransaction)con.BeginTransaction(); try { foreach (FileMetaDataItem item in items) { var paramList = new SqliteParameterCollection { new SqliteParameter("@sourceId", DbType.String) { Value = item.SourceId }, new SqliteParameter("@path", DbType.String) { Value = item.RelativePath } }; dbAccess.ExecuteNonQuery(cmdText, paramList); } transaction.Commit(); } catch (Exception) { transaction.Rollback(); return(false); } } return(true); }
/// <summary> /// Create schema of sync source table /// No transaction supported /// </summary> public override void CreateSchema() { SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME), true); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); } string cmdText = "CREATE TABLE IF NOT EXISTS " + Configuration.TBL_DATASOURCE_INFO + " ( " + Configuration.COL_SOURCE_ABSOLUTE_PATH + " TEXT, " + Configuration.COL_SOURCE_ID + " TEXT PRIMARY KEY)"; db.ExecuteNonQuery(cmdText, false); } }
/// <summary> /// Add Folder metadata into datatabse /// Add is atomic action /// </summary> /// <param name="folders"></param> /// <returns></returns> public bool Add(IList <FolderMetadataItem> folders) { var db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME), false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); } var trasaction = (SqliteTransaction)con.BeginTransaction(); try { string cmdText = string.Format("INSERT INTO {0}( {1},{2},{3})VALUES (@source_id , @relative_path, @isEmpty)", Configuration.TLB_FOLDERMETADATA, Configuration.COL_SOURCE_ID, Configuration.COL_FOLDER_RELATIVE_PATH, Configuration.COL_IS_FOLDER_EMPTY); foreach (FolderMetadataItem item in folders) { var paramList = new SqliteParameterCollection { new SqliteParameter("@source_id", DbType.String) { Value = item.SourceId }, new SqliteParameter("@relative_path", DbType.String) { Value = item.RelativePath }, new SqliteParameter("@isEmpty", DbType.Int32) { Value = item.IsEmpty } }; db.ExecuteNonQuery(cmdText, paramList); } trasaction.Commit(); return(true); } catch (Exception) { trasaction.Rollback(); return(false); } } }
public override bool UpdateFolderMetadata(FolderMetadata oldMetadata, FolderMetadata newMetadata, bool storeOnlyEmptyFolder) { oldMetadata.FolderMetadataItems.ToList().Sort(new FolderMetadataItemComparer()); newMetadata.FolderMetadataItems.ToList().Sort(new FolderMetadataItemComparer()); //Find the metadata items that in the current folder only IEnumerable <FolderMetadataItem> newOnly = newMetadata.FolderMetadataItems.Where( @new => !oldMetadata.FolderMetadataItems.Contains(@new, new FolderMetadataItemComparer()) && (storeOnlyEmptyFolder?Files.FileUtils.IsDirectoryEmpty(@new.AbsolutePath):true) ); //find metadata items that not in the current folder but metadata store IEnumerable <FolderMetadataItem> oldOnly = oldMetadata.FolderMetadataItems.Where( old => !newMetadata.FolderMetadataItems.Contains(old, new FolderMetadataItemComparer())); var db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME), false); using (var con = db.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); } var transaction = (SqliteTransaction)con.BeginTransaction(); try { this.Add(newOnly.ToList(), con); this.Delete(oldOnly.ToList(), con); transaction.Commit(); } catch (Exception) { transaction.Rollback(); return(false); } } return(true); }
/// <summary> /// Create schema for SyncJob table /// </summary> public override void CreateSchema() { SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, DATABASE_NAME), true); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); } string cmdText = "CREATE TABLE IF NOT EXISTS " + SYNCJOB_TABLE + "(" + COL_SYNCJOB_ID + " VARCHAR(50) PRIMARY KEY, " + COL_SYNCJOB_NAME + " VARCHAR(50) UNIQUE NOT NULL, " + COL_METADATA_SOURCE_LOCATION + " TEXT, " + COL_SYNC_SOURCE_ID + " VARCHAR (50), " + "FOREIGN KEY (" + COL_SYNC_SOURCE_ID + ") REFERENCES " + DATASOURCE_INFO_TABLE + "(" + COL_SOURCE_ID + "))"; db.ExecuteNonQuery(cmdText, false); } }
public override SyncJob Load(string jobName) { SyncJob p = null; SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, DATABASE_NAME), false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); } string cmdText = "SELECT * FROM " + SYNCJOB_TABLE + " p, " + DATASOURCE_INFO_TABLE + " d WHERE p" + "." + COL_SYNC_SOURCE_ID + " = d" + "." + COL_SOURCE_ID + " AND " + COL_SYNCJOB_NAME + " = @pname"; SqliteParameterCollection paramList = new SqliteParameterCollection(); paramList.Add(new SqliteParameter("@pname", System.Data.DbType.String) { Value = jobName }); db.ExecuteReader(cmdText, paramList, reader => { // TODO: constructor of Profile takes in more arguments to remove dependency on IntermediaryStorage and SyncSource class. SyncSource source = new SyncSource((string)reader[COL_SYNC_SOURCE_ID], (string)reader[COL_SOURCE_ABS_PATH]); IntermediaryStorage mdSource = new IntermediaryStorage((string)reader[COL_METADATA_SOURCE_LOCATION]); p = new SyncJob((string)reader[COL_SYNCJOB_ID], (string)reader[COL_SYNCJOB_NAME], source, mdSource); return; } ); } return(p); }
public override void CreateSchema() { SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME), true); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); } string cmdText = "CREATE TABLE IF NOT EXISTS " + Configuration.TBL_ACTION + " ( " + Configuration.COL_ACTION_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + Configuration.COL_CHANGE_IN + " TEXT, " + Configuration.COL_ACTION_TYPE + " INT, " + Configuration.COL_OLD_RELATIVE_PATH + " TEXT, " + Configuration.COL_NEW_RELATIVE_PATH + " TEXT, " + Configuration.COL_NEW_HASH + " TEXT, " + Configuration.COL_OLD_HASH + " TEXT)"; db.ExecuteNonQuery(cmdText, null); } }
/// <summary> /// Add sync source to database /// no transaction supports /// </summary> /// <param name="s"></param> /// <returns></returns> public override bool Add(SyncSource s) { if (GetSyncSourceCount() == 2) { throw new SyncSourcesNumberExceededException(m_ResourceManager.GetString("err_onlyTwoSyncSourceFolders")); } string insertText = "INSERT INTO " + Configuration.TBL_DATASOURCE_INFO + "(" + Configuration.COL_SOURCE_ID + "," + Configuration.COL_SOURCE_ABSOLUTE_PATH + ") VALUES (@id, @path)"; SqliteParameterCollection paramList = new SqliteParameterCollection { new SqliteParameter("@id", DbType.String) { Value = s.ID }, new SqliteParameter("@path", DbType.String) { Value = s.Path } }; SQLiteAccess dbAccess = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME), false); using (SqliteConnection con = dbAccess.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); } dbAccess.ExecuteNonQuery(insertText, paramList); } return(true); }
/// <summary> /// Add sync source to database /// no transaction supports /// </summary> /// <param name="s"></param> /// <returns></returns> public override bool Add(SyncSource s) { if (GetSyncSourceCount() == 2) throw new SyncSourcesNumberExceededException(m_ResourceManager.GetString("err_onlyTwoSyncSourceFolders")); string insertText = "INSERT INTO " + Configuration.TBL_DATASOURCE_INFO + "(" + Configuration.COL_SOURCE_ID + "," + Configuration.COL_SOURCE_ABSOLUTE_PATH + ") VALUES (@id, @path)"; SqliteParameterCollection paramList = new SqliteParameterCollection { new SqliteParameter("@id", DbType.String) {Value = s.ID}, new SqliteParameter("@path", DbType.String) {Value = s.Path} }; SQLiteAccess dbAccess = new SQLiteAccess(Path.Combine (this.StoragePath, Configuration.DATABASE_NAME),false); using (SqliteConnection con = dbAccess.NewSQLiteConnection()) { if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); dbAccess.ExecuteNonQuery(insertText, paramList); } return true; }
public override bool Delete(string sourceID, SourceOption option) { string opt = (option == SourceOption.SOURCE_ID_NOT_EQUALS ) ? " <> " : " = "; SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),false); using (SqliteConnection con = db.NewSQLiteConnection ()) { if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); string cmdText = "DELETE FROM " + Configuration.TBL_ACTION + " WHERE " + Configuration.COL_CHANGE_IN + " " + opt + " @id"; SqliteParameterCollection paramList = new SqliteParameterCollection(); paramList.Add(new SqliteParameter("@id", System.Data.DbType.Int32) { Value = sourceID }); db.ExecuteNonQuery(cmdText, paramList); } return true; }
/// <summary> /// Create schema of sync source table /// No transaction supported /// </summary> public override void CreateSchema() { SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),true); using (SqliteConnection con = db.NewSQLiteConnection ()) { if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); string cmdText = "CREATE TABLE IF NOT EXISTS " + Configuration.TBL_DATASOURCE_INFO + " ( " + Configuration.COL_SOURCE_ABSOLUTE_PATH + " TEXT, " + Configuration.COL_SOURCE_ID + " TEXT PRIMARY KEY)"; db.ExecuteNonQuery(cmdText, false); } }
public override bool UpdateFileMetadata(FileMetaData oldMetadata, FileMetaData newMetadata) { //Sort list of metadata items before search using linq oldMetadata.MetaDataItems.ToList().Sort(new FileMetaDataItemComparer()); newMetadata.MetaDataItems.ToList().Sort(new FileMetaDataItemComparer()); var mdComparer = new FileMetaDataComparer(oldMetadata, newMetadata); var db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); var transaction = (SqliteTransaction)con.BeginTransaction(); try { this.Add(mdComparer.RightOnly.ToList(), con); this.Delete(mdComparer.LeftOnly.ToList(), con); this.Update(mdComparer.BothModified.ToList(), con); transaction.Commit(); } catch (Exception) { transaction.Rollback(); return false; } } return true; }
/// <summary> /// Update details of a sync source /// </summary> /// <param name="source"></param> /// <returns></returns> public override bool Update(SyncSource source) { SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),false); using (SqliteConnection con = db.NewSQLiteConnection ()) { if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); string cmdText = "UPDATE " + Configuration.TBL_DATASOURCE_INFO + " SET " + Configuration.COL_SOURCE_ABSOLUTE_PATH + " = @path WHERE " + Configuration.COL_SOURCE_ID + " = @id"; SqliteParameterCollection paramList = new SqliteParameterCollection { new SqliteParameter("@id", DbType.String) {Value = source.ID}, new SqliteParameter("@path", DbType.String) {Value = source.Path} }; db.ExecuteNonQuery(cmdText, false); } return true; }
/// <summary> /// Get number of sync sources in intermediate storage /// Maximum numbers of sync sources can be added is 2 /// </summary> /// <returns></returns> public override int GetSyncSourceCount() { try { SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),false); using (SqliteConnection con = db.NewSQLiteConnection ()) { if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); string cmdText = "SELECT COUNT (DISTINCT " + Configuration.COL_SOURCE_ID + ") AS num" + " FROM " + Configuration.TBL_DATASOURCE_INFO; return Convert.ToInt32(db.ExecuteScalar(cmdText, null)); } } catch (Exception) { // Possible exception during first-run when DataSourceInfo Table not created yet. // Log error? return -1; } }
public override bool DeleteSyncSourceInIntermediateStorage(SyncSource source) { SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME), false); SqliteTransaction transaction = null; try { using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); } transaction = (SqliteTransaction)con.BeginTransaction(); using (SqliteCommand cmd = con.CreateCommand()) { cmd.CommandText = "DELETE FROM " + Configuration.TBL_METADATA + " WHERE " + Configuration.COL_SOURCE_ID + " = @id"; cmd.Parameters.Add(new SqliteParameter("@id", DbType.String) { Value = source.ID }); cmd.ExecuteNonQuery(); cmd.CommandText = "DELETE FROM " + Configuration.TLB_FOLDERMETADATA + " WHERE " + Configuration.COL_SOURCE_ID + " = @id"; cmd.Parameters.Clear(); cmd.Parameters.Add(new SqliteParameter("@id", DbType.String) { Value = source.ID }); cmd.ExecuteNonQuery(); cmd.CommandText = "DELETE FROM " + Configuration.TBL_DATASOURCE_INFO + " WHERE " + Configuration.COL_SOURCE_ID + " = @id"; cmd.Parameters.Clear(); cmd.Parameters.Add(new SqliteParameter("@id", DbType.String) { Value = source.ID }); cmd.ExecuteNonQuery(); cmd.CommandText = "DELETE FROM " + Configuration.TBL_ACTION + " WHERE " + Configuration.COL_CHANGE_IN + " = @id"; cmd.Parameters.Clear(); cmd.Parameters.Add(new SqliteParameter("@id", DbType.String) { Value = source.ID }); cmd.ExecuteNonQuery(); transaction.Commit(); } } } catch (Exception) { if (transaction != null && transaction.Connection.State == ConnectionState.Open) { transaction.Rollback(); } throw; } return(true); }
/// <summary> /// Update meta data including file metadata and folder metadata /// </summary> /// <param name="oldMetadata"></param> /// <param name="newMetada"></param> /// <returns></returns> public override bool Update(Metadata oldMetadata, Metadata newMetada) { var db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); var trasaction = (SqliteTransaction)con.BeginTransaction(); // TODO: make 3 actions atomic try { if (!UpdateFileMetadata(oldMetadata.FileMetadata, newMetada.FileMetadata, con)) throw new Exception(); if (!UpdateFolderMetadata(oldMetadata.FolderMetadata, newMetada.FolderMetadata, con)) throw new Exception(); trasaction.Commit(); } catch (Exception) { trasaction.Rollback(); return false; } } return true; }
/// <summary> /// Delete a file in sync source folder and update action table /// </summary> /// <param name="action"></param> /// <param name="profile"></param> public static void DeleteInSyncFolderAndUpdateActionTable(SyncAction action, SyncJob job) { string absolutePathInSyncSource = job.SyncSource.Path + action.RelativeFilePath; SQLiteAccess access = new SQLiteAccess(Path.Combine(job.IntermediaryStorage.Path, Configuration.DATABASE_NAME),true); SqliteConnection con = access.NewSQLiteConnection(); if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(job.IntermediaryStorage.Path, Configuration.DATABASE_NAME))); SqliteTransaction transaction = (SqliteTransaction)con.BeginTransaction(); try { SQLiteSyncActionsProvider actProvider = (SQLiteSyncActionsProvider)SyncClient.GetSyncActionsProvider(job.IntermediaryStorage.Path); actProvider.Delete(action); if (!Files.FileUtils.Delete(absolutePathInSyncSource, true)) throw new Exception(String.Format(m_ResourceManager.GetString("err_cannotDeleteFile"), absolutePathInSyncSource)); transaction.Commit(); /* Files.FileUtils.DeleteFileAndFolderIfEmpty(job.IntermediaryStorage.DirtyFolderPath, job.IntermediaryStorage.DirtyFolderPath + action.RelativeFilePath , true); */ } catch (Exception) { transaction.Rollback(); throw; } finally { if (con != null) con.Dispose(); } }
public override bool Update(SyncJob job) { if (this.SyncJobExists(job.Name, job.ID)) throw new SyncJobNameExistException(String.Format(m_ResourceManager.GetString("err_syncjobCreated"), job.Name)); SQLiteSyncSourceProvider provider = (SQLiteSyncSourceProvider)SyncClient.GetSyncSourceProvider(job.IntermediaryStorage.Path); if (provider.GetSyncSourceCount() > 2) throw new SyncSourcesNumberExceededException(m_ResourceManager.GetString("err_onlyTwoSyncSourceFolders")); // Update a profile requires update 2 tables at the same time, // If one update on a table fails, the total update action must fail too. string updateProfileText = "UPDATE " + SYNCJOB_TABLE + " SET " + COL_METADATA_SOURCE_LOCATION + " = @mdSource, " + COL_SYNCJOB_NAME + " = @name WHERE " + COL_SYNCJOB_ID + " = @id;"; SqliteParameterCollection paramList = new SqliteParameterCollection(); // Add parameters for 1st Update statement paramList.Add(new SqliteParameter("@mdSource", System.Data.DbType.String) { Value = job.IntermediaryStorage.Path }); paramList.Add(new SqliteParameter("@name", System.Data.DbType.String) { Value = job.Name }); paramList.Add(new SqliteParameter("@id", System.Data.DbType.String) { Value = job.ID }); SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, DATABASE_NAME),false); using (SqliteConnection con = db.NewSQLiteConnection ()) { if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, DATABASE_NAME))); SqliteTransaction transaction = (SqliteTransaction)con.BeginTransaction(); try { SQLiteSyncSourceProvider.Update(job.SyncSource,con ); db.ExecuteNonQuery(updateProfileText, paramList); transaction.Commit(); return true; } catch (Exception) { transaction.Rollback(); throw; } } }
public override bool Delete(IList<SyncAction> actions) { SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),false); using (SqliteConnection con = db.NewSQLiteConnection ()) { if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); string cmdText = "DELETE FROM " + Configuration.TBL_ACTION + " WHERE " + Configuration.COL_ACTION_ID + " = @id"; SqliteParameterCollection paramList = new SqliteParameterCollection(); foreach (SyncAction action in actions) { paramList.Clear(); paramList.Add(new SqliteParameter("@id", DbType.Int32) { Value = action.ActionId }); db.ExecuteNonQuery(cmdText, paramList); } } return true; }
public override bool SourceExist(string sourceId) { try { SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME), false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); using (SqliteCommand cmd = con.CreateCommand()) { cmd.CommandText = "SELECT * FROM " + Configuration.TBL_DATASOURCE_INFO + " WHERE " + Configuration.COL_SOURCE_ID + " = @sourceId"; cmd.Parameters.Add(new SqliteParameter( "@sourceId", DbType.String) { Value = sourceId }); using (SqliteDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { return true; } } } } } catch (Exception) {return false;} return false; }
public override IList<SyncAction> Load(string sourceID, SourceOption option) { string opt = (option == SourceOption.SOURCE_ID_NOT_EQUALS) ? " <> " : " = "; IList<SyncAction> actions = new List<SyncAction>(); SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),false); using (SqliteConnection con = db.NewSQLiteConnection ()) { if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); string cmdText = "SELECT * FROM " + Configuration.TBL_ACTION + " WHERE " + Configuration.COL_CHANGE_IN + opt + " @sourceId"; SqliteParameterCollection paramList = new SqliteParameterCollection(); paramList.Add(new SqliteParameter("@sourceId", System.Data.DbType.String) { Value = sourceID }); db.ExecuteReader(cmdText, paramList, reader => { ChangeType actionType = (ChangeType)reader[Configuration.COL_ACTION_TYPE]; if (actionType == ChangeType.DELETED) { DeleteAction delAction = new DeleteAction( (int)reader[Configuration.COL_ACTION_ID], (string)reader[Configuration.COL_CHANGE_IN], (string)reader[Configuration.COL_OLD_RELATIVE_PATH], (string)reader[Configuration.COL_OLD_HASH]); actions.Add(delAction); } else if (actionType == ChangeType.NEWLY_CREATED) { CreateAction createAction = new CreateAction( (int)reader[Configuration.COL_ACTION_ID], (string)reader[Configuration.COL_CHANGE_IN], (string)reader[Configuration.COL_NEW_RELATIVE_PATH], (string)reader[Configuration.COL_NEW_HASH]); actions.Add(createAction); } else if (actionType == ChangeType.RENAMED) { RenameAction renameAction = new RenameAction( (int)reader[Configuration.COL_ACTION_ID], (string)reader[Configuration.COL_CHANGE_IN], (string)reader[Configuration.COL_NEW_RELATIVE_PATH], (string)reader[Configuration.COL_OLD_RELATIVE_PATH], (string)reader[Configuration.COL_OLD_HASH]); actions.Add(renameAction); } } ); } return actions; }
/// <summary> /// Rename a file in sync source folder and update action table /// </summary> /// <param name="action"></param> /// <param name="profile"></param> /// <exception cref="System.ComponentModel.Win32Exception"></exception> public static void RenameInSyncFolderAndUpdateActionTable(RenameAction action, SyncJob job) { if (!Directory.Exists(job.SyncSource.Path)) throw new SyncSourceException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), job.SyncSource.Path)); if (!Directory.Exists(job.IntermediaryStorage.Path)) throw new SyncSourceException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), job.IntermediaryStorage.Path)); string oldAbsolutePathInSyncSource = job.SyncSource.Path + action.PreviousRelativeFilePath; string newAbsolutePathInSyncSource = job.SyncSource.Path + action.RelativeFilePath; SQLiteAccess access = new SQLiteAccess(Path.Combine (job.IntermediaryStorage.Path, Configuration.DATABASE_NAME),true); SqliteConnection con = access.NewSQLiteConnection(); if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(job.IntermediaryStorage.Path, Configuration.DATABASE_NAME))); SqliteTransaction transaction = (SqliteTransaction)con.BeginTransaction(); try { SyncActionsProvider actProvider = SyncClient.GetSyncActionsProvider(job.IntermediaryStorage.Path); actProvider.Delete(action); if (File.Exists(oldAbsolutePathInSyncSource) && !Files.FileUtils.Move(oldAbsolutePathInSyncSource, newAbsolutePathInSyncSource, true)) throw new Exception(String.Format(m_ResourceManager.GetString("err_cannotRenameFile"), oldAbsolutePathInSyncSource)); transaction.Commit(); } catch (Exception) { transaction.Rollback(); throw; } finally { if (con != null) con.Dispose(); } }
private bool InsertRenameAction(RenameAction renameAction) { SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),false); using (SqliteConnection con = db.NewSQLiteConnection ()) { if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); string cmdText = "INSERT INTO " + Configuration.TBL_ACTION + " ( " + Configuration.COL_CHANGE_IN + "," + Configuration.COL_ACTION_TYPE + "," + Configuration.COL_OLD_RELATIVE_PATH + "," + Configuration.COL_NEW_RELATIVE_PATH + "," + Configuration.COL_OLD_HASH + ") VALUES (@changeIn, @action, @oldPath, @newPath, @oldHash)"; SqliteParameterCollection paramList = new SqliteParameterCollection(); paramList.Add(new SqliteParameter("@changeIn", DbType.String) { Value = renameAction.SourceID }); paramList.Add(new SqliteParameter("@action", DbType.Int32) { Value = renameAction.ChangeType }); paramList.Add(new SqliteParameter("@oldPath", DbType.String) { Value = renameAction.PreviousRelativeFilePath }); paramList.Add(new SqliteParameter("@newPath", DbType.String) { Value = renameAction.RelativeFilePath }); paramList.Add(new SqliteParameter("@oldHash", DbType.String) { Value = renameAction.FileHash }); db.ExecuteNonQuery(cmdText, paramList); } return true; }
public static void CopyToDirtyFolderAndUpdateActionTable(SyncAction action, SyncJob job) { if (!Directory.Exists(job.SyncSource.Path)) throw new SyncSourceException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), job.SyncSource.Path)); if ( !Directory.Exists(job.IntermediaryStorage.Path)) throw new SyncSourceException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), job.IntermediaryStorage.Path)); if (!Directory.Exists(job.IntermediaryStorage.DirtyFolderPath)) Directory.CreateDirectory(job.IntermediaryStorage.DirtyFolderPath); // TODO: make it atomic?? string absolutePathInSyncSource = job.SyncSource.Path + action.RelativeFilePath; string absolutePathInImediateStorage = job.IntermediaryStorage.DirtyFolderPath + action.RelativeFilePath; var db = new SQLiteAccess(Path.Combine(job.IntermediaryStorage.Path, Configuration.DATABASE_NAME),true); var con = db.NewSQLiteConnection(); if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(job.IntermediaryStorage.Path, Configuration.DATABASE_NAME))); var transaction = (SqliteTransaction)con.BeginTransaction(); try { var actProvider = (SQLiteSyncActionsProvider)SyncClient.GetSyncActionsProvider(job.IntermediaryStorage.Path); actProvider.Add(action, con); if (!FileUtils.Copy(absolutePathInSyncSource, absolutePathInImediateStorage, true)) throw new Exception(String.Format(m_ResourceManager.GetString("err_cannotCopyFile"), absolutePathInSyncSource)); transaction.Commit(); } catch (OutOfDiskSpaceException) { transaction.Rollback(); throw; } catch (Exception) { transaction.Rollback(); throw; } finally { if (con != null) con.Dispose(); } }
public override void CreateSchema() { SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),true); using (SqliteConnection con = db.NewSQLiteConnection ()) { if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); string cmdText = "CREATE TABLE IF NOT EXISTS " + Configuration.TBL_ACTION + " ( " + Configuration.COL_ACTION_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + Configuration.COL_CHANGE_IN + " TEXT, " + Configuration.COL_ACTION_TYPE + " INT, " + Configuration.COL_OLD_RELATIVE_PATH + " TEXT, " + Configuration.COL_NEW_RELATIVE_PATH + " TEXT, " + Configuration.COL_NEW_HASH + " TEXT, " + Configuration.COL_OLD_HASH + " TEXT)"; db.ExecuteNonQuery(cmdText, null); } }
/// <summary> /// Load all the sync sources objects /// </summary> /// <returns></returns> public override IList<SyncSource> LoadAll() { IList<SyncSource> syncSources = new List<SyncSource>(); SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),false); using (SqliteConnection con = db.NewSQLiteConnection ()) { if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); using (SqliteCommand cmd = con.CreateCommand ()) { cmd.CommandText = "SELECT * FROM " + Configuration.TBL_DATASOURCE_INFO; using (SqliteDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { syncSources.Add(new SyncSource((string)reader[Configuration.COL_SOURCE_ID], (string)reader[Configuration.COL_SOURCE_ABSOLUTE_PATH])); } } } } return syncSources; }
public override bool Add(SyncJob job) { if (this.SyncJobExists(job.Name, job.ID)) throw new SyncJobNameExistException(String.Format(m_ResourceManager.GetString("err_syncjobCreated"), job.Name)); SQLiteAccess dbAccess1 = new SQLiteAccess(Path.Combine (this.StoragePath, Configuration.DATABASE_NAME),false); SQLiteAccess dbAccess2 = new SQLiteAccess(Path.Combine(job.IntermediaryStorage.Path, Configuration.DATABASE_NAME),false); SqliteConnection con1 = dbAccess1.NewSQLiteConnection(); SqliteConnection con2 = dbAccess2.NewSQLiteConnection(); if (con1 == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, DATABASE_NAME))); if (con2 == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(job.IntermediaryStorage.Path, Configuration.DATABASE_NAME))); SqliteTransaction transaction1 = (SqliteTransaction)con1.BeginTransaction(); SqliteTransaction transaction2 = (SqliteTransaction)con2.BeginTransaction(); try { string insertJobText = "INSERT INTO " + SYNCJOB_TABLE + " (" + COL_SYNCJOB_ID + ", " + COL_SYNCJOB_NAME + " ," + COL_METADATA_SOURCE_LOCATION + ", " + COL_SYNC_SOURCE_ID + ") VALUES (@id, @name, @meta, @source)"; SqliteParameterCollection paramList = new SqliteParameterCollection(); paramList.Add(new SqliteParameter("@id", System.Data.DbType.String) { Value = job.ID }); paramList.Add(new SqliteParameter("@name", System.Data.DbType.String) { Value = job.Name }); paramList.Add(new SqliteParameter("@meta", System.Data.DbType.String) { Value = job.IntermediaryStorage.Path }); paramList.Add(new SqliteParameter("@source", System.Data.DbType.String) { Value = job.SyncSource.ID }); dbAccess1.ExecuteNonQuery(insertJobText, paramList); SQLiteSyncSourceProvider.Add(job.SyncSource, con1); SQLiteSyncSourceProvider provider = (SQLiteSyncSourceProvider)SyncClient.GetSyncSourceProvider(job.IntermediaryStorage.Path); if (provider.GetSyncSourceCount() == 2) throw new SyncSourcesNumberExceededException(m_ResourceManager.GetString("err_onlyTwoSyncSourceFolders")); SQLiteSyncSourceProvider.Add(job.SyncSource, con2); transaction1.Commit(); transaction2.Commit(); return true; } catch (Exception) { transaction1.Rollback(); transaction2.Rollback(); throw; } finally { if (con1 != null) con1.Dispose(); if (con2 != null) con2.Dispose(); } }
/// <summary> /// Create schema for SyncJob table /// </summary> public override void CreateSchema() { SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, DATABASE_NAME),true); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); string cmdText = "CREATE TABLE IF NOT EXISTS " + SYNCJOB_TABLE + "(" + COL_SYNCJOB_ID + " VARCHAR(50) PRIMARY KEY, " + COL_SYNCJOB_NAME + " VARCHAR(50) UNIQUE NOT NULL, " + COL_METADATA_SOURCE_LOCATION + " TEXT, " + COL_SYNC_SOURCE_ID + " VARCHAR (50), " + "FOREIGN KEY (" + COL_SYNC_SOURCE_ID + ") REFERENCES " + DATASOURCE_INFO_TABLE + "(" + COL_SOURCE_ID + "))"; db.ExecuteNonQuery(cmdText, false); } }
public override bool Delete(SyncJob job) { SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, DATABASE_NAME),false); using (SqliteConnection con = db.NewSQLiteConnection ()) { if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, DATABASE_NAME))); SqliteParameterCollection paramList = new SqliteParameterCollection(); string cmdText = "DELETE FROM " + SyncSource.DATASOURCE_INFO_TABLE + " WHERE " + SyncSource.SOURCE_ID + " = @sid;"; paramList.Add(new SqliteParameter("@sid", System.Data.DbType.String) { Value = job.SyncSource.ID }); cmdText += "DELETE FROM " + SYNCJOB_TABLE + " WHERE " + COL_SYNCJOB_ID + " = @pid"; paramList.Add(new SqliteParameter("@pid", System.Data.DbType.String) { Value = job.ID }); db.ExecuteNonQuery(cmdText, paramList); } return true; }
public override bool DeleteSyncSourceInIntermediateStorage(SyncSource source) { SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),false); SqliteTransaction transaction = null ; try { using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); transaction = (SqliteTransaction)con.BeginTransaction(); using (SqliteCommand cmd = con.CreateCommand()) { cmd.CommandText = "DELETE FROM " + Configuration.TBL_METADATA + " WHERE " + Configuration.COL_SOURCE_ID + " = @id"; cmd.Parameters.Add(new SqliteParameter("@id", DbType.String) { Value = source.ID }); cmd.ExecuteNonQuery(); cmd.CommandText = "DELETE FROM " + Configuration.TLB_FOLDERMETADATA + " WHERE " + Configuration.COL_SOURCE_ID + " = @id"; cmd.Parameters.Clear(); cmd.Parameters.Add(new SqliteParameter("@id", DbType.String) { Value = source.ID }); cmd.ExecuteNonQuery(); cmd.CommandText = "DELETE FROM " + Configuration.TBL_DATASOURCE_INFO + " WHERE " + Configuration.COL_SOURCE_ID + " = @id"; cmd.Parameters.Clear(); cmd.Parameters.Add(new SqliteParameter("@id", DbType.String) { Value = source.ID }); cmd.ExecuteNonQuery(); cmd.CommandText = "DELETE FROM " + Configuration.TBL_ACTION + " WHERE " + Configuration.COL_CHANGE_IN + " = @id"; cmd.Parameters.Clear(); cmd.Parameters.Add(new SqliteParameter("@id", DbType.String) { Value = source.ID }); cmd.ExecuteNonQuery(); transaction.Commit(); } } } catch (Exception) { if ( transaction != null && transaction.Connection.State == ConnectionState.Open) transaction.Rollback(); throw; } return true; }
public override SyncJob Load(string jobName) { SyncJob p = null; SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, DATABASE_NAME),false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); string cmdText = "SELECT * FROM " + SYNCJOB_TABLE + " p, " + DATASOURCE_INFO_TABLE + " d WHERE p" + "." + COL_SYNC_SOURCE_ID + " = d" + "." + COL_SOURCE_ID + " AND " + COL_SYNCJOB_NAME + " = @pname"; SqliteParameterCollection paramList = new SqliteParameterCollection(); paramList.Add(new SqliteParameter("@pname", System.Data.DbType.String) { Value = jobName }); db.ExecuteReader(cmdText, paramList, reader => { // TODO: constructor of Profile takes in more arguments to remove dependency on IntermediaryStorage and SyncSource class. SyncSource source = new SyncSource((string)reader[COL_SYNC_SOURCE_ID], (string)reader[COL_SOURCE_ABS_PATH]); IntermediaryStorage mdSource = new IntermediaryStorage((string)reader[COL_METADATA_SOURCE_LOCATION]); p = new SyncJob((string)reader[COL_SYNCJOB_ID], (string)reader[COL_SYNCJOB_NAME], source, mdSource); return; } ); } return p; }
public override bool UpdateFolderMetadata(FolderMetadata oldMetadata, FolderMetadata newMetadata, bool storeOnlyEmptyFolder) { oldMetadata.FolderMetadataItems.ToList().Sort(new FolderMetadataItemComparer()); newMetadata.FolderMetadataItems.ToList().Sort(new FolderMetadataItemComparer()); //Find the metadata items that in the current folder only IEnumerable<FolderMetadataItem> newOnly = newMetadata.FolderMetadataItems.Where( @new => !oldMetadata.FolderMetadataItems.Contains(@new, new FolderMetadataItemComparer()) && (storeOnlyEmptyFolder?Files.FileUtils.IsDirectoryEmpty(@new.AbsolutePath):true) ); //find metadata items that not in the current folder but metadata store IEnumerable<FolderMetadataItem> oldOnly = oldMetadata.FolderMetadataItems.Where( old => !newMetadata.FolderMetadataItems.Contains(old, new FolderMetadataItemComparer())); var db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),false); using (var con = db.NewSQLiteConnection()) { if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); var transaction = (SqliteTransaction)con.BeginTransaction(); try { this.Add(newOnly.ToList(), con); this.Delete(oldOnly.ToList(), con); transaction.Commit(); } catch (Exception) { transaction.Rollback(); return false; } } return true; }
public override IList<SyncJob> LoadAllJobs() { IList<SyncJob> jobs = new List<SyncJob>(); // Note: The SQL depends on other tables as well which might not be created. // So empty SyncJob list should be returned if there is an exception try { SQLiteAccess db = new SQLiteAccess(Path.Combine (this.StoragePath, Configuration.DATABASE_NAME ),false); using (SqliteConnection con = db.NewSQLiteConnection ()) { if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); using (SqliteCommand cmd = con.CreateCommand()) { cmd.CommandText = "SELECT * FROM " + SYNCJOB_TABLE + " p, " + DATASOURCE_INFO_TABLE + " d WHERE p" + "." + COL_SYNC_SOURCE_ID + " = d" + "." + COL_SOURCE_ID; using (SqliteDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { SyncSource source = new SyncSource((string)reader[COL_SYNC_SOURCE_ID], (string)reader[COL_SOURCE_ABS_PATH]); IntermediaryStorage mdSource = new IntermediaryStorage((string)reader[COL_METADATA_SOURCE_LOCATION]); SyncJob p = new SyncJob((string)reader[COL_SYNCJOB_ID], (string)reader[COL_SYNCJOB_NAME], source, mdSource); jobs.Add(p); } } } } } catch (Exception ex) { Console.WriteLine(ex.Message); // Log error? } return jobs; }
public override bool Update(IList<FileMetaDataItem> items) { SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); SqliteTransaction trasaction = (SqliteTransaction)con.BeginTransaction(); try { foreach (FileMetaDataItem item in items) { string cmdText = "UPDATE " + Configuration.TBL_METADATA + " SET " + Configuration.COL_HASH_CODE + " = @hash, " + Configuration.COL_LAST_MODIFIED_TIME + " = @lmf" + " WHERE " + Configuration.COL_RELATIVE_PATH + " = @rel AND " + Configuration.COL_SOURCE_ID + " = @sourceId"; SqliteParameterCollection paramList = new SqliteParameterCollection(); paramList.Add(new SqliteParameter("@hash", DbType.String) { Value = item.HashCode }); paramList.Add(new SqliteParameter("@lmf", DbType.DateTime) { Value = item.LastModifiedTime }); paramList.Add(new SqliteParameter("@rel", DbType.String) { Value = item.RelativePath }); paramList.Add(new SqliteParameter("@sourceId", DbType.String) { Value = item.SourceId }); db.ExecuteNonQuery(cmdText, false); } trasaction.Commit(); } catch (Exception) { trasaction.Rollback(); return false; } } return true; }
public bool SyncJobExists(string jobName, string id) { SQLiteAccess db = new SQLiteAccess(Path.Combine (this.StoragePath, Configuration.DATABASE_NAME ),false); using (SqliteConnection con = db.NewSQLiteConnection ()) { if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); // TODO: Change sql to SELECT COUNT(*)? string cmdText = "SELECT * FROM " + SYNCJOB_TABLE + " WHERE " + COL_SYNCJOB_NAME + " = @profileName AND " + COL_SYNCJOB_ID + " <> @id"; SqliteParameterCollection paramList = new SqliteParameterCollection(); paramList.Add(new SqliteParameter("@profileName", System.Data.DbType.String) { Value = jobName }); paramList.Add(new SqliteParameter("@id", System.Data.DbType.String) { Value = id}); bool found = false; db.ExecuteReader(cmdText, paramList, reader => { found = true; return; } ); return found; } }
public override FolderMetadata LoadFolderMetadata(string currId, SourceOption option) { string opt = (option == SourceOption.SOURCE_ID_NOT_EQUALS) ? " <> " : " = "; var folderMetadata = new FolderMetadata(currId, this.RootPath); var db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); string cmdText = "SELECT * FROM " + Configuration.TLB_FOLDERMETADATA + " WHERE " + Configuration.COL_SOURCE_ID + opt + " @sourceId"; var paramList = new SqliteParameterCollection {new SqliteParameter("@sourceId", DbType.String) {Value = currId}}; db.ExecuteReader(cmdText, paramList, reader => folderMetadata.FolderMetadataItems.Add( new FolderMetadataItem( (string)reader[Configuration.COL_SOURCE_ID], (string)reader[Configuration.COL_FOLDER_RELATIVE_PATH], (int)reader[Configuration.COL_IS_FOLDER_EMPTY] ))); } return folderMetadata; }
/// <summary> /// Add a list of file metadata item into database /// Actom action /// </summary> /// <param name="mData"></param> /// <returns></returns> public override bool Add(IList <FileMetaDataItem> mData) { var db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME), false); using (SqliteConnection con = db.NewSQLiteConnection()) { if (con == null) { throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME))); } var trasaction = (SqliteTransaction)con.BeginTransaction(); try { const string cmdText = "INSERT INTO " + Configuration.TBL_METADATA + "( " + Configuration.COL_SOURCE_ID + "," + Configuration.COL_RELATIVE_PATH + "," + Configuration.COL_HASH_CODE + "," + Configuration.COL_LAST_MODIFIED_TIME + "," + Configuration.COL_NTFS_ID1 + "," + Configuration.COL_NTFS_ID2 + ")" + "VALUES (@source_id , @relative_path, @hash_code, @last_modified_time, @ntfs_id1, @ntfs_id2) "; foreach (FileMetaDataItem item in mData) { var paramList = new SqliteParameterCollection { new SqliteParameter("@source_id", DbType.String) { Value = item.SourceId }, new SqliteParameter("@relative_path", DbType.String) { Value = item.RelativePath }, new SqliteParameter("@hash_code", DbType.String) { Value = item.HashCode }, new SqliteParameter("@last_modified_time", DbType.DateTime) { Value = item.LastModifiedTime }, new SqliteParameter("@ntfs_id1", DbType.Int32) { Value = item.NTFS_ID1 }, new SqliteParameter("@ntfs_id2", DbType.Int32) { Value = item.NTFS_ID2 } }; db.ExecuteNonQuery(cmdText, paramList); } trasaction.Commit(); } catch (Exception) { trasaction.Rollback(); return(false); } } return(true); }