001// Copyright (c) 2001 Hursh Jain (http://www.mollypages.org) 
002// The Molly framework is freely distributable under the terms of an
003// MIT-style license. For details, see the molly pages web site at:
004// http://www.mollypages.org/. Use, modify, have fun !
005
006package fc.jdbc.dbo;
007
008import java.sql.*;
009import java.util.*;
010import java.io.*;
011
012import fc.io.*;
013import fc.util.*;
014import fc.jdbc.*;
015
016/**
017Create a table containing all JDBC/SQL types we are interested in. The
018table is created using the database and jdbc url in the specified
019configuration file. The "-db" flag is optional, if specified that table
020is created in that catalog/schema. (Note: postgresql doesn't currently
021allow us to set a db catalog, the db catalog/schema must be specified as
022part of the jdbc url itself).
023*/
024public class CreateAllTypes
025{
026static DBName dbname ;
027static String tablename;
028
029public static void main(String args[]) throws Exception
030  {
031  String defaultTableName = "alltypes";
032  
033  Args myargs = new Args(args);
034  myargs.setUsage("java CreateAllTypes -conf conf_file [-db db_schema] [-table table_name_to_create]");
035  
036  String db     = myargs.get("db");
037  String conf   = myargs.getRequired("conf");
038  tablename     = myargs.get("table", defaultTableName);
039
040  Log.getDefault().setLevel(SystemLog.DEBUG); 
041  FilePropertyMgr fprops = new FilePropertyMgr(new File(conf));
042  ConnectionMgr cmgr = new SimpleConnectionMgr(fprops);
043  
044  String url = fprops.get("jdbc.url");
045  if (url.indexOf("postgres") != -1)
046    dbname = DBName.postgres;
047  else if (url.indexOf("mysql") != -1)
048    dbname = DBName.mysql;
049  else if (url.indexOf("oracle") != -1)
050    dbname = DBName.oracle;
051  else
052    throw new Exception("I do not understand this database type. Only mysql/postgresql supported currently."); /*f$%# oracle*/
053    
054  Connection con = cmgr.getConnection();
055  if (db != null)
056    con.setCatalog(db);
057
058  String sql = getSQL();
059  Statement stmt = con.createStatement();
060  System.out.println("Creating table..");
061  int result = stmt.executeUpdate(sql);
062  System.out.println("...done [success]");
063  con.close();
064  }
065
066static String getSQL() 
067  {
068  String create = 
069  "create table " + tablename + " ( ";  
070  
071  create += (dbname == DBName.mysql) ?
072    "id         int auto_increment, "
073    :
074    "id         serial, "; 
075  
076  create += 
077     "smallint_val    smallint, " 
078   + "int_val       int not null, "
079   + "bigint_val      bigint, "
080   + "float_val     float(24), "
081   + "double_val      float(53), "
082   + "numeric_val     numeric(5,2), "
083   + "char_val      char(10), "
084   + "varchar_val     varchar(10), "
085   + "longvarchar_val   text, "
086   + "date_val      date, "
087   + "time_val      time, "
088   + "timestamp_val   timestamp, "
089   + "bit_val       bit, "
090   ;
091   
092  create += (dbname == DBName.mysql) ?
093    ("boolean_val     bool, " 
094   + "varbinary_val   blob, ")
095    :
096    ("boolean_val     boolean, " 
097   + "varbinary_val   bytea, ");
098  
099  if (dbname == DBName.mysql) {
100  create += 
101    "json_val       json, ";
102  }
103  else if (dbname == DBName.postgres) {
104  create += 
105    "array_val      varchar(10)[], "
106  + "json_val       json, "
107  + "jsonb_val      jsonb, ";
108  }
109
110  create += " PRIMARY KEY (id) )";
111  
112  return create;
113  }
114  
115}