Category Archives: Web

Converting Your Corporate Intranet to Drupal

Though I have fun working on SynthNet and other projects at night, during the day I fill the role of mild-mannered network administrator at the Manchester-Boston Regional Airport (actually, the day job is quite a bit of fun as well). One of the ongoing projects I’ve taken on is adding all of our various Intranet-oriented services into a single platform for central management, easier use, and cost effectiveness. As mentioned in a previous article (linked to below, see NMS Integration), I knew Drupal was the right candidate for the job, simply due to the sheer number of modules available for a wide array of functionality, paired with constant patching and updates from the open source community.  We needed a versatile, sustainable solution that was completely customizable but wasn’t going to break the bank.

The Mission

The goal of our Drupal Intranet site was to provide the following functionality:

  1. PDF Document Management System
    1. Categorization, customized security, OCR
    2. Desktop integrated uploads
    3. Integration with asset management system
  2. Asset Management System
    1. Inventory database
    2. Barcode tracking
    3. Integration with our NMS (Zenoss)
    4. Integration with Document Management System (connect item with procurement documents such as invoices and purchase orders)
    5. Automated scanning/entry of values for computer-type assets (CPU/Memory/HD Size/MAC Address/etc)
    6. Physical network information (For network devices, switch and port device is connected to)
    7. For network switches, automated configuration backups
  3. Article Knowledgebase (categorization, customized security)
  4. Help Desk (ticketing, email integration, due dates, ownership, etc)
  5. Public Address System integration (Allow listening to PA System)
  6. Active Directory Integration (Users, groups, and security controlled from Windows AD)
  7. Other non-exciting generic databases (phone directories, etc)

Implementation

Amazingly enough, the core abilities of Drupal covered the vast majority of the required functionality out of the box.  By making use of custom content types with CCK fields, Taxonomy, Views, and Panels, the typical database functionality (entry, summary table listings, sorting, searching, filtering, etc) of the above items was reproduced easily.  However, specialized modules and custom coding was necessary for the following parts:

  1. Customized Security – Security was achieved for the most part via Taxonomy Access Control and Content Access.  TAC allowed us to control access to content based on user roles and categorization of said content (e.g. a user who was a member of the “executive staff” role would have access to documents with a specific taxonomy field set to “sensitive information”, whereas other users would not).  Additionally, Content Access allows you to further refine access down to the specific node level, so each document can have individual security assigned to it.
  2. OCR – This was the one of the few areas we chose to delve into a commercial product.  While there are some open source solutions out there, some of the commercial engines are still considerably more accurate, including the one we choose, ABBYY.  They make a Linux version of the software that can be driven via the shell.  With a little custom coding, we have the ABBYY software running on each PDF upload, turning it into an indexed PDF.  A preview of the document is shown in flash format by first creating a swf version (using pdf2swf), then using FlexPaper/SWF Tools.
  3. Linking Documents – This was performed with node references and the Node Reference Explorer module, allowing a user friendly popup dialogs to choose the content to link to.
  4. Desktop Integration – Instead of going through the full steps of creating a new node each time, choosing a file to upload, filling in fields, etc, we wanted the user to be able to right click a PDF file on their desktop, and select “Send To -> Document Archive” from Windows.  For this, we did end up doing a custom .NET application that established an HTTP connection to the Drupal site and POSTed the files to it.  Design of this application is an article in itself (maybe soon!).
  5. Barcoding – This was the last place we used a commercial product simply due to the close integration with our barcode printers (Zebra) – we wanted to stick with the ZebraDesigner product.  However, one of the options in the product is to accept the ID of the barcode from an outside source (text/xml/etc), so this was simply a matter of having Drupal put the appropriate ID of the current hardware item into a file and automating ZebraDesigner to open and print it.
  6. NMS (Zenoss) Integration – The article of how we accomplished this can be found here.
  7. Automated Switch Configuration Backups and Network Tracking – This just took a little custom coding and was not as difficult as it might seem.  Once all our network switches were entered into the asset management system and we had each IP address, during the Drupal cron hook, we had the module cURL the config via the web interface of the switch by feeding it a SHOW STARTUP-CONFIG command (e.g. http://IP/level/15/exec/-/show/startup-config/CR) – which was saved and attached to the node.  Additionally, we grabbed the MAC database off each switch (SHOW MAC-ADDRESS-TABLE), and parsed that, comparing the MAC addresses on each asset to each switch port, and recording the switch/port location into each asset.  We could now see where each device on the network was connected.  A more detailed description of the exact process used for this may also be a future article.
  8. Help Desk – While this could have been accomplished with a custom content type and views, we chose to make use of the Support Ticketing Module, as it had some added benefits (graphs, email integration, etc)
  9. Public Address System – Our PA system can generate ICECast streams of its audio.  We picked these up using the FFMp3 flash MP3 Live Stream Player.
  10. Automated Gathering of Hardware Info – For this, we made use of a free product called WinAudit loaded into the AD login scripts.  WinAudit will take a full accounting of pretty much everything on a computer (hardware, software, licenses, etc) and dump them to a csv/xml file.  We have all our AD machines taking audit during logins, then dumping these files to a central location for Drupal to update the asset database during the cronjob.
  11. Active Directory Integration – The first step was to ensure the apache server itself was a domain member, which we accomplished through the standard samba/winbind configurations.  We then setup the PAM Authentication module which allowed the Drupal login to make use of the PHP PAM package, which ultimately allows it to use standard Linux PAM authentication – which once integrated into AD, includes all AD accounts/groups.  A little custom coding was also done to ensure matching Drupal roles were created for each AD group a user was a part of – allowing us to control access with Drupal (see #1 above) via AD groups.

There was a liberal dose of code within a custom module to glue some of the pieces together in a clean fashion, but overall the system works really smoothly, even with heavy use.  And the best part is, it consists of mainly free software, which is awesome considering how much we would have paid had we gone completely commercial for everything.

Please feel free to shoot me any specific questions about functionality if you have them – there were a number of details I didn’t want to bog the article down with, but I’d be happy to share my experiences.

Hardware Monitoring: Syncing Drupal with Zenoss

Overview

One of the more daunting tasks of managing a larger network is keeping track of all your devices – both physically, and from a network monitoring perspective.  When I arrived on the job 3 years ago, the first major task I laid down for myself was implementing both an asset management system, as well as a network monitoring system, to ensure we always knew what we had, and if it was functioning properly.

I decided almost immediately that Drupal was the right candidate for the job of asset management.  There are a number of commercial IT/helpdesk systems out there which work great, but they are usually fairly expensive with recurring licensing costs, and my history with them has always been shaky.  Plus, I find myself not always using all the functionality I paid for.  I knew with my Drupal experience, I could get something comparable up in almost no time – this is not a discredit to IT packages, but moreso the power of the Drupal framework.

Network Monitoring – Cue Zenoss

Now that I had the hardware DB taken care of, I needed a NMS for monitoring.  Originally I was planning on Nagios, but a contractor who works for us (now friend) had introduced me to Zenoss, another open source alternative.  Zenoss is awesome – is absolutely has its quirks, and is not the most intuitive system to learn, but once things are up and running it’s great – and tremendously powerful.  So the choice was made.

Now – I had both pieces, but I absolutely hate entering data twice, and the interoperability guy in me loves integrating systems.  So I decided to write a script that would sync our Drupal database with Zenoss.  Drupal would serve as our master system, and any hardware we entered into it would automatically port over to Zenoss.  Any changes or deletions we made (IP address, location, name, etc) would sync over as well.

The below script performs this synchronization.  Some warnings up front – I’m not a Python guy by any means, I specifically learned it for this script, so I apologize for any slopping coding or obvious Python-y mistakes.  I’ve tried to thoroughly comment it to document how to use it and how it works.  Hopefully it can help some others out as well!

# Description: Sync devices to be monitored from Drupal to Zenoss
#
# Setup Work: Create a (or use an existing) content type that houses your hardware items to be monitored.
# They should have CCK fields for the IP address of the device, the name, and the type of
# device it is. The device type will determine the Zenoss class the script adds it to, and hence
# the kind of monitoring it will receive (e.g. Linux server, switch, ping only, etc)
#
# Additionally, in Zenoss, create a custom property field that will house the nid of the Drupal
# node. This serves as the foreign key and will be used to link the item in Drupal to its entry in Zenoss
#
# Usage: This script should be run from zendmd, and may be run once or periodically. We run it every 20 minutes from
# a cron job.
# It will create new entries in Zenoss for items not yet imported, delete ones that no longer exist in
# Drupal (it will only delete ones that were originally imported from Drupal), and will update ones that have
# been updated (type, IP, location, etc).
#
# Note: Excuse all the extra commits - we experienced some issues with data not being saved, and I threw some extra in
# there - they're almost definitely not necessaryimport MySQLdb

# Take a taxonomy term from Drupal identifying the type of monitoring to be done,
# and convert it to the appropriate Zenoss class path. Update these to whatever terms
# and Zenoss class paths that make sense for your environment. We setup ones for
# Linux and Windows servers, switches, waps, UPSes, PDUes, etc, as can be seen.
def getClassPath(passType):

if passType.lower() == "windows":
return "/Server/Windows"
elif passType.lower() == "linux":
return "/Server/Linux"
elif passType.lower() == "switch":
return "/Network/Switch"
elif passType.lower() == "mwap":
return "/Network/WAP/Managed"
elif passType.lower() == "uwap":
return "/Network/WAP/Unmanaged"
elif passType.lower() == "ups":
return "/Power/UPS"
elif passType.lower() == "pdu":
return "/Power/PDU"
elif passType.lower() == "camera":
return "/Camera"
elif passType.lower() == "cphone":
return "/Network/Telephone/Crash"
elif passType.lower() == "sphone":
return "/Network/Telephone/Standard"
elif passType.lower() == "printer":
return "/Printer"
elif passType.lower() == "converter":
return "/Network/Converter"
elif passType.lower() == "ping":
return "/Ping"
return "/Ping"

# Connect to Drupal's MySQL DB (Replace these values with the appropriate ones for your system)
imsConn = MySQLdb.connect(DRUPAL_MYSQL_SERVER, MYSQL_USER, MYSQL_PASSWORD, MYSQL_DB)
imsCursor = imsConn.cursor()

# Execute the query to grab all your items to be monitored. In our case, we have a node type called "hardware" that had CCK fields identifying the IP address,
# the type of hardware (a taxonomy term that dictated the Zenoss class of the item - see getClassPath above), a physical location, etc.
# You'll want to change the specific table/field names, but the inner join will probably stay, as you'll want to grab both the node and CCK fields that belong to it.
imsCursor.execute("""
SELECT node.nid, content_type_hardware.field_hardware_dns_value, content_type_hardware.field_hardware_location_value, content_type_hardware.field_hardware_ip_value, content_type_hardware.field_hardware_monitor_type_value, content_type_hardware.field_hardware_switchname_value, content_type_hardware.field_hardware_switchport_value
FROM node
INNER JOIN content_type_hardware ON node.nid = content_type_hardware.nid
""")

# Loop through all returned records - Check for additions, changes, and removals
while (1):
#tempRow is our current hardware item record
tempRow = imsCursor.fetchone()
if tempRow == None:
# No more entries, break out of loop
break
else:
# Search Zenoss records for the nid of the hardware item. A custom field will need to be created in Zenoss to serve
# as this foreign key. In our case, we used MHTIMSID - but you can use anything you'd like - just be sure to create the field in Zenoss.
found = False
for d in dmd.Devices.getSubDevices():
if d.cMHTIMSID != "":
if int(d.cMHTIMSID) == tempRow[0]:
found = True
break

if found == False:
# Hardware item not found, add it if it's monitored
if tempRow[4] != None:
dmd.DeviceLoader.loadDevice(("%s.yourdomain.com" % tempRow[1]).lower(), getClassPath(tempRow[4]),
"", "", # tag="", serialNumber="",
"", "", "", # zSnmpCommunity="", zSnmpPort=161, zSnmpVer=None,
"", 1000, "%s (%s - %s)" % (tempRow[2], tempRow[5], tempRow[6]), # rackSlot=0, productionState=1000, comments="",
"", "", # hwManufacturer="", hwProductName="" (neither or both),
"", "", # osManufacturer="", osProductName="" (neither or both).
"", "", "", #locationPath="",groupPaths=[],systemPaths=[],
"localhost", # performanceMonitor="localhost',
"none")
tempDevice = find(("%s.yourdomain.com" % tempRow[1]).lower())
tempDevice.setManageIp(tempRow[3])
commit()
# Save nid to Zenoss record (to serve as foreign key) for syncing
tempDevice._setProperty("cMHTIMSID","MHTIMS ID","string")
tempDevice.cMHTIMSID = tempRow[0];
commit()
else:
# Hardware item found - delete, update, or do nothing
if tempRow[4] == None:
# Delete if not set to monitor
dmd.Devices.removeDevices(d.id)
else:
# Update DNS and IP to current values
if d.getDeviceName() != ("%s.yourdomain.com" % tempRow[1]).lower():
d.renameDevice(("%s.yourdomain.com" % tempRow[1]).lower())
if d.getManageIp() != tempRow[3]:
d.setManageIp(tempRow[3])
commit()

# Change class if not set to "Manual" (We setup a taxonomy term called "Manual" that would turn off automatic Zenoss class selection during syncing
# and allow us to manually specificy the class of the device.
if tempRow[4] != "Manual":
d.changeDeviceClass(getClassPath(tempRow[4]))
commit()

# Update comments (location change)
d.comments = "%s (%s - %s)" % (tempRow[2], tempRow[5], tempRow[6])
commit()

# Save any missed changes
commit()

# Close connection to database
imsCursor.close()
imsConn.close()

Spotlight: Leah Creates

One of the best things about being involved in the world of technology, besides getting a front row seat to all the amazing advancements made every day, is meeting and talking with the creative people who make the magic happen. I think I’m especially lucky, having strong ties to a range of different areas such as networking and development, to have met a diverse mix of very talented people.

Web Developer Extraordinaire

To say business exists in a social media world where online presence and reputation is important would be the understatement of the century. Companies today live and die by their ability to harness the power of the web. And while there are many developers out there, a true burden lies in finding talented and experienced ones. Not only does Leah fall into this camp, combining expert design skill with seasoned web development knowledge, but she possesses something that many in the industry don’t – a real love and respect for what her customers are trying to accomplish with their website. This truly shines through both in her work, and how she treats her clients. It translates into a special website that speaks its goals and connects to its visitors like no other site could. It is the difference between a good looking site and a truly powerful site.

The Proof is in the Pudding

I’ve known Leah for a number of years, having had the privilege of working with her on a number of projects professionally – and her sites continue to really impress me. Some excellent examples of recent projects: Be Irreplaceable | Donna Heart.

I love these examples, as they show how she has taken a general framework like WordPress, and turned it into a beautiful site that really communicates the site’s message. They feel personable and comfortable when you visit them, unlike a lot of cold and bland sites out there. They have that truly personal touch which is key to connecting with the audience.

For even more examples of her work, check out her online portfolio.

So if you’re looking to build a new website for your business, or need to re-imagine the one you already have, I really suggest keeping Leah Creates in mind. She is amazing at both what she does, and how she does it – something setting her apart from so many other development houses out there.

LeahCreates

Creating a PHP Graphing Calculator

When I want to learn a new technology or technique, I like to think about little projects I’ve always wanted to try, and see if the project could be implemented using the new technology.

In this case, I’ve worked quite a bit with .NET’s image and graphic related classes and methods, but had never really investigated the PHP equivalent. After reading up on PHP’s GD library, I decided to give writing a simple graphing calculator a go.

This tutorial will get you started and allow your users to enter in equations to generate graphs like this:

Graph 1

Graph 2

It will also let users use standard PHP functions and code, giving them use of the full math library, modulus, random functions, etc. You can do some really neat things like this:

Graph 3

So let’s get started. In this example, the webpage has a single text box that takes an equation, submits back to the page, and the PHP writes an image directly to the browser. Please note, this example doesn’t do any error checking or sanitation of input – please include it in your final code, especially since this page makes use of the “eval” function which is a huge security risk.


< ? php
if ($_POST['tempBox'] != '')
{
   // width, height, and scale of graph.  These could also be user defined values.
   $GLOBALS['width'] = 500;
   $GLOBALS['height'] = 500;
   $GLOBALS['scale'] = 1;


   // drawGrid draws X and Y axis with markers every 50 points
   // We pass in the image resource to draw to
   function drawGrid($passGraphic)
   {
      // Grid is grey
      $gridColor = imagecolorallocate($passGraphic, 200, 200, 200);

      // Draw two lines, one vertical in the middle of the grid (width/2)
      // and another horizontal in the middle of the grid (height/2)
      imageline($passGraphic, $GLOBALS['width']/2, 0, $GLOBALS['width']/2, $GLOBALS['height'], $gridColor);
      imageline($passGraphic, 0, $GLOBALS['height']/2, $GLOBALS['width'], $GLOBALS['height']/2, $gridColor);
      
      // Draw a marker ever 50 points on the X axis.  We do this by starting at the center
      // of the board, then incrementing 50 with every iteration.  We also
      // draw a mark in the mirror location to cover the negative side of the axis
      for ($lcv = 0 ; $lcv < $GLOBALS['width'] / 2 / $GLOBALS['scale']; $lcv += 50)
      {
           // Set tempX and tempY to the current marker location
           $tempX = $lcv;
           $tempY = 0;
  
           // Convert to image coordinates
           cartToPixels($tempX, $tempY);

           // Draw the line
           imageline($passGraphic, $tempX, $tempY - 10, $tempX, $tempY + 10, $gridColor); 

           // Now do the same for the negative side of the axis
           $tempX = $lcv * -1;
           $tempY = 0;
           cartToPixels($tempX, $tempY);
           imageline($passGraphic, $tempX, $tempY - 10, $tempX, $tempY + 10, $gridColor); 
      } 

      // We use the same method for drawing markers on the Y axis.
      for ($lcv = 0 ; $lcv < $GLOBALS['height'] / 2 / $GLOBALS['scale'] ; $lcv += 50)
      {
           $tempX = 0;
           $tempY = $lcv;
           cartToPixels($tempX, $tempY);
           imageline($passGraphic, $tempX - 10, $tempY, $tempX + 10, $tempY, $gridColor); 
           $tempX = 0;
           $tempY = $lcv * -1;
           cartToPixels($tempX, $tempY);
           imageline($passGraphic, $tempX - 10, $tempY, $tempX + 10, $tempY, $gridColor);
      } 


   }

   // cartToPixels converts Cartesian coordinates to image coordinates, using the SCALE variable to
   // do a zoom conversion between the two.  
   // The two gotchas are, coordinates start from the center, and the Y axis goes in the
   // opposite direction (image coordinates, increase in Y goes down.  Cartesian, increase
   // in Y goes up).
   function cartToPixels(&$passX, &$passY)
   {
      // Start from the middle, otherwise 1 to 1 for X
      $passX = $GLOBALS['width'] / 2 + $passX * $GLOBALS['scale'];

      // Start from the middle, also subtract to flip for Y
      $passY = $GLOBALS['height'] / 2 - $passY * $GLOBALS['scale'];

   }

   // pixelsToCart converts from image coordinates to
   // Cartesian coordinates.  Uses the same process as
   // above but reversed.
   function pixelsToCart(&$passX, &$passY)
   {
      $passX = ($passX - $GLOBALS['width'] / 2) / $GLOBALS['scale'];
      $passY = ($passY + $GLOBALS['height'] / 2) / $GLOBALS['scale'];
   }

   // The plot function simply takes Cartesian coordinates and 
   // plots a dot on the screen
   function plot($passGraphic, $passCartX, $passCartY)
   {
      // We use green for our graphs
      $plotColor = imagecolorallocate($passGraphic, 0, 255, 0);

      // Convert Cartesian coordinates to image coordinates
      cartToPixels($passCartX, $passCartY);

      // Then draw a dot there
      imagesetpixel($passGraphic, $passCartX, $passCartY, $plotColor);
   }

   // Push out an image/png mime type to the browser so it knows
   // a PNG image is coming and not HTML
   header ("Content-type: image/png");

   // Create a new image resource with the dimensions dictated
   // by our width and height globals.  Starts off with a black
   // background automatically.
   $im = @imagecreatetruecolor($GLOBALS['width'], $GLOBALS['height'])
      or die("Cannot Initialize new GD image stream");
  
   // Draw the grid on our graph first (under everything) 
   drawGrid($im);

   // We start a loop from 0 (First pixel in width of graph) to the width end,
   // converting image X coordinate to Cartesian X coordinate, then retrieving
   // the corresponding Y Cartesian coordinate (remember, our equations are
   // in 'y =' form.  We multiply it by 50 to give a better resolution - e.g. multiple dots on
   // the Y axis for each X.  This gives us a solid line instead of a bunch of dots for steep
   // functions.  The higher the number, the smoother the line (and the slower the program)
   for ($lcv = 0 ; $lcv < $GLOBALS['width'] * 50 / $GLOBALS['scale'] ; $lcv++)
   {
      // Get the left most point on the graph in Cartesian coordinates
      $tempX = 0;
      $tempY = 0;
      pixelsToCart($tempX, $tempY);
    
      // Now get the current Cartesian X coordinate by adding our current loop 
      // value divided by 50
      $tempX += $lcv/50;
 
      // Here's where the magic happens.  In a nutshell, we're setting the Y coordinate
      // ($tempY) in relation to the current X coordinate ($tempX)
      // by using the expression specified by the user.  We use PHP's eval function,
      // which allows us to take a string (the user's input box) and run it as if it
      // were PHP code.  We're also converting X to $tempX because that's the actual
      // name of our X variable in our code, not just
      // 'X' - but we don't want the user to have to type '$tempX', so we make it easy for
      // them.  This is the line of code to be especially careful of, as a user could insert 
      // malicious PHP code and do bad things.  
      @eval("\$tempY = " . str_replace('X', '$tempX', $_POST['tempBox']) . ";");

       // Now that we have both coordinates, we plot it!
      plot($im, $tempX, $tempY); 
   }

   // We write the equation on the graph in red
   $textColor = imagecolorallocate($im, 255, 0, 0);

   // Now write the equation on the graph
   imagestring($im, 4, 10, $GLOBALS['height'] - 25, 'y = ' . $_POST['tempBox'], $textColor);

   // Output a PNG file now that it's all built
   imagepng($im);

   // Free up the resource
   imagedestroy($im);
}
else
{
? >

<html>
   <body>
      
      <div style="margin-bottom:20px; font-weight:bold; font-size:16px">Simple Grapher</div>

      <form id="graphForm" action="?< ? php echo rand(); ? >" method="post">
         Enter expression: y = <input type="input" id="tempBox" name="tempBox"> <input type="submit" value="Graph!">
      </form>

      <div style="margin-bottom:15px">Enter expression using variable X (PHP code accepted).  Both axes in range of -250 to 250.</div>
      <div>Examples:
         <ul>
            <li><a href="javascript:populate('cos(X / 30) * 50')">cos(X / 30) * 50</a></li>
            <li><a href="javascript:populate('pow(X / 30, 3) - X')">pow(X / 30, 3) - X</a></li>
            <li><a href="javascript:populate('pow(X, 2) / 15 - 200')">pow(X, 2) / 15 - 200</a></li>
            <li><a href="javascript:populate('2000 / X')">2000 / X</a></li>
            <li><a href="javascript:populate('tan(X/ 25) * 20')">tan(X/ 25) * 20</a></li>
            <li><a href="javascript:populate('-3 * X + 50')">-3 * X + 50</a></li>
            <li><a href="javascript:populate('rand() % X')">rand() % X</a></li>
            <li><a href="javascript:populate('pow(X,2) % X - X')">pow(X,2) % X - X</a></li>
            <li><a href="javascript:populate('pow(X,2) % X - cos(X/20) * 40')">pow(X,2) % X - cos(X/20) * 40 (favorite)</a></li>
         </ul>
      </div>
   </body>
</html>

< script type="text/javascript">

   function populate(passExpression)
   {
      document.getElementById('tempBox').value = passExpression;
      document.getElementById('graphForm').submit();
   }

< /script>

< ? php
}
? >

That’s it – a few things to note: We tack on a random value to the URL (check out the form code in the HTML section) to ensure our browser doesn’t show a cached copy of the image. Since its always using the same filename, the browser would almost certainly show old copies unless a refresh was requested or cache was cleared (depending on the browser).

As you might have seen from the code, generating images is very easy in PHP. We can create a new image resource using the imagecreatetruecolor function. This creates a blank resource, we can also create from preexisting images. Once we have an image resource, we can perform basic graphic functions on it like lines, dots, fill, text, etc. Once we’re done, the imagepng function can convert it into actual PNG binary code (in our case we dump it to the browser, but you can specify a filename too to save it to).

It’s definitely fun to play around with the graphic library, you can do a lot of neat things and add some needed graphics to normally text-only server side output. Have fun!

Rotate, Flip, and Resize Images in .NET

Often times if you’re writing a web application that deals with user uploaded photographs and other images, a nice feature set to have is one that allows users to do basic manipulations on their photos, such as rotating, flipping, and resizing (the last being very important for download friendly images). Luckily, the .NET framework makes such functionality easy to implement.

Read the Image

Once the image has been uploaded and saved into a directory somewhere accessible by the web server, the first step common to all the procedures is to take the image data inside of the file and get it into memory usable by .NET. We do this by loading the image into a System.Drawing.Bitmap object. One of the constructors of Bitmap class can take the filename of an image, thereby automatically decoding the format and reading in the data into the Bitmap object. .NET supports the most common image formats, such as JPEG, GIF, BMP, and PNG.

        Dim PhotoBMP As Bitmap

        PhotoBMP = New Bitmap(“myfile.jpg”)

Above we have statically specified a filename, but you may, of course, dynamically pass in the filename saved on the webserver.

Flips and Rotations

Reorienting the image is incredibly easy. The Bitmap class contains a method called “RotateFlip” that does what you would think – rotates and/or flips. The RotateFlip method takes a single argument, one of RotateFlipType type. Intellisense will enumerate all the options, but each combination of rotation and flip is included: 90, 180, 270 degrees, and flips across X, Y or both axes.

        PhotoBMP.RotateFlip(RotateFlipType.Rotate270FlipNone)

Above we rotate our image clockwise 270 degrees (i.e. counter-clockwise 90 degrees). We do not perform a flip.

        PhotoBMP.RotateFlip(RotateFlipType.RotateNoneFlipY)

Above we flip our image across the Y axis but do not perform a rotation. You may also combine both flips and rotations simultaneously if desired.

Saving (and Converting) the Image

At this point, our reorientation has only been performed upon the bitmap data we have in memory, our file is still untouched. We can save our changes back to the original file, or to a new file if desired. The Bitmap class contains an easy to use Save method.

        PhotoBMP.Save(“myfile.jpg”)

Save is overloaded in a number of ways, but you may also specify a filetype as the second argument. In our example, if we wanted to convert our jpg to a png:

        PhotoBMP.Save(“myfile.png”,ImageFormat.Png)

Very easy as can be seen, no need to lookup file formats and encodings. You may also want to read in the current extension to direct what actions are taken. I use the following regular expression to parse the ending extension of the file.

        Regex.Match(FileName, ".[^.]+$")

Resizing Images

Resizing images is a little more difficult, but not too bad once you know the procedure. You start by loading bitmap data in the same fashion we did for rotating and flipping. Assuming you want to keep the image in proportion when changing the size, as well as resize with a maximum resolution in mind, we’ll need to calculate the aspect ratio of the image and our conversion coefficient. This may sound complicated, but fear not.

Calculate Aspect Ratio

There are a few methods of doing the math, but in general we know the maximum dimension we want. E.g. 1024×768, 800×600, 640×480, etc. We calculate the aspect ratio of our maximum desired size by dividing the width by the height. In these examples it is 1.333.

Then we must calculate the aspect ratio of the current image. We can obtain the width and height of our bitmap with the width and height properties of the Bitmap class. We know if we have a greater aspect ratio than our target max size’s aspect ratio, then our width is larger and is the confining dimension. If our aspect ratio is less, our height is larger and is the confining dimension. Once we know our confining dimension, to get the conversion coefficient, we divide our current image confining dimension size by the target confining dimension size.

E.g. If we’re converting 2048×1800 to a maximum of 1024×768, we calculate our target image aspect ratio, which we already know is 1.333. Then we calculate our original image aspect ratio, which is 1.138. This is less than 1.333, so we know height is our confining dimension. So we divide 1800 by 768 to get our conversion coefficient, or 2.344. Now we can calculate our target width size by dividing our current width by 2.344, which is 874. Our new image will by 874×768.

Confused? It’s not really as bad as it sounds. First you’re figuring out which dimension will match the largest dimension of your target size, then you’re decreasing the other dimension by the same amount. Here it is in code.

        OrigAspectRatio = PhotoBMP.Width / PhotoBMP.Height

        TargetAspectRatio = 1024 / 768

        If OrigAspectRatio < TargetAspectRatio
   	     ‘Height is confining
	     NewHeight = 768
	     NewWidth = PhotoBMP.Width / (PhotoBMP.Height / 768)
        Else
	     ‘Width is confining
	     NewWidth = 1024
  	     NewHeight = PhotoBMP.Height / (PhotoBMP.Width / 1024)
        End If

Perform the Resize

Now we have our target width and height via good old fashioned math, now the technical bits for .NET to perform its magic. .NET can perform resizing via the Graphics class, by resizing data from the original bitmap data into a new, smaller bitmap. First we make a new, blank Bitmap of the correct new size.

        NewBMP = New Bitmap(newWidth, newHeight)

Then we create a new Graphics image using the reference of this new Bitmap object.

        ConvertGraphics = Graphics.FromImage(NewBMP)

Then we specify the method of scaling the image. Different methods work better for different amounts of scaling, but High Quality Bicubic is the one I tend to use.

        ConvertGraphics.InterpolationMode = Drawing2D.InterpolationMode.HighQualityBicubic

Now comes the actual conversion, we use the DrawImage method to draw our original bitmap into the new bitmap at the new size (at the starting coordinates of 0,0 in the new bitmap

        ConvertGraphics.DrawImage(PhotoBMP, 0, 0, NewBMP.Width, NewBMP.Height)

Dispose our old bitmap

        PhotoBMP.Dispose()

Save our new

        NewBMP.Save(“new.jpg”, ImageFormat.Jpeg)

And dispose

        NewBMP.Dispose()

And we’re done! You can also use the Graphics object to perform a number of paint-oriented operations on your image, such as lines and drawing text, but that you can have fun experimenting with.

With the exception of resizing which requires a few calculations, .NET makes image manipulation exteremly easy, and when combined into a web application, offers users some useful tools at virtually no cost to you.

Dynamically Truncate SQL Parameters in .NET

[SQL Server] String or Binary data would be truncated.

Run into this error before? The cause is usually fairly clear, we’re attempting to insert or update a field in a table with more data than it can hold. E.g. we’ve executed a SQL insert statement that attempts to assign a string 100 characters in length to a varchar field that can hold 80 characters max. Microsoft SQL Server complains, and we receive a nice error in our .NET application.

Solution 1: Don’t send too much data

This is an easy solution (and not the purpose of the article) and not always possible, but it is completely valid and often times desirable if you are writing an application that works with a SQL database in a static manner where the fields you’re updating are known ahead of time. There are a number of ways to ensure you don’t exceed the maximum size of a field.

If your data is coming from a form item, such as a text box, ensure the maximum size of the text box doesn’t exceed the maximum size of the field in the database. If you’re working with items that cannot be limited, simply take a substring of the data and manually truncate it to a size allowed by the field in the database. Many times we know what data and what sizes we’re working with at design time.

Solution 2: Read field sizes from database and dynamically truncate data

There are many times when solution 1 is not an option, mostly in situations where the database we’re working with is unknown, whether partially or fully, at design time. I’ve worked on a number of ETL tools and interoperability oriented applications where I can’t know the field size in the database at design time, it must be discovered at runtime.

One of the great parts about ADO.NET is the ease in which it deals with parameters. At the core of executing SQL statements is the SqlCommand (or OleDbCommand, or odbcCommand) class. After obtaining an instance associated with a database connection, we assign our SQL statement to it and all our parameters and values to it. In our case, the size of the parameter values we assign to it may exceed the maximum size of our receiving database fields. We need a method of iterating through each parameter and ensuring the value assigned isn’t too big.

Reading table schema

The heart of this routine is detecting which table you’re dealing with, then reading the schema of that table, then discovering the max size of each field.

I start by declaring a sub procedure that takes a SqlCommand as argument by reference.

        Sub TruncateParameters(ByRef passCommand As SqlCommand)

I then declared the following variables

        Dim targetTable As String 
        Dim LCV As Integer
        Dim tableCommand As SqlCommand
        Dim tableReader As SqlDataReader
        Dim tempRow As DataRow

targetTable is the name of the table we’ll be checking. I read it from the actual SQL statement in the SqlCommand – but I was dealing with fairly simple SQL statements. You may want to manually pass the table names in if you’re dealing with SQL statements that will be difficult to parse.

tableCommand, tableReader, and tempRow will be used to read in the schema of our targetTable.

Next I parse the SQL statement inside of passCommand to obtain the targetTable name, but again, you may want to manually pass the name of the table ahead of time if you’re dealing with difficult to parse SQL statements. Also, if you are familiar with regular expressions, you can pare this routine down quite a bit – I present it here completely algorithmically for people who aren’t familiar with regular expressions.

       If passCommand.CommandText.ToLower().IndexOf("update") > -1 Then
             'UPDATE command
             targetTable = passCommand.CommandText.Substring(passCommand.CommandText.ToLower().IndexOf("update") + 7)
       Else
             'INSERT command
             targetTable = passCommand.CommandText.Substring(passCommand.CommandText.ToLower().IndexOf("insert") + 12)
       End If

       targetTable = targetTable.Substring(0, targetTable.IndexOf(" "))

Next we read (up to) 1 row from our target table to obtain the schema.

        ' Open Data Reader for schema table
        tableCommand = New SqlCommand("SELECT TOP 1 * FROM " & targetTable, passCommand.Connection)
        tableReader = tableCommand.ExecuteReader()

This is where the magic happens – we iterate through each parameter in our passCommand, examine the field matching the parameter, get its max length, then truncate the value assigned to each respective parameter to the max size of the field. This routine assumes that you’ve used the same parameter names as field names. If you haven’t, you would need to do further parsing of the SQL statement to deduce a parameter name -> field name mapping. This routine is also targeted for strings, but would work just as well for binary data assuming you had a method of truncating such data. Also, there are a lot of shortcuts for doing case insensitive compares, my style is to convert to lower case and compare as I can never remember what is case sensitive and what is not. Feel free to use your own style.

        ' Loop through each parameter

        For LCV = 0 To passCommand.Parameters.Count - 1
            For Each tempRow In tableReader.GetSchemaTable().Rows
                If "@" & tempRow("ColumnName").ToString().ToLower() = passCommand.Parameters.Item(LCV).ParameterName.ToLower() Then
                    If tempRow("DataType").ToString().ToLower() = "system.string" Then
                        If passCommand.Parameters.Item(LCV).Value.ToString().Length > tempRow("ColumnSize") Then
                            passCommand.Parameters.Item(LCV).Value = passCommand.Parameters.Item(LCV).Value.ToString().Substring(0, tempRow("ColumnSize"))
                        End If
                        Exit For
                    End If
                End If
            Next
        Next

Finally, we close out our connection and end the sub procedure.

        tableReader.Close()

End Sub

There is some work involved, but the idea of the above routine can be customized to fit your situation. Once you have a solid parameter truncating routine in your arsenal, you can call it for any SqlCommand object, and ensure you never get a “String or Binary data would be truncated.” error message again!