Examples with SQLDialect com.yahoo.elide.datastores.aggregation.queryengines.sql.dialects.SQLDialect used on opensource projects

Example 1 with SQLDialect

use of com.yahoo.elide.datastores.aggregation.queryengines.sql.dialects.SQLDialect in project elide by yahoo.

the class SQLQueryEngine method executeQuery.

@Override
public QueryResult executeQuery(Query query, Transaction transaction) {
    SqlTransaction sqlTransaction = (SqlTransaction) transaction;
    ConnectionDetails details = query.getConnectionDetails();
    DataSource dataSource = details.getDataSource();
    SQLDialect dialect = details.getDialect();
    Query expandedQuery = expandMetricQueryPlans(query);
    // Translate the query into SQL.
    NativeQuery sql = toSQL(expandedQuery, dialect);
    String queryString = sql.toString();
    QueryResult.QueryResultBuilder resultBuilder = QueryResult.builder();
    NamedParamPreparedStatement stmt;
    Pagination pagination = query.getPagination();
    if (returnPageTotals(pagination)) {
        resultBuilder.pageTotals(getPageTotal(expandedQuery, sql, query, sqlTransaction));
    }
    log.debug("SQL Query: " + queryString);
    stmt = sqlTransaction.initializeStatement(queryString, dataSource);
    // Supply the query parameters to the query
    supplyFilterQueryParameters(query, stmt, dialect);
    // Run the primary query and log the time spent.
    ResultSet resultSet = runQuery(stmt, queryString, Function.identity());
    resultBuilder.data(new EntityHydrator(resultSet, query, metadataDictionary));
    return resultBuilder.build();
}

Example 2 with SQLDialect

use of com.yahoo.elide.datastores.aggregation.queryengines.sql.dialects.SQLDialect in project elide by yahoo.

the class SQLQueryEngine method toPageTotalSQL.

/**
 * Takes a SQLQuery and creates a new clone that instead returns the total number of records of the original
 * query.
 *
 * @param query The client query
 * @param sql The generated SQL query
 * @param sqlDialect the SQL dialect
 * @return A new query that returns the total number of records.
 */
private NativeQuery toPageTotalSQL(Query query, NativeQuery sql, SQLDialect sqlDialect) {
    // TODO: refactor this method
    String groupByDimensions = query.getAllDimensionProjections().stream().map(SQLColumnProjection.class::cast).filter(SQLColumnProjection::isProjected).map((column) -> column.toSQL(query, metaDataStore)).collect(Collectors.joining(", "));
    if (groupByDimensions.isEmpty()) {
        // Metric projection without group by dimension will return onely 1 record.
        return null;
    }
    NativeQuery innerQuery = NativeQuery.builder().projectionClause(groupByDimensions).fromClause(sql.getFromClause()).joinClause(sql.getJoinClause()).whereClause(sql.getWhereClause()).groupByClause(String.format("GROUP BY %s", groupByDimensions)).havingClause(sql.getHavingClause()).build();
    return NativeQuery.builder().projectionClause("COUNT(*)").fromClause(QueryTranslator.getFromClause("(" + innerQuery + ")", applyQuotes("pagination_subquery", sqlDialect), sqlDialect)).build();
}

Example 3 with SQLDialect

use of com.yahoo.elide.datastores.aggregation.queryengines.sql.dialects.SQLDialect in project elide by yahoo.

the class JoinExpressionExtractor method visitJoinReference.

@Override
public Set<String> visitJoinReference(JoinReference reference) {
    JoinPath joinPath = reference.getPath();
    List<PathElement> pathElements = joinPath.getPathElements();
    ColumnContext currentCtx = this.context;
    for (int i = 0; i < pathElements.size() - 1; i++) {
        PathElement pathElement = pathElements.get(i);
        Type<?> joinClass = pathElement.getFieldType();
        String joinFieldName = pathElement.getFieldName();
        SQLJoin sqlJoin = currentCtx.getQueryable().getJoin(joinFieldName);
        ColumnContext joinCtx;
        String onClause;
        JoinType joinType;
        String fullExpression;
        if (sqlJoin != null) {
            joinType = sqlJoin.getJoinType();
            joinCtx = (ColumnContext) currentCtx.get(joinFieldName);
            if (joinType.equals(JoinType.CROSS)) {
                onClause = EMPTY;
            } else {
                onClause = ON + currentCtx.resolve(sqlJoin.getJoinExpression());
            }
        } else {
            joinType = JoinType.LEFT;
            SQLTable table = metaDataStore.getTable(joinClass);
            joinCtx = ColumnContext.builder().queryable(table).alias(appendAlias(currentCtx.getAlias(), joinFieldName)).metaDataStore(currentCtx.getMetaDataStore()).column(currentCtx.getColumn()).tableArguments(mergedArgumentMap(table.getArguments(), currentCtx.getTableArguments())).build();
            onClause = ON + String.format("%s.%s = %s.%s", currentCtx.getAlias(), dictionary.getAnnotatedColumnName(pathElement.getType(), joinFieldName), joinCtx.getAlias(), dictionary.getAnnotatedColumnName(joinClass, dictionary.getIdFieldName(joinClass)));
        }
        SQLDialect sqlDialect = currentCtx.getQueryable().getDialect();
        String joinAlias = applyQuotes(joinCtx.getAlias(), sqlDialect);
        String joinKeyword = currentCtx.getQueryable().getDialect().getJoinKeyword(joinType);
        String joinSource = constructTableOrSubselect(joinCtx, joinClass);
        if (sqlDialect.useASBeforeTableAlias()) {
            fullExpression = String.format("%s %s AS %s %s", joinKeyword, joinSource, joinAlias, onClause);
        } else {
            fullExpression = String.format("%s %s %s %s", joinKeyword, joinSource, joinAlias, onClause);
        }
        joinExpressions.add(fullExpression);
        /**
         * If this `for` loop runs more than once, context should be switched to join context.
         */
        currentCtx = joinCtx;
    }
    // If reference within current join reference is of type PhysicalReference, then below visitor doesn't matter.
    // If it is of type LogicalReference, then visitLogicalReference method will recreate visitor with correct
    // value of ColumnProjection in context.
    JoinExpressionExtractor visitor = new JoinExpressionExtractor(currentCtx);
    joinExpressions.addAll(reference.getReference().accept(visitor));
    return joinExpressions;
}

Example 4 with SQLDialect

use of com.yahoo.elide.datastores.aggregation.queryengines.sql.dialects.SQLDialect in project elide by yahoo.

the class SQLUnitTest method init.

public static void init(SQLDialect sqlDialect, Set<Optimizer> optimizers, MetaDataStore metaDataStore) {
    Properties properties = new Properties();
    properties.put("driverClassName", "org.h2.Driver");
    String jdbcUrl = "jdbc:h2:mem:db1;DB_CLOSE_DELAY=-1" + ";NON_KEYWORDS=VALUE,USER" + ";DATABASE_TO_UPPER=FALSE" + getCompatabilityMode(sqlDialect.getDialectType());
    properties.put("jdbcUrl", jdbcUrl);
    HikariConfig config = new HikariConfig(properties);
    DataSource dataSource = new HikariDataSource(config);
    try (Connection h2Conn = dataSource.getConnection()) {
        h2Conn.createStatement().execute("RUNSCRIPT FROM 'classpath:prepare_tables.sql'");
    } catch (SQLException e) {
        ((HikariDataSource) dataSource).close();
        throw new IllegalStateException(e);
    }
    SQLUnitTest.metaDataStore = metaDataStore;
    dictionary = EntityDictionary.builder().build();
    dictionary.bindEntity(PlayerStatsWithView.class);
    dictionary.bindEntity(PlayerStatsView.class);
    dictionary.bindEntity(PlayerStats.class);
    dictionary.bindEntity(Country.class);
    dictionary.bindEntity(SubCountry.class);
    dictionary.bindEntity(Player.class);
    dictionary.bindEntity(CountryView.class);
    dictionary.bindEntity(CountryViewNested.class);
    dictionary.bindEntity(Continent.class);
    dictionary.bindEntity(GameRevenue.class);
    filterParser = RSQLFilterDialect.builder().dictionary(dictionary).build();
    // Manually register the serdes because we are not running a complete Elide service.
    CoerceUtil.register(Day.class, new Day.DaySerde());
    CoerceUtil.register(Hour.class, new Hour.HourSerde());
    CoerceUtil.register(ISOWeek.class, new ISOWeek.ISOWeekSerde());
    CoerceUtil.register(Minute.class, new Minute.MinuteSerde());
    CoerceUtil.register(Month.class, new Month.MonthSerde());
    CoerceUtil.register(Quarter.class, new Quarter.QuarterSerde());
    CoerceUtil.register(Second.class, new Second.SecondSerde());
    CoerceUtil.register(Week.class, new Week.WeekSerde());
    CoerceUtil.register(Year.class, new Year.YearSerde());
    metaDataStore.populateEntityDictionary(dictionary);
    // Need to provide details for connections used by all available models.
    Map<String, ConnectionDetails> connectionDetailsMap = new HashMap<>();
    connectionDetailsMap.put("mycon", new ConnectionDetails(dataSource, sqlDialect));
    connectionDetailsMap.put("SalesDBConnection", new ConnectionDetails(DUMMY_DATASOURCE, sqlDialect));
    Function<String, ConnectionDetails> connectionLookup = (name) -> connectionDetailsMap.getOrDefault(name, new ConnectionDetails(dataSource, sqlDialect));
    engine = new SQLQueryEngine(metaDataStore, connectionLookup, optimizers, new DefaultQueryPlanMerger(metaDataStore), new DefaultQueryValidator(metaDataStore.getMetadataDictionary()));
    playerStatsTable = (SQLTable) metaDataStore.getTable("playerStats", NO_VERSION);
    videoGameTable = (SQLTable) metaDataStore.getTable("videoGame", NO_VERSION);
    playerStatsViewTable = (SQLTable) metaDataStore.getTable("playerStatsView", NO_VERSION);
    playerStatsViewTableArgs = new HashMap<>();
    playerStatsViewTableArgs.put("rating", Argument.builder().name("overallRating").value("Great").build());
    playerStatsViewTableArgs.put("minScore", Argument.builder().name("minScore").value("0").build());
}

Example 5 with SQLDialect

use of com.yahoo.elide.datastores.aggregation.queryengines.sql.dialects.SQLDialect in project elide by yahoo.

the class AggregationDataStoreIntegrationTest method createHarness.

@Override
protected DataStoreTestHarness createHarness() {
    HikariConfig config = new HikariConfig(File.separator + "jpah2db.properties");
    DataSource defaultDataSource = new HikariDataSource(config);
    SQLDialect defaultDialect = SQLDialectFactory.getDefaultDialect();
    ConnectionDetails defaultConnectionDetails = new ConnectionDetails(defaultDataSource, defaultDialect);
    Properties prop = new Properties();
    prop.put("javax.persistence.jdbc.driver", config.getDriverClassName());
    prop.put("javax.persistence.jdbc.url", config.getJdbcUrl());
    EntityManagerFactory emf = Persistence.createEntityManagerFactory("aggregationStore", prop);
    Map<String, ConnectionDetails> connectionDetailsMap = new HashMap<>();
    // Add an entry for "mycon" connection which is not from hjson
    connectionDetailsMap.put("mycon", defaultConnectionDetails);
    // Add connection details fetched from hjson
    VALIDATOR.getElideSQLDBConfig().getDbconfigs().forEach(dbConfig -> connectionDetailsMap.put(dbConfig.getName(), new ConnectionDetails(getDataSource(dbConfig, getDBPasswordExtractor()), SQLDialectFactory.getDialect(dbConfig.getDialect()))));
    return new AggregationDataStoreTestHarness(emf, defaultConnectionDetails, connectionDetailsMap, VALIDATOR);
}