001: /*
002: * Copyright 2006-2007 Pentaho Corporation. All rights reserved.
003: * This software was developed by Pentaho Corporation and is provided under the terms
004: * of the Mozilla Public License, Version 1.1, or any later version. You may not use
005: * this file except in compliance with the license. If you need a copy of the license,
006: * please go to http://www.mozilla.org/MPL/MPL-1.1.txt.
007: *
008: * Software distributed under the Mozilla Public License is distributed on an "AS IS"
009: * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. Please refer to
010: * the license for the specific language governing your rights and limitations.
011: *
012: * Additional Contributor(s): Martin Schmid gridvision engineering GmbH
013: */
014: package org.pentaho.reportdesigner.crm.report.datasetplugin.sampledb;
015:
016: import org.jetbrains.annotations.NonNls;
017: import org.pentaho.reportdesigner.crm.report.datasetplugin.jdbc.JDBCClassLoader;
018:
019: import java.sql.Connection;
020:
021: /**
022: * User: Martin
023: * Date: 17.02.2006
024: * Time: 13:09:49
025: */
026: @NonNls
027: public class SampleDB {
028: private SampleDB() {
029: }
030:
031: @SuppressWarnings({"HardCodedStringLiteral"})
032: public static void initSampleDB() throws Exception {
033: Connection c = JDBCClassLoader.getConnection(
034: "org.hsqldb.jdbcDriver", "jdbc:hsqldb:mem:sample",
035: "sa", "");
036:
037: //Class.forName("org.hsqldb.jdbcDriver");
038:
039: //noinspection JDBCResourceOpenedButNotSafelyClosed
040: //Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:sample", "sa", "");
041:
042: c.createStatement().executeUpdate(
043: "CREATE MEMORY TABLE " + "addresses ("
044: + "address_id INT NOT NULL, "
045: + "addressline1 VARCHAR NOT NULL, "
046: + "addressline2 VARCHAR, "
047: + "zip_code INT NOT NULL, "
048: + "city VARCHAR NOT NULL, "
049: + "PRIMARY KEY(address_id) " + ")");
050:
051: c
052: .createStatement()
053: .executeUpdate(
054: "CREATE MEMORY TABLE "
055: + "customers ("
056: + "customer_id INT NOT NULL, "
057: + "first_name VARCHAR NOT NULL, "
058: + "last_name VARCHAR NOT NULL, "
059: + "address_id INT NOT NULL, "
060: + "PRIMARY KEY(customer_id), "
061: + "FOREIGN KEY (address_id) REFERENCES addresses(address_id) "
062: + ")");
063:
064: c
065: .createStatement()
066: .executeUpdate(
067: "CREATE MEMORY TABLE "
068: + "orders ("
069: + "order_id INT NOT NULL, "
070: + "order_date TIMESTAMP NOT NULL, "
071: + "shipping_date TIMESTAMP, "
072: + "customer_id INT NOT NULL, "
073: + "PRIMARY KEY(order_id), "
074: + "FOREIGN KEY (customer_id) REFERENCES customers(customer_id)"
075: + ")");
076:
077: c.createStatement().executeUpdate(
078: "CREATE MEMORY TABLE " + "products ("
079: + "product_id INT NOT NULL, "
080: + "product_name VARCHAR NOT NULL, "
081: + "product_description VARCHAR, "
082: + "price DECIMAL NOT NULL, "
083: + "PRIMARY KEY(product_id) " + ")");
084:
085: c
086: .createStatement()
087: .executeUpdate(
088: "CREATE MEMORY TABLE "
089: + "order_items ("
090: + "order_item_id INT NOT NULL, "
091: + "order_id INT NOT NULL, "
092: + "product_id INT NOT NULL, "
093: + "PRIMARY KEY(order_item_id), "
094: + "FOREIGN KEY (order_id) REFERENCES orders(order_id), "
095: + "FOREIGN KEY (product_id) REFERENCES products(product_id)"
096: + ")");
097:
098: c
099: .createStatement()
100: .executeUpdate(
101: "INSERT INTO addresses (address_id, addressline1, addressline2, zip_code, city) VALUES (1, 'Bernstrasse 5', null, 3000, 'Bern')");
102: c
103: .createStatement()
104: .executeUpdate(
105: "INSERT INTO addresses (address_id, addressline1, addressline2, zip_code, city) VALUES (2, 'Schwanengasse 5+7', null, 3200, 'Biel')");
106: c
107: .createStatement()
108: .executeUpdate(
109: "INSERT INTO addresses (address_id, addressline1, addressline2, zip_code, city) VALUES (3, 'Elsterweg 17', null, 5134, 'Gurzelen')");
110:
111: c
112: .createStatement()
113: .executeUpdate(
114: "INSERT INTO customers (customer_id, first_name, last_name, address_id) VALUES (1, 'Hugo', 'Habicht', 1)");
115: c
116: .createStatement()
117: .executeUpdate(
118: "INSERT INTO customers (customer_id, first_name, last_name, address_id) VALUES (2, 'Hans', 'Meiser', 2)");
119: c
120: .createStatement()
121: .executeUpdate(
122: "INSERT INTO customers (customer_id, first_name, last_name, address_id) VALUES (3, 'Hans', 'Müller', 2)");
123: c
124: .createStatement()
125: .executeUpdate(
126: "INSERT INTO customers (customer_id, first_name, last_name, address_id) VALUES (4, 'Erik', 'Brown', 3)");
127: c
128: .createStatement()
129: .executeUpdate(
130: "INSERT INTO customers (customer_id, first_name, last_name, address_id) VALUES (5, 'Patrick', 'Simpson', 2)");
131:
132: c
133: .createStatement()
134: .executeUpdate(
135: "INSERT INTO orders (order_id, order_date, shipping_date, customer_id) VALUES (1, '2005-12-23', null , 1)");
136: c
137: .createStatement()
138: .executeUpdate(
139: "INSERT INTO orders (order_id, order_date, shipping_date, customer_id) VALUES (2, '2005-01-15', '2005-01-19', 2)");
140: c
141: .createStatement()
142: .executeUpdate(
143: "INSERT INTO orders (order_id, order_date, shipping_date, customer_id) VALUES (3, '2005-03-17', '2005-03-23', 3)");
144: c
145: .createStatement()
146: .executeUpdate(
147: "INSERT INTO orders (order_id, order_date, shipping_date, customer_id) VALUES (4, '2005-07-27', '2005-08-2', 4)");
148: c
149: .createStatement()
150: .executeUpdate(
151: "INSERT INTO orders (order_id, order_date, shipping_date, customer_id) VALUES (5, '2005-07-28', '2005-08-3', 5)");
152:
153: c
154: .createStatement()
155: .executeUpdate(
156: "INSERT INTO products (product_id, product_name, product_description, price) VALUES (1, 'Matrix', null, 15.98)");
157: c
158: .createStatement()
159: .executeUpdate(
160: "INSERT INTO products (product_id, product_name, product_description, price) VALUES (2, 'Mogli', null, 23.98)");
161: c
162: .createStatement()
163: .executeUpdate(
164: "INSERT INTO products (product_id, product_name, product_description, price) VALUES (3, 'Electra', null, 9.65)");
165: c
166: .createStatement()
167: .executeUpdate(
168: "INSERT INTO products (product_id, product_name, product_description, price) VALUES (4, 'Hell Boy', null, 17.30)");
169: c
170: .createStatement()
171: .executeUpdate(
172: "INSERT INTO products (product_id, product_name, product_description, price) VALUES (5, 'Harry Potter', null, 6.95)");
173: c
174: .createStatement()
175: .executeUpdate(
176: "INSERT INTO products (product_id, product_name, product_description, price) VALUES (6, 'Incredibles', 'After creating the last great traditionally animated film of the 20th century, The Iron Giant, filmmaker Brad Bird joined top-drawer studio Pixar to create this exciting, completely entertaining computer-animated film. Bird gives us a family of \"supers,\" a brood of five with special powers desperately trying to fit in with the 9-to-5 suburban lifestyle. Of course, in a more innocent world, Bob and Helen Parr were superheroes, Mr. Incredible and Elastigirl. But blasted lawsuits and public disapproval forced them and other supers to go incognito, making it even tougher for their school-age kids, the shy Violet and the aptly named Dash. When a stranger named Mirage (voiced by Elizabeth Pena) secretly recruits Bob for a potential mission, the old glory days spin in his head, even if his body is a bit too plump for his old super suit.', 14.97)");
177: c
178: .createStatement()
179: .executeUpdate(
180: "INSERT INTO products (product_id, product_name, product_description, price) VALUES (7, 'Lost', 'Along with Desperate Housewives, Lost was one of the two breakout shows in the fall of 2004. Mixing suspense and action with a sci-fi twist, it began with a thrilling pilot episode in which a jetliner traveling from Australia to Los Angeles crashes, leaving 48 survivors on an unidentified island with no sign of civilization or hope of imminent rescue. That may sound like Gilligans Island meets Survivor, but Lost kept viewers tuning in every Wednesday night--and spending the rest of the week speculating on Web sites--with some irresistible hooks (not to mention the beautiful women). First, theres a huge ensemble cast of no fewer than 14 regular characters, and each episode fills in some of the back story on one of them. Theres a doctor; an Iraqi soldier; a has-been rock star; a fugitive from justice; a self-absorbed young woman and her brother; a lottery winner; a father and son; a Korean couple; a pregnant woman; and others. Second, theres a host of unanswered questions: What is the mysterious beast that lurks in the jungle? Why do polar bears and wild boars live there? Why has a woman been transmitting an SOS message in French from somewhere on the island for the last 16 years? Why do impossible wishes seem to come true? Are they really on a physical island, or somewhere else? What is the significance of the recurring set of numbers? And will Kate ever give up her bad-boy fixation and hook up with Jack?', 38.99)");
181:
182: c
183: .createStatement()
184: .executeUpdate(
185: "INSERT INTO order_items (order_item_id, order_id, product_id) VALUES ( 1, 1, 1)");
186: c
187: .createStatement()
188: .executeUpdate(
189: "INSERT INTO order_items (order_item_id, order_id, product_id) VALUES ( 2, 1, 2)");
190: c
191: .createStatement()
192: .executeUpdate(
193: "INSERT INTO order_items (order_item_id, order_id, product_id) VALUES ( 3, 1, 3)");
194: c
195: .createStatement()
196: .executeUpdate(
197: "INSERT INTO order_items (order_item_id, order_id, product_id) VALUES ( 4, 1, 4)");
198: c
199: .createStatement()
200: .executeUpdate(
201: "INSERT INTO order_items (order_item_id, order_id, product_id) VALUES ( 5, 1, 5)");
202: c
203: .createStatement()
204: .executeUpdate(
205: "INSERT INTO order_items (order_item_id, order_id, product_id) VALUES ( 6, 2, 1)");
206: c
207: .createStatement()
208: .executeUpdate(
209: "INSERT INTO order_items (order_item_id, order_id, product_id) VALUES ( 7, 2, 3)");
210: c
211: .createStatement()
212: .executeUpdate(
213: "INSERT INTO order_items (order_item_id, order_id, product_id) VALUES ( 8, 2, 5)");
214: c
215: .createStatement()
216: .executeUpdate(
217: "INSERT INTO order_items (order_item_id, order_id, product_id) VALUES ( 9, 3, 1)");
218: c
219: .createStatement()
220: .executeUpdate(
221: "INSERT INTO order_items (order_item_id, order_id, product_id) VALUES (10, 3, 2)");
222: c
223: .createStatement()
224: .executeUpdate(
225: "INSERT INTO order_items (order_item_id, order_id, product_id) VALUES (11, 3, 3)");
226: c
227: .createStatement()
228: .executeUpdate(
229: "INSERT INTO order_items (order_item_id, order_id, product_id) VALUES (12, 4, 1)");
230: c
231: .createStatement()
232: .executeUpdate(
233: "INSERT INTO order_items (order_item_id, order_id, product_id) VALUES (13, 4, 2)");
234: c
235: .createStatement()
236: .executeUpdate(
237: "INSERT INTO order_items (order_item_id, order_id, product_id) VALUES (14, 4, 3)");
238: c
239: .createStatement()
240: .executeUpdate(
241: "INSERT INTO order_items (order_item_id, order_id, product_id) VALUES (15, 4, 4)");
242: c
243: .createStatement()
244: .executeUpdate(
245: "INSERT INTO order_items (order_item_id, order_id, product_id) VALUES (16, 4, 5)");
246: c
247: .createStatement()
248: .executeUpdate(
249: "INSERT INTO order_items (order_item_id, order_id, product_id) VALUES (17, 4, 6)");
250: c
251: .createStatement()
252: .executeUpdate(
253: "INSERT INTO order_items (order_item_id, order_id, product_id) VALUES (18, 4, 7)");
254: c
255: .createStatement()
256: .executeUpdate(
257: "INSERT INTO order_items (order_item_id, order_id, product_id) VALUES (19, 5, 1)");
258: c
259: .createStatement()
260: .executeUpdate(
261: "INSERT INTO order_items (order_item_id, order_id, product_id) VALUES (20, 5, 2)");
262:
263: /*
264: SELECT
265: CUSTOMERS.FIRST_NAME, CUSTOMERS.LAST_NAME,
266: PRODUCTS.PRODUCT_NAME, PRODUCTS.PRODUCT_DESCRIPTION, PRODUCTS.PRICE
267: FROM CUSTOMERS
268: JOIN ORDERS ON CUSTOMERS.CUSTOMER_ID=ORDERS.CUSTOMER_ID
269: JOIN ORDER_ITEMS ON ORDER_ITEMS.ORDER_ID=ORDERS.ORDER_ID
270: JOIN PRODUCTS ON ORDER_ITEMS.PRODUCT_ID=PRODUCTS.PRODUCT_ID
271: ORDER BY
272: CUSTOMERS.FIRST_NAME, PRODUCTS.PRODUCT_NAME
273: */
274:
275: }
276: }
|