Gitiles
Code Review Sign In
gerrit.opencord.org / freeDiameter-old / 13b0e7de0d66906d50e074a339f890d6e59813ad / . / contrib / app_acct_tools / purge_to_file.php
blob: 0fc9f29f787c4a61c08fb5928f30961a2df2928b [file] [log] [blame]
Brian Waters13d96012017-12-08 16:53:31 -0600[diff] [blame]1<?php
2# This file may be called either from CLI or web PHP installation.
3
4# The copyright is the same as the freeDiameter project. Licence is BSD.
5
6/*-------------------------------------------------------*/
7
8/* -- These parameters must match the app_acct.conf file -- */
9
10/* The Connection String used to access the database.
11 Example: "host=localhost dbname=app_acct user=freediameter password=foo" */
12$CONNSTR="";
13
14/* The name of the table were the records are saved by app_acct.fdx */
15$TABLE="incoming";
16
17
18/* -- These parameters are specific to this script -- */
19
20/* Name of the dump file to create. It can be relative or absolute.
21 The script fails if this file already exists.
22 Use for example "data-".date("Ymdhis").".sql" to avoid collisions. */
23$DUMPFILE="incoming_data.sql";
24
25/* Does the script also echoes an HTML TABLE of the data processed? */
26$DISPLAY_HTML=FALSE; // it would be better to detect if we are called through Web or CLI...
27/* In addition, issue a full HTML page (including headers) ? */
28$HTML_HEADERS=TRUE;
29
30/*-------------------------------------------------------*/
31
32/* Connect to the database */
33$dbconn = pg_connect($CONNSTR)
34 or die('Could not connect: ' . pg_last_error() . "\n");
35
36/* Check if the file exists */
37$file = fopen($DUMPFILE, "xb")
38 or die("The file '$DUMPFILE' already exists or cannot be created, aborting.\n");
39
40/* First, query the table format */
41$format_sql = "SELECT ".
42 " a.attname AS field,".
43 " t.typname AS type,".
44/* " a.attlen AS length,". */
45 " a.atttypmod AS lengthvar,".
46 " a.attnotnull AS notnull".
47 " FROM".
48 " pg_class c,".
49 " pg_attribute a,".
50 " pg_type t".
51 " WHERE".
52 " c.relname = '" . $TABLE . "'".
53 " AND a.attnum > 0".
54 " AND a.attrelid = c.oid".
55 " AND a.atttypid = t.oid".
56 " ORDER BY a.attnum";
57$result = pg_query($dbconn, $format_sql) or die('Query failed: ' . pg_last_error() . "\n");
58
59/* app_acct.fdx only uses a few different fields types: */
60$conv_types = array(
61 "timestamptz" => "timestamp with time zone", /* recorded on */
62 "bytea" => "bytea", /* octet string */
63 "int4" => "integer", /* unsigned32, integer32, float32 */
64 "int8" => "bigint" /* unsigned64, integer64, float64 */
65 );
66
67$fields_types=array();
68
69fwrite($file, " -- Data purged on ".date(DATE_RFC822)."\n\n");
70fwrite($file, " -- Format of the table it was extracted from:\n");
71fwrite($file, " -- CREATE TABLE \"".$TABLE."\" (\n");
72$i = 0;
73while ($field = pg_fetch_array($result, null, PGSQL_ASSOC)) {
74 if ($i++)
75 fwrite($file, ",\n");
76 fwrite($file, " -- \"".$field["field"]."\" ");
77 if (array_key_exists($field["type"], $conv_types))
78 $fields_types[$field["field"]] = $conv_types[$field["type"]];
79 else
80 $fields_types[$field["field"]] = $field["type"];
81 fwrite($file, $fields_types[$field["field"]]);
82 if ($field["lengthvar"] != "-1")
83 fwrite($file, "(".$field["lengthvar"].")");
84 if ($field["notnull"] == "t")
85 fwrite($file, " NOT NULL");
86}
87fwrite($file, "\n -- );\n\n");
88pg_free_result($result);
89
90/* Now, the data */
91$result = pg_query($dbconn, "SELECT * FROM \"".$TABLE."\"") or die('Query failed: ' . pg_last_error() . "\n");
92if (pg_num_rows($result) > 0) {
93 fwrite($file, "INSERT INTO \"".$TABLE."\"\n (\n");
94 $i = pg_num_fields($result);
95 if ($DISPLAY_HTML && $HTML_HEADERS) echo "<HTML>\n<HEAD><TITLE>Purge</TITLE></HEAD>\n<BODY>\n";
96 if ($DISPLAY_HTML) echo "<TABLE>\n <TR>\n";
97 for ($j = 0; $j < $i; $j++) {
98 fwrite($file, ($j ? ", " : "") . "\"" . pg_escape_string(pg_field_name($result, $j)). "\"");
99 if ($DISPLAY_HTML) echo " <TD>".htmlentities(pg_field_name($result, $j))."</TD>\n";
100 }
101 fwrite($file, "\n )\n VALUES \n");
102 if ($DISPLAY_HTML) echo " </TR>\n";
103 $i = 0;
104 while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
105 if ($i++)
106 fwrite($file, ",\n");
107 fwrite($file, " ( ");
108
109 if ($DISPLAY_HTML) echo " <TR>\n";
110 $j = 0;
111 $sql = "";
112 foreach ($line as $f => $v) {
113 if (!is_null($v))
114 switch ($fields_types[$f]) {
115 case "bytea":
116 $v = "E'".pg_escape_bytea(pg_unescape_bytea($v))."'";
117 break;
118 case "timestamp with time zone":
119 $v = "E'".pg_escape_string($v)."'";
120 break;
121 }
122 else
123 $v = "NULL";
124
125 if ($DISPLAY_HTML) echo " <TD>".htmlentities(print_r($line[$f], TRUE))."</TD>\n";
126
127 fwrite($file, ($j ? ", " : "") . $v);
128 $sql .= ($j ? " AND " : "") . "\"".pg_escape_string($f)."\" ";
129 if (is_null($line[$f]))
130 $sql .= " IS NULL";
131 else
132 $sql .= " = " . $v;
133 $j++;
134 }
135 fwrite($file, ")");
136 if ($DISPLAY_HTML) echo " </TR>\n";
137 $res = pg_query( "DELETE FROM \"".$TABLE."\" WHERE ".$sql) or die('DELETE query failed: ' . pg_last_error() . "\n");
138 pg_free_result($res);
139 }
140 fwrite($file, "\n;\n");
141 if ($DISPLAY_HTML) echo "</TABLE>\n";
142 if ($DISPLAY_HTML && $HTML_HEADERS) echo "</BODY>\n</HTML>\n";
143 else echo $i." records have been successfully written to '".$DUMPFILE."' and removed from database.\n";
144} else {
145 if ($DISPLAY_HTML) echo "<p><em>No new record in the database</em></p>\n";
146 else echo "No new record in the database, the generated file is empty.\n";
147}
148pg_free_result($result);
149
150/* Closing connection */
151pg_close($dbconn);
152
153/* Closing the file */
154fclose($file);
155
156
157?>