We’ve already made it to the third of our successful product hackdays, giving our developers a day to work on tweaks, gripes, improvements, or whole new features of their choosing and then sharing those with the rest of the team.
The day’s projects
Alan – Test scripting improvements
I did some prototype work on a new version of our “TPA” test scripting language. I wanted to store a representation of the test objects and commands in the application’s configuration tables and then begin using that stored knowledge to generate the test SQL dynamically rather than having lengthy stored procedures.
Chris G – Gemini Upgrade
I looked into upgrading our task tracking software Gemini to the latest version. The new version seemed to solve most of my main bugbears – editing worked in any browser, two people editing at the same time was handled better (but not as good as Bugzilla). It also allowed integration with SharePoint, and SVN, as well as Windows authentication.
Upgrading straight to the new version was not possible, and unfortunately Countersoft have redesigned their website so all links to the download the intermediate version redirect to the homepage. After contacting their support I was able to get a copy. Upgrading then was not too difficult, there were ~60 tasks that were orphaned (not part of a project) and had to be excluded from the migration, these weren’t accessible through the old front end anyway.
The further upgrade to the latest version appeared to go without a hitch but when viewing the frontend none of the links worked, and going directly to issues only displayed the description and not comments or additional fields. I suspect this is because of the introduction of Project Templates which would need to be configured. I recommend interested parties performing a more serious evaluation of Gemini v6 when it’s released, deciding we do want to upgrade, and devoting some time to get this to work.
Chris H – Xamarin Mobile App
My aim for the day was to try out the Xamarin cross-platform mobile development toolkit, which supports Android, iOS, and windows development using C#. To keep things manageable I focused on Android development only, and decided to start on an app to collect homecare actuals using a mobile phone.
The experience was not altogether positive. Initially I started from the Xamarin sample field service app but after two hours of trying to figure out the dependencies was getting nowhere and decided to stop. The various build error messages didn’t clearly point me in the direction of what was going on, and Googling them didn’t produce any results.
Following that, I began building an app from scratch cribbing code from hello world and tutorial projects provided by Xamarin. These were of high quality and well documented and I feel that studied in more depth would be an excellent way to learn more about the system. However, my progress was fairly slow and this wasn’t helped by the slowness of the android emulator (apparently using a real device is much better) or the flakiness of its connection to the debugger.
Chris P – Improve Coverage of Smoke Tests
ContrOCC currently includes some smoke tests which are run by a debug option to programmatically display screens to flush out coding errors and especially compilation errors in SQL stored procedures used to select data since such errors are only detected at run time.
I have worked on extending the coverage of these smoke tests so that:
- Where tabbed controls are used, each tab is displayed. This ensures that any data associated with lists on these tabs gets loaded since for performance reason loading is delayed until the tab is displayed.
- Where lists are used, ensure that:
- Where no population has been performed (eg. where for performance reasons population is not done until user enters filter criteria and selects Apply) that the list gets populated
- The popup form associated with the list gets displayed to extend coverage of SQL stored procedures which are executed
- Lists and tabs on displayed popup forms are recursively processed as for the main content type screens
- All dictionary lists and their associated popup forms are displayed. Currently the smoke test just displays one dictionary.
Generally I feel that the changes are relatively robust and could be included with minimal risk into the main development. The main issue would be to make sure the test database is updated to include data for all lists to prevent a load of warnings relating to tests which cannot be run at present.
Julian – Performance Dashboard Reports
These reports are designed to help users identify performance bottlenecks on a SQL Server. Key points:
- It’s a free download from Microsoft, consisting of a setup script (“setup.sql” which has to be run on the server), a fairly short help file (PerfDash.chm) and ~20 Report Definition Files.
- Install on the PC from which you’re running SQL Server Management Studio (SSMS).
- There are versions for 2005, 2008 and 2012.
- They simply make use of the Dynamic Management Views (DMVs) that were introduced in SQL Server 2005.
- Therefore they are lightweight in operation.
- They use the Custom Reports functionality – Reporting Services is not involved / required.
- ContrOCC uses some of these in the Performance Information framework.
- They report on all databases on the server.
- This might be a problem if we wanted to run a dashboard at an LA.
- It also means that information relating to ContrOCC databases may be missing / hard to find (as only a certain amount of data is retained).
Once you’ve got the download, running them is very simple:
From the dashboard, you can navigate to a number of other screens which show additional details, such as those relating to CPU usage, IO stats, missing indexes etc.
Summary
They provide a GUI for displaying information from DMVs – details we are already gathering using the Performance Information menu item, such as those relating to missing indexes etc.
Advantages
- Nice, easy to use GUI incorporating drill down.
- Gives the impression of real time monitoring.
- Good for rapid troubleshooting of immediate problems.
Disadvantages
- Not restricted to a “database of interest.”
- Unlikely that clients would allow us to install on live servers, no matter how lightweight, especially if hosting non-OCC databases.
Julian – Extended Events
These became available in SQL 2008 and provide a more granular level of monitoring than is available with tools such as SQL Server Profiler (which it is probably intended to replace). Like the Performance Dashboard Reports, and unlike SQL Server Profiler’s GUI, they have a low performance impact on the server. In 2008, there was no associated GUI interface and information was returned as XML. However, by SQL 2012, SSMS included a GUI component for working with Extended Events and viewing the results which makes everything a great deal simpler:
The general idea is that you start monitoring the events you’re interested in, leave it running for a while and then have a look at the results. There are about 300 events to chose from and results can be saved either to a file on the server (for large data sets of historical data) or a ring buffer (where older ones are overwritten).
Default Session
The system_health session is set up and enabled by default (as in the screenshot above). It reports on 17 events, including errors (mainly those relating to memory problems), waits and deadlocks. If you Watch Live Data, you get a list of events and, if you select one, its details. Depending on the type of event, you’ll get a different set of details. For the deadlock one (highlighted above), you get a graphical display of what happened!
Summary
We could use this new functionality at any client site running SQL 2008 or later if we wanted to monitor what’s going on in a variety of areas. Ideally, they would be on SQL 2012. There’s obviously an overlap with SQL Server Profiler and also Performance Monitor. Tools such as these may be useful if we need evidence to help inform clients about issues with the specification of their servers, for example, by using them to show that they need more memory, improved configuration, etc.
Maciej – Optimising UDFs using CLR code
My plan was to analyse and replace a number of User Defined Functions (UDFs) that cause some performance issues with CLR-based code. I played with one UDF in particular, trying to find out which piece of it takes the most time. It turned out that this UDF performs some data access which could be rewritten with CLR. I then spend the rest of the time analysing how the UDF works in detail and creating a general idea how to rewrite it with C#.
Unfortunately, I have had not enough time to implement the actual code. I’m going to do that in my spare time to check if the idea of CLR replacement is right in terms of performance.
Mark – jQuery CLNDR plugin
I investigated the jQuery CLNDR plugin for creating custom calendar controls on web sites. Unlike other calendar controls, CLNDR uses a developer-provided UI template rather than generating markup, allowing much more customisable displays. I wanted to see if it could be used for improving custom calendars in our web app, in particular the week picker and the timetabled actuals display.
Notes and observations
- It has three dependencies: jQuery 1.7+, moment.js (a JavaScript date-handling library) and underscore.js (a utilities library that includes a default template implementation).
- Documentation is somewhat lacking, particularly in explaining how to use templates. There is a hook to use your own template renderer if you want.
- The default template markup uses
<%=…%>which causes problems on ASPX pages. Different markup tags can be used. - The existing methods are aimed at a month display only. The basic method returns an one-month array of day objects which are iterated through to create the individual day cells in the calendar. All navigation events assume 1 month or 1 year forward or back.
- Calendar events are provided in a JSON array and are completely configurable. Event attributes can be used to set event div properties such as CSS class (see example below). Events appear in a collection associated with each day object. There is also an “all events in the current month” collection.
- Event start and end times are not handled by default, but judicious use of margins and template markup should allow a more timeline-based display. Multi-day events are possible, so for example a suspension could be displayed.
- There is a basic (JavaScript) event handling model that captures click events on navigation controls and calendar cells. Other event handlers can be bound in
readyanddoneRenderingevents.
Conclusion
The lack of week navigation means that it is not developed enough for our purposes at present, but it is worth keeping an eye on.
Matthew – Investigation into self-updating/installing client
The original intention was to create an example of a self-installing or self-updating ContrOCC Client which would in theory enable more frequent C# releases by providing simpler deployment. However, upon investigation it seemed that this was not especially valuable as the ‘simplest’ method for deploying ContrOCC clients via Group Policy is already used at some sites.
Instead I analysed the various reasons why clients might be reluctant to deploy new software updates, methods to address this reluctance, and different technologies and approaches to allow ContrOCC to ‘Update’ itself if we chose to do so.
Mike – Automatically generating database schema metadata XML
The ContrOCC metadata is a large XML file that contains a representation of the database schema that developers keep in sync. Currently the only way this can be done is by editing the XML text by hand. I have created a tool which reads the schema details from the database and updates the metadata XML automatically, prompting for table/column description text where it is required but none exists. There are other uses that this could be put to such as using it for faster schema checking but currently I have not had the time to try this.
There are a number of command line options (some of which can probably be dropped), a command to merge with the metadata would be:
mdd --server=localhost --database=Controcc_Testing ^ --merge --tablename=T_Actual ^ --metadata-xml="C:controccMetadataContrOCC Metadata.xml" ^ --output="Merged Metadata.xml"
The server setting will default to localhost so can be omitted in this example and if mdd is run within the structure of a ContrOCC checkout it will attempt to find the metadata file. There is also an --interactive option which will prompt for descriptions if none are present.
Nathan – Script to show info about an import/export specification
I’ve written a SQL script that outputs useful information about the stored procedures and/or columns for one or more of ContrOCC’s import/export specifications. This information includes flattened details of the parameters used in the SP as well as usage counts.
Tanmaya – Evaluation of AutoIt and Visual Studio for ContrOCC User Interface testing.
AutoIt is a freeware automation language for Windows. It has a BASIC-like structure and supports network protocols and Win32 DLLs. It comes with a Window Info tool which can be used to hover over a control/dialog to get its class name, ID, etc.
Advantages – Free, light weight
Disadvantages – It is time consuming to learn new language, find control ids and script them. Maintenance wise its costly too i.e. UI layout changes, we need to revisit the script/compile them to make them work.
Visual Studio test projects comes as part of the Premium or Ultimate editions. To create one, select New Project > C# > Test > Coded UI Test Project.
It come with a Coded UI Test Builder tool which lets you record actions, review them and automatically generate the test class. Development and testing with Coded UI is very quick and easy to use. The multiple tests can be run in one click and it produces reports just like existing ContrOCC automated test.
Summary – Considering all advantages, I would prefer going with Visual Studio instead of AutoIt.
Tom – Service Broker
I continued a previous project investigating Service Broker as a way to improve auditing performance.
Tomasz – Auto Comments
For my hackday project I tried writing a semi-auto commenting tool. The idea behind it is that we don’t write comments until the final commit when the simple application would find all the changes you did and point them out one by one in the changed files while navigating to the nearest comments/history section and fill in TaskID, date, author automatically leaving only the comment to the user.
I didn’t manage to complete the whole thing, but finished with something that can fetch revision changes based on revision number and date range, fetch file changes for selected revisions and display files and diff between them with additional navigation between change spots. What’s left is actually inserting the comments, which I’d like to follow up on another hackday or in my spare time.
Ulen – Getting Gemini to give “heads-up” on invalid tasks
A routine waste of time for a Responsible Developer is chasing up tasks with invalid information – such as missing Release Notes, or mismatched status information.
My task today was to write a routine that can be scheduled directly from Gemini (our task tracker) to email the developer and/or Task originator that something is amiss on the task, giving them a chance to fix things ahead of time.
In essence the job will run periodically throughout the day, and check for Tasks that have been recently updated. The type of problem found can then have a configured grace period before it sends the email (e.g. Unauthorised tasks would inform the Assignee asap; mismatched status/resolution would be more lenient up to an hour or two). It can then send a summary of all problems to the RD once per day.
This will involve a certain level of hard-coding to begin with but in time this process can be tweaked, further automated, and pertinent details held in Gemini in attributes against the Version.