//+------------------------------------------------------------------+
//| 스크립트 프로그램 시작 기능 |
//+------------------------------------------------------------------+
void OnStart()
{
string filename="departments.sqlite";
//--- 공통 터미널 폴더에서 데이터베이스 생성 또는 열기
int db=DatabaseOpen(filename, DATABASE_OPEN_READWRITE | DATABASE_OPEN_CREATE |DATABASE_OPEN_COMMON);
if(db==INVALID_HANDLE)
{
Print("DB: ", filename, " 코드와 함께 열기 실패 ", GetLastError());
return;
}
//--- COMPANY 테이블을 생성
if(!CreataTableCompany(db))
{
DatabaseClose(db);
return;
}
//--- DEPARTMENT 테이블을 생성
if(!CreataTableDepartment(db))
{
DatabaseClose(db);
return;
}
//--- COMPANY 및 DEPARTMENT 테이블에 모든 필드 목록을 표시
PrintFormat("요청 인쇄 시도 \"PRAGMA TABLE_INFO(COMPANY);PRAGMA TABLE_INFO(DEPARTMENT)\"");
if(DatabasePrint(db, "PRAGMA TABLE_INFO(COMPANY);PRAGMA TABLE_INFO(DEPARTMENT)", 0)<0)
{
PrintFormat("DatabasePrint(\"PRAGMA TABLE_INFO()\") failed, error code=%d", GetLastError());
DatabaseClose(db);
return;
}
//--- 로그에 COMPANY 테이블을 표시
PrintFormat("요청 인쇄 시도 \"SELECT * from COMPANY\"");
if(DatabasePrint(db, "SELECT * from COMPANY", 0)<0)
{
Print("DatabasePrint failed with code ", GetLastError());
DatabaseClose(db);
return;
}
//--- COMPANY 및 DEPARTMENT 테이블을 결합하기 위한 요청 텍스트
string request="SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT "
"ON COMPANY.ID = DEPARTMENT.EMP_ID";
//--- 테이블 조합 결과를 표시
PrintFormat("요청 인쇄 시도 \"SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT\"");
if(DatabasePrint(db, request, 0)<0)
{
Print("DatabasePrint failed with code ", GetLastError());
DatabaseClose(db);
return;
}
//--- 데이터베이스 닫기
DatabaseClose(db);
}
/*
결과:
Try to print request "PRAGMA TABLE_INFO(COMPANY);PRAGMA TABLE_INFO(DEPARTMENT)"
#| cid name type notnull dflt_value pk
-+-------------------------------------------
1| 0 ID INT 1 1
2| 1 NAME TEXT 1 0
3| 2 AGE INT 1 0
4| 3 ADDRESS CHAR(50) 0 0
5| 4 SALARY REAL 0 0
#| cid name type notnull dflt_value pk
-+------------------------------------------
1| 0 ID INT 1 1
2| 1 DEPT CHAR(50) 1 0
3| 2 EMP_ID INT 1 0
Try to print request "SELECT * from COMPANY"
#| ID NAME AGE ADDRESS SALARY
-+--------------------------------
1| 1 Paul 32 California 25000.0
2| 2 Allen 25 Texas 15000.0
3| 3 Teddy 23 Norway 20000.0
4| 4 Mark 25 Rich-Mond 65000.0
5| 5 David 27 Texas 85000.0
6| 6 Kim 22 South-Hall 45000.0
7| 7 James 24 Houston 10000.0
Try to print request "SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT"
#| EMP_ID NAME DEPT
-+-------------------------
1| 1 Paul IT Billing
2| 2 Allen Engineering
3| Teddy
4| Mark
5| David
6| Kim
7| 7 James Finance
*/
//+------------------------------------------------------------------+
//| COMPANY 테이블 생성 |
//+------------------------------------------------------------------+
bool CreateTableCompany(int database)
{
//--- COMPANY 테이블이 있는 경우 삭제
if(DatabaseTableExists(database, "COMPANY"))
{
//--- 테이블 삭제
if(!DatabaseExecute(database, "DROP TABLE COMPANY"))
{
Print("코드가 있는 테이블 COMPANY 삭제 실패 ", GetLastError());
return(false);
}
}
//--- COMPANY 테이블을 생성
if(!DatabaseExecute(database, "CREATE TABLE COMPANY("
"ID INT PRIMARY KEY NOT NULL,"
"NAME TEXT NOT NULL,"
"AGE INT NOT NULL,"
"ADDRESS CHAR(50),"
"SALARY REAL );"))
{
Print("DB: 코드가 있는 테이블 COMPANY 삭제 실패 ", GetLastError());
return(false);
}
//--- COMPANY 테이블에 데이터 입력
if(!DatabaseExecute(database, "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 25000.00); "
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00); "
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00); "
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Mark', 25, 'Rich-Mond', 65000.00); "
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'David', 27, 'Texas', 85000.0); "
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Kim', 22, 'South-Hall', 45000.0); "
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (7, 'James', 24, 'Houston', 10000.00); "))
{
Print("코드로 COMPANY 삽입 실패 ", GetLastError());
return(false);
}
//--- 성공
return(true);
}
//+------------------------------------------------------------------+
//| DEPARTMENT 테이블 생성 |
//+------------------------------------------------------------------+
bool CreateTableDepartment(int database)
{
//--- DEPARTMENT 테이블이 있는 경우 삭제
if(DatabaseTableExists(database, "DEPARTMENT"))
{
//--- 테이블 삭제
if(!DatabaseExecute(database, "DROP TABLE DEPARTMENT"))
{
Print("코드로 테이블 DEPARTMENT를 삭제 실패 ", GetLastError());
return(false);
}
}
//--- DEPARTMENT 테이블을 생성
if(!DatabaseExecute(database, "CREATE TABLE DEPARTMENT ("
"ID INT PRIMARY KEY NOT NULL,"
"DEPT CHAR(50) NOT NULL,"
"EMP_ID INT NOT NULL);"))
{
Print("DB: 코드로 테이블 DEPARTMENT 생성 실패 ", GetLastError());
return(false);
}
//--- DEPARTMENT 테이블에 데이터를 입력
if(!DatabaseExecute(database, "INSERT INTO DEPARTMENT (ID,DEPT,EMP_ID) VALUES (1, 'IT Billing', 1); "
"INSERT INTO DEPARTMENT (ID,DEPT,EMP_ID) VALUES (2, 'Engineering', 2); "
"INSERT INTO DEPARTMENT (ID,DEPT,EMP_ID) VALUES (3, 'Finance', 7);"))
{
Print("코드로 DEPARTMENT 삽입 실패 ", GetLastError());
return(false);
}
//--- 성공
return(true);
}
//+-------------------------------------------------------------------
|