[Quantproject-developers] QuantProject/b2_DataAccess DataBaseVersionManager.cs,1.3,1.4
Brought to you by:
glauco_1
|
From: <mi...@us...> - 2004-01-16 19:17:03
|
Update of /cvsroot/quantproject/QuantProject/b2_DataAccess
In directory sc8-pr-cvs1:/tmp/cvs-serv14860/b2_DataAccess
Modified Files:
DataBaseVersionManager.cs
Log Message:
The management of the database has been simplified.
The class will update the existing database to the last structure designed in the project.
Index: DataBaseVersionManager.cs
===================================================================
RCS file: /cvsroot/quantproject/QuantProject/b2_DataAccess/DataBaseVersionManager.cs,v
retrieving revision 1.3
retrieving revision 1.4
diff -C2 -d -r1.3 -r1.4
*** DataBaseVersionManager.cs 3 Jan 2004 19:01:43 -0000 1.3
--- DataBaseVersionManager.cs 16 Jan 2004 19:17:00 -0000 1.4
***************
*** 46,61 ****
public class DataBaseVersionManager
{
- private OleDbDataAdapter oleDbDataAdapter;
- private DataTable dataTable_version;
- private string selectString;
- private string updateString;
- private int databaseVersion;
private OleDbConnection oleDbConnection;
delegate void updatingMethodHandler();
! private SortedList updatingMethods;
#region "DataBaseVersionManager"
/// <summary>
! /// The method initialize a populate updatingMethods, a SortedList
/// that contains all the delegates that encapsulate all methods
/// whose function is to modify the structure of the database.
--- 46,56 ----
public class DataBaseVersionManager
{
private OleDbConnection oleDbConnection;
delegate void updatingMethodHandler();
! private ArrayList updatingMethods;
#region "DataBaseVersionManager"
/// <summary>
! /// The method initialize a populate updatingMethods, an ArrayList
/// that contains all the delegates that encapsulate all methods
/// whose function is to modify the structure of the database.
***************
*** 65,74 ****
private void initialize_updatingMethods()
{
! this.updatingMethods = new SortedList();
! //After adding a new private method to the class, write
! //the following code, paying attention to the version number:
! //this.updatingMethods.Add(yourVersionNumber,
! // new updatingMethodHandler(this.yourMethodName))
! this.updatingMethods.Add( 2 , new updatingMethodHandler(this.updateToVersion2) );
}
public DataBaseVersionManager(OleDbConnection oleDbConnection)
--- 60,74 ----
private void initialize_updatingMethods()
{
! this.updatingMethods = new ArrayList();
! // After adding a new private method to the class (e.g. dropConstraints o
! // or similar) write similar code to the following:
! this.updatingMethods.Add(new updatingMethodHandler(this.createDatabase));
! this.updatingMethods.Add(new updatingMethodHandler(this.createTables));
! this.updatingMethods.Add(new updatingMethodHandler(this.alterTablesAddPrimaryKeys));
! this.updatingMethods.Add(new updatingMethodHandler(this.alterTablesAddForeignKeys));
! this.updatingMethods.Add(new updatingMethodHandler(this.alterTablesAddIndexes));
! this.updatingMethods.Add(new updatingMethodHandler(this.alterTablesAddColumns));
! this.updatingMethods.Add(new updatingMethodHandler(this.dropTables));
!
}
public DataBaseVersionManager(OleDbConnection oleDbConnection)
***************
*** 78,88 ****
this.oleDbConnection = oleDbConnection;
this.oleDbConnection.Open();
- this.selectString = "SELECT * FROM version";
- this.updateString = "UPDATE version SET veId = ?";
- this.oleDbDataAdapter = new OleDbDataAdapter(selectString, this.oleDbConnection);
- this.oleDbDataAdapter.UpdateCommand = new OleDbCommand(this.updateString,this.oleDbConnection);
- this.oleDbDataAdapter.UpdateCommand.Parameters.Add("@veId", OleDbType.Integer);
- this.oleDbDataAdapter.UpdateCommand.Parameters["@veId"].SourceColumn = "veId";
- this.dataTable_version = new DataTable();
this.initialize_updatingMethods();
}
--- 78,81 ----
***************
*** 96,146 ****
#endregion
#region "updating methods"
! private void updateToVersion2()
{
! string command =
! "create table validatedTickers " +
! "( vtTicker TEXT(8) , vtStartDate DATETIME , vtEndDate DATETIME , vtDate DATETIME, " +
! "CONSTRAINT myKey PRIMARY KEY ( vtTicker ) )";
! OleDbCommand oleDbCommand = new OleDbCommand( command , this.oleDbConnection );
! oleDbCommand.ExecuteNonQuery();
}
#endregion
#region "UpdateDataBaseStructure"
! private int getDataBaseVersionNumber()
! {
! this.dataTable_version.Clear();
! this.oleDbDataAdapter.Fill(this.dataTable_version);
! DataRow dataRow = this.dataTable_version.Rows[0];
! return (int)dataRow["veId"];
! }
! private void setNewDataBaseVersionNumber(int newVersionNumber)
! {
! this.dataTable_version.Rows[0]["veId"] = newVersionNumber;
! this.oleDbDataAdapter.Update(this.dataTable_version);
! this.dataTable_version.AcceptChanges();
! }
! private void executeMethod(DictionaryEntry itemContainingMethod)
! {
! this.databaseVersion = this.getDataBaseVersionNumber();
! int differenceBetweenNewVersionAndDataBaseVersion =
! (int)itemContainingMethod.Key - this.databaseVersion;
! if(differenceBetweenNewVersionAndDataBaseVersion == 1)
! //db's structure can be updated by the method contained in the item
! {
! updatingMethodHandler handler = (updatingMethodHandler)itemContainingMethod.Value;
! handler();
! //it calls the method that modifies the db structure
! this.setNewDataBaseVersionNumber((int)itemContainingMethod.Key);
! }
! }
! public void UpdateDataBaseStructure()
{
try
{
! foreach (DictionaryEntry method in this.updatingMethods)
{
! this.executeMethod(method);
}
this.oleDbConnection.Close();
--- 89,171 ----
#endregion
+
#region "updating methods"
! private void createDatabase()
! {
! //TODO code to create an empty DB if user doesn't select the QuantProject.mdb;
! }
! private void createTables()
{
! this.executeCommand("CREATE TABLE tickers (tiTicker TEXT(8))");
! this.executeCommand("CREATE TABLE quotes (quTicker TEXT(8), quDate DATETIME, " +
! "quOpen REAL, quHigh REAL, quLow REAL, quClose REAL, " +
! "quVolume INTEGER, quAdjustedClose REAL, quAdjustedCloseToCloseRatio FLOAT)");
! // table where to store the time period for which tickers' quotes have been validated
! this.executeCommand("CREATE TABLE validatedTickers " +
! "( vtTicker TEXT(8) , vtStartDate DATETIME , vtEndDate DATETIME , vtDate DATETIME)");
! // table of groups where you can collect tickers.
! // Groups are used to simplify operations like:
! // validating, updating data from the web, testing strategies
! this.executeCommand("CREATE TABLE tickerGroups " +
! "( tgId TEXT(8) , tgDescription(100), tgTgId TEXT(8))");
! // where to store the relation between a ticker and a group
! // NOTE that a group can be created inside another group and
! // a ticker can belong to one or more groups
! this.executeCommand("CREATE TABLE tickers_tickerGroups " +
! "( ttTgId TEXT(8) , ttTiId TEXT(8))");
}
+
+ private void alterTablesAddPrimaryKeys()
+ {
+ this.executeCommand("ALTER TABLE tickers ADD CONSTRAINT PKtiTicker PRIMARY KEY (tiTicker)");
+ this.executeCommand("ALTER TABLE quotes ADD CONSTRAINT PKquTicker_quDate " +
+ "PRIMARY KEY (quTicker, quDate)");
+
+ this.executeCommand("ALTER TABLE validatedTickers ADD CONSTRAINT myKey PRIMARY KEY ( vtTicker )");
+ this.executeCommand("ALTER TABLE tickers_tickerGroups " +
+ "ADD CONSTRAINT PKttTgId_ttTiId PRIMARY KEY ( ttTgId, ttTiId)");
+ }
+ private void alterTablesAddForeignKeys()
+ {
+ // add code here for adding foreign keys to existing tables;
+ }
+ private void alterTablesAddColumns()
+ {
+ //add code here for adding columns to existing tables;
+ }
+ private void alterTablesAddIndexes()
+ {
+ //add code here for adding indexes to existing tables;
+ }
+
+ private void dropTables()
+ {
+ this.executeCommand("DROP TABLE version");
+ }
+ private void executeCommand(string commandToBeExecuted)
+ {
+ try
+ {
+ OleDbCommand oleDbCommand = new OleDbCommand( commandToBeExecuted , this.oleDbConnection );
+ int checkCommandExecution = oleDbCommand.ExecuteNonQuery();
+ }
+ catch(Exception ex)
+ {
+ string notUsed = ex.ToString();// to avoid warning after compilation
+ }
+ }
+
#endregion
+
#region "UpdateDataBaseStructure"
! public void UpdateDataBaseStructure()
{
try
{
! foreach (Object method in this.updatingMethods)
{
! updatingMethodHandler handler = (updatingMethodHandler)method;
! handler();
}
this.oleDbConnection.Close();
***************
*** 153,156 ****
--- 178,182 ----
}
#endregion
+
}
}
|