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";}
}
?>