001: package simpleorm.simplewebapp.eg.dbute;
002:
003: import simpleorm.core.*;
004: import simpleorm.simplewebapp.eg.dbute.WUser;
005: import simpleorm.simplewebapp.core.WPage;
006: import simpleorm.simplewebapp.core.WException;
007: import simpleorm.simplewebapp.context.WPageContext;
008:
009: import java.sql.*;
010:
011: public class WCreateDB {
012: static public String rawUrl = null;
013: static boolean proxy = false;
014:
015: /**
016: * Used by all other SimpleORM tests.
017: */
018: public static void dbConnect() throws Exception {
019:
020: // Daffodil does not work, create table syntax, SimpleORM.
021: //Class.forName("in.co.daffodil.db.jdbc.DaffodilDBDriver");
022: //SDataSource ds = new SDataSource("jdbc:daffodilDB_embedded:drivertestdb;create=true;user=daffodil;password=daffodil");
023: SConnection.attach(getDataSource(), "TestCon");
024: }
025:
026: public static SDataSource getDataSource() {
027: try {
028: SDataSource ds = null;
029:
030: if (!proxy) {// hsql todo unify with openJdbc
031: rawUrl = "jdbc:hsqldb:mem:hsqlTempFiles:shutdown=true";
032: Class.forName("org.hsqldb.jdbcDriver");
033: ds = new SDataSource(rawUrl, "sa", "");
034: }
035:
036: if (proxy) {// daffodil
037: rawUrl = "jdbc:daffodilDB_embedded:drivertestdb;create=true;user=daffodil;password=daffodil";
038: Class
039: .forName("in.co.daffodil.db.jdbc.DaffodilDBDriver");
040: ds = new SDataSource(rawUrl);
041: }
042:
043: if (proxy) {// JDBC Driver
044: Class.forName("rsa.edp.driver.jdbc.RsaProxyDriver");
045: ds = new SDataSource("jdbc:rsaProxy::"
046: + rawUrl.substring(5));
047: }
048:
049: return ds;
050: } catch (Exception ex) {
051: throw new WException(ex);
052: }
053: }
054:
055: public static Connection openJdbc() throws Exception { // todo unify with dbConnect
056: if (!proxy) {
057: rawUrl = "jdbc:hsqldb:mem:hsqlTempFiles:shutdown=true";
058: Class.forName("org.hsqldb.jdbcDriver");
059: return DriverManager.getConnection(rawUrl, "sa", "");
060: }
061: return null;
062: }
063:
064: static void createTables() {
065: createUserTable();
066: }
067:
068: private static void createUserTable() {
069: WUserCustomize.dummyLoadClass();
070:
071: SConnection.begin();
072: SConnection.dropTableNoError("RSA_USER");
073: SConnection.rawUpdateDB(WUser.meta.createTableSQL());
074: SConnection.commit();
075:
076: if (false)
077: throw new RuntimeException("test");
078: SConnection.begin();
079: SDataLoader userDL = new SDataLoader(WUser.meta,
080: new SFieldMeta[] { WUser.NAME, WUser.ROLE,
081: WUser.LOCATION, WUser.PHONE, WUser.HAPPY,
082: WUser.CREDIT_CARD, WUser.SOCIAL_SECURITY_NR,
083: WUser.MOTHER_MAIDEN });
084: WUser users[] = (WUser[]) userDL
085: .insertRecords(new Object[][] {
086: { "Alice Smith", "Thinker", "Here",
087: "123 45678", true,
088: "1234 5678 9012 3456", "123-45-6789",
089: "Green" },
090: { "Bob Brown", "Thinker", "There", "234 5678",
091: false, "2234 5678 9012 3456",
092: "223-45-6789", "Brown" },
093: { "Charley Smith", "Manager", "Here",
094: "234 1234", true,
095: "3234 5678 9012 3456", "323-45-6789",
096: "Red" },
097: { "Debbie Jones", "Manager", "There",
098: "012 3456", false,
099: "1234 5678 9012 3456", "123-45-6789",
100: "Green" },
101: { "Edward Smith", "Doer", "There", "001 5678",
102: true, "1234 5678 9012 3456",
103: "123-45-6789", "Green" },
104: { "Fiona Brown", "Doer", "There", "888 5678",
105: true, "1234 5678 9012 3456",
106: "123-45-6789", "Green" },
107: { "George Smith", "Doer", "Here", "777 5678",
108: false, "1234 5678 9012 3456",
109: "123-45-6789", "Green" },
110: { "Harry Jones", "Doer", "Here", "000 5678",
111: false, "1234 5678 9012 3456",
112: "123-45-6789", "Green" }, });
113: SConnection.commit();
114: }
115:
116: static public void doDbInit(WPage web) throws Exception {
117:
118: web.getLogger().info("Creating Tables...");
119: createTables();
120:
121: //dumpRaw(web, "RSA_USER");
122: // if (proxy) encryptColumns(web);
123:
124: SConnection.begin();
125: SResultSet res = WUser.meta.newQuery().execute();
126: while (res.hasNext()) {
127: WUser usr = (WUser) res.getRecord();
128: // Out is dirty here -- Form has not started with <HTML> etc. BUT Ok for a simple hack.
129: web.getLogger().info(
130: "WUser " + usr.getString(WUser.USER_ID) + ", "
131: + usr.getString(WUser.NAME) + ", "
132: + usr.getString(WUser.PHONE) + ", "
133: + usr.getString(WUser.CREDIT_CARD) + ", "
134: + usr.getString(WUser.SOCIAL_SECURITY_NR));
135: }
136: SConnection.commit();
137:
138: //dumpRaw(web, "RSA_USER");
139: web.getLogger().info("done DbIniting...");
140: }
141:
142: // static void encryptColumns(WPage web) throws Exception {
143: // SConnection.begin();
144: // DConnection con = (DConnection) SConnection.getBegunDBConnection();
145: // DTableEncrypter etab = new DTableEncrypter(con, "RSA_USER");
146: //
147: // //etab.setColumnKey( "NAME", "KEY1");
148: // etab.setColumnKey("SOCIAL_SECURITY", "KEY2");
149: // etab.setColumnKey("CREDIT_CARD", "KEY3");
150: //
151: // etab.alterTableAddEnc();
152: // con.commit();
153: //
154: // etab.encryptTableRows();
155: // con.commit();
156: //
157: // etab.alterTableDropEnc();
158: //
159: // SConnection.commit();
160: // }
161:
162: static public void dumpRaw(WPage web, String table)
163: throws Exception {
164: WPageContext pc = web.getPageContext();
165: pc.println("==== raw data " + table + " ====");
166: PreparedStatement ps = null;
167: ResultSet rs = null;
168: Connection con = DriverManager.getConnection(rawUrl);
169: String query = "SELECT * FROM " + table;
170: ps = con.prepareStatement(query);
171: //ps.setString(px+1, params[px]);
172: rs = ps.executeQuery();
173: int cols = rs.getMetaData().getColumnCount();
174: String head = "";
175: for (int mx = 0; mx < cols; mx++)
176: head += rs.getMetaData().getColumnName(mx + 1) + ", ";
177: pc.println(head);
178: while (rs.next()) {
179: String line = "";
180: for (int cx = 0; cx < cols; cx++)
181: line += rs.getString(cx + 1) + ", ";
182: pc.println(line);
183: }
184: rs.close();
185: ps.close();
186: con.close();
187: }
188: }
|