Implemented unit testing for SQLExaminer.

2 files changed, 293 insertions, 33 deletions

diff --git a/Software/DB/SQLExaminer Projects/Override Tables.sdeproj b/Software/DB/SQLExaminer Projects/Override Tables.sdeproj
index 6b27b8c9a..c97b84dda 100644
--- a/Software/DB/SQLExaminer Projects/Override Tables.sdeproj
+++ b/Software/DB/SQLExaminer Projects/Override Tables.sdeproj
@@ -5,15 +5,14 @@
       <StorageType>LiveDb</StorageType>
       <ServerName>localhost\sqlexpress</ServerName>
       <Database>Tango</Database>
-      <WinAuth>False</WinAuth>
+      <WinAuth>True</WinAuth>
       <AzurePwd>False</AzurePwd>
-      <Login>Synchronizer</Login>
     </Source>
     <Source id="2">
       <EngineType>MsSqlServer</EngineType>
       <StorageType>LiveDb</StorageType>
-      <ServerName>twine.local\sqltwine</ServerName>
-      <Database>Tango</Database>
+      <ServerName>localhost\sqlexpress</ServerName>
+      <Database>Test</Database>
       <WinAuth>True</WinAuth>
       <AzurePwd>False</AzurePwd>
     </Source>
@@ -121,7 +120,7 @@
         <Column name="NAME" />
       </Columns>
     </Item>
-    <Item name="dbo.CATS" type="table" extracted="True">
+    <Item name="dbo.CATS" type="table">
       <Columns>
         <Column name="GUID" key="true" />
         <Column name="ID" />
@@ -659,17 +658,6 @@
         <Column name="DESCRIPTION" />
       </Columns>
     </Item>
-    <Item name="dbo.MACHINES_EVENTS_VIEW" type="view">
-      <Columns>
-        <Column name="DATE" />
-        <Column name="MACHINE" />
-        <Column name="USER_NAME" />
-        <Column name="GROUP" />
-        <Column name="CATEGORY" />
-        <Column name="TYPE" />
-        <Column name="DESCRIPTION" />
-      </Columns>
-    </Item>
     <Item name="dbo.MEDIA_COLORS" type="table" extracted="True">
       <Columns>
         <Column name="GUID" key="true" />
@@ -958,6 +946,152 @@
         <Column name="DESCRIPTION" />
       </Columns>
     </Item>
+    <Item name="Machine_Versions_Configurations" type="query" extracted="True">
+      <Columns>
+        <Column name="GUID" key="true" />
+        <Column name="ID" />
+        <Column name="LAST_UPDATED" />
+        <Column name="NAME" />
+        <Column name="CREATION_DATE" />
+        <Column name="APPLICATION_VERSION_GUID" />
+        <Column name="APPLICATION_OS_VERSION_GUID" />
+        <Column name="APPLICATION_FIRMWARE_VERSION_GUID" />
+        <Column name="APPLICATION_DISPLAY_PANEL_VERSION_GUID" />
+        <Column name="EMBEDDED_FIRMWARE_VERSION_GUID" />
+        <Column name="EMBEDDED_SOFTWARE_VERSION_GUID" />
+        <Column name="HARDWARE_VERSION_GUID" />
+      </Columns>
+      <Queries>
+        <Query id="1">
+          <Text><![CDATA[SELECT CONFIGURATIONS.*
+
+FROM MACHINE_VERSIONS
+INNER JOIN CONFIGURATIONS ON CONFIGURATIONS.GUID = MACHINE_VERSIONS.DEFAULT_CONFIGURATION_GUID]]></Text>
+          <UpdateTables>
+            <Table schema="dbo" name="CONFIGURATIONS" allowDelete="True" allowInsert="True" allowUpdate="True">
+              <KeyColumns>
+                <KeyColumn name="GUID" />
+              </KeyColumns>
+              <UpdateColumns>
+                <UpdateColumn name="ID" />
+                <UpdateColumn name="GUID" />
+                <UpdateColumn name="LAST_UPDATED" />
+                <UpdateColumn name="NAME" />
+                <UpdateColumn name="CREATION_DATE" />
+                <UpdateColumn name="APPLICATION_VERSION_GUID" />
+                <UpdateColumn name="APPLICATION_OS_VERSION_GUID" />
+                <UpdateColumn name="APPLICATION_FIRMWARE_VERSION_GUID" />
+                <UpdateColumn name="APPLICATION_DISPLAY_PANEL_VERSION_GUID" />
+                <UpdateColumn name="EMBEDDED_FIRMWARE_VERSION_GUID" />
+                <UpdateColumn name="EMBEDDED_SOFTWARE_VERSION_GUID" />
+                <UpdateColumn name="HARDWARE_VERSION_GUID" />
+              </UpdateColumns>
+            </Table>
+          </UpdateTables>
+        </Query>
+        <Query id="2">
+          <Text><![CDATA[SELECT CONFIGURATIONS.*
+
+FROM MACHINE_VERSIONS
+INNER JOIN CONFIGURATIONS ON CONFIGURATIONS.GUID = MACHINE_VERSIONS.DEFAULT_CONFIGURATION_GUID]]></Text>
+          <UpdateTables>
+            <Table schema="dbo" name="CONFIGURATIONS" allowDelete="True" allowInsert="True" allowUpdate="True">
+              <KeyColumns>
+                <KeyColumn name="GUID" />
+              </KeyColumns>
+              <UpdateColumns>
+                <UpdateColumn name="ID" />
+                <UpdateColumn name="GUID" />
+                <UpdateColumn name="LAST_UPDATED" />
+                <UpdateColumn name="NAME" />
+                <UpdateColumn name="CREATION_DATE" />
+                <UpdateColumn name="APPLICATION_VERSION_GUID" />
+                <UpdateColumn name="APPLICATION_OS_VERSION_GUID" />
+                <UpdateColumn name="APPLICATION_FIRMWARE_VERSION_GUID" />
+                <UpdateColumn name="APPLICATION_DISPLAY_PANEL_VERSION_GUID" />
+                <UpdateColumn name="EMBEDDED_FIRMWARE_VERSION_GUID" />
+                <UpdateColumn name="EMBEDDED_SOFTWARE_VERSION_GUID" />
+                <UpdateColumn name="HARDWARE_VERSION_GUID" />
+              </UpdateColumns>
+            </Table>
+          </UpdateTables>
+        </Query>
+      </Queries>
+    </Item>
+    <Item name="Machine_Versions_Configurations_IdsPacks" type="query" extracted="True">
+      <Columns>
+        <Column name="GUID" key="true" />
+        <Column name="ID" />
+        <Column name="LAST_UPDATED" />
+        <Column name="CONFIGURATION_GUID" />
+        <Column name="DISPENSER_TYPE_GUID" />
+        <Column name="LIQUID_TYPE_GUID" />
+        <Column name="CARTRIDGE_TYPE_GUID" />
+        <Column name="MID_TANK_TYPE_GUID" />
+        <Column name="IDS_PACK_FORMULA_GUID" />
+        <Column name="NAME" />
+        <Column name="PACK_INDEX" />
+        <Column name="IS_EMPTY" />
+      </Columns>
+      <Queries>
+        <Query id="1">
+          <Text><![CDATA[SELECT IDS_PACKS.*
+
+FROM MACHINE_VERSIONS
+INNER JOIN CONFIGURATIONS ON CONFIGURATIONS.GUID = MACHINE_VERSIONS.DEFAULT_CONFIGURATION_GUID
+INNER JOIN IDS_PACKS ON IDS_PACKS.CONFIGURATION_GUID = CONFIGURATIONS.GUID]]></Text>
+          <UpdateTables>
+            <Table schema="dbo" name="IDS_PACKS" allowDelete="True" allowInsert="True" allowUpdate="True">
+              <KeyColumns>
+                <KeyColumn name="GUID" />
+              </KeyColumns>
+              <UpdateColumns>
+                <UpdateColumn name="ID" />
+                <UpdateColumn name="GUID" />
+                <UpdateColumn name="LAST_UPDATED" />
+                <UpdateColumn name="CONFIGURATION_GUID" />
+                <UpdateColumn name="DISPENSER_TYPE_GUID" />
+                <UpdateColumn name="LIQUID_TYPE_GUID" />
+                <UpdateColumn name="CARTRIDGE_TYPE_GUID" />
+                <UpdateColumn name="MID_TANK_TYPE_GUID" />
+                <UpdateColumn name="IDS_PACK_FORMULA_GUID" />
+                <UpdateColumn name="NAME" />
+                <UpdateColumn name="PACK_INDEX" />
+                <UpdateColumn name="IS_EMPTY" />
+              </UpdateColumns>
+            </Table>
+          </UpdateTables>
+        </Query>
+        <Query id="2">
+          <Text><![CDATA[SELECT IDS_PACKS.*
+
+FROM MACHINE_VERSIONS
+INNER JOIN CONFIGURATIONS ON CONFIGURATIONS.GUID = MACHINE_VERSIONS.DEFAULT_CONFIGURATION_GUID
+INNER JOIN IDS_PACKS ON IDS_PACKS.CONFIGURATION_GUID = CONFIGURATIONS.GUID]]></Text>
+          <UpdateTables>
+            <Table schema="dbo" name="IDS_PACKS" allowDelete="True" allowInsert="True" allowUpdate="True">
+              <KeyColumns>
+                <KeyColumn name="GUID" />
+              </KeyColumns>
+              <UpdateColumns>
+                <UpdateColumn name="ID" />
+                <UpdateColumn name="GUID" />
+                <UpdateColumn name="LAST_UPDATED" />
+                <UpdateColumn name="CONFIGURATION_GUID" />
+                <UpdateColumn name="DISPENSER_TYPE_GUID" />
+                <UpdateColumn name="LIQUID_TYPE_GUID" />
+                <UpdateColumn name="CARTRIDGE_TYPE_GUID" />
+                <UpdateColumn name="MID_TANK_TYPE_GUID" />
+                <UpdateColumn name="IDS_PACK_FORMULA_GUID" />
+                <UpdateColumn name="NAME" />
+                <UpdateColumn name="PACK_INDEX" />
+                <UpdateColumn name="IS_EMPTY" />
+              </UpdateColumns>
+            </Table>
+          </UpdateTables>
+        </Query>
+      </Queries>
+    </Item>
   </Items>
   <SchemaMapping>
     <SMItem schema1="dbo" schema2="dbo" />
diff --git a/Software/DB/SQLExaminer Projects/Provision Machine.sdeproj b/Software/DB/SQLExaminer Projects/Provision Machine.sdeproj
index 1343686d3..ec457f2e3 100644
--- a/Software/DB/SQLExaminer Projects/Provision Machine.sdeproj
+++ b/Software/DB/SQLExaminer Projects/Provision Machine.sdeproj
@@ -5,17 +5,14 @@
       <StorageType>LiveDb</StorageType>
       <ServerName>localhost\sqlexpress</ServerName>
       <Database>Tango</Database>
-      <WinAuth>False</WinAuth>
+      <WinAuth>True</WinAuth>
       <AzurePwd>False</AzurePwd>
-      <Login>Synchronizer</Login>
-      <Password>Aa123456</Password>
-      <SavePasswords>true</SavePasswords>
     </Source>
     <Source id="2">
       <EngineType>MsSqlServer</EngineType>
       <StorageType>LiveDb</StorageType>
-      <ServerName>twine.local\sqltwine</ServerName>
-      <Database>Tango</Database>
+      <ServerName>localhost\sqlexpress</ServerName>
+      <Database>Test</Database>
       <WinAuth>True</WinAuth>
       <AzurePwd>False</AzurePwd>
     </Source>
@@ -661,17 +658,6 @@
         <Column name="DESCRIPTION" />
       </Columns>
     </Item>
-    <Item name="dbo.MACHINES_EVENTS_VIEW" type="view">
-      <Columns>
-        <Column name="DATE" />
-        <Column name="MACHINE" />
-        <Column name="USER_NAME" />
-        <Column name="GROUP" />
-        <Column name="CATEGORY" />
-        <Column name="TYPE" />
-        <Column name="DESCRIPTION" />
-      </Columns>
-    </Item>
     <Item name="dbo.MEDIA_COLORS" type="table">
       <Columns>
         <Column name="GUID" key="true" />
@@ -1039,6 +1025,72 @@ WHERE MACHINES.SERIAL_NUMBER = '@']]></Text>
         </Query>
       </Queries>
     </Item>
+    <Item name="Sync_Cats" type="query" extracted="True">
+      <Columns>
+        <Column name="GUID" key="true" />
+        <Column name="ID" />
+        <Column name="LAST_UPDATED" />
+        <Column name="NAME" />
+        <Column name="MACHINE_GUID" />
+        <Column name="RML_GUID" />
+        <Column name="LIQUID_TYPE_GUID" />
+        <Column name="DATA" />
+      </Columns>
+      <Queries>
+        <Query id="1">
+          <Text><![CDATA[SELECT CATS.*
+
+FROM CATS
+
+INNER JOIN MACHINES ON MACHINES.GUID = CATS.MACHINE_GUID
+
+WHERE MACHINES.SERIAL_NUMBER = '1111']]></Text>
+          <UpdateTables>
+            <Table schema="dbo" name="CATS" allowDelete="True" allowInsert="True" allowUpdate="True">
+              <KeyColumns>
+                <KeyColumn name="GUID" />
+              </KeyColumns>
+              <UpdateColumns>
+                <UpdateColumn name="ID" />
+                <UpdateColumn name="GUID" />
+                <UpdateColumn name="LAST_UPDATED" />
+                <UpdateColumn name="NAME" />
+                <UpdateColumn name="MACHINE_GUID" />
+                <UpdateColumn name="RML_GUID" />
+                <UpdateColumn name="LIQUID_TYPE_GUID" />
+                <UpdateColumn name="DATA" />
+              </UpdateColumns>
+            </Table>
+          </UpdateTables>
+        </Query>
+        <Query id="2">
+          <Text><![CDATA[SELECT CATS.*
+
+FROM CATS
+
+INNER JOIN MACHINES ON MACHINES.GUID = CATS.MACHINE_GUID
+
+WHERE MACHINES.SERIAL_NUMBER = '1111']]></Text>
+          <UpdateTables>
+            <Table schema="dbo" name="CATS" allowDelete="True" allowInsert="True" allowUpdate="True">
+              <KeyColumns>
+                <KeyColumn name="GUID" />
+              </KeyColumns>
+              <UpdateColumns>
+                <UpdateColumn name="ID" />
+                <UpdateColumn name="GUID" />
+                <UpdateColumn name="LAST_UPDATED" />
+                <UpdateColumn name="NAME" />
+                <UpdateColumn name="MACHINE_GUID" />
+                <UpdateColumn name="RML_GUID" />
+                <UpdateColumn name="LIQUID_TYPE_GUID" />
+                <UpdateColumn name="DATA" />
+              </UpdateColumns>
+            </Table>
+          </UpdateTables>
+        </Query>
+      </Queries>
+    </Item>
     <Item name="Sync_Configuration" type="query" extracted="True">
       <Columns>
         <Column name="GUID" key="true" />
@@ -1387,6 +1439,80 @@ WHERE MACHINES.SERIAL_NUMBER = '@']]></Text>
         </Query>
       </Queries>
     </Item>
+    <Item name="Sync_Users" type="query" extracted="True">
+      <Columns>
+        <Column name="GUID" key="true" />
+        <Column name="ID" />
+        <Column name="LAST_UPDATED" />
+        <Column name="DELETED" />
+        <Column name="EMAIL" />
+        <Column name="PASSWORD" />
+        <Column name="ORGANIZATION_GUID" />
+        <Column name="CONTACT_GUID" />
+        <Column name="ADDRESS_GUID" />
+        <Column name="LAST_LOGIN" />
+      </Columns>
+      <Queries>
+        <Query id="1">
+          <Text><![CDATA[SELECT USERS.*
+
+FROM USERS
+
+INNER JOIN ORGANIZATIONS ON ORGANIZATIONS.GUID = USERS.ORGANIZATION_GUID
+INNER JOIN MACHINES ON MACHINES.ORGANIZATION_GUID = ORGANIZATIONS.GUID
+
+WHERE MACHINES.SERIAL_NUMBER = '@']]></Text>
+          <UpdateTables>
+            <Table schema="dbo" name="USERS" allowDelete="True" allowInsert="True" allowUpdate="True">
+              <KeyColumns>
+                <KeyColumn name="EMAIL" />
+              </KeyColumns>
+              <UpdateColumns>
+                <UpdateColumn name="ID" />
+                <UpdateColumn name="GUID" />
+                <UpdateColumn name="LAST_UPDATED" />
+                <UpdateColumn name="DELETED" />
+                <UpdateColumn name="EMAIL" />
+                <UpdateColumn name="PASSWORD" />
+                <UpdateColumn name="ORGANIZATION_GUID" />
+                <UpdateColumn name="CONTACT_GUID" />
+                <UpdateColumn name="ADDRESS_GUID" />
+                <UpdateColumn name="LAST_LOGIN" />
+              </UpdateColumns>
+            </Table>
+          </UpdateTables>
+        </Query>
+        <Query id="2">
+          <Text><![CDATA[SELECT USERS.*
+
+FROM USERS
+
+INNER JOIN ORGANIZATIONS ON ORGANIZATIONS.GUID = USERS.ORGANIZATION_GUID
+INNER JOIN MACHINES ON MACHINES.ORGANIZATION_GUID = ORGANIZATIONS.GUID
+
+WHERE MACHINES.SERIAL_NUMBER = '@']]></Text>
+          <UpdateTables>
+            <Table schema="dbo" name="USERS" allowDelete="True" allowInsert="True" allowUpdate="True">
+              <KeyColumns>
+                <KeyColumn name="EMAIL" />
+              </KeyColumns>
+              <UpdateColumns>
+                <UpdateColumn name="ID" />
+                <UpdateColumn name="GUID" />
+                <UpdateColumn name="LAST_UPDATED" />
+                <UpdateColumn name="DELETED" />
+                <UpdateColumn name="EMAIL" />
+                <UpdateColumn name="PASSWORD" />
+                <UpdateColumn name="ORGANIZATION_GUID" />
+                <UpdateColumn name="CONTACT_GUID" />
+                <UpdateColumn name="ADDRESS_GUID" />
+                <UpdateColumn name="LAST_LOGIN" />
+              </UpdateColumns>
+            </Table>
+          </UpdateTables>
+        </Query>
+      </Queries>
+    </Item>
     <Item name="Sync_UsersRoles" type="query" extracted="True">
       <Columns>
         <Column name="GUID" key="true" />