Examples with Field cbit.sql.Field used on opensource projects

Example 1 with Field

use of cbit.sql.Field in project vcell by virtualcell.

the class MigrateDbManager method migrate.

public static void migrate(Table table, ConnectionFactory conFactory_from, ConnectionFactory conFactory_to) throws SQLException {
    System.out.println("starting migration of table " + table.getTableName());
    assert (conFactory_from.getDatabaseSyntax() == DatabaseSyntax.ORACLE);
    assert (conFactory_to.getDatabaseSyntax() == DatabaseSyntax.POSTGRES);
    Connection con_from = null;
    Connection con_to = null;
    try {
        con_from = conFactory_from.getConnection(new Object());
        con_to = conFactory_to.getConnection(new Object());
        con_from.setReadOnly(true);
        con_to.setReadOnly(false);
        // 
        // get Last key in target table (using the "to" connection) - NULL if no records.
        // 
        String sql = "SELECT ID FROM " + table.getTableName() + " ORDER BY ID DESC LIMIT 1";
        System.out.println(sql);
        // 
        // should disable self-referential reference in vc_struct table (field "parentRef")
        // 
        KeyValue lastkey = null;
        try (Statement stmt_to_init = con_to.createStatement();
            ResultSet rset_to_init = stmt_to_init.executeQuery(sql)) {
            if (rset_to_init.next()) {
                BigDecimal keyBigDecimal = rset_to_init.getBigDecimal(1);
                if (!rset_to_init.wasNull()) {
                    lastkey = new KeyValue(keyBigDecimal);
                }
            }
        }
        // 
        // gather fields to copy
        // 
        ArrayList<Field> fieldsToCopy = new ArrayList<Field>();
        for (Field f : table.getFields()) {
            // if (f.getSqlDataType() == SQLDataType.clob_text || f.getSqlDataType() == SQLDataType.blob_bytea){
            // continue; // insert in another pass
            // }
            fieldsToCopy.add(f);
        }
        // 
        // form the query using the "from" connection
        // 
        StringBuffer selectBuffer = new StringBuffer();
        selectBuffer.append("SELECT ");
        for (int i = 0; i < fieldsToCopy.size(); i++) {
            selectBuffer.append(fieldsToCopy.get(i).getUnqualifiedColName());
            if (i < fieldsToCopy.size() - 1) {
                selectBuffer.append(",");
            }
        }
        selectBuffer.append(" FROM " + table.getTableName());
        if (lastkey != null) {
            selectBuffer.append(" WHERE id > " + lastkey + " ");
        }
        selectBuffer.append(" ORDER BY id");
        System.out.println(selectBuffer.toString());
        // 
        // form the prepared INSERT statement for the "to" connection
        // 
        StringBuffer insertBuffer = new StringBuffer();
        insertBuffer.append("INSERT INTO " + table.getTableName() + " values (");
        for (int i = 0; i < fieldsToCopy.size(); i++) {
            insertBuffer.append("?");
            if (i < fieldsToCopy.size() - 1) {
                insertBuffer.append(",");
            }
        }
        insertBuffer.append(")");
        System.out.println(insertBuffer.toString());
        try (Statement stmt_from = con_from.createStatement();
            ResultSet rset_from = stmt_from.executeQuery(selectBuffer.toString());
            PreparedStatement stmt_to = con_to.prepareStatement(insertBuffer.toString())) {
            long rowCount = 0;
            long batchCount = 0;
            while (rset_from.next()) {
                for (int i = 0; i < fieldsToCopy.size(); i++) {
                    Field f = fieldsToCopy.get(i);
                    switch(f.getSqlDataType().basicDataType) {
                        case BLOB:
                            {
                                boolean found = false;
                                Object lob_object = rset_from.getObject(i + 1);
                                if (!rset_from.wasNull()) {
                                    if (lob_object instanceof java.sql.Blob) {
                                        java.sql.Blob blob_object = (java.sql.Blob) lob_object;
                                        byte[] bytes = blob_object.getBytes((long) 1, (int) blob_object.length());
                                        stmt_to.setBytes(i + 1, bytes);
                                        found = true;
                                    }
                                }
                                if (!found) {
                                    stmt_to.setNull(i + 1, java.sql.Types.LONGVARBINARY);
                                }
                                break;
                            }
                        case CLOB:
                            {
                                boolean found = false;
                                Object lob_object = rset_from.getObject(i + 1);
                                if (!rset_from.wasNull()) {
                                    if (lob_object instanceof java.sql.Clob) {
                                        java.sql.Clob clob_object = (java.sql.Clob) lob_object;
                                        byte[] ins = new byte[(int) clob_object.length()];
                                        try {
                                            clob_object.getAsciiStream().read(ins);
                                            String str = new String(ins);
                                            stmt_to.setString(i + 1, str);
                                            found = true;
                                        } catch (IOException e) {
                                            e.printStackTrace();
                                        }
                                    }
                                }
                                if (!found) {
                                    stmt_to.setNull(i + 1, java.sql.Types.LONGVARCHAR);
                                }
                                break;
                            }
                        case CHAR:
                            {
                                String str = rset_from.getString(i + 1);
                                if (!rset_from.wasNull()) {
                                    stmt_to.setString(i + 1, str);
                                } else {
                                    stmt_to.setNull(i + 1, java.sql.Types.CHAR);
                                }
                                break;
                            }
                        case VARCHAR:
                            {
                                String str = rset_from.getString(i + 1);
                                if (!rset_from.wasNull()) {
                                    stmt_to.setString(i + 1, str);
                                } else {
                                    stmt_to.setNull(i + 1, java.sql.Types.VARCHAR);
                                }
                                break;
                            }
                        case DATE:
                            {
                                Date date = rset_from.getDate(i + 1);
                                if (!rset_from.wasNull()) {
                                    stmt_to.setDate(i + 1, date);
                                } else {
                                    stmt_to.setNull(i + 1, java.sql.Types.DATE);
                                }
                                break;
                            }
                        case BIGINT:
                            {
                                BigDecimal bigDecimal = rset_from.getBigDecimal(i + 1);
                                if (!rset_from.wasNull()) {
                                    stmt_to.setBigDecimal(i + 1, bigDecimal);
                                } else {
                                    stmt_to.setNull(i + 1, java.sql.Types.BIGINT);
                                }
                                break;
                            }
                        case NUMERIC:
                            {
                                BigDecimal bigDecimal = rset_from.getBigDecimal(i + 1);
                                if (!rset_from.wasNull()) {
                                    stmt_to.setBigDecimal(i + 1, bigDecimal);
                                } else {
                                    stmt_to.setNull(i + 1, java.sql.Types.NUMERIC);
                                }
                                break;
                            }
                        default:
                            {
                                throw new RuntimeException("support for JDBC Type " + f.getSqlDataType().basicDataType + " not yet supported");
                            }
                    }
                }
                stmt_to.addBatch();
                rowCount++;
                batchCount++;
                if (batchCount >= 300) {
                    System.out.println("writing " + batchCount + " of " + rowCount + " records into table " + table.getTableName());
                    batchCount = 0;
                    stmt_to.executeBatch();
                    con_to.commit();
                }
            }
            if (batchCount > 0) {
                stmt_to.executeBatch();
                con_to.commit();
            }
        }
        // end try
        System.out.println("starting migration of table " + table.getTableName());
    } finally {
        if (con_from != null)
            con_from.close();
        if (con_to != null)
            con_to.close();
    }
}

Example 2 with Field

use of cbit.sql.Field in project vcell by virtualcell.

the class GeomDbDriver method getFilaments.

/**
 * Insert the method's description here.
 * Creation date: (7/29/00 2:10:42 PM)
 * @param con java.sql.Connection
 * @param geom cbit.vcell.geometry.Geometry
 */
private void getFilaments(Connection con, Geometry geom) throws SQLException, DataAccessException {
    String sql = null;
    sql = " SELECT " + filamentTable.filamentName.getQualifiedColName() + "," + curveTable.curveData.getQualifiedColName() + " FROM " + filamentTable.getTableName() + "," + curveTable.getTableName() + " WHERE " + filamentTable.geometryRef.getQualifiedColName() + " = " + geom.getVersion().getVersionKey() + " AND " + curveTable.filamentRef.getQualifiedColName() + " = " + filamentTable.id.getQualifiedColName();
    // System.out.println(sql);
    Statement stmt = con.createStatement();
    try {
        ResultSet rset = stmt.executeQuery(sql);
        while (rset.next()) {
            String filColName = filamentTable.filamentName.toString();
            String filamentType = rset.getString(filColName);
            Field curveCol = curveTable.curveData;
            // Curve curve = CurveTable.decodeCurve(new String(rset.getBytes(curveColName)));
            String curveString = (String) DbDriver.getLOB(rset, curveCol, dbSyntax);
            Curve curve = CurveTable.decodeCurve(curveString);
            // 
            geom.getGeometrySpec().getFilamentGroup().addCurve(filamentType, curve);
        }
    } catch (Exception e) {
        throw new DataAccessException(e.toString());
    } finally {
        // Release resources include resultset
        stmt.close();
    }
}

Example 3 with Field

use of cbit.sql.Field in project vcell by virtualcell.

the class GeometryTable method getInfoSQL.

/**
 * This method was created in VisualAge.
 * @return java.lang.String
 */
public String getInfoSQL(User user, String extraConditions, String special, boolean bCheckPermission, DatabaseSyntax dbSyntax) {
    UserTable userTable = UserTable.table;
    GeometryTable gTable = this;
    ExtentTable eTable = ExtentTable.table;
    SoftwareVersionTable swvTable = SoftwareVersionTable.table;
    String sql;
    Field[] f = { userTable.userid, new cbit.sql.StarField(gTable), eTable.extentX, eTable.extentY, eTable.extentZ, swvTable.softwareVersion };
    Table[] t = { gTable, userTable, eTable, swvTable };
    switch(dbSyntax) {
        case ORACLE:
            {
                String condition = // links in the extent table
                eTable.id.getQualifiedColName() + " = " + gTable.extentRef.getQualifiedColName() + " AND " + userTable.id.getQualifiedColName() + " = " + // links in the userTable
                gTable.ownerRef.getQualifiedColName() + " AND " + gTable.id.getQualifiedColName() + " = " + swvTable.versionableRef.getQualifiedColName() + "(+) ";
                if (extraConditions != null && extraConditions.trim().length() > 0) {
                    condition += " AND " + extraConditions;
                }
                sql = DatabasePolicySQL.enforceOwnershipSelect(user, f, t, (OuterJoin) null, condition, special, dbSyntax, bCheckPermission);
                return sql;
            }
        case POSTGRES:
            {
                String condition = // links in the extent table
                eTable.id.getQualifiedColName() + " = " + gTable.extentRef.getQualifiedColName() + " AND " + userTable.id.getQualifiedColName() + " = " + gTable.ownerRef.getQualifiedColName() + // links in the userTable
                " ";
                // " AND " + gTable.id.getQualifiedColName() + " = " + swvTable.versionableRef.getQualifiedColName()+"(+) ";
                if (extraConditions != null && extraConditions.trim().length() > 0) {
                    condition += " AND " + extraConditions;
                }
                OuterJoin outerJoin = new OuterJoin(gTable, swvTable, JoinOp.LEFT_OUTER_JOIN, gTable.id, swvTable.versionableRef);
                sql = DatabasePolicySQL.enforceOwnershipSelect(user, f, t, outerJoin, condition, special, dbSyntax, bCheckPermission);
                return sql;
            }
        default:
            {
                throw new RuntimeException("unexpected DatabaseSyntax " + dbSyntax);
            }
    }
}

Example 4 with Field

use of cbit.sql.Field in project vcell by virtualcell.

the class MathDescTable method getInfoSQL.

/**
 * This method was created in VisualAge.
 * @return java.lang.String
 */
public String getInfoSQL(User user, String extraConditions, String special, DatabaseSyntax dbSyntax) {
    UserTable userTable = UserTable.table;
    MathDescTable vTable = MathDescTable.table;
    SoftwareVersionTable swvTable = SoftwareVersionTable.table;
    String sql;
    // Field[] f = {userTable.userid,new cbit.sql.StarField(vTable)};
    Field[] f = new Field[] { vTable.id, userTable.userid, swvTable.softwareVersion };
    f = (Field[]) org.vcell.util.BeanUtils.addElements(f, vTable.versionFields);
    f = (Field[]) org.vcell.util.BeanUtils.addElement(f, vTable.geometryRef);
    Table[] t = { vTable, userTable, swvTable };
    switch(dbSyntax) {
        case ORACLE:
            {
                String condition = // links in the userTable
                userTable.id.getQualifiedColName() + " = " + vTable.ownerRef.getQualifiedColName() + " AND " + vTable.id.getQualifiedColName() + " = " + swvTable.versionableRef.getQualifiedColName() + "(+) ";
                if (extraConditions != null && extraConditions.trim().length() > 0) {
                    condition += " AND " + extraConditions;
                }
                sql = DatabasePolicySQL.enforceOwnershipSelect(user, f, t, (OuterJoin) null, condition, special, dbSyntax);
                return sql;
            }
        case POSTGRES:
            {
                // links in the userTable
                String condition = userTable.id.getQualifiedColName() + " = " + vTable.ownerRef.getQualifiedColName() + " ";
                // " AND " + vTable.id.getQualifiedColName() + " = " + swvTable.versionableRef.getQualifiedColName()+"(+) ";
                if (extraConditions != null && extraConditions.trim().length() > 0) {
                    condition += " AND " + extraConditions;
                }
                OuterJoin outerJoin = new OuterJoin(vTable, swvTable, JoinOp.LEFT_OUTER_JOIN, vTable.id, swvTable.versionableRef);
                sql = DatabasePolicySQL.enforceOwnershipSelect(user, f, t, outerJoin, condition, special, dbSyntax);
                return sql;
            }
        default:
            {
                throw new RuntimeException("unexpected DatabaseSyntax " + dbSyntax);
            }
    }
}

Example 5 with Field

use of cbit.sql.Field in project vcell by virtualcell.

the class MathDescriptionDbDriver method getMathDescriptionSQL.

/**
 * This method was created in VisualAge.
 * @return cbit.vcell.math.MathDescription
 * @param user cbit.vcell.server.User
 * @param mathDescKey cbit.sql.KeyValue
 */
private MathDescription getMathDescriptionSQL(QueryHashtable dbc, Connection con, User user, KeyValue mathDescKey) throws SQLException, DataAccessException, ObjectNotFoundException {
    String sql;
    Field[] f = { userTable.userid, new cbit.sql.StarField(mathDescTable) };
    Table[] t = { mathDescTable, userTable };
    String condition = mathDescTable.id.getQualifiedColName() + " = " + mathDescKey + " AND " + userTable.id.getQualifiedColName() + " = " + mathDescTable.ownerRef.getQualifiedColName();
    sql = DatabasePolicySQL.enforceOwnershipSelect(user, f, t, (OuterJoin) null, condition, null, dbSyntax);
    // System.out.println(sql);
    MathDescription mathDescription = null;
    Statement stmt = con.createStatement();
    try {
        ResultSet rset = stmt.executeQuery(sql);
        if (rset.next()) {
            // 
            // note: must call mathDescTable.getMathDescription() first (rset.getBytes(language) must be called first)
            // 
            mathDescription = mathDescTable.getMathDescription(rset, con, dbSyntax);
            // 
            // get Geometry reference and assign to mathDescription
            // 
            java.math.BigDecimal bigD = rset.getBigDecimal(MathDescTable.table.geometryRef.toString());
            KeyValue geomRef = null;
            if (!rset.wasNull()) {
                geomRef = new KeyValue(bigD);
            } else {
                throw new DataAccessException("Error:  Geometry Reference Cannot be Null for MathDescription");
            }
            Geometry geom = (Geometry) geomDB.getVersionable(dbc, con, user, VersionableType.Geometry, geomRef, false);
            try {
                mathDescription.setGeometry(geom);
            } catch (java.beans.PropertyVetoException e) {
                e.printStackTrace(System.out);
                throw new DataAccessException("DataAccess Exception: " + e.getMessage());
            }
        } else {
            throw new ObjectNotFoundException("MathDescription id=" + mathDescKey + " not found for user '" + user + "'");
        }
    } finally {
        // Release resources include resultset
        stmt.close();
    }
    return mathDescription;
}