1   /**
2    * Copyright (c) 2000-2008 Liferay, Inc. All rights reserved.
3    *
4    * Permission is hereby granted, free of charge, to any person obtaining a copy
5    * of this software and associated documentation files (the "Software"), to deal
6    * in the Software without restriction, including without limitation the rights
7    * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
8    * copies of the Software, and to permit persons to whom the Software is
9    * furnished to do so, subject to the following conditions:
10   *
11   * The above copyright notice and this permission notice shall be included in
12   * all copies or substantial portions of the Software.
13   *
14   * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15   * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16   * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
17   * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18   * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
19   * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
20   * SOFTWARE.
21   */
22  
23  package com.liferay.portal.tools;
24  
25  import Zql.ZConstant;
26  import Zql.ZInsert;
27  import Zql.ZStatement;
28  import Zql.ZqlParser;
29  
30  import com.liferay.portal.kernel.util.StringMaker;
31  import com.liferay.portal.kernel.util.StringPool;
32  import com.liferay.portal.kernel.util.StringUtil;
33  import com.liferay.portal.kernel.util.Validator;
34  import com.liferay.util.FileUtil;
35  
36  import java.io.BufferedReader;
37  import java.io.ByteArrayInputStream;
38  import java.io.StringReader;
39  
40  import java.sql.Connection;
41  import java.sql.DriverManager;
42  import java.sql.PreparedStatement;
43  import java.sql.Statement;
44  import java.sql.Timestamp;
45  
46  import java.util.List;
47  
48  /**
49   * <a href="DBLoader.java.html"><b><i>View Source</i></b></a>
50   *
51   * @author Brian Wing Shun Chan
52   *
53   */
54  public class DBLoader {
55  
56      public static void main(String[] args) {
57          if (args.length == 2) {
58              new DBLoader(args[0], args[1], StringPool.BLANK);
59          }
60          else if (args.length == 3) {
61              new DBLoader(args[0], args[1], args[2]);
62          }
63          else {
64              throw new IllegalArgumentException();
65          }
66      }
67  
68      public DBLoader(String databaseType, String databaseName, String fileName) {
69          try {
70              _databaseType = databaseType;
71              _databaseName = databaseName;
72              _fileName = fileName;
73  
74              if (_databaseType.equals("derby")) {
75                  _loadDerby();
76              }
77              else if (_databaseType.equals("hypersonic")) {
78                  _loadHypersonic();
79              }
80          }
81          catch (Exception e) {
82              e.printStackTrace();
83          }
84      }
85  
86      private PreparedStatement _getStatementDerby(Connection con, String sql)
87          throws Exception {
88  
89          sql = StringUtil.replace(
90              sql, "current timestamp", "'current timestamp'");
91  
92          sql += ";";
93  
94          ZqlParser zParser = new ZqlParser(
95              new ByteArrayInputStream(sql.getBytes()));
96  
97          ZStatement zStatement = zParser.readStatement();
98  
99          ZInsert zInsert = (ZInsert)zStatement;
100 
101         sql = "insert into " + zInsert.getTable() + " (";
102 
103         List<String> columns = zInsert.getColumns();
104 
105         for (int i = 0; i < columns.size(); i++) {
106             sql += columns.get(i);
107 
108             if ((i + 1) < columns.size()) {
109                 sql += ", ";
110             }
111         }
112 
113         sql += ") values (";
114 
115         for (int i = 0; i < columns.size(); i++) {
116             sql += "?";
117 
118             if ((i + 1) < columns.size()) {
119                 sql += ", ";
120             }
121         }
122 
123         sql += ")";
124 
125         PreparedStatement ps = con.prepareStatement(sql);
126 
127         List<ZConstant> values = zInsert.getValues();
128 
129         for (int i = 0; i < values.size(); i++) {
130             ZConstant zConstant = values.get(i);
131 
132             int pos = i + 1;
133 
134             String value = zConstant.getValue();
135 
136             if (value.equals("current timestamp")) {
137                 ps.setTimestamp(
138                     pos, new Timestamp(System.currentTimeMillis()));
139             }
140             else if (value.length() < 32000) {
141                 ps.setString(pos, zConstant.getValue());
142             }
143             else {
144                 ps.setCharacterStream(
145                     pos, new StringReader(value), value.length());
146             }
147         }
148 
149         return ps;
150     }
151 
152     private void _loadDerby() throws Exception {
153         Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
154 
155         Connection con = DriverManager.getConnection(
156             "jdbc:derby:" + _databaseName + ";create=true", "", "");
157 
158         if (Validator.isNull(_fileName)) {
159             _loadDerby(con, "../sql/portal/portal-derby.sql");
160             _loadDerby(con, "../sql/indexes.sql");
161         }
162         else {
163             _loadDerby(con, _fileName);
164         }
165     }
166 
167     private void _loadDerby(Connection con, String fileName)
168         throws Exception {
169 
170         StringMaker sm = new StringMaker();
171 
172         BufferedReader br = new BufferedReader(
173             new StringReader(FileUtil.read(fileName)));
174 
175         String line = null;
176 
177         while ((line = br.readLine()) != null) {
178             if (!line.startsWith("--")) {
179                 sm.append(line);
180 
181                 if (line.endsWith(";")) {
182                     String sql = sm.toString();
183 
184                     sql =
185                         StringUtil.replace(
186                             sql,
187                             new String[] {
188                                 "\\'",
189                                 "\\\"",
190                                 "\\\\",
191                                 "\\n",
192                                 "\\r"
193                             },
194                             new String[] {
195                                 "''",
196                                 "\"",
197                                 "\\",
198                                 "\n",
199                                 "\r"
200                             });
201 
202                     sql = sql.substring(0, sql.length() - 1);
203 
204                     sm = new StringMaker();
205 
206                     if (sql.startsWith("commit")) {
207                         continue;
208                     }
209 
210                     PreparedStatement ps = null;
211 
212                     if (sql.startsWith("insert into Image (") ||
213                         sql.startsWith("insert into JournalArticle (") ||
214                         sql.startsWith("insert into JournalStructure (") ||
215                         sql.startsWith("insert into JournalTemplate (") ||
216                         sql.startsWith("insert into Layout (") ||
217                         sql.startsWith("insert into PortletPreferences (")) {
218 
219                         // Derby isn't able to process long inserts. Zql parses
220                         // the SQL statement so that we can manually set the
221                         // insert statement. Zql also isn't able to properly
222                         // parse certain unicode characters.
223 
224                         sql = StringUtil.replace(
225                             sql,
226                             new String[] {
227                                 "\u0161",
228                                 "\u017e",
229                                 "\u2013",
230                                 "\u2014",
231                                 "\u2015",
232                                 "\u2019",
233                                 "\u2022",
234                                 "\u201c",
235                                 "\u2122"
236                             },
237                             new String[] {
238                                 StringPool.BLANK,
239                                 StringPool.BLANK,
240                                 StringPool.BLANK,
241                                 StringPool.BLANK,
242                                 StringPool.BLANK,
243                                 StringPool.BLANK,
244                                 StringPool.BLANK,
245                                 StringPool.BLANK,
246                                 StringPool.BLANK
247                             });
248 
249                         try {
250                             ps = _getStatementDerby(con, sql);
251                         }
252                         catch (Exception e) {
253                             System.out.println("Unable to parse " + sql);
254 
255                             e.printStackTrace();
256 
257                             throw e;
258                         }
259                     }
260                     else {
261                         ps = con.prepareStatement(sql);
262                     }
263 
264                     try {
265                         ps.executeUpdate();
266                     }
267                     catch (Exception e) {
268                         System.out.println("Unable to execute " + sql);
269 
270                         e.printStackTrace();
271 
272                         throw e;
273                     }
274                     finally {
275                         if (ps != null) {
276                             ps.close();
277                         }
278                     }
279                 }
280             }
281         }
282 
283         br.close();
284     }
285 
286     private void _loadHypersonic() throws Exception {
287         Class.forName("org.hsqldb.jdbcDriver");
288 
289         // See LEP-2927. Appending ;shutdown=true to the database connection URL
290         // guarantees that ${_databaseName}.log is purged.
291 
292         Connection con = DriverManager.getConnection(
293             "jdbc:hsqldb:" + _databaseName + ";shutdown=true", "sa", "");
294 
295         if (Validator.isNull(_fileName)) {
296             _loadHypersonic(con, "../sql/portal/portal-hypersonic.sql");
297             _loadHypersonic(con, "../sql/indexes.sql");
298         }
299         else {
300             _loadHypersonic(con, _fileName);
301         }
302 
303         // Shutdown Hypersonic
304 
305         Statement statement = con.createStatement();
306 
307         statement.execute("SHUTDOWN COMPACT");
308 
309         statement.close();
310 
311         con.close();
312 
313         // Hypersonic will encode unicode characters twice, this will undo
314         // it
315 
316         String content = FileUtil.read(_databaseName + ".script");
317 
318         content = StringUtil.replace(content, "\\u005cu", "\\u");
319 
320         FileUtil.write(_databaseName + ".script", content);
321     }
322 
323     private void _loadHypersonic(Connection con, String fileName)
324         throws Exception {
325 
326         StringMaker sm = new StringMaker();
327 
328         BufferedReader br = new BufferedReader(
329             new StringReader(FileUtil.read(fileName)));
330 
331         String line = null;
332 
333         while ((line = br.readLine()) != null) {
334             if (!line.startsWith("//")) {
335                 sm.append(line);
336 
337                 if (line.endsWith(";")) {
338                     String sql = sm.toString();
339 
340                     sql =
341                         StringUtil.replace(
342                             sql,
343                             new String[] {
344                                 "\\\"",
345                                 "\\\\",
346                                 "\\n",
347                                 "\\r"
348                             },
349                             new String[] {
350                                 "\"",
351                                 "\\",
352                                 "\\u000a",
353                                 "\\u000a"
354                             });
355 
356                     sm = new StringMaker();
357 
358                     PreparedStatement ps = con.prepareStatement(sql);
359 
360                     ps.executeUpdate();
361 
362                     ps.close();
363                 }
364             }
365         }
366 
367         br.close();
368     }
369 
370     private String _databaseType;
371     private String _databaseName;
372     private String _fileName;
373 
374 }