1 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
2 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
3 <html xmlns="http://www.w3.org/1999/xhtml" id="sixapart-standard">
5 <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
6 <meta name="generator" content="Movable Type 5.2.3" />
8 <link rel="stylesheet" href="http://defaria.com/blogs/Status/styles-site.css" type="text/css" />
9 <link rel="alternate" type="application/atom+xml" title="Atom" href="http://defaria.com/blogs/Status/atom.xml" />
10 <link rel="alternate" type="application/rss+xml" title="RSS 2.0" href="http://defaria.com/blogs/Status/index.xml"$>" />
12 <title>Status for Andrew DeFaria: October 9, 2005 - October 15, 2005 Archives</title>
14 <link rel="start" href="http://defaria.com/blogs/Status/" title="Home" />
15 <link rel="prev" href="http://defaria.com/blogs/Status/archives/week_2005_10_02.html" title="October 2, 2005 - October 8, 2005" />
16 <link rel="next" href="http://defaria.com/blogs/Status/archives/week_2005_10_16.html" title="October 16, 2005 - October 22, 2005" />
18 <body class="layout-one-column">
20 <div id="container-inner" class="pkg">
23 <div id="banner-inner" class="pkg">
24 <h1 id="banner-header"><a href="http://defaria.com/blogs/Status/" accesskey="1">Status for Andrew DeFaria</a></h1>
25 <h2 id="banner-description">Searchable status reports and work log</h2>
30 <div id="pagebody-inner" class="pkg">
32 <div id="alpha-inner" class="pkg">
34 <p class="content-nav">
35 <a href="http://defaria.com/blogs/Status/archives/week_2005_10_02.html">« October 2, 2005 - October 8, 2005</a> |
36 <a href="http://defaria.com/blogs/Status/">Main</a>
37 | <a href="http://defaria.com/blogs/Status/archives/week_2005_10_16.html">October 16, 2005 - October 22, 2005 »</a>
43 <h2 class="date-header">October 15, 2005</h2>
45 <div class="entry" id="entry-451">
46 <h3 class="entry-header">PQA Clearquest: Resolving remaining issues with bad data</h3>
47 <div class="entry-content">
48 <div class="entry-body">
49 <p>This is to document all of the issues related with bad data in the source databases that is impeeding the PQA Merge process. Along with the issues I will present what I did to work around the problems and what I suggest we should do to provide a smooth transition of these databases</p>
51 <h2>Resolving Data Conversion Problems in PQA Database Merge</h2>
53 <h3>New fields that are mandatory</h3>
55 <p>The following fieilds are new in the destination database (Cont) and are also mandatory thus must be filled in with something:</p>
58 <li><b>Cont:Found_On_Gold:</b> This field is defaulted to <i>No</i>.</li>
61 <h3>Fields with blank or invalid data</h3>
63 <p>The following fields have blank or invalid data in the source database thus fail validation when added to the new database:</p>
66 <li><b>TO:SQATestCase:</b> This field, when blank, is set to the string "N/A". This field is also renamed to <b>Cont:PQATestCase</b>.</li>
68 <li><b>TO:Title_2:</b> This field, when blank, is set to the string "N/A". This field is also renamed to <b>Cont:TItle</b>.</li>
70 <li><b>Prod:Software_Version:</b> This field, when blank, is set to the string "N/A".</li>
72 <li><b>Prod:HUT_Version:</b> This field, when blank, is set to the string "N/A". This field is also renamed to <b>Cont:Board_Revision</b>.</li>
74 <li><b>Prod:Software_Version:</b> This field, when blank, is set to the string "N/A".</li>
76 <li><b>Prod:SQATestCase:</b> This field, when blank, is set to the string "N/A". This field is also renamed to <b>Cont:PQATestCase</b>.</li>
79 <h3>Fields with data that are no longer valid in new database</h3>
81 <p>The following fields have data in the source database but fail validation when added to the new database due to reasons like not being on the list of acceptable values for the field in question:</p>
84 <li><b>Prod:Category</b> This field sometimes has the value of <i>Hardware</i> or <i>System Bios</i> which are not valid choices in the new database. For now I am changing <i>Hardware</i> -> <i>Hardware - Chip</i> and <i>System Bios</i> -> <i>Software</i>. I suggest we add <i>Hardware</i> and <i>System Bios</i> to the constant list as <i>HARDWARE</i> and <i>SYSTEM BIOS</i> (Capitalized) so we can easily find and correct these later.</li>
86 <li><b>Prod:Issue_Classification:</b> This field sometimes has the value of <i>Hardware</i> which is not a valid choice in the new database. For now I'm changing <i>Hardware</i> -> <i>Requirement</i>. I suggest we add <i>Hardware</i> to the constant list as <i>HARDWARE</i> (Capitalized) so we can easily find and correct these later.</li>
88 <li><b>Prod:Resolution:</b> This field sometimes has the value of <i>HW Fix</i> or <i>MAC Core</i> which are not valid choices in the new database. For now I am changing <i>HW Fix</i> -> <i>HW - Fix</i> and <i>MAC Core</i> -> <i>SW Fix</i>. I suggest we add <i>HW - Fix</i> and <i>MAC Core</i> to the constant list as <i>HW - FIX</i> and <i>MAC CORE</i> (Capitalized) so we can easily find and correct these later.</li>
90 <li><b>Prod:HUT_Revision:</b> This field is sometimes set to "n/a". This is translated to "N/A" (note capitalization).</li>
93 <h3>Fields that need truncating</h3>
95 <p>The following fields have data that is too large to fit in the size of the corresponding field in the destination database:</p>
98 <li><b>Prod:ActionNotes:</b> This field is defined with a maximum length of 50 characters in the TO database, 250 characters in the Prod database and 50 characters in the Cont database. Some entries in the Prod database are greater than 50 characters thus require truncation. I suggest that we increase the maximum length of <b>Cont:ActionNotes</b> to 250 characters to avoid this problem.</li>
101 <p class="entry-footer">
102 <span class="post-footers">Posted by at 6:41 PM</span> <span class="separator">|</span> <a class="permalink" href="http://defaria.com/blogs/Status/archives/000451.html">Permalink</a>
112 <h2 class="date-header">October 14, 2005</h2>
114 <div class="entry" id="entry-450">
115 <h3 class="entry-header">PQA Merge - Action Hooks & Attachments</h3>
116 <div class="entry-content">
117 <div class="entry-body">
119 <li>There are action hooks in Cont that send out email. I fear my testing may be generating lots of unnecessary email. Would like to have the Action Hook's email procedures coded to be conditional based on say an env var so we can turn off email during the production merge.</lI>
121 <li>Attachments are currently not handled because they are different and require special code to iterate through them to copy them to the destination database</li>
123 <li>Need to have the delete action available on the defect record so pqaclean can work. I've modified my schema for that</li>
125 <li>Changed pqamerge to only get the dbid's then to obtain the entity records when needed. Previously I would build a query with all fields. That query took from 6-40 minutes to run before I could even obtain the first record! Also the memory size of Perl grew very big. This algorithm is much faster with the query taking under 1 second!</li>
127 <li>Working on problems with the source data.</li>
130 <h3>Bad data. BAD DATA! Go sit in the corner!</h3>
132 <p>I'm discovering that not all the data in the source databases are clean to start with. For example, HUT_Version -> Board_Revision. in TO all defects have valid HUT_Versions. With Prod some of the HUT_Versions are blank! That's not good as that's an invalid value. For example, use Clearquest to bring up record Prod00002978's Board_Revision (It's shown in Clearquest as Board_Revision but is tied to the field HUT_Version) is blank. Select <strong>Modify</strong> and the field will be <font color=red>red</font>. On that same record you'll notice that <strong>Category</strong> is also <font color=red>red</font>. This is because <strong>Category</strong> is a <em>Constant list</em> and there is no <em>Hardware</em> in that constant list! There are <em>Hardware - Board</em> and <em>Hardware - Chip</em> however. Which do I choose?</p>
134 <p class="entry-footer">
135 <span class="post-footers">Posted by at 11:14 AM</span> <span class="separator">|</span> <a class="permalink" href="http://defaria.com/blogs/Status/archives/000450.html">Permalink</a>
145 <h2 class="date-header">October 13, 2005</h2>
147 <div class="entry" id="entry-449">
148 <h3 class="entry-header">PQA Merge</h3>
149 <div class="entry-content">
150 <div class="entry-body">
152 <li>Added Display, Logger and new TimeUtils to pqaclean and pqamerge</li>
154 <li>Finally got ProjectExists() to work. Turns out I was using the wrong CQPerlExt constant causing pqamerge to blow up</li>
156 <li>Received guidance from Vinh regarding certain field transformations. Incorporated them into pqamerge</li>
158 <li>Pqamerge now merging all of TO:defect records with a few exceptions that I'm fixing</li>
162 <p class="entry-footer">
163 <span class="post-footers">Posted by at 4:09 PM</span> <span class="separator">|</span> <a class="permalink" href="http://defaria.com/blogs/Status/archives/000449.html">Permalink</a>
173 <h2 class="date-header">October 12, 2005</h2>
175 <div class="entry" id="entry-448">
176 <h3 class="entry-header">PQA Clearquest: Project fields</h3>
177 <div class="entry-content">
178 <div class="entry-body">
180 <li>Started coding TransferDefects(). This is where the rubber hits the road</li>
183 <h3>Project Fields</h3>
187 <p>Vinh, you may be seeing me asking more questions as I get into coding pqamerge. This email is about Project. Let me see if I understand this:</p>
189 <p>There are the following "project" related fields:</p>
192 <li><b>TO:Project:</b> Reference to Project stateless record. Since there are no Project records in the TO database I assume this field is not used and should be ignored from TO.</li>
194 <li><b>Prod:Project:</b> There are records in Prod. I assume these should be translated to <b>Cont:Found_In_Project</b>.</li>
196 <li><b>Prod:Project_Name:</b> Apparently not used and should be ignored?</li>
198 <li><b>TO:Found_In_Project:</b> Short string dynamic list consisting of <em>Release T2.0</em>, <em>Release T2.1</em>, <em>Release T2.5</em> and <em>Release T3.0</em>. I assume that this translates to <b>Cont:Found_In_Project</b> which is a reference to <b>Cont:Project</b>?</li>
201 <p>There are other "project" related fields that I'm a little confused about:</p>
204 <li><b>TO:CommittedToProject:</b> Short string, dynamic list</li>
206 <li><b>TO:DeferredToProject:</b> Short string, dynamic list</li>
208 <li><b>Prod:CommittedToProject:</b> Short string, <u>constant</u> list</li>
210 <li><b>Prod:DeferredToProject:</b> Short string, <u>constant</u> list</li>
212 <li><b>Cont:CommittedToProject:</b> Short string, dynamic list</li>
214 <li><b>Cont:DeferredToProject:</b> Short string, dynamic list</li>
217 <p>Should all of these be changed to references to the Cont:Project stateless record?</p>
219 <h3>Values for new mandatory fields</h3>
221 <p>I've added routines to dynamically create those dynamic lists, however I am having problems with the following fields. These fields are new for Cont and are mandatory. Since I don't have old data to fill them in with I do not know what you want them to be set to. Here are the fields and my guesses:</p>
224 <li><b> Board_Revision:</b> Default value: ???</li>
226 <li><b>Found_On_Gold:</b> This is a constant list of "Yes" or "No". I assume "No"</li>
228 <li><b>PQATestCase:</b> This is a constant list of "Yes" or "N/A". I assume "N/A"</li>
231 <p class="entry-footer">
232 <span class="post-footers">Posted by at 11:40 AM</span> <span class="separator">|</span> <a class="permalink" href="http://defaria.com/blogs/Status/archives/000448.html">Permalink</a>
242 <h2 class="date-header">October 11, 2005</h2>
244 <div class="entry" id="entry-447">
245 <h3 class="entry-header">PQA Clearquest</h3>
246 <div class="entry-content">
247 <div class="entry-body">
249 <li>Resolved problems being able to log into the new Cont database by redoing the importation of CQSchema, CQ_Controller_Prod and CQ_Controller_Test</li>
251 <li>Imported user accounts database from old schema to new schema. This was done by simply setting all users to all databases. While this is not what we will do for the production move it allows me to continue coding and testing</li>
253 <li>Started coding pqamerge. Got to the point of being able to transfer Customer and Project records</li>
255 <li>Coded pqaclean. This is a little script to effectively erase the destination database. This allows me to run pqamerge over and over from a clean slate. Had to add a delete action to the defect record</li>
258 <p class="entry-footer">
259 <span class="post-footers">Posted by at 9:15 PM</span> <span class="separator">|</span> <a class="permalink" href="http://defaria.com/blogs/Status/archives/000447.html">Permalink</a>
269 <h2 class="date-header">October 10, 2005</h2>
271 <div class="entry" id="entry-446">
272 <h3 class="entry-header">PQA Clearquest</h3>
273 <div class="entry-content">
274 <div class="entry-body">
276 <li>Hooked up new PQA schema database and data database</li>
278 <li>Starting to code merge routines</li>
280 <li>Created PQA Schedule (See below)</li>
283 <h3>Moving MS/SQL Databases</h3>
285 <p>Moving a MS/SQL Database from one host to another involves a bit of tweaking to get the new system to recognize that it has a new database. I will attempt to detail here what needs to be done. </p>
287 <p>In order to import Clearquest databases to a new MS SQL Database Server:</p>
290 <li>Place imported datafile onto new machine.</li>
292 <li>Start the MS SQL Server Enterprise Manager (C:\Windows\system32\mmc.exe /s "C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server Enterprise Manager.msc")</li>
294 <li>Expand tree until you see "(local)" then "Databases". Right click on Database and select New Database to create an empty new database. Name the database appropriately. On the Data Files tab we changed the default path of C:\Program Files\Microsoft SQL Server\MSSQL\data\<database name>.mdf to a more approriate place. Also check the Transaction Log tab to adust path.</li>
296 <li>Restore database by right clicked on the new database you created and selecting All Tasks: Restore Database. Under Restore select From Device then click Add then find the restored db file. Under Options change the paths for Move to physical file name.</li>
298 <li>For Clearquest Schema databases only (You may need to restart the SQL Server Enterprise Manager after the above restore of the database): Since Clearquest uses a database to hold the schema and that schema points to known user databases, importing a Clearquest schema will have entries that point over to <b>projection</b> databases! These need to change:
301 <li>In SQL Sever Enterprise Manager locate your imported schema database and double click on Tables.</li>
303 <li>Find master_dbs, right click on master_dbs and select Open Table: Return All Rows.</li>
305 <li>Change the server column to the name of the new server</li>
307 <li>Select Run (the ! symbol in the toolbar)</li>
310 <li>Folllow instructions at <a href="http://support.microsoft.com/default.aspx?scid=kb;%5bLN%5d;Q240872">How to resolve permission issues when you move a database between servers that are running SQL Server</a> for fixing permissions problems.</li>
312 <li>Import user databases. Note the fix above regarding master_dbs:server is <b>not</b> required for user databases</li>
315 <h3>Merge Routines and Connecting to Multiple Schemas</h3>
317 <p>With two schema databases I was having problems with my Perl script connecting to the databases. In the past I had:
319 <div class="code"><pre>
320 ## Internal variables ##
323 my $password = "*****";
324 my $masterdb = ""; # Don't need a masterdb (Using default?)
330 $session = CQPerlExt::CQSession_Build ();
332 $session->UserLogon ($login, $password, $db_name, $masterdb);
336 <p>Now when I run this in the presence of two schema databases I get:</p>
339 The database "MASTR" belonging to master database "2002.05.00" is an invalid name. Enter the correct name of a ClearQuest user database. at c:/Program Files/Rational/ClearQuest/lib/CQPerlExt.pm line 3713.
342 <p>I think I need to specify the proper $ masterdb name but I don't know what that might be... Hmmm... Seems that $masterdb is really just the Connection name that you'd see when you start Clearquest Designer or Clearquest Client and there are multiple schemas to choose from. That's odd because that could be potentially anything as the user could rename it. However if I use PQA_Old on p4test (which is what I named the old schema connection) then it works.</p>
344 <p>OK, more work on this tomorrow. I have already defined the new defect record in my Perl module. I just have to start coding the merge routines now...</p>
346 <h3>PQA Schedule</h3>
348 <p>The following is a rough estimate of the work needed to be completed to get to the point where we are ready to convert the live production databases</p>
351 <li> Restore new Schema DB and Data DB (<font color="#999">1 Day</font>)</li>
353 <li> Investigate record/field mappings (<font color="#999">2 Days</font>)</li>
355 <li>Determine order fo record additions (<font color="#999">1 Day</font>)</li>
357 <li>Investigate requirements for adding of records to Clearquest (<font color="#999">2 Days</font>)</li>
359 <li>Code/Test merge procedures (<font color="#999">3 Day</font>)</li>
361 <li>Test conversion on <b>p4test</b> (<font color="#999">4 Days</font>)</li>
364 <p><b>Total:</b> 13 Days</p>
366 <p class="entry-footer">
367 <span class="post-footers">Posted by at 12:11 PM</span> <span class="separator">|</span> <a class="permalink" href="http://defaria.com/blogs/Status/archives/000446.html">Permalink</a>