Examples with SqlPhysicalModel org.pentaho.metadata.model.SqlPhysicalModel used on opensource projects

Example 1 with SqlPhysicalModel

use of org.pentaho.metadata.model.SqlPhysicalModel in project pentaho-kettle by pentaho.

the class MetadataGenerator method generatePhysicalMetadataModel.

public Domain generatePhysicalMetadataModel() throws KettleException {
    // First do some checking and lookups...
    // 
    String targetDatabaseName = ConceptUtil.getString(logicalDomain, DefaultIDs.DOMAIN_TARGET_DATABASE);
    if (Utils.isEmpty(targetDatabaseName)) {
        throw new KettleException("Please specify a target database!");
    }
    DatabaseMeta targetDatabaseMeta = DatabaseMeta.findDatabase(databases, targetDatabaseName);
    if (targetDatabaseMeta == null) {
        throw new KettleException("Target database with name '" + targetDatabaseName + "' can't be found!");
    }
    // Now start creation of a new domain with physical underpinning.
    // 
    Domain domain = new Domain();
    // Copy the domain information...
    // 
    domain.setId(createId("DOMAIN", null, domain));
    domain.setName(logicalDomain.getName());
    domain.setDescription(logicalDomain.getDescription());
    // 
    for (LogicalModel logicalModel : logicalDomain.getLogicalModels()) {
        // Copy model information...
        // 
        LogicalModel model = new LogicalModel();
        model.setId(createId("MODEL", domain, model));
        model.setName(logicalModel.getName());
        model.setDescription(logicalModel.getDescription());
        // Create a physical model...
        // 
        SqlPhysicalModel sqlModel = new SqlPhysicalModel();
        sqlModel.setDatasource(createSqlDataSource(targetDatabaseMeta));
        model.setPhysicalModel(sqlModel);
        for (LogicalTable logicalTable : logicalModel.getLogicalTables()) {
            LogicalTable table = new LogicalTable();
            table.setId(createId("LOGICAL_TABLE", logicalModel, logicalTable));
            table.setName(logicalTable.getName());
            table.setDescription(logicalTable.getDescription());
            String targetTable = ConceptUtil.getString(logicalTable, DefaultIDs.LOGICAL_TABLE_PHYSICAL_TABLE_NAME);
            SqlPhysicalTable sqlTable = new SqlPhysicalTable(sqlModel);
            table.setPhysicalTable(sqlTable);
            // Copy name & description from physical level...
            // 
            sqlTable.setId(createId("PHYSICAL_TABLE", logicalModel, logicalTable));
            sqlTable.setName(logicalTable.getName());
            sqlTable.setDescription(logicalTable.getDescription());
            sqlTable.setTableType(ConceptUtil.getTableType(logicalTable));
            sqlTable.setTargetSchema(targetDatabaseMeta.getPreferredSchemaName());
            sqlTable.setTargetTable(targetTable);
        }
    }
    return domain;
}

Example 2 with SqlPhysicalModel

use of org.pentaho.metadata.model.SqlPhysicalModel in project pentaho-platform by pentaho.

the class SqlMetadataQueryExec method executeQuery.

public IPentahoResultSet executeQuery(Query queryObject) {
    // need to get the correct DatabaseMeta
    SqlPhysicalModel sqlModel = (SqlPhysicalModel) queryObject.getLogicalModel().getPhysicalModel();
    DatabaseMeta databaseMeta = ThinModelConverter.convertToLegacy(sqlModel.getId(), sqlModel.getDatasource());
    // this connection needs closed
    boolean closeConnection = true;
    DatabaseMeta activeDatabaseMeta = getActiveDatabaseMeta(databaseMeta);
    SQLConnection sqlConnection = getConnection(activeDatabaseMeta);
    String sql = null;
    try {
        if ((sqlConnection == null) || !sqlConnection.initialized()) {
            // $NON-NLS-1$
            logger.error(Messages.getInstance().getErrorString("SQLBaseComponent.ERROR_0007_NO_CONNECTION"));
            // TODO: throw an exception up the stack.
            return null;
        }
        // Fix for PDB-1753
        for (Parameter param : queryObject.getParameters()) {
            String pName = param.getName();
            if (parameters.containsKey(pName) && parameters.get(pName) != null && !parameters.get(pName).getClass().isArray()) {
                parameters.put(pName, this.convertParameterValue(param, parameters.get(pName)));
            }
        }
        MappedQuery mappedQuery = null;
        try {
            SqlGenerator sqlGenerator = createSqlGenerator();
            mappedQuery = sqlGenerator.generateSql(queryObject, LocaleHelper.getLocale().toString(), getMetadataDomainRepository(), activeDatabaseMeta, parameters, true);
        } catch (Exception e) {
            throw new RuntimeException(e.getLocalizedMessage(), e);
        }
        Integer timeout = getTimeout();
        if (timeout != null && timeout >= 0) {
            sqlConnection.setQueryTimeout(timeout);
        }
        Integer maxRows = getMaxRows();
        if (maxRows != null && maxRows >= 0) {
            sqlConnection.setMaxRows(maxRows);
        }
        Boolean readOnly = isReadOnly();
        if (readOnly != null && readOnly.booleanValue()) {
            sqlConnection.setReadOnly(true);
        }
        IPentahoResultSet localResultSet = null;
        sql = mappedQuery.getQuery();
        if (logger.isDebugEnabled()) {
            // $NON-NLS-1$
            logger.debug("SQL: " + sql);
        }
        if (getDoQueryLog()) {
            // $NON-NLS-1$
            logger.info("SQL: " + sql);
        }
        // populate prepared sql params
        List<Object> sqlParams = null;
        if (mappedQuery.getParamList() != null) {
            sqlParams = new ArrayList<Object>();
            for (String param : mappedQuery.getParamList()) {
                Object sqlParam = parameters.get(param);
                // lets see if the parameter is a multi valued param
                if (sqlParam instanceof Object[]) {
                    Object[] multivaluedParamValues = (Object[]) sqlParam;
                    for (Object p : multivaluedParamValues) {
                        sqlParams.add(p);
                    }
                    if (multivaluedParamValues.length == 0) {
                        sqlParams.add("");
                    }
                } else {
                    sqlParams.add(sqlParam);
                }
            }
        }
        try {
            if (!isForwardOnly()) {
                if (sqlParams != null) {
                    localResultSet = sqlConnection.prepareAndExecuteQuery(sql, sqlParams);
                } else {
                    localResultSet = sqlConnection.executeQuery(sql);
                }
            } else {
                if (sqlParams != null) {
                    localResultSet = sqlConnection.prepareAndExecuteQuery(sql, sqlParams, SQLConnection.RESULTSET_FORWARDONLY, SQLConnection.CONCUR_READONLY);
                } else {
                    localResultSet = sqlConnection.executeQuery(sql, SQLConnection.RESULTSET_FORWARDONLY, SQLConnection.CONCUR_READONLY);
                }
            }
            IPentahoMetaData metadata = mappedQuery.generateMetadata(localResultSet.getMetaData());
            ((SQLResultSet) localResultSet).setMetaData(metadata);
            closeConnection = false;
        } catch (Exception e) {
            logger.error(Messages.getInstance().getErrorString("SqlMetadataQueryExec.ERROR_0002_ERROR_EXECUTING_QUERY", e.getLocalizedMessage(), // $NON-NLS-1$
            sql));
            // $NON-NLS-1$
            logger.debug("error", e);
            return null;
        }
        return localResultSet;
    } finally {
        if (closeConnection && sqlConnection != null) {
            sqlConnection.close();
        }
    }
}

Example 3 with SqlPhysicalModel

use of org.pentaho.metadata.model.SqlPhysicalModel in project pentaho-platform by pentaho.

the class MetadataQueryComponentIT method getJdbcDomain.

public Domain getJdbcDomain() {
    Domain domain = getBasicDomain();
    SqlDataSource dataSource = ((SqlPhysicalModel) domain.getPhysicalModels().get(0)).getDatasource();
    dataSource.setType(DataSourceType.NATIVE);
    dataSource.setDatabaseName("file:src/test/resources/solution/system/data/sampledata");
    dataSource.setUsername("pentaho_user");
    dataSource.setPort("-1");
    dataSource.setPassword("password");
    domain.setId("JDBCDOMAIN");
    return domain;
}

Example 4 with SqlPhysicalModel

use of org.pentaho.metadata.model.SqlPhysicalModel in project data-access by pentaho.

the class DSWDatasourceServiceImpl method loadBusinessData.

public BusinessData loadBusinessData(String domainId, String modelId) throws DatasourceServiceException {
    Domain domain = getMetadataDomainRepository().getDomain(domainId);
    List<List<String>> data = null;
    if (domain.getPhysicalModels().get(0) instanceof InlineEtlPhysicalModel) {
        InlineEtlPhysicalModel model = (InlineEtlPhysicalModel) domain.getPhysicalModels().get(0);
        String relativePath = PentahoSystem.getSystemSetting("file-upload-defaults/relative-path", // $NON-NLS-1$
        String.valueOf(CsvTransformGenerator.DEFAULT_RELATIVE_UPLOAD_FILE_PATH));
        String csvFileLoc = PentahoSystem.getApplicationContext().getSolutionPath(relativePath);
        data = DatasourceServiceHelper.getCsvDataSample(csvFileLoc + model.getFileLocation(), model.getHeaderPresent(), model.getDelimiter(), model.getEnclosure(), 5);
    } else {
        SqlPhysicalModel model = (SqlPhysicalModel) domain.getPhysicalModels().get(0);
        String query = model.getPhysicalTables().get(0).getTargetTable();
        SerializedResultSet resultSet = DatasourceServiceHelper.getSerializeableResultSet(model.getDatasource().getDatabaseName(), query, 5, PentahoSessionHolder.getSession());
        data = resultSet.getData();
    }
    return new BusinessData(domain, data);
}

Example 5 with SqlPhysicalModel

use of org.pentaho.metadata.model.SqlPhysicalModel in project data-access by pentaho.

the class DebugModelerService method serializeModels.

public String serializeModels(Domain domain, String name) throws Exception {
    String domainId;
    PentahoSystemHelper.init();
    initKettle();
    try {
        DSWDatasourceServiceImpl datasourceService = new DSWDatasourceServiceImpl();
        ModelerWorkspace model = new ModelerWorkspace(new GwtModelerWorkspaceHelper(), datasourceService.getGeoContext());
        model.setModelName(name);
        model.setDomain(domain);
        String solutionStorage = AgileHelper.getDatasourceSolutionStorage();
        // $NON-NLS-1$  //$NON-NLS-2$
        String metadataLocation = "resources" + RepositoryFile.SEPARATOR + "metadata";
        String path = solutionStorage + RepositoryFile.SEPARATOR + metadataLocation + RepositoryFile.SEPARATOR;
        // $NON-NLS-1$
        domainId = path + name + ".xmi";
        IApplicationContext appContext = PentahoSystem.getApplicationContext();
        if (appContext != null) {
            path = PentahoSystem.getApplicationContext().getSolutionPath(path);
        }
        File pathDir = new File(path);
        if (!pathDir.exists()) {
            pathDir.mkdirs();
        }
        IPentahoSession session = getSession();
        // Keep a reference to the mondrian catalog
        model.getWorkspaceHelper().populateDomain(model);
        LogicalModel lModel = domain.getLogicalModels().get(0);
        String catName = lModel.getName(LocalizedString.DEFAULT_LOCALE);
        // $NON-NLS-1$
        lModel.setProperty("MondrianCatalogRef", catName);
        // Serialize domain to xmi.
        /*
        DISABLED DUE TO USE OF OLD API
      String base = PentahoSystem.getApplicationContext().getSolutionRootPath();
      String parentPath = ActionInfo.buildSolutionPath(solutionStorage, metadataLocation, ""); //$NON-NLS-1$
      int status = repository.publish(base, '/' + parentPath, name + ".xmi", reportXML.getBytes("UTF-8"),
      true); //$NON-NLS-1$ //$NON-NLS-2$
      if (status != ISolutionRepository.FILE_ADD_SUCCESSFUL) {
        throw new RuntimeException("Unable to save to repository. Status: " + status); //$NON-NLS-1$
      }

      // Serialize domain to olap schema.
      lModel = domain.getLogicalModels().get(1);
      MondrianModelExporter exporter = new MondrianModelExporter(lModel, LocalizedString.DEFAULT_LOCALE);
      String mondrianSchema = exporter.createMondrianModelXML();
      Document schemaDoc = DocumentHelper.parseText(mondrianSchema);
      byte[] schemaBytes = schemaDoc.asXML().getBytes("UTF-8"); //$NON-NLS-1$

      status = repository.publish(base, '/' + parentPath, name + ".mondrian.xml", schemaBytes, true); //$NON-NLS-1$  
      if (status != ISolutionRepository.FILE_ADD_SUCCESSFUL) {
        throw new RuntimeException("Unable to save to repository. Status: " + status); //$NON-NLS-1$  
      }

      // Refresh Metadata
      PentahoSystem.publish(session, MetadataPublisher.class.getName());
      */
        // Write this catalog to the default Pentaho DataSource and refresh the cache.
        // $NON-NLS-1$
        File file = new File(path + name + ".mondrian.xml");
        // Need to find a better way to get the connection name instead of using the Id.
        String catConnectStr = "Provider=mondrian;DataSource=\"" + ((SqlPhysicalModel) domain.getPhysicalModels().get(0)).getId() + // $NON-NLS-1$
        "\"";
        String catDef = // $NON-NLS-1$
        "solution:" + solutionStorage + RepositoryFile.SEPARATOR + "resources" + RepositoryFile.SEPARATOR + "metadata" + RepositoryFile.SEPARATOR + file.getName();
        addCatalog(catName, catConnectStr, catDef, session);
    } catch (Exception e) {
        getLogger().error(e);
        throw e;
    }
    return domainId;
}