/*
 * Scone - The Web Enhancement Framework
 * Copyright (C) 2009 Harald Weinreich, Volkert Buchmann, Frank Wollenweber, Torsten Haß
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 */
package  scone.util;


import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.InputStreamReader;
import java.io.PrintWriter;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.StringTokenizer;
import java.util.Vector;


/**
 * this class automatically generates objects representing sql-records
 *
 * WARNING: The status of this class is currently not known! You should not use it!
 *
 * @author Volkert Buchmann
 */
public class POCreator {
    String name;
    String tableName;
    Vector colNames;
    Hashtable colTypes;
    Hashtable colDefaults;
    Hashtable keyTypes;
    Hashtable refTables;
    Hashtable refCols;
    Hashtable colComments;
    boolean hasGeneratedKey = false;
    String genKeyName = "";

    /**
     * put your documentation comment here
     * @param args
     */
    public static void main(String[] args) {
        POCreator POC = new POCreator(args[0]);

        POC.parseSqlSource();
        POC.transTypes();
        POC.printMe();
        POC.createTableRecordSource();
        POC.createCacheSource();
    }

    /**
     * put your documentation comment here
     */
    public void createCacheSource() {
        try {
            PrintWriter out = new PrintWriter(new BufferedWriter(new FileWriter(
                    name + "Cache.java")));

            out.print("package scone.netobjects;\n\n" + "import scone.util.*;\n" + "import java.util.*;\n\n" + "/**\n" + " * caches the " + name + " objects\n" + " */\n\n" + "public class " + name + "Cache extends Observable implements TableRecordCacheInterface{\n\n" + "   protected static CacheTable cache;\n\n" + "   public static Object lock=new Object();\n\n" + "   protected static " + name + "Cache observable;\n\n" + "   public static void init(int timeTillCleanUp,int gcType){\n" + "      cache = new CacheTable(");
            if (hasGeneratedKey) {
                out.print("2");
            } else {
                out.print("1");
            }
            out.print(",timeTillCleanUp,gcType);\n" + "      lock=new Object();\n" + "   }\n\n");
            // create normal get method
            // javadoc
            out.print("   /**\n");
            out.print("    * returns the " + name + " with the specified keys\n");
            out.print("    * This method will create such an object it it does not exist.\n");
            for (Enumeration e = colNames.elements(); e.hasMoreElements();) {
                String col = (String) e.nextElement();

                if (((String) keyTypes.get(col)).equals("KEY")) {
                    out.println("    * @param " + col + " " + colComments.get(col));
                }
            }
            out.print("    */\n");
            // signatur
            out.print("   public static " + name + " get(");
            boolean dummy = false;

            for (Enumeration e = colNames.elements(); e.hasMoreElements();) {
                String col = (String) e.nextElement();

                if (((String) keyTypes.get(col)).equals("KEY")) {
                    if (dummy) {
                        out.print(", ");
                    }
                    if (refTables.get(col) != null) {
                        String r = (String) refTables.get(col);

                        out.print(r + " " + col);
                    } else {
                        out.print("String " + col);
                    }
                    dummy = true;
                }
            }
            out.print("){\n" + "      String key=getHashKey(");
            dummy = false;
            for (Enumeration e = colNames.elements(); e.hasMoreElements();) {
                String col = (String) e.nextElement();

                if (((String) keyTypes.get(col)).equals("KEY")) {
                    if (dummy) {
                        out.print(", ");
                    }
                    dummy = true;
                    out.print(col);
                }
            }
            out.print(");\n" + "      " + name + " check=null;\n" + "      synchronized(lock){\n" + "         check=(" + name + ")cache.get(0,key);\n" + "         if(check==null){\n" + "            check=new " + name + "(");
            dummy = false;
            for (Enumeration e = colNames.elements(); e.hasMoreElements();) {
                String col = (String) e.nextElement();

                if (((String) keyTypes.get(col)).equals("KEY")) {
                    if (dummy) {
                        out.print(", ");
                    }
                    dummy = true;
                    out.print(col);
                }
            }
            out.print(");\n" + "            cache.put(check);\n" + "         }\n" + "      }\n" + "      return check;\n" + "   }\n\n");
            // create normal check method
            // javadoc
            out.print("   /**\n");
            out.print("    * returns the " + name + " with the specified keys if it exists, otherwise null\n");
            for (Enumeration e = colNames.elements(); e.hasMoreElements();) {
                String col = (String) e.nextElement();

                if (((String) keyTypes.get(col)).equals("KEY")) {
                    out.println("    * @param " + col + " " + colComments.get(col));
                }
            }
            out.print("    */\n");
            // signatur
            out.print("   public static " + name + " check(");
            dummy = false;
            for (Enumeration e = colNames.elements(); e.hasMoreElements();) {
                String col = (String) e.nextElement();

                if (((String) keyTypes.get(col)).equals("KEY")) {
                    if (dummy) {
                        out.print(", ");
                    }
                    if (refTables.get(col) != null) {
                        String r = (String) refTables.get(col);

                        out.print(r + " " + col);
                    } else {
                        out.print("String " + col);
                    }
                    dummy = true;
                }
            }
            out.print("){\n" + "      String key=getHashKey(");
            dummy = false;
            for (Enumeration e = colNames.elements(); e.hasMoreElements();) {
                String col = (String) e.nextElement();

                if (((String) keyTypes.get(col)).equals("KEY")) {
                    if (dummy) {
                        out.print(", ");
                    }
                    dummy = true;
                    out.print(col);
                }
            }
            out.print(");\n" + "      " + name + " check=null;\n" + "      synchronized(lock){\n" + "         check=(" + name + ")cache.get(0,key);\n" + "         if(check==null){\n" + "            check=" + name + ".check(");
            dummy = false;
            for (Enumeration e = colNames.elements(); e.hasMoreElements();) {
                String col = (String) e.nextElement();

                if (((String) keyTypes.get(col)).equals("KEY")) {
                    if (dummy) {
                        out.print(", ");
                    }
                    dummy = true;
                    out.print(col);
                }
            }
            out.print(");\n" + "            if(check!=null) cache.put(check);\n" + "         }\n" + "      }\n" + "      return check;\n" + "   }\n\n");
            // generated key check-method
            if (hasGeneratedKey) {
                // javadoc
                out.print("   /**\n");
                out.print("    * returns the " + name + " with the specified key if it exists, null otherwise\n");
                out.print("    * @param " + genKeyName + " " + colComments.get(genKeyName) + ";\n");
                out.print("    */\n");
                // signatur
                out.print("   public static " + name + " checkById(String " + genKeyName + "){\n" + "      String key=" + genKeyName + ";\n" + "      " + name + " check=null;\n" + "      synchronized(lock){\n" + "         check=(" + name + ")cache.get(1,key);\n" + "         if(check==null){\n" + "            check=" + name + ".checkById(" + genKeyName + ");\n" + "            if(check!=null) cache.put(check);\n" + "         }\n" + "      }\n" + "      return check;\n" + "   }\n\n");
            }
            // hashkey
            // javadoc
            out.print("   /**\n");
            out.print("    * returns a hashkey\n");
            for (Enumeration e = colNames.elements(); e.hasMoreElements();) {
                String col = (String) e.nextElement();

                if (((String) keyTypes.get(col)).equals("KEY")) {
                    out.println("    * @param " + col + " " + colComments.get(col));
                }
            }
            out.print("    * @return the hashkey\n");
            out.print("    */\n");
            // signatur
            out.print("   public static String getHashKey(");
            dummy = false;
            for (Enumeration e = colNames.elements(); e.hasMoreElements();) {
                String col = (String) e.nextElement();

                if (((String) keyTypes.get(col)).equals("KEY")) {
                    if (dummy) {
                        out.print(", ");
                    }
                    if (refTables.get(col) != null) {
                        String r = (String) refTables.get(col);

                        out.print(r + " " + col);
                    } else {
                        out.print("String " + col);
                    }
                    dummy = true;
                }
            }
            out.print("){\n" + "      return ");
            // create the key
            dummy = false;
            for (Enumeration e = colNames.elements(); e.hasMoreElements();) {
                String col = (String) e.nextElement();

                if (((String) keyTypes.get(col)).equals("KEY")) {
                    if (dummy) {
                        out.print("+\"\\n\"+");
                    }
                    dummy = true;
                    if (refTables.get(col) != null) {
                        String refCol = (String) refCols.get(col);

                        refCol = refCol.substring(0, 1).toUpperCase()
                                + refCol.substring(1);
                        out.print(col + ".get" + refCol + "()");
                    } else {
                        out.print(col);
                    }
                }
            }
            out.print(";\n   }\n\n");
            // observer methods
            out.print("   /**\n" + "    * adds an Observer to the " + name + "Cache class.\n" + "    * <br>\n" + "    * Use this static method instead of addObserver() !\n" + "    * @param o the Observer\n" + "    */\n" + "   public static void putObserver(Observer o){\n" + "      observable.addObserver(o);\n" + "   }\n\n" + "   /**\n" + "    * removes an Observer from the AccessCache class.\n" + "    * <br>\n" + "    * Use this static method instead of deleteObserver() !\n" + "    * @param o the Observer\n" + "    */\n" + "   public static void removeObserver(Observer o){\n" + "      observable.deleteObserver(o);\n" + "   }\n\n" + "   //notify the observers\n" + "   protected static void broadcastEvent(AccessEvent e){\n" + "      observable.setChanged();\n" + "      observable.notifyObservers(e);\n" + "   }\n\n" + "   public static void clean(){\n" + "    cache.clean();\n" + "   }\n");
            out.print("}");
            out.close();
        } catch (Exception e) {
            ErrorLog.log(this, "createCacheSource()", "", e);
        }
    }

    /**
     * put your documentation comment here
     */
    public void printMe() {
        System.out.println("OBJECT NAME " + name);
        System.out.println("TABLE  NAME " + tableName);
        for (Enumeration e = colNames.elements(); e.hasMoreElements();) {
            String col = (String) e.nextElement();

            System.out.print(col + " " + colTypes.get(col) + " DEFAULT ");
            if (((String) colTypes.get(col)).equals("STRING")) {
                System.out.print("'" + colDefaults.get(col) + "' ");
            } else {
                System.out.print(colDefaults.get(col) + " ");
            }
            if (keyTypes.get(col) == null) {
                System.out.print("FIELD");
            } else {
                System.out.print(keyTypes.get(col));
            }
            if (refTables.get(col) != null) {
                System.out.print(" REFERENCES " + refTables.get(col) + "." + refCols.get(col));
            }
            System.out.println();
        }
    }

    /**
     * creates a new POCreator which will create a new persistent object <code>name</code>
     * @param name the name of the object
     */
    public POCreator(String name) {
        this.name = name;
        tableName = "";
        colNames = new Vector();
        colTypes = new Hashtable();
        colDefaults = new Hashtable();
        keyTypes = new Hashtable();
        refTables = new Hashtable();
        refCols = new Hashtable();
        colComments = new Hashtable();
    }

    /**
     * parses the sql file which must be named after the object to be created: <code>name</code>.sql
     */
    public void parseSqlSource() {
        try {
            BufferedReader file = new BufferedReader(new InputStreamReader(new FileInputStream(
                    name + ".sql")));
            String line = "";

            // skip comments
            while ((line = file.readLine()) != null) {
                if (line.length() > 0 && line.charAt(0) != '#') {
                    break;
                }
            }
            StringTokenizer st = new StringTokenizer(line);

            // find out the tablename
            while (st.hasMoreTokens()) {
                line = st.nextToken();
                if (!line.equals("") && !line.equals("(")) {
                    tableName = line;
                }
            }
            // ensure that it does not terminate with (
            if (tableName.charAt(tableName.length() - 1) == '(') {
                tableName = tableName.substring(0, tableName.length() - 1);
            }
            // find out fieldnames
            while ((line = file.readLine()) != null) {
                String field = "";
                String type = "";
                String key = "";
                String refTable = "";
                String refCol = "";
                String dummy = "";
                String defaultValue = "";
                String comment = "";

                // skip comments
                if (line.length() > 0 && line.charAt(0) != '#') {
                    st = new StringTokenizer(line);
                    // read fieldName
                    if (st.hasMoreTokens()) {
                        field = st.nextToken();
                    }
                    // read keys
                    if (field.equals("KEY")) {
                        st = new StringTokenizer(line.substring(line.indexOf("(") + 1, line.indexOf(")")), ",");
                        while (st.hasMoreTokens()) {
                            keyTypes.put(st.nextToken().trim(), "KEY");
                        }
                    } else if (field.equals("INDEX")) {           // do nothing!
                    } // yes, it is really a field!
                    else {
                        if (line.indexOf("#") > 0) {
                            comment = line.substring(line.indexOf("#") + 1);
                        }
                        // skip empty tokens and read fieldType
                        while (st.hasMoreTokens()) {
                            type = st.nextToken();
                            if (type.length() != 0) {
                                break;
                            }
                        }
                        // now try to find out default,primary key and references!
                        while (st.hasMoreTokens()) {
                            dummy = st.nextToken();
                            if (dummy.equalsIgnoreCase("AUTO_INCREMENT")) {
                                keyTypes.put(field, "GENERATED");
                                hasGeneratedKey = true;
                                genKeyName = field;
                            }
                            if (dummy.equalsIgnoreCase("DEFAULT")) {
                                if (st.hasMoreTokens()) {
                                    defaultValue = st.nextToken();
                                }
                                if (defaultValue.charAt(0) == '\''
                                        || defaultValue.charAt(0) == '"') {
                                    if (defaultValue.length() > 2) {
                                        defaultValue = defaultValue.substring(1,
                                                defaultValue.length() - 2);
                                    } else {
                                        defaultValue = "";
                                    }
                                }
                            }
                            if (dummy.equalsIgnoreCase("REFERENCES")) {
                                if (st.hasMoreTokens()) {
                                    refTable = st.nextToken();
                                }
                                while (st.hasMoreTokens()) {
                                    refCol = st.nextToken();
                                    if (!refCol.equals("(")) {
                                        if (refCol.charAt(0) == '(') {
                                            refCol = refCol.substring(1);
                                        }
                                        if (refCol.indexOf(")") > 0) {
                                            refCol = refCol.substring(0, refCol.indexOf(")"));
                                        }
                                        refCol.trim();
                                        break;
                                    }
                                }
                            }
                        }
                        colNames.addElement(field);
                        colTypes.put(field, type);
                        colDefaults.put(field, defaultValue);
                        colComments.put(field, comment);
                        if (keyTypes.get(field) == null) {
                            keyTypes.put(field, "FIELD");
                        }
                        if (refTable.length() > 0) {
                            refTables.put(field, refTable);
                            refCols.put(field, refCol);
                        }
                    }
                }
                System.out.println("found a new field:\n" + field + " " + type + " DEFAULT " + defaultValue + " REFERENCES " + refTable + " " + refCol);
            }
        } catch (Exception e) {
            // ErrorLog.log(this,"parseSqlSource()","Could not parse file "+name+".sql",e);
            e.printStackTrace();
        }
    }

    // ----------------------------------------------
    public void createTableRecordSource() {
        try {
            PrintWriter out = new PrintWriter(new BufferedWriter(new FileWriter(
                    name + ".java")));

            out.println("package scone.netobjects;\n\n" + "import scone.proxy.*;\n" + "import java.util.*;\n" + "import adc.parser.*;\n" + "\n" + "/**\n");
            // hier kommt die klassen-doku hin
            out.println(" */\n" + "public class " + name + " extends TableRecord{\n" + "   protected static DBTableAdapter dbTable;\n\n");
            // hier kommen die referenzierten classen hin
            for (Enumeration e = colNames.elements(); e.hasMoreElements();) {
                String col = (String) e.nextElement();

                if (refTables.get(col) != null) {
                    out.println("   " + refTables.get(col) + " " + col + ";");
                }
            }
            out.println("\n   //set sql meta-data\n" + "   static{\n" + "      dbTable=new DBTableAdapter(\"" + name + "\"," + colNames.size() + ");\n");
            // declare the fields
            for (Enumeration e = colNames.elements(); e.hasMoreElements();) {
                String col = (String) e.nextElement();

                out.println("      dbTable.addField(\"" + col + "\", dbTable." + colTypes.get(col) + ",\"" + colDefaults.get(col) + "\",dbTable." + keyTypes.get(col) + ");");
            }
            out.println("   }\n\n");
            // accessor and mutators
            for (Enumeration e = colNames.elements(); e.hasMoreElements();) {
                String col = (String) e.nextElement();

                // is this a referenced class?
                if (refTables.get(col) != null) {
                    out.println("   /**\n" + "    * gets " + colComments.get(col) + "\n" + "    * @return " + colComments.get(col) + "\n" + "    */\n" + "   public " + refTables.get(col) + " get" + col.substring(0, 1).toUpperCase() + col.substring(1) + "(){return " + col + ";}\n");
                } else {
                    // die accessor-methode
                    out.println("   /**\n" + "    * gets " + colComments.get(col) + "\n" + "    * @return " + colComments.get(col) + "\n" + "    */\n" + "   public String get" + col.substring(0,
                            1).toUpperCase() + col.substring(1) + "(){" + "return (String)fieldValues.get(\"" + col + "\");}\n");
                    // wenn es kein key ist, brauchen wir auch eine mutator-methode
                    if (((String) keyTypes.get(col)).equals("FIELD")) {
                        out.println("   /**\n" + "    * sets " + colComments.get(col) + "\n" + "    * @param value " + colComments.get(col) + "\n" + "    */\n" + "   public void set" + col.substring(0,
                                1).toUpperCase() + col.substring(1) + "(String value){" + "fieldValues.put(\"" + col + "\",value);}\n");
                    }
                }
            }
            int pos;

            // create constructor---------------------------------------
            // javadoc
            out.print("   /**\n");
            out.print("    * creates a new " + name + " with the specified keys\n");
            for (Enumeration e = colNames.elements(); e.hasMoreElements();) {
                String col = (String) e.nextElement();

                if (((String) keyTypes.get(col)).equals("KEY")) {
                    out.println("    * @param " + col + " " + colComments.get(col));
                }
            }
            out.print("    */\n");
            // signatur
            out.print("   public " + name + "(");
            boolean dummy = false;

            for (Enumeration e = colNames.elements(); e.hasMoreElements();) {
                String col = (String) e.nextElement();

                if (((String) keyTypes.get(col)).equals("KEY")) {
                    if (dummy) {
                        out.print(", ");
                    }
                    if (refTables.get(col) != null) {
                        String r = (String) refTables.get(col);

                        out.print(r + " " + col);
                    } else {
                        out.print("String " + col);
                    }
                    dummy = true;
                }
            }
            out.print("){\n" + "      dbTable.init(this);\n");
            // fill the referenced objects
            for (Enumeration e = colNames.elements(); e.hasMoreElements();) {
                String col = (String) e.nextElement();

                if (((String) keyTypes.get(col)).equals("KEY")) {
                    if (refTables.get(col) != null) {
                        out.println("      this." + col + " = " + col + ";");
                    }
                }
            }
            for (Enumeration e = colNames.elements(); e.hasMoreElements();) {
                String col = (String) e.nextElement();

                if (((String) keyTypes.get(col)).equals("KEY")) {
                    if (refTables.get(col) != null) {
                        String refCol = (String) refCols.get(col);

                        refCol = refCol.substring(0, 1).toUpperCase()
                                + refCol.substring(1);
                        out.print("      fieldValues.put(\"" + col + "\"," + col + ".get" + refCol + "());\n");
                    } else {
                        out.print("      fieldValues.put(\"" + col + "\"," + col + ");\n");
                    }
                }
            }
            out.println("      dbTable.dbInit(this);\n" + "   }\n");
            // create check-constructors
            out.println("   private " + name + "(){}\n\n" + "   /** \n" + "    * returns " + name + " if it exists in the database, null otherwise.");
            for (Enumeration e = colNames.elements(); e.hasMoreElements();) {
                String col = (String) e.nextElement();

                if (((String) keyTypes.get(col)).equals("KEY")) {
                    out.println("    * @param " + col + " " + colComments.get(col));
                }
            }
            out.println("    * @return the specified " + name + " or null if it does not exists in the database.\n" + "    */\n");
            // signatur
            out.print("   public static " + name + " check(");
            dummy = false;
            for (Enumeration e = colNames.elements(); e.hasMoreElements();) {
                String col = (String) e.nextElement();

                if (((String) keyTypes.get(col)).equals("KEY")) {
                    if (dummy) {
                        out.print(", ");
                    }
                    if (refTables.get(col) != null) {
                        String r = (String) refTables.get(col);

                        out.print(r + " " + col);
                    } else {
                        out.print("String " + col);
                    }
                    dummy = true;
                }
            }
            out.print("){\n" + "      " + name + " check=new " + name + "();\n" + "      check.dbTable.init(check);\n");
            // fill the referenced objects
            for (Enumeration e = colNames.elements(); e.hasMoreElements();) {
                String col = (String) e.nextElement();

                if (((String) keyTypes.get(col)).equals("KEY")) {
                    if (refTables.get(col) != null) {
                        out.println("      check." + col + " = " + col + ";");
                    }
                }
            }
            for (Enumeration e = colNames.elements(); e.hasMoreElements();) {
                String col = (String) e.nextElement();

                if (((String) keyTypes.get(col)).equals("KEY")) {
                    if (refTables.get(col) != null) {
                        String refCol = (String) refCols.get(col);

                        refCol = refCol.substring(0, 1).toUpperCase()
                                + refCol.substring(1);
                        out.print("      check.fieldValues.put(\"" + col + "\"," + col + ".get" + refCol + "());\n");
                    } else {
                        out.print("      check.fieldValues.put(\"" + col + "\"," + col + ");\n");
                    }
                }
            }
            out.println("      check.dbTable.dbInit(check);\n" + "      if(check.hasRecordInDB)\n" + "         return check;\n" + "      else\n" + "         return null;\n" + "   }\n");
            if (hasGeneratedKey) {
                out.println("   /** \n" + "    * returns " + name + " if it exists in the database, null otherwise.\n" + "    * @param " + genKeyName + " " + colComments.get(genKeyName) + "\n" + "    * @return the specified " + name + " or null if it does not exists in the database.\n" + "    */\n" + "   public static " + name + " checkById(String " + genKeyName + "){\n" + "      " + name + " check=new " + name + "();\n" + "      check.dbTable.init(check);\n" + "      check.fieldValues.put(\"" + genKeyName + "\"," + genKeyName + ");\n" + "      check.dbTable.dbCheck(check);\n" + "      if(check.hasRecordInDB){");
                // fill the referenced objects
                for (Enumeration e = colNames.elements(); e.hasMoreElements();) {
                    String col = (String) e.nextElement();

                    if (((String) keyTypes.get(col)).equals("KEY")) {
                        if (refTables.get(col) != null) {
                            out.println("         check." + col + " = " + refTables.get(col) + "Cache.getById((String)check.fieldValues.get(\"" + col + "\"));");
                        }
                    }
                }
                out.print("         return check;\n" + "      }\n" + "      return null;\n" + "   }\n\n");
            }
            // getkey()
            out.print("   public Object getKey(int keyNo){\n" + "      switch(keyNo){\n" + "         case 0: return ");
            dummy = false;
            for (Enumeration e = colNames.elements(); e.hasMoreElements();) {
                String col = (String) e.nextElement();

                if (((String) keyTypes.get(col)).equals("KEY")) {
                    if (dummy) {
                        out.print("+\"\\n\"+");
                    }
                    out.print("fieldValues.get(\"" + col + "\")");
                    dummy = true;
                }
            }
            out.print(";\n");
            if (hasGeneratedKey) {
                out.print("         case 1: return fieldValues.get(\"" + genKeyName + "\");\n");
            }
            out.print("         default: return \"\";\n" + "      }\n" + "   }\n\n");
            // close class
            out.print("   public void store(){\n" + "      dbTable.updateDB(this);\n" + "   }\n" + "}");
            out.close();
        } catch (Exception ex) {
            ErrorLog.log(this, "createTableRecordSource()", "", ex);
        }
    }

    // ----------------------------------------------
    // translates all sql types into po-types
    public void transTypes() {
        for (Enumeration e = colNames.elements(); e.hasMoreElements();) {
            String col = (String) e.nextElement();

            colTypes.put(col, getType((String) colTypes.get(col)));
        }
    }

    // translates a sql-type into a po-type
    public String getType(String sqlType) {
        sqlType = sqlType.toUpperCase();
        for (Enumeration e = NUMBERS.elements(); e.hasMoreElements();) {
            if (sqlType.startsWith((String) e.nextElement())) {
                return  "NUMBER";
            }
        }
        for (Enumeration e = STRINGS.elements(); e.hasMoreElements();) {
            if (sqlType.startsWith((String) e.nextElement())) {
                return  "STRING";
            }
        }
        for (Enumeration e = BLOBS.elements(); e.hasMoreElements();) {
            if (sqlType.startsWith((String) e.nextElement())) {
                return  "BLOB";
            }
        }
        for (Enumeration e = DATES.elements(); e.hasMoreElements();) {
            if (sqlType.startsWith((String) e.nextElement())) {
                return  "DATE";
            }
        }
        return  "UNKNOWN";
    }
    static Vector NUMBERS = new Vector();
    static Vector STRINGS = new Vector();
    static Vector BLOBS = new Vector();
    static Vector DATES = new Vector();
    static {
        NUMBERS.addElement("TINYINT");
        NUMBERS.addElement("SMALLINT");
        NUMBERS.addElement("MEDIUMINT");
        NUMBERS.addElement("INT");
        NUMBERS.addElement("INTEGER");
        NUMBERS.addElement("BIGINT");
        NUMBERS.addElement("REAL");
        NUMBERS.addElement("DOUBLE");
        NUMBERS.addElement("FLOAT");
        NUMBERS.addElement("DECIMAL");
        NUMBERS.addElement("NUMERIC");
        STRINGS.addElement("CHAR");
        STRINGS.addElement("VARCHAR");
        STRINGS.addElement("TINYTEXT");
        STRINGS.addElement("TEXT");
        STRINGS.addElement("MEDIUMTEXT");
        STRINGS.addElement("LONGTEXT");
        BLOBS.addElement("TINYBLOB");
        BLOBS.addElement("BLOB");
        BLOBS.addElement("MEDIUMBLOB");
        BLOBS.addElement("LONGBLOB");
        DATES.addElement("DATE");
        DATES.addElement("TIME");
        DATES.addElement("TIMESTAMP");
        DATES.addElement("DATETIME");
    }
}

