It is a memorandum because I built an environment like a title in my work and stumbled in various places.
--I wanted to write a unit test of a batch program that would connect to the Oracle database (oracle12c) with sqlalchemy and dump the required information. ――Since SQL was written directly in this product code, I tried to build and check the SQLite environment on the unit test, but I saw a reasonable opinion that it is better to have the DB client in the unit test environment and the production environment. Therefore, I decided to build an oracle12c environment even in a unit test environment. ――It's reasonable to say that it's not good to write SQL directly, but to refactor for that, you have to write a unit test first.
Since there is no unit test environment in the first place, we first built a unit test environment locally. Just like building a unit test environment using a common DB, you can run the unit test while starting db with docker-compose.
Click here to create a docker image for oracle12c.
docker-compose.yml
version: '2'
services:
oracle-database:
image: oracle/database:12.1.0.2-ee
container_name: oracle-database
ports:
- 1521:1521
volumes:
- ./startup:/opt/oracle/scripts/startup
environment:
- ORACLE_SID=SID
- ORACLE_PWD=passw0rd
- ORACLE_PDB=pdb
Some preparation is required to connect to this DB.
In the case of Oracle database, the database will be created in the user (schema), so it is necessary to create the user first. It's tedious to write user-created code in test code every time, so let users be created at database startup.
startup/startup.sql
ALTER SESSION SET container = pdb;
GRANT DBA TO PDBADMIN;
GRANT UNLIMITED TABLESPACE TO PDBADMIN;
CREATE USER testuser
IDENTIFIED BY passw0rd
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
GRANT DBA TO testuser;
GRANT UNLIMITED TABLESPACE TO testuser;
The Oracle database docker image runs the sql file in the startup folder as the sysdb user, so modify the session to add the user on the pluggable DB (PDB). Also, in the initial state, PDBADMIN does not have the authority equivalent to admin (why?), So the authority is granted.
Here, the user is created as testuser / passw0rd. Since it is used only for unit tests, this user also has admin equivalent privileges.
tests/test.py
import unittest
import cx_Oracle
import sqlalchemy
class Test(unittest.TestCase):
def setUp(self):
self.sut = ... #Instance to be tested
dsn = cx_Oracle.makedsn("oracle-database", 1521, service_name = "pdb")
self.testuser = sqlalchemy.create_engine(f"oracle+cx_oracle://testuser:passw0rd@{dsn}")
self.create_testtable()
def tearDown(self):
self.drop_testtable()
def test__testmethod__describe(self):
# SetUp
expected = ...
# Exercise
self.sut.testmethod(...)
# Verify
actual = ...
self.assertEqual(expected, actual)
def create_testtable(self):
self.testuser.execute(f"""
CREATE TABLE testtable (
... define columns ...
)
""")
def drop_testtable(self):
self.testuser.execute("DROP TABLE testtable")
if __name__ == "__main__":
unittest.main()
I think there are various ways to write a test, so as an example. The important part here is the part that connects to oracle12c with the setUp method. Create a DSN and use that information to build a connection URL and create a connection engine. There are various connection methods in sqlalchemy, but to connect to the PDB you need to connect by ServiceName (not SID), and you need to create a DSN for that. The hostname of the DSN should match the container name defined in docker-compose.yml.
If you prepare __init__.py in tests, unit tests will be executed by executing pytest at the root.
docker-compose up -d
pytest
For the time being, this will set up the minimum environment. However, there are problems with this environment.
As you can see, after docker-compose up, it takes a lot of time to build the initial database, and it takes about 5 to 10 minutes until the database finally opens (= can be connected). Also, the initial database build rarely fails (even though it's built with Docker). Considering the environment as a unit test, the handling is quite bad.
Back up the initial database to solve this problem. There is a reason to say "backup" here. This is because this initial database will be updated steadily in the form of overwriting after connecting from the DBMS. The same is true when the database becomes dirty due to unit test execution, but even if nothing is done, the file size increases and the database becomes more and more "dirty". It is undeniable that the test may fail due to such external factors, so in order to build a stable test environment, it is necessary to restore the initial database backed up just before docker-compose up. is there.
Some technique is required to build the initial database and prepare a backup. An example is shown here.
First, build an Oracle database image to build the initial database. The example uses docker-compose, but you can use a similar docker command.
docker-compose-oradata.yml
version: '2'
services:
oracle-database:
image: oracle/database:12.1.0.2-ee
container_name: oracle-database
ports:
- 1521:1521
volumes:
- ./oradata:/opt/oracle/oradata
environment:
- ORACLE_SID=SID
- ORACLE_PWD=passw0rd
- ORACLE_PDB=pdb
docker-compose -f docker-compose-oradata.yml up
After execution, the initial database will be built in the oradata folder. This construction takes about 5-10 minutes. In the above example, it is not in detach mode in order to check the timing of whether it is over or not.
From here on, it's important to shut down the database with Ctrl + C right after the build is complete. If you lean on it, the initial database will be overweight (about 1.5 GB). You can still mount it, but it doesn't make sense if the files you back up are unnecessarily large, so shut them down immediately after construction.
Starting oracle-database ... done
Attaching to oracle-database
oracle-database | ORACLE PASSWORD FOR SYS, SYSTEM AND PDBADMIN: passw0rd
oracle-database |
oracle-database | LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 15-OCT-2020 12:03:40
oracle-database |
oracle-database | Copyright (c) 1991, 2014, Oracle. All rights reserved.
oracle-database |
oracle-database | Starting /opt/oracle/product/12.1.0.2/dbhome_1/bin/tnslsnr: please wait...
oracle-database |
oracle-database | TNSLSNR for Linux: Version 12.1.0.2.0 - Production
oracle-database | System parameter file is /opt/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
oracle-database | Log messages written to /opt/oracle/diag/tnslsnr/bf429c874900/listener/alert/log.xml
oracle-database | Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
oracle-database | Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
oracle-database |
oracle-database | Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
oracle-database | STATUS of the LISTENER
oracle-database | ------------------------
oracle-database | Alias LISTENER
oracle-database | Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
oracle-database | Start Date 15-OCT-2020 12:03:40
oracle-database | Uptime 0 days 0 hr. 0 min. 0 sec
oracle-database | Trace Level off
oracle-database | Security ON: Local OS Authentication
oracle-database | SNMP OFF
oracle-database | Listener Parameter File /opt/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
oracle-database | Listener Log File /opt/oracle/diag/tnslsnr/bf429c874900/listener/alert/log.xml
oracle-database | Listening Endpoints Summary...
oracle-database | (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
oracle-database | (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
oracle-database | The listener supports no services
oracle-database | The command completed successfully
oracle-database | Cleaning up failed steps
oracle-database | 4% complete
oracle-database | Copying database files
oracle-database | 5% complete
oracle-database | 6% complete
oracle-database | 30% complete
oracle-database | Creating and starting Oracle instance
oracle-database | 32% complete
oracle-database | 35% complete
oracle-database | 36% complete
oracle-database | 37% complete
oracle-database | 41% complete
oracle-database | 44% complete
oracle-database | 45% complete
oracle-database | 48% complete
oracle-database | Completing Database Creation
oracle-database | 50% complete
oracle-database | 53% complete
oracle-database | 55% complete
oracle-database | 63% complete
oracle-database | 66% complete
oracle-database | 74% complete
oracle-database | Creating Pluggable Databases
oracle-database | 79% complete
oracle-database | 100% complete
oracle-database | Look at the log file "/opt/oracle/cfgtoollogs/dbca/SID/SID0.log" for further details.
oracle-database |
oracle-database | SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 15 12:11:34 2020
oracle-database |
oracle-database | Copyright (c) 1982, 2014, Oracle. All rights reserved.
oracle-database |
oracle-database |
oracle-database | Connected to:
oracle-database | Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
oracle-database | With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
oracle-database |
oracle-database | SQL>
oracle-database | System altered.
oracle-database |
oracle-database | SQL>
oracle-database | Pluggable database altered.
oracle-database |
oracle-database | SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
oracle-database | With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
oracle-database | The Oracle base remains unchanged with value /opt/oracle
oracle-database | #########################
oracle-database | DATABASE IS READY TO USE!
oracle-database | #########################
oracle-database | The following output is now a tail of the alert.log:
oracle-database | Completed: alter pluggable database PDB open
oracle-database | Thu Oct 15 12:11:33 2020
oracle-database | CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE '/opt/oracle/oradata/SID/PDB/PDB_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
oracle-database | Completed: CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE '/opt/oracle/oradata/SID/PDB/PDB_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
oracle-database | ALTER DATABASE DEFAULT TABLESPACE "USERS"
oracle-database | Completed: ALTER DATABASE DEFAULT TABLESPACE "USERS"
oracle-database | Thu Oct 15 12:11:34 2020
oracle-database | ALTER SYSTEM SET control_files='/opt/oracle/oradata/SID/control01.ctl' SCOPE=SPFILE;
oracle-database | ALTER PLUGGABLE DATABASE PDB SAVE STATE
oracle-database | Completed: ALTER PLUGGABLE DATABASE PDB SAVE STATE
^CERROR: Aborting.
Back up the oradata folder somewhere so that the contents of this oradata folder will be restored before running docker-compose up before running the test (in actual business, the contents of oradata should be restored. Zip-compressed and backed up to S3). The original docker-compose.yml should mount the oradata folder.
docker-compose.yml
version: '2'
services:
oracle-database:
image: oracle/database:12.1.0.2-ee
container_name: oracle-database
ports:
- 1521:1521
volumes:
- ./oradata:/opt/oracle/oradata
- ./startup:/opt/oracle/scripts/startup
environment:
- ORACLE_SID=SID
- ORACLE_PWD=passw0rd
- ORACLE_PDB=pdb
By doing this, the time required to open the database could be reduced to about 1 to 2 minutes.
The main subject.
When using a unit test DB on GitLab CI, it is a good idea to use services. However, the image of the Oracle database has to be managed in some kind of private repository, I have never set up a private repository in services & I made other docker images in practice, so here I will use Docker in Docker (dind) I built the environment.
There is one problem when building a dind environment with GitLab CI. That is, the directory where the volume is mounted will be the host directory. Normally (not limited to GitLab CI) job execution itself is done in the docker container, so if you execute docker-compose like this time in it, the oradata and startup folders will be in the docker container where the job was executed. Instead, you end up specifying the folder on the host that is running that docker.
There are various solutions to this, but this time I avoided it by creating a docker image that includes oradata. Since the oracle database image should be placed in the private repository in the first place, the image including oradata is only prepared there, so the local environment does not change much.
The Dockerfile that creates the oracle database image including the oradata created earlier looks like this.
Dockerfile
FROM oracle/database:12.1.0.2-ee
ENV ORACLE_SID SID
ENV ORACLE_PWD passw0rd
ENV ORACLE_PDB pdb
COPY --chown=oracle:dba oradata/ /opt/oracle/oradata/
COPY startup/ /opt/oracle/scripts/startup/
docker build -t oracle/database:12.1.0.2-ee-with-oradata
There are two points to note.
First, oradata depends on the environment variables ORACLE_SID, ORACLE_PWD, and ORACLE_PDB, so add these environment variables to your Dockerfile as well. If these change, recreate oradata as well.
The other is to change the owner to oracle: dba when copying oradata. If you copy it as is, it will become the root user, and the oracle user will not be able to mount the initial database.
Then use this image and run it in .gitlab-ci.yml.
docker-compose.yml
version: '2'
services:
oracle-database:
image: oracle/database:12.1.0.2-ee-with-oradata
container_name: oracle-database
ports:
- 1521:1521
yml:.gitlab-ci.yml
pytest:
stage: test
image: docker:dind
script:
- apk update && apk add bash python3 python3-dev py3-pip docker-compose
- python3 -m pip install pytest
- docker-compose up -d
- sleep 120s
- pytest
(I'm not sure about building the execution environment for pytest, I'll check it later)
Since it takes time to open the initial database, we use sleep 120s as an example here.
For the time being, by doing something like this, it became possible to run unit tests using the Oracle database on the dind of GitLab CI.
--A unit test environment using Oracle database was built on dind of GitLab-CI. --By preparing oradata for the initial database, we were able to reduce the time it takes to start up the database. --Since the royal road is to use services in the unit test environment that uses a database in GitLab-CI, I would like to aim to break away from dind in that way. ――In the first place, dind is an environment for debugging Docker itself, so I would like to avoid using it positively. ――In practice, AWS ECR is used for the repository, so I would like to investigate the authority around that in the future. ――I would like to know other cases where you are using Oracle database in practice and are building a CI environment to write unit tests. ――If you can talk about something like "I'm like this!", I'd appreciate it if you could comment.
Recommended Posts