c77_mvc/INSTALLATION.md

Installation Guide for c77_mvc PostgreSQL Extension

Prerequisites

Before installing the c77_mvc extension, ensure you have:

1. PostgreSQL 11 or later installed
2. Administrative access to your PostgreSQL instance
3. The c77_dbh extension installed (required dependency)
4. Git (if installing from source repository)

Standard Installation

Option 1: Using PostgreSQL Extensions Directory

1. Copy the extension files to your PostgreSQL extensions directory:

# Get the extension directory location
export PGEXTDIR=$(pg_config --sharedir)/extension

# Copy files
sudo cp c77_mvc.control $PGEXTDIR/
sudo cp c77_mvc--1.0.sql $PGEXTDIR/

2. Connect to your PostgreSQL database and create the extension:

CREATE EXTENSION c77_dbh;  -- Install dependency first if not already installed
CREATE EXTENSION c77_mvc;

Option 2: Installing from Git Repository

1. Clone the repository:

git clone https://git.jctr3.com/trogers1884/c77_mvc.git
cd c77_mvc

2. Copy files to your PostgreSQL extensions directory:

export PGEXTDIR=$(pg_config --sharedir)/extension
sudo cp c77_mvc.control $PGEXTDIR/
sudo cp c77_mvc--1.0.sql $PGEXTDIR/

3. Connect to your PostgreSQL database and create the extension:

CREATE EXTENSION c77_dbh;  -- Install dependency first if not already installed
CREATE EXTENSION c77_mvc;

Manual Installation

If you prefer to install the extension manually or if you need to customize the installation process, follow these steps:

1. Ensure the c77_dbh extension is already installed:

SELECT * FROM pg_extension WHERE extname = 'c77_dbh';

If not installed, install it first:

CREATE EXTENSION c77_dbh;

2. Create the table and functions manually by executing the SQL commands:

-- Create the audit table
CREATE TABLE IF NOT EXISTS public.c77_mvc_table_fitness_audit (
  run_id BIGSERIAL,
  run_timestamp timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
  source_schema text COLLATE pg_catalog."default",
  source_table text COLLATE pg_catalog."default",
  analysis_result jsonb,
  notes text[] COLLATE pg_catalog."default",
  CONSTRAINT table_fitness_audit_pkey PRIMARY KEY (run_id)
) TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS idx_table_fitness_audit_table
  ON public.c77_mvc_table_fitness_audit USING btree
  (source_schema COLLATE pg_catalog."default" ASC NULLS LAST, source_table COLLATE pg_catalog."default" ASC NULLS LAST)
  TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS idx_table_fitness_audit_timestamp
  ON public.c77_mvc_table_fitness_audit USING btree
  (run_timestamp ASC NULLS LAST)
  TABLESPACE pg_default;

-- Now execute all the function creation SQL commands from c77_mvc--1.0.sql
-- (Copy and paste all CREATE OR REPLACE FUNCTION statements from the SQL file)

3. Verify the installation:

-- Check if the main table exists
SELECT * FROM pg_tables WHERE tablename = 'c77_mvc_table_fitness_audit';

-- Check if key functions exist
SELECT proname, pronamespace::regnamespace as schema 
FROM pg_proc 
WHERE proname LIKE 'c77_mvc%' 
ORDER BY proname;

Troubleshooting

Common Issues

1. Dependency Error: If you see an error about missing the c77_dbh extension, make sure it's installed properly:

CREATE EXTENSION c77_dbh;

2. Permission Issues: Ensure your PostgreSQL user has sufficient privileges:

-- For a specific user
GRANT ALL ON SCHEMA public TO your_user;
GRANT ALL ON ALL TABLES IN SCHEMA public TO your_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO your_user;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO your_user;

3. Schema Issues: If you're installing to a non-public schema, adjust permissions accordingly:

-- Replace 'custom_schema' with your target schema
GRANT ALL ON SCHEMA custom_schema TO your_user;
GRANT ALL ON ALL TABLES IN SCHEMA custom_schema TO your_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA custom_schema TO your_user;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA custom_schema TO your_user;

Checking for Successful Installation

To verify if the extension was installed correctly:

-- List installed extensions
SELECT * FROM pg_extension WHERE extname = 'c77_mvc';

-- Check if the main table exists
SELECT * FROM information_schema.tables WHERE table_name = 'c77_mvc_table_fitness_audit';

-- Test a simple function
SELECT public.c77_mvc_calculate_sample_size(1000000);

Upgrading

To upgrade from a previous version of the extension:

ALTER EXTENSION c77_mvc UPDATE TO '1.0';

Uninstallation

If you need to uninstall the extension:

DROP EXTENSION c77_mvc;

Note: This will not remove the tables and objects created by the extension. To completely remove all objects:

DROP EXTENSION c77_mvc CASCADE;

Getting Help

For additional help or to report issues:

• Visit the repository at: https://git.jctr3.com/trogers1884/c77_mvc
• Contact the maintainer via issues on the repository