DJS Consulting Tech Blog
Technical Information You Can Use (or not - but hey, at least it’s free!)

Oracle SQL Developer is a Java-based tool that provides a graphical interface to a database. While it’s main focus is Oracle (of course), it can be hooked up, via JDBC, to many other databases, such as MySQL, PostgreSQL, and SQL Server. It’s similar to Toad, but is provided by Oracle at no cost.

Oracle provides SQL Developer in either an RPM, or a generic binary install. I like the ability to manage packages, but I’ve never had much luck at getting RPM to run on Ubuntu. I downloaded the RPM file, and, using alien, I converted the package to a .deb package (Debian package format) and installed it. I worked like a charm!

I haven’t tested it with gcj, but using Sun’s Java 6 update 7 from the Ubuntu repositories, it ran just fine. After you install the package, do a directory list on /usr/lib/jvm. You’re looking for the Sun JDK - if it’s installed, you’ll have a symlink java-6-sun that points to java-6-sun-1.6.0.07. Once you’ve determined the location of the JDK, run “sqldeveloper” from the command line - the program will prompt you for the path to your JDK. Enter it (probably “/usr/lib/jvm/java-6-sun”) and you’re good to go. (You have to install the package as root - but, for the rest of these steps, use your normal user, not root, as this puts settings in a .sqldeveloper directory off your home directory.) The package installs an icon in the “Programming” or “Development” group. Once you’ve told it where the JDK is, you can use this to launch it.

Download SQL Developer 1.5.1 Debian Package

Version 2.1 of Daniel’s DropDowns has been released. This fixes a problem introduced with the 2.5-series of WordPress - the output of the WordPress tag changed, so the search-and-replace portion that added a “Select Category” entry didn’t work. This has been fixed in version 2.1. I also corrected a small bug that caused the first entry in the category list to be selected if a default wasn’t specified.

It can be downloaded from the WordPress Plug-In Directory. Enjoy!

I found a script over on the Lunarpages Forums about using PHP to back up your site. I have taken it, modified it a little, beefed up the documentation a lot, and am now posting it here. You can download the source code for it, and it is also displayed below.

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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102

<?php
/**
 * Generic Backup Script.
 * 
 * To configure this script for your purposes, just edit the parameters below.
 * Once you have the parameters set properly, when the script executes, it will
 * create an archive file, gzip it, and e-mail it to the address specified.  It
 * can be executed through cron with the command
 * 
 * php -q [name of script]
 * 
 * You are free to use this, modify it, copy it, etc.  However, neither DJS
 * Consulting nor Daniel J. Summers assume any responsibility for good or bad
 * things that happen when modifications of this script are run.
 * 
 * @author Daniel J. Summers <daniel@djs-consulting.com>
 */
 
// --- SCRIPT PARAMETERS ---
 
/*  -- File Name --
	This is the name of the file that you're backing up, and should contain no
	slashes.  For example, if you're backing up a database, this might look
	something like...
$sFilename = "backup-my_database_name-" . date("Y-m-d") . ".sql"; */
$sFilename = "backup-[whatever-it-is]-" . date("Y-m-d") . ".[extension]";
 
/*  -- E-mail Address --
	This is the e-mail address to which the message will be sent. */
$sEmailAddress = "[your e-mail address]";
 
/*  -- E-mail Subject --
	This is the subject that will be on the e-mail you receive. */
$sEmailSubject = "[something meaningful]";
 
/*  -- E-mail Message --
	This is the text of the message that will be sent. */
$sMessage = "Compressed database backup file $sFilename.gz attached.";
 
/*  -- Backup Command --
	This is the command that does the work.
 
	A note on the database commands - your setup likely requires a password
	for these commands, and they each allow you to pass a password on the
	command line.  However, this is very insecure, as anyone who runs "ps" can
	see your password!  For MySQL, you can create a ~/.my.cnf file - it is
	detailed at http://dev.mysql.com/doc/refman/4.1/en/password-security.html .
	For PostgreSQL, the file is ~/.pgpass, and it is detailed at
	http://www.postgresql.org/docs/8.0/interactive/libpq-pgpass.html .  Both of
	these files should be chmod-ded to 600, so that they can only be viewed by
	you, the creator.
 
	That being said, some common commands are...
 
- Backing Up a MySQL Database
$sBackupCommand = "mysqldump -u [user_name] [db_name] > $sFilename";
 
- Backing Up a PostgreSQL Database
$sBackupCommand = "pg_dump [db_name] -h localhost -U [user_name] -d -O > $sFilename";
 
- Backing Up a set of files (tar and gzip)
$sBackupCommand = "tar cvf $sFilename [directory]/*";
 
Whatever command you use, this script appends .gz to the filename after the command is executed.  */
$sBackupCommand = "[a backup command]";
 
// --- END OF SCRIPT PARAMETERS ---
//
// Edit below at your own risk.  :)
 
// Do the backup.
$sResult = passthru($sBackupCommand . "; gzip $sFilename");
$sFilename .= ".gz";
 
// Create the message.
$sMessage = "Compressed database backup file $sFilename attached.";
$sMimeBoundary = "<<<:" . md5(time());
$sData = chunk_split(base64_encode(implode("", file($sFilename))));
 
$sHeaders = "From: $sEmailAddress\r\n"
		. "MIME-Version: 1.0\r\n"
		. "Content-type: multipart/mixed;\r\n"
		. " boundary=\"$sMimeBoundary\"\r\n";
 
$sContent = "This is a multi-part message in MIME format.\r\n\r\n"
		. "--$sMimeBoundary\r\n"
		. "Content-Type: text/plain; charset=\"iso-8859-1\"\r\n"
		. "Content-Transfer-Encoding: 7bit\r\n\r\n"
		. $sMessage."\r\n"
		. "--$sMimeBoundary\r\n"
		. "Content-Disposition: attachment;\r\n"
		. "Content-Type: Application/Octet-Stream; name=\"$sFilename\"\r\n"
		. "Content-Transfer-Encoding: base64\r\n\r\n"
		. $sData."\r\n"
		. "--$sMimeBoundary\r\n";
 
// Send the message.
mail($sEmailAddress, $sEmailSubject, $sContent, $sHeaders);
 
// Delete the file - we don't need it any more.
unlink($sFilename);
?>

While working on the Not So Extreme Makeover: Community Edition site, I came up with an algorithm that simplifies anything else I’ve ever written to deal with this condition. I’ll set the scenario, explain the algorithm, share how I implemented it in PHP, and provide a modification if the scenario is a bit more complicated.

Scenario - You have two parent tables, and a child table with a many-to-one relationship with both parent tables, used to map entries in the two parent tables to each other. For this example, we’ll use these three tables…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

CREATE TABLE volunteer (
    vol_id  integer  NOT NULL,
    vol_last_name  varchar(50)  NOT NULL,
    ...etc...
  PRIMARY KEY (vol_id)
);
 
CREATE TABLE r_volunteer_area (
    rva_id  integer  NOT NULL,
    rva_description  varchar(255)  NOT NULL,
  PRIMARY KEY (rva_id)
);
 
CREATE TABLE volunteer_area (
    va_volunteer_id  integer  NOT NULL,
    va_area_id  integer  NOT NULL,
  PRIMARY KEY (va_volunteer_id, va_area_id),
  FOREIGN KEY (va_volunteer_id) REFERENCES volunteer (vol_id),
  FOREIGN KEY (va_area_id) REFERENCES r_volunteer_area (rva_id)
);

Algorithm - The three-step algorithm is as follows…

1. Create a comma-delimited string of IDs for the child table.
2. Delete the IDs from the child table that are not in the list.
3. Insert the IDs into the child table that are not there already.

Implementation - In PHP, if you have an array, it’s easy to come up with comma-delimited list. To get an array of values back in a post, define your fields with “[]” after the name…

1
2
3
4

<input type="checkbox" name="area[]" id="chkArea1" value="1" />
<label for="chkArea1">Do Something</label><br />
<input type="checkbox" name="area[]" id="chkArea7" value="7" />
<label for="chkArea7">Do Something Else</label>

Here’s the PHP code, using PHP Data Objects (PDO) as the database interface, behind a helper class that creates the statement, appends the parameters, and executes it. (The “quoting” escapes the statement to avoid potential SQL injection attacks - putting it in its own class would make the implementation here much cleaner.)

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

/**
 * STEP 1
 *    Create a comma-delimited list of IDs.
 */
 
// Quote will return the string as '2,3,4' - since we're using this
// as an IN clause of integers, we'll strip the quotes off.
$sAreas = $pdo->quote(join(",", $_POST["area"]));
$sAreas = substr($sAreas, 1, strlen($sAreas) - 1);
 
// Quote the volunteer ID.
$iVol = $pdo->quote($_POST["vol"], PDO::PARAM_INT);
 
/**
 * STEP 2
 *    Delete the IDs that are no longer in the list.
 */
$dbService->executeCommand(
    "DELETE FROM volunteer_area
    WHERE   va_volunteer_id = ?
        AND va_area_id NOT IN ($sAreas)",
    array($iVol);
 
/**
 * STEP 3
 *    Insert the IDs that are not yet in the list.
 */
$dbService->executeCommand(
    "INSERT INTO volunteer_area
        SELECT $iVol, rva_id
        FROM r_volunteer_area
        WHERE   rva_id IN ($sAreas)
            AND rva_id NOT IN
            (SELECT va_area_id
            FROM volunteer_area
            WHERE va_volunteer_id = ?)",
    array($iVol));

Modification - Suppose that now you accepted comments along with each of the checkboxes, so a simple two-integer insert/delete is no longer sufficient. You would still only need to break step 3 into two steps.

1. Get a list of IDs to update.
2. For each ID in the posted list
   1. If the ID exists in the update list, update it.
   2. Otherwise, insert it.

The implementation would then be able to use this list to make the decision without hitting the database every time.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

// Assume this returns an associative array of IDs.
$aUpdates = $dbService->performSelect(
    "SELECT va_area_id
    FROM volunteer_area
    WHERE   va_volunteer_id = ?
        AND va_area_id IN ($sAreas)",
    array($iVol));
 
foreach($_POST["area"] as $iArea) {
    if (in_array($iArea, $aUpdates)) {
        // Update the table
        ...etc...
    }
    else {
        // Insert into the table
        ...etc...
    }
}

I think you’ll agree that this is much better than spinning through a loop, doing a count on each ID to see if it exists, then either doing an update or an insert based on the count. And, while the implementation here is PHP, it could easily be implemented in any language that supports arrays and database access.

I have released version 2 of HCSB Verse of the Day, the WordPress plug-in that provides a verse or passage each day, using the reference provided by BibleGateway.com. I also completed the required files for the WordPress Plug-In Directory, so it can be downloaded from there.

New in this version..

• New Tag - There is now a tag votd_hcsb() that puts out the heading, the text, the reference, and the credit line all in one. This will simplify the template modification required to implement the plug-in.
• Custom Tag - There is a separate file where you can specify a separate group of tags, and the votd_hcsb() tag will utilize it instead of its default. This also means that, even if future versions change the default, the custom tag layout will be used.
• Two Versions - WordPress (and most plug-ins) must be compatible with PHP version 4. However, if your web server is running PHP version 5, there is now a PHP 5 version included. It incorporates the object-oriented enhancements in PHP 5.
• Options Revamped - Since I initially wrote the plug-in, I’ve learned that WordPress allows an option to be an array. So, to streamline its usage, the options are now an array, and only require one row in the database instead of five. There is also a file to clean up the old options.
• Bug Fixes - BibleGateway.com changed the way they display multiple passages (ex. “Matthew 1:13, 17-19″); version 2 has a fix that makes that work again.

As always, if you encounter any problems with the plug-in, just let me know and I’ll try to help. Enjoy!

I have released version 2.0.1 of Daniel’s DropDowns, the WordPress plug-in that provides category and archive dropdown template tags. It is available on its page at the WordPress Plug-In Directory.

This was a minor change - the auto-navigating lists added in version 2 needed a slight tweak. There wasn’t a “select category” entry, so one could not navigate to the first item in the list without going somewhere else first. The category dropdown now has this entry, and is consistent with the archive dropdown.

Enjoy!

I have released version 2 of my category and archive drop-down plug-in for WordPress. It is hosted at the WordPress Plug-In Directory - you can go get it there. Following are a few of the changes that were made.

• Both tags now have only 2 parameters - the type of navigation and the text for the link or button. For navigation type, ‘button’ remains the default, and ‘link’ is still available. However, a third option of ‘auto’ has been added, which will render the list as an auto-navigating select box.
• The CSS parameters for the select and button elements were dropped. How these elements can be specified using CSS is detail in the top-of-plugin comments, along with an example.
• The usage examples in the comments now have an example of how to put the tag in a template in such a way that, if the plug-in is disabled, the template will still render. This could be done with 1.0, but I didn’t give an example.
• I added PHP Documentor-style comments to both functions.

Let me know if you have any problems with it, or any ideas for other behavior. Enjoy!

WordPress 2.3 introduced tags, but unless you’re using the default theme, your theme (like mine) probably didn’t support them. Nowhere did I find a good example of how to add tags to your theme. Then, I was playing around with the theme switcher on my personal blog, and discovered that the default theme had tag support. I looked at it, and it was amazingly simple.

There is a new template tag called …drumroll… the_tags. It takes three parameters: how to begin the list, how to separate each tag, and how to end the list. The tag does not do any output if a post hasn’t been tagged, so it can safely sit in your theme until you need it to be active.

Here’s how I did it in my personal blog. (Tags will appear on this blog shortly.)

1

<?php the_tags('<div class="tags">Tags &raquo; ', ' &bull; ', '</div>'); ?>

Of course, you could also do it using an unordered list…

1

<?php the_tags('Tags<ul class="tags"><li>', '</li><li>', '</li></ul>'); ?>

Drop some styling for the “tags” class in your theme’s CSS, and you’re good to go!

(Well, not quite. You’ll want to make sure to make the same change in your main index template, single post template, and archive template, so that the tags appear no matter how the user got to the post.)

I’ve created a plug-in that I’m now using on my personal site to provide the category and archive drop-down lists. It’s called “Daniel’s DropDowns”, and it will create drop-downs with either a link or a button to go to the selection in the drop-down, and a CSS class can be specified for the button and the drop-down box.

daniels_dropdowns.txt - Daniel’s DropDowns 1.0

To install it, download the file, rename it “daniels_dropdowns.php”, and upload it to your /wp-content/plugins directory. Then, enable it, and add the template tags to your theme. (The documentation at the top of the file lists all the template tags provided, and the options that can be passed to them.)

I have used a hack in the theme of my personal site for a while to obtain the “Verse of the Day” in the Holman Christian Standard Bible (HCSB) version. With things that I’ve learned about plug-ins, I decided to take a stab at creating a plug-in to do this work. That way, if I changed themes, I wouldn’t have to hack the new one they way I’ve hacked the current one.

The fruits of my labor is the new HCSB Verse of the Day plug-in for WordPress. It uses the reference from BibleGateway.com, then gets the text from their regular website. (They do not provide the service, citing copyright restrictions.) How to use the plug-in, along with my justification on copyright grounds, is in the top of the file’s comments. If they’re not clear, certainly post comments here and I’ll address them. (I do plan to submit the plug-in to WordPress.org - but, they require a specific README file format that it will take me a bit to put together.)

votd_hcsb.txt — HCSB Verse of the Day 1.0

To install it, download the file, rename it “votd_hcsb.php”, and upload it to your /wp-content/plugins directory. Then, enable it, and add the template tags to your theme - that’s it!