MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for February, 2024

Add PostGIS to PostgreSQL

without comments

The following blog post shows you how to add PostGIS and PgRouting to your existing install of PostgeSQL 14 on the Ubuntu Desktop, Version 22.0.4. This blog post relies on information in this earlier Install and Configure PostgreSQL on Ubuntu post. Generalized documentation on PostGIS exists at this URL.

You install the postgis libraries:

sudo apt install -y postgis

You install the postgresql-14-pgrouting libraries:

sudo apt install -y postgresql-14-pgrouting

You should also install ogr2ogr program, which is a command-line utility for converting data between GIS data formats, including common file formats and common spatial databases. You install the ogr2ogr libraries:

sudo apt install -y gdal-bin

Verify the installation by using the which utility, like

which -a ogr2ogr

It should return:

/usr/bin/ogr2ogr

You can qualify the installed PostGIS packages with the following command:

dpkg -l | grep -i postgis

It should display:

ii  postgis                                    3.2.0+dfsg-1ubuntu1                     amd64        Geographic objects support for PostgreSQL
ii  postgis-doc                                3.2.0+dfsg-1ubuntu1                     all          Geographic objects support for PostgreSQL -- documentation
ii  postgresql-14-pgrouting                    3.3.0-2                                 amd64        Routing functionality support for PostgreSQL/PostGIS
ii  postgresql-14-pgrouting-scripts            3.3.0-2                                 all          Routing functionality support for PostgreSQL/PostGIS - SQL scripts
ii  postgresql-14-postgis-3                    3.2.0+dfsg-1ubuntu1                     amd64        Geographic objects support for PostgreSQL 14
ii  postgresql-14-postgis-3-scripts            3.2.0+dfsg-1ubuntu1                     all          Geographic objects support for PostgreSQL 14 -- SQL scripts

Connect as the postgres user by becoming the root user with this command:

sudo sh

Then, assume the role of the postgres user with this command:

su - postgres

Connect to the PostgreSQL database as the privileged postgres owner/user:

psql postgres

You will see the following prompt after connecting to the PostgreSQL database:

postgres@student-virtual-machine:~$ psql
psql (14.10 (Ubuntu 14.10-0ubuntu0.22.04.1))
Type "help" for help.
 
postgres=#

As the the privileged postgres owner/user issue the following commands to create the gisdb database and set a new search path for it:

CREATE DATABASE gisdb;
ALTER DATABASE gisdb SET search_path=public,postgis,contrib,tiger;

Connect to the gisdb database:

\connect gisdb

You are now connected to database gisdb as the postgres user. You change to the postgis schema, and create the following extensions in this schema.

CREATE SCHEMA postgis;
CREATE EXTENSION postgis SCHEMA postgis;
CREATE EXTENSION postgis_raster SCHEMA postgis;
CREATE EXTENSION fuzzystrmatch SCHEMA postgis;
CREATE EXTENSION address_standardizer_data_us SCHEMA postgis;

You query the modified catalog with this query:

SELECT n.nspname AS "Name"
,      pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM   pg_catalog.pg_namespace n
WHERE  n.nspname !~ '^pg_'
AND n.nspname <> 'information_schema'
ORDER BY 1;

It should return:

  Name   |  Owner   
---------+----------
 postgis | postgres
 public  | postgres
(2 rows)

You must assign the postgis_tiger_geocoder and postgis_topology without a schema assignment. The
postgis_tiger_eeocoder must be assigned by default to the tiger schema, and the postgis_topology schema.

CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION postgis_topology;

You reuse the same above referenced query to see the modified catalog with this query:

It should return:

    Name    |  Owner   
------------+----------
 postgis    | postgres
 public     | postgres
 tiger      | postgres
 tiger_data | postgres
 topology   | postgres
(5 rows)

Connect as the gisdb database with this command:

\connect gisdb

You can see the active PostGIS extension with this command:

\dx postgis

It shows:

                               List of installed extensions
  Name   | Version | Schema  |                        Description                         
---------+---------+---------+------------------------------------------------------------
 postgis | 3.2.0   | postgis | PostGIS geometry and geography spatial types and functions
(1 row)

Now, you can use this query:

SELECT postgis_full_version();

to discover what PostGIS version is installed:

                                                                       postgis_full_version                                                                        
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="3.2.0 c3e3cc0" [EXTENSION] PGSQL="140" GEOS="3.10.2-CAPI-1.16.0" PROJ="8.2.1" LIBXML="2.9.12" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"
(1 row)

Connect back to as the privileged postgres owner/user with this command:

\connect postgres

Next, check the available databases with this command:

\l

It should display the following:

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 gisdb     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 videodb   | student  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/student          +
           |          |          |             |             | student=CTc/student  +
           |          |          |             |             | dba=CTc/student
(5 rows)

As the privileged postgres owner/user make the following grants with these commands:

GRANT TEMPORARY, CONNECT ON DATABASE gisdb TO PUBLIC;
GRANT ALL PRIVILEGES ON DATABASE gisdb TO postgres;
GRANT ALL PRIVILEGES ON DATABASE gisdb TO dba;

After making the grants, check the available databases access with this \l command:

\l

It should display the following:

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 gisdb     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | dba=CTc/postgres
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 videodb   | student  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/student          +
           |          |          |             |             | student=CTc/student  +
           |          |          |             |             | dba=CTc/student
(5 rows)

At this point, you can exit psql, the postgres user’s account, and the root user’s account. This should return you to your sudoer account, which in my case is the student user.

Connect to the gisdb with the following command-line interface command:

psql -U student -W -d gisdb

You can create the following, as per instructions in PostGIS Chapter 9 instructions:

-- Conditionally drop table.
DROP TABLE IF EXISTS geometries;
 
-- Create table with geometry column in table.
CREATE TABLE geometries
( geometries_id  INT
, name           VARCHAR
, geometry_obj   GEOMETRY);
 
 
INSERT INTO geometries
( name
, geometry_obj )
VALUES
 ('Point', 'POINT(0 0)')
,('Linestring', 'LINESTRING(0 0, 1 1, 2 1, 2 2)')
,('Polygon', 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))')
,('PolygonWithHole', 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))')
,('Collection', 'GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))');
 
SELECT name
,      ST_AsText(geometry_obj)
FROM   geometries;

Unfortunately, it raises the following error when attempting to create the geometries table:

psql:/home/student/Code/postgis/geometry.sql:7: ERROR:  type "geometry" does not exist
LINE 4: , geometry_obj   GEOMETRY);

As always, I hope the solutions presented helps move forward implementations of the technology. You can also find an excellent tutorial to learning PostGIS in the Introduction to PostGIS tutorial.

Written by maclochlainn

February 15th, 2024 at 10:57 pm

Sqlite on Ubuntu

without comments

We decided to include some existing Sqlite databases in our AWS Ubuntu learning lab because they’re used by the Data Science courses (specifically, DS 250). Installing Sqlite is quite simple:

sudo apt install -y sqlite

You can check the install by using the which utility, like:

which -a sqlite3

On Ubuntu, it should return:

/usr/bin/sqlite3

There is a friendly help document online that can provide insight in how to use Sqlite. You can create a new student.db database with the following syntax from the Ubuntu CLI (Command-Line Interface):

sqlite3 student.db

It would return the following:

SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" FOR usage hints.
sqlite>

A little warning about the simple example and how it opens only a transient in-memory database. If you want a persistent database, you must open sqlite3 without any arguments. Then, you must use the .open method to open a persistent student.db database create a file in the relative directory path where you launched sqlite3 executable. You can read more about persistent in Sqlite in the documentation.

.open student.db

The alternative opens the student.db file in a fully qualified path:

.open /home/student/Code/sqlite/db/student.db

If you type .databases at the sqlite> prompt it would return:

sqlite> .databases
main: /home/student/Code/sqlite/db/student.db r/w

Let’s create a script file that creates two tables, a foreign key reference from one of the tables to the other, and some data with the following create_sample.sql script:

-- Drop knight table if exists.
DROP TABLE IF EXISTS knight;
 
-- Drop kingdom table if exists.
DROP TABLE IF EXISTS kingdom;
 
-- Create normalized table kingdom
CREATE TABLE kingdom
( kingdom_id    INTEGER PRIMARY KEY
, kingdom_name  VARCHAR(20)
, population    INTEGER
, book          VARCHAR(40));
 
-- Insert kingdom into table.
INSERT INTO kingdom
( kingdom_id
, kingdom_name
, population
, book )
VALUES
 ( 1, 'Narnia', 42100, 'Prince Caspian' )
,( 2, 'Narnia', 77600, 'The Lion, The Witch and The Wardrobe' )
,( 3, 'Camelot', 15200, 'The Once and Future King' );
 
-- Create normalized knight table.
CREATE TABLE knight
( knight_id             INTEGER PRIMARY KEY
, knight_name           VARCHAR(22)
, kingdom_allegiance_id INTEGER
, allegiance_start_date text
, allegiance_end_date   text
, book                  VARCHAR(40)
, FOREIGN KEY (kingdom_allegiance_id)
  REFERENCES kingdom(kingdom_id));
 
-- Insert knights into table.  
INSERT INTO knight
( knight_id
, knight_name
, kingdom_allegiance_id
, allegiance_start_date
, allegiance_end_date
, book )
VALUES
 (  1, 'Peter the Magnificent', 2, '1272-03-20', '1292-06-19', 'The Lion, The Witch and The Wardrobe' )
,(  2, 'Edmund the Just', 2, '1272-03-20', '1292-06-19', 'The Lion, The Witch and The Wardrobe' )
,(  3, 'Susan the Gentle', 2, '1272-03-20', '1292-06-19', 'The Lion, The Witch and The Wardrobe' )
,(  4, 'Lucy the Valiant', 2, '1272-03-20', '1292-06-19', 'The Lion, The Witch and The Wardrobe' )
,(  5, 'Peter the Magnificent',	1, '1531-04-12', '1328-05-31', 'Prince Caspian' )
,(  6, 'Edmund the Just', 1, '1531-04-12', '1328-05-31', 'Prince Caspian' )
,(  7, 'Susan the Gentle', 1, '1531-04-12', '1328-05-31', 'Prince Caspian' )
,(  8, 'Lucy the Valiant', 1, '1531-04-12', '1328-05-31', 'Prince Caspian' )
,(  9, 'King Arthur', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' )
,( 10, 'Sir Lionel', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' )
,( 11, 'Sir Bors', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' )
,( 12, 'Sir Bors', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' )
,( 13, 'Sir Galahad', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' )
,( 14, 'Sir Gawain', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' )
,( 15, 'Sir Tristram', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' )
,( 16, 'Sir Percival', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' )
,( 17, 'Sir Lancelot', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' );

You can run the create_sample.sql script with the following syntax using an absolute path:

sqlite> .read /home/student/Code/sqlite/create_tables.sql

Then, you can write a query like this to retrieve the data from two tables:

SELECT k.kingdom_name
,      kn.knight_name
FROM   kingdom k INNER JOIN knight kn
ON     k.kingdom_id = kn.kingdom_allegiance_id
WHERE  k.book = 'Prince Caspian';

It will return the following:

Narnia|Peter the Magnificent
Narnia|Edmund the Just
Narnia|Susan the Gentle
Narnia|Lucy the Valiant

You can exit sqlite3 by entering .quit or Control+D (the system End-Of-File character). If you can determine whether you have a transient or persistent student.db database file with the long list (ll) command.

The following command:

ll /home/student/Code/sqlite/db/student.db

should return the following:

-rw-r--r-- 1 student student 12288 Feb  9 23:01 /home/student/Code/sqlite/db/student.db

If the command returned a 0 sized student.db database file, you created a transient Sqlite table. You’ll need to redo the creation of the student.db database file with the .open command as qualified above.

If you want to detach a database from your active Sqlite session, you can issue the following command to remove it:

sqlite> DETACH DATABASE student.db

Let’s jazz it up a bit with some Python. The first example verifies the ODBC driver’s ability to connect to Sqlite. Please note that it returns the same result for a transient and persistent database file. You can refer to the following documentation for Python examples.

#!/usr/bin/python
 
# Import sqlite3 ODBC library.
import sqlite3
 
try:
  # Open a connection to the student.db database
  db = sqlite3.connect('/home/student/Code/sqlite/db/student.db')
 
  # Print a string to say you've connected to the student.db database.
  print("Sqlite database connection success.")
 
except sqlite3.Error as e:
  print('SQLite error: %s' % (' '.join(e.args)))
  print("Exception class is: ", e.__class__)
  print('SQLite traceback: ')
  exc_type, exc_value, exc_tb = sys.exc_info()
  print(traceback.format_exception(exc_type, exc_value, exc_tb))
  sys.exit(1)
finally:
  # Close the connection when it is open.
  if db:
    db.close()

You can run the sqlite_connection.py script with the following syntax from its local directory:

Sqlite database connection success.

Assuming you have created a persistent Sqlite database, as qualified above with the .open command and fully qualified file name. A fully qualified file name as a path from a Linux mount point to the file.

You must use the fully qualified file name for a persistent Sqlite student.db database as the database parameter for the sqlite3.connect() method, as shown on line #9 of the sqlite_query.py program below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
#!/usr/bin/python
 
# Import sys library.
import sqlite3
 
try:
  # Open a connection to a persistent database, which should use
  # a fully qualified file name, but may use a relative file
  # name when the Python code is in the same directory as a 
  # persistent student.db sqlite3 database.
  db = sqlite3.connect('/home/student/Code/sqlite/db/student.db')
 
  # Create a cursor.
  cursor = db.cursor()
 
  # Define a query.
  query = "SELECT k.kingdom_name "                           \
          ",      kn.knight_name "                           \
          "FROM   kingdom k INNER JOIN knight kn "           \
          "ON     k.kingdom_id = kn.kingdom_allegiance_id "  \
          "WHERE  k.book = 'Prince Caspian'"
 
  # Execute the cursor with the query.
  cursor.execute( query )
 
  # Display the rows returned by the query.
  for (kingdom_name, knight_name) in cursor:
    print('{0} has {1}'.format( kingdom_name.title(), knight_name.title()))
 
except sqlite3.Error as e:
  print('SQLite error: %s' % (' '.join(e.args)))
  print("Exception class is: ", e.__class__)
  print('SQLite traceback: ')
  exc_type, exc_value, exc_tb = sys.exc_info()
  print(traceback.format_exception(exc_type, exc_value, exc_tb))
  sys.exit(1)
finally:
  # Close the connection when it is open.
  if db:
    db.close()

As always, I hope this helps those trying to get up and running with Sqlite.

Written by maclochlainn

February 9th, 2024 at 8:47 pm

Git Hub Desktop on Ubuntu

without comments

I need to install Git Hub Desktop on Ubuntu. It was quite straightforward:

bash -c "$(curl -fsSL https://raw.githubusercontent.com/kontr0x/github-desktop-install/main/installGitHubDesktop.sh)"

You can find it in the 9-dot menu’s second page or launch it from the command line, like:

github-desktop

Either will launch the GitHub Desktop, as shown below:


As always, I hope this helps those looking for concise and complete free answer on installing GitHub Desktop.

Written by maclochlainn

February 9th, 2024 at 6:40 pm

Posted in github,Linux,Ubuntu

Tagged with

Ruby+PostgreSQL on Ubuntu

without comments

This extends the earlier post on installing and configuring Ruby 3.3.0 on Ubuntu 22.0.4. Please refer to that earlier post to install Ruby. This post shows you how to install the necessary libraries and Ruby Gems for PostgreSQL.

You need to install the libra-dev package, as shown:

sudo apt install postgresql libpq-dev

Next, you need to install the PG Gem:

gem install pg

You can now write a postgres_version.rb program to verify a connection to the PostgreSQL database, like:

# Include Ruby Gem libraries.
require 'rubygems'
require 'pg'
 
# Begin block.
begin
  # Create a new connection resource.
  db = PG::connect( 'localhost', 5432, '', '', 'videodb', 'student', 'student')
 
  # Create a result set.
  stmt = db.query('SELECT version() AS version')
 
  # Read through the result set hash.
  stmt.each do | row |
    puts "#{row['version']}"
  end
  # Release the result set resources.
  stmt.freeze
 
rescue PG::Error => e
  # Print the error.
  puts "ERROR #{e.error} (#{e.sqlstate})"
  puts "Can't connect to the PostgreSQL database specified."
  # Signal an error.
  exit 1
 
ensure
  # Close the connection when it is open.
  db.close if db
end

Call the postgres_version.rb program with this syntax:

ruby mysql_version.rb

It should return:

PostgreSQL 14.10 (Ubuntu 14.10-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit

The postgres_columns.rb script returns a couple columns concatenated into a single column:

# Include Ruby Gem libraries.
require 'rubygems'
require 'pg'
 
# Begin block.
begin
  # Create a new connection resource.
  db = PG::connect( 'localhost', 5432, '', '', 'videodb', 'student', 'student')
 
  # Create a result set.
  stmt = db.query("SELECT   CONCAT(nh.last_name, ', ', nh.first_name) AS name " + \
                  "FROM     new_hire nh " +                                       \
                  "ORDER BY nh.last_name")
 
  # Read through the result set hash.
  stmt.each do | row |
    out = ""
    i = 0
    while i < stmt.fields.count()
      # Check when not last column and use the:
      #   - Hash returned by the result set for the value, and
      #   - String array value returned by the statement object
      #     as the name value of the hash by leveraging its 
      #     numeric index.
      if i < stmt.fields.count() - 1
        out += "#{row[stmt.fields[i]]}"
        out += ", "
      else
        out += "#{row[stmt.fields[i]]}"
      end
      i += 1
    end
    puts "#{out}"
  end
 
  # Release the result set resources.
  stmt.freeze
 
rescue PG::Error => e
  # Print the error.
  puts "ERROR #{e.error} (#{e.sqlstate})"
  puts "Can't connect to PostgreSQL database specified."
  # Signal an error.
  exit 1
 
ensure
  # Close the connection when it is open.
  db.close if db
end

Call the postgres_columns.rb program with this syntax:

ruby mysql_columns.rb

It should return:

Chabot, Henry
Lewis, Malcolm

As always, I hope this helps those looking to learn and solve a problem. You can find the PG Gem documentation here.

Written by maclochlainn

February 7th, 2024 at 12:15 am

VSCode Package Error

without comments

While running an update on Ubuntu 22.0.4 with the following syntax I got an error on finding the VSCode Package. I ran this to update before adding Ruby and Rails to an Ubuntu virtual machine instance.

sudo apt-get update

I manually moved the vscode.list file to my student user’s home directory and removed the file from /etc/apt/sources.list.d directory. This allowed me to update all other packages.

Don’t forget to replace the vscode.list file in the /etc/apt/sources.list.d directory.

Written by maclochlainn

February 5th, 2024 at 1:06 pm

Posted in Linux,Unix,VSCode

Tagged with

Ruby+MySQL on Ubuntu

without comments

This post goes through installing and configuring Ruby and Ruby on Rails for MySQL. The first step requires updating the Ubuntu OS:

sudo apt-get update

Interestingly, I found that the man-db service had inadvertently stopped. It raised the following error:

E: dpkg was interrupted, you must manually run 'sudo dpkg --configure -a' to correct the problem.

You run this command to find the problem with the dpkg utility:

sudo dpkg --configure -a

It returned:

Setting up man-db (2.10.2-1) ...
Updating database of manual pages ...
man-db.service is a disabled or a static unit not running, not starting it.

The following command started the man-db service:

sudo systemctl start man-db.service

Next, you install the prerequisite packages with this command:

sudo apt-get install -y git-core zlib1g-dev build-essential libssl-dev libreadline-dev libyaml-dev libsqlite3-dev sqlite3 libxml2-dev libxslt1-dev libcurl4-openssl-dev software-properties-common libffi-dev

Use the cd command to change to the student home directory. Clone the asdf as the multiple runtime version manager with this command:

git clone https://github.com/excid3/asdf.git ~/.asdf

The following is the output of the git clone command:

Cloning into '/home/student/.asdf'...
remote: Enumerating objects: 8756, done.
remote: Counting objects: 100% (829/829), done.
remote: Compressing objects: 100% (476/476), done.
remote: Total 8756 (delta 428), reused 657 (delta 334), pack-reused 7927
Receiving objects: 100% (8756/8756), 3.10 MiB | 4.29 MiB/s, done.
Resolving deltas: 100% (5148/5148), done.

Next, you fix your .bashrc file by adding the following components:

echo '. "$HOME/.asdf/asdf.sh"' >> ~/.bashrc
echo '. "$HOME/.asdf/completions/asdf.bash"' >> ~/.bashrc
echo 'legacy_version_file = yes' >> ~/.asdfrc
echo 'export EDITOR="code --wait"' >> ~/.bashrc

Source the modifies shell, which you can do like this:

exec $SHELL

or, like:

. ${HOME}/.bashrc

Add the following asdf plug-ins:

asdf plugin add ruby
asdf plugin add nodejs

Install Ruby with the following command:

asdf install ruby 3.3.0

Install Ruby Global with this syntax:

asdf global ruby 3.3.0

Update the Ruby Gems with this command:

gem update --system

You can confirm your Ruby install with two commands. First, use the which utility to check the Ruby install:

which -a ruby

It should return:

/home/student/.asdf/shims/ruby

Then, check the Ruby version:

ruby -v

It should return:

ruby 3.3.0 (2023-12-25 revision 5124f9ac75) [x86_64-linux]

Assuming you’ve installed and configured MySQL 8 on Ubuntu, you need this additional library to support the necessary Ruby Gem:

sudo apt-get install -y libmysqlclient-dev

Now, you can install the current MySQL Ruby Gem:

gem install mysql2

You can now write a mysql_connection.rb program to verify a connection to the MySQL 8 database, like:

# Include Ruby Gem libraries.
require 'rubygems'
require 'mysql2'
 
begin
  # Create new database connection.
  db = Mysql2::Client.new( :host     => 'localhost' \
                         , :username => 'student'   \
                         , :password => 'student'   \
                         , :database => 'studentdb')
 
  # Create a result set.
  stmt = db.query('SELECT version() AS version')
 
  # Read through the result set hash.
  stmt.each do | row |
    puts "#{row['version']}"
  end
  # Release the result set resources.
  stmt.free
 
rescue Mysql2::Error => e
  # Print the error.
  puts "ERROR #{e.errno} (#{e.sqlstate}): #{e.error}"
  puts "Can't connect to the MySQL database specified."
  # Signal an error.
  exit 1
 
ensure
  # Close the connection when it is open.
  db.close if db
end

Call the program with this syntax:

ruby mysql_connection.rb

It should return:

Connected to the MySQL database server.

You can verify the version with this mysql_version.rb program:

# Include Ruby Gem libraries.
require 'rubygems'
require 'mysql2'
 
begin
  # Create new database connection.
  db = Mysql2::Client.new( :host     => 'localhost' \
                         , :username => 'student'   \
                         , :password => 'student'   \
                         , :database => 'studentdb')
 
  # Create a result set.
  rs = db.query('SELECT version() AS version')
 
  # Read through the result set hash.
  rs.each do | row |
    puts "#{row['version']}"
  end
 
  # Release the result set resources.
  rs.free
 
rescue Mysql2::Error => e
  # Print the error.
  puts "ERROR #{e.errno} (#{e.sqlstate}): #{e.error}"
  puts "Can't connect to the MySQL database specified."
  # Signal an error.
  exit 1
 
ensure
  # Close the connection when it is open.
  db.close if db
end

On Ubuntu, it should return:

8.0.35-0ubuntu0.22.04.1

If you don’t know anything about the mysql2 Ruby Gem, you should read the documentation. It’s very concise and requires a basic understanding of Ruby programming. The two specific pages who may want to check for the next examples are:

The mysql_version.rb version uses the known string literal for columns or column aliases returned by the SQL statement, which becomes the stmt (or statement) in the program. The next program eliminates the need to enumerate with the text-based columns from the query by using the Statement#fields array values by use of a numeric index. The numeric index returns the field names from the Statement#fields class to use in as the name for values in the Result#fields value found in the row variable of the for loop.

# Include Ruby Gem libraries.
require 'rubygems'
require 'mysql2'
 
# Begin block.
begin
  # Create a new connection resource.
  db = Mysql2::Client.new( :host     => 'localhost' \
                         , :username => 'student'   \
                         , :password => 'student'   \
                         , :database => 'studentdb')
 
  # Create a result set.
  stmt = db.query("SELECT   DISTINCT i.item_title, ra.rating " +       \
                  "FROM     item i INNER JOIN rating_agency ra " +     \
                  "ON       i.item_rating_id = ra.rating_agency_id " + \
                  "WHERE    ra.rating_agency = 'MPAA'" +               \
                  "ORDER BY 1")
 
  # Read through the result set hash.
  stmt.each do | row |
    out = ""
    i = 0
    while i < stmt.fields.count()
      # Check when not last column and use the:
      #   - Hash returned by the result set for the value, and
      #   - String array value returned by the statement object
      #     as the name value of the hash by leveraging its 
      #     numeric index.
      if i < stmt.fields.count() - 1
        out += "#{row[stmt.fields[i]]}"
        out += ", "
      else
        out += "#{row[stmt.fields[i]]}"
      end
      i += 1
    end
    puts "#{out}"
  end
 
  # Release the result set resources.
  stmt.free
 
rescue Mysql2::Error => e
  # Print the error.
  puts "ERROR #{e.errno} (#{e.sqlstate}): #{e.error}"
  puts "Can't connect to MySQL database specified."
  # Signal an error.
  exit 1
 
ensure
  # Close the connection when it is open.
  db.close if db
end

It returns the select two columns from the query:

A Man for All Seasons, G
Around the World in 80 Days, G
Beau Geste, PG
Brave Heart, R
Camelot, G
Casino Royale, PG-13
...
Tomorrow Never Dies, PG-13
Tora! Tora! Tora!, G
Tron, PG

The following mysql_query_params.rb Ruby example accepts a single argument to leverage a wild card query in MySQL:

require 'rubygems'
require 'mysql2'
 
# Input external arguments.
arguments = ARGV
 
# Check for one input parameter and substitute an empty string
# when one isn't found.
if arguments.length == 1
  argument = arguments[0]
else
  argument = ""
end
 
# Begin block.
begin
  # Create a new connection resource.
  db = Mysql2::Client.new( :host     => 'localhost' \
                         , :username => 'student'   \
                         , :password => 'student'   \
                         , :database => 'studentdb')
 
  # Create a result set.
  stmt = db.prepare("SELECT   DISTINCT i.item_title, ra.rating " +       \
                    "FROM     item i INNER JOIN rating_agency ra " +     \
                    "ON       i.item_rating_id = ra.rating_agency_id " + \
                    "WHERE    ra.rating_agency = 'MPAA'" +               \
                    "AND      i.item_title LIKE CONCAT(?,'%')" +         \
                    "ORDER BY 1")
 
  # Bind the variable into the query.
  rs = stmt.execute(argument)
 
  # Read through the result set hash.
  rs.each do | row |
    out = ""
    i = 0
    while i < rs.fields.count()
      # Check when not last column and use the:
      #   - Hash returned by the result set for the value, and
      #   - String array value returned by the statement object
      #     as the name value of the hash by leveraging its 
      #     numeric index.
      if i < rs.fields.count() - 1
        out += "#{row[rs.fields[i]]}"
        out += ", "
      else
        out += "#{row[rs.fields[i]]}"
      end
      i += 1
    end
    puts "#{out}"
  end
 
  # Release the result set resources.
  rs.free
 
rescue Mysql2::Error => e
  # Print the error.
  puts "ERROR #{e.errno} (#{e.sqlstate}): #{e.error}"
  puts "Can't connect to MySQL database specified."
  # Signal an error.
  exit 1
 
ensure
  # Close the connection when it is open.
  db.close if db
end

If you call the mysql_query_params.rb program with this syntax:

ruby mysql_aquery_params.rb Harry

It’ll return the following from the studentdb database:

Harry Potter and the Chamber of Secrets, PG
Harry Potter and the Deathly Hallows, Part 1, PG-13
Harry Potter and the Deathly Hallows, Part 2, PG-13
Harry Potter and the Goblet of Fire, PG-13
Harry Potter and the Half Blood Prince, PG
Harry Potter and the Order of the Phoenix, PG-13
Harry Potter and the Prisoner of Azkaban, PG
Harry Potter and the Sorcerer's Stone, PG

After that, you should install Rails (check for current version beyond 1/2024). Install Ruby Global with this syntax:

gem install rails -v 7.1.3

Check the version installed:

rails -v

It should return:

Rails 7.1.3

Run this command to enable Rails for MySQL 8:

rails new myapp -d mysql

If you want to configure a username and password for MySQL, edit the config/database.yml file.

As always, I hope this helps somebody looking for step-by-step guide.

Written by maclochlainn

February 3rd, 2024 at 4:57 pm