Examples with EDb org.hortonmachine.dbs.compat.EDb used on opensource projects

Example 1 with EDb

use of org.hortonmachine.dbs.compat.EDb in project hortonmachine by TheHortonMachine.

the class DbsHelper method runRawSqlToFeatureCollection.

/**
 * Extractes a featurecollection from an sql statement.
 *
 * <p>The assumption is made that the first string after the FROM
 * keyword in the select statement is the table that contains the geometry.
 *
 * @param name the name of the resulting layer.
 * @param db
 * @param simpleSql the sql.
 * @param roi an optional region to limit the query to.
 * @return the features.
 * @throws Exception
 */
public static DefaultFeatureCollection runRawSqlToFeatureCollection(String name, ASpatialDb db, String simpleSql, Polygon roi) throws Exception {
    int indexOf = simpleSql.toLowerCase().indexOf("from");
    String afterFrom = simpleSql.substring(indexOf + 5).trim();
    String tableName = null;
    if (afterFrom.startsWith("'")) {
        int nextAp = afterFrom.indexOf("'", 1) + 1;
        tableName = afterFrom.substring(0, nextAp);
    } else {
        int nextSpace = afterFrom.indexOf(' ');
        tableName = afterFrom.substring(0, nextSpace);
    }
    if (tableName == null) {
        throw new RuntimeException("The geometry table name needs to be the first after the FROM keyword.");
    }
    GeometryColumn geometryColumns = db.getGeometryColumnsForTable(SqlName.m(tableName));
    if (geometryColumns == null) {
        throw new IllegalArgumentException("The supplied table name doesn't seem to be spatial: " + tableName);
    }
    if (geometryColumns.srid == 0) {
        // fallback with hope
        geometryColumns.srid = 4326;
    }
    String geomColumnName = geometryColumns.geometryColumnName;
    if (roi != null) {
        String where = db.getSpatialindexGeometryWherePiece(SqlName.m(tableName), null, roi);
        simpleSql += " where " + where;
    }
    String _simpleSql = simpleSql;
    return db.execOnConnection(connection -> {
        DefaultFeatureCollection fc = new DefaultFeatureCollection();
        try (IHMStatement stmt = connection.createStatement();
            IHMResultSet rs = stmt.executeQuery(_simpleSql)) {
            IHMResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            int geometryIndex = -1;
            CoordinateReferenceSystem crs = CrsUtilities.getCrsFromEpsg("EPSG:" + geometryColumns.srid);
            SimpleFeatureTypeBuilder b = new SimpleFeatureTypeBuilder();
            String _name = "shpexport";
            if (name != null)
                _name = name;
            b.setName(_name);
            b.setCRS(crs);
            EDb eDb = db.getType();
            ADatabaseSyntaxHelper syntaxHelper = eDb.getDatabaseSyntaxHelper();
            IGeometryParser gp = eDb.getGeometryParser();
            for (int i = 1; i <= columnCount; i++) {
                String columnTypeName = rsmd.getColumnTypeName(i);
                String columnName = rsmd.getColumnName(i);
                if (geomColumnName.equalsIgnoreCase(columnName) || ESpatialiteGeometryType.isGeometryName(columnTypeName)) {
                    geometryIndex = i;
                    if (rs.next()) {
                        Geometry geometry = gp.fromResultSet(rs, geometryIndex);
                        b.add("the_geom", geometry.getClass());
                        break;
                    }
                }
            }
            for (int i = 1; i <= columnCount; i++) {
                String columnTypeName = rsmd.getColumnTypeName(i);
                String columnName = rsmd.getColumnName(i);
                if (i != geometryIndex) {
                    if (columnName.toLowerCase().equals("id")) {
                        columnName = "origid";
                    }
                    if (columnName.length() > 9) {
                        columnName = columnName.substring(0, 9);
                    }
                    if ("INTEGER".equals(columnTypeName) || syntaxHelper.INTEGER().equals(columnTypeName)) {
                        b.add(columnName, Integer.class);
                    } else if (syntaxHelper.LONG().equals(columnTypeName)) {
                        b.add(columnName, Long.class);
                    } else if ("DOUBLE".equals(columnTypeName) || "FLOAT".equals(columnTypeName) || syntaxHelper.REAL().equals(columnTypeName)) {
                        b.add(columnName, Double.class);
                    } else if ("DATE".equals(columnTypeName)) {
                        b.add(columnName, Date.class);
                    } else if (syntaxHelper.TEXT().equals(columnTypeName) || true) {
                        b.add(columnName, String.class);
                    }
                }
            }
            SimpleFeatureType type = b.buildFeatureType();
            SimpleFeatureBuilder builder = new SimpleFeatureBuilder(type);
            do {
                try {
                    List<Object> values = new ArrayList<>();
                    Geometry geometry = gp.fromResultSet(rs, geometryIndex);
                    values.add(geometry);
                    for (int j = 1; j <= columnCount; j++) {
                        if (j != geometryIndex) {
                            Object object = rs.getObject(j);
                            values.add(object);
                        }
                    }
                    builder.addAll(values);
                    SimpleFeature feature = builder.buildFeature(null);
                    fc.add(feature);
                } catch (Exception e) {
                    logger.insertError("DbsHelper", "ERROR", e);
                }
            } while (rs.next());
        }
        return fc;
    });
}

Example 2 with EDb

use of org.hortonmachine.dbs.compat.EDb in project hortonmachine by TheHortonMachine.

the class DatabaseLasDataManager method open.

/**
 * Open the main folder file and read the main index.
 *
 * @throws Exception
 */
@Override
public void open() throws Exception {
    EDb edb = EDb.fromFileDesktop(databaseFile);
    spatialDb = edb.getSpatialDb();
    spatialDb.open(databaseFile.getAbsolutePath());
    GeometryColumn geometryColumnsForTable = spatialDb.getGeometryColumnsForTable(LasCellsTable.TABLENAME);
    crs = CrsUtilities.getCrsFromSrid(geometryColumnsForTable.srid);
    isOpen = true;
}

Example 3 with EDb

use of org.hortonmachine.dbs.compat.EDb 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 4 with EDb

use of org.hortonmachine.dbs.compat.EDb 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;
}

Example 5 with EDb

use of org.hortonmachine.dbs.compat.EDb in project hortonmachine by TheHortonMachine.

the class DatabaseViewer method main.

public static void main(String[] args) throws Exception {
    GuiUtilities.setDefaultLookAndFeel();
    DefaultGuiBridgeImpl gBridge = new DefaultGuiBridgeImpl();
    final DatabaseViewer controller = new DatabaseViewer(gBridge);
    SettingsController.applySettings(controller);
    final JFrame frame = gBridge.showWindow(controller.asJComponent(), "HortonMachine Database Viewer");
    GuiUtilities.setDefaultFrameIcon(frame);
    GuiUtilities.addClosingListener(frame, controller);
    File openFile = null;
    if (args.length > 0 && new File(args[0]).exists()) {
        openFile = new File(args[0]);
    } else {
        String lastPath = PreferencesHandler.getPreference(DatabaseGuiUtils.HM_SPATIALITE_LAST_FILE, (String) null);
        if (lastPath != null) {
            File tmp = new File(lastPath);
            if (tmp.exists()) {
                openFile = tmp;
            }
        }
    }
    if (openFile != null) {
        String absolutePath = openFile.getAbsolutePath();
        EDb dbType = null;
        if (SpatialiteCommonMethods.isSqliteFile(openFile)) {
            if (absolutePath.endsWith(EDb.GEOPACKAGE.getExtension())) {
                dbType = EDb.GEOPACKAGE;
            } else {
                dbType = EDb.SPATIALITE;
            }
        } else if (absolutePath.endsWith(EDb.H2GIS.getExtension())) {
            dbType = EDb.H2GIS;
        } else {
            absolutePath = null;
        }
        controller.openDatabase(dbType, absolutePath, null, null);
    }
}