This guide provides step-by-step instructions for setting up the openIMIS Database using PostgreSQL or MS SQL Server. Follow these steps to clone the repository, create the database and user, and run the necessary SQL scripts.

PostgreSQL

...

Prerequisites

• PostgreSQL installed and running.

• Access to PostgreSQL with administrative privileges.

• Git installed.

• Command Line Interface (CLI) available (Windows).

Step-by-Step Instructions

1. Connect to PostgreSQL (Windows)

First, connect to your PostgreSQL instance using a command-line tool or a database client. Ensure you are connected with a user that has administrative privileges, usually the username is postgres.

psql -U postgres

Replace postgres with your administrative username if it is different.

2. Create the Database (Windows)

Create a new database named test_imis.

CREATE DATABASE "test_imis";

3. Create the User and Grant Connection Privileges (Windows)

Create a new user named IMISuser with the specified password and grant the user the privilege to connect to the test_imis database.

CREATE USER "IMISuser" WITH PASSWORD 'IMISuser@1234';
GRANT CONNECT ON DATABASE test_imis TO "IMISuser";

4. Switch to the Database (Windows)

Switch to the test_imis database to grant further permissions for IMISuser using the user that has administrative privileges (in this case, postgres). Ensure you are logged in with the correct user by running the following script:

\c test_imis

Check the current user:

SELECT session_user, current_user;

5. Install PL/pgSQL Extension and Grant Permissions (Windows)

Ensure that the PL/pgSQL procedural language extension is installed. Then grant the necessary schema and table permissions to IMISuser.

CREATE EXTENSION IF NOT EXISTS "plpgsql" WITH SCHEMA "pg_catalog";
COMMENT ON EXTENSION "plpgsql" IS 'PL/pgSQL procedural language';

GRANT CREATE ON SCHEMA public TO "IMISuser";
GRANT USAGE ON SCHEMA public TO "IMISuser";
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO "IMISuser";
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO "IMISuser";

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "IMISuser";

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO "IMISuser";

COMMENT ON SCHEMA "public" IS 'standard public schema';

6. Clone the openIMIS Database Repository (Windows)

Clone the openIMIS database repository from GitHub.

git clone <https://github.com/openimis/database_postgresql.git>

7. Prepare the SQL Scripts (Ubuntu)

Open the Ubuntu Terminal (WSL) and navigate to the directory containing the concatenate_files.sh script.

cd /mnt/c/<Path to your cloned repo>/database_postgresql

Run the script to generate the SQL files. After running the script, navigate to the Output folder to find the fullDemoDatabase.sql or EmptyDatabase.sql files.

8. Modify the SQL Scripts (Windows)

Open the generated fullDemoDatabase.sql or EmptyDatabase.sql files in Windows and comment out the following lines:

-- COMMENT ON SCHEMA "public" IS 'standard public schema';
-- CREATE EXTENSION IF NOT EXISTS "plpgsql" WITH SCHEMA "pg_catalog";
-- COMMENT ON EXTENSION "plpgsql" IS 'PL/pgSQL procedural language';

9. Login with IMISuser (Windows)

Log in to PostgreSQL using the IMISuser account in the Windows CLI.

psql -U IMISuser -d test_imis

10. Verify the File Path (Windows)

Navigate to the directory containing the SQL files in the Windows CLI and list the files to ensure the correct path.

cd C:\<Path to your cloned repo>\database_postgresql\output
dir

Ensure fullDemoDatabase.sql is listed.

11. Execute the SQL Scripts (Windows)

Execute the modified fullDemoDatabase.sql or EmptyDatabase.sql scripts to set up the database in the Windows CLI.

psql -U IMISuser -d test_imis -f C:\<Path to your cloned repo>\database_postgresql\output\fullDemoDatabase.sql

or

psql -U IMISuser -d test_imis -f C:\<Path to your cloned repo>\database_postgresql\output\EmptyDatabase.sql

Replace <Path to your cloned repo> with the actual path to the folder where you have cloned the database_postgresql repository.

Summary

You have now successfully set up the openIMIS Database in PostgreSQL with the necessary user and permissions. The user IMISuser has the appropriate rights to interact with the database and its objects. Additionally, you have cloned the openIMIS database repository and executed the required SQL scripts.

MS SQL

Prerequisites

• SQL Server installed and running.

• Access to SQL Server Management Studio (SSMS) or a similar database client.

• Git installed.

• Linux shell available (e.g., WSL on Windows).

Step-by-Step Instructions

1. Clone openIMIS Database Project

Clone the openIMIS Database repository from GitHub.

git clone <https://github.com/openimis/openimis-db_dkr.git>

2. Create a New Database

Open SQL Server Management Studio (SSMS) or your preferred SQL Server client and create a new database, for example, test_imis.

3. Create a Database User

Create a new database user named IMISuser with read, write, and adminddl access only to the test_imis database.

Create User (SSMS)

1. In SSMS, navigate to your SQL Server instance.

2. Expand Security > Logins.

3. Right-click Logins and select New Login.

4. Enter IMISuser as the Login name.

5. Select SQL Server authentication and set a password.

6. Uncheck Enforce password policy if desired.

7. In the User Mapping page, map IMISuser to the test_imis database.

8. Assign db_datareader, db_datawriter, and db_ddladmin roles to IMISuser.

9. Click OK to create the user.

4. Log in to the Database as IMISuser

Log in to the test_imis database using the newly created IMISuser.

sqlcmd -S <Your_Server_Name> -U IMISuser -P <Your_Password> -d test_imis

Replace <Your_Server_Name>, <Your_Password>, and test_imis with your server name, password, and database name, respectively.

5. Navigate to the Cloned Folder

Navigate to the database_ms_sqlserver folder within the cloned openIMIS Database Project.

cd openimis-db_dkr/database_ms_sqlserver

6. Execute the Script File in a Linux Shell

Open a Linux shell (e.g., WSL on Windows) and execute the script file concatenate_files.sh located in the database_ms_sqlserver folder.

./concatenate_files.sh

This script will generate a SQL script file in the output folder, e.g., fullDemoDatabase.sql.

7. Run the Generated SQL Script

Run the generated SQL script (fullDemoDatabase.sql) in SQL Server to set up the database schema and initial data.

Execute Script (SSMS)

1. Open SSMS and connect to your SQL Server instance.

2. Open the fullDemoDatabase.sql file generated in the output folder.

3. Ensure that the test_imis database is selected.

4. Execute the script by pressing F5 or clicking the Execute button.

Summary

...

Installation Guide

MS SQL Installation Guide