1. Understand the Schema of your Database
Open the terminal in the location of your .db file.
Enter the following command to start the SQLite Console.
sqlite3 NameOfDatabase.db
1.1 All tables
Then give the following command to the console:
.schema
This will give you all the information you need about all of your tables, including the data type of the fields. In other words, the above command will give you, your database schema
.
An output example of the above command is the following:
CREATE TABLE log (ID INTEGER PRIMARY KEY AUTOINCREMENT, userID INTEGER, cardID INTEGER, eventID INTEGER, nameOnTicket TEXT, pricePaid REAL);
CREATE TABLE card (cardID INTEGER PRIMARY KEY AUTOINCREMENT, cardNum TEXT, securityCode TEXT, expiryMonth INTEGER, expiryYear INTEGER, addressID INTEGER, userID INTEGER);
It actually returns the command to re-create the tables, so that is also handy if you would like to output the queries
to re-create your tables or to create a documentation for your database/application, but also to understand the structure and the table of your database.
1.2 Specific table
Additionally, you can see the schema of a specific table, using the following command:
.schema TableName
Which will return back the schema of the TableName
table.
2. Integrate SQLite with Eclipse
Another option is to integrate your SQLite Database with Eclipse bellow you can find the steps to do that. The steps bellow have been copied here from the official Eclipse wiki, that you can find here.
1) Download the SQLite drivers from here. The actual zip file with the
driver is at 3. Expand the zip somewhere locally and note the
location.
2) Put the sqlite_jni.dll from the zip into your JRE's bin directory.
The driver requires this file to be in the java library path.
3) In Eclipse with DTP 1.0 installed (preferably the final build or a
nightly build dated 110806 or later), go to the Preferences
(Window->Preferences) and select the Connectivity->Driver Definitions
page.
4) Select the "Generic JDBC" category in the Available Driver
Definitions tree and click "Add...".
5) Select "Generic JDBC Driver->Generic JDBC Driver" in the Available
Driver Templates tree. Give the new generic JDBC driver a name like
"javasqlite JDBC driver". Click OK.
6) Click "Add Jar/Zip" and select the sqlite.jar from the driver zip
you expanded in step 1. Click Open.
7) In the Properties table, select the Driver Class property and click
the "..." button. If the jar is accessible, you will see a dialog
appear with at lease one class in the list. Select
"SQLite.JDBCDriver". Click OK.
8) Also in the Properties table, select the Driver URL property and
type the following: jdbc:sqlite:/DRIVE:/dirA/dirB/dbfile
9) Click OK on the Edit Driver Definition dialog. You should see your
new driver appear in the driver list on the Driver Definitions
preference page.
10) Click OK to close the Preferences dialog.
11) If the Data Source Explorer is not open, open the
Connectivity->Data Source Explorer view from the Window->Show View
menu or open the Database Development perspective from the
Window->Open Perspective.
12) In the Data Source Explorer, right-click on the Databases category
and select New...
13) In the New Connection Profile wizard's Wizard Selection Page,
choose the SQL Model-JDBC Connection entry in the list and click Next.
14) Give your new profile a name like "SQLiteTestDB". Click Next.
15) In the "Select a driver from the drop-down" combo box, select your
new SQLite driver definition. Modify the file path in the sample URL
to match the path to your local SQLite database.
16) Click "Test Connection" to verify you can connect to your
database.
17) Click Finish to create the profile.
18) In the Data Source Explorer, right-click on the new profile and
select Connect. You should see content appear in the tree beneath the
profile. Browse through your database to view available tables and
their columns.