001: package simpleorm.examples;
002:
003: import simpleorm.core.*; // .* OK, all classes prefixed with "S".
004: import java.io.File;
005: import java.io.FileOutputStream;
006: import java.io.PrintStream;
007:
008: /** Tests and demonstrates Identifying Foreign Keys.
009: */
010: public class IdentFKeysTest implements SConstants {
011: // ### Provide an E-R diagram of the schema.
012:
013: public static void main(String[] argv) throws Exception {
014: System.setErr(System.out); // Tidy up any stack traces.
015: // SLog.level = 20; //Uncomment to reduce trace output.
016: TestUte.initializeTest(IdentFKeysTest.class);
017: try {
018: testInit();
019: payrollTest();
020: payrollQuery();
021: payrollUpdate();
022: uglyPaySlipDetail();
023:
024: } finally {
025: SConnection.detachAndClose();
026: }
027: }
028:
029: /** Prepare for tests, Delete old data. */
030: static void testInit() throws Exception {
031: System.out
032: .println("################ testInit #################");
033: SConnection.begin();
034:
035: /// Delete any old data from a previous run.
036: TestUte.dropAllTables();
037:
038: int level = SLog.slog.level;
039: SLog.slog.level = 20;
040:
041: /// Dump the internal definitions of the records, mainly for debugging.
042: SLog.slog.message("Period Fields "
043: + Payroll.Period.meta.allFieldsString());
044: SLog.slog.message("PaySlip Fields "
045: + Payroll.PaySlip.meta.allFieldsString());
046: SLog.slog.message("SlipDetail Fields "
047: + Payroll.PaySlipDetail.meta.allFieldsString());
048: SLog.slog.message("UglySlipDetail Fields "
049: + Payroll.UglyPaySlipDetail.meta.allFieldsString());
050:
051: SConnection.rawUpdateDB(Department.meta.createTableSQL());
052: SConnection.rawUpdateDB(Employee.meta.createTableSQL());
053: SConnection.rawUpdateDB(Payroll.Period.meta.createTableSQL());
054: SConnection.rawUpdateDB(Payroll.PaySlip.meta.createTableSQL());
055: SConnection.rawUpdateDB(Payroll.PaySlipDetail.meta
056: .createTableSQL());
057: SConnection.rawUpdateDB(Payroll.UglyPaySlipDetail.meta
058: .createTableSQL());
059:
060: SLog.slog.level = level;
061:
062: SConnection.commit();
063:
064: /// Create some Departments and Employees.
065: SConnection.begin();
066:
067: SDataLoader deptDL = new SDataLoader(Department.meta);
068: deptDL
069: .insertRecords(new Object[][] {
070: { "100", "One00", "Count Pennies", "10000",
071: "200000" },
072: { "200", "Two00", "Be Happy", "20000", "150000" },
073: { "300", "Three00", "Enjoy Life", "30000",
074: "300000" } });
075:
076: SDataLoader empDL = new SDataLoader(Employee.meta);
077: Department d100 = (Department) Department.meta
078: .findOrCreate("100");
079: Department d200 = (Department) Department.meta
080: .findOrCreate("200");
081: /// Create an Employee
082: Employee e100 = (Employee) empDL.insertRecord(new Object[] {
083: "100", "One00", "123 456 7890", "10000", "3", null,
084: null });
085: Employee[] emps1 = (Employee[]) empDL
086: .insertRecords(new Object[][] {
087: { "200", "Two00", "123 456 7890", "20000", "0",
088: d200, e100 },
089: { "300", "Three00", "123 456 7890", "30000",
090: "1", null, e100 } });
091: SConnection.commit();
092:
093: }
094:
095: /** Demonstrates more advanced schema involving multi column multi
096: level identifying foreign keys.*/
097: static void payrollTest() {
098: System.out
099: .println("################ Payroll Test #################");
100: SConnection.begin();
101:
102: /// Create Periods
103: SDataLoader prdDL = new SDataLoader(Payroll.Period.meta);
104: Payroll.Period[] prds = (Payroll.Period[]) prdDL
105: .insertRecords(new Object[][] {
106: { "2001", "1", "1234" },
107: { "2002", "1", "2345" },
108: { "2002", "2", "3456" } });
109:
110: /// Create PaySlips
111: Employee e100 = (Employee) Employee.meta.findOrCreate("100");
112: Employee e200 = (Employee) Employee.meta.findOrCreate("200");
113:
114: SDataLoader slipDL = new SDataLoader(Payroll.PaySlip.meta);
115: Payroll.PaySlip[] slips = (Payroll.PaySlip[]) slipDL
116: .insertRecords(new Object[][] {
117: { e100, prds[0], "2001, prd1, emp100" },
118: { e200, prds[0], "2001, prd1, emp200" },
119: { e100, prds[2], "2002, prd2, emp100" },
120: { e200, prds[2], "Deleted e200 2" },
121: { e200, prds[1], "ps4 Deleted e200 1" } });
122:
123: /// Create PaySlipDetails
124: SDataLoader detailDL = new SDataLoader(
125: Payroll.PaySlipDetail.meta);
126: Payroll.PaySlipDetail[] details = (Payroll.PaySlipDetail[]) detailDL
127: .insertRecords(new Object[][] {
128: { slips[0], "11", "123" },
129: { slips[0], "12", "234" },
130: { slips[1], "11", "345" },
131: { slips[2], "13", "456" },
132: { slips[4], "22", "567" } });
133:
134: /// Change a Detail.
135: details[0].setInt(details[0].VALUE, 567);
136: SConnection.flush();
137:
138: /// Check sums to ensure that the database is updated correctly.
139: Object slipSum1 = SConnection
140: .rawQueryJDBC("SELECT SUM(VALUE) FROM XX_PSLIP_DTL "
141: + " WHERE INCONSIST_EMP_NR = '100' ");
142: if (((Number) slipSum1).intValue() != 567 + 234 + 456)
143: throw new SException.Test("Bad Payslip Emp sum " + slipSum1);
144:
145: Object slipSum2 = SConnection
146: .rawQueryJDBC("SELECT SUM(VALUE) FROM XX_PSLIP_DTL "
147: + " WHERE YEAR = 2001 ");
148: if (((Number) slipSum2).intValue() != 567 + 234 + 345)
149: throw new SException.Test("Bad Payslip Year sum "
150: + slipSum2);
151:
152: SConnection.commit();
153: SConnection.begin();
154:
155: /// Explicitly retrieve a value from PaySlipDetail
156: Payroll.Period p2002_2 = (Payroll.Period) Payroll.Period.meta
157: .findOrCreate(new Object[] { "2002", "2" });
158: Employee e100a = (Employee) Employee.meta.findOrCreate("100");
159: Payroll.PaySlip pe100_2002_2 = (Payroll.PaySlip) Payroll.PaySlip.meta
160: .findOrCreate(new Object[] { e100a, p2002_2 });
161: Payroll.PaySlipDetail pe100_2002_2_13 = (Payroll.PaySlipDetail) Payroll.PaySlipDetail.meta
162: .findOrCreate(new Object[] { pe100_2002_2, "13" });
163: int val = pe100_2002_2_13.getInt(pe100_2002_2_13.VALUE);
164: if (val != 456)
165: throw new SException.Test("Bad PaySlipDetail value " + val);
166:
167: SConnection.commit();
168:
169: }
170:
171: /** Test basic queries on PayslipDetails */
172: static void payrollQuery() {
173: SConnection.begin();
174:
175: SResultSet rs0 = Payroll.PaySlip.meta.select(
176: "COMMENTS = '2001, prd1, emp100'", null).execute();
177: Payroll.PaySlip ps0 = (Payroll.PaySlip) rs0.getOnlyRecord();
178:
179: SResultSet psdq = Payroll.PaySlipDetail.meta.newQuery().eq(
180: Payroll.PaySlipDetail.PAY_SLIP, ps0).execute();
181: double total = 0;
182: while (psdq.hasNext()) {
183: Payroll.PaySlipDetail psd = (Payroll.PaySlipDetail) psdq
184: .getRecord();
185: double val = psd.getDouble(psd.VALUE);
186: total += val;
187: SLog.slog.debug("PSDtl " + psd + " = " + val + " " + total);
188: }
189: TestUte.assertTrue(total == 567 + 234);
190:
191: SConnection.commit();
192: }
193:
194: /** More testing on updating Payrolls */
195: static void payrollUpdate() {
196: SConnection.begin();
197: SResultSet rs0 = Payroll.PaySlip.meta.select(
198: "COMMENTS = 'Deleted e200 2'", null).execute();
199: Payroll.PaySlip ps2 = (Payroll.PaySlip) rs0.getOnlyRecord();
200:
201: Employee e200a = (Employee) ps2.getReference(ps2.EMPLOYEE);
202: TestUte.assertTrue("Two00".equals(e200a.getString(e200a.NAME)));
203:
204: ps2.deleteRecord();
205:
206: SConnection.commit();
207: SConnection.begin();
208:
209: Employee e200b = (Employee) Employee.meta.findOrCreate("200");
210: Payroll.Period prd1 = (Payroll.Period) Payroll.Period.meta
211: .findOrCreate(new Object[] { "2002", "1" });
212: Payroll.PaySlip ps4 = (Payroll.PaySlip) Payroll.PaySlip.meta
213: .findOrCreate(new Object[] { e200b, prd1 });
214: ps4.assertNotNewRow();
215: TestUte.assertTrue("ps4 Deleted e200 1".equals(ps4
216: .getString(ps4.COMMENTS)));
217:
218: Payroll.PaySlipDetail psd4 = (Payroll.PaySlipDetail) Payroll.PaySlipDetail.meta
219: .findOrCreate(new Object[] { ps4, "22" });
220: psd4.assertNotNewRow();
221:
222: psd4.setDouble(psd4.VALUE, 123);
223:
224: SConnection.flush();
225:
226: psd4.deleteRecord();
227:
228: ps4.deleteRecord();
229:
230: SConnection.commit();
231:
232: }
233:
234: static void uglyPaySlipDetail() {
235: SConnection.begin();
236:
237: /// Create UglyPaySlipDetails
238: SResultSet rs0 = Payroll.PaySlip.meta.select(
239: "COMMENTS = '2001, prd1, emp100'", null).execute();
240: Payroll.PaySlip ps2 = (Payroll.PaySlip) rs0.getOnlyRecord();
241:
242: SDataLoader detailDL = new SDataLoader(
243: Payroll.UglyPaySlipDetail.meta);
244: Payroll.UglyPaySlipDetail[] details = (Payroll.UglyPaySlipDetail[]) detailDL
245: .insertRecords(new Object[][] { { ps2,
246: SJSharp.newInteger(99), "666" } });
247:
248: SConnection.flush();
249:
250: /// Change a Detail.
251: details[0].setInt(details[0].VALUE, 567);
252: SConnection.commit();
253: SConnection.begin();
254:
255: /// Read it back and check
256: Payroll.PaySlip ps2a = (Payroll.PaySlip) Payroll.PaySlip.meta
257: .select("COMMENTS = '2001, prd1, emp100'", null)
258: .execute().getOnlyRecord();
259:
260: Payroll.UglyPaySlipDetail ud99 = (Payroll.UglyPaySlipDetail) Payroll.UglyPaySlipDetail.meta
261: .findOrCreate(new Object[] { ps2a,
262: SJSharp.newInteger(99) });
263: int val = ud99.getInt(ud99.VALUE);
264: if (val != 567)
265: throw new SException.Test("Bad ugly value " + val);
266: SConnection.commit();
267:
268: }
269:
270: }
|