001: /*
002: * Copyright 2007 The Kuali Foundation.
003: *
004: * Licensed under the Educational Community License, Version 1.0 (the "License");
005: * you may not use this file except in compliance with the License.
006: * You may obtain a copy of the License at
007: *
008: * http://www.opensource.org/licenses/ecl1.php
009: *
010: * Unless required by applicable law or agreed to in writing, software
011: * distributed under the License is distributed on an "AS IS" BASIS,
012: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013: * See the License for the specific language governing permissions and
014: * limitations under the License.
015: */
016: package org.kuali.module.pdp;
017:
018: // This is a utility to help load data from a spreadsheet. It is not needed for production or unit tests.
019: // It should be deleted before the release.
020:
021: import java.io.BufferedInputStream;
022: import java.io.DataInputStream;
023: import java.io.File;
024: import java.io.FileInputStream;
025: import java.io.FileNotFoundException;
026: import java.io.IOException;
027: import java.util.ArrayList;
028: import java.util.Collections;
029: import java.util.Iterator;
030: import java.util.List;
031:
032: import org.apache.commons.lang.StringUtils;
033:
034: public class DataLoader {
035: public static int count = 0;
036:
037: public static void main(String[] args) {
038: DataLoader ld = new DataLoader();
039: List dater = ld.readFile("/Users/jsissom/fields.csv");
040:
041: List sqlData = ld.convertLines(dater);
042:
043: Collections.reverse(sqlData);
044: ld.processDeletes(sqlData);
045:
046: Collections.reverse(sqlData);
047: ld.processInserts(sqlData);
048: }
049:
050: class SqlData {
051: public String tableFields[];
052: public String fieldTypes[];
053: public String pks[];
054: public String fields[];
055: }
056:
057: public List convertLines(List lines) {
058: List output = new ArrayList();
059: String tableFields[] = null;
060: String fieldTypes[] = null;
061: String pks[] = null;
062:
063: for (Iterator iter = lines.iterator(); iter.hasNext();) {
064: String line = (String) iter.next();
065:
066: String fields[] = line.split(",");
067: if (fields.length > 1) {
068: if (!StringUtils.isEmpty(fields[0])) {
069: if ("Types".equals(fields[0])) {
070: fieldTypes = fields;
071: } else if ("PK".equals(fields[0])) {
072: pks = fields;
073: } else if ("DATA".equals(fields[0])) {
074: SqlData sd = new SqlData();
075: sd.tableFields = tableFields;
076: sd.fieldTypes = fieldTypes;
077: sd.pks = pks;
078: sd.fields = fields;
079: output.add(sd);
080: } else {
081: tableFields = fields;
082: }
083: }
084: }
085: }
086: return output;
087: }
088:
089: public List readFile(String fileName) {
090: List data = new ArrayList();
091: File file = new File(fileName);
092: FileInputStream fis = null;
093: BufferedInputStream bis = null;
094: DataInputStream dis = null;
095:
096: try {
097: fis = new FileInputStream(file);
098:
099: // Here BufferedInputStream is added for fast reading.
100: bis = new BufferedInputStream(fis);
101: dis = new DataInputStream(bis);
102:
103: while (dis.available() != 0) {
104: String line = dis.readLine();
105: data.add(line);
106: }
107:
108: fis.close();
109: bis.close();
110: dis.close();
111: return data;
112: } catch (FileNotFoundException e) {
113: e.printStackTrace();
114: } catch (IOException e) {
115: e.printStackTrace();
116: }
117: return null;
118: }
119:
120: private void processDeletes(List dater) {
121: for (Iterator iter = dater.iterator(); iter.hasNext();) {
122: SqlData sd = (SqlData) iter.next();
123:
124: generateDelete(sd.tableFields, sd.fieldTypes, sd.pks,
125: sd.fields);
126: }
127: }
128:
129: private void print(String[] fields, String title) {
130: System.out.print(title + " ");
131: for (int i = 0; i < fields.length; i++) {
132: System.out.print(fields[i] + ",");
133: }
134: System.out.println("");
135: }
136:
137: private void processInserts(List dater) {
138: for (Iterator iter = dater.iterator(); iter.hasNext();) {
139: SqlData sd = (SqlData) iter.next();
140:
141: generateInsert(sd.tableFields, sd.fieldTypes, sd.pks,
142: sd.fields);
143: }
144: }
145:
146: private void generateDelete(String[] tableFields,
147: String[] fieldTypes, String[] pks, String[] values) {
148: StringBuffer sb = new StringBuffer("SELECT ");
149: sb.append(DataLoader.count++);
150: sb.append(" FROM DUAL\n/\nDELETE FROM ");
151: sb.append(tableFields[0]);
152: sb.append(" WHERE ");
153: boolean stuff = false;
154: for (int i = 1; i < tableFields.length; i++) {
155: if ("X".equals(pks[i])) {
156: if (stuff) {
157: sb.append(" AND ");
158: }
159: sb.append(tableFields[i]);
160: sb.append(" = ");
161: sb.append(value(values[i], fieldTypes[i]));
162: stuff = true;
163: }
164: }
165: sb.append("\n/\n");
166: System.out.println(sb);
167: }
168:
169: private void generateInsert(String[] tableFields,
170: String[] fieldTypes, String[] pks, String[] values) {
171: StringBuffer sb = new StringBuffer("SELECT ");
172: sb.append(DataLoader.count++);
173: sb.append(" FROM DUAL\n/\nINSERT INTO ");
174: sb.append(tableFields[0]);
175: sb.append(" (");
176: boolean stuff = false;
177: for (int i = 1; i < tableFields.length; i++) {
178: if (!StringUtils.isEmpty(values[i])) {
179: if (stuff) {
180: sb.append(",");
181: }
182: sb.append(tableFields[i]);
183: stuff = true;
184: }
185: }
186: sb.append(") VALUES (");
187: stuff = false;
188: for (int i = 1; i < values.length; i++) {
189: if (!StringUtils.isEmpty(values[i])) {
190: if (stuff) {
191: sb.append(",");
192: }
193: sb.append(value(values[i], fieldTypes[i]));
194: stuff = true;
195: }
196: }
197: sb.append(")\n/\n");
198: System.out.println(sb);
199: }
200:
201: private String value(String value, String type) {
202: StringBuffer sb = new StringBuffer();
203: if ("S".equals(type)) {
204: sb.append("'");
205: sb.append(value);
206: sb.append("'");
207: } else if ("D".equals(type)) {
208: sb.append("to_date('");
209: sb.append(value);
210: sb.append("','MM/DD/YYYY')");
211: } else if ("N".equals(type)) {
212: sb.append(value);
213: } else if ("F".equals(type)) {
214: sb.append("sys_guid()");
215: }
216: return sb.toString();
217: }
218: }
|