Django Oracle11g 연동하는 방법

Django Oracle11g 연동

Django 프레임워크 사용할 때 Oracle 버전에 대한 Dependency가 있습니다.

https://docs.djangoproject.com/en/2.0/releases/2.0/#dropped-support-for-oracle-11-2

Django 2.0 release notes | Django documentation | Django

Django The web framework for perfectionists with deadlines. Overview Download Documentation News Community Code Issues About ♥ Donate

docs.djangoproject.com

Django 2.0부터 Oracle11g에 대한 지원을 중단하게 되었습니다. 그리고 2.X Python도 지원하지 않습니다. 그래서 Django 프레임워크를 이용한 개발 시 Oracle Database를 사용한다면 버전 Dependency에대해서 고려해야합니다.

Django Oracle11g 연동 편법

하지만 Django 2.X 버전에서 Oracle11g를 사용할 수 있는 편법이 한가지 있습니다.

Django 2.X에서 Oracle12c 이상을 지원하는 이유는 SQL 문법이 다르게 사용되기 때문입니다. Django의 Model을 다룰 때 Oracle12c에서 지원하고 Oracle11g에서 지원하지 않는 문법을 사용하기 때문에 Django 2.X에서 Oracle11g를 사용하면 아래와 같은 상환에서 오류가 발생합니다.

manage.py migrate 할때 SQL문법과 관련된 오류 발생
- 그래서 django admin에 필요한 테이블들을 생성하지 못한다.

이런 문제를 해결하기 위해 Oracle11g에서 사용하는 계정에 미리 django admin에서 사용되는 스키마를 생성하면 사용할 수 있다.

스키마를 미리 생성해서 사용하는 방법으로 2가지가 있다.

Django 1.11 설치 후 2.X 설치

만약 Oracle11g를 사용해야하는 환경이라면 Django 1.11을 먼저 설치하고 migrate을 진행해서 admin 테이블들을 생성하고 Django 2.X로 다시 설치해서 진행하면 가능하다.

DDL Script 추출 후 적용

두번째 방법으로 Oracle12c에서 적용된 테이블들을 Oracle11g에 맞게 DDL 스크립트를 작성해서 스키마를 정의하는 방법이다.

Python 3.9.6
Django 2.2.24
Oracle11g
cx_Oracle 8.2

아래 Script는 위의 환경에서 테스트한 스키마입니다. Oracle12c에서 추출하고 Oracle11g에 맞게 수정한 내용입니다.

/*
django 2.X 버전에서는 oracle 12c 이상 버전만 지원한다.
oracle 11g를 사용하려면 django admin 스키마를 주입해서 사용해야한다.

하위 테이블들에 대한 스키마 생성
AUTH_GROUP
DJANGO_CONTENT_TYPE
AUTH_PERMISSION
AUTH_GROUP_PERMISSIONS
DJANGO_MIGRATIONS
DJANGO_SESSION
AUTH_USER
DJANGO_ADMIN_LOG
AUTH_USER_GROUPS
AUTH_USER_USER_PERMISSIONS
*/


--AUTH_GROUP
CREATE TABLE "AUTH_GROUP"(
    "ID" NUMBER(11,0)  NOT NULL ENABLE,
	"NAME" NVARCHAR2(150),
	PRIMARY KEY ("ID"),
	UNIQUE ("NAME")
);

CREATE SEQUENCE "AUTH_GROUP_SEQ"
    MINVALUE 1
    MAXVALUE 999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

CREATE OR REPLACE TRIGGER "AUTH_GROUP_TR"
BEFORE INSERT ON "AUTH_GROUP"
FOR EACH ROW
WHEN (new."ID" IS NULL) BEGIN
	SELECT "AUTH_GROUP_SEQ".nextval
	INTO :new."ID" FROM dual;
END;

ALTER TRIGGER "AUTH_GROUP_TR" ENABLE;


--DJANGO_CONTENT_TYPE
CREATE TABLE "DJANGO_CONTENT_TYPE"(
    "ID" NUMBER(11,0) NOT NULL ENABLE,
	"APP_LABEL" NVARCHAR2(100),
	"MODEL" NVARCHAR2(100),
	PRIMARY KEY ("ID"),
	CONSTRAINT "DJANGO_CO_APP_LABEL_76BD3D3B_U" UNIQUE ("APP_LABEL", "MODEL")
);

CREATE SEQUENCE "DJANGO_CONTENT_TYPE_SEQ"
    MINVALUE 1
    MAXVALUE 999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

CREATE OR REPLACE TRIGGER "DJANGO_CONTENT_TYPE_TR"
BEFORE INSERT ON "DJANGO_CONTENT_TYPE"
FOR EACH ROW
WHEN (new."ID" IS NULL) BEGIN
	SELECT "DJANGO_CONTENT_TYPE_SEQ".nextval
	INTO :new."ID" FROM dual;
END;

ALTER TRIGGER "DJANGO_CONTENT_TYPE_TR" ENABLE;


--AUTH_PERMISSION
CREATE TABLE "AUTH_PERMISSION"(
    "ID" NUMBER(11,0) NOT NULL ENABLE,
	"NAME" NVARCHAR2(255),
	"CONTENT_TYPE_ID" NUMBER(11,0) NOT NULL ENABLE,
	"CODENAME" NVARCHAR2(100),
	PRIMARY KEY ("ID"),
 	CONSTRAINT "AUTH_PERM_CONTENT_T_01AB375A_U" UNIQUE ("CONTENT_TYPE_ID", "CODENAME"),
    CONSTRAINT "AUTH_PERM_CONTENT_T_2F476E4B_F" FOREIGN KEY ("CONTENT_TYPE_ID")
	    REFERENCES "DJANGO_CONTENT_TYPE" ("ID") DEFERRABLE INITIALLY DEFERRED ENABLE
);

CREATE INDEX "AUTH_PERMI_CONTENT_TY_2F476E4B" ON "AUTH_PERMISSION" ("CONTENT_TYPE_ID");

CREATE SEQUENCE "AUTH_PERMISSION_SEQ"
    MINVALUE 1
    MAXVALUE 999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

CREATE OR REPLACE TRIGGER "AUTH_PERMISSION_TR"
BEFORE INSERT ON "AUTH_PERMISSION"
FOR EACH ROW
WHEN (new."ID" IS NULL) BEGIN
	SELECT "AUTH_PERMISSION_SEQ".nextval
	INTO :new."ID" FROM dual;
END;

ALTER TRIGGER "AUTH_PERMISSION_TR" ENABLE;


--AUTH_GROUP_PERMISSION
CREATE TABLE "AUTH_GROUP_PERMISSIONS"(
	"ID" NUMBER(11,0) NOT NULL ENABLE,
	"GROUP_ID" NUMBER(11,0) NOT NULL ENABLE,
	"PERMISSION_ID" NUMBER(11,0) NOT NULL ENABLE,
	PRIMARY KEY ("ID"),
	CONSTRAINT "AUTH_GROU_GROUP_ID__0CD325B0_U" UNIQUE ("GROUP_ID", "PERMISSION_ID"),
	CONSTRAINT "AUTH_GROU_GROUP_ID_B120CBF9_F" FOREIGN KEY ("GROUP_ID")
	    REFERENCES "AUTH_GROUP" ("ID") DEFERRABLE INITIALLY DEFERRED ENABLE,
	CONSTRAINT "AUTH_GROU_PERMISSIO_84C5C92E_F" FOREIGN KEY ("PERMISSION_ID")
	    REFERENCES "AUTH_PERMISSION" ("ID") DEFERRABLE INITIALLY DEFERRED ENABLE
);

CREATE INDEX "AUTH_GROUP_GROUP_ID_B120CBF9" ON "AUTH_GROUP_PERMISSIONS" ("GROUP_ID");
CREATE INDEX "AUTH_GROUP_PERMISSION_84C5C92E" ON "AUTH_GROUP_PERMISSIONS" ("PERMISSION_ID");

CREATE SEQUENCE "AUTH_GROUP_PERMISSIONS_SEQ" 
    MINVALUE 1 
    MAXVALUE 999999999 
    START WITH 1 
    INCREMENT BY 1 
    CACHE 20;

CREATE OR REPLACE TRIGGER "AUTH_GROUP_PERMISSIONS_TR"
BEFORE INSERT ON "AUTH_GROUP_PERMISSIONS"
FOR EACH ROW
WHEN (new."ID" IS NULL) BEGIN
	SELECT "AUTH_GROUP_PERMISSIONS_SEQ".nextval
	INTO :new."ID" FROM dual;
END;

ALTER TRIGGER "AUTH_GROUP_PERMISSIONS_TR" ENABLE;


--DJANGO_MIGRATIONS
CREATE TABLE "DJANGO_MIGRATIONS"(
	"ID" NUMBER(11,0) NOT NULL ENABLE,
	"APP" NVARCHAR2(255),
	"NAME" NVARCHAR2(255),
	"APPLIED" TIMESTAMP (6) NOT NULL ENABLE,
	PRIMARY KEY ("ID")
);

CREATE SEQUENCE "DJANGO_MIGRATIONS_SEQ" 
    MINVALUE 1 
    MAXVALUE 999999999 
    START WITH 1 
    INCREMENT BY 1 
    CACHE 20;

CREATE OR REPLACE TRIGGER "DJANGO_MIGRATIONS_TR"
BEFORE INSERT ON "DJANGO_MIGRATIONS"
FOR EACH ROW
WHEN (new."ID" IS NULL) BEGIN
	SELECT "DJANGO_MIGRATIONS_SEQ".nextval
	INTO :new."ID" FROM dual;
END;

ALTER TRIGGER "DJANGO_MIGRATIONS_TR" ENABLE;


--DJANGO_SESSION
CREATE TABLE "DJANGO_SESSION"(
    "SESSION_KEY" NVARCHAR2(40) NOT NULL ENABLE,
	"SESSION_DATA" NCLOB,
	"EXPIRE_DATE" TIMESTAMP (6) NOT NULL ENABLE,
	PRIMARY KEY ("SESSION_KEY")
);

CREATE INDEX "DJANGO_SES_EXPIRE_DAT_A5C62663" ON "DJANGO_SESSION" ("EXPIRE_DATE");


--AUTH_USER
CREATE TABLE "AUTH_USER"(
	"ID" NUMBER(11,0) NOT NULL ENABLE,
	"PASSWORD" NVARCHAR2(128),
	"LAST_LOGIN" TIMESTAMP (6),
	"IS_SUPERUSER" NUMBER(1,0) NOT NULL ENABLE,
	"USERNAME" NVARCHAR2(150),
	"FIRST_NAME" NVARCHAR2(30),
	"LAST_NAME" NVARCHAR2(150),
	"EMAIL" NVARCHAR2(254),
	"IS_STAFF" NUMBER(1,0) NOT NULL ENABLE,
	"IS_ACTIVE" NUMBER(1,0) NOT NULL ENABLE,
	"DATE_JOINED" TIMESTAMP (6) NOT NULL ENABLE,
	CHECK ("IS_SUPERUSER" IN (0,1)) ENABLE,
	CHECK ("IS_STAFF" IN (0,1)) ENABLE,
	CHECK ("IS_ACTIVE" IN (0,1)) ENABLE,
	PRIMARY KEY ("ID"),
	UNIQUE ("USERNAME")
);

CREATE SEQUENCE "AUTH_USER_SEQ" 
    MINVALUE 1 
    MAXVALUE 999999999 
    START WITH 1 
    INCREMENT BY 1 
    CACHE 20;

CREATE OR REPLACE TRIGGER "AUTH_USER_TR"
BEFORE INSERT ON "AUTH_USER"
FOR EACH ROW
WHEN (new."ID" IS NULL) BEGIN
	SELECT "AUTH_USER_SEQ".nextval
	INTO :new."ID" FROM dual;
END;

ALTER TRIGGER "AUTH_USER_TR" ENABLE;


--DJANGO_ADMIN_LOG
CREATE TABLE "DJANGO_ADMIN_LOG"(
	"ID" NUMBER(11,0) NOT NULL ENABLE,
	"ACTION_TIME" TIMESTAMP (6) NOT NULL ENABLE,
	"OBJECT_ID" NCLOB,
	"OBJECT_REPR" NVARCHAR2(200),
	"ACTION_FLAG" NUMBER(11,0) NOT NULL ENABLE,
	"CHANGE_MESSAGE" NCLOB,
	"CONTENT_TYPE_ID" NUMBER(11,0),
	"USER_ID" NUMBER(11,0) NOT NULL ENABLE,
	CHECK ("ACTION_FLAG" >= 0) ENABLE,
	PRIMARY KEY ("ID"),
	CONSTRAINT "DJANGO_AD_CONTENT_T_C4BCE8EB_F" FOREIGN KEY ("CONTENT_TYPE_ID")
	    REFERENCES "DJANGO_CONTENT_TYPE" ("ID") DEFERRABLE INITIALLY DEFERRED ENABLE,
	CONSTRAINT "DJANGO_AD_USER_ID_C564EBA6_F" FOREIGN KEY ("USER_ID")
	    REFERENCES "AUTH_USER" ("ID") DEFERRABLE INITIALLY DEFERRED ENABLE
);

CREATE INDEX "DJANGO_ADM_CONTENT_TY_C4BCE8EB" ON "DJANGO_ADMIN_LOG" ("CONTENT_TYPE_ID");
CREATE INDEX "DJANGO_ADM_USER_ID_C564EBA6" ON "DJANGO_ADMIN_LOG" ("USER_ID");

CREATE SEQUENCE "DJANGO_ADMIN_LOG_SEQ" 
    MINVALUE 1 
    MAXVALUE 999999999 
    START WITH 1 
    INCREMENT BY 1 
    CACHE 20;

CREATE OR REPLACE TRIGGER "DJANGO_ADMIN_LOG_TR"
BEFORE INSERT ON "DJANGO_ADMIN_LOG"
FOR EACH ROW
WHEN (new."ID" IS NULL) BEGIN
	SELECT "DJANGO_ADMIN_LOG_SEQ".nextval
	INTO :new."ID" FROM dual;
END;

ALTER TRIGGER "DJANGO_ADMIN_LOG_TR" ENABLE;


--AUTH_USER_GROUPS
CREATE TABLE "AUTH_USER_GROUPS"(
	"ID" NUMBER(11,0) NOT NULL ENABLE,
	"USER_ID" NUMBER(11,0) NOT NULL ENABLE,
	"GROUP_ID" NUMBER(11,0) NOT NULL ENABLE,
	PRIMARY KEY ("ID"),
	CONSTRAINT "AUTH_USER_USER_ID_G_94350C0C_U" UNIQUE ("USER_ID", "GROUP_ID"),
	CONSTRAINT "AUTH_USER_USER_ID_6A12ED8B_F" FOREIGN KEY ("USER_ID")
	    REFERENCES "AUTH_USER" ("ID") DEFERRABLE INITIALLY DEFERRED ENABLE,
	CONSTRAINT "AUTH_USER_GROUP_ID_97559544_F" FOREIGN KEY ("GROUP_ID")
	    REFERENCES "AUTH_GROUP" ("ID") DEFERRABLE INITIALLY DEFERRED ENABLE
);

CREATE INDEX "AUTH_USER__USER_ID_6A12ED8B" ON "AUTH_USER_GROUPS" ("USER_ID");
CREATE INDEX "AUTH_USER__GROUP_ID_97559544" ON "AUTH_USER_GROUPS" ("GROUP_ID");

CREATE SEQUENCE "AUTH_USER_GROUPS_SEQ" 
    MINVALUE 1 
    MAXVALUE 999999999 
    START WITH 1 
    INCREMENT BY 1 
    CACHE 20;

CREATE OR REPLACE TRIGGER "AUTH_USER_GROUPS_TR"
BEFORE INSERT ON "AUTH_USER_GROUPS"
FOR EACH ROW
WHEN (new."ID" IS NULL) BEGIN
	SELECT "AUTH_USER_GROUPS_SEQ".nextval
	INTO :new."ID" FROM dual;
END;

ALTER TRIGGER "AUTH_USER_GROUPS_TR" ENABLE;


--AUTH_USER_USER_PERMISSIONS
CREATE TABLE "AUTH_USER_USER_PERMISSIONS"(
	"ID" NUMBER(11,0)  NOT NULL ENABLE,
	"USER_ID" NUMBER(11,0) NOT NULL ENABLE,
	"PERMISSION_ID" NUMBER(11,0) NOT NULL ENABLE,
	PRIMARY KEY ("ID"),
	CONSTRAINT "AUTH_USER_USER_ID_P_14A6B632_U" UNIQUE ("USER_ID", "PERMISSION_ID"),
	CONSTRAINT "AUTH_USER_USER_ID_A95EAD1B_F" FOREIGN KEY ("USER_ID")
	    REFERENCES "AUTH_USER" ("ID") DEFERRABLE INITIALLY DEFERRED ENABLE,
	CONSTRAINT "AUTH_USER_PERMISSIO_1FBB5F2C_F" FOREIGN KEY ("PERMISSION_ID")
	    REFERENCES "AUTH_PERMISSION" ("ID") DEFERRABLE INITIALLY DEFERRED ENABLE
);

CREATE INDEX "AUTH_USER__USER_ID_A95EAD1B" ON "AUTH_USER_USER_PERMISSIONS" ("USER_ID");
CREATE INDEX "AUTH_USER__PERMISSION_1FBB5F2C" ON "AUTH_USER_USER_PERMISSIONS" ("PERMISSION_ID");

CREATE SEQUENCE "AUTH_USER_USER_PERMISSIONS_SEQ" 
    MINVALUE 1 
    MAXVALUE 999999999 
    START WITH 1 
    INCREMENT BY 1 
    CACHE 20;

CREATE OR REPLACE TRIGGER "AUTH_USER_USER_PERMISSIONS_TR"
BEFORE INSERT ON "AUTH_USER_USER_PERMISSIONS"
FOR EACH ROW
WHEN (new."ID" IS NULL) BEGIN
	SELECT "AUTH_USER_USER_PERMISSIONS_SEQ".nextval
	INTO :new."ID" FROM dual;
END;

ALTER TRIGGER "AUTH_USER_USER_PERMISSIONS_TR" ENABLE;

Django 2.X, Oracle11g환경에서 위의 스키마를 적용해주고 서버를 실행해준다면 admin에 바로 접근해서 사용할수 있다.

주의사용

단, 여전히 Django 2.X버전에서는 Oracle11g를 지원하지 않기 때문에 manage.py에서 사용되는 migrate와 같은 명령은 사용할 수 없다. 프로젝트를 배포해서 사용할수는 있지만... Django에서 제공하는 명령들을 사용하지 못한다.

저작자표시 비영리 변경금지

마이자몽

Django Oracle11g 연동하는 방법