SQL for managing wordlists

A long time ago I started a notebook where I would record passages from books. Most of them are now in the quotes database. I also started collecting interesting words. The first word whose definition I recorded was fatalism: belief that everything is controlled by fat. The first serious words were bellicose: warlike and ameliorate: to make or desire to be better, improve. There are now 1896 interesting words in the database, along with examples of usage.

As part of an app that we were making we assigned a difficulty level to words. There are over 16,000 words that have a difficulty level assigned to them. The code to assign levels to is straightforward since the tables are in the same database.


UPDATE favorite_words, words_by_level 
SET favorite_words.level = words_by_level.level
WHERE favorite_words.word = words_by_level.word

Even though only one table is being updated, you need to mention both in the UPDATE section of the code.

I have another project where I am collecting words and quotes from Nero Wolfe stories. In that case I want to make sure that every word in that table has a definition in the favorite words table. I’m running the code in the Wolfe database and the favorite word table is in a different database so I need to make sure that I prefix the tables in the other database with the database name.


SELECT NWquote.word FROM NWquote 
LEFT JOIN words.favorite_words 
ON words.favorite_words.word = NWquote.word 
WHERE words.favorite_words.word IS NULL

I looked for words in my Wolfe database that did not have a level in my favorite words database and manually updated the level in the favorite words database.


SELECT DISTINCT NWquote.word FROM NWquote
LEFT JOIN words.favorite_words
ON words.favorite_words.word = NWquote.word
WHERE words.favorite_words.level = 0
ORDER BY NWquote.word

Surprisingly, around two hundred of my favorite words still did not have a difficulty level assigned so after manually checking them I assigned the rest to level 6.


UPDATE words.favorite_words, NWdatabase.NWquote
SET words.favorite_words.level = 6 
WHERE words.favorite_words.word = NWdatabase.NWquote.word 
AND words.favorite_words.level = 0

Sorting columnar data in BBEdit

I had a list of books in a MariaDB table that I wanted to redo by date. There may be a way to export selected fields and sorted data from MariDB using PHPMyAdmin, but I don’t know the trick.

But it is possible, in some cases, to sort data by columns in BBEdit if the data cooperates. In this case it did.


(1, 'Fer-de-Lance', NULL, 1934, '553385453', 
(2, 'The League of Frightened Men', NULL, 1935, '553385453', 
(3, 'The Rubber Band', NULL, 1936, '553763091', 
(9, 'The Silent Speaker', NULL, 1946, '553234978', 
(10, 'Too Many Women', NULL, 1947, '553250663', 
...
(34, 'Black Orchids', NULL, 1942, '0553257196',
(35, 'Not Quite Dead Enough', NULL, 1944, '0553261096',

All I need to do was figure out a grep pattern that would match just the date field.
The first thing I did was search for the field I wanted to sort by. It took some experimenting but the code used to find the field I was looking for is:

NULL, \d{4}

where \d means look for digits and {4} tells how many.

Sort search example
Putting that code into the “Sort using pattern” sort field results in lines sorted by date. Alternatively,


\d{4},

also works.

Then its a simple matter of stripping off the ID field and renumbering.

That word, it does not mean what you think it means.

I’ve noticed a lot of spam and tweets lately that are grammatically correct, but do not quite convey the message the writer thinks they do. This post is a collection of my favorites—it will grow over time.

I work as a professional Web Designer / Developer in a reputed firm.

reputed
1. Accorded a reputation.
2. Supposed or assumed to be true.
3. Generally believed to exist, but that is not the case.

Pretty sure they meant reputable, but who knows.

Blood work

I’ve never been one for getting annual physicals but they are covered by Medicare and when they called me to schedule mine, I figured why not. In preparation for the physical I’m getting some blood work done. They recommended a PSA test, even though they aren’t a particularly reliable indicator of prostate cancer. Cholesterol of course. I suggested a B12 test since I’m mostly vegan and probably don’t get a lot of B12 in the foods I eat. I thought it was amusing that the reason given on the order is:
Persistent vegetative state.