Fortysomething Geek

A departure from my typical gadget, general computing posts. Today, I'm going to discuss database auditing trail. Or, if you are searching from the web, "How to keep track of database column value changes."

So what is an Audit Trail (also commonly called an Audit Log)? It is basically a record keeping of changes in a database. For example, if a user updates a record, an audit log should record who made the changes and what the changes were. For some industries, this is an absolute requirement. There are many different ways to implement it and in this blog, I am going to share a technique I use for web applications.

Here is an example of why you need to have an audit trail or a history table of your database changes. I developed a B2B web app that sends materials to various retail stores. Often, the address changes and shipments are often lost. Usually, the client like to blame the developers for the missed shipments which could be very costly. However, with an an audit trail, the developer can proved that the wrong address was the result of bad input on behalf of the client.

A common method to audit changes in a database is to create a trigger that monitors INSERTS, UPDATE, DELETES. Here is an good example fro this website,simple-talk.

Unfortunately, most web applications usually only have a single user login for the app itself. Users are often stored in a user table. If your users are managed by a web front end with a user table, triggers won't work for this type of scenario.

Another common mistake is to make a duplicate history table which mimics the table you want to log. If you have multiple tables, you end making multiple history tables. To me, this is bad design.

My method is to make a singular history table of all the changes. Yes, there is overhead as there are often two transactions. One for the actual UPDATE/INSERT and one for the actual logging. However, I think the overhead is well worth it in most circumstances. This can be done as a trigger or a concurrent action for your application.

Here is an example schema of an audit trail log table in MySQL. It is a single table that can account for any database table you plan to use. It is an "Amorphous" table; meaning it is consolidated and links by reference to whatever you need to log. In my example,the Audit Log is a table called "AuditLog" for this discussion.



Let me explain some of the entity columns.
ParentID is the key the record you are logging.
ParentObject is the table you are referencing.
CreatedById is the user ID of the user and CreatedDate should be obvious to when the log was created.
FieldName refers to the table column.
DataType refers to the TYPE of the column: VARCHAR, DECIMAL, INT, etc.
OldValue refers to the original data and NewValue is the new data.
If your data is a larger, you can use OldText/NewText for large changes like TEXT.
Notes are internal notes you can flag such as deletes, inserts,etc.

Here is an example of a record that was updated. The id of that original record was 4420 and we know it is the Stores table that was updated based on the ParentObject.
The Field was the Address. As you can see, the old address was 15426 Emerald.. and it was updated by userid 52. You can simply do a JOIN to get more info on the user.


This works well for my needs. To restore a record, you can do an UPDATE with a JOIN.

UPDATE $ParentObject s SET s.`$FieldName` = A.OldValue JOIN AuditLog A ON s.Id = A.ParentId WHERE A.id = 179;

id	ParentObject	FieldName	ParentId	CreateDate	CreatedById	OldValue	NewValue
1241	user	name	124	2005年03月01日	1	J Smith	Jon Smith
1654	user	name	124	2009年08月25日	2	Jon Smith	Jonah Smith
2547	user	name	124	2010年01月10日	1	Jonah Smith	Jonathan Smith
3645	user	name	124	2010年07月21日	4	Jonathan Smith	Jonathan Smith Sr.

So if you are using something like PHP and MySQL, you can log database changes and restore updates easily. This is a simple way to keep track of database changes.

iWorks  for iCloud beta is the latest entrance into the online office productivity from Apple. In beta form, it is Apple's interpretation of the word processor, spreadsheet and presentation app. And it is simply amazing in terms of web technologies. I have not been so impressed with web design since the first use of invisible GIFs and sliced tables from 1996 (that is how old I am).

In their first try, Apple  pretty much out-classed both Google Docs and Microsoft's Office 365 in the online productivity space. Apple's first try at this is impressive indeed.

If you ever used iWorks (Pages, Numbers, and Keynote) , you will be in for a surprise because the web versions are well built.

Straight up, this is amazing piece of web development - WebApps that don't act like web apps. They act and perform like desktop applications. Drag-n-drop, rotation, with UI that looks like a native desktop application. When you move elements around a page, you experience instantaneous page formatting and styling.

Here is an example. Pages on the Left. Google Docs on the right. Pages is like a layout application with the ability to layer. The User Interface is thoroughly thought out.

Read more »

Want to run a web app server on your smartphone or tablet? Specifically, run a full fledge PHP-MySQL Web Server straight from your Android device.

Well, there are a few pre-built apps on the Google App Store. In the past, there were a few lightweight HTTP servers running customize apps like AirDroid or PAW. These new ones have full PHP and MySQL run-times.

I tried a few and settled with Bit Web Server. I also tried KSWEB.

Bit WebServer also comes with PHPMyAdmin (PMA) installed and you are ready to go.
You can install Drupal and Wordpress as well. I got Code Igniter running without any problems.





A few notes. These apps tend to be a bit buggy. They can force-quit or hang under load. I can't really blame them as the fault may be with Android OS itself or the hardware I am running. I've tried them with the Galaxy Nexus and a Samsung Galaxy Tab 2. I've been reading that the OS (Jelly Bean and ICS) may starve the apps due to power savings and other reasons such as auto close.

I can't really complain though. I'm not running anything mission critical. If the app crashes, I simply restart it. It comes in handy show off web apps.

Moreover, it runs LIGHTTPD instead of Apache so PHP run as a CGI.

The apps range from 2ドル to 3ドル. Yeah, you're paying for open source software and may have an issue with it. However, think about the time you are saving without having to resort to building PHP/MySQL from scratch and make it work on your phone. I have no problem with the pricing.

Just add a keyboard and you are ready to go!





Links:

Bit Web Server
KSWEB.

I am often asked if the iPad can be used for real work. The iPad has been called a "recreational" tablet and it lacks a real productivity suite according to many detractors. Well, I don't use Microsoft Office in my day-to-day work activities so the appeal of a Surface RT like device does not appeal to me.
However, I do use an iPad because of its great connectivity features: LTE 4G, the ability to access Cisco IPSEC VPN, great remote access apps in the form of SSH, SFTP, X11, RDP, and VNC.
I use it in my data center to diagnose database servers, apache web servers, write code, fix problems in our network infrastructure. It has become part of my work tools.

Is an iPad a replacement for a laptop? It depends on what you use it for. I can attest to the great ability to quickly login remotely and fix something be it configuring a firewall, updating a MySQL database remotely, or executing a bash script. In short, it is definitely a tool in my arsenal I use for work. And quite frankly, I don't think there are much compromises.

Today, I am going to share some of those apps I use. I am going to concentrate on some great web development apps.


I am going to concentrate on these apps:
iSSH, Gusto, Textastic, MySQL Mobile Database Client, and iMockups


There are many other great apps but I only want to cover 5 for brevity. In fact, one can choose other mySQL clients, mockup and diagramming apps but I wanted to show apps that take advantage of the iPad's real estate. I've seen many other apps on other platforms that are simply "blown-up" phone apps and that is not the way to go on a tablet.

iSSH

If there is only one app I have to rely on, it would be iSSH. I have used it for many years and it has saved my butt on many occasions. It is an awesome SSH client (and can even be used as an X11 client). I've been called up in the middle of the night to reboot servers or restart dead daemon services. In short, it simply works. You can even use SSH keys. But the killer feature is the transparent keyboard and floating multi-gesture pop-up programmable cursors for use in the shell.

Like I said, if there was only one app to choose, this would be it. All I need is console access with VIM and nano and I'm set. I cannot stress how important the multi-gesture buttons are. When you are typing on a screen soft keyboard, you can quickly tab or arrow-up in an instant. It helps when you need to type really quickly in the console. For example, typing in service stop, I can quickly arrow up to repeat the command and service start. That is intuitive for someone who uses the shell and is acclimated to using arrow buttons on a keyboard.




Text Editors/IDE: Gusto & Textastic

Next, there are two text editors you should look at for the iPad. Gusto and Textastic. I originally used Textastic because it was the first text editor to support SFTP. This is important because all my servers are *NIX based (Linux/FreeBSD). Both have their strengths and weaknesses. Gusto now supports SFTP which gives it feature parity.

If you ever used Sublime 2 or themed your text editor in the Guber/Solarize themes, you will love both apps. Both can color code to make it extremely pleasant to read and edit code.

Both apps give you a full screen view and a file explorer panel view. Textastic is easier to swap views whereas the switch in Gusto is hidden in a modal dialog setting.

Here are screenshots of Gusto.



Here are Texstatic screenshots:






With Firebug and a web inspector, you can analyze console javascript errors and evaluate DOM objects.

With Texstatic, you can pretty much use the iPad as a standalone device to develop HTML5/Javascript apps.
You only need access to a remote server in the case of developing Ruby/Python or PHP web apps. And the connectivity options will ensure you can connect easily to any Linux or VPS *NIX servers.
With an external keyboard, this makes the iPad an incredibly powerful and productive device.

Both apps with have their fans. Both are good at updating remote code when you need to.

For uber cross-platform geeks, both apps are easily accessible by Linux distros. This is pretty important when compared to other devices on the market that have problems in Linux due to MTP. The iPad was pretty much plug-n-play on Ubuntu and Linux Mint. Dropping, copying, and synching files between Ubuntu and iOS 6 is a sweet.




The only thing that would both these two competing apps complete would be to include a version control mechanism. It would be awesome to have GIT capabilities in either apps.

MySQL Clients

There are countless number of good MySQL clients on the iTunes app store. I can't list them all so you need to test and try the one you like best.

MySQL Mobile Database Client is a fairly decent one that works pretty well. I often use just iSSH and access MySQL in the terminal but I understand people like GUI clients.

Once again, this apps shows the strength of iOS in terms of tablets specific apps. This app intuitively takes advantage of the screen instead of just blowing up a single column smartphone view/layout I've seen on Android.

You can quickly select tables, view individual rows and edit them. You can also manually make queries to your heart content. I've used this apps several times when clients call me in the middle of lunch to manually over-ride or update a record.




Mockup Apps; iMockups

Lastly, there are many apps to help you visualize and markup ideas. There are dozen of great apps to allow you to flow-chart, diagram ideas. I often use TouchDraw (it was cheap) and then there some apps that go beyond flow-charting and making UML diagrams. One of those apps is iMockups.

iMockups won't make you a fancy flow-chart but it will let you quickly prototype your ideas in an interactive presentation for client approvals. This app can be used to visualize web and iOS mobile apps.

You simply drag elements of a web view and define their interaction. You can use this quickly visual a HTML form or database recordset results. Or you can design a navigation toolbar and it will actually be interactive and go to the page/slide you want to show next. I've seen many web producers use tools like Powerpoint and Keynote to do this but it ends up taking longer. With this apps, you simply drag the page elements and is actually very productive.




All these apps cost money. That is one of the major difference against other ecosystem. I am certain you can find some free equivalents. Great quality apps tend to cost money and I am more than willing to pay if it helps me. I can pretty much say many of these apps have already paid themselves off in emergencies. The ability to enjoy my lunch at a restaurant without rushing back to the office to make a SQL update is priceless. The ability to add a host deny rule using iSSH on my servers in the middle of a D-O-S (denial of service) attack is another priceless example.

There you have it. Some great apps for your recreational tablet to do some work. I will write some more articles like this for other use cases. So the next time someone says the iPad can't be use for work, these apps can prove them wrong. These apps also show the strengths of the iPad vis-a-vis their competitors. Tablet specific and tablet design apps make a big difference.

If you do a search on the Microsoft Metro store for the Surface RT as of this writing, good luck finding a SFTP/SSH client.

Once again, the links to these apps:
iSSH, Gusto, Textastic, MySQL Mobile Database Client, and iMockups

One of the things I do is mentor junior and young web developers. One of the most asked question is how to debug AJAX, XMLHttpRequests, also known as XHR.

Well, it is very easy. In most modern web browsers, there are web developer tools. There are native tools built in IE, Safari and Chrome. They are often called Web/Developer Inspectors. With Firefox, you can use Firebug.

Each browsers are different in how you access the XHR console. I won't go into details but it is very easy to find. Launch your web inspector or right-click and "inspect element" before you make your AJAX call. When the web developer tool (called by various names and differing across versions of the same browsers). Look for resources or Network. Then narrow down for scripts or XHR (short for XMLHttpRequest) options.



Below are screenshots of Chromium under Ubuntu 10.10 and Safari under Mac OSX.




Whenever I make an AJAX call, I look for the script/resource I am calling. In my screenshot above, I am both calling a script called ajax/_ajax_sample.php.

Here is the HTML I use to call my AJAX. It is a simple AJAX post, passing some variables in a form-like POST.

 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> 
 <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> 
 <head> 
 <script src="//ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js" type="text/javascript"</script> 
 <script type="text/javascript"> 
 $(document).ready(function() { 
 $.ajax({ 
 type: "POST", url: "ajax/_ajax_sample.php", data: { action: 'update file', user_name : 'joe blow', user_id: 100 }, 
 dataType: "json", 
 success: function(data){ 
 if(data.status == 'success'){ 
 if (data.post_action == 'show_div') { $("#results_div").html(data.msg); } 
 } else { 
 alert( "There was an error: " + data.msg); 
 } 
 } // end of data.status = success 
 }); // end of ajax 
 }); 
 </script> 
 <html><body> 
 Results: 
 <div id ="results_div" style="display:block;width:400px;background-color:#CCCCCC">Nothing Yet</div> 
 </body></html> 

If you go back and look at the two browser screen grabs, I am passing the following POST data. This is indicated in the "Header" tabs. The Header's tab is what you post to your resource. You will see other behind-the-scene info such as Request Headers, the method, and the Response Headers (how the server informs the browser it will process).

For our purposes, they only thing I am looking for is the "Form Data" of what I posted. I just need to know if what I posted was correct. They are:

 action:update file 
 user_name:joe blow 
 user_id:100 

My script or resource will takes the Form Data Post  from my AJAX call in the same manner you would be sending data through a HTML form or query string.

This is how you check you are posting the right data or not.

When your CGI resource or script gets the request, it will eventually return some data. The web inspector will also you give a read-out between the time it gets the request to the time it finishes. You can see how efficient your back-end code is.  See below for example of latency and processing time.



The results  are often HTML data or JSON. To see what it returns, simply click on the "Content" tab. There may be other tabs such as preview or JSON to show the same results.

As you can see in the next screenshot, my result was in JSON. If you go back and look at the same HTML file posted, I take the JSON and use it to execute another javascript function based on what I got back. Namely, filling the data into a DIV called results_div. The status return a 'success'.

 {"status":"success","msg":"your ajax worked mr\/mrs joe blow","post_action":"show_div","record_id":40} 

Read more »

In my day job, I tend to keep up to date with relevant technologies and one of the latest web buzzwords today is noSQL. CouchDB, Cassandra, mongoDB, and many other noSQL alternatives have been gaining popularity with the young folks. Like usual, it is my job to keep abreast. I've been keeping myself entertained with mostly mongodb because I think it is one of the easiest and quickest to learn.

If you are looking to explore mongoDB, there are some great GUI tools to get you started. Installing mongoDB is pretty trivial so I won't cover it here.



On OSX, one of the best program I've come across is MongoHub. It is a very pretty and intuitive application. Within a few minutes, I was able to get some tangible progress in evaluating mongoDB. It has the ability to import MYSQL table schema into a mongoDB collection. I imported a working mySQL db I have been working on and I was able to quickly make JSON queries just from glancing at the mongodb reference.

Instead of the normal “SELECT db_column FROM table WHERE db_col = value AND db_col2 =value2” , you use BSON like this:

db.COLLECTION.find({'key': 'value'})

Since we are not using the console, there is no need to invoke the mongodb find command, it is all gui driven. For my imported DB collection, I simply typed in my query expression like: {'City' : /^Con/i }

Instead of playing with some “Hello World” tutorials, I already had a working set of data for evaluation within 5 minutes of installation. There was no need for test records or test data, I had real working data that I was already comfortable with. Another 20 minutes, I was able to write some PHP scripts to query and display records.

MongoHub can be found here:
http://mongohub.todayclose.com/


I haven't found anything in Linux comparable to MongoHub but these two solutions worked for me: PHPMoAdmin and JMongoBrowser.

Once you have the PHP mongo driver installed, you can run a web-based admin script. It works and you can create collections and schemas rather quickly. It is PHP based and there was no configuration or mucking around.

PhpMoAdmin:
http://www.phpmoadmin.com/




The other GUI is JmongoBrowser. It is cross platform and Java based. Again, it works but there is nothing to write home about.

JmongoBrowser:
http://edgytech.com/jmongobrowser/



Both phpMoAdmin and JmongoBrowser installed in Ubuntu 12.04 without issues. They also run on MacOSX.

By now, you have probably seen websites with "AJAX" style upload. Technically many of them are not really AJAX. XMLHttpRequest (AJAX) upload is not technically possible due to security limitations of Javascript. However, we still call them AJAX uploaders because they act and feel like it to the end-user; meaning the web pages are posting files without refreshing.

So where are the "AJAX uploaders?"


Many of them are actually SWF (Flash based) uploaders billed as "jquery file upload plugins." I'm sure many of them work great but I prefer to avoid Flash as much as possible.

There are also HTML5 support in some new browsers for asynchronous file uploads via AJAX post but I've had problems with some browsers like Safari and problems with different file types.

Today, I will show you how to simulate an AJAX upload without the use of Flash. If you have done some googling, the most common way to do it is to use a hidden iframe. This is considered a hack but it works. There are some tutorials out there but mine will show you how to get a callback from your upload script using Jquery. You can use pure Javascript but Jquery is very convenient.
A callback will be a JSON reply that the host page (the one doing the upload) can retrieve and act upon. For example, if the upload failed, you can notify the user. Or you can pass the record ID of the file after it was stored in a database.

First, you need to add a hidden iframe (mine is called upload_hidden) to your upload page.






Then you need to set the form to post to the hidden frame.

 function setTarget() { 
 document.getElementById('upload_form').onsubmit=function() { document.getElementById('upload_form').target = 'upload_hidden';} 
 } 

Then make sure you call it onload.

 window.onload=setTarget; 

Now for the pseudo callback. The trick is to check every time the iframe loads new content and parse the results. The way I do it is to embed my JSON reply in a div from the upload script.

After processing my upload, my PHP code generates the JSON wrapped in a DIV.

 <?php 
 $finished = array ('status'=>'success','time'=>date('Y-m-d H:i:s',time()),'db_insert_id'=>$record_id, ); 
 echo "<div id ='upload_status'>"; 
 echo json_encode($finished); 
 echo "</div>"; ?> 

And here is my JQuery code to parse the JSON from the PHP loaded into the hidden div.

 $(document).ready(function() { 
 $('#upload_hidden').load(function() { 
 var a = $("#upload_hidden").contents().find("#upload_status").html(); 
 if (a !=null) { 
 var obj = jQuery.parseJSON(a); 
 if (obj.status == 'success') { 
 alert ("file to saved to db as " + obj.db_insert_id); 
 } // #end success 
 } // #end a!=null 
 }); // #end upload_hidden load 
 }); 

The key thing to note are:

a = $("#upload_hidden").contents().find("#upload_status").html();

obj = jQuery.parseJSON(a)

Every time the iframe is loaded, I look inside the iframe for anything inside a div called "upload_status." When the iframe is initially loaded with a blank placeholder, nothing happens because the content is empty. However, when it detects anything, I parse whatever is inside the div as my JSON string.


After uploading a file, here are the results. Obviously, you would need to hide the iframe after you do some testing.

To wrap up. This is one way to handle callbacks in an pseudo-AJAX file upload. There are probably other ways to do it but this was something quick and works for my needs.
Here is the example code.

 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
 <html xmlns="http://www.w3.org/1999/xhtml"> 
 <head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> 
 <title>Example</title> 
 <script type="text/javascript" src="http://code.jquery.com/jquery-latest.js"></script> 
 <script type="text/javascript"> 
 function setTarget() { 
 document.getElementById('upload_form').onsubmit=function() { document.getElementById('upload_form').target = 'upload_hidden';} 
 } 
 $(document).ready(function() { 
 $('#upload_hidden').load(function() { 
 var a = $("#upload_hidden").contents().find("#upload_status").html(); 
 if (a !=null) { 
 var obj = jQuery.parseJSON(a); 
 if (obj.status == 'success') { 
 alert ("file to saved to db as " + obj.db_insert_id); 
 } // #end success 
 } // #end a!=null 
 }); // #end upload_hidden load 
 }); 
 window.onload=setTarget; 
 </script> 
 </head> 
 <body> 
 <iframe id="upload_hidden" name="upload_hidden" src="blank.html" style="display:none;"></iframe> 
 <form enctype="multipart/form-data" method ="POST" action ="upload_json.php" id ="upload_form"> 
 <input type="file" name="upload_file"> 
 <button name="Upload" type="submit" value ="Upload">Upload</button> 
 </form> 
 </body> 
 </html>