package com.techsoft.sql;

public class OracleSql {
	
	public static final String SELECT_ALL_SQL = "SELECT SQLID, \n"
												+ "OID, \n"
												+ "PID, \n"
												+ "MID, \n"
                                                + "OBJ_VERSION, \n" 
                                                + "SQLNAME, \n" 
                                                + "SQLDESC, \n" 
                                                + "DEF_SELECT_SQL, \n"
                                                + "DEF_SELECT_PARAMS, \n" 
                                                + "DEF_SELECT_METADATA, \n"
                                                + "DEF_INSERT_SQL, \n" 
                                                + "DEF_INSERT_PARAMS, \n"
                                                + "DEF_UPDATE_SQL, \n" 
                                                + "DEF_UPDATE_PARAMS, \n"
                                                + "DEF_DELETE_SQL, \n" 
                                                + "DEF_DELETE_PARAMS, \n" 
                                                + "SELECT_SQL, \n"
                                                + "INSERT_SQL, \n" 
                                                + "UPDATE_SQL, \n" 
                                                + "DELETE_SQL, \n"
                                             	+ "SQL_OPTIONS,  \n" 
                                                + "TABLENAME, \n"
                                             	+ "KEYFIELD \n"
                                             	+ "FROM TB_SM_SQLDEFINE ";

	public static final String SELECT_SQL =  "SELECT SQLID, \n"
		                                     + "OID, \n"
			                                 + "PID, \n"
			                                 + "MID, \n"
                                             + "OBJ_VERSION, \n" 
                                             + "SQLNAME, \n" 
                                             + "SQLDESC, \n" 
                                             + "DEF_SELECT_SQL, \n"
                                             + "DEF_SELECT_PARAMS, \n" 
                                             + "DEF_SELECT_METADATA, \n"
                                             + "DEF_INSERT_SQL, \n" 
                                             + "DEF_INSERT_PARAMS, \n"
                                             + "DEF_UPDATE_SQL, \n" 
                                             + "DEF_UPDATE_PARAMS, \n"
                                             + "DEF_DELETE_SQL, \n" 
                                             + "DEF_DELETE_PARAMS, \n" 
                                             + "SELECT_SQL, \n"
                                             + "INSERT_SQL, \n" 
                                             + "UPDATE_SQL, \n" 
                                             + "DELETE_SQL, \n"
         	                                 + "SQL_OPTIONS,  \n" 
                                             + "TABLENAME, \n" 
                                             + "KEYFIELD \n"
         	                                 + "FROM TB_SM_SQLDEFINE "
		                                	 + "WHERE SQLID = ?";

	public static final String INSERT_SQL = "declare \n"
                                            + "  rec number; \n"
                                            + "begin \n"
                                            + "  select count(*) into rec from tb_sm_sqldefine t where t.sqlid = ?; \n"
                                            + "  if (rec <= 0) then \n"
                                            + "    insert into tb_sm_sqldefine t \n"
                                            + "      (t.oid, \n"
                                            + "       T.SQLID, \n"
                                            + "       T.OBJ_VERSION, \n"
                                            + "       T.SQLNAME, \n"
                                            + "       T.DEF_SELECT_SQL, \n"
                                            + "       T.DEF_SELECT_PARAMS, \n"
                                            + "       T.DEF_SELECT_METADATA, \n"
                                            + "       T.DEF_INSERT_SQL, \n"
                                            + "       T.DEF_INSERT_PARAMS, \n"
                                            + "       T.DEF_UPDATE_SQL, \n"
                                            + "       T.DEF_UPDATE_PARAMS, \n"
                                            + "       T.DEF_DELETE_SQL, \n"
                                            + "       T.DEF_DELETE_PARAMS, \n"
                                            + "       T.SELECT_SQL, \n"
                                            + "       T.INSERT_SQL, \n"
                                            + "       T.UPDATE_SQL, \n"
                                            + "       T.DELETE_SQL, \n"
                                            + "       T.SQL_OPTIONS, \n"
                                            + "       T.TABLENAME, \n"
                                            + "       T.KEYFIELD)"
                                            + "    values \n"
                                            + "      (to_char(sysdate, 'yyyy-MM-dd-hh24-mi-ss-ssssssssss'), \n"
                                            + "       ?, \n"
                                            + "       1, \n"
                                            + "       ?, \n"
                                            + "       ?, \n"
                                            + "       ?, \n"
                                            + "       ?, \n"
                                            + "       ?, \n"
                                            + "       ?, \n"
                                            + "       ?, \n"
                                            + "       ?, \n"
                                            + "       ?, \n"
                                            + "       ?, \n"
                                            + "       ?, \n"
                                            + "       ?, \n"
                                            + "       ?, \n"
                                            + "       ?, \n"
                                            + "       ?, \n"
                                            + "       ?, \n"
                                            + "       ?); \n"
                                            + "  else \n"
                                            + "    update tb_sm_sqldefine t \n"
                                            + "       set T.OBJ_VERSION         = T.OBJ_VERSION + 1, \n"
                                            + "           T.SQLNAME             = ?, \n"
                                            + "           T.DEF_SELECT_SQL      = ?, \n"
                                            + "           T.DEF_SELECT_PARAMS   = ?, \n"
                                            + "           T.DEF_SELECT_METADATA = ?, \n"
                                            + "           T.DEF_INSERT_SQL      = ?, \n"
                                            + "           T.DEF_INSERT_PARAMS   = ?, \n"
                                            + "           T.DEF_UPDATE_SQL      = ?, \n"
                                            + "           T.DEF_UPDATE_PARAMS   = ?, \n"
                                            + "           T.DEF_DELETE_SQL      = ?, \n"
                                            + "           T.DEF_DELETE_PARAMS   = ?, \n"
                                            + "           T.SELECT_SQL          = ?, \n"
                                            + "           T.INSERT_SQL          = ?, \n"
                                            + "           T.UPDATE_SQL          = ?, \n"
                                            + "           T.DELETE_SQL          = ?, \n"
                                            + "           T.SQL_OPTIONS         = ?, \n"
                                            + "           T.TABLENAME           = ?, \n"
                                            + "           T.KEYFIELD            = ? \n"
                                            + "     where t.sqlid = ?; \n"
                                            + "  end if; \n"
                                            + "end; \n";

	public static final String DELETE_SQL = "";

}
