[Quantproject-developers] QuantProject/b2_DataAccess DataBaseVersionManager.cs,1.14,1.15 b2_DataAccess.csproj,1.15,1.16

[Marco M. <mi...@us...>]

Update of /cvsroot/quantproject/QuantProject/b2_DataAccess
In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv3761/b2_DataAccess

Modified Files:
	DataBaseVersionManager.cs b2_DataAccess.csproj 
Log Message:
Modified database's structure. New fields added to tickers_tickerGroups table.

GetTableOfSelectedTickers in SelectorByGroup now selects tickers belonging to the specified group at a given date, when SelectorByGroup has been instantiated with a particular date.


Index: b2_DataAccess.csproj
===================================================================
RCS file: /cvsroot/quantproject/QuantProject/b2_DataAccess/b2_DataAccess.csproj,v
retrieving revision 1.15
retrieving revision 1.16
diff -C2 -d -r1.15 -r1.16
*** b2_DataAccess.csproj	20 Jan 2005 19:12:28 -0000	1.15
--- b2_DataAccess.csproj	27 Aug 2005 19:20:26 -0000	1.16
***************
*** 138,141 ****
--- 138,146 ----
                  />
                  <File
+                     RelPath = "Tables\EventType.cs"
+                     SubType = "Code"
+                     BuildAction = "Compile"
+                 />
+                 <File
                      RelPath = "Tables\FaultyTickers.cs"
                      SubType = "Code"

Index: DataBaseVersionManager.cs
===================================================================
RCS file: /cvsroot/quantproject/QuantProject/b2_DataAccess/DataBaseVersionManager.cs,v
retrieving revision 1.14
retrieving revision 1.15
diff -C2 -d -r1.14 -r1.15
*** DataBaseVersionManager.cs	1 Dec 2004 22:47:00 -0000	1.14
--- DataBaseVersionManager.cs	27 Aug 2005 19:20:26 -0000	1.15
***************
*** 26,29 ****
--- 26,30 ----
  using System.Data;
  using System.Collections;
+ using QuantProject.ADT;
  
  
***************
*** 66,73 ****
  		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));
  		
      }
--- 67,76 ----
  		this.updatingMethods.Add(new updatingMethodHandler(this.createTables));
  		this.updatingMethods.Add(new updatingMethodHandler(this.alterTablesAddPrimaryKeys));
! 		this.updatingMethods.Add(new updatingMethodHandler(this.alterTablesRemovePrimaryKeys));
!     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));
+ 		this.updatingMethods.Add(new updatingMethodHandler(this.updateTables));
  		
      }
***************
*** 160,166 ****
  			this.executeCommand("ALTER TABLE tickerGroups ADD CONSTRAINT PKtgId PRIMARY KEY (tgId)");
  			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()
  	{
--- 163,174 ----
  			this.executeCommand("ALTER TABLE tickerGroups ADD CONSTRAINT PKtgId PRIMARY KEY (tgId)");
  			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 alterTablesRemovePrimaryKeys()
+   {
+     this.executeCommand("DROP INDEX PKttTgId_ttTiId ON tickers_tickerGroups");
+   }
  	private void alterTablesAddForeignKeys()
  	{
***************
*** 177,184 ****
--- 185,201 ----
      this.executeCommand("ALTER TABLE validatedTickers " +
        "ADD COLUMN vtEditDate DATETIME");
+     this.executeCommand("ALTER TABLE tickers_tickerGroups " + 
+       "ADD COLUMN ttEventType TEXT(1) NOT NULL");
+     this.executeCommand("ALTER TABLE tickers_tickerGroups " +
+       "ADD COLUMN ttEventDate DATETIME NOT NULL");
  	}
  	private void alterTablesAddIndexes()
  	{
  		//add code here for adding indexes to existing tables;
+     this.executeCommand("CREATE INDEX " + 
+                         "PK_ttTgId_ttTiId_ttEventDate_ttEventDate " +
+                         "ON tickers_tickerGroups " + 
+                         "(ttTgId, ttTiId, ttEventType, ttEventDate) "+
+                        	"WITH PRIMARY");
  	}
  
***************
*** 187,190 ****
--- 204,220 ----
  		this.executeCommand("DROP TABLE version");
  		this.executeCommand("DROP TABLE visuallyValidatedTickers");
+ 	}
+ 	
+ 	//inserts new rows or updates 
+ 	//existing rows in tables after structure's modifications
+ 	private void updateTables()
+ 	{
+ 		this.executeCommand("UPDATE tickers_tickerGroups " +
+ 		                    "SET tickers_tickerGroups.ttEventType ='I', " +
+ 		                    "tickers_tickerGroups.ttEventDate =" + 
+                         SQLBuilder.GetDateConstant(ConstantsProvider.DefaultDateForTickersAddedToGroups) + " " +
+ 		                    "WHERE tickers_tickerGroups.ttEventType Is Null " +
+ 		                    "AND tickers_tickerGroups.ttEventDate Is Null");
+ 		
  	}	
  	private void executeCommand(string commandToBeExecuted)