Tag Archives: databases

Simple MySQL Diff

Dead simple way to diff two MySQL databases (probably applicable to others as well).  Just create a new file with the following:

#!/bin/sh

mysqldump database1 --no-data -u username -p > db1.sql
mysqldump database2 --no-data -u username -p > db2.sql
diff db1.sql db2.sql | grep \( -B 1

Make sure to chmod o+x the file to enable execution. This will output the locations of changes in your databases, and you can go to the line numbers to find the actual changes.

Setting up a new Rackspace Cloud Server with Debian 6, Name.com, and PHP/MySQL

Another instructional blog post as I try to figure out something.

Configuring a website with Rackspace.com is much different than setting one up with a shared hosting service such as Dreamhost or GoDaddy.  I could write more about this, but it’s not really productive.

Anyways, here are the steps I went through:

1. Buy a domain.

I chose Name.com, for their cheap prices and no-bullshit/non-shady website (e.g. GoDaddy).  For $8.25/month (after coupon code), you get a .com domain.

2. Configure Google Apps

With Name.com, this is very simple, there’s a button for it.  After that, follow Google’s very detailed instructions.  I chose this option instead of configuring mail on my own server because it’s simpler, and a very pretty interface.  Plus, all the people working with me on the site already have gmail.

3. Set up a Rackspace.com Cloud Server Account

Pretty simple, not many options to choose.  At first, all you need will be a cloud server.

4. Set up your server on Rackspace.com

I went with a Debian 6.0 server with 512MB of RAM after about 10 minutes of internet research.  I have some experience with CentOS, Ubuntu, and Fedora, but the internet said “Debian” so I went with that.

5. Set up DNS on Name.com

First, delete the “A” DNS records on name.com.  These can be found under “Domain Management” » “DNS Record Management.”  Next, add a new “A” record with “*” in the Record Host field and your Rackspace IP in the Record Answer field.  Add another one with a blank Record Host field and the same IP.

6. Log in to your server as root, install everything

Using the provided root password from Rackspace, open up a terminal (on Windows use PuTTY).  Enter
#apt-get install apache2 php5 libapache2-mod-php5 mysql-server mysql-client php5-mysql
At this point, your DNS information will take a little while to propagate across the internet.  However, if you type your url into your address bar, you should get a page that says “It works!” (As of Debian 6).
Missing data…
Next add the following to your .htaccess for compression and www-removal.  Make sure to change “domain.com” to your domain.
RewriteEngine On
RewriteBase /
RewriteCond %{HTTP_HOST} !^domain.com$ [NC]
RewriteRule ^(.*)$ http://domain.com/$1 [L,R=301]

# compress text, html, javascript, css, xml:
AddOutputFilterByType DEFLATE text/plain
AddOutputFilterByType DEFLATE text/html
AddOutputFilterByType DEFLATE text/xml
AddOutputFilterByType DEFLATE text/css
AddOutputFilterByType DEFLATE application/xml
AddOutputFilterByType DEFLATE application/xhtml+xml
AddOutputFilterByType DEFLATE application/rss+xml
AddOutputFilterByType DEFLATE application/javascript
AddOutputFilterByType DEFLATE application/x-javascript

Storing recurring events in a database

One problem that seems to affect many web developers (and desktop programmers as well) is how to store recurring events in a database. There are several ways to do this, all with varying degrees of complexity (both inserting and selecting), storage requirements, and requirements on the type of recurrences. These can all be found by googling, and most are language agnostic in their implementations.  In my case, I needed to store events that could have a very complex set of requirements. Some examples:

  • Weekly on Thursday and Friday forever
  • Once a month on Friday for 6 months
  • Every other Friday
  • Every third Friday of the month

Also, it needed to be able to handle exceptions and extensions to existing recurring events, and the events could not be purely virtualized instances, since other objects would references event id numbers as a way of collating data collected. After all this, a set of three tables seems appropriate, with Event, EventModel, and EventException objects.  Each Event is an instantized version of EventModel, and once instantized will remain forever in the database.  While this is not best practice, negative infinity in our case is relatively manageable for now.  In the future, implicit grouping of data according to a index-less key may be used.  EventModel, which contains all the attributes of a single event, plus meta data relating to recurrences acts as a prototype event for recurring events.  To normalize the data a bit, even non-recurring events will be stored as EventModels.  This will aid in data manipulation later.  The EventException object belongs to a third table, which stores individual event exceptions.

In practice, a daily cron script will create events for that day based upon the rules in EventModels.

When viewing events, the easiest way is to view EventModels, with sub-grouping of recent and upcoming events associated with that EventModel.

 

Of course, I’m not an actual programmer or computer scientist, so suggestions are welcome.