Mercurial > hg > freeDiameter

comparison contrib/app_acct_tools/purge_to_file.php @ 533:4cdf146f11d5

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added a set of example PHP scripts to parse the app_acct.fdx data.
author Sebastien Decugis <sdecugis@nict.go.jp>
date Tue, 07 Sep 2010 16:57:48 +0900
parents
children
comparison
equal deleted inserted replaced
532:4cb8f63a0f67 533:4cdf146f11d5
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
69 fwrite($file, " -- Data purged on ".date(DATE_RFC822)."\n\n");
70 fwrite($file, " -- Format of the table it was extracted from:\n");
71 fwrite($file, " -- CREATE TABLE \"".$TABLE."\" (\n");
72 $i = 0;
73 while ($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 }
87 fwrite($file, "\n -- );\n\n");
88 pg_free_result($result);
89
90 /* Now, the data */
91 $result = pg_query($dbconn, "SELECT * FROM \"".$TABLE."\"") or die('Query failed: ' . pg_last_error() . "\n");
92 if (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 }
148 pg_free_result($result);
149
150 /* Closing connection */
151 pg_close($dbconn);
152
153 /* Closing the file */
154 fclose($file);
155
156
157 ?>
"Welcome to our mercurial repository"