Examples with TableLevel org.hortonmachine.dbs.compat.objects.TableLevel used on opensource projects

Example 1 with TableLevel

use of org.hortonmachine.dbs.compat.objects.TableLevel in project hortonmachine by TheHortonMachine.

the class Spatialite2H2gis method getTables.

private List<TableLevel> getTables() throws Exception {
    DbLevel dbLevel = DbLevel.getDbLevel(spatialite, ISpatialTableNames.USERDATA);
    List<TableLevel> tablesList = dbLevel.typesList.get(0).tablesList;
    Collections.sort(tablesList, new Comparator<TableLevel>() {

        @Override
        public int compare(TableLevel o1, TableLevel o2) {
            if (o1.hasFks()) {
                List<ColumnLevel> columnsList = o1.columnsList;
                for (ColumnLevel col : columnsList) {
                    if (col.references != null) {
                        String refTable = col.tableColsFromFK()[0];
                        if (o2.tableName.equalsIgnoreCase(refTable)) {
                            // linked, create other first
                            return -1;
                        }
                    }
                }
                if (!o2.hasFks()) {
                    return 1;
                }
            }
            if (o2.hasFks()) {
                List<ColumnLevel> columnsList = o2.columnsList;
                for (ColumnLevel col : columnsList) {
                    if (col.references != null) {
                        String refTable = col.tableColsFromFK()[0];
                        if (o1.tableName.equalsIgnoreCase(refTable)) {
                            // linked, create other first
                            return -1;
                        }
                    }
                }
                if (!o1.hasFks()) {
                    return -1;
                }
            }
            return 0;
        }
    });
    return tablesList;
}

Example 2 with TableLevel

use of org.hortonmachine.dbs.compat.objects.TableLevel in project hortonmachine by TheHortonMachine.

the class Spatialite2Postgis method generateSchema.

public void generateSchema() throws Exception {
    List<TableLevel> tablesList = getTables();
    System.out.println("Order of creation:");
    for (TableLevel tableLevel : tablesList) {
        System.out.println("->" + tableLevel.tableName);
    }
    List<TableLevel> todo = new ArrayList<>(tablesList);
    List<TableLevel> nextTodo = new ArrayList<>();
    finalDoneOrder = new ArrayList<>();
    int count = 0;
    ADatabaseSyntaxHelper dsh = postgis.getType().getDatabaseSyntaxHelper();
    while (todo.size() > 0 && count < 10) {
        for (TableLevel tableLevel : todo) {
            SqlName tableName = SqlName.m(tableLevel.tableName);
            String tableSql = getTableSql(spatialite, tableName);
            System.out.println("Trying to create table: " + tableName);
            String tableSqlChecked = postgis.getType().getDatabaseSyntaxHelper().checkSqlCompatibilityIssues(tableSql);
            /*
                 *  dirty hack: in my db spatialite had integers where long should be
                 *  since spatialite is dynamic in this. In postgis we need longs.
                 */
            tableSqlChecked = tableSqlChecked.replaceAll("INTEGER", dsh.LONG());
            tableSqlChecked = tableSqlChecked.replaceAll("LONG", dsh.LONG());
            tableSqlChecked = tableSqlChecked.replaceAll("DOUBLE", dsh.REAL());
            tableSqlChecked = tableSqlChecked.replaceAll("LINESTRING", "lseg");
            tableSqlChecked = tableSqlChecked.replaceAll("MULTIPOLYGON", "polygon");
            // the the_geom field comes with "" from spatialite
            tableSqlChecked = tableSqlChecked.replaceAll("\"", "");
            try {
                postgis.executeInsertUpdateDeleteSql(tableSqlChecked);
                if (tableLevel.isGeo) {
                    GeometryColumn geometryColumn = spatialite.getGeometryColumnsForTable(tableName);
                    try {
                        String columnName = geometryColumn.geometryColumnName;
                        String drop = "ALTER TABLE " + tableName + " DROP COLUMN " + columnName + ";";
                        postgis.executeInsertUpdateDeleteSql(drop);
                        // add spatial
                        // TODO
                        EGeometryType type = geometryColumn.geometryType;
                        // EGeometryType.fromSpatialiteCode(geometryColumn.geometryType);
                        postgis.addGeometryXYColumnAndIndex(tableName, columnName, type.name(), geometryColumn.srid + "", false);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                List<String> indexSqls = getIndexSqls(spatialite, tableName);
                for (String indexSql : indexSqls) {
                    if (indexSql == null) {
                        continue;
                    }
                    postgis.executeInsertUpdateDeleteSql(indexSql);
                }
                finalDoneOrder.add(tableLevel);
                System.out.println("Created table: " + tableName);
            } catch (Exception e) {
                System.out.println("Trying again later for table: " + tableName + " -> " + e.getMessage());
                e.printStackTrace();
                // try later in inverse order
                nextTodo.add(0, tableLevel);
            }
        }
        todo.clear();
        todo.addAll(nextTodo);
        nextTodo.clear();
        count++;
        System.out.println("*************");
    }
}

Example 3 with TableLevel

use of org.hortonmachine.dbs.compat.objects.TableLevel in project hortonmachine by TheHortonMachine.

the class Spatialite2Postgis method copyData.

public void copyData() throws Exception {
    List<TableLevel> tablesList = getTables();
    if (finalDoneOrder != null) {
        tablesList = finalDoneOrder;
    }
    for (TableLevel tableLevel : tablesList) {
        SqlName tableName = SqlName.m(tableLevel.tableName);
        System.out.println("Copy table " + tableName);
        System.out.println("Read data...");
        QueryResult queryResult = spatialite.getTableRecordsMapFromRawSql("select * from " + tableName, -1);
        System.out.println("Done.");
        System.out.println("Insert data...");
        int geometryIndex = queryResult.geometryIndex;
        List<String> names = queryResult.names;
        StringBuilder namesSb = new StringBuilder();
        StringBuilder qmSb = new StringBuilder();
        GeometryColumn gCol = null;
        for (int i = 0; i < names.size(); i++) {
            namesSb.append(",").append(names.get(i));
            if (i == geometryIndex) {
                gCol = spatialite.getGeometryColumnsForTable(tableName);
                qmSb.append(",ST_GeomFromText(?, " + gCol.srid + ")");
            } else {
                qmSb.append(",?");
            }
        }
        String namesStr = namesSb.substring(1);
        String qmStr = qmSb.substring(1);
        String prepared = "insert into " + tableName + " (" + namesStr + ") values (" + qmStr + ");";
        GeometryColumn _gCol = gCol;
        postgis.execOnConnection(connection -> {
            String emptyGeomStr = null;
            try (IHMPreparedStatement stmt = connection.prepareStatement(prepared)) {
                long count = 0;
                for (Object[] objects : queryResult.data) {
                    for (int i = 0; i < objects.length; i++) {
                        if (i == geometryIndex) {
                            if (objects[i] == null) {
                                if (emptyGeomStr == null) {
                                    EGeometryType gType = _gCol.geometryType;
                                    // TODO check ESpatialiteGeometryType gType = ESpatialiteGeometryType.forValue(geometryType);
                                    if (gType.isLine()) {
                                        if (gType.isMulti()) {
                                            emptyGeomStr = gf.createLineString((CoordinateSequence) null).toText();
                                        } else {
                                            emptyGeomStr = gf.createMultiLineString(null).toText();
                                        }
                                    } else if (gType.isPoint()) {
                                        if (gType.isMulti()) {
                                            emptyGeomStr = gf.createMultiPoint((CoordinateSequence) null).toText();
                                        } else {
                                            emptyGeomStr = gf.createPoint((Coordinate) null).toText();
                                        }
                                    } else if (gType.isPolygon()) {
                                        if (gType.isMulti()) {
                                            emptyGeomStr = gf.createMultiPolygon(null).toText();
                                        } else {
                                            emptyGeomStr = gf.createPolygon((CoordinateSequence) null).toText();
                                        }
                                    }
                                }
                                stmt.setString(i + 1, emptyGeomStr);
                            } else {
                                stmt.setString(i + 1, (String) objects[i].toString());
                            }
                        } else if (objects[i] == null) {
                            stmt.setObject(i + 1, null);
                        } else if (objects[i] instanceof Boolean) {
                            stmt.setBoolean(i + 1, (boolean) objects[i]);
                        } else if (objects[i] instanceof byte[]) {
                            stmt.setBytes(i + 1, (byte[]) objects[i]);
                        } else if (objects[i] instanceof Double) {
                            stmt.setDouble(i + 1, (double) objects[i]);
                        } else if (objects[i] instanceof Float) {
                            stmt.setFloat(i + 1, (float) objects[i]);
                        } else if (objects[i] instanceof Integer) {
                            stmt.setInt(i + 1, (int) objects[i]);
                        } else if (objects[i] instanceof Long) {
                            stmt.setLong(i + 1, (long) objects[i]);
                        } else if (objects[i] instanceof Short) {
                            stmt.setShort(i + 1, (short) objects[i]);
                        } else if (objects[i] instanceof String) {
                            stmt.setString(i + 1, (String) objects[i]);
                        } else {
                            stmt.setObject(i + 1, objects[i]);
                        }
                    }
                    stmt.addBatch();
                    count++;
                    if (count % 10000 == 0) {
                        System.out.println("Inserted: " + count);
                        stmt.executeBatch();
                    }
                }
                int[] executeUpdate = stmt.executeBatch();
            }
            System.out.println("Done.");
            return null;
        });
    }
}

Example 4 with TableLevel

use of org.hortonmachine.dbs.compat.objects.TableLevel in project hortonmachine by TheHortonMachine.

the class DatabaseController method init.

@SuppressWarnings("serial")
private void init() {
    databaseTreeView = new DatabaseTreeView();
    _mainSplitPane.setLeftComponent(databaseTreeView);
    sqlEditorView = new SqlEditorView();
    dataTableView = new DataTableView();
    JSplitPane rightSplitPanel = new JSplitPane(JSplitPane.VERTICAL_SPLIT, sqlEditorView, dataTableView);
    rightSplitPanel.setDividerLocation(0.3);
    _mainSplitPane.setRightComponent(rightSplitPanel);
    _mainSplitPane.setDividerLocation(0.3);
    String[] oldSqlCommandsArray = PreferencesHandler.getPreference("HM_OLD_SQL_COMMANDS", new String[0]);
    for (String oldSql : oldSqlCommandsArray) {
        oldSqlCommands.add(oldSql);
    }
    sqlEditorView._limitCountTextfield.setText("1000");
    sqlEditorView._limitCountTextfield.setToolTipText("1000 is default and used when no valid number is supplied. -1 means no limit.");
    dataTableView._recordCountTextfield.setEditable(false);
    sqlEditorView._sqlEditorAreaPanel.setLayout(new BorderLayout());
    dataTableView._formatDatesPatternTextField.setText("date, ts, timestamp");
    JTabbedPane tabbedDataViewerPane = new JTabbedPane();
    JTabbedPane tabbedEditorPane = new JTabbedPane();
    tabbedDataViewerPane.addChangeListener(e -> {
        if (editorPanesArray != null && dataTablesArray != null) {
            int selectedIndex = tabbedDataViewerPane.getSelectedIndex();
            currentSqlEditorArea = editorPanesArray[selectedIndex];
            currentDataTable = dataTablesArray[selectedIndex];
            tabbedEditorPane.setSelectedIndex(selectedIndex);
        }
    });
    tabbedEditorPane.addChangeListener(e -> {
        if (editorPanesArray != null && dataTablesArray != null) {
            int selectedIndex = tabbedEditorPane.getSelectedIndex();
            currentSqlEditorArea = editorPanesArray[selectedIndex];
            currentDataTable = dataTablesArray[selectedIndex];
            tabbedDataViewerPane.setSelectedIndex(selectedIndex);
        }
    });
    int tabCount = 5;
    dataTablesArray = new JTable[tabCount];
    for (int i = 0; i < tabCount; i++) {
        JPanel panel1 = new JPanel();
        panel1.setLayout(new BorderLayout());
        tabbedDataViewerPane.addTab("Viewer " + (i + 1), panel1);
        MultiLineTableCellRenderer wordWrapRenderer = new MultiLineTableCellRenderer();
        JTable table = new JTable() {

            public TableCellRenderer getCellRenderer(int row, int column) {
                if (currentConnectedNosqlDatabase != null) {
                    return wordWrapRenderer;
                } else {
                    return super.getCellRenderer(row, column);
                }
            }
        };
        JScrollPane dataTablesScrollpane = new JScrollPane(table);
        panel1.add(dataTablesScrollpane, BorderLayout.CENTER);
        if (i == 0) {
            currentDataTable = table;
        }
        dataTablesArray[i] = table;
        addDataTableContextMenu(table);
    }
    editorPanesArray = new JTextPane[tabCount];
    for (int i = 0; i < tabCount; i++) {
        JPanel panel1 = new JPanel();
        panel1.setLayout(new BorderLayout());
        tabbedEditorPane.addTab("Editor " + (i + 1), panel1);
        JTextPane sqlEditorArea = new JTextPane();
        JScrollPane _sqlEditorAreaScrollpane = new JScrollPane(sqlEditorArea);
        panel1.add(_sqlEditorAreaScrollpane, BorderLayout.CENTER);
        SqlDocument doc = new SqlDocument();
        sqlEditorArea.setDocument(doc);
        if (i == 0) {
            currentSqlEditorArea = sqlEditorArea;
        }
        addSqlAreaContextMenu(sqlEditorArea);
        editorPanesArray[i] = sqlEditorArea;
    }
    sqlEditorView._sqlEditorAreaPanel.add(tabbedEditorPane, BorderLayout.CENTER);
    dataTableView._dataViewerPanel.setLayout(new BorderLayout());
    dataTableView._dataViewerPanel.add(tabbedDataViewerPane, BorderLayout.CENTER);
    _newDbButton.setVerticalTextPosition(SwingConstants.BOTTOM);
    _newDbButton.setHorizontalTextPosition(SwingConstants.CENTER);
    _newDbButton.setText(NEW);
    _newDbButton.setToolTipText(NEW_TOOLTIP);
    _newDbButton.setPreferredSize(preferredToolbarButtonSize);
    _newDbButton.setIcon(ImageCache.getInstance().getImage(ImageCache.NEW_DATABASE));
    _newDbButton.addActionListener(e -> {
        JDialog f = new JDialog();
        f.setModal(true);
        NewDbController newDb = new NewDbController(f, guiBridge, false, null, null, null, null, false);
        f.add(newDb, BorderLayout.CENTER);
        f.setTitle("Create new database");
        f.pack();
        f.setIconImage(ImageCache.getInstance().getImage(ImageCache.NEW_DATABASE).getImage());
        f.setLocationRelativeTo(_newDbButton);
        f.setVisible(true);
        f.setDefaultCloseOperation(JDialog.DISPOSE_ON_CLOSE);
        if (newDb.isOk()) {
            String dbPath = newDb.getDbPath();
            String user = newDb.getDbUser();
            String pwd = newDb.getDbPwd();
            EDb dbType = newDb.getDbType();
            createNewDatabase(dbType, dbPath, user, pwd);
        }
    });
    _connectDbButton.setVerticalTextPosition(SwingConstants.BOTTOM);
    _connectDbButton.setHorizontalTextPosition(SwingConstants.CENTER);
    _connectDbButton.setText(CONNECT);
    _connectDbButton.setToolTipText(CONNECT_TOOLTIP);
    _connectDbButton.setPreferredSize(preferredToolbarButtonSize);
    _connectDbButton.setIcon(ImageCache.getInstance().getImage(ImageCache.CONNECT));
    _connectDbButton.addActionListener(e -> {
        JDialog f = new JDialog();
        f.setModal(true);
        String lastPath = PreferencesHandler.getPreference(DatabaseGuiUtils.HM_LOCAL_LAST_FILE, "");
        String lastUser = PreferencesHandler.getPreference(DatabaseGuiUtils.HM_JDBC_LAST_USER, "sa");
        String lastPwd = PreferencesHandler.getPreference(DatabaseGuiUtils.HM_JDBC_LAST_PWD, "");
        NewDbController newDb = new NewDbController(f, guiBridge, true, lastPath, null, lastUser, lastPwd, true);
        f.add(newDb, BorderLayout.CENTER);
        f.setTitle("Open database");
        f.pack();
        f.setIconImage(ImageCache.getInstance().getImage(ImageCache.CONNECT).getImage());
        f.setLocationRelativeTo(_connectDbButton);
        f.setVisible(true);
        f.setDefaultCloseOperation(JDialog.DISPOSE_ON_CLOSE);
        if (newDb.isOk()) {
            String dbPath = newDb.getDbPath();
            String user = newDb.getDbUser();
            String pwd = newDb.getDbPwd();
            EDb dbType = newDb.getDbType();
            boolean connectInRemote = newDb.connectInRemote();
            if (connectInRemote) {
                PreferencesHandler.setPreference(DatabaseGuiUtils.HM_LOCAL_LAST_FILE, dbPath);
                dbPath = "jdbc:h2:tcp://localhost:9092/" + dbPath;
                PreferencesHandler.setPreference(DatabaseGuiUtils.HM_JDBC_LAST_USER, user);
                PreferencesHandler.setPreference(DatabaseGuiUtils.HM_JDBC_LAST_PWD, pwd);
                openRemoteDatabase(dbPath, user, pwd);
            } else {
                PreferencesHandler.setPreference(DatabaseGuiUtils.HM_LOCAL_LAST_FILE, dbPath);
                PreferencesHandler.setPreference(DatabaseGuiUtils.HM_JDBC_LAST_USER, user);
                PreferencesHandler.setPreference(DatabaseGuiUtils.HM_JDBC_LAST_PWD, pwd);
                openDatabase(dbType, dbPath, user, pwd);
            }
        }
    });
    _connectRemoteDbButton.setVerticalTextPosition(SwingConstants.BOTTOM);
    _connectRemoteDbButton.setHorizontalTextPosition(SwingConstants.CENTER);
    _connectRemoteDbButton.setText(CONNECT_REMOTE);
    _connectRemoteDbButton.setToolTipText(CONNECT_REMOTE_TOOLTIP);
    _connectRemoteDbButton.setPreferredSize(preferredToolbarButtonSize);
    _connectRemoteDbButton.setIcon(ImageCache.getInstance().getImage(ImageCache.CONNECT_REMOTE));
    _connectRemoteDbButton.addActionListener(e -> {
        JDialog f = new JDialog();
        f.setModal(true);
        String lastPath = PreferencesHandler.getPreference(DatabaseGuiUtils.HM_JDBC_LAST_URL, "jdbc:h2:tcp://localhost:9092/absolute_dbpath");
        String lastUser = PreferencesHandler.getPreference(DatabaseGuiUtils.HM_JDBC_LAST_USER, "sa");
        String lastPwd = PreferencesHandler.getPreference(DatabaseGuiUtils.HM_JDBC_LAST_PWD, "");
        NewDbController newDb = new NewDbController(f, guiBridge, false, null, lastPath, lastUser, lastPwd, false);
        f.add(newDb, BorderLayout.CENTER);
        f.setTitle("Connect to remote database");
        f.pack();
        f.setIconImage(ImageCache.getInstance().getImage(ImageCache.CONNECT_REMOTE).getImage());
        f.setLocationRelativeTo(_newDbButton);
        f.setVisible(true);
        f.setDefaultCloseOperation(JDialog.DISPOSE_ON_CLOSE);
        if (newDb.isOk()) {
            String dbPath = newDb.getDbPath();
            String user = newDb.getDbUser();
            String pwd = newDb.getDbPwd();
            PreferencesHandler.setPreference(DatabaseGuiUtils.HM_JDBC_LAST_USER, user);
            PreferencesHandler.setPreference(DatabaseGuiUtils.HM_JDBC_LAST_PWD, pwd);
            openRemoteDatabase(dbPath, user, pwd);
        }
    });
    _disconnectDbButton.setVerticalTextPosition(SwingConstants.BOTTOM);
    _disconnectDbButton.setHorizontalTextPosition(SwingConstants.CENTER);
    _disconnectDbButton.setText(DISCONNECT);
    _disconnectDbButton.setToolTipText(DISCONNECT_TOOLTIP);
    _disconnectDbButton.setPreferredSize(preferredToolbarButtonSize);
    _disconnectDbButton.setIcon(ImageCache.getInstance().getImage(ImageCache.DISCONNECT));
    _disconnectDbButton.addActionListener(e -> {
        try {
            closeCurrentDb(true);
        } catch (Exception e1) {
            Logger.INSTANCE.insertError("", "ERROR", e1);
        }
    });
    _historyButton.setVerticalTextPosition(SwingConstants.BOTTOM);
    _historyButton.setHorizontalTextPosition(SwingConstants.CENTER);
    _historyButton.setText(SQL_HISTORY);
    _historyButton.setToolTipText(SQL_HISTORY_TOOLTIP);
    _historyButton.setPreferredSize(preferredToolbarButtonSize);
    _historyButton.setIcon(ImageCache.getInstance().getImage(ImageCache.HISTORY_DB));
    _historyButton.addActionListener(e -> {
        try {
            if (oldSqlCommands.size() == 0) {
                JOptionPane.showMessageDialog(this, "No history available.");
                return;
            }
            String[] sqlHistory = oldSqlCommands.toArray(new String[0]);
            String selected = GuiUtilities.showComboDialog(this, "HISTORY", "", sqlHistory, null);
            if (selected != null) {
                addTextToQueryEditor(selected);
            }
        } catch (Exception e1) {
            Logger.INSTANCE.insertError("", "ERROR", e1);
        }
    });
    _templatesButton.setVerticalTextPosition(SwingConstants.BOTTOM);
    _templatesButton.setHorizontalTextPosition(SwingConstants.CENTER);
    _templatesButton.setText(SQL_TEMPLATES);
    _templatesButton.setToolTipText(SQL_TEMPLATES_TOOLTIP);
    _templatesButton.setPreferredSize(preferredToolbarButtonSize);
    _templatesButton.setIcon(ImageCache.getInstance().getImage(ImageCache.TEMPLATE));
    _templatesButton.addActionListener(e -> {
        try {
            if (currentConnectedSqlDatabase == null) {
                GuiUtilities.showWarningMessage(this, THIS_ACTION_IS_AVAILABLE_ONLY_FOR_SQL_DATABASES);
                return;
            }
            LinkedHashMap<String, String> templatesMap = CommonQueries.getTemplatesMap(currentConnectedSqlDatabase.getType());
            String[] sqlTemplates = templatesMap.keySet().toArray(new String[0]);
            String selected = GuiUtilities.showComboDialog(this, "TEMPLATES", "", sqlTemplates, null);
            if (selected != null) {
                String sql = templatesMap.get(selected);
                addTextToQueryEditor(sql);
            }
        } catch (Exception e1) {
            Logger.INSTANCE.insertError("", "ERROR", e1);
        }
    });
    addComponentListener(new ComponentListener() {

        public void componentShown(ComponentEvent e) {
        }

        public void componentResized(ComponentEvent e) {
        }

        public void componentMoved(ComponentEvent e) {
        }

        public void componentHidden(ComponentEvent e) {
            onClose();
        }
    });
    try {
        databaseTreeView._databaseTreeView.setMinimumSize(new Dimension(300, 200));
        addJtreeDragNDrop();
        addJtreeContextMenu();
        setRightTreeRenderer();
        databaseTreeView._databaseTree.addTreeSelectionListener(new TreeSelectionListener() {

            public void valueChanged(TreeSelectionEvent evt) {
                TreePath[] paths = evt.getPaths();
                currentSelectedDb = null;
                currentSelectedTable = null;
                currentSelectedColumn = null;
                currentSelectedLeaf = null;
                if (paths.length > 0) {
                    Object selectedItem = paths[0].getLastPathComponent();
                    if (selectedItem instanceof DbLevel) {
                        currentSelectedDb = (DbLevel) selectedItem;
                    } else if (selectedItem instanceof ColumnLevel) {
                        currentSelectedColumn = (ColumnLevel) selectedItem;
                    } else if (selectedItem instanceof TableLevel) {
                        currentSelectedTable = (TableLevel) selectedItem;
                        try {
                            QueryResult queryResult = null;
                            if (currentConnectedSqlDatabase != null) {
                                if (currentConnectedSqlDatabase instanceof ASpatialDb) {
                                    queryResult = ((ASpatialDb) currentConnectedSqlDatabase).getTableRecordsMapIn(SqlName.m(currentSelectedTable.tableName), null, SQL_ONSELECT_LIMIT, -1, null);
                                } else {
                                    queryResult = currentConnectedSqlDatabase.getTableRecordsMapFromRawSql("select * from " + currentSelectedTable.tableName, SQL_ONSELECT_LIMIT);
                                }
                            } else if (currentConnectedNosqlDatabase != null) {
                                INosqlCollection collection = currentConnectedNosqlDatabase.getCollection(currentSelectedTable.tableName);
                                List<INosqlDocument> result = collection.find(null, NOSQL_ONSELECT_LIMIT);
                                queryResult = nosqlToQueryResult(result);
                            }
                            loadDataViewer(queryResult);
                        } catch (Exception e) {
                            Logger.INSTANCE.insertError("", "ERROR", e);
                        }
                    } else if (selectedItem instanceof LeafLevel) {
                        currentSelectedLeaf = (LeafLevel) selectedItem;
                    } else {
                        currentSelectedTable = null;
                        currentDataTable.setModel(new DefaultTableModel());
                    }
                }
            }
        });
        databaseTreeView._databaseTree.setVisible(false);
    } catch (Exception e1) {
        Logger.INSTANCE.insertError("", "Error", e1);
    }
    layoutTree(null, false);
    sqlEditorView._runQueryButton.setIcon(ImageCache.getInstance().getImage(ImageCache.RUN));
    sqlEditorView._runQueryButton.setToolTipText(RUN_QUERY_TOOLTIP);
    sqlEditorView._runQueryButton.setText("");
    sqlEditorView._runQueryButton.setPreferredSize(preferredSqleditorButtonSize);
    sqlEditorView._runQueryButton.addActionListener(e -> {
        String sqlText = currentSqlEditorArea.getText().trim();
        if (sqlText.length() == 0) {
            return;
        }
        final LogConsoleController logConsole = new LogConsoleController(null);
        pm = logConsole.getProgressMonitor();
        Logger.INSTANCE.setOutPrintStream(logConsole.getLogAreaPrintStream());
        Logger.INSTANCE.setErrPrintStream(logConsole.getLogAreaPrintStream());
        JFrame window = guiBridge.showWindow(logConsole.asJComponent(), "Console Log");
        new Thread(() -> {
            boolean hadErrors = false;
            try {
                logConsole.beginProcess("Run query");
                hadErrors = runQuery(sqlText, pm);
            } catch (Exception ex) {
                pm.errorMessage(ex.getLocalizedMessage());
                hadErrors = true;
            } finally {
                logConsole.finishProcess();
                logConsole.stopLogging();
                Logger.INSTANCE.resetStreams();
                if (!hadErrors) {
                    logConsole.setVisible(false);
                    window.dispose();
                }
            }
        }, "DatabaseController->run query").start();
    });
    sqlEditorView._runQueryAndStoreButton.setIcon(ImageCache.getInstance().getImage(ImageCache.RUN_TO_FILE));
    sqlEditorView._runQueryAndStoreButton.setToolTipText(RUN_QUERY_TO_FILE_TOOLTIP);
    sqlEditorView._runQueryAndStoreButton.setText("");
    sqlEditorView._runQueryAndStoreButton.setPreferredSize(preferredSqleditorButtonSize);
    sqlEditorView._runQueryAndStoreButton.addActionListener(e -> {
        File selectedFile = null;
        String sqlText = currentSqlEditorArea.getText().trim();
        if (sqlText.length() > 0) {
            if (isSelectOrPragma(sqlText)) {
                File[] saveFiles = guiBridge.showSaveFileDialog("Select file to save to", PreferencesHandler.getLastFile(), null);
                if (saveFiles != null && saveFiles.length > 0) {
                    try {
                        PreferencesHandler.setLastPath(saveFiles[0].getAbsolutePath());
                    } catch (Exception e1) {
                        Logger.INSTANCE.insertError("", "ERROR", e1);
                    }
                } else {
                    return;
                }
                selectedFile = saveFiles[0];
            } else {
                JOptionPane.showMessageDialog(this, "Writing to files is allowed only for SELECT statements and PRAGMAs.", "WARNING", JOptionPane.WARNING_MESSAGE, null);
                return;
            }
        }
        final LogConsoleController logConsole = new LogConsoleController(null);
        pm = logConsole.getProgressMonitor();
        Logger.INSTANCE.setOutPrintStream(logConsole.getLogAreaPrintStream());
        Logger.INSTANCE.setErrPrintStream(logConsole.getLogAreaPrintStream());
        JFrame window = guiBridge.showWindow(logConsole.asJComponent(), "Console Log");
        final File f_selectedFile = selectedFile;
        new Thread(() -> {
            boolean hadErrors = false;
            try {
                if (f_selectedFile != null) {
                    logConsole.beginProcess("Run query");
                    hadErrors = runQueryToFile(sqlText, f_selectedFile, pm);
                }
            } catch (Exception ex) {
                pm.errorMessage(ex.getLocalizedMessage());
                hadErrors = true;
            } finally {
                logConsole.finishProcess();
                logConsole.stopLogging();
                Logger.INSTANCE.resetStreams();
                if (!hadErrors) {
                    logConsole.setVisible(false);
                    window.dispose();
                }
            }
        }, "DatabaseController->run query to file").start();
    });
    sqlEditorView._runQueryAndStoreShapefileButton.setIcon(ImageCache.getInstance().getImage(ImageCache.RUN_TO_SHAPEFILE));
    sqlEditorView._runQueryAndStoreShapefileButton.setToolTipText(RUN_QUERY_TO_SHAPEFILE_TOOLTIP);
    sqlEditorView._runQueryAndStoreShapefileButton.setText("");
    sqlEditorView._runQueryAndStoreShapefileButton.setPreferredSize(preferredSqleditorButtonSize);
    sqlEditorView._runQueryAndStoreShapefileButton.addActionListener(e -> {
        if (!(currentConnectedSqlDatabase instanceof ASpatialDb)) {
            GuiUtilities.showWarningMessage(this, THIS_ACTION_IS_AVAILABLE_ONLY_FOR_SPATIAL_DATABASES);
            return;
        }
        File selectedFile = null;
        String sqlText = currentSqlEditorArea.getText().trim();
        if (sqlText.length() > 0) {
            if (sqlText.toLowerCase().startsWith("select")) {
                File[] saveFiles = guiBridge.showSaveFileDialog("Select shapefile to save to", PreferencesHandler.getLastFile(), HMConstants.vectorFileFilter);
                if (saveFiles != null && saveFiles.length > 0) {
                    try {
                        PreferencesHandler.setLastPath(saveFiles[0].getAbsolutePath());
                    } catch (Exception e1) {
                        Logger.INSTANCE.insertError("", "ERROR", e1);
                    }
                } else {
                    return;
                }
                selectedFile = saveFiles[0];
            } else {
                JOptionPane.showMessageDialog(this, "Writing to shapefile is allowed only for SELECT statements.", "WARNING", JOptionPane.WARNING_MESSAGE, null);
                return;
            }
        }
        final LogConsoleController logConsole = new LogConsoleController(null);
        pm = logConsole.getProgressMonitor();
        Logger.INSTANCE.setOutPrintStream(logConsole.getLogAreaPrintStream());
        Logger.INSTANCE.setErrPrintStream(logConsole.getLogAreaPrintStream());
        JFrame window = guiBridge.showWindow(logConsole.asJComponent(), "Console Log");
        final File f_selectedFile = selectedFile;
        new Thread(() -> {
            boolean hadErrors = false;
            try {
                if (f_selectedFile != null) {
                    logConsole.beginProcess("Run query");
                    hadErrors = runQueryToShapefile(sqlText, f_selectedFile, pm);
                }
            } catch (Exception ex) {
                pm.errorMessage(ex.getLocalizedMessage());
                hadErrors = true;
            } finally {
                logConsole.finishProcess();
                logConsole.stopLogging();
                Logger.INSTANCE.resetStreams();
                if (!hadErrors) {
                    logConsole.setVisible(false);
                    window.dispose();
                }
            }
        }, "DatabaseController->run query to shapefile").start();
    });
    setViewQueryButton(sqlEditorView._viewQueryButton, preferredSqleditorButtonSize, currentSqlEditorArea);
    sqlEditorView._clearSqlEditorbutton.setIcon(ImageCache.getInstance().getImage(ImageCache.TRASH));
    sqlEditorView._clearSqlEditorbutton.setToolTipText(CLEAR_SQL_EDITOR);
    sqlEditorView._clearSqlEditorbutton.setText("");
    sqlEditorView._clearSqlEditorbutton.setPreferredSize(preferredSqleditorButtonSize);
    sqlEditorView._clearSqlEditorbutton.addActionListener(e -> {
        currentSqlEditorArea.setText("");
    });
}

Example 5 with TableLevel

use of org.hortonmachine.dbs.compat.objects.TableLevel in project hortonmachine by TheHortonMachine.

the class DatabaseViewer method makeTableAction.

@SuppressWarnings("serial")
protected List<Action> makeTableAction(final TableLevel selectedTable) {
    List<Action> actions = new ArrayList<>();
    addIfNotNull(actions, sqlTemplatesAndActions.getCountRowsAction(selectedTable, this));
    addSeparator(actions);
    addIfNotNull(actions, sqlTemplatesAndActions.getInsertCollectionDocumentAction(guiBridge, this));
    addIfNotNull(actions, sqlTemplatesAndActions.getUpdateCollectionDocumentAction(guiBridge, this));
    addIfNotNull(actions, sqlTemplatesAndActions.getDeleteCollectionDocumentByIdAction(guiBridge, this));
    addIfNotNull(actions, sqlTemplatesAndActions.getSelectAction(selectedTable, this));
    addIfNotNull(actions, sqlTemplatesAndActions.getInsertAction(selectedTable, this));
    addIfNotNull(actions, sqlTemplatesAndActions.getGenerateInsertExportAction(selectedTable, this));
    addSeparator(actions);
    addIfNotNull(actions, sqlTemplatesAndActions.getDropAction(selectedTable, this));
    addSeparator(actions);
    addIfNotNull(actions, sqlTemplatesAndActions.getReprojectTableAction(selectedTable, this));
    addSeparator(actions);
    if (selectedTable.isGeo) {
        EDb type = currentConnectedSqlDatabase.getType();
        boolean useToWktBridge = true;
        if (type == EDb.GEOPACKAGE) {
            useToWktBridge = false;
        }
        addIfNotNull(actions, sqlTemplatesAndActions.getImportShapefileDataAction(guiBridge, selectedTable, this, useToWktBridge));
        addIfNotNull(actions, sqlTemplatesAndActions.getQuickViewTableAction(selectedTable, this));
        addIfNotNull(actions, sqlTemplatesAndActions.getQuickViewTableGeometriesAction(selectedTable, this));
        addIfNotNull(actions, sqlTemplatesAndActions.getOpenInSldEditorAction(selectedTable, this));
        addIfNotNull(actions, sqlTemplatesAndActions.getOpenInGformsEditorAction(selectedTable, this));
    }
    if (selectedTable.tableName.equals(LogDb.TABLE_MESSAGES)) {
        actions.add(new AbstractAction("Show HTML report") {

            @Override
            public void actionPerformed(ActionEvent e) {
                try {
                    String sql = "select  " + LogDb.type_NAME + ", " + LogDb.TimeStamp_NAME + ", " + LogDb.tag_NAME + "," + LogDb.message_NAME + " from " + LogDb.TABLE_MESSAGES + " order by " + LogDb.TimeStamp_NAME + " desc";
                    LinkedHashMap<String, List<Message>> day2MessageMap = new LinkedHashMap<>();
                    currentConnectedSqlDatabase.execOnConnection(connection -> {
                        try (IHMStatement stmt = connection.createStatement();
                            IHMResultSet rs = stmt.executeQuery(sql)) {
                            while (rs.next()) {
                                int type = rs.getInt(1);
                                long ts = rs.getLong(2);
                                String tag = rs.getString(3);
                                if (tag == null)
                                    tag = "";
                                String msg = rs.getString(4);
                                String tsString = new DateTime(ts).toString(HMConstants.dateTimeFormatterYYYYMMDDHHMMSS);
                                String[] split = tsString.split(":");
                                String dayHour = split[0];
                                Message logMsg = new Message();
                                logMsg.tag = tag;
                                logMsg.msg = msg;
                                logMsg.ts = ts;
                                logMsg.type = type;
                                List<Message> messages = day2MessageMap.get(dayHour);
                                if (messages == null) {
                                    messages = new ArrayList<Message>();
                                    day2MessageMap.put(dayHour, messages);
                                }
                                messages.add(logMsg);
                            }
                            return "";
                        }
                    });
                    HtmlReport rep = new HtmlReport();
                    StringBuilder sb = new StringBuilder();
                    rep.openReport(sb, "Log Messages");
                    rep.openTable(sb, 98);
                    String white = "#FFFFFF";
                    String warning = "#ffb380";
                    String debug = "#afe9af";
                    String error = "#ff5555";
                    String header = "#e6e6e6";
                    String oddRow = "#f2f2f2";
                    String evenRow = "#d5f9fe";
                    rep.openTableRow(sb);
                    rep.openTableCell(sb, header, "11", null, null);
                    sb.append("DAY + HOUR ");
                    rep.closeTableCell(sb);
                    rep.openTableCell(sb, header, "6", null, null);
                    sb.append("MIN:SEC");
                    rep.closeTableCell(sb);
                    rep.openTableCell(sb, header, "13", null, null);
                    sb.append("TAG");
                    rep.closeTableCell(sb);
                    rep.openTableCell(sb, header, "80", null, null);
                    sb.append("MESSAGE");
                    rep.closeTableCell(sb);
                    rep.closeTableRow(sb);
                    boolean odd = false;
                    for (Entry<String, List<Message>> entry : day2MessageMap.entrySet()) {
                        String day = entry.getKey();
                        List<Message> msgList = entry.getValue();
                        rep.openTableRow(sb);
                        String firstColor = evenRow;
                        if (odd) {
                            firstColor = oddRow;
                        }
                        odd = !odd;
                        int rowSpan = msgList.size() + 1;
                        rep.openTableCell(sb, firstColor, "11", null, rowSpan + "");
                        sb.append(day);
                        rep.closeTableCell(sb);
                        rep.closeTableRow(sb);
                        for (Message message : msgList) {
                            String color = white;
                            if (message.type == EMessageType.DEBUG.getCode()) {
                                color = debug;
                            } else if (message.type == EMessageType.WARNING.getCode()) {
                                color = warning;
                            } else if (message.type == EMessageType.ERROR.getCode()) {
                                color = error;
                            }
                            String tsString = new DateTime(message.ts).toString(HMConstants.dateTimeFormatterYYYYMMDDHHMMSS);
                            String[] split = tsString.split(":");
                            String time = split[1] + ":" + split[2];
                            rep.openTableRow(sb);
                            rep.openTableCell(sb, color, "6", null, null);
                            sb.append(time);
                            rep.closeTableCell(sb);
                            if (message.tag.length() == 0) {
                                rep.openTableCell(sb, color, "80", "2", null);
                                sb.append("<pre>").append(message.msg).append("</pre>");
                                rep.closeTableCell(sb);
                            } else {
                                rep.openTableCell(sb, color, "13", null, null);
                                sb.append("<b>").append(message.tag).append("<b>");
                                rep.closeTableCell(sb);
                                rep.openTableCell(sb, color, "70", null, null);
                                sb.append("<pre>").append(message.msg).append("</pre>");
                                rep.closeTableCell(sb);
                            }
                            rep.closeTableRow(sb);
                        }
                    }
                    rep.closeTable(sb);
                    rep.closeReport(sb);
                    File tmpFile = File.createTempFile("HM-", "_debug.html");
                    FileUtilities.writeFile(sb.toString(), tmpFile);
                    GuiUtilities.openFile(tmpFile);
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        });
    }
    return actions;
}