MySQL database import scripts for the Moby Word Lists, Parts of Speech, and Thesaurus

PrepMoby.pl

#!/usr/bin/perl -wT
#
# PrepMoby.pl	2011-10-19
# This file is part of the MobyWordListsMySQL project.
# Copyright (C)2010, 2011 Steven Whitney.
# Initially published by http://25yearsofprogramming.com.
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# Version 3 as published by the Free Software Foundation.
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
#
# NOTES:
# This script uses Perl to prepare for importing two of the Moby Word Lists text files into the Moby MySQL database.
# It creates modified copies of the files without changing the original files.
# 1) It translates the mobypos.txt file from Macintosh MacRoman charset to MySQL latin1 (Windows-1252).
# 2) It fixes problems with the original Moby mthesaur.txt file.
#
# Much of these procedures can be done more efficiently with ssed and Linux shell commands, but one goal of this
# project was that it should be cross-platform, and Windows counterparts (especially sort) don't have 
# equivalent functionality.
# 
# CHANGELOG:
# 2011-10-19: 
#   Made the code in step 2b) fast enough to use, re-enabled it, and removed the workaround. 
#   Changed method of removing line ends and whitespace.

use strict;
use warnings;

use Encode;

# --------------------------------------------------------------------------------
# MOBY PARTS OF SPEECH (MOBYPOS) PREPARATION
# The original mobypos.txt is in MacOS MacRoman character encoding (although it has CRLF line ends).
# MySQL cannot directly import some of the characters into its latin1 character set. 
# This script translates the file to cp1252 (Windows 1252, which is the same as MySQL latin1).
# It seems that it cannot translate it all the way to ASCII. I had to do that manually with the linux iconv utility.
# In the Perl "Encode" class, the "substitution" character for illegal chars is always "?", not a char chosen to be similar. 
# So iconv is the only conversion method to ASCII I've found, and it's Linux-only, not Windows. 

print(STDERR "Transcoding mobypos.txt (MacRoman) to mobypos-cp1252.txt (Windows-1252 aka latin1)...\n");

my $infile = "mobypos.txt";
my $outfile = "mobypos-cp1252.txt";
open(INFILE, "<", $infile) or die("Can't open $infile: $!");
open(OUTFILE, ">", $outfile) or die("Can't open $outfile: $!");
# binmode prevents \n from being converted on output to \r\n in Windows
# it is necessary because the line terminator must be explicitly declared in MySQL LOAD DATA LOCAL INFILE;
# it doesn't adjust itself to the operating system, so we must ensure that our output here is the same for Win/Lin.
binmode(OUTFILE);	

my $line;
while($line = <INFILE>)				# reads 1 line at a time
{
	# REMOVE LEADING, TRAILING WHITESPACE, NEWLINES.
	$line =~ s/^[[:space:]]*(.*?)[[:space:]]*$/$1/;
	if(length($line) > 0)			# ensure whatever is left is not a blank line
	{
		Encode::from_to($line, "MacRoman", "cp1252", Encode::FB_DEFAULT);
		# This just turns non-ASCII chars into question marks. 
		# It doesn't choose a similar replacement char like iconv does. Unusable.
		# Encode::from_to($line, "cp1252", "ascii", Encode::FB_WARN);	
		print(OUTFILE "$line\n");	# put back a consistent \n in either Lin/Win.
	}
}
close(INFILE);
close(OUTFILE);


# --------------------------------------------------------------------------------
# MOBY THESAURUS PREPARATION.
# 1) MAKE A CLEAN COPY OF MTHESAUR.TXT, FIXING LINES WITH MULTIPLE CONSECUTIVE COMMAS AND WITH COMMA(S) AT LINE ENDS.
print(STDERR "MAKING A CLEAN COPY OF MTHESAUR.TXT...\n");

$infile = "mthesaur.txt";
$outfile = "mthesaur-clean.txt";
open(INFILE, "<", $infile) or die("Can't open $infile: $!");
open(OUTFILE, ">", $outfile) or die("Can't open $outfile: $!");
binmode(OUTFILE);	

while($line = <INFILE>)
{
	$line =~ s/^[[:space:]]*(.*?)[[:space:]]*$/$1/;
	if(length($line) > 0)			# ensure whatever is left is not a blank line
	{
		$line =~ s/,,+/,/gi;		# condense multiple commas to one
		$line =~ s/,$//;			# remove trailing commas
		print(OUTFILE "$line\n");	# put back a consistent \n.
	}	
}
close(INFILE);
close(OUTFILE);

# 2) CREATE FILE FOR MAKING A SIMPLE PHRASES LIST. REQUIRES TWO STEPS, USING A TEMP FILE.
# 2a) PUT EACH WORD OR PHRASE ON ITS OWN LINE.
print(STDERR "CREATING TEMP FILE TO MAKE A UNIQUE PHRASES LIST...\n");

$infile = "mthesaur-clean.txt";
$outfile = "mthesaur-phrases-unsorted.txt";	# source file to later sort+uniqueify, which you can do manually if desired.
open(INFILE, "<", $infile) or die("Can't open $infile: $!");
open(OUTFILE, ">", $outfile) or die("Can't open $outfile: $!");
binmode(OUTFILE);

while($line = <INFILE>)
{
	$line =~ s/^[[:space:]]*(.*?)[[:space:]]*$/$1/;
	if(length($line) > 0)			# ensure whatever is left is not a blank line
	{
		if($line =~ /^,/)			# paranoid check; should never happen
		{
			close(INFILE);
			close(OUTFILE);
			unlink("mthesaur-phrases-unsorted.txt");		
			die("mthesaur-clean.txt is corrupt. A line starts with a comma.\n");
		}
		$line =~ s/,/\n/g;			# change all commas to line ends
		print(OUTFILE "$line\n");	# write it back, adding the missing \n at original real end of line
	}	
}
close(INFILE);
close(OUTFILE);

# 2b) NOW SORT IT AND CULL TO A LIST OF UNIQUES. 

print(STDERR "SORTING AND CULLING PHRASES LIST TO UNIQUE ENTRIES ONLY...\n");

$infile = "mthesaur-phrases-unsorted.txt";
$outfile = "mthesaur-phrases-uniq.txt";	
open(INFILE, "<", $infile) or die("Can't open $infile: $!");
open(OUTFILE, ">", $outfile) or die("Can't open $outfile: $!");
binmode(OUTFILE);

my %phrasearray = ();
while(<INFILE>) 
{
	s/^[[:space:]]*(.*?)[[:space:]]*$/$1/;
	$phrasearray{$_} = 1 if(length);
}
foreach(sort keys %phrasearray) 
{
	print(OUTFILE "$_\n");
}
close(INFILE);
close(OUTFILE);


# 3) CREATE THE SOURCE FILE FOR THE MYSQL THESAURUS.
# Converts the cleaned copy of the Moby mthesaur.txt file
# FROM
#	root,related1,related2...\n
# TO
#	root,root\n
#	root,related1\n
#	root,related2\n
#	...
print(STDERR "CREATING THE SOURCE FILE FOR THE MYSQL THESAURUS...\n");

$infile = "mthesaur-clean.txt";
$outfile = "mthesaur-split.txt";
open(INFILE, "<", $infile) or die("Can't open $infile: $!");
open(OUTFILE, ">", $outfile) or die("Can't open $outfile: $!");
binmode(OUTFILE);

my $rootword;
while($line = <INFILE>)							# reads 1 line at a time into the scalar (non-array) variable
{
	$line =~ s/^[[:space:]]*(.*?)[[:space:]]*$/$1/;
	if(length($line) > 0)						# ensure whatever is left is not a blank line
	{
		$line =~ /^([^,]*).*/;					# just parses for regex, takes no action
		$rootword = $1;							# the word or phrase before the first comma is the root
		$line =~ s/([^,]+),?/$rootword,$1\n/gi;	# insert root word and comma before every word; separate entries with newlines
		print(OUTFILE "$line");					# the newlines are already embedded	
	}
}
close(INFILE);
close(OUTFILE);

print(STDERR "DONE.\n");
print(STDERR "The next step is to run the CreateMobyDatabase.sql script in MySQL.\n");

CreateMobyDatabase.sql

/*	CreateMobyDatabase.sql	2011-10-19
	Creates and populates all the tables in the MOBY MySQL database.
	This file is part of the MobyWordListsMySQL project.
	Copyright (C)2010, 2011 Steven Whitney.
	Initially published by http://25yearsofprogramming.com.

	This program is free software; you can redistribute it and/or
	modify it under the terms of the GNU General Public License
	Version 3 as published by the Free Software Foundation.
	This program is distributed in the hope that it will be useful,
	but WITHOUT ANY WARRANTY; without even the implied warranty of
	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
	GNU General Public License for more details.
	You should have received a copy of the GNU General Public License
	along with this program; if not, write to the Free Software
	Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.

NOTES:
--Adding indexes for pos and thesaurus made a huge speed improvement (100X?) for some queries. 
--The script does not create tables for these files: USACONST.TXT (US Constitution), roget13a.txt (sample thesaurus formatting).
--Dropping a table and recreating it is a way to reset an auto_increment field to start at 1. 

CHANGELOG:
2011-10-19:
  Simplified the code for loading mobythesauruswordlist because Perl can now create its data source quickly.

*/
-- Automatically display all errors and warnings.
WARNINGS;

DROP DATABASE IF EXISTS `moby`;
-- The default collation is case-insensitive, which is probably better for most purposes.
CREATE DATABASE IF NOT EXISTS `moby`
	  CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `moby`;

-- Most of the tables are imported directly from the Moby distribution text files, which have line ends of \r\n.

DROP TABLE IF EXISTS `crosswordlegal`;
CREATE TABLE `crosswordlegal`
(
	`alphaid` INT UNSIGNED NOT NULL AUTO_INCREMENT,
	`alpha` VARCHAR(255) NOT NULL,
	PRIMARY KEY (`alphaid`)
) ENGINE = INNODB;
-- LOAD FROM SCRABBLE DICTIONARY VERSION 1 FILE
LOAD DATA LOCAL INFILE 'CROSSWD.TXT' 
INTO TABLE `crosswordlegal` 
LINES TERMINATED BY '\r\n'
(alpha) 
;
-- AND FROM SCRABBLE DICTIONARY VERSION 2 FILE
LOAD DATA LOCAL INFILE 'CRSWD-D.TXT' 
INTO TABLE `crosswordlegal` 
LINES TERMINATED BY '\r\n'
(alpha) 
;

DROP TABLE IF EXISTS `acronyms`;
CREATE TABLE `acronyms` LIKE `crosswordlegal`;
LOAD DATA LOCAL INFILE 'ACRONYMS.TXT' 
INTO TABLE `acronyms` 
LINES TERMINATED BY '\r\n'
(alpha) 
;

DROP TABLE IF EXISTS `commondictionarywords`;
CREATE TABLE `commondictionarywords` LIKE `crosswordlegal`;
LOAD DATA LOCAL INFILE 'COMMON.TXT' 
INTO TABLE `commondictionarywords` 
LINES TERMINATED BY '\r\n'
(alpha) 
;

DROP TABLE IF EXISTS `compoundwords`;
CREATE TABLE `compoundwords` LIKE `crosswordlegal`;
LOAD DATA LOCAL INFILE 'COMPOUND.TXT' 
INTO TABLE `compoundwords` 
LINES TERMINATED BY '\r\n'
(alpha) 
;

DROP TABLE IF EXISTS `wordfreqenglish`;
CREATE TABLE `wordfreqenglish` LIKE `crosswordlegal`;
LOAD DATA LOCAL INFILE 'FREQ.TXT' 
INTO TABLE `wordfreqenglish` 
LINES TERMINATED BY '\r\n' 
IGNORE 2 LINES 
(alpha) 
;

DROP TABLE IF EXISTS `namesall`;
CREATE TABLE `namesall` LIKE `crosswordlegal`;
LOAD DATA LOCAL INFILE 'NAMES.TXT' 
INTO TABLE `namesall` 
LINES TERMINATED BY '\r\n' 
(alpha) 
;

DROP TABLE IF EXISTS `namesf`;
CREATE TABLE `namesf` LIKE `crosswordlegal`;
LOAD DATA LOCAL INFILE 'NAMES-F.TXT' 
INTO TABLE `namesf` 
LINES TERMINATED BY '\r\n' 
(alpha) 
;

DROP TABLE IF EXISTS `namesm`;
CREATE TABLE `namesm` LIKE `crosswordlegal`;
LOAD DATA LOCAL INFILE 'NAMES-M.TXT' 
INTO TABLE `namesm` 
LINES TERMINATED BY '\r\n' 
(alpha) 
;

DROP TABLE IF EXISTS `oftenmisspelled`;
CREATE TABLE `oftenmisspelled` LIKE `crosswordlegal`;
LOAD DATA LOCAL INFILE 'OFTENMIS.TXT' 
INTO TABLE `oftenmisspelled` 
LINES TERMINATED BY '\r\n' 
(alpha) 
;

DROP TABLE IF EXISTS `places`;
CREATE TABLE `places` LIKE `crosswordlegal`;
LOAD DATA LOCAL INFILE 'PLACES.TXT' 
INTO TABLE `places` 
LINES TERMINATED BY '\r\n' 
(alpha) 
;

DROP TABLE IF EXISTS `singlewords`;
CREATE TABLE `singlewords` LIKE `crosswordlegal`;
LOAD DATA LOCAL INFILE 'SINGLE.TXT' 
INTO TABLE `singlewords` 
LINES TERMINATED BY '\r\n' 
(alpha) 
;

DROP TABLE IF EXISTS `wordfreqenglishinternet`;
CREATE TABLE `wordfreqenglishinternet`
(
	`alphaid` INT UNSIGNED NOT NULL AUTO_INCREMENT,
	`alpha` VARCHAR(255) NOT NULL,
	`freqpct` DOUBLE PRECISION NOT NULL,
	PRIMARY KEY (`alphaid`)
) ENGINE = INNODB;
LOAD DATA LOCAL INFILE 'FREQ-INT.TXT' 
INTO TABLE `wordfreqenglishinternet` 
FIELDS TERMINATED BY ' '
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES 
(freqpct,alpha) 
;

DROP TABLE IF EXISTS `amytansubstringfrequencies`;
CREATE TABLE `amytansubstringfrequencies`
(
	-- id not alphaid to emphasize that these are substrings unrelated to words in the other lists
	`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
	`freq` INT UNSIGNED NOT NULL,
	`alpha` VARCHAR(255) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE = INNODB;
LOAD DATA LOCAL INFILE 'FICTION.TXT' 
INTO TABLE `amytansubstringfrequencies` 
FIELDS TERMINATED BY '<' 
LINES TERMINATED BY '>\r\n'
(freq,alpha) 
;

DROP TABLE IF EXISTS `kingjamessubstringfrequencies`;
CREATE TABLE `kingjamessubstringfrequencies` LIKE `amytansubstringfrequencies`;
LOAD DATA LOCAL INFILE 'KJVFREQ.TXT' 
INTO TABLE `kingjamessubstringfrequencies` 
FIELDS TERMINATED BY '<' 
LINES TERMINATED BY '>\r\n'
(freq,alpha) 
;

-- A Moby Parts of Speech table in the format of the original text file.
DROP TABLE IF EXISTS `mobypos`;
CREATE TABLE `mobypos`
(
	`alphaid` INT UNSIGNED NOT NULL AUTO_INCREMENT,
	`alpha` VARCHAR(255) NOT NULL,					-- case can be important, but probably best not to use binary collation as default.
	`pos` VARCHAR(20) NOT NULL COLLATE latin1_bin,	-- the pos field chars are case sensitive. You must use REGEXP BINARY.
	PRIMARY KEY (`alphaid`),
	INDEX (`alpha`)
) ENGINE = INNODB;
LOAD DATA LOCAL INFILE 'mobypos-cp1252.txt'			-- in this file, European words have their accented characters 
INTO TABLE `mobypos` 
FIELDS TERMINATED BY '\\' 
	ESCAPED BY ''			-- required to make the field terminator above work properly
LINES TERMINATED BY '\n'
(alpha,pos) 
;

/* 
(OPTIONAL. IF YOU DO NOT WANT TO DO THIS, COMMENT OUT THE CODE...)
ADD THE 7-BIT ASCII TRANSLITERATIONS OF ENTRIES WITH NON-ENGLISH CHARACTERS. 
FOR EXAMPLE, ICONV REPLACES FRENCH ACCENTED "E" CHARACTERS WITH A PLAIN "E".  
IF THIS FILE IS IMPORTED INTO THE TABLE, EACH OF THOSE ENTRIES WILL HAVE TWO ROWS, ONE WITH ACCENTS, 
ONE WITHOUT, WHICH ALLOWS DATABASE SEARCHES ON EITHER ONE. 
THE INPUT FILE HAS 1231 LINES. IT WAS CREATED IN LINUX WITH: 
iconv -f CP1252 -t ASCII//TRANSLIT mobypos-cp1252.txt > mobypos.ascii;
diff mobypos-cp1252.txt mobypos.ascii | grep -iP '^\> ' | ssed -Re '{s/\> (.*)/\1/}' > mobypos-ascii-additions.txt;
*/
LOAD DATA LOCAL INFILE 'mobypos-ascii-additions.txt' 
INTO TABLE `mobypos` 
FIELDS TERMINATED BY '\\' 
	ESCAPED BY ''			-- required to make the field terminator above work properly
LINES TERMINATED BY '\n'
(alpha,pos) 
;

-- A transformed version of Moby Parts of Speech. 
-- Instead of 1 entry per word, with a list of parts of speech per word (word\NV),
-- it has multiple entries for multi-pos words, with only 1 pos per entry (word\N, word\V).
-- In the source file, POS are listed in decreasing order of usage frequency. freqrank preserves that important ranking.
DROP TABLE IF EXISTS `mobypos1per`;
CREATE TABLE `mobypos1per`
(
	`alphaid` INT UNSIGNED NOT NULL AUTO_INCREMENT,
	`alpha` VARCHAR(255) NOT NULL,
	`pos` CHAR(1) NOT NULL COLLATE latin1_bin,		-- the pos field chars are case sensitive. You must use REGEXP BINARY.
	`freqrank` TINYINT UNSIGNED NOT NULL DEFAULT 0,	-- preserves significance of the pos list order from mobypos
	PRIMARY KEY (`alphaid`),
	INDEX (`alpha`),
	INDEX (`pos`)
) ENGINE = INNODB;
-- build it by extracting from mobypos
INSERT INTO mobypos1per (alpha, pos, freqrank) SELECT alpha, MID(pos,1,1), 1 FROM mobypos WHERE LENGTH(pos) >= 1;
INSERT INTO mobypos1per (alpha, pos, freqrank) SELECT alpha, MID(pos,2,1), 2 FROM mobypos WHERE LENGTH(pos) >= 2;
INSERT INTO mobypos1per (alpha, pos, freqrank) SELECT alpha, MID(pos,3,1), 3 FROM mobypos WHERE LENGTH(pos) >= 3;
INSERT INTO mobypos1per (alpha, pos, freqrank) SELECT alpha, MID(pos,4,1), 4 FROM mobypos WHERE LENGTH(pos) >= 4;
INSERT INTO mobypos1per (alpha, pos, freqrank) SELECT alpha, MID(pos,5,1), 5 FROM mobypos WHERE LENGTH(pos) >= 5;
INSERT INTO mobypos1per (alpha, pos, freqrank) SELECT alpha, MID(pos,6,1), 6 FROM mobypos WHERE LENGTH(pos) >= 6;
INSERT INTO mobypos1per (alpha, pos, freqrank) SELECT alpha, MID(pos,7,1), 7 FROM mobypos WHERE LENGTH(pos) >= 7;
INSERT INTO mobypos1per (alpha, pos, freqrank) SELECT alpha, MID(pos,8,1), 8 FROM mobypos WHERE LENGTH(pos) >= 8;
-- accuracy check: total number of parts of speech should equal total number of new records, 1 part of speech each
SELECT SUM(LENGTH(`pos`)) FROM mobypos;  
SELECT COUNT(*) FROM mobypos1per; 

-- THESAURUS SECTION.

DROP TABLE IF EXISTS `mobythesauruswordlist`;
CREATE TABLE `mobythesauruswordlist` LIKE `crosswordlegal`;

-- THIS LOADS THE TABLE mobythesauruswordlist.
LOAD DATA LOCAL INFILE 'mthesaur-phrases-uniq.txt' 
INTO TABLE `mobythesauruswordlist` 
LINES TERMINATED BY '\n' 
(alpha);


-- Create the main thesaurus table. The Perl script created the input file for this, 
-- changing the thesaurus format
-- FROM
--	root,related1,related2...\n
-- TO
--	root,root\n
--	root,related1\n
--	root,related2\n
DROP TABLE IF EXISTS `mobythesaurus`;
CREATE TABLE `mobythesaurus`
(
	`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
	`root` VARCHAR(255) NOT NULL,
	`related` VARCHAR(255) NOT NULL,
	PRIMARY KEY (`id`),
	INDEX (`root`),
	INDEX (`related`)
) ENGINE = INNODB;
LOAD DATA LOCAL INFILE 'mthesaur-split.txt' 
INTO TABLE `mobythesaurus` 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
(root,related) 
;

SHOW TABLES;

	Search the web Search this site
Copyright ©2012 Steven Whitney. Last modified Wed 11/28/2012 21:04:45 -0800.