MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for September, 2019

Session Variables

without comments

In MySQL and Oracle, you set a session variable quite differently. That means you should expect there differences between setting a session variable in Postgres. This blog post lets you see how to set them in all three databases. I’m always curious what people think but I’m willing to bet that MySQL is the simplest approach. Postgres is a bit more complex because you must use a function call, but Oracle is the most complex.

The difference between MySQL and Postgres is an “@” symbol versus a current_setting() function call. Oracle is more complex because it involves the mechanics in Oracle’s sqlplus shell, SQL dialect, and PL/SQL language (required to assign a value to a variable).

MySQL

MySQL lets you declare a session variable in one step and use it one way in a SQL statement or stored procedure.

  1. You set a session variable on a single line with the following command: 

    SET @my_variable_name := 'My Value';

  2. You can query a variable from the pseudo table dual or as a comparison value in the SELECT-list 

    SELECT @my_variable_name AS "The Value" FROM dual;

    or WHERE clause

    SELECT column_name
FROM   table_name
WHERE  column_name = @my_variable_name;

Postgres

Postgres lets you declare a session variable in one step and use it one way in a SQL statement or stored procedure.

  1. You set a session variable in a single line. It iss critical to note that you must use double quotes around the session variable name and single quotes for the value. You raise an error when you use a single quote instead a double quote around the session variable name. The syntax is: 

    SET SESSION "videodb.table_name" = 'new_hire';

  2. You can query a variable from the pseudo table dual or as a comparison value in the SELECT-list with the current_setting() function call. 

    SELECT current_setting('videodb.table_name') AS "The Value";

    or WHERE clause

    SELECT column_name
FROM   table_name
WHERE  column_name = current_setting('videodb.table_name');

Oracle

There are two steps required to declare a session variable in Oracle. First, you need to define the variable in the SQL*Plus session. Oracle lets you define a variable like you would define a variable in the C language, using extern before the variable’s type. Second, you assign a value to the session variable in an anonymous PL/SQL block. There is no single line statement to declare a variable with an initial value.

  1. You set a session variable by using the VARIABLE keyword, a variable name, and data type. The supported data types are: BLOB, BFILE, BINARY_DOUBLE, BINARY_FLOAT, CHAR, CLOB, NCHAR, NCLOB, NVARCHAR2, REFCURSOR, and VARCHAAR2. You define a variable with the following syntax: 

    VARIABLE bv_variable_name VARCHAR2(30)

  2. You assign a value to the bind variable inside an anonymous block by prefacing the variable name with a colon. You assign values inside PL/SQL with the walrus operator (:=) and a string enclosed by single quotes. Anonymous blocks start with a BEGIN and end with an END followed by a semicolon (;) and a forward slash (/) to dispatch the block for execution. The following example shows a full block: 

    BEGIN
  :bv_variable_name := 'Some Value';
END;
/

  3. You can query any declared variable from the pseudo table dual or as a comparison value in the SELECT-list 

    SELECT :bv_variable_name FROM dual;

    or WHERE clause

    SELECT column_name
FROM   table_name
WHERE  column_name = :bv_variable_name;

Written by maclochlainn

September 28th, 2019 at 9:01 pm

Posted in MySQL,MySQL 8,Oracle,Oracle 11g,Oracle 12c,Oracle 18c,Postgres,PostgreSQL,Uncategorized

Tagged with , ,

Python-Postgres Query

without comments

As I committed to a student, here are sample programs for writing a Python query against the Postgres 11 database. The first one returns rows or tuples. The latter formats the text returned as columns.

If you’re one of many looking for the key psycopg2 driver library, you can find it in most distro repositories as: python3-psycopg2. You can use dnf or yum to install it separately or you can install pgadmin4, which includes the psycopg2 library.

The first example returns the entire row from a new_hire table with two rows:

import psycopg2
 
try:
  # Open a connection to the database.
  connection = psycopg2.connect( user="student"
                               , password="student"
                               , port="5432"
                               , dbname="videodb")
 
  # Open a cursor.
  cursor = connection.cursor()
 
  # Assign a static query.
  query = "SELECT * FROM new_hire"
 
  # Parse and execute the query.
  cursor.execute(query)
 
  # Fetch all rows from a table.
  records = cursor.fetchall()
 
  # Read through and print the rows as tuples.
  for row in range(0, len(records)):
    print(records[row]) 
 
except (Exception, psycopg2.Error) as error :
  print("Error while fetching data from PostgreSQL", error)
 
finally:
  # Close the database connection.
  if (connection):
    cursor.close()
    connection.close()

The first example returns the rows as tuples, which is probably desired if you want to consume the result in another Python program. Here’s the output retrieved:

(1001, 'Malcolm', 'Jacob', 'Lewis', datetime.date(2018, 2, 14))
(1002, 'Henry', None, 'Chabot', datetime.date(1990, 7, 31))

The second one returns the rows and formats the columns into output for a csv style file:

import psycopg2
 
try:
  # Open a connection to the database.
  connection = psycopg2.connect( user="student"
                               , password="student"
                               , port="5432"
                               , dbname="videodb")
 
  # Open a cursor.
  cursor = connection.cursor()
 
  # Assign a static query.
  query = "SELECT * FROM new_hire"
 
  # Parse and execute the query.
  cursor.execute(query)
 
  # Read through and print the formatted columns of each row.
  for (new_hire_id, first_name, middle_name, last_name, hire_date) in cursor:
    if (isinstance(middle_name,type(None))):
      print("{},'{} {}','{:%d-%b-%Y}'".format(new_hire_id, first_name, last_name, hire_date))
    else:
      print("{},'{} {} {}','{:%d-%b-%Y}'".format(new_hire_id, first_name, middle_name, last_name, hire_date))
 
except (Exception, psycopg2.Error) as error :
  print("Error while fetching data from PostgreSQL", error)
 
finally:
  # Close the database connection.
  if (connection):
    cursor.close()
    connection.close()

The second one returns the rows and formatted columns for a csv style file:

1001,'Malcolm Jacob Lewis','14-Feb-2018'
1002,'Henry Chabot','31-Jul-1990'

As always, I hope these help those looking for a starting place with Python and Postgres.

Written by maclochlainn

September 23rd, 2019 at 1:23 am

Posted in Fedora,Linux,Postgres,PostgreSQL,Python,Python 3.x

Tagged with

pgAdmin4 on Fedora 30

without comments

While attempting an install of pgAdmin and updating a Fedora 30 environment, I encountered a conflict on the upgrade of MySQL 8.0.17-1 to 8.0.17.2. The community-mysql-8.0.17-2.fc30.x86_64 had conflicts with:

  • mysql-community-client-8.0.17-1.fc30.x86_64 package
  • mysql-community-server-8.0.17-1.fc30.x86_64 package

I tried to update the system before install pgadmin4 with the following syntax:

dnf -y update && dnf -y install pgadmin4

The dnf utility raise the following MySQL package errors during transaction checking:

Display detailed console log →

Error: Transaction check error:
  file /usr/bin/mysql conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysql_config_editor conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqladmin conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqlbinlog conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqlcheck conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqldump conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqlimport conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqlpump conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqlshow conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqlslap conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/ibd2sdi conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/innochecksum conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/my_print_defaults conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/myisam_ftdump conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/myisamchk conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/myisamlog conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/myisampack conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/mysql_secure_installation conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/mysql_ssl_rsa_setup conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/mysql_tzinfo_to_sql conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/mysql_upgrade conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqld_pre_systemd conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/perror conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib/systemd/system/mysqld.service conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib/systemd/system/mysqld@.service conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/adt_null.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/auth_socket.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/component_audit_api_message_emit.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/component_log_filter_dragnet.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/component_log_sink_json.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/component_log_sink_syseventlog.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/component_validate_password.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/connection_control.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/ddl_rewriter.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/group_replication.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/ha_example.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/ha_mock.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/innodb_engine.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/keyring_file.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/keyring_udf.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/libmemcached.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/locking_service.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/mypluglib.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/mysql_clone.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/mysql_no_login.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/rewrite_example.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/rewriter.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/semisync_master.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/semisync_slave.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/validate_password.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/version_token.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/sbin/mysqld conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /var/lib/mysql conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /var/lib/mysql-keyring conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/libmysqlclient.so.21.1.17 conflicts between attempted installs of community-mysql-libs-8.0.17-2.fc30.x86_64 and mysql-community-libs-8.0.17-1.fc30.x86_64

Since I’m not sure what’s wrong or how to fix it, I’ve put it in my queue of things to get to later. However, when I figure it out I’ll update this blog page with the solution or work around. If anybody knows the fix and would like to share, please let me know.

I removed the pending update packages with the following command:

dnf clean packages

Then, I simply installed pgadmin4 with the following command:

dnf -y install pgadmin4

Display detailed console log →

 

The pgadmin4 configuration instructions can be found for several Linux versions at Josphat Mutai’s Computing for Geeks web page. On Fedora 30, you need to do the following:

  • Install, start, and enable Apache as the httpd service unless you already have done that.
  • Copy the /etc/httpd/conf.d/pgadmin4.conf.sample file to /etc/httpd/conf.d/pgadmin4.conf, which is a new file.
  • Restart the httpd service to incorporate the pgadmin4 configuration file.

After that, you create the following new directories as the root or sudo user:

  • /var/lib/pgadmin4
  • /var/log/pgadmin4

You can make both directories with a single mkdir command, like:

mkdir -p /var/lib/pgadmin4 /var/log/pgadmin4

As the root or sudo user, change the ownership of these two directories to the apache user with the following syntax:

chown -R apache:apache /var/lib/pgadmin4 /var/log/pgadmin4

You add the following four statements to the config_distro.py file in the /usr/lib/python3.7/site-packages/pgadmin4-web directory as the root or sudo user:

LOG_FILE = '/var/log/pgadmin4/pgadmin4.log'
SQLITE_PATH = '/var/lib/pgadmin4/pgadmin4.db'
SESSION_DB_PATH = '/var/lib/pgadmin4/sessions'
STORAGE_DIR = '/var/lib/pgadmin4/storage'

You need to setup the pgadmin user with the following python3 command:

python3 /usr/lib/python3.7/site-packages/pgadmin4-web/setup.py

Enter the following values, a real email address and a password twice:

NOTE: Configuring authentication for SERVER mode.
 
Enter the email address and password to use for the initial pgAdmin user account:
 
Email address: admin@example.com   
Password: your_password
Retype password: your_password
pgAdmin 4 - Application Initialisation
======================================

Assuming you have an enabled firewall, you need to issue the following two commands as the root or sudo user:

rirewall-cmd --permanent --add-service=http
firewall-cmd --reload

You invoke pgAdmin4 from within a browser window with the following URL for a stand alone workstation (for a workstation on a DNS network you would enter pgadmin.domain.domain_type in lieu of localhost):

pgadmin/localhost/pgadmin4

You most likely will encounter an Internal Server Error, the recommended fix is reputed to be:

ausearch -c 'httpd' --raw | audit2allow -M my-httpd
semodule -X 300 -i my-httpd.pp

It didn’t work for me. At the end of the process, I have an Internal Server Error. It is something that I’ll try to fix next. The actual error message:

Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request.
 
Please contact the server administrator at root@localhost to inform them of the time this error occurred, and the actions you performed just before this error.
 
More information about this error may be available in the server error log.

If somebody figures out the last step before I do, that’s great. Let me and everybody else know the mystery.

On a positive note, the pgadmin4 package provided the psycopg2 library. I had looked for it as a psycopg2 package but it is in python3-psycopg2 package.

Written by maclochlainn

September 21st, 2019 at 5:29 pm

Posted in Linux,MySQL,MySQL 8,pgAdmin4,Postgres,Python,Python 2.7,Python 3.x

Tagged with , , ,

Postgres & Sequences

without comments

Many developers already know about the change from int to serial in Postgres. Serial is simpler because it automatically makes the column not null and links it to a sequence. The sequence is created with the following naming convention:

table_name_column_name_seq

The Postgres serial type always has a start with value of 1. Though, you can alter it immediately after creating the table.

You can create a serial surrogate key column like this:

CREATE TABLE new_hire
( new_hire_id  SERIAL        CONSTRAINT new_hire_pk PRIMARY KEY
, first_name   VARCHAR(20)   NOT NULL
, middle_name  VARCHAR(20)
, last_name    VARCHAR(20)   NOT NULL
, hire_date    DATE          NOT NULL
, UNIQUE(first_name, middle_name, hire_date));

If you want the sequence to start with 1001, you can ALTER the sequence after creating the table. Before you change the sequence staring value, you may want to confirm the automatic name assigned to the sequence. You can do that with the following query:

SELECT pg_get_serial_sequence('new_hire','new_hire_id');

It should return:

     pg_get_serial_sequence      
---------------------------------
 public.new_hire_new_hire_id_seq
(1 row)

Here’s the syntax for changing the current starting value of a Postgres sequence:

ALTER SEQUENCE new_hire_new_hire_id_seq RESTART WITH 1001;

If you want more control, you still have it with the older syntax. The older way requires that you create the sequence before you create the table, like this:

CREATE SEQUENCE new_hire_s START WITH 1001;

Then, you create the new_hire table like this:

CREATE TABLE new_hire
( new_hire_id  INT           NOT NULL DEFAULT NEXTVAL('new_hire_s')
, first_name   VARCHAR(20)   NOT NULL
, middle_name  VARCHAR(20)
, last_name    VARCHAR(20)   NOT NULL
, hire_date    DATE          NOT NULL
, PRIMARY KEY(new_hire_id)
, UNIQUE(first_name, middle_name, hire_date));

You probably will test this from a file because tables are tedious type and usually much larger than the sample new_hire table. If you wonder how to do that, typing help from the psql> prompt will not reveal it. The key is the \i command, like this:

\i new_hire.sql

Unfortunately, the psql> command-line shell does not allow for embedding a means to log the activity of your script file. You must do that from the Linux command-line through redirecting the console to a log file.

The rules are pretty simple for logging file results:

 -a, --echo-all       echo all input from script
 -b, --echo-errors    echo failed commands
 -e, --echo-queries   echo commands sent to the server

Here’s an example connecting to a videodb database with a student user:

psql -d videodb -U student -W -f postgres_tables.sql -a &> postgres_tables.txt

As always, I hope this helps those looking for how to solve the puzzle of a new environment.

Written by maclochlainn

September 16th, 2019 at 12:39 am

Posted in Linux,Postgres,PostgreSQL,sql

Tagged with ,

mysqli Strict Standards

with 2 comments

Six years ago I wrote a common lookup post to illustrate the effectiveness of things used throughout your applications. Now, I’m updating my student image with a more complete solution to show how to avoid update anomalies.

In the prior post, I used a while loop in PHP, like the following:

do {
      ...
} while($stmt->next_result());

Using PHP Version 7.3.8 and MySQL 8.0.16, that now raises the following error message:

Strict Standards: mysqli_stmt::next_result(): There is no next result set. Please, call mysqli_stmt_more_results()/mysqli_stmt::more_results() to check whether to call this function/method in /var/www/html/app/library.inc on line 81

You can see this type of error when you set the following parameters in your file during testing:

ini_set('display_errors',1);
ini_set('display_startup_errors',1);
error_reporting(E_ALL);

You can read more about error handling at this web page. The new and strict compliance standard for mysqli managing rows is:

do {
      ...
} while($stmt->more_result());

As always, I hope this helps those looking for an answer.

Written by maclochlainn

September 14th, 2019 at 10:30 pm

Posted in LAMP,MySQL,MySQL 8,mysqli,PHP

Tagged with ,

MySQL Update in mysqli

without comments

Somebody didn’t like the MySQLi Update Query example on the tutorialspoint.com website because it use the procedure mysqli_query style. Here’s a simple example of using the object-oriented method version. More or less, instead of query it uses the more intuitive execute() method.

The update_member function contains the logic and below it is a call to the test the function. It relies on a MySQLCredentials.inc file that contains the hostname, user name, password, and database name. You can create create member table, like my example in MySQL 8, or any other table in your MySQL database.

<?php
/*
||  Function Name: update_member
*/
function update_member($account_number, $member_type, $credit_card_number, $credit_card_type) {
 
  // Include the credentials file if omitted.
  include_once("MySQLCredentials.inc");
 
  // Assign credentials to connection.
  $mysqli = new mysqli(HOSTNAME, USERNAME, PASSWORD, DATABASE);
 
  // Check for connection error and print message.
  if ($mysqli->connect_errno) {
    print $mysqli->connect_error."<br />";
    print "Connection not established ...<br />";
  }
  else {
    // Initial statement.
    $stmt = $mysqli->stmt_init();
 
    /* Disabling auto commit when you want two or more statements executed as a set.
    || ------------------------------------------------------------
    ||  You would add the following command to disable the default
    ||  of auto commit.
    ||  ------------------------------
    ||   $mysqli->autocommit(FALSE);
    || ------------------------------------------------------------
    */
 
    // Declare a static query.
    $sql = "UPDATE   member\n"
         . "SET      member_type = ?\n"
         . ",        credit_card_number = ?\n"
         . ",        credit_card_type = ?\n"
         . "WHERE    account_number = ?\n";
 
    /* Prepare statement.
    || ------------------------------------------------------------
    ||  Please note that the bind_param method is a position 
    ||  rather than named notation, which means you must provide
    ||  the variables in the same order as they are found in
    ||  the defined $sql variable as "?".
    || ------------------------------------------------------------
    ||  print($sql);
    ||  print("Member Type:      [1][".$member_type."]\n");
    ||  print("Credit Card No:   [2][".$credit_card_number."]\n");
    ||  print("Credit Card Type: [3][".$credit_card_type."]\n");
    ||  print("Account Number:   [4][".$account_number."]\n");
    || ------------------------------------------------------------
    */
    if ($stmt->prepare($sql)) {
      $stmt->bind_param("ssss",$member_type,$credit_card_number,$credit_card_type,$account_number); } 
 
    // Attempt query and exit with failure before processing.
    if (!$stmt->execute()) {
 
      // Print failure to resolve query message.
      print $mysqli->error."<br />";
      print "Failed to resolve query ...<br />";
    }
    else {   
      /*  Manually commiting writes when you have disabled the
      ||  default auto commit setting, explained above.
      || ------------------------------------------------------------
      ||  You would add the following command to commit the 
      ||  transaction.
      ||  ------------------------------
      ||   $mysqli->commit();
      || ------------------------------------------------------------
      */
    }
  }
}
 
// Test case
update_member('US00011', '1006', '6011-0000-0000-0078', '1007');
?>

I put this logic in a function.php file. If you do the same, you can run the test case like this from the command line:

php function.sql

As always, I hope this helps.

Written by maclochlainn

September 14th, 2019 at 6:20 pm

Posted in Database Design,LAMP,MySQL,MySQL 8,mysqli,PHP

Tagged with

Cassandra on Fedora 30

without comments

The first thing to do with Fedora 30 is to check what part of Apache Cassandra is installed. You can use the following rpm command to determine that:

rpm -qa | grep cassandra

My Fedora 30 returned the following values:

cassandra-java-libs-3.11.1-12.fc30.x86_64
cassandra-python2-cqlshlib-3.11.1-12.fc30.x86_64
cassandra-3.11.1-12.fc30.x86_64
python2-cassandra-driver-3.18.0-1.fc30.x86_64

Notably missing from the list of rpm list is the cassandra-server package. You install cassandra-server with the def utility:

dnf install -y cassandra-server

You should get an installation log like the following for the cassandra-server package:

Display detailed console log →

Last metadata expiration check: 0:26:07 ago on Wed 11 Sep 2019 09:10:08 PM MDT.
Package cassandra-3.11.1-12.fc30.x86_64 is already installed.
Dependencies resolved.
===========================================================================================================================
 Package                        Architecture         Version                                    Repository            Size
===========================================================================================================================
Installing:
 cassandra-server               x86_64               3.11.1-12.fc30                             fedora               180 k
Installing dependencies:
 sigar                          x86_64               1.6.5-0.20.git58097d9.fc27                 fedora                76 k
 
Transaction Summary
===========================================================================================================================
Install  2 Packages
 
Total download size: 255 k
Installed size: 738 k
Is this ok [y/N]: y
Downloading Packages:
(1/2): sigar-1.6.5-0.20.git58097d9.fc27.x86_64.rpm                                         131 kB/s |  76 kB     00:00    
(2/2): cassandra-server-3.11.1-12.fc30.x86_64.rpm                                          233 kB/s | 180 kB     00:00    
---------------------------------------------------------------------------------------------------------------------------
Total                                                                                      116 kB/s | 255 kB     00:02     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                   1/1 
  Installing       : sigar-1.6.5-0.20.git58097d9.fc27.x86_64                                                           1/2 
  Running scriptlet: sigar-1.6.5-0.20.git58097d9.fc27.x86_64                                                           1/2 
  Running scriptlet: cassandra-server-3.11.1-12.fc30.x86_64                                                            2/2 
  Installing       : cassandra-server-3.11.1-12.fc30.x86_64                                                            2/2 
  Running scriptlet: cassandra-server-3.11.1-12.fc30.x86_64                                                            2/2 
  Verifying        : cassandra-server-3.11.1-12.fc30.x86_64                                                            1/2 
  Verifying        : sigar-1.6.5-0.20.git58097d9.fc27.x86_64                                                           2/2 
 
Installed:
  cassandra-server-3.11.1-12.fc30.x86_64                      sigar-1.6.5-0.20.git58097d9.fc27.x86_64                     
 
Complete!

Fedora Magazine has a great Get Started with Apache Cassandra on Fedora article on all the steps required to setup clusters. This article only covers creating and enabling the Cassandra service, and setting up a single node Cassandra instance.

You start Cassandra with the following command as the root user:

systemctl start cassandra

You enable Cassandra with the following command as the root user:

systemctl enable cassandra

It creates the following symlink:

Created symlink /etc/systemd/system/multi-user.target.wants/cassandra.service → /usr/lib/systemd/system/cassandra.service.

You can connect to the Test cluster with the following command:

cqlsh

You should see the following:

Connected to Test Cluster at 127.0.0.1:9042.
[cqlsh 5.0.1 | Cassandra 3.11.1 | CQL spec 3.4.4 | Native protocol v4]
Use HELP for help.

You can see the options by typing the help command:

Documented shell commands:
===========================
CAPTURE  CLS          COPY  DESCRIBE  EXPAND  LOGIN   SERIAL  SOURCE   UNICODE
CLEAR    CONSISTENCY  DESC  EXIT      HELP    PAGING  SHOW    TRACING
 
CQL help topics:
================
AGGREGATES               CREATE_KEYSPACE           DROP_TRIGGER      TEXT     
ALTER_KEYSPACE           CREATE_MATERIALIZED_VIEW  DROP_TYPE         TIME     
ALTER_MATERIALIZED_VIEW  CREATE_ROLE               DROP_USER         TIMESTAMP
ALTER_TABLE              CREATE_TABLE              FUNCTIONS         TRUNCATE 
ALTER_TYPE               CREATE_TRIGGER            GRANT             TYPES    
ALTER_USER               CREATE_TYPE               INSERT            UPDATE   
APPLY                    CREATE_USER               INSERT_JSON       USE      
ASCII                    DATE                      INT               UUID     
BATCH                    DELETE                    JSON            
BEGIN                    DROP_AGGREGATE            KEYWORDS        
BLOB                     DROP_COLUMNFAMILY         LIST_PERMISSIONS
BOOLEAN                  DROP_FUNCTION             LIST_ROLES      
COUNTER                  DROP_INDEX                LIST_USERS      
CREATE_AGGREGATE         DROP_KEYSPACE             PERMISSIONS     
CREATE_COLUMNFAMILY      DROP_MATERIALIZED_VIEW    REVOKE          
CREATE_FUNCTION          DROP_ROLE                 SELECT          
CREATE_INDEX             DROP_TABLE                SELECT_JSON

Here’s my script that creates Cassandra keyspace, which is more or less a database. You use the USE command to connect to the keyspace or database, like you would in MySQL. You do not have sequences in Cassandra because they’re not a good fit for a distributed architecture. Cassandra does not support a native procedural extension like relational databases. You must create User-defined functions (UDFs) by embedding the logic in Java.

This script does the following:

  • Creates a keyspace
  • Uses the keyspace
  • Conditionally drops tables and functions
  • Creates two tables
  • Inserts data into the two tables
  • Queries data from the tables

I also included a call to a UDF inside a query in two of the examples. One of the queries demonstrates how to return a JSON structure from a query. To simplify things and provide clarification of the scripts behaviors, the details are outlined below.

  • The first segment of the script creates the keyspace, changes the scope to use the keyspace, conditionally drop tables, create tables, and insert values into the tables: 

    /* Create a keyspace in Cassandra, which is like a database
   in MySQL or a schema in Oracle. */
CREATE KEYSPACE IF NOT EXISTS student
  WITH REPLICATION = {
     'class':'SimpleStrategy'
    ,'replication_factor': 1 }
  AND DURABLE_WRITES = true;
 
/* Use the keyspace or connect to the database. */
USE student;
 
/* Drop the member table from the student keyspace. */
DROP TABLE IF EXISTS member;
 
/* Create a member table in the student keyspace. */
CREATE TABLE member
( member_number       VARCHAR
, member_type         VARCHAR
, credit_card_number  VARCHAR
, credit_card_type    VARCHAR
, PRIMARY KEY ( member_number ));
 
/* Conditionally drop the contact table from the student keyspace. */
DROP TABLE IF EXISTS contact;
 
/* Create a contact table in the student keyspace. */
CREATE TABLE contact
( contact_number      VARCHAR
, contact_type        VARCHAR
, first_name          VARCHAR
, middle_name         VARCHAR
, last_name           VARCHAR
, member_number       VARCHAR
, PRIMARY KEY ( contact_number ));
 
/* Insert a row into the member table. */
INSERT INTO member
( member_number, member_type, credit_card_number, credit_card_type )
VALUES
('SFO-12345','GROUP','2222-4444-5555-6666','VISA');
 
/* Insert a row into the contact table. */
INSERT INTO contact
( contact_number, contact_type, first_name, middle_name, last_name, member_number )
VALUES
('CUS_00001','FAMILY','Barry', NULL,'Allen','SFO-12345');
 
/* Insert a row into the contact table. */
INSERT INTO contact
( contact_number, contact_type, first_name, middle_name, last_name, member_number )
VALUES
('CUS_00002','FAMILY','Iris', NULL,'West-Allen','SFO-12345');
 
/* Insert a row into the member table. */
INSERT INTO member
( member_number, member_type, credit_card_number, credit_card_type )
VALUES
('SFO-12346','GROUP','3333-8888-9999-2222','VISA');
 
/* Insert a row into the contact table. */
INSERT INTO contact
( contact_number, contact_type, first_name, middle_name, last_name, member_number )
VALUES
('CUS_00003','FAMILY','Caitlin','Marie','Snow','SFO-12346');

    The following queries the member table:

    /* Select all columns from the member table. */
SELECT * FROM member;

    It returns the following:

     member_number | credit_card_number  | credit_card_type | member_type
---------------+---------------------+------------------+-------------
     SFO-12345 | 2222-4444-5555-6666 |             VISA |       GROUP
     SFO-12346 | 3333-8888-9999-2222 |             VISA |       GROUP
  • Create a concatenate User-defined function (UDF) for Cassandra. The first step requires you to edit the cassandra.yaml file, which you find in the /etc/cassandra/default.conf directory. There is a single parameter that you need to edit, and it is the enable_user_defined_functions parameter. By default the parameter is set to false, and you need to enable it to create UDFs.

    If you open the cassandra.yaml file as the root user, you should find the parameter on line 987, like:

    983
984
985
986
987

    # If unset, all GC Pauses greater than gc_log_threshold_in_ms will log at
# INFO level
# UDFs (user defined functions) are disabled by default.
# As of Cassandra 3.0 there is a sandbox in place that should prevent execution of evil code.
enable_user_defined_functions: false

    After you make the edit, the cassandra.yaml file should look like this:

    983
984
985
986
987

    # If unset, all GC Pauses greater than gc_log_threshold_in_ms will log at
# INFO level
# UDFs (user defined functions) are disabled by default.
# As of Cassandra 3.0 there is a sandbox in place that should prevent execution of evil code.
enable_user_defined_functions: true

    After you make the change, you can create your own UDF. The following UDF formats the first, middle, and last name so there’s only one whitespace between the first and last name when there middle name value is null.

    This type of function must use a CALLED ON NULL INPUT clause in lieu of a RETURNS NULL ON NULL INPUT clause. The latter would force the function to return a null value if any one of the parameters were null.

    /* Drop the concatenate function because a replace disallows changing a
   RETURNS NULL ON NULL INPUT with a CALLED ON NULL INPUT without raising
   an "89: InvalidRequest" exception. */
DROP FUNCTION concatenate;
 
/* Create a user-defined function to concatenate names. */
CREATE OR REPLACE FUNCTION concatenate (first_name VARCHAR, middle_name VARCHAR, last_name VARCHAR)
CALLED ON NULL INPUT
RETURNS VARCHAR
LANGUAGE java
AS $$
  /* Concatenate first and last names when middle name is null, and
     first, middle, and last names when middle name is not null. */
  String name;
 
  /* Check for null middle name. */
  if (middle_name == null) {
    name = first_name + " " + last_name; }
  else {
    name = first_name + " " + middle_name + " " + last_name; }
 
  return name;
$$;

    • Query the values from the contact table with the UDF function in the SELECT-list:

    /* Query the contact information. */
SELECT member_number
,      contact_number
,      contact_type
,      concatenate(first_name, middle_name, last_name) AS full_name
FROM   contact;

    It returns the following:

     member_number | contact_number | contact_type | full_name
---------------+----------------+--------------+--------------------
     SFO-12345 |      CUS_00001 |       FAMILY |        Barry Allen
     SFO-12345 |      CUS_00002 |       FAMILY |    Iris West-Allen
     SFO-12346 |      CUS_00003 |       FAMILY | Caitlin Marie Snow

    Query the values from the contact table with a JSON format:

    /* Query the contact information and return in a JSON format. */
SELECT JSON
       contact_number
,      contact_type
,      concatenate(first_name, middle_name, last_name) AS full_name
FROM   contact;

    It returns the following:

     [json]
-------------------------------------------------------------------------------------------------
{"contact_number": "CUS_00001", "contact_type": "FAMILY", "full_name": "Barry Allen"}
{"contact_number": "CUS_00002", "contact_type": "FAMILY", "full_name": "Iris West-Allen"}
{"contact_number": "CUS_00003", "contact_type": "FAMILY", "full_name": "Caitlin Marie Snow"}

Written by maclochlainn

September 12th, 2019 at 1:17 am

Posted in Cassandra,cqlsh,Fedora,Java,Java 8,Linux

Tagged with ,

Django on Fedora 30

without comments

It seemed opportune to add Django to the Fedora 30 instance that I build and maintain for my students. Here are the instructions, which I developed with the prior Fedora 28/29 instructions.

  1. Check your Python3 installation with the following command: 

    python3 -V

    It should return this but if it doesn’t you should install python3:

    Python 3.7.4

  2. Check whether pip3 is installation by installing it when its not: 

    sudo def -y install python3-php

    It should return:

    Last metadata expiration check: 0:44:52 ago on Tue 10 Sep 2019 11:02:33 AM MDT.
Package python3-pip-19.0.3-3.fc30.noarch is already installed.
Dependencies resolved.
Nothing to do.
Complete!

  3. Check whether Django is installation by installing it when its not with pip3 installation utility: 

    sudo pip3 install --user Django

    It should return the following if installed:

    Requirement already satisfied: Django in /usr/lib/python3.7/site-packages (2.1.10)
Requirement already satisfied: pytz in /usr/lib/python3.7/site-packages (from Django) (2018.5)

  4. Check your django-admin account location with the which utility: 

    which django-admin

    It should return the following on Fedora 30 when installed:

    /usr/bin/django-admin

  5. Create a Django test application with the django-admin utility by creating a project directory. My directory is a bit deep. For reference, it is: 

    /home/student/Code/python/django/projects

    Change to that projects directory, and run the following command:

    django-admin startproject test_app

    After that command change directory with the cd command into the test_app subdirectory in your projects directory. Run the manage.py program with the following command:

    python3 manage.py migrate

    You should see the following:

    Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying sessions.0001_initial... OK

Next, your would create an admin account. You’re done.

Written by maclochlainn

September 10th, 2019 at 12:47 pm

Posted in Django,Fedora,LAMP,Linux,Python,Python 3.x

Tagged with

Python MySQL Query

without comments

Somebody asked me how to expand a prior example with the static variables so that it took arguments at the command line for the variables. This example uses Python 3 new features in the datetime package.

There’s a small trick converting the string arguments to date data types. Here’s a quick example that shows you how to convert the argument list into individual date data type variables:

#!/usr/bin/python3
 
# include standard modules
import sys
from datetime import datetime
 
# Capture argument list.
fullCmdArguments = sys.argv
 
# Assignable variables.
beginDate = ""
endDate = ""
 
# Assign argument list to variable.
argumentList = fullCmdArguments[1:]
 
# Enumerate through the argument list where beginDate precedes endDate as strings.
try:
  for i, s in enumerate(argumentList):
    if (i == 0):
      beginDate = datetime.date(datetime.fromisoformat(s))
    elif (i == 1):
      endDate = datetime.date(datetime.fromisoformat(s))
except ValueError:
  print("One of the first two arguments is not a valid date (YYYY-MM-DD).")
 
# Print the processed values and types.
print("Begin Date: [",beginDate,"][",type(beginDate),"]")
print("End Date:   [",endDate,"][",type(endDate),"]")

Assume you call this arguments.py. Then, you call it with valid conforming date format value like the following command-line example:

./arguments.py 2001-01-01 2003-12-31

It returns the arguments after they have been converted to date data types. The results should look like this:

Begin Date:  1991-01-01 [ <class 'datetime.date'> ]
End Date:    2004-12-31 [ <class 'datetime.date'> ]

The next Python example accepts dynamic arguments at the command line to query the MySQL database:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87

#!/usr/bin/python3
 
# Import the library.
import sys
import mysql.connector
from datetime import datetime
from datetime import date
from mysql.connector import errorcode
 
# Capture argument list.
fullCmdArguments = sys.argv
 
# Assignable variables.
start_date = ""
end_date = ""
 
# Assign argument list to variable.
argumentList = fullCmdArguments[1:]
 
#  Check and process argument list.
# ============================================================
#  If there are less than two arguments provide default values.
#  Else enumerate and convert strings to dates.
# ============================================================
if (len(argumentList) < 2):
  # Set a default start date.
  if (isinstance(start_date,str)):
    start_date = date(1980, 1, 1)
 
  # Set the default end date.
  if (isinstance(end_date,str)):
    end_date = datetime.date(datetime.today())
else:
  # Enumerate through the argument list where beginDate precedes endDate as strings.
  try:
    for i, s in enumerate(argumentList):
      if (i == 0):
        start_date = datetime.date(datetime.fromisoformat(s))
      elif (i == 1):
        end_date = datetime.date(datetime.fromisoformat(s))
  except ValueError:
    print("One of the first two arguments is not a valid date (YYYY-MM-DD).")
 
#  Attempt the query.
# ============================================================
#  Use a try-catch block to manage the connection.
# ============================================================
try:
  # Open connection.
  cnx = mysql.connector.connect(user='student', password='student',
                                host='127.0.0.1',
                                database='studentdb')
  # Create cursor.
  cursor = cnx.cursor()
 
  # Set the query statement.
  query = ("SELECT CASE "
           "         WHEN item_subtitle IS NULL THEN item_title "
           "         ELSE CONCAT(item_title,': ',item_subtitle) "
           "         END AS title, "
           "release_date "
           "FROM item "
           "WHERE release_date BETWEEN %s AND %s "
           "ORDER BY item_title")
 
  # Execute cursor.
  cursor.execute(query, (start_date, end_date))
 
  # Display the rows returned by the query.
  for (item_name, release_date) in cursor:
    print("{}, {:%d-%b-%Y}".format(item_name, release_date))
 
#  Handle exception and close connection.
# ============================================================
except mysql.connector.Error as e:
  if e.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  elif e.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print("Error code:", e.errno)        # error number
    print("SQLSTATE value:", e.sqlstate) # SQLSTATE value
    print("Error message:", e.msg)       # error message
 
# Close the connection when the try block completes.
finally:
  cnx.close()

You can call the python-mysql-query.py program with the following syntax:

./python-mysql-query.py 2001-01-01 2003-12-31

It returns the films between 1 Jan 2001 and 31 Dec 2003, like this:

Clear and Present Danger: Special Collector's Edition, 06-May-2003
Die Another Day: 2-Disc Ultimate Version, 03-Jun-2003
Die Another Day, 03-Jun-2003
Die Another Day, 03-Jun-2003
Golden Eye, 03-Jun-2003
Golden Eye: Special Edition, 03-Jun-2003
Harry Potter and the Chamber of Secrets, 28-May-2002
Harry Potter and the Chamber of Secrets: Two-Disc Special Edition, 28-May-2002
Harry Potter and the Sorcerer's Stone, 28-May-2002
Harry Potter and the Sorcerer's Stone: Two-Disc Special Edition, 28-May-2002
Harry Potter and the Sorcerer's Stone: Full Screen Edition, 28-May-2002
MarioKart: Double Dash, 17-Nov-2003
Pirates of the Caribbean, 30-Jun-2003
RoboCop, 24-Jul-2003
Splinter Cell: Chaos Theory, 08-Apr-2003
Star Wars II: Attack of the Clones, 16-May-2002
Star Wars II: Attack of the Clones, 16-May-2002
The Chronicles of Narnia: The Lion, the Witch and the Wardrobe, 30-Jun-2003
The Chronicles of Narnia: The Lion, the Witch and the Wardrobe, 16-May-2002

As always, I hope this helps somebody who wants to learn how to use Python with the MySQL database.

Written by maclochlainn

September 6th, 2019 at 10:31 pm

Posted in Fedora,Linux,MySQL,MySQL 8,Python,Python 3.x

Tagged with ,

Postgres 11 Video DB

without comments

Installing PostgreSQL 11 on Fedora, Version 30, requires an update to my previous instructions to create a sandboxed user. A sandboxed user can only access a non-data dictionary database with a password. In the real world, rather than a personal test instance you would configure users to include aspects of networking. However, this post is only showing you how to connect from the local server.

This post builds on my PostgreSQL Installation blog post and shows you how to create a tablespace, database, role, and user. It also shows you how to change the default configuration for how users connect to the database.

The following steps create a tablespace, database, role, and user:

  1. Create tablespace

The directory for the data dictionary changes with PostgreSQL has changed. You can find it with the following command:

postgres=# show data_directory;

This will return the following:

     data_directory     
------------------------
 /var/lib/pgsql/11/data
(1 row)

You need to create a physical videoDB subdirectory in the /var/lib/pgsql/11 directory. You can use the following syntax from the /var/lib/pgsql/11 directory to create the videoDB subdirectory:

mkdir videoDB
  1. Create Tablespace

You can create a video_db tablespace with the following syntax:

CREATE TABLESPACE video_db
  OWNER postgres
  LOCATION '/var/lib/pgsql/11/videoDB';

This will return the following:

CREATE TABLESPACE

You can query whether you successfully create the video_db tablespace with the following:

SELECT * FROM pg_tablespace;

It should return the following:

  spcname   | spcowner | spcacl | spcoptions 
------------+----------+--------+------------
 pg_default |       10 |        | 
 pg_global  |       10 |        | 
 video_db   |       10 |        | 
(3 rows)
  1. Create a Database

You can create a videodb database with the following syntax:

CREATE DATABASE videodb
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = video_db
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE = 'en_US.UTF-8'
       CONNECTION LIMIT = -1;

You can verify the creation of the videodb with the following command:

postgres# \l

It should show you a display like the following:

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

Then, you can assign comment to the database with the following syntax:

COMMENT ON DATABASE videodb IS 'Video Database';
  1. Create a Role, Grant, and User

In this section you create a dba role, grant privileges on a videodb database to a role, and create a user with the role that you created previously with the following three statements. There are three steps in this sections.

  • The first step creates a dba role: 

    CREATE ROLE dba WITH SUPERUSER;
  • The second step grants all privileges on a videodb database to a dba role: 

    GRANT ALL PRIVILEGES ON DATABASE videodb TO dba;
  • The third step creates a student user with the dba role: 

    CREATE USER student
  WITH ROLE dba
       ENCRYPTED PASSWORD 'student';
  • It is possible that you may (and should if this is a new instance you are building) encounter an error when you try to connect as a sandboxed user. The syntax to connect as the student user is: 

    psql -d videodb -U student -W

    You may encounter this error:

    psql: FATAL:  Peer authentication failed for user "student"

    You can fix this in PostgreSQL 11 by changing the user access parameters in the pg_hba.conf configuration file. The file is found in the /var/lib/pgsql/11/data directory and you need to edit it as the root or postgres user.

    The default entry is:

    # TYPE  DATABASE        USER            ADDRESS                 METHOD
 
# "local" is for Unix domain socket connections only
local   all             all                                    peer

    You should replace it with the following:

    # TYPE  DATABASE        USER            ADDRESS                 METHOD
 
# "local" is for Unix domain socket connections only
local   postgres         all                                    peer
local   videodb          student                                md5

    After you save those changes in the pg_hba.conf file, you need to restart the PostgreSQL (postgresql-11) service. You can do that with the following command as the root user:

    service postgresql-11 restart

  1. Connect to the videodb as the student user

Once the postgresql-11 service is restarted, you can connect with the sandboxed student user with this syntax:

psql -d videodb -U student -W

If you did everything correctly, you should see the following after correctly providing the student password for the student user:

psql (11.4, server 11.5)
Type "help" for help.
 
videodb=>

After connecting to the videodb database, you can query the current database, like

SELECT current_database();

It should return the following:

 current_database 
------------------
 videodb
(1 row)

This has shown you how to create a videodb database, dba role, and student user.

Written by maclochlainn

September 5th, 2019 at 12:21 am

Posted in Fedora,Linux,PostgreSQL,psql,Unix

Tagged with ,