//---------------------------------------------------
static bool IsNewHakbun(xRemoteDB db, string hakbun)
{
string query = string.Format("SELECT xHakbun FROM xSTUDENTS WHERE xHakbun='{0}'", hakbun);
if (!DB_Query(db, query)) return false;
if (db.HasRows) return Error("같은 학번이 이미 존재합니다." + hakbun);
return true;
}
static void Main(string[] args)
{
xRemoteDB db = new xRemoteDB("http://localhost:8080");
dropTables(db);
createTables(db);
insertRows(db);
}//Main
}//Main
//----------------------------------------------------
static int dropTable(xRemoteDB db, string tableName)
{
string queryStr = string.Format("DROP TABLE IF EXISTS {0}", tableName);
if (!DB_Query(db, queryStr)) return 0;
Console.WriteLine("Table '{0}' dropped.", tableName);
return 1;
}
//------------------------------------------------------
static int insertRow(xRemoteDB db, string tableName, string dataStr)
{
string queryStr = string.Format("INSERT INTO {0} VALUES({1})", tableName, dataStr);
if (!DB_Query(db, queryStr)) return 0;
Console.WriteLine("row inserted into '{0}'.", tableName);
return 1; //OK
}
//----------------------------------------------------
static int createTable(xRemoteDB db, string tableName, string tableDesign)
{
string queryStr = string.Format("CREATE TABLE {0} ({1}) ENGINE=InnoDB DEFAULT CHARSET=utf8", tableName, tableDesign);
if (!DB_Query(db, queryStr)) return 0;
Console.WriteLine("Table '{0}' Created.", tableName);
return 1;
}
//-----------------------------------------------
static bool IsValidDeptCode(xRemoteDB db, string code)
{
string query = string.Format("SELECT * FROM xDEPARTMENT WHERE xDept_code='{0}'", code);
if (!DB_Query(db, query)) return false;
if (db.HasRows) return true;
return false;
}
//-----------------------------------------------
static bool IsValid_IDENTIFIER(xRemoteDB db, string id)
{
if (string.IsNullOrEmpty(id)) return Error("학번이 null이거나 빈문자열입니다.");
if (id.Length != 8) return Error("학번의 길이는 8문자이어야 합니다");
if (IsNumericString(id.Substring(0, 2)) == false) return Error("학번의 처음 두 문자는 년도를 나타내야 합니다.(예: 19)");
if (id.Substring(5, 3).Equals("000")) return Error("'000'은 올바른 학번의 일련번호가 아닙니다");
if (IsNumericString(id.Substring(5, 3)) == false) return Error("학번의 마지막 세 문자는 일련번호여야 합니다.(예: 001)");
if (IsValidDeptCode(db, id.Substring(2, 3)) == false) return Error("학번의 3,4,5번째 문자에 일치하는 학과코드가 없습니다.");
return true;
}
//------------------------------------------------
static bool DB_Query(xRemoteDB db, string query)
{
//Console.WriteLine("DB_Query: " + query);
string err_msg = db.Query(query);
if (err_msg != null)
{
Console.WriteLine(err_msg + "\n" + query);
return false;
}
return true;
}
//-----------------------------------------------
static int insertStudent(xRemoteDB db, string hakbun, string name, string pass, string dept, int status, string addr, string email, string phone)
{
hakbun = hakbun.Trim();
if (IsValid_IDENTIFIER(db, hakbun) == false) return 0;
if (IsNewHakbun(db, hakbun) == false) return 0; //새 학생 추가인 경우에만 실행
if (IsValid_PEOPLE_NAME(name) == false) return 0;
if (IsValid_PASSWORD(pass) == false) return 0;
string str = string.Format("'{0}', '{1}', '{2}', '{3}', {4}, '{5}', '{6}', '{7}'",
hakbun, name, pass, dept, status, addr, email, phone);
return insertRow(db, "xSTUDENTS", str);
}
//------------------------------------------------------
static void dropTables(xRemoteDB db)
{
int cnt = 0;
cnt += dropTable(db, "xSTUDENTS");
cnt += dropTable(db, "xDEPARTMENT");
cnt += dropTable(db, "xSTUDENT_STATUS");
cnt += dropTable(db, "xENROLL_CLASS");
cnt += dropTable(db, "xOPEN_CLASS");
cnt += dropTable(db, "xCURRICULUM");
cnt += dropTable(db, "xCLASSES");
cnt += dropTable(db, "xTEACHERS");
Console.WriteLine("{0} tables dropped.\n", cnt);
}
//-------------------------------------------------------
static void createTables(xRemoteDB db)
{
int cnt = 0;
cnt += createTable(db, "xSTUDENT_STATUS",
@"xStatus_code int not null,
xStatus_title varchar(5) not null,
PRIMARY KEY(xStatus_code)");
cnt += createTable(db, "xDEPARTMENT",
@"xDept_code varchar(10) not null,
xDept_name varchar(30) not null,
xDept_use int not null,
xDept_year varchar(4) not null,
PRIMARY KEY(xDept_code)" );
cnt += createTable(db, "xSTUDENTS",
@"xHakbun varchar(8) not null,
xName varchar(20) not null,
xPassword varchar(20) not null,
xDept varchar(10) not null,
xStatus int not null,
xAddress varchar(50) not null,
xEmail varchar(30),
xPhone varchar(20),
PRIMARY KEY(xHakbun),
FOREIGN KEY(xDept) REFERENCES xDEPARTMENT(xDept_code),
FOREIGN KEY(xStatus) REFERENCES xSTUDENT_STATUS(xStatus_code)");
cnt += createTable(db, "xTEACHERS",
@"xID varchar(8) not null,
xName varchar(20) not null,
xPassword varchar(20) not null,
xDept varchar(10) not null,
xEMail varchar(30),
xPhone varchar(20),
PRIMARY KEY(xID)" );
cnt += createTable(db, "xCLASSES",
@"xCode varchar(9) not null,
xName varchar(20) not null,
xCredit int not null,
xHours int not null,
xYear varchar(4) not null,
PRIMARY KEY(xCode)");
cnt += createTable(db, "xCURRICULUM",
@"xYear varchar(4) not null,
xCode varchar(9) not null,
xPre_class varchar(9),
PRIMARY KEY(xYear, xCode),
FOREIGN KEY(xPre_class) REFERENCES xCLASSES(xCode),
FOREIGN KEY(xCode) REFERENCES xCLASSES(xCode)");
cnt += createTable(db, "xOPEN_CLASS",
@"xCurri_year varchar(4) not null,
xCode varchar(9) not null,
xOpen_year varchar(4) not null,
xOpen_semester int not null,
xDivision int,
xClass_time varchar(30) not null,
xTeacher varchar(8) not null,
PRIMARY KEY(xCurri_year, xCode),
FOREIGN KEY(xTeacher) REFERENCES xTEACHERS(xID),
FOREIGN KEY(xCurri_year, xCode) REFERENCES xCURRICULUM(xYear, xCode)");
cnt += createTable(db, "xENROLL_CLASS",
@"xCurri_year varchar(4) not null,
xCode varchar(9) not null,
xHakbun varchar(8) not null,
xGrade varchar(1) not null,
PRIMARY KEY(xCurri_year, xCode, xHakbun),
FOREIGN KEY(xCurri_year, xCode) REFERENCES xOPEN_CLASS(xCurri_year, xCode)");
Console.WriteLine("{0} tabels Created.\n", cnt);
}
//-------------------------------------------------
static void insertRows(xRemoteDB db)
{
int cnt = 0;
//(db, "테이블명", "데이터 문자열")
//xSTUDENT_STATUS -------------------------------------------
cnt += insertRow(db, "xSTUDENT_STATUS", "0, '재학'");
cnt += insertRow(db, "xSTUDENT_STATUS", "1, '휴학'");
cnt += insertRow(db, "xSTUDENT_STATUS", "2, '자퇴'");
cnt += insertRow(db, "xSTUDENT_STATUS", "3, '제적'");
cnt += insertRow(db, "xSTUDENT_STATUS", "4, '수료'");
cnt += insertRow(db, "xSTUDENT_STATUS", "9, '졸업'");
//xDEPARTMENT -------------------------------------------
cnt += insertRow(db, "xDEPARTMENT", "'615', '의료IT공학과', 1, '2012'");
cnt += insertRow(db, "xDEPARTMENT", "'620', '의공학부', 0, '2012'");
cnt += insertRow(db, "xDEPARTMENT", "'621', '제약생명공학과', 0, '2012'");
cnt += insertRow(db, "xDEPARTMENT", "'622', '의료공간디자인', 0, '2012'");
Console.WriteLine("{0} rows inserted.------------", cnt);
cnt = 0;
//xStudents ---------------------------------------------
cnt += insertStudent(db, "19615001", "강만기", "kanxxA1234@", "615", 0,
"서울시 종로구 효자동로 123번지", "*****@*****.**", "010-123-4567" );
cnt += insertStudent(db, "19615002", "김한길", "Kim_1234!", "615", 0,
"대전시 서구 관저동로 333번지", "*****@*****.**", "010-543-2525");
cnt += insertStudent(db, "19615003", "민상호", "Min@1234#", "615", 0,
"대구시 달성구 달성공원로 543번지", "*****@*****.**", "010-222-3355");
cnt += insertStudent(db, "19615004", "박길동", "Park999!", "615", 0,
"충남 세종시 신세종로 001번지", "*****@*****.**", "010-544-2216");
cnt += insertStudent(db, "18615001", "고민봉", "MinBong1234!", "615", 0,
"충북 청주시 상당1로 555번지", "*****@*****.**", "010-876-4576");
cnt += insertStudent(db, "18615002", "나경우", "Na_1234!", "615", 0,
"대전시 중구 태평로 333번지", "*****@*****.**", "010-377-7589");
cnt += insertStudent(db, "18615003", "도미노", "Domino7658@", "615", 0,
"서울시 용산구 이촌동로 122번지", "*****@*****.**", "010-555-6756");
cnt += insertStudent(db, "17615001", "구현미", "KuHyun9876!", "615", 0,
"부산시 동래구 온천로 428번지", "*****@*****.**", "010-231-2256");
cnt += insertStudent(db, "17615002", "송상수", "XangSu2234!", "615", 0,
"경기도 성남시 수정구 태평1로 234번지", "*****@*****.**", "010-786-1985");
cnt += insertStudent(db, "17615003", "안민수", "An$1234!", "615", 0,
"강원도 춘천시 효자동로 599번지", "*****@*****.**", "010-466-4656");
cnt += insertStudent(db, "16615001", "김혁재", "hjKim8234!", "615", 0,
"경남 합천군 쌍백로 468번지", "*****@*****.**", "010-436-2545");
cnt += insertStudent(db, "16615002", "노수영", "No!1234!", "615", 0,
"강원도 평창군 용평로 976번지", "*****@*****.**", "010-424-2323");
Console.WriteLine("{0} students inserted.--------------", cnt);
}