Examples with ColumnType com.servoy.j2db.query.ColumnType used on opensource projects

Example 1 with ColumnType

use of com.servoy.j2db.query.ColumnType in project servoy-client by Servoy.

the class FoundSetManager method insertToDataSource.

public Object[] insertToDataSource(String name, IDataSet dataSet, ColumnType[] columnTypes, String[] pkNames, boolean create, boolean skipOnLoad, String server) throws ServoyException {
    if (name == null) {
        return null;
    }
    String dataSource = IServer.VIEW_SERVER.equals(server) ? DataSourceUtils.createViewDataSource(name) : DataSourceUtils.createInmemDataSource(name);
    IDataSet fixedDataSet = dataSet;
    List<ColumnType> fixedColumnTypes;
    if (columnTypes == null) {
        ColumnType[] dataSetTypes = BufferedDataSetInternal.getColumnTypeInfo(dataSet);
        fixedColumnTypes = dataSetTypes == null ? null : asList(dataSetTypes);
    } else {
        fixedColumnTypes = asList(columnTypes);
    }
    // get column def from the first in-mem datasource found
    ServoyJSONObject columnsDef = null;
    Iterator<TableNode> tblIte = application.getFlattenedSolution().getTableNodes(dataSource);
    int onLoadMethodId = -1;
    while (tblIte.hasNext()) {
        TableNode tn = tblIte.next();
        if (columnsDef == null)
            columnsDef = tn.getColumns();
        if (onLoadMethodId == -1)
            onLoadMethodId = tn.getOnFoundSetLoadMethodID();
    }
    HashMap<String, ColumnInfoDef> columnInfoDefinitions = null;
    boolean[] rowsToStringserialize = null;
    if (columnsDef == null) {
        // if we have array columns, convert values using StringSerializer
        if (containsArrayType(fixedColumnTypes)) {
            rowsToStringserialize = new boolean[fixedColumnTypes.size()];
            for (int i = 0; i < fixedColumnTypes.size(); i++) {
                ColumnType columnType = fixedColumnTypes.get(i);
                if (columnType.getSqlType() == Types.ARRAY) {
                    fixedColumnTypes.set(i, ColumnType.getColumnType(IColumnTypes.TEXT));
                    rowsToStringserialize[i] = true;
                }
            }
        }
    } else {
        TableDef tableInfo = DatabaseUtils.deserializeTableInfo(columnsDef);
        columnInfoDefinitions = new HashMap<String, ColumnInfoDef>();
        List<String> inmemColumnNames = new ArrayList<>();
        List<String> inmemPKs = new ArrayList<>();
        List<ColumnType> inmemColumnTypes = new ArrayList<>();
        for (int j = 0; j < tableInfo.columnInfoDefSet.size(); j++) {
            ColumnInfoDef cid = tableInfo.columnInfoDefSet.get(j);
            if (cid.autoEnterType != ColumnInfo.SEQUENCE_AUTO_ENTER || cid.autoEnterSubType != ColumnInfo.DATABASE_IDENTITY) {
                inmemColumnNames.add(cid.name);
                inmemColumnTypes.add(cid.columnType);
            }
            if ((cid.flags & IBaseColumn.IDENT_COLUMNS) != 0) {
                inmemPKs.add(cid.name);
            }
            columnInfoDefinitions.put(cid.name, cid);
            // apply stringserializer on designed datasources
            if (JSONSerializerWrapper.STRING_SERIALIZER_NAME.equals(cid.converterName)) {
                if (rowsToStringserialize == null) {
                    rowsToStringserialize = new boolean[fixedColumnTypes.size()];
                }
                rowsToStringserialize[j] = true;
            }
        }
        if (pkNames == null && inmemPKs.size() > 0) {
            pkNames = inmemPKs.toArray(new String[inmemPKs.size()]);
        }
        if (!asList(dataSet.getColumnNames()).equals(inmemColumnNames) || !compareColumnTypes(fixedColumnTypes, inmemColumnTypes)) {
            if (dataSet.getColumnCount() > 0 && !asList(dataSet.getColumnNames()).equals(inmemColumnNames)) {
                Debug.warn("Dataset column names definition does not match inmem table definition for datasource : " + dataSource + " columns of dataset: " + Arrays.toString(dataSet.getColumnNames()) + ", columns of in mem definition: " + inmemColumnNames);
            }
            if (fixedColumnTypes != null && !compareColumnTypes(fixedColumnTypes, inmemColumnTypes)) {
                Debug.warn("Dataset column types definition does not match inmem table definition for datasource : " + dataSource + " types of dataset: " + fixedColumnTypes + ", types of in mem definition: " + inmemColumnTypes);
            }
            fixedColumnTypes = inmemColumnTypes;
            fixedDataSet = BufferedDataSetInternal.createBufferedDataSet(inmemColumnNames.toArray(new String[inmemColumnNames.size()]), fixedColumnTypes.toArray(new ColumnType[fixedColumnTypes.size()]), new ArrayList<Object[]>(), false);
        }
    }
    // - in the else branch above (already defined in the table node), columns that are not auto sequences or dbidents; see inmemColumnNames/inmemColumnTypes(which is at this point the same as fixedColumnTypes)
    if (dataSet.getRowCount() > 0 && dataSet.getRow(0).length != fixedColumnTypes.size()) {
        // $NON-NLS-1$
        throw new RepositoryException("Data set rows do not match column count");
    }
    if (rowsToStringserialize != null) {
        replaceValuesWithSerializedString(dataSet, rowsToStringserialize);
    }
    try {
        ITable table = IServer.VIEW_SERVER.equals(server) ? viewDataSources.get(dataSource) : inMemDataSources.get(dataSource);
        if (table == null && !create) {
            throw new RepositoryException("Appending to non-existing datasource: " + dataSource);
        }
        GlobalTransaction gt = getGlobalTransaction();
        String tid = null;
        String serverName = server == null ? (table == null ? IServer.INMEM_SERVER : table.getServerName()) : server;
        if (gt != null) {
            tid = gt.getTransactionID(serverName);
        }
        if (create && table != null) {
            // temp table was used before, delete all data in it
            FoundSet foundSet = (FoundSet) getSharedFoundSet(dataSource);
            foundSet.removeLastFound();
            try {
                QueryDelete delete = new QueryDelete(new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema(), true));
                SQLStatement deleteStatement = new SQLStatement(ISQLActionTypes.DELETE_ACTION, table.getServerName(), table.getName(), null, tid, delete, null);
                application.getDataServer().performUpdates(application.getClientID(), new ISQLStatement[] { deleteStatement });
            } catch (Exception e) {
                Debug.log(e);
                table = null;
            }
            RowManager element = rowManagers.get(dataSource);
            if (element != null) {
                element.flushAllCachedRows();
            }
        }
        InsertResult insertResult = application.getDataServer().insertDataSet(application.getClientID(), fixedDataSet, dataSource, table == null ? IServer.INMEM_SERVER : table.getServerName(), table == null ? null : table.getName(), /* create temp table when null */
        tid, fixedColumnTypes == null ? null : fixedColumnTypes.toArray(new ColumnType[fixedColumnTypes.size()]), /* inferred from dataset when null */
        pkNames, columnInfoDefinitions);
        if (insertResult != null) {
            table = insertResult.getTable();
            // then call insertDataSet again so the data is inserted with the columns defined in the the dataset.
            if (dataSet != fixedDataSet && dataSet.getRowCount() > 0) {
                insertResult = application.getDataServer().insertDataSet(application.getClientID(), dataSet, dataSource, table.getServerName(), table.getName(), tid, fixedColumnTypes == null ? null : fixedColumnTypes.toArray(new ColumnType[fixedColumnTypes.size()]), /* inferred from dataset when null */
                pkNames, columnInfoDefinitions);
            }
            if (IServer.INMEM_SERVER.equals(serverName)) {
                inMemDataSources.put(dataSource, table);
            } else {
                viewDataSources.put(dataSource, table);
            }
            fireTableEvent(table);
            if (!skipOnLoad && fixedDataSet.getRowCount() == 0 && onLoadMethodId > 0) {
                IFoundSetInternal sharedFoundSet = getSharedFoundSet(dataSource);
                executeFoundsetTriggerReturnFirst(sharedFoundSet.getTable(), new Object[] { DataSourceUtils.getInmemDataSourceName(dataSource) }, StaticContentSpecLoader.PROPERTY_ONFOUNDSETLOADMETHODID, false, (Scriptable) sharedFoundSet);
            }
            if (create) {
                // only refresh when it is a new full load, when adding data to an existing table, it is only applicable to the (shared) foundset
                refreshFoundSetsFromDB(dataSource, null, false);
            }
            return insertResult.getGeneratedPks();
        }
    } catch (RemoteException e) {
        throw new RepositoryException(e);
    }
    return null;
}

Example 2 with ColumnType

use of com.servoy.j2db.query.ColumnType in project servoy-client by Servoy.

the class FoundSetManager method insertToDataSource.

public Object[] insertToDataSource(String name, IDataSet dataSet, ColumnType[] columnTypes, WrappedObjectReference<String[]> pkNames, boolean create, boolean skipOnLoad, String server) throws ServoyException {
    if (name == null) {
        return null;
    }
    WrappedObjectReference<String[]> actualPkNames = pkNames;
    if (actualPkNames == null)
        actualPkNames = new WrappedObjectReference<String[]>(null);
    String dataSource = IServer.VIEW_SERVER.equals(server) ? DataSourceUtils.createViewDataSource(name) : DataSourceUtils.createInmemDataSource(name);
    // initial dataset to use, but can also be set later to a 0 row dataset that is created to match columnNames and columnTypes with an in-mem
    // table definition, if that is available and columns do not match with the initial dataset
    IDataSet fixedDataSet = dataSet;
    List<ColumnType> fixedColumnTypes;
    if (columnTypes == null) {
        ColumnType[] dataSetTypes = BufferedDataSetInternal.getColumnTypeInfo(dataSet);
        fixedColumnTypes = dataSetTypes == null ? null : asList(dataSetTypes);
    } else {
        fixedColumnTypes = asList(columnTypes);
    }
    // get column def from the first in-mem datasource found
    ServoyJSONObject columnsDef = null;
    Iterator<TableNode> tblIte = application.getFlattenedSolution().getTableNodes(dataSource);
    int onLoadMethodId = -1;
    while (tblIte.hasNext()) {
        TableNode tn = tblIte.next();
        if (columnsDef == null)
            columnsDef = tn.getColumns();
        if (onLoadMethodId == -1)
            onLoadMethodId = tn.getOnFoundSetLoadMethodID();
    }
    HashMap<String, ColumnInfoDef> columnInfoDefinitions = null;
    Set<Integer> columnsThatNeedToStringSerialize = null;
    if (columnsDef == null) {
        // if we have array columns, convert values using StringSerializer
        if (containsArrayType(fixedColumnTypes)) {
            columnsThatNeedToStringSerialize = new HashSet<>();
            for (int i = 0; i < fixedColumnTypes.size(); i++) {
                ColumnType columnType = fixedColumnTypes.get(i);
                if (columnType.getSqlType() == Types.ARRAY) {
                    fixedColumnTypes.set(i, ColumnType.getColumnType(IColumnTypes.TEXT));
                    columnsThatNeedToStringSerialize.add(Integer.valueOf(i));
                }
            }
        }
    } else {
        TableDef tableInfo = DatabaseUtils.deserializeTableInfo(columnsDef);
        // column names that are not SEQUENCE_AUTO_ENTER/DATABASE_IDENTITY from table node (columnsDef)
        List<String> inmemColumnNamesThatCanSetData = new ArrayList<>();
        // column types of columns that are not SEQUENCE_AUTO_ENTER/DATABASE_IDENTITY from table node (columnsDef)
        List<ColumnType> inmemColumnTypesForColumnsThatCanSetData = new ArrayList<>();
        // ALL column defs from design time table node (columnsDef)
        columnInfoDefinitions = new HashMap<String, ColumnInfoDef>();
        // pk/rowid column names from design time table node (columnsDef)
        List<String> inmemPKs = new ArrayList<>();
        for (int j = 0; j < tableInfo.columnInfoDefSet.size(); j++) {
            ColumnInfoDef cid = tableInfo.columnInfoDefSet.get(j);
            if (cid.autoEnterType != ColumnInfo.SEQUENCE_AUTO_ENTER || cid.autoEnterSubType != ColumnInfo.DATABASE_IDENTITY) {
                // we only support auto-enter for in-mem tables based on dbident (that is then handled by hsql)
                // that is why we only check for that here; for example, if one would define at design time a
                // uuid generator pk column on an in-mem table, that would not work; it would try to insert null into a non-nullable column
                inmemColumnNamesThatCanSetData.add(cid.name);
                inmemColumnTypesForColumnsThatCanSetData.add(cid.columnType);
            }
            if ((cid.flags & IBaseColumn.IDENT_COLUMNS) != 0) {
                inmemPKs.add(cid.name);
            }
            columnInfoDefinitions.put(cid.name, cid);
            // apply stringserializer on designed datasources
            if (JSONSerializerWrapper.STRING_SERIALIZER_NAME.equals(cid.converterName)) {
                if (columnsThatNeedToStringSerialize == null) {
                    columnsThatNeedToStringSerialize = new HashSet<>();
                }
                columnsThatNeedToStringSerialize.add(Integer.valueOf(j));
            }
        }
        if (actualPkNames.o == null && inmemPKs.size() > 0) {
            actualPkNames.o = inmemPKs.toArray(new String[inmemPKs.size()]);
        }
        if (!asList(dataSet.getColumnNames()).equals(inmemColumnNamesThatCanSetData) || !compareColumnTypes(fixedColumnTypes, inmemColumnTypesForColumnsThatCanSetData)) {
            if (dataSet.getColumnCount() > 0 && /*
													 * do not generate warning if this is just the initial load of a design time inmem table that adds 0 rows
													 * and doesn't care about columns
													 */
            !asList(dataSet.getColumnNames()).equals(inmemColumnNamesThatCanSetData)) {
                Debug.warn("Dataset column names definition does not match inmem table definition for datasource : " + dataSource + " columns of dataset: " + Arrays.toString(dataSet.getColumnNames()) + ", columns of in mem definition: " + inmemColumnNamesThatCanSetData);
            }
            if (fixedColumnTypes != null && !compareColumnTypes(fixedColumnTypes, inmemColumnTypesForColumnsThatCanSetData)) {
                Debug.warn("Dataset column types definition does not match inmem table definition for datasource : " + dataSource + " types of dataset: " + fixedColumnTypes + ", types of in mem definition: " + inmemColumnTypesForColumnsThatCanSetData);
            }
            fixedColumnTypes = inmemColumnTypesForColumnsThatCanSetData;
            fixedDataSet = BufferedDataSetInternal.createBufferedDataSet(inmemColumnNamesThatCanSetData.toArray(new String[inmemColumnNamesThatCanSetData.size()]), fixedColumnTypes.toArray(new ColumnType[fixedColumnTypes.size()]), new ArrayList<Object[]>(), false);
        }
    }
    // - in the else branch above (already defined at design time), columns that are not auto sequences or dbidents; see inmemColumnNames/inmemColumnTypes(which is at this point the same as fixedColumnTypes)
    if (fixedColumnTypes != null && dataSet.getRowCount() > 0 && dataSet.getRow(0).length != fixedColumnTypes.size()) {
        // $NON-NLS-1$
        throw new RepositoryException("Data set rows do not match column count");
    }
    if (columnsThatNeedToStringSerialize != null) {
        replaceValuesWithSerializedString(dataSet, columnsThatNeedToStringSerialize);
    }
    try {
        ITable table = IServer.VIEW_SERVER.equals(server) ? viewDataSources.get(dataSource) : inMemDataSources.get(dataSource);
        if (table == null && !create) {
            throw new RepositoryException("Appending to non-existing datasource: " + dataSource);
        }
        GlobalTransaction gt = getGlobalTransaction();
        String tid = null;
        String serverName = server == null ? (table == null ? IServer.INMEM_SERVER : table.getServerName()) : server;
        if (gt != null) {
            tid = gt.getTransactionID(serverName);
        }
        if (create && table != null) {
            // temp table was used before, delete all data in it
            FoundSet foundSet = (FoundSet) getSharedFoundSet(dataSource);
            foundSet.removeLastFound();
            try {
                QueryDelete delete = new QueryDelete(new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema(), true));
                SQLStatement deleteStatement = new SQLStatement(ISQLActionTypes.DELETE_ACTION, table.getServerName(), table.getName(), null, tid, delete, null);
                application.getDataServer().performUpdates(application.getClientID(), new ISQLStatement[] { deleteStatement });
            } catch (Exception e) {
                Debug.log(e);
                table = null;
            }
            RowManager element = rowManagers.get(dataSource);
            if (element != null) {
                element.flushAllCachedRows();
            }
        }
        InsertResult insertResult = application.getDataServer().insertDataSet(application.getClientID(), fixedDataSet, dataSource, table == null ? IServer.INMEM_SERVER : table.getServerName(), table == null ? null : table.getName(), /* create temp table when null */
        tid, fixedColumnTypes == null ? null : fixedColumnTypes.toArray(new ColumnType[fixedColumnTypes.size()]), /* inferred from dataset when null */
        actualPkNames.o, columnInfoDefinitions);
        if (insertResult != null) {
            table = insertResult.getTable();
            // do we want to fix that somehow or the caller should just make sure it calls it with the correct column order?
            if (dataSet != fixedDataSet && dataSet.getRowCount() > 0) {
                insertResult = application.getDataServer().insertDataSet(application.getClientID(), dataSet, dataSource, table.getServerName(), table.getName(), tid, columnTypes, /* will be inferred by called method from dataset if null */
                actualPkNames.o, columnInfoDefinitions);
            }
            if (IServer.INMEM_SERVER.equals(serverName)) {
                inMemDataSources.put(dataSource, table);
            } else {
                viewDataSources.put(dataSource, table);
            }
            fireTableEvent(table);
            if (!skipOnLoad && dataSet.getRowCount() == 0 && onLoadMethodId > 0) {
                IFoundSetInternal sharedFoundSet = getSharedFoundSet(dataSource);
                executeFoundsetTriggerReturnFirst(sharedFoundSet.getTable(), new Object[] { DataSourceUtils.getInmemDataSourceName(dataSource) }, StaticContentSpecLoader.PROPERTY_ONFOUNDSETLOADMETHODID, false, (Scriptable) sharedFoundSet);
            }
            if (create) {
                // only refresh when it is a new full load, when adding data to an existing table, it is only applicable to the (shared) foundset
                refreshFoundSetsFromDB(dataSource, null, false);
            }
            return insertResult.getGeneratedPks();
        }
    } catch (RemoteException e) {
        throw new RepositoryException(e);
    }
    return null;
}

Example 3 with ColumnType

use of com.servoy.j2db.query.ColumnType in project servoy-client by Servoy.

the class SQLGenerator method getEmptyDataSetForDummyQuery.

/**
 * check if the query is will never return any rows, in that case just return an empty dataset.
 */
public static IDataSet getEmptyDataSetForDummyQuery(ISQLSelect sqlSelect) {
    if (sqlSelect instanceof QuerySelect && ((QuerySelect) sqlSelect).getCondition(CONDITION_SEARCH) != null) {
        // all named conditions in QuerySelecta are AND-ed, if one always results to false, skip the query
        for (IBaseSQLCondition condition : ((QuerySelect) sqlSelect).getCondition(CONDITION_SEARCH).getConditions()) {
            boolean skipQuery = false;
            if (condition instanceof SetCondition && ((SetCondition) condition).isAndCondition()) {
                // check for EQUALS_OPERATOR
                int ncols = ((SetCondition) condition).getKeys().length;
                int[] operators = ((SetCondition) condition).getOperators();
                boolean eqop = true;
                for (int i = 0; i < ncols; i++) {
                    if (operators[i] != IBaseSQLCondition.EQUALS_OPERATOR) {
                        eqop = false;
                    }
                }
                if (eqop) {
                    Object value = ((SetCondition) condition).getValues();
                    if (value instanceof Placeholder) {
                        Object phval = ((Placeholder) value).getValue();
                        // cleared foundset
                        skipQuery = phval instanceof DynamicPkValuesArray && ((DynamicPkValuesArray) phval).getPKs().getRowCount() == 0;
                    } else if (value instanceof Object[][]) {
                        skipQuery = ((Object[][]) value).length == 0 || ((Object[][]) value)[0].length == 0;
                    }
                }
            }
            if (skipQuery) {
                // no need to query, dummy condition (where 1=2) here
                List<IQuerySelectValue> columns = ((QuerySelect) sqlSelect).getColumns();
                String[] columnNames = new String[columns.size()];
                ColumnType[] columnTypes = new ColumnType[columns.size()];
                for (int i = 0; i < columns.size(); i++) {
                    IQuerySelectValue col = columns.get(i);
                    columnNames[i] = col.getAliasOrName();
                    BaseColumnType columnType = col.getColumnType();
                    columnTypes[i] = columnType == null ? ColumnType.getInstance(Types.OTHER, 0, 0) : ColumnType.getInstance(columnType.getSqlType(), columnType.getLength(), columnType.getScale());
                }
                return BufferedDataSetInternal.createBufferedDataSet(columnNames, columnTypes, new SafeArrayList<Object[]>(0), false);
            }
        }
    }
    // query needs to be run
    return null;
}

Example 4 with ColumnType

use of com.servoy.j2db.query.ColumnType in project servoy-client by Servoy.

the class MetaDataUtils method deserializeTableMetaDataContents.

/**
 * Deserialize table contents string to of buffered dataset to a string, includes column names and type info
 *
 * @param data
 * @return
 * @throws JSONException
 */
public static BufferedDataSet deserializeTableMetaDataContents(String data) throws JSONException {
    if (data == null) {
        return null;
    }
    ServoyJSONObject json = new ServoyJSONObject(data, true);
    JSONArray jsonColumns = (JSONArray) json.get("columns");
    String[] columnNames = new String[jsonColumns.length()];
    ColumnType[] columnTypes = new ColumnType[jsonColumns.length()];
    for (int c = 0; c < jsonColumns.length(); c++) {
        JSONObject jsonColumn = (JSONObject) jsonColumns.get(c);
        columnNames[c] = jsonColumn.getString("name");
        JSONArray typeArray = new JSONArray(jsonColumn.getString("type"));
        columnTypes[c] = ColumnType.getInstance(typeArray.getInt(0), typeArray.getInt(1), typeArray.getInt(2));
    }
    List<Object[]> rows = new ArrayList<Object[]>();
    JSONArray jsonArray = (JSONArray) json.get("rows");
    for (int r = 0; r < jsonArray.length(); r++) {
        JSONArray rowobj = (JSONArray) jsonArray.get(r);
        Object[] row = new Object[columnNames.length];
        for (int i = 0; i < columnNames.length; i++) {
            Object val = rowobj.get(i);
            if (val == JSONObject.NULL) {
                row[i] = null;
            } else if (Column.mapToDefaultType(columnTypes[i].getSqlType()) == IColumnTypes.MEDIA && val instanceof String) {
                row[i] = Utils.decodeBASE64((String) val);
            } else if (Column.mapToDefaultType(columnTypes[i].getSqlType()) == IColumnTypes.DATETIME && val instanceof String) {
                Date parsed = ServoyJSONObject.parseDate((String) val);
                // convert when possible, otherwise leave to driver (fails on mysql)
                row[i] = parsed == null ? val : parsed;
            } else {
                row[i] = val;
            }
        }
        rows.add(row);
    }
    return BufferedDataSetInternal.createBufferedDataSet(columnNames, columnTypes, rows, false);
}

Example 5 with ColumnType

use of com.servoy.j2db.query.ColumnType in project servoy-client by Servoy.

the class FoundSetManager method compareColumnTypes.

private static boolean compareColumnTypes(List<ColumnType> types1, List<ColumnType> types2) {
    if (types1 == types2)
        return true;
    if (types1 == null || types2 == null)
        return false;
    int length = types1.size();
    if (types2.size() != length)
        return false;
    for (int i = 0; i < length; i++) {
        ColumnType type1 = types1.get(i);
        ColumnType type2 = types2.get(i);
        if (type1 == null && type2 != null)
            return false;
        if (type1 != null && type2 == null)
            return false;
        // if they are not equal then test if this type is a TEXT or INTEGER column on both ends, then only type is needed
        if (!type1.equals(type2) && !(type1.getSqlType() == type2.getSqlType() && (type1.getSqlType() == IColumnTypes.TEXT || type1.getSqlType() == IColumnTypes.INTEGER))) {
            return false;
        }
    }
    return true;
}