In gcfrench/store: Store commonly used functions and lookups

knitr::opts_chunk$set(
  eval = FALSE,
  collapse = TRUE,
  comment = "#>"
)

# This is included to allow the markdown document to knit when eval = FALSE
con <- NULL

These notes were made whilst creating a postgreSQL database and importing records using an R script. They include setting up administration and read access roles, transferring database to a different server, linking the postgreSQL database to a Microsoft Access database and useful SQL.

Database administration

Setting user privileges in schema within a database

Login to server as superuser creating database admin user and creating new server connection for database admin user in pgadmin4

CREATE ROLE DATABASE_NAME_admin LOGIN CREATEDB CREATEROLE PASSWORD 'string';
SELECT * FROM pg_roles;

-- Create - SERVER
-- General - Name = DATABASE_NAME
-- Connection - HOST NAME (eg. localhost)
-- Connection - Username = DATABASE_NAME_admin
-- Connection - Password = 'string'

CREATE DATABASE DATABASE_NAME OWNER DATABASE_NAME_admin;

Add postgis extensions for a spatial database

CREATE EXTENSION postgis;

Create read only access role and access user role on database

-- Create user group role
CREATE ROLE DATABASE_NAME_read_access;

-- Allow connection to database
GRANT CONNECT ON DATABASE DATABASE_NAME TO DATABASE_NAME_read_access;

-- Create user which inherits read access user group role
CREATE ROLE DATABASE_NAME_read_user LOGIN PASSWORD 'string';
GRANT DATABASE_NAME_read_access TO DATABASE_NAME_read_user;

Import and run create_DATABASE_NAME_schema.sql to create schema and read access in public schema

-- Included in procedure is setting read access to schema database
-- Allow access to schema
GRANT USAGE ON SCHEMA SCHEMA_NAME TO DATABASE_NAME_read_access;

-- Allow select access to all tables
GRANT SELECT ON ALL TABLES IN SCHEMA SCHEMA_NAME TO DATABASE_NAME_read_access;

-- Allow select access to all future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA SCHEMA_NAME
GRANT SELECT ON TABLES TO DATABASE_NAME_read_access;

Create key on database keyring for database admin user on local machine
Run DATABASE_NAME.Rmd script to populate database
Run VACUUM maintenance

VACUUM;

Backup SCHEMA_NAME schema SCHEMA_NAME_schema_backup_YYYYMMDD.sql by Right click schema - Backup
Create new server connection for read access user in pgadmin4

{sql connection=con} -- Create - SERVER -- General - Name = DATABASE_NAME -- Connection - HOST NAME (eg. localhost) -- Connection - Username = DATABASE_NAME_read_access -- Connection - Password = 'string'
Create key on database keyring for read access user on local machine
Login as superuser and back up DATABASE_NAME roles as sql by Right click server name - Backup Globals DATABASE_NAME_database_roles_YYYYMMDD.sql, selecting SQL from this file

Transfer database to another server

CREATE ROLE DATABASE_NAME_admin;
ALTER ROLE DATABASE_NAME_admin WITH NOSUPERUSER INHERIT CREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5_string';

CREATE ROLE DATABASE_NAME_read_access;
ALTER ROLE DATABASE_NAME_read_access WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;

CREATE ROLE DATABASE_NAME_read_user;
ALTER ROLE DATABASE_NAME_read_user WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5_string';

Create DATABASE_NAME database under DATABASE_NAME_admin ownership

CREATE DATABASE DATABASE_NAME OWNER DATABASE_NAME_admin;

Create SCHEMA_NAME schema under DATABASE_NAME_admin ownership

CREATE SCHEMA SCHEMA_NAME;

Restore schema by right click schema - Restore
Grant access to user roles

-- Allow connection to database
GRANT CONNECT ON DATABASE DATABASE_NAME TO DATABASE_NAME_read_access;

-- Allow access to schema
GRANT USAGE ON SCHEMA SCHEMA_NAME TO DATABASE_NAME_read_access;

-- Allow select access to all tables
GRANT SELECT ON ALL TABLES IN SCHEMA SCHEMA_NAME TO DATABASE_NAME_read_access;

-- Allow select access to all future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA SCHEMA_NAME
GRANT SELECT ON TABLES TO DATABASE_NAME_read_access;

SQL

Truncate all tables within a schema

This SQL truncates all the tables in a schema following instructions in stack overflow Truncating all tables in a Postgres database

```{sql connection=con} DO $$ DECLARE r RECORD; BEGIN FOR r IN (SELECT schemaname, tablename FROM pg_tables WHERE schemaname = 'SCHEMA_NAME') LOOP EXECUTE 'TRUNCATE TABLE ' || quote_ident(r.schemaname) || '.'
|| quote_ident(r.tablename) || ' RESTART IDENTITY CASCADE'; END LOOP; END $$;

## Get OSGB or Irish grid reference precision

```{sql connection=con}
CREATE OR REPLACE FUNCTION getPrecision (gridReference VARCHAR(14))
  RETURNS SMALLINT AS $$
  DECLARE 
     p INTEGER;
 BEGIN 
   CASE 
     WHEN gridReference ~ '^\D{1,2}\d{2}$' THEN p = 10000;
     WHEN gridReference ~ '^\D{1,2}\d{2}\D{2}$' THEN p = 5000;
     WHEN gridReference ~ '^\D{1,2}\d{2}\D{1}$' THEN p = 2000;
     WHEN gridReference ~ '^\D{1,2}\d{4}$' THEN p = 1000;
     WHEN gridReference ~ '^\D{1,2}\d{6}$' THEN p = 100;
     WHEN gridReference ~ '^\D{1,2}\d{8}$' THEN p = 10;
     WHEN gridReference ~ '^\D{1,2}\d{10}$' THEN p = 1;
     ELSE
         RAISE INFO 'UNABLE TO FIND PRECISION FOR %', gridReference;
         p = NULL;
    END CASE;
    RETURN p;
 END; $$
 LANGUAGE 'plpgsql';

-- CONVERTS GRIDREFERENCE TO 10KM
CREATE OR REPLACE FUNCTION convertGRto10KM (
 INOUT gridReference varchar(4))
AS $$
BEGIN
  CASE
    WHEN gridReference ~ '^\D{1,2}\d{2}$' THEN gridReference = gridReference; -- 10000
    WHEN gridReference ~ '^\D{1,2}\d{2}\D{1,2}$' THEN gridReference = SUBSTRING(gridReference, '^\D{1,2}\d{2}'); -- 5000 | 2000
    WHEN gridReference ~ '^\D{1,2}\d{4}$' THEN gridReference = CONCAT(SUBSTRING(gridReference, '^\D{1,2}\d{1}'), LEFT(regexp_replace(gridreference, '^\D{1,2}\d{2}', ''), 1)); -- 1000
    WHEN gridReference ~ '^\D{1,2}\d{6}$' THEN gridReference = CONCAT(SUBSTRING(gridReference, '^\D{1,2}\d{1}'), LEFT(regexp_replace(gridreference, '^\D{1,2}\d{3}', ''), 1)); -- 100
    WHEN gridReference ~ '^\D{1,2}\d{8}$' THEN gridReference = CONCAT(SUBSTRING(gridReference, '^\D{1,2}\d{1}'), LEFT(regexp_replace(gridreference, '^\D{1,2}\d{4}', ''), 1)); -- 10
    WHEN gridReference ~ '^\D{1,2}\d{10}$' THEN gridReference = CONCAT(SUBSTRING(gridReference, '^\D{1,2}\d{1}'), LEFT(regexp_replace(gridreference, '^\D{1,2}\d{5}', ''), 1)); -- 1
    ELSE 
        RAISE INFO 'UNABLE TO CONVERT % TO 10KM GRIDREFERENCE', gridReference;
        gridReference = NULL;
  END CASE;
END; $$
LANGUAGE 'plpgsql';

Convert grid reference to 1km

```{sql connection=con} CREATE OR REPLACE FUNCTION convertGRto1KM ( INOUT gridReference varchar(4)) AS $$ BEGIN CASE WHEN gridReference ~ '^\D{1,2}\d{4}$' THEN gridReference = gridReference; -- 1000 WHEN gridReference ~ '^\D{1,2}\d{6}$' THEN gridReference = CONCAT(SUBSTRING(gridReference, '^\D{1,2}\d{2}'), LEFT(regexp_replace(gridreference, '^\D{1,2}\d{3}', ''), 2)); -- 100 WHEN gridReference ~ '^\D{1,2}\d{8}$' THEN gridReference = CONCAT(SUBSTRING(gridReference, '^\D{1,2}\d{2}'), LEFT(regexp_replace(gridreference, '^\D{1,2}\d{4}', ''), 2)); -- 10 WHEN gridReference ~ '^\D{1,2}\d{10}$' THEN gridReference = CONCAT(SUBSTRING(gridReference, '^\D{1,2}\d{2}'), LEFT(regexp_replace(gridreference, '^\D{1,2}\d{5}', ''), 2)); -- 1 ELSE RAISE INFO 'UNABLE TO CONVERT % TO 1KM GRIDREFERENCE', gridReference; gridReference = NULL; END CASE; END; $$ LANGUAGE 'plpgsql';

-- CONVERTS GRIDREFERENCE TO 2KM CREATE OR REPLACE FUNCTION convertGRto2KM ( INOUT gridReference varchar(4)) AS $$ DECLARE tenKM VARCHAR(4); oneKM VARCHAR(6); twoKMNumber VARCHAR(2); twoKMLetter CHAR(1); BEGIN IF gridreference ~ '^\D{1,2}\d{2}\D{1}$' THEN -- 2000 ELSIF gridreference ~ '^\D{1,2}\d{4,10}$' THEN -- 1000 | 100 | 10 | 1 tenKM = convertGRto10KM(gridReference); oneKM = convertGRto1KM(gridReference); twoKMNumber = CONCAT(LEFT(regexp_replace(oneKM , '^\D{1,2}\d{1}', ''), 1), RIGHT (regexp_replace(oneKM , '^\D{1,2}\d{1}', ''), 1)); CASE WHEN twoKMNumber IN ('00', '01', '10', '11') THEN twoKMLetter = 'A'; WHEN twoKMNumber IN ('02', '03', '12', '13') THEN twoKMLetter = 'B'; WHEN twoKMNumber IN ('04', '05', '14', '15') THEN twoKMLetter = 'C'; WHEN twoKMNumber IN ('06', '07', '16', '17') THEN twoKMLetter = 'D'; WHEN twoKMNumber IN ('08', '09', '18', '19') THEN twoKMLetter = 'E'; WHEN twoKMNumber IN ('20', '21', '30', '31') THEN twoKMLetter = 'F'; WHEN twoKMNumber IN ('22', '23', '32', '33') THEN twoKMLetter = 'G'; WHEN twoKMNumber IN ('24', '25', '34', '35') THEN twoKMLetter = 'H'; WHEN twoKMNumber IN ('26', '27', '36', '37') THEN twoKMLetter = 'I'; WHEN twoKMNumber IN ('28', '29', '38', '39') THEN twoKMLetter = 'J'; WHEN twoKMNumber IN ('40', '41', '50', '51') THEN twoKMLetter = 'K'; WHEN twoKMNumber IN ('42', '43', '52', '53') THEN twoKMLetter = 'L'; WHEN twoKMNumber IN ('44', '45', '54', '55') THEN twoKMLetter = 'M'; WHEN twoKMNumber IN ('46', '47', '56', '57') THEN twoKMLetter = 'N'; WHEN twoKMNumber IN ('48', '49', '58', '59') THEN twoKMLetter = 'P'; WHEN twoKMNumber IN ('60', '61', '70', '71') THEN twoKMLetter = 'Q'; WHEN twoKMNumber IN ('62', '63', '72', '73') THEN twoKMLetter = 'R'; WHEN twoKMNumber IN ('64', '65', '74', '75') THEN twoKMLetter = 'S'; WHEN twoKMNumber IN ('66', '67', '76', '77') THEN twoKMLetter = 'T'; WHEN twoKMNumber IN ('68', '69', '78', '79') THEN twoKMLetter = 'U'; WHEN twoKMNumber IN ('80', '81', '90', '91') THEN twoKMLetter = 'V'; WHEN twoKMNumber IN ('82', '83', '92', '93') THEN twoKMLetter = 'W'; WHEN twoKMNumber IN ('84', '85', '94', '95') THEN twoKMLetter = 'X'; WHEN twoKMNumber IN ('86', '87', '96', '97') THEN twoKMLetter = 'Y'; WHEN twoKMNumber IN ('88', '89', '98', '99') THEN twoKMLetter = 'Z'; ELSE twoKMLetter = NULL; END CASE; IF tenKM IS NOT NULL OR twoKMLetter IS NOT NULL THEN gridreference = CONCAT(tenKM, twoKMLetter); ELSE RAISE INFO 'UNABLE TO CONVERT % TO 2KM GRIDREFERENCE', gridReference; gridReference = NULL; END IF; ELSE gridReference = NULL; -- 10000 | 5000 END IF; END; $$ LANGUAGE 'plpgsql'; ```

Link Microsoft Access database

Install PostgreSQL 32 bit ODBC driver on machine using Postgres's Application Stack Builder or via website. Need corresponding version ie for PostgReSQL 11 need psqlodbs_11_01_0000.zip
Create File Datasource using PostgreSQL Unicode driver and file DNS
Link PostgreSQL tables into Microsoft Access
Create pass-through queries storing connection string in properties
If have odbc error when opening table then try refreshing link in Linked Table Manager