_conn = @pg_connect("host=".$host." port=".$port." dbname=".$database." user=".$user." password=".$password);
//DEBUG_S(-3,'conn'.$names." ",$params);
if (!is_resource($this->_conn)) {
$this->_set_error('CREATE CONNECTION FAILED');
return;
}
//if (false === mysql_select_db($database, $this->_conn)) {
// $this->_set_error('SELECT DATABASE FAILED');
// return;
//}
if ($names != '') {
$this->query("SET CLIENT_ENCODING TO '".$names."';");
}
}
function getConnection() {
return $this->_conn;
}
function getVersion($version) {
if (!$this->_version) {
//$sql = "SHOW VARIABLES LIKE 'version';";
$this->_version=pg_version($this->_conn);
//if ($r = $this->fetch($res)) {
// // [Variable_name] => version, [Value] => 4.0.26-log
// $this->_version = $r->Value;
//}
}
return $this->_version;
}
/**
* Wykonuje podane zapytanie i zwraca wynik mysql_query().
*/
function query( $query, $msg = 'Query ERROR.' ) {
$null = null;
if (!$this->_conn) { return $null; }
$res = pg_query($this->_conn,$query);
if (!$res) {
DEBUG_S(-3,'error ',array($query),__FILE__,__FUNCTION__,__LINE__);
$this->_set_error('SQL QUERY FAILED: '.pg_result_error($this->_conn)."(".$query.")".pg_last_error($this->_conn));
return $null;
}
return $res;
}
function fetch( $res ) {
$ret = null;
if ($res) $ret = pg_fetch_object( $res );
return $ret;
}
function fetch_row( $res ) {
$ret = null;
if ($res) $ret = pg_fetch_row( $res );
return $ret;
}
function fetch_assoc( $res ) {
$ret = null;
if ($res) $ret = pg_fetch_assoc( $res );
return $ret;
}
function fetch_array($res) {
$ret = null;
if ($res) $ret = pg_fetch_array($res);
return $ret;
}
function count( $res ) {
return pg_num_rows( $res );
}
function num_rows( $res ) {
return pg_num_rows( $res );
}
function insert_id() {
$sql="select lastval();";
$res = $this->query($sql);
$last=$this->fetch_array($res);
return $last[0];
//return fetch_row( $res ); //TODO TESTING!!!
//die('TEST THIS FUNCTION!!!');
//return mysql_insert_id( $this->_conn );
}
function show_tables($table=null) {
$sql="SELECT table_name as \"Tables_in_".$this->getDatabaseName()."\", table_type as \"Table_type\" FROM information_schema.tables where table_schema NOT IN ('pg_catalog', 'information_schema'); ";
if(!empty($table)) $sql="SELECT table_name as \"Tables_in_".$this->getDatabaseName()."\", table_type as \"Table_type\" FROM information_schema.tables where table_schema NOT IN ('pg_catalog', 'information_schema') and table_name='".$table."' ";
DEBUG_S(-3,$sql,$sql,__FILE__,__FUNCTION__,__LINE__);
$res = $this->query($sql);
return $res;
}
function describe_table($table) {
$sql="select column_name as \"Field\" from INFORMATION_SCHEMA.COLUMNS where table_name = '".$table."'";
$sql="SELECT
f.attname AS \"Field\",
-- pg_catalog.format_type(f.atttypid,f.atttypmod) AS type,
CASE
when pg_catalog.format_type(f.atttypid,f.atttypmod) like 'double precision' then 'double(8,2)'
when pg_catalog.format_type(f.atttypid,f.atttypmod) like 'numeric' then 'double(8,2)'
when pg_catalog.format_type(f.atttypid,f.atttypmod) like 'smallint' then 'int(11)'
when pg_catalog.format_type(f.atttypid,f.atttypmod) like 'bigint' then 'int(11)'
when pg_catalog.format_type(f.atttypid,f.atttypmod) like 'integer' then
( select cast(replace(pg_catalog.format_type(f.atttypid,f.atttypmod), 'integer', 'int(11)') as char(255)) )
when pg_catalog.format_type(f.atttypid,f.atttypmod) like '%character varying%' then
( select cast(replace(pg_catalog.format_type(f.atttypid,f.atttypmod), 'character varying', 'varchar') as char(255)) )
when pg_catalog.format_type(f.atttypid,f.atttypmod) like 'character(%' then
( select cast(replace(pg_catalog.format_type(f.atttypid,f.atttypmod), 'character', 'varchar') as char(255)) )
when pg_catalog.format_type(f.atttypid,f.atttypmod) like 'timestamp without time zone' then 'datetime'
when pg_catalog.format_type(f.atttypid,f.atttypmod) like 'date' then 'date'
when pg_catalog.format_type(f.atttypid,f.atttypmod) like 'text' then 'text'
when pg_catalog.format_type(f.atttypid,f.atttypmod) like 'real' then 'double'
when pg_catalog.format_type(f.atttypid,f.atttypmod) like 'geometry(%' then
( select cast( split_part( lower(replace(replace(pg_catalog.format_type(f.atttypid,f.atttypmod), 'geometry(', ''),')','')) , ',',1) as char(255)) )
else ( select cast( 'enum('''||string_agg(enumlabel,''',''') || ''')' as text)
FROM pg_enum e
JOIN pg_type t ON e.enumtypid = t.oid
WHERE t.typname = replace(pg_catalog.format_type(f.atttypid,f.atttypmod ),'\"','' ) )
END as \"Type\" ,
CASE f.attnotnull
WHEN 't' THEN 'NO'
WHEN 'f' THEN 'YES'
END AS \"Null\",
CASE
WHEN p.contype='p' THEN 'PRI'
ELSE ''
END AS Key,
-- CASE
-- WHEN p.contype = 'u' THEN 't'
-- ELSE 'f'
-- END AS uniquekey,
-- CASE
-- WHEN p.contype = 'f' THEN g.relname
-- END AS foreignkey,
-- CASE
-- WHEN p.contype = 'f' THEN p.confkey
-- END AS foreignkey_fieldnum,
-- CASE
-- WHEN p.contype = 'f' THEN g.relname
-- END AS foreignkey,
-- CASE
-- WHEN p.contype = 'f' THEN p.conkey
-- END AS foreignkey_connnum,
CASE
WHEN f.atthasdef = 'n' THEN ''
WHEN f.atthasdef = 't' AND d.adsrc like 'nextval(%' THEN ''
WHEN f.atthasdef = 't' THEN d.adsrc
END AS default,
CASE
WHEN f.atthasdef = 't' AND d.adsrc like 'nextval(%' THEN 'auto_increment'
END AS Extra
FROM pg_attribute f
JOIN pg_class c ON c.oid = f.attrelid
JOIN pg_type t ON t.oid = f.atttypid
LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)
LEFT JOIN pg_class AS g ON p.confrelid = g.oid
WHERE c.relkind = 'r'::char
AND n.nspname = 'public' -- Replace with Schema name
AND c.relname = '".$table."' -- Replace with table name
AND f.attnum > 0
-- ORDER BY number
;
";
// echo "".$sql."
";
$result = $this->query($sql) or self::die1('\nProblem z '.$sql);
DEBUG_S(-3,'describe table '.$table,array($sql,$result,$this->num_rows),__FILE__,__FUNCTION__,__LINE__);
return $result;
}
function describe_table_value($table) {
$res = self::describe_table($table);
while($h=self::fetch($res)) {
$result[$h->Field]=$h;
}
return $result;
}
function show_index($table,$only_primary_flag=false) {
$sql="select table_name as \"Table\"
,case string_agg( cast( indisunique as char(255) ) ,'')
when 'true' then '0'
else '1'
END as \"Non_unique\"
, CASE string_agg( cast( indisprimary as char(255) ) ,'')
when 'true' then 'PRIMARY'
else column_name
END as \"Key_name\"
, CASE string_agg( cast( indisprimary as char(255) ) ,'')
when 'true' then 'PRIMARY'
-- else cast(string_agg(index_name) as char(255))
END
,column_name as \"Column_name\"
from (
select
t.relname as table_name,
i.relname as index_name,
a.attname as column_name,
ix.indnatts as indnatts,
unnest(ix.indkey) as unn,
a.attnum,
ix.indisunique as indisunique,
ix.indisprimary as indisprimary
,ix.indkey
,a.attrelid
,i.oid
from
pg_class t,
pg_class i,
pg_index ix,
pg_attribute a
where
t.oid = ix.indrelid
and i.oid = ix.indexrelid
and a.attrelid = t.oid
and a.attnum = ANY(ix.indkey)
and t.relkind = 'r'
-- and t.relnamespace = 10
and t.relname = '".$table."'
";
if($only_primary_flag) $sql.=" and ix.indisprimary is true ";
$sql.="
order by
t.relname,
i.relname
, generate_subscripts(ix.indkey,1)) sb
where unn = attnum
group by table_name,column_name
-- group by index_name
";
DEBUG_S(-3,'Show index',array($sql),__FILE__,__FUNCTION__,__LINE__);
$res = $this->query($sql) or self::die1('\nProblem z '.$sql);
return $res;
}
function show_index_value($table) {
$res=self::show_index($table,true);
while($h=self::fetch($res)) {
return $h->Column_name;
}
}
function die1($txt) {
DEBUG_S(-3,'Died',$txt,__FILE__,__FUNCTION__,__LINE__);
die();
}
function show_triggers($table) {
$sql="SELECT
trigger_name as \"Trigger\"
,event_manipulation as \"Event\"
,event_object_table as \"Table\"
,action_statement as \"Statement\"
,action_timing as \"Timing\"
FROM information_schema.triggers
WHERE event_object_table='".$table."'
ORDER BY event_object_table,event_manipulation";
DEBUG_S(-3,'Show triggers',array($sql),__FILE__,__FUNCTION__,__LINE__);
$res = $this->query($sql) or self::die1('\nProblem z '.$sql);
return $res;
}
function show_create_trigger($table,$trigger) {
$sql="SELECT
trigger_name as \"Trigger\"
,action_statement as \"SQL Original Statement\"
-- ,action_timing as \"Timing\"
-- character_set_client=utf8
-- collation_connection=utf8_general_ci
-- Database Collation=latin2_general_ci
FROM information_schema.triggers
WHERE trigger_name='".$trigger."' and event_object_table='".$table."'
ORDER BY event_object_table,event_manipulation";
DEBUG_S(-3,'Show triggers',array($sql),__FILE__,__FUNCTION__,__LINE__);
$res = $this->query($sql) or self::die1('\nProblem z '.$sql);
return $res;
}
function from_mysql_struct($type) {
if(strstr($type, 'int(') ) return 'integer';
else if(strstr($type, 'double') ) return 'double precision';
else if(strstr($type, 'varchar(') ) return $type;
else if(strstr($type, 'char(') ) return $type;
else if(strstr($type, 'enum(') ) return $type; //todo
else if(strstr($type, 'text') ) return $type;
else if(strstr($type, 'datetime') ) return 'TIMESTAMP';
else if(strstr($type, 'date') ) return 'date';
else if(strstr($type, 'polygon') ) return 'geometry(Polygon)';
else if(strstr($type, 'linestring') ) return 'geometry(linestring)';
else if(strstr($type, 'point') ) return 'geometry(point)';
else if(strstr($type, 'timestamp') ) return 'timestamp';
else die('(TODO)Unknowns transl struct:'.$type);
}
//used mainly to translate enum values
function alter_add_struct($table,$column,$type) {
if(strstr($type, "enum('")) {
$sql="DROP TYPE IF EXISTS \"".$table."_".$column."\"";
self::query($sql) or die1($sql);
$sql="CREATE TYPE \"".$table."_".$column."\" AS ".$type;
self::query($sql) or die1($sql);
$sql='alter table "'.$table.'" add "'.$column.'" "'.$table.'_'.$column.'"';
self::query($sql) or die1($sql);
} else if(strstr($type, "datetime")) {
$sql='alter table "'.$table.'" add "'.$column.'" timestamp without time zone ';
self::query($sql) or die1($sql);
} else {
$sql='alter table "'.$table.'" add "'.$column.'" '.$type;
self::query($sql) or die1($sql);
}
if(strstr($type, "linestring'")) {
$sql="SELECT UpdateGeometrySRID('".$table."','".$column."',4326);";
self::query($sql) or die1($sql);
} else if(strstr($type, "polygon'")) {
$sql="SELECT UpdateGeometrySRID('".$table."','".$column."',4326);";
self::query($sql) or die1($sql);
} else if(strstr($type, "multipoint'")) {
$sql="SELECT UpdateGeometrySRID('".$table."','".$column."',4326);";
self::query($sql) or die1($sql);
}
}
/** Returns formated array to parse it to SQL (especialy to get GIS data to keep as WKT)
*
*/
function GET_DB_OBJ_cols($table) {
$structure=self::describe_table_value($table);
foreach($structure as $Field=>$Struct) {
//echo $Struct->Type;
if((strstr($Struct->Type,"multipoint") )||strstr($Struct->Type,"linestring") ||strstr($Struct->Type,"polygon"))
$col[]=' ST_AsText("'.$Field.'") as "'.$Field.'"';
else $col[]='"'.$Field.'"';
}
return $col;
}
function get_by_data_column($table,$date_column,$timestamp,$now,$remote_key) {
$structure=self::describe_table_value($table);
if(strlen($timestamp)==0) $timestamp='0';
DEBUG_S(-3,'Struct',$structure,__FILE__,__FUNCTION__,__LINE__);
$col=self::GET_DB_OBJ_cols($table);
$sql='Select '.implode(',', $col).' from "'.$table.'" where extract(epoch from "'.$date_column.'") > '.$timestamp.' and extract(epoch from "'.$date_column.'") < '.$now;
//if(empty($timestamp))
$sql.=' or "'.$date_column.'" is null';
$sql.=' or "'.$remote_key.'" < 0 ';
// $sql.=' or "'.$remote_key.'"=\'\'';
DEBUG_S(-3,'biore dane ',$sql,__FILE__,__FUNCTION__,__LINE__);
$res=self::query($sql) or die1($sql);
return $res;
}
function get_current_time() {
self::query("set timezone to 'UTC'");
$sql=" select extract(epoch from now() at time zone 'utc') as time"; // bo nie dziala extract( epoch from CURRENT_TIMESTAMP at time zone 'UTC')
$res=self::query($sql);
while($h=self::fetch($res)) {
return $h->time;
}
}
function get_current_timestamp() {
self::query("set timezone to 'UTC'"); //TODO jakas glupota nie dziala extract( epoch from CURRENT_TIMESTAMP at time zone 'UTC')
$sql=" select now() at time zone 'utc' as time ";
$res=self::query($sql);
while($h=self::fetch($res)) {
return $h->time;
}
}
function set_last_sync_date($table,$now,$remote_db_id,$local_db_id,$timestamp_column='A_RECORD_UPDATE_DATE') {
$sql="insert into \"_SYNC_DATABASE_TABLE_STATE\" ( \"TABLE_NAME\", \"LAST_SYNCED\" , \"DATABASE_SYNCED_ID\" , \"DATABASE_LOCAL_ID\" )
values ('".$table."' , TIMESTAMP WITH TIME ZONE 'epoch' + ".$now." * INTERVAL '1 second' , '".$remote_db_id."' , '".$local_db_id."')";
DEBUG_S(-3,'update sync date',$sql,__FILE__,__FUNCTION__,__LINE__);
self::query($sql);
$sql="update _SYNC_DATABASE_TABLE_STATE set LAST_SYNCED=from_unixtime('".$now."') where TABLE_NAME='".$table."'
and DATABASE_SYNCED_ID='".$remote_db_id."' and DATABASE_LOCAL_ID='".$local_db_id."' ";
$sql_c="update \"".$table."\" set \"".$timestamp_column."\"=TIMESTAMP WITH TIME ZONE 'epoch' + ".$now." * INTERVAL '1 second' where \"".$timestamp_column."\" is null ";
self::query($sql_c) or die('315Problem z '.$sql_c);
DEBUG_S(-3,'update sync date',array($sql,$sql_c),__FILE__,__FUNCTION__,__LINE__);
}
function get_last_sync_date($table,$remote_db_id,$local_db_id) {
$sql="select \"TABLE_NAME\", extract(epoch from \"LAST_SYNCED\" at time zone 'utc') as \"LAST_SYNCED\" from \"_SYNC_DATABASE_TABLE_STATE\"
where \"DATABASE_SYNCED_ID\"='".$remote_db_id."' and \"DATABASE_LOCAL_ID\"='".$local_db_id."' and \"TABLE_NAME\"='".$table."'
order by \"ID\" desc limit 1
";
$res=self::query($sql);
DEBUG_S(-3,'Get date',$sql,__FILE__,__FUNCTION__,__LINE__);
while($h=self::fetch($res)) {
return $h->LAST_SYNCED;
}
return 0;
}
/** Use it before pasting obj to UPDATE_OBJ or ADD_NEW_OBJ to maintain GIS data especially
*
*/
function MAKE_DB_OBJ($table,$obj) {
$structure=self::describe_table_value($table); //todo to cache optimize
foreach(get_object_vars($obj) as $k=>$v) {
if(trim($structure[$k]->Type)=='multipoint') {
if(empty($v)) $v='null';
else $v = "ST_GeomFromText('".$v."',4326)";
} else if(trim($structure[$k]->Type)=='linestring') {
if(empty($v)) $v='null';
else $v = "ST_GeomFromText('".$v."',4326)";
} else if(trim($structure[$k]->Type)=='polygon') {
if(empty($v)) $v='null';
else $v = "ST_GeomFromText('".$v."',4326)";
} else if(trim($structure[$k]->Type)=='multipolygon') {
if(empty($v)) $v='null';
else $v = "ST_GeomFromText('".$v."',4326)";
} else if(trim($structure[$k]->Type)=='datetime') {
if(empty($v)) continue;
if($v=='0000-00-00 00:00:00') continue;
} else if(strstr($structure[$k]->Type,'double')) {
if(empty($v)) continue;
} else if(trim($structure[$k]->Type)=='date') {
if(empty($v)) continue;
if($v=='0000-00-00') continue;
} else if(strstr(trim($structure[$k]->Type),'enum(')) {
if(empty($v)) continue;
}
$obj_ret->$k=$v;
}
DEBUG_S(-3,'objs',array($obj,$obj_ret),__FILE__,__FUNCTION__,__LINE__);
return $obj_ret;
}
function STRUCTURE_GENERATE($TABLES,$null=null,$TABLES_STRUCTURE_DATA,$flags=array()){
DEBUG_S(-3,'flags',$flags,__FILE__,__FUNCTION__,__LINE__);
//global $this;
//! try to create info for functions and procedures -> ['__SCHEMA_FUNCTIONS__ARRAY__']
/*
$sql="show function status where Db='".$this->getDatabaseName()."';";
DEBUG_S(-3,'sql function detail :: ',$sql,__FILE__,__FUNCTION__,__LINE__) ;
$result=$this->query($sql) or self::die1('\nProblem z '.$sql);
while($h=mysql_fetch_object($result)) {
$sql_="show create function `{$h->Name}`" ;
DEBUG_S(-3,'sql function detail :: ',$sql_,__FILE__,__FUNCTION__,__LINE__) ;
$result_=$this->query($sql_) or self::die1('\nProblem z '.$sql);
while($h_=mysql_fetch_object($result_)) {
$TABLES_IMPORT['__SCHEMA_FUNCTIONS__ARRAY__']['FUNCTION'][$h->Name]=$h_;
}
}
*/
/*
$sql="show PROCEDURE status where Db='".$this->getDatabaseName()."';";
DEBUG_S(-3,'sql function detail :: ',$sql_,__FILE__,__FUNCTION__,__LINE__) ;
$result=$this->query($sql) or self::die1('\nProblem z '.$sql);
while($h=mysql_fetch_object($result)) {
$sql_="show create PROCEDURE `{$h->Name}`" ;
DEBUG_S(-3,'sql PROCEDURE detail :: ',$sql_,__FILE__,__FUNCTION__,__LINE__) ;
$result_=$this->query($sql_) or self::die1('\nProblem z '.$sql);
while($h_=mysql_fetch_object($result_)) {
$TABLES_IMPORT['__SCHEMA_FUNCTIONS__ARRAY__']['PROCEDURE'][$h->Name]=$h_;
}
}
DEBUG_S(-3,'struktury funkcji: __SCHEMA_FUNCTIONS__ARRAY__ ',$TABLES_IMPORT['__SCHEMA_FUNCTIONS__ARRAY__'],__FILE__,__FUNCTION__,__LINE__) ;
//self::die1('stop function during dev.');
*/
foreach($TABLES as $TABLE ){
//$sql="show tables like '".$TABLE."' ;" ;
DEBUG_S(-3,'//! Listing possible tables to write to config',$TABLE,__FILE__,__FUNCTION__,__LINE__);
$result=$this->show_tables($TABLE) or self::die1('\nProblem z '.$sql);
if($this->num_rows($result)==0) {
// nie ma tabeli - trzeba zapisac
DEBUG_S(-3," BRAK TABELI ".$TABLE,null,__FILE__,__FUNCTION__,__LINE__);
//self::die1();
if(isset($TABLES_IMPORT[$TABLE])) {
DEBUG_S(-3," Table already imported - problem in twice declared table ".$TABLE,null,__FILE__,__FUNCTION__,__LINE__);
self::die1();
}
} else {
DEBUG_S(-3,'We found rows'.$this->num_rows($result),$TABLE,__FILE__,__FUNCTION__,__LINE__);
//$sql="show full tables like '".$TABLE."' ;" ;
//$result=$this->query($sql) or self::die1('\nProblem z '.$sql);
$result=$this->show_tables($TABLE);
while($h=$this->fetch($result)) {
$TABLES_IMPORT[$TABLE]['Table_type']=$h->{'Table_type'};
DEBUG_S(-3,'//! Reading to conf if table is WIEV or not :sql/result',$h->{'Table_type'},__FILE__,__FUNCTION__,__LINE__);
}
$FLAG_CONTABLE=false;
if( ($TABLES_IMPORT[$TABLE]['Table_type']=='BASE TABLE')
|| ( in_array('with_view', $flags) && $TABLES_IMPORT[$TABLE]['Table_type']=='VIEW' )
) {
$FLAG_CONTABLE=true;
//$sql=" describe `".$TABLE."` ;" ;
//echo "\n ".$sql;
// $result=$this->query($sql) or self::die1('\nProblem z '.$sql);
$result=$this->describe_table($TABLE);
while($h=$this->fetch($result)) {
//echo "found ";
$TABLES_IMPORT[$TABLE]['FIELDS'][]=$h;
}
//$sql=" show index from `".$TABLE."` ;" ;
//$result=$this->query($sql) or self::die1('\nProblem z '.$sql);
$result=self::show_index($TABLE);
$TMP_KEYNAME=array();
while($h=self::fetch($result)) {
if(isset($h->Column_name)) { $h->Column_name="`".$h->Column_name."`";
if(!isset($TMP_KEYNAME[$h->Key_name])) $TMP_KEYNAME[$h->Key_name]=$h->Column_name;
else $TMP_KEYNAME[$h->Key_name].=",".$h->Column_name;
}
//if(isset($TABLES_IMPORT[$TABLE]['INDEX'][$h->Key_name])) {
// $TABLES_IMPORT[$TABLE]['INDEX'][$h->Key_name]->Column_name.=", ".$h->Column_name." ";
//} else {
//}
$TABLES_IMPORT[$TABLE]['INDEX'][$h->Key_name]=$h;
$TABLES_IMPORT[$TABLE]['INDEX'][$h->Key_name]->Column_name=$TMP_KEYNAME[$h->Key_name];
}
if(in_array($TABLE,$TABLES_STRUCTURE_DATA)) { //bedziemy dodawac dane tej tabeli
$sql=" select * from `".$TABLE."` ;" ;
$result=$this->query($sql) or self::die1('\nProblem z '.$sql);
while($h=self::fetch($result)) {
$TABLES_IMPORT[$TABLE]['VALUES'][]=$h;
}
}
DEBUG_S(-3,"Dane tabeli KOMORKI ".$TABLE." ",$TABLES_IMPORT[$TABLE]['FIELDS'],__FILE__, __FUNCTION__, __LINE__);
DEBUG_S(-3,"Dane tabeli INDEKSY ".$TABLE." ",$TABLES_IMPORT[$TABLE]['INDEX'],__FILE__, __FUNCTION__, __LINE__);
if(isset($TABLES_IMPORT[$TABLE]['VALUES'])) DEBUG_S(-3,"Dane tabeli VALUES ".$TABLE." ",$TABLES_IMPORT[$TABLE]['VALUES'],__FILE__, __FUNCTION__, __LINE__);
//! reading triggers to conf -> $TABLES_IMPORT[$TABLE]['TRIGGERS']
//$sql=" show triggers like '".$TABLE."' ;" ;
//$result=$this->query($sql) or self::die1('\nProblem z '.$sql);
$result=self::show_triggers($TABLE);
while($h=self::fetch($result)) {
// $sql_="show create trigger {$h->Trigger} ;" ;
// echo $sql_;
// $result_=$this->query($sql_) or self::die1('\nProblem z '.$sql_);
$result_=self::show_create_trigger($TABLE,$h->Trigger);
while($h_=self::fetch($result_)) {
DEBUG_S(-3,"Dane tabeli h_ ".$TABLE." ",$h_,__FILE__, __FUNCTION__, __LINE__);
$TABLES_IMPORT[$TABLE]['TRIGGERS'][$h->Trigger]=$h_;
}
}
if(isset($TABLES_IMPORT[$TABLE]['TRIGGERS'])) DEBUG_S(-3,"Dane tabeli TRIGGERS ".$TABLE." ",$TABLES_IMPORT[$TABLE]['TRIGGERS'],__FILE__, __FUNCTION__, __LINE__);
}
if(($TABLES_IMPORT[$TABLE]['Table_type']=='VIEW')) {
$FLAG_CONTABLE=true;
$sql_="show create view `{$TABLE}` ;" ;
//echo $sql_;
$result_=$this->query($sql_) or self::die1('\nProblem z '.$sql_);
while($h_=self::fetch($result_)) {
DEBUG_S(-3,"//! trying to fetch create view fom".$TABLE." sql/res",array($sql_,$h_),__FILE__, __FUNCTION__, __LINE__);
$TABLES_IMPORT[$TABLE]['VIEW']=$h_;
}
} if(!$FLAG_CONTABLE) {
DEBUG_S(-3,"Unknown table type ! ".$TABLE." ".$TABLES_IMPORT[$TABLE]['Table_type'],$TABLES_IMPORT[$TABLE],__FILE__, __FUNCTION__, __LINE__);
self::die1();
}
}
}
//print_r($TABLES_IMPORT);
if(!function_exists('func_sort_tables_import')) {
function func_sort_tables_import($a,$b){
if($a['Table_type']==$b['Table_type']) return 0;
else if($a['Table_type']=='VIEW') return 1;
else return -1;
}
}
uasort($TABLES_IMPORT,'func_sort_tables_import');
DEBUG_S(-3,'Wygenerowane struktury (TABLES_IMPORT)',$TABLES_IMPORT,__FILE__,__FUNCTION__,__LINE__);
return($TABLES_IMPORT);
}
function STRUCTURE_GENERATE_PARSE($config_base_structure_unserialized,$options=array()) {
if(!empty($options['CONFIRM_SQL_UPDATE'])) $CONFIRM_SQL_UPDATE=1;
//budowa struktur w bazie
DEBUG_S(-3,'config_base_structure_unserialized',$config_base_structure_unserialized,__FILE__, __FUNCTION__, __LINE__) ;
foreach($config_base_structure_unserialized as $TABLE=>$ARR_TABLE) { //dla kazdej tabeli
set_time_limit(36000);
//! if($TABLE<>'_CRM_PROCES_USER_STATS_wiev') continue; //trigger development
//! try to sync __SCHEMA_FUNCTIONS__ARRAY__
// triggers sync disabled
/*
if(in_array('SKIP-PROCEDURE', $options)) {
DEBUG_S(-3,'SKIP-PROCEDURES option',null,__FILE__,__FUNCTION__,__LINE__);
continue;
}
if($TABLE=='__SCHEMA_FUNCTIONS__ARRAY__') {
DEBUG_S(-3,'Try to synchronize functions and procedures',$ARR_TABLE,__FILE__,__FUNCTION__,__LINE__);
//BEGIN PROCEDURE
$sql=" SHOW PROCEDURE STATUS; ;" ; //listujemy nasze procedury
DEBUG_S(-3,'Looking for PROCEDURE ',$sql,__FILE__,__FUNCTION__,__LINE__);
$result=$this->query($sql) or die('\nProblem z '.$sql);
while($h=mysql_fetch_object($result)) {
if($h->Db<>$this->getDatabaseName()) continue;
set_time_limit(36000);
$sql_="show create PROCEDURE {$h->Name} ;" ;
//echo $sql_;
$result_=$this->query($sql_) or die('\nProblem z '.$sql_);
while($h_=mysql_fetch_object($result_)) {
$FIELDS_CHECKED['FOUNDS'][$h->Name]=$h_;
if(!isset($ARR_TABLE['PROCEDURE'][$h->Name])) {
DEBUG_S(-3,'We have some more functions - we will not delete them:'.$h->Name,null,__FILE__,__FUNCTION__,__LINE__);
//$FIELDS_CHECKED['TO_DELETE'][$h->Name]=$h_;
}
}
}
DEBUG_S(-3,'Try to unserialize PROCEDURE and install to db:',$ARR_TABLE['PROCEDURE'],__FILE__,__FUNCTION__,__LINE__);
foreach($ARR_TABLE['PROCEDURE'] as $TRIGGER_INDEX=>$TRIGGER_OBJECT) {
if(!isset($FIELDS_CHECKED['FOUNDS'][$TRIGGER_INDEX])) { $FIELDS_CHECKED['TO_CREATE'][$TRIGGER_INDEX]=$TRIGGER_OBJECT;
DEBUG_S(-3,'We have object to create '.$TRIGGER_INDEX,$TRIGGER_OBJECT,__FILE__,__FUNCTION__,__LINE__);
}
}
//staramy sie zaktualizowac stare triggery, pod warunkiem, ze sie roznia
foreach($FIELDS_CHECKED['FOUNDS'] as $TRIGGER_INDEX=>$TRIGGER_OBJECT) {
DEBUG_S(-3,'OBJ/OBJ for '.$TRIGGER_INDEX,array($TRIGGER_OBJECT->{'Create Procedure'},$ARR_TABLE['PROCEDURE'][$TRIGGER_INDEX]->{'Create Procedure'}),__FILE__, __FUNCTION__, __LINE__);
if(isset($ARR_TABLE['PROCEDURE'][$TRIGGER_INDEX])) //we first try to find if this trigger exists or delete it
if($TRIGGER_OBJECT->{'Create Procedure'}<>$ARR_TABLE['PROCEDURE'][$TRIGGER_INDEX]->{'Create Procedure'} ) {
DEBUG_S(-3,"Data are different ({$TRIGGER_INDEX}). We add object to be created as new.",__FILE__, __FUNCTION__, __LINE__);
$FIELDS_CHECKED['TO_CREATE'][$TRIGGER_INDEX]=$ARR_TABLE['PROCEDURE'][$TRIGGER_INDEX];
$FIELDS_CHECKED['TO_DELETE'][$TRIGGER_INDEX]=true;
}
}
if(isset($FIELDS_CHECKED['TO_DELETE'])) {
DEBUG_S(-3,'We have some data to delete',$FIELDS_CHECKED['TO_DELETE'],__FILE__,__FUNCTION__,__LINE__);
foreach($FIELDS_CHECKED['TO_DELETE'] as $TRIGGER_INDEX=>$TRIGGER_OBJECT ) {
$sql="DROP PROCEDURE IF EXISTS `{$TRIGGER_INDEX}`; ";
DEBUG_S(-3,' sql to delete '.$TRIGGER_INDEX,$sql,__FILE__,__FUNCTION__,__LINE__);
if(!empty($CONFIRM_SQL_UPDATE)) $result=$this->query($sql) or DEBUG_S(-3,'Problem ze SQL:',$sql,__FILE__, __FUNCTION__, __LINE__) or die();
}
}
//tworzymy brakujace procedury
foreach ($FIELDS_CHECKED['TO_CREATE'] as $TRIGGER_INDEX=>$TRIGGER_OBJECT) {
$sql=$TRIGGER_OBJECT->{'Create Procedure'};
DEBUG_S(-3,'Try to add new '.$TRIGGER_INDEX,$sql,__FILE__,__FUNCTION__,__LINE__);
if(!empty($CONFIRM_SQL_UPDATE)) $result=$this->query($sql) or DEBUG_S(-3,'Problem ze SQL:',$sql,__FILE__, __FUNCTION__, __LINE__) or die();
}
unset($FIELDS_CHECKED);
//EOF PROCEDURE
//BEGIN FUNCTION
$sql=" SHOW FUNCTION STATUS ;" ; //listujemy nasze procedury
DEBUG_S(-3,'Looking for FUNCTION ',$sql,__FILE__,__FUNCTION__,__LINE__);
$result=$this->query($sql) or die('\nProblem z '.$sql);
while($h=mysql_fetch_object($result)) {
set_time_limit(36000);
$sql_="show create FUNCTION {$h->Name} ;" ;
//echo $sql_;
$result_=$this->query($sql_) or die('\nProblem z '.$sql_);
while($h_=mysql_fetch_object($result_)) {
$FIELDS_CHECKED['FOUNDS'][$h->Name]=$h_;
if(!isset($ARR_TABLE['FUNCTION'][$h->Name])) {
DEBUG_S(-3,'We have some more functions - we will not delete them:'.$h->Name,null,__FILE__,__FUNCTION__,__LINE__);
// $FIELDS_CHECKED['TO_DELETE'][$h->Name]=$h_;
}
}
}
DEBUG_S(-3,'Try to unserialize FUNCTION and install to db:',$ARR_TABLE['FUNCTION'],__FILE__,__FUNCTION__,__LINE__);
foreach($ARR_TABLE['FUNCTION'] as $TRIGGER_INDEX=>$TRIGGER_OBJECT) {
if(!isset($FIELDS_CHECKED['FOUNDS'][$TRIGGER_INDEX])) { $FIELDS_CHECKED['TO_CREATE'][$TRIGGER_INDEX]=$TRIGGER_OBJECT;
DEBUG_S(-3,'We have object to create '.$TRIGGER_INDEX,$TRIGGER_OBJECT,__FILE__,__FUNCTION__,__LINE__);
}
}
//staramy sie zaktualizowac stare triggery, pod warunkiem, ze sie roznia
foreach($FIELDS_CHECKED['FOUNDS'] as $TRIGGER_INDEX=>$TRIGGER_OBJECT) {
DEBUG_S(-3,'OBJ/OBJ for '.$TRIGGER_INDEX,array($TRIGGER_OBJECT->{'Create Function'},$ARR_TABLE['FUNCTION'][$TRIGGER_INDEX]->{'Create Function'}),__FILE__, __FUNCTION__, __LINE__);
if(isset($ARR_TABLE['FUNCTION'][$TRIGGER_INDEX])) //we first try to find if this trigger exists or delete it
if($TRIGGER_OBJECT->{'Create Function'}<>$ARR_TABLE['FUNCTION'][$TRIGGER_INDEX]->{'Create Function'} ) {
DEBUG_S(-3,"Data are different ({$TRIGGER_INDEX}). We add object to be created as new.",__FILE__, __FUNCTION__, __LINE__);
$FIELDS_CHECKED['TO_CREATE'][$TRIGGER_INDEX]=$ARR_TABLE['FUNCTION'][$TRIGGER_INDEX];
$FIELDS_CHECKED['TO_DELETE'][$TRIGGER_INDEX]=true;
}
}
if(isset($FIELDS_CHECKED['TO_DELETE'])) {
DEBUG_S(-3,'We have some data to delete',$FIELDS_CHECKED['TO_DELETE'],__FILE__,__FUNCTION__,__LINE__);
foreach($FIELDS_CHECKED['TO_DELETE'] as $TRIGGER_INDEX=>$TRIGGER_OBJECT ) {
$sql="DROP FUNCTION IF EXISTS `{$TRIGGER_INDEX}`; ";
DEBUG_S(-3,' sql to delete '.$TRIGGER_INDEX,$sql,__FILE__,__FUNCTION__,__LINE__);
if(!empty($CONFIRM_SQL_UPDATE)) $result=$this->query($sql) or DEBUG_S(-3,'Problem ze SQL:',$sql,__FILE__, __FUNCTION__, __LINE__) or die();
}
}
//tworzymy brakujace procedury
foreach ($FIELDS_CHECKED['TO_CREATE'] as $TRIGGER_INDEX=>$TRIGGER_OBJECT) {
$sql=$TRIGGER_OBJECT->{'Create Function'};
DEBUG_S(-3,'Try to add new '.$TRIGGER_INDEX,$sql,__FILE__,__FUNCTION__,__LINE__);
if(!empty($CONFIRM_SQL_UPDATE)) $result=$this->query($sql) or DEBUG_S(-3,'Problem ze SQL:',$sql,__FILE__, __FUNCTION__, __LINE__) or die();
}
unset($FIELDS_CHECKED);
//EOF FUNCTION
//die('app in devel');
continue; //no go to another table in loop
} //eof
*/
//! detect if src table is TABLE or VIEW
//foreach($TABLES as $TABLE ){
// $sql="show full tables like '".$TABLE."' ;" ;
DEBUG_S(-3,'SQL:'.$this->getDatabaseName(),$sql,__FILE__, __FUNCTION__, __LINE__) ;
// $result=$this->query($sql) or die('\nProblem z '.$sql);
$result=$this->show_tables($TABLE);
while($h=$this->fetch($result)) {
$FIELDS_CHECKED['Table_type']=$h->{'Table_type'};
}
DEBUG_S(-3,'FIELDS_CHECKED:',$FIELDS_CHECKED,__FILE__, __FUNCTION__, __LINE__) ;
if($this->count($result)==0) {
// nie ma tabeli - trzeba zapisac
if($ARR_TABLE['Table_type']=='BASE TABLE') {
//DEBUG_S(-3,"\n BRAK TABELI dodajemy : ".$TABLE."!!!",null,__FILE__,__FUNCTION__,__LINE__);
// $sql="create table ".$TABLE." ( ".$ARR_TABLE['FIELDS'][0]->Field." ".$ARR_TABLE['FIELDS'][0]->Type ; // ID int(11) NOT NULL AUTO_INCREMENT,
if(trim($ARR_TABLE['FIELDS'][0]->Extra)=='auto_increment') {
if(in_array('SKIP-PRIMARY-KEY', $options)) {
$sql="create table \"".$TABLE."\" ( \"id_".$options['getDB']."\" serial
, CONSTRAINT \"".$TABLE."_pkey\" PRIMARY KEY (\"id_".$options['getDB']."\")" ;
} else $sql="create table \"".$TABLE."\" ( \"".$ARR_TABLE['FIELDS'][0]->Field."\" serial " ; // ID int(11) NOT NULL AUTO_INCREMENT,
} else $sql="create table \"".$TABLE."\" ( \"".$ARR_TABLE['FIELDS'][0]->Field."\" ".$this->from_mysql_struct($ARR_TABLE['FIELDS'][0]->Type) ; // ID int(11) NOT NULL AUTO_INCREMENT,
// if($ARR_TABLE['FIELDS'][0]->Null=='NO') $sql.=" not null "; else $sql.=" null ";
//if($ARR_TABLE['FIELDS'][0]->Extra=='auto_increment') $sql.=" AUTO_INCREMENT "; else $sql.=" ";
//$sql.=" ) DEFAULT CHARSET=latin2 ";
$sql.=" ) ";
DEBUG_S(-3,"Brak tabeli: ".$TABLE." dodajemy sql:",$sql,__FILE__,__FUNCTION__,__LINE__) ;
//if(!empty($CONFIRM_SQL_UPDATE))
$this->query($sql) or die('\nProblem z CREATE '.$sql);
if($TABLE=='CRM_LISTA_ZASOBOW') {
$sql=" alter table \"".$TABLE."\" AUTO_INCREMENT=1000 ";
if(!empty($CONFIRM_SQL_UPDATE)) $this->query($sql) or die('\nProblem z AUTO INCREMENT '.$sql);
}
} else if($ARR_TABLE['Table_type']=='VIEW') {
$sql=$ARR_TABLE['VIEW']->{'Create View'};
DEBUG_S(-3,"Brak widoku: ".$TABLE." dodajemy sql:",$sql,__FILE__,__FUNCTION__,__LINE__) ;
if(!empty($CONFIRM_SQL_UPDATE)) $this->query($sql) or die('\nProblem ze '.$sql);
}
} else {
//try to detect if types are the same
if($ARR_TABLE['Table_type']<>$FIELDS_CHECKED['Table_type']) die('Todo: ERROR VIEW/TABLE different types ');
if($ARR_TABLE['Table_type']=='VIEW') {
if(isset($ARR_TABLE['VIEW'])) {
$sql_="show create view `{$TABLE}` ;" ;
echo $sql_;
$result_=$this->query($sql_) or die('\nProblem z '.$sql_);
while($h_=self::fetch($result_)) {
DEBUG_S(-3,"//! get current view info ".$TABLE." sql/res",array($sql_,$h_),__FILE__, __FUNCTION__, __LINE__);
$FIELDS_CHECKED['VIEW']=$h_;
}
//ereg_replace(, , )
if(strtolower($FIELDS_CHECKED['VIEW']->{'Create View'})<>strtolower($ARR_TABLE['VIEW']->{'Create View'})) {
$sql="drop view `{$TABLE}`";
if(!empty($CONFIRM_SQL_UPDATE)) $this->query($sql) or die('\nProblem ze '.$sql);
$sql_=$ARR_TABLE['VIEW']->{'Create View'};
DEBUG_S(-3,"//! Views are different for ".$TABLE." . Dropping old view and adding new (old/new/sql/sql):",array(strtolower($FIELDS_CHECKED['VIEW']->{'Create View'}),$ARR_TABLE['VIEW']->{'Create View'},$sql,$sql_,$h_),__FILE__, __FUNCTION__, __LINE__);
if(!empty($CONFIRM_SQL_UPDATE)) $this->query($sql_) or die('\nProblem ze '.$sql_);
}
}
}
}
//always create
if($ARR_TABLE['Table_type']=='BASE TABLE') {
set_time_limit(36000);
//$sql=" describe `".$TABLE."` ;" ;
//DEBUG_S(-3,'SQL:describe '.$TABLE,$sql,__FILE__, __FUNCTION__, __LINE__) ;
//$result=$this->query($sql) or die('\nProblem z '.$sql);
$result=$this->describe_table($TABLE) or die('\nProblem z '.$sql);
$FIELDS_CHECKED=""; unset($FIELDS_CHECKED);
foreach($ARR_TABLE['FIELDS'] as $TSTIND=>$TST_VAL) { //tu robie tabelke do odhaczania z niej zmiennych
$FIELDS_CHECKED['FIELD_IN_CONFIG_TO_BE_PASSED'][$TST_VAL->Field]=1;
$FIELDS_CHECKED['FIELD_IN_CONFIG_THAT_EXISTS'][$TST_VAL->Field]=1;
}
DEBUG_S(-3,'FIELDS_CHECKED',$FIELDS_CHECKED,__FILE__, __FUNCTION__, __LINE__) ;
$var_passed=false;
while($h=$this->fetch($result)) {
//tu sprawdzamy czy sa wszystkie kolumny i maja takie same typy
$FIELDS_CHECKED['FIELD_FROM_DB'][$h->Field]=1;
if(!isset($FIELDS_CHECKED['FIELD_IN_CONFIG_THAT_EXISTS'][$h->Field])) {
DEBUG_S(-3,'Za duzo o komorke '.$h->Field,$FIELDS_CHECKED,__FILE__, __FUNCTION__, __LINE__) ;
$FIELDS_CHECKED['FIELD_IN_CONFIG_THAT_NOT_EXISTS'][$h->Field]=1;
}
foreach($ARR_TABLE['FIELDS'] as $TSTIND=>$TST_VAL) { //tu sprawdzam czy kolumna jest i jaka
// echo "\n Tst ".$TST_VAL->Field." ==? ".$h->Field;
if($TST_VAL->Field==$h->Field) {
$FIELDS_CHECKED['FIELD_FOUND_IN_REMOTE_DB'][$h->Field]=1;
if(isset($FIELDS_CHECKED['FIELD_IN_CONFIG_TO_BE_PASSED'][$h->Field])) //jezeli byla komorka ramowa
unset($FIELDS_CHECKED['FIELD_IN_CONFIG_TO_BE_PASSED'][$h->Field]);
}
}
//$TABLES_IMPORT[$TABLE]['FIELDS']=$h;
}
if(!empty($FIELDS_CHECKED['FIELD_IN_CONFIG_TO_BE_PASSED'])) DEBUG_S(-3,'Brakuje komorek dla '.$TABLE." ",array($FIELDS_CHECKED['FIELD_IN_CONFIG_TO_BE_PASSED']),__FILE__, __FUNCTION__, __LINE__);
DEBUG_S(-3,'FIELDS_CHECKED',$FIELDS_CHECKED,__FILE__, __FUNCTION__, __LINE__) ;
//dodajemy brakujace komorki
if(count($FIELDS_CHECKED['FIELD_IN_CONFIG_TO_BE_PASSED'])>0) {
foreach($FIELDS_CHECKED['FIELD_IN_CONFIG_TO_BE_PASSED'] as $FIELD_TO_ADD=>$VAL ){
set_time_limit(36000);
//echo "\n Trying to add column: ".$FIELD_TO_ADD;
if(isset($INDEX_OF_CONFIG_TABLE)) unset($INDEX_OF_CONFIG_TABLE);
foreach($ARR_TABLE['FIELDS'] as $TSTIND=>$TST_VAL) { //trzeba znalezc numer ID indeksu brakujacej komorki
if($TST_VAL->Field==$FIELD_TO_ADD) $INDEX_OF_CONFIG_TABLE=$TSTIND;
}
// $sql="create table ".$TABLE." ( ".$ARR_TABLE['FIELDS'][0]->Field." ".$ARR_TABLE['FIELDS'][0]->Type ; // ID int(11) NOT NULL AUTO_INCREMENT,
//if($ARR_TABLE['FIELDS'][0]->Extra=='auto_increment') $sql.=" AUTO_INCREMENT "; else $sql.=" ";
//* $sql="alter table \"".$TABLE."\" add \"".$FIELD_TO_ADD."\" ".$this->from_mysql_struct($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type)." ";
self::alter_add_struct($TABLE,$FIELD_TO_ADD,$this->from_mysql_struct($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type));
//if(($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Default)) {
/// if(($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Default=='NULL')||($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Default=='CURRENT_TIMESTAMP'))
/// $sql.=" DEFAULT ".$ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Default." ";// (".$INDEX_OF_CONFIG_TABLE.")";
/// else $sql.=" DEFAULT '".$ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Default."' ";// (".$INDEX_OF_CONFIG_TABLE.")";
// }
/// TODO !!! if($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Null=='NO') $sql.=" not null "; else $sql.=" null ";
// $sql.=" null ";
// $sql.=" ;";
// echo "\n ".$sql;
// $this->query($sql) or DEBUG_S(-3,'Problem z sql',$sql,__FILE__, __FUNCTION__, __LINE__) or die();
}
} //eof dodajemy brakujace komorki
//Sprawdzamy czy sa zgodne typy komorek...
//$sql=" describe `".$TABLE."` ;" ;
//DEBUG_S(-3,'describe '.$TABLE,$sql,__FILE__, __FUNCTION__, __LINE__) ;
$result=$this->describe_table($TABLE) or die('\nProblem z '.$sql);
//$result=$this->query($sql) or die('\nProblem z '.$sql);
while($h=self::fetch($result)) {
set_time_limit(36000);
$FIELDS_CHECKED['FIELD_FOUND_IN_REMOTE_DB_PROFILE'][$h->Field]=$h;
}
DEBUG_S(-3,'FIELDS_CHECKED',$FIELDS_CHECKED,__FILE__, __FUNCTION__, __LINE__) ;
foreach($FIELDS_CHECKED['FIELD_FOUND_IN_REMOTE_DB_PROFILE'] as $FIELD_TO_CHK=>$VAL) {
if(isset($INDEX_OF_CONFIG_TABLE)) unset($INDEX_OF_CONFIG_TABLE);
foreach($ARR_TABLE['FIELDS'] as $TSTIND=>$TST_VAL) { //trzeba znalezc numer ID indeksu brakujacej komorki
if(trim($TST_VAL->Field)==trim($FIELD_TO_CHK)) $INDEX_OF_CONFIG_TABLE=$TSTIND;
}
if(isset($FIELDS_CHECKED['FIELD_IN_CONFIG_THAT_NOT_EXISTS'][$FIELD_TO_CHK])) continue; //jezeli komorki nie ma i jest tylko lokalnie - ignorujemy!
// to jakis glupi warunek ?
if(!isset($INDEX_OF_CONFIG_TABLE)) die('Nie znaleziono indeksu w konfiguracji dla '.$TABLE.' i komorki '.$FIELD_TO_CHK);
// echo "\nsprawdzamy wszystkie wlasciwosci dla db ".$TABLE." ".$FIELD_TO_CHK." col ".$INDEX_OF_CONFIG_TABLE;
$CHK_TYPES_ARS=array('Type','Null','Default');
$sql="";
// echo "\n if ".var_dump($VAL->Type)." =? ".var_dump($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type) ;
if(!strcmp(trim($VAL->Type),trim($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type))) {
//! aktualizujemy typy
$sql=" alter table \"".$TABLE."\" ALTER COLUMN \"".$FIELD_TO_CHK."\" ".$ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type." ; ";
if(strstr(trim($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type), 'char(')&& strstr(trim($VAL->Type), 'char(')){
$src_char_len=ereg_replace('[^0-9]', '',trim($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type) );
$dst_char_len=ereg_replace('[^0-9]', '',trim($VAL->Type) );
if($src_char_len>$dst_char_len) {
DEBUG_S(-3,'Aktualizacja rozszerzajaca zakres pola CHAR : CUR_CHAR_LEN ='.$src_char_len." and dest=".$dst_char_len ,$sql,__FILE__, __FUNCTION__, __LINE__);
if(!empty($CONFIRM_SQL_UPDATE)) $result=$this->query($sql) or DEBUG_S(-3,'Problem ze SQL:',$sql,__FILE__, __FUNCTION__, __LINE__) or die('\nProblem z '.$sql);
} else {
DEBUG_S(-3,'celowe pominiecie - aktualizacja moze skasowac dane w bazie!!! :',$sql,__FILE__, __FUNCTION__, __LINE__);
// die();
}
} else if(strstr(trim($VAL->Type), 'enum(') && strstr(trim($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type), 'enum(' )) { //jezeli zmienia sie ENUM na inne ENUM
preg_match('/^enum\((.*)\)$/', trim($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type), $src_char_len);
preg_match('/^enum\((.*)\)$/', trim($VAL->Type), $dst_char_len);
$src_char_len=explode(',', $src_char_len[1]);$dst_char_len=explode(',', $dst_char_len[1]);
foreach($src_char_len as $var) {
if( !in_array($var, $dst_char_len)) $dst_char_len[]=$var;
}
$dst_char_len="enum(".implode(",", $dst_char_len).")";
$sql=" alter table \"".$TABLE."\" CHANGE \"".$FIELD_TO_CHK."\" \"".$FIELD_TO_CHK."\" ".$dst_char_len." ; ";
DEBUG_S(-3,'Uzupelniamy strukture o ewentualnie brakujace elementy (sql,wynik polaczenia,import.strukt.,akt_strukt.)',array($sql,$dst_char_len,$ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type,$VAL->Type),__FILE__, __FUNCTION__, __LINE__);
if(!empty($CONFIRM_SQL_UPDATE)) $result=$this->query($sql) or DEBUG_S(-3,'Problem ze SQL:',$sql,__FILE__, __FUNCTION__, __LINE__) or die('\nProblem z '.$sql);
} else if(strstr(trim($VAL->Type), 'set(') && strstr(trim($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type), 'set(' )) { //jezeli zmienia sie ENUM na inne ENUM
preg_match('/^set\((.*)\)$/', trim($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type), $src_char_len);
preg_match('/^set\((.*)\)$/', trim($VAL->Type), $dst_char_len);
$src_char_len=explode(',', $src_char_len[1]);$dst_char_len=explode(',', $dst_char_len[1]);
foreach($src_char_len as $var) {
if( !in_array($var, $dst_char_len)) $dst_char_len[]=$var;
}
$dst_char_len="set(".implode(",", $dst_char_len).")";
$sql=" alter table \"".$TABLE."\" CHANGE \"".$FIELD_TO_CHK."\" \"".$FIELD_TO_CHK."\" ".$dst_char_len." ; ";
DEBUG_S(-3,'Uzupelniamy strukture o ewentualnie brakujace elementy (sql,wynik polaczenia,import.strukt.,akt_strukt.)',array($sql,$dst_char_len,$ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type,$VAL->Type),__FILE__, __FUNCTION__, __LINE__);
if(!empty($CONFIRM_SQL_UPDATE)) $result=$this->query($sql) or DEBUG_S(-3,'Problem ze SQL:',$sql,__FILE__, __FUNCTION__, __LINE__) or die('\nProblem z '.$sql);
} else if(strstr(trim($VAL->Type), 'int(') && strstr(trim($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type), 'int(' )) {
DEBUG_S(-3,'Te same typy INT',array($VAL->Type,$ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type),__FILE__,__FUNCTION__,__LINE__);
preg_match('/^int\((.*)\)$/', $ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type, $src_char_len);
preg_match('/^int\((.*)\)$/', $VAL->Type, $dst_char_len);
if($src_char_len[1]>$dst_char_len[1]) {
$sql=" alter table \"".$TABLE."\" ALTER COLUMN \"".$FIELD_TO_CHK."\" type ".self::from_mysql_struct($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type)." ; ";
DEBUG_S(-3,'Aktualizacja rozszerzajaca zakres pola INT ma byc ='.$src_char_len[1]." byla dest=".$dst_char_len[1] ,$sql,__FILE__, __FUNCTION__, __LINE__);
// if(!empty($CONFIRM_SQL_UPDATE)) $result=$this->query($sql) or DEBUG_S(-3,'Problem ze SQL:',$sql,__FILE__, __FUNCTION__, __LINE__) or die('\nProblem z '.$sql);
} else {
DEBUG_S(-3,'Pominieta Aktualizacja zawiezajaca zakres pola INT ma byc ='.$src_char_len[1]." byla dest=".$dst_char_len[1] ,$sql,__FILE__, __FUNCTION__, __LINE__);
}
} else if( ( strstr(trim($VAL->Type), 'char(') || strstr(trim($VAL->Type), 'varchar(') ) && strstr(trim($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type), 'text' )
|| ( strstr(trim($VAL->Type), 'bigint(') ) && strstr(trim($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type), 'longblob' )
|| ( strstr(trim($VAL->Type), 'mediumtext') ) && strstr(trim($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type), 'longblob' )
|| ( strstr(trim($VAL->Type), 'decimal(42') ) && strstr(trim($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type), 'decimal(41' )
|| ( strstr(trim($VAL->Type), 'decimal(43') ) && strstr(trim($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type), 'decimal(41' )
) {
$sql=" alter table \"".$TABLE."\" CHANGE \"".$FIELD_TO_CHK."\" \"".$FIELD_TO_CHK."\" ".$ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type." ; ";
DEBUG_S(-3,'Aktualizacja rozszerzajaca zakres pola varchar/char() do text()',$sql,__FILE__, __FUNCTION__, __LINE__);
if(!empty($CONFIRM_SQL_UPDATE)) $result=$this->query($sql) or DEBUG_S(-3,'Problem ze SQL:',$sql,__FILE__, __FUNCTION__, __LINE__) or die('\nProblem z '.$sql);
// DEBUG_S(-3,'Pominieta Aktualizacja zawiezajaca zakres pola INT ma byc ='.$src_char_len[1]." byla dest=".$dst_char_len[1] ,$sql,__FILE__, __FUNCTION__, __LINE__);
} else {
DEBUG_S(-3,'Celowe zabicie z uwagi na niebezpieczenstwo zmiany typu: sql/old/new',array($sql,$VAL->Type,$ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type),__FILE__, __FUNCTION__, __LINE__);
echo " DO AKTAULIZACJI PHP BO TO JEST BLAD PHP!!!";
continue;
die();
if(!empty($CONFIRM_SQL_UPDATE)) $result=$this->query($sql) or DEBUG_S(-3,'Problem ze SQL:',$sql,__FILE__, __FUNCTION__, __LINE__) or die('\nProblem z '.$sql);
}
}
if($VAL->Null!=$ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Null)
if($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Null=='YES') {
$sql=" alter table `".$TABLE."` CHANGE `".$FIELD_TO_CHK."` `".$FIELD_TO_CHK."` ".$ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type." NULL ; ";
if(!empty($CONFIRM_SQL_UPDATE)) $result=$this->query($sql) or DEBUG_S(-3,'Problem ze SQL ',$sql,__FILE__, __FUNCTION__, __LINE__) or DIE();
} else {
$sql=" alter table `".$TABLE."` CHANGE `".$FIELD_TO_CHK."` `".$FIELD_TO_CHK."` ".$ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type." NOT NULL ; ";
if(!empty($CONFIRM_SQL_UPDATE)) $result=$this->query($sql) or DEBUG_S(-3,'Problem ze SQL ',$sql,__FILE__, __FUNCTION__, __LINE__) or DIE();
}
if($VAL->Default!=$ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Default) {
if($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Default=='CURRENT_TIMESTAMP')
$sql=" alter table `".$TABLE."` CHANGE `".$FIELD_TO_CHK."` `".$FIELD_TO_CHK."` ".$ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type." DEFAULT ".str_replace("'","",$ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Default)." ; ";
else if(empty($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Default))
$sql=" alter table `".$TABLE."` CHANGE `".$FIELD_TO_CHK."` `".$FIELD_TO_CHK."` ".$ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type." ; ";
else $sql=" alter table `".$TABLE."` CHANGE `".$FIELD_TO_CHK."` `".$FIELD_TO_CHK."` ".$ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type." DEFAULT '".$ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Default."' ; ";
if(!empty($CONFIRM_SQL_UPDATE)) $result=$this->query($sql) or DEBUG_S(-3,'Problem ze SQL ',$sql,__FILE__, __FUNCTION__, __LINE__) or DIE();
}
//autoincrement:
if(in_array('SKIP-PRIMARY-KEY', $options)) {
DEBUG_S(4,'SKIP-PRIMARY-KEY option',null,__FILE__,__FUNCTION__,__LINE__);
} else if((trim($VAL->Extra)!=trim($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Extra))&&trim($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Extra)=='auto_increment' ) {
//sprawdzmy czy nie ma w tabeli klucza z PRIMARY z ID
//$sql="show keys from `".$TABLE."`";
//$result=$this->query($sql) or DEBUG_S(-3,'Problem ze SQL ',$sql,__FILE__, __FUNCTION__, __LINE__) or DIE();
$result=self::show_index($TABLE);
$TEST_IF_HAS_PRIMARY=false;
while($tst=self::fetch($result)) { //dla kazdego klucza
if(trim($tst->Column_name)==$FIELD_TO_CHK) //sprawdz czy jest klucz do tej kolumny
if(trim($tst->Key_name)=='PRIMARY') $TEST_IF_HAS_PRIMARY=true; //jezeli jest primary, to flagujemy
}
if($TEST_IF_HAS_PRIMARY==false) { //bedziemy dodawac primary key
$sql="ALTER TABLE \"".$TABLE."\" ADD PRIMARY KEY ( \"".$FIELD_TO_CHK."\" )";
$result=$this->query($sql) or DEBUG_S(-3,'Problem ze SQL ',$sql,__FILE__, __FUNCTION__, __LINE__) or DIE();
}
$sql=" alter table \"".$TABLE."\" ALTER COLUMN \"".$FIELD_TO_CHK."\" TYPE ".$ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Type." ".$ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE]->Extra." ; ";
echo "\n SQL auto_increment ".$sql ;
if(!empty($CONFIRM_SQL_UPDATE)) $result=$this->query($sql) or DEBUG_S(-3,'Problem ze SQL ',$sql,__FILE__, __FUNCTION__, __LINE__) or DIE();
}
//foreach($ARR_TABLE['FIELDS'][$INDEX_OF_CONFIG_TABLE] as $TST_FIELD_IND=>$TST_FIELD_VAL) {
// echo "\n TST2 ".$TST_FIELD_IND." ?= ".$FIELDS_CHECKED['FIELD_FOUND_IN_REMOTE_DB_PROFILE'][$TST_FIELD_IND]->$TST_FIELD_IND;
//}
}
//print_r($FIELDS_CHECKED);
/* TODO to implement to postgressql!!!
$sql=" show index from `".$TABLE."` ;" ;
echo "\n ".$sql;
$result=$this->query($sql) or die('\nProblem z '.$sql);
while($h=mysql_fetch_object($result)) {
set_time_limit(36000);
$FIELDS_CHECKED['INDEX_FOUND_IN_TABLES'][$h->Key_name]=$h;
}
foreach($ARR_TABLE['INDEX'] as $FOUND_INDEX_NAME=>$FOUND_INDEX_ARR){
$FIELDS_CHECKED['INDEX_TO_BE_FOUND_IN_TABLES'][$FOUND_INDEX_NAME]=$FOUND_INDEX_ARR;
}
DEBUG_S(-3,'Znaleziono indeksy w tabeli '.$TABLE." FOUND/TO_BE_FOUND",array($FIELDS_CHECKED['INDEX_FOUND_IN_TABLES'],$FIELDS_CHECKED['INDEX_TO_BE_FOUND_IN_TABLES']),__FILE__, __FUNCTION__, __LINE__);
//teraz wygaszam znalezione indeksy
foreach($ARR_TABLE['INDEX'] as $FOUND_INDEX_NAME=>$FOUND_INDEX_ARR){
echo "\n If jest ".$FOUND_INDEX_NAME;
if(isset($FIELDS_CHECKED['INDEX_FOUND_IN_TABLES'][$FOUND_INDEX_NAME]))
unset($FIELDS_CHECKED['INDEX_TO_BE_FOUND_IN_TABLES'][$FOUND_INDEX_NAME]);
}
//print_r($FIELDS_CHECKED['INDEX_TO_BE_FOUND_IN_TABLES']);
//brakujace klucze dodajemy
foreach($FIELDS_CHECKED['INDEX_TO_BE_FOUND_IN_TABLES'] as $FOUND_INDEX_NAME=>$FOUND_INDEX_ARR) {
if($FOUND_INDEX_NAME=='PRIMARY') {
$FOUND_INDEX_ARR->Column_name=str_replace('`','',$FOUND_INDEX_ARR->Column_name);
$sql="ALTER TABLE `".$TABLE."` ADD PRIMARY KEY ( `".$FOUND_INDEX_ARR->Column_name."` ) ;";
echo "\n 927: ".$sql;
if(!empty($CONFIRM_SQL_UPDATE)) $result=$this->query($sql) or print_r($FIELDS_CHECKED) && die('\nProblem z '.$sql);
$sql="ALTER TABLE `".$TABLE."` CHANGE `".$FOUND_INDEX_ARR->Column_name."` `".$FOUND_INDEX_ARR->Column_name."` ".$FIELDS_CHECKED['FIELD_FOUND_IN_REMOTE_DB_PROFILE'][$FOUND_INDEX_ARR->Column_name]->Type." AUTO_INCREMENT ; ";
echo "\n ".$sql;
if(!empty($CONFIRM_SQL_UPDATE)) $result=$this->query($sql) or print_r($FIELDS_CHECKED) && die('\nProblem z '.$sql);
if(!empty($CONFIRM_SQL_UPDATE)) $result=$this->query($sql) or print_r($FIELDS_CHECKED) && die('\nProblem z '.$sql);
} else if($FOUND_INDEX_ARR->Non_unique==1) {
$sql="ALTER TABLE `".$TABLE."` ADD KEY `".$FOUND_INDEX_ARR->Key_name."` ( ".$FOUND_INDEX_ARR->Column_name." ) ;" ;
echo "\n ".$sql;
if(!empty($CONFIRM_SQL_UPDATE)) $result=$this->query($sql) or DEBUG_S(-3,'Problem ze SQL:',$sql,__FILE__, __FUNCTION__, __LINE__) or die();
} else {
set_time_limit(36000);
//@2013-01-17 poprawka do kluczy bledu w stylu ALTER TABLE `IN7_PRODUKTY_CECHY` ADD UNIQUE `UNIK_CECHY_PRODUKTU` ( CECHA_ID, PRODUKT_ID , DESC ) ;
$CORRECT_INDEX_FOUNDKEY="";
if(!strstr($FOUND_INDEX_ARR->Column_name,'`')) {
$explode=explode(' ',$FOUND_INDEX_ARR->Column_name);
foreach($explode as $exploded) {
$exploded=str_replace(',','',$exploded); //wywalenie przecinkow
if((!strstr($exploded,' '))&&(strlen($exploded)>1))
if($CORRECT_INDEX_FOUNDKEY) $CORRECT_INDEX_FOUNDKEY.=" , `".$exploded."` ";
else $CORRECT_INDEX_FOUNDKEY.=" `".$exploded."` ";
}
} else $CORRECT_INDEX_FOUNDKEY=$FOUND_INDEX_ARR->Column_name;
// $CORRECT_INDEX_FOUNDKEY=$FOUND_INDEX_ARR->Column_name;
$sql="ALTER TABLE `".$TABLE."` ADD UNIQUE `".$FOUND_INDEX_ARR->Key_name."` ( ".$CORRECT_INDEX_FOUNDKEY." ) ;" ;
echo "\n ".$sql;
if(!empty($CONFIRM_SQL_UPDATE)) $result=$this->query($sql) or DEBUG_S(-3,'Problem ze SQL:',$sql,__FILE__, __FUNCTION__, __LINE__) or die();
}
}
flush();
//print_r($FIELDS_CHECKED);
unset($FIELDS_CHECKED);
$sql=" show triggers like '".$TABLE."' ;" ; //listujemy nasze triggery z tej tabeli
DEBUG_S(-3,'Looking for triggers in '.$TABLE,$sql,__FILE__,__FUNCTION__,__LINE__);
$result=$this->query($sql) or die('\nProblem z '.$sql);
while($h=mysql_fetch_object($result)) {
set_time_limit(36000);
$sql_="show create trigger {$h->Trigger} ;" ;
//echo $sql_;
$result_=$this->query($sql_) or die('\nProblem z '.$sql_);
while($h_=mysql_fetch_object($result_)) {
$FIELDS_CHECKED['TRIGGER_FOUNDS'][$h->Trigger]=$h_;
if(!isset($ARR_TABLE['TRIGGERS'][$h->Trigger])) $FIELDS_CHECKED['TRIGGER_TO_DELETE'][$h->Trigger]=$h_;
}
}
DEBUG_S(-3,'Try to unserialize trigger and install to db:',$ARR_TABLE['TRIGGERS'],__FILE__,__FUNCTION__,__LINE__);
//! try to synchronize triggers $TABLE=>$ARR_TABLE
foreach($ARR_TABLE['TRIGGERS'] as $TRIGGER_INDEX=>$TRIGGER_OBJECT) {
if(!isset($FIELDS_CHECKED['TRIGGER_FOUNDS'][$TRIGGER_INDEX])) $FIELDS_CHECKED['TRIGGER_TO_CREATE'][$TRIGGER_INDEX]=$TRIGGER_OBJECT;
}
//staramy sie zaktualizowac stare triggery, pod warunkiem, ze sie roznia
foreach($FIELDS_CHECKED['TRIGGER_FOUNDS'] as $TRIGGER_INDEX=>$TRIGGER_OBJECT) {
DEBUG_S(-3,'TRIGGER_OBJECT/$TRIGGER_OBJECT for '.$TRIGGER_INDEX,array($TRIGGER_OBJECT,$ARR_TABLE['TRIGGERS'][$TRIGGER_INDEX]),__FILE__, __FUNCTION__, __LINE__);
if(isset($ARR_TABLE['TRIGGERS'][$TRIGGER_INDEX])) //we first try to find if this trigger exists or delete it
if($TRIGGER_OBJECT->{'SQL Original Statement'}<>$ARR_TABLE['TRIGGERS'][$TRIGGER_INDEX]->{'SQL Original Statement'} ) {
DEBUG_S(-3,"Triggers are different ({$TRIGGER_INDEX}). We add object to be created as new.",__FILE__, __FUNCTION__, __LINE__);
$FIELDS_CHECKED['TRIGGER_TO_CREATE'][$TRIGGER_INDEX]=$ARR_TABLE['TRIGGERS'][$TRIGGER_INDEX];
$FIELDS_CHECKED['TRIGGER_TO_DELETE'][$TRIGGER_INDEX]=true;
}
}
if(isset($FIELDS_CHECKED['TRIGGER_TO_DELETE'])) {
DEBUG_S(-3,'We have some triggers to delete',$FIELDS_CHECKED['TRIGGER_TO_DELETE'],__FILE__,__FUNCTION__,__LINE__);
foreach($FIELDS_CHECKED['TRIGGER_TO_DELETE'] as $TRIGGER_INDEX=>$TRIGGER_OBJECT ) {
$sql="DROP TRIGGER IF EXISTS `{$TRIGGER_INDEX}`; ";
DEBUG_S(-3,' sql to delete trigger '.$TRIGGER_INDEX,$sql,__FILE__,__FUNCTION__,__LINE__);
if(!empty($CONFIRM_SQL_UPDATE)) $result=$this->query($sql) or DEBUG_S(-3,'Problem ze SQL:',$sql,__FILE__, __FUNCTION__, __LINE__) or die();
}
}
//tworzymy brakujace procedury
foreach ($FIELDS_CHECKED['TRIGGER_TO_CREATE'] as $TRIGGER_INDEX=>$TRIGGER_OBJECT) {
//$sql="DROP TRIGGER IF EXISTS `{$TRIGGER_INDEX}`; ";
//DEBUG_S(-3,'Delete old trigger '.$TRIGGER_INDEX,$sql,__FILE__,__FUNCTION__,__LINE__);
//if(!empty($CONFIRM_SQL_UPDATE)) $result=$this->query($sql) or DEBUG_S(-3,'Problem ze SQL:',array($sql,mysql_error() ),__FILE__, __FUNCTION__, __LINE__) or die();
//if(!empty($CONFIRM_SQL_UPDATE)) $result=$this->query("show create trigger {$TRIGGER_INDEX}") or DEBUG_S(-3,'Problem ze SQL:'.mysql_error(),$sql,__FILE__, __FUNCTION__, __LINE__) or die();
$sql=$TRIGGER_OBJECT->{'SQL Original Statement'};
DEBUG_S(-3,'Try to add new trigger '.$TRIGGER_INDEX,$sql,__FILE__,__FUNCTION__,__LINE__);
if(!empty($CONFIRM_SQL_UPDATE)) $result=$this->query($sql) or DEBUG_S(-3,'Problem ze SQL:',$sql,__FILE__, __FUNCTION__, __LINE__) or die();
}
*/
}
//tu bedziemy dodawac wartosci insert :
if(!strstr($TABLE, '__SCHEMA_FUNCTIONS__ARRAY__')) {
$sql="select count(*) as ilosc from \"".$TABLE."\" ;" ;
DEBUG_S(-3,'SQL: licze czy cos jest w tabeli ',$sql,__FILE__, __FUNCTION__, __LINE__) ;
$result=$this->query($sql) or die('\nProblem z '.$sql);
$ilosc=0;
//while($h=mysql_fetch_object($result)) {
// $ilosc=$h->ilosc;
//}
//if($ilosc>0) {
// DEBUG_S(-2,'Nie trzeba dodawac rekordow - juz jakies sa w tabeli: ilosc : ',$ilosc,__FILE__,__FUNCTION__,__LINE__);
// continue ;
//} else {
if(!empty($ARR_TABLE['VALUES']))
foreach($ARR_TABLE['VALUES'] as $ROW) {
$sql="insert ignore into `".$TABLE."` values ( ";
$vals="";
foreach($ROW as $FIELD=>$VAL) {
if(!$vals) $vals.="'".$VAL."'" ; else $vals.=" , '".$VAL."' ";
}
$sql.=$vals." ) ;";
if(!empty($CONFIRM_SQL_UPDATE)) $this->query($sql) or die('\nProblem z '.$sql);
DEBUG_S(-3,'Dodanie rekordu do tabeli ',$sql,__FILE__,__FUNCTION__,__LINE__);
}
//}
}
}
//}
DEBUG_S(-3,'function in devel',$array,__FILE__,__FUNCTION__,__LINE__);
}
function SHOW_TABLE_STATUS_LIKE($table) {
$return['engine']='default';
$res=$this->query("SELECT pg_class.relname, pg_attribute.attname
FROM pg_class, pg_attribute, pg_index
WHERE pg_class.oid = pg_attribute.attrelid AND
pg_class.oid = pg_index.indrelid AND
pg_index.indkey[0] = pg_attribute.attnum AND
pg_index.indisprimary = 't'
and pg_class.relname='".$table."';");
$record=$this->fetch_assoc($res);
$return['primary']=$record['attname'];
$sql='select max("'.$return['primary'].'")+1 as max from "'.$table.'";';
$res=$this->query($sql);
$record=$this->fetch_assoc($res);
$return['Auto_increment']=$record['max'];
return $return;
}
function affected_rows($res) {
return pg_affected_rows( $res );
}
function _( $str ) {
return pg_escape_string( $str);
}
function error() {
return "#".pg_result_error_field($this->_conn).": ".pg_result_error($this->_conn);
}
function get_by_id( $table, $id ) {
$primary=self::show_index_value($table); //TODO to optimalize cache
$null = null;
$sql = "select p.*
from \"".$table."\" as p
where p.\"".$primary."\"='".$id."'
";
$res = $this->query( $sql );
if ($r = $this->fetch( $res )) {
return $r;
}
return $null;
}
/**
* @returns int
* 1 - changed but without add hist
* 2 - changed and add hist
* 0 - nothing to change
* -1 - sql errors
* -2 - error id not exists in DB
* -3 - error ID not set
*
* TODO: sprawdzac czy w hist mozna odczytac aktualny stan, jesli nie to dodac caly rekord do HIST, jako 'procesy-fix-hist-data'
*/
public function UPDATE_OBJ($table, $sql_obj,$timestamp=null,$skip_author=null) {
$structure=self::describe_table_value($table);
$primary=self::show_index_value($table);
if (!isset($sql_obj->$primary) || $sql_obj->$primary <= 0) {
return -3;
}
$id = $sql_obj->$primary;
// check id record $id exists
if (($curr_obj = $this->get_by_id( $table, $sql_obj->$primary )) == null) {
return -2;
}
// check if enything changed
$changed = false;
$fields_to_change = get_object_vars($sql_obj);
//DEBUG_S(-3,'chk',array($fields_to_change),__FILE__,__FUNCTION__,__LINE__);
foreach ($fields_to_change as $k => $v) {
if ($k == $primary) continue;
if ($v == $curr_obj->$k) {// === ?
unset($sql_obj->$k);
} else {
$changed = true;
}
}
if ($changed == false) {
return 0;// record not changed
}
$sql_arr = array();
// TODO: add admin columns if exists in table - search in session
$admin_col = array();
$admin_col []= 'A_RECORD_CREATE_DATE';
$admin_col []= 'A_RECORD_CREATE_AUTHOR';
// ...
// $sql_obj->A_RECORD_UPDATE_DATE = date('Y-m-d H:i');
if(!empty($timestamp)) //fixed timestamp option TODO @2015-01-not working due to trigger?
$sql_obj->A_RECORD_UPDATE_DATE = $timestamp;
else
$sql_obj->A_RECORD_UPDATE_DATE = self::get_current_timestamp();
$sql_obj->A_RECORD_UPDATE_AUTHOR = User::getName();
foreach (get_object_vars($sql_obj) as $k => $v) {
if($k=='A_RECORD_CREATE_DATE'&& $v=='0000-00-00 00:00:00') {
unset($k);
unset($v);
continue;
} else if(strstr(trim($structure[$k]->Type),'date') && (strstr($v,'-00'))) {
$v="'".str_replace("-00","-01",$v)."'" ;
} else if(strstr(trim($structure[$k]->Type),'date') && (strstr($v,'0000'))) {
$v="'".str_replace("0000","1970",$v)."'" ;
} else if (substr($v, 0, strlen('ST_GeomFromText')) == 'ST_GeomFromText') {
} else if (strtoupper($v) == 'NOW()') {
$v = 'NOW()';
} else if (strtoupper($v) == 'NULL') {
$v = 'NULL';
} else {
$v = $this->_($v);
$v = "'{$v}'";
}
$sql_arr [] = '"'.$k.'"='.$v;
}
$sql = "update \"{$table}\" set ".implode(",", $sql_arr)." where \"".$primary."\"='{$id}' ; ";
// DEBUG_S(-3,'update',array($sql),__FILE__,__FUNCTION__,__LINE__);
// die();
$res=$this->query($sql);
if ($this->has_errors()) {
//DEBUG_S(-3,'errors',$this->has_errors,__FILE__,__FUNCTION__,__LINE__);
return -1;
}
$ret = $this->affected_rows($res);
// DEBUG_S(-3,'affected',$ret,__FILE__,__FUNCTION__,__LINE__);
if ($ret) {
$sql_obj->ID_USERS2 = $sql_obj->$primary;
unset($sql_obj->$primary);
$new_id = $this->ADD_NEW_OBJ("{$table}_HIST", $sql_obj);
if ($new_id) {
$ret += 1;
}
}
return $ret;
}
function ADD_NEW_OBJ( $table, $sql_obj,$dieonerror=null ) {
$structure=self::describe_table_value($table);
$primary=self::show_index_value($table);
$sql_arr = array();
// TODO: add admin columns if exists in table - search in session
$admin_col = array();
$admin_col []= 'A_RECORD_CREATE_DATE';
$admin_col []= 'A_RECORD_CREATE_AUTHOR';
$admin_col []= 'A_RECORD_UPDATE_DATE';
$admin_col []= 'A_RECORD_UPDATE_AUTHOR';
// ...
if (substr($table, 0, -5) == '_HIST') {
$sql_obj->A_RECORD_UPDATE_DATE = date('Y-m-d H:i');
$sql_obj->A_RECORD_UPDATE_AUTHOR = User::getName();
} else {
$sql_obj->A_RECORD_CREATE_DATE = date('Y-m-d H:i');
$sql_obj->A_RECORD_CREATE_AUTHOR = User::getName();
}
foreach (get_object_vars($sql_obj) as $k => $v) {
if($k==$primary) {
unset($k);
unset($v);
continue;
} else if (substr($v, 0, strlen('ST_GeomFromText')) == 'ST_GeomFromText') {
} else if(strstr(trim($structure[$k]->Type),'int')) {
if(strlen($v)>0) {
$v=$v;
} else if(empty($v)) $v='null';
else $v = $v;
} else if(trim($structure[$k]->Type)=='datetime') {
if(empty($v)) $v='null';
else if(strstr($v,'-00')) $v="'".str_replace("-00","-01",$v)."'" ;
else $v = "'".$v."'::timestamp";
} else if(trim($structure[$k]->Type)=='date') {
if($v=='0000-00-00') $v='null';
else if(strstr($v,'-00')) $v="'".str_replace("-00","-01",$v)."'" ;
else $v = "'".$v."'";
} else if (strtoupper($v) == 'NOW()') {
$v = 'NOW()';
} else if (strtoupper($v) == 'NULL' && substr($table, -5) != '_HIST') {
$v = 'NULL';
} else if(strstr($structure[$k]->Type,'int(')) {
$v = $v ;
} else {
$v = $this->_($v);
$v = "'{$v}'";
}
$sql_arr ['"'.$k.'"'] = $v;
}
$sql = "insert into \"{$table}\" (".implode(",", array_keys($sql_arr)).") values (".implode(",", array_values($sql_arr))."); ";
//DEBUG_S(-3,' insert sql ',$sql,__FILE__,__FUNCTION__,__LINE__);
$this->query($sql);
if ($this->has_errors()) {
if($dieonerror) {
DEBUG_S(-3,'Has errors died',$this->get_errors(),__FILE__,__FUNCTION__,__LINE__);
die();
}
// echo'db errors: (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($this->get_errors());print_r($sql);echo'';
}
$ret_id = $this->insert_id();
if (substr($table, -5) == '_HIST') {
return $ret_id;
}
if ($ret_id) {
$sql_obj->ID_USERS2 = $ret_id;
unset($sql_obj->ID);
$new_id_hist = $this->ADD_NEW_OBJ($table . '_HIST', $sql_obj);
// error jesli nie udalo sie dodac rekordu do tabeli _HIST
}
return $ret_id;
}
function MAKE_SE_AND_HIST_FIELDS_FOR_TABLE($table){
$primary=self::show_index_value($table);
//TODO @2014-10-01 zrobic tabele z autoincrement + hist
//die('function incomplete');
//$sql="show tables like '".$table."';";
DEBUG_S(-3,'primary detected ',array($sql,$primary),__FILE__,__FUNCTION__,__LINE__);
$USERS_COLUMN_INIT2['REQUEST_COLUMN']=array(
'A_RECORD_CREATE_DATE'=>'datetime','A_RECORD_CREATE_AUTHOR'=>'varchar(100)','SYNC_SQIX_STATUS'=>'varchar(100) ',
'A_RECORD_UPDATE_DATE'=>'datetime','A_RECORD_UPDATE_AUTHOR'=>'varchar(100) ',
'L_APPOITMENT_DATE'=>'varchar(30) ',
'L_APPOITMENT_USER'=>"varchar(100) ",
'L_APPOITMENT_PERIOD'=>"varchar(4) ",
'L_APPOITMENT_INFO'=>"varchar(255) ",
'L_APPOITMENT_TYPE'=>"enum('', 'ARRANGED', 'TO_ARRANGE', 'CANCELLED', 'TO_CANCEL', 'RELATE', 'WAIT')",
'A_STATUS'=>"enum('WAITING', 'NORMAL', 'MONITOR', 'WARNING', 'OFF_SOFT', 'OFF_HARD', 'DELETED')",
'A_STATUS_INFO'=>"varchar(255) ",
'A_ADM_COMPANY'=>'varchar(100) ','A_CLASSIFIED'=>'varchar(100) '
);
//DEBUG_S(-3,'zapytanie',$sql,__FILE__,__FUNCTION__,__LINE__);
//$result=self::query($sql) or die1(' Problem ze sql '.$sql);
$result=self::show_tables($table);
while($h=self::fetch_array($result)) {
$res=self::show_tables($h[0].'_HIST');
if(self::num_rows($res)==0) {
//$CONNLOCAL->query($sql)
$sql='create table IF NOT EXISTS "'.$h[0].'_HIST"(
"ID" SERIAL PRIMARY KEY,
"ID_USERS2" integer NOT NULL,
"_action_time" timestamp without time zone,
"_action_type" character varying(16)
)';
DEBUG_S(-3,'create table hist '.$h[0],array($sql),__FILE__,__FUNCTION__,__LINE__);
$rescr=self::query($sql) or die1(' Problem ze sql '.$sql);
$sql=' CREATE INDEX "'.$h[0].'_ID_USERS2_index" ON "'.$h[0].'_HIST" ("ID_USERS2")';
$rescr=self::query($sql) or die1(' Problem ze sql '.$sql);
} else DEBUG_S(-3,'Histable '.$h[0].'_HIST existied',null,__FILE__,__FUNCTION__,__LINE__);
//kolumny z bazy istniejace
// $sql_="describe `".$h[0]."`";
// $result_=self::query($sql_) or die1(' Problem ze sql '.$sql_);
$result_=self::describe_table($table);
$result__=self::describe_table($table."_HIST");
while($h__=self::fetch($result__)) {
$hist_exists[$h__->Field]=true;
}
while($h_=self::fetch($result_)) {
$table_exists[$h_->Field]=true;
if(empty($hist_exists[$h_->Field])) {
DEBUG_S(-3,'dane z tabeli',$h_,__FILE__,__FUNCTION__,__LINE__);
//$sql="\n alter table \"".$h[0]."_HIST\" add \"".$h_->Field."\" ; " ;
//self::query($sql) or DEBUG_S(-3,' Problem ze sql '.$sql,array($sql),__FILE__,__FUNCTION__,__LINE__);
self::alter_add_struct($table."_HIST",$h_->Field," text default 'N/S;'");
$hist_exists[$h_->Field]=true;
}
}
foreach($USERS_COLUMN_INIT2['REQUEST_COLUMN'] as $tst_column=>$tst_type) {
if(empty($table_exists[$tst_column])) {
//$sql="\n alter table \"".$h[0]."\" add \"".$tst_column."\" ".$tst_type." ; " ;
//self::query($sql) or DEBUG_S(-3,' Problem ze sql '.$sql,array($sql),__FILE__,__FUNCTION__,__LINE__);
self::alter_add_struct($table,$tst_column,$tst_type);
if(empty($hist_exists[$tst_column])) {
// $sql="\n alter table \"".$h[0]."_HIST\" add \"".$tst_column."\" char(255) default 'N/S;' ; " ;
// self::query($sql) or DEBUG_S(-3,' Problem ze sql '.$sql,array($sql),__FILE__,__FUNCTION__,__LINE__);
self::alter_add_struct($table."_HIST",$tst_column,'char(255) default \'N/S;\'');
$hist_exists[$tst_column]=true;
}
}
}
}
//Auto trigger funciton for history and edits
$sql = '
CREATE OR REPLACE FUNCTION "'.$table.'_HIST_func"() RETURNS TRIGGER AS $body$
DECLARE
v_old_data TEXT;
v_new_data TEXT;
v_currval INT;
BEGIN
/* If this actually for real auditing (where you need to log EVERY action),
then you would need to use something like dblink or plperl that could log outside the transaction,
regardless of whether the transaction committed or rolled back.
*/
/* This dance with casting the NEW and OLD values to a ROW is not necessary in pg 9.0+ */
IF (TG_OP = \'UPDATE\') THEN
v_old_data := ROW(OLD.*);
v_new_data := ROW(NEW.*);
INSERT INTO "'.$table.'_HIST" ("ID_USERS2","_action_time","A_RECORD_UPDATE_AUTHOR","_action_type")
VALUES (NEW.'.$primary.',now(),session_user::TEXT,TG_OP);
v_currval := currval(\'"'.$table.'_HIST_ID_seq"\');
';
foreach($hist_exists as $column=>$true) {
if($column=='ID') continue;
if($column=='ID_USERS2') continue;
if($column=='A_RECORD_UPDATE_AUTHOR') continue;
if($column=='A_RECORD_UPDATE_DATE') continue;
if($column=='A_RECORD_CREATE_DATE') continue;
if($column=='A_RECORD_CREATE_AUTHOR') continue;
if($column=='_action_time') continue;
if($column=='_action_type') continue;
$sql.='
IF cast(ROW(OLD."'.$column.'")as text)!=cast(ROW(NEW."'.$column.'")as text) THEN
UPDATE "'.$table.'_HIST" set "'.$column.'"=NEW."'.$column.'" where "ID"=v_currval;
-- ELSE
-- UPDATE "'.$table.'_HIST" set "'.$column.'"=\'N/S;\' where "ID"=v_currval;
END IF;
';
}
$sql.='
-- UPDATE "'.$table.'" set "A_RECORD_UPDATE_DATE"=now(), "A_RECORD_UPDATE_AUTHOR"=session_user::TEXT where "'.$primary.'"=NEW."'.$primary.'";
RETURN NEW;
ELSIF (TG_OP = \'INSERT\') THEN
v_new_data := ROW(NEW.*);
END IF;
END;
$body$
LANGUAGE plpgsql
SECURITY DEFINER
';
DEBUG_S(-3,'Definied autohistory function',array($sql),__FILE__,__FUNCTION__,__LINE__);
self::query($sql) or die1($sql);
$res=self::show_create_trigger($table,$table.'_HIST_trigger');
if(self::num_rows($res)==0) {
$sql='CREATE TRIGGER "'.$table.'_HIST_trigger"
AFTER UPDATE ON "'.$table.'"
FOR EACH ROW EXECUTE PROCEDURE "'.$table.'_HIST_func"();';
DEBUG_S(-3,'Definied autohistory triggers',array($sql),__FILE__,__FUNCTION__,__LINE__);
self::query($sql) or die1($sql);
}
self::query("CREATE OR REPLACE FUNCTION update_A_RECORD_UPDATE_DATE_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.\"A_RECORD_UPDATE_DATE\" = now() at time zone 'utc';
NEW.\"A_RECORD_UPDATE_AUTHOR\" = session_user::TEXT;
RETURN NEW;
END;
$$ language 'plpgsql';");
$res=self::show_create_trigger($table,'update_A_RECORD_UPDATE_DATE');
if(self::num_rows($res)==0) {
self::query("CREATE TRIGGER \"update_A_RECORD_UPDATE_DATE\" BEFORE UPDATE
ON \"".$table."\" FOR EACH ROW EXECUTE PROCEDURE
update_A_RECORD_UPDATE_DATE_column();");
}
self::query("CREATE OR REPLACE FUNCTION create_A_RECORD_UPDATE_DATE_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.\"A_RECORD_CREATE_DATE\" = now() at time zone 'utc';
NEW.\"A_RECORD_CREATE_AUTHOR\" = session_user::TEXT;
RETURN NEW;
END;
$$ language 'plpgsql';");
$res=self::show_create_trigger($table,'create_A_RECORD_UPDATE_DATE');
if(self::num_rows($res)==0) {
self::query("CREATE TRIGGER \"create_A_RECORD_UPDATE_DATE\" BEFORE INSERT
ON \"".$table."\" FOR EACH ROW EXECUTE PROCEDURE
create_A_RECORD_UPDATE_DATE_column();");
}
//! IMPORT_SORT_DESC_INFO
}
}