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.
...
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
.
Code Block | ||
---|---|---|
| ||
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
.
Code Block | ||
---|---|---|
| ||
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.
Code Block | ||
---|---|---|
| ||
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:
Code Block | ||
---|---|---|
| ||
\c test_imis |
Check the current user:
Code Block | ||
---|---|---|
| ||
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
.
Code Block | ||
---|---|---|
| ||
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.
Code Block | ||
---|---|---|
| ||
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.
Code Block | ||
---|---|---|
| ||
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:
Code Block | ||
---|---|---|
| ||
-- 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.
Code Block | ||
---|---|---|
| ||
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.
Code Block | ||
---|---|---|
| ||
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.
Code Block | ||
---|---|---|
| ||
psql -U IMISuser -d test_imis -f C:\<Path to your cloned repo>\database_postgresql\output\fullDemoDatabase.sql |
or
Code Block | ||
---|---|---|
| ||
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.
Code Block | ||
---|---|---|
| ||
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)
In SSMS, navigate to your SQL Server instance.
Expand
Security
>Logins
.Right-click
Logins
and selectNew Login
.Enter
IMISuser
as the Login name.Select
SQL Server authentication
and set a password.Uncheck
Enforce password policy
if desired.In the
User Mapping
page, mapIMISuser
to thetest_imis
database.Assign
db_datareader
,db_datawriter
, anddb_ddladmin
roles toIMISuser
.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
.
Code Block | ||
---|---|---|
| ||
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.
Code Block | ||
---|---|---|
| ||
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.
Code Block | ||
---|---|---|
| ||
./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)
Open SSMS and connect to your SQL Server instance.
Open the
fullDemoDatabase.sql
file generated in theoutput
folder.Ensure that the
test_imis
database is selected.Execute the script by pressing
F5
or clicking theExecute
button.
Summary
...