001 package hep.aida.ref.sql.Test;
002
003 import hep.aida.ref.sql.SQLTuple;
004 import hep.aida.ref.sql.SQLTupleFactory;
005 import hep.aida.ref.sql.Util;
006 import hep.aida.ref.sql.AIDAURL;
007 import hep.aida.ref.sql.SQLTupleException;
008
009 // SQL
010 import java.sql.Connection;
011 import java.sql.Statement;
012 import java.sql.ResultSet;
013 import java.sql.SQLException;
014
015 // AIDA
016 import hep.aida.IAnalysisFactory;
017 import hep.aida.ITree;
018 import hep.aida.ITuple;
019 import hep.aida.ITupleFactory;
020 import hep.aida.IEvaluator;
021
022 // Log4J
023 import org.apache.log4j.Logger;
024
025 // Java
026 import java.util.Properties;
027 import java.util.Enumeration;
028 import java.util.Random;
029 import java.io.InputStream;
030 import java.io.FileInputStream;
031 import java.io.IOException;
032 import java.io.FileNotFoundException;
033
034 /** <code>Write</code> writes one NTuple table.
035 * Database should exist, NTuple is created if
036 * needed, it is overwritten if it exists.
037 * <br>
038 * <code>Write</code> is an example to show reading API and
039 * capabilities of SQLTuple.
040 * <p><font color="#880088">
041 * <pre>
042 * $Log: Write.java,v $
043 * Revision 1.35 2007/05/23 16:38:45 hrivnac
044 * logical connections for Plotter; better UML
045 *
046 * Revision 1.34 2005/09/28 16:21:06 hrivnac
047 * code cleaning
048 *
049 * Revision 1.33 2004/10/22 15:33:00 hrivnac
050 * cleaned
051 *
052 * Revision 1.32 2004/10/20 23:02:29 hrivnac
053 * Types mapping simplified
054 *
055 * Revision 1.31 2004/07/06 14:24:50 hrivnac
056 * support for Oracle
057 *
058 * Revision 1.30 2004/05/22 15:12:59 hrivnac
059 * class id reformated
060 *
061 * Revision 1.29 2004/04/20 09:28:08 hrivnac
062 * bug fixes
063 *
064 * Revision 1.28 2004/04/16 15:32:58 hrivnac
065 * Type.properties to setup Type.class
066 *
067 * Revision 1.27 2004/04/15 08:49:02 hrivnac
068 * ColMan fixes
069 *
070 * Revision 1.26 2004/04/14 15:34:28 hrivnac
071 * changes required by ColMan
072 *
073 * Revision 1.25 2004/04/14 13:39:47 hrivnac
074 * 1.5 warnings fixed
075 *
076 * Revision 1.24 2004/04/13 15:45:54 hrivnac
077 * AIDA URL introduced
078 *
079 * Revision 1.23 2004/02/10 14:50:58 hrivnac
080 * JavaDoc tags completed
081 *
082 * Revision 1.22 2004/02/04 13:30:42 hrivnac
083 * - improvement of Enums internal mapping
084 * - general cleaning
085 *
086 * Revision 1.21 2003/11/24 15:13:22 hrivnac
087 * Logging improved.
088 *
089 * Revision 1.20 2003/11/20 17:21:58 hrivnac
090 * Java 1.5 natively supported, Log4J reporting improved.
091 *
092 * Revision 1.19 2003/11/05 19:46:22 hrivnac
093 * - FreeHEP 1.2.1
094 * - JAIDA 3.2.1
095 *
096 * Revision 1.1 2003/09/30 16:46:02 hrivnac
097 * - Writing test in TestWrite
098 * - SQLTuple projections supported
099 *
100 * </pre>
101 * </font></p>
102 * @opt attributes
103 * @opt operations
104 * @opt types
105 * @opt visibility
106 * @version $Id: Write.java,v 1.35 2007/05/23 16:38:45 hrivnac Exp $
107 * @author <a href="mailto:Julius.Hrivnac@cern.ch">J.Hrivnac</a> */
108 public class Write {
109
110 /** Initiate processing.
111 * @param args <ol>
112 * <li>The URL of existing database,</li>
113 * <li>the number of rows to write,</li>
114 * <li>the user name to be used,</li>
115 * <li>the user password to be used.</li>
116 * </ol> */
117 public static void main(String[] args) {
118 if (args.length != 4) {
119 log.fatal(Util.urlHelp("hep.aida.ref.sql.Test.Write <url> <rows> <user> <passwd>"));
120 System.exit(1);
121 }
122 try {
123 (new Write()).process(args[0], new Integer(args[1]), args[2], args[3]);
124 }
125 catch (Exception e) {
126 log.fatal(Util.report("*** Write failed ***", e), e);
127 }
128 }
129
130 /** Perform processing.
131 * @param url The URL of existing database.
132 * @param rows The number of rows to write.
133 * @param user The user name to be used.
134 * @param passwd The user password to be used.
135 * @throws SQLTuple if the operation can't be performed. */
136 public void process(String url,
137 int rows,
138 String user,
139 String passwd) throws SQLTupleException {
140
141 log.debug("Writing SQLTuple " + url + " as " + user + ", " + rows + " rows long");
142
143 // Parse URL
144 AIDAURL aidaurl = new AIDAURL(url);
145 String dbName = aidaurl.db();
146 String tableName = aidaurl.table();
147
148 // Create options
149 String options = "hep.aida.ref.sql.db=" + dbName + ";"
150 + "hep.aida.ref.sql.user=" + user + ";"
151 + "hep.aida.ref.sql.passwd=" + passwd;
152
153 // Create top-level factory (selection can be done
154 // by -Dhep.aida.IAnalysisFactory command-line option)
155 IAnalysisFactory af = IAnalysisFactory.create();
156
157 // Create Tree
158 ITree tree = null;
159 try {
160 tree = af.createTreeFactory().create(dbName, aidaurl.storeType(), false, true, options);
161 }
162 catch (IOException e) {
163 throw new SQLTupleException("Can't create Tree from following options: " + options, e);
164 }
165
166 // Create ITupleFactory
167 ITupleFactory tf = af.createTupleFactory(tree);
168
169 // Delete NTuple (if it exists)
170 if (tf instanceof SQLTupleFactory) {
171 ((SQLTupleFactory)tf).delete(url);
172 }
173
174 // Define and initialise NTuple
175 String columnString = "";
176 columnString += "boolean attboolean; ";
177 columnString += "byte attbyte; ";
178 columnString += "char attchar = 'a'; ";
179 columnString += "short attshort = 5; ";
180 columnString += "int attint = 6; ";
181 columnString += "long attlong = 7; ";
182 columnString += "float attfloat = 8.0; ";
183 columnString += "double attdouble = 9.0; ";
184 columnString += "java.lang.String token_string";
185 ITuple tuple = tf.create(tableName, tableName, columnString, options);
186 String[] indexes = {"attshort", "attint", "attlong"};
187
188 // Initialise Random number generator
189 Random random = new Random();
190
191 // Start time measure
192 long start = System.currentTimeMillis();
193
194 // Fill rows
195 byte[] bytes = new byte[1];
196 for (int j = 0; j < rows; j++) {
197 tuple.fill(0, random.nextBoolean());
198 random.nextBytes(bytes);
199 tuple.fill(1, bytes[0]);
200 tuple.fill(2, 'a');
201 tuple.fill(3, (short)bytes[0]);
202 tuple.fill(4, random.nextInt());
203 if (aidaurl.storeType().equals("sql") &&
204 aidaurl.protocol().equals("postgresql")) {
205 tuple.fill(5, random.nextInt());
206 }
207 else {
208 tuple.fill(5, random.nextLong());
209 }
210 tuple.fill(6, random.nextFloat());
211 tuple.fill(7, random.nextDouble());
212 tuple.fill(8, "blalabla" + random.nextInt());
213 tuple.addRow();
214 }
215
216 // Add column defined by Evaluator
217 log.debug("Adding new column floatdivdouble = attfloat / attdouble");
218 IEvaluator evaluator = tf.createEvaluator("attfloat / attdouble");
219 if (tuple instanceof SQLTuple) {
220 ((SQLTuple)tuple).addColumn("floatdivdouble", Float.TYPE, evaluator);
221 }
222
223 // Quatify
224 int xrows = tuple.rows();
225
226 // Commit and close Tree
227 try {
228 tree.commit();
229 tree.close();
230 }
231 catch (IOException e) {
232 throw new SQLTupleException("Can't commit and close Tree", e);
233 }
234
235 // Report spent time and results
236 double delta = (System.currentTimeMillis() - start) / 1000.0;
237 log.info(xrows + " rows created in " + delta + " s");
238
239 }
240
241 /** Logging . */
242 private static Logger log = Logger.getLogger(Write.class);
243
244 }
245