Wordle

wordle

Wordle! It’s all the rage these days. This game, which only had a few hundred daily players in November 2021, has ballooned in to a hit sensation in the last 3 months. Wordle is a very simple word game in which players have six chances to guess the five letter word of the day. With each successive word the board tells you if each This game is unique in that there is only one word per day, limiting the amount of time and effort a player can devote to the game per day.

A story published by The New York Times gives a good background of how this game came to be. Worlde was created by a software engineer named Josh Wardle (a spin on his own name) during the dull days of the Covid 19 lock down. Wardle and his partner enjoy playing other daily word games so he decided to create a game for the two of them. After some time sharing among his family he decided to take the game public in October of 2021.

Wordle has also invited analysis of the best starting word for the game. One linguist said that irate was the best starting word. This inspired tiktok user crvlwanek to do a more in depth analysis of all 5 letter words. This was done by assigning each letter a score given it’s use in other words and summing the score up for each word. From this, he determined that the word later was indeed the best word. Crvlwanek determined that xylyl was the worst starting word. This analysis concluded irate was the 5th best word.

Indeed, the game has become so popular that The New York Times has purchased the game for an undisclosed 7 figure amount. The Times plans to add the game to their other word games. No word yet if a subscription to the paper will be involved to play the game going forward.

I too have joined the wordle bandwagon in the last few weeks. It is enjoyable because I can put a word take time to reflect on the next guess. Sometimes I minimize the window and come back to it a short time later. Unfortunately for me, word games have never been my strong suite. Recalling words out of thin air is not something I excel at. I can recall words when I needed in conversation. But, recalling a series of words given a few clues has never been my thing.

But I AM good at using computers to store and retrieving large amounts of data. Given that skill I created a tool to help solve (ie: cheat) wordle puzzles. Yeah, I know there are dozens of word finders out there already for crossword puzzles and the like. But why not add one more to the mix.

Using my tool, I’ve found that even if later contains the most common set of letters, it leads to ambiguity. Meaning when you enter later in wordle and feed the results into the word helped, you’ll get hundreds of like words.

Anyway, you can find my word finding tool here. The source of English words is from dwyl on github. Enjoy!

Customizing SQL Server Management Studio

SQL Server Management Studio Header

I’ve noticed my posts on how to show line numbers and configure keyboard shortcuts in Oracle SQL Developer seem to get a decent amount of search traffic. So, to expand on the theme I figured it might be valuable to share the same details on customizing SQL Server Management Studio environments. For this, I will keep these details in a single article.

In these examples I am using SQL Server Management Studio version 18.8. This version is freely available to download from Microsoft here. However, these setting have been in the same location of SSMS since I started using MS SQL all these years ago (SQL 2005 Anyone?).

Application Setting

To access the application options in SSMS, navigate to the Tools menu and select Options. Many of the options for customizing SQL Server Management Studio will be found here.

SQL Server Management Studio Options

Showing Line Numbers

Once in the options screen, in the list on the left navigate to Text Editor → All Languages → General. Toggle the Line Numbers option to enable or disable.

SQL Server Management Studio Line Numbers

Keyboard Shortcuts

While in the options screen, in the list on the left navigate to Environment → Keyboard → Query Shortcuts. In this screen you can add various keyboard shortcuts to run queries or stored procedures using hotkeys.

By default, SSMS comes with Alt+F1, Ctrl+1 and Ctrl+2 mapped to sp_help, sp_who and sp_lock respectively. I like to add a mapping to Ctrl+F1 for sp_helptext which will retrieve the definition of the highlighted stored procedure or function.

The great thing about keyboard shortcuts is you can highlight text and hit a given hotkey and the highlighted text will be input for the stored procedure/function. For example, sp_helptext requires an object name as input. To query sp_helptext the syntax would look like this: sp_helptext [ @objname = ] 'name'. With the keyboard shortcut, you only need to highlight the text in the editor and hit the hotkey (Ctrl+F1 in this case).

SQL Server Management Studio Query Shortcuts

In Short

As you can see in these menus, there are several changes you can make when customizing SQL Server Management Studio for your needs. When working in a new environment these two changes are the ones I make immediately. Enjoy!

Easy Web Based Reporting Tool

Overview

While redeploying this site I came across the need to have a simple reporting tool to display SQL queries. Previously I had a SQL utility open on my desktop and would connect and run queries as needed. But I need to be on my home network or open open up the firewall on the server. Both solutions are not ideal so back to the drawing board. Instead it is easy enough to create a webpage using PHP and exposure the queries I use the most.

Solution

Luckily I had created such a tool long ago when I had worked on my Twitter scraper in PHP. At the time I had the exact same requirements for viewing data quickly and easily. So I was able to put this together pretty quick. I figured since the tool has multiple uses on any such platform it would be more appropriate to maintain it as a stand alone package.

The tool is essentially a single PHP page backed by a MySQL data table publishes the results of SQL views. Essentially each row in the provided SQL table represents a report. The details of the table contain the name, display name, url, sort order and active flag. The name is used by the PHP page to lookup the report definition in the SQL table. The display name is what the PHP prints in the list of available reports. The URL is what is drawn in the HTML code of the PHP page. This should be a reference to the PHP page with the report name parameter appended, even though it could link to any page desired. The sort order determined the order in which available reports are displayed and the active flag determines if the given report is displayed in the list of reports.

The PHP page will hit the MySQL server to determine if the view exists when a report is called,. The SQL object defined must be a view. If it does exist it will pull the column names from the information schema and build a table header with those names. It will then select all rows from the view and draw the HTML table.

The report pulls the entire view contents so it’s best to limit the number rows returned for performance reasons. There is a CSS file which defines some basic attributes such as font type and size plus background color.

Not much more to it than that. You can find this on my Github page.

Golden Tee Holes In One

Golden Tee has become a favorite parlor game of mine. It’s quite a bit of fun to hang out and play (virtual) golf with good people. In my few years of playing the game regularly I’ve gotten much better. I’ve also managed to hit a few holes In one. Check out the links to each golden tee holes in one:

The Goods

June 18, 2019:
Number 1

February 25, 2020:
Number 2

March 13 2020: This is by far my favorite and most unexpected.
Number 3

March 16 2020: This happened during a solo round just days before Covid-19 Lockdown began. This was also just 3 days after the previous ace.
Number 4

Get Yours

Get out of the house and go play you a round of Golden Tee. Find a location with the game here.

Why We Sleep by Dr. Matthew Walker

This book by Dr. Matthew Walker covers most aspects of sleep over four sections. The first, this thing called sleep, covers the details of what sleep is and the evolutionary origins of sleep and the different stages of sleep. It also details how different animals sleep and the amounts of time dedicated to each stage plus the cycles of sleep within different animals. Section two, why you should sleep, goes into additional details of why we sleep and the various benefits of sleep on our physical, mental, emotional and philological health. The third section is how and why we dream. The topic of this section is self explanatory. The final section, from sleeping pills to society transformed, discusses sleep and the issues surrounding sleep in modern Western society.

It turns out all animals sleep or rest in one form or another. It comes as non-trivial and not something you would think about. The extended lack of sleep for any animal can be fatal. This suggests that sleep is an essential part of the functioning of animals and should not be neglected. It also indicates sleep is one of the foundational elements of animals evolutionary history and sets us apart from other living beings on earth.

When it comes to mammals, those that live in the sea sleep with one half of their brain at a time. This allows them to stay conscious while resting so they can stay alert and keep swimming. They still rest for 8 hours a day though, 4 hours with one part of their brain and 4 hour with the other.

There are 4 different stages of sleep but they can be grouped into two general categories. One is non-REM sleep the other is REM sleep (very creative names, I know). There are many differences between NREM and REM sleep and each stage performs different functions. NREM sleep is when the brain waves move slower. It also brings the feeling of restfulness to you in the morning. REM sleep is the stage of sleep when you dream. It is also closer to wakefulness in terms of when it occurs. NREM and REM sleep are responsible for different nerve activities within the brain. Combined these stages are responsible mental health and memory function.

One interesting difference mentioned between NREM and REM sleep is the limbic system is disabled during REM sleep causing people not to act out their dreams. However, the limbic system is not disabled during NREM sleep. This means that sleepwalking occurs during NREM sleep. It is also the reason why sleepwalkers do not remember their activities during the time they are sleep walking.

Over the course of one’s lifetime the need for each stage of sleep and indeed the amount of time spent in each stage of sleep changes. Babies and small children get more deep NREM sleep and less REM sleep. This makes sense because younger children are growing physically much more during the early years of life. As people get older the balance of sleep cycles shifts towards more REM sleep.

Dr. Walker presents evidence of mental issues later in life due to lack of sleep. He makes the link to Alzheimer’s disease being caused in part by lack of sleep throughout life. One anecdotal example of this is Margaret Thatcher and Ronald Regan who both often bragged about only sleeping 4 hours a night. Ultimately they both suffered from Alzheimer’s which may have been avoided had they taken sleep more seriously.

Why We Sleep by Dr. Matthew Walker is fascinating and full of interesting information as to why sleep is important and should not be considered as part of a healthy lifestyle. I initially heard Dr. Walker and the discussion of these topics on The Joe Rogan Experience which you can find on YouTube, Spotify or most other podcast outlets. This book is also featured on Bill Gates’ blog Gates Notes.

Visit Dr. Walker’s website here. Also, you can read a much more abbreviated article of the difference between NREM and REM sleep here.

why we sleep
Why We Sleep

Oracle DUAL Table

My expertise with databases was learned with Microsoft SQL Server. Over the years I learned how to use other DBMS, but Oracle is the only other enterprise level system I have attempted to learn. Although most mainstream database systems follow ANSI standards, there are several nuances to each one which make can make learning the next one challenging. In my experience, going from MS SQL to Oracle proved to be a steep learning curve. Properly assigning values to variables and using the Oracle DUAL Table was part of that learning curve.

Variables in Oracle vs. SQL Server

One area that is completely different is working with and assigning variables when writing SQL “programs” (TSQL in MS SQL and PL/SQL in Oracle). Using TSQL you can declare a variable using any name you like prefixed with an “@” and followed by a datatype. In PL/SQL you do not prefix variable names with any special characters. In this example I show how to declare and assign a string variable with a value.

Also in Oracle you cannot assign a string directly to a variable like you can in MS SQL. You must “select” the value from the DUAL table which is an automatically generated table in Oracle. More details on the DUAL table can be found here.

In SQL Server
DECLARE @Something VARCHAR(500)
SET @Something = ‘Some Random String’
OR
SELECT @Something = ‘Some Random String’

In Oracle
SpacePath varchar(500)
SELECT ‘Some Random String’ INTO SpacePath FROM dual;

Side Note

I recently revisited old unpublished blog posts and this was one of them. This was initially outlined in September 2017 using Oracle 12c. In January 2021 the details were elaborated and published.

Tricky SQL Test: Part 3

This is Part 3 of the three part series detailing the ‘tricky SQL test’ I took to get a potential job.  Read Part 1 of the series here and Part 2 here.

The Test

Given a table events with the following structure:

create table events (
  event_type integer not null,
  value integer not null,
  time timestamp not null,
  unique (event_type, time)
):

insert into events (event_type, value, time) values (2, 5, '2015-05-09 12:42:00');
insert into events (event_type, value, time) values (4, -42, '2015-05-09 13:19:57');
insert into events (event_type, value, time) values (2, 2, '2015-05-09 14:48:30');
insert into events (event_type, value, time) values (2, 7, '2015-05-09 12:54:39');
insert into events (event_type, value, time) values (3, 16, '2015-05-09 13:19:57');
insert into events (event_type, value, time) values (3, 20, '2015-05-09 15:01:09');

Write an SQL query that, for each event_type that has been registered more than once, returns the difference between the latest (i.e. the most recent in terms of time) and the second latest value.  the table should be orderred by event_type (in ascending order).

For example, given the following data:

event_type  | value     | time
------------+-----------+--------------------
2           | 5         | 2015-05-09 12:42:00
4           | -42       | 2015-05-09 13:19:57
2           | 2         | 2015-05-09 14:48:30
2           | 7         | 2015-05-09 12:54:39
3           | 16        | 2015-05-09 13:19:57
3           | 20        | 2015-05-09 15:01:09

Your query should return the follow rowset:

 event_type | value     
------------+-----------
2           | -5
3           | 4

For the event_type 2, the latest value is 2 and the second latest value is 7, so the difference between them is -5.

The names of the columns in the rowset don’t matter, but their order does.

Welcome Again

Welcome again! The Travisty blog has officially relaunched. I started this blog back in 2017 and wrote a few posts within the first year but then lost interest and moved on to other things. I also shut the server hosting these sites. But, I recently started this server back up to host another site. After some tinkering I realized I could host multiple sites using the same IP address. Since this site was already configured it took minimal effort to get it back online. So we’re back for 2021, BAM! This year is already shaping up to be icing on the cake known as 2020.

So far I am already working on a number of posts and topics. In addition to the topics I set out to write about originally I will also publish “book reports” in which I will document the books I am reading. I may also look to do some updates to the site, though I do quite like the 2017 look and feel that I left off with. Feels so retro. So that’s that. Perhaps we’ll keep this going a bit longer this time around. Welcome and enjoy!

Tricky SQL Test: Part 2

This is Part 2 of the three part series detailing the ‘tricky SQL test’ I took to get a potential job.  Read Part 1 of the series here.  This one is much easier than Part 1.  I got most of the credit for this one, but I couldn’t figure out the group by to limit the number previous results.

The Test

Given a table events with the follow structure:

CREATE TABLE camera (
    cameraId INT NOT NULL,
    warningType INT NOT NULL,
    warningLevel INT NOT NULL,
    warningTime DATETIME NOT NULL,
    UNIQUE INDEX warningTime (warningTime)
);

insert into camera (cameraId, warningType, warningLevel, warningTime) values (2, 2, 8, '2017-09-13 13:42:00');
insert into camera (cameraId, warningType, warningLevel, warningTime) values (2, 4, -46, '2017-09-13 14:19:57');
insert into camera (cameraId, warningType, warningLevel, warningTime) values (2, 2, 7, '2017-09-13 15:48:30');
insert into camera (cameraId, warningType, warningLevel, warningTime) values (3, 2, 3, '2017-09-13 13:54:39');
insert into camera (cameraId, warningType, warningLevel, warningTime) values (2, 3, 38, '2017-09-13 14:32:36');

Write an SQL query that, for each camera and warning type (cameraId, warningType), returns the most recent value (in terms of time).  the table should be ordered by cameraId (in ascending order), and warningType (in ascending order).

For example, given the following data:

cameraId   | warningType | warningLevel | warningTime
-----------+-------------+--------------+--------------------
2          | 2           | 5            | 2017-09-13 13:42:00
2          | 4           | -46          | 2017-09-13 14:19:57
2          | 2           | 7            | 2017-09-13 15:48:30
3          | 2           | 3            | 2017-09-13 13:54:39
2          | 3           | 38           | 2017-09-13 14:32:36

Your query would return:

cameraId | warningType | warningLevel
---------+-------------+--------------
2        | 2           | 7
2        | 3           | 38
2        | 4           | -46
3        | 2           | 3

The names of the columns in the rowset don’t matter, but their order does.