001: package simpleorm.examples;
002:
003: import simpleorm.core.*; // .* OK, all classes prefixed with "S".
004: import java.util.HashMap;
005: import java.util.ArrayList;
006:
007: /** Similar to ADemo tests, but with more assertions.
008: */
009:
010: public class BasicTests implements SConstants {
011:
012: public static void main(String[] argv) throws Exception {
013: // SLog.level = 20; //Uncomment to reduce trace output.
014: TestUte.initializeTest("BasicTests", true); // use deprecated attach.
015: try {
016: createDeptEmp();
017: deptTest();
018: empTest();
019: metaTest();
020: queryTest();
021: } finally {
022: SConnection.detachAndClose();
023: }
024: }
025:
026: /** Prepare for tests, Delete old data. */
027: static void createDeptEmp() throws Exception {
028: SLog.slog
029: .message("################ CreateDeptEmp #################");
030: SConnection.begin();
031:
032: TestUte.dropAllTables();
033: SConnection.rawUpdateDB(Department.meta.createTableSQL());
034: SConnection.rawUpdateDB(Employee.meta.createTableSQL());
035:
036: SConnection.commit();
037: }
038:
039: /** Basic examples/tests not involving foreign keys. */
040: static void deptTest() throws Exception {
041: SLog.slog
042: .message("################ deptTest #################");
043: SConnection.begin();
044:
045: /// Create some Departments using the SDataLoader
046: SDataLoader deptDL = new SDataLoader(Department.meta);
047: Department d2 = (Department) deptDL.insertRecord(new String[] {
048: "100", "One00XX", "Count Pennies", "10000", "200000" });
049: SConnection.flush(); // SQL Insert
050: d2.setString(d2.NAME, "One00"); // Causes SQL Update.
051: deptDL
052: .insertRecords(new Object[][] {
053: { "200", "Two00", "Be Happy", "20000", "150000" },
054: { "300", "Three00", "Enjoy Life", "30000",
055: "300000" } });
056:
057: SConnection.commit();
058: SConnection.begin();
059:
060: /// Retrieve a Department and check that we have the right one.
061: String key = "100";
062: Department department = (Department) Department.meta
063: .findOrCreate(key);
064: department.assertNotNewRow();
065:
066: /// Retrieve the Name using explicit meta data.
067: HashMap deptMap = (HashMap) Department.meta
068: .getProperty(SFIELD_MAP);
069: SFieldMeta deptName = (SFieldMeta) deptMap.get("NAME");
070: SFieldMeta deptName2 = Department.meta.getField("NAME");
071: TestUte.assertTrue(deptName == deptName2);
072: String name = department.getString(deptName);
073: if (!"One00".equals(name))
074: throw new Exception("Dept Name " + name);
075: TestUte.assertEqual("NAME", Department.NAME
076: .getString(SSimpleORMProperties.SCOLUMN_NAME));
077:
078: /// Query the same Department again. This does not query the database.
079: String oo = "1";
080: String key2 = oo + "00"; // don't want it == key
081: TestUte.assertTrue(!department.wasInCache());
082: Department department2 = (Department) Department.meta
083: .findOrCreate(key2);
084: TestUte.assertTrue(department2.wasInCache());
085: if (department != department2)
086: throw new Exception("Departments not identical"
087: + department + department2);
088:
089: /// Create a new, empty department and set some column values.
090: Department newDept = (Department) Department.meta
091: .findOrCreate("900");
092: newDept.assertNewRow();
093: newDept.setString(Department.NAME, "New900");
094: newDept.setString(Department.BUDGET, "90000");
095:
096: SConnection.flush();
097:
098: /// At this point there should be three departments with > $10,000.
099: selectDepartments(20000 + 30000 + 90000);
100:
101: /// Rollback new 900 Department. Flush() does not mean commit.
102: SConnection.rollback();
103:
104: SConnection.begin();
105:
106: /// Delete Department 300
107: Department delDept = (Department) Department.meta
108: .findOrCreate("300");
109: delDept.deleteRecord();
110:
111: /// Insert and then Delete Department 500 -- ie do nothing.
112: Department insDelDept = (Department) Department.meta
113: .findOrCreate("500");
114: insDelDept.setString(Department.BUDGET, "666");
115: insDelDept.deleteRecord(); // No SQL generated.
116:
117: SConnection.commit();
118: SConnection.begin();
119:
120: /// Check only one department left > $10,000.
121: selectDepartments(20000);
122: selectDepartments(20000 + 10);
123:
124: SConnection.commit();
125: SConnection.begin();
126:
127: selectDepartments(20000 + 20); // Now updated.
128:
129: SArrayList al = Department.meta
130: .select("BUDGET > 10000", "NAME").execute()
131: .getArrayList(1000);
132: if (al.size() != 1)
133: throw new SException.Test("Wrong ArrayList size "
134: + al.size());
135:
136: SConnection.commit();
137: }
138:
139: /** Query all the departments with Budget > 10000 and check that the
140: total budget == total.
141: This uses the lower level SQL interface,
142: ADemo does the same thing using the Query interface. */
143: static void selectDepartments(int total) {
144:
145: /// Prepare and execute the query. See QueryTest.java for the query builder.
146: SPreparedStatement stmt = Department.meta.select("BUDGET > ?",
147: "NAME DESC");
148: stmt.setDouble(1, 10000);
149: SResultSet res = stmt.execute();
150:
151: /// loop through the results, adding up the budgets.
152: double totBudget = 0;
153: while (res.hasNext()) {
154: Department dept = (Department) res.getRecord();
155: double budget = dept.getDouble(Department.BUDGET);
156: SLog.slog.message("DEPARTMENT: "
157: + dept.getString(Department.NAME) + " $" + budget);
158: totBudget += budget;
159: dept.setDouble(dept.BUDGET, budget + 10);
160: }
161:
162: /// Check that the total is what we expect.
163: if (totBudget != total)
164: throw new SException.Test("Wrong Total " + totBudget);
165: }
166:
167: /**
168: Examples/Tests of the (non identifying) foreign key relationship
169: from Employee to Department, and from Employee to
170: Employee.Manager. */
171: static void empTest() { // Foreign Keys
172: SLog.slog.message("################ empTest #################");
173: SConnection.begin();
174:
175: /// Dump all the fields in the Employee record. Note that the
176: /// DEPT_ID is included even though it is not explicitly part of the
177: /// Employee record's definition.
178: SLog.slog.message("Employee Fields "
179: + Employee.meta.allFieldsString());
180:
181: TestUte.assertEqual((String) Employee.meta.sFieldNames.get(9),
182: "RESUME");
183:
184: /// Create an Employee
185: SDataLoader empDL = new SDataLoader(Employee.meta);
186: Employee e100 = (Employee) empDL.insertRecord(new Object[] {
187: "100", "One00", "123 456 7890", "10000", "3", null,
188: null });
189:
190: /// Explicitily set the Department
191: Department d100 = (Department) Department.meta
192: .findOrCreate("100");
193: Department d200 = (Department) Department.meta
194: .findOrCreate("200");
195:
196: e100.setReference(e100.DEPARTMENT, d200);
197:
198: /// Retrieve the Department using explicit meta data.
199: SFieldReference deptRef = (SFieldReference) Employee.meta
200: .getField("XX_DEPARTMENT");
201: Department dept1 = (Department) e100.getReference(deptRef);
202: if (!dept1.getString(d100.NAME).equals("Two00"))
203: throw new SException.Test("Bad Dept Two00 " + e100 + dept1);
204:
205: /// Null the Departent
206: e100.setReference(e100.DEPARTMENT, null);
207: Department dept2 = (Department) e100
208: .getReference(e100.DEPARTMENT);
209: if (dept2 != null)
210: throw new SException.Test("Bad Dept Null " + e100 + dept2);
211:
212: /// And reassign it.
213: e100.setReference(e100.DEPARTMENT, d100);
214: Department dept3 = (Department) e100
215: .getReference(e100.DEPARTMENT);
216: if (!dept3.getString(d100.NAME).equals("One00"))
217: throw new SException.Test("Bad Dept One00 " + e100 + dept3);
218:
219: SLog.slog.message("e100#" + e100.allFields());
220:
221: /// Create more Employees setting the Department using the DataLoader
222: Employee[] emps1 = (Employee[]) empDL
223: .insertRecords(new Object[][] {
224: { "200", "Two00", null, null, "0", null, e100 },
225: { "300", "Three00", "123 456 7890", "31000",
226: "1", d200, e100 } });
227: if (emps1[0].getString(Employee.PHONE_NR) != null
228: || emps1[0].getInt(Employee.SALARY) != 0 // 0 for null follows JDBC
229: || !emps1[0].isEmpty(Employee.SALARY)
230: || emps1[0].getObject(Employee.SALARY) != null)
231: throw new SException.Test("Bad Employee Nulls" + emps1[0]);
232: empDL.insertRecords(new Object[][] { // Twice should not cause grief
233: { "200", "Two00", "123 456 7890", "20000", "0",
234: d200, e100 },
235: { "300", "Three00", "123 456 7890", "30000",
236: "1", null, e100 } });
237:
238: /// Check e100 still valid after a flush()
239: SConnection.flush();
240: if (!e100.getString(Employee.NAME).equals("One00"))
241: throw new SException.Test("Flush() corrupted " + e100);
242:
243: SConnection.commit();
244: SConnection.begin();
245:
246: /// Check e100 record NOT valid after a commit()
247: /// (locks released so need to requery).
248: try {
249: e100.getString(Employee.NAME); // Error as e100 destroyed by commit.
250: throw new SException.Test("e100 not destroyed.");
251: } catch (SException.Error er) {
252: } // We want an exception here.
253:
254: /// Retrieve e100 again, and show all its field values.
255: /// Compare allFields() with the concise toString() that just shows keys.
256: Employee e100a = (Employee) Employee.meta.findOrCreate("100");
257: SLog.slog.message("e100a#" + e100a.allFields());
258:
259: /// Get the referenced d100 Department. Noe that the SQL query
260: /// only happens here.
261: Department d100a = (Department) e100a
262: .getReference(e100a.DEPARTMENT);
263: SLog.slog.message("d100a#" + d100a.allFields());
264:
265: /// Change an Employee's manager, creating a loop, and check.
266: Employee e300 = (Employee) Employee.meta.findOrCreate("300");
267: e100a.setReference(e100a.MANAGER, e300);
268: // So e200, e300 --> e100; e100 --> e300; ie. a loop, OK.
269: Employee e100b = (Employee) e300.getReference(e300.MANAGER);
270: if (e100b != e100a)
271: throw new SException.Test("e300.getReference " + e100a
272: + e300 + e100b);
273:
274: /// Check database updated correctly, this time using rawQueryJDBC().
275: SConnection.flush();
276: Object salSum = SConnection
277: .rawQueryDB("SELECT SUM(M.SALARY) FROM XX_EMPLOYEE M, XX_EMPLOYEE E "
278: + " WHERE M.EMPEE_ID = E.MANAGER_EMPEE_ID ");
279:
280: //"SELECT SUM(" // No Subselects in MySQL
281: //+ " (SELECT SALARY FROM XX_EMPLOYEE M "
282: //+ " WHERE M.EMPEE_ID = E.MANAGER_EMPEE_ID)) "
283: //+ "FROM XX_EMPLOYEE E"); // Sum of all manager's salary.
284: if (((Number) salSum).intValue() != 10000 + 10000 + 30000)
285: throw new SException.Test("Bad Mgr Salary sum " + salSum);
286:
287: SConnection.commit();
288: }
289:
290: /** Test of meta data and properties. */
291: static void metaTest() {
292: SFieldMeta empeeId = Employee.meta.getField("EMPEE_ID");
293: TestUte.assertTrue(empeeId == Employee.EMPEE_ID);
294: Integer size = (Integer) empeeId.getProperty(SBYTE_SIZE);
295: TestUte.assertTrue(size.intValue() == 20);
296:
297: SFieldMeta mgr = Employee.MANAGER;
298: int idx = SJSharp.object2Int(mgr.getProperty(SFIELD_INDEX));
299: SRecordMeta emp = (SRecordMeta) mgr.getProperty(SRECORD_META);
300: SFieldMeta[] flds = (SFieldMeta[]) emp
301: .getProperty(SFIELD_METAS);
302: TestUte.assertTrue(mgr == flds[idx]);
303: }
304:
305: static void queryTest() {
306: SConnection.begin();
307:
308: /// BUDGET > ?, IN
309: SQuery deptq = Department.meta.newQuery().gt(Department.BUDGET,
310: SJSharp.newInteger(1234))
311: //.and()
312: .lt(Department.NAME, "J").in(Department.DEPT_ID,
313: new Object[] { "100", "200" }).descending(
314: Department.NAME);
315: TestUte
316: .assertEqual(
317: deptq.toString(),
318: "[SQuery [SRecordMeta Department] XX_DEPARTMENT.\"BUDGET\" > ? AND XX_DEPARTMENT.\"NAME\" < ? AND XX_DEPARTMENT.\"DEPT_ID\" IN ( ? , ? ) BY XX_DEPARTMENT.\"NAME\" DESC]");
319:
320: /// Mission -- mixed case
321: SQuery deptqCase = Department.meta.newQuery().eq(
322: Department.MISSION, "Be Happy").ascending(
323: Department.MISSION);
324: SResultSet deptqCaseR = deptqCase.execute();
325: deptqCaseR.hasNext(1);
326: Department d200m = (Department) deptqCaseR.getRecord();
327: TestUte.assertEqual(d200m.getString(d200m.NAME), "Two00");
328:
329: /// Simple Foreign Key
330: Department d100 = (Department) Department.meta
331: .findOrCreate("100");
332: Department d200 = (Department) Department.meta
333: .findOrCreate("200");
334: SQuery empdeptq = Employee.meta.newQuery().eq(
335: Employee.DEPARTMENT, d100)
336: .ne(Employee.DEPARTMENT, d200).isNotNull(
337: Employee.DEPARTMENT); // silly query just for testing
338: //empdeptq.putProperty(SQOFFSET, 10);
339: //empdeptq.putProperty(SQLIMIT, 20);
340:
341: TestUte
342: .assertEqual(
343: empdeptq.toString(),
344: "[SQuery [SRecordMeta Employee] ( XX_EMPLOYEE.\"DEPT_ID\" = ? ) AND ( XX_EMPLOYEE.\"DEPT_ID\" <> ? ) AND ( XX_EMPLOYEE.\"DEPT_ID\" IS NOT NULL ) BY ]");
345: SResultSet empdr = empdeptq.execute();
346: empdr.hasNext(1);
347: Employee e100 = (Employee) empdr.getRecord();
348: TestUte.assertEqual(e100.getString(e100.NAME), "One00");
349:
350: // Join test
351: SQuery joinQ = Employee.meta.newQuery().join(
352: Employee.DEPARTMENT).eq(Department.NAME, "One00");
353: TestUte
354: .assertEqual(
355: joinQ.toString(),
356: "[SQuery [SRecordMeta Employee] ( XX_EMPLOYEE.\"DEPT_ID\" = XX_DEPARTMENT.\"DEPT_ID\" ) AND XX_DEPARTMENT.\"NAME\" = ? BY ]");
357: SResultSet joinR = joinQ.execute();
358: joinR.hasNext(1);
359: Employee e100Q = (Employee) joinR.getRecord();
360: TestUte.assertEqual(e100Q.getString(e100Q.NAME), "One00");
361:
362: // offset/limit tests
363: SPreparedStatement limitps = new SPreparedStatement();
364: limitps.setOffset(1);
365: limitps.setLimit(1);
366: SResultSet limitR = Employee.meta.newQuery().ascending(
367: Employee.EMPEE_ID).execute(limitps);
368: double sum = 0;
369: while (limitR.hasNext()) {
370: Employee eeLimit = (Employee) limitR.getRecord();
371: //SLog.slog.debug(
372: // "Limit " + eeLimit.getString(eeLimit.NAME) + " "
373: // + eeLimit.getDouble(eeLimit.SALARY));
374: sum += eeLimit.getDouble(eeLimit.SALARY);
375: }
376: TestUte.assertTrue(sum == 20000);
377:
378: SConnection.commit();
379: }
380: }
|