private String getUniqueConstraintsSqlInformix(InformixDatabase database, Schema schema, String name) {
    """
	Gets an SQL query that returns the constraint names and columns for all UNIQUE constraints.
	@param database A database object of the InformixDatabase type
	@param schema Name of the schema to examine (or null for all)
	@param name Name of the constraint to examine (or null for all)
	@return A lengthy SQL statement that fetches the constraint names and columns
    """
        StringBuffer sqlBuf = new StringBuffer();

        sqlBuf.append("SELECT * FROM (\n");

        // Yes, I am serious about this. It appears there are neither CTE/WITH clauses nor PIVOT/UNPIVOT operators
        // in Informix SQL.
        for (int i=1; i<=16; i++) {
            if (i>1)
                sqlBuf.append("UNION ALL\n");
            sqlBuf.append(
                String.format("  SELECT\n" +
                        "    CONS.owner,\n" +
                        "    CONS.constrname AS constraint_name,\n" +
                        "    COL.colname AS column_name,\n" +
                        "    CONS.constrtype,\n" +
                        "    %d               AS column_index\n" +
                        "  FROM informix.sysconstraints CONS\n" +
                        "    JOIN informix.sysindexes IDX ON CONS.idxname = IDX.idxname\n" +
                        "    JOIN informix.syscolumns COL ON COL.tabid = CONS.tabid AND COL.colno = IDX.part%d\n",
                        i, i
                )
            );
        }

        // Finish the subquery and filter on the U(NIQUE) constraint type
        sqlBuf.append(
                "                ) SUBQ\n" +
                "WHERE constrtype='U' \n");

        String catalogName = database.correctObjectName(schema.getCatalogName(), Catalog.class);
        String constraintName = database.correctObjectName(name, UniqueConstraint.class);

        // If possible, filter for catalog name and/or constraint name
        if (catalogName != null) {
            sqlBuf.append("AND owner='" + catalogName + "'\n");
        }
        if (constraintName != null) {
            sqlBuf.append("AND constraint_name='" + constraintName + "'");
        }

        // For correct processing, it is important that we get all columns in order.
        sqlBuf.append("ORDER BY owner, constraint_name, column_index");

        // Return the query
        return sqlBuf.toString();
    }
private String getUniqueConstraintsSqlInformix(InformixDatabase database, Schema schema, String name) {
        StringBuffer sqlBuf = new StringBuffer();

        sqlBuf.append("SELECT * FROM (\n");

        // Yes, I am serious about this. It appears there are neither CTE/WITH clauses nor PIVOT/UNPIVOT operators
        // in Informix SQL.
        for (int i=1; i<=16; i++) {
            if (i>1)
                sqlBuf.append("UNION ALL\n");
            sqlBuf.append(
                String.format("  SELECT\n" +
                        "    CONS.owner,\n" +
                        "    CONS.constrname AS constraint_name,\n" +
                        "    COL.colname AS column_name,\n" +
                        "    CONS.constrtype,\n" +
                        "    %d               AS column_index\n" +
                        "  FROM informix.sysconstraints CONS\n" +
                        "    JOIN informix.sysindexes IDX ON CONS.idxname = IDX.idxname\n" +
                        "    JOIN informix.syscolumns COL ON COL.tabid = CONS.tabid AND COL.colno = IDX.part%d\n",
                        i, i
                )
            );
        }

        // Finish the subquery and filter on the U(NIQUE) constraint type
        sqlBuf.append(
                "                ) SUBQ\n" +
                "WHERE constrtype='U' \n");

        String catalogName = database.correctObjectName(schema.getCatalogName(), Catalog.class);
        String constraintName = database.correctObjectName(name, UniqueConstraint.class);

        // If possible, filter for catalog name and/or constraint name
        if (catalogName != null) {
            sqlBuf.append("AND owner='" + catalogName + "'\n");
        }
        if (constraintName != null) {
            sqlBuf.append("AND constraint_name='" + constraintName + "'");
        }

        // For correct processing, it is important that we get all columns in order.
        sqlBuf.append("ORDER BY owner, constraint_name, column_index");

        // Return the query
        return sqlBuf.toString();
    }
private String getUniqueConstraintsSqlInformix ( InformixDatabase database , Schema schema , String name ) { StringBuffer sqlBuf = new StringBuffer ( ) ; sqlBuf . append ( "SELECT * FROM (\n" ) ; // Yes, I am serious about this. It appears there are neither CTE/WITH clauses nor PIVOT/UNPIVOT operators // in Informix SQL. for ( int i = 1 ; i <= 16 ; i ++ ) { if ( i > 1 ) sqlBuf . append ( "UNION ALL\n" ) ; sqlBuf . append ( String . format ( "  SELECT\n" + "    CONS.owner,\n" + "    CONS.constrname AS constraint_name,\n" + "    COL.colname AS column_name,\n" + "    CONS.constrtype,\n" + "    %d               AS column_index\n" + "  FROM informix.sysconstraints CONS\n" + "    JOIN informix.sysindexes IDX ON CONS.idxname = IDX.idxname\n" + "    JOIN informix.syscolumns COL ON COL.tabid = CONS.tabid AND COL.colno = IDX.part%d\n" , i , i ) ) ; } // Finish the subquery and filter on the U(NIQUE) constraint type sqlBuf . append ( "                ) SUBQ\n" + "WHERE constrtype='U' \n" ) ; String catalogName = database . correctObjectName ( schema . getCatalogName ( ) , Catalog . class ) ; String constraintName = database . correctObjectName ( name , UniqueConstraint . class ) ; // If possible, filter for catalog name and/or constraint name if ( catalogName != null ) { sqlBuf . append ( "AND owner='" + catalogName + "'\n" ) ; } if ( constraintName != null ) { sqlBuf . append ( "AND constraint_name='" + constraintName + "'" ) ; } // For correct processing, it is important that we get all columns in order. sqlBuf . append ( "ORDER BY owner, constraint_name, column_index" ) ; // Return the query return sqlBuf . toString ( ) ; }
Gets an SQL query that returns the constraint names and columns for all UNIQUE constraints.
@param database A database object of the InformixDatabase type
@param schema Name of the schema to examine (or null for all)
@param name Name of the constraint to examine (or null for all)
@return A lengthy SQL statement that fetches the constraint names and columns
Gets an SQL query that returns the constraint names and columns for all UNIQUE constraints .