agggen.java

来自「数据仓库展示程序」· Java 代码 · 共 983 行 · 第 1/3 页

            pw.print(
                agg.getExpression(sqlQuery.getDialect().quoteIdentifier(
                    factTableName, c.getName())).toUpperCase());
/*
            pw.print("SUM(");
            pw.print(sqlQuery.getDialect().quoteIdentifier(factTableName, c.getName()));
            pw.print(") AS ");
*/
            pw.print(" AS ");
            pw.print(sqlQuery.getDialect().quoteIdentifier(c.getName()));
            pw.println(',');
        }

        // do fact_count
        pw.print(prefix);
        pw.print("COUNT(*) AS ");
        pw.println(sqlQuery.getDialect().quoteIdentifier(getFactCount()));

        pw.println("FROM ");
        pw.print(prefix);
        pw.print(sqlQuery.getDialect().quoteIdentifier(factTableName));
        pw.print(" ");
        pw.println(sqlQuery.getDialect().quoteIdentifier(factTableName));

        pw.println("GROUP BY ");
        for (Iterator it = notLostColumnUsages.iterator(); it.hasNext(); ) {
            JdbcSchema.Table.Column.Usage usage = 
                    (JdbcSchema.Table.Column.Usage) it.next();
            JdbcSchema.Table.Column c = usage.getColumn();

            pw.print(prefix);
            pw.print(sqlQuery.getDialect().quoteIdentifier(factTableName, c.getName()));
            if (it.hasNext()) {
                pw.println(',');
            } else {
                pw.println(';');
            }
        }

        return sw.toString();
    }
    /** 
     * Return a String containing the sql code to create a collapsed dimension 
     * table. 
     * 
     * @return collapsed dimension sql code
     */
    public String createCollapsed() {
        StringWriter sw = new StringWriter(512);
        PrintWriter pw = new PrintWriter(sw);
        String prefix = "    ";

        pw.print("CREATE TABLE ");
        pw.print(makeCollapsedAggregateTableName(getFactTableName()));
        pw.println(" (");

        // do foreign keys
        for (Iterator it = collapsedColumnUsages.values().iterator(); 
                    it.hasNext(); ) {
            List list = (List) it.next();
            for (Iterator lit = list.iterator(); lit.hasNext(); ) {
                JdbcSchema.Table.Column.Usage usage = 
                    (JdbcSchema.Table.Column.Usage) lit.next();
                addColumnCreate(pw, prefix, usage);
            }
        }

        // do measures
        for (Iterator it = measures.iterator(); it.hasNext(); ) {
            JdbcSchema.Table.Column.Usage usage = 
                (JdbcSchema.Table.Column.Usage) it.next();

            addColumnCreate(pw, prefix, usage);
        }
        // do fact_count
        pw.print(prefix);
        pw.print(getFactCount());
        pw.println(" INTEGER NOT NULL");

        pw.println(");");
        return sw.toString();
    }
    /** 
     * Return the sql code to populate a collapsed dimension table from 
     * the fact table. 
     * 
     * @return 
     */
    public String insertIntoCollapsed() {
        StringWriter sw = new StringWriter(512);
        PrintWriter pw = new PrintWriter(sw);
        String prefix = "    ";
        String factTableName = getFactTableName();
        SqlQuery sqlQuery = getSqlQuery();

        pw.print("INSERT INTO ");
        pw.print(makeCollapsedAggregateTableName(getFactTableName()));
        pw.println(" (");


        for (Iterator it = collapsedColumnUsages.values().iterator(); 
                    it.hasNext(); ) {
            List list = (List) it.next();
            for (Iterator lit = list.iterator(); lit.hasNext(); ) {
                JdbcSchema.Table.Column.Usage usage = 
                    (JdbcSchema.Table.Column.Usage) lit.next();
                JdbcSchema.Table.Column c = usage.getColumn();
                pw.print(prefix);
                if (usage.usagePrefix != null) {
                    pw.print(usage.usagePrefix);
                }
                pw.print(c.getName());
                pw.println(',');
            }
        }

        for (Iterator it = measures.iterator(); it.hasNext(); ) {
            JdbcSchema.Table.Column.Usage usage = 
                (JdbcSchema.Table.Column.Usage) it.next();
            JdbcSchema.Table.Column c = usage.getColumn();

            pw.print(prefix);
            String name = getUsageName(usage);
            pw.print(name);
            //pw.print(c.getName());
            pw.println(',');
        }
        // do fact_count
        pw.print(prefix);
        pw.print(getFactCount());
        pw.println(")");

        pw.println("SELECT");
        int dimCnt = 0;
        for (Iterator it = collapsedColumnUsages.values().iterator(); 
                    it.hasNext(); ) {
            List list = (List) it.next();
            for (Iterator lit = list.iterator(); lit.hasNext(); ) {
                JdbcSchema.Table.Column.Usage usage = 
                    (JdbcSchema.Table.Column.Usage) lit.next();
                JdbcSchema.Table.Column c = usage.getColumn();
                JdbcSchema.Table t = c.getTable();

                pw.print(prefix);
                pw.print(sqlQuery.getDialect().quoteIdentifier(t.getName(), c.getName()));
                pw.print(" AS ");
                String n = (usage.usagePrefix == null)
                    ? c.getName() : usage.usagePrefix + c.getName();
                pw.print(sqlQuery.getDialect().quoteIdentifier(n));
                pw.println(',');
            }
        }
        for (Iterator it = measures.iterator(); it.hasNext(); ) {
            JdbcSchema.Table.Column.Usage usage = 
                (JdbcSchema.Table.Column.Usage) it.next();
            JdbcSchema.Table.Column c = usage.getColumn();
            JdbcSchema.Table t = c.getTable();
            RolapAggregator agg = usage.getAggregator();

            pw.print(prefix);
            pw.print(
                agg.getExpression(sqlQuery.getDialect().quoteIdentifier(
                    t.getName(), c.getName())).toUpperCase());
/*
            pw.print("SUM(");
            pw.print(sqlQuery.getDialect().quoteIdentifier(t.getName(), c.getName()));
            pw.print(") AS ");
*/
            pw.print(" AS ");
            pw.print(sqlQuery.getDialect().quoteIdentifier(c.getName()));
            pw.println(',');
        }

        // do fact_count
        pw.print(prefix);
        pw.print("COUNT(*) AS ");
        pw.println(sqlQuery.getDialect().quoteIdentifier(getFactCount()));

        pw.println("FROM ");
        pw.print(prefix);
        pw.print(sqlQuery.getDialect().quoteIdentifier(factTableName));
        pw.print(" ");
        pw.print(sqlQuery.getDialect().quoteIdentifier(factTableName));
        pw.println(',');

        // add dimension tables
        dimCnt = 0;
        for (Iterator it = collapsedColumnUsages.keySet().iterator(); 
                    it.hasNext(); ) {
            RolapStar.Table rt = (RolapStar.Table) it.next();

            pw.print(prefix);
            pw.print(sqlQuery.getDialect().quoteIdentifier(rt.getAlias()));
            pw.print(" AS ");
            pw.print(sqlQuery.getDialect().quoteIdentifier(rt.getAlias()));

            // walk up tables
            if (rt.getParentTable() != null) {
                while (rt.getParentTable().getParentTable() != null) {
                    rt = rt.getParentTable();

                    pw.println(',');

                    pw.print(prefix);
                    pw.print(sqlQuery.getDialect().quoteIdentifier(rt.getAlias()));
                    pw.print(" AS ");
                    pw.print(sqlQuery.getDialect().quoteIdentifier(rt.getAlias()));
                }
            }

            if (it.hasNext()) {
                pw.println(',');
            } else {
                pw.println();
            }
        }

        pw.println("WHERE ");
        for (Iterator it = collapsedColumnUsages.keySet().iterator(); 
                    it.hasNext(); ) {
            RolapStar.Table rt = (RolapStar.Table) it.next();

            RolapStar.Condition cond = rt.getCondition();
            if (cond == null) {
                continue;
            }
            pw.print(prefix);
            pw.print(cond.toString(sqlQuery));

            if (rt.getParentTable() != null) {
                while (rt.getParentTable().getParentTable() != null) {
                    rt = rt.getParentTable();
                    cond = rt.getCondition();

                    pw.println(" and");

                    pw.print(prefix);
                    pw.print(cond.toString(sqlQuery));
                }
            }

            if (it.hasNext()) {
                pw.println(" and");
            } else {
                pw.println();
            }
        }

        pw.println("GROUP BY ");
        dimCnt = 0;
        for (Iterator it = collapsedColumnUsages.values().iterator(); 
                    it.hasNext(); ) {
            List list = (List) it.next();
            for (Iterator lit = list.iterator(); lit.hasNext(); ) {
                JdbcSchema.Table.Column.Usage usage = 
                    (JdbcSchema.Table.Column.Usage) lit.next();
                JdbcSchema.Table.Column c = usage.getColumn();
                JdbcSchema.Table t = c.getTable();

                pw.print(prefix);
                pw.print(sqlQuery.getDialect().quoteIdentifier(t.getName(), c.getName()));

                if (it.hasNext()) {
                    pw.println(',');
                } else {
                    pw.println(';');
                }
            }
        }

        return sw.toString();
    }



    private String getUsageName(final JdbcSchema.Table.Column.Usage usage) {
        JdbcSchema.Table.Column c = usage.getColumn();
        String name = c.getName();
        // if its a measure which is based upon a foreign key, then
        // the foreign key column name is already used (for the foreign key
        // column) so we must choose a different name.
        if (usage.getColumnType() == JdbcSchema.MEASURE_COLUMN_USAGE) {
            if (c.hasUsage(JdbcSchema.FOREIGN_KEY_COLUMN_USAGE)) {
                name = usage.getSymbolicName().replace(' ', '_').toUpperCase();
            }
        }
        return name;
    }

    private void addColumnCreate(final PrintWriter pw, 
                                 final String prefix,
                                 final JdbcSchema.Table.Column.Usage usage) {
        JdbcSchema.Table.Column c = usage.getColumn();
        String name = getUsageName(usage);

        pw.print(prefix);
        if (usage.usagePrefix != null) {
            pw.print(usage.usagePrefix);
        }
        pw.print(name);
        pw.print(' ');
        pw.print(c.getTypeName().toUpperCase());
        switch (c.getType()) {
        case Types.NUMERIC :
        case Types.DECIMAL :
            pw.print('(');
            pw.print(c.getNumPrecRadix());
            pw.print(",");
            pw.print(c.getDecimalDigits());
            pw.print(')');
            break;
        case Types.CHAR :
        case Types.VARCHAR :
            pw.print('(');
            pw.print(c.getCharOctetLength());
            pw.print(')');
            break;
        default :
        }
        if (! c.isNullable()) {
            pw.print(" NOT NULL");
        }
        pw.println(',');

    }
}