JSQLiteClient is released under the GPL: http://www.gnu.org/licenses/gpl.html

Please visit http://www.arachnoid.com/JSQLiteClient for more documentation and to acquire the latest version of JSQLiteClient. This help page explains how to use JSQLiteClient, while the home page covers installation issues and certain other topics in greater depth.

For formatting reasons and to properly read these instructions, users may want to temporarily make the JSQLiteClient program frame larger.

Introduction | Features | Command-line Operation | Miscellaneous Notes

JSQLiteClient is a Java program that serves as a SQLIte/SQLIte3 database client (meaning a program that interacts with database files). With JSQLiteClient you can create, author and edit SQLite databases, and conduct sophisticated searches (in database terminology these searches are called "queries"). You can analyze database construction and optimization, and copy generated content to other environments in several formats. You can create, delete, and modify data and tables, and perform many kinds of maintenance tasks using JSQLiteClient's built-in SQLite terminal.

Because JSQLiteClient is written in Java, it will run on virtually any platform. And to run, JSQLiteClient needs the most recent version of Java, available free at http://java.com. Many runtime issues are resolved by simply upgrading to the most recent Java runtime engine.

This section's topics follow the order of the JSQLiteClient tabs one uses to access different features. The topics are:

• Setup/Log 

  This tab allows you to set some program options and read the system log. There is also a font size dropdown list that allows the user to change the font size in the displayed tables, the SQL terminal, and this help page.

  The Setup/Log tab contains the system command/error log that records commands (and possible errors) issued from the query panel and the SQL terminal. Log entries from the query panel are prefixed with "Q:", while those from the SQL terminal are prefixed with "T:".

  One may copy the log to the system clipboard by pressing the Copy Log button. The  Clear Log button allows the user to erase the system log. The Ellipsize checkbox forces the log table lines to fit onto the display by making the lines either longer or shorter, depending on their natural length. In some cases, for example while reading error messages, it's better to deselect Ellipsize.

• Table 

  The Table tab shows the result table from the most recently executed SQLite query. Like the log table described above, if the user enables Ellipsize, the table display will be forced into the program window's width. So, to read all the table's entries in full, one normally wants to disable Ellipsize and use horizontal scrolling to access the full width of each record, but to get an abbreviated overview of the table's content, enable Ellipsize.

  If the user clicks one of the table's rows ("records"), the Edit/Enter tab (explained below) will open, offering the option to change the record's contents.

• Edit/Enter 

  The Edit/Enter tab offers the ability to create new records, delete records, make copies of existing records, and edit or enter record data. But to prevent inadvertent changes to database content, this feature is disabled by default. To enable it, select  Allow Edits at the lower right of the editing display.

  The rows in the editing display are labeled with field names at the left. It's not obvious at first glance, but each of the editing controls accepts multi-line entries — just press Enter to start a new line. When the user changes any of the field content, that field becomes red, showing that an entry has been made but not yet committed to the database. To commit your entries to the database, either press the  Commit button or press Shift+Enter within the editing field. To revert your entries before committing them to the database, press  Cancel.

  After asking for confirmation, the  Delete button will delete the presently selected record from the database. Remember about editing, deleting and similar actions, that there is no "Undo" button for a database table — changes are permanent. So think about backing up tables you intend to edit (see below for more about this).

  The  Copy button will make a copy of the presently selected record within the current table, but this feature can't always work, in which case the  Copy button will be disabled. The reason one can't always copy a record is that copying requires a table to have a primary key, an issue explained below under Table Description .

  The  New button creates a new, mostly-empty record in the present table. I say "mostly" empty because, to save the user some effort and anticipating typical entries, any existing date, time and datetime fields are automatically filled in with the present date and time. It's important to understand that the design of some tables prevents the creation of a new, empty record, a topic beyond the scope of these instructions.

  Navigation and commit

  Because the edit entry controls accept linefeeds and allow internal navigation with the arrow keys, one may move between entry fields with Shift+Up arrow and Shift+Down arrow. A record may be committed to the database either by typing Shift+Enter within an entry control, or by pressing the  Commit button.

  Backup

  Remember about record editing that database content editing is not like word processing and most other computer activities, where there is an "Undo" button for nearly everything. A database table has precisely one state, and no sense of history, so one must (a) be careful, or (b) always have a backup copy of the edited table or the entire database. Actually, I recommend both: (a) be careful, and (b) always have a backup. On that topic, the Table Description  tab described below has a table backup feature whose use I strongly recommend.

  Editing and querying

  Before considering editing a table that results from a selective query, be sure to read the item "Don't mix queries and edits" at the bottom of the next section.

• Query 

  Detailed query example

  Database querying is practically a science, but the simplest examples are easily explained and performed. However, to be able to present meaningful examples, the user needs to have the same table used for the tutorial. So:

  • There is a perfect practice database file table available on my website — just click here to download it.
  • To read this file into JSQLiteClient, put the downloaded file in any convenient location, run JSQLiteClient, and use the provided "File" button (uper left) to locate and load the file.

  Now that the user has the source table for the following examples, we can expect the same outcomes as those presented on this page. To set up for your first query experiments, do this:

  • Select the Query  tab.
  • At the top left of the query display, verify that the correct file has been loaded (i.e. "people.sqlite3").
  • At the top right of the query display, choose the "people" table.
  • Click the  Query button at the bottom of the program display.

  The result should be a table display of 1000 records of make-believe people, with some associated information. This practice table is perfect for writing experimental queries — it's big enough to be interesting, and it's entirely made up, so no real person's information is exposed.

  Now we can write queries. A real-world SQLite query might look like this:

  SELECT `First Name`,`Last Name`,`Gender` FROM `people` WHERE `age` > 25 AND `age` < 50 ORDER BY `Age`

  In fact, the reader can copy the query statement from this page and paste it into the provided SQLite terminal entry control, and it should execute correctly. But such complex queries are difficult to get right when hand-entered, which is the reason for the Query  entry pane — its purpose is to simplify the entry of complex queries.

  The basic outline of the above SQLite query is:

  SELECT (selected fields) FROM (table) WHERE (conditions) (ORDER BY / GROUP BY etc.)

  Using this outline as a guide, the JSQLiteClient query display is organized this way:

  • (selected fields) : from a "Prefix arguments" entry, or if blank, then from the "include" check-boxes at the right of the query pane.
  • (table) : from the table dropdown list at the top of the program window.
  • (conditions) : from the row of entries at the center of the query display, two per field.
  • (ORDER BY / GROUP BY etc.) : from "Postfix Arguments" if an entry is made.

  The first thing to understand is that blank query fields are ignored — only those that have entries are included in the SQLite query.

  Now let's write a query:

  • For this example, the table is assumed to have been preselected to be "people" as above.
  • In the "Age" field entries, enter "> 20" at the left and and "< 30" at the right:

    

  • Now press the  Query button at the bottom of the program display.
  • If the settings and entries have been made correctly, the outcome label at the lower left should say, "Read 161 records `people`". And the "Query" line above the query entries should read:

    SELECT * FROM `people` WHERE ( `Age` > 20 AND `Age` < 30 )

  • Now move to the right side of the query entry pane, to the column of "include" check-boxes, and deselect all fields except "Last Name" , "Age" , and "Music". This is an easy way to choose specific fields for inclusion in a query result.
  • Press  Query again.
  • Notice that the number of selected records is the same (161) but the included fields correspond to the selected check-boxes — "Last Name" , "Age" , and "Music".
  • From this example, we learn that the WHERE clause, defined by the field entries, decides the number of records (rows) in the result, but the check-boxes at the right define the SELECT clause, thus deciding the number and identity of fields (columns).

  Another example. The above example selected 161 records from a set of 1000. That leaves (1000 - 161) 839 that were not selected. Can we design a query that selects only the records not selected by the above query?

  • For the age entry, instead of "Age > 20" and "Age < 30", we want to type in its logical opposite.
  • So for this new query we begin by typing "Age <= 20" and "Age >= 30", and we press  Query.
  • The result isn't 839, in fact, it's zero. We appear to have forgotten something in our new query.
  • What we must understand is that the logical opposite of "Age > 20 And Age < 30" is "Age <= 20 Or Age >= 30".
  • So between the two age entries, click "Or" instead of "And":

    

  • Press  Query again. If the entries have been made correctly, the label at the lower left should say "Read 839 records from tutorial.people" and the query text at the top of the query pane should be:

    SELECT * FROM `people` WHERE ( `Age` <= 20 OR `Age` >= 30 )

  Here's a somewhat more advanced example:

  • Preserve the entries above, don't erase them.
  • In the "Prefix arguments" entry, type "Music, count(*) AS Total"
  • In the "Postfix arguments" entry, type "GROUP BY Music".
  • Press  Query.
  • This should be the result:

    Music	Total
    Baroque	171
    C & W	155
    Classical	169
    Jazz	167
    Rock	177

  • The text form of the query, visible at the "Query:" display at the top of the entry pane, should be:

    SELECT Music, count(*) AS Total FROM `people` WHERE ( `Age` <= 20 OR `Age` >= 30 ) GROUP BY Music

  • This query result tells us how many individuals (from our imaginary population), outside the age range of 20 and 30, favor which kinds of music. That's a rather complex database result, and one arrived at with little effort.
  • This example also shows that:
    • An entry to "Prefix arguments" takes precedence over the field-selection checkboxes, and
    • Relatively simple queries can produce interesting and useful results.

  Regular Expressions

  The SQLite database engine includes syntax for a regular expression function but doesn't provide a definition — individual SQLite-compliant client programs must define their own function if they want to use this feature. JSQLiteClient defines such a regular expression function, therefore a query or view that includes the "REGEXP" keyword will be processed as one would expect:

  SELECT * FROM (tablename) WHERE (fieldname) REGEXP "(regular expression)"

  This property of SQLite has a drawback — a database that provides results reliant on REGEXP (in a custom view, for example) simply won't work in an environment that doesn't define REGEXP. But when writing code to process database tables, for example in Python, one can simply select the entire table without filtering, then process the results using Python's regular expression features, which are more flexible in any case.

  Query Export Formats

  There is one more important query panel feature — the ability to copy the displayed table onto the system clipboard in various forms, using one of the buttons at the top of the query display:

  •  Copy TSV: produces a a Tab-Separated-Values (TSV) plain-text table and places it on the system clipboard. This form is ideal for pasting into a spreadsheet program.
  •  Copy HTML: produces an HTML-formatted document and places it on the system clipboard (this is how the query result table above was created)
  •  Browser: launches the system browser showing a Web page version of the table.

  Backtick database, table and field names

  While using JSQLiteClient, for most entries the query entry code will manage this issue automatically, but at times the user may have to manually type in a query, as for example when using the prefix and postfix entry fields. When entering a "raw query" without any automatic formatting, remember that nearly everything but the actual argument should be enclosed in `backticks`. Example:

  SELECT `City`,`State`,`Zipcode` FROM `table` WHERE `City` = "Jefferson" ORDER BY `Zipcode`

  This may seem like overkill, and SQLite would have accepted this particular example without the backticks, because none of the names have embedded spaces. But in the general case, and especially when a query raises an incomprehensible error message, check for a name that's not enclosed in backticks.

  Quote text arguments that include spaces

  When making query entries, remember that numerical arguments don't need to be quoted:

  `Age` > 20

  but text arguments may sometimes need to be quoted:

  `Name` = "John Smith"

  This is a very common source of SQLite errors among new users, and if one were to omit the quotes around "John Smith", the SQLite error message would only sow confusion:

  Unknown column 'John' in 'where clause'

  Numerical arguments don't need to be quoted, but it's not an error to quote them. Some SQLite old hands adopt the habit of quoting all arguments, regardless of type.

  Different kinds of quotation

  The above example showed a typical SQLite query using `backticks`, plus an argument enclosed in normal quotation marks. The choice of quotation mark might not seem important, but unfortunately it is.

  • Database, table and field names should be quoted with `backticks`, but never 'single quotes' or "double quotes" (to find out why, try it :) ).
  • Text arguments may sometimes need to be enclosed in either 'single' or "double" quotes.

  Why are there two acceptable quotation marks for arguments? Easy to explain — If an argument contains one of the two acceptable quotation marks, one can avoid ambiguity by enclosing it with the other:

  • "That's not fun at all."
  • 'He said his name was "John".'

  In the special case where an argument contains both kinds of quotation mark, the user should use a special escape notation:

  • " John said, \"That's terrible!\" "
  • ' John said, "That\'s terrible!" '

  Special characters

  The query entry controls (and the SQLite terminal entry control to be described below) are single-line entry controls, which means they won't accept tabs or linefeeds. So how does one use tabs or linefeeds as part of a query? Easily answered — to enter a linefeed into a query, you "escape" it — you type a reverse-slash before the identifier, example \n for linefeed. Here are the special characters that are accepted this way:

  • \t : tab
  • \b : backspace
  • \n : linefeed
  • \r : carriage return
  • \f : formfeed

  Entry history

  Because the "Prefix" and "Postfix" text controls (top and bottom of the query entry pane) are often used to customize queries, they have a history feature and this history is preserved between program runs. To access the history, click in the control of interest and press the up-arrow and down-arrow keys to browse past entries. To clear this history, click the  Clear H button at the upper right. For other query entry controls, the up-arrow and down-arrow keys navigate between fields.

  Navigation and commit

  Because the query entry controls are single-line, they don't use the up and down arrow keys. This means one may move between entry fields with the up-arrow and down-arrow keys. And a query may be executed by typing Enter within an entry control, or by pressing the  Query button at the bottom of the display.

  Don't mix queries and edits

  Before leaving the topic of queries, I should add that editing activities and querying activities are so different that users should try to avoid mixing them. The most reliable table editing is against a full table with all its records and fields on display, while the most interesting queries involve only a small part of a complete table. Editing a table that is the outcome of a complex query is a tricky business, best avoided.

  Just one example. While editing records, the user may want to create a new, empty record to fill with information. But if the displayed table results from a selective query, the new record may not appear in the table listing, and without an error message to explain why not. The reason might be that the current query's arguments exclude the added record. (I mention this because I had the experience.)

  To avoid these sorts of problems, before editing records and in most cases, press the  Clear Q button at the upper right in the query dialog, then press  Query again.

  An exception to the above rule would be a large table that takes too long to reload after each edit (because of SQLite's automatic content processing features, JSQLiteClient always reloads a table after each edit). In that case a selective query makes sense because it speeds up the editing process. But the user needs to remember that a query that speeds up edits may also exclude records the user expects to see.

• Table Description 

  The TableDescription tab shows the internal structure of the selected table and offers some tools for table management.

  One of the more important fields a database table should possess is a primary key. A primary key, unique to each record, allows much faster database operation as well as avoiding one of the more annoying database behaviors — the inability to distinguish between records. In fact, without a primary key, JSQLiteClient won't allow the user to copy records within a table, because of the good possibility that the database won't be able to distinguish the copy from the original.

  For a simple table with a handful of records, all of them clearly distinguishable, this is not an issue. But as tables become larger, the primary key issue also becomes larger. JSQLiteClient has a simple solution to this problem, located on the TableDescription tab — the  Add Key button that will add a primary key to the currently selected table.

  Another equally important feature is the  Backup button, which will make a backup copy of an entire table. This is an important safeguard against data loss when used in advance of database editing. The  Backup button creates an exact copy of a table, with all its properties and data intact. The copy table has the name of the original, with "_backup" appended.

• SQL Terminal 

  The  SQL Terminal is for advanced database activities, and should be used carefully. There are safeguards elsewhere in JSQLiteClient, but they can't protect against an error when using the SQL terminal. It cannot be overemphasized that a few careless keystrokes in the terminal can wipe out an entire database.

  The JSQLiteClient SQL terminal resembles a standard MySQL utility, the "mysql" command-line utility, but it isn't exactly the same. The terminal built into JSQLiteClient can do things the standard utility cannot (like reading URLs), and vice versa — there are some things the standard MySQL command-line utility can do that the built-in utility cannot. The reason for including the built-in tool is so that JSQLiteClient will function exactly the same on any platform for which there is a Java runtime engine, including platforms that don't have the standard command-line utility installed.

  Remember about the SQL terminal that, because it has a single-line entry control and therefore cannot accept control characters, it instead accepts the escaped "Special Characters" described in the Query section above.

  The SQL terminal is ideal for certain database maintenance tasks, and the user will probably find it convenient and easy to use — but do remember that there are no safeguards against a mistyped command.

  The terminal has a history feature, a record of past entries that is accessible using the arrow keys, and this history is preserved between program runs. The terminal also has a button to copy the contents of the display to the system clipboard for use elsewhere.

• Help 

  This page is on the help pane, a useful utility that allows full-text searching and that will launch the system browser whenever the user clicks a hyperlink. It should be possible to read this help file while familiarizing oneself with JSQLiteClient, by switching tabs between the help pane and another tab of interest.

JSQLiteClient has some command-line arguments that simplify launching the program with a particular configuration, or one of many configurations for different purposes. The arguments are:

Usage: -f "name of font" -d database -t table
       -r read table -h help
        

• -f (fontname) : forces use of a particular font for table, terminal and editing displays. In most cases this won't be needed — Java knows how to access system fonts, and if a suitable Unicode font is installed on your system, chances are Java (and JSQLiteClient) will be able to access it.
• -d (database name) : as above.
• -t (table name) : as above.
• -r (read table) : This option causes a default query to be executed when the program starts. This, along with the other command-line arguments, has the effect of automating the launch process, so at startup the program reads and displays the table chosen by the user.
• -h (help) : prints the above option list and exits.

Unicode issues

• A modern computer program must support Unicode, and this goes double for a database program. JSQLiteClient fully supports Unicode text, but for correct display it requires that your system have Unicode font support. What this means is that JSQLiteClient will properly manage Unicode database content, but won't necessarily display it correctly unless the system has Unicode font support. Here are some sources for Unicode fonts:

  • Bitstream Cyberbit, a good Unicode font, meaning it has a large, but not complete, collection of glyphs. The link includes sources for download.
  • Arial Unicode MS, a better font than that above, which includes all the characters in version 2.0 of the Unicode standard, but apparently not free. It is available from various sources on the Web, but its standing and ownership are unclear.
  • Code2000, an ambitious, seemingly comprehensive, but now-abandoned project. Various versions of this font are available online, including some named "Code2001" and "Code2002".

Similarities with the "sqlite3" command-line utility

• Users familiar with the sqlite3 command-line application may recognize some of its behaviors in my terminal application, and may think I'm simply running the external application with a Java front end. But no. Because of portability concerns, I decided to write my own code, and it resembles the MySQL command-line app only superficially. My Java version has the advantage that it will run on any platform exactly the same, and I don't have to try to locate an external application that may not even be installed on some systems.

Program state preservation

• JSQLiteClient maintains a configuration file located on your system at (configuration path). In this file is a pretty complete representation of the program's state — server name and access port, user name (no password is saved), and a number of user-settable options that I thought it desirable to preserve between program runs. Over the years I have increased the number of settings I wanted to preserve, and I've gradually developed better ways to save and restore program settings. This program's configuration file preserves a great number of things, such as the most recently entered query, the location and size of the program window, the last dialog tab the user selected, the history list (past entries) from the SQL terminal, and many more — even the last scroll position on this help page.

Partition bar

• Some JSQLiteClient displays are partitioned by a horizontal bar. This bar's position can be raised/lowered to suit the user's requirements (just drag the mouse cursor on the bar). The user's choice is preserved in the program configuration, our next topic.

Saved Web pages

• When the user presses the  Browser button, JSQLiteClient generates a Web page, saves it on your system in (webpage path), and launches the system browser to display it. This means that, over time, Web pages will accumulate in the above directory and the user may want to either move these pages for other uses, or delete them.

JSQLiteClient resources

Here are some JSQLiteClient-related resource locations:

Resource	Location
JSQLiteClient Home Page	http://arachnoid.com/JSQLiteClient
JSQLiteClient version	2.1
User home directory	(user home directory)
JSQLiteClient Web page directory location	(webpage path)
JSQLiteClient configuration file location	(configuration path)
Currently running JSQLiteClient application location	(application path)

Bug reports

• If you find a bug in JSQLiteClient, please visit http://arachnoid.com/messages and leave a report. Please make sure what you report is actually a bug in the program.