<?php
namespace CC;
class FILE{
	const D_BASE = __DIR__ . '/../..';
	const D_LOG = self::D_BASE . '/log';
	const D_SYSDB = self::D_BASE . '/sys_db';
	const D_USRDB = self::D_BASE . '/db';
	const D_EXPDB = self::D_BASE . '/exp_db';
	const D_EXPCSV = self::D_BASE . '/exp_csv';

	const ERR_LOG = self::D_LOG . '/application.log';
	const REQ_LOG = self::D_LOG . '/request.log';

	const SYSDB = self::D_SYSDB . '/sys.db';
	const DBTPL = self::D_SYSDB . '/usr.db';

	const USRDB = self::D_USRDB . '/usr_$ID$.db';

	const DELDB = self::D_EXPDB . '/DEL_$DATE$_$ID$.db';
	const EXPDB = self::D_EXPDB . '/EXPORT_$DATE$_$ID$.db';

	const EXPCSV = self::D_EXPCSV . '/$DATE$_$ID$_$TID$.csv';
};
class SYSDB{
	const IMPORT_DB_NAME = 'IMPORT_$DATE$';
	public static function getImpDbName( ){
		$ret = self::IMPORT_DB_NAME;
		return mb_ereg_replace('\$DATE\$', date('YmdHis') , $ret); 
	}

	const SQL_AND_COLS = [
		'TBL_DB'=>[
			'SQL' => 'select id,name,[order] from tbl_db',
			'COLS' => ['id','name','order'],
			'TYPE' => [ true , false , true ],
		],
		'MST_TYPE'=>[
			'SQL' => 'select id,name,inner from mst_type',
			'COLS' => ['id','name','inner'],
			'TYPE' => [ true , false , false ],
		],
		'MST_MODE'=>[
			'SQL' => 'select id,name from mst_mode',
			'COLS' => ['id','name'],
			'TYPE' => [ true , false ],
		],
		'MST_TYPE_MODE'=>[
			'SQL' => 'select type_id,mode_id from mst_type_mode',
			'COLS' => ['type_id','mode_id'],
			'TYPE' => [ true , true ],
		],
		'INS_TBL_DB'=>[
			'SQL' => 'insert into tbl_db( name , [order] ) values ( ? , ? )',
			'COLS' => ['name','order'],
			'TYPE' => [ false , true ],
		],
		'DEL_TBL_DB'=>[
			'SQL' => 'delete from tbl_db where id = ?',
			'COLS' => [ 'id' ],
			'TYPE' => [ true ],
		],
		'UPD_TBL_DB'=>[
//UPDATE テーブル名 SET カラム名1 = 値1, カラム名2 = 値2, ... WHERE 条件式;
			'SQL' => 'update tbl_db set name = ? , [order] = ? where id = ?',
			'COLS' => ['name','order','id'],
			'TYPE' => [ false , true , true ],
		],
		'GET_DB_INFO'=>[
			'SQL' => 'select id,name,[order] from tbl_db where id = ?',
			'COLS' => ['id','name','order'],
			'TYPE' => [ true , false , true ],
		],
		'INSERT_IMP_DB'=>[
			'SQL' => 'insert into tbl_db ( name , [order] ) select ? , max([order]) + 1 from tbl_db',
			'COLS' => ['name'],
			'TYPE' => [ false ],
		],
		'GET_IMP_DB_ID_INFO'=>[
			'SQL' => 'select id from tbl_db as t1 left outer join (select max(id) as mx from tbl_db ) as t2 where t1.id = t2.mx',
			'COLS' => ['id'],
			'TYPE' => [ true ],
		],

	];
};
class EXPORTS{
	const DATESTR = '-1 day';
	public static function getDelDate(){
		return strtotime( self::DATESTR );
	}
}
class USRDB{
	const SQLITE_MASTER = 'select name from sqlite_master where type="table" and name not like "sqlite\_%" escape "\"';
	const COL_LIST = 'PRAGMA table_info(?)';
	const COL_LIST_COLS = ['cid','name','type','notnull','dflt_value','pk'];
	const SYST_U_INDEXTNAME = '_sys_tbl';
	const UNIQUE_INDEX_NAME = '_sys_unique_index_$';
	const SYSTBLS = [
		'_sys_tbl',
		'_sys_col',
		'_sys_option',
		'_sys_unique',
		'_sys_unique_col',
		'_sys_relation',
		'_sys_relation_col',
		'_sys_auto_num',
		'_sys_sort',
	];
	const SYSTBL_COLS = [
		'_sys_tbl'=>[
			'id' => [ 'name' => 'id' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 1 ],
			'name' => [ 'name' => 'name' , 'type' => 'text' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
			'order' => [ 'name' => 'order' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
			'autoID' => [ 'name' => 'autoID' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
		],
		'_sys_col'=>[
			'id' => [ 'name' => 'id' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 1 ],
			'tableId' => [ 'name' => 'tableId' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
			'name' => [ 'name' => 'name' , 'type' => 'text' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
			'type' => [ 'name' => 'type' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
			'pk' => [ 'name' => 'pk' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
			'order' => [ 'name' => 'order' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
			'css' => [ 'name' => 'css' , 'type' => 'text' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
		],
		'_sys_option'=>[
			'id' => [ 'name' => 'id' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 1 ],
			'tableId' => [ 'name' => 'tableId' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
			'colId' => [ 'name' => 'colId' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
			'value' => [ 'name' => 'value' , 'type' => 'text' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
			'order' => [ 'name' => 'order' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
		],
		'_sys_unique'=>[
			'id' => [ 'name' => 'id' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 1 ],
			'Table' => [ 'name' => 'Table' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
		],
		'_sys_unique_col'=>[
			'id' => [ 'name' => 'id' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 1 ],
			'uId' => [ 'name' => 'uId' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
			'col' => [ 'name' => 'col' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
		],
		'_sys_relation'=>[
			'id' => [ 'name' => 'id' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 1 ],
			'cTable' => [ 'name' => 'cTable' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
			'pTable' => [ 'name' => 'pTable' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
		],
		'_sys_relation_col'=>[
			'id' => [ 'name' => 'id' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 1 ],
			'rId' => [ 'name' => 'rId' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
			'cCol' => [ 'name' => 'cCol' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
			'pCol' => [ 'name' => 'pCol' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
		],
		'_sys_auto_num'=>[
			'id' => [ 'name' => 'id' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 1 ],
			'prefix' => [ 'name' => 'prefix' , 'type' => 'text' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
			'suffix' => [ 'name' => 'suffix' , 'type' => 'text' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
			'length' => [ 'name' => 'length' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
			'zeroPad' => [ 'name' => 'zeroPad' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
		],
		'_sys_sort'=>[
			'id' => [ 'name' => 'id' , 'type' => 'integer' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 1 ],
			'name' => [ 'name' => 'name' , 'type' => 'text' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
			'sortStr' => [ 'name' => 'sortStr' , 'type' => 'text' , 'notnull' => 0 , 'dflt_value' => null , 'pk' => 0 ],
		],
	];
	const SQL_AND_COLS = [
		//画面呼び出しに直接値を返すのに利用する類のSQL
		'_sys_tbl'=>[
			'SQL' => 'select t.id,t.name,t.[order],t.autoID from _sys_tbl as t ',
			'COLS' => ['id','name','order','autoID'],
			'TYPE' => [ true , false , true, true ],
		],
		'_sys_col'=>[
			'SQL' => 'select c.id,c.tableId,c.name,c.type,c.pk,c.[order],c.css from _sys_col as c ',
			'COLS' => ['id','tableId','name','type','pk','order','css'],
			'TYPE' => [ true , true , false , true, true , true , false ],
		],
		'_sys_option'=>[
			'SQL' => 'select id,tableId,colId,value,[order] from _sys_option ',
			'COLS' => ['id','tableId','colId','value','order'],
			'TYPE' => [ true , true , true , false , true ],
		],
		'_sys_unique_join'=>[
			'SQL' => 'select u.id as uId, u.[Table] as [Table] , uc.id as id , uc.uId as ucUId , uc.col as col from _sys_unique_col as uc left outer join _sys_unique as u on u.id = uc.uId' ,
			'COLS' => ['uId','Table','id','ucUId','col'],
			'TYPE' => [ true , true , true , true, true ],
			'COLS_P' => ['uId'=>'id','Table'=>'Table'],
			'COLS_C' => ['id'=>'id','ucUId'=>'uId','col'=>'col'],
		],
		'_sys_relation_join'=>[
			'SQL' => 'select r.id as rId, r.cTable as cTable , r.pTable as pTable , rc.id as id , rc.rId as rcRId , rc.cCol as cCol, rc.pCol as pCol from _sys_relation_col as rc left outer join _sys_relation as r on r.id = rc.rId' ,
			'COLS' => ['rId','cTable','pTable','id','rcRId','cCol','pCol'],
			'TYPE' => [ true , true , true , true, true , true, true ],
			'COLS_P' => ['rId'=>'id','cTable'=>'cTable','pTable'=>'pTable'],
			'COLS_C' => ['id'=>'id','rcRId'=>'rId','cCol'=>'cCol','pCol'=>'pCol'],
		],
		'_sys_auto_num'=>[
			'SQL' => 'select id,prefix,suffix,length,zeroPad from _sys_auto_num ',
			'COLS' => ['id','prefix','suffix','length','zeroPad'],
			'TYPE' => [ true , false , false , true, true ],
		],
		'_sys_sort'=>[
			'SQL' => 'select id,name,sortStr from _sys_sort ',
			'COLS' => ['id','name','sortStr'],
			'TYPE' => [ true , false , false ],
		],

		//insert後にID取得目的で別途発行されるSQLの類
		'_sys_auto_num_max_id'=>[
			'SQL' => 'select max(id) as id from _sys_auto_num ',
			'COLS' => ['id'],
			'TYPE' => [ true ],
		],
		'_sys_unique_max_id'=>[
			'SQL' => 'select max(id) as id from _sys_unique ',
			'COLS' => ['id'],
			'TYPE' => [ true ],
		],
		'_sys_relation_max_id'=>[
			'SQL' => 'select max(id) as id from _sys_relation ',
			'COLS' => ['id'],
			'TYPE' => [ true ],
		],
		//整合性チェックでのみ利用する類のSQL
		'chk_sys_tbl_rlt'=>[
			'SQL' => 'select exists( select * from _sys_col as c1 where not exists( select * from _sys_tbl as t1 where t1.id = c1.tableId ) ) or exists( select * from _sys_option as o1 where not exists( select * from _sys_col as c2 where o1.colId = c2.id and o1.tableId = c2.tableId ) ) or exists( select * from _sys_unique_col as uc1 left outer join _sys_unique as u1 on ( u1.id = uc1.uId ) where not exists( select * from _sys_col as c3 where uc1.col = c3.id and u1.[Table] = c3.tableId ) ) or exists( select * from _sys_relation_col as rc1 left outer join _sys_relation as r1 on ( r1.id = rc1.rId ) where not exists( select * from _sys_col as c4 where rc1.cCol = c4.id and r1.cTable = c4.tableId ) or not exists( select * from _sys_col as c5 where rc1.pCol = c5.id and r1.pTable = c5.tableId ) ) or exists( select * from _sys_tbl as t2 where autoId not null and not exists( select * from _sys_auto_num as a1 where t2.autoId = a1.id ) ) as err',
			'COLS' => ['err'],
			'TYPE' => [ true ],
		],
		'check_sys_unique'=>[
			'SQL' => 'select u.id as id ,t.name as name from _sys_unique as u left outer join _sys_tbl as t on (u.[Table] = t.id)',
			'COLS' => ['id','name'],
			'TYPE' => [ true , false ],
		],
		'check_unique_idx_schema'=>[
			'SQL' => 'select name,tbl_name from sqlite_master where type="index" and name not like "sqlite%" ',
			'COLS' => ['name','tbl_name'],
			'TYPE' => [ false , false ],
		],

		//IDからIDを引く類。
		//同様のものが必要な場合、ここに追加するのではなく、
		//SQL_AND_COLSの全件取得SQLにSQL_WHEREを追加して利用する形式を推奨
		//表IDからUID
		'uid_by_tid'=>[
			'SQL' => 'select id from _sys_unique where [Table] = ?',
			'COLS' => ['id'],
			'TYPE' => [ true ],
		],
		//列IDからUID
		'uid_by_cid'=>[
			'SQL' => 'select u.id as id from _sys_unique as u where exists( select * from _sys_unique_col as uc where col = ? and u.id = uc.uid )',
			'COLS' => ['id'],
			'TYPE' => [ true ],
		],
		//表IDからRID
		'rid_by_tid'=>[
			'SQL' => 'select id from _sys_relation where ? in( pTable , cTable )',
			'COLS' => ['id'],
			'TYPE' => [ true ],
		],
		//列IDからUID
		'rid_by_cid'=>[
			'SQL' => 'select r.id as id from _sys_relation as r where exists( select * from _sys_relation_col as rc where ? in(pCol , cCol ) and r.id = rc.rid )',
			'COLS' => ['id'],
			'TYPE' => [ true ],
		],

	//結合対象取得用SQL
		//巨大で複雑なので、大コメントを別途付記した。
		//meで示したIDの表に結合されるべき表IDとしてhim,その結合の関連付けidとしてridが取得される。
		'relation_targs'=>[
			'SQL' => 'select r.me as me , r.him as him , r.rid as rid from( '.
					'select cTable as me, pTable as him, id as rid, 0 as flip from _sys_relation '.
					'union all select pTable as me, cTable as him, id as rid, 1 as flip from _sys_relation '.
				') as r where exists( '.
					'select * from ( '.
						'select * from _sys_unique '.
						'union all select -id , id from _sys_tbl where exists ( select * from _sys_col where _sys_tbl.id = _sys_col.tableId and pk = 1 ) '.
					') as uAndT where r.him = uAndT.[Table] and not exists( '.
						'select * from ( '.
							'select * from _sys_unique_col '.
							'union all select -id , -tableId , id from _sys_col where pk = 1 '.
						')as ucAndPk where ucAndPk.uid = uAndT.id and not exists( '.
							'select * from ( '.
								'select rid , cCol as myCol , pCol as hisCol , 0 as flip from _sys_relation_col '.
								'union all select rid , pCol as myCol , cCol as hisCol , 1 as flip from _sys_relation_col '.
							') as rc where ucAndPk.col = rc.hisCol and rc.rid = r.rid and rc.flip = r.flip '.
				')	)	)',
			'COLS' => ['me','him','rid'],
			'TYPE' => [ true , true , true ],
		],
	//AUTONUM取得用SQL
		//巨大で複雑なので、大コメントを別途付記した。
		//idとして、表のautonum部分の最大数が取得される。
		//利用時はまず、sqlのtblNameを対象表の表名で置換する。
		//続いて、sqlのcolNameを対象表の表名で置換する。
		//バインドパラメータとして、COLS_PARAM,TYPE_PARAMに併せてパラメータを用意する。
		'max_autonum_usrtbl'=>[
			'SQL' =>
				'select max( cast( id as integer ) )as id from('.
					'select substr( usr.id , length(prefix)+1, length( usr.id ) - length(prefix || suffix) ) as id from('.
						'select '.
							'COALESCE(?,"") as prefix,'.
							'COALESCE(?,"") as suffix,'.
							'________col______ as id '.
						'from '.
							'________table______'.
					') as usr where usr.id like ( prefix ||"%"||suffix)'.
				')where cast( cast( id as Integer ) as Text ) = ltrim( id , " ") or cast( cast( id as Integer ) as Text ) = ltrim( id , "0") or ( id != "" and ltrim( id , "0") = "")'
			,
			'COLS' => ['id'],
			'TYPE' => [ true ],
			'COLS_PARAM' => ['prefix','suffix'],
			'TYPE_PARAM' => [ false,false ],
			'tblName' => '________table______',
			'colName' => '________col______',
		],
		//利用時はまず、sqlのtblNameを対象表の表名で置換する。
		'max_rowid_usrtbl'=>[
			'SQL' =>'select max( rowid )as rowid from ________table______',
			'COLS' => ['rowid'],
			'TYPE' => [ true ],
			'tblName' => '________table______',
		],



	//削除
		//_sys_tbl
		'del_sys_tbl'=>[
			'SQL' => 'delete from _sys_tbl where id = ?',
			'COLS' => [ 'id' ],
			'TYPE' => [  true ],
		],
		//_sys_col
		'del_sys_col'=>[
			'SQL' => 'delete from _sys_col where id = ?',
			'COLS' => [ 'id' ],
			'TYPE' => [  true ],
		],
		//_sys_unique
		'del_unique'=>[
			'SQL1' => 'delete from _sys_unique_col where uid = ?',
			'SQL2' => 'delete from _sys_unique where id = ?',
			'COLS' => ['id' ],
			'TYPE' => [ true ],
		],

		//_sys_relation
		'del_relation_by_r'=>[
			'SQL1' => 'delete from _sys_relation where id = ?',
			'SQL2' => 'delete from _sys_relation_col where rid = ?',
			'COLS' => [ 'id' ],
			'TYPE' => [  true ],
		],
		'del_relation_by_c'=>[
			'SQL1' => 'delete from _sys_relation where exists( select * from _sys_relation_col as rc where ? in( rc.pCol , rc.cCol ) and rc.rId = _sys_relation.id )',
			'SQL2' => 'delete from _sys_relation_col where not exists( select * from _sys_relation as r where _sys_relation_col.rId = r.id ) or exists( select * from _sys_relation_col as rc where ? in( rc.pCol , rc.cCol ) and _sys_relation_col.rId = rc.rId ) ',
			'COLS' => [ 'id' ],
			'TYPE' => [  true ],
		],
		'del_relation_by_t'=>[
			'SQL1' => 'delete from _sys_relation where ? in( pTable , cTable )',
			'SQL2' => 'delete from _sys_relation_col where not exists( select * from _sys_relation as r where _sys_relation_col.rId = r.id ) or exists( select * from _sys_relation as r2 where _sys_relation_col.rId = r2.id and  ? in( r2.pTable , r2.cTable ) )',
			'COLS' => [ 'id' ],
			'TYPE' => [  true ],
		],
		//_sys_option
		'del_option_by_o'=>[
			'SQL' => 'delete from _sys_option where id = ?',
			'COLS' => [ 'id' ],
			'TYPE' => [  true ],
		],
		'del_option_by_c'=>[
			'SQL' => 'delete from _sys_option where colId = ?',
			'COLS' => [ 'id' ],
			'TYPE' => [  true ],
		],
		'del_option_by_t'=>[
			'SQL' => 'delete from _sys_option where tableId = ?',
			'COLS' => [ 'id' ],
			'TYPE' => [  true ],
		],

		//_sys_auto_num
		'del_auto_num_by_a'=>[
			'SQL' => 'delete from _sys_auto_num where id = ?',
			'COLS' => [ 'id' ],
			'TYPE' => [  true ],
		],
		'del_auto_num_by_t'=>[
			'SQL' => 'delete from _sys_auto_num where exists( select * from _sys_tbl as t where id = ? and t.autoID = _sys_auto_num.id',
			'COLS' => [ 'id' ],
			'TYPE' => [  true ],
		],
		//_sys_sort
		'del_sys_sort'=>[
			'SQL' => 'delete from _sys_sort',
			'COLS' => [],
			'TYPE' => [],
		],

	//更新
		//_sys_tbl
		'upd_sys_tbl_name_order'=>[
			'SQL' => 'update _sys_tbl set name = ? , [order] = ? where id = ?',
			'COLS' => ['name','order','id'],
			'TYPE' => [ false , true , true ],
		],
		'upd_sys_tbl_auto'=>[
			'SQL' => 'update _sys_tbl set autoId = ? where id = ?',
			'COLS' => ['autoId','id'],
			'TYPE' => [ true , true ],
		],
		'upd_sys_auto_num'=>[
			'SQL' => 'update _sys_auto_num set prefix = ? ,suffix = ? ,length = ? ,zeroPad = ? where id = ?',
			'COLS' => ['prefix','suffix','length','zeroPad','id'],
			'TYPE' => [ false , false , true, true , true ],
		],
		'upd_sys_col'=>[
			'SQL' => 'update _sys_col set tableId = ?,name = ?,type = ?,pk = ?,[order] = ?,css = ? where id = ?',
			'COLS' => ['tableId','name','type','pk','order','css','id'],
			'TYPE' => [  true , false , true, true , true , false , true ],
		],
		'upd_sys_relation'=>[
			'SQL' => 'update _sys_relation set pTable = ?, cTable = ? where id = ?',
			'COLS' => ['pTable','cTable','id'],
			'TYPE' => [  true , true, true ],
		],
	//挿入
		'ins_sys_sort'=>[
			'SQL' => 'insert into _sys_sort( name , sortStr ) values ( ? , ? )',
			'COLS' => [ 'name','sortStr' ],
			'TYPE' => [ false , false ],
		],
		//_sys_tbl
		'ins_sys_tbl'=>[
			'SQL' => 'insert into _sys_tbl ( name , [order] ) values ( ? , ? )',
			'COLS' => ['name','order' ],
			'TYPE' => [ false , true ],
			'INSERT_WHERE' => 'where name = ? and [order] = ?',
		],
		//_sys_col
		'ins_sys_col'=>[
			'SQL' => 'insert into _sys_col ( tableId , name , type , pk , [order] , css ) values ( ? , ?  , ? , ? , ? , ? )',
			'COLS' => ['tableId','name','type','pk','order','css'],
			'TYPE' => [ true , false , true, true , true , false ],
			'INSERT_WHERE' => 'where tableId = ? and name = ? and type = ? and pk = ? and [order] = ? and css = ?',
		],
		//_sys_option
		'ins_sys_option'=>[
			'SQL' => 'insert into _sys_option ( tableId,colId,value,[order] ) values ( ? , ? , ? , ? )',
			'COLS' => [ 'tableId','colId','value','order'],
			'TYPE' => [ true , true , false , true ],
		],
		'ins_sys_auto_num'=>[
			'SQL' => 'insert into _sys_auto_num ( prefix,suffix,length,zeroPad ) values ( ? , ?  , ? , ? )',
			'COLS' => ['prefix','suffix','length','zeroPad'],
			'TYPE' => [ false , false , true, true ],
		],
		'ins_sys_unique'=>[
			'SQL' => 'insert into _sys_unique ( [Table] ) values ( ? )',
			'COLS' => ['Table'],
			'TYPE' => [ true ],
		],
		'ins_sys_unique_col'=>[
			'SQL' => 'insert into _sys_unique_col ( uId,col ) values ( ? , ? )',
			'COLS' => ['uId','col'],
			'TYPE' => [ true, true ],
		],
		'ins_sys_relation'=>[
			'SQL' => 'insert into _sys_relation ( cTable,pTable ) values ( ? , ? )',
			'COLS' => ['cTable','pTable'],
			'TYPE' => [ true , true ],
		],
		'ins_sys_relation_col'=>[
			'SQL' => 'insert into _sys_relation_col ( rId,cCol,pCol ) values ( ? , ? , ? )',
			'COLS' => ['rId','cCol','pCol'],
			'TYPE' => [ true, true, true ],
		],


	];
	


	const SQL_WHERE = [
		'sys_tbl_tid' => ' where t.id = ?',
		'sys_col_cid' => ' where c.id = ?',
		'sys_col_tid' => ' where c.tableId = ? ',
		'sys_auto_num_tid' => ' where exists(select * from _sys_tbl where autoId = _sys_auto_num.id and _sys_tbl.id = ? ) ',
	];



	const DDL=[
		'DROP_INDEX'=>'drop index if exists $',
		'CRATE_INDEX'=>'create unique index $name$ on $tname$( ___cols___ )',
		'DROP_TABLE'=>'drop table $',
		'ALTER_TABLE'=>'alter table ___current___ rename to ___new___',
		'CREATE_TABLE'=>'create table ___name___ ( ___cols___ )',
		'CREATE_TALBE_PK'=>',primary key( ___KEYS___ )',
	];
	public static function getColListSql( $tname ){
		return mb_ereg_replace('\?', "'". \SQLite3::escapeString( $tname )."'" , self::COL_LIST);
	}
	public static function getUniqueIndexName( $uid ){
		return mb_ereg_replace('\$', $uid , self::UNIQUE_INDEX_NAME);
	}
	public static function getOnlyIdParam( $id ){
		return [1=>['value' => $id ,'type' => \PDO::PARAM_INT ]];
	}
	//sqlDataを元に$param配列をCOLSのキーで探し、sql用パラメータ化させる。
	//aryKeyChangerが存在する場合、aryKeyChangerのキーに合致するCOLSのキーは、
	//aryKeyChangerの要素に置き換えて、paramをチェックする。
	//paramに必要なキーが無い場合、null値と見なす。
	//paramが配列ではない場合、nullを返す。
	public static function getMultiParam( $param , $sqlData , $aryKeyChanger = [] ){
		if( ! is_array( $param ) ){
			return null;
		}
		$ret =[];
		$cols = $sqlData['COLS'];
		if( is_array( $aryKeyChanger ) && count( $aryKeyChanger ) > 0 ){
			foreach( $cols as $key => $value ){
				if( array_key_exists( $value , $aryKeyChanger ) ){
					$cols[ $key ] = $aryKeyChanger[ $value ];
				};
			};
		}
		$type = $sqlData['TYPE'];
		foreach( $cols as $key => $col ){
			$ret[ $key + 1 ] = ['value' => $param[ $col ] ,'type' => ( $type[$key] ? \PDO::PARAM_INT : \PDO::PARAM_STR ) ];
		}
		return $ret;
	}
	public static function createUserTParam( $value , $innerType ){
		switch($innerType){
			case 'Integer' :
			case 'integer' :
			case 'INTEGER' :
				if($value == '' || ! is_numeric($value) ){
					$value = null;
					$type = \PDO::PARAM_NULL;
				}else{
//					$value = floatval( $value );
					$type = \PDO::PARAM_STR;
				}
				break;
			case 'Real' :
			case 'real' :
			case 'REAL' :
				if($value == '' || ! is_numeric($value) ){
					$value = null;
					$type = \PDO::PARAM_NULL;
				}else{
					$type = \PDO::PARAM_STR;
				}
				break;
			case 'Text' :
			case 'text' :
			case 'TEXT' :
				if($value === null ){
					$type = \PDO::PARAM_NULL;
				}else{
					$type = \PDO::PARAM_STR;
				}
				break;
		}
		return ['value' => $value , 'type'=>$type];
	}
	public static function createSelectCriteria( $colName , $Mode , $Not , $aryCriteria , $colInnerType ){
		errLog(
		'createSelectCriteria Called'."\t".
		'colName:'.$colName."\t".
		'Mode:'.$Mode."\t".
		'Not:'.$Not."\t".
		'aryCriteria:'.json_encode($aryCriteria)."\t".
		'colInnerType:'.$colInnerType
		 , true );
		$ret = ['CRTR'=>' and ','PRM'=>[]];
		if( $Not ){
			$ret['CRTR'] .= 'not ';
		}
		switch($Mode){
		case '3'://range
			$min = $aryCriteria[0];
			$max = $aryCriteria[1];
			if( $min === '' && $max === '' ){
				$ret['CRTR'] .= '1 ';
			}else if( $min === '' ){
				$ret['CRTR'] .= $colName.' <= ? ';
				$ret['PRM'][] = self::createUserTParam( $max , $colInnerType );
			}else if( $max === '' ){
				$ret['CRTR'] .= $colName.' >= ? ';
				$ret['PRM'][] = self::createUserTParam( $min , $colInnerType );
			}else{
				$ret['CRTR'] .= $colName.' between ? and ? ';
				$ret['PRM'][] = self::createUserTParam( $min , $colInnerType );
				$ret['PRM'][] = self::createUserTParam( $max , $colInnerType );
			}

			break;
		case '4'://select
		case '5'://bool
			$ret['CRTR'] .= $colName.' = ? ';
			$ret['PRM'][] = self::createUserTParam( $aryCriteria[0] , $colInnerType );
			break;
		case '1'://match
		case '2'://like
		case '6'://glob
			$crCore = '';
			switch($Mode){
				case '1'://match
					$crCore = $colName.' = ? ' ;
					break;
				case '2'://like
					$crCore = $colName.' like ? ';
					break;
				case '6'://glob
					$crCore = $colName.' glob ? ';
					break;
			}
			
			if( count($aryCriteria) == 1 ){
				$ret['CRTR'] .= $crCore;
				$ret['PRM'][] = self::createUserTParam( $aryCriteria[0] , $colInnerType );
			}else{
				$ret['CRTR'] .= '( ';
				foreach( $aryCriteria as $criteria ){
					$ret['CRTR'] .= $crCore.'or ';
					$ret['PRM'][] = self::createUserTParam( $criteria , $colInnerType );
				}
				$ret['CRTR'] .= '0 ) ';
			}
			break;
		case '7'://isNull
			$ret['CRTR'] .= $colName.' is Null ';
			break;
		}
		return $ret;
	}
};
class RES{
	const RES_ERR_BASE = ['error'=>'','discription'=>''];

	const RES_I = ['mode'=>'I'];

	const RES_F = self::RES_ERR_BASE + ["mode" => 'F' ];
	const RES_D = self::RES_ERR_BASE + ["mode" => 'D' ];
	const RES_T = self::RES_ERR_BASE + ["mode" => 'T' ];
	const RES_O = self::RES_ERR_BASE + ["mode" => 'O' ];
	const RES_E = self::RES_ERR_BASE + ["mode" => 'E' ];
	const RES_W = self::RES_ERR_BASE + ["mode" => 'W' ];

	const RES_SYSERRORS = [
		'SYS_F_NOT_FOUND' =>
			['error'=>'SYSTEM FILE NOT FOUND'] + self::RES_F ,
		'SYS_F_CONSISTENT' => 
			['error'=>'SYSTEM FILE CONSISTENT ERROR'] + self::RES_F ,
		'SYS_T_CONSISTENT' =>
			['error'=>'SYSTEM TABLE CONSISTENT ERROR'] + self::RES_D ,
		'USR_DB_ID_NOT_FOUND' =>
			['error'=>'DATABASE NOT FOUND'] + self::RES_D ,
		'USR_DB_F_NOT_FOUND' =>
			['error'=>'DATABASE FILE NOT FOUND','discription'=>'database name: $'] + self::RES_D ,
		'USR_T_ID_NOT_FOUND' =>
			['error'=>'TABLE DATA NOT FOUND'] + self::RES_T ,
		'USR_T_NOT_FOUND' =>
			['error'=>'TABLE NOT FOUND','discription'=>'table name: $'] + self::RES_T ,
		'USR_C_ID_NOT_FOUND' =>
			['error'=>'COLUMN DATA NOT FOUND'] + self::RES_O ,
		'USR_C_NOT_FOUND' =>
			['error'=>'COLUMN NOT FOUND','discription'=>'column name: $'] + self::RES_O ,
		'USR_UQ_ID_NOT_FOUND' =>
			['error'=>'UNIQUE CONSTRAINT DATA NOT FOUND'] + self::RES_O ,
		'USR_UQ_NOT_FOUND' =>
			['error'=>'UNIQUE CONSTRAINT NOT FOUND'] + self::RES_O ,
		'SYS_T_IO_ERROR' =>
			['error'=>'SYSTEM TABLE INPUT ERROR'] + self::RES_E ,
		'ILLEGAL_PARAM' =>
			['error'=>'ILLEGAL PARAMETER'] + self::RES_E ,
		'USE_DB_CANNOT_CONNECT' =>
			['error'=>'USER DB CANNOT CONNECT','discription'=>'database name: $'] + self::RES_E ,
		'USER_TBL_CANOT_CONV' =>
			['error'=>'Table convert error','discription'=>''] + self::RES_E ,
		'USER_TBL_CANOT_SELECT' =>
			['error'=>'select error','discription'=>''] + self::RES_E ,
		'AUTO_NUMBER_OVER_FLOW' =>
			['error'=>'Auto number length over flow','discription'=>''] + self::RES_W ,
		'USER_TBL_CANOT_INSERT' =>
			['error'=>'Insert error','discription'=>''] + self::RES_W ,
		'USER_TBL_CANOT_UPDATE' =>
			['error'=>'Update error','discription'=>''] + self::RES_W ,
		'USER_TBL_CANOT_DELETE' =>
			['error'=>'Delete error','discription'=>''] + self::RES_W ,
		'USER_TBL_CANOT_EXPORT' =>
			['error'=>'Table export error','discription'=>''] + self::RES_W ,
		'USER_TBL_CANOT_IMPORT' =>
			['error'=>'Table import error','discription'=>''] + self::RES_W ,
		'JSON_CONVERT_ERROR' =>
			['error'=>'cannot put json','discription'=>''] + self::RES_W ,


	];

};
