Computer Programming

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

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.

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.

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
            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



A good tutorial at DDJ.

There's going to be a conflict with Prototype, because it also uses $, but differently.

Email Obfuscation and Shielding Script

Here's a perl script that takes email addresses as arguments, and returns javascript code that hides your email address from web spiders. The email address is also linked so it's clickable.

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


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

Josh's 3-Column Layout in CSS

Josh Haglund came up with an awesome way to do a 3-column layout in CSS.

Let's suppose you have three DIVs, arranged into three columns with the float:left and float:right styles. (Chances are, if you're reading this, you know what this is. If not, Google some other pages, and see what others do.) The common problem (aside from learning to use floats) is that the columns aren't all the same height.

The quick solution is to create a background image that looks like the 3-column layout. If it's a simple layout, then you should be able to use a 1-pixel tall, very wide line, repeated several hundred times, to create the columns. Put that skinny gif into a DIV via a background-image:url(skinny.gif).

Mixed Object Types in ContentIterWriter

Josh and I were congratulating ourselves on the relative goodness of ContentIterWriter (aka ContentObject), and it's been bouncing around my brain a while.

There was a recent situation where I thought I'd try to put multimedia objects into CIW, and had a hard time. The job to do this never really happened, so figuring it out didn't happen either. I was thinking of creating a hierarchy of tables, where there's a general table for all objects, and a table for each specific type. (That was a bad idea.) Today I think the right way to do it isn't to create a super-type that can hold all types of media, but to create an object that takes CIW objects as input, and sorts them based on a specified field name for each object.

The only constraint is that the CIWs are pre-sorted.

Participate: Social Networking Software

This is a social network that was written back around 2004. My friends were doing a distributed anti-war protest. It was basically not that well attended, because people weren't really aware that the President was a liar, and that he would eventually suspend habeas corpus. On top of that, it was in competition with another demo, and that really killed it. The software did so-so, but it got taken down after the demos, because I got paranoid about the cops subpoenaing the entire database.

Also, the software was kind of hard to use. A lot of features were basically implicit, and you had to be pretty savvy to "get" it. Still, a lot of Friendster features were ripped off, and a couple features, like the double-blind email-based contact system were cool.

Drupal: Upgrade Notes

Been upgrading from an old Drupal 4.5 (aka Civicspace 82) to Drupal 5.1.

Dropping CiviCRM

I had to get rid of the unused civicrm tables. Installing it seemed like a good idea at the time, but no point of using it for a tiny campaign. Here's a script that will delete the tables. It's posted here because the InnoDB engine complains if you drop these in the wrong order, and violate some constraints.

drop table if exists `civicrm_activity`;
drop table if exists `civicrm_activity_history`;
drop table if exists `civicrm_activity_type`;
drop table if exists `civicrm_address`;
drop table if exists `civicrm_county`;
drop table if exists `civicrm_custom_option`;
drop table if exists `civicrm_custom_value`;
drop table if exists `civicrm_donation_page`;
drop table if exists `civicrm_email_history`;

Name Based Dispatch

Here's a simple way to do name-based dispatch. $dispatchTable = array( 'First Thing' => 'functionName', 'Second Way' => array('ClassName', 'StaticMethod') ); ... ... $data = "Your data here, probably in an array."; call_user_func( $dispatchTable[ $name ], $data ); This is nice, because you can put the dispatch table at the top of your code, or in a separate file. I'm using it in a payment processing system, where different products may be dispatched to different code. You get a level of indirection here, where you can swap out different methods for each product (or use the same method). The only "trick" here is that, call_user_func() takes a pseudo-type known as a callback. Callbacks come in three forms:

NVU - text fields for copy-and-paste

Here's how to create one of those text fields with HTML that the user's supposed to copy-and-paste into their page. It's not hard.

Create a form.

Add a Text Area. Give it a name, and set the rows and columns.

From the text area dialog, click on the "Advanced Properties..."

Click on the Javascript tab.

Add a property named "onclick" with the value of "this.focus();;".

Click OK.

Click OK.

NOTE: I found a serious problem - NVU's code reformatting will cause the html code to break within myspace, because NVU inserts newlines. To fix the problem, you have to save out the source, join all the lines, and upload the file manually.

Anti-Pattern: Working With Live Data

I recently lost a chunk of data while I was developing a nice little macro to produce a report. How it was lost, is pretty sad. I had become used to pressing a few keys to clear out my spreadsheet, and I accidentally pressed the keys on a spreadsheet of the live data. Pffft. Data vanished.

I luckily had most of the data in another document, and restored some of the lost data, but, the lost bits were lost. All this was due, not to faulty code, but because I failed to create a development sandbox.

Yes, this was only a macro, but, even for something so simple, it's smart to make a separate place to develop it. This sandbox would have contained a copy of the data.

A sandbox is better than a backup. That's because the sandbox is a minimal subset of what you need to write your program. The real deployment environment is usually a lot more complex. To back up the real environment, so it's safe to develop in there, could be more difficult than you could imagine, and take a long time, too.

Syndicate content