Tag Archives: database

Website Housekeeping: TablePress & Huge IT Portfolio

I think I have finally managed to get some serious website maintenance and cleanup done so I can move forward with confidence.   The tasks that I have performed over the last little while includes:

  • Full backup of my domain
  • Backup of my MySQL database for dwcryan.com
  • Deleting 2 Huge IT Portfolio Plugin tables from the database (the plugin itself I deleted a ways back)
  • Deleting 2 Unused Plugins (Aeolus Creative Portfolio and Hello Dolly)
  • Upgrading WordPress version (always trying to keep up to date for security reasons)
  • TablePress plugin update failed putting my site into maintenance mode, had to rename folder and install fresh (thankfully tables embedded into posts so they remained! 🙂 )
  • Using WP-Sweep to clean up unused, orphaned and duplicated data as well as to optimize my database tables (especially for removing old revisions of posts to try and speed things up)

I’ve used WP-Sweep once before and today was my second.  I still have one unused plug, Waving Portfolio, remaining.  I recall having issues with the plugin but am still in the hunt for how to do up my portfolio which leads me to the outstanding items on my website:

  • Update to latest version of Quark Theme… will happen soon now that I have backed up and cleaned up a fair bit, including backing up the Quark Theme folder specifically encase I loose my customizations (I should probably read up and potentially do a proper Child Theme)
  • Complete my homepage, still not sure what I want to do for the body
  • Investigate Portfolio option (quark gallery format and some manual maintenance on a portfolio page?)

This is always on going and though it may cause a little stress at times when things break as a whole I love it and enjoy the challenge which is why I need to try and get cracking on some personal project ideas of my own.

Database Design v3 – Quilt

Tracker Database v3 - Quilting
Tracker Database v3 – Quilting

I’m back!  This project has been put on the back burner for a while, but now that it is fall and winter is soon to follow I can’t think of a better time to ramp up development again, especially since a new requirement for my tracker application has arisen!  In chatting with my girlfriend, who is an avid quilter, would like a place to track the quilts she has completed.  Currently this is done by taking a picture of the quilt and storing just the image on her laptop.  She would like to keep track of other information as well, which lead to the addition of a Quilt table (first eight rows) and a Quilt Image Path table (remaining rows) including the following columns:

QuiltIDint(11)Primary Key of Quilt Table, Auto Increment (Also FK of QuiltImagePath table)
UserIDint(11)Foreign Key from User table
Lengthdouble(5,2)Will use inches as unit
Widthdouble(5,2)Will use inches as unit
Patternvarchar(255)The name of the pattern used in creating the quilt
Recipientvarchar(200)Who the quilt was made for
CompletedOndateWhen quilt was finished
Costdecimal(6,2)Total cost of quilt, includes materials, etc.
ImagePathvarchar(255)Path, with file name, to the quilt relative to project
ImagePathIDint(11)Primary Key of Quilt Image Path table, Auto Increment

I thought this would be a nice addition, as it also adds in another technical element I haven’t had to deal with in any of the other items I am tracking.  This is the addition of being able to upload, store, reference, and display an image related to the content.  Also have to implement the functionality to delete a record along with corresponding images.  I believe, without attempting an implementation yet, I am going to accomplish this by:

  • Storing the images in a folder on disk (this folder will be contained within my VS project so I know how to properly reference the folder)
  • Rename the file on upload after record is created to include primary key in file name for if need to cross reference for any reason, also ensures unique names are being stored
  • Storing the relative path (from project perspective) with file name in the database (FilePath column)
  • Implement deleting of a record to also include deleting of the file, on missing files skip and continue with deletion of record

My goal for the winter is to have an initial implementation for all the features I currently have slated for my tracker application.

Ping Results to Database

I updated my script to write the results to a database to allow for easier manipulation of the data through queries, instead of having to traverse a file, linearly.  The database design is very simple, consisting of only two tables, and can be easily expanded to contain more information about the device.   The bold fields in the tables are required fields.  There is a 1-to-many relationship between the device and the ping results, since each result can only be for one device, but each device can have many results.

Ping Results Database
Ping Results Database

The full script can be seen at the bottom of the post.  The script starts a job running the ping script block 4 times, sleeping for 14 seconds in between.  It is not an exact science here, but the idea is that the script can be scheduled using Windows Task Scheduler to run every minute and will ping all the machine on first starting and about every 15 seconds to make 4 times in a minute.

The script block creates a new SQL Client Connection object and opens a connection to the database, using the following code:

$sqlConnection = new-object System.Data.SqlClient.SqlConnection "server=IT-SERVICES\SQLEXPRESS;database=PingResults;User ID=username;Password=pass;"
$sqlConnection.Open()

Note that the username and password have been changed.

A command object is then created, where the only thing we need to set is the command text (what we want to execute on the SQL server), which in our is a SQL statement to retrieve the device ID and name for only devices that have been marked as active.  We need both as we are pinging the devices by its name and when inserting we need to be able to link the result to the correct device, which as noted above in the database design is reference by the foreign key (FK) DeviceId in the PingResult table.  Once the command text is set we execute the command by calling ExecuteReader().

#Create a command object
$sqlCommand = $sqlConnection.CreateCommand()
$sqlCommand.CommandText = "Select DeviceId, Name from [PingResults].[dbo].[Device] where IsActive=1"
 
#Execute the Command
$sqlReader = $sqlCommand.ExecuteReader()

The returned results are then looped through, pinging each machine in turn and formatting the results into the proper format for a SQL insert statement. Since we cannot insert null into a tinyint column we set the response time to live and response time to -1 if the returned result is null or empty. -1 is chosen since it has no practical meaning in this context (i.e. you can’t have a negative response time).

$sqlInsertQuery += "(" + $deviceId + ",'" + $RunDate +"'," + $status +"," + $timeout + ","+$ttl+","+ $rttl +",'"+$ri+"',"+$RT+"),"

The $sqlInsertQuery is initialized to store the start of the insert SQL statement and then each row to be inserted is formatted and appended to the end of the query, utilizing the line above.  Once all results have been concatenated to the query the loop exits and the trailing comma (,) is trimmed.  The database connection is then closed.  A new connection is opened, setting the command text to the built SQL insert statement, executed, and then closed.  This means for each run of the script block 2 queries are ran against the database.  The first is to retrieve the list of active machines and the second is to do an insert of all the results.

What’s Next?

The next phase of this project is to read the results that have been accumulating in the database to generate the appropriate table and graphs.  Each row of the table is going to consist of the machine name (linking to it’s day, week, month graph webpage), the percentage of down time over the last 120 points (30 minutes) and the average latency over the same interval.   The last recorded latency will be displayed in a column with the font colour indicating whether the device is up (green) or down (red).

The full script:

#Win32_PingStatus class
#http://library.wmifun.net/cimv2/win32_pingstatus.html
#    11001 Buffer Too Small
#    11002 Destination Net Unreachable
#    11003 Destination Host Unreachable
#    11004 Destination Protocol Unreachable
#    11005 Destination Port Unreachable
#    11006 No Resources
#    11007 Bad Option
#    11008 Hardware Error
#    11009 Packet Too Big
#    11010 Request Timed Out
#    11011 Bad Request
#    11012 Bad Route
#    11013 TimeToLive Expired Transit
#    11014 TimeToLive Expired Reassembly
#    11015 Parameter Problem
#    11016 Source Quench
#    11017 Option Too Big
#    11018 Bad Destination
#    11032 Negotiating IPSEC
#    11050 General Failure 
 
$pingScriptBlock = {
 
	$sqlInsertQuery = "INSERT INTO [PingResults].[dbo].[PingResult]
			   ([DeviceId]
			   ,[RunDate]
			   ,[Status]
			   ,[Timeout]
			   ,[TimeToLive]
			   ,[ResponseTTL]
			   ,[ReplyI]
			   ,[ResponseTime])
		 VALUES
			   "
 
	# Connect and run a command using SQL Native Client, Returns a recordset
	# Create and open a database connection
	$sqlConnection = new-object System.Data.SqlClient.SqlConnection "server=IT-SERVICES\SQLEXPRESS;database=PingResults;User ID=username;Password=pass;"
	$sqlConnection.Open()
 
	#Create a command object
	$sqlCommand = $sqlConnection.CreateCommand()
	$sqlCommand.CommandText = "Select DeviceId, Name from [PingResults].[dbo].[Device] where IsActive=1"
 
	#Execute the Command
	$sqlReader = $sqlCommand.ExecuteReader()
 
	#Parse the records
	while ($sqlReader.Read())
	{
		$MachineName = ($sqlReader["Name"].ToString())
		$deviceId = ($sqlReader["DeviceId"].ToString())
		$RunDate = Get-Date
 
		$PingStatus = Gwmi Win32_PingStatus -Filter "Address = '$MachineName'"
		Select-Object StatusCode
		$status = ($PingStatus.StatusCode)
		#Select-Object Timeout
		$timeout = ($PingStatus.Timeout)
		$ttl = ($PingStatus.TimeToLive)
		$rttl = ($PingStatus.ResponseTimeToLive)
		$ri = ($PingStatus.ReplyInconsistency)
		$RT = ($PingStatus.ResponseTime)
 
		#Won't let me insert empty/NULL for the value so going to make these -1 (which has no practical significance since rttl & RT cannot be -'ve)
		if([string]::IsNullOrEmpty($rttl))
		{
			$rttl = -1
		}
		if([string]::IsNullOrEmpty($RT))
		{
			$RT = -1
		}
 
		$sqlInsertQuery += "(" + $deviceId + ",'" + $RunDate +"'," + $status +"," + $timeout + ","+$ttl+","+ $rttl +",'"+$ri+"',"+$RT+"),"
	}
 
	$sqlInsertQuery = $sqlInsertQuery.Trim(',')
 
	# Close the database connection
	$sqlConnection.Close()
 
	# Create and open a database connection
	$sqlConnection = new-object System.Data.SqlClient.SqlConnection "server=IT-SERVICES\SQLEXPRESS;database=PingResults;User ID=username;Password=pass;"
	$sqlConnection.Open()
 
	#Create a command object
	$sqlCommand = $sqlConnection.CreateCommand()
	$sqlCommand.CommandText = $sqlInsertQuery
 
	#Execute the Command
	$sqlReader = $sqlCommand.ExecuteReader()
 
	# Close the database connection
	$sqlConnection.Close()
}
 
# 
Start-Job $pingScriptBlock
Start-Sleep -s 14
Start-Job $pingScriptBlock
Start-Sleep -s 14
Start-Job $pingScriptBlock
Start-Sleep -s 14
Start-Job $pingScriptBlock
 
Write-Host "Waiting for jobs to complete"
While(Get-Job -State "Running") { Start-Sleep 2 }
 
# Display output from all jobs
Get-Job | Receive-Job
 
# Cleanup
Remove-Job *

Tracker Database Design

Tracker Database v.1

What is an application without some sort of storage mechanism behind the scenes?  Above represents my database design which I am going to go into more details about.

At the heart of the database is the User table which stores user information and associates a reading list, measurement, or golf round with a corresponding user such that users can only see their data when logged in.  This also makes it easy to track additional information by adding the corresponding tables which will then get associated with a user if I choose to expand the application at a later time.  The other tables which stores, what I will call public data, is visible to all users to avoid duplication of data.  This includes golf course information (Golf Course, Address, Region and Course Par Information tables) and book information (Author, Book, and what authors are associated with which book). The measurement and course par info tables each have a ‘special’ column.  The measurement table contains a column called Type which takes a number from 1-4.  Since this never changes the application can handle the numbers, rather than creating an additional static look-up table in the database.  The number representation is as follows:

  1. Weight
  2. Neck
  3. Waist
  4. Hips

The course par information table contains a column called IsCNine to accommodate courses that have more than 18 holes with a format of: (Course Color)*( )(Front|Back).  Where there can be 0 or more colors   Examples of this would be:

  • White Front
  • Blue Front
  • Blue Back
  • Front
  • Back

This format allow for courses which provide 27 holes of golf as they typically have a white, blue, and red course, each with 9 wholes.  I’d imagine both would also have a front and back nine.  I have never played at a 27 whole golf course but I try to develop for future (or potential) possibilities.

I’ll explain the user profile dynamic content and dashboards text boxes in the diagram in my next post about the dynamic content and dashboards I plan to provide in my application.