Home arrow MySQL arrow Page 3 - Lord Of The Strings Part 1
MYSQL

Lord Of The Strings Part 1


I recently enjoyed the latest in the Lord of the Rings trilogy of movies at the cinema. I was intrigued by Tolkien’s invented languages (such as Elvish and Dwarvish) and was curious to know where the languages came from, or more precisely, which real language was the biggest influence on Tolkien for his inventions. As I have been thinking about issues of string similarity recently (see Matching Strings and Algorithms), I wondered whether I could extend my ideas of string similarity to language similarity. In other words, could I discover to which real language Tolkien’s artificial language is most similar?

Author Info:
By: Simon White
Rating: 5 stars5 stars5 stars5 stars5 stars / 55
March 15, 2004
TABLE OF CONTENTS:
  1. · Lord Of The Strings Part 1
  2. · Word Lists and Databases
  3. · Word Lists
  4. · Loading the Text Files to the DB
  5. · Running the Query

print this article
SEARCH DEVARTICLES

Lord Of The Strings Part 1 - Word Lists
(Page 3 of 5 )


At this point, I had 15 word lists of different sizes, and a total of over 1.3 million words (the wc command shows the number of lines, words and characters in each file):

$ wc *.txt
  25485   25485  259551 danish.txt
 178429  178430 1998881 dutch.txt
  56553   56553  509773 english.txt
 287698  287698 3500749 finnish.txt
 138257  138257 1524757 french.txt
 160086  160086 2060734 german.txt
  18028   18028  172943 hungarian.txt
 115506  115506  934652 japanese.txt
  77107   77107  850131 latin.txt
  61843   61843  589234 norwegian.txt
 109861  109861 1022137 polish.txt
  86061   86061  850532 spanish.txt
  18417   18417  181973 swahili.txt
  12146   12146  105192 swedish.txt
    470     470    3768 tolkien.txt
1345947 1345948 14565007 total


Storing the Word Lists in a Database
Now that the word lists had been cleaned, my next aim was to access them from a computer program. Although I could have written a program to access the word lists directly as files, I felt a database would offer considerable flexibility to query the data and analyze the results. I was also worried about the volume of data, and reasoned that the database would help in accessing and managing the word lists efficiently. I didn’t look around much when choosing a database to store the word lists — MySQL was the natural choice because it is fast, flexible and above all, free. And besides, it was already installed on my computer!

I knew I would need only a single table to store all the word lists in the database. Each row of the table could hold one word together with the language to which it belongs. However, to devise the schema precisely, I needed to find out how many characters to allow per word. A quick bash shell command against the text files told me the lengths of the words in the word lists:

 
cat *.txt|awk '{print length($0)}'|sort –n|uniq

The command first runs an awk script over the text files to get the lengths of the lines, then performs a numeric sort, and finally removes duplicate lines in the output. Using this command, I found that the longest word in the input was 57 characters, so decided to make the database column to hold the words 60 characters long.
The table for storing the words is created as follows:


CREATE TABLE words (
  word        varchar
(60),
  lang        enum
("DANISH""DUTCH""ENGLISH""FINNISH""FRENCH""GERMAN""HUNGARIAN""JAPANESE""LATIN""NORWEGIAN""POLISH""SPANISH""SWAHILI""SWEDISH""TOLKIEN"),
  word_id     int
(10)     NOT NULL auto_increment,
  primary key 
(word_id),
  index lang_i 
(lang),
  index word_i 
(word)
  
);


blog comments powered by Disqus
MYSQL ARTICLES

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 – More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials