Notes: the MySQL script must be run before the application is launched!
Introduction
Recently I found a great Bug Tracking Tool. But, before I use that one in production mode, I found that there are a few things missing from it like:
- ability to send notification e-mails (if a solution was found)
- ability to attach files (of any kind), such as screenshots, etc.
- ability to log the interaction between bugs (dependencies)
- developed as a standalone Windows-based binary application
A really good alternative would be the ToDoList and BugReporter projects for some of my tasks; but I needed a more scalable, distributed solution ...
Using the Software
Did you run the MySQL script for database creation? Then it is time to establish your first connection to the database. You have to enter the Server name, Database name, username and password access to database, as you can see below: (please make sure you have the proper rights to apply INSERT, UPDATE and DELETE statements on the Issue Tracker's database tables)
Every time the Issue Tracker application starts, it checks for the current logged in user, using the GetUserNameEx function (and then with GetUserName function if the first one fails) and adds it to the database if it is not there already. I assume that the computers using this software are on a private network using Active Directory authentication, which is the case for most software companies.
Next, you should choose your settings using the Settings... option from Issue Tracker's system menu:
And now you are ready to use the Issue Tracker application.
- to add a new bug click New, fill in the information, then click Insert; to abort the operation hit the Cancel button.
- to modify the data of a bug select it from the top list, click Change, update the information, than click Update; to abort the operation hit the Cancel button.
- to save the bug's information into a HTML document, select it from the top list, click Save as ..., then choose a file name, and hit OK.
- to print the bug's information, select it from the top list, click Print ..., than select your printer, and hit OK.
- to search the database by specific fields, unselect the issues from the top list, fill in the search criteria, and than hit Search.
The implementation
The Issue Tracker application has been designed with three layers of abstraction in mind (so that this software can be easily extended):
- the MFC/application layer - provides the basic functions of a typical Windows application; this layer it is implemented into the
CIssueTrackerApp, CIssueTrackerDlg, CUserConfigurationDlg, CFileAttachementsDlg, CConnectionSettingsDlg and CSmtpConfigurationDlg classes; - the object/data manipulation layer - provides specific functions to insert / delete / search for users, issues, and for its associated files and notes; it is implemented into the
CIssueUserItem, CIssueUserList, CIssueFileItem, CIssueFileList, CIssueNoteItem, CIssueNoteList, CIssueDataItem and CIssueDataListcode classes; - the ODBC database access layer - provides wrapper functions for database access through ODBC API; it is implemented into the
CDatabaseConnector and CDatabaseRecordset classes.
There is not really much to be said about the classes from the MFC/application layer:
- the
CIssueTrackerDlg class is the main interface of the application; - the
CUserConfigurationDlg class implements the user configuration dialog; - the
CFileAttachementsDlg class allows the user to perform basic operations on the files attached on a bug; - the
CConnectionSettingsDlg class allows the user to set-up the connection to database; - the
CSmtpConfigurationDlg class allows the user to set-up the connection to SMTP server.
The CIssueUserItem class is a wrapper around the IssueUser table and it has the following member functions:
UINT GetID() - returns the ID of an user;SetID( UINT uintID ) - sets the ID of an user;CString GetFullName() - returns the full name of an user;void SetFullName( CString lpszFullName ) - sets the full name of an user;CString GetEmailAddr() - returns the e-mail address of an user;void SetEmailAddr( CString lpszEmailAddr ) - sets the e-mail address of an user;GetNotification() - returns TRUE if a user wants notification through e-mail, and FALSE otherwise;void SetNotification( UINT uintNotification ) - enables/disables e-mail notification for an user;void CopyDataFrom( CIssueUserItem * pIssueUser ) - makes a copy of the data of another object of the same type.
The CIssueUserList class is an array of CIssueUserItem objects and it has the following member functions:
int GetSize() - returns the count of the users currently available;CIssueUserItem * GetItemAt( int nItemIndex ) - returns the user from nItemIndex position in array;void SetItemAt( int nItemIndex, CIssueUserItem * pIssueUser ) - sets a new user to nItemIndex position in array;void RemoveAllItems() - deletes all users stored in array;BOOL LoadAllItems() - load all users from database;BOOL InsertItem( CIssueUserItem * pIssueUser ) - inserts one new user into array, and also into the database;BOOL UpdateItem( CIssueUserItem * pIssueUser ) - updates the data of an user from array, and also into the database;BOOL DeleteItem( CIssueUserItem * pIssueUser ) - deletes an user from array, and also from the database;CIssueUserItem * SearchItem( UINT uintID, BOOL bUseDatabase ) - search for an user into array/database, based on his/her ID;CIssueUserItem * SearchItem( CString lpszFullName, BOOL bUseDatabase ) - search for an user into array/database, based on his/her full name;CIssueUserItem * SearchAddr( CString lpszEmailAddr, BOOL bUseDatabase ) - search for an user into array/database, based on his/her e-mail address;UINT GetUserLoggedID() - returns the ID of the currently logged in user;void SetUserLoggedID( UINT uintID ) - sets the ID of the currently logged in user;CString GetUserLoggedName() - returns the full name of the currently logged in user;void SetUserLoggedName( CString lpszName ) - sets the full name of the currently logged in user;CString GetUserLoggedEmailAddr() - returns the e-mail address of the currently logged in user;void SetUserLoggedEmailAddr( CString lpszEmailAddr ) - sets the e-mail address of the currently logged in user;BOOL ValidateCurrentUserLogged() - adds the currently logged in user to the database if its not there.
The CIssueFileItem class is a wrapper around the IssueFile table and it has the following member functions:
UINT GetID() - returns the ID of the file;void SetID( UINT uintID ) - sets the ID of the file;UINT GetIssueID() - returns the ID of an issue associated with the file;void SetIssueID( UINT uintIssueID ) - sets the ID of an issue associated with the file;CIssueUserItem * GetAuthor() - returns the author of the file;void SetAuthor( CIssueUserItem * itemAuthor ) - sets the author of the file;CString GetFileName() - returns the name of the file;void SetFileName( CString lpszFileName ) - sets the name of the file;UINT GetFileSize() - returns the size in bytes of the file;void SetFileSize( UINT uintFileSize ) - sets the size in bytes of the file;CTime GetModified() - returns the date/time of the file;void SetModified( CTime timeModified ) - sets the date/time of the file;CLongBinary * GetContent() - gets the content of the file;void SetContent( CLongBinary * byteContent ) - set the content of the file;void CopyDataFrom( CIssueFileItem* pIssueFile ) - makes a copy of the data of another object of the same type.
The CIssueFileList class is an array of CIssueFileItem objects and it has the following member functions:
int GetSize() - returns the count of the files currently available for an issue;CIssueFileItem * GetItemAt( int nItemIndex ) - returns the file from nItemIndex position in array;void SetItemAt( int nItemIndex, CIssueFileItem * pIssueFile ) - sets a new file to nItemIndex position in array;void RemoveAllItems() - deletes all files stored in array;BOOL LoadAllItems( UINT uintIssueID, CIssueUserList * listIssueUser ) - load all files from database associated to an issue;BOOL InsertItem( CIssueFileItem * pIssueFile ) - inserts one new file into array, and also into the database;BOOL UpdateItem( CIssueFileItem * pIssueFile ) - updates the data of a file from array, and also into the database;BOOL DeleteItem( CIssueFileItem * pIssueFile ) - deletes a file from array, and also from the database;CIssueFileItem * SearchItem( UINT uintID, CIssueUserList * listIssueUser, BOOL bUseDatabase ) - search for a file into array/database, based on its ID.
The CIssueNoteItem class is a wrapper around the IssueNote table and it has the following member functions:
UINT GetID() - returns the ID of the note;void SetID( UINT uintID ) - sets the ID of the note;UINT GetIssueID() - returns the ID of an issue associated with the note;void SetIssueID( UINT uintIssueID ) - sets the ID of an issue associated with the note;CIssueUserItem * GetAuthor() - returns the author of the note;void SetAuthor( CIssueUserItem * itemAuthor ) - sets the author of the note;UINT GetFileSize() - returns the size in bytes of the note;void SetFileSize( UINT uintFileSize ) - sets the size in bytes of the note;CTime GetModified() - returns the date/time of the note;void SetModified( CTime timeModified ) - sets the date/time of the note;CLongBinary * GetContent() - gets the content of the note;void SetContent( CLongBinary * byteContent ) - set the content of the note;void CopyDataFrom( CIssueNoteItem* pIssueNote ) - makes a copy of the data of another object of the same type.
The CIssueNoteList class is an array of CIssueNoteItem objects and it has the following member functions:
int GetSize() - returns the count of the notes currently available for an issue;CIssueNoteItem * GetItemAt( int nItemIndex ) - returns the note from nItemIndex position in array;void SetItemAt( int nItemIndex, CIssueNoteItem * pIssueNote ) - sets a new note to nItemIndex position in array;void RemoveAllItems() - deletes all notes stored in array;BOOL LoadAllItems( UINT uintIssueID, CIssueUserList * listIssueUser ) - load all notes from database associated to an issue;BOOL InsertItem( CIssueNoteItem * pIssueNote ) - inserts one new note into array, and also into the database;BOOL UpdateItem( CIssueNoteItem * pIssueNote ) - updates the data of a note from array, and also into the database;BOOL DeleteItem( CIssueNoteItem * pIssueNote ) - deletes a note from array, and also from the database;CIssueNoteItem * SearchItem( UINT uintID, CIssueUserList * listIssueUser, BOOL bUseDatabase ) - search for a note into array/database, based on its ID.
The CIssueDataItem class is a wrapper around the IssueData table and it has the following member functions:
UINT GetID() - returns the ID of an issue;void SetID( UINT uintID ) - sets the ID of an issue;CString GetTitle() - returns the title of an issue;void SetTitle( CString lpszTitle ) - sets the title of an issue;CString GetProduct() - returns the product where the issue was generated;void SetProduct( CString lpszProduct ) - sets the product where the issue was generated;CString GetComponent() - returns the component where the issue was generated;void SetComponent( CString lpszComponent ) - sets the component where the issue was generated;CString GetVersion() - returns the version of software witch generated the issue;void SetVersion( CString lpszVersion ) - sets the version of software witch generated the issue;CString GetPlatform() - returns the platform of software where the issue was generated;void SetPlatform( CString lpszPlatform ) - sets the platform of software where the issue was generated;UINT GetStatus() - sets the status of the issue;void SetStatus( UINT uintStatus ) - returns the status of the issue;UINT GetImportance() - returns the priority of the issue;void SetImportance( UINT uintImportance ) - sets the priority of the issue;CString GetMilestone() - returns the deadline of the issue;void SetMilestone( CString lpszMilestone ) - sets the deadline of the issue;CIssueUserItem * GetReporter() - returns the user who reported this issue;SetReporter( CIssueUserItem * itemReporter ) - sets the user who reported this issue;CIssueUserItem * GetResolver() - returns the software developer who should find a fix for this issue;void SetResolver( CIssueUserItem * itemResolver ) - sets the software developer who should find a fix for this issue;CIssueUserItem * GetConfirmer() - returns the QA contact who would confirm the fix for this issue;void SetConfirmer( CIssueUserItem * itemConfirmer ) - sets the QA contact who would confirm the fix for this issue;CString GetDependencies() - returns the IDs of the issues on witch this one depends;void SetDependencies( CString lpszDependencies ) - sets the IDs of the issues on witch this one depends;CString GetBlocksIssues() - returns the IDs of the issues blocked by this issue;void SetBlocksIssues( CString lpszBlocksIssues ) - sets the IDs of the issues blocked by this issue;CString GetDistribution() - returns the CC list of e-mail addresses of the notification e-mail;void SetDistribution( CString lpszDistribution ) - sets the CC list of e-mail addresses of the notification e-mail;CTime GetModified() - returns the date/time when this issue was last modified;void SetModified( CTime timeModified ) - sets the date/time when this issue was last modified;void CopyDataFrom( CIssueDataItem* pIssueData ) - makes a copy of the data of another object of the same type.
The CIssueDataList class is an array of CIssueDataItem objects and it has the following member functions:
int GetSize() - returns the count of the issues currently available;CIssueDataItem * GetItemAt( int nItemIndex ) - returns the issue from nItemIndex position in array;void SetItemAt( int nItemIndex, CIssueDataItem * pIssueData ) - sets a new issue to nItemIndex position in array;void RemoveAllItems() - deletes all issues stored in array;BOOL LoadAllItems( BOOL bRefreshUsers, BOOL bLoadAllFiles, BOOL bLoadAllNotes, BOOL bFindRelations ) - load all issues from database;BOOL InsertItem( CIssueDataItem * pIssueData ) - inserts one new issue into array, and also into the database;BOOL UpdateItem( CIssueDataItem * pIssueData ) - updates the data of an issue from array, and also into the database;BOOL DeleteItem( CIssueDataItem * pIssueData ) - deletes an issue from array, and also from the database;CIssueNoteItem * SearchItem( UINT uintID, BOOL bUseDatabase ) - search for an issue into array/database, based on its ID;BOOL SearchEngine( CIssueDataItem * pIssueData, BOOL bRefreshUsers, BOOL bLoadAllFiles, BOOL bLoadAllNotes, BOOL bFindRelations ) - search for all issues that matches the fields specified in pIssueData;BOOL FindRelation( UINT uintID, CString &lpszBlocks ) - search for all IDs of the issues that this issue blocks.
The CDatabaseConnector class is the basic resource to access our database through ODBC driver and it has the following member functions:
void AllocConnectorHandle() - allocates a new handle for a database connection;void FreeConnectorHandle() - frees the handle allocated before for a database connection;BOOL DriverConnect( LPCTSTR lpszInputConnection ) - creates a new ODBC connection to the database;BOOL OpenConnection() - opens a new ODBC connection to the database;void CloseConnection() - close the current ODBC connection to the database;BOOL ExecuteStatement( LPCTSTR lpszStatement ) - executes an SQL statement (such an INSERT, UPDATE, DELETE);void SetLoginTimeout( const long nLoginTimeout ) - sets the timeout for establishing a connection to the database;long GetLoginTimeout() - returns the timeout for establishing a connection to the database;BOOL IsDatabaseOpened() - returns TRUE if a connection to the database was established, and FALSE otherwise;long GetRowsAffected() - returns the number of the rows affected by the last SQL interrogation executed;DATABASE_TYPE GetDatabaseType() - returns the database type of the current connection (e.g. SQL SERVER, ORACLE, MYSQL etc.);void SetDatabaseType( DATABASE_TYPE uintDatabaseType ) - sets the database type for a new connection.
The CDatabaseRecordset class makes use of the connection provided by CDatabaseConnector and it has the following member functions:
void AllocRecordsetHandle() - allocates a new handle for an SQL statement;void FreeRecordsetHandle() - frees the handle allocated before for an SQL statement;BOOL OpenRecordset( LPCTSTR lpszStatement ) - executes an SQL interrogation and fetch the row set of data;void CloseRecordset() - close the row set of data created by the previous SQL interrogation;void GetFieldValue( WORD nFieldIndex, LONG nFieldType, DWORD nFieldSize, void* pDataBuffer ) - fetches a value from the nFieldIndex column, of type nFieldType, and it stores to pDataBuffer of size given by nFieldSize;void GetColumnAttr( WORD nColumnIndex, LPTSTR lpszColumnName, WORD nBufferLength, WORD &nColumnType, ULONG &nColumnSize, WORD &nColumnScale, BOOL &bNullable ) - gets the attributes of a column from the current row set of data;void SeekFirstRecord() - moves the cursor to the first record of the current row set of data;void SeekLastRecord() - moves the cursor to the last record of the current row set of data;void SeekPrevRecord() - moves the cursor to the previous record of the current row set of data;void SeekNextRecord() - moves the cursor to the next record of the current row set of data;long GetNumResultRows() - returns the number of the rows affected by the last SQL interrogation executed;long GetNumResultCols() - returns the number of columns in the current row set of data;void SetQueryTimeout( const long nQueryTimeout ) - sets the timeout for a SQL interrogation;long GetQueryTimeout() - returns the timeout selected for a SQL interrogation;BOOL IsRecordsetOpened() - returns TRUE if a row set of data is available, and FALSE otherwise;BOOL IsRecordsetBOF() - returns TRUE if the cursor reached the begin of row set of data, and FALSE otherwise;BOOL IsRecordsetEOF() - returns TRUE if the cursor reached the end of row set of data, and FALSE otherwise.
Points of interest
It is well known that Windows provides an easy access to its Registry database. So the Issue Tracker application makes a good use of it to store the database access settings. But I just could not store the access password in plain text. So I looked at the Microsoft Cryptography Library and I decided to implement two helper functions for this purpose: GetRegistryPassword and SetRegistryPassword.
BOOL GetRegistryPassword( LPCTSTR lpszCryptoKey, LPCTSTR lpszSection, LPCTSTR lpszEntry, LPTSTR lpszValue, LPCTSTR lpszDefault )
{
if ( !lpszSection || !lpszEntry || !lpszValue )
return FALSE;
if ( !USE_CRYPTO_METHODS )
{
return ( _tcscpy( lpszValue, AfxGetApp()->GetProfileString( lpszSection, lpszEntry, lpszDefault ) ) != NULL);
}
if ( !lpszCryptoKey )
lpszCryptoKey = AfxGetAppName();
LPBYTE lpcbPassword = (LPBYTE) lpszCryptoKey;
const DWORD dwPasswordLen = (DWORD) ( sizeof(TCHAR) * _tcslen( lpszCryptoKey ) );
BYTE lpcbDataValue[PASSWORD_MAXLENGTH];
DWORD dwHowManyBytes = 0;
LPBYTE lpcbTempBuffer = NULL;
BOOL bDecryptionDone = FALSE;
HCRYPTPROV hCryptoProvider = NULL;
HCRYPTHASH hCryptoHash = NULL;
HCRYPTKEY hCryptoKey = NULL;
if ( AfxGetApp()->GetProfileBinary( lpszSection, lpszEntry, (LPBYTE *)&lpcbTempBuffer, (UINT *)&dwHowManyBytes ) )
{
if ( dwHowManyBytes != 0)
{
ZeroMemory( lpcbDataValue, sizeof( lpcbDataValue ) );
CopyMemory( lpcbDataValue, lpcbTempBuffer, dwHowManyBytes );
if ( CryptAcquireContext( &hCryptoProvider, NULL, NULL, PROV_RSA_FULL, 0 ) )
{
if ( CryptCreateHash( hCryptoProvider, CALG_MD5, NULL, 0, &hCryptoHash ) )
{
if ( CryptHashData( hCryptoHash, lpcbPassword, dwPasswordLen, 0 ) )
{
if ( CryptDeriveKey( hCryptoProvider, CALG_RC4, hCryptoHash, CRYPT_EXPORTABLE, &hCryptoKey ) )
{
if ( CryptDecrypt( hCryptoKey, NULL, TRUE, 0, lpcbDataValue, &dwHowManyBytes ) )
{
bDecryptionDone = TRUE;
_tcscpy( lpszValue, (LPTSTR) lpcbDataValue );
}
else
{
DisplayLastError( _T("CryptDecrypt: ") );
}
VERIFY( CryptDestroyKey( hCryptoKey ) );
}
else
{
DisplayLastError( _T("CryptDeriveKey: ") );
}
}
else
{
DisplayLastError( _T("CryptHashData: ") );
}
VERIFY( CryptDestroyHash( hCryptoHash ) );
}
else
{
DisplayLastError( _T("CryptCreateHash: ") );
}
VERIFY( CryptReleaseContext( hCryptoProvider, 0 ) );
}
else
{
DisplayLastError( _T("CryptAcquireContext: ") );
}
}
}
else
{
if ( !dwHowManyBytes )
{
_tcscpy( lpszValue, lpszDefault );
bDecryptionDone = TRUE;
}
}
if ( lpcbTempBuffer != NULL )
delete lpcbTempBuffer;
return bDecryptionDone;
}
and
BOOL SetRegistryPassword( LPCTSTR lpszCryptoKey, LPCTSTR lpszSection, LPCTSTR lpszEntry, LPTSTR lpszValue )
{
if ( !lpszSection || !lpszEntry || !lpszValue )
return FALSE;
if ( !USE_CRYPTO_METHODS )
{
return AfxGetApp()->WriteProfileString( lpszSection, lpszEntry, lpszValue );
}
if ( !lpszCryptoKey )
lpszCryptoKey = AfxGetAppName();
LPBYTE lpcbPassword = (LPBYTE) lpszCryptoKey;
const DWORD dwPasswordLen = (DWORD)( sizeof( TCHAR ) * _tcslen( lpszCryptoKey ) );
BYTE lpcbDataValue[PASSWORD_MAXLENGTH];
const DWORD dwDataValueLen = PASSWORD_MAXLENGTH;
DWORD dwHowManyBytes = dwDataValueLen;
BOOL bEncryptionDone = FALSE;
HCRYPTPROV hCryptoProvider = NULL;
HCRYPTHASH hCryptoHash = NULL;
HCRYPTKEY hCryptoKey = NULL;
ZeroMemory( lpcbDataValue, sizeof( lpcbDataValue ) );
CopyMemory( lpcbDataValue, lpszValue, dwDataValueLen );
if ( CryptAcquireContext( &hCryptoProvider, NULL, NULL, PROV_RSA_FULL, 0 ) )
{
if ( CryptCreateHash( hCryptoProvider, CALG_MD5, NULL, 0, &hCryptoHash ) )
{
if ( CryptHashData( hCryptoHash, lpcbPassword, dwPasswordLen, 0 ) )
{
if ( CryptDeriveKey( hCryptoProvider, CALG_RC4, hCryptoHash, CRYPT_EXPORTABLE, &hCryptoKey ) )
{
if ( CryptEncrypt( hCryptoKey, NULL, TRUE, 0, lpcbDataValue, &dwHowManyBytes, dwDataValueLen ) )
{
bEncryptionDone = AfxGetApp()->WriteProfileBinary( lpszSection, lpszEntry, lpcbDataValue, (UINT)dwHowManyBytes );
}
else
{
DisplayLastError( _T("CryptEncrypt: ") );
}
VERIFY( CryptDestroyKey( hCryptoKey ) );
}
else
{
DisplayLastError( _T("CryptDeriveKey: ") );
}
}
else
{
DisplayLastError( _T("CryptHashData: ") );
}
VERIFY( CryptDestroyHash( hCryptoHash ) );
}
else
{
DisplayLastError( _T("CryptCreateHash: ") );
}
VERIFY( CryptReleaseContext( hCryptoProvider, 0 ) );
}
else
{
DisplayLastError( _T("CryptAcquireContext: ") );
}
return bEncryptionDone;
}
Other sensitive data are stored (only into the database) using MySQL's AES_ENCRYPT and AES_DECRYPT functions. You can found more information on this subject here.
And if you would like to run this application against either MS SQL Server or Oracle databases, then you need to adapt the SQL statements from the IssueTrackerExt.cpp file.
Final words
Issue Tracker application uses many components that have been published on The Code Project. Many thanks to:
In order to recompile this Visual C++ .NET 2005 project, you should consider downloading the boost::regex library from Boost's Official Page, since I'm using it to read e-mail addresses and issue's dependencies list.
The PJ Naughter's CPJNSMTPConnection class also requires the OpenSSL Project. A good choice would be to download the Windows binaries of the OpenSSL Project from Shining Light Productions page.
Whilst this tool was originally intended for my personal use only, it is now a 'community' project, so if you find it useful and want to make suggestions for enhancements or bug fixes, then post below.
History
- version 1.00 (July 13th, 2014) - Initial release.
