08.06.10

Keeping the OpenSUSE messages log tidy

Posted in Uncategorized at 8:20 am by BrianS

Our linux server, an OpenSUSE 11 box, uses syslog-ng to capture log messages. By default syslog-ng is set up to report statistics every hour. Our servers, however, aren’t intended to be used as logging servers so the message handling statistics aren’t very useful. Plus, the statistics messages end up flooding the system log, making it difficult to parse the log for more relevant messages. To rectify the situation I disabled the stats messages by editing /etc/syslog-ng/syslog-ng.conf so that the global options read:

options { long_hostnames(off); sync(0); perm(0640); stats(0); };

The relevant option is stats(). The numerical value is based on seconds and so the default is 3600 (or once an hour). By setting the option to 0 no statistical message should be logged.

07.23.10

AbleCommerce Tax Zone Modification

Posted in Web Development at 12:58 pm by BrianS

One of the features of AbleCommerce is the ability to charge taxes based on address. The feature is nice, but has one major flaw … the tax rate has to managed manually. We actually have only a few states where we are required to charge taxes, but one of those is California which is horribly convoluted. The problem boils down to, essentially, the need to track the tax rate for each zip code individually. You can’t even really use blocks of zip codes because a locality’s zip codes may not be consecutive.

AbleCommerce uses the concept of zones for assigning tax rates. For our purposes a zone can be applied based on state or a combination of state and a list of zip codes. Because of California’s tax rules and AbleCommerce’s tax rule management functionality we have to create a separate zone for each tax rate. The zip code list is just a comma-separated string of zip codes. Maintaining the information for the >1000 zip codes is difficult to do in this format.

I created a simple PHP-based page that keeps track of each zip code and its associated tax rate. While not perfect, this goes a long way towards simplifying maintenance of this information. A link to this page can be located in the AbleCommerce administrative section under Configure->Regions->Zones.

AbleCommerce has one other issue that has to be addressed. The default AbleCommerce installation limits a zone’s zip code filter to 255 characters (or about 40 zip codes) in the database and on the web form. Due to the situation with California taxes we have some zones that have significantly more than 40 zip codes and would require the creation of over 70 zones. To get around this problem I removed the web form limit and modified the database field ac_shipZones@PostalCodeFilter to be varchar(8000).

07.12.10

wordpress 3.0 upgrade

Posted in Uncategorized at 8:53 am by dpeery

The Climate Literacy community site has been upgraded to wordpress 3, as has the techlog (plus available plugin updates).

03.05.10

Rewriting URLs on IIS6

Posted in System Administration, Web Development at 6:27 pm by BrianS

One of the nice things about CakePHP is that it attempts to make a site more friendly to the average web site visitor by creating easier-to-remember URLs. There are a few techniques that CakePHP uses, but for the sake of this conversation we’ll focus on one way in particular: apache’s mod_rewrite functionality. Using mod_rewrite, URLs that would normally include the controller file (index.php) and a querystring can be rewritten as a simple file path.

Microsoft doesn’t include mod_rewrite-style functionality in IIS by default, though it has created an extension for IIS7. Since we are currently using IIS6 the extension isn’t an option for us. Fortunately there are a few other options available, including an open-source project called Ionics Isapi Rewrite Filter (IIRF). IIRF is an ISAPI filter that is very similar to mod_rewrite in terms of functionality. After some testing I’ve found this filter works almost perfectly for enabling CakePHP’s friendly URLs.

Getting Started

Installation is painless and requires only a few steps. The following is based on the IIRF 2.0.1.15 release which does not include an installer:

  1. Extract the files from the IIRF archive to a folder on the server.
  2. Open the properties of the IIS root “Web Sites” folder or the specific site that needs IIRF.
  3. In the “ISAPI Filters” tab create a new entry for IIRF; name the new filter (e.g. “IIRF”); specify the IIRF DLL located with the extracted IIRF files at \bin\IIRF.dll.
  4. Ensure that the IIS user has read/execute access to the IIRF DLL and read/write access to any directories that will be used for logging.
  5. Restart IIRF.

IIRF should now be installed, but I’ve found that sometimes the filter won’t appear to be active in the “ISAPI Filters” tab until it has been used the first time. To enable IIRF all you have to do is create a file called IIRF.ini and place it in the web site root folder or in the root of a virtual directory. This file contains any local configuration directives (such as log directory) and URL rewriting rules.

The obligatory gotcha

I mentioned before that the filter works almost perfectly. The only situation where I’ve had problems up to now is if the URL contains one or more space characters. The IIRF log indicates that a URL with a space is being parsed correctly and returning a valid URL. And yet the web server reports a 404 error.

I’ve only done testing when the final URL references a physical file, but based on a conversation in the support forum I suspect this problem also affects parameters in the querystring. I haven’t yet had a chance to fully investigate the issue, so I don’t have a work-around yet when the issue affects physical files. There does, however, appear to be a work-around for spaces in the querystring.

For now I have decided to ensure that any URLs parsed by IIRF that will point to a physical file/directory does not contain spaces.

References

CakePHP usage:

Space-in-URL problems:

02.26.10

Moving from Microsoft Access to MySQL

Posted in IERI, MySQL, Web Development at 12:58 pm by BrianS

We recently decided to make a public release of an old web-based application coded in ASP (classic, using VBScript) and using Microsoft Access. In order to make this application public we need to make a few modifications, not the least of which is moving from Microsoft Access to MySQL. Using Microsoft Access on the back-end would significantly hamper the ability of the application to support concurrent users, among other issues.

The majority of the coding modifications have yet to be made, but the database switch has already occurred. In the process of moving from MS Access to MySQL I discovered a few settings that would be helpful should this action need to be performed for other applications. These settings should enable similar applications to be moved with minimal modification to the programming.

First, let’s review some settings related to the MySQL ODBC driver. The settings are relevant to all versions of the driver, but the name of the setting may be different on different versions (I’m using 5.1.6). Here are the options which should be selected:

  • Return matched rows instead of affected rows
  • Treat BIGINT columns as INT columns
  • Enable safe options

The following information relates more generally to changes that may have to be made in the code:

  • MySQL doesn’t really support server-side cursors so the ODBC drivers fakes it. This is, mostly, fine except that some properties of the Recordset object are not available (namely RecordCount). In order to get full cursor support you should change the location from the server to the client (adUseClient; 3).
  • ASP doesn’t understand non-signed integers. This causes problems when performing operations using these values unless you manually type the value in your script, e.g. scriptvar = CInt(objrs(“dbcol”)). The other solution is to make all integers signed. Otherwise you will see the error: Variable uses an Automation type not supported in VBScript.
  • Finally, check your SQL statements for any VBA function calls. These will either have to be modified into MySQL-compatible function calls or removed from the SQL code altogether.

There are a number of issues that may be encountered when attempting to convert an ASP-based application from MS Access to MySQL. The issues addressed here are only those relevant to this particular application. Other applications may require additional or different solutions and settings.

Resources:

05.08.09

Subversion and Third-Party Code

Posted in System Administration, Web Development at 5:28 pm by BrianS

Often in the course of developing a project it is desirable to use code from a  third party. The main benefit, of course, is being able to add functionality without developing that functionality from scratch. Some third-party projects we have used in the past include FCKEditor (now CKEditor), the Yahoo! User Interface library, and, more recently, the CakePHP framework.

When our code is maintained in a subversion repository there are two options for including third-party code: externals and vendor branches. Read the rest of this entry »

04.17.09

CakePHP and CURRENT_TIMESTAMP

Posted in CakePHP, MySQL, PHP, Web Development at 3:15 pm by BrianS

As of cakePHP 1.2.1.8004 you can’t use CURRENT_TIMESTAMP as the default for a column. With MySQL when the default is set to CURRENT_TIMESTAMP the current date+time is inserted for a new row that doesn’t specifically define the value of the column.

From what I can tell, when CakePHP specifies the values for all columns when it creates a new record. A column with a defined default that is not specifically set by the user is manually set by CakePHP (rather than let MySQL handle defaults upon record insertion). But CakePHP doesn’t understand the CURRENT_TIMESTAMP keyword and so treats it as a string and wraps it in quotes. This breaks the resulting INSERT statement and you receive an error:

Incorrect datetime value: ‘CURRENT_TIMESTAMP’

Interestingly, columns that are named “created” and “modified” receive special handling by CakePHP. These columns are treated like auto-update columns by CakePHP and it sets them as expected. With the special handling of these columns in mind it is possible to get around the CURRENT_TIMESTAMP bug by following the recommended settings for created/modified columns, i.e. specifying the field as DATETIME with a default of NULL. CakePHP will automatically update the columns when inserting/updating records.

References:

Authentication & Authorization with Scaffolding

Posted in CakePHP, Web Development at 1:45 pm by BrianS

Though scaffolding is not recommended for production sites, I’ve found it quite handy when just getting started. Unfortunately, it doesn’t appear that the authentication/authorization (auth^2) mechanism works with scaffolding. You can, however, get auth^2 working manually with just a few lines of code.

First, follow the steps of the Simple Acl controlled Application tutorial from the CakePHP cookbook up to the section on logging in.

Next, we need to insert the code that updates the ARO when a user is added or edited. Normally you would place this code in your add/edit action in the users controller, but for scaffolded actions we’ll use another callback function. Add the following code to your users controller:

function _afterScaffoldSave($action) {
  $aro =& $this->Acl->Aro;
  $user = $aro->findByForeignKeyAndModel($this->data['User']['id'], 'User');
  $group = $aro->findByForeignKeyAndModel($this->data['User']['group_id'], 'Group');
  $aro->id = $user['Aro']['id'];
  $aro->save(array('parent_id' => $group['Aro']['id']));
  return TRUE;
}

Note: for scaffold callbacks you must return TRUE; or the scaffold will not finish building the page.

The above code has been modified from the original in the tutorial, which included a conditional that checked for a change in the user’s group. As far as I can tell scaffolding causes CakePHP to return only the updated record (even when using the _beforeScaffold method) so you’re unable to compare the old and new values. As a result you have to update the ARO with every update, even if the user’s group is not updated.

Finally, for scaffolded actions we need a way to determine if the user is authorized. The AuthComponent has all the functionality we need. Add the following function to any controller using scaffolding that needs auth^2:

function _beforeScaffold($action) {
  if ($this->Auth->user() == NULL && !in_array('*', $this->Auth->allowedActions) && !in_array($action, $this->Auth->allowedActions)) {
    $this->Session->write('Auth.redirect', '/' . $this->name . '/' . $action);
    $this->Auth->loginRedirect = array('controller' => $this->name, 'action' => $action);
    $this->redirect($this->Auth->loginAction, NULL, TRUE);
    return FALSE;
  } else if (!in_array('*', $this->Auth->allowedActions) && !in_array($action, $this->Auth->allowedActions) && $this->Auth->user() !== NULL && !$this->Acl->check($this->Auth->user(),$this->Auth->action())) {
    $url = '/' . implode('/',$this->Auth->loginAction) == $this->referer() ? '/' : $this->referer();
    $this->Session->setFlash('You do not have permission to perform that action.');
    $this->redirect($url, NULL, TRUE);
  }
}

This function checks to see if the user is logged in when accessing restricted actions. If not, the user is redirected to the login page. If so, and if the user is attempting to access a page for which he has no permissions, then the user is bounced back to the referring page.

Of course, you can skip all this if you build a skeleton CRUD using cake bake and specify not to use scaffolding.

02.20.09

Updates for IE8

Posted in Web Development at 4:00 pm by BrianS

With the release of IE8 getting closer I took a moment to check out our web site and Benchmarks Online in the new browser. As I suspected, the incompatibilities on our web site were of the same ilk as when IE7 was released and required minor updates. The updates to Benchmarks were also fairly minor, though did require a bit of effort to track down. Luckily IE8 includes developer tools right in the browser, making debugging web content much easier.

While I was at it, I decided to see how the JS+CSS was working in Safari (latest version on Windows and Mac) and Opera (latest versions on Windows). There were some minor issues with these browsers that I fixed as well. Quickly, Safari and Opera both were having trouble displaying the bullets in my ordered list when the pseudo multi-column-styling was applied. The odd thing is that the numbers were there, just not visible until an element was forced on top of them.

Finally, I decided to update the multi-column list code on the web site to that used in Benchmarks (which is more robust). I did run across one problem in the update that I may have to investigate further. Essentially, the script was not correctly positioning the second+ columns if a margin was specified on the container OL/UL.

02.06.09

Excel drops empty columns on CSV export

Posted in Web Development at 4:58 pm by BrianS

I’m often tasked with getting data from a flat format (Excel, CSV, etc.) into a database. When the format is Excel  and I have only a few files to work with I find it easiest to export the Excel file to CSV or TSV format. This makes it much easier to script the data conversion using PHP. There’s only one problem, Excel doesn’t always represent the total number of columns in each row. If there are blank values in the columns at the end of a row in the spreadsheet then the exported data may have fewer value delimeters than expected.

This bug has been documented by Microsoft for Excel 2003 and earlier. The solution given by Microsoft is poorly worded, but basically it says to make sure the cells at the end of a row always contain data. Not always a realistic proposition. Luckily I found a step-by-step solution that works perfectly:

Put a formula that evaluates to empty (=”" ) in the last column of  the rows that are empty:

  1. select the range in the last column: edit->goto, click on the “Special…” button, Choose “Blanks” and click OK.
  2. type the formula: =”"
  3. hit ctrl-enter

Now when you save the document the appropriate number of delimeters should be present, even in rows with empty cells.

« Previous entries Next Page » Next Page »