Attachment #114436 for bug #204550

This Bugzilla instance is a read-only archive of historic NetBeans bug reports. To report a bug in NetBeans please follow the project's instructions for reporting issues.

View | Details | Raw Unified | Return to bug 204550
Collapse All | Expand All





package org.netbeans.modules.db.sql.execute;

import org.netbeans.modules.db.sql.history.SQLHistoryEntry;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;

import org.netbeans.api.db.explorer.DatabaseConnection;
import org.netbeans.modules.db.dataview.api.DataView;
import org.netbeans.modules.db.sql.history.SQLHistoryManager;
import org.openide.filesystems.FileObject;
import org.openide.filesystems.FileUtil;

/**
 * Support class for executing SQL statements.


    private static final Logger LOGGER = Logger.getLogger(SQLExecuteHelper.class.getName());
    private static final boolean LOG = LOGGER.isLoggable(Level.FINE);
    private static final FileObject USERDIR = FileUtil.getConfigRoot();
    
    /**
     * Executes a SQL string, possibly containing multiple statements. Returns the execution

            DataView view = DataView.create(conn, sql, pageSize);

            // Save SQL statements executed for the SQLHistoryManager
            SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry(url, sql, new Date()));

            result = new SQLExecutionResult(info, view);


        }
                
        // Persist SQL executed
        SQLHistoryManager.getInstance().save();

        if (!cancelled) {
            return new SQLExecutionResults(results);




              <Group type="103" groupAlignment="0" attributes="0">
                  <Group type="102" attributes="0">
                      <Group type="103" groupAlignment="0" attributes="0">
                          <Component id="jScrollPane1" pref="645" max="32767" attributes="0"/>
                          <Group type="102" attributes="0">
                              <Component id="jLabel1" min="-2" max="-2" attributes="0"/>
                              <EmptySpace type="unrelated" max="-2" attributes="0"/>
                              <Component id="connectionUrlComboBox" pref="301" max="32767" attributes="0"/>
                              <EmptySpace type="separate" max="-2" attributes="0"/>
                              <Component id="jLabel2" min="-2" max="-2" attributes="0"/>
                              <EmptySpace type="unrelated" max="-2" attributes="0"/>

              <Group type="103" groupAlignment="0" attributes="0">
                  <Component id="insertSQLButton" alignment="0" min="-2" max="-2" attributes="0"/>
                  <Group type="102" alignment="1" attributes="0">
                      <Component id="jScrollPane1" pref="268" max="32767" attributes="0"/>
                      <EmptySpace max="-2" attributes="0"/>
                      <Group type="103" groupAlignment="3" attributes="0">
                          <Component id="sqlLimitTextField" alignment="3" min="-2" max="-2" attributes="0"/>

      </Properties>
    </Component>
    <Component class="javax.swing.JComboBox" name="connectionUrlComboBox">
      <Properties>
        <Property name="renderer" type="javax.swing.ListCellRenderer" editor="org.netbeans.modules.form.RADConnectionPropertyEditor">
          <Connection code="new ConnectionUrlRenderer()" type="code"/>
        </Property>
      </Properties>
      <AccessibilityProperties>
        <Property name="AccessibleContext.accessibleName" type="java.lang.String" editor="org.netbeans.modules.i18n.form.FormI18nStringEditor">
          <ResourceString bundle="org/netbeans/modules/db/sql/execute/ui/Bundle.properties" key="ASCN_ConnectionCombo" replaceFormat="org.openide.util.NbBundle.getMessage({sourceFileName}.class, &quot;{key}&quot;)"/>

      <SubComponents>
        <Component class="javax.swing.JTable" name="sqlHistoryTable">
          <Properties>
            <Property name="autoCreateColumnsFromModel" type="boolean" value="false"/>
            <Property name="model" type="javax.swing.table.TableModel" editor="org.netbeans.modules.form.RADConnectionPropertyEditor">
              <Connection code="htm" type="code"/>
            </Property>
            <Property name="tableHeader" type="javax.swing.table.JTableHeader" editor="org.netbeans.modules.form.editors2.JTableHeaderEditor">
              <TableHeader reorderingAllowed="true" resizingAllowed="true"/>
                <Column maxWidth="-1" minWidth="-1" prefWidth="-1" resizable="true">
                  <Title/>
                  <Editor/>
                  <Renderer/>
                </Column>
                <Column maxWidth="-1" minWidth="-1" prefWidth="-1" resizable="true">
                  <Title/>
                  <Editor/>
                  <Renderer/>
                </Column>
              </TableColumnModel>
            </Property>
            <Property name="gridColor" type="java.awt.Color" editor="org.netbeans.beaninfo.editors.ColorEditor">
              <Color blue="c0" green="c0" id="lightGray" palette="1" red="c0" type="palette"/>
            </Property>
          </Properties>
          <AccessibilityProperties>
            <Property name="AccessibleContext.accessibleName" type="java.lang.String" editor="org.netbeans.modules.i18n.form.FormI18nStringEditor">
              <ResourceString bundle="org/netbeans/modules/db/sql/execute/ui/Bundle.properties" key="ACSN_History" replaceFormat="org.openide.util.NbBundle.getMessage({sourceFileName}.class, &quot;{key}&quot;)"/>
            </Property>
            <Property name="AccessibleContext.accessibleDescription" type="java.lang.String" editor="org.netbeans.modules.i18n.form.FormI18nStringEditor" postCode="int dateColumnWidth1 = new JTextField(dateTimeFormat.format(new Date())).getPreferredSize().width;&#xa;int dateColumnWidth2 = new JTextField(NbBundle.getMessage(SQLHistoryPanel.class, &quot;LBL_DateTableTitle&quot;) + &quot;XXXXX&quot;).getPreferredSize().width;&#xa;int dateColumnWidth = Math.max(dateColumnWidth1, dateColumnWidth2);&#xa;        &#xa;TableColumnModel sqlHistoryTableTCM = sqlHistoryTable.getColumnModel();&#xa;&#xa;TableColumn sqlHistoryTableColumn;&#xa;&#xa;sqlHistoryTableColumn = new TableColumn(1);&#xa;sqlHistoryTableColumn.setHeaderValue(NbBundle.getMessage(SQLHistoryPanel.class, &quot;LBL_SQLTableTitle&quot;));&#xa;&#xa;sqlHistoryTableTCM.addColumn(sqlHistoryTableColumn);&#xa;&#xa;sqlHistoryTableColumn = new TableColumn(2);&#xa;sqlHistoryTableColumn.setMinWidth(dateColumnWidth);&#xa;sqlHistoryTableColumn.setPreferredWidth(dateColumnWidth);&#xa;sqlHistoryTableColumn.setMaxWidth(dateColumnWidth);&#xa;sqlHistoryTableColumn.setHeaderValue(NbBundle.getMessage(SQLHistoryPanel.class, &quot;LBL_DateTableTitle&quot;));&#xa;sqlHistoryTableColumn.setCellRenderer(new DefaultTableCellRenderer() {&#xa;    public Component getTableCellRendererComponent(JTable table, Object value, boolean isSelected, boolean hasFocus, int row, int column) {&#xa;        if(value instanceof Date) {&#xa;            value = dateTimeFormat.format((Date) value);&#xa;        }&#xa;        return super.getTableCellRendererComponent(table, value, isSelected, hasFocus, row, column);&#xa;    }&#xa;});&#xa;        &#xa;sqlHistoryTableTCM.addColumn(sqlHistoryTableColumn);">
              <ResourceString bundle="org/netbeans/modules/db/sql/execute/ui/Bundle.properties" key="ACSD_History" replaceFormat="org.openide.util.NbBundle.getMessage({sourceFileName}.class, &quot;{key}&quot;)"/>
            </Property>
          </AccessibilityProperties>
          <AuxValues>
            <AuxValue name="JavaCodeGenerator_CreateCodeCustom" type="java.lang.String" value="new JTable() {&#xa;    public Component prepareRenderer(TableCellRenderer renderer,&#xa;        int rowIndex, int vColIndex) {&#xa;        Component c = super.prepareRenderer(renderer, rowIndex, vColIndex);&#xa;        if (c instanceof JComponent) {&#xa;            JComponent jc = (JComponent)c;&#xa;            jc.setToolTipText(getSQLHistoryTooltipValue(sqlHistoryTable, rowIndex, vColIndex));&#xa;        }&#xa;        return c;&#xa;    }&#xa;}"/>
          </AuxValues>
        </Component>
      </SubComponents>

    <Component class="javax.swing.JTextField" name="sqlLimitTextField">
      <Properties>
        <Property name="text" type="java.lang.String" editor="org.netbeans.modules.form.RADConnectionPropertyEditor">
          <Connection code="Integer.toString(SQLHistoryManager.getInstance().getListSize())" type="code"/>
        </Property>
        <Property name="minimumSize" type="java.awt.Dimension" editor="org.netbeans.beaninfo.editors.DimensionEditor">
          <Dimension value="[18, 22]"/>

        <Property name="AccessibleContext.accessibleName" type="java.lang.String" editor="org.netbeans.modules.i18n.form.FormI18nStringEditor">
          <ResourceString bundle="org/netbeans/modules/db/sql/execute/ui/Bundle.properties" key="ACSN_Save" replaceFormat="org.openide.util.NbBundle.getMessage({sourceFileName}.class, &quot;{key}&quot;)"/>
        </Property>
        <Property name="AccessibleContext.accessibleDescription" type="java.lang.String" editor="org.netbeans.modules.i18n.form.FormI18nStringEditor" postCode="sqlLimitTextField.setInputVerifier(new InputVerifier() {&#xa;&#xa;            public boolean verify(JComponent input) {&#xa;                JTextField tf = (JTextField) input;&#xa;                return tf.getText().matches(&quot;^\\d+$&quot;);&#xa;            }&#xa;        });">
          <ResourceString bundle="org/netbeans/modules/db/sql/execute/ui/Bundle.properties" key="ACSD_Save" replaceFormat="org.openide.util.NbBundle.getMessage({sourceFileName}.class, &quot;{key}&quot;)"/>
        </Property>
      </AccessibilityProperties>




 * 
 * Portions Copyrighted 2009 - 2010 Sun Microsystems, Inc.
 */

/*
 * SQLHistoryPanel.java
 *

package org.netbeans.modules.db.sql.execute.ui;

import java.awt.Component;
import java.awt.Cursor;
import java.awt.Window;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.text.DateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.DefaultComboBoxModel;
import javax.swing.InputVerifier;
import javax.swing.JComponent;
import javax.swing.JEditorPane;
import javax.swing.JLabel;
import javax.swing.JList;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.ListSelectionModel;
import javax.swing.RowFilter;
import javax.swing.RowFilter.Entry;
import javax.swing.RowSorter.SortKey;
import javax.swing.SortOrder;
import javax.swing.event.DocumentEvent;
import javax.swing.event.DocumentListener;
import javax.swing.event.ListSelectionEvent;
import javax.swing.event.ListSelectionListener;
import javax.swing.event.TableModelEvent;
import javax.swing.event.TableModelListener;
import javax.swing.table.AbstractTableModel;
import javax.swing.table.DefaultTableCellRenderer;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.JTableHeader;
import javax.swing.table.TableCellRenderer;
import javax.swing.table.TableColumn;
import javax.swing.table.TableColumnModel;
import javax.swing.table.TableRowSorter;
import javax.swing.text.BadLocationException;
import javax.swing.text.Caret;
import javax.swing.text.Document;
import org.netbeans.api.db.explorer.ConnectionManager;
import org.netbeans.api.db.explorer.DatabaseConnection;
import org.netbeans.api.editor.EditorRegistry;
import org.netbeans.modules.db.sql.history.SQLHistoryEntry;
import org.netbeans.modules.db.sql.history.SQLHistoryException;
import org.netbeans.modules.db.sql.history.SQLHistoryManager;
import org.netbeans.modules.db.sql.history.SQLHistoryModel;
import org.netbeans.modules.db.sql.history.SQLHistoryModelImpl;
import org.netbeans.modules.db.sql.history.SQLHistoryPersistenceManager;
import org.netbeans.modules.db.sql.loader.SQLDataLoader;
import org.openide.awt.MouseUtils;
import org.openide.filesystems.FileObject;
import org.openide.filesystems.FileUtil;
import org.openide.util.Exceptions;
import org.openide.util.NbBundle;
import org.openide.util.NotImplementedException;
import org.openide.util.RequestProcessor;
import org.openide.util.RequestProcessor.Task;

/**
 *
 * @author John Baker
 */
public class SQLHistoryPanel extends javax.swing.JPanel {

    private TableRowSorter<HistoryTableModel> rowSorter;
    private HistoryTableModel htm = new HistoryTableModel();
    public static final String SAVE_STATEMENTS_CLEARED = ""; // NOI18N  
    public static final int SAVE_STATEMENTS_MAX_LIMIT = 10000; 
    public static final int TABLE_DATA_WIDTH_SQL = 125;
    public static final Logger LOGGER = Logger.getLogger(SQLHistoryPanel.class.getName());
    private static final FileObject USERDIR = FileUtil.getConfigRoot();
    private static final FileObject historyRoot = USERDIR.getFileObject(SQLHistoryManager.SQL_HISTORY_FOLDER);
    private static Object[][] data;
    private Set<String> currentConnections = new HashSet<String>();
    private SQLHistoryView view;
    private JEditorPane editorPane;
    final private ListSelectionModel sqlTableSelektion;
    final private DateFormat dateTimeFormat = DateFormat.getDateTimeInstance(DateFormat.SHORT, DateFormat.SHORT);
    private static RequestProcessor RP = new RequestProcessor(SQLHistoryPanel.class);
    private final Task initTask;

    /** Creates new form SQLHistoryPanel */
    public SQLHistoryPanel(final JEditorPane editorPane) {
        this.editorPane = editorPane;

        initComponents();

        rowSorter = new TableRowSorter<HistoryTableModel>(htm);
        sqlHistoryTable.setRowSorter(rowSorter);
        List<SortKey> sortKeys = new ArrayList<SortKey>();
        sortKeys.add(new SortKey(2, SortOrder.DESCENDING));
        rowSorter.setSortKeys(sortKeys);
        rowSorter.setSortsOnUpdates(true);
        rowSorter.sort();

        sqlTableSelektion = sqlHistoryTable.getSelectionModel();

        updateURLList();

        htm.addTableModelListener(new TableModelListener() {

            @Override
            public void tableChanged(TableModelEvent e) {
                updateURLList();
            }
        });

        searchTextField.getDocument().addDocumentListener(new DocumentListener() {

            @Override
            public void insertUpdate(DocumentEvent e) {
                updateFilter();
            }

            @Override
            public void removeUpdate(DocumentEvent e) {
                updateFilter();
            }

            @Override
            public void changedUpdate(DocumentEvent e) {
                updateFilter();
            }

            @Override
            public List<SQLHistory> getSQLHistoryList() throws SQLHistoryException {
                return Collections.emptyList();
            }

            @Override
            public void setSQLHistoryList(List<SQLHistory> sqlHistoryList) {
            }
        });

        sqlHistoryTable.addMouseListener(new MouseAdapter() {
        initComponents();
        setupSQLSaveLimit();
        initTask = RP.post(new Runnable() {

            @Override
            public void run() {
                lazyInit();
            }
        });
    }

    public void lazyInit() {
        view = new SQLHistoryView(new SQLHistoryModelImpl());
        for (DatabaseConnection existingConnection : ConnectionManager.getDefault().getConnections()) {
            urlAliasMap.put(existingConnection.getDatabaseURL(), existingConnection.getDisplayName());
            aliasUrlMap.put(existingConnection.getDisplayName(), existingConnection.getDatabaseURL());
        }
        // Adjust table column width
        SwingUtilities.invokeLater(new Runnable() {

            @Override
            public void run() {
                setWaitingState(false);
                initSQLHistoryTableData();
                initComponentData();
                adjustColumnPreferredWidths(sqlHistoryTable);
                sqlHistoryTable.revalidate();
            }
        });
    }

    @Override
    public void addNotify() {
        super.addNotify();
        //show progress for initialize method
        final Window w = findWindowParent();
        if (w != null) {
            w.addWindowListener(new WindowAdapter() {

                @Override
                public void windowOpened(WindowEvent e) {
                    setWaitingState(! initTask.isFinished());
                }
            });
        }
    }

    private void setWaitingState(boolean waitingState) {
        boolean enabled = !waitingState;
        Component parent = getParent();
        Component rootPane = getRootPane();
        if (parent != null) {
            parent.setEnabled(enabled);
        }
        if (rootPane != null) {
            if (enabled) {
                rootPane.setCursor(null);
            } else {
                rootPane.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
            }
        }
    }

    private Window findWindowParent() {
        Component c = this;
        while (c != null) {
            c = c.getParent();
            if (c instanceof Window) {
                return (Window) c;
            }
        }
        return null;
    }

    private void setupSQLSaveLimit() {
        // SQL statments save limit
        String savedLimit = NbPreferences.forModule(SQLHistoryPanel.class).get("SQL_STATEMENTS_SAVED_FOR_HISTORY", ""); // NOI18N
        if (null != savedLimit && !savedLimit.equals(SAVE_STATEMENTS_CLEARED)) {
            sqlLimitTextField.setText(savedLimit);
        } else {
            sqlLimitTextField.setText(SQLHistoryManager.SAVE_STATEMENTS_MAX_LIMIT_ENTERED);
            savedLimit = SQLHistoryManager.SAVE_STATEMENTS_MAX_LIMIT_ENTERED;
            NbPreferences.forModule(SQLHistoryPanel.class).put("SQL_STATEMENTS_SAVED_FOR_HISTORY", SQLHistoryManager.SAVE_STATEMENTS_MAX_LIMIT_ENTERED);  // NOI18N
        }
    }

    private void initComponentData() {
        searchTextField.getDocument().addDocumentListener((HistoryTableModel) sqlHistoryTable.getModel());
        sqlHistoryTable.getColumnModel().getColumn(0).setHeaderValue(NbBundle.getMessage(SQLHistoryPanel.class, "LBL_SQLTableTitle"));
        sqlHistoryTable.getColumnModel().getColumn(1).setHeaderValue(NbBundle.getMessage(SQLHistoryPanel.class, "LBL_DateTableTitle"));
        // Add mouse listener to listen for mouse click on the table header so columns can be sorted
        JTableHeader header = sqlHistoryTable.getTableHeader();
        header.addMouseListener(new ColumnListener());

        // Add mouse listener for the case when a user double-clicks on a row to insert SQL
        sqlHistoryTable.addMouseListener(new MouseAdapter() {
            @Override
            public void mouseClicked(MouseEvent e) {
                if (MouseUtils.isDoubleClick(e)) {
                    insertSQL();
                    e.consume();
            }
    }
        });

        sqlTableSelektion.addListSelectionListener(
                new ListSelectionListener() {

            @Override
                    public void valueChanged(ListSelectionEvent e) {
                        if (sqlTableSelektion.isSelectionEmpty()) {
                            insertSQLButton.setEnabled(false);
                        } else {
                            insertSQLButton.setEnabled(true);
            }
    }
                });

        connectionUrlComboBox.addActionListener(new ActionListener() {

                @Override
            public void actionPerformed(ActionEvent e) {
                updateFilter();
                }
            });
        }

    private void updateFilter() {
        List<RowFilter<HistoryTableModel, Integer>> rowFilter = new ArrayList<RowFilter<HistoryTableModel, Integer>>();

        String url = (String) connectionUrlComboBox.getSelectedItem();

        if ( url != null && !url.equals(NbBundle.getMessage(SQLHistoryPanel.class, "LBL_URLComboBoxAllConnectionsItem"))) {
            rowFilter.add(new EqualsFilter(url, 0));
    }

        if (!searchTextField.getText().equals("")) {
            rowFilter.add(new ContainsInsensitiveFilter(searchTextField.getText(), 1));
        }

        if (rowFilter.size() > 0) {
            rowSorter.setRowFilter(RowFilter.andFilter(rowFilter));
        } else {
            rowSorter.setRowFilter(null);
                }
            }
        });

    private void updateURLList() {
        List<String> urls = new ArrayList<String>(htm.getJdbcURLs());
        urls.add(0, NbBundle.getMessage(SQLHistoryPanel.class, "LBL_URLComboBoxAllConnectionsItem"));
        Object selected = connectionUrlComboBox.getSelectedItem();
        connectionUrlComboBox.setModel(new DefaultComboBoxModel(urls.toArray()));
        if(selected != null && urls.contains(selected)) {
            connectionUrlComboBox.setSelectedItem(selected);
        } else {
            connectionUrlComboBox.setSelectedIndex(0);
            return ;
        }
        // Initialize sql column data          
        List<String> sqlList = view.getSQLList(null);
        List<String> dateList = view.getDateList(null);
        data = new Object[sqlList.size()][2];
        int row = 0;
        int maxLength; 
        int length;
        for (String sql : sqlList) {
            length = sql.trim().length();
            maxLength = length > TABLE_DATA_WIDTH_SQL ? TABLE_DATA_WIDTH_SQL : length;
            data[row][0] = sql.trim().substring(0, maxLength).replaceAll("\n", " ");
            row++;
        }
        // Initialize data
        row = 0;
        for (String date : dateList) {
            data[row][1] = date;
            row++;
        }
    }


                Component c = super.prepareRenderer(renderer, rowIndex, vColIndex);
                if (c instanceof JComponent) {
                    JComponent jc = (JComponent)c;
                    jc.setToolTipText(getSQLHistoryTooltipValue(sqlHistoryTable, rowIndex, vColIndex));
                }
                return c;
            }


        jLabel1.setText(org.openide.util.NbBundle.getMessage(SQLHistoryPanel.class, "LBL_Connection")); // NOI18N

        connectionUrlComboBox.setRenderer(new ConnectionUrlRenderer());

        jLabel2.setLabelFor(searchTextField);
        org.openide.awt.Mnemonics.setLocalizedText(jLabel2, org.openide.util.NbBundle.getMessage(SQLHistoryPanel.class, "LBL_Match")); // NOI18N


            }
        });

        sqlHistoryTable.setAutoCreateColumnsFromModel(false);
        sqlHistoryTable.setModel(htm);
        jScrollPane1.setViewportView(sqlHistoryTable);
        sqlHistoryTable.getAccessibleContext().setAccessibleName(org.openide.util.NbBundle.getMessage(SQLHistoryPanel.class, "ACSN_History")); // NOI18N
        sqlHistoryTable.getAccessibleContext().setAccessibleDescription(org.openide.util.NbBundle.getMessage(SQLHistoryPanel.class, "ACSD_History")); // NOI18N
        int dateColumnWidth1 = new JTextField(dateTimeFormat.format(new Date())).getPreferredSize().width;
        int dateColumnWidth2 = new JTextField(NbBundle.getMessage(SQLHistoryPanel.class, "LBL_DateTableTitle") + "XXXXX").getPreferredSize().width;
        int dateColumnWidth = Math.max(dateColumnWidth1, dateColumnWidth2);

        TableColumnModel sqlHistoryTableTCM = sqlHistoryTable.getColumnModel();

        TableColumn sqlHistoryTableColumn;

        sqlHistoryTableColumn = new TableColumn(1);
        sqlHistoryTableColumn.setHeaderValue(NbBundle.getMessage(SQLHistoryPanel.class, "LBL_SQLTableTitle"));

        sqlHistoryTableTCM.addColumn(sqlHistoryTableColumn);

        sqlHistoryTableColumn = new TableColumn(2);
        sqlHistoryTableColumn.setMinWidth(dateColumnWidth);
        sqlHistoryTableColumn.setPreferredWidth(dateColumnWidth);
        sqlHistoryTableColumn.setMaxWidth(dateColumnWidth);
        sqlHistoryTableColumn.setHeaderValue(NbBundle.getMessage(SQLHistoryPanel.class, "LBL_DateTableTitle"));
        sqlHistoryTableColumn.setCellRenderer(new DefaultTableCellRenderer() {
            public Component getTableCellRendererComponent(JTable table, Object value, boolean isSelected, boolean hasFocus, int row, int column) {
                if(value instanceof Date) {
                    value = dateTimeFormat.format((Date) value);
                }
                return super.getTableCellRendererComponent(table, value, isSelected, hasFocus, row, column);
            }
        });

        sqlHistoryTableTCM.addColumn(sqlHistoryTableColumn);

        sqlLimitLabel.setLabelFor(sqlLimitTextField);
        org.openide.awt.Mnemonics.setLocalizedText(sqlLimitLabel, org.openide.util.NbBundle.getMessage(SQLHistoryPanel.class, "LBL_SqlLimit")); // NOI18N

        sqlLimitTextField.setText(Integer.toString(SQLHistoryManager.getInstance().getListSize()));
        sqlLimitTextField.setMinimumSize(new java.awt.Dimension(18, 22));

        org.openide.awt.Mnemonics.setLocalizedText(sqlLimitButton, org.openide.util.NbBundle.getMessage(SQLHistoryPanel.class, "LBL_ApplyButton")); // NOI18N

                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 645, Short.MAX_VALUE)
                            .addGroup(layout.createSequentialGroup()
                                .addComponent(jLabel1)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                                .addComponent(connectionUrlComboBox, 0, 301, Short.MAX_VALUE)
                                .addGap(18, 18, 18)
                                .addComponent(jLabel2)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)

                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(insertSQLButton)
                    .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                        .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 268, Short.MAX_VALUE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(sqlLimitTextField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)

        insertSQLButton.getAccessibleContext().setAccessibleDescription(org.openide.util.NbBundle.getMessage(SQLHistoryPanel.class, "ACSD_Insert")); // NOI18N
        sqlLimitTextField.getAccessibleContext().setAccessibleName(org.openide.util.NbBundle.getMessage(SQLHistoryPanel.class, "ACSN_Save")); // NOI18N
        sqlLimitTextField.getAccessibleContext().setAccessibleDescription(org.openide.util.NbBundle.getMessage(SQLHistoryPanel.class, "ACSD_Save")); // NOI18N
        sqlLimitTextField.setInputVerifier(new InputVerifier() {

            public boolean verify(JComponent input) {
                JTextField tf = (JTextField) input;
                return tf.getText().matches("^\\d+$");
            }
        });
        sqlLimitButton.getAccessibleContext().setAccessibleName(org.openide.util.NbBundle.getMessage(SQLHistoryPanel.class, "ACSN_Apply")); // NOI18N
        sqlLimitButton.getAccessibleContext().setAccessibleDescription(org.openide.util.NbBundle.getMessage(SQLHistoryPanel.class, "ACSD_Apply")); // NOI18N
    }// </editor-fold>//GEN-END:initComponents

    verifySQLLimit();
}//GEN-LAST:event_sqlLimitButtonActionPerformed


    private void insertSQL() {
        try {
            JEditorPane pane = (JEditorPane) EditorRegistry.lastFocusedComponent();
            List<SQLHistory> sqlHistoryList = view.getCurrentSQLHistoryList();
            int i = 0;
            String sqlToInsert = ""; // NOI18N
            InsertSQLUtility insertUtility = new InsertSQLUtility();
            for (SQLHistory sqlHistory : sqlHistoryList) {
                if (sqlHistoryTable.isRowSelected(i)) {
                    sqlToInsert = sqlHistory.getSql().trim();
                    JEditorPane pane = (JEditorPane)EditorRegistry.lastFocusedComponent();
                    String mime = pane.getContentType();
                    if (mime.equals(SQLDataLoader.SQL_MIME_TYPE)) {
                        editorPane = pane;
                    }
            int min = sqlTableSelektion.getMinSelectionIndex();
            int max = sqlTableSelektion.getMaxSelectionIndex();
            for (int i = min; i <= max; i++) {
                if (sqlHistoryTable.isRowSelected(i)) {
                    int modelIndex = sqlHistoryTable.convertRowIndexToModel(i);
                    String sql = ((String) htm.getValueAt(modelIndex, 1)).trim();
                    insertIntoDocument(sql, editorPane);
                }
                // increment for the next row
                i++;
            }
            
        } catch (BadLocationException ex) {
            Exceptions.printStackTrace(ex);
        }
    }

    private void verifySQLLimit() {
        String enteredLimitString = sqlLimitTextField.getText();
        String currentLimit = Integer.toString(SQLHistoryManager.getInstance().getListSize());
        String maxLimit = Integer.toString(SQLHistoryManager.MAX_SQL_STATEMENTS_SAVED_FOR_HISTORY);
        if (enteredLimitString.equals(SAVE_STATEMENTS_CLEARED)) {
            sqlLimitTextField.setText(currentLimit);
            return;
    }
        try {
            Integer enteredLimit = Integer.valueOf(enteredLimitString);
            if (enteredLimit > SQLHistoryManager.MAX_SQL_STATEMENTS_SAVED_FOR_HISTORY) {
                inputWarningLabel.setText(NbBundle.getMessage(SQLHistoryPanel.class, "LBL_NumberInputWarningLabel"));
                sqlLimitTextField.setText(maxLimit);
                SQLHistoryManager.getInstance().setListSize(SQLHistoryManager.MAX_SQL_STATEMENTS_SAVED_FOR_HISTORY);
                } else {
                inputWarningLabel.setText(""); // NOI18N
                SQLHistoryManager.getInstance().setListSize(enteredLimit);
                }
        } catch (NumberFormatException ex) {
                inputWarningLabel.setText(NbBundle.getMessage(SQLHistoryPanel.class, "LBL_NumberInputWarningLabel"));
            sqlLimitTextField.setText(currentLimit);
        }
        htm.refresh();
                }

    public String getSQLHistoryTooltipValue(JTable historyTable, int row, int col) {
        HistoryTableModel historyTableModel = (HistoryTableModel) historyTable.getModel();
        int modelColumn = historyTable.convertColumnIndexToModel(col);
        int modelRow = historyTable.convertRowIndexToModel(row);

        if (String.class.isAssignableFrom(historyTableModel.getColumnClass(modelColumn))) {
            String data = (String) historyTableModel.getValueAt(modelRow, modelColumn);
            return "<html>" + data.trim().replace("\n", "<br>") + "</html>";       // NOI18N
        } else if (Date.class.isAssignableFrom(historyTableModel.getColumnClass(modelColumn))) {
            Date data = (Date) historyTableModel.getValueAt(modelRow, modelColumn);
            return DateFormat.getInstance().format(data);
        }
        ((HistoryTableModel) sqlHistoryTable.getModel()).refreshTable(sqlHistoryList);
        NbPreferences.forModule(SQLHistoryPanel.class).put("SQL_STATEMENTS_SAVED_FOR_HISTORY", Integer.toString(iLimit));  // NOI18N               
        sqlLimitTextField.setText(SQLHistoryManager.SAVE_STATEMENTS_MAX_LIMIT_ENTERED);
    }
    
    private void updateSaveLimitUponReset(String enteredLimit) {
        try {
            int iLimit;
            if (enteredLimit.trim().length() < 10) {
                iLimit = Integer.parseInt(enteredLimit);
            } else {
            return null;
                iLimit = SAVE_STATEMENTS_MAX_LIMIT + 1;
            }
            String savedLimit = NbPreferences.forModule(SQLHistoryPanel.class).get("SQL_STATEMENTS_SAVED_FOR_HISTORY", SAVE_STATEMENTS_CLEARED); // NOI18N
            if (iLimit < 0) {
                inputWarningLabel.setText(NbBundle.getMessage(SQLHistoryPanel.class, "LBL_TextInputWarningLabel"));
                if (savedLimit != null) {
                    sqlLimitTextField.setText(savedLimit);
                } else {
                    sqlLimitTextField.setText(SQLHistoryManager.SAVE_STATEMENTS_MAX_LIMIT_ENTERED); 
                }
            } else if (iLimit > SAVE_STATEMENTS_MAX_LIMIT) {
                sqlLimitButton.setEnabled(true);
                inputWarningLabel.setText(NbBundle.getMessage(SQLHistoryPanel.class, "LBL_NumberInputWarningLabel"));
                // reset user's input
                if (savedLimit != null) {
                    sqlLimitTextField.setText(savedLimit);
                } else {
                    sqlLimitTextField.setText(SAVE_STATEMENTS_CLEARED); 
                    sqlLimitTextField.setText(SQLHistoryManager.SAVE_STATEMENTS_MAX_LIMIT_ENTERED); 
                }
            } else {
                inputWarningLabel.setText(""); // NOI18N
                if (SQLHistoryPersistenceManager.getInstance().updateSQLSaved(iLimit, historyRoot).size() > 0) {
                    List<SQLHistory> sqlHistoryList = SQLHistoryPersistenceManager.getInstance().retrieve(historyRoot);
                    view.setCurrentSQLHistoryList(sqlHistoryList);
                    ((HistoryTableModel) sqlHistoryTable.getModel()).refreshTable(sqlHistoryList);
                    view.updateConnectionUrl();
                    NbPreferences.forModule(SQLHistoryPanel.class).put("SQL_STATEMENTS_SAVED_FOR_HISTORY", Integer.toString(iLimit));  // NOI18N
                }
            }

    private int insertIntoDocument(String s, JEditorPane target)
            throws BadLocationException {
        Document doc = target.getDocument();
        if (s == null) {
            s = "";
            // reset user's input
            String savedLimit = NbPreferences.forModule(SQLHistoryPanel.class).get("SQL_STATEMENTS_SAVED_FOR_HISTORY", ""); // NOI18N
            if (savedLimit != null) {
                sqlLimitTextField.setText(savedLimit);
            } else {
                sqlLimitTextField.setText(SQLHistoryManager.SAVE_STATEMENTS_MAX_LIMIT_ENTERED); 
            }
        if (doc == null) {
            return -1;
        }        
        int start = -1;
        try {
            Caret caret = target.getCaret();
            int p0 = Math.min(caret.getDot(), caret.getMark());
            int p1 = Math.max(caret.getDot(), caret.getMark());
            doc.remove(p0, p1 - p0);
            start = caret.getDot();
            doc.insertString(start, s + ";\n", null); // NOI18N
        } catch (BadLocationException ble) {
            LOGGER.log(Level.WARNING, org.openide.util.NbBundle.getMessage(SQLHistoryPanel.class, "LBL_InsertAtLocationError") + ble);
    }
        return start;
    private void handleSQLHistoryException() {
        LOGGER.log(Level.WARNING, NbBundle.getMessage(SQLHistoryPanel.class, "LBL_ErrorParsingSQLHistory"));
        List<SQLHistory> sqlHistoryList = SQLHistoryPersistenceManager.getInstance().retrieve();
        view.setCurrentSQLHistoryList(sqlHistoryList);
        ((HistoryTableModel) sqlHistoryTable.getModel()).refreshTable(sqlHistoryList);
        view.updateConnectionUrl();
    }

    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JComboBox connectionUrlComboBox;
    private javax.swing.JLabel inputWarningLabel;

    private javax.swing.JTextField sqlLimitTextField;
    // End of variables declaration//GEN-END:variables

    private final class HistoryTableModel extends AbstractTableModel {
        private SQLHistoryModel model;
        List<SQLHistory> sqlHistoryList = new ArrayList<SQLHistory>();
        List<SQLHistory> currentSQLHistoryList  = new ArrayList<SQLHistory>();
        public static final String MATCH_EMPTY = ""; // NOI18N

        public SQLHistoryView(SQLHistoryModel model) {
            this.model = model;
            init();
        }
        
        private List<SQLHistoryEntry> sqlList;
        private List<String> jdbcURLs = new ArrayList<String>();
        private SQLHistoryManager shm = SQLHistoryManager.getInstance();
                this.currentSQLHistoryList = model.getSQLHistoryList();
            } catch (SQLHistoryException ex) {
                LOGGER.log(Level.INFO, NbBundle.getMessage(SQLHistoryPanel.class, "LBL_ErrorParsingSQLHistory"), ex); // NOI18N
                sqlHistoryList = SQLHistoryPersistenceManager.getInstance().retrieve();
                setCurrentSQLHistoryList(sqlHistoryList);
            }
        }

        public void setCurrentSQLHistoryList(List<SQLHistory> sqlHistoryList) {
            currentSQLHistoryList = sqlHistoryList;
        }

        public List<SQLHistory> getCurrentSQLHistoryList() {
            return currentSQLHistoryList;
        }

        public List<SQLHistory> getSQLHistoryList() {
            return sqlHistoryList;
        }
        
        public void setSQLHistoryList(List<SQLHistory> sqlHistoryList) {
             this.sqlHistoryList = sqlHistoryList;
        }
        
        /**
         * Get the SQL statement string at the row,col position in the table and convert the string to html
         * @param row - table row
         * @param col - table column
         * @return    - formatted SQL statement for the specified row, col
         */
        public String getSQLHistoryTooltipValue(int row, int col) {
            List<SQLHistory> sqlHistoryListForTooltip =  view.getCurrentSQLHistoryList();
            if (sqlHistoryListForTooltip != null && row < sqlHistoryListForTooltip.size()) {
                if (col == 0) {
                    String sqlRow = sqlHistoryListForTooltip.get(row).getSql().trim();
                    while (sqlRow.indexOf("\n") != -1) {        // NOI18N
                        sqlRow = replace(sqlRow, "\n", "<br>"); // NOI18N
                    }
                    return "<html>" + sqlRow + "</html>";       // NOI18N
                } else {
                    return DateFormat.getInstance().format(sqlHistoryListForTooltip.get(row).getDate());
                }
            } else {
                return null;
            }
        }
        
        /**
         * Convert sql statement to html for proper rendering in the table's tooltip
         * @param target - original string
         * @param from - string to replace
         * @param to - string to replace with
         * @return - updated string
         */
        public String replace(String target, String from, String to) {
            int start = target.indexOf(from);
            if (start == -1) {
                return target;
            }
            int lf = from.length();
            char[] targetChars = target.toCharArray();
            StringBuilder buffer = new StringBuilder();
            int copyFrom = 0;
            while (start != -1) {
                buffer.append(targetChars, copyFrom, start - copyFrom);
                buffer.append(to);
                copyFrom = start + lf;
                start = target.indexOf(from, copyFrom);
            }
            buffer.append(targetChars, copyFrom, targetChars.length - copyFrom);
            return buffer.toString();
        }
             
        public HistoryTableModel() {
            refresh();
            if (sqlHistoryList == null) {
                sqlHistoryList = getSQLHistoryList();
            }

        public void refresh() {
            sqlList = new ArrayList<SQLHistoryEntry>(shm.getSQLHistory());
            for (SQLHistoryEntry sqe : sqlList) {
                String url = sqe.getUrl();
                if (!jdbcURLs.contains(url)) {
                    jdbcURLs.add(url);
                }
            }
            fireTableDataChanged();
        }

        public List<String> getDateList(List<SQLHistory> sqlHistoryList) {
            List<String> dateList = new ArrayList<String>();
            List<String> sqlList = new ArrayList<String>();
            if (sqlHistoryList == null) {
                sqlHistoryList = getSQLHistoryList();
            }

            for (SQLHistory sqlHistory : sqlHistoryList) {
                String date = DateFormat.getInstance().format(sqlHistory.getDate());
                String sql = sqlHistory.getSql();
                // need to make sure that the date is the one that belongs with the SQL
                if (!sqlList.contains(sql)) {                                        
                    sqlList.add(sql); 
                    dateList.add(date);
                }
            }
            return dateList;
        }

        public void updateConnectionUrl() {
            // Initialize combo box data
            currentConnections.clear();
            // Set default item in the combo box
            String defaultSelectedItem = NbBundle.getMessage(SQLHistoryPanel.class, "LBL_URLComboBoxAllConnectionsItem");
            currentConnections.add(defaultSelectedItem);

            for (SQLHistory sqlHistory : sqlHistoryList) {
                String url = sqlHistory.getUrl();
                if (urlAliasMap.containsKey(url)) {
                    // add connection display name
                    currentConnections.add(urlAliasMap.get(url));
                } else {
                    // add URL
                    currentConnections.add(url);
                }
            }
            // Initialize combo box
            connectionUrlComboBox.setModel(new DefaultComboBoxModel(currentConnections.toArray()));
            connectionUrlComboBox.setSelectedItem(defaultSelectedItem);
            connectionUrlComboBox.revalidate();
        }

        private List<SQLHistory> filterSQLHistoryList() {
            List<SQLHistory> filteredSqlHistoryList = new ArrayList<SQLHistory>();
            String match = searchTextField.getText();
            String url = (String)connectionUrlComboBox.getSelectedItem();
            if (aliasUrlMap.containsKey(url)) {
                url = aliasUrlMap.get(url);
            }
            // modify list of SQL to reflect a selection from the Connection dropdown or if a match text entered
            for (SQLHistory sqlHistory : sqlHistoryList) {
                if (sqlHistory.getUrl().equals(url) || url.equals(NbBundle.getMessage(SQLHistoryPanel.class, "LBL_URLComboBoxAllConnectionsItem"))) {
                    if (!match.equals(MATCH_EMPTY)) {
                        if (sqlHistory.getSql().toLowerCase().indexOf(match.toLowerCase()) != -1) {
                            filteredSqlHistoryList.add(sqlHistory);
                        }
                    } else {
                        filteredSqlHistoryList.add(sqlHistory);
                    }
                }
            }
            currentSQLHistoryList = filteredSqlHistoryList;
            return filteredSqlHistoryList;
        }
    }

    private final class HistoryTableModel extends DefaultTableModel implements ActionListener, DocumentListener {
        List<String> sqlList;
        List<String> dateList;
        int sortCol = 1;
        boolean sortAsc = false;

        @Override
        public int getRowCount() {
            return sqlList.size();
                insertSQLButton.setEnabled(false);
            } 
            return data.length;
        }

        @Override
        public int getColumnCount() {
            return 4;
        }

        @Override
        public Class<?> getColumnClass(int c) {
            switch (c) {
                case 0:
                return String.class;
                case 1:
                    return String.class;
                case 2:
                    return Date.class;
                case 3:
                    return SQLHistoryEntry.class;
                default:
                    return Object.class;
            }
        }



        @Override
        public Object getValueAt(int row, int col) {
            switch (col) {
                case 0:
                    return sqlList.get(row).getUrl();
                case 1:
                    return sqlList.get(row).getSql();
                case 2:
                    return sqlList.get(row).getDate();
                case 3:
                    return sqlList.get(row);
                default:
                    return null;
            } 
            return data[row][col];
        }

        @Override
        public void setValueAt(Object value, int row, int col) {
            throw new NotImplementedException();
    }

        public List<String> getJdbcURLs() {
            return jdbcURLs;
        }
        }

    private class EqualsFilter extends RowFilter<HistoryTableModel, Integer> {

        private String referenz;
        private int referenzColumn;

        public EqualsFilter(String referenz, int referenzColumn) {
            this.referenz = referenz;
            this.referenzColumn = referenzColumn;
        }

            @Override
        public boolean include(Entry<? extends HistoryTableModel, ? extends Integer> entry) {
            return ((String) entry.getModel().getValueAt(entry.getIdentifier(), referenzColumn)).equals(referenz);
            }
        };

    private class ContainsInsensitiveFilter extends RowFilter<HistoryTableModel, Integer> {

        private String referenz;
        private int referenzColumn;

        public ContainsInsensitiveFilter(String referenz, int referenzColumn) {
            this.referenz = referenz.toLowerCase();
            this.referenzColumn = referenzColumn;
        }

        @Override
        public boolean include(Entry<? extends HistoryTableModel, ? extends Integer> entry) {
            return ((String) entry.getModel().getValueAt(entry.getIdentifier(), referenzColumn)).toLowerCase().contains(referenz);
        }

        @Override
        public void removeTableModelListener(TableModelListener arg0) {
            // not used
        }

        public void adjustColumnPreferredWidths(JTable table) {
            // Get max width for cells in column and make that the preferred width
            TableColumnModel columnModel = table.getColumnModel();
            for (int col = 0; col < table.getColumnCount(); col++) {

                int maxwidth = 0;
                for (int row = 0; row < table.getRowCount(); row++) {
                    TableCellRenderer rend =
                            table.getCellRenderer(row, col);
                    Object value = table.getValueAt(row, col);
                    Component comp =
                            rend.getTableCellRendererComponent(table,
                            value,
                            false,
                            false,
                            row,
                            col);
                    maxwidth = Math.max(comp.getPreferredSize().width, maxwidth);
                }
                TableColumn column = columnModel.getColumn(col);
                column.setPreferredWidth(maxwidth);
                column.setHeaderRenderer(createDefaultRenderer());
            }
        }

        @Override
        public void actionPerformed(ActionEvent evt) {
            processUpdate();
        }
        
        public void refreshTable(List<SQLHistory> sqlHistoryList) {
            if (initTask == null) {
                return ;
            }
            String url;
            // Get the connection url from the combo box
            if (sqlHistoryList.size() > 0) {
                url = connectionUrlComboBox.getSelectedItem().toString();
                if (aliasUrlMap.containsKey(url)) {
                    url = aliasUrlMap.get(url);
                }
            } else {
                url = NbBundle.getMessage(SQLHistoryPanel.class, "LBL_URLComboBoxAllConnectionsItem");
            }
            sqlList = new ArrayList<String>();
            dateList = new ArrayList<String>();
            connectionUrlComboBox.setToolTipText(url);
            int length;
            int maxLength;
            for (SQLHistory sqlHistory : sqlHistoryList) {
                if (url.equals(NbBundle.getMessage(SQLHistoryPanel.class, "LBL_URLComboBoxAllConnectionsItem")) ||
                      url.equals(sqlHistory.getUrl())) {
                    length = sqlHistory.getSql().trim().length();
                    maxLength = length > TABLE_DATA_WIDTH_SQL ? TABLE_DATA_WIDTH_SQL : length;
                    sqlList.add(sqlHistory.getSql().trim().substring(0, maxLength).replaceAll("\n", " "));
                    dateList.add(DateFormat.getInstance().format(sqlHistory.getDate()));
                }
            }
            // Initialize sql column data
            data = null;
            data = new Object[sqlList.size()][2];
            int row = 0;
            for (String sql : sqlList) {
                length = sql.trim().length();
                maxLength = length > TABLE_DATA_WIDTH_SQL ? TABLE_DATA_WIDTH_SQL : length;
                data[row][0] = sql.trim().substring(0, maxLength).replaceAll("\n", " ");
                row++;
            }
            // Initialize date column data
            row = 0;
            for (String date : dateList) {
                data[row++][1] = date;
            }
            // Refresh table
            if (data.length > 0) {
                sqlHistoryTable.revalidate();
            } else {
                sqlHistoryTable.revalidate();
                insertSQLButton.setEnabled(false);
            }
        }

        @Override
        public void insertUpdate(DocumentEvent evt) {
            processUpdate();
                    } 

        @Override
        public void removeUpdate(DocumentEvent evt) {
            processUpdate();
                    }

        private void processUpdate() {
            view.setCurrentSQLHistoryList(view.filterSQLHistoryList());
            sqlHistoryTable.repaint();
            sqlHistoryTable.clearSelection();
            refreshTable(view.getCurrentSQLHistoryList());
                    }

        @Override
        public void changedUpdate(DocumentEvent arg0) {
            // unused
        }

        public List<SQLHistory> sortData() {
            // Refresh the table
            List<SQLHistory> filteredSQLHistoryList = view.filterSQLHistoryList();
            SQLComparator sqlComparator = new SQLComparator(sortCol, sortAsc);
            Collections.sort(filteredSQLHistoryList, sqlComparator);
            view.setCurrentSQLHistoryList(filteredSQLHistoryList);
            refreshTable( filteredSQLHistoryList);
            return filteredSQLHistoryList;
        }
    }
    };
    public static String read(Document doc) throws InterruptedException, Exception {
        Renderer r = new Renderer(doc);
        doc.render(r);

        synchronized (r) {
            while (!r.done) {
                r.wait();
                if (r.err != null) {
                    throw new Exception(r.err);
                }
            }
        }
        return r.result;
    }

    private static class Renderer implements Runnable {
        Document doc;
        String result;
        Throwable err;
        boolean done;

        Renderer(Document doc) {
            this.doc = doc;
        }

        @Override
        public synchronized void run() {
            try {
                result = doc.getText(0, doc.getLength());
            } catch (Throwable e) {
                err = e;
                Exceptions.printStackTrace(e);
            }
            done = true;
            notify();
        }
    }

    private class InsertSQLUtility {

        public InsertSQLUtility() {
        }

        public void insert(String s, JEditorPane target)
                throws BadLocationException {
            insert(s, target, false);
        }

        public void insert(String s, JEditorPane target, boolean reformat)
                throws BadLocationException {

            if (s == null) {
                s = "";  // NOI18N
            }

            Document doc = target.getDocument();
            if (doc != null) {
                insert(s, target, doc);
            }
        }

        private int insert(String s, JEditorPane target, Document doc)
                throws BadLocationException {

            int start = -1;
            try {
                Caret caret = target.getCaret();
                int p0 = Math.min(caret.getDot(), caret.getMark());
                int p1 = Math.max(caret.getDot(), caret.getMark());
                doc.remove(p0, p1 - p0);
                start = caret.getDot();
                doc.insertString(start, s + ";\n", null); // NOI18N
            } catch (BadLocationException ble) {
                LOGGER.log(Level.WARNING, org.openide.util.NbBundle.getMessage(SQLHistoryPanel.class, "LBL_InsertAtLocationError") + ble);
            }
            return start;
        }
    }

    private static void adjustColumnPreferredWidths(JTable table) {
        TableColumnModel columnModel = table.getColumnModel();
        for (int col = 0; col < table.getColumnCount(); col++) {

            int maxwidth = 0;
            for (int row = 0; row < table.getRowCount(); row++) {
                TableCellRenderer rend =
                        table.getCellRenderer(row, col);
                Object value = table.getValueAt(row, col);
                Component comp =
                        rend.getTableCellRendererComponent(table,
                        value,
                        false,
                        false,
                        row,
                        col);
                maxwidth = Math.max(comp.getPreferredSize().width, maxwidth);
            }
            TableColumn column = columnModel.getColumn(col);
            column.setPreferredWidth(maxwidth);
        }
    }

    private static final class ConnectionUrlRenderer extends DefaultListCellRenderer {

        @Override
        public Component getListCellRendererComponent(JList list, Object value, int index, boolean isSelected, boolean cellHasFocus) {
            JLabel component = (JLabel) super.getListCellRendererComponent(list, value, index, isSelected, cellHasFocus);
            component.setToolTipText((String) value);
            return component;
        }
    }

    private TableCellRenderer createDefaultRenderer() {
        DefaultTableCellRenderer label = new DefaultTableCellRenderer() {

            @Override
            public Component getTableCellRendererComponent(JTable table, Object value, boolean isSelected, boolean hasFocus, int row, int column) {
                if (table != null) {
                    JTableHeader header = table.getTableHeader();
                    if (header != null) {
                        setForeground(header.getForeground());
                        setBackground(header.getBackground());
                        setFont(header.getFont());
                    }
                }
                setText((value == null) ? "" : value.toString());
                setBorder(UIManager.getBorder("TableHeader.cellBorder"));
                return SQLHistoryPanel.this;
            }
        };
        label.setHorizontalAlignment(JLabel.CENTER);
        return label;
    }

    private class ColumnListener extends MouseAdapter {
        @Override
        public void mouseClicked(MouseEvent e) {
            HistoryTableModel model = (HistoryTableModel)sqlHistoryTable.getModel();
            TableColumnModel colModel = sqlHistoryTable.getColumnModel();
            int colModelIndex = colModel.getColumnIndexAtX(e.getX());
            int modelIndex = colModel.getColumn(colModelIndex).getModelIndex();
            if (modelIndex < 0) {
                return;
            }
            if (model.sortCol == modelIndex) {
                model.sortAsc = !model.sortAsc;
            } else {
                model.sortCol = modelIndex;
            }
            model.sortData();
            sqlHistoryTable.tableChanged(new TableModelEvent(model));
            sqlHistoryTable.repaint();
        }
    }

    private class SQLComparator implements Comparator<SQLHistory> {

        protected int sortCol;
        protected boolean sortAsc;

        public SQLComparator(int sortCol, boolean sortAsc) {
            this.sortCol = sortCol;
            this.sortAsc = sortAsc;
        }

        @Override
        public int compare(SQLHistory sql1, SQLHistory sql2) {
            int result = 0;
            if (!(sql1 instanceof SQLHistory) || !(sql2 instanceof SQLHistory)) {
                return result;
            }
            SQLHistory sqlHistory1 = sql1;
            SQLHistory sqlHistory2 = sql2;

            switch (sortCol) {
                case 0: // SQL
                    String s1 = sqlHistory1.getSql().trim().toLowerCase();
                    String s2 = sqlHistory2.getSql().trim().toLowerCase();
                    result = s1.compareTo(s2);
                    break;
                case 1: // Date
                    Date d1 = sqlHistory1.getDate();
                    Date d2 = sqlHistory2.getDate();
                    result = d1.compareTo(d2);
                    break;
            }
            if (!sortAsc) {
                result = -result;
            }
            return result;
        }

        @Override
        public boolean equals(Object obj) {
            if (obj instanceof SQLComparator) {
                SQLComparator compObj = (SQLComparator) obj;
                return (compObj.sortCol == sortCol) && (compObj.sortAsc == sortAsc);
            }
            return false;
        }

        @Override
        public int hashCode() {
            int hash = 3;
            hash = 17 * hash + this.sortCol;
            hash = 17 * hash + (this.sortAsc ? 1 : 0);
            return hash;
        }
    }
}




/*
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 *
 * Copyright 2008-2010 Oracle and/or its affiliates. All rights reserved.
 *
 * Oracle and Java are registered trademarks of Oracle and/or its affiliates.
 * Other names may be trademarks of their respective owners.
 *
 * The contents of this file are subject to the terms of either the GNU
 * General Public License Version 2 only ("GPL") or the Common
 * Development and Distribution License("CDDL") (collectively, the
 * "License"). You may not use this file except in compliance with the
 * License. You can obtain a copy of the License at
 * http://www.netbeans.org/cddl-gplv2.html
 * or nbbuild/licenses/CDDL-GPL-2-CP. See the License for the
 * specific language governing permissions and limitations under the
 * License.  When distributing the software, include this License Header
 * Notice in each file and include the License file at
 * nbbuild/licenses/CDDL-GPL-2-CP.  Oracle designates this
 * particular file as subject to the "Classpath" exception as provided
 * by Oracle in the GPL Version 2 section of the License file that
 * accompanied this code. If applicable, add the following below the
 * License Header, with the fields enclosed by brackets [] replaced by
 * your own identifying information:
 * "Portions Copyrighted [year] [name of copyright owner]"
 * 
 * If you wish your version of this file to be governed by only the CDDL
 * or only the GPL Version 2, indicate your decision by adding
 * "[Contributor] elects to include this software in this distribution
 * under the [CDDL or GPL Version 2] license." If you do not indicate a
 * single choice of license, a recipient has the option to distribute
 * your version of this file under either the CDDL, the GPL Version 2 or
 * to extend the choice of license to its licensees as provided above.
 * However, if you add GPL Version 2 code and therefore, elected the GPL
 * Version 2 license, then the option applies only if the new code is
 * made subject to such option by the copyright holder.
 * 
 * Contributor(s):
 * 
 * Portions Copyrighted 2008-2010 Sun Microsystems, Inc.
 */

package org.netbeans.modules.db.sql.history;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import javax.xml.bind.annotation.XmlElement;
import javax.xml.bind.annotation.XmlRootElement;
import javax.xml.bind.annotation.XmlTransient;

@XmlRootElement(name="history")
public class SQLHistory implements Set<SQLHistoryEntry> {
    @XmlTransient
    private int historyLimit = 100;
    @XmlElement(name="sql")
    private Set<SQLHistoryEntry> history;

    public SQLHistory() {
        history = new HashSet<SQLHistoryEntry>();
    }

    public String toString() {
        return history.toString();
    }

    public <T> T[] toArray(T[] a) {
        return history.toArray(a);
    }

    public Object[] toArray() {
        return history.toArray();
    }

    public int size() {
        return history.size();
    }

    public boolean retainAll(Collection<?> c) {
        return history.retainAll(c);
    }

    public boolean removeAll(Collection<?> c) {
        return history.removeAll(c);
    }

    public boolean remove(Object o) {
        return history.remove(o);
    }

    public Iterator<SQLHistoryEntry> iterator() {
        return history.iterator();
    }
     
    public boolean isEmpty() {
        return history.isEmpty();
        this.sql = sql;
        this.date = date;
    }
    
    public int hashCode() {
        return history.hashCode();
    }
    
    public boolean equals(Object o) {
        return history.equals(o);
    }
    
    public boolean containsAll(Collection<?> c) {
        return history.containsAll(c);
    }

    public boolean contains(Object o) {
        return history.contains(o);
    }

    public void clear() {
        history.clear();
    }
     
    public boolean addAll(Collection<? extends SQLHistoryEntry> c) {
        boolean changed = false;
        for(SQLHistoryEntry sqe: c) {
            changed |= this.add(sqe);
        }
        return changed;
    }
    
    public boolean add(SQLHistoryEntry e) {
        boolean result = history.add(e);
        if(! result) {
            history.remove(e);
            result = history.add(e);
        }
        enforceLimit();
        return result;
    }
    
    public void enforceLimit() {
        if(size() > historyLimit) {
            List<SQLHistoryEntry> list = new ArrayList<SQLHistoryEntry>(history);
            Collections.sort(list, new Comparator<SQLHistoryEntry>() {
                @Override
                public int compare(SQLHistoryEntry o1, SQLHistoryEntry o2) {
                    return o2.getDate().compareTo(o1.getDate());
                }
            });
            history.clear();
            history.addAll(list.subList(0, historyLimit));
        }
    }
    
    @XmlTransient
    public int getHistoryLimit() {
        return historyLimit;
    }

    public void setHistoryLimit(int historyLimit) {
        this.historyLimit = historyLimit;
        enforceLimit();
    }
}




/*
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 *
 * Copyright 2011 Oracle and/or its affiliates. All rights reserved.
 *
 * Oracle and Java are registered trademarks of Oracle and/or its affiliates.
 * Other names may be trademarks of their respective owners.
 *
 * The contents of this file are subject to the terms of either the GNU
 * General Public License Version 2 only ("GPL") or the Common
 * Development and Distribution License("CDDL") (collectively, the
 * "License"). You may not use this file except in compliance with the
 * License. You can obtain a copy of the License at
 * http://www.netbeans.org/cddl-gplv2.html
 * or nbbuild/licenses/CDDL-GPL-2-CP. See the License for the
 * specific language governing permissions and limitations under the
 * License.  When distributing the software, include this License Header
 * Notice in each file and include the License file at
 * nbbuild/licenses/CDDL-GPL-2-CP.  Oracle designates this
 * particular file as subject to the "Classpath" exception as provided
 * by Oracle in the GPL Version 2 section of the License file that
 * accompanied this code. If applicable, add the following below the
 * License Header, with the fields enclosed by brackets [] replaced by
 * your own identifying information:
 * "Portions Copyrighted [year] [name of copyright owner]"
 *
 * If you wish your version of this file to be governed by only the CDDL
 * or only the GPL Version 2, indicate your decision by adding
 * "[Contributor] elects to include this software in this distribution
 * under the [CDDL or GPL Version 2] license." If you do not indicate a
 * single choice of license, a recipient has the option to distribute
 * your version of this file under either the CDDL, the GPL Version 2 or
 * to extend the choice of license to its licensees as provided above.
 * However, if you add GPL Version 2 code and therefore, elected the GPL
 * Version 2 license, then the option applies only if the new code is
 * made subject to such option by the copyright holder.
 *
 * Contributor(s):
 *
 * Portions Copyrighted 2011 Sun Microsystems, Inc.
 */
package org.netbeans.modules.db.sql.history;

import java.text.DateFormat;
import java.text.ParseException;
import java.util.Date;
import javax.xml.bind.annotation.XmlAttribute;
import javax.xml.bind.annotation.XmlTransient;
import javax.xml.bind.annotation.XmlType;
import javax.xml.bind.annotation.XmlValue;

@XmlType(name = "sql")
public class SQLHistoryEntry {

    private String url;
    private String sql;
    private Date date;

    protected SQLHistoryEntry() {
    }

    public SQLHistoryEntry(String url, String sql, Date date) {
        this.url = url;
        this.sql = sql;
        this.date = date;
    }

    @XmlTransient
    public Date getDate() {
        return date;
    }

    protected void setDate(Date date) {
        this.date = date;
    }

    @XmlValue
    public String getSql() {
        return sql;
    }

    protected void setSql(String sql) {
        if (sql != null) {
            this.sql = sql.trim();
        } else {
            this.sql = sql;
        }
    }

    @XmlAttribute
    public String getUrl() {
        return url;
    }

    protected void setUrl(String url) {
        this.url = url;
    }

    @XmlAttribute(name = "date")
    protected String getDateXMLVariant() {
        if (this.date == null) {
            return null;
        } else {
            return Long.toString(date.getTime());
        }
    }

    protected void setDateXMLVariant(String value) {
        try {
            date = new Date(Long.parseLong(value));
        } catch (NumberFormatException nfe) {
            // #152486 - previously date stored in text format
            try {
                date = DateFormat.getDateTimeInstance(DateFormat.SHORT, DateFormat.SHORT).parse(value);
            } catch (ParseException pe) {
                // # 152486; Date stored is not parsable, so reset the date to the current timestamp
                date = new Date();
            }
        }

    }

    @Override
    public String toString() {
        return "SQLHistoryEntry{" + "url=" + url + ", sql=" + sql + ", date=" + date + '}';
    }

    @Override
    public boolean equals(Object obj) {
        if (obj == null) {
            return false;
        }
        if (getClass() != obj.getClass()) {
            return false;
        }
        final SQLHistoryEntry other = (SQLHistoryEntry) obj;
        if ((this.url == null) ? (other.url != null) : !this.url.toLowerCase().equals(other.url.toLowerCase())) {
            return false;
        }
        if ((this.sql == null) ? (other.sql != null) : !this.sql.toLowerCase().equals(other.sql.toLowerCase())) {
            return false;
        }
        return true;
    }

    @Override
    public int hashCode() {
        int hash = 7;
        hash = 37 * hash + (this.sql != null ? this.sql.toLowerCase().hashCode() : 0);
        return hash;
    }
}




/*
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 *
 * Copyright 2010 Oracle and/or its affiliates. All rights reserved.
 *
 * Oracle and Java are registered trademarks of Oracle and/or its affiliates.
 * Other names may be trademarks of their respective owners.
 *
 * The contents of this file are subject to the terms of either the GNU
 * General Public License Version 2 only ("GPL") or the Common
 * Development and Distribution License("CDDL") (collectively, the
 * "License"). You may not use this file except in compliance with the
 * License. You can obtain a copy of the License at
 * http://www.netbeans.org/cddl-gplv2.html
 * or nbbuild/licenses/CDDL-GPL-2-CP. See the License for the
 * specific language governing permissions and limitations under the
 * License.  When distributing the software, include this License Header
 * Notice in each file and include the License file at
 * nbbuild/licenses/CDDL-GPL-2-CP.  Oracle designates this
 * particular file as subject to the "Classpath" exception as provided
 * by Oracle in the GPL Version 2 section of the License file that
 * accompanied this code. If applicable, add the following below the
 * License Header, with the fields enclosed by brackets [] replaced by
 * your own identifying information:
 * "Portions Copyrighted [year] [name of copyright owner]"
 *
 * If you wish your version of this file to be governed by only the CDDL
 * or only the GPL Version 2, indicate your decision by adding
 * "[Contributor] elects to include this software in this distribution
 * under the [CDDL or GPL Version 2] license." If you do not indicate a
 * single choice of license, a recipient has the option to distribute
 * your version of this file under either the CDDL, the GPL Version 2 or
 * to extend the choice of license to its licensees as provided above.
 * However, if you add GPL Version 2 code and therefore, elected the GPL
 * Version 2 license, then the option applies only if the new code is
 * made subject to such option by the copyright holder.
 *
 * Contributor(s):
 *
 * Portions Copyrighted 2008 Sun Microsystems, Inc.
 */

package org.netbeans.modules.db.sql.history;

/**
 *
 * @author jbaker
 */
public class SQLHistoryException extends Exception {
    SQLHistoryException() {
    }

    SQLHistoryException(String msg) {
    }

    SQLHistoryException(String msg, Throwable cause) {
    }
}




 */
package org.netbeans.modules.db.sql.history;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBException;
import javax.xml.bind.Marshaller;
import javax.xml.bind.Unmarshaller;
import org.netbeans.modules.db.sql.execute.ui.SQLHistoryPanel;
import org.openide.filesystems.FileObject;
import org.openide.filesystems.FileUtil;
import org.openide.util.Exceptions;
import org.openide.util.NbPreferences;

/**
 *
 * @author John Baker
 */
public class SQLHistoryManager  {

    JAXBContext context;
    private static final String SQL_HISTORY_DIRECTORY = "Databases/SQLHISTORY"; // NOI18N
    private static final String SQL_HISTORY_FILE = "sql_history.xml"; // NOI18N
    public static final String OPT_SQL_STATEMENTS_SAVED_FOR_HISTORY = "SQL_STATEMENTS_SAVED_FOR_HISTORY"; // NOI18N
    public static final int DEFAULT_SQL_STATEMENTS_SAVED_FOR_HISTORY = 100;
    public static final int MAX_SQL_STATEMENTS_SAVED_FOR_HISTORY = 10000;
    private static SQLHistoryManager _instance = null;    
    private static final Logger LOGGER = Logger.getLogger(SQLHistoryEntry.class.getName());
    private SQLHistory sqlHistory;
    private int listSize;
    private FileObject historyRoot;

    protected SQLHistoryManager() {
        ClassLoader orig = Thread.currentThread().getContextClassLoader();
        Thread.currentThread().setContextClassLoader(SQLHistoryManager.class.getClassLoader());
        try {
            context = JAXBContext.newInstance("org.netbeans.modules.db.sql.history", SQLHistoryManager.class.getClassLoader());
            loadHistory();
        } catch (JAXBException ex) {
            throw new RuntimeException(ex);
        } finally {
            Thread.currentThread().setContextClassLoader(orig);
        }
    }
    
    public static SQLHistoryManager getInstance() {

        return _instance;
    }

    /**
     * Get the value of listSize
     *
     * @return the value of listSize
     */
    public int getListSize() {
        return NbPreferences.forModule(SQLHistoryPanel.class).getInt("OPT_SQL_STATEMENTS_SAVED_FOR_HISTORY", DEFAULT_SQL_STATEMENTS_SAVED_FOR_HISTORY);
    }

    protected FileObject getHistoryRoot(boolean create) throws IOException {
        FileObject result = null;
        FileObject historyRootDir = getConfigRoot().getFileObject(getRelativeHistoryPath());
        if (historyRootDir != null || create) {
            if (historyRootDir == null) {
                historyRootDir = FileUtil.createFolder(getConfigRoot(), getRelativeHistoryPath());
    }
            FileObject historyRoot = historyRootDir.getFileObject(getHistoryFilename());

            if (historyRoot != null || create) {
                if(historyRoot == null) {
                    historyRoot = historyRootDir.createData(getHistoryFilename());
    }
                result = historyRoot;
    }
        }
        return result;
    }
    
    protected FileObject getConfigRoot() {
        return FileUtil.getConfigRoot();
    }
    
    protected String getRelativeHistoryPath() {
        return SQL_HISTORY_DIRECTORY;
    }

    protected String getHistoryFilename() {
        return SQL_HISTORY_FILE;
                }

    public void setListSize(int listSize) {
        NbPreferences.forModule(SQLHistoryPanel.class).putInt("OPT_SQL_STATEMENTS_SAVED_FOR_HISTORY", listSize);
        sqlHistory.setHistoryLimit(listSize);
    }

    public void saveSQL(SQLHistoryEntry sqlStored) {
        sqlHistory.add(sqlStored);
            }
     * @param listSize new value of listSize
     */
    public void setListSize(int listSize) {
        this.listSize = listSize;
    }

    private void loadHistory() {
    public void saveSQL(SQLHistory sqlStored) {
        sqlList.add(sqlStored);
    }
    
    public void save(FileObject userdirRoot) {
        try {
            Unmarshaller unmarshaller = context.createUnmarshaller();
            InputStream is = getHistoryRoot(false).getInputStream();
            sqlHistory = (SQLHistory) unmarshaller.unmarshal(is);
            sqlHistory.setHistoryLimit(getListSize());
            is.close();
            // Start managing the persistence of SQL statements that have been executed
            SQLHistoryPersistenceManager.getInstance().create(historyRoot, sqlList);
            sqlList.clear();
        } catch (Exception ex) {
            sqlHistory = new SQLHistory();
            sqlHistory.setHistoryLimit(getListSize());
            LOGGER.log(Level.INFO, ex.getMessage());
            LOGGER.log(Level.INFO, NbBundle.getMessage(SQLHistoryManager.class, "MSG_ErrorParsingHistoryFile"));
        }
    }
    
    public void save() {
        try {
            Marshaller marshaller = context.createMarshaller();
            OutputStream os = getHistoryRoot(true).getOutputStream();
            marshaller.marshal(sqlHistory, os);
            os.close();
        } catch (Exception ex) {
            LOGGER.log(Level.INFO, ex.getMessage());
            }
        }    

    public SQLHistory getSQLHistory() {
        return sqlHistory;
        String sql;
        if (!getUrlsUsed().isEmpty()) {
            for (SQLHistory historyItem : sqlList) {
                sql = historyItem.getSql();
                if (url.equals(historyItem.getUrl())) {
                    sqlAsString.add(sql);
                }
            }
        }
        return sqlAsString;

    }

    private List<String> getUrlsUsed() {
        List<String> urls = new ArrayList<String>();
        String url;
        for (SQLHistory historyItem : sqlList) {
            url = historyItem.getUrl();
            if (!urls.contains(url)) {
                urls.add(url);
            }
        }
        return urls;
    }
    
    public int updateList(int limit, String historyFilePath, FileObject root) throws SQLHistoryException {
        List<SQLHistory> updatedSQLHistoryList = new ArrayList<SQLHistory>();
        int numItemsToRemove = 0;
        try {
            updatedSQLHistoryList = SQLHistoryPersistenceManager.getInstance().retrieve(root);
            if (limit >= updatedSQLHistoryList.size()) {
                // no changes needed to the current list
                return -1;
            }
            // Remove elements from list based on the number of statements to save that is set in the SQL History dialog
            numItemsToRemove = updatedSQLHistoryList.size() - limit;
            for (int i = 0; i < numItemsToRemove; i++) {
                updatedSQLHistoryList.remove(0);
            }
         } catch (ClassNotFoundException ex) {
            LOGGER.log(Level.INFO, NbBundle.getMessage(SQLHistoryPanel.class, "MSG_RuntimeErrorRetrievingHistory") + ex);
        }    
        sqlList = updatedSQLHistoryList;
        return numItemsToRemove;
    }
}




/*
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 *
 * Copyright 2010 Oracle and/or its affiliates. All rights reserved.
 *
 * Oracle and Java are registered trademarks of Oracle and/or its affiliates.
 * Other names may be trademarks of their respective owners.
 *
 * The contents of this file are subject to the terms of either the GNU
 * General Public License Version 2 only ("GPL") or the Common
 * Development and Distribution License("CDDL") (collectively, the
 * "License"). You may not use this file except in compliance with the
 * License. You can obtain a copy of the License at
 * http://www.netbeans.org/cddl-gplv2.html
 * or nbbuild/licenses/CDDL-GPL-2-CP. See the License for the
 * specific language governing permissions and limitations under the
 * License.  When distributing the software, include this License Header
 * Notice in each file and include the License file at
 * nbbuild/licenses/CDDL-GPL-2-CP.  Oracle designates this
 * particular file as subject to the "Classpath" exception as provided
 * by Oracle in the GPL Version 2 section of the License file that
 * accompanied this code. If applicable, add the following below the
 * License Header, with the fields enclosed by brackets [] replaced by
 * your own identifying information:
 * "Portions Copyrighted [year] [name of copyright owner]"
 * 
 * If you wish your version of this file to be governed by only the CDDL
 * or only the GPL Version 2, indicate your decision by adding
 * "[Contributor] elects to include this software in this distribution
 * under the [CDDL or GPL Version 2] license." If you do not indicate a
 * single choice of license, a recipient has the option to distribute
 * your version of this file under either the CDDL, the GPL Version 2 or
 * to extend the choice of license to its licensees as provided above.
 * However, if you add GPL Version 2 code and therefore, elected the GPL
 * Version 2 license, then the option applies only if the new code is
 * made subject to such option by the copyright holder.
 * 
 * Contributor(s):
 * 
 * Portions Copyrighted 2008 Sun Microsystems, Inc.
 */

package org.netbeans.modules.db.sql.history;

import java.util.List;

/**
 *
 * @author John Baker
 */
public interface SQLHistoryModel {
    void initialize();
    
    void setFilter(String filter);
        
    String getFilter();
    
    List<SQLHistory> getSQLHistoryList() throws SQLHistoryException;
    
    void setSQLHistoryList(List<SQLHistory> sqlHistoryList);
    
}




/*
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 *
 * Copyright 2010 Oracle and/or its affiliates. All rights reserved.
 *
 * Oracle and Java are registered trademarks of Oracle and/or its affiliates.
 * Other names may be trademarks of their respective owners.
 *
 * The contents of this file are subject to the terms of either the GNU
 * General Public License Version 2 only ("GPL") or the Common
 * Development and Distribution License("CDDL") (collectively, the
 * "License"). You may not use this file except in compliance with the
 * License. You can obtain a copy of the License at
 * http://www.netbeans.org/cddl-gplv2.html
 * or nbbuild/licenses/CDDL-GPL-2-CP. See the License for the
 * specific language governing permissions and limitations under the
 * License.  When distributing the software, include this License Header
 * Notice in each file and include the License file at
 * nbbuild/licenses/CDDL-GPL-2-CP.  Oracle designates this
 * particular file as subject to the "Classpath" exception as provided
 * by Oracle in the GPL Version 2 section of the License file that
 * accompanied this code. If applicable, add the following below the
 * License Header, with the fields enclosed by brackets [] replaced by
 * your own identifying information:
 * "Portions Copyrighted [year] [name of copyright owner]"
 * 
 * If you wish your version of this file to be governed by only the CDDL
 * or only the GPL Version 2, indicate your decision by adding
 * "[Contributor] elects to include this software in this distribution
 * under the [CDDL or GPL Version 2] license." If you do not indicate a
 * single choice of license, a recipient has the option to distribute
 * your version of this file under either the CDDL, the GPL Version 2 or
 * to extend the choice of license to its licensees as provided above.
 * However, if you add GPL Version 2 code and therefore, elected the GPL
 * Version 2 license, then the option applies only if the new code is
 * made subject to such option by the copyright holder.
 * 
 * Contributor(s):
 * 
 * Portions Copyrighted 2010 Sun Microsystems, Inc.
 */

package org.netbeans.modules.db.sql.history;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.logging.Logger;
import org.openide.filesystems.FileObject;
import org.openide.filesystems.FileUtil;
import org.openide.util.NbPreferences;

/**
 *
 * @author John Baker
 */
public class SQLHistoryModelImpl implements SQLHistoryModel {    
    private static final int SAVE_STATEMENTS_EMPTY = 0; // NOI18N
    private static final String SAVE_STATEMENTS_CLEARED = ""; // NOI18N  
    private static final Logger LOGGER = Logger.getLogger(SQLHistoryModelImpl.class.getName());
    private static final FileObject USERDIR = FileUtil.getConfigRoot();

    List<SQLHistory> _sqlHistoryList = new ArrayList<SQLHistory>();
    
    @Override
    public void initialize() {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void setFilter(String filter) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public String getFilter() {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public List<SQLHistory> getSQLHistoryList() throws SQLHistoryException {
        List<SQLHistory> retrievedSQL = new ArrayList<SQLHistory>();
        _sqlHistoryList.clear();
        try {
            boolean isRewriteSQLRequired = false;
            FileObject historyRoot = USERDIR.getFileObject(SQLHistoryManager.SQL_HISTORY_FOLDER);
            if (historyRoot == null) {
                return new ArrayList<SQLHistory>();
            }
            // Read persisted SQL from  file            
            retrievedSQL = SQLHistoryPersistenceManager.getInstance().retrieve(historyRoot);
            // Remove duplicates
            if (!isSQLUnique(retrievedSQL)) {
                retrievedSQL = removeDuplicates(retrievedSQL);
                isRewriteSQLRequired = true;
            }
            // Get saved limit
            String savedLimit = NbPreferences.forModule(SQLHistoryPersistenceManager.class).get("SQL_STATEMENTS_SAVED_FOR_HISTORY", "");
            if (savedLimit.equals(SAVE_STATEMENTS_CLEARED)) {
                savedLimit = SQLHistoryManager.SAVE_STATEMENTS_MAX_LIMIT_ENTERED;
            }
            int limit = Integer.parseInt(savedLimit);
            // Remove any elements if save limit is exceeded
            if (retrievedSQL.size() > limit) {
                retrievedSQL = removeExtraSQL(retrievedSQL, limit);
                isRewriteSQLRequired = true;
            }
            if (isRewriteSQLRequired) {
                // Remove all elements from sql_history.xml
                SQLHistoryPersistenceManager.getInstance().updateSQLSaved(SAVE_STATEMENTS_EMPTY, historyRoot);
                // Write new list;  reversing list is required for persisting the SQL
                Collections.reverse(retrievedSQL);
                SQLHistoryPersistenceManager.getInstance().create(historyRoot, retrievedSQL);
                // return list to the expected order for viewing
                Collections.reverse(retrievedSQL);
            }
        } catch (ClassNotFoundException ex) {
            throw new SQLHistoryException();
        }
        return retrievedSQL;
    }
    
    private boolean isSQLUnique(List<SQLHistory> sqlHistoryList) {
        List<SQLHistory> revSqLHistoryList = new ArrayList<SQLHistory>();
        boolean isUnique = true;

        for (SQLHistory sqlHistory : sqlHistoryList) {
            for (SQLHistory revHistory : revSqLHistoryList) {
                if (revHistory.getSql().trim().equals(sqlHistory.getSql().trim())) {
                    if (revHistory.getUrl().equals(sqlHistory.getUrl())) {
                        isUnique = false;
                    }
                }
            }
            revSqLHistoryList.add(sqlHistory);
        }
        return isUnique;
    }
    
    private List<SQLHistory> removeDuplicates(List<SQLHistory> sqlHistoryList) {
         List<SQLHistory> revSqLHistoryList = new ArrayList<SQLHistory>();
         boolean canAdd = true;
            for (SQLHistory sqlHistory : sqlHistoryList) {
                for (SQLHistory revHistory : revSqLHistoryList) {
                    if (revHistory.getSql().trim().equals(sqlHistory.getSql().trim())) {
                        if (revHistory.getUrl().equals(sqlHistory.getUrl())) {
                            canAdd = false;
                        }
                    }
                }
                if (canAdd) {
                    revSqLHistoryList.add(sqlHistory);
                } else {
                    canAdd = true;
                }
            }
            return revSqLHistoryList;        
    }
    
    private List<SQLHistory> removeExtraSQL(List<SQLHistory> sqlHistoryList, int limit) {
        int i = 0;
        List<SQLHistory> revSqLHistoryList = new ArrayList<SQLHistory>();

        for (SQLHistory sqlHistory : sqlHistoryList) {
            if (i < limit) {
                revSqLHistoryList.add(sqlHistory);
            }
            i++;
        }    
        return revSqLHistoryList;
    }

    @Override
    public void setSQLHistoryList(List<SQLHistory> sqlHistoryList) {
        _sqlHistoryList = sqlHistoryList;
    }
}




/*
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 *
 * Copyright 1997-2010 Oracle and/or its affiliates. All rights reserved.
 *
 * Oracle and Java are registered trademarks of Oracle and/or its affiliates.
 * Other names may be trademarks of their respective owners.
 *
 * The contents of this file are subject to the terms of either the GNU
 * General Public License Version 2 only ("GPL") or the Common
 * Development and Distribution License("CDDL") (collectively, the
 * "License"). You may not use this file except in compliance with the
 * License. You can obtain a copy of the License at
 * http://www.netbeans.org/cddl-gplv2.html
 * or nbbuild/licenses/CDDL-GPL-2-CP. See the License for the
 * specific language governing permissions and limitations under the
 * License.  When distributing the software, include this License Header
 * Notice in each file and include the License file at
 * nbbuild/licenses/CDDL-GPL-2-CP.  Oracle designates this
 * particular file as subject to the "Classpath" exception as provided
 * by Oracle in the GPL Version 2 section of the License file that
 * accompanied this code. If applicable, add the following below the
 * License Header, with the fields enclosed by brackets [] replaced by
 * your own identifying information:
 * "Portions Copyrighted [year] [name of copyright owner]"
 *
 * Contributor(s):
 *
 * The Original Software is NetBeans. The Initial Developer of the Original
 * Software is Sun Microsystems, Inc. Portions Copyright 1997-2010 Sun
 * Microsystems, Inc. All Rights Reserved.
 *
 * If you wish your version of this file to be governed by only the CDDL
 * or only the GPL Version 2, indicate your decision by adding
 * "[Contributor] elects to include this software in this distribution
 * under the [CDDL or GPL Version 2] license." If you do not indicate a
 * single choice of license, a recipient has the option to distribute
 * your version of this file under either the CDDL, the GPL Version 2 or
 * to extend the choice of license to its licensees as provided above.
 * However, if you add GPL Version 2 code and therefore, elected the GPL
 * Version 2 license, then the option applies only if the new code is
 * made subject to such option by the copyright holder.
 */
package org.netbeans.modules.db.sql.history;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.text.DateFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import org.openide.util.Exceptions;
import org.xml.sax.Attributes;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import org.openide.filesystems.FileLock;
import org.openide.filesystems.FileObject;
import org.openide.filesystems.FileSystem;
import org.openide.filesystems.FileUtil;
import org.openide.loaders.DataFolder;
import org.openide.loaders.XMLDataObject;
import org.openide.util.NbPreferences;
import org.openide.xml.EntityCatalog;
import org.openide.xml.XMLUtil;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NamedNodeMap;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.w3c.dom.Text;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;

/**
 * Manage creation and updates to persisted SQL statements
 *
 * @author John Baker
 */
public class SQLHistoryPersistenceManager {
    private static final int READ = 0;
    private static final int ADD = 1;
    private static final int REMOVE = 2;
    private static final int CREATE = 3;
    public static final Logger LOGGER = Logger.getLogger(SQLHistoryPersistenceManager.class.getName());
    private static SQLHistoryPersistenceManager _instance = null;
    private static Document document;
    private List<SQLHistory> sqlHistoryList;
    private int numElemsToRemove = 0;

    private SQLHistoryPersistenceManager() {
    }

    public static SQLHistoryPersistenceManager getInstance() {
        if (null == _instance) {
            _instance = new SQLHistoryPersistenceManager();
        }
        return _instance;
    }

    public void removeHistoryFile(FileObject historyRoot) {
        try {
            FileObject folder = DataFolder.findFolder(historyRoot).getPrimaryFile();
            String fn = FileUtil.getFileDisplayName(folder) + File.separator + SQLHistoryManager.SQL_HISTORY_FILE_NAME;
            FileObject historyFo = FileUtil.toFileObject(FileUtil.normalizeFile(new File(fn)));
            historyFo.delete();
        } catch (IOException ex) {
            Exceptions.printStackTrace(ex);
        }

    }

    public void create(FileObject historyFileObject, List<SQLHistory> sqlHistoryList) throws SQLHistoryException {
        try {
            this.sqlHistoryList = sqlHistoryList;
            DataFolder df = DataFolder.findFolder(historyFileObject);
            int action = df.getChildren().length == 0 ? CREATE : ADD;
            AtomicFileAction writer = new AtomicFileAction(df, null, action, sqlHistoryList);
            df.getPrimaryFile().getFileSystem().runAtomicAction(writer);
        } catch (IOException ex) {
            throw new SQLHistoryException(ex.getLocalizedMessage(), ex);
        }
    }

    public List<SQLHistory> retrieve(FileObject historyFileObject) throws ClassNotFoundException, SQLHistoryException {
        Handler handler = null;
        int limit;
        String limitUser = NbPreferences.forModule(SQLHistoryPersistenceManager.class).get("SQL_STATEMENTS_SAVED_FOR_HISTORY", "");
        if (!limitUser.isEmpty()) { // NOI18N
            try {
                limit = Integer.parseInt(limitUser);
            } catch (NumberFormatException nfe) {
                limit = Integer.parseInt(SQLHistoryManager.SAVE_STATEMENTS_MAX_LIMIT_ENTERED);
            }
        } else {
            limit = Integer.parseInt(SQLHistoryManager.SAVE_STATEMENTS_MAX_LIMIT_ENTERED);
        }
        try {
            handler = new Handler(limit);
            DataFolder df = DataFolder.findFolder(historyFileObject);
            AtomicFileAction reader = new AtomicFileAction(df, handler, READ, null);
            df.getPrimaryFile().getFileSystem().runAtomicAction(reader);

        } catch (IOException ex) {
            LOGGER.log(Level.FINE, ex.getLocalizedMessage(), ex);
            sqlHistoryList = handler.getXmlSqlHistoryList();
            throw new SQLHistoryException();
        }
        if (handler != null) {
            return handler.getXmlSqlHistoryList();
        } else {
            return new ArrayList<SQLHistory>();
        }
    }

    public List<SQLHistory> retrieve() {
        return sqlHistoryList;
    }

    public void setNumElemsToRemove(int elemsToRemove) {
        numElemsToRemove = elemsToRemove;
    }

    public int getNumElemsToRemove() {
        return numElemsToRemove;
    }

    public List<SQLHistory> updateSQLSaved(int limit, FileObject historyFileObject) throws SQLHistoryException {
        List<SQLHistory> updatedSQLHistoryList = null;
        try {
            if (historyFileObject == null) {
                return new ArrayList<SQLHistory>();
            }
            updatedSQLHistoryList = retrieve(historyFileObject);
            // Remove elements from list based on the number of statements to save that is set in the SQL History dialog
            if (limit < updatedSQLHistoryList.size()) {
                numElemsToRemove = updatedSQLHistoryList.size() - limit;
                boolean containsElems = true;
                containsElems = !updatedSQLHistoryList.isEmpty();
                if (containsElems && numElemsToRemove == updatedSQLHistoryList.size()) {
                    // remove all
                    DataFolder df = DataFolder.findFolder(historyFileObject);
                    List<SQLHistory> emptyList = Collections.emptyList();
                    AtomicFileAction writer = new AtomicFileAction(df, null, CREATE, emptyList);
                    df.getPrimaryFile().getFileSystem().runAtomicAction(writer);
                } else if (containsElems && (limit == 0 || numElemsToRemove >= 0)) {
                    DataFolder df = DataFolder.findFolder(historyFileObject);
                    AtomicFileAction modifier = new AtomicFileAction(df, null, REMOVE, updatedSQLHistoryList);
                    df.getPrimaryFile().getFileSystem().runAtomicAction(modifier);
                }
            }
            updatedSQLHistoryList = retrieve(historyFileObject);
        } catch (ClassNotFoundException ex) {
            throw new SQLHistoryException(ex.getLocalizedMessage(), ex);
        } catch (IOException ex) {
            throw new SQLHistoryException(ex.getLocalizedMessage(), ex);
        }
        return updatedSQLHistoryList;
    }

    private static final class AtomicFileAction implements FileSystem.AtomicAction {
        List<SQLHistory> sqlHistoryList;
        DataFolder parent;
        boolean remove;
        FileObject data;
        Handler handler;
        int actionType;

        AtomicFileAction(DataFolder parent, Handler handler, int actionType, List<SQLHistory> sqlHistoryList) {
            this.parent = parent;
            this.handler = handler;
            this.sqlHistoryList = sqlHistoryList;
            this.actionType = actionType;
        }

        @Override
        public void run() throws IOException {
            FileLock lck = null;
            OutputStream ostm = null;
            PrintWriter writer = null;
            XmlWriter xmlWriter = null;
            DocumentBuilderFactory factory = null;
            DocumentBuilder builder = null;
            try {
                FileObject folder = parent.getPrimaryFile();
                String fn = FileUtil.getFileDisplayName(folder) + File.separator + SQLHistoryManager.SQL_HISTORY_FILE_NAME;
                // Read, Write or Update the persisted SQL file
                switch (actionType) {
                    case READ:
                        FileObject historyFo = FileUtil.toFileObject(FileUtil.normalizeFile(new File(fn)));
                        XMLDataObject obj = (XMLDataObject) XMLDataObject.find(historyFo);
                        InputSource inputSource = new InputSource(obj.getPrimaryFile().getInputStream());
                        inputSource.setSystemId(historyFo.getURL().toExternalForm());
                        XMLReader reader = XMLUtil.createXMLReader();
                        reader.setContentHandler(handler);
                        reader.setErrorHandler(handler);
                        reader.setEntityResolver(EntityCatalog.getDefault());
                        reader.parse(inputSource);
                        break;
                    case ADD:
                        factory = DocumentBuilderFactory.newInstance();
                        builder = factory.newDocumentBuilder();
                        data = FileUtil.toFileObject(FileUtil.normalizeFile(new File(fn)));
                        InputStream inputStream = data.getInputStream();
                        document = builder.parse(inputStream);
                        inputStream.close();
                        lck = data.lock();
                        ostm = data.getOutputStream(lck);
                        writer = new PrintWriter(new OutputStreamWriter(ostm, "UTF8")); //NOI18N
                        // Create or update then write the DOM
                        xmlWriter = new XmlWriter(data, sqlHistoryList, writer);
                        xmlWriter.write(xmlWriter.createElements(document), ""); // NOI18N
                        break;
                    case CREATE:
                        factory = DocumentBuilderFactory.newInstance();
                        builder = factory.newDocumentBuilder();
                        if (new File(fn).exists()) {
                            data = folder.getFileObject(SQLHistoryManager.SQL_HISTORY_FILE_NAME);
                        } else {
                            data = folder.createData(SQLHistoryManager.SQL_HISTORY_FILE_NAME);
                        }
                        lck = data.lock();
                        ostm = data.getOutputStream(lck);
                        writer = new PrintWriter(new OutputStreamWriter(ostm, "UTF8")); //NOI18N
                        document = builder.newDocument();
                        xmlWriter = new XmlWriter(data, sqlHistoryList, writer);
                        xmlWriter.write();
                        // Create or update then write the DOM
                        xmlWriter = new XmlWriter(data, sqlHistoryList, writer);
                        xmlWriter.write(xmlWriter.createElements(document), ""); // NOI18N
                        break;
                    case REMOVE:
                        factory = DocumentBuilderFactory.newInstance();
                        builder = factory.newDocumentBuilder();
                        if (folder.getChildren().length > 0) {
                            data = FileUtil.toFileObject(FileUtil.normalizeFile(new File(fn)));
                            InputStream is = data.getInputStream();
                            document = builder.parse(is);
                            is.close();
                            lck = data.lock();
                            ostm = data.getOutputStream(lck);
                            writer = new PrintWriter(new OutputStreamWriter(ostm, "UTF8")); //NOI18N
                        }
                        // Create or update then write the DOM
                        xmlWriter = new XmlWriter(data, sqlHistoryList, writer);
                        xmlWriter.write(); // NOI18N
                        xmlWriter.write(xmlWriter.removeElements(document), ""); // NOI18N
                        break;
                }
            } catch (ParserConfigurationException ex) {
                LOGGER.log(Level.INFO, ex.getMessage());
                throw new IOException(ex.getLocalizedMessage(), ex);

            } catch (SAXException ex) {
                LOGGER.log(Level.INFO, ex.getMessage());
                throw new IOException(ex.getLocalizedMessage(),ex);
            } finally {
                if (writer != null) {
                    writer.flush();
                    writer.close();
                    writer = null;
                }
                if (ostm != null) {
                    ostm.close();
                    ostm = null;
                }
                if (lck != null) {
                    lck.releaseLock();
                }
            }
        }
    }

    private static final class XmlWriter {
        private PrintWriter pw;
        private List<SQLHistory> sqlHistoryList;

        public XmlWriter(FileObject data, List<SQLHistory> sqlHistoryList, PrintWriter pw) {
            this.sqlHistoryList = sqlHistoryList;
            this.pw = pw;
        }

        /**
         * 
         * create XML elements from SQL statements
         */
        private Node createElements(Document document) {
            Element newNode = null;
            Element nameNode = null;
            if (null == document.getDocumentElement()) {
                newNode = document.createElement("history");  // NOI18N
                for (SQLHistory sqlHistory : sqlHistoryList) {
                    nameNode = document.createElement("sql");  // NOI18N
                    nameNode.appendChild(document.createTextNode(sqlHistory.getSql()));
                    nameNode.setAttribute("url", sqlHistory.getUrl());  // NOI18N
                    nameNode.setAttribute("date", new Long(sqlHistory.getDate().getTime()).toString());  // NOI18N
                    newNode.appendChild(nameNode);
                }
                document.adoptNode(newNode);
            } else {
                newNode = document.getDocumentElement();
                for (SQLHistory sqlHistory : sqlHistoryList) {
                    nameNode = document.createElement("sql");  // NOI18N
                    nameNode.appendChild(document.createTextNode(sqlHistory.getSql()));
                    nameNode.setAttribute("url", sqlHistory.getUrl());  // NOI18N
                    nameNode.setAttribute("date", new Long(sqlHistory.getDate().getTime()).toString());  // NOI18N
                    newNode.insertBefore(nameNode, newNode.getFirstChild());
                }
            }
            return newNode;
        }

        /**
         * 
         * remove XML elements when the number of statements to save is reduced in the SQL History dialog   
         */
        private Node removeElements(Document document) {
            NodeList nodes = null;
            Element history = document.getDocumentElement();
            if (null != history) {
                nodes = history.getElementsByTagName("sql");
                int elemsToRemove = SQLHistoryPersistenceManager.getInstance().getNumElemsToRemove();
                // Statements to save was set to 0                   
                if (elemsToRemove == 0) {
                    for (int i = 0; i < nodes.getLength(); i++) {
                        if (nodes.item(0) != null) {
                            history.removeChild(nodes.item(0));
                        }
                    }
                }
                // Remove elements from the DOM
                for (int i = 0; i < elemsToRemove; i++) {
                    if (nodes.item(0) != null) {
                        history.removeChild(nodes.item(nodes.getLength() - 1));
                    }
                }
            }
            return history;
        }

        private void write() {
            pw.println("<?xml version='1.0' encoding='UTF-8' ?>");
        }

        /**
         * 
         * write the SQL statements as xml
         */
        private void write(Node node, String indent) {
            switch (node.getNodeType()) {
                case Node.DOCUMENT_NODE: {
                    Document doc = (Document) node;
                    pw.println(indent + "<?xml version='1.0'?>");  // NOI18N
                    Node child = doc.getFirstChild();
                    while (child != null) {
                        write(child, indent);
                        child = child.getNextSibling();
                    }
                    break;
                }
                case Node.ELEMENT_NODE: {
                    Element elt = (Element) node;
                    pw.print(indent + "<" + elt.getTagName());
                    NamedNodeMap attrs = elt.getAttributes();
                    for (int i = 0; i < attrs.getLength(); i++) {
                        Node a = attrs.item(i);
                        pw.print(" " + a.getNodeName() + "='" + fixup(a.getNodeValue()) + "'");  // NOI18N
                    }
                    pw.println(">");  // NOI18N
                    String newindent = indent + "    ";  // NOI18N
                    Node child = elt.getFirstChild();
                    while (child != null) {
                        write(child, newindent);
                        child = child.getNextSibling();
                    }

                    pw.println(indent + "</" + elt.getTagName() + ">");  // NOI18N
                    break;
                }
                case Node.TEXT_NODE: {
                    Text textNode = (Text) node;
                    String text = textNode.getData().trim();
                    if ((text != null) && text.length() > 0) {
                        pw.println(indent + fixup(text));
                    }
                    break;
                }
                default:
                    LOGGER.log(Level.INFO, "Ignoring node: " + node.getClass().getName());  // NOI18N
                    break;
            }

        }

        private String fixup(String s) {
            StringBuilder sb = new StringBuilder();
            int len = s.length();
            for (int i = 0; i < len; i++) {
                char c = s.charAt(i);
                switch (c) {
                    default:
                        sb.append(c);
                        break;
                    case '<':               // NOI18N
                        sb.append("&lt;");  // NOI18N
                        break;
                    case '>':               // NOI18N
                        sb.append("&gt;");  // NOI18N
                        break;
                    case '&':                // NOI18N
                        sb.append("&amp;");  // NOI18N
                        break;
                    case '"':                 // NOI18N
                        sb.append("&quot;");  // NOI18N
                        break;
                    case '\'':               // NOI18N
                        sb.append("&apos;"); // NOI18N
                        break;
                }
            }
            return sb.toString();
        }
    }

    /**
     * SAX handler for reading the XML file.
     */
    private static final class Handler extends DefaultHandler implements ContentHandler {

        private static final String ELEMENT_SQL = "sql"; // NOI18N
        private static final String ATTR_URL_PROPERTY_VALUE = "url"; // NOI18N
        private static final String ATTR_DATE_PROPERTY_VALUE = "date"; // NOI18N
        private static String url;
        private static StringBuilder sql;
        private static Date date;
        boolean matchingUrl = false;
        private List<SQLHistory> xmlSqlHistoryList = new ArrayList<SQLHistory>();
        static boolean isSql = false;
        private int limit;
        private static Calendar calendar = Calendar.getInstance();
        
        public Handler(int limit) {
            this.limit = limit;
        }

        @Override
        public void startElement(String uri, String localName, String qName, Attributes attrs) throws SAXException {
            if (ELEMENT_SQL.equals(qName)) {
                isSql = true;
                url = attrs.getValue(ATTR_URL_PROPERTY_VALUE);
                try {
                    calendar.setTimeInMillis(Long.parseLong(attrs.getValue(ATTR_DATE_PROPERTY_VALUE)));
                    date = calendar.getTime();
                } catch (NumberFormatException nfe) {
                    // #152486 - previously date stored in text format
                    try {
                        date = DateFormat.getDateTimeInstance(DateFormat.SHORT, DateFormat.SHORT).parse(attrs.getValue(ATTR_DATE_PROPERTY_VALUE));
                    } catch (ParseException pe) {
                        // # 152486; Date stored is not parsable, so reset the date to the current timestamp
                        date = calendar.getTime();
                    }
                }
            } else {
                isSql = false;
            }
        }

        @Override
        public void endElement(String uri, String localName, String qName) {
            if (ELEMENT_SQL.equals(qName)) {
                if (url != null && sql != null && date != null) {
                    addHistory(url, sql.toString(), date);
                    reset();
                }
            }
        }

        private static void reset() {
            // reset data
            url = null;
            date = null;
            sql = null;
        }

        private void addHistory(String url, String sql, Date date) {
            if ((xmlSqlHistoryList.size() <= limit) || limit == 0) {
                xmlSqlHistoryList.add(new SQLHistory(url, sql, date));
                setXmlSqlHistoryList(xmlSqlHistoryList);
            } else {
                // remove a statement from the end of the list
                xmlSqlHistoryList.remove(xmlSqlHistoryList.size() - 1);
            }
        }

        @Override
        public void characters(char buf[], int offset, int length) {
            if (isSql) {
                String parsedValue = new String(buf, offset, length);
                if (sql == null) {
                    sql = new StringBuilder();
                    sql.append(parsedValue);
                } else {
                    sql.append(parsedValue);
                }
            }
        }

        public void setXmlSqlHistoryList(List<SQLHistory> sqlHistoryList) {
            xmlSqlHistoryList = sqlHistoryList;
        }

        public List<SQLHistory> getXmlSqlHistoryList() {
            if (xmlSqlHistoryList == null) {
                return new ArrayList<SQLHistory>();
            } else {
                return xmlSqlHistoryList;
            }
        }
    }
}

Line 0 Link Here

(-)a/db.core/src/org/netbeans/modules/db/sql/history/jaxb.index (+2 lines)
	1	SQLHistory
	2	SQLHistoryEntry




 */
package org.netbeans.modules.db.sql.history;

import java.io.File;
import java.io.IOException;
import java.text.DateFormat;
import java.text.ParseException;
import java.util.Date;
import org.netbeans.junit.NbTestCase;
import org.netbeans.junit.NbTestSuite;
import org.openide.filesystems.FileObject;

 * @author John Baker
 */
public class SQLHistoryManagerTest extends NbTestCase {

    public static final String SQL_HISTORY_FOLDER = "Databases/SQLHISTORY"; // NOI18N
    public static final String SQL_HISTORY_FILE_NAME = "sql_history.xml";  // NOI18N

    /** Default constructor.
     * @param testName name of particular test case
     */

    }
    
    public void testUpdateListRemoveEqualNumber() {
        SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// derby", "select * from TRAVEL.TRIP", new Date()));
        SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// postgres", "select * from TRAVEL.TRIP", new Date()));
        SQLHistoryManager.getInstance().setListSize(2);
        assertEquals(2, SQLHistoryManager.getInstance().getSQLHistory().size());
            SQLHistoryManager.getInstance().saveSQL(new SQLHistory("jdbc:// postgres", "select * from TRAVEL.TRIP", DateFormat.getInstance().parse("07/10/96 4:5 PM, PDT")));
            // Save SQL
            SQLHistoryManager.getInstance().save(root);
            String historyFileRootPath = FileUtil.getFileDisplayName(root) + File.separator + "Databases" + File.separator + "SQLHISTORY";
            FileObject historyFileObject = FileUtil.toFileObject(new File(historyFileRootPath));
            String historyFilePath = historyFileRootPath + File.separator + SQL_HISTORY_FILE_NAME;
            // Limit to 2 SQL statements
            SQLHistoryManager.getInstance().updateList(2, historyFilePath , historyFileObject);
            assertEquals(0, SQLHistoryManager.getInstance().getSQLHistory().size());
        } catch (SQLHistoryException ex) {
            Exceptions.printStackTrace(ex);
        } catch (IOException ex) {
            Exceptions.printStackTrace(ex);
        } catch (ParseException ex) {
            Exceptions.printStackTrace(ex);
        }
    }
    
    public void testUpdateListRemoveLessNumber3() {
        SQLHistoryManager.getInstance().setListSize(100);
        SQLHistoryManager.getInstance().getSQLHistory().clear();
            // Create a list of SQL statements
        SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// derby", "select * from TRAVEL.TRIP", new Date()));
        SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// postgres", "select * from TRAVEL.TRIPTYPE", new Date()));
        SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// postgres", "select * from TRAVEL.TRIP", new Date()));
        SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// postgres", "select * from TRAVEL.TRIP", new Date()));
        SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// postgres", "select * from TRAVEL.PERSON", new Date()));
        assertEquals(4, SQLHistoryManager.getInstance().getSQLHistory().size());
        SQLHistoryManager.getInstance().setListSize(3);
        assertEquals(3, SQLHistoryManager.getInstance().getSQLHistory().size());
            String historyFilePath = FileUtil.getFileDisplayName(root) + File.separator + "Databases" + File.separator + "SQLHISTORY";
            FileObject historyFileObject = root.getFileObject(SQL_HISTORY_FOLDER);
            // Limit to 3 SQL statements
            SQLHistoryPersistenceManager.getInstance().updateSQLSaved(3, historyFileObject);
            assertEquals(3, SQLHistoryPersistenceManager.getInstance().retrieve(historyFileObject).size());
        } catch (SQLHistoryException ex) {
            Exceptions.printStackTrace(ex);
        } catch (IOException ex) {
            Exceptions.printStackTrace(ex);
        } catch (ClassNotFoundException ex) {
            Exceptions.printStackTrace(ex);
        } catch (ParseException ex) {
            Exceptions.printStackTrace(ex);
        }
    }
    
    public void testUpdateListRemoveLessNumber1() {
        SQLHistoryManager.getInstance().setListSize(100);
        SQLHistoryManager.getInstance().getSQLHistory().clear();
            // Create a list of SQL statements
        SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// derby", "select * from TRAVEL.TRIP", new Date()));
        SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// postgres1", "select * from TRAVEL.TRIP", new Date()));
        SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// postgres2", "select * from TRAVEL.TRIP", new Date()));
        SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// postgres3", "select * from TRAVEL.TRIP", new Date()));
        SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// postgres4", "select * from TRAVEL.TRIP", new Date()));
        assertEquals(5, SQLHistoryManager.getInstance().getSQLHistory().size());
        SQLHistoryManager.getInstance().setListSize(1);
            String historyFilePath = FileUtil.getFileDisplayName(root) + File.separator + "Databases" + File.separator + "SQLHISTORY";
            FileObject historyFileObject = FileUtil.toFileObject(new File(historyFilePath));
            // Limit to 1 SQL statement
            SQLHistoryManager.getInstance().updateList(1, historyFilePath , historyFileObject);
            assertEquals(1, SQLHistoryManager.getInstance().getSQLHistory().size());      
        } catch (SQLHistoryException ex) {
            Exceptions.printStackTrace(ex);
        } catch (IOException ex) {
            Exceptions.printStackTrace(ex);
        } catch (ParseException ex) {
            Exceptions.printStackTrace(ex);
        }
    }
    
      public void testUpdateListRemoveAll() {
        SQLHistoryManager.getInstance().setListSize(100);
        SQLHistoryManager.getInstance().getSQLHistory().clear();
            // Create a list of SQL statements
        SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// derby", "select * from TRAVEL.TRIP", new Date()));
        SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// postgres1", "select * from TRAVEL.TRIP", new Date()));
        SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// postgres2", "select * from TRAVEL.TRIP", new Date()));
        SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// postgres3", "select * from TRAVEL.TRIP", new Date()));
        SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// postgres4", "select * from TRAVEL.TRIP", new Date()));
        assertEquals(5, SQLHistoryManager.getInstance().getSQLHistory().size());
        SQLHistoryManager.getInstance().setListSize(0);
        assertEquals(0, SQLHistoryManager.getInstance().getSQLHistory().size());
            FileObject historyFileObject = FileUtil.toFileObject(new File(historyFilePath));            
            // Limit to 0 SQL statements
            SQLHistoryManager.getInstance().updateList(0, historyFilePath, historyFileObject);
            SQLHistoryPersistenceManager.getInstance().updateSQLSaved(0, historyFileObject);
            assertEquals(0, SQLHistoryPersistenceManager.getInstance().retrieve(historyFileObject).size());                        
        } catch (SQLHistoryException ex) {
            Exceptions.printStackTrace(ex);
        } catch (IOException ex) {
            Exceptions.printStackTrace(ex);
        } catch (ClassNotFoundException ex) {
            Exceptions.printStackTrace(ex);
        }  catch (ParseException ex) {
            Exceptions.printStackTrace(ex);
        }
    }
    
}




import org.netbeans.junit.NbTestCase;
import org.openide.filesystems.FileObject;
import org.openide.filesystems.FileUtil;
import org.openide.util.Exceptions;

/**
 * @author John Baker, Jiri Skrivanek


    /** Test testExecuteStatements passes if no exceptions occur. */
    public void testExecuteStatements() throws Exception {
        SQLHistoryManager testableManager = new SQLHistoryManager() {
        sqlHistoryList.add(new SQLHistory("jdbc:// mysql", "select * from TRAVEL.PERSON", Calendar.getInstance().getTime()));
        FileObject fo = FileUtil.toFileObject(getWorkDir());
        sqlHistoryList.add(new SQLHistory("jdbc:// oracle", "select * from PERSON", Calendar.getInstance().getTime()));
        SQLHistoryPersistenceManager.getInstance().create(fo, sqlHistoryList);
    }

            @Override
            protected FileObject getConfigRoot() {
                try {
                    return FileUtil.toFileObject(getWorkDir());
                } catch (IOException ex) {
                    throw new RuntimeException(ex);
                }
            }

            @Override
            protected String getRelativeHistoryPath() {
                return "";
            }
            
        };
        testableManager.getSQLHistory().add(new SQLHistoryEntry("jdbc:// mysql", "select * from TRAVEL.PERSON", Calendar.getInstance().getTime()));
        testableManager.getSQLHistory().add(new SQLHistoryEntry("jdbc:// oracle", "select * from PERSON", Calendar.getInstance().getTime()));
        testableManager.save();
    }

    /** Tests parsing of date format. */
    public void testDateParsing() throws Exception {
        final URL u = this.getClass().getResource("sql_history.xml");
        final FileObject fo = FileUtil.toFileObject(new File(u.toURI()));
        SQLHistoryManager testableManager = new SQLHistoryManager() {
            @Override
            protected FileObject getHistoryRoot(boolean create) throws IOException {
                return fo;
            }
        };
        
        List<SQLHistoryEntry> sqlHistoryList = new ArrayList<SQLHistoryEntry>(testableManager.getSQLHistory());
        for (SQLHistoryEntry sqlHistory : sqlHistoryList) {
            assertNotNull(sqlHistory.getDate());
        }
    }

Return to bug 204550

Lines 44-50 Link Here

(-)a/db.core/src/org/netbeans/modules/db/sql/execute/SQLExecuteHelper.java (-6 / +3 lines)
44		44
45	package org.netbeans.modules.db.sql.execute;	45	package org.netbeans.modules.db.sql.execute;
46		46
47	import org.netbeans.modules.db.sql.history.SQLHistory;	47	import org.netbeans.modules.db.sql.history.SQLHistoryEntry;
48	import java.util.ArrayList;	48	import java.util.ArrayList;
49	import java.util.Collections;	49	import java.util.Collections;
50	import java.util.Date;	50	import java.util.Date;
Lines 54-61 Link Here
54	import org.netbeans.api.db.explorer.DatabaseConnection;	54	import org.netbeans.api.db.explorer.DatabaseConnection;
55	import org.netbeans.modules.db.dataview.api.DataView;	55	import org.netbeans.modules.db.dataview.api.DataView;
56	import org.netbeans.modules.db.sql.history.SQLHistoryManager;	56	import org.netbeans.modules.db.sql.history.SQLHistoryManager;
57	import org.openide.filesystems.FileObject;
58	import org.openide.filesystems.FileUtil;
59		57
60	/**	58	/**
61	* Support class for executing SQL statements.	59	* Support class for executing SQL statements.
Lines 68-74 Link Here
68		66
69	private static final Logger LOGGER = Logger.getLogger(SQLExecuteHelper.class.getName());	67	private static final Logger LOGGER = Logger.getLogger(SQLExecuteHelper.class.getName());
70	private static final boolean LOG = LOGGER.isLoggable(Level.FINE);	68	private static final boolean LOG = LOGGER.isLoggable(Level.FINE);
71	private static final FileObject USERDIR = FileUtil.getConfigRoot();
72		69
73	/**	70	/**
74	* Executes a SQL string, possibly containing multiple statements. Returns the execution	71	* Executes a SQL string, possibly containing multiple statements. Returns the execution
Lines 119-125 Link Here
119	DataView view = DataView.create(conn, sql, pageSize);	116	DataView view = DataView.create(conn, sql, pageSize);
120		117
121	// Save SQL statements executed for the SQLHistoryManager	118	// Save SQL statements executed for the SQLHistoryManager
122	SQLHistoryManager.getInstance().saveSQL(new SQLHistory(url, sql, new Date()));	119	SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry(url, sql, new Date()));
123		120
124	result = new SQLExecutionResult(info, view);	121	result = new SQLExecutionResult(info, view);
125		122
Lines 153-159 Link Here
153	}	150	}
154		151
155	// Persist SQL executed	152	// Persist SQL executed
156	SQLHistoryManager.getInstance().save(USERDIR);	153	SQLHistoryManager.getInstance().save();
157		154
158	if (!cancelled) {	155	if (!cancelled) {
159	return new SQLExecutionResults(results);	156	return new SQLExecutionResults(results);

Lines 21-31 Link Here

(-)a/db.core/src/org/netbeans/modules/db/sql/execute/ui/SQLHistoryPanel.form (-29 / +11 lines)
21	<Group type="103" groupAlignment="0" attributes="0">	21	<Group type="103" groupAlignment="0" attributes="0">
22	<Group type="102" attributes="0">	22	<Group type="102" attributes="0">
23	<Group type="103" groupAlignment="0" attributes="0">	23	<Group type="103" groupAlignment="0" attributes="0">
24	<Component id="jScrollPane1" pref="655" max="32767" attributes="0"/>	24	<Component id="jScrollPane1" pref="645" max="32767" attributes="0"/>
25	<Group type="102" attributes="0">	25	<Group type="102" attributes="0">
26	<Component id="jLabel1" min="-2" max="-2" attributes="0"/>	26	<Component id="jLabel1" min="-2" max="-2" attributes="0"/>
27	<EmptySpace type="unrelated" max="-2" attributes="0"/>	27	<EmptySpace type="unrelated" max="-2" attributes="0"/>
28	<Component id="connectionUrlComboBox" pref="306" max="32767" attributes="0"/>	28	<Component id="connectionUrlComboBox" pref="301" max="32767" attributes="0"/>
29	<EmptySpace type="separate" max="-2" attributes="0"/>	29	<EmptySpace type="separate" max="-2" attributes="0"/>
30	<Component id="jLabel2" min="-2" max="-2" attributes="0"/>	30	<Component id="jLabel2" min="-2" max="-2" attributes="0"/>
31	<EmptySpace type="unrelated" max="-2" attributes="0"/>	31	<EmptySpace type="unrelated" max="-2" attributes="0"/>
Lines 65-71 Link Here
65	<Group type="103" groupAlignment="0" attributes="0">	65	<Group type="103" groupAlignment="0" attributes="0">
66	<Component id="insertSQLButton" alignment="0" min="-2" max="-2" attributes="0"/>	66	<Component id="insertSQLButton" alignment="0" min="-2" max="-2" attributes="0"/>
67	<Group type="102" alignment="1" attributes="0">	67	<Group type="102" alignment="1" attributes="0">
68	<Component id="jScrollPane1" pref="168" max="32767" attributes="0"/>	68	<Component id="jScrollPane1" pref="268" max="32767" attributes="0"/>
69	<EmptySpace max="-2" attributes="0"/>	69	<EmptySpace max="-2" attributes="0"/>
70	<Group type="103" groupAlignment="3" attributes="0">	70	<Group type="103" groupAlignment="3" attributes="0">
71	<Component id="sqlLimitTextField" alignment="3" min="-2" max="-2" attributes="0"/>	71	<Component id="sqlLimitTextField" alignment="3" min="-2" max="-2" attributes="0"/>
Lines 90-100 Link Here
90	</Properties>	90	</Properties>
91	</Component>	91	</Component>
92	<Component class="javax.swing.JComboBox" name="connectionUrlComboBox">	92	<Component class="javax.swing.JComboBox" name="connectionUrlComboBox">
93	<Properties>
94	<Property name="renderer" type="javax.swing.ListCellRenderer" editor="org.netbeans.modules.form.RADConnectionPropertyEditor">
95	<Connection code="new ConnectionUrlRenderer()" type="code"/>
96	</Property>
97	</Properties>
98	<AccessibilityProperties>	93	<AccessibilityProperties>
99	<Property name="AccessibleContext.accessibleName" type="java.lang.String" editor="org.netbeans.modules.i18n.form.FormI18nStringEditor">	94	<Property name="AccessibleContext.accessibleName" type="java.lang.String" editor="org.netbeans.modules.i18n.form.FormI18nStringEditor">
100	<ResourceString bundle="org/netbeans/modules/db/sql/execute/ui/Bundle.properties" key="ASCN_ConnectionCombo" replaceFormat="org.openide.util.NbBundle.getMessage({sourceFileName}.class, "{key}")"/>	95	<ResourceString bundle="org/netbeans/modules/db/sql/execute/ui/Bundle.properties" key="ASCN_ConnectionCombo" replaceFormat="org.openide.util.NbBundle.getMessage({sourceFileName}.class, "{key}")"/>
Lines 163-199 Link Here
163	<SubComponents>	158	<SubComponents>
164	<Component class="javax.swing.JTable" name="sqlHistoryTable">	159	<Component class="javax.swing.JTable" name="sqlHistoryTable">
165	<Properties>	160	<Properties>
		161	<Property name="autoCreateColumnsFromModel" type="boolean" value="false"/>
166	<Property name="model" type="javax.swing.table.TableModel" editor="org.netbeans.modules.form.RADConnectionPropertyEditor">	162	<Property name="model" type="javax.swing.table.TableModel" editor="org.netbeans.modules.form.RADConnectionPropertyEditor">
167	<Connection code="new HistoryTableModel()" type="code"/>	163	<Connection code="htm" type="code"/>
168	</Property>	164	</Property>
169	<Property name="columnModel" type="javax.swing.table.TableColumnModel" editor="org.netbeans.modules.form.editors2.TableColumnModelEditor">	165	<Property name="tableHeader" type="javax.swing.table.JTableHeader" editor="org.netbeans.modules.form.editors2.JTableHeaderEditor">
170	<TableColumnModel selectionModel="0">	166	<TableHeader reorderingAllowed="true" resizingAllowed="true"/>
171	<Column maxWidth="-1" minWidth="-1" prefWidth="-1" resizable="true">
172	<Title/>
173	<Editor/>
174	<Renderer/>
175	</Column>
176	<Column maxWidth="-1" minWidth="-1" prefWidth="-1" resizable="true">
177	<Title/>
178	<Editor/>
179	<Renderer/>
180	</Column>
181	</TableColumnModel>
182	</Property>
183	<Property name="gridColor" type="java.awt.Color" editor="org.netbeans.beaninfo.editors.ColorEditor">
184	<Color blue="c0" green="c0" id="lightGray" palette="1" red="c0" type="palette"/>
185	</Property>	167	</Property>
186	</Properties>	168	</Properties>
187	<AccessibilityProperties>	169	<AccessibilityProperties>
188	<Property name="AccessibleContext.accessibleName" type="java.lang.String" editor="org.netbeans.modules.i18n.form.FormI18nStringEditor">	170	<Property name="AccessibleContext.accessibleName" type="java.lang.String" editor="org.netbeans.modules.i18n.form.FormI18nStringEditor">
189	<ResourceString bundle="org/netbeans/modules/db/sql/execute/ui/Bundle.properties" key="ACSN_History" replaceFormat="org.openide.util.NbBundle.getMessage({sourceFileName}.class, "{key}")"/>	171	<ResourceString bundle="org/netbeans/modules/db/sql/execute/ui/Bundle.properties" key="ACSN_History" replaceFormat="org.openide.util.NbBundle.getMessage({sourceFileName}.class, "{key}")"/>
190	</Property>	172	</Property>
191	<Property name="AccessibleContext.accessibleDescription" type="java.lang.String" editor="org.netbeans.modules.i18n.form.FormI18nStringEditor">	173	<Property name="AccessibleContext.accessibleDescription" type="java.lang.String" editor="org.netbeans.modules.i18n.form.FormI18nStringEditor" postCode="int dateColumnWidth1 = new JTextField(dateTimeFormat.format(new Date())).getPreferredSize().width; int dateColumnWidth2 = new JTextField(NbBundle.getMessage(SQLHistoryPanel.class, "LBL_DateTableTitle") + "XXXXX").getPreferredSize().width; int dateColumnWidth = Math.max(dateColumnWidth1, dateColumnWidth2); TableColumnModel sqlHistoryTableTCM = sqlHistoryTable.getColumnModel(); TableColumn sqlHistoryTableColumn; sqlHistoryTableColumn = new TableColumn(1); sqlHistoryTableColumn.setHeaderValue(NbBundle.getMessage(SQLHistoryPanel.class, "LBL_SQLTableTitle")); sqlHistoryTableTCM.addColumn(sqlHistoryTableColumn); sqlHistoryTableColumn = new TableColumn(2); sqlHistoryTableColumn.setMinWidth(dateColumnWidth); sqlHistoryTableColumn.setPreferredWidth(dateColumnWidth); sqlHistoryTableColumn.setMaxWidth(dateColumnWidth); sqlHistoryTableColumn.setHeaderValue(NbBundle.getMessage(SQLHistoryPanel.class, "LBL_DateTableTitle")); sqlHistoryTableColumn.setCellRenderer(new DefaultTableCellRenderer() { public Component getTableCellRendererComponent(JTable table, Object value, boolean isSelected, boolean hasFocus, int row, int column) { if(value instanceof Date) { value = dateTimeFormat.format((Date) value); } return super.getTableCellRendererComponent(table, value, isSelected, hasFocus, row, column); } }); sqlHistoryTableTCM.addColumn(sqlHistoryTableColumn);">
192	<ResourceString bundle="org/netbeans/modules/db/sql/execute/ui/Bundle.properties" key="ACSD_History" replaceFormat="org.openide.util.NbBundle.getMessage({sourceFileName}.class, "{key}")"/>	174	<ResourceString bundle="org/netbeans/modules/db/sql/execute/ui/Bundle.properties" key="ACSD_History" replaceFormat="org.openide.util.NbBundle.getMessage({sourceFileName}.class, "{key}")"/>
193	</Property>	175	</Property>
194	</AccessibilityProperties>	176	</AccessibilityProperties>
195	<AuxValues>	177	<AuxValues>
196	<AuxValue name="JavaCodeGenerator_CreateCodeCustom" type="java.lang.String" value="new JTable() { public Component prepareRenderer(TableCellRenderer renderer, int rowIndex, int vColIndex) { Component c = super.prepareRenderer(renderer, rowIndex, vColIndex); if (c instanceof JComponent) { JComponent jc = (JComponent)c; jc.setToolTipText(view.getSQLHistoryTooltipValue(rowIndex, vColIndex)); } return c; } };"/>	178	<AuxValue name="JavaCodeGenerator_CreateCodeCustom" type="java.lang.String" value="new JTable() { public Component prepareRenderer(TableCellRenderer renderer, int rowIndex, int vColIndex) { Component c = super.prepareRenderer(renderer, rowIndex, vColIndex); if (c instanceof JComponent) { JComponent jc = (JComponent)c; jc.setToolTipText(getSQLHistoryTooltipValue(sqlHistoryTable, rowIndex, vColIndex)); } return c; } }"/>
197	</AuxValues>	179	</AuxValues>
198	</Component>	180	</Component>
199	</SubComponents>	181	</SubComponents>
Lines 214-220 Link Here
214	<Component class="javax.swing.JTextField" name="sqlLimitTextField">	196	<Component class="javax.swing.JTextField" name="sqlLimitTextField">
215	<Properties>	197	<Properties>
216	<Property name="text" type="java.lang.String" editor="org.netbeans.modules.form.RADConnectionPropertyEditor">	198	<Property name="text" type="java.lang.String" editor="org.netbeans.modules.form.RADConnectionPropertyEditor">
217	<Connection code="SQLHistoryManager.SAVE_STATEMENTS_MAX_LIMIT_ENTERED" type="code"/>	199	<Connection code="Integer.toString(SQLHistoryManager.getInstance().getListSize())" type="code"/>
218	</Property>	200	</Property>
219	<Property name="minimumSize" type="java.awt.Dimension" editor="org.netbeans.beaninfo.editors.DimensionEditor">	201	<Property name="minimumSize" type="java.awt.Dimension" editor="org.netbeans.beaninfo.editors.DimensionEditor">
220	<Dimension value="[18, 22]"/>	202	<Dimension value="[18, 22]"/>
Lines 224-230 Link Here
224	<Property name="AccessibleContext.accessibleName" type="java.lang.String" editor="org.netbeans.modules.i18n.form.FormI18nStringEditor">	206	<Property name="AccessibleContext.accessibleName" type="java.lang.String" editor="org.netbeans.modules.i18n.form.FormI18nStringEditor">
225	<ResourceString bundle="org/netbeans/modules/db/sql/execute/ui/Bundle.properties" key="ACSN_Save" replaceFormat="org.openide.util.NbBundle.getMessage({sourceFileName}.class, "{key}")"/>	207	<ResourceString bundle="org/netbeans/modules/db/sql/execute/ui/Bundle.properties" key="ACSN_Save" replaceFormat="org.openide.util.NbBundle.getMessage({sourceFileName}.class, "{key}")"/>
226	</Property>	208	</Property>
227	<Property name="AccessibleContext.accessibleDescription" type="java.lang.String" editor="org.netbeans.modules.i18n.form.FormI18nStringEditor">	209	<Property name="AccessibleContext.accessibleDescription" type="java.lang.String" editor="org.netbeans.modules.i18n.form.FormI18nStringEditor" postCode="sqlLimitTextField.setInputVerifier(new InputVerifier() { public boolean verify(JComponent input) { JTextField tf = (JTextField) input; return tf.getText().matches("^\\d+$"); } });">
228	<ResourceString bundle="org/netbeans/modules/db/sql/execute/ui/Bundle.properties" key="ACSD_Save" replaceFormat="org.openide.util.NbBundle.getMessage({sourceFileName}.class, "{key}")"/>	210	<ResourceString bundle="org/netbeans/modules/db/sql/execute/ui/Bundle.properties" key="ACSD_Save" replaceFormat="org.openide.util.NbBundle.getMessage({sourceFileName}.class, "{key}")"/>
229	</Property>	211	</Property>
230	</AccessibilityProperties>	212	</AccessibilityProperties>

Lines 41-71 Link Here

(-)a/db.core/src/org/netbeans/modules/db/sql/history/SQLHistoryManager.java (-99 / +85 lines)
41	*/	41	*/
42	package org.netbeans.modules.db.sql.history;	42	package org.netbeans.modules.db.sql.history;
43		43
44		44	import java.io.IOException;
45	import java.util.ArrayList;	45	import java.io.InputStream;
46	import java.util.List;	46	import java.io.OutputStream;
47	import java.util.logging.Level;	47	import java.util.logging.Level;
48	import java.util.logging.Logger;	48	import java.util.logging.Logger;
		49	import javax.xml.bind.JAXBContext;
		50	import javax.xml.bind.JAXBException;
		51	import javax.xml.bind.Marshaller;
		52	import javax.xml.bind.Unmarshaller;
49	import org.netbeans.modules.db.sql.execute.ui.SQLHistoryPanel;	53	import org.netbeans.modules.db.sql.execute.ui.SQLHistoryPanel;
50	import org.openide.filesystems.FileObject;	54	import org.openide.filesystems.FileObject;
51	import org.openide.filesystems.FileUtil;	55	import org.openide.filesystems.FileUtil;
52	import org.openide.util.NbBundle;	56	import org.openide.util.Exceptions;
		57	import org.openide.util.NbPreferences;
53		58
54	/**	59	/**
55	*	60	*
56	* @author John Baker	61	* @author John Baker
57	*/	62	*/
58	public class SQLHistoryManager {	63	public class SQLHistoryManager {
59	public static final String SQL_HISTORY_FOLDER = "Databases/SQLHISTORY"; // NOI18N	64
60	public static final String SQL_HISTORY_FILE_NAME = "sql_history.xml"; // NOI18N	65	JAXBContext context;
61	public static final String SAVE_STATEMENTS_MAX_LIMIT_ENTERED = "100"; // NOI18N	66	private static final String SQL_HISTORY_DIRECTORY = "Databases/SQLHISTORY"; // NOI18N
		67	private static final String SQL_HISTORY_FILE = "sql_history.xml"; // NOI18N
		68	public static final String OPT_SQL_STATEMENTS_SAVED_FOR_HISTORY = "SQL_STATEMENTS_SAVED_FOR_HISTORY"; // NOI18N
		69	public static final int DEFAULT_SQL_STATEMENTS_SAVED_FOR_HISTORY = 100;
		70	public static final int MAX_SQL_STATEMENTS_SAVED_FOR_HISTORY = 10000;
62	private static SQLHistoryManager _instance = null;	71	private static SQLHistoryManager _instance = null;
63	private static final Logger LOGGER = Logger.getLogger(SQLHistory.class.getName());	72	private static final Logger LOGGER = Logger.getLogger(SQLHistoryEntry.class.getName());
64	private List<SQLHistory> sqlList = new ArrayList<SQLHistory>();	73	private SQLHistory sqlHistory;
65	private int listSize;
66	private FileObject historyRoot;
67		74
68	private SQLHistoryManager() {	75	protected SQLHistoryManager() {
		76	ClassLoader orig = Thread.currentThread().getContextClassLoader();
		77	Thread.currentThread().setContextClassLoader(SQLHistoryManager.class.getClassLoader());
		78	try {
		79	context = JAXBContext.newInstance("org.netbeans.modules.db.sql.history", SQLHistoryManager.class.getClassLoader());
		80	loadHistory();
		81	} catch (JAXBException ex) {
		82	throw new RuntimeException(ex);
		83	} finally {
		84	Thread.currentThread().setContextClassLoader(orig);
		85	}
69	}	86	}
70		87
71	public static SQLHistoryManager getInstance() {	88	public static SQLHistoryManager getInstance() {
Lines 75-181 Link Here
75	return _instance;	92	return _instance;
76	}	93	}
77		94
78	/**
79	* Get the value of listSize
80	*
81	* @return the value of listSize
82	*/
83	public int getListSize() {	95	public int getListSize() {
84	return listSize;	96	return NbPreferences.forModule(SQLHistoryPanel.class).getInt("OPT_SQL_STATEMENTS_SAVED_FOR_HISTORY", DEFAULT_SQL_STATEMENTS_SAVED_FOR_HISTORY);
		97	}
		98
		99	protected FileObject getHistoryRoot(boolean create) throws IOException {
		100	FileObject result = null;
		101	FileObject historyRootDir = getConfigRoot().getFileObject(getRelativeHistoryPath());
		102	if (historyRootDir != null \|\| create) {
		103	if (historyRootDir == null) {
		104	historyRootDir = FileUtil.createFolder(getConfigRoot(), getRelativeHistoryPath());
		105	}
		106	FileObject historyRoot = historyRootDir.getFileObject(getHistoryFilename());
		107
		108	if (historyRoot != null \|\| create) {
		109	if(historyRoot == null) {
		110	historyRoot = historyRootDir.createData(getHistoryFilename());
		111	}
		112	result = historyRoot;
		113	}
		114	}
		115	return result;
85	}	116	}
86		117
87	public FileObject getHistoryRoot() {	118	protected FileObject getConfigRoot() {
88	return historyRoot;	119	return FileUtil.getConfigRoot();
		120	}
		121
		122	protected String getRelativeHistoryPath() {
		123	return SQL_HISTORY_DIRECTORY;
89	}	124	}
90		125
91	public void setHistoryRoot(FileObject root) {	126	protected String getHistoryFilename() {
92	historyRoot = root;	127	return SQL_HISTORY_FILE;
		128	}
		129
		130	public void setListSize(int listSize) {
		131	NbPreferences.forModule(SQLHistoryPanel.class).putInt("OPT_SQL_STATEMENTS_SAVED_FOR_HISTORY", listSize);
		132	sqlHistory.setHistoryLimit(listSize);
93	}	133	}
94		134
95	/**	135	public void saveSQL(SQLHistoryEntry sqlStored) {
96	* Set the value of listSize	136	sqlHistory.add(sqlStored);
97	*	137	}
98	* @param listSize new value of listSize
99	*/
100	public void setListSize(int listSize) {
101	this.listSize = listSize;
102	}
103		138
104		139	private void loadHistory() {
105	public void saveSQL(SQLHistory sqlStored) {
106	sqlList.add(sqlStored);
107	}
108
109	public void save(FileObject userdirRoot) {
110	try {	140	try {
111	// create a folder in the userdir for sql_history.xml file that maintains a list of executed SQL	141	Unmarshaller unmarshaller = context.createUnmarshaller();
112	setHistoryRoot(userdirRoot.getFileObject(SQL_HISTORY_FOLDER));	142	InputStream is = getHistoryRoot(false).getInputStream();
113	if (null == historyRoot \|\| !historyRoot.isValid()) {	143	sqlHistory = (SQLHistory) unmarshaller.unmarshal(is);
114	historyRoot = FileUtil.createFolder(userdirRoot, SQL_HISTORY_FOLDER);	144	sqlHistory.setHistoryLimit(getListSize());
115	}	145	is.close();
116	// Start managing the persistence of SQL statements that have been executed
117	SQLHistoryPersistenceManager.getInstance().create(historyRoot, sqlList);
118	sqlList.clear();
119	} catch (Exception ex) {	146	} catch (Exception ex) {
		147	sqlHistory = new SQLHistory();
		148	sqlHistory.setHistoryLimit(getListSize());
120	LOGGER.log(Level.INFO, ex.getMessage());	149	LOGGER.log(Level.INFO, ex.getMessage());
121	LOGGER.log(Level.INFO, NbBundle.getMessage(SQLHistoryManager.class, "MSG_ErrorParsingHistoryFile"));
122	}	150	}
123	}	151	}
124		152
125	public List<SQLHistory> getSQLHistory() {	153	public void save() {
126	return sqlList;	154	try {
127	}	155	Marshaller marshaller = context.createMarshaller();
128		156	OutputStream os = getHistoryRoot(true).getOutputStream();
129	public List<SQLHistory> retrieve() {	157	marshaller.marshal(sqlHistory, os);
130	return new ArrayList<SQLHistory>();	158	os.close();
131		159	} catch (Exception ex) {
132	}	160	LOGGER.log(Level.INFO, ex.getMessage());
		161	}
		162	}
133		163
134	public List<String> retrieve(String url) {	164	public SQLHistory getSQLHistory() {
135	List<String> sqlAsString = new ArrayList<String>();	165	return sqlHistory;
136	String sql;
137	if (!getUrlsUsed().isEmpty()) {
138	for (SQLHistory historyItem : sqlList) {
139	sql = historyItem.getSql();
140	if (url.equals(historyItem.getUrl())) {
141	sqlAsString.add(sql);
142	}
143	}
144	}
145	return sqlAsString;
146
147	}
148
149	private List<String> getUrlsUsed() {
150	List<String> urls = new ArrayList<String>();
151	String url;
152	for (SQLHistory historyItem : sqlList) {
153	url = historyItem.getUrl();
154	if (!urls.contains(url)) {
155	urls.add(url);
156	}
157	}
158	return urls;
159	}
160
161	public int updateList(int limit, String historyFilePath, FileObject root) throws SQLHistoryException {
162	List<SQLHistory> updatedSQLHistoryList = new ArrayList<SQLHistory>();
163	int numItemsToRemove = 0;
164	try {
165	updatedSQLHistoryList = SQLHistoryPersistenceManager.getInstance().retrieve(root);
166	if (limit >= updatedSQLHistoryList.size()) {
167	// no changes needed to the current list
168	return -1;
169	}
170	// Remove elements from list based on the number of statements to save that is set in the SQL History dialog
171	numItemsToRemove = updatedSQLHistoryList.size() - limit;
172	for (int i = 0; i < numItemsToRemove; i++) {
173	updatedSQLHistoryList.remove(0);
174	}
175	} catch (ClassNotFoundException ex) {
176	LOGGER.log(Level.INFO, NbBundle.getMessage(SQLHistoryPanel.class, "MSG_RuntimeErrorRetrievingHistory") + ex);
177	}
178	sqlList = updatedSQLHistoryList;
179	return numItemsToRemove;
180	}	166	}
181	}	167	}

Lines 41-50 Link Here

(-)a/db.core/test/unit/src/org/netbeans/modules/db/sql/history/SQLHistoryManagerTest.java (-90 / +36 lines)
41	*/	41	*/
42	package org.netbeans.modules.db.sql.history;	42	package org.netbeans.modules.db.sql.history;
43		43
44	import java.io.File;
45	import java.io.IOException;	44	import java.io.IOException;
46	import java.text.DateFormat;	45	import java.text.DateFormat;
47	import java.text.ParseException;	46	import java.text.ParseException;
		47	import java.util.Date;
48	import org.netbeans.junit.NbTestCase;	48	import org.netbeans.junit.NbTestCase;
49	import org.netbeans.junit.NbTestSuite;	49	import org.netbeans.junit.NbTestSuite;
50	import org.openide.filesystems.FileObject;	50	import org.openide.filesystems.FileObject;
Lines 62-69 Link Here
62	* @author John Baker	62	* @author John Baker
63	*/	63	*/
64	public class SQLHistoryManagerTest extends NbTestCase {	64	public class SQLHistoryManagerTest extends NbTestCase {
		65
65	public static final String SQL_HISTORY_FOLDER = "Databases/SQLHISTORY"; // NOI18N	66	public static final String SQL_HISTORY_FOLDER = "Databases/SQLHISTORY"; // NOI18N
66	public static final String SQL_HISTORY_FILE_NAME = "sql_history.xml"; // NOI18N	67	public static final String SQL_HISTORY_FILE_NAME = "sql_history.xml"; // NOI18N
		68
67	/** Default constructor.	69	/** Default constructor.
68	* @param testName name of particular test case	70	* @param testName name of particular test case
69	*/	71	*/
Lines 107-213 Link Here
107	}	109	}
108		110
109	public void testUpdateListRemoveEqualNumber() {	111	public void testUpdateListRemoveEqualNumber() {
110	try {	112	SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// derby", "select * from TRAVEL.TRIP", new Date()));
111	FileObject root = FileUtil.toFileObject(getWorkDir());	113	SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// postgres", "select * from TRAVEL.TRIP", new Date()));
112	// Create a list of SQL statements	114	SQLHistoryManager.getInstance().setListSize(2);
113	SQLHistoryManager.getInstance().saveSQL(new SQLHistory("jdbc:// derby", "select * from TRAVEL.TRIP", DateFormat.getInstance().parse("07/10/96 4:5 PM, PDT")));	115	assertEquals(2, SQLHistoryManager.getInstance().getSQLHistory().size());
114	SQLHistoryManager.getInstance().saveSQL(new SQLHistory("jdbc:// postgres", "select * from TRAVEL.TRIP", DateFormat.getInstance().parse("07/10/96 4:5 PM, PDT")));
115	// Save SQL
116	SQLHistoryManager.getInstance().save(root);
117	String historyFileRootPath = FileUtil.getFileDisplayName(root) + File.separator + "Databases" + File.separator + "SQLHISTORY";
118	FileObject historyFileObject = FileUtil.toFileObject(new File(historyFileRootPath));
119	String historyFilePath = historyFileRootPath + File.separator + SQL_HISTORY_FILE_NAME;
120	// Limit to 2 SQL statements
121	SQLHistoryManager.getInstance().updateList(2, historyFilePath , historyFileObject);
122	assertEquals(0, SQLHistoryManager.getInstance().getSQLHistory().size());
123	} catch (SQLHistoryException ex) {
124	Exceptions.printStackTrace(ex);
125	} catch (IOException ex) {
126	Exceptions.printStackTrace(ex);
127	} catch (ParseException ex) {
128	Exceptions.printStackTrace(ex);
129	}
130	}	116	}
131		117
132	public void testUpdateListRemoveLessNumber3() {	118	public void testUpdateListRemoveLessNumber3() {
133	try {	119	SQLHistoryManager.getInstance().setListSize(100);
134	FileObject root = FileUtil.toFileObject(getWorkDir());	120	SQLHistoryManager.getInstance().getSQLHistory().clear();
135	// Create a list of SQL statements	121	// Create a list of SQL statements
136	SQLHistoryManager.getInstance().saveSQL(new SQLHistory("jdbc:// derby", "select * from TRAVEL.TRIP", DateFormat.getInstance().parse("07/10/96 4:5 PM, PDT")));	122	SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// derby", "select * from TRAVEL.TRIP", new Date()));
137	SQLHistoryManager.getInstance().saveSQL(new SQLHistory("jdbc:// postgres", "select * from TRAVEL.TRIPTYPE", DateFormat.getInstance().parse("07/10/96 4:5 PM, PDT")));	123	SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// postgres", "select * from TRAVEL.TRIPTYPE", new Date()));
138	SQLHistoryManager.getInstance().saveSQL(new SQLHistory("jdbc:// postgres", "select * from TRAVEL.TRIP", DateFormat.getInstance().parse("07/10/96 4:5 PM, PDT")));	124	SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// postgres", "select * from TRAVEL.TRIP", new Date()));
139	SQLHistoryManager.getInstance().saveSQL(new SQLHistory("jdbc:// postgres", "select * from TRAVEL.TRIP", DateFormat.getInstance().parse("07/10/96 4:5 PM, PDT")));	125	SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// postgres", "select * from TRAVEL.TRIP", new Date()));
140	SQLHistoryManager.getInstance().saveSQL(new SQLHistory("jdbc:// postgres", "select * from TRAVEL.PERSON", DateFormat.getInstance().parse("07/10/96 4:5 PM, PDT")));	126	SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// postgres", "select * from TRAVEL.PERSON", new Date()));
141	assertEquals(5, SQLHistoryManager.getInstance().getSQLHistory().size());	127	assertEquals(4, SQLHistoryManager.getInstance().getSQLHistory().size());
142	// Save SQL	128	SQLHistoryManager.getInstance().setListSize(3);
143	SQLHistoryManager.getInstance().save(root);	129	assertEquals(3, SQLHistoryManager.getInstance().getSQLHistory().size());
144	String historyFilePath = FileUtil.getFileDisplayName(root) + File.separator + "Databases" + File.separator + "SQLHISTORY";
145	FileObject historyFileObject = root.getFileObject(SQL_HISTORY_FOLDER);
146	// Limit to 3 SQL statements
147	SQLHistoryPersistenceManager.getInstance().updateSQLSaved(3, historyFileObject);
148	assertEquals(3, SQLHistoryPersistenceManager.getInstance().retrieve(historyFileObject).size());
149	} catch (SQLHistoryException ex) {
150	Exceptions.printStackTrace(ex);
151	} catch (IOException ex) {
152	Exceptions.printStackTrace(ex);
153	} catch (ClassNotFoundException ex) {
154	Exceptions.printStackTrace(ex);
155	} catch (ParseException ex) {
156	Exceptions.printStackTrace(ex);
157	}
158	}	130	}
159		131
160	public void testUpdateListRemoveLessNumber1() {	132	public void testUpdateListRemoveLessNumber1() {
161	try {	133	SQLHistoryManager.getInstance().setListSize(100);
162	FileObject root = FileUtil.toFileObject(getWorkDir());	134	SQLHistoryManager.getInstance().getSQLHistory().clear();
163	// Create a list of SQL statements	135	// Create a list of SQL statements
164	SQLHistoryManager.getInstance().saveSQL(new SQLHistory("jdbc:// derby", "select * from TRAVEL.TRIP", DateFormat.getInstance().parse("07/10/96 4:5 PM, PDT")));	136	SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// derby", "select * from TRAVEL.TRIP", new Date()));
165	SQLHistoryManager.getInstance().saveSQL(new SQLHistory("jdbc:// postgres1", "select * from TRAVEL.TRIP", DateFormat.getInstance().parse("07/10/96 4:5 PM, PDT")));	137	SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// postgres1", "select * from TRAVEL.TRIP", new Date()));
166	SQLHistoryManager.getInstance().saveSQL(new SQLHistory("jdbc:// postgres2", "select * from TRAVEL.TRIP", DateFormat.getInstance().parse("07/10/96 4:5 PM, PDT")));	138	SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// postgres2", "select * from TRAVEL.TRIP", new Date()));
167	SQLHistoryManager.getInstance().saveSQL(new SQLHistory("jdbc:// postgres3", "select * from TRAVEL.TRIP", DateFormat.getInstance().parse("07/10/96 4:5 PM, PDT")));	139	SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// postgres3", "select * from TRAVEL.TRIP", new Date()));
168	SQLHistoryManager.getInstance().saveSQL(new SQLHistory("jdbc:// postgres4", "select * from TRAVEL.TRIP", DateFormat.getInstance().parse("07/10/96 4:5 PM, PDT")));	140	SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// postgres4", "select * from TRAVEL.TRIP", new Date()));
169	// Save SQL	141	assertEquals(5, SQLHistoryManager.getInstance().getSQLHistory().size());
170	SQLHistoryManager.getInstance().save(root);	142	SQLHistoryManager.getInstance().setListSize(1);
171	String historyFilePath = FileUtil.getFileDisplayName(root) + File.separator + "Databases" + File.separator + "SQLHISTORY";
172	FileObject historyFileObject = FileUtil.toFileObject(new File(historyFilePath));
173	// Limit to 1 SQL statement
174	SQLHistoryManager.getInstance().updateList(1, historyFilePath , historyFileObject);
175	assertEquals(1, SQLHistoryManager.getInstance().getSQLHistory().size());	143	assertEquals(1, SQLHistoryManager.getInstance().getSQLHistory().size());
176	} catch (SQLHistoryException ex) {
177	Exceptions.printStackTrace(ex);
178	} catch (IOException ex) {
179	Exceptions.printStackTrace(ex);
180	} catch (ParseException ex) {
181	Exceptions.printStackTrace(ex);
182	}
183	}	144	}
184		145
185	public void testUpdateListRemoveAll() {	146	public void testUpdateListRemoveAll() {
186	try {	147	SQLHistoryManager.getInstance().setListSize(100);
187	FileObject root = FileUtil.toFileObject(getWorkDir());	148	SQLHistoryManager.getInstance().getSQLHistory().clear();
188	// Create a list of SQL statements	149	// Create a list of SQL statements
189	SQLHistoryManager.getInstance().saveSQL(new SQLHistory("jdbc:// derby", "select * from TRAVEL.TRIP", DateFormat.getInstance().parse("07/10/96 4:5 PM, PDT")));	150	SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// derby", "select * from TRAVEL.TRIP", new Date()));
190	SQLHistoryManager.getInstance().saveSQL(new SQLHistory("jdbc:// postgres1", "select * from TRAVEL.TRIP", DateFormat.getInstance().parse("07/10/96 4:5 PM, PDT")));	151	SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// postgres1", "select * from TRAVEL.TRIP", new Date()));
191	SQLHistoryManager.getInstance().saveSQL(new SQLHistory("jdbc:// postgres2", "select * from TRAVEL.TRIP", DateFormat.getInstance().parse("07/10/96 4:5 PM, PDT")));	152	SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// postgres2", "select * from TRAVEL.TRIP", new Date()));
192	SQLHistoryManager.getInstance().saveSQL(new SQLHistory("jdbc:// postgres3", "select * from TRAVEL.TRIP", DateFormat.getInstance().parse("07/10/96 4:5 PM, PDT")));	153	SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// postgres3", "select * from TRAVEL.TRIP", new Date()));
193	SQLHistoryManager.getInstance().saveSQL(new SQLHistory("jdbc:// postgres4", "select * from TRAVEL.TRIP", DateFormat.getInstance().parse("07/10/96 4:6 PM, PDT")));	154	SQLHistoryManager.getInstance().saveSQL(new SQLHistoryEntry("jdbc:// postgres4", "select * from TRAVEL.TRIP", new Date()));
194	// Save SQL	155	assertEquals(5, SQLHistoryManager.getInstance().getSQLHistory().size());
195	SQLHistoryManager.getInstance().save(root);	156	SQLHistoryManager.getInstance().setListSize(0);
196	String historyFilePath = FileUtil.getFileDisplayName(root) + File.separator + "Databases" + File.separator + "SQLHISTORY";	157	assertEquals(0, SQLHistoryManager.getInstance().getSQLHistory().size());
197	FileObject historyFileObject = FileUtil.toFileObject(new File(historyFilePath));
198	// Limit to 0 SQL statements
199	SQLHistoryManager.getInstance().updateList(0, historyFilePath, historyFileObject);
200	SQLHistoryPersistenceManager.getInstance().updateSQLSaved(0, historyFileObject);
201	assertEquals(0, SQLHistoryPersistenceManager.getInstance().retrieve(historyFileObject).size());
202	} catch (SQLHistoryException ex) {
203	Exceptions.printStackTrace(ex);
204	} catch (IOException ex) {
205	Exceptions.printStackTrace(ex);
206	} catch (ClassNotFoundException ex) {
207	Exceptions.printStackTrace(ex);
208	} catch (ParseException ex) {
209	Exceptions.printStackTrace(ex);
210	}
211	}	158	}
212
213	}	159	}

Lines 1-74 Link Here

(-)a/db.core/src/org/netbeans/modules/db/sql/history/SQLHistory.java (-60 / +110 lines)
1	/*
2	* DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
3	*
4	* Copyright 2008-2010 Oracle and/or its affiliates. All rights reserved.
5	*
6	* Oracle and Java are registered trademarks of Oracle and/or its affiliates.
7	* Other names may be trademarks of their respective owners.
8	*
9	* The contents of this file are subject to the terms of either the GNU
10	* General Public License Version 2 only ("GPL") or the Common
11	* Development and Distribution License("CDDL") (collectively, the
12	* "License"). You may not use this file except in compliance with the
13	* License. You can obtain a copy of the License at
14	* http://www.netbeans.org/cddl-gplv2.html
15	* or nbbuild/licenses/CDDL-GPL-2-CP. See the License for the
16	* specific language governing permissions and limitations under the
17	* License. When distributing the software, include this License Header
18	* Notice in each file and include the License file at
19	* nbbuild/licenses/CDDL-GPL-2-CP. Oracle designates this
20	* particular file as subject to the "Classpath" exception as provided
21	* by Oracle in the GPL Version 2 section of the License file that
22	* accompanied this code. If applicable, add the following below the
23	* License Header, with the fields enclosed by brackets [] replaced by
24	* your own identifying information:
25	* "Portions Copyrighted [year] [name of copyright owner]"
26	*
27	* If you wish your version of this file to be governed by only the CDDL
28	* or only the GPL Version 2, indicate your decision by adding
29	* "[Contributor] elects to include this software in this distribution
30	* under the [CDDL or GPL Version 2] license." If you do not indicate a
31	* single choice of license, a recipient has the option to distribute
32	* your version of this file under either the CDDL, the GPL Version 2 or
33	* to extend the choice of license to its licensees as provided above.
34	* However, if you add GPL Version 2 code and therefore, elected the GPL
35	* Version 2 license, then the option applies only if the new code is
36	* made subject to such option by the copyright holder.
37	*
38	* Contributor(s):
39	*
40	* Portions Copyrighted 2008-2010 Sun Microsystems, Inc.
41	*/
42		1
43	package org.netbeans.modules.db.sql.history;	2	package org.netbeans.modules.db.sql.history;
44		3
45	import java.util.Date;	4	import java.util.ArrayList;
		5	import java.util.Collection;
		6	import java.util.Collections;
		7	import java.util.Comparator;
		8	import java.util.HashSet;
		9	import java.util.Iterator;
		10	import java.util.List;
		11	import java.util.Set;
		12	import javax.xml.bind.annotation.XmlElement;
		13	import javax.xml.bind.annotation.XmlRootElement;
		14	import javax.xml.bind.annotation.XmlTransient;
46		15
47	/**	16	@XmlRootElement(name="history")
48	*	17	public class SQLHistory implements Set<SQLHistoryEntry> {
49	* @author John Baker	18	@XmlTransient
50	*/	19	private int historyLimit = 100;
51	public class SQLHistory {	20	@XmlElement(name="sql")
52	String sql;	21	private Set<SQLHistoryEntry> history;
53	String url;	22
54	Date date;	23	public SQLHistory() {
		24	history = new HashSet<SQLHistoryEntry>();
		25	}
		26
		27	public String toString() {
		28	return history.toString();
		29	}
		30
		31	public <T> T[] toArray(T[] a) {
		32	return history.toArray(a);
		33	}
		34
		35	public Object[] toArray() {
		36	return history.toArray();
		37	}
		38
		39	public int size() {
		40	return history.size();
		41	}
		42
		43	public boolean retainAll(Collection<?> c) {
		44	return history.retainAll(c);
		45	}
		46
		47	public boolean removeAll(Collection<?> c) {
		48	return history.removeAll(c);
		49	}
		50
		51	public boolean remove(Object o) {
		52	return history.remove(o);
		53	}
		54
		55	public Iterator<SQLHistoryEntry> iterator() {
		56	return history.iterator();
		57	}
55		58
56	public SQLHistory(String url, String sql, Date date) {	59	public boolean isEmpty() {
57	this.url = url;	60	return history.isEmpty();
58	this.sql = sql;
59	this.date = date;
60	}	61	}
61		62
62	public String getUrl() {	63	public int hashCode() {
63	return url;	64	return history.hashCode();
64	}	65	}
65		66
66	public String getSql() {	67	public boolean equals(Object o) {
67	return sql;	68	return history.equals(o);
68	}	69	}
69		70
70	public Date getDate() {	71	public boolean containsAll(Collection<?> c) {
71	return date;	72	return history.containsAll(c);
72	}	73	}
73		74
		75	public boolean contains(Object o) {
		76	return history.contains(o);
		77	}
		78
		79	public void clear() {
		80	history.clear();
		81	}
		82
		83	public boolean addAll(Collection<? extends SQLHistoryEntry> c) {
		84	boolean changed = false;
		85	for(SQLHistoryEntry sqe: c) {
		86	changed \|= this.add(sqe);
		87	}
		88	return changed;
		89	}
		90
		91	public boolean add(SQLHistoryEntry e) {
		92	boolean result = history.add(e);
		93	if(! result) {
		94	history.remove(e);
		95	result = history.add(e);
		96	}
		97	enforceLimit();
		98	return result;
		99	}
		100
		101	public void enforceLimit() {
		102	if(size() > historyLimit) {
		103	List<SQLHistoryEntry> list = new ArrayList<SQLHistoryEntry>(history);
		104	Collections.sort(list, new Comparator<SQLHistoryEntry>() {
		105	@Override
		106	public int compare(SQLHistoryEntry o1, SQLHistoryEntry o2) {
		107	return o2.getDate().compareTo(o1.getDate());
		108	}
		109	});
		110	history.clear();
		111	history.addAll(list.subList(0, historyLimit));
		112	}
		113	}
		114
		115	@XmlTransient
		116	public int getHistoryLimit() {
		117	return historyLimit;
		118	}
		119
		120	public void setHistoryLimit(int historyLimit) {
		121	this.historyLimit = historyLimit;
		122	enforceLimit();
		123	}
74	}	124	}

Line 0 Link Here

(-)a/db.core/src/org/netbeans/modules/db/sql/history/SQLHistoryEntry.java (+152 lines)
		1	/*
		2	* DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
		3	*
		4	* Copyright 2011 Oracle and/or its affiliates. All rights reserved.
		5	*
		6	* Oracle and Java are registered trademarks of Oracle and/or its affiliates.
		7	* Other names may be trademarks of their respective owners.
		8	*
		9	* The contents of this file are subject to the terms of either the GNU
		10	* General Public License Version 2 only ("GPL") or the Common
		11	* Development and Distribution License("CDDL") (collectively, the
		12	* "License"). You may not use this file except in compliance with the
		13	* License. You can obtain a copy of the License at
		14	* http://www.netbeans.org/cddl-gplv2.html
		15	* or nbbuild/licenses/CDDL-GPL-2-CP. See the License for the
		16	* specific language governing permissions and limitations under the
		17	* License. When distributing the software, include this License Header
		18	* Notice in each file and include the License file at
		19	* nbbuild/licenses/CDDL-GPL-2-CP. Oracle designates this
		20	* particular file as subject to the "Classpath" exception as provided
		21	* by Oracle in the GPL Version 2 section of the License file that
		22	* accompanied this code. If applicable, add the following below the
		23	* License Header, with the fields enclosed by brackets [] replaced by
		24	* your own identifying information:
		25	* "Portions Copyrighted [year] [name of copyright owner]"
		26	*
		27	* If you wish your version of this file to be governed by only the CDDL
		28	* or only the GPL Version 2, indicate your decision by adding
		29	* "[Contributor] elects to include this software in this distribution
		30	* under the [CDDL or GPL Version 2] license." If you do not indicate a
		31	* single choice of license, a recipient has the option to distribute
		32	* your version of this file under either the CDDL, the GPL Version 2 or
		33	* to extend the choice of license to its licensees as provided above.
		34	* However, if you add GPL Version 2 code and therefore, elected the GPL
		35	* Version 2 license, then the option applies only if the new code is
		36	* made subject to such option by the copyright holder.
		37	*
		38	* Contributor(s):
		39	*
		40	* Portions Copyrighted 2011 Sun Microsystems, Inc.
		41	*/
		42	package org.netbeans.modules.db.sql.history;
		43
		44	import java.text.DateFormat;
		45	import java.text.ParseException;
		46	import java.util.Date;
		47	import javax.xml.bind.annotation.XmlAttribute;
		48	import javax.xml.bind.annotation.XmlTransient;
		49	import javax.xml.bind.annotation.XmlType;
		50	import javax.xml.bind.annotation.XmlValue;
		51
		52	@XmlType(name = "sql")
		53	public class SQLHistoryEntry {
		54
		55	private String url;
		56	private String sql;
		57	private Date date;
		58
		59	protected SQLHistoryEntry() {
		60	}
		61
		62	public SQLHistoryEntry(String url, String sql, Date date) {
		63	this.url = url;
		64	this.sql = sql;
65	this.date = date;
66	}
67
68	@XmlTransient
69	public Date getDate() {
70	return date;
71	}
72
73	protected void setDate(Date date) {
74	this.date = date;
75	}
76
77	@XmlValue
78	public String getSql() {
79	return sql;
80	}
81
82	protected void setSql(String sql) {
83	if (sql != null) {
84	this.sql = sql.trim();
85	} else {
86	this.sql = sql;
87	}
88	}
89
90	@XmlAttribute
91	public String getUrl() {
92	return url;
93	}
94
95	protected void setUrl(String url) {
96	this.url = url;
97	}
98
99	@XmlAttribute(name = "date")
100	protected String getDateXMLVariant() {
101	if (this.date == null) {
102	return null;
103	} else {
104	return Long.toString(date.getTime());
105	}
106	}
107
108	protected void setDateXMLVariant(String value) {
109	try {
110	date = new Date(Long.parseLong(value));
111	} catch (NumberFormatException nfe) {
112	// #152486 - previously date stored in text format
113	try {
114	date = DateFormat.getDateTimeInstance(DateFormat.SHORT, DateFormat.SHORT).parse(value);
115	} catch (ParseException pe) {
116	// # 152486; Date stored is not parsable, so reset the date to the current timestamp
117	date = new Date();
118	}
119	}
120
121	}
122
123	@Override
124	public String toString() {
125	return "SQLHistoryEntry{" + "url=" + url + ", sql=" + sql + ", date=" + date + '}';
126	}
127
128	@Override
129	public boolean equals(Object obj) {
130	if (obj == null) {
131	return false;
132	}
133	if (getClass() != obj.getClass()) {
134	return false;
135	}
136	final SQLHistoryEntry other = (SQLHistoryEntry) obj;
137	if ((this.url == null) ? (other.url != null) : !this.url.toLowerCase().equals(other.url.toLowerCase())) {
138	return false;
139	}
140	if ((this.sql == null) ? (other.sql != null) : !this.sql.toLowerCase().equals(other.sql.toLowerCase())) {
141	return false;
142	}
143	return true;
144	}
145
146	@Override
147	public int hashCode() {
148	int hash = 7;
149	hash = 37 * hash + (this.sql != null ? this.sql.toLowerCase().hashCode() : 0);
150	return hash;
151	}
152	}

Lines 1-58 Link Here

(-)a/db.core/src/org/netbeans/modules/db/sql/history/SQLHistoryException.java (-58 lines)
1	/*
2	* DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
3	*
4	* Copyright 2010 Oracle and/or its affiliates. All rights reserved.
5	*
6	* Oracle and Java are registered trademarks of Oracle and/or its affiliates.
7	* Other names may be trademarks of their respective owners.
8	*
9	* The contents of this file are subject to the terms of either the GNU
10	* General Public License Version 2 only ("GPL") or the Common
11	* Development and Distribution License("CDDL") (collectively, the
12	* "License"). You may not use this file except in compliance with the
13	* License. You can obtain a copy of the License at
14	* http://www.netbeans.org/cddl-gplv2.html
15	* or nbbuild/licenses/CDDL-GPL-2-CP. See the License for the
16	* specific language governing permissions and limitations under the
17	* License. When distributing the software, include this License Header
18	* Notice in each file and include the License file at
19	* nbbuild/licenses/CDDL-GPL-2-CP. Oracle designates this
20	* particular file as subject to the "Classpath" exception as provided
21	* by Oracle in the GPL Version 2 section of the License file that
22	* accompanied this code. If applicable, add the following below the
23	* License Header, with the fields enclosed by brackets [] replaced by
24	* your own identifying information:
25	* "Portions Copyrighted [year] [name of copyright owner]"
26	*
27	* If you wish your version of this file to be governed by only the CDDL
28	* or only the GPL Version 2, indicate your decision by adding
29	* "[Contributor] elects to include this software in this distribution
30	* under the [CDDL or GPL Version 2] license." If you do not indicate a
31	* single choice of license, a recipient has the option to distribute
32	* your version of this file under either the CDDL, the GPL Version 2 or
33	* to extend the choice of license to its licensees as provided above.
34	* However, if you add GPL Version 2 code and therefore, elected the GPL
35	* Version 2 license, then the option applies only if the new code is
36	* made subject to such option by the copyright holder.
37	*
38	* Contributor(s):
39	*
40	* Portions Copyrighted 2008 Sun Microsystems, Inc.
41	*/
42
43	package org.netbeans.modules.db.sql.history;
44
45	/**
46	*
47	* @author jbaker
48	*/
49	public class SQLHistoryException extends Exception {
50	SQLHistoryException() {
51	}
52
53	SQLHistoryException(String msg) {
54	}
55
56	SQLHistoryException(String msg, Throwable cause) {
57	}
58	}

Lines 1-572 Link Here

(-)a/db.core/src/org/netbeans/modules/db/sql/history/SQLHistoryPersistenceManager.java (-572 lines)
1	/*
2	* DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
3	*
4	* Copyright 1997-2010 Oracle and/or its affiliates. All rights reserved.
5	*
6	* Oracle and Java are registered trademarks of Oracle and/or its affiliates.
7	* Other names may be trademarks of their respective owners.
8	*
9	* The contents of this file are subject to the terms of either the GNU
10	* General Public License Version 2 only ("GPL") or the Common
11	* Development and Distribution License("CDDL") (collectively, the
12	* "License"). You may not use this file except in compliance with the
13	* License. You can obtain a copy of the License at
14	* http://www.netbeans.org/cddl-gplv2.html
15	* or nbbuild/licenses/CDDL-GPL-2-CP. See the License for the
16	* specific language governing permissions and limitations under the
17	* License. When distributing the software, include this License Header
18	* Notice in each file and include the License file at
19	* nbbuild/licenses/CDDL-GPL-2-CP. Oracle designates this
20	* particular file as subject to the "Classpath" exception as provided
21	* by Oracle in the GPL Version 2 section of the License file that
22	* accompanied this code. If applicable, add the following below the
23	* License Header, with the fields enclosed by brackets [] replaced by
24	* your own identifying information:
25	* "Portions Copyrighted [year] [name of copyright owner]"
26	*
27	* Contributor(s):
28	*
29	* The Original Software is NetBeans. The Initial Developer of the Original
30	* Software is Sun Microsystems, Inc. Portions Copyright 1997-2010 Sun
31	* Microsystems, Inc. All Rights Reserved.
32	*
33	* If you wish your version of this file to be governed by only the CDDL
34	* or only the GPL Version 2, indicate your decision by adding
35	* "[Contributor] elects to include this software in this distribution
36	* under the [CDDL or GPL Version 2] license." If you do not indicate a
37	* single choice of license, a recipient has the option to distribute
38	* your version of this file under either the CDDL, the GPL Version 2 or
39	* to extend the choice of license to its licensees as provided above.
40	* However, if you add GPL Version 2 code and therefore, elected the GPL
41	* Version 2 license, then the option applies only if the new code is
42	* made subject to such option by the copyright holder.
43	*/
44	package org.netbeans.modules.db.sql.history;
45
46	import java.io.File;
47	import java.io.IOException;
48	import java.io.InputStream;
49	import java.io.OutputStream;
50	import java.io.OutputStreamWriter;
51	import java.io.PrintWriter;
52	import java.text.DateFormat;
53	import java.text.ParseException;
54	import java.util.ArrayList;
55	import java.util.Calendar;
56	import java.util.Collections;
57	import java.util.Date;
58	import java.util.List;
59	import org.openide.util.Exceptions;
60	import org.xml.sax.Attributes;
61	import java.util.logging.Level;
62	import java.util.logging.Logger;
63	import javax.xml.parsers.DocumentBuilder;
64	import javax.xml.parsers.DocumentBuilderFactory;
65	import javax.xml.parsers.ParserConfigurationException;
66	import org.openide.filesystems.FileLock;
67	import org.openide.filesystems.FileObject;
68	import org.openide.filesystems.FileSystem;
69	import org.openide.filesystems.FileUtil;
70	import org.openide.loaders.DataFolder;
71	import org.openide.loaders.XMLDataObject;
72	import org.openide.util.NbPreferences;
73	import org.openide.xml.EntityCatalog;
74	import org.openide.xml.XMLUtil;
75	import org.w3c.dom.Document;
76	import org.w3c.dom.Element;
77	import org.w3c.dom.NamedNodeMap;
78	import org.w3c.dom.Node;
79	import org.w3c.dom.NodeList;
80	import org.w3c.dom.Text;
81	import org.xml.sax.ContentHandler;
82	import org.xml.sax.InputSource;
83	import org.xml.sax.SAXException;
84	import org.xml.sax.XMLReader;
85	import org.xml.sax.helpers.DefaultHandler;
86
87	/**
88	* Manage creation and updates to persisted SQL statements
89	*
90	* @author John Baker
91	*/
92	public class SQLHistoryPersistenceManager {
93	private static final int READ = 0;
94	private static final int ADD = 1;
95	private static final int REMOVE = 2;
96	private static final int CREATE = 3;
97	public static final Logger LOGGER = Logger.getLogger(SQLHistoryPersistenceManager.class.getName());
98	private static SQLHistoryPersistenceManager _instance = null;
99	private static Document document;
100	private List<SQLHistory> sqlHistoryList;
101	private int numElemsToRemove = 0;
102
103	private SQLHistoryPersistenceManager() {
104	}
105
106	public static SQLHistoryPersistenceManager getInstance() {
107	if (null == _instance) {
108	_instance = new SQLHistoryPersistenceManager();
109	}
110	return _instance;
111	}
112
113	public void removeHistoryFile(FileObject historyRoot) {
114	try {
115	FileObject folder = DataFolder.findFolder(historyRoot).getPrimaryFile();
116	String fn = FileUtil.getFileDisplayName(folder) + File.separator + SQLHistoryManager.SQL_HISTORY_FILE_NAME;
117	FileObject historyFo = FileUtil.toFileObject(FileUtil.normalizeFile(new File(fn)));
118	historyFo.delete();
119	} catch (IOException ex) {
120	Exceptions.printStackTrace(ex);
121	}
122
123	}
124
125	public void create(FileObject historyFileObject, List<SQLHistory> sqlHistoryList) throws SQLHistoryException {
126	try {
127	this.sqlHistoryList = sqlHistoryList;
128	DataFolder df = DataFolder.findFolder(historyFileObject);
129	int action = df.getChildren().length == 0 ? CREATE : ADD;
130	AtomicFileAction writer = new AtomicFileAction(df, null, action, sqlHistoryList);
131	df.getPrimaryFile().getFileSystem().runAtomicAction(writer);
132	} catch (IOException ex) {
133	throw new SQLHistoryException(ex.getLocalizedMessage(), ex);
134	}
135	}
136
137	public List<SQLHistory> retrieve(FileObject historyFileObject) throws ClassNotFoundException, SQLHistoryException {
138	Handler handler = null;
139	int limit;
140	String limitUser = NbPreferences.forModule(SQLHistoryPersistenceManager.class).get("SQL_STATEMENTS_SAVED_FOR_HISTORY", "");
141	if (!limitUser.isEmpty()) { // NOI18N
142	try {
143	limit = Integer.parseInt(limitUser);
144	} catch (NumberFormatException nfe) {
145	limit = Integer.parseInt(SQLHistoryManager.SAVE_STATEMENTS_MAX_LIMIT_ENTERED);
146	}
147	} else {
148	limit = Integer.parseInt(SQLHistoryManager.SAVE_STATEMENTS_MAX_LIMIT_ENTERED);
149	}
150	try {
151	handler = new Handler(limit);
152	DataFolder df = DataFolder.findFolder(historyFileObject);
153	AtomicFileAction reader = new AtomicFileAction(df, handler, READ, null);
154	df.getPrimaryFile().getFileSystem().runAtomicAction(reader);
155
156	} catch (IOException ex) {
157	LOGGER.log(Level.FINE, ex.getLocalizedMessage(), ex);
158	sqlHistoryList = handler.getXmlSqlHistoryList();
159	throw new SQLHistoryException();
160	}
161	if (handler != null) {
162	return handler.getXmlSqlHistoryList();
163	} else {
164	return new ArrayList<SQLHistory>();
165	}
166	}
167
168	public List<SQLHistory> retrieve() {
169	return sqlHistoryList;
170	}
171
172	public void setNumElemsToRemove(int elemsToRemove) {
173	numElemsToRemove = elemsToRemove;
174	}
175
176	public int getNumElemsToRemove() {
177	return numElemsToRemove;
178	}
179
180	public List<SQLHistory> updateSQLSaved(int limit, FileObject historyFileObject) throws SQLHistoryException {
181	List<SQLHistory> updatedSQLHistoryList = null;
182	try {
183	if (historyFileObject == null) {
184	return new ArrayList<SQLHistory>();
185	}
186	updatedSQLHistoryList = retrieve(historyFileObject);
187	// Remove elements from list based on the number of statements to save that is set in the SQL History dialog
188	if (limit < updatedSQLHistoryList.size()) {
189	numElemsToRemove = updatedSQLHistoryList.size() - limit;
190	boolean containsElems = true;
191	containsElems = !updatedSQLHistoryList.isEmpty();
192	if (containsElems && numElemsToRemove == updatedSQLHistoryList.size()) {
193	// remove all
194	DataFolder df = DataFolder.findFolder(historyFileObject);
195	List<SQLHistory> emptyList = Collections.emptyList();
196	AtomicFileAction writer = new AtomicFileAction(df, null, CREATE, emptyList);
197	df.getPrimaryFile().getFileSystem().runAtomicAction(writer);
198	} else if (containsElems && (limit == 0 \|\| numElemsToRemove >= 0)) {
199	DataFolder df = DataFolder.findFolder(historyFileObject);
200	AtomicFileAction modifier = new AtomicFileAction(df, null, REMOVE, updatedSQLHistoryList);
201	df.getPrimaryFile().getFileSystem().runAtomicAction(modifier);
202	}
203	}
204	updatedSQLHistoryList = retrieve(historyFileObject);
205	} catch (ClassNotFoundException ex) {
206	throw new SQLHistoryException(ex.getLocalizedMessage(), ex);
207	} catch (IOException ex) {
208	throw new SQLHistoryException(ex.getLocalizedMessage(), ex);
209	}
210	return updatedSQLHistoryList;
211	}
212
213	private static final class AtomicFileAction implements FileSystem.AtomicAction {
214	List<SQLHistory> sqlHistoryList;
215	DataFolder parent;
216	boolean remove;
217	FileObject data;
218	Handler handler;
219	int actionType;
220
221	AtomicFileAction(DataFolder parent, Handler handler, int actionType, List<SQLHistory> sqlHistoryList) {
222	this.parent = parent;
223	this.handler = handler;
224	this.sqlHistoryList = sqlHistoryList;
225	this.actionType = actionType;
226	}
227
228	@Override
229	public void run() throws IOException {
230	FileLock lck = null;
231	OutputStream ostm = null;
232	PrintWriter writer = null;
233	XmlWriter xmlWriter = null;
234	DocumentBuilderFactory factory = null;
235	DocumentBuilder builder = null;
236	try {
237	FileObject folder = parent.getPrimaryFile();
238	String fn = FileUtil.getFileDisplayName(folder) + File.separator + SQLHistoryManager.SQL_HISTORY_FILE_NAME;
239	// Read, Write or Update the persisted SQL file
240	switch (actionType) {
241	case READ:
242	FileObject historyFo = FileUtil.toFileObject(FileUtil.normalizeFile(new File(fn)));
243	XMLDataObject obj = (XMLDataObject) XMLDataObject.find(historyFo);
244	InputSource inputSource = new InputSource(obj.getPrimaryFile().getInputStream());
245	inputSource.setSystemId(historyFo.getURL().toExternalForm());
246	XMLReader reader = XMLUtil.createXMLReader();
247	reader.setContentHandler(handler);
248	reader.setErrorHandler(handler);
249	reader.setEntityResolver(EntityCatalog.getDefault());
250	reader.parse(inputSource);
251	break;
252	case ADD:
253	factory = DocumentBuilderFactory.newInstance();
254	builder = factory.newDocumentBuilder();
255	data = FileUtil.toFileObject(FileUtil.normalizeFile(new File(fn)));
256	InputStream inputStream = data.getInputStream();
257	document = builder.parse(inputStream);
258	inputStream.close();
259	lck = data.lock();
260	ostm = data.getOutputStream(lck);
261	writer = new PrintWriter(new OutputStreamWriter(ostm, "UTF8")); //NOI18N
262	// Create or update then write the DOM
263	xmlWriter = new XmlWriter(data, sqlHistoryList, writer);
264	xmlWriter.write(xmlWriter.createElements(document), ""); // NOI18N
265	break;
266	case CREATE:
267	factory = DocumentBuilderFactory.newInstance();
268	builder = factory.newDocumentBuilder();
269	if (new File(fn).exists()) {
270	data = folder.getFileObject(SQLHistoryManager.SQL_HISTORY_FILE_NAME);
271	} else {
272	data = folder.createData(SQLHistoryManager.SQL_HISTORY_FILE_NAME);
273	}
274	lck = data.lock();
275	ostm = data.getOutputStream(lck);
276	writer = new PrintWriter(new OutputStreamWriter(ostm, "UTF8")); //NOI18N
277	document = builder.newDocument();
278	xmlWriter = new XmlWriter(data, sqlHistoryList, writer);
279	xmlWriter.write();
280	// Create or update then write the DOM
281	xmlWriter = new XmlWriter(data, sqlHistoryList, writer);
282	xmlWriter.write(xmlWriter.createElements(document), ""); // NOI18N
283	break;
284	case REMOVE:
285	factory = DocumentBuilderFactory.newInstance();
286	builder = factory.newDocumentBuilder();
287	if (folder.getChildren().length > 0) {
288	data = FileUtil.toFileObject(FileUtil.normalizeFile(new File(fn)));
289	InputStream is = data.getInputStream();
290	document = builder.parse(is);
291	is.close();
292	lck = data.lock();
293	ostm = data.getOutputStream(lck);
294	writer = new PrintWriter(new OutputStreamWriter(ostm, "UTF8")); //NOI18N
295	}
296	// Create or update then write the DOM
297	xmlWriter = new XmlWriter(data, sqlHistoryList, writer);
298	xmlWriter.write(); // NOI18N
299	xmlWriter.write(xmlWriter.removeElements(document), ""); // NOI18N
300	break;
301	}
302	} catch (ParserConfigurationException ex) {
303	LOGGER.log(Level.INFO, ex.getMessage());
304	throw new IOException(ex.getLocalizedMessage(), ex);
305
306	} catch (SAXException ex) {
307	LOGGER.log(Level.INFO, ex.getMessage());
308	throw new IOException(ex.getLocalizedMessage(),ex);
309	} finally {
310	if (writer != null) {
311	writer.flush();
312	writer.close();
313	writer = null;
314	}
315	if (ostm != null) {
316	ostm.close();
317	ostm = null;
318	}
319	if (lck != null) {
320	lck.releaseLock();
321	}
322	}
323	}
324	}
325
326	private static final class XmlWriter {
327	private PrintWriter pw;
328	private List<SQLHistory> sqlHistoryList;
329
330	public XmlWriter(FileObject data, List<SQLHistory> sqlHistoryList, PrintWriter pw) {
331	this.sqlHistoryList = sqlHistoryList;
332	this.pw = pw;
333	}
334
335	/**
336	*
337	* create XML elements from SQL statements
338	*/
339	private Node createElements(Document document) {
340	Element newNode = null;
341	Element nameNode = null;
342	if (null == document.getDocumentElement()) {
343	newNode = document.createElement("history"); // NOI18N
344	for (SQLHistory sqlHistory : sqlHistoryList) {
345	nameNode = document.createElement("sql"); // NOI18N
346	nameNode.appendChild(document.createTextNode(sqlHistory.getSql()));
347	nameNode.setAttribute("url", sqlHistory.getUrl()); // NOI18N
348	nameNode.setAttribute("date", new Long(sqlHistory.getDate().getTime()).toString()); // NOI18N
349	newNode.appendChild(nameNode);
350	}
351	document.adoptNode(newNode);
352	} else {
353	newNode = document.getDocumentElement();
354	for (SQLHistory sqlHistory : sqlHistoryList) {
355	nameNode = document.createElement("sql"); // NOI18N
356	nameNode.appendChild(document.createTextNode(sqlHistory.getSql()));
357	nameNode.setAttribute("url", sqlHistory.getUrl()); // NOI18N
358	nameNode.setAttribute("date", new Long(sqlHistory.getDate().getTime()).toString()); // NOI18N
359	newNode.insertBefore(nameNode, newNode.getFirstChild());
360	}
361	}
362	return newNode;
363	}
364
365	/**
366	*
367	* remove XML elements when the number of statements to save is reduced in the SQL History dialog
368	*/
369	private Node removeElements(Document document) {
370	NodeList nodes = null;
371	Element history = document.getDocumentElement();
372	if (null != history) {
373	nodes = history.getElementsByTagName("sql");
374	int elemsToRemove = SQLHistoryPersistenceManager.getInstance().getNumElemsToRemove();
375	// Statements to save was set to 0
376	if (elemsToRemove == 0) {
377	for (int i = 0; i < nodes.getLength(); i++) {
378	if (nodes.item(0) != null) {
379	history.removeChild(nodes.item(0));
380	}
381	}
382	}
383	// Remove elements from the DOM
384	for (int i = 0; i < elemsToRemove; i++) {
385	if (nodes.item(0) != null) {
386	history.removeChild(nodes.item(nodes.getLength() - 1));
387	}
388	}
389	}
390	return history;
391	}
392
393	private void write() {
394	pw.println("<?xml version='1.0' encoding='UTF-8' ?>");
395	}
396
397	/**
398	*
399	* write the SQL statements as xml
400	*/
401	private void write(Node node, String indent) {
402	switch (node.getNodeType()) {
403	case Node.DOCUMENT_NODE: {
404	Document doc = (Document) node;
405	pw.println(indent + "<?xml version='1.0'?>"); // NOI18N
406	Node child = doc.getFirstChild();
407	while (child != null) {
408	write(child, indent);
409	child = child.getNextSibling();
410	}
411	break;
412	}
413	case Node.ELEMENT_NODE: {
414	Element elt = (Element) node;
415	pw.print(indent + "<" + elt.getTagName());
416	NamedNodeMap attrs = elt.getAttributes();
417	for (int i = 0; i < attrs.getLength(); i++) {
418	Node a = attrs.item(i);
419	pw.print(" " + a.getNodeName() + "='" + fixup(a.getNodeValue()) + "'"); // NOI18N
420	}
421	pw.println(">"); // NOI18N
422	String newindent = indent + " "; // NOI18N
423	Node child = elt.getFirstChild();
424	while (child != null) {
425	write(child, newindent);
426	child = child.getNextSibling();
427	}
428
429	pw.println(indent + "</" + elt.getTagName() + ">"); // NOI18N
430	break;
431	}
432	case Node.TEXT_NODE: {
433	Text textNode = (Text) node;
434	String text = textNode.getData().trim();
435	if ((text != null) && text.length() > 0) {
436	pw.println(indent + fixup(text));
437	}
438	break;
439	}
440	default:
441	LOGGER.log(Level.INFO, "Ignoring node: " + node.getClass().getName()); // NOI18N
442	break;
443	}
444
445	}
446
447	private String fixup(String s) {
448	StringBuilder sb = new StringBuilder();
449	int len = s.length();
450	for (int i = 0; i < len; i++) {
451	char c = s.charAt(i);
452	switch (c) {
453	default:
454	sb.append(c);
455	break;
456	case '<': // NOI18N
457	sb.append("<"); // NOI18N
458	break;
459	case '>': // NOI18N
460	sb.append(">"); // NOI18N
461	break;
462	case '&': // NOI18N
463	sb.append("&"); // NOI18N
464	break;
465	case '"': // NOI18N
466	sb.append("""); // NOI18N
467	break;
468	case '\'': // NOI18N
469	sb.append("'"); // NOI18N
470	break;
471	}
472	}
473	return sb.toString();
474	}
475	}
476
477	/**
478	* SAX handler for reading the XML file.
479	*/
480	private static final class Handler extends DefaultHandler implements ContentHandler {
481
482	private static final String ELEMENT_SQL = "sql"; // NOI18N
483	private static final String ATTR_URL_PROPERTY_VALUE = "url"; // NOI18N
484	private static final String ATTR_DATE_PROPERTY_VALUE = "date"; // NOI18N
485	private static String url;
486	private static StringBuilder sql;
487	private static Date date;
488	boolean matchingUrl = false;
489	private List<SQLHistory> xmlSqlHistoryList = new ArrayList<SQLHistory>();
490	static boolean isSql = false;
491	private int limit;
492	private static Calendar calendar = Calendar.getInstance();
493
494	public Handler(int limit) {
495	this.limit = limit;
496	}
497
498	@Override
499	public void startElement(String uri, String localName, String qName, Attributes attrs) throws SAXException {
500	if (ELEMENT_SQL.equals(qName)) {
501	isSql = true;
502	url = attrs.getValue(ATTR_URL_PROPERTY_VALUE);
503	try {
504	calendar.setTimeInMillis(Long.parseLong(attrs.getValue(ATTR_DATE_PROPERTY_VALUE)));
505	date = calendar.getTime();
506	} catch (NumberFormatException nfe) {
507	// #152486 - previously date stored in text format
508	try {
509	date = DateFormat.getDateTimeInstance(DateFormat.SHORT, DateFormat.SHORT).parse(attrs.getValue(ATTR_DATE_PROPERTY_VALUE));
510	} catch (ParseException pe) {
511	// # 152486; Date stored is not parsable, so reset the date to the current timestamp
512	date = calendar.getTime();
513	}
514	}
515	} else {
516	isSql = false;
517	}
518	}
519
520	@Override
521	public void endElement(String uri, String localName, String qName) {
522	if (ELEMENT_SQL.equals(qName)) {
523	if (url != null && sql != null && date != null) {
524	addHistory(url, sql.toString(), date);
525	reset();
526	}
527	}
528	}
529
530	private static void reset() {
531	// reset data
532	url = null;
533	date = null;
534	sql = null;
535	}
536
537	private void addHistory(String url, String sql, Date date) {
538	if ((xmlSqlHistoryList.size() <= limit) \|\| limit == 0) {
539	xmlSqlHistoryList.add(new SQLHistory(url, sql, date));
540	setXmlSqlHistoryList(xmlSqlHistoryList);
541	} else {
542	// remove a statement from the end of the list
543	xmlSqlHistoryList.remove(xmlSqlHistoryList.size() - 1);
544	}
545	}
546
547	@Override
548	public void characters(char buf[], int offset, int length) {
549	if (isSql) {
550	String parsedValue = new String(buf, offset, length);
551	if (sql == null) {
552	sql = new StringBuilder();
553	sql.append(parsedValue);
554	} else {
555	sql.append(parsedValue);
556	}
557	}
558	}
559
560	public void setXmlSqlHistoryList(List<SQLHistory> sqlHistoryList) {
561	xmlSqlHistoryList = sqlHistoryList;
562	}
563
564	public List<SQLHistory> getXmlSqlHistoryList() {
565	if (xmlSqlHistoryList == null) {
566	return new ArrayList<SQLHistory>();
567	} else {
568	return xmlSqlHistoryList;
569	}
570	}
571	}
572	}