Archive for October, 2012
Common Lookup Tables 1
My students wanted an example of how to use a lookup table in the database. I thought it would be a great idea to create a simple example like this one. You can read further on my second post about common lookup tables.
A lookup table is a generalization that holds lists of values that support end-user selections. The following example uses a combination of the common_lookup_table and common_lookup_column columns to identify sets of value for drop down lists. The end-user selects a value from the list to identify a unique row, and returns a common_lookup_id surrogate key value.
The sample code uses the table defined in the previous illustration. It uses a simple HTML drop down list, a PHP library.inc file, and an HTML display form. Below is the drop down selection set for a table and column value.
The drop down list code uses an insecure and trivial GET method to keep the example simple, as shown below:
<html>
<header>
<title>Select Option Sample</title>
<style type="text/css">
/* Class tag element styles. */
.label {min-width:200px;text-align:left;}
.title {font-weight:bold;font-style:italic;font-size:125%;}
</style>
</header>
<body>
<?php
// Include libraries.
include_once("library.inc");
// Declare input variables.
$table_name = (isset($_GET['table_name'])) ? $_GET['table_name'] : $table_name = "item";
$column_name = (isset($_GET['column_name'])) ? $_GET['column_name'] : $column_name = "item_type";
// Call function.
get_lookup($table_name, $column_name);
?>
</body>
</html> |
The library.inc file contains the logic to use a PHP prepared statement to read and render a SELECT HTML tag. It uses OPTION tags for all values in the drop down list. The values for the OPTION tag are the surrogate key values from the common_lookup_id column of the common_lookup table, and text elements are the descriptive values from the common_lookup_meaning column.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 | <?php /* || Program Name: library.inc */ function get_lookup($table_name, $column_name) { // Assign credentials to connection. $mysqli = new mysqli("localhost", "student", "student", "studentdb"); // Check for connection error and print message. if ($mysqli->connect_errno) { print $mysqli->connect_error."<br />"; print "Connection not established ...<br />"; } else { // Initial statement. $stmt = $mysqli->stmt_init(); // Declare a static query. $sql = "SELECT cl.common_lookup_id\n" . ", cl.common_lookup_meaning\n" . "FROM common_lookup cl\n" . "WHERE common_lookup_table = ?\n" . "AND common_lookup_column = ?\n" . "ORDER BY 2"; // Prepare statement. if ($stmt->prepare($sql)) { $stmt->bind_param("ss",$table_name,$column_name); } // Loop through a result set until completed. do { // Attempt query and exit with failure before processing. if (!$stmt->execute()) { // Print failure to resolve query message. print $mysqli->error."<br />"; print "Failed to resolve query ...<br />"; } else { // Fetch a row for processing. $result = $stmt->get_result(); // Print the opening select tag. print '<form method="post" name="myForm" action="submitItemType.php">'; print '<div class="title">Select unique from list:</div>'; print '<select name="item_type" size="1" onChange="change(this.form.item_type)">'; print "<option class=label value='' selected>Select </option>"; // Read through the rows of the array. while( $row = $result->fetch_array(MYSQLI_NUM) ) { print "<option class=label value='".$row[0]."'>".$row[1]."</option>"; } } } while($stmt->next_result()); // Print the closing HTML table tag. print '</select>'; print '<input name="submit" type="submit" value="Submit">'; print '</form>'; // Release connection resource. $mysqli->close(); } } ?> |
The display form action calls the submitItemType.php program, which displays the value from the OPTION tag selected in the prior form. The code for the display is:
<html> <head> </head> <body> <?php print "ITEM_TYPE -> [".$_POST['item_type']."]"; ?> </body> </html> |
It generates:
Hope this helps illustrate the value of and mechanics of lookup tables. As mentioned above, there’s a newer post on how to leverage common_lookup tables with more working code.
Why Stored Programs?
Why should you use stored programs? Great question, here’s my little insight into a situation that I heard about in a large organization.
A very large organization is having a technology argument. In someway, like politics, half-truth drives this type of discussion. This company has hundreds of databases and they’re about half SQL Server and Oracle. The argument (half-truth) states that using T-SQL or PL/SQL yields “spaghetti” code!
It seems like an old argument from my perspective. After all, I’ve been working with T-SQL and PL/SQL for a long time. Spaghetti code exists in every language when unskilled programmers solve problems but the point here is one of software architecture, and an attempt to malign stored programming in general. Let’s examine the merit of the argument against stored programs.
First of all, the argument against stored programs is simply not true. SQL DML statements, like the INSERT, UPDATE, and DELETE statements should maintain ACID compliant interactions with a single table in a database. Unfortunately, the same statements create anomalies (errors) in a poorly designed database.
Stored programs provide the ability to perform ACID compliant interactions across a series of tables in a database. They may also hide database design errors and protect the data from corruption. The same can’t be said for Java or C# developers. Java and C# developers frequently fail to see database design errors or they overlook them as inconsequential. This type of behavior results in corrupt data.
It typically raises cost, errors, and overall application complexity when key logic migrates outside the database. If you’re asking why, that’s great. Here are my thoughts on why:
- Making a Java or C# programmer responsible for managing the transaction scope across multiple tables in a database is not trivial. It requires a Java programmer that truly has mastered SQL. As a rule, it means a programmer writes many more lines of logic in their code because they don’t understand how to use SQL. It often eliminates joins from being performed in the database where they would considerably outperform external language operations.
- Identifying bottlenecks and poor usage of data becomes much more complex for DBAs because small queries that avoid joins don’t appear problematic inside the database. DBAs don’t look at the execution or scope of transactions running outside of the database and you generally are left with anecdotal customer complaints about the inefficiency of the application. Therefore, you have diminished accountability.
- Developing a library of stored procedures (and functions) ensures the integrity of transaction management. It also provides a series of published interfaces to developers writing the application logic. The published interface provides a modular interface, and lets developers focus on delivering quality applications without worrying about the database design. It lowers costs and increases quality by focusing developers on their strengths rather than trying to make them generalists. That having been said, it should never mask a poorly designed database!
- Service level agreements are critical metrics in any organization because they compel efficiency. If you mix the logic of the database and the application layer together, you can’t hold the development team responsible for the interface or batch processing metrics because they’ll always “blame” the database. Likewise, you can’t hold the database team responsible for performance when their metrics will only show trivial DML statement processing. Moreover, the DBA team will always show you that it’s not their fault because they’ve got metrics!
- Removing transaction controls from the database server generally means you increase the analysis and design costs. That’s because few developers have deep understanding of a non-database programming language and the database. Likewise, input from DBAs is marginalized because the solution that makes sense is disallowed by design fiat. Systems designed in this type of disparate way often evolve into extremely awkward application models.
Interestingly, the effective use of T-SQL or PL/SQL often identifies, isolates, and manages issues in poorly designed database models. That’s because they focus on the integrity of transactions across tables and leverage native database features. They also act like CSS files, effectively avoiding the use of inline style or embedded SQL and transaction control statements.
Let’s face this fact; any person who writes something like “spaghetti” code in the original context is poorly informed. They’re typically trying to sidestep blame for an existing bad application design or drive a change of platform without cost justification.
My take on this argument is two fold. Technologists in the organization may want to dump what they have and play with something else; or business and IT management may want to sidestep the wrath of angry users by blaming their failure on technology instead of how they didn’t design, manage, or deliver it.
Oh, wait … isn’t that last paragraph the reason for the existence of pre-package software? 😉 Don’t hesitate to chime in, after all it’s just my off-the-cuff opinion.
Open World 2012
In prior years a daily update from Open World was possible, but this year my schedule was too full to support it. This is my compendium of thoughts about MySQL Connect, JavaOne, and Open World 2012.
MySQL Connect was great – good sessions re-enforcing the positive investments Oracle is making in the product. I’ll leave to others to qualify changes in what elements of technology are opened or closed along the road to a better MySQL. The announcement of Connector/Python 1.0 GA on Saturday was great news and as a community we owe a lot to Greet Vanderkelen.
NoSQL is a hot topic along with using JSON objects and it was interesting hearing of some unequal testing paradigms to position non-Oracle solutions to be “better” than Oracle solutions. Naturally, the MongoDB was the elephant in the room during those conversations. Some of the discussions seemed more like political rants than technical dialog. A great spot to start with NoSQL and JSON would be downloading Oracle’s MySQL 5.6 Release Candidate.
There were also more PostgreSQL conversations this year and fairly accurate comparisons between it and Oracle or MySQL from folks. It certainly looks like it may gain more ground.
Java 7 is awesome, and my favorite feature is clearly NIO2, reinforced at JavaOne. NIO2 brings static methods to interactions with external directory and file sources. It removes directories from the files class, which is long overdue. The nature of those static methods also happen to fit within the definition of Java code that lives inside the Oracle database and gives me a whole host of thoughts about potential in Oracle Database 12c.
Larry Ellison’s keynote was impressive because it gives us a clear vision of Oracle’s direction and Duncan Davies captured the keynote well in his blog. The continued presence of Red Hat and VMWare offers interesting reality checks to their key contributions to world wide implementation of the Oracle technical stack.
Issues that seem most critical to those I’ve chatted with are storage, security, tools, and development languages. A nice update on security can be found in the new edition of Hacking Exposed 7: Network Security Secrets & Solutions
On the forthcoming Oracle 12c release, Information Week just released a good summary view. The introduction of the R programming language on the Exadata Server leads me to wonder about what uses may magically appears in Oracle Enterprise Manager down the road. The TIOBE Index for September 2012 doesn’t list the R language in the top 20 programming languages but there’s always the future. No mention of Erlang programming language at any of the conferences that I caught but it’s inevitably on the horizon as application servers evolve.
Now we wait for the Oracle Database 12c release, which looks like something in the very short term. Perhaps right after the holidays …
Subscribe via RSS