Mercurial > hg > freeDiameter

<?php
# This file may be called either from CLI or web PHP installation.

# The copyright is the same as the freeDiameter project. Licence is BSD.

/*-------------------------------------------------------*/

/* -- These parameters must match the app_acct.conf file -- */

/* The Connection String used to access the database.
  Example: "host=localhost dbname=app_acct user=freediameter password=foo" */
$CONNSTR="";

/* The name of the table were the records are saved by app_acct.fdx */
$TABLE="incoming";


/* -- These parameters are specific to this script -- */

/* Name of the dump file to create. It can be relative or absolute.
   The script fails if this file already exists.
   Use for example "data-".date("Ymdhis").".sql" to avoid collisions. */
$DUMPFILE="incoming_data.sql";

/* Does the script also echoes an HTML TABLE of the data processed? */
$DISPLAY_HTML=FALSE; // it would be better to detect if we are called through Web or CLI...
/* In addition, issue a full HTML page (including headers) ? */
$HTML_HEADERS=TRUE;

/*-------------------------------------------------------*/

/* Connect to the database */
$dbconn = pg_connect($CONNSTR)
    or die('Could not connect: ' . pg_last_error() . "\n");

/* Check if the file exists */
$file = fopen($DUMPFILE, "xb")
    or die("The file '$DUMPFILE' already exists or cannot be created, aborting.\n");

/* First, query the table format */
$format_sql =   "SELECT ".
		"   a.attname AS field,".
		"   t.typname AS type,".
/*		"   a.attlen AS length,". */
		"   a.atttypmod AS lengthvar,".
		"   a.attnotnull AS notnull".
		" FROM".
		"   pg_class c,".
		"   pg_attribute a,".
		"   pg_type t".
		" WHERE".
		"   c.relname = '" . $TABLE . "'".
		"   AND a.attnum > 0".
		"   AND a.attrelid = c.oid".
		"   AND a.atttypid = t.oid".
		"   ORDER BY a.attnum";
$result = pg_query($dbconn, $format_sql) or die('Query failed: ' . pg_last_error() . "\n");

/* app_acct.fdx only uses a few different fields types: */
$conv_types = array(
    "timestamptz" => "timestamp with time zone", /* recorded on */
    "bytea" => "bytea",   /* octet string */
    "int4" => "integer",  /* unsigned32, integer32, float32 */
    "int8" => "bigint"    /* unsigned64, integer64, float64 */
    );

$fields_types=array();

fwrite($file, " -- Data purged on ".date(DATE_RFC822)."\n\n");
fwrite($file, " -- Format of the table it was extracted from:\n");
fwrite($file, " -- CREATE TABLE \"".$TABLE."\" (\n");
$i = 0;
while ($field = pg_fetch_array($result, null, PGSQL_ASSOC)) {
	if ($i++)
		fwrite($file, ",\n");
	fwrite($file, " --    \"".$field["field"]."\" ");
	if (array_key_exists($field["type"], $conv_types))
		$fields_types[$field["field"]] = $conv_types[$field["type"]];
	else
		$fields_types[$field["field"]] = $field["type"];
	fwrite($file, $fields_types[$field["field"]]);
	if ($field["lengthvar"] != "-1")
		fwrite($file, "(".$field["lengthvar"].")");
	if ($field["notnull"] == "t")
		fwrite($file, " NOT NULL");
}
fwrite($file, "\n -- );\n\n");
pg_free_result($result);

/* Now, the data */
$result = pg_query($dbconn, "SELECT * FROM \"".$TABLE."\"") or die('Query failed: ' . pg_last_error() . "\n");
if (pg_num_rows($result) > 0) {
	fwrite($file, "INSERT INTO \"".$TABLE."\"\n (\n");
	$i = pg_num_fields($result);
	if ($DISPLAY_HTML && $HTML_HEADERS) echo "<HTML>\n<HEAD><TITLE>Purge</TITLE></HEAD>\n<BODY>\n";
	if ($DISPLAY_HTML) echo "<TABLE>\n  <TR>\n";
	for ($j = 0; $j < $i; $j++) {
	      fwrite($file, ($j ? ", " : "") . "\"" . pg_escape_string(pg_field_name($result, $j)). "\"");
	      if ($DISPLAY_HTML) echo "    <TD>".htmlentities(pg_field_name($result, $j))."</TD>\n";
	}
	fwrite($file, "\n )\n VALUES \n");
	if ($DISPLAY_HTML) echo "  </TR>\n";
	$i = 0;
	while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
		if ($i++)
			fwrite($file, ",\n");
		fwrite($file, " ( ");

		if ($DISPLAY_HTML) echo "  <TR>\n";
		$j = 0;
		$sql = "";
		foreach ($line as $f => $v) {
			if (!is_null($v))
				switch ($fields_types[$f]) {
					case "bytea":
						$v = "E'".pg_escape_bytea(pg_unescape_bytea($v))."'";
						break;
					case "timestamp with time zone":
						$v = "E'".pg_escape_string($v)."'";
						break;
				}
			else
				$v = "NULL";

	      		if ($DISPLAY_HTML) echo "    <TD>".htmlentities(print_r($line[$f], TRUE))."</TD>\n";

			fwrite($file, ($j ? ", " : "") . $v);
			$sql .= ($j ? " AND " : "") . "\"".pg_escape_string($f)."\" ";
			if (is_null($line[$f]))
				$sql .= " IS NULL";
			else
				$sql .= " = " . $v;
			$j++;
		}
		fwrite($file, ")");
		if ($DISPLAY_HTML) echo "  </TR>\n";
		$res = pg_query( "DELETE FROM \"".$TABLE."\" WHERE ".$sql) or die('DELETE query failed: ' . pg_last_error() . "\n");
		pg_free_result($res);
	}
	fwrite($file, "\n;\n");
	if ($DISPLAY_HTML) echo "</TABLE>\n";
	if ($DISPLAY_HTML && $HTML_HEADERS) echo "</BODY>\n</HTML>\n";
        else echo $i." records have been successfully written to '".$DUMPFILE."' and removed from database.\n";
} else {
	if ($DISPLAY_HTML) echo "<p><em>No new record in the database</em></p>\n";
        else echo "No new record in the database, the generated file is empty.\n";
}
pg_free_result($result);

/* Closing connection */
pg_close($dbconn);

/* Closing the file */
fclose($file);


?>
author	Luke Mewburn <luke@mewburn.net>
date	Sun, 05 Apr 2020 08:27:37 +1000
parents	4cdf146f11d5
children