1 | 2

1 - 5   [7]

The Installation Bane

Relates to IDEs

It is always a pleasure [sarcasm] after installing new software to discover that it has scrambled the functioning of other regular programmes on my system. Earlier today I stumbled across the Style Studio CSS editor and decided to give it a quick try out, being on the look-out for a lightweight CSS auto-completion tool I could tie in with Ultra Edit (just to help out all those times the memory fades and I have to dive for a reference book to look up a property value). The features list promised a lot, perhaps too much for my requirements as I had previously found with TopStyle Pro and Style Master.

Unfortunately it didn't meet expectations being quite resource heavy and failing a number of times in graphical display. Well the delight came when I entered the Norton control panel to reset options and discovered the Windows Scripting engine was no longer working! (I suppose it is too much to ask software vendors to give an idea of how their software will mangle the operating system!)

Fortunately, I always use Install Watch Pro for new software installs from Windows executables, and so quickly opened it up to see what damage CSS had caused. While I was amused to see it had created over 50,000 new registry entries for the actual software itself, I was less pleased to see it had totally reconfigured the type library for the VB Script dynamic link library to point to an older version, installed to a subdirectory of the new software's root during installation! As it happened I only had to restore about 10 registry keys, but certainly without Install Watch Pro's clear snapshot of the preinstallation configuration I would not have know where to begin! I can't recommend this freeware software enough.

The outcome, two hours of the day gone and none the wiser!? As for CSS editor, I actually decided just to update TopStyleLite to version 3 on my system. This serves my main requirement, however it would be nice to find a tool that gives site reports similar to those produced in TopStyle Pro and Style Studio, without the added burden of a full blown CSS/HTML Editor.

Posted on Feb 29, 2004 at 02:03:23. [Comments for The Installation Bane- 0]

MySQL Schema Via PEAR::DB_DataObject

Relates to PHP and MySQL, PEAR

I have been updating some of my PEAR classes and stumbled upon the DB_DataObject package. I have actually been trying to implement a data modelling layer within my PHP CMS project for some time, and this package is a comprehensive collection of methods to achieve just that abstraction. Configuration was relatively painless and with a set of data objects ready to go I have been testing their application in the CMS API.

In the original interface, I was parsing initialisation files (parse_ini_file) to load the table schema from which the appropriate form or table model could be constructed utilising a mix of PEAR's HTML packages and custom classes. The main problem with this was that changes in the MySQL table schema had to be manually updated in the ini file.

When a set of data objects are created with DB_DataObject::createTables(), an ini file is also created that records the database schema. This file is parsed internally when a new data object is instantiated for a particular entity. The table schema is instantly available by calling DB_DataObject::table().


$obj =& DB_DataObject::factory('data_object');
$dbo_schema = $obj->table();
echo "<pre>";
foreach($dbo_schema as $fld_name => $fld_type)
{
  printf("%-20s", $fld_name);
  if ($fld_type & DB_DATAOBJECT_INT) echo "int ";
  if ($fld_type & DB_DATAOBJECT_STR) echo "str ";  
  if ($fld_type & DB_DATAOBJECT_DATE) echo "date ";  
  if ($fld_type & DB_DATAOBJECT_TIME) echo "time ";  
  if ($fld_type & DB_DATAOBJECT_BOOL) echo "bool ";  
  if ($fld_type & DB_DATAOBJECT_TXT) echo "txt ";  
  if ($fld_type & DB_DATAOBJECT_BLOB) echo "blob ";  
  if ($fld_type & DB_DATAOBJECT_NOTNULL) echo "not null ";  
  if ($fld_type & DB_DATAOBJECT_MYSQLTIMESTAMP)
     echo "timestamp ";  
  echo "\n";
}
echo "</pre>";

The returned schema contains key/value pairs where the key is the field (attribute) name, and the value is the datatype(s) as defined when the ini file is created. The datatype value is conditionally tested against the named constants with the bitwise & operator to determine the actual types defined. This works fine for a table model, where the schema fields and a generalised data type is all I require [the table model uses DOM scripting to allow column reordering based on general data types]. However, for the form model, I need to know the maximum allowed size for fields, default values and other metadata to compose accessible and usable form controls. For this the obvious solution is to fall back to the MySQL layer itself, and run a "SHOW COLUMNS" SQL query.

A PEAR::DB object is already a persistant property of the instantiated data object, so if this can be retrieved a standard DB::getAll() query can be run.


$db =& $obj->getDatabaseConnection();
$mysql_schema = $db->getAll("SHOW COLUMNS FROM "
                              . $obj->tableName());
echo "<pre>";
foreach($mysql_schema as $field)
{
  printf("%-8s %-25s %-10s %-6s %-20s\n",
         $field[0], $field[1],
         (empty($field[2]) ? "[ NOT NULL ]" : ""),
         $field[3], $field[4]);  
}
echo "</pre>";

DB_DataObject::getDatabaseConnection() returns a reference to the PEAR::DB object. To keep the code snippet general DB_DataObject::tableName() is called to retrieve the table name within the SQL query. Example output of this code is:


id      smallint(5)   unsigned  [ NOT NULL ]   PRI
name    varchar(80)             [ NOT NULL ]
url     varchar(100)                           MUL
version float(3,1)              [ NOT NULL ]          1.0
amended timestamp(14)
added   timestamp(14)

This provides all the data I need to create a corresponding set of form controls. If the CMS model is amending an entity, loading the data is just a case of calling $obj->get(id), and the data can be updated with $obj->update() once it has been passed through a data validator, and the new values assigned to the object's properties.

Next is to put the SQL builder aspect of DB_DataObject to test with some of the more complex triple join queries currently in the CMS model…

Posted on Feb 28, 2004 at 00:52:09. [Comments for MySQL Schema Via PEAR::DB_DataObject- 1]

Article On Accessible DHTML

Relates to Accessibility and DOM Scripting

One of my aims now the new site is up and running is to start writing some articles. I regularly find my blog entries become too drawn out, and ideally these would be better presented as short articles. As always, I am currently way too busy to really get underway with this. However, to get the ball rolling, I have posted a short article on creating DHTML content without compromising accessibility. It basically surmises the process I went through on a recent site development to create a manual image slideshow, hence allowing the maximum preservation of screen real estate.

I haven't found time to add a comments section following the article (which I do hope to do for all future articles, since discussion generally brings out further ideas and enhancements). However, feel free to post any remarks, criticisms, or errors for the article under this blog entry. I hope it is of use to those crossing the accessibility threashold.

Posted on Feb 25, 2004 at 23:04:08. [Comments for Article On Accessible DHTML- 0]

Firefox Rises From The Ashes

Relates to Web Standards and Firefox and Co

Well it was once called Pheonix, but that feels like a distant ancestor of the newly released Firefox Browser this week. I decided to hold back the excitement a few days to let the server load ease off, and finally tried out Firefox yesterday.

Already much acclaim has been accredited to this, version 0.8 of the pre-release browser of the future, and I can only add further praise. The development team have clearly put a lot of preparation into this latest release, even down to the finer details like the slick new icons and advertising logos.

The benefits for development have already been raised on Stylish Scripting, suffice to say I am eternally grateful to finally see the DOM Inspector integral to the outfit.

For general browsing, the extensions just get better and better. A favourite of mine has always been the RSS Reader Panel, being very lightweight and quick and easy to get running. This is better than ever with a check for updates option, OPML import and export, stripping of HTML tags in the tooltip summary and the ability to load the feed in the content window with custom CSS. The RSS icon has even been dusted off to blend naturally with the other icons.

To be expected from a Mozilla based browser, Firefox has excellent support for Web Standards, and the inclusion of the Windows executable installer should make setup more accessible to the average user. I could probably ramble on indefinately about the finer features, but all I will say is if you are reading this blog and have not tried Firefox yet, you don't know what you are missing!

Posted on Feb 12, 2004 at 21:28:03. [Comments for Firefox Rises From The Ashes- 0]

Excel To MySQL Via PHP ODBC And COM

Relates to PHP and MySQL, COM

As a keen statistician with a fascination for weather variation over time (in particular in relation to equilibrium tidal variations) I have acquired comprehensive weather data over the past few years from my digital weather station. All the archive pressure and temperature readings are tucked away in Excel spreadsheets - this year I have actually moved over to Open Office as my spreadsheet of choice. I finally decided it was time to move some of this data into a MySQL database on my server, where I could then manipulate the data for display on the web.

To start with I have decided to keep things simple, storing average daily pressure and temperature for graphical display through the GD library. In part for experimentation, I had a look last weekend at the options for achieving the transfer using the PHP Command Shell.

Firstly I tried using ODBC by creating a System DSN for the Microsoft Excel Driver, and SQL to extract the data. The inbuilt ODBC PHP functions are fairly intuitive and the script was quite simple to build. However the solution was too proprietary since I had to first use MS Query to establish the field names for the required columns and synchronising the query itself:


define("COL_DATE", "F2");
define("COL_AVG_PRESSURE", "F27");
define("COL_AVG_TEMP", "F30");

[..snip..]

$s_query = "SELECT `" . $arr_month . "$`." . COL_DATE
         . ", `" . $arr_month . "$`." . COL_AVG_TEMP . " "
         . ", `" . $arr_month . "$`." . COL_AVG_PRESSURE . " "
         . "FROM `" . $arr_month . "$` "
         . "ORDER BY `" . $arr_month . "$`." . COL_DATE;

Instead I decided to have a look at the DCOM abilities of PHP. Unfortunately documentation is sparse in the PHP Manual and across the Web generally, with only one clear example of accessing Excel that I could find. So I decided to trial and error a bit to see how much of the Excel Object Model is recognised. Based in part on the example mentioned above this was the best solution I could get to run:


$xls = new COM("Excel.sheet") or die("Did not connect");
print "Application name:{$xls->Application->value}\n";
print "Loaded Version: {$xls->Application->version}\n";
foreach($years as $year)
{
  $workbook = "C:\weather\_" . $year . ".xls";  
  $wkb = $xls->Application->Workbooks->Open($workbook) 
         or die("Failed to Open Workbook"); 
  $xls->Application->Visible = 1;
  foreach($sheets as $sheet)
  {
    $month = (array_search($sheet, $sheets) + 1);
    $ws = $wkb->Worksheets($sheet);
    $ws->activate;
    for ($day = 1, $i = First_Cell; 
         $i <= Last_Cell; $i++, $day++)
    {
      if (checkdate($month, $day, $year))
      {
        $arr_day[0] = $year . "-" 
                    . $month . "-" . $day;
        $cell = $ws->Cells($i, AD);
        $cell->activate;
        $arr_day[1] = (! empty($cell->value)) 
                    ? sprintf("%01.1f", $cell->value) 
                    : NULL;  
        $cell = $ws->Cells($i, AA);
        $cell->activate;
        $arr_day[2] = (! empty($cell->value)) 
                    ? round($cell->value) 
                    : NULL;          
        $values[] = $arr_day;
      }      
    }
  }
  $xls->Application->ActiveWorkbook->Close("False");
}
$xls->Release();
unset($xls);

This algorithm quite literally steps through each required cell in turn, by cycling through the monthly worksheets and yearly files (note the constants AA and AD define the indexes of the required columns). It is however quite inefficient, and a faster method would be to extract values as range objects using a call like $xls->Application->Range('Jan!$AA$6:$AA$36).Value, but the PHP COM library does not seem to accept this. Still, the above code works and could be manipulated and applied to future data. However, I will probably look to use Python in the future, with a broader implementation of COM via the Win32Com module and its integration with Open Office. At least I now know that MS Office documents can be manipulated by PHP.

Posted on Feb 12, 2004 at 21:26:53. [Comments for Excel To MySQL Via PHP ODBC And COM- 10]

Breadcrumbs Trail

[ Home ] -> TW Blog -> Archives for February 2004
Site Map

The Severn Solutions website achieves the following standards:

[ XHTML 1.0 ] [ CSS 2 ] [ WAI AA ] [ Bobby AA ]

Page compiled in 0.007 seconds