Off the Top: Database Entries

April 6, 2014

Non-UNIX Timestamping has Me Stamping in Frustration

In the slow process of updating things here on this site I have nearly finished with the restructuring the HTML (exception is the about page, which everytime I start on it the changes I start to make lead quickly to a bigger redesign).

Taking a break from the last HTML page restructuring I was looking at finally getting to correctly timestamping and listing the post times based on blog posting location. Everything after the 1783rd blog post currently picks up the server time stamp and that server is sitting in Eastern Australia, so everything (other than blog posts from Sydney) are not correctly timestamped. Most things are 14 to 15 hours ahead of when they were posted - yes, posted from the future.

Looking at my MySQL tables I didn’t use a Unix timestamp, but a SQL datetime as the core date stored and then split the date and time into separate varables created with parsing timestamp in PHP. This leaves 3 columns to convert. It is a few scripts to write, but not bad, but just a bit of a pain. Also in this change is setting up time conversions that are built into the post location, but shifts in time for day light savings starts adding pain that I don’t want to introduce. I’ve been considering posting in GMT / UTC and on client side showing the posting time with relative user local time with a little JavaScript.

I would like to do this before a server / host move I’ve been considering. At this point I may set up a move and just keep track of the first post on the new server, then at some point correct the time for the roughly 500 posts while the server was in Eastern Australia.

May 2, 2012

The Data Journalism Handbook is Available

The Data Journalism Handbook is finally available online and soon as the book Data Journalism Handbook - from Amazon or The Data Journalism Handbook - from O’Reilly, which is quite exciting. Why you ask?

In the October of 2010 the Guardian in the UK posted a Data Journalism How To Guide that was fantastic. This was a great resource not only for data journalists, but for anybody who has interest in finding, gathering, assessing, and doing something with the data that is shared found in the world around us. These skill are not the sort of thing that many of us grew up with nor learned in school, nor are taught in most schools today (that is another giant problem). This tutorial taught me a few things that have been of great benefit and filled in gaps I had in my tool bag that was still mostly rusty and built using the tool set I picked up in the mid-90s in grad school in public policy analysis.

In the Fall of 2011 at MozFest in London many data journalist and others of like mind got together to share their knowledge. Out of this gathering was the realization and starting point for the handbook. Journalists are not typically those who have the deep data skills, but if they can learn (not a huge mound to climb) and have it made sensible and relatively easy in bite sized chunk the journalists will be better off.

All of us can benefit from this book in our own hands. Getting to the basics of how gather and think through data and the questions around it, all the way through how to graphically display that data is incredibly beneficial. I know many people who have contributed to this Handbook and think the world of their contributions. Skimming through the version that is one the web I can quickly see this is going to be an essential reference for all, not just journalists, nor bloggers, but for everybody. This could and likely should be the book used in classes in high schools and university information essentials taught first semester first year.

August 25, 2003

June 23, 2003

ODBC on Apple Jaguar

ODBC in Apple Jaguar to help share data between applications.

March 16, 2003

MySQL becoming a viable choice for corporations

Fortune's David Kirkpatrick discusses MySQL Database being very popular on the Web and it is free. This is not a suprise to many of us as I thought I was the last to use MySQL in 2000 for a professional project. I quickly found that the combination of MySQL with PHP on a Sun Solaris U10 could provide 70k to 90k page builds per hour. That was nearly static rates when compared to a Windows box. One of the nice things we found was MySQL was not only lightening fast, but very well suited for Web development and driving content dynamically. MySQL is the type of database the MS Access could have been. MySQL is what is behind this site and works wonderfully. The database does not currently have transactions, which is not needed for most of the work where it is used.

I was very happy to learn MySQL is profitable. It was very odd that somebody in the article brought up all the money that is being left on the table for MySQL. The CEO of MySQL gave a fitting responce in asking what the other companies were doing with the money they charged. I was pleased as punch that MySQL is at home on every Mac running OS X.

August 25, 2002

MySQL for Jaguar

MySQL for Jaguar loaded wonderfully and easily.

July 2, 2002

May 10, 2002

PHP and MySQL for managing images on the Web

Managing Images With a Web Database Application with PHP and MySQL and nothing up the sleeves. The folks from O'Reilly Net offer this one, which is not in the Web Database Applications with PHP and MySQL. The book is one of my favorite information application development books at the moment for a variety of reasons: ease of coding principles, explaining application development, explaination, then using what is learned and implementing it.

April 25, 2002

WYSIWYG in browser part two

The second part to theWYSIWYG editor in a Web browser is available. This section gets into implementing the HTML portion from the first section into the storage components of this article.

April 4, 2002

So you want to build your own weblog tool like the one here? Start with PHP and MySQL with a little Apache and a sprinkling of arrays and script code. Yes, this is basically what is under this puppy.

March 13, 2002

O'Reilly Net continues its Apache on OS X series with integration of MySQL. This is one element I have not set-up as of yet and one that will make development of this site much easier and also make the development portable.

February 24, 2002

Web Reference guides us through efficient SQL calls to the database. Effiecient calls help speed the application's presentation and puts less of a demand on the server's resources.

February 17, 2002

Ah, an even newer update to MySQL on Mac OS X. My last build was a little buggy, which was to versions back. It is seeming that it is contiually a good thing to follow the setup info on Marc Liyanage site.

February 8, 2002

Okay, O'Reilly Net has been offering a lot of good resources of late and I am a little behind in catching up.

Those of us that need a light weight database for a small project and are using Microsoft Windows often turn to Access to perform the task. Steven Roman offers his tips in how to set up an Access database. The best tip is right up front:

Don't touch your PC until you have taken the time to rough out a design of your database.
The tips keep coming and many of them apply to any other database development. Once I learned to think of information and metadata in a relational database format (which also helps with setting up XML documents) application development became easier. Understanding a little database design will also help ask the right questions when setting up an application, database, and/or project.

This article also helps define the limitations of Access databases. Each database will have its own limitations or peculiarities. Knowing these differences will help think about the application, information, and how they can and will be used is helpful.

February 7, 2002

DM Review provides a history of metadata and the future of metadata in one article, what more could you want. It is a good overview of the past tools and goals of metadata. Central to providing a solid information application is understanding the data and information we are working with. This understanding allows us to tie is to other information to give that data better depth and this is best done with metadata. Metadata help explains the data and allows for data to be tied (or joined) to other data.

An example would be putting together a quick view of all products sold in an ice cream truck. The driver has a small computer that captures all the sales he has makes throughout the day. At certain intervals he synchs his sales to a central computer so that he can easily stop back at the warehouse and stock up on items he is low on. The driver has sold 7 of ab4532 and 16 of tr78t in the past hour. He has had requests for pp887 too. This information is useful for the computers and the data has meaning, but it needs some metadata to know that he will need 7 of Abners Glow-cicles, 16 Tricky Rootbeer pops, a carton of covered paper cups. This metadata will help the person that will prepare these items for the driver's arrival at the warehouse. The "pp" indicates that a paper product is needed and the number following is its actual product code. The raw data now has meaning with tanks to the metadata.

January 24, 2002

I now have MySQL running on my OS X Powerbook. I largely followed the directions from The Business Mac MySQL instructions. I used the install package from and found it a rather easy build. I did have to make one minor adjustment. When trying to start MySQL for the first time it is good to place an empty mysql.sock file in the /tmp/ directory. The other item to add is create a directory (sudo mkdir) libexec in the mysql directory then go into the libexec directory and create a softline to ../bin/mysld (sudo ln -s ../bin/mysqld mysqld). You should be good to go.

January 13, 2002

Content management is back at the forefront of every aspect of my digital life again. Content management revolves around keeping information current, accurate, and reusable (there are many more elements, but these cut to the core of many issues). Maintaining Websites and providing information resources on the broader Internet have revolved around static Web pages or information stored in MS Word, PDF files, etc. Content management has been a painful task of keeping this information current and accurate across all these various input and output platforms. This brings us to content management systems (CMS).

As I pointed to earlier, there are good resources for getting and understanding CMS and how our roles change when we implement a CMS. Important to understanding is the separation of content (data and information), from the presentation (layout and style), and from the application (PDF, Web page, MS Word document, etc.). This requires an input mechanism, usually a form that captures the information and places it in is data/information store, which may be a database, XML document, or a combination of these. This also provides for a workflow process that involved proofing and editing the information along with versioning the information.

Key to the CMS is separation of content, which means there needs to be a way to be a method of keeping links aside from the input flow. Mark Baker provides a great article, What Does Your Content Management System Call This Guy about how to handle links. Links are an element that separates the CMS-lite tools (Blogger, Movable Type, etc.) from more robust CMS (other elements of difference are more expansive workflow, metadata capturing, and content type handling (images, PDF, etc. and their related metadata needs)). Links in many older systems, often used for newspaper and magazine publications (New York Times and San Francisco Chronicle) placed their links outside of the body of the article. The external linking provided an easy method of providing link management that helps ensure there are no broken links (if an external site changes the location (URL) it there really should only be one place that we have to modify that link, searching every page looking for links to replace). The method in the Baker article outlines how many current systems provide this same service, which is similar to Wiki Wiki's approach. The Baker outlined method also will benefit greatly from all of the Information Architecture work you have done to capture classifications of information and metadata types (IA is a needed and required part of nearly every development process).

What this gets us is content that we can easily output to a Web site in HTML/XHTML in a template that meets all accessibility requirements, ensures quality assurance has been performed, and provides a consistent presentation of information. The same information can be output in a more simple presentation template for handheld devices (AvantGo for example) or WML for WAP. The same information can be provided in an XML document, such as RSS, which provides others access to information more easily. The same information can be output to a template that is stored in PDF that is then sent to a printer to output in a newsletter or the PDF distributed for the users to print out on their own. The technologies for information presentation are ever changing and CMS allows us to easily keep up with these changes and output the information in the "latest and greatest", while still being able to provide information to those using older technologies.

January 6, 2002

Troy Janish's XML tutorial for A List Apart is a great resource. The elements for implementing XML are rather solid now. Knowing how to make use of XML is going to be a needed skill set for many. The debate over database storage of content objects over XML storage of the same will continue for quite some time. I have my leaning for database storage, given their data storage and manipulation functionality built in, but outputing XML documents to share the information seems to be a better option than providing authorized access to the database.

November 1, 2001

The Dev Shed offers a scenario many of us gone through in their Time is Money article. It walks through a tough scenario of delivering an intricate project with few requirements. In all it is a nice over view of developing a web based application using PHP and MySQL.

Finding the source of phpMyAdmin was a chore. It is a great tool for those with MySQL databases and have PHP on the box. It is a GUI interface to ease management of the tables and properties. I have used it for quite some time on another site and now it is time to take it to work. It is free so the price is right. It does not do ERD, but it is better than command line interfaces.

Previous Month

This work is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike License.