Autopsy  4.18.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-2019 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.util.ArrayList;
28 import java.util.Arrays;
29 import java.util.Collection;
30 import java.util.Collections;
31 import java.util.LinkedHashMap;
32 import java.util.List;
33 import java.util.Map;
34 import java.util.Objects;
35 import java.util.concurrent.ExecutionException;
36 import java.util.function.Consumer;
37 import java.util.logging.Level;
38 import javax.swing.JComboBox;
39 import javax.swing.JFileChooser;
40 import javax.swing.JOptionPane;
41 import javax.swing.SwingWorker;
42 import javax.swing.filechooser.FileNameExtensionFilter;
43 import org.apache.commons.io.FilenameUtils;
44 import org.openide.util.NbBundle;
45 import org.openide.windows.WindowManager;
46 import org.sleuthkit.autopsy.casemodule.Case;
47 import org.sleuthkit.autopsy.coreutils.SQLiteTableReaderException;
48 import org.sleuthkit.autopsy.coreutils.Logger;
49 import org.sleuthkit.autopsy.coreutils.MessageNotifyUtil;
50 import org.sleuthkit.datamodel.AbstractFile;
51 import org.sleuthkit.autopsy.coreutils.SQLiteTableReader;
52 
56 @SuppressWarnings("PMD.SingularField") // UI widgets cause lots of false positives
57 class SQLiteViewer extends javax.swing.JPanel implements FileTypeViewer {
58 
59  private static final long serialVersionUID = 1L;
60  public static final String[] SUPPORTED_MIMETYPES = new String[]{"application/x-sqlite3"};
61  private static final int ROWS_PER_PAGE = 100;
62  private static final Logger logger = Logger.getLogger(FileViewer.class.getName());
63  private final SQLiteTableView selectedTableView = new SQLiteTableView();
64  private AbstractFile sqliteDbFile;
65 
66  private SQLiteTableReader viewReader;
67 
68  private Map<String, Object> row = new LinkedHashMap<>();
69  private List<Map<String, Object>> pageOfTableRows = new ArrayList<>();
70  private List<String> currentTableHeader = new ArrayList<>();
71  private String prevTableName;
72 
73  private int numRows; // num of rows in the selected table
74  private int currPage = 0; // curr page of rows being displayed
75 
76  SwingWorker<?, ?> worker;
77 
81  SQLiteViewer() {
82  initComponents();
83  jTableDataPanel.add(selectedTableView, BorderLayout.CENTER);
84  }
85 
91  @SuppressWarnings("unchecked")
92  // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
93  private void initComponents() {
94 
95  jHdrPanel = new javax.swing.JPanel();
96  tablesDropdownList = new javax.swing.JComboBox<>();
97  jLabel1 = new javax.swing.JLabel();
98  numEntriesField = new javax.swing.JTextField();
99  jLabel2 = new javax.swing.JLabel();
100  currPageLabel = new javax.swing.JLabel();
101  jLabel3 = new javax.swing.JLabel();
102  numPagesLabel = new javax.swing.JLabel();
103  prevPageButton = new javax.swing.JButton();
104  nextPageButton = new javax.swing.JButton();
105  exportCsvButton = new javax.swing.JButton();
106  jTableDataPanel = new javax.swing.JPanel();
107 
108  jHdrPanel.setPreferredSize(new java.awt.Dimension(536, 40));
109 
110  tablesDropdownList.setModel(new javax.swing.DefaultComboBoxModel<>(new String[] { "Item 1", "Item 2", "Item 3", "Item 4" }));
111  tablesDropdownList.addActionListener(new java.awt.event.ActionListener() {
112  public void actionPerformed(java.awt.event.ActionEvent evt) {
113  tablesDropdownListActionPerformed(evt);
114  }
115  });
116 
117  org.openide.awt.Mnemonics.setLocalizedText(jLabel1, org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.jLabel1.text")); // NOI18N
118 
119  numEntriesField.setEditable(false);
120  numEntriesField.setText(org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.numEntriesField.text")); // NOI18N
121  numEntriesField.setBorder(null);
122 
123  org.openide.awt.Mnemonics.setLocalizedText(jLabel2, org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.jLabel2.text")); // NOI18N
124 
125  org.openide.awt.Mnemonics.setLocalizedText(currPageLabel, org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.currPageLabel.text")); // NOI18N
126 
127  org.openide.awt.Mnemonics.setLocalizedText(jLabel3, org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.jLabel3.text")); // NOI18N
128 
129  org.openide.awt.Mnemonics.setLocalizedText(numPagesLabel, org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.numPagesLabel.text")); // NOI18N
130 
131  prevPageButton.setIcon(new javax.swing.ImageIcon(getClass().getResource("/org/sleuthkit/autopsy/corecomponents/btn_step_back.png"))); // NOI18N
132  org.openide.awt.Mnemonics.setLocalizedText(prevPageButton, org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.prevPageButton.text")); // NOI18N
133  prevPageButton.setBorderPainted(false);
134  prevPageButton.setContentAreaFilled(false);
135  prevPageButton.setDisabledSelectedIcon(new javax.swing.ImageIcon(getClass().getResource("/org/sleuthkit/autopsy/corecomponents/btn_step_back_disabled.png"))); // NOI18N
136  prevPageButton.setMargin(new java.awt.Insets(2, 0, 2, 0));
137  prevPageButton.setPreferredSize(new java.awt.Dimension(23, 23));
138  prevPageButton.addActionListener(new java.awt.event.ActionListener() {
139  public void actionPerformed(java.awt.event.ActionEvent evt) {
140  prevPageButtonActionPerformed(evt);
141  }
142  });
143 
144  nextPageButton.setIcon(new javax.swing.ImageIcon(getClass().getResource("/org/sleuthkit/autopsy/corecomponents/btn_step_forward.png"))); // NOI18N
145  org.openide.awt.Mnemonics.setLocalizedText(nextPageButton, org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.nextPageButton.text")); // NOI18N
146  nextPageButton.setBorderPainted(false);
147  nextPageButton.setContentAreaFilled(false);
148  nextPageButton.setDisabledSelectedIcon(new javax.swing.ImageIcon(getClass().getResource("/org/sleuthkit/autopsy/corecomponents/btn_step_forward_disabled.png"))); // NOI18N
149  nextPageButton.setMargin(new java.awt.Insets(2, 0, 2, 0));
150  nextPageButton.setPreferredSize(new java.awt.Dimension(23, 23));
151  nextPageButton.addActionListener(new java.awt.event.ActionListener() {
152  public void actionPerformed(java.awt.event.ActionEvent evt) {
153  nextPageButtonActionPerformed(evt);
154  }
155  });
156 
157  org.openide.awt.Mnemonics.setLocalizedText(exportCsvButton, org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.exportCsvButton.text")); // NOI18N
158  exportCsvButton.addActionListener(new java.awt.event.ActionListener() {
159  public void actionPerformed(java.awt.event.ActionEvent evt) {
160  exportCsvButtonActionPerformed(evt);
161  }
162  });
163 
164  javax.swing.GroupLayout jHdrPanelLayout = new javax.swing.GroupLayout(jHdrPanel);
165  jHdrPanel.setLayout(jHdrPanelLayout);
166  jHdrPanelLayout.setHorizontalGroup(
167  jHdrPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
168  .addGroup(jHdrPanelLayout.createSequentialGroup()
169  .addContainerGap()
170  .addComponent(jLabel1)
171  .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
172  .addComponent(tablesDropdownList, javax.swing.GroupLayout.PREFERRED_SIZE, 130, javax.swing.GroupLayout.PREFERRED_SIZE)
173  .addGap(18, 18, 18)
174  .addComponent(numEntriesField, javax.swing.GroupLayout.PREFERRED_SIZE, 71, javax.swing.GroupLayout.PREFERRED_SIZE)
175  .addGap(15, 15, 15)
176  .addComponent(jLabel2)
177  .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
178  .addComponent(currPageLabel)
179  .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
180  .addComponent(jLabel3)
181  .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
182  .addComponent(numPagesLabel)
183  .addGap(18, 18, 18)
184  .addComponent(prevPageButton, javax.swing.GroupLayout.PREFERRED_SIZE, 23, javax.swing.GroupLayout.PREFERRED_SIZE)
185  .addGap(0, 0, 0)
186  .addComponent(nextPageButton, javax.swing.GroupLayout.PREFERRED_SIZE, 23, javax.swing.GroupLayout.PREFERRED_SIZE)
187  .addGap(29, 29, 29)
188  .addComponent(exportCsvButton)
189  .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
190  );
191  jHdrPanelLayout.setVerticalGroup(
192  jHdrPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
193  .addGroup(jHdrPanelLayout.createSequentialGroup()
194  .addContainerGap()
195  .addGroup(jHdrPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
196  .addComponent(exportCsvButton)
197  .addComponent(nextPageButton, javax.swing.GroupLayout.PREFERRED_SIZE, 23, javax.swing.GroupLayout.PREFERRED_SIZE)
198  .addComponent(prevPageButton, javax.swing.GroupLayout.PREFERRED_SIZE, 23, javax.swing.GroupLayout.PREFERRED_SIZE)
199  .addGroup(jHdrPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
200  .addComponent(tablesDropdownList, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
201  .addComponent(jLabel1)
202  .addComponent(numEntriesField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
203  .addComponent(jLabel2)
204  .addComponent(currPageLabel)
205  .addComponent(jLabel3)
206  .addComponent(numPagesLabel)))
207  .addContainerGap())
208  );
209 
210  jTableDataPanel.setLayout(new java.awt.BorderLayout());
211 
212  javax.swing.GroupLayout layout = new javax.swing.GroupLayout(this);
213  this.setLayout(layout);
214  layout.setHorizontalGroup(
215  layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
216  .addComponent(jHdrPanel, javax.swing.GroupLayout.DEFAULT_SIZE, 569, Short.MAX_VALUE)
217  .addComponent(jTableDataPanel, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
218  );
219  layout.setVerticalGroup(
220  layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
221  .addGroup(layout.createSequentialGroup()
222  .addComponent(jHdrPanel, javax.swing.GroupLayout.PREFERRED_SIZE, 53, javax.swing.GroupLayout.PREFERRED_SIZE)
223  .addGap(0, 0, 0)
224  .addComponent(jTableDataPanel, javax.swing.GroupLayout.DEFAULT_SIZE, 317, Short.MAX_VALUE))
225  );
226  }// </editor-fold>//GEN-END:initComponents
227 
228  private void nextPageButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_nextPageButtonActionPerformed
229  WindowManager.getDefault().getMainWindow().setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
230  currPage++;
231  if (currPage * ROWS_PER_PAGE > numRows) {
232  nextPageButton.setEnabled(false);
233  }
234  currPageLabel.setText(Integer.toString(currPage));
235  prevPageButton.setEnabled(true);
236 
237  // read and display a page of rows
238  String tableName = (String) this.tablesDropdownList.getSelectedItem();
239  readTable(tableName, (currPage - 1) * ROWS_PER_PAGE + 1, ROWS_PER_PAGE);
240  WindowManager.getDefault().getMainWindow().setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
241  }//GEN-LAST:event_nextPageButtonActionPerformed
242 
243  private void prevPageButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_prevPageButtonActionPerformed
244 
245  WindowManager.getDefault().getMainWindow().setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
246  currPage--;
247  if (currPage == 1) {
248  prevPageButton.setEnabled(false);
249  }
250  currPageLabel.setText(Integer.toString(currPage));
251  nextPageButton.setEnabled(true);
252 
253  // read and display a page of rows
254  String tableName = (String) this.tablesDropdownList.getSelectedItem();
255  readTable(tableName, (currPage - 1) * ROWS_PER_PAGE + 1, ROWS_PER_PAGE);
256  WindowManager.getDefault().getMainWindow().setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
257  }//GEN-LAST:event_prevPageButtonActionPerformed
258 
259  private void tablesDropdownListActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_tablesDropdownListActionPerformed
260  JComboBox<?> cb = (JComboBox<?>) evt.getSource();
261  String tableName = (String) cb.getSelectedItem();
262  if (null == tableName) {
263  return;
264  }
265  WindowManager.getDefault().getMainWindow().setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
266  selectTable(tableName);
267  WindowManager.getDefault().getMainWindow().setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
268  }//GEN-LAST:event_tablesDropdownListActionPerformed
269 
277  @NbBundle.Messages({"SQLiteViewer.csvExport.fileName.empty=Please input a file name for exporting.",
278  "SQLiteViewer.csvExport.title=Export to csv file",
279  "SQLiteViewer.csvExport.confirm.msg=Do you want to overwrite the existing file?"})
280  private void exportCsvButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_exportCsvButtonActionPerformed
281  Case openCase = Case.getCurrentCase();
282  File caseDirectory = new File(openCase.getExportDirectory());
283  JFileChooser fileChooser = new JFileChooser();
284  fileChooser.setDragEnabled(false);
285  fileChooser.setCurrentDirectory(caseDirectory);
286  //Set a filter to let the filechooser only work for csv files
287  FileNameExtensionFilter csvFilter = new FileNameExtensionFilter("*.csv", "csv");
288  fileChooser.addChoosableFileFilter(csvFilter);
289  fileChooser.setAcceptAllFileFilterUsed(true);
290  fileChooser.setFileFilter(csvFilter);
291  fileChooser.setFileSelectionMode(JFileChooser.FILES_ONLY);
292  String defaultFileName = (String) this.tablesDropdownList.getSelectedItem();
293  fileChooser.setSelectedFile(new File(defaultFileName));
294  int choice = fileChooser.showSaveDialog((Component) evt.getSource()); //TODO
295  if (JFileChooser.APPROVE_OPTION == choice) {
296  File file = fileChooser.getSelectedFile();
297  if (file.exists() && FilenameUtils.getExtension(file.getName()).equalsIgnoreCase("csv")) {
298  if (JOptionPane.YES_OPTION == JOptionPane.showConfirmDialog(this,
299  Bundle.SQLiteViewer_csvExport_confirm_msg(),
300  Bundle.SQLiteViewer_csvExport_title(),
301  JOptionPane.YES_NO_OPTION)) {
302  } else {
303  return;
304  }
305  }
306 
307  exportTableToCsv(file);
308  }
309  }//GEN-LAST:event_exportCsvButtonActionPerformed
310 
311  // Variables declaration - do not modify//GEN-BEGIN:variables
312  private javax.swing.JLabel currPageLabel;
313  private javax.swing.JButton exportCsvButton;
314  private javax.swing.JPanel jHdrPanel;
315  private javax.swing.JLabel jLabel1;
316  private javax.swing.JLabel jLabel2;
317  private javax.swing.JLabel jLabel3;
318  private javax.swing.JPanel jTableDataPanel;
319  private javax.swing.JButton nextPageButton;
320  private javax.swing.JTextField numEntriesField;
321  private javax.swing.JLabel numPagesLabel;
322  private javax.swing.JButton prevPageButton;
323  private javax.swing.JComboBox<String> tablesDropdownList;
324  // End of variables declaration//GEN-END:variables
325 
326  @Override
327  public List<String> getSupportedMIMETypes() {
328  return Arrays.asList(SUPPORTED_MIMETYPES);
329  }
330 
331  @Override
332  public void setFile(AbstractFile file) {
333  if (worker != null) {
334  worker.cancel(true);
335  worker = null;
336  }
337  resetComponent();
338 
339  if (file == null) {
340  return;
341  }
342 
343  processSQLiteFile(file);
344  }
345 
346  @Override
347  public Component getComponent() {
348  return this;
349  }
350 
351  @Override
352  public void resetComponent() {
353  tablesDropdownList.setEnabled(true);
354  tablesDropdownList.removeAllItems();
355  numEntriesField.setText("");
356 
357  if(viewReader != null) {
358  try {
359  viewReader.close();
360  } catch (SQLiteTableReaderException ex) {
361  //Could not successfully close the reader, nothing we can do to recover.
362  }
363  }
364  row = new LinkedHashMap<>();
365  pageOfTableRows = new ArrayList<>();
366  currentTableHeader = new ArrayList<>();
367  viewReader = null;
368  sqliteDbFile = null;
369  }
370 
374  @NbBundle.Messages({
375  "SQLiteViewer.comboBox.noTableEntry=No tables found",
376  "SQLiteViewer.errorMessage.interrupted=The processing of the file was interrupted.",
377  "SQLiteViewer.errorMessage.noCurrentCase=The case has been closed.",
378  "SQLiteViewer.errorMessage.failedToExtractFile=The file could not be extracted from the data source.",
379  "SQLiteViewer.errorMessage.failedToQueryDatabase=The database tables in the file could not be read.",
380  "SQLiteViewer.errorMessage.failedToinitJDBCDriver=The JDBC driver for SQLite could not be loaded.",
381  "# {0} - exception message", "SQLiteViewer.errorMessage.unexpectedError=An unexpected error occurred:\n{0).",})
382  private void processSQLiteFile(final AbstractFile file) {
383 
384  WindowManager.getDefault().getMainWindow().setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
385  worker = new SQLiteViewerWorker(file) {
386  @Override
387  public void done() {
388  if (isCancelled()) {
389  return;
390  }
391 
392  WorkerResults results;
393  try {
394  results = get();
395  sqliteDbFile = file;
396  viewReader = results.getReader();
397  tablesDropdownList.removeAllItems();
398  Collection<String> dbTablesMap = results.getDbTablesMap();
399  if (dbTablesMap.isEmpty()) {
400  tablesDropdownList.addItem(Bundle.SQLiteViewer_comboBox_noTableEntry());
401  tablesDropdownList.setEnabled(false);
402  } else {
403  dbTablesMap.forEach((tableName) -> {
404  tablesDropdownList.addItem(tableName);
405  });
406  }
407 
408  WindowManager.getDefault().getMainWindow().setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
409  } catch (InterruptedException | ExecutionException ex) {
410  logger.log(Level.SEVERE, String.format("Failed to display SQL Viewer for file (%d)", file.getId()), ex);
411  }
412  }
413  };
414 
415  worker.execute();
416  }
417 
418  @NbBundle.Messages({"# {0} - tableName",
419  "SQLiteViewer.selectTable.errorText=Error getting row count for table: {0}"
420  })
421  private void selectTable(String tableName) {
422  try {
423  numRows = viewReader.getRowCount(tableName);
424  numEntriesField.setText(numRows + " entries");
425 
426  currPage = 1;
427  currPageLabel.setText(Integer.toString(currPage));
428  numPagesLabel.setText(Integer.toString((numRows / ROWS_PER_PAGE) + 1));
429 
430  prevPageButton.setEnabled(false);
431 
432  if (numRows > 0) {
433  exportCsvButton.setEnabled(true);
434  nextPageButton.setEnabled(((numRows > ROWS_PER_PAGE)));
435  readTable(tableName, (currPage - 1) * ROWS_PER_PAGE + 1, ROWS_PER_PAGE);
436  } else {
437  exportCsvButton.setEnabled(false);
438  nextPageButton.setEnabled(false);
439 
440  currentTableHeader = new ArrayList<>();
441  viewReader.read(tableName);
442  Map<String, Object> columnRow = new LinkedHashMap<>();
443  for (int i = 0; i < currentTableHeader.size(); i++) {
444  columnRow.put(currentTableHeader.get(i), "");
445  }
446  selectedTableView.setupTable(Collections.singletonList(columnRow));
447  }
448  } catch (SQLiteTableReaderException ex) {
449  logger.log(Level.WARNING, String.format("Failed to load table %s " //NON-NLS
450  + "from DB file '%s' (objId=%d)", tableName, sqliteDbFile.getName(), //NON-NLS
451  sqliteDbFile.getId()), ex.getMessage());
452  MessageNotifyUtil.Message.error(Bundle.SQLiteViewer_selectTable_errorText(tableName));
453  }
454  }
455 
456  @NbBundle.Messages({"# {0} - tableName",
457  "SQLiteViewer.readTable.errorText=Error getting rows for table: {0}"})
458  private void readTable(String tableName, int startRow, int numRowsToRead) {
459  try {
460  //If the table name has changed, then clear our table header. SQLiteTableReader
461  //will also detect the table name has changed and begin reading it as if it
462  //were a brand new table.
463  if (!tableName.equals(prevTableName)) {
464  prevTableName = tableName;
465  }
466  currentTableHeader = new ArrayList<>();
467  viewReader.read(tableName, numRowsToRead, startRow - 1);
468  selectedTableView.setupTable(pageOfTableRows);
469  pageOfTableRows = new ArrayList<>();
470  } catch (SQLiteTableReaderException ex) {
471  logger.log(Level.WARNING, String.format("Failed to read table %s from DB file '%s' " //NON-NLS
472  + "(objId=%d) starting at row [%d] and limit [%d]", //NON-NLS
473  tableName, sqliteDbFile.getName(), sqliteDbFile.getId(),
474  startRow - 1, numRowsToRead), ex.getMessage());
475  MessageNotifyUtil.Message.error(Bundle.SQLiteViewer_readTable_errorText(tableName));
476  }
477  }
478 
485  private SQLiteTableReader initReader(AbstractFile sqliteFile) {
486  return new SQLiteTableReader.Builder(sqliteFile)
487  .forAllColumnNames((columnName) -> {
488  currentTableHeader.add(columnName);
489  })
490  .forAllTableValues(getForAllStrategy()).build();
491  }
492 
505  private Consumer<Object> getForAllStrategy() {
506  return new Consumer<Object>() {
507  private int rowIndex = 0;
508 
509  @Override
510  public void accept(Object t) {
511  rowIndex++;
512  String objectStr = (t instanceof byte[]) ? "BLOB Data not shown"
513  : Objects.toString(t, "");
514 
515  row.put(currentTableHeader.get(rowIndex - 1), objectStr);
516 
517  //If we have built up a full database row, then add it to our page
518  //of rows to be displayed in the UI.
519  if (rowIndex == currentTableHeader.size()) {
520  pageOfTableRows.add(row);
521  row = new LinkedHashMap<>();
522  }
523  rowIndex %= currentTableHeader.size();
524  }
525 
526  };
527  }
528 
529  private int totalColumnCount;
530 
531  @NbBundle.Messages({"SQLiteViewer.exportTableToCsv.write.errText=Failed to export table content to csv file.",
532  "SQLiteViewer.exportTableToCsv.FileName=File name: ",
533  "SQLiteViewer.exportTableToCsv.TableName=Table name: "
534  })
535  private void exportTableToCsv(File file) {
536  final File csvFile = new File(file.toString() + ".csv");
537  final String tableName = (String) this.tablesDropdownList.getSelectedItem();
538 
539  SwingWorker<String, Void> csvWorker = new SwingWorker<String, Void>() {
540  @Override
541  protected String doInBackground() throws Exception {
542  try (FileOutputStream out = new FileOutputStream(csvFile, false)) {
543  try (SQLiteTableReader sqliteStream = new SQLiteTableReader.Builder(sqliteDbFile)
544  .forAllColumnNames(getColumnNameCSVStrategy(out))
545  .forAllTableValues(getForAllCSVStrategy(out)).build()) {
546  totalColumnCount = sqliteStream.getColumnCount(tableName);
547  sqliteStream.read(tableName);
548  }
549  } catch (IOException | SQLiteTableReaderException | RuntimeException ex) {
550  logger.log(Level.WARNING, String.format("Failed to export table [%s]"
551  + " to CSV in sqlite file '%s' (objId=%d)", tableName, sqliteDbFile.getName(),
552  sqliteDbFile.getId()), ex.getMessage()); //NON-NLS
553 
554  return Bundle.SQLiteViewer_exportTableToCsv_write_errText();
555  }
556  return "";
557  }
558 
559  @Override
560  public void done() {
561  try {
562  String message = get();
563  if (!message.isEmpty()) {
564  MessageNotifyUtil.Message.error(message);
565  }
566  } catch (InterruptedException | ExecutionException ex) {
567  logger.log(Level.SEVERE, "Failure occurred writing sql csv file.", ex);
568  }
569  }
570 
571  };
572 
573  csvWorker.execute();
574  }
575 
590  private Consumer<String> getColumnNameCSVStrategy(FileOutputStream out) {
591  return new Consumer<String>() {
592  private int columnIndex = 0;
593 
594  @Override
595  public void accept(String columnName) {
596  columnIndex++;
597  String csvString = columnName;
598  //Format the value to adhere to the format of a CSV file
599  if (columnIndex == 1) {
600  csvString = "\"" + csvString + "\"";
601  } else {
602  csvString = ",\"" + csvString + "\"";
603  }
604  if (columnIndex == totalColumnCount) {
605  csvString += "\n";
606  }
607 
608  try {
609  out.write(csvString.getBytes());
610  } catch (IOException ex) {
611  /*
612  * If we can no longer write to the output stream, toss a
613  * runtime exception to get out of iteration. We explicitly
614  * catch this in exportTableToCsv() above.
615  */
616  throw new RuntimeException(ex);
617  }
618  }
619  };
620  }
621 
636  private Consumer<Object> getForAllCSVStrategy(FileOutputStream out) {
637  return new Consumer<Object>() {
638  private int rowIndex = 0;
639 
640  @Override
641  public void accept(Object tableValue) {
642  rowIndex++;
643  //Substitute string representation of blob with placeholder text.
644  //Automatically wrap the value in quotes in case it contains commas.
645  String objectStr = (tableValue instanceof byte[])
646  ? "BLOB Data not shown" : Objects.toString(tableValue, "");
647  objectStr = "\"" + objectStr + "\"";
648 
649  if (rowIndex > 1) {
650  objectStr = "," + objectStr;
651  }
652  if (rowIndex == totalColumnCount) {
653  objectStr += "\n";
654  }
655 
656  try {
657  out.write(objectStr.getBytes());
658  } catch (IOException ex) {
659  /*
660  * If we can no longer write to the output stream, toss a
661  * runtime exception to get out of iteration. We explicitly
662  * catch this in exportTableToCsv() above.
663  */
664  throw new RuntimeException(ex);
665  }
666  rowIndex %= totalColumnCount;
667  }
668  };
669  }
670 
671  @Override
672  public boolean isSupported(AbstractFile file) {
673  return true;
674  }
675 
680  private class SQLiteViewerWorker extends SwingWorker<WorkerResults, Void> {
681 
682  private final AbstractFile file;
683 
684  SQLiteViewerWorker(AbstractFile file) {
685  this.file = file;
686  }
687 
688  @Override
689  protected WorkerResults doInBackground() throws Exception {
690  SQLiteTableReader reader = initReader(file);
691  Collection<String> dbTablesMap = reader.getTableNames();
692 
693  return new WorkerResults(reader, dbTablesMap);
694  }
695 
696  }
697 
698  /*
699  * Stores the data gather from the
700  */
701  private class WorkerResults {
702 
703  private final SQLiteTableReader reader;
704  private final Collection<String> dbTablesMap;
705 
706  WorkerResults(SQLiteTableReader reader, Collection<String> dbTablesMap) {
707  this.reader = reader;
708  this.dbTablesMap = dbTablesMap;
709  }
710 
711  SQLiteTableReader getReader() {
712  return reader;
713  }
714 
715  Collection<String> getDbTablesMap() {
716  return dbTablesMap;
717  }
718  }
719 
720 }

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