Building A Domain Drop List

Well here comes part 2, of the guide. What Do You Need is Part 1 which details the requirements, application process, etc. This article assumes you have Nominet Membership, EPP, DAC and Zonefile Access along with suitable hosting.

Drop List Building Applications

You could do this in 1 large application to handle it all, but I think that’s a mistake. Writing a collection of small tools each with a simple job, reduces server load and risk of timing out. Not to mention makes it easier to handle.

So first things first…

A Simple Database

You will need a simple database to hold the list and dates…

id(int), domain(varchar(136)), dropdate(timestamp), updated(timestamp) on update).

How simple is that database… I haven’t posted a database schema, since you may want to add Tag, Creation Date, Expiry Date, even break it down to show Keywords or Extensions, or anything else you require really. You could also include things like domain length, if it exists in other .UK family extensions and much more, so I have just given you the absolute basic.

I would personally include the domain length, the second level family extension, and possibly creation year.

Loading The List

The first small application is one which can read the 10.5 million names in the CSV and load these into the database. The odds are a shared hosting account wouldn’t be able to handle this kind of long resource hungry process, hence why you’ll need Suitable VPS hosting or similar.

This application is as simple as…

$file = "path/to.csv";
$handle = fopen($file,"r");
do{
$domain = strtolower(trim($data[0]));
$result = mysql_query("INSERT INTO `zonefile`.`droplist` (`id`, `domain`, `dropdate`, `updated`) 
VALUES (NULL, '$domain', NULL, CURRENT_TIMESTAMP);");
}
while($data=fgetcsv($handle,1000,",","'"));

This can take anywhere up to half hour I would guess, depending on the power of your server and available memory.

In order to add domain lenth, you would need to have added a length column to the database earlier. Once that’s done either with the MySQL command char_length() or the php command length(). The easiest would be…

$result = mysql_query("INSERT INTO `zonefile`.`droplist` (`id`, `domain`, `length`, `dropdate`, `updated`) 
VALUES (NULL, '$domain', CHAR_LENGTH($domain), NULL, CURRENT_TIMESTAMP);");

You could just as easily do…

$domain = strtolower(trim($data[0]));
$length = length($domain);
$result = mysql_query("INSERT INTO `zonefile`.`droplist` (`id`, `domain`,  `length`, `dropdate`, `updated`) 
VALUES (NULL, '$domain', '$length', NULL, CURRENT_TIMESTAMP);");

You’re choice entirely, adding the extension would work the same way.

Obtaining The Drop Dates

When the domain names are loaded into the database, you will need another small application to read them one by one or in clusters, poll them with the Nominet DAC, and populate the database with the returned data. Since I posted a Dac Query Snippet already, I’ll just link to that, and you can add in the MySQL and the loop yourself.

You could use something like

SELECT domain FROM 'zonefile'.'droplist' WHERE 'dropdate" IS NULL LIMIT 1400

I have selected the limit of 1400, this will take approximately 5 minutes at 200ms / 5x per second allowing for latency. A simple CRONJOB set to load the script every 5 minutes and you’re golden. You can do smaller or greater amounts but it will eat memory and resources potentially making the server sluggish. Experiment a little but remember to adjust your CRON and bear in mind the DAC limitations.

Assuming you have used my DAC Query Code and added the extra bits. You will need to use an SQL Query to extrapolate the dropdate from expiry date which is returned by the DAC. I’m going to assume you have moved the expiry into a variable, but you can work on the array value too.

UPDATE `zonefile`.`droplist` SET `dropdate` = date_add('$expiry',INTERVAL 92 DAY)
 WHERE `domain` = '$domain';

The above query updates the dropdate, where the domain matches, and adds 92 days on to the returned expiry date to create the expected drop date. You can add any other data you want based on the DAC output by adding to this query as you wish.

DAC Limitations and Rules

Now would be a good time to discus the Rules and Limitations of the Domain Availability Checker (DAC), the DAC Usage Instructions are here too.. You are limited to polling 432,000 queries per day, with a maximum of 16 names per second (1,000 per rolling minute). By queries that means ‘#limits’ or ‘#usage’ or actual domains they all count. Go over either of these limits and you will be blocked from DAC access until your quota recovers on a rolling 24 hour basis.

IF you do happen to hit a block, then the DAC will return a result like…

domain.co.uk,B,35065

A simple if…then trap, will be able to detect this and convert it from the number of seconds (35065) into a human readable time frame, I used to use this old code snippet.

if($response[1] == "B"){
	$blocktime = gmdate("G:i:s", $response[2]);
	$blocktime = explode(':', $blocktime);
	if($blocktime[0] == 00){
		$blocktime = $blocktime[1] . " minutes and ". $blocktime[2] ." seconds";
	}else{
		$blocktime = $blocktime[0] . " hours and " . $blocktime[1] . " minutes and ". $blocktime[2] ." seconds";
	}
	echo $blocktime;
	socket_send ($sock, "#exit\r\n", 9, 0);
	socket_shutdown($sock, 2);
	socket_close($sock);
	die();
}

Well that’s your drop list, built and populated with a little expense, a chunk of time and some basic coding, much of it done for you.

Depending on the efficiency of your code, server latency and some other factors, it could take up to 26 days scan the whole zone file, by this point your database would be up to 4 weeks out of date. This is because the zone file is 24 hours old when released. Part 3: Maintaining A Drop List, we will deal with this problem and work on updating the database.