I've finally gotten around to downloading those US state department cables that Wikileaks acquired. You can acquire them at Cryptome:
John Young would probably appreciate it if you can find another way of acquiring the file so he doesn't have to pay the bandwidth bill. I'm not linking directly so he doesn't get hammered by bots following the link.
Storing the cables
The cables will need to be imported into a database before they can be read. I am using MySQL from the XAMPP distribution.
Create a final table and an all-text table for importing into. (Attempting to import directly into the date field will zero out the dates).
create table Cables (
id int PRIMARY KEY,
referenceIDs text, -- really a pipe-separated array
data mediumtext -- some larger than 65536 chars
create table Cables2 (
Load the data using mysql's LOAD DATA INFILE, which needs some help
to learn to read multi-line CSV correctly.
load data infile 'c:\\cables.csv' into table cables2 fields ENCLOSED BY '"' escaped by '\\' terminated by ',';
There should be zero warnings. If you see warnings, you can use
"show warnings" to see at what record number the import failed.
Copy from the staging table to the final table. This will take about
insert into cables (select
str_to_date(datetime, "%m/%d/%Y %k:%i"),
Then create an index on the dates to speed up future searches. This takes two minutes on my computer.
create index idx_date on cables (date);
Building an index for text searches
You will want to search the cables for a specific subject. Two methods are
MySQL has fulltext indexing. I found it to be too slow.
Creating the index took 25 minutes:
create fulltext index idx_text on cables (header, data);
Fulltext index queries using MATCH AGAINST took 1-2 minutes to run.
select count(*) from cables where match (header, data) against ('Sudan');
select count(*) from cables where match (header, data) against ('Sudan') OR match (header,data) against ('Sudanese');
I found the fulltext index to be slower than sequentially searching the table for "data like '%SUDAN%'". YMMV.
Custom word cache
I built my own index using separate tables for all search terms and for the connections between the search terms and the cables.
create table words(
wordID int auto_increment NOT NULL,
word varchar(64) NOT NULL,
CONSTRAINT cx_word_uniqueness UNIQUE (wordID, word)
create table idx_words(
wordID int NOT NULL REFERENCES words(id),
cableID int NOT NULL REFERENCES cables(id),
INDEX idx_word_match (wordID, cableID),
CONSTRAINT cx_words UNIQUE (wordID, cableID)
The custom index can be seeded with two queries:
INSERT INTO words (word) VALUES ('SUDAN');
INSERT INTO idx_words (
SELECT words.wordID, cables.id FROM words, cables
WHERE words.word = 'SUDAN' && upper(cables.data) LIKE '%SUDAN%'
It takes few minutes to seed each word, but searches are instantaneous with a small number of seeded words. I have not tested this with a large number of seeded words.
Other text search methods?
If you know of a better search method, please mention it in comments.
Other search indexes?
The cables have additional information that a sufficiently intelligent program can pull out of the data field and add to the database metadata. If someone has already done this work, please mention it in comments.
Reading the cables
You will need a program to pull the data out of the database in a form that you can read. I wrote a quick and dirty PHP program to display search results as HTML.
Cables before circa 2000 were in ALL CAPS and are difficult to read. A program could potentially convert the text to normal mixed case, although it would need to be able to recognize acronyms and peoples' names. If someone has already done this work, please mention it in comments.
A program could potentially recognize key words such as peoples' names, and link these words to other sources of information such as History Commons and Wikipedia. If someone has already done this work, please mention it in comments.