Autopsy  4.8.0
Graphical digital forensics platform for The Sleuth Kit and other tools.
SQLiteViewer.java
Go to the documentation of this file.
1 /*
2  * Autopsy Forensic Browser
3  *
4  * Copyright 2018 Basis Technology Corp.
5  * Contact: carrier <at> sleuthkit <dot> org
6  *
7  * Licensed under the Apache License, Version 2.0 (the "License");
8  * you may not use this file except in compliance with the License.
9  * You may obtain a copy of the License at
10  *
11  * http://www.apache.org/licenses/LICENSE-2.0
12  *
13  * Unless required by applicable law or agreed to in writing, software
14  * distributed under the License is distributed on an "AS IS" BASIS,
15  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
16  * See the License for the specific language governing permissions and
17  * limitations under the License.
18  */
19 package org.sleuthkit.autopsy.contentviewers;
20 
21 import java.awt.BorderLayout;
22 import java.awt.Component;
23 import java.awt.Cursor;
24 import java.io.File;
25 import java.io.FileOutputStream;
26 import java.io.IOException;
27 import java.sql.Connection;
28 import java.sql.DriverManager;
29 import java.sql.ResultSet;
30 import java.sql.ResultSetMetaData;
31 import java.sql.SQLException;
32 import java.sql.Statement;
33 import java.util.ArrayList;
34 import java.util.Arrays;
35 import java.util.Collection;
36 import java.util.Collections;
37 import java.util.LinkedHashMap;
38 import java.util.LinkedList;
39 import java.util.List;
40 import java.util.Map;
41 import java.util.Objects;
42 import java.util.logging.Level;
43 import javax.swing.JComboBox;
44 import javax.swing.JFileChooser;
45 import javax.swing.JOptionPane;
46 import javax.swing.filechooser.FileNameExtensionFilter;
47 import org.apache.commons.io.FilenameUtils;
48 import org.openide.util.NbBundle;
49 import org.openide.windows.WindowManager;
50 import org.sleuthkit.autopsy.casemodule.Case;
51 import org.sleuthkit.autopsy.casemodule.NoCurrentCaseException;
52 import org.sleuthkit.autopsy.coreutils.Logger;
53 import org.sleuthkit.datamodel.AbstractFile;
54 import org.sleuthkit.datamodel.TskCoreException;
55 import org.sleuthkit.autopsy.coreutils.MessageNotifyUtil;
56 import org.sleuthkit.autopsy.coreutils.SqliteUtil;
57 
61 @SuppressWarnings("PMD.SingularField") // UI widgets cause lots of false positives
62 class SQLiteViewer extends javax.swing.JPanel implements FileTypeViewer {
63 
64  private static final long serialVersionUID = 1L;
65  public static final String[] SUPPORTED_MIMETYPES = new String[]{"application/x-sqlite3"};
66  private static final int ROWS_PER_PAGE = 100;
67  private static final Logger logger = Logger.getLogger(FileViewer.class.getName());
68  private final SQLiteTableView selectedTableView = new SQLiteTableView();
69  private AbstractFile sqliteDbFile;
70  private File tmpDbFile;
71  private Connection connection;
72  private int numRows; // num of rows in the selected table
73  private int currPage = 0; // curr page of rows being displayed
74 
78  public SQLiteViewer() {
79  initComponents();
80  jTableDataPanel.add(selectedTableView, BorderLayout.CENTER);
81  }
82 
88  @SuppressWarnings("unchecked")
89  // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
90  private void initComponents() {
91 
92  jHdrPanel = new javax.swing.JPanel();
93  tablesDropdownList = new javax.swing.JComboBox<>();
94  jLabel1 = new javax.swing.JLabel();
95  numEntriesField = new javax.swing.JTextField();
96  jLabel2 = new javax.swing.JLabel();
97  currPageLabel = new javax.swing.JLabel();
98  jLabel3 = new javax.swing.JLabel();
99  numPagesLabel = new javax.swing.JLabel();
100  prevPageButton = new javax.swing.JButton();
101  nextPageButton = new javax.swing.JButton();
102  exportCsvButton = new javax.swing.JButton();
103  jTableDataPanel = new javax.swing.JPanel();
104 
105  jHdrPanel.setPreferredSize(new java.awt.Dimension(536, 40));
106 
107  tablesDropdownList.setModel(new javax.swing.DefaultComboBoxModel<>(new String[] { "Item 1", "Item 2", "Item 3", "Item 4" }));
108  tablesDropdownList.addActionListener(new java.awt.event.ActionListener() {
109  public void actionPerformed(java.awt.event.ActionEvent evt) {
110  tablesDropdownListActionPerformed(evt);
111  }
112  });
113 
114  org.openide.awt.Mnemonics.setLocalizedText(jLabel1, org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.jLabel1.text")); // NOI18N
115 
116  numEntriesField.setEditable(false);
117  numEntriesField.setText(org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.numEntriesField.text")); // NOI18N
118  numEntriesField.setBorder(null);
119 
120  org.openide.awt.Mnemonics.setLocalizedText(jLabel2, org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.jLabel2.text")); // NOI18N
121 
122  org.openide.awt.Mnemonics.setLocalizedText(currPageLabel, org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.currPageLabel.text")); // NOI18N
123 
124  org.openide.awt.Mnemonics.setLocalizedText(jLabel3, org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.jLabel3.text")); // NOI18N
125 
126  org.openide.awt.Mnemonics.setLocalizedText(numPagesLabel, org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.numPagesLabel.text")); // NOI18N
127 
128  prevPageButton.setIcon(new javax.swing.ImageIcon(getClass().getResource("/org/sleuthkit/autopsy/corecomponents/btn_step_back.png"))); // NOI18N
129  org.openide.awt.Mnemonics.setLocalizedText(prevPageButton, org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.prevPageButton.text")); // NOI18N
130  prevPageButton.setBorderPainted(false);
131  prevPageButton.setContentAreaFilled(false);
132  prevPageButton.setDisabledSelectedIcon(new javax.swing.ImageIcon(getClass().getResource("/org/sleuthkit/autopsy/corecomponents/btn_step_back_disabled.png"))); // NOI18N
133  prevPageButton.setMargin(new java.awt.Insets(2, 0, 2, 0));
134  prevPageButton.setPreferredSize(new java.awt.Dimension(23, 23));
135  prevPageButton.addActionListener(new java.awt.event.ActionListener() {
136  public void actionPerformed(java.awt.event.ActionEvent evt) {
137  prevPageButtonActionPerformed(evt);
138  }
139  });
140 
141  nextPageButton.setIcon(new javax.swing.ImageIcon(getClass().getResource("/org/sleuthkit/autopsy/corecomponents/btn_step_forward.png"))); // NOI18N
142  org.openide.awt.Mnemonics.setLocalizedText(nextPageButton, org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.nextPageButton.text")); // NOI18N
143  nextPageButton.setBorderPainted(false);
144  nextPageButton.setContentAreaFilled(false);
145  nextPageButton.setDisabledSelectedIcon(new javax.swing.ImageIcon(getClass().getResource("/org/sleuthkit/autopsy/corecomponents/btn_step_forward_disabled.png"))); // NOI18N
146  nextPageButton.setMargin(new java.awt.Insets(2, 0, 2, 0));
147  nextPageButton.setPreferredSize(new java.awt.Dimension(23, 23));
148  nextPageButton.addActionListener(new java.awt.event.ActionListener() {
149  public void actionPerformed(java.awt.event.ActionEvent evt) {
150  nextPageButtonActionPerformed(evt);
151  }
152  });
153 
154  org.openide.awt.Mnemonics.setLocalizedText(exportCsvButton, org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.exportCsvButton.text")); // NOI18N
155  exportCsvButton.addActionListener(new java.awt.event.ActionListener() {
156  public void actionPerformed(java.awt.event.ActionEvent evt) {
157  exportCsvButtonActionPerformed(evt);
158  }
159  });
160 
161  javax.swing.GroupLayout jHdrPanelLayout = new javax.swing.GroupLayout(jHdrPanel);
162  jHdrPanel.setLayout(jHdrPanelLayout);
163  jHdrPanelLayout.setHorizontalGroup(
164  jHdrPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
165  .addGroup(jHdrPanelLayout.createSequentialGroup()
166  .addContainerGap()
167  .addComponent(jLabel1)
168  .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
169  .addComponent(tablesDropdownList, javax.swing.GroupLayout.PREFERRED_SIZE, 130, javax.swing.GroupLayout.PREFERRED_SIZE)
170  .addGap(18, 18, 18)
171  .addComponent(numEntriesField, javax.swing.GroupLayout.PREFERRED_SIZE, 71, javax.swing.GroupLayout.PREFERRED_SIZE)
172  .addGap(15, 15, 15)
173  .addComponent(jLabel2)
174  .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
175  .addComponent(currPageLabel)
176  .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
177  .addComponent(jLabel3)
178  .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
179  .addComponent(numPagesLabel)
180  .addGap(18, 18, 18)
181  .addComponent(prevPageButton, javax.swing.GroupLayout.PREFERRED_SIZE, 23, javax.swing.GroupLayout.PREFERRED_SIZE)
182  .addGap(0, 0, 0)
183  .addComponent(nextPageButton, javax.swing.GroupLayout.PREFERRED_SIZE, 23, javax.swing.GroupLayout.PREFERRED_SIZE)
184  .addGap(29, 29, 29)
185  .addComponent(exportCsvButton)
186  .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
187  );
188  jHdrPanelLayout.setVerticalGroup(
189  jHdrPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
190  .addGroup(jHdrPanelLayout.createSequentialGroup()
191  .addContainerGap()
192  .addGroup(jHdrPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
193  .addComponent(exportCsvButton)
194  .addComponent(nextPageButton, javax.swing.GroupLayout.PREFERRED_SIZE, 23, javax.swing.GroupLayout.PREFERRED_SIZE)
195  .addComponent(prevPageButton, javax.swing.GroupLayout.PREFERRED_SIZE, 23, javax.swing.GroupLayout.PREFERRED_SIZE)
196  .addGroup(jHdrPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
197  .addComponent(tablesDropdownList, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
198  .addComponent(jLabel1)
199  .addComponent(numEntriesField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
200  .addComponent(jLabel2)
201  .addComponent(currPageLabel)
202  .addComponent(jLabel3)
203  .addComponent(numPagesLabel)))
204  .addContainerGap())
205  );
206 
207  jTableDataPanel.setLayout(new java.awt.BorderLayout());
208 
209  javax.swing.GroupLayout layout = new javax.swing.GroupLayout(this);
210  this.setLayout(layout);
211  layout.setHorizontalGroup(
212  layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
213  .addComponent(jHdrPanel, javax.swing.GroupLayout.DEFAULT_SIZE, 569, Short.MAX_VALUE)
214  .addComponent(jTableDataPanel, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
215  );
216  layout.setVerticalGroup(
217  layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
218  .addGroup(layout.createSequentialGroup()
219  .addComponent(jHdrPanel, javax.swing.GroupLayout.PREFERRED_SIZE, 53, javax.swing.GroupLayout.PREFERRED_SIZE)
220  .addGap(0, 0, 0)
221  .addComponent(jTableDataPanel, javax.swing.GroupLayout.DEFAULT_SIZE, 317, Short.MAX_VALUE))
222  );
223  }// </editor-fold>//GEN-END:initComponents
224 
225  private void nextPageButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_nextPageButtonActionPerformed
226  WindowManager.getDefault().getMainWindow().setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
227  currPage++;
228  if (currPage * ROWS_PER_PAGE > numRows) {
229  nextPageButton.setEnabled(false);
230  }
231  currPageLabel.setText(Integer.toString(currPage));
232  prevPageButton.setEnabled(true);
233 
234  // read and display a page of rows
235  String tableName = (String) this.tablesDropdownList.getSelectedItem();
236  readTable(tableName, (currPage - 1) * ROWS_PER_PAGE + 1, ROWS_PER_PAGE);
237  WindowManager.getDefault().getMainWindow().setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
238  }//GEN-LAST:event_nextPageButtonActionPerformed
239 
240  private void prevPageButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_prevPageButtonActionPerformed
241 
242  WindowManager.getDefault().getMainWindow().setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
243  currPage--;
244  if (currPage == 1) {
245  prevPageButton.setEnabled(false);
246  }
247  currPageLabel.setText(Integer.toString(currPage));
248  nextPageButton.setEnabled(true);
249 
250  // read and display a page of rows
251  String tableName = (String) this.tablesDropdownList.getSelectedItem();
252  readTable(tableName, (currPage - 1) * ROWS_PER_PAGE + 1, ROWS_PER_PAGE);
253  WindowManager.getDefault().getMainWindow().setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
254  }//GEN-LAST:event_prevPageButtonActionPerformed
255 
256  private void tablesDropdownListActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_tablesDropdownListActionPerformed
257  JComboBox<?> cb = (JComboBox<?>) evt.getSource();
258  String tableName = (String) cb.getSelectedItem();
259  if (null == tableName) {
260  return;
261  }
262  WindowManager.getDefault().getMainWindow().setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
263  selectTable(tableName);
264  WindowManager.getDefault().getMainWindow().setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
265  }//GEN-LAST:event_tablesDropdownListActionPerformed
266 
274  @NbBundle.Messages({"SQLiteViewer.csvExport.fileName.empty=Please input a file name for exporting.",
275  "SQLiteViewer.csvExport.title=Export to csv file",
276  "SQLiteViewer.csvExport.confirm.msg=Do you want to overwrite the existing file?"})
277  private void exportCsvButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_exportCsvButtonActionPerformed
278  Case openCase = Case.getCurrentCase();
279  File caseDirectory = new File(openCase.getExportDirectory());
280  JFileChooser fileChooser = new JFileChooser();
281  fileChooser.setDragEnabled(false);
282  fileChooser.setCurrentDirectory(caseDirectory);
283  //Set a filter to let the filechooser only work for csv files
284  FileNameExtensionFilter csvFilter = new FileNameExtensionFilter("*.csv", "csv");
285  fileChooser.addChoosableFileFilter(csvFilter);
286  fileChooser.setAcceptAllFileFilterUsed(true);
287  fileChooser.setFileFilter(csvFilter);
288  fileChooser.setFileSelectionMode(JFileChooser.FILES_ONLY);
289  String defaultFileName = (String) this.tablesDropdownList.getSelectedItem();
290  fileChooser.setSelectedFile(new File(defaultFileName));
291  int choice = fileChooser.showSaveDialog((Component) evt.getSource()); //TODO
292  if (JFileChooser.APPROVE_OPTION == choice) {
293  File file = fileChooser.getSelectedFile();
294  if (file.exists() && FilenameUtils.getExtension(file.getName()).equalsIgnoreCase("csv")) {
295  if (JOptionPane.YES_OPTION == JOptionPane.showConfirmDialog(this,
296  Bundle.SQLiteViewer_csvExport_confirm_msg(),
297  Bundle.SQLiteViewer_csvExport_title(),
298  JOptionPane.YES_NO_OPTION)) {
299  } else {
300  return;
301  }
302  }
303 
304  exportTableToCsv(file);
305  }
306  }//GEN-LAST:event_exportCsvButtonActionPerformed
307 
308  // Variables declaration - do not modify//GEN-BEGIN:variables
309  private javax.swing.JLabel currPageLabel;
310  private javax.swing.JButton exportCsvButton;
311  private javax.swing.JPanel jHdrPanel;
312  private javax.swing.JLabel jLabel1;
313  private javax.swing.JLabel jLabel2;
314  private javax.swing.JLabel jLabel3;
315  private javax.swing.JPanel jTableDataPanel;
316  private javax.swing.JButton nextPageButton;
317  private javax.swing.JTextField numEntriesField;
318  private javax.swing.JLabel numPagesLabel;
319  private javax.swing.JButton prevPageButton;
320  private javax.swing.JComboBox<String> tablesDropdownList;
321  // End of variables declaration//GEN-END:variables
322 
323  @Override
324  public List<String> getSupportedMIMETypes() {
325  return Arrays.asList(SUPPORTED_MIMETYPES);
326  }
327 
328  @Override
329  public void setFile(AbstractFile file) {
330  WindowManager.getDefault().getMainWindow().setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
331  sqliteDbFile = file;
332  processSQLiteFile();
333  WindowManager.getDefault().getMainWindow().setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
334  }
335 
336  @Override
337  public Component getComponent() {
338  return this;
339  }
340 
341  @Override
342  public void resetComponent() {
343  tablesDropdownList.setEnabled(true);
344  tablesDropdownList.removeAllItems();
345  numEntriesField.setText("");
346 
347  // close DB connection to file
348  if (null != connection) {
349  try {
350  connection.close();
351  connection = null;
352  } catch (SQLException ex) {
353  logger.log(Level.SEVERE, "Failed to close DB connection to file.", ex); //NON-NLS
354  }
355  }
356 
357  sqliteDbFile = null;
358  }
359 
363  @NbBundle.Messages({
364  "SQLiteViewer.comboBox.noTableEntry=No tables found",
365  "SQLiteViewer.errorMessage.interrupted=The processing of the file was interrupted.",
366  "SQLiteViewer.errorMessage.noCurrentCase=The case has been closed.",
367  "SQLiteViewer.errorMessage.failedToExtractFile=The file could not be extracted from the data source.",
368  "SQLiteViewer.errorMessage.failedToQueryDatabase=The database tables in the file could not be read.",
369  "SQLiteViewer.errorMessage.failedToinitJDBCDriver=The JDBC driver for SQLite could not be loaded.",
370  "# {0} - exception message", "SQLiteViewer.errorMessage.unexpectedError=An unexpected error occurred:\n{0).",})
371  private void processSQLiteFile() {
372 
373  tablesDropdownList.removeAllItems();
374 
375  try {
376  String localDiskPath = SqliteUtil.writeAbstractFileToLocalDisk(sqliteDbFile);
377  SqliteUtil.findAndCopySQLiteMetaFile(sqliteDbFile);
378  // Load the SQLite JDBC driver, if necessary.
379  Class.forName("org.sqlite.JDBC"); //NON-NLS
380  connection = DriverManager.getConnection("jdbc:sqlite:" + localDiskPath); //NON-NLS
381 
382  Collection<String> dbTablesMap = getTables();
383  if (dbTablesMap.isEmpty()) {
384  tablesDropdownList.addItem(Bundle.SQLiteViewer_comboBox_noTableEntry());
385  tablesDropdownList.setEnabled(false);
386  } else {
387  dbTablesMap.forEach((tableName) -> {
388  tablesDropdownList.addItem(tableName);
389  });
390  }
391  } catch (ClassNotFoundException ex) {
392  logger.log(Level.SEVERE, String.format("Failed to initialize JDBC SQLite '%s' (objId=%d)", sqliteDbFile.getName(), sqliteDbFile.getId()), ex); //NON-NLS
393  MessageNotifyUtil.Message.error(Bundle.SQLiteViewer_errorMessage_failedToinitJDBCDriver());
394  } catch (SQLException ex) {
395  logger.log(Level.SEVERE, String.format("Failed to get tables from DB file '%s' (objId=%d)", sqliteDbFile.getName(), sqliteDbFile.getId()), ex); //NON-NLS
396  MessageNotifyUtil.Message.error(Bundle.SQLiteViewer_errorMessage_failedToQueryDatabase());
397  } catch (IOException | NoCurrentCaseException | TskCoreException ex) {
398  logger.log(Level.SEVERE, String.format("Failed to create temp copy of DB file '%s' (objId=%d)", sqliteDbFile.getName(), sqliteDbFile.getId()), ex); //NON-NLS
399  MessageNotifyUtil.Message.error(Bundle.SQLiteViewer_errorMessage_failedToExtractFile());
400  }
401  }
402 
408  private Collection<String> getTables() throws SQLException {
409  Collection<String> tableNames = new LinkedList<>();
410  try (Statement statement = connection.createStatement();
411  ResultSet resultSet = statement.executeQuery(
412  "SELECT name FROM sqlite_master "
413  + " WHERE type= 'table' ")){
414  while (resultSet.next()) {
415  tableNames.add(resultSet.getString("name")); //NON-NLS
416  }
417  }
418  return tableNames;
419  }
420 
421  @NbBundle.Messages({"# {0} - tableName",
422  "SQLiteViewer.selectTable.errorText=Error getting row count for table: {0}"
423  })
424  private void selectTable(String tableName) {
425 
426  try (Statement statement = connection.createStatement();
427  ResultSet resultSet = statement.executeQuery(
428  "SELECT count (*) as count FROM " + "\"" + tableName + "\"")) { //NON-NLS{
429 
430  numRows = resultSet.getInt("count");
431  numEntriesField.setText(numRows + " entries");
432 
433  currPage = 1;
434  currPageLabel.setText(Integer.toString(currPage));
435  numPagesLabel.setText(Integer.toString((numRows / ROWS_PER_PAGE) + 1));
436 
437  prevPageButton.setEnabled(false);
438 
439  if (numRows > 0) {
440  exportCsvButton.setEnabled(true);
441  nextPageButton.setEnabled(((numRows > ROWS_PER_PAGE)));
442  readTable(tableName, (currPage - 1) * ROWS_PER_PAGE + 1, ROWS_PER_PAGE);
443  } else {
444  exportCsvButton.setEnabled(false);
445  nextPageButton.setEnabled(false);
446  selectedTableView.setupTable(Collections.emptyList());
447  }
448 
449  } catch (SQLException ex) {
450  logger.log(Level.SEVERE, String.format("Failed to load table %s from DB file '%s' (objId=%d)", tableName, sqliteDbFile.getName(), sqliteDbFile.getId()), ex); //NON-NLS
451  MessageNotifyUtil.Message.error(Bundle.SQLiteViewer_selectTable_errorText(tableName));
452  }
453  }
454 
455  @NbBundle.Messages({"# {0} - tableName",
456  "SQLiteViewer.readTable.errorText=Error getting rows for table: {0}"})
457  private void readTable(String tableName, int startRow, int numRowsToRead) {
458 
459  try (
460  Statement statement = connection.createStatement();
461  ResultSet resultSet = statement.executeQuery(
462  "SELECT * FROM " + "\"" + tableName + "\""
463  + " LIMIT " + Integer.toString(numRowsToRead)
464  + " OFFSET " + Integer.toString(startRow - 1))) {
465 
466  List<Map<String, Object>> rows = resultSetToArrayList(resultSet);
467  if (Objects.nonNull(rows)) {
468  selectedTableView.setupTable(rows);
469  } else {
470  selectedTableView.setupTable(Collections.emptyList());
471  }
472  } catch (SQLException ex) {
473  logger.log(Level.SEVERE, String.format("Failed to read table %s from DB file '%s' (objId=%d)", tableName, sqliteDbFile.getName(), sqliteDbFile.getId()), ex); //NON-NLS
474  MessageNotifyUtil.Message.error(Bundle.SQLiteViewer_readTable_errorText(tableName));
475  }
476  }
477 
478  @NbBundle.Messages("SQLiteViewer.BlobNotShown.message=BLOB Data not shown")
479  private List<Map<String, Object>> resultSetToArrayList(ResultSet resultSet) throws SQLException {
480  ResultSetMetaData metaData = resultSet.getMetaData();
481  int columns = metaData.getColumnCount();
482  ArrayList<Map<String, Object>> rowlist = new ArrayList<>();
483  while (resultSet.next()) {
484  Map<String, Object> row = new LinkedHashMap<>(columns);
485  for (int i = 1; i <= columns; ++i) {
486  if (resultSet.getObject(i) == null) {
487  row.put(metaData.getColumnName(i), "");
488  } else {
489  if (metaData.getColumnTypeName(i).compareToIgnoreCase("blob") == 0) {
490  row.put(metaData.getColumnName(i), Bundle.SQLiteViewer_BlobNotShown_message());
491  } else {
492  row.put(metaData.getColumnName(i), resultSet.getObject(i));
493  }
494  }
495  }
496  rowlist.add(row);
497  }
498 
499  return rowlist;
500  }
501 
502  @NbBundle.Messages({"SQLiteViewer.exportTableToCsv.write.errText=Failed to export table content to csv file.",
503  "SQLiteViewer.exportTableToCsv.FileName=File name: ",
504  "SQLiteViewer.exportTableToCsv.TableName=Table name: "
505  })
506  private void exportTableToCsv(File file) {
507  String tableName = (String) this.tablesDropdownList.getSelectedItem();
508  try (
509  Statement statement = connection.createStatement();
510  ResultSet resultSet = statement.executeQuery("SELECT * FROM " + "\"" + tableName + "\"")) {
511  List<Map<String, Object>> currentTableRows = resultSetToArrayList(resultSet);
512 
513  if (Objects.isNull(currentTableRows) || currentTableRows.isEmpty()) {
514  logger.log(Level.INFO, String.format("The table %s is empty. (objId=%d)", tableName, sqliteDbFile.getId())); //NON-NLS
515  } else {
516  File csvFile;
517  String fileName = file.getName();
518  if (FilenameUtils.getExtension(fileName).equalsIgnoreCase("csv")) {
519  csvFile = file;
520  } else {
521  csvFile = new File(file.toString() + ".csv");
522  }
523 
524  try (FileOutputStream out = new FileOutputStream(csvFile, false)) {
525 
526  out.write((Bundle.SQLiteViewer_exportTableToCsv_FileName() + csvFile.getName() + "\n").getBytes());
527  out.write((Bundle.SQLiteViewer_exportTableToCsv_TableName() + tableName + "\n").getBytes());
528  // Set up the column names
529  Map<String, Object> row = currentTableRows.get(0);
530  StringBuffer header = new StringBuffer();
531  for (Map.Entry<String, Object> col : row.entrySet()) {
532  String colName = col.getKey();
533  if (header.length() > 0) {
534  header.append(',').append(colName);
535  } else {
536  header.append(colName);
537  }
538  }
539  out.write(header.append('\n').toString().getBytes());
540 
541  for (Map<String, Object> maps : currentTableRows) {
542  StringBuffer valueLine = new StringBuffer();
543  maps.values().forEach((value) -> {
544  if (valueLine.length() > 0) {
545  valueLine.append(',').append(value.toString());
546  } else {
547  valueLine.append(value.toString());
548  }
549  });
550  out.write(valueLine.append('\n').toString().getBytes());
551  }
552  }
553  }
554  } catch (SQLException ex) {
555  logger.log(Level.SEVERE, String.format("Failed to read table %s from DB file '%s' (objId=%d)", tableName, sqliteDbFile.getName(), sqliteDbFile.getId()), ex); //NON-NLS
556  MessageNotifyUtil.Message.error(Bundle.SQLiteViewer_readTable_errorText(tableName));
557  } catch (IOException ex) {
558  logger.log(Level.SEVERE, String.format("Failed to export table %s to file '%s'", tableName, file.getName()), ex); //NON-NLS
559  MessageNotifyUtil.Message.error(Bundle.SQLiteViewer_exportTableToCsv_write_errText());
560  }
561  }
562 
563 
564 }

Copyright © 2012-2018 Basis Technology. Generated on: Thu Oct 4 2018
This work is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.