gvenzl/TestDatabaseSetup.md

## TestDatabaseSetup.md

      
    Raw
  

              TestDatabaseSetup.md
            
          
    Test Database Setup

Container creation

Podman run
Test database and user creation


MySQL
Postgres
Oracle
SQL Server
Db2

Connect as test user to test database


MySQL
Postgres
Oracle
SQL Server
Db2

Test database and user deletion


MySQL
Postgres
Oracle
SQL Server
Db2

Podman run

podman run -d --name mysql -e MYSQL_ROOT_PASSWORD=LetsTest1 -p 3306:3306 -v mysql:/var/lib/mysql mysql

podman run -d --name postgres -e POSTGRES_PASSWORD=LetsTest1 -p 5432:5432 -v postgres:/var/lib/postgresql/data postgres

podman run -d --name oracle-free -e ORACLE_PASSWORD=LetsTest1 -p 1521:1521 -v oracle-free:/opt/oracle/oradata gvenzl/oracle-xe:23-slim

# Volumes aren't supported for SQL Server on Mac
podman run -d --name sqlserver -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=LetsTest1' -p 1433:1433 -v sqlserver:/var/opt/mssql mcr.microsoft.com/mssql/server:2022-latest

docker run -d --name db2 -e LICENSE=accept --privileged=true -e DBNAME=test -e DB2INST1_PASSWORD=LetsTest1 -p 50000:50000 -v db2:/database icr.io/db2_community/db2
Test database and user creation

MySQL

Shell

mysql -u root -pLetsTest1
Podman

podman exec -ti mysql mysql -u root -pLetsTest1
Create test database and test user

CREATE DATABASE test;
CREATE USER 'test' IDENTIFIED BY 'LetsTest1';
GRANT ALL PRIVILEGES ON test.* TO 'test';
FLUSH PRIVILEGES;
exit;
Postgres

Shell

psql -U postgres
Podman

podman exec -it postgres psql -U postgres
Create test database and test user

CREATE DATABASE test;
CREATE USER test WITH ENCRYPTED PASSWORD 'LetsTest1';
GRANT ALL PRIVILEGES ON DATABASE test TO test;
\c test test
CREATE SCHEMA test AUTHORIZATION test;
exit;
Oracle

Shell

sqlplus sys/LetsTest1@localhost/XE as sysdba
Podman

podman exec -ti oracle-xe sqlplus sys/LetsTest1@localhost/XE as sysdba
Create test database and test user

CREATE PLUGGABLE DATABASE test ADMIN USER test IDENTIFIED BY LetsTest1 FILE_NAME_CONVERT=('pdbseed','test');
ALTER PLUGGABLE DATABASE test OPEN;
ALTER PLUGGABLE DATABASE test SAVE STATE;
ALTER SESSION SET CONTAINER=test;
GRANT CONNECT, RESOURCE, CREATE VIEW, UNLIMITED TABLESPACE TO test;
exit;
SQL Server

Shell

sqlcmd -S localhost -U sa -P LetsTest1
Podman

podman exec -it sqlserver /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P LetsTest1
Create test database and test user

sp_configure 'contained database authentication', 1;
RECONFIGURE;
go
CREATE DATABASE test CONTAINMENT=PARTIAL;
go
USE test;
CREATE USER test WITH PASSWORD = 'LetsTest1';
go
ALTER ROLE db_owner ADD MEMBER test;
go
exit
Db2

Shell

db2 -t
Docker

docker exec -ti db2 su - db2inst1 -c "db2 -t"
Create test database

CREATE DATABASE test;
quit;
Connect as test user to test database

MySQL

Shell

mysql -u test -pLetsTest1 -D test
Podman

podman exec -ti mysql mysql -u test -pLetsTest1 -D test
Postgres

Shell

psql -U test -d test
Podman

podman exec -it postgres psql -U test -d test
Oracle

Shell

sqlplus test/LetsTest1@localhost/XE
Podman

sudo podman exec -ti oracle-xe su oracle -c "sqlplus test/LetsTest1@localhost/XE"
SQL Server

Shell

sqlcmd -S localhost -U test -P LetsTest1 -d test
Podman

podman exec -it sqlserver /opt/mssql-tools/bin/sqlcmd -S localhost -U test -P LetsTest1 -d test
Db2

Shell

db2 -t
CONNECT TO TEST;
Docker

docker exec -ti db2 su - db2inst1 -c "db2 -t"
CONNECT TO TEST;
Test database and user deletion

MySQL

Shell

mysql -u root -pLetsTest1
Podman

podman exec -ti mysql mysql -u root -pLetsTest1
Drop test database and test user

DROP DATABASE test;
DROP USER 'test';
FLUSH PRIVILEGES;
exit;
Postgres

Shell

psql -U postgres
Podman

podman exec -it postgres psql -U postgres
Drop test database and test user

DROP DATABASE test;
DROP USER test;
exit;
Oracle

Shell

sqlplus sys/LetsTest1@localhost/XE as sysdba
Podman

sudo podman exec -ti oracle-xe su oracle -c "sqlplus sys/LetsTest1@localhost/XE as sysdba"
Drop test database including test user

ALTER PLUGGABLE DATABASE test CLOSE;
DROP PLUGGABLE DATABASE test INCLUDING DATAFILES;
exit;
SQL Server

Shell

sqlcmd -S localhost -U sa -P LetsTest1
Podman

podman exec -it sqlserver /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P LetsTest1
Drop test database including test user

DROP DATABASE test;
go
exit
Db2

Shell

db2
Docker

docker exec -ti db2 su - db2inst1 -c db2
Drop test database

CONNECT TO TEST
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS
UNQUIESCE DATABASE
CONNECT RESET
DEACTIVATE DATABASE test
DROP DATABASE test
quit