DEV Community: Kagunda JM

ODK Central Domain Change: A Painless Backup and Restore Tutorial

Kagunda JM — Sat, 21 Oct 2023 20:00:00 +0000

Introduction

ODK Central is a powerful tool for collecting, managing, and analyzing data using mobile devices. However, there may be situations where you need to move your ODK deployment from one server to another, such as changing hosting providers, upgrading hardware, or switching domains.

This tutorial will walk you through the steps of creating a direct backup of your ODK Central database, copying the backup file and other essential data to the destination server, and restoring the backup file on the new server. The tutorial will also show you how to update the domain names in the Enketo Redis databases and config files to avoid errors when previewing forms on the new server.

By following this tutorial, you will be able to migrate your ODK deployment from one server to another with minimal downtime and data loss.

Prerequisites

Before you begin, ensure that you have the following prerequisites:

A running instance of ODK Central on the source and destination servers.
Both source and destination servers are running the same version of ODK Central.
Access to the command line interface (CLI) of both source and destination servers.
Sufficient disk space on both servers to store the backup files and other essential data.

If you have met these requirements, you will be able to follow this tutorial easily and avoid errors or issues that may arise during the backup and restore process.

Backup ODK Central Main Data on the Source Server

Log in to your ODK Central source server.
From the Terminal window, create a folder odk-backups for the ODK Central backup files in the logged-in user's home folder. You can use a different name that follows your folder naming conventions.
```
mkdir ~/odk-backups
```
Run the command below to perform a direct backup of your ODK Central database. Replace backup-password, your-odk-username@your-odk-domain-name, your-odk-domain-name, and backup-filename.zip placeholder values with actual values for your ODK Central installation.
```
  curl -X POST -H "Content-Type: application/json" -d '{"passphrase": "backup-password"}' -su your-odk-username@your-odk-domain-name https://your-odk-domain-name/v1/backup --output ~/odk-backups/backup-filename.zip
```
{"passphrase": "backup-password"}: password that will be used to encrypt the backup file
your-odk-username@your-odk-domain-name: Username that you use to log into the ODK Central web interface
your-odk-domain-name: Registered domain name for your ODK Central installation
backup-filename.zip: The name that will be assigned to the backup file.

You will be asked to provide your user password before the command can run. The password you type will be the password for the user that you use to log into the ODK Central web interface

Wait for the backup process to run to completion.

Below is an example of commands contained in the previous steps.

root:~#
root:~# mkdir odk-backups
root:~#
root:~# curl -X POST -H "Content-Type: application/json" -d '{"passphrase": "bk202309"}' -k  -u testapp@example.com https://odk-src.kagundajm.codes/v1/backup --output ~/odk-backups/odk-bk20230913-08AM.zip
Enter host password for user 'testapp@example.com':
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                Dload  Upload   Total   Spent    Left  Speed
100  481M    0  481M    0    26  4819k      0 --:--:--  0:01:42 --:--:-- 10.4M
root:~#
root:~# cd odk-backups/
root:~#
root:~/odk-backups# ls -la
total 493356
drwxr-xr-x  2 root root      4096 Sep 12 05:17 .
drwx------ 10 root root      4096 Sep 12 05:17 ..
-rw-r--r--  1 root root 505183986 Sep 12 05:19 odk-bk20230913-08AM.zip
root:~/odk-backups#

List ODK Central Docker Containers

This step is not mandatory, but it helps you understand the source of Docker container names used in this tutorial.

On the source server, change directory to the ODK Central installation folder
```
cd central
```

List names and state of ODK Central Docker containers using the following command:

docker ps --format 'table {{.Names}}\t{{.State}}'

--format option will list names and headers of the required fields. \t is a tab character to separate the fields.

Running the command will output a list similar to the following:

NAMES                          STATE
central-nginx-1                running
central-service-1              running
central-enketo-1               running
central-pyxform-1              running
central-enketo_redis_cache-1   running
central-mail-1                 running
central-postgres14-1           running
central-enketo_redis_main-1    running

Backup Enketo Redis Data and Secrets

Redis stores data in memory (RAM) which allows for fast data access and retrieval. The ODK Central installation includes two Redis database instances; central-enketo_redis_main-1 and central-enketo_redis_cache-1. To backup the Redis databases, you run commands to flush the in-memory data to disk.

Log in to your ODK Central source server if you had logged out.
Change directory to the ODK Central installation folder
```
cd ~/central
```
Flush in-memory data to disk on the main instance of Enketo Redis database by running the following command.
```
docker exec central-enketo_redis_main-1 redis-cli -p 6379 save
```
The command instructs Docker to execute the Redis Command Line Interface (CLI) tool (redis-cli )within the Docker container named central-enketo_redis_main-1 which is listening on port 6379. save creates a snapshot of the current state of the Redis database and writes it to disk.

The server should display OK after the command completes and a backup file named enketo-main.rdb will be created in the /data/ folder of the central-enketo_redis_main-1 container.

Copy the enketo-main.rdb file from the Docker container to the backup folder you created.

docker cp  central-enketo_redis_main-1:/data/enketo-main.rdb ~/odk-backups/enketo-main.rdb

Flush in-memory data to disk on the cache instance of Enketo Redis database.
```
docker exec central-enketo_redis_cache-1 redis-cli -p 6380 save
```
The server should display OK after the command completes and a backup file named enketo-cache.rdb will be created in the /data/ folder of the central-enketo_redis_cache-1 container.

Copy the enketo-cache.rdb backup file from the Docker container to the backup folder you created.

docker cp  central-enketo_redis_cache-1:/data/enketo-cache.rdb ~/odk-backups/enketo-cache.rdb

The central-service-1 container has a /etc/secrets folder which contains files used to store secret keys for the Enketo server. These keys are used to encrypt and decrypt data. Copy the secrets folder to the odk-backups folder.

docker cp central-service-1:/etc/secrets ~/odk-backups

The following is an example sequence of the commands and outputs for the above steps.

  root:~/odk-backups# cd ~/central
  root:~/central#
  root:~/central# docker exec central-enketo_redis_main-1 redis-cli -p 6379 save
  OK
  root:~/central# docker cp  central-enketo_redis_main-1:/data/enketo-main.rdb ~/odk-backups/enketo-main.rdb
  Successfully copied 39.9kB to /root/odk-backups/enketo-main.rdb
  root:~/central#
  root:~/central# docker exec central-enketo_redis_cache-1 redis-cli -p 6380 save
  OK
  root:~/central#
  root:~/central# docker cp  central-enketo_redis_cache-1:/data/enketo-cache.rdb ~/odk-backups/enketo-cache.rdb
  Successfully copied 2.05kB to /root/odk-backups/enketo-cache.rdb
  root:~/central#
  root:~/central# docker cp central-service-1:/etc/secrets ~/odk-backups
  Successfully copied 4.61kB to /root/odk-backups
  root:~/central#

Copy Enketo config file from Docker container to a text file in the backup folder.

docker cp central-enketo-1:/srv/src/enketo_express/config/config.json ~/odk-backups/enketo-config-keys.txt

Open the text file using the nano editor.

nano  ~/odk-backups/enketo-config-keys.txt

Using Control + K (^K) keys, remove all key/pair values in the text file except "encryption key", "less secure encryption key", and "api key". After you have completed removing all other key/values pairs, your text file should contain entries similar to the following:

"encryption key": "JAUzy4moYjA284rORjMcd2FIQCEdniSx9rmYZrqZSdxwzmG1WY6Apx6QAphgAFc9",
"less secure encryption key": "KgyIvGRFtnctP99qGzi0ii1GVg1j453U",
"api key": "YJXZRqiOirbSijVROqnh3HGCvuhEwhmKFrkyDkwl4HHIYo0Z3YNitfQH1McNp7srlf1RcvCfXX8vzCVoINW7UgZAIT6jbjR8gxaYJR3wzE8dNGZgdEitwl7cs2abkWUA",

Press Control + O (^O) to save the changes.
Press ENTER key to confirm the file name.
Press Control + X (^X) to close the editor.
Log out of the source server

Copy Backups From Source Server To Destination Server

Log in to your ODK Central destination server.
If you are using username/password to connect to the source server, run the command below to copy the ~/odk-backups folder to the destination server. You will be prompted to type the user's password before the rsync command starts the syncing process.
```
rsync -avz user@remote_server:~/odk-backups  ~
```
Use the below rsync command if you use SSH keys to log in to your source server.
```
rsync -avz -e "ssh -i ssh-key-file"  user@remote_server:~/odk-backups  ~
```
-a : Archive mode, which preserves file attributes and permissions.

-v : Verbose output, so you can see the progress.

-z : Compresses data during transfer, reducing the network bandwidth used.

-e "ssh -i ssh-key-file" : Tells rsync to use SSH for connecting to the remote server using -i ssh-key-file SSH private key file for authentication.

user@remote_server:~/odk-backups/ : user is the username for the remote server while remote_server is the IP address or registered hostname for the remote server. odk-backups refers to the folder containing the backup filles on the remote server.

Replace user, remote_server, and ssh-key-file with actual values for your environment.

A message and prompt similar to the one below may be displayed if SSH does not have a record of the remote server's public key. Make sure you trust the remote server before typing ‘yes’ to continue connecting.

  The authenticity of host 'kagundajm.codes (93.184.216.34)' can't be established.
  ED25519 key fingerprint is SHA256:7dvpverJGLBDWqd8uxsLX9kWgBiZnYcXL07B+w3eIds.
  This key is not known by any other names
  Are you sure you want to continue connecting (yes/no/[fingerprint])? yes

Restore ODK Central Direct Backup File

The first time ODK Central runs, it creates the /data/transfer folder on the host server and on the central-service-1 Docker container. The purpose of the folder is to allow exchange of data between the host server and the Docker container. Any changes made to /data/transfer in the central-service-1 container will be reflected in /data/transfer on your host server, and vice versa. To restore the Direct Backup file, you require to place the file in this folder.

Log in to your ODK Central destination server if you have logged out.
Move the Direct Backup file to the /data/transfer folder using the command below. Replace {backup-filename.zip} with the actual name that you used during the backup process.
```
mv ~/odk-backups/{backup-filename.zip} /data/transfer/
```
If you encounter a "cannot create regular file: Permission denied" error, prefix the command with sudo.
```
sudo mv ~/odk-backups/{backup-filename.zip} /data/transfer/
```
Change directory to ODK Central installation folder by running the following commands.
```
cd && cd central
```
Before running the command below, be aware that the command will replace all data on the destination server with the backup snapshot data. Once you are certain you want to replace the existing data, restore the backup using the following command:
```
docker compose exec service node /usr/odk/lib/bin/restore.js /data/transfer/{backup-filename.zip} { backup-password}
```
The command tells Docker to go into the running container named service and execute a Node.js script (restore.js) which expects a backup file (backup-filename.zip) and backup password (backup-password).

Replace backup-filename.zip with the file name you moved to the /data/transfer/ folder and {backup-password} with the password used during the backup process.

If you did not set a backup password during the backup process, press ENTER key immediately after typing the backup filename. Avoid trailing spaces after the filename backup-filename.zip.
```
docker compose exec service node /usr/odk/lib/bin/restore.js /data/transfer/{backup-filename.zip}
```
If the server displays "no configuration file provided: not found", you are running the command outside of ODK Central installation folder.

Wait for the backup restoration process to complete. The duration of your wait time will depend on the size of your backup file. After the restore process runs to completion, a success message including tasks that you require to revisit and verify will be displayed.

kagunda:~$
kagunda:~$ cd && cd central
kagunda:~/central$ docker compose exec service node /usr/odk/lib/bin/restore.js /data/transfer/odk-bk20230913-08AM.zip bk202309
Success. You will have to log out of the site and log back in.
    IMPORTANT: EVERYTHING has been restored to the way things were at the time of backup, including:
    * all passwords and email addresses.
    * anything deleted since the backup was made now exists again.
    * your backup settings.
    Please revisit all of these and make sure they are okay.
'{"success":true}'
kagunda:~/central$

Restore Enketo Redis Data and Secrets

Make sure you are stilled logged in to the destination server and you are working from the ODK Central installation folder.

Copy the main Enketo Redis database (enketo-main.rdb) to the Docker container using the following commands:

docker stop central-enketo_redis_main-1
docker cp ~/odk-backups/enketo-main.rdb central-enketo_redis_main-1:/data/enketo-main.rdb;
docker start central-enketo_redis_main-1;

Example commands and output:

kagunda:~/central$
kagunda:~/central$ docker stop central-enketo_redis_main-1
central-enketo_redis_main-1
kagunda:~/central$ docker cp ~/odk-backups/enketo-main.rdb central-enketo_redis_main-1:/data/enketo-main.rdb;
Successfully copied 39.9kB to central-enketo_redis_main-1:/data/enketo-main.rdb
kagunda:~/central$
kagunda:~/central$ docker start central-enketo_redis_main-1;
central-enketo_redis_main-1
kagunda:~/central$

Copy the Enketo Redis cache database (enketo-cache.rdb) to the Docker container:

docker stop central-enketo_redis_cache-1
docker cp ~/odk-backups/enketo-cache.rdb central-enketo_redis_cache-1:/data/enketo-cache.rdb;
docker start central-enketo_redis_cache-1;

Copy the secrets folder to the central-service-1 Docker container:

docker stop central-service-1
docker cp ~/odk-backups/secrets central-service-1:/etc;
docker start central-service-1;

Update Destination Server Enketo Config File.

Copy the config keys text file to the config folder in the Enketo Docker container.

docker cp  ~/odk-backups/enketo-config-keys.txt central-enketo-1:/srv/src/enketo_express/config/

Open a Bash shell in the Enketo Docker container.

  docker exec -it central-enketo-1 /bin/bash

Install nano editor.
```
apt-get install nano -y
```
-y option will assume "yes" as the answer to any prompts that may occur during the installation process.
Open the Enketo config file with the nano editor.
```
nano config/config.json;
```

Prefix "encryption key", "less secure encryption key", and "api key" keys with # (or any other character). This will help you differentiate between the existing keys from the new keys when updating.

  {
      "app name": "Enketo",
      "base path": "-",
      #"encryption key": "contents of enketo-secret",
      "id length": 31,
      #"less secure encryption key": "contents of enketo-less-secret",
      "linked form and data server": {
          #"api key": "contents of enketo-api-key",
          ...
      },
      ...
  }

Move to the beginning of the file and press the ENTER key after the opening curly brace ({) to create a blank line.
Press Control + R (^R) keys. The editor will display a "File to insert [from ./]:" prompt. Type config/enketo-config-keys.txt next to the prompt and press the ENTER key to insert the contents of the file at the cursor location.
Move the cursor to the "encryption key" line of the data that has been inserted to the file.
Press Control + K (^K) keys to cut the line.
Move the cursor to the beginning of "encryption key" prefixed with a # character.
Press Control + U (^U) keys to paste.
Repeat the previous three steps to update "less secure encryption key" and "api key" values.
Remove the lines prefixed with # by moving to each line and pressing Control + K (^K).
Save the changes and exit the nano editor.
Installation of nano editor was temporarily. Therefore, uninstall the editor and exit the Enketo container using the following commands.
```
apt-get remove nano -y;
exit;
```
Restart the Enketo Docker container to effect the changes in the configuration file.
```
docker restart central-enketo-1
```
Optionally show the active status of the docker containers.
```
docker ps --format 'table {{.Names}}\t{{.State}}'
```

Change Domain Names

If the ODK Central domain names on the source and destination servers are different, you will encounter the following error when you attempt to preview restored forms on the destination server.

This form does not exist or you no longer have access to it. Please check the URL for any missing characters.
If the form existed previously, it may have been archived, disabled or deleted. If this is unexpected, please contact the person who asked you to fill the form.
(Attempted to access form with ID: )

To resolve the error, you must replace the old domain values with the new domain values in the Redis databases.

Create Domain Update Script Files

On the destination server, create ~/main-odk-domains.sh script file.
```
nano ~/main-odk-domains.sh
```

Update the file with the following:

#!/bin/sh

OLD_DOMAIN="old-domain.com"
NEW_DOMAIN="new-domain.com"

keys=$(redis-cli -p 6379 KEYS  "*")

for key in $keys
do
  if [ "$(redis-cli -p 6379 type "$key")" = "string" ]; then
    if echo "$key" | grep -q "$OLD_DOMAIN"; then
      new_key=$(echo "$key" | sed "s/$OLD_DOMAIN/$NEW_DOMAIN/g")
      redis-cli -p 6379  RENAME "$key" "$new_key"
    fi
  elif [ "$(redis-cli -p 6379 type "$key")" = "hash" ]; then
    existingRosaServer=$(redis-cli -p 6379  HGET "$key" "openRosaServer")
    if echo "$existingRosaServer" | grep -q "$OLD_DOMAIN"; then
      newRosaServer=$(echo "$existingRosaServer" | sed "s/$OLD_DOMAIN/$NEW_DOMAIN/g")
      redis-cli -p 6379 HSET "$key" "openRosaServer" "$newRosaServer"
    fi

    if echo "$key" | grep -q "$OLD_DOMAIN"; then
      new_key=$(echo "$key" | sed "s/$OLD_DOMAIN/$NEW_DOMAIN/g")
      redis-cli -p 6379  RENAME "$key" "$new_key"
    fi
  fi
done

The script fetches all keys from the Redis database and iterates over each key. For each key, it substitutes any occurrences of old domain values with the new domain values and renames the key in the database. Where the type of key is hash, the value of the openRosaServer field is updated to the new domain.

The sed command is used to substitute old domain values with new domain values. The g at the end of the command tells sed to replace all occurrences of OLD_DOMAIN in the key.

grep -q command searches for old domain value matches within the key and exits with either a success or failure. The -q option suppresses display of the output.

Save the file and exit the nano editor.
Make the script file executable
```
chmod +x  ~/main-odk-domains.sh
```

Copy main-odk-domains.sh to cache-odk-domains.sh

cp ~/main-odk-domains.sh ~/cache-odk-domains.sh

Open cache-odk-domains.sh with a text editor and replace any occurrences of redis-cli -p 6379 with redis-cli -p 6380.
Save the changes and exit the editor.

Update Enketo Redis Main Database

Copy the main-odk-domains.sh script file to the main Enketo Redis database Docker container.
```
docker cp ~/main-odk-domains.sh central-enketo_redis_main-1:/root
```
Open a Bourne shell in the main Enketo Redis database container.
```
  docker exec -it central-enketo_redis_main-1 sh
```
Run the script file
```
/root/main-odk-domains.sh
```
Optionally, confirm the update has been successful
```
redis-cli -p 6379 KEYS "*NEW_DOMAIN*"
```
Exit the Docker container
```
exit
```

Update Enketo Redis Cache Database

Copy the cache-odk-domains.sh script file to the cache Enketo Redis database Docker container.
```
 docker cp ~/cache-odk-domains.sh central-enketo_redis_cache-1:/root
```
Open a Bourne shell in the cache Enketo Redis database container
```
  docker exec -it central-enketo_redis_cache-1 sh
```
Run the script file
```
/root/cache-odk-domains.sh
```
Exit the Docker container
```
exit
```

Conclusion

This tutorial has shown you how to backup and restore ODK Central data. You have learned how to create a direct backup of your ODK Central database, copy the backup file and other essential data to the destination server, and restore the backup file on the new server. You have also learned how to update the domain names in the Enketo Redis databases and config files to avoid errors when previewing forms on the new server.

If you found this article useful, please share it with your friends and colleagues who might benefit from it.

Thank you for reading!

Resources

How to Transform SQL Queries to Crosstabs in PostgreSQL

Kagunda JM — Wed, 08 Mar 2023 18:59:34 +0000

A SELECT SQL query retrieves data from a database in a tabular form. The first row, or header row, has the column names, and all the other rows have the data that was retrieved.

In a crosstab, the data is condensed and the names of one or more columns are rotated. You can add row and column totals to a crosstab. For example, a list of salesmen's monthly sales may include the months in the column headers. By rotating the data, it is easier to read and understand how the facts relate to one another.

Other names you might come across for crosstabs are matrix reports, pivot for SQL server databases, and pivot tables on spreadsheets like Microsoft Excel, Google Sheets, and LibreOffice.

The following topics are covered in this post:

Crosstabs using CASE conditional expression
Using Common Table Expressions (CTE) for a crosstab
Crosstabs using aggregate FILTER clause
Using the crosstab() function in PostgreSQL
Using a PostgreSQL crosstab() function with more than three columns
Using ARRAY data type to re-arrange extra crosstab columns

This post uses queries from a database populated with data from the Northwind database for Postgres. The queries are run using DBeaver SQL client.

Crosstabs Using CASE Conditional Expression

The SQL CASE expression enables you to choose a value depending on a condition, much like an if-then-else conditional statement. The syntax for the CASE expression is as follows:



CASE WHEN condition THEN result
     [WHEN ...]
     [ELSE result]
END

If a condition evaluates to true, the result value will be chosen, otherwise; the result value contained in the optional ELSE expression will be chosen.

In the example below, the SQL CASE statement is used to make a crosstab of monthly total sales by employees for the first four months of 1997.

If you run the query above, you will get a crosstab like the one below.

You can combine the previous query with a UNION ALL operator and the following SQL SELECT query to include the total for each month in the cross-tab.

The SQL CASE expression is supported by most database systems.

Using Common Table Expressions (CTE) For A Crosstab

Common Table Expressions (CTE) in SQL let you create a temporary table in a SELECT SQL statement. It is then possible to reference this named temporary table in subsequent SELECT, INSERT, UPDATE, or DELETE SQL statements. The CTEs simplify the SQL queries by breaking them down into smaller, more manageable parts that are easier to read and understand. In PostgreSQL, CTEs are called WITH queries.

You can rewrite the previous CASE crosstab query using a CTE as follows:

Crosstabs Using Aggregate FILTER Clause

From PostgreSQL 9.4, you can use the FILTER clause to perform aggregate functions on specific records. The FILTER clause is less wordy and has a cleaner syntax than the CASE statement.

The following SQL query uses the FILTER clause to generate a crosstab.

The SQL COALESCE function replaces any null values in the crosstab with zero (0) values.

Using PostgreSQL `crosstab()` Function

The crosstab() function is part of the optional tablefunc module.

You can run the SELECT COUNT(*) FROM pg_extension WHERE extname='tablefunc'; query to see if the tablefunc extension is installed on the database you are using. If the result of the query is 0, install and activate the tablefunc⁣ extension using the CREATE EXTENSION IF NOT EXIST tablefunc;⁣ SQL command. The tablefunc⁣ module can be installed by non-superusers who have the CREATE⁣ privilege.

The crosstab function has several options, crosstab(sql text), crosstabN(sql text), and crosstab(source_sql text, category_sql text). Examples in this post use the crosstab(source_sql text, category_sql text) function option.

The first parameter (source_sql text) in a crosstab(source_sql text, category_sql text) function is the source SQL SELECT query statement and must return at least three (3) columns of data to pivot or rotate. The first column (row_name) contains data values to be used as row identifiers in the final result; data in the second column (category) represents category values that will be rotated to column headers in the pivot table, and the third column (value) contains data to be assigned to each cell of the final crosstab. The second parameter text category_sql is a query returning a category list for the columns.

The crosstab function returns a set of records with unknown data types. Therefore, you must alias the returned columns with column names and types using the AS (col1 type, col2 type, ...) clause. Failing to alias the columns will cause a column definition list is required for functions returning "record" error.

The SQL query below shows how to use the crosstab(source_sql text, category_sql text) function. A dollar-quoted string constant ($$) has been used to maintain formatting consistency and remove the need to escape single quotes (') by doubling them.

Using A PostgreSQL `crosstab()` Function With More Than Three Columns

If your source_sql text SQL query returns more than three columns, the additional or extra columns must be placed between the row_name and category columns. In the previous crosstab SQL query, any extra or additional columns must be placed between the salesman and order_month columns.

PostgreSQL converts identifiers/column names to lowercase by default. To capitalize the crosstab column headers, enclose them within double quotes.

Using ARRAY Data Type To Re-Arrange Extra Crosstab Columns

A crosstab with more than three columns has limitations in the order placement of the additional columns in the final crosstab. However, you can re-arrange the crosstab columns by inserting the extra columns into an arrays data type.

Conclusion

Crosstabs are a powerful way to summarize and analyze data from a database. By presenting data in a condensed and organized format, crosstabs make it easier to analyze relationships between different variables.

The CASE conditional expression, aggregate FILTER clause, and the PostgreSQL crosstab() function are some of the methods for creating crosstabs. If the crosstab() function uses more than three columns to summarize data, the ARRAY data type may be used to re-arrange the extra columns into the correct positions. Using crosstabs, PostgreSQL users can create reports and dashboards that help them make informed business decisions.

Resources

How To Install ODK Central on Ubuntu 20.04

Kagunda JM — Thu, 02 Dec 2021 12:22:54 +0000

Introduction

Open Data Kit (ODK) is a set (components) of free, open-source software used for facilitating data collection using mobile devices. ODK Central or Central is the server component. Without the server, the process of data collection cannot proceed.

Installing ODK Central on a DigitalOcean server is the recommended method and is well documented. Where situations demand installing Central on a different cloud provider, documentation of the process is either scanty or altogether lacking.

This post steps through the process of installing Central on a custom Linux server not hosted by DigitalOcean and contains the following sections:

Prerequisites
Checking DNS Propagation
Checking for Available Disk Space
Installation of the Docker Engine
Customizing Docker Engine Data Root Folder
Configuring Docker Engine to run as a non-root user
Installing Docker Compose
Installing ODK Central
Starting Up ODK Central
Create ODK Central Administrator

Prerequisites

You can SSH to your server using a user who has sudo rights.
You have purchased a domain name. In this post, I will be using odk-central.example.com as the domain name. Remember to substitute the domain name with your domain name.
You have created the domain (odk-central.example.com) and sub-domain (www.odk-central.example.com) names on your cloud provider. If the www sub-domain is not created, Let’s Encrypt fails with Challenge failed for domain odk-central.example.com when validating requests for SSL certificates for your domain.
You have updated the A/AAAA records for your domain and sub-domain to point to your server. An A-record links your domain name or sub-domain name to an IP address for your server.

Checking DNS Propagation

Using the ping command, we will test whether our domain name is reachable from our local computer.

Open a terminal or command window and run the following command ping odk-central.example.com -c4. The -c4 option species the number of requests. The -c4 option is not required when running the command from a Windows computer; windows defaults to four ping requests. Replace odk-central.example.com with your domain name.

If the response you get is similar to "ping: cannot resolve odk-central.example.com: Unknown host" or "Ping request could not find host odk-central.example.com. Please check the name and try again.", then your server is not reachable.

Changes you make to the A-records could take several hours to propagate. There are online tools that may assist in checking the reachability of your domains and sub-domains. Some of these tools are:
DNSPropagation, DNS Google, DNS Tester](https://www.dnstester.net/) among others can help in checking on the propagation process.

Do not forget to check whether you can also ping the www sub-domain.

Checking for Available Disk Space

ODK Central uses multiple Docker containers. During the installation process, Docker images required by ODK Central service get downloaded to the server. Before starting the install process, verify that your server has adequate space. Establish whether the available space is on one hard disk drive or spread over multiple hard disk drives.

To display the drives and space used, run df --human-readable or df -h command from a terminal window.

Running the df command on a server provisioned on DigitalOcean may display an output similar to the following:

Filesystem      Size  Used Avail Use% Mounted on
udev            3.9G     0  3.9G   0% /dev
tmpfs           797M  1.6M  795M   1% /run
/dev/vda1       155G   11G  145G   7% /
tmpfs           3.9G     0  3.9G   0% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
...

The sample output shows that the server has one partition /dev/vda1 mounted on the root (/) directory with 145Gb of free space.

However, running the df command on my custom server displays a different output as shown below:

Filesystem                   Size  Used Avail Use% Mounted on
/dev/root                     29G  2.1G   27G   7% /
devtmpfs                     3.9G     0  3.9G   0% /dev
tmpfs                        3.9G     0  3.9G   0% /dev/shm
tmpfs                        796M  1.1M  795M   1% /run
/dev/sdb1                    503G   73M  478G   1% /data
...

From the output, observe that the custom server has two disk partitions; /dev/root mounted on root (/) directory with 27Gb of free space and /dev/sdb1 mounted on /data directory with 476Gb of free space. To avoid running out of disk space on the root directory, we need to configure the Docker Engine to store data on the data directory; likewise, we require to install ODK Central on the data directory.

Install Docker Engine

A Docker Engine installation on Ubuntu requires the server to be running a 64-bit version of Ubuntu 18.04 or higher.

You can check the installed version of Ubuntu on your server by running lsb_release -a or cat /etc/lsb-release command from a terminal window.

Use the following steps to install Docker:

Update the server's local repository package information by running the sudo apt update command. Running this command is required every time before you install or upgrade software.
Install packages required to install Docker using sudo apt install ca-certificates curl gnupg lsb-release
- ca-certificates - common digital certificates used to verify and encrypt data between a server and other websites
- curl - tool used to transfer data from one server to another server.
- gnupg - command line tool that encrypts and signs files; it also known as gpg.
- lsb-release - a utility that displays Linux Standard Base (LSB) information about a Linux distribution. o display your distribution information, run the lsb_release -a or cat /etc/lsb-release command from a terminal window.
Download Docker's official GPG key specific to Ubuntu and save the key to /usr/share/keyrings/docker-archive-keyring.gpg file on our server.
```
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo gpg --dearmor -o /usr/share/keyrings/docker-archive-keyring.gpg
```
Add Docker's stable repository to our server's local packages repository.
```
echo \
"deb [arch=$(dpkg --print-architecture) signed-by=/usr/share/keyrings/docker-archive-keyring.gpg] https://download.docker.com/linux/ubuntu \
$(lsb_release -cs) stable" | sudo tee /etc/apt/sources.list.d/docker.list > /dev/null
```
The following is an explanation of the sub-commands used in the previous command:
- dpkg --print-architecture - determines the system's architecture such as amd64
- lsb_release -cs - outputs the Linux distribution release codename, such as bionic for Ubuntu 18.04 or focal for Ubuntu 20.04
- tee will read input from echo command and write the output to /etc/apt/sources.list.d/docker.list file
Update packages in our server's repository - sudo apt update
Install the latest version of Docker Engine sudo apt install docker-ce docker-ce-cli containerd.io
Verify Docker installation using sudo docker -v command. This should output version number for installed Docker.
The docker service starts automatically on Ubuntu; however, we can run the systemctl enable docker command to confirm that the service will start.

Configure Docker Data Root Folder

By default, Docker stores all its data within the /var/lib/docker directory. You can verify the location by running docker info and looking for Docker Root Dir: entry from the output.

Our custom server has limited space on the root directory. As such, we need to configure Docker to save its data within our data directory.

The preferred option to configure Docker is through a /etc/docker/daemon.json JSON settings file. However, Docker does not automatically create the /etc/docker/daemon.json file.

Stop the Docker service by running the sudo service docker stop command.

Run the sudo nano /etc/docker/daemon.json to create the file.

Update the file with the following:

{
  "data-root": "/data/docker-data"
}

To save and close the /etc/docker/daemon.json file, press CTRL+X keys together, then Y to confirm that you want to save the modified file and finally press the ENTER key to confirm the file name.

Restart the Docker daemon by running the sudo service docker start command.

Confirm that Docker's storage location has been updated to match the entry in our /etc/docker/daemon.json file by running the docker info | grep "Docker Root Dir" command. The output should reflect the changes we made in our configuration file - Docker Root Dir: /data/docker-data

Use Docker As Non-Root User

Every time a non-root user runs the docker command, the user must prefix the command with sudo. To avoid typing sudo docker, add the connected user to the docker group.

Create the docker group by running the sudo groupadd docker command.
Add connected user to the docker group by running the sudo usermod -aG docker $USER command. You can replace $USER with your preferred user - sudo usermod -aG docker kagunda.
Activate the changes to groups by running the newgrp docker command or log out and log back in again if running the command does not work.
Verify that you can access and download Docker images without sudo by running the docker run hello-world command.

Install Docker Compose

Run the following command to download the current stable release of Docker Compose:

sudo curl -L "https://github.com/docker/compose/releases/download/1.29.2/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose

Assign execute permissions to the docker-compose binary:
```
sudo chmod +x /usr/local/bin/docker-compose
```

Optionally, create a symbolic link to /usr/bin

sudo ln -s /usr/local/bin/docker-compose /usr/bin/docker-compose

Test the docker-compose installation by running the docker-compose --version command.

Install ODK Central

UFW (Uncomplicated Firewall) comes pre-installed on Ubuntu but is disabled. However, you can run the ufw disable command to confirm the firewall is disabled.
On our custom server, we want to install ODK Central on the data directory. To change the directory, run the cd /data command.

Download the ODK Central software using the sudo git clone https://github.com/getodk/central command. If you connected to the server as a root user, omit the sudo prefix - git clone https://github.com/getodk/central

root@odk-server:/data#
root@odk-server:/data# git clone https://github.com/getodk/central
Cloning into 'central'...
remote: Enumerating objects: 1015, done.
remote: Counting objects: 100% (1014/1014), done.
remote: Compressing objects: 100% (499/499), done.
remote: Total 1015 (delta 550), reused 912 (delta 490), pack-reused 1
Receiving objects: 100% (1015/1015), 191.22 KiB | 5.03 MiB/s, done.
Resolving deltas: 100% (550/550), done.
root@odk-server:/data#
root@odk-server:/data#

After the above command completes, type the cd central command to move to the ODK Central installation folder.

Download missing ODK Central components by running the git submodule update -i command.

root@odk-server:/data#
root@odk-server:/data# cd central/
root@odk-server:/data/central# git submodule update -i
Submodule 'client' (https://github.com/getodk/central-frontend.git) registered for path 'client'
Submodule 'server' (https://github.com/getodk/central-backend.git) registered for path 'server'
Cloning into '/data/central/client'...
Cloning into '/data/central/server'...
Submodule path 'client': checked out '5cc6fd79d112ce36d6298c61bb8817689c4c323b'
Submodule path 'server': checked out '1d1a3a59969e61383da74119e405e67778b7a170'
root@odk-server:/data/central#
root@odk-server:/data/central#
root@odk-server:/data/central#

Copy ODK Central settings template file by running the mv .env.template .env command.
Edit the settings file by running the nano .env command. This will launch the nano editor and display the contents of the .env settings file.
- Change the value of DOMAIN to use your domain name. In my case, the value will be DOMAIN=odk-central.example.com
- Change the value of SYSADMIN_EMAIL to use a valid email for your email. Let's Encrypt service will use this email address to notify you if something goes wrong when issuing your security certificates.
- Press CTRL+X keys together, then Y to confirm that you want to save the modified file and finally press ENTER key to confirm the file name.
Type docker-compose build to build the stack of applications required to run ODK Central service. The process will take download all required ODK Central Docker images and will take time. After the process completes, you should see a success message, and the terminal prompt gets displayed.
Run the docker-compose up --no-start command. This command tells Docker to create all containers required by ODK Central without starting these containers.

Starting Up ODK Central

Make sure you are still in the central directory and run the docker-compose up -d. The -d option starts the ODK Central containers in the background and leaves them running.

To check whether all ODK Central containers have finished loading, run the docker-compose ps command.

nginx is the web server container for ODK Central. It should display Up or Up (healthy) under the State column. If the state column displays Up (health: starting), give it more time to complete starting up. A state of Exit 0 for the secrets container means everything is fine.

After the web server has started successfully, visit your domain in a web browser. If everything worked out as expected, the ODK Central login page should be displayed.

Create ODK Central Administrator

Make sure you are on the central folder. If you are in any other folder, move to the central folder by running the cd /data/central command. Substitute /data/central with the location where you installed ODK Central.
Run the following command to create an account within ODK Central.
```
docker-compose exec service odk-cmd --email YOUREMAIL@ADDRESSHERE.com user-create
```
Substitute YOUREMAIL@ADDRESSHERE.com with the email for the user you wish to create. You will be requested to provide a password for the new account.

Promote the account to an administrator using the following command:

docker-compose exec service odk-cmd --email YOUREMAIL@ADDRESSHERE.com user-promote

TTo create other users, navigate to your domain in a web browser, log in using the administrator account you have created and create new users from the ODK Central interface.

If you ever forget or lose the administrator's password, you can reset the password by running the following command:

docker-compose exec service odk-cmd --email YOUREMAIL@ADDRESSHERE.com user-set-password

Summary

ODK Central is the server-side software used for data collection using mobile devices. When installing ODK Central on a cloud provider other than DigitalOcean, you are on your own.

In this post, I installed ODK Central on a server with two hard drives and having limited space on the root directory. I started by looking at ways to check for DNS propagation for your domain names, installed Docker, configured Docker to save data in a custom directory, cloned the ODK Central repository, and finally configured and tested the ODK Central application was up and running.

I hope the post may be helpful to anyone wishing to install ODK Central on a custom cloud provider other than DigitalOcean.

Resources

How to Drop Multiple Databases in PostgreSQL

Kagunda JM — Thu, 28 Oct 2021 16:42:20 +0000

Introduction

While performing database integration tests, things went south, and I ended up with more than 15 (fifteen) temporary PostgreSQL test databases.

I use OmniDB, an open-source application for managing databases. Version (3.0.2b) of OmniDB does not have an option for selecting and dropping multiple databases. To drop a database in OmniDB, you right-click on a database, select Drop Database from the context menu, run the query to drop the database, and finally close the query window. The thought of repeating the four steps on all those test databases led me to think of other efficient alternatives.

The following three alternatives for dropping multiple databases in PostgreSQL came to mind:

Anonymous Code Block
Interactive Terminal
Using a shell script

My test databases are prefixed with test; I will extract the names of the databases from pg_database database using SELECT datname FROM pg_database WHERE datname LIKE 'test%' AND datistemplate=false SQL query. The WHERE clause in the SQL query will restrict the database names to my test databases and non-template databases.

In the following sections, I explore the above three options in dropping multiple databases in PostgreSQL.

Anonymous Code Block

The DO statement in PostgreSQL executes an anonymous code block. In the past, I have used an anonymous code block to drop tables from a database.

DO $$
  DECLARE
      r RECORD;
BEGIN
    FOR r IN
        SELECT datname
        FROM pg_database
        WHERE datname LIKE 'test%' AND datistemplate=false
    LOOP
        EXECUTE 'DROP DATABASE ' || quote_ident(r.datname) || ';' ;
    END LOOP;
END $$;

This time, however, the anonymous code block throws an exception:

DROP DATABASE cannot be executed from a function
CONTEXT: SQL statement "DROP DATABASE test_01024da6a3;"
PL/pgSQL function inline_code_block line 12 at EXECUTE

As it turns out, you cannot execute DROP DATABASE statements inside a transaction block and anonymous code blocks being functions are transactional and this is the reason for the exception.

Interactive Terminal

PostgreSQL interactive terminal (psql) allows you to enter, edit, execute commands, and view results of SQL queries. In addition to SQL queries, psql provides meta-commands which are commands processed by psql without being sent to a PostgreSQL server; all meta-commands have a backslash () prefix. In the following paragraphs, I explain how to dynamically create a DROP DATABASE SQL query and run the query using the \gexec meta-command.

Before you execute PostgreSQL SQL commands, you have to connect to a PostgreSQL server. To connect to a PostgreSQL server, run the psql -d postgres command from a terminal window. If you don't specify the user name, PostgreSQL will default to the current operating system user. The default user has permission to create and drop databases. You can connect as a different user by appending the -U command-line option to the psql command (psql -U username -d postgres).

Upon successful connection, the psql prompt you get will either be postgres=> or postgres=# if you are a database superuser. At the prompt, type your SQL query to construct DROP DATABASE statements for the databases you want to drop. Do not terminate the query with a semicolon (;) --SELECT 'DROP DATABASE ' || quote_ident(datname) || ';' FROM pg_database WHERE datname LIKE 'test%' AND datistemplate=false. If you terminate the query with a semicolon (;), the query will run when you press the RETURN key.

The \gexec meta-command sends the current query buffer to the server and treats each column of each row of the query's output as an SQL statement to be executed. In our case, our query will output a DROP DATABASE SQL query for each test database. \gexec meta-command will then run each statement from the output.

postgres=#
postgres=# SELECT 'DROP DATABASE ' || quote_ident(datname) || ';'
postgres=# FROM pg_database
postgres=# WHERE datname LIKE 'test%' AND datistemplate=false
postgres=#
postgres=# \gexec

Using Shell Script

Scripts help in automating repetitive tasks. You create a text file, type commands in the file, make the file executable, and any time you want to repeat the commands, you execute the script file instead of retyping the commands.

Creating and executing a shell script involves the following steps:

Use a text editor to create your script file
Insert commands in the file.
Save and close the file.
Make the script executable.
Execute the script.

Create a drop-test-dbs.sh text file and update with the following:

#!/bin/bash

test_databases_file=~/projects/test_dbs.txt
psql -d postgres -c "COPY (SELECT datname FROM pg_database WHERE datname LIKE 'test%' AND datistemplate=false) TO '$test_databases_file'"

while read dbname
do
  echo "dropping DB $dbname..."
  dropdb "$dbname"
done < $test_databases_file

echo "removing $test_databases_file file"
rm $test_databases_file

#!/bin/bash is called a shebang or a bang line. It specifies the interpreter that will execute the commands.

The script uses psql to connect to a PostgreSQL server, run SQL query to fetch names of the test databases and copy the database names to a test_dbs.txt file. We then open the created file (containing database names), and for each database name, we drop the database using the dropdb utility program. Finally, we remove the file containing the database names.

After creating our script file, we then open the console or terminal window and assign the execute permission to the file using the chmod command (chmod +x drop-test-dbs.sh).

Finally, to drop the databases, we execute the commands in the file by running the file from a console or terminal window (./drop-test-dbs.sh). If the script file is not within the current folder, remember to include the file path (file-location/drop-test-dbs.sh).

Wrapping Up

There are various methods for dropping multiple databases in PostgreSQL.

ou can use desktop or web-based database management tools to drop databases. Some database management tools allow dropping one database at a time; others can drop multiple databases.

You cannot drop PostgreSQL databases within a transaction; PostgreSQL functions (including anonymous functions) are transactional.

psql is a powerful command-line tool that ships with PostgreSQL. You can use psql to connect to a server and interact with databases. Using psql and querying the pg_database database for the required databases together with \gexec meta-command is another option for dropping multiple databases in PostgreSQL. However, failing to filter for required databases will drop all databases from your server.

If the task of dropping multiple databases is repetitive, you can consolidate the psql commands into a shell script file and execute the script file.

If you would like to create multiple databases for testing the methods described in this post, you can create and run a script file with the following commands:

#!/bin/bash

for n in {1..15}
do
  # Use Bash command substitution to store the
  # output (GUID) from uuidgen command
  DB_ID=$(uuidgen)

  # Use Bash Substring expansion to extract
  # 8 characters of the GUID starting from index 0
  db="test_db_${DB_ID:0:8}"

  createdb "$db"
  echo "DB $db created"
done

How To Export Data From Database Query To XML

Kagunda JM — Wed, 30 Jun 2021 02:55:51 +0000

Introduction

Extensible Markup Language (XML) has been in existence for more than two decades. XML is a markup language for encoding documents in a format that is both human and machine readable. In XML, you define your own custom tags, elements and attributes to meet your specific needs. XML is case sensitive, allows comments and hierarchy is important. An XML file is a text file and can be opened with any text editor. The following is a sample of an XML document:

<!-- sample comment -->
<Customers> <!-- root element -->
  <Customer>  <!-- nested element -->
    <FirstName>Helena</FirstName>
  </Customer>
  <Customer>
  ...
  </Customer>
  ...
</Customers>

When we run a query against a relational database table, the resulting data is presented mostly in tabular format. Each horizontal row represents a record while the columns flow vertically and represent an attribute of the record.

In this post, I explore how MS SQL Server and PostgreSQL databases may be queried to output the data in an XML format instead of tabular format. Each of these databases contain inbuilt functionality to output XML. You can also manually write queries combining XML elements and data from the databases to create XML output. Manually created XML output is tedious, will be missing a root element and will therefore not conform to a well formed xml document.

Create XML Manually

The sample data used within this post will be from a hypothetical customers table with the following structure and sample data.

id	first_name	last_name	city	state
1	François	Tremblay	Montreal	QC
2	Bjørn	Hansen	Oslo
3	Helena	Holý	Prague
4	Fernanda	Ramos	Brasília	DF

Create a file named customers.xml and open it with a text editor of your choice.

Create a root element in the file so that the XML file we create will be a well formed xml document by inserting the following contents to the file you:

<Customers>

</Customers>

Compose your query for data by enclosing your data columns within XML elements. Use the CONCAT() function to combine the XML elements and the data. CONCAT() function is supported by both MS SQL Server and PostgreSQL. Use COALESCE() function to replace any null values with an empty string.

SELECT CONCAT(
   '<Customer>',
      '<Id>', id, '</Id>',
      '<FirstName>', first_name, '</FirstName>',
      '<LastName>', last_name, '</LastName>',
      '<City>', COALESCE(city, ''), '</City>',
      '<State>', COALESCE(state, ''), '</State>',
   '</Customer>') customers_xml
 FROM customers LIMIT 2;

Run the query, select the results, copy and paste them between the opening and closing root element in the XML file we created.

<Customers>
  <Customer><Id>1</Id><FirstName>François</FirstName><LastName>Tremblay</LastName><City>Montreal</City><State>QC</State></Customer>
  <Customer><Id>2</Id><FirstName>Bjørn</FirstName><LastName>Hansen</LastName><City>Oslo</City><State></State></Customer>
</Customers>

Save your XML file.

SQL Fiddle

Output XML Using Database Inbuilt Functions

PostgreSQL

PostgreSQL has query_to_xml(query text, nulls boolean, tableforest boolean, targetns text) function for displaying data in XML format. The query_to_xml function requires four (4) arguments:

query - The actual SQL query as text.
nulls - If elements with null values should be included. When the value is false, columns with null values will be omitted from the XML document when the XML elements are generated. A true value for the parameter will output a self-closing element containing <columnname xsi:nil="true"/> attribute.
tableforest - Will put each row in different XML documents. That means that each query data row will be wrapped in a row root element.
targetns - The namespace to put the result in. Pass a blank string to use the default namespace.

After running the query, table tag will appear as the root element while row tag will be set for each row in the data. You can then update the table and row tags with your required tag names.

Using our sample table, we generate XML using the following:

SELECT query_to_xml(
    'SELECT t.id "Id"
     , t.first_name "FirstName"
     , t.last_name "LastName"
     , t.city "City"
     , t.state "State"
    FROM customers t LIMIT 2', true , false, '')

The output after running the function will be as follows:

<table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
    <Id>1</Id>
    <FirstName>François</FirstName>
    <LastName>Tremblay</LastName>
    <City>Montreal</City>
    <State>QC</State>
  </row>
  <row>
    <Id>2</Id>
    <FirstName>Bjørn</FirstName>
    <LastName>Hansen</LastName>
    <City>Oslo</City>
    <State xsi:nil="true"/>
  </row>
</table>

SQL Fiddle

Select and copy the XML output.

Create a file named customers.xml and open the file with a text editor.

Paste the contents you copied above to the file, replace table with Customers, row with Customer and save the file.

If you want your XML document to include the XML Schema, then use query_to_xml_and_xmlschema function instead of query_to_xml function.

Using the COPY function, you can copy the XML output directly to a file using the following syntax:

COPY(
  SELECT query_to_xml(
      'SELECT t.id "Id"
      , t.first_name "FirstName"
      , t.last_name "LastName"
      , t.city "City"
      , t.state "State"
      FROM customers t', true , false, '')
) TO '~/tmp/customers.xml'

Note that the COPY command has limited file access and user permissions. To run the command, the user running the command must be either a superuser or a member of the pg_write_server_files. However, great care should be taken when granting a user any of these roles as these roles are able to access any file on the server file system.

SQL Server

SQL Server uses a FOR XML clause in select query to transform the query results to XML output.

SELECT TOP 2
  id Id
  ,first_name FirstName
  ,last_name LastName
  ,city City
  ,COALESCE(state, '') State
FROM customers
FOR XML PATH('Customer'), ROOT('Customers')

Remember to enclose any nullable columns within the COALESCE() function to prevent them being omitted from the XML output in case they don't have values.

SQL Server has the advantage of allowing for customization of the root element and object node elements depending on FOR XML mode selected. The following will be the output after running the above query:

<Customers>
  <Customer>
    <Id>1</Id>
    <FirstName>François</FirstName>
    <LastName>Tremblay</LastName>
    <City>Montreal</City>
    <State>QC</State>
  </Customer>
  <Customer>
    <Id>2</Id>
    <FirstName>Bjørn</FirstName>
    <LastName>Hansen</LastName>
    <City>Oslo</City>
    <State></State>
  </Customer>
</Customers>

SQL Fiddle

Select and copy the XML output.

Create a file named customers.xml and open the file with a text editor.

Paste the contents you copied above to the file and save the file.

You can also create the xml file in a single step by using the bulk copy program utility (bcp). The bcp utility can be used for importing and exporting data from/to a data file.

The following command uses bcp utility to create an XML file from the sample data we have been using.

bcp "SELECT TOP 2
  id Id
  ,first_name FirstName
  ,last_name LastName
  ,city City
  ,COALESCE(state, '') State
FROM customers
FOR XML PATH('Customer'), ROOT('Customers')" queryout ~/Documents/customers.xml -S localhost -d testdb  -c -U sa

-S specifies the SQL Server instance to connect to
-d species the database to connect to
-c performs the operation using a character data type without prompting for each field
-U specifies the login ID used to connect to SQL Server

Summary

In this post, we looked at various options available to create XML data from PostgreSQl and MS SQL Server database queries. One option would be to generate the XML data manually while another option would be to use in-built functions contained within these databases. It is also possible to output XML data files using PostgreSQL COPY command or SQL Server bcp utility.

Resources

Installing MS SQL Server 2019 on Parrot OS

Kagunda JM — Tue, 22 Jun 2021 11:13:55 +0000

Introduction

Parrot OS is a light Linux distribution based on Debian. Microsoft SQL Server or SQL Server is a relational database management system (RDBMS) developed by Microsoft. Starting with SQL Server 2017, Microsoft added Linux support and thus making any newer versions cross platform.

This post steps through the process of SQL Server 2019 Developer edition installation on Parrot OS and contains the following topics:

downloading and configuring MS SQL Server 2019 package repository
running the SQL Server setup process
installation of SQL Server command line tools
installation Azure Data Studio
running queries in Azure Data Studio

The commands used in this post will also work on any Debian based distribution like Ubuntu, MX Linux, Linux Mint, Knoppix among many others.

Prerequisites

Parrot OS has very minimal memory requirements (256MB) and can be run directly from a USB flash drive. However, to run MS SQL Server 2019, Parrot OS must be installed on a hard disk. The computer running Parrot OS must also meet the following requirements:

have minimum of 2 GB memory
have 6 GB or more free hard disk space
contain a x64 processor with 2 or more cores

Configure SQL Server 2019 Repository

The repository is used to acquire the database engine package, mssql-server, and related SQL Server packages.

Open the Terminal window
Register Microsoft Ubuntu repository by running curl https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list | sudo tee /etc/apt/sources.list.d/mssql-server-2019.list command and type the password for the super user. tee command will write the downloaded file to /etc/apt/sources.list.d folder.
Run ls /etc/apt/sources.list.d command and confirm that mssql-server-2019.list file has been copied to /etc/apt/sources.list.d folder folder and you should see mssql-server-2019.list among the files
Run sudo apt-get update to update the system
If you receive an error 'The following signatures couldn't be verified because the public key is not available: NO_PUBKEY EB3E94ADBE1229CF', add the key to trusted keys using gpg --keyserver keyserver.ubuntu.com --recv-keys EB3E94ADBE1229CF and re-run sudo apt-get update command.

Install SQL Server Developer Edition

Run sudo apt-get install -y mssql-server command to install SQL Server
After the package installation completes, run sudo /opt/mssql/bin/mssql-conf setup
Type 2 to select Developer edition and press the return key
Type Y to accept the license terms and press return key
Type and confirm the system administrator password and press return key. The password should have a minimum of eight characters
Once the configuration is complete, type systemctl status mssql-server --no-pager to verify that the service is up and running. --no-pager will force any long lines to wrap.

Install SQL Server Command Line Tools

Command-line tools are used for running SQL commands from a Terminal window. Various command-line tools are available among them sqsh or SQSHELL, sqlcmd and mssql-cli.

sqsh

Parrot OS comes bundled with sqsh command line tool and therefore, no installation is required.

To use sqsh, open a Terminal window and run sqsh -S localhost -U sa -C 'SELECT @@VERSION command and type the SQL Server administrator password you used during setup. This will display the SQL Server version and Linux OS Version and the Terminal prompt is re-displayed.

To login into sqsh interactive terminal, run sqsh -S localhost -U sa. You will be prompted for the password and if the connection is successful, you will get the sqsh command prompt '>1'. You exit from the sqsh interactive session by typing the command exit or quit` to end the session.

sqlcmd

The sqlcmd utility in SQL Server lets you submit T-SQL statements or batches to a local or remote instance of SQL Server. Before using

Register Microsoft Ubuntu repository by running curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list command
Run sudo apt-get update and sudo apt-get install mssql-tools unixodbc-dev to update the sources list and installation commands.
Add /opt/mssql-tools/bin/ to your PATH environment variable by running echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc and source ~/.bashrc commands
To test connectivity, run sqlcmd -S localhost -U sa -Q 'SELECT @@VERSION and enter the password.

To open an sqlcmd interactive session, you use the same command just like sqsh command-line tool but replace sqsh with sqlcmd - sqlcmd -S localhost -U sa. You also use exit or quit to end the session.

While sqsh and sqlcmd may have similarities, sqlcmd has an improved feedback and output format than sqsh.

mssql-cli

mssql-cli is another cross-platform open source interactive command line query tool for SQL Server. Auto-completion, Syntax highlighting and Multi-line queries are some of the features that make mssql-cli more appealing to use compared to sqsh and sqlcmd.

However, at the time of this post, there is no support for Ubuntu 20.04 and Debian 10 which Parrot OS 4.11 is based on.

Install and Launch Azure Data Studio

Some people prefer working with GUI tools instead of command-line tools. If you have installed SQL Server Management Studio (SSMS),
you can connect to an MS SQL Server database installed on Linux through SSMS. As SSMS is only available in Windows operating systems, you can use Azure Data Studio instead of SSMS. Azure Data Studio is a free and cross-platform.

In the following sections, I step through the process of installing, connecting and running queries on Azure Data Studio.

Download Azure Data Studio
To extract the file, run sudo dpkg -i ./Downloads/azuredatastudio-linux-<version string>.deb. Replace <version string> with the version of the downloaded file. The version I downloaded is 1.29.0 so the command to run will be sudo dpkg -i ./Downloads/azuredatastudio-linux-1.29.0.deb.

Creating a Connection In Azure Data Studio

To Launch Azure Data Studio run azuredatastudio from a Terminal window. You can also launch by clicking on the Application Launcher, typing Azure in the search area and select Azure Data Studio.
Click on Add Connection on the left sidebar below SERVERS
Under Connection Details, fill in the fields as follows:
- Server Name: localhost or just type a . (dot).
- User name: sa
- Password:
- Name (optional): optionally type a preferred name or leave blank to use localhost as the connection name.
Click on Connect button

Run Query In Azure Data Studio

In Azure Data Studio, commands are run through the query window.

Launch Azure Data Studio if not already launched
Right click on localhost or the name of the connection you created
Select New Query
Paste or type your query statements. For our testing, type SELECT @@VERSION, and
Click on Run

Summary

In this post we went through the process of installing and setting up MS SQL Server 2019 Developer Edition on Parrot OS. The same steps would also be applicable to on any Debian based Linux distribution. After setting up of SQL Server was complete, we looked at working with SQL Server using command-line tools and Azure Data Studio.

Resources

Add Snowpack to ASP.NET Core Web App

Kagunda JM — Fri, 19 Mar 2021 01:42:19 +0000

Introduction

I stumbled on Snowpack while searching for a solution to resolve a ParcelJS error.

Building my application assets with ParcelJS worked without errors but when running the application, none of the JS scripts were running. Upon checking the browser console window, I was getting the following error: Uncaught (in promise) Error: Cannot find module. I never managed to resolve the error but Snowpack appeared in one of the searches.

A quick look at the following two paragraphs on Snowpack's landing page and I made a decision to replace ParcelJS with Snowpack.

Snowpack is a lightning-fast frontend build tool, designed for the modern web. It is an alternative to heavier, more complex bundlers like webpack or Parcel in your development workflow. ...

Once you try it, it's impossible to go back to anything else.

Snowpack builds individual files and caches the built files. Whenever any cached file changes, then Snowpack rebuilds that single file alone. Snowpack will also rebuild and cache any new files added to a project. Snowpack runs through your package.json file application dependencies builds and converts the dependencies to JavaScript ESM modules where necessary and places these modules in the build output folder. File bundling is an opt-in using Snowpack's plugins.

In this post, I go through the steps of adding Snowpack to the build and publish process of an ASP.NET Core web application. I create a new application but the process is also applicable to an existing project.

Prerequisites

Make sure you have installed downloaded and installed .NET SDK.

You can verify that .NET is installed by opening a new terminal window and running the command dotnet.

Snowpack v3.0 introduced Streaming Imports which fetches imported packages on-demand thereby eliminating dependency on package managers like npm or yarn. In this post, I will stick with the traditional way of managing packages therefore make sure you have Node.js and npm or yarn installed.

Create Web Application

If you have an existing ASP.NET Core web application, you can skip this step. Also replace any mention of SnowpackTest project with the name of your project.

Open terminal window and run the following command to create a new web application dotnet new webApp -o SnowpackTest

Navigate to the project folder by running cd SnowpackTest

Open the project using your preferred source code editor. If you are using Visual Studio Code (what I will use in this post) then run code .

Create Snowpack project root

Create Client (or use your preferred name) folder in the root of the project. This will serve as the root project for Snowpack.

Move css and js folders from wwwroot to Client folder.

Move favicon.ico to the root of Client folder.

Open VS Code terminal (select View, Terminal from menu). This will open at the project's root folder.

Change directory to the Client folder by running cd Client command.

Create and empty package.json file by running npm init --yes from the terminal.--yes allows the command to generate the file without prompting you for any required data.

Install dependencies contained within the lib folder under wwwroot by running npm i bootstrap popper.js jquery jquery-validation jquery-validation-unobtrusive command

Install Snowpack as a dev dependency npm install --save-dev snowpack@3.0.13

Re-Create Files in wwwroot/lib Folder

We will re-create files contained within the wwwroot/lib folder under Client folder. The files we create are going reference files located within the node_modules folder.

Create bootstrap.css file under Client/css folder. Update the file with the following @import "bootstrap/dist/css/bootstrap.css"; statement.
Create bootstrap.js file under Client/js folder and insert import bootstrap from 'bootstrap'; to the file.
Create jquery.js file under Client/js folder and insert import jquery from 'jquery'; to the file.
I will combine the jQuery validation files but you can create individual separate files. Create jquery-validation.js file under Client/js folder. Insert the following two statements to the file import 'jquery-validation'; and import 'jquery-validation-unobtrusive';

The structure of your Client folder after completing above steps should resemble the following:

After the above steps, you can delete wwwroot folder. We will configure Snowpack to re-create the folder for us during the build process.

Configure Snowpack

Snowpack supports the following configuration files:

package.json - the file should contain a Snowpack config object ("snowpack": {...})
snowpack.config.js - this is a Javascript file exporting a Snowpack config object (module.exports ={ ... })
snowpack.config.json - A JSON file containing config ({ ... })

You can also use command-line interface (CLI) flag to provide a different config file (snowpack --config ./path/to/snowpack.deploy.json). CLI flags take precedence over other configuration file settings.

Open package.json located within Client folder and add a Snowpack config object above the dependencies config object or anywhere within the file.

Update the config object with the following:

"scripts": {
  "build-snowpack": "snowpack build"
},
"snowpack": {
    "exclude": [
      "*.js",
      "*.json"
    ],
    "buildOptions": {
      "out": "../wwwroot/dist"
    }
},

We want to exclude any configuration files located within the Snowpack project root from the build process by using the exclude directive. By default, Snowpack excludes files within node_modules folder.

out is the local directory where Snowpack will output our final build.

Test Snowpack Build

Open VS Code terminal if not already open.

If the terminal opens within the web application root folder, run npm run build-snowpack --prefix ./Client command. --prefix points npm to the _package.json` file location.

If the terminal is on Client folder, run npm run build-snowpack command.

After the command completes, you get an output similar to the following:

[snowpack] ! building source files...    
[snowpack] ✔ build complete [0.08s]    
[snowpack] ! building dependencies...    
[snowpack] ✔ dependencies ready! [2.83s]    
[snowpack] ! verifying build...    
[snowpack] ✔ verification complete [0.00s]    
[snowpack] ! writing build to disk...    
[snowpack] watching for changes...

The wwwroot folder will be re-created at the root of the project. CSS and JS files within Client folder will be copied to the wwwroot/dist folder under respective folders.

Snowpack will also create a _snowpack folder within wwwroot/dist folder. This is the folder for Snowpack metadata. You can set a different name for this folder by setting the value under buildOptions.metaUrlPath.

Any CSS and JS files within wwwroot/dist folder that reference packages from the node_modules get updated during the build process to point to the _snowpack folder. For example Client/css/bootstrap.css contains @import "bootstrap/dist/css/bootstrap.css";. After the build process, wwwroot/dist/css/bootstrap.css will contain import "../_snowpack/pkg/bootstrap/dist/css/bootstrap.css";.

Update JS and CSS File References

Open _Layout.cshtml file located within Pages/Shared folder. Replace CSS link references with the following:

<link rel="shortcut icon" href="~/dist/favicon.ico">
<link rel="stylesheet" href="~/dist/css/bootstrap.css" />
<link rel="stylesheet" href="~/dist/css/site.css" />

At the bottom of the file, update the script references with the following:

<script type="module" src="~/dist/js/jquery.js"> </script>
<script type="module" src="~/dist/js/bootstrap.js"></script>
<script type="module" src="~/dist/js/site.js"></script>

Open _ValidationScriptsPartial.cshtml within Pages/Shared folder and replace the contents with <script type="module" src="~/dist/js/jquery-validation.js"></script>

By declaring type="module" on our script tags, we tell the browser that we are using JavaScript's ES Modules (ESM). Failing to add type="module" on the scripts will display an Uncaught SyntaxError: Cannot use import statement outside a module on the browser console window when the application runs.

Configure Application Build and Changes Watch

Before we start watching Snowpack build process, let's update our package.json file with a command for watching the build process. Open ./Client/package.json file and append "watch-snowpack": "snowpack build --watch" to the scripts object. `--watch will ensure that the build process does not complete and exit but will continue watching for any file changes that Snowpack is to build.

"scripts": {
   "build-snowpack": "snowpack build",
   "watch-snowpack": "snowpack build --watch"
},

Open VS Code integrated terminal if not already open. Make sure you are at the root of project. If you are on Client folder, run cd .. command to take you back to the project root folder.

Run dotnet watch run command. This will build the project and continue watching for any changes to the files.

Split the terminal window by right clicking the terminal window and selecting Split from the context menu.

On the new terminal tab, run npm run watch-snowpack --prefix ./Client command. Snowpack will build all the files and continue monitoring files for any changes.

Test Snowpack Build and Watch

In a web browser, navigate to https://localhost:5001/ to display the default welcome page.

Open Pages/Index.cshtml in the code editor and replace <p>Learn about <a href="https://docs.microsoft.com/aspnet/core">building Web apps with ASP.NET Core</a>.</p> with the following

<div class="card w-50 mx-auto">
   <div class="card-body">
      <h5 id="name" class="card-title">Name</h5>
        <div class="card-text">Email: <span id="email" class="small font-weight-bold"></span></div>
        <div class="card-text">phone: <span id="phone"  class="small font-weight-bold"></span></div>
        <div class="card-text">Website: <span id="website" class="small font-weight-bold"></span></div>
        <button id="fetchUser" class="btn btn-primary mt-5">Fetch User</button>
      </div>
</div>

Save the file and .NET should rebuild the project for you.

We are going to fetch dummy random users from https://jsonplaceholder.typicode.com and display them on the homepage.

Open Client/site.js and update the file with the following code:

 const fetchUser = document.getElementById('fetchUser');
 fetchUser?.addEventListener('click', async function() {
    const userId = Math.floor(Math.random() * (10 - 1) + 1);
    const url = `https://jsonplaceholder.typicode.com/users/${userId}`;

    const response = await fetch(url);
    if (response.ok) {
      const user = await response.json();
      if (user) {
        document.getElementById('name').innerHTML = user.name;
        document.getElementById('email').innerHTML = user.email;
        document.getElementById('phone').innerHTML = user.phone;
        document.getElementById('website').innerHTML = user.website;
      }
    }
 })

Saving the changes, you will observe that Snowpack displays a [snowpack] File changed... message on the terminal.

Refresh the page on the browser and the page gets replaced with blank user data.

If you click on the Fetch User button, the homepage should now display data for a random user.

Creating a new JS or CSS file within the Client folder should trigger a build and the new file appended to the wwwwroot folder. Renamimg a file will however leave the previous file within the wwwroot folder. This should not be an issue as during project publishing, we will perform a clean build.

Kill all terminal tabs by right clicking on each open terminal tab and selecting Kill Terminal from the context menu.

Prepare Project for Publishing

The project publishing process will involve the following 3 tasks:

Embed Snowpack build process to the .NET publishing process
Minify our CSS and JS files
Remove un-used CSS classes

1. Embed Snowpack build process to the .NET publishing process

Open the project file SnowpackTest.csproj at the root of the project and update as follows:

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>net5.0</TargetFramework>

    <ClientRoot>$(ProjectDir)Client\</ClientRoot>
    <DefaultItemExcludes>$(DefaultItemExcludes);$(ClientRoot)/*.*;$(ProjectDir)*.Development.json</DefaultItemExcludes>

  </PropertyGroup>

  <Target Name="NpmRunPublish" BeforeTargets="Publish">
    <Exec WorkingDirectory="$(ProjectDir)" Command="npm run publish --prefix ./Client" />
  </Target>

</Project>

<ClientRoot>$(ProjectDir)Client\</ClientRoot> defines a property and value that will be referenced later in the project file.

$(ProjectDir) and $(DefaultItemExcludes) are predefined MSBuild properties.

DefaultItemExcludes defines patterns for files that MSBuild should exclude from the build process.

$(ClientRoot) references the property created earlier within the project file.

Within Client/package.json, add dotnet-publish command under scripts object.

"scripts": {
  "build-snowpack": "snowpack build",
  "watch-snowpack": "snowpack build --watch",
  "publish": "NODE_ENV=production snowpack build --config snowpack.publish.json"
},

publish uses CLI to tell Snowpack the configuration file (snowpack.publish.json) to use.

NODE_ENV=production signals to Node.js that we are running in production and not development environment which is the default.

2. Minify CSS and JS files.

Create Client/snowpack.publish.json file and update with the following:

{
  "exclude": [
    "*.js",
    "*.json"
  ],
  "plugins": [
    ["@snowpack/plugin-optimize", {"target" : "es2020"}]
  ],
  "buildOptions": {
    "out": "../publish/wwwroot/dist"
  }
}

You customize Snowpack build process using build plugins.

Open the integrated terminal and install Snowpack optimize plugin using following command:

npm install --save-dev --prefix ./Client @snowpack/plugin-optimize

The optimize plugin will minify the CSS and JS files.

3. Remove Un-used CSS Classes

Removing CSS class names will reduce the size of our CSS files.

Run the following command from the integrated terminal::

npm install --save-dev --prefix ./Client @snowpack/plugin-postcss postcss postcss-cli @fullhuman/postcss-purgecss

If you are running the command from the Client folder, omit the --prefix ./Client:

Update Client/snowpack.publish.json file to include Snowpack PostCSS plugin.

    {
      "exclude": [
        "*.js",
        "*.json"
      ],
      "plugins": [
        ["@snowpack/plugin-postcss"],
        ["@snowpack/plugin-optimize", {"target" : "es2020"}]
      ],
      "buildOptions": {
        "out": "../publish/wwwroot/dist"
      }
    }

Configure PostCSS to remove any unused CSS classes from our CSS files.

Create Client/postcss.config.js file and update with the following:

const purgecss = require('@fullhuman/postcss-purgecss')({
  content: [
    '../Pages/**/*.cshtml',
    './**/*.js',
  ],
  css: [ '../publish/**/*.css'],
});

module.exports = {
  plugins: [
    ...process.env.NODE_ENV === 'production' ? [purgecss] : []
  ]
}

content refers to the location of files containing CSS class names while css is the location of the CSS files. We have also configured PurgeCSS to run when the process environment is set to production.

Publish .NET Project

To publish the application, run dotnet publish -c Release -o ./publish from the terminal window. Make sure you are running the command from the project root folder.

If you open site.css and site.js within the publish, you will observe that the files are now compressed.

Try adding some unused CSS classes within Client/css/site.css and then publish the .NET project. You will observe that those un-used CSS classes are missing in the published CSS file.

Conclusion

This post addressed one way of adding Snowpack build tool to an ASP.NET Core project. I relocated files within default wwwroot folder to a different folder and let Snowpack create the wwwroot folder during the build process. By use of Snowpack plugins, I minified the JS and CSS files and removed any un-used CSS classes from CSS files. Modifying the .NET project file made it possible to embed Snowpack's build process to the .NET project publishing process.

Snowpack's Origin

You can watch Snowpack with Fred K. Schott or listen to the podcast

That's all for this post.

The Data Directory Contains an Old postmaster.pid File

Kagunda JM — Wed, 27 Jan 2021 17:39:51 +0000

PostgreSQL Connection Failure

Sometimes, computers have a life of their own. You shut down your laptop and on the next boot, you are unable to connect to a PostgreSQL 12 database.

Opening the installed Postgres.app, I notice it displays stale postmaster.pid file error.

Postmaster.pid

What the heck is the postmaster.pid file? Turns out that the postmaster.pid is a lock file used to prevent two instances of the same PostgreSQL server from running on the same data-directory.

While the server is running, its PID is stored in the file postmaster.pid in the data directory. This is used to prevent multiple server instances from running in the same data directory and can also be used for shutting down the server.

This is the first time I am encountering such an error. I click on the Start button to force a re-start and I get back a dialog with the following message: The data directory contains a postmaster.pid file, which usually means that the server is already running. When the server crashes or is killed, you have to remove this file before you can restart the server. Make sure that the database process is definitely not running anymore, otherwise your data directory will be corrupted.

Click on OK button to close the dialog window.

Resolving the Problem

Open Postgres.app
Click on Server Settings...
Click on Show button next to the Data Directory. This should open the data directory of your PostgreSQl installation.
Locate the postmaster.pid file
Delete the postmaster.pid file. Right click on the file and select Move to Bin
After deleting the file, opening or making the Postgres GUI app window have focus will change the error message from stale postmaster.pid file to Not running. Click Start button on Postgres GUI app to start PostgreSQL server.

Way Forward

Why not let PostgreSQL auto remove a stale postmaster.pid file? This would make a user manage a PostgreSQL database without knowing its internal workings. Pros and cons of auto removing a stale postmaster.pid exist and balancing between these two is not easy. Therefore, as at the time of this writing, the auto removal of the file remains an open issue and the user's responsibility.

How To Alter a Column Used By A View or Rule

Kagunda JM — Wed, 13 Jan 2021 13:03:53 +0000

In PostgreSQL, assume you have a table and view with the following definitions:

CREATE TABLE boq_items (
    id character varying(22) NOT NULL,
    item_no character varying(50) NOT NULL,
    activity_name character varying(255) NOT NULL,
    page_no int NOT NULL,
    qty numeric(14,2) NOT NULL,
    rate numeric(14,2) NOT NULL,
    bq_amt numeric(14,2) NOT NULL
);

CREATE VIEW vw_boq_item_names AS
    SELECT activity_name FROM boq_items;

Attempting to change the definition of activity_name column using ALTER TABLE boq_items ALTER activity_name TYPE text, ALTER activity_name SET NOT NULL; will return a cannot alter type of a column used by a view or rule. DETAIL: rule _RETURN on view vw_boq_item_names depends on column "activity_name" error . PostgreSQL will throw the same error if you attempt to change any column definition of the table.

PostgreSQL allows running DDL statements in a transaction. To resolve the error, we require to drop the view, run the alter statement and recreate the view but enclosing the these statements in a transaction.

BEGIN;
  DROP VIEW vw_boq_item_names;

  ALTER TABLE boq_items
    ALTER activity_name TYPE text,
    ALTER activity_name SET NOT NULL;

  CREATE VIEW vw_boq_item_names AS
    SELECT activity_name FROM boq_items;

COMMIT;

If you are using FluentMigrator database migration framework, exclude the BEGIN; and COMMIT; statements - otherwise the migration will fail with an error.

public override void Up()
{
  var sql = "DROP VIEW vw_boq_item_names;"  +

            "ALTER TABLE boq_items " +
              "ALTER activity_name TYPE text, " +
              "ALTER activity_name SET NOT NULL;" +

            "CREATE VIEW vw_boq_item_names AS " +
                "SELECT activity_name FROM boq_items;"

    Execute.Sql(sql);
}

In situations where a table has a lot of dependencies, or an object has cascading dependencies, a solution would be to create two functions - one to save the dependencies and the other to restore these dependencies. Each of these functions will require parameters for the schema and the object in the schema which has dependencies. Before changing the table and column definitions, call the function to save and drop the dependencies, make the definition changes and finally call the function to restore the dependencies.

select util.deps_save_and_drop_dependencies('mdm', 'global_item_master_swap');

alter table mdm.global_item_master_swap
alter column prod_id type varchar(128),
alter column prod_nme type varchar(512);

select util.deps_restore_dependencies('mdm', 'global_item_master_swap');

Stop worrying about table and view dependencies in PostgreSQL details how these functions work. The post includes links to an sql fiddle and a up to date gist.

I hope this will be helpful to anyone encountering cannot alter type of a column used by a view or rule error in PostgeSQL while updating table column definitions.

How to Drop All Tables in PostgreSQL Database

Kagunda JM — Mon, 19 Oct 2020 20:24:36 +0000

Introduction

The DROP TABLE command in PostgreSQL removes a table definition, all data and indexes of a table from a database. DROP TABLE will fail if the table has other objects that depend on it like views and foreign key definitions. The command will also fail and display a table does not exist message if the table being dropped does not exist. PostgreSQL does not have a drop all tables command and you have to define your own way of performing this task.

In this post, I explain how to drop one or multiple tables from a PostgreSQL database. Finally, I end the post with one approach that can be used to drop all tables from a database.

Drop Table Command Syntax

DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] is the formal syntax for deleting a table from a PostgreSQL database. The name of the table is the only required parameter for the drop table command. To run the drop table command successfully, the user running the command must either be the table owner, schema owner or a superuser.

When dropping a table or tables, you may optionally prefix the table names with the schema. A schema is a namespace that contains database objects(except roles and tablespaces) but unlike namespaces in normal programming usage or directories or folders in operating systems lingo, namespaces in PostgreSQL cannot be nested. Omitting a schema in object names automatically assumes a schema named public.

You can run the command or commands from a PostgreSQL interactive terminal (psql) or as a query if you are using graphical user interface administration tools such as pgAdmin, Beekeeper Studio, Adminer or any other GUI tool that will allow you to connect to a PostgreSQL database.

Drop Table Command Explained

DROP TABLE public.table_name; will drop the table table_name from the database. public is the schema that owns the table and may be omitted.Using DROP TABLE table_name; without the schema will also work. To delete more than one table, you can include the tables within the command but separate the table names with commas - DROP TABLE table01, public.table2, myschema.table01;.

IF EXISTS prevents PostgreSQL from throwing an error if the table being dropped does not exist. Running DROP TABLE IF EXISTS table_name; from an interactive terminal will return a NOTICE: table "table_name" does not exist, skipping while Beekeeper Studio responds with Query Executed Successfully: No Results".

Adding a CASCADE to the drop table command also drops any objects that depend on the table (such as views or foreign key references). RESTRICT prevents dropping of a table if any objects depend on the table. If CASCADE is not specified, the drop table command defaults to RESTRICT. Therefore, DROP TABLE table_name; is similar to DROP TABLE table_name RESTRICT;

Dropping All Tables

PostgreSQL does not have a specific command to drop all tables from a database. To drop all tables, we will create an anonymous code block and execute the code block. To accomplish the task, we select all table names for a schema from the information_schema.tables and store the names in a RECORD type variable. We then loop through these table names and execute the drop table command for each table name.

DO executes an anonymous code block in a procedural language. The code block is treated as though it were the body of a function with no parameters and returning no rows. The LANGUAGE is optional and may be written either before or after the code block. When language is omitted, it defaults to plpgsql.

The $$ is a dollar-quoted string constant (dollar quoting) and allow use of single quotes (') or backslashes (\) without having to escape them by doubling ('' or \\). Dollar quoting can be used to replace single quotes anywhere in SQL scripts and are used at the beginning and ending of SQL blocks. If you use more than one set in a block, you can put a token (label) between the $$ to make them unique ($SomeTag$Dianne's horse$SomeTag$).

DECLARE r RECORD; declares a variable of type RECORD to hold the table names.

current_schema() returns the name of the schema that is first in the search path. The current_schema() can be replaced by the actual schema name WHERE table_schema='public'. To drop tables for another schema, replace public with the name of the schema. Using the schema name can be useful to reduce the scope of deletion to only the tables owned by the schema.

BEGIN and END statements are used for wrapping multiple lines of SQL code into a statement block.

As the drop table SQL statements are generated dynamically, we quote the table names using quote_ident function.

You run the anonymous code block in the normal manner that you run a drop table command.

Summary

In this post, we looked the DROP TABLE command to drop a single table and multiple tables from a database. By enclosing the command in an anonymous code block, we extended the command to drop all tables from a PostgreSQL database.

That brings us to the end of the post. I hope you the post was helpful in one way or the other.

How To Integrate Asp.NET Core Project with Bootstrap, TailwindCSS and ParcelJS

Kagunda JM — Fri, 02 Oct 2020 20:11:09 +0000

Introduction

When you create a new ASP.NET Core Razor Pages project, the new project comes bundled with Bootstrap CSS framework and jQuery Javascript library. Files for both Bootstrap and jQuery are placed in a wwwroot/lib folder. The lib folder contains both compressed and un-compressed versions of all files your project may require.

During development, you add more CSS to customize the design of your final application. At times, the CSS you add may end up duplicating formatting under different class names. Coming up with these CSS class names at times also prove to turn out to be a challenge. This is where Tailwind CSS comes in handy. Tailwind CSS is a highly customizable, low-level CSS framework that gives you all of the building blocks you need to build bespoke designs. Each Tailwind CSS class (w-16) maps to a single CSS declaration (width: 4rem;). By combining these classes, you can freely apply any CSS declarations to a page without ever thinking about coming up with CSS class names. Tailwind does not contain any predefined components and this is where Bootstrap comes in handy. You let Bootstrap provide the components like buttons, cards, and more while enhancing these components using Taiwind CSS.

Combining both Bootstrap and Tailwind CSS will however result in a huge increases the size of the CSS files. ParcelJS to our rescue. Parcel is a web application bundler which offers blazing fast performance and requires zero configuration. Using Parcel will allow us to compress and combine the CSS files into a single file. Now we get the best from both Bootstrap and Tailwind CSS and end up with manageable minimized CSS files.

In this post, we create an ASP.NET Core razor web application, add Tailwind CSS to the project, bundle the project asset files with Parcel and finally add the bundling process to our project build process.

Prerequisites

Your development environment should already be set up to create ASP.NET Core applications and have Node JS installed.

If however you are starting from a clean slate, you can run the following installations to follow along:

Install Visual Studio Code
Install .NET Core SDK
Install C# for Visual Studio Code extension
Install Node.js.

Create ASP.NET Core Razor Project

Open a terminal or console window and run the following command dotnet new razor -o ParcelBootstrapTailwind and wait for the project creation process to complete. razor is the template of the project. To see a list of available templates, run dot new --help command. -o creates a folder for our project.

Open the project by running code ParcelBootstrapTailwind command.

Click Yes when the Required assets to build and debug are missing from ParcelBootstrapTailwind'. Add them? dialog is displayed.

If the dialog closes before you press the Yes button, select View > Command Palette.., type .NET and click on .NET: Generate Assets for Build and Debug.

From the main menu, select Run > Run Without Debugging and a browser page should open with a Welcome message.

Now that our project runs using the bundled CSS library, we can move to the next phase. Back in the text editor, stop the running project by selecting Run > Stop Debugging.

Prepare Project To Use Parcel

We need to do a bit of re-organization before using ParcelJS to bundle our asset files.

Create an assets folder in the root of the project. Your are free to name the folder differently.
Within the assets folder, create a file and name it main.js. You can use a different name for the file. This file will be the entry point for Parcel.
Copy or move js and css folders from wwwroot to the assets folder
Delete the wwwroot folder
Open Pages/Shared/Layout.cshtml file and replace <link rel="stylesheet" href="~/lib/bootstrap/dist/css/bootstrap.min.css" /> and <link rel="stylesheet" href="~/css/site.css" /> with <link rel="stylesheet" href="~/dist/css/main.css" />. Scroll down the file and replace <script src="~/lib/jquery/dist/jquery.min.js"></script>, <script src="~/lib/bootstrap/dist/js/bootstrap.bundle.min.js"></script> and <script src="~/js/site.js" asp-append-version="true"></script> with <script src="~/dist/js/main.js"></script>
From the main menu, select View > Terminal.
We need to create a package.json: file at the root of the project. Run npm init -y command from the Terminal window. -y will make the command run using the default options without prompting us. If you want to supply the values for any of the required options, then omit the -y from the command.
Install Bootstrap and jQuery. Run npm install bootstrap jquery jquery-validation jquery-validation-unobtrusive popper.js from the Terminal.
We will include the jQuery validation Javascript files into our build process. Therefore, Pages/Shared/_ValidationScriptsPartial.cshtml file is no longer needed and can be deleted.
Run npm i -D parcel-bundler parcel-plugin-purgecss parcel-plugin-custom-dist-structure command to install Parcel and plugins we will require in our build process. Both parcel-plugin-purgecss and parcel-plugin-custom-dist-structure plugins are optional and may be omitted. However, we will configure parcel-plugin-purgecss to remove any unused CSS classes. Parcel.js outputs all built resources in the same folder. We will therefore use parcel-plugin-custom-dist-structure to move files to custom folders within the output folder.
Open main.js and add code to import Bootstrap and jQuery as follows:
Open package.json file at the root of the project and add scripts to build Parcel. Replace the scripts section with the following:

Scroll down and add the following code to move files to custom folders. Add the code below "devDependencies" or any other location:
To remove any un-used CSS class during deployment, create a purgecss.config.js. We will update this file with the content files that contain CSS class names and the location of our CSS files.
From the terminal window, let us test that Parcel build works as expected. From the Terminal window, run npm run build and npm run publish. If the builds run to completion, you should output similar to the following:
To complete this section, let's run the the application. Select Run > Run Without Debugging from the main menu and a browser page should open with a Welcome message similar to the way it was before using Parcel to bundle our CSS and Javascript files.

Add Tailwind CSS

Open the Terminal window and run npm install tailwindcss. This command will add the Tailwind CSS package to our project.

Create assets/css/tailwind.css file and update with the following:

Make sure @tailwind base; is commented out to avoid conflicting with Bootstrap.

Some CSS class are common to both Bootstrap and Tailwind. We are therefore going to use a prefix for Tailwind CSS to differentiate between Bootstrap and Tailwind classes. Create a tailwind.config.js file within the root of the project by running npx tailwindcss init command from the terminal window. Open the file and add prefix: 'tw-' prefix to use for Tailwind classes. Also, as we are using a Parcel parcel-plugin-purgecss plugin for purging un-used classes, we will therefore add purge: false to disable the purge feature within Tailwind. After these changes, your tailwind.config.js file should look similar to the following:

Add an import import './css/tailwind.css' statement for the tailwind.css within main.js just before the import './css/site.css'.

The final step for adding Tailwind to our project is by adding a PostCSS plugin to our Parcel build chain. Create postcss.config.js file at the root of the project.

If we now run npm run build, we should see that there is an increase in size on the main.css. The size however reduces drastically when we run the npm run publish.

To test whether the Tailwind CSS classes are being used, open Pages/_Layout.cshtml and change the background color of the tag by adding a class="tw-bg-gray-200" class. Also add a gradient to the nav bar by adding tw-bg-gradient-to-r tw-from-teal-400 tw-to-blue-500 CSS classes to the tag. Run npm run build command and select Run > Run Without Debugging from the main menu. You should observe that the home page and the nav bar are now displayed using our new changes.

When working with Tailwind CSS classes in Visual Studio Code, the Tailwind CSS IntelliSense extension will come in handy.This extension provides features such as autocomplete, syntax highlighting, and linting. To install the extension, open your Extensions and search for Tailwind CSS IntelliSense.

Also, if you have some CSS code and cannot remember out its equivalent class name in Tailwind CSS, Tailwind CSS class search will be there to assist. Type a CSS style name and the website will display both the Tailwind CSS class name and style properties for you.

Add Parcel To Project MsBuild Process.

To integrate Parcel build process to the project build process, we make some modifications to our project file. This will ensure that the npm run commands run before the project build and publish process is performed.

The Microsoft Build Engine (MSBuild) tool uses properties, items, tasks, and targets to build a project. Properties are key-value pairs which direct MSBuild what to do. You define properties using <PropertyName>Value</PropertyName> elements. MSBuild has its own reserved properties. You reference property values using $(PropertyName) syntax. Items are inputs into the build system while tasks are commands which are executed in order to complete a Target. Targets group tasks together.

In our project, we require properties for assets, nodemodules, and wwwroot folders. Open ParcelBootstrapTailwind.csproj and add these properties within <ProjectGroup> as follows:

Next we create our Targets. The first Target will check whether NodeJS is installed and that the node_modules folder exists. If NodeJS is not installed, it will not be possible to proceed with with the build and we request the user to first install NodeJS. If node_modules folder does not exist, we first start by re-installing NPM packages before proceeding with the build. The other two targets will be called depending on whether we are doing a normal build or publishing our application. If we are running a normal build, the build process will invoke npm run build otherwise npm run publish will be invoked. The updated ParcelBootstrapTailwind.csproj should be similar to the following:

From the main menu, select Run > Run Without Debugging and if we typed everything without errors, a browser page should open with a Welcome message. To test for the project publishing, run dotnet publish -c Release -o ./publish from the terminal. You will observe the project will build and create a publish folder at the root of the project. The size of the CSS file within the publish/wwwroot/dist/css/main.css will be much smaller than the one within wwwroot/dist/css/main.css.

Conclusion

Not all of us are good at CSS and designing websites. By using CSS frameworks such as Bootstrap, we can use existing predefined components to create good looking websites. Using the low-level classes provided by Tailwind CSS enhances the look and feel of our websites without having to create custom classes. And when our websites are ready for deployment, we can bundle our website assets with no configuration using Parcel.

In this post, we set up a new project in Visual Studio Code, added Bootstrap and Tailwind and finally bundled our asset files using Parcel. As a final step, we automated the assets bundling commands into the project build process thereby ending up with minified asset files ready for deployment.

Thank you for reading this far and I hope you picked something along the way that can be of use in your current or next project.

Happy Bootstraping and Tailwinding with Parcel!

Git - Not Something We Can Merge

Kagunda JM — Sun, 30 Aug 2020 19:48:34 +0000

I use Git for version control. My workflow is simple; create branch from the main branch, make changes on the new branch, commit the changes and merge the changes to the main branch. I have aliased the git command to g and combine the commands in a single line in the form g checkout master;g merge branch-name; and it works.

It was therefore a surprise when I ran g checkout master;g merge add_progress;g push;g checkout add_progress and Git responded with merge: add_progress - not something we can merge. Being a moderate git user, my first thought was I had used a restricted word in the branch name. Could I have typed the branch name wrongly? I rechecked the branch name and re-ran the command--same result.

Before searching the cause of the error, I replaced the branch name with the commit ID for the merge command. That worked without raising the error. So, what was wrong with my branch name? Does Git have any restrictions on branch names?

It Could Be a Silly Typo

Turns out, the error can arise from a typo in the branch name. Taking a deeper look on my Git command, I realized I had typed an underscore (_) for the branch name (add_progress) instead of a dash (add-progress), damn it!

I could not figure out why the Git team decided against a simplified error like merge: - invalid branch name. This begged the question, are there any restrictions or guidelines on branch naming?

Git Branch Naming Conventions

While conventions are good for consistency, no single common convention exists for branch naming. Different teams adopt different styles for naming their Git branches.

Some teams prefer prefixing branch names with (first and last names followed by a forward and branch description separated by hyphens)[https://dev.to/g_abud/advanced-git-reference-1o9j].

Others break down features into subtasks and name branches based on feature-subtasks. This would have a billing-module have branches names such as billing-module-setup, billing-ui-changes and billing-database-migration.

Change types as a prefix for naming branches (new-feature/twitter-feed, refactor/homepage/carousel, fix/twitter-feed/number-of-tweets) is adopted by other teams. Using this style and separating the change type and feature with a slash (/) makes Git create separate folders in git/refs/heads/ directory of your repository.

BEM (Block Element Modifier) is another convention used in naming Git branches. This style takes reason__details--tag format. Reason is the change type, details provide a short description of the change while the tag is optional. When the tag is available, it may refer to an external tracker or other extra details.

Branch Naming Restrictions

Restrictions on naming branches borrows from restrictions in reference names. Names cannot:

begin with a dot (.) or end with the sequence .lock.
have two consecutive dots .. anywhere.
have ASCII control characters, space, tilde (~), caret (^), or colon (:) anywhere.
have question-mark (?), asterisk (*), or open bracket ([) anywhere.
begin or end with a slash (/) or contain multiple consecutive slashes
end with a dot (.).
contain a sequence (@{).

Git ignores a backslash (\) in a name. For example g checkout -b po\le will create a branch named pole.

An opening bracket ( in a name produces an incomplete command prompt (>). You have to press Ctrl+C to revert to your normal terminal prompt. Maybe the ( has a special meaning to Git.

Every cloud has a silver lining. Without the error, I would not have taken the effort to learn how others handle Git branching conventions and naming.

DEV Community: Kagunda JM

ODK Central Domain Change: A Painless Backup and Restore Tutorial

Introduction

Prerequisites

Backup ODK Central Main Data on the Source Server

List ODK Central Docker Containers

Backup Enketo Redis Data and Secrets

Copy Backups From Source Server To Destination Server

Restore ODK Central Direct Backup File

Restore Enketo Redis Data and Secrets

Update Destination Server Enketo Config File.

Change Domain Names

Create Domain Update Script Files

Update Enketo Redis Main Database

Update Enketo Redis Cache Database

Conclusion

Resources

How to Transform SQL Queries to Crosstabs in PostgreSQL

Crosstabs Using CASE Conditional Expression

Using Common Table Expressions (CTE) For A Crosstab

Crosstabs Using Aggregate FILTER Clause

Using PostgreSQL crosstab() Function

Using A PostgreSQL crosstab() Function With More Than Three Columns

Using ARRAY Data Type To Re-Arrange Extra Crosstab Columns

Conclusion

Resources

How To Install ODK Central on Ubuntu 20.04

Introduction

Prerequisites

Checking DNS Propagation

Checking for Available Disk Space

Install Docker Engine

Configure Docker Data Root Folder

Use Docker As Non-Root User

Install Docker Compose

Install ODK Central

Starting Up ODK Central

Create ODK Central Administrator

Summary

Resources

How to Drop Multiple Databases in PostgreSQL

Introduction

Anonymous Code Block

Interactive Terminal

Using Shell Script

Wrapping Up

How To Export Data From Database Query To XML

Introduction

Create XML Manually

Output XML Using Database Inbuilt Functions

PostgreSQL

SQL Server

Summary

Resources

Installing MS SQL Server 2019 on Parrot OS

Introduction

Prerequisites

Configure SQL Server 2019 Repository

Install SQL Server Developer Edition

Install SQL Server Command Line Tools

Install and Launch Azure Data Studio

Creating a Connection In Azure Data Studio

Run Query In Azure Data Studio

Summary

Resources

Add Snowpack to ASP.NET Core Web App

Introduction

Prerequisites

Create Web Application

Create Snowpack project root

Re-Create Files in wwwroot/lib Folder

Configure Snowpack

Test Snowpack Build

Update JS and CSS File References

Configure Application Build and Changes Watch

Test Snowpack Build and Watch

Prepare Project for Publishing

1. Embed Snowpack build process to the .NET publishing process

2. Minify CSS and JS files.

3. Remove Un-used CSS Classes

Using PostgreSQL `crosstab()` Function

Using A PostgreSQL `crosstab()` Function With More Than Three Columns