postgres: IPS Objekte enumerieren

macht 2 Postgres-Tabellen, die alle IPS-Objekte (inkl. ihrer Parents) enthalten. Gut um Variablen zuzurordnen, welche man in Postgres verwenden will.


<?php
$IpsObjectList = IPS_GetObjectList();
$CountIpsObjects = count($IpsObjectList);
echo "Number :" . $CountIpsObjects. " ips objects "."<br>". chr(13);


$dbconn = pg_connect("host=x.x.x.x port=5432 dbname=IpsLogging user=xxx password=xxx") or die ('Verbindungsaufbau fehlgeschlagen: ' . pg_last_error());
echo "postgres connect - ". $dbconn . "<br>";
if ($dbconn==0) {die ("no odbc connection");}

//recreate IpsObjects
pg_query($dbconn, "DROP TABLE IpsObjects CASCADE;");
pg_query($dbconn, loadSqlDefinitions()["IpsObject_TableDef"]);


//restore any views that are dependent on the table being dropped/recreated.
pg_query($dbconn, loadSqlDefinitions()["ips_integerWithObjectname_ViewDef"]);
pg_query($dbconn, loadSqlDefinitions()["ips_booleanWithObjectname_ViewDef"]);
pg_query($dbconn, loadSqlDefinitions()["ips_floatWithObjectname_ViewDef"]);


$SqlExecuteString ="";
$SqlInsertString = "ChildrenIDs, HasChildren, IsPersistent, ObjectIcon, ObjectID, ObjectIdent, ObjectInfo, ObjectIsHidden, ObjectIsReadOnly, ObjectName, ObjectPosition, ObjectSummary, ObjectType, ParentID";

(string) $successfulInserts = "";


//iterate through IPS objects
$ObjectCounter = 0;
foreach ($IpsObjectList as $key1 => $value1)
	{
	$ObjectCounter +=1;
   //echo "Counter: ". $ObjectCounter;
	//echo "[$key1] => $value1"."<br>";
	
	$IpsObject=IPS_GetObject($value1);
		(string)$ChildrenIDs = utf8_encode(SqlNull(implode(",",$IpsObject['ChildrenIDs'])));
		$HasChildren = boolString($IpsObject['HasChildren']);
		//(bool)$IsPersistent= $IpsObject['IsPersistent'];
		$IsPersistent= 'NULL';
		(string)$ObjectIcon=utf8_encode(SqlNull($IpsObject['ObjectIcon']));
		(int)$ObjectID=SqlNull($IpsObject['ObjectID']);
		(string)$ObjectIdent=utf8_encode(SqlNull($IpsObject['ObjectIdent']));
		(string)$ObjectInfo=utf8_encode(SqlNull($IpsObject['ObjectInfo']));
		(bool)$ObjectIsHidden= boolString($IpsObject['ObjectIsHidden']);
		(bool)$ObjectIsReadOnly= boolString($IpsObject['ObjectIsReadOnly']);
		(string)$ObjectName=utf8_encode($IpsObject['ObjectName']);
		(string)$ObjectPosition=utf8_encode(SqlNull($IpsObject['ObjectPosition']));
		(string)$ObjectSummary=utf8_encode(SqlNull($IpsObject['ObjectSummary']));
		(int)$ObjectType=SqlNull($IpsObject['ObjectType']);
		(int)$ParentID=SqlNull($IpsObject['ParentID']);

$SqlValueString =
  "'".$ChildrenIDs."',".
  $HasChildren . ",".
  $IsPersistent . ",".
  "'" .$ObjectIcon."'" . ",".
  $ObjectID . ",".
  "'" .$ObjectIdent."'" . ",".
  "'".$ObjectInfo."'". ",".
  $ObjectIsHidden . ",".
  $ObjectIsReadOnly . ",".
  "'".$ObjectName. "'".",".
  $ObjectPosition . ",".
  "'".$ObjectSummary. "'".",".
  $ObjectType . ",".
  $ParentID;

$SqlExecuteString = "INSERT INTO IpsObjects ($SqlInsertString) VALUES ($SqlValueString);".chr(13);
echo $SqlExecuteString;
$QueryResult = pg_query($dbconn,  $SqlExecuteString);
if (!$QueryResult)
{
//  echo "A Postgres error occured:". pg_last_error();
	}
else
$successfulInserts .= $ObjectID . ",". $ObjectName.",".$ParentID.chr(13)."<br>";
}
//echo "<br>".$successfulInserts . "<br>";

pg_query($dbconn, loadSqlDefinitions()["ipsobjects_with_parent_ViewDef"]);
pg_close($dbconn);


//----------------------------------------------


function SqlNull($SqlValue)
	{
// insert the string "Null" into a null variable, for compatibility with Access SQL.
	if (((string)$SqlValue==""))
		{(string)$SqlValue='NULL';return ($SqlValue);}
	else
		{return ($SqlValue);}
	}
	
	
//-----------------------------------------------
function loadSqlDefinitions()
{
$sqlDefArray =array(
"IpsObject_TableDef" =>
"CREATE TABLE IpsObjects (
					CHILDRENIDS TEXT,
					HASCHILDREN BOOLEAN,
					ISPERSISTENT BOOLEAN,
					OBJECTICON TEXT,
					OBJECTID INTEGER UNIQUE,
					OBJECTIDENT TEXT,
					OBJECTINFO TEXT,
					OBJECTISHIDDEN BOOLEAN,
					OBJECTISREADONLY BOOLEAN,
					OBJECTNAME TEXT,
					OBJECTPOSITION TEXT,
					OBJECTSUMMARY TEXT,
					OBJECTTYPE INTEGER,
					PARENTID INTEGER,
					ID SERIAL,
                    PRIMARY KEY(ID)
                    );",

"ips_booleanWithObjectname_ViewDef"
=>
"CREATE OR REPLACE VIEW ips_booleanWithObjectname AS
 SELECT loggingdb_ips_boolean.varid,
    loggingdb_ips_boolean.ipstimestamp,
    loggingdb_ips_boolean.ipsvalue,
    ipsobjects.objectname,
    ipsobjects.objectid
   FROM loggingdb_ips_boolean
     LEFT JOIN ipsobjects ON loggingdb_ips_boolean.varid = ipsobjects.objectid
  ORDER BY loggingdb_ips_boolean.varid;

ALTER TABLE ips_booleanWithObjectname
  OWNER TO aag;",

"ips_floatWithObjectname_ViewDef"
=>
"CREATE OR REPLACE VIEW ips_floatWithObjectName AS
 SELECT loggingdb_ips_float.varid,
    loggingdb_ips_float.ipstimestamp,
    loggingdb_ips_float.ipsvalue,
    ipsobjects.objectname,
    ipsobjects.objectid
   FROM loggingdb_ips_float
     LEFT JOIN ipsobjects ON loggingdb_ips_float.varid = ipsobjects.objectid
  ORDER BY loggingdb_ips_float.varid;

ALTER TABLE ips_floatWithObjectName
  OWNER TO aag;",

"ips_integerWithObjectname_ViewDef"
=>
"CREATE OR REPLACE VIEW ips_integerWithObjectName AS
 SELECT loggingdb_ips_integer.varid,
    loggingdb_ips_integer.ipstimestamp,
    loggingdb_ips_integer.ipsvalue,
    ipsobjects.objectname,
    ipsobjects.objectid
   FROM loggingdb_ips_integer
     LEFT JOIN ipsobjects ON loggingdb_ips_integer.varid = ipsobjects.objectid
  ORDER BY loggingdb_ips_integer.varid;

ALTER TABLE ips_integerWithObjectName
  OWNER TO ips;",


"ipsobjects_with_parent_ViewDef"
=>
"DROP TABLE ipsobjects_with_parent;
CREATE TABLE ipsobjects_with_parent AS
 SELECT ipsobjects_1.objectname AS parentname,
    ipsobjects.childrenids,
    ipsobjects.haschildren,
    ipsobjects.ispersistent,
    ipsobjects.objecticon,
    ipsobjects.objectid,
    ipsobjects.objectident,
    ipsobjects.objectinfo,
    ipsobjects.objectishidden,
    ipsobjects.objectisreadonly,
    ipsobjects.objectname,
    ipsobjects.objectposition,
    ipsobjects.objectsummary,
    ipsobjects.objecttype,
    ipsobjects.parentid,
    ipsobjects.id

   FROM ipsobjects
     LEFT JOIN ipsobjects ipsobjects_1
	  ON ipsobjects.parentid = ipsobjects_1.objectid;

ALTER TABLE ipsobjects_with_parent
  OWNER TO ips;");

  
return $sqlDefArray;
}
//------------------------------------
function boolString($boolValue) {
	if ($boolValue == true)
	{return "TRUE";}
	else
	{return "FALSE";}

}

?>