Class DatabaseEditor

java.lang.Object

java.awt.Component

java.awt.Container

java.awt.Window

java.awt.Frame

javax.swing.JFrame

DatabaseEditor

All Implemented Interfaces:

java.awt.image.ImageObserver, java.awt.MenuContainer, java.io.Serializable, javax.accessibility.Accessible, javax.swing.RootPaneContainer, javax.swing.WindowConstants

public class DatabaseEditor
extends javax.swing.JFrame

This class implements a window displaying the tables and views contained in a database (or all tables and views in all attached databases). Left clicking on a table name will bring up a table editor. Right clicking on a table name will bring up a find/replace window that operates on tables. There are four buttons: Refresh - refreshes the window contents (needed after adding tables) Show Hidden Tables - shows sqlite tables and name of database Open Command Window - opens a command window for sql statements Open Database - opens a database in a new process Types of windows: database chooser - chooses a database file to edit or view database editor - list of tables and views in database table editor - contents of a database table or view or result set result table viewer - a view on a select statement result cell editor - editor for a cell in a table, view, or result set command window - a window for executing sql commands find/replace - controls finding and replacing on a cell editor column search window - searches a table column All internal operations oprate on strings. When accessing the database the dbCharset is used for translation to and from the database to Strings (the second argument on the command line defaulting to UTF-8). When accessing files the fileCharset is used to convert file bytes to and from Strings (the third argument on the command line defaulting to UTF-8).

See Also:

Serialized Form

Nested Class Summary

Nested Classes

Modifier and Type	Class and Description
(package private) static class	DatabaseEditor.DatabaseTableEntry This class is a pure data structure.
(package private) class	DatabaseEditor.EditWindow An undoable text edit window This is the base class for all of the text windows.
(package private) class	DatabaseEditor.FindReplace This class provides a search and replace function for table entries.
(package private) static class	DatabaseEditor.RowProc Abstract class to operate on rows of a table.
(package private) class	DatabaseEditor.Search This class searches many cells in a table column.
(package private) class	DatabaseEditor.Table This class implements a window displaying the contents of a database table, view, or result set.
(package private) class	DatabaseEditor.UndoableTextArea This class is identical to a JTextArea but also has undo / redo capability.

Nested classes/interfaces inherited from class javax.swing.JFrame

javax.swing.JFrame.AccessibleJFrame

Nested classes/interfaces inherited from class java.awt.Frame

java.awt.Frame.AccessibleAWTFrame

Nested classes/interfaces inherited from class java.awt.Window

java.awt.Window.AccessibleAWTWindow, java.awt.Window.Type

Nested classes/interfaces inherited from class java.awt.Container

java.awt.Container.AccessibleAWTContainer

Nested classes/interfaces inherited from class java.awt.Component

java.awt.Component.AccessibleAWTComponent, java.awt.Component.BaselineResizeBehavior, java.awt.Component.BltBufferStrategy, java.awt.Component.FlipBufferStrategy

Field Summary

Fields

Modifier and Type	Field and Description
(package private) java.io.File	backupFile
(package private) static int	BUFSIZ
(package private) java.sql.Connection	conn
(package private) static java.lang.String	currentDirectory
(package private) static java.nio.charset.Charset	dbCharset
(package private) java.util.ArrayList<javax.swing.JFrame>	dependents
(package private) java.io.File	editFile
(package private) static java.nio.charset.Charset	fileCharset
(package private) boolean	hidden
(package private) javax.swing.JList<java.lang.Object>	list
(package private) static long	serialVersionUID
(package private) java.util.ArrayList<DatabaseEditor.DatabaseTableEntry>	tables
(package private) static int[][]	trans

Fields inherited from class javax.swing.JFrame

accessibleContext, EXIT_ON_CLOSE, rootPane, rootPaneCheckingEnabled

Fields inherited from class java.awt.Frame

CROSSHAIR_CURSOR, DEFAULT_CURSOR, E_RESIZE_CURSOR, HAND_CURSOR, ICONIFIED, MAXIMIZED_BOTH, MAXIMIZED_HORIZ, MAXIMIZED_VERT, MOVE_CURSOR, N_RESIZE_CURSOR, NE_RESIZE_CURSOR, NORMAL, NW_RESIZE_CURSOR, S_RESIZE_CURSOR, SE_RESIZE_CURSOR, SW_RESIZE_CURSOR, TEXT_CURSOR, W_RESIZE_CURSOR, WAIT_CURSOR

Fields inherited from class java.awt.Component

BOTTOM_ALIGNMENT, CENTER_ALIGNMENT, LEFT_ALIGNMENT, RIGHT_ALIGNMENT, TOP_ALIGNMENT

Fields inherited from interface javax.swing.WindowConstants

DISPOSE_ON_CLOSE, DO_NOTHING_ON_CLOSE, HIDE_ON_CLOSE

Fields inherited from interface java.awt.image.ImageObserver

ABORT, ALLBITS, ERROR, FRAMEBITS, HEIGHT, PROPERTIES, SOMEBITS, WIDTH

Constructor Summary

Constructors

Constructor and Description
DatabaseEditor(java.sql.Connection conn, java.lang.String db, boolean hidden) Create a database editor

Method Summary

Modifier and Type	Method and Description
(package private) static java.io.File	chooseFile(java.lang.String approve, java.lang.String[]... filters) Bring up a file browser to choose a file.
(package private) static void	close(java.io.Closeable file) Closes a closable closable file/object ignoring null arguments and handling exceptions.
(package private) void	dumpDatabase(java.io.OutputStream out) Dump the main database
(package private) static java.lang.String	escapeString(java.lang.String s, char quote) if null return "null" otherwise surround string with the quote character and escape quote characters with a double quote character.
(package private) int	findIgnoreCase(boolean ignoreCase, java.lang.String s1, java.lang.String s2, int fromIndex) Finds String in another String ignoring case if ignoreCase is true.
(package private) int	getEndOfSqlStatement(java.lang.String s, int start) Find the end of an SQL staement starting at character position start.
(package private) java.lang.String	getWithCharset(java.sql.ResultSet rs, int i) Gets an item from a ResultSet using the statically defined character set.
(package private) static boolean	IdChar(char c)
(package private) void	initList(boolean hidden) Using the sqlite_master and sqlite_temp_master tables construct a list of all tables in the attached databases.
(package private) static int	lineNumber(java.lang.String s, int charPos) Returns the line number of a character position in a string
static void	main(java.lang.String[] args) View or edit a sqlite database.
(package private) void	makeCommandWindow(java.lang.String title, java.lang.String contents) Make a command window for executing sql statements.
(package private) static void	makeTableList(java.lang.String db) Bring up a database editor
(package private) void	putWithCharset(java.sql.PreparedStatement stat, int i, java.lang.String data) Sets a ? item in a PreparedStatement from a string using the java default character set This method originally created blobs created by setBytes causing problems when comparing with numerical values.
(package private) void	queryTable(java.lang.String query, DatabaseEditor.RowProc rowProc) Queries a database and calls a method on each row of the result set.
(package private) java.lang.String	readFileIntoString(java.io.File file) Read an entire file into a String using fileCharset for conversion.
(package private) void	runSchemaDumpQuery(java.io.OutputStream out, java.lang.String query) Dump table etc.
(package private) void	runTableDumpQuery(java.io.OutputStream out, java.lang.String query) Run query and output result followed by a ';' for each row.
(package private) void	showTable(java.lang.String tableName) Show the table with the given name
(package private) static boolean	stringCompare(java.lang.String s, int position, java.lang.String query) Check to see if the String query is at position in String s.

Methods inherited from class javax.swing.JFrame

addImpl, createRootPane, frameInit, getAccessibleContext, getContentPane, getDefaultCloseOperation, getGlassPane, getGraphics, getJMenuBar, getLayeredPane, getRootPane, getTransferHandler, isDefaultLookAndFeelDecorated, isRootPaneCheckingEnabled, paramString, processWindowEvent, remove, repaint, setContentPane, setDefaultCloseOperation, setDefaultLookAndFeelDecorated, setGlassPane, setIconImage, setJMenuBar, setLayeredPane, setLayout, setRootPane, setRootPaneCheckingEnabled, setTransferHandler, update

Methods inherited from class java.awt.Frame

addNotify, getCursorType, getExtendedState, getFrames, getIconImage, getMaximizedBounds, getMenuBar, getState, getTitle, isResizable, isUndecorated, remove, removeNotify, setBackground, setCursor, setExtendedState, setMaximizedBounds, setMenuBar, setOpacity, setResizable, setShape, setState, setTitle, setUndecorated

Methods inherited from class java.awt.Window

addPropertyChangeListener, addPropertyChangeListener, addWindowFocusListener, addWindowListener, addWindowStateListener, applyResourceBundle, applyResourceBundle, createBufferStrategy, createBufferStrategy, dispose, getBackground, getBufferStrategy, getFocusableWindowState, getFocusCycleRootAncestor, getFocusOwner, getFocusTraversalKeys, getIconImages, getInputContext, getListeners, getLocale, getModalExclusionType, getMostRecentFocusOwner, getOpacity, getOwnedWindows, getOwner, getOwnerlessWindows, getShape, getToolkit, getType, getWarningString, getWindowFocusListeners, getWindowListeners, getWindows, getWindowStateListeners, hide, isActive, isAlwaysOnTop, isAlwaysOnTopSupported, isAutoRequestFocus, isFocusableWindow, isFocusCycleRoot, isFocused, isLocationByPlatform, isOpaque, isShowing, isValidateRoot, pack, paint, postEvent, processEvent, processWindowFocusEvent, processWindowStateEvent, removeWindowFocusListener, removeWindowListener, removeWindowStateListener, reshape, setAlwaysOnTop, setAutoRequestFocus, setBounds, setBounds, setCursor, setFocusableWindowState, setFocusCycleRoot, setIconImages, setLocation, setLocation, setLocationByPlatform, setLocationRelativeTo, setMinimumSize, setModalExclusionType, setSize, setSize, setType, setVisible, show, toBack, toFront

Methods inherited from class java.awt.Container

add, add, add, add, add, addContainerListener, applyComponentOrientation, areFocusTraversalKeysSet, countComponents, deliverEvent, doLayout, findComponentAt, findComponentAt, getAlignmentX, getAlignmentY, getComponent, getComponentAt, getComponentAt, getComponentCount, getComponents, getComponentZOrder, getContainerListeners, getFocusTraversalPolicy, getInsets, getLayout, getMaximumSize, getMinimumSize, getMousePosition, getPreferredSize, insets, invalidate, isAncestorOf, isFocusCycleRoot, isFocusTraversalPolicyProvider, isFocusTraversalPolicySet, layout, list, list, locate, minimumSize, paintComponents, preferredSize, print, printComponents, processContainerEvent, remove, removeAll, removeContainerListener, setComponentZOrder, setFocusTraversalKeys, setFocusTraversalPolicy, setFocusTraversalPolicyProvider, setFont, transferFocusDownCycle, validate, validateTree

Methods inherited from class java.awt.Component

action, add, addComponentListener, addFocusListener, addHierarchyBoundsListener, addHierarchyListener, addInputMethodListener, addKeyListener, addMouseListener, addMouseMotionListener, addMouseWheelListener, bounds, checkImage, checkImage, coalesceEvents, contains, contains, createImage, createImage, createVolatileImage, createVolatileImage, disable, disableEvents, dispatchEvent, enable, enable, enableEvents, enableInputMethods, firePropertyChange, firePropertyChange, firePropertyChange, firePropertyChange, firePropertyChange, firePropertyChange, firePropertyChange, firePropertyChange, firePropertyChange, getBaseline, getBaselineResizeBehavior, getBounds, getBounds, getColorModel, getComponentListeners, getComponentOrientation, getCursor, getDropTarget, getFocusListeners, getFocusTraversalKeysEnabled, getFont, getFontMetrics, getForeground, getGraphicsConfiguration, getHeight, getHierarchyBoundsListeners, getHierarchyListeners, getIgnoreRepaint, getInputMethodListeners, getInputMethodRequests, getKeyListeners, getLocation, getLocation, getLocationOnScreen, getMouseListeners, getMouseMotionListeners, getMousePosition, getMouseWheelListeners, getName, getParent, getPeer, getPropertyChangeListeners, getPropertyChangeListeners, getSize, getSize, getTreeLock, getWidth, getX, getY, gotFocus, handleEvent, hasFocus, imageUpdate, inside, isBackgroundSet, isCursorSet, isDisplayable, isDoubleBuffered, isEnabled, isFocusable, isFocusOwner, isFocusTraversable, isFontSet, isForegroundSet, isLightweight, isMaximumSizeSet, isMinimumSizeSet, isPreferredSizeSet, isValid, isVisible, keyDown, keyUp, list, list, list, location, lostFocus, mouseDown, mouseDrag, mouseEnter, mouseExit, mouseMove, mouseUp, move, nextFocus, paintAll, prepareImage, prepareImage, printAll, processComponentEvent, processFocusEvent, processHierarchyBoundsEvent, processHierarchyEvent, processInputMethodEvent, processKeyEvent, processMouseEvent, processMouseMotionEvent, processMouseWheelEvent, removeComponentListener, removeFocusListener, removeHierarchyBoundsListener, removeHierarchyListener, removeInputMethodListener, removeKeyListener, removeMouseListener, removeMouseMotionListener, removeMouseWheelListener, removePropertyChangeListener, removePropertyChangeListener, repaint, repaint, repaint, requestFocus, requestFocus, requestFocusInWindow, requestFocusInWindow, resize, resize, revalidate, setComponentOrientation, setDropTarget, setEnabled, setFocusable, setFocusTraversalKeysEnabled, setForeground, setIgnoreRepaint, setLocale, setMaximumSize, setName, setPreferredSize, show, size, toString, transferFocus, transferFocusBackward, transferFocusUpCycle

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait

Methods inherited from interface java.awt.MenuContainer

getFont, postEvent

Field Detail

serialVersionUID

static final long serialVersionUID

See Also:

Constant Field Values

conn

java.sql.Connection conn

dependents

java.util.ArrayList<javax.swing.JFrame> dependents

dbCharset

static java.nio.charset.Charset dbCharset

fileCharset

static java.nio.charset.Charset fileCharset

BUFSIZ

static final int BUFSIZ

See Also:

Constant Field Values

editFile

java.io.File editFile

backupFile

java.io.File backupFile

trans

static final int[][] trans

currentDirectory

static java.lang.String currentDirectory

tables

java.util.ArrayList<DatabaseEditor.DatabaseTableEntry> tables

list

javax.swing.JList<java.lang.Object> list

hidden

boolean hidden

Constructor Detail

DatabaseEditor

DatabaseEditor(java.sql.Connection conn,
               java.lang.String db,
               boolean hidden)

Create a database editor

Parameters:

conn - the database connection

db - the name of the database

hidden - true if showing hidden tables (always called with false)

Method Detail

close

static void close(java.io.Closeable file)

Closes a closable closable file/object ignoring null arguments and handling exceptions.

Parameters:

file - the file/object to be closed

getWithCharset

java.lang.String getWithCharset(java.sql.ResultSet rs,
                                int i)
                         throws java.sql.SQLException

Gets an item from a ResultSet using the statically defined character set.

Parameters:

rs - the result set to use

i - the column number to fetch

Returns:

the result string or null

Throws:

java.sql.SQLException - Throws if problem with SQLite.

putWithCharset

void putWithCharset(java.sql.PreparedStatement stat,
                    int i,
                    java.lang.String data)
             throws java.sql.SQLException

Sets a ? item in a PreparedStatement from a string using the java default character set This method originally created blobs created by setBytes causing problems when comparing with numerical values. The commented out lines exhibit this behavior. The current setString method does not create blobs. We probably should check and use Strings for numbers and blobs for non-numbers. Could cast the ? to a TEXT via "cast(? as text)"

Parameters:

stat - the prepared statement with ?

i - the index of the ? to be set

data - the string of data or null

Throws:

java.sql.SQLException - thrown if problems with SQLite

readFileIntoString

java.lang.String readFileIntoString(java.io.File file)

Read an entire file into a String using fileCharset for conversion. Note that the conversion must be all at once because characters may straddle buffer boundaries. Returns null on error.

Parameters:

file - the name of the file to be read in

Returns:

the entire contents of the file converted to a String

escapeString

static java.lang.String escapeString(java.lang.String s,
                                     char quote)

if null return "null" otherwise surround string with the quote character and escape quote characters with a double quote character. This will allow using strings in SQL statements and double quotes for identifiers. We now use ? in sql for data - this handles nulls too.

Parameters:

s - the String to escape

quote - the quote character

Returns:

the escaped string

findIgnoreCase

int findIgnoreCase(boolean ignoreCase,
                   java.lang.String s1,
                   java.lang.String s2,
                   int fromIndex)

Finds String in another String ignoring case if ignoreCase is true. If s1 is null, returns -1. Search starts at fromIndex. To search the entire string set fromIndex to 0.

Parameters:

ignoreCase - true if case is to be ignored

s1 - String to search

s2 - String to search for

fromIndex - location to start search

Returns:

index of s2 in s1 or -1 if unsuccessful

showTable

void showTable(java.lang.String tableName)

Show the table with the given name

Parameters:

tableName - the table to display

makeCommandWindow

void makeCommandWindow(java.lang.String title,
                       java.lang.String contents)

Make a command window for executing sql statements. The Execute Selection button will execute the command in the window or the command in the current selection if the current selection is not empty. The Execute Selection button executes the current selection as an SQL command. If there is no selection the entire contents of the window is executed. The Select Next Command button selects the sql command following the current selection. The Break Lines in Selection button breaks long line in the current selection. The Print button prints the edit window to a printer. The Read File button reads the selected file and replaces the window contents with the contents of the file. This operation is undoable. The Write File button writes the window contents to the selected file.

Parameters:

title - the title of the window

contents - the initial contents of the command window

queryTable

void queryTable(java.lang.String query,
                DatabaseEditor.RowProc rowProc)

Queries a database and calls a method on each row of the result set.

Parameters:

query - the query (select statement) to execute

rowProc - a class encapsulating the method to run on each row

runTableDumpQuery

void runTableDumpQuery(java.io.OutputStream out,
                       java.lang.String query)

Run query and output result followed by a ';' for each row. Assume only one column in the result.

Parameters:

out - the OutputStream to be used for output

query - the query string

runSchemaDumpQuery

void runSchemaDumpQuery(java.io.OutputStream out,
                        java.lang.String query)

Dump table etc. in the main database.

Parameters:

out - the OutputStream to be used for output

query - the query string

dumpDatabase

void dumpDatabase(java.io.OutputStream out)

Dump the main database

Parameters:

out - the OutputStream to be used for output

IdChar

static boolean IdChar(char c)

lineNumber

static int lineNumber(java.lang.String s,
                      int charPos)

Returns the line number of a character position in a string

Parameters:

s - the string to search

charPos - the position in the string

Returns:

the line number (one-based) of the character position

stringCompare

static boolean stringCompare(java.lang.String s,
                             int position,
                             java.lang.String query)

Check to see if the String query is at position in String s. This comparison ignores case.

Parameters:

s - the base String to check

position - character position in s to check

query - the string to match

Returns:

true if String query is in s at position

getEndOfSqlStatement

int getEndOfSqlStatement(java.lang.String s,
                         int start)

Find the end of an SQL staement starting at character position start. Only works for valid SQL statements. Note that the discovered SQL statement may not be valid for illegal SQL statements. The complexity is because of the syntax of CREATE TRIGGER statements. This code was taken from the sqlite database C code. This implementation uses a state machine with 8 states: (0) INVALID We have not yet seen a non-whitespace character. (1) START At the beginning or end of an SQL statement. This routine returns if it ends in the START state or reaches the end of the string. (2) NORMAL We are in the middle of statement which ends with a single semicolon. (3) EXPLAIN The keyword EXPLAIN has been seen at the beginning of a statement. (4) CREATE The keyword CREATE has been seen at the beginning of a statement, possibly preceded by EXPLAIN and/or followed by TEMP or TEMPORARY (5) TRIGGER We are in the middle of a trigger definition that must be ended by a semicolon, the keyword END, and another semicolon. (6) SEMI We've seen the first semicolon in the ";END;" that occurs at the end of a trigger definition. (7) END We've seen the ";END" of the ";END;" that occurs at the end of a trigger definition.

Parameters:

s - the String of SQL statements

start - the character position of the beginning of the SQL statement

Returns:

the character position one beyond the end of the discovered statement

chooseFile

static java.io.File chooseFile(java.lang.String approve,
                               java.lang.String[]... filters)

Bring up a file browser to choose a file. Second argument is a String[][] where each array is {desc, ext, ext, ext}. this matches the arguments to Class FileNameExtensionFilter

Parameters:

approve - the label in the approve button

filters - a description of the filters to be used

Returns:

the name of the chosen file or null if no selection

makeTableList

static void makeTableList(java.lang.String db)

Bring up a database editor

Parameters:

db - the name of the database to display

initList

void initList(boolean hidden)

Using the sqlite_master and sqlite_temp_master tables construct a list of all tables in the attached databases. Currently assume table and database names do not contain "." or use "." in db.name Note that the sqlite_master and sqlite_temp_master tables are not in the list of tables so they have to be added separately.

Parameters:

hidden - true if listing hidden tables

main

public static void main(java.lang.String[] args)

View or edit a sqlite database. Note that there are two character sets - one for the database and one for the file system. They both default to UTF-8. Usage: java DatabaseEditor <dbname> [<dbCharset> [<File System charset>]]

Parameters:

args - The command line arguments.