Copy data from TSV File into a new table in specified connection.
@param connection
@param fileName
@param tableReference
@throws SQLException
@throws FileNotFoundException
@throws IOException
320325
public static void readTSV(Connection connection, String fileName, String tableReference) throws SQLException, FileNotFoundException, IOException {
        File file = URIUtilities.fileFromString(fileName);
        if (FileUtil.isFileImportable(file, "tsv")) {
            TSVDriverFunction tsvDriver = new TSVDriverFunction();
            tsvDriver.importFile(connection, tableReference, file, new EmptyProgressVisitor());
        }
    }
727752
public static void createTables( Connection connection ) throws IOException, SQLException {
        StringBuilder sB = new StringBuilder();
        sB.append("CREATE TABLE ");
        sB.append(TABLE_NOTES);
        sB.append(" (");
        sB.append(NotesTableFields.COLUMN_ID.getFieldName());
        sB.append(" INTEGER PRIMARY KEY, ");
        sB.append(NotesTableFields.COLUMN_LON.getFieldName()).append(" REAL NOT NULL, ");
        sB.append(NotesTableFields.COLUMN_LAT.getFieldName()).append(" REAL NOT NULL,");
        sB.append(NotesTableFields.COLUMN_ALTIM.getFieldName()).append(" REAL NOT NULL,");
        sB.append(NotesTableFields.COLUMN_TS.getFieldName()).append(" DATE NOT NULL,");
        sB.append(NotesTableFields.COLUMN_DESCRIPTION.getFieldName()).append(" TEXT, ");
        sB.append(NotesTableFields.COLUMN_TEXT.getFieldName()).append(" TEXT NOT NULL, ");
        sB.append(NotesTableFields.COLUMN_FORM.getFieldName()).append(" CLOB, ");
        sB.append(NotesTableFields.COLUMN_STYLE.getFieldName()).append(" TEXT,");
        sB.append(NotesTableFields.COLUMN_ISDIRTY.getFieldName()).append(" INTEGER");
        sB.append(");");
        String CREATE_TABLE_NOTES = sB.toString();

        sB = new StringBuilder();
        sB.append("CREATE INDEX notes_ts_idx ON ");
        sB.append(TABLE_NOTES);
        sB.append(" ( ");
        sB.append(NotesTableFields.COLUMN_TS.getFieldName());
        sB.append(" );");
        String CREATE_INDEX_NOTES_TS = sB.toString();

        sB = new StringBuilder();
        sB.append("CREATE INDEX notes_x_by_y_idx ON ");
        sB.append(TABLE_NOTES);
        sB.append(" ( ");
        sB.append(NotesTableFields.COLUMN_LON.getFieldName());
        sB.append(", ");
        sB.append(NotesTableFields.COLUMN_LAT.getFieldName());
        sB.append(" );");
        String CREATE_INDEX_NOTES_X_BY_Y = sB.toString();

        sB = new StringBuilder();
        sB.append("CREATE INDEX notes_isdirty_idx ON ");
        sB.append(TABLE_NOTES);
        sB.append(" ( ");
        sB.append(NotesTableFields.COLUMN_ISDIRTY.getFieldName());
        sB.append(" );");
        String CREATE_INDEX_NOTES_ISDIRTY = sB.toString();

        try (Statement statement = connection.createStatement()) {
            statement.setQueryTimeout(30); // set timeout to 30 sec.

            statement.executeUpdate(CREATE_TABLE_NOTES);
            statement.executeUpdate(CREATE_INDEX_NOTES_TS);
            statement.executeUpdate(CREATE_INDEX_NOTES_X_BY_Y);
            statement.executeUpdate(CREATE_INDEX_NOTES_ISDIRTY);
        } catch (Exception e) {
            throw new IOException(e.getLocalizedMessage());
        }
    }
1021083
public static void copyFields(Connection connection, SimpleResultSet rs, TableLocation tableLocation) throws SQLException {
        DatabaseMetaData meta = connection.getMetaData();
        ResultSet columnsRs = meta.getColumns(tableLocation.getCatalog(null), tableLocation.getSchema(null),
                tableLocation.getTable(), null);
        Map<Integer, Object[]> columns = new HashMap<Integer, Object[]>();
        int COLUMN_NAME = 0, COLUMN_TYPE = 1, COLUMN_TYPENAME = 2, COLUMN_PRECISION = 3, COLUMN_SCALE = 4;
        try {
            while (columnsRs.next()) {
                Object[] columnInfoObjects = new Object[COLUMN_SCALE + 1];
                columnInfoObjects[COLUMN_NAME] = columnsRs.getString("COLUMN_NAME");
                columnInfoObjects[COLUMN_TYPE] = columnsRs.getInt("DATA_TYPE");
                columnInfoObjects[COLUMN_TYPENAME] = columnsRs.getString("TYPE_NAME");
                columnInfoObjects[COLUMN_PRECISION] = columnsRs.getInt("COLUMN_SIZE");
                columnInfoObjects[COLUMN_SCALE] = columnsRs.getInt("DECIMAL_DIGITS");
                columns.put(columnsRs.getInt("ORDINAL_POSITION"), columnInfoObjects);
            }
        } finally {
            columnsRs.close();
        }
        for(int i=1;i<=columns.size();i++) {
            Object[] columnInfoObjects = columns.get(i);
            rs.addColumn((String)columnInfoObjects[COLUMN_NAME], (Integer)columnInfoObjects[COLUMN_TYPE],
                    (String)columnInfoObjects[COLUMN_TYPENAME], (Integer)columnInfoObjects[COLUMN_PRECISION]
                    , (Integer)columnInfoObjects[COLUMN_SCALE]);
        }
    }
405839
private void setSchemaOnConnection(Connection connection) throws SQLException {
		logger.finest("Entering " + CLASSNAME +".setSchemaOnConnection()");

		String dbProductName = connection.getMetaData().getDatabaseProductName();
		if (!"Oracle".equals(dbProductName) && !"Microsoft SQL Server".equals(dbProductName)) {
			PreparedStatement ps = null;
			if ("MySQL".equals(dbProductName)) {
				ps = connection.prepareStatement("USE " + schema);
			} else {
				ps = connection.prepareStatement("SET SCHEMA ?");
				ps.setString(1, schema);
			}
			ps.executeUpdate(); 
			ps.close();
		}

		logger.finest("Exiting " + CLASSNAME +".setSchemaOnConnection()");
	}
1230869
@Override
  public void write(JdbcEntryData record) throws IOException {
    if (LOG.isDebugEnabled()) {
      LOG.debug("Writing " + record);
    }
    try {
      this.commands.insert(this.databaseName, this.tableName, record);
      this.recordWrittenCount++;
    } catch (Exception e) {
      this.failed = true;
      throw new RuntimeException(e);
    }
  }
54229
public static void createTables(Connection connection) throws Exception {
        StringBuilder sB = new StringBuilder();
        sB.append("CREATE TABLE ");
        sB.append(TABLE_LOG);
        sB.append(" (");
        sB.append(COLUMN_ID);
        sB.append(" INTEGER PRIMARY KEY AUTOINCREMENT, ");
        sB.append(COLUMN_DATAORA).append(" INTEGER NOT NULL, ");
        sB.append(COLUMN_LOGMSG).append(" TEXT ");
        sB.append(");");
        String CREATE_TABLE = sB.toString();

        sB = new StringBuilder();
        sB.append("CREATE INDEX " + TABLE_LOG + "_" + COLUMN_ID + " ON ");
        sB.append(TABLE_LOG);
        sB.append(" ( ");
        sB.append(COLUMN_ID);
        sB.append(" );");
        String CREATE_INDEX = sB.toString();

        sB = new StringBuilder();
        sB.append("CREATE INDEX " + TABLE_LOG + "_" + COLUMN_DATAORA + " ON ");
        sB.append(TABLE_LOG);
        sB.append(" ( ");
        sB.append(COLUMN_DATAORA);
        sB.append(" );");
        String CREATE_INDEX_DATE = sB.toString();


        try (Statement statement = connection.createStatement()) {
            statement.setQueryTimeout(30); // set timeout to 30 sec.

            statement.executeUpdate(CREATE_TABLE);
            statement.executeUpdate(CREATE_INDEX);
            statement.executeUpdate(CREATE_INDEX_DATE);
        } catch (Exception e) {
            throw new IOException(e.getLocalizedMessage());
        }
    }
1314400
private void setupConnection(final Connection connection) throws ConnectionSetupException
   {
      try {
         if (networkTimeout == UNINITIALIZED) {
            networkTimeout = getAndSetNetworkTimeout(connection, validationTimeout);
         }
         else {
            setNetworkTimeout(connection, validationTimeout);
         }

         if (connection.isReadOnly() != isReadOnly) {
            connection.setReadOnly(isReadOnly);
         }

         if (connection.getAutoCommit() != isAutoCommit) {
            connection.setAutoCommit(isAutoCommit);
         }

         checkDriverSupport(connection);

         if (transactionIsolation != defaultTransactionIsolation) {
            connection.setTransactionIsolation(transactionIsolation);
         }

         if (catalog != null) {
            connection.setCatalog(catalog);
         }

         if (schema != null) {
            connection.setSchema(schema);
         }

         executeSql(connection, config.getConnectionInitSql(), true);

         setNetworkTimeout(connection, networkTimeout);
      }
      catch (SQLException e) {
         throw new ConnectionSetupException(e);
      }
   }
651311
public static boolean tableExists(Connection connection, String tableName) throws SQLException {
        try (Statement statement = connection.createStatement()) {
            statement.execute("SELECT * FROM " + TableLocation.parse(tableName) + " LIMIT 0;");
            return true;
        } catch (SQLException ex) {
            return false;
        }
    }
483273
public static boolean createTableIfMissing (
        Connection conn, String table, String[] definition, String postamble)
        throws SQLException
    {
        if (tableExists(conn, table)) {
            return false;
        }

        Statement stmt = conn.createStatement();
        try {
            stmt.executeUpdate("create table " + table +
                               "(" + StringUtil.join(definition, ", ") + ") " + postamble);
        } finally {
            close(stmt);
        }

        log.info("Database table '" + table + "' created.");
        return true;
    }
353280
public void addConnection (Connection connection) {
		if (connection == null) throw new IllegalArgumentException("connection cannot be null.");

		synchronized (connectionsLock) {
			Connection[] newConnections = new Connection[connections.length + 1];
			newConnections[0] = connection;
			System.arraycopy(connections, 0, newConnections, 1, connections.length);
			connections = newConnections;
		}

		connection.addListener(invokeListener);

		if (TRACE) trace("kryonet", "Added connection to ObjectSpace: " + connection);
	}
679903
public synchronized void insert (Connection conn, T[] objects)
        throws SQLException
    {
        StringBuilder sql = new StringBuilder(
            "insert into " + name + " (" + listOfFields + ") values (?");
        for (int i = 1; i < nColumns; i++) {
            sql.append(",?");
        }
        sql.append(")");
        PreparedStatement insertStmt = conn.prepareStatement(sql.toString());
        for (int i = 0; i < objects.length; i++) {
            bindUpdateVariables(insertStmt, objects[i], null);
            insertStmt.addBatch();
        }
        insertStmt.executeBatch();
        insertStmt.close();
    }
227226
private void processTableData() throws IOException
   {
      DirectoryEntry dir = (DirectoryEntry) m_viewDir.getEntry("CTable");

      VarMeta varMeta = new VarMeta12(new DocumentInputStream(((DocumentEntry) dir.getEntry("VarMeta"))));
      Var2Data varData = new Var2Data(varMeta, new DocumentInputStream(((DocumentEntry) dir.getEntry("Var2Data"))));
      FixedData fixedData = new FixedData(230, new DocumentInputStream(((DocumentEntry) dir.getEntry("FixedData"))));
      //System.out.println(varMeta);
      //System.out.println(varData);
      //System.out.println(fixedData);

      TableContainer container = m_file.getTables();
      TableFactory14 factory = new TableFactory14(TABLE_COLUMN_DATA_STANDARD, TABLE_COLUMN_DATA_ENTERPRISE, TABLE_COLUMN_DATA_BASELINE);
      int items = fixedData.getItemCount();
      for (int loop = 0; loop < items; loop++)
      {
         byte[] data = fixedData.getByteArrayValue(loop);
         Table table = factory.createTable(m_file, data, varMeta, varData);
         container.add(table);
         //System.out.println(table);
      }
   }
1049659
private void createTables(Connection conn) throws DatabaseException {
        LOGGER.debug("Creating database structure");

        try (InputStream is = FileUtils.getResourceAsStream(DB_STRUCTURE_RESOURCE)) {
            final String dbStructure = IOUtils.toString(is, StandardCharsets.UTF_8);

            Statement statement = null;
            try {
                statement = conn.createStatement();
                statement.execute(dbStructure);
            } catch (SQLException ex) {
                LOGGER.debug("", ex);
                throw new DatabaseException("Unable to create database statement", ex);
            } finally {
                DBUtils.closeStatement(statement);
            }
        } catch (IOException ex) {
            throw new DatabaseException("Unable to create database schema", ex);
        }
    }
657842
public void createTable(String tblName, Schema sch, Transaction tx) {
		if (tblName != TCAT_TBLNAME && tblName != FCAT_TBLNAME)
			formatFileHeader(tblName, tx);
		// Optimization: store the ti
		tiMap.put(tblName, new TableInfo(tblName, sch));

		// insert one record into tblcat
		RecordFile tcatfile = tcatInfo.open(tx, true);
		tcatfile.insert();
		tcatfile.setVal(TCAT_TBLNAME, new VarcharConstant(tblName));
		tcatfile.close();

		// insert a record into fldcat for each field
		RecordFile fcatfile = fcatInfo.open(tx, true);
		for (String fldname : sch.fields()) {
			fcatfile.insert();
			fcatfile.setVal(FCAT_TBLNAME, new VarcharConstant(tblName));
			fcatfile.setVal(FCAT_FLDNAME, new VarcharConstant(fldname));
			fcatfile.setVal(FCAT_TYPE, new IntegerConstant(sch.type(fldname)
					.getSqlType()));
			fcatfile.setVal(FCAT_TYPEARG, new IntegerConstant(sch.type(fldname)
					.getArgument()));
		}
		fcatfile.close();
	}
470112
private void updateTable(Connection connection) throws DatabaseException {
        try {
			// Add the SOURCEHISTORYID column to the db if necessary
			if (!DbUtils.hasColumn(connection, TABLE_NAME, SOURCEHISTORYID)) {
			    DbUtils.execute(connection, "ALTER TABLE "+TABLE_NAME+" ADD COLUMN "+SOURCEHISTORYID+" INT DEFAULT 0");
			}
			
			// Add the ATTACK column to the db if necessary
			if (!DbUtils.hasColumn(connection, TABLE_NAME, ATTACK)) {
			    DbUtils.execute(connection, "ALTER TABLE "+TABLE_NAME+" ADD COLUMN "+ATTACK+" VARCHAR(32768) DEFAULT ''");
			}
			
			if (!DbUtils.hasColumn(connection, TABLE_NAME, EVIDENCE)) {
				// Evidence, cweId and wascId all added at the same time
			    DbUtils.execute(connection, "ALTER TABLE "+TABLE_NAME+" ADD COLUMN "+EVIDENCE+" VARCHAR(16777216) DEFAULT ''");
			    DbUtils.execute(connection, "ALTER TABLE "+TABLE_NAME+" ADD COLUMN "+CWEID+" INT DEFAULT -1");
			    DbUtils.execute(connection, "ALTER TABLE "+TABLE_NAME+" ADD COLUMN "+WASCID+" INT DEFAULT -1");
			}
			
			if (!DbUtils.hasIndex(connection, TABLE_NAME, ALERT_INDEX)) {
				// this speads up session loading
				DbUtils.execute(connection, "CREATE INDEX "+ALERT_INDEX+" ON "+TABLE_NAME+" ("+SOURCEHISTORYID+")");
			}

			if (!DbUtils.hasColumn(connection, TABLE_NAME, SOURCEID)) {
				DbUtils.execute(connection, "ALTER TABLE "+TABLE_NAME+" ADD COLUMN "+SOURCEID+" INT DEFAULT 0");
				DbUtils.execute(connection, "CREATE INDEX INDEX_ALERT_SOURCEID ON "+TABLE_NAME+" ("+SOURCEID+")");
			}
		} catch (SQLException e) {
			throw new DatabaseException(e);
		}
        
    }
1274393
public static void createTables( Connection connection ) throws IOException, SQLException {

        /*
         * gps log table
         */
        StringBuilder sB = new StringBuilder();
        sB.append("CREATE TABLE ");
        sB.append(TABLE_GPSLOGS);
        sB.append(" (");
        sB.append(GpsLogsTableFields.COLUMN_ID.getFieldName() + " INTEGER PRIMARY KEY, ");
        sB.append(GpsLogsTableFields.COLUMN_LOG_STARTTS.getFieldName()).append(" LONG NOT NULL,");
        sB.append(GpsLogsTableFields.COLUMN_LOG_ENDTS.getFieldName()).append(" LONG NOT NULL,");
        sB.append(GpsLogsTableFields.COLUMN_LOG_LENGTHM.getFieldName()).append(" REAL NOT NULL, ");
        sB.append(GpsLogsTableFields.COLUMN_LOG_ISDIRTY.getFieldName()).append(" INTEGER NOT NULL, ");
        sB.append(GpsLogsTableFields.COLUMN_LOG_TEXT.getFieldName()).append(" TEXT NOT NULL ");
        sB.append(");");
        String CREATE_TABLE_GPSLOGS = sB.toString();

        /*
         * gps log data table
         */
        sB = new StringBuilder();
        sB.append("CREATE TABLE ");
        sB.append(TABLE_GPSLOG_DATA);
        sB.append(" (");
        sB.append(GpsLogsDataTableFields.COLUMN_ID.getFieldName() + " INTEGER PRIMARY KEY, ");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_LON.getFieldName()).append(" REAL NOT NULL, ");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_LAT.getFieldName()).append(" REAL NOT NULL,");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_ALTIM.getFieldName()).append(" REAL NOT NULL,");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_TS.getFieldName()).append(" DATE NOT NULL,");
        sB.append(GpsLogsDataTableFields.COLUMN_LOGID.getFieldName()).append(" INTEGER NOT NULL ");
        sB.append("CONSTRAINT ");
        sB.append(GpsLogsDataTableFields.COLUMN_LOGID.getFieldName());
        sB.append(" REFERENCES ");
        sB.append(TABLE_GPSLOGS);
        sB.append("(" + GpsLogsTableFields.COLUMN_ID.getFieldName() + ") ON DELETE CASCADE");
        sB.append(");");
        String CREATE_TABLE_GPSLOG_DATA = sB.toString();

        sB = new StringBuilder();
        sB.append("CREATE INDEX gpslog_id_idx ON ");
        sB.append(TABLE_GPSLOG_DATA);
        sB.append(" ( ");
        sB.append(GpsLogsDataTableFields.COLUMN_LOGID.getFieldName());
        sB.append(" );");
        String CREATE_INDEX_GPSLOG_ID = sB.toString();

        sB = new StringBuilder();
        sB.append("CREATE INDEX gpslog_ts_idx ON ");
        sB.append(TABLE_GPSLOG_DATA);
        sB.append(" ( ");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_TS.getFieldName());
        sB.append(" );");
        String CREATE_INDEX_GPSLOG_TS = sB.toString();

        sB = new StringBuilder();
        sB.append("CREATE INDEX gpslog_x_by_y_idx ON ");
        sB.append(TABLE_GPSLOG_DATA);
        sB.append(" ( ");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_LON.getFieldName());
        sB.append(", ");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_LAT.getFieldName());
        sB.append(" );");
        String CREATE_INDEX_GPSLOG_X_BY_Y = sB.toString();

        sB = new StringBuilder();
        sB.append("CREATE INDEX gpslog_logid_x_y_idx ON ");
        sB.append(TABLE_GPSLOG_DATA);
        sB.append(" ( ");
        sB.append(GpsLogsDataTableFields.COLUMN_LOGID.getFieldName());
        sB.append(", ");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_LON.getFieldName());
        sB.append(", ");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_LAT.getFieldName());
        sB.append(" );");
        String CREATE_INDEX_GPSLOG_LOGID_X_Y = sB.toString();

        /*
         * properties table
         */
        sB = new StringBuilder();
        sB.append("CREATE TABLE ");
        sB.append(TABLE_GPSLOG_PROPERTIES);
        sB.append(" (");
        sB.append(GpsLogsPropertiesTableFields.COLUMN_ID.getFieldName());
        sB.append(" INTEGER PRIMARY KEY, ");
        sB.append(GpsLogsPropertiesTableFields.COLUMN_LOGID.getFieldName());
        sB.append(" INTEGER NOT NULL ");
        sB.append("CONSTRAINT " + GpsLogsPropertiesTableFields.COLUMN_LOGID.getFieldName() + " REFERENCES ");
        sB.append(TABLE_GPSLOGS);
        sB.append("(");
        sB.append(GpsLogsTableFields.COLUMN_ID);
        sB.append(") ON DELETE CASCADE,");
        sB.append(GpsLogsPropertiesTableFields.COLUMN_PROPERTIES_COLOR.getFieldName()).append(" TEXT NOT NULL, ");
        sB.append(GpsLogsPropertiesTableFields.COLUMN_PROPERTIES_WIDTH.getFieldName()).append(" REAL NOT NULL, ");
        sB.append(GpsLogsPropertiesTableFields.COLUMN_PROPERTIES_VISIBLE.getFieldName()).append(" INTEGER NOT NULL");
        sB.append(");");
        String CREATE_TABLE_GPSLOGS_PROPERTIES = sB.toString();

        try (Statement statement = connection.createStatement()) {
            statement.setQueryTimeout(30); // set timeout to 30 sec.

            statement.executeUpdate(CREATE_TABLE_GPSLOGS);

            statement.executeUpdate(CREATE_TABLE_GPSLOG_DATA);
            statement.executeUpdate(CREATE_INDEX_GPSLOG_ID);
            statement.executeUpdate(CREATE_INDEX_GPSLOG_TS);
            statement.executeUpdate(CREATE_INDEX_GPSLOG_X_BY_Y);
            statement.executeUpdate(CREATE_INDEX_GPSLOG_LOGID_X_Y);

            statement.executeUpdate(CREATE_TABLE_GPSLOGS_PROPERTIES);
        } catch (Exception e) {
            throw new IOException(e.getLocalizedMessage());
        }

    }
1020621
public static void createEmptyTable(Connection connection, String tableReference) throws SQLException {
        try (Statement statement = connection.createStatement()) {
            statement.execute("CREATE TABLE "+ tableReference+ " ()");
        }
    }