1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 package org.miloss.fgsms.tools;
21
22 import java.io.PrintWriter;
23 import java.sql.Connection;
24 import java.sql.Driver;
25 import java.sql.DriverManager;
26 import java.sql.PreparedStatement;
27 import java.sql.ResultSet;
28 import java.util.ArrayList;
29 import java.util.List;
30 import org.apache.commons.cli.CommandLine;
31 import org.apache.commons.cli.CommandLineParser;
32 import org.apache.commons.cli.DefaultParser;
33 import org.apache.commons.cli.Options;
34 import org.miloss.fgsms.common.Constants;
35 import org.miloss.fgsms.common.Utility;
36
37
38
39
40
41
42
43
44 public class DatabaseExport {
45
46 public static void main(String[] args) throws Exception {
47 System.out.println("For best results, stop the FGSMS server(s)");
48 String database = "fgsms_performance";
49 String url = "jdbc:postgresql://localhost:5432/";
50 String username = "fgsms";
51 String password = "";
52 String outputFile = "export";
53 Options options = new Options();
54 options.addOption("u", true, "username, if not specified, fgsms will be used");
55 options.addOption("p", true, "password, if not specified, you will be prompted");
56 options.addOption("db", true, "database to export, default is fgsms_performance but can also be fgsms_config");
57 options.addOption("server", true, "the jdbc connection url to the database server, default is jdbc:postgresql://localhost:5432/");
58 options.addOption("output", true, "output file name, if not specified, export_databasename.sql will be used");
59
60 CommandLineParser parser = new DefaultParser();
61 CommandLine cmd = parser.parse(options, args);
62
63 if (cmd.hasOption("server")) {
64 url = cmd.getOptionValue("server");
65 }
66 if (cmd.hasOption("u")) {
67 username = cmd.getOptionValue("u");
68 }
69 if (cmd.hasOption("p")) {
70 password = cmd.getOptionValue("p");
71 } else {
72 password = getStringPw();
73 }
74 if (cmd.hasOption("db")) {
75 database = cmd.getOptionValue("db");
76 }
77 if (cmd.hasOption("output")) {
78 outputFile = cmd.getOptionValue("output");
79 } else {
80 outputFile = outputFile + "_" + database + ".sql";
81 }
82
83 Driver d = (Driver) Class.forName("org.postgresql.Driver").newInstance();
84 DriverManager.registerDriver(d);
85 Connection con = DriverManager.getConnection(url + database, username, password);
86
87 PrintWriter fos = new PrintWriter(outputFile,Constants.CHARSET);
88
89 exportPerformance(fos, con);
90 System.out.println(outputFile + " can now be imported into another postgres instance using the following command");
91 System.out.println("## makes 2 test databases with all the tables and indexes");
92 System.out.println("> psql -U postgres -f db_test.sql");
93 System.out.println("## import the data");
94 System.out.println("> psql -U postgres -d (target database) -f " + outputFile);
95 System.out.println("example > psql -U postgres -d fgsms_performance_test -f " + outputFile);
96
97 }
98
99 private static String getStringPw() {
100 System.out.print("Password = ");
101 return new String(System.console().readPassword());
102 }
103
104 static String[] rawDataEncryptedColumns = new String[]{
105 "requestheaders",
106 "responseheaders",
107 "requestxml",
108 "responsexml", "memo"};
109
110 private static void exportPerformance(PrintWriter fos, Connection con) throws Exception {
111
112 PreparedStatement prepareStatement = con.prepareStatement("SELECT table_name FROM information_schema.tables WHERE table_schema='public'");
113 ResultSet executeQuery = prepareStatement.executeQuery();
114 List<String> tableNames = new ArrayList<String>();
115 while (executeQuery.next()) {
116 String t = executeQuery.getString(1);
117 System.out.println("preparing table " + t);
118 tableNames.add(t);
119 }
120
121 List<Table> tables = new ArrayList<Table>();
122 executeQuery.close();
123 prepareStatement.close();
124 for (int i = 0; i < tableNames.size(); i++) {
125 prepareStatement = con.prepareStatement("SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = 'public' AND table_name = ?");
126 prepareStatement.setString(1, tableNames.get(i));
127 executeQuery = prepareStatement.executeQuery();
128 Table t = new Table();
129 t.name = tableNames.get(i);
130 while (executeQuery.next()) {
131 Column c = new Column();
132 c.name = executeQuery.getString("column_name");
133 String type = executeQuery.getString("data_type");
134 if (type.equalsIgnoreCase("bytea")) {
135 c.type = JDBCType.BYTEA;
136 }
137 if (type.equals("integer")) {
138 c.type = JDBCType.INTEGER;
139 }
140 if (type.equals("bigint")) {
141 c.type = JDBCType.BIGINT;
142 }
143 if (type.equals("text")) {
144 c.type = JDBCType.VARCHAR;
145 }
146 if (type.equals("boolean")) {
147 c.type = JDBCType.BOOLEAN;
148 }
149 if (type.equals("double precision")) {
150 c.type = JDBCType.DOUBLE;
151 }
152 t.columns.add(c);
153 if (t.name.equalsIgnoreCase("rawdata")) {
154 for (int x = 0; x < rawDataEncryptedColumns.length; x++) {
155 if (c.name.equalsIgnoreCase(rawDataEncryptedColumns[x])) {
156 c.isFgsmsEncrypted = true;
157 break;
158 }
159 }
160 }
161
162 }
163 executeQuery.close();
164 prepareStatement.close();
165 System.out.println("table schema scanned and prepared");
166 tables.add(t);
167
168 }
169
170 for (int i = 0; i < tables.size(); i++) {
171 System.out.println("exporting table " + tables.get(i).name);
172 exportTable(tables.get(i), con, fos);
173 }
174 fos.flush();
175 fos.close();
176
177
178 }
179
180 private static void exportTable(Table table, Connection con, PrintWriter fos) throws Exception {
181 fos.println();
182 fos.println();
183 fos.println();
184 PreparedStatement prepareStatement = con.prepareStatement("select * from " + table.name + " ");
185
186 StringBuilder sb = new StringBuilder("insert into " + table.name + "(");
187 for (int i = 0; i < table.columns.size(); i++) {
188 sb.append(table.columns.get(i).name).append(",");
189 }
190 sb.deleteCharAt(sb.length() - 1);
191 sb.append(") VALUES (");
192 final String header = sb.toString();
193 prepareStatement.close();
194
195 ResultSet executeQuery = prepareStatement.executeQuery();
196 while (executeQuery.next()) {
197 fos.write(header);
198 for (int i = 0; i < table.columns.size(); i++) {
199
200 switch (table.columns.get(i).type) {
201 case BIGINT: {
202 long val = executeQuery.getLong(table.columns.get(i).name);
203 fos.write(val + "");
204 }
205 break;
206 case INTEGER: {
207 int vali = executeQuery.getInt(table.columns.get(i).name);
208 fos.write(vali + "");
209 }
210 break;
211 case BOOLEAN: {
212 boolean valb = executeQuery.getBoolean(table.columns.get(i).name);
213 fos.write(valb + "");
214 }
215 break;
216 case VARCHAR: {
217 String vals = executeQuery.getString(table.columns.get(i).name);
218 fos.write("'" + escape(vals) + "'");
219 }
220 break;
221 case DOUBLE: {
222 double vals = executeQuery.getDouble(table.columns.get(i).name);
223 fos.write(vals + "");
224 }
225 break;
226 case BYTEA: {
227 if (table.columns.get(i).isFgsmsEncrypted) {
228 byte[] bits = executeQuery.getBytes(table.columns.get(i).name);
229 if (bits != null) {
230 String clear = Utility.DE(new String(bits));
231 bits = clear.getBytes(Constants.CHARSET);
232 fos.write("E'\\x" + bytesToHex(bits) + "'");
233 } else {
234 fos.write("NULL");
235 }
236
237 } else {
238
239 String vals = new String(executeQuery.getBytes(table.columns.get(i).name));
240 fos.write("E'\\x" + bytesToHex(vals.getBytes()) + "'");
241 }
242 }
243 break;
244 default:
245 throw new Exception("unhandled case for " + table.name + "," + table.columns.get(i).name);
246 }
247 if (i + 1 != table.columns.size()) {
248 fos.write(",");
249 }
250 }
251
252 fos.write(");\n");
253 }
254 executeQuery.close();
255 prepareStatement.close();
256
257 }
258
259 final protected static char[] hexArray = "0123456789ABCDEF".toCharArray();
260
261
262
263
264
265
266 public static String bytesToHex(byte[] bytes) {
267 char[] hexChars = new char[bytes.length * 2];
268 for (int j = 0; j < bytes.length; j++) {
269 int v = bytes[j] & 0xFF;
270 hexChars[j * 2] = hexArray[v >>> 4];
271 hexChars[j * 2 + 1] = hexArray[v & 0x0F];
272 }
273 return new String(hexChars);
274 }
275
276 private static String escape(String vals) {
277 if (vals == null) {
278 return "";
279 }
280 if (vals.endsWith("\\"))
281 {
282 vals = vals + "\\";
283 }
284 return vals.replace("'", "\\'");
285 }
286 }