Computer Programming

Yes, a bit specific, but I need to store some links!

Mutliple Time Sheets 6, Finally

This is a blog about the development of MTS 6, the next version of the wildly unpopular Multiple Timesheets.

It's one of those apps that just never took off. It was probably to quirky and personal, and worked a little funny compared to other solutions. Despite these problems, it helped me make money.

Lately, I've needed to track my time more closely, because work is getting overwhelming, and I'm letting it overwhelm me. I also need to get my finances in order.

Thus, the next version.

With new, exciting (yawn) features planned.

  • Uses a database!
  • Support for more than one user!
  • Track expenses!
  • Invoice for expenses *and* time!

Daylight Savings Time Misconfigurations between Computers Can Lead to Cumulative Data Errors

Symptom: my web app starting losing a day whenever I edited some data records. This app was working fine for a while, then, in March, it started losing time. Because my time was quantized to days, it looked like I was losing a day every time I saved the record.

The environment was a shared web hosting account, with PHP for the app, and a MySQL database behind it, on a separate machine.

According to a new law, starting in 2007, DST starts three weeks earlier than it has in the past.

I suspected that the problem is that one machine has the old style DST timezones, while the other machine has the new DST settings. So I created a test script to simulate the effect of multiple edits on a record.

Salary Surveys for Information Technology

2008-03-12: a report on Tech Republic. It's done by Global Knowledge

Web dev salary survey on Zeldman's A List Apart

Another popular survey is done by SAGE.

PHP List Hack: Shared Codebase

This is a trick to allow you to run one copy of the PHPList software on your server, and have separate configurations for each domain. The main advantage is that you don't have a bunch of copies of the same software all over the place. (PHPList takes up around 5 megs of disk. Using a similar technique with Drupal [which takes up 6 megs], we can install Drupal and PHPList in 260k of space, not including databases.)

At the top of index.php and admin/index.php, there's some code that looks for the config.php file. This new code snippet allowed me to create a config file named "phplist.config.php" in the user's root (or the ftp root) instead of the usual places.

Object-Oriented Parser for iCalendar

Been working on a parser for ICS files, and it's done in an OO style - so that parts of the data become instantiated as objects, and the parse tree is a hierarchy of objects.

MS Access: Inserting and Deleting Contact Items With VBA

Gripe: VBA syntax is difficult. The object system is a little confusing too. It's just very hard to use. To make things even more difficult, the sample code out there is kind of *weird*. Maybe there's some good reasons for doing things their way, but, it just seems verbose, error prone, and hard to write, to me.

Here's some code that is the start of a library to work with Outlook's folders. It's based on some code samples from the web, refactored into something resembling a library.

MS Access: Printing the Range of Data on the Page on a Report

I wanted to print a report that indicated the first and last item on each page, just like a dictionary has. You know: "Azeri - Babcock", "Milk - Minder". It makes it easier to flip through printouts.

This is how to do it. It will put the range in the footer. I haven't figured out how to do one in the header, which is what I originally wanted, but found too difficult to do. (There is probably a way.)

First, take your report, and add an unbound field to your report. Rename it to "Range". See the picture below.

MS Access: Logging Messages

Here's some code to help you log messages to a table. First, make a table called tblLog, with at least these columns: Timestamp, User, Computer, Message. (You don't need a primary key.)

Set the default value of Timestamp to NOW().

Copy the following code into a code module.

Also, add a reference to "Active DS Type something or other". It has the active directory functions you need to discover the username.

Function StartUp()
    Dim dummy
    dummy = LogOpen()
    DoCmd.OpenForm "frmHidden", acNormal, , , , acHidden
    StartUp = Null
End Function

MS Access: Application Configuration Settings in Tables

This is a relational way to store application configuration in a table. It uses two tables, so you can store multiple configurations, so that you can use the tool over and over, and still retain the old settings. One table stores configurations, and one stores a since row with the current configuration in use.

Setting values are retrieved from the configuration tables with queries like this:
(SELECT PreRegActivityID FROM Congress7_Config WHERE ID=(SELECT CurrentConfigID FROM Congress7_CurrentConfig))

MS Access: Inserting Blank Rows

This is a way to insert empty or empty-like rows into a list of "seats" that contains not only reservations, but a number saying how many seats a group of people have. If the number is greater than the number of seats, this adds new blank rows for empty seats.

Sub insertBlankRows()
    Dim dbs As Database, qdf As QueryDef, strSQL As String
    Dim rst As Recordset
    
    Set dbs = CurrentDb
    strSQL = "SELECT tblSeats.OrganizationId, [MaxOfSeats]-Count([OrganizationId]) AS Difference, " & _

Updating a Combo Box with a Requery

In MS Access, when you want a form containing a foreign key, you typically use a combo box that's populated with data from the foreign table. The underlying field is the fkey's value, but the combo box displays another column from that table that is easier for people to read. For example:

The Foo table has an fkey, PeopleID, that refers to the tblPeople table.
TblPeople has a field, Name, that is the person's name - a human comprehensible value.
The frmFoo form has a combo box that's bound to the PeopleID fkey.

Perl Rocks (even yet)

Perl still rocks. People still say it's hard to read (true), but it's because the language is terse.

sub getAccountIdsOfSitesToSuspend
{
    $sql = <<EOQ;
        SELECT account_id
        FROM account
        WHERE
            account.account_balance < (SELECT triggerAmount FROM fk_suspension_rules)
            AND (
                    SELECT IF(SUM(transaction_ammount), SUM(transaction_ammount), 0)
                    FROM transaction
                    WHERE transaction_date > DATE_SUB( CURDATE(),
                        INTERVAL (SELECT paymentWindow FROM fk_suspension_rules) DAY)
                    AND transaction.account_id = account.account_id

Removing Rows from a Table with Access, for JBlast

The goal is to remove all the bad fax numbers from a JBlast fax list. This applies to any situation where you want to remove one list of data from another list of data. Another way to say it is that you have a full list, and you're trying to remove a sublist from the full list.

The way you do it is by taking the full list, and then doing a JOIN that will add a column that identifies the rows that are present in the sublist. You can do this with a LEFT JOIN. A LEFT JOIN includes all the rows in the left table, in this case, the full list. It matches on a key in the right table, and when there's a match, columns from the right table are included; when there's no match, the columns are set to NULL.

The following image shows a left join in Access.

Coding Tactics, Mysteries of Access

Tactics, tactics, tactics: an essay about how to become a better programmer by studying code. Great analogies.

Why does Access hurt? It's just weird how you can modify the standard controls in Access, but they usually look like crap. You get a lot of control, via the numerous object properties, but, the interface never looks very nice.

MySQL Optimization

Here's a noob-to-noob optimization trick. Suppose you have a database table with, say, 200,000 records, and you regularly select on multiple criteria. The rule for selection is to put the most specific WHERE clause first, and the least specific last. The goal is to cut down the search set to something small, and then search through the smaller set. Get all the queries using this order, then create a composite index over the keys to speed up the search even more.

Here are some before and after shots, based on real queries (from sf-active):

select * from tb where display='t' and parent_id=0 and id > 198000 limit 0,30

After:

select * from tb where id > 198000 and parent_id=0 and display='t' limit 0,30
Syndicate content