Fable Database Support

Fable has database support for Apache Derby, a relational database implemented in Java. It supports either the embedded JDBC driver or the network client JDBC driver. The database is controlled by preferences in Window->Preferences->GrainSpotter->Run. There is also a simple SQL console where you can enter SQL commands. Currently only GrainSpotter uses the Fable database, but the database implementation itself is independent of and separate from GrainSpotter.

Preferences

The database is controlled by preferences in Window->Preferences->GrainSpotter->Run. Note that Fable reads the database information on startup, so any values you change require a restart of Fable to be seen. The default preferences are:

Database Preferences

Enabled

The database is only used if Enabled is checked.

Embedded

If Embedded is checked, then the org.apache.derby.jdbc.EmbeddedDriver is used, otherwise the org.apache.derby.jdbc.ClientDriver is used. With the embedded driver, Fable starts the database server. With the client driver the server is started externally and independently of Fable. This must be done before Fable is started. The embedded driver is simpler to use, but has the disadvantage that it may only be accessed from the same JVM (which means only from Fable itself). Only one instance of Fable can use it at a time. With the client driver other applications can be accessing the database at the same time, and it can be available when Fable is not running or from other instances of Fable. Unless you are experienced, it is probably better to use the embedded driver. See the Derby documentation for more details. In the future Fable may extended to other databases by providing more driver options.

URL

The URL determines the connection; that is, what database is accessed. The default should work to get started. An example of a URL that connects to a network data base at C:\Derby\Data\Fable\GrainSpotterDB that is started externally is:

jdbc:derby://localhost:1527/C:\Derby\Data\Fable\GrainSpotterDB;user=mary

Note that if user is specified in the URL then the default schema will be the user (e.g. MARY in the example). Otherwise it is APP. This is independent of the values for User and Password below.

User

The name of the user if authentication is used. The default should work OK with the embedded driver.

Password

The password if authentication is used. Notice that in this implementation of Fable the password is presented in clear text and is not secure. The default should work OK with the embedded driver.

SQL Console

Fable supports a simple console for querying the database. Select Tools->SQL Console to open it. You will see a prompt in the window :

sql >

You can type SQL statements in the console to query and control the database. SQL is a powerful yet simple to use query language. If you need help on SQL refer to the web, e.g. W3 Schools SQL

Here are some examples of queries you can do and their outputs :

Example 1 - select * from grains

sql > select * from grains

Results of query select * from grains

GRAIN GVE_EXPECTED GVE_MEASURED IA U11 U12 U13 U21 U22 U23 U31 U32 U33

-------------------------------------------------

1 227 55 0.0936 0.270058267 -0.962618139 -0.020853092 0.762187999 0.226961938 -0.606265399 0.588334928 0.147833007 0.79498894

2 227 77 0.428 0.333347238 -0.830150018 0.446912258 -0.548771086 -0.556294928 -0.624008212 0.766635451 -0.037241111 -0.641001705

Example 2 - select grain,gvector,h,k,l from grainfits where h=1

sql > select grain,gvector,h,k,l from grainfits where h=1

Results of query select grain,gvector,h,k,l from grainfits where h=1

GRAIN GVECTOR H K L

-------------------------------------------------

1 101 1 -3 -1

1 99 1 -3 1

1 244 1 -5 -1

1 245 1 -5 1

2 8 1 -1 1

2 96 1 -1 3

2 242 1 -1 5

Some Useful SQL Statements

The following are some useful SQL statements to get you started.

values current schema Determine the current schema
select * from sys.systables where tabletype='t' See what user tables are created
select * from grains where fid=0 Show the grains where the FID column is 0
delete from files where fid=0

Delete all rows from the FILES table where the FID column is 0

drop table app.files
drop table app.grainfits
drop table app.grains
drop schema app restrict
Clear out tables with the names FILES, GRAINFITS, and GRAINS from the APP schema, then remove the schema.

GrainSpotter Database

If databases are enabled in Fable, then whenever a .log file is read, data are put into three tables, FILES, GRAINS, and GRAINFITS. If these tables do not exist, they are created. The FILES table contains information about the file and the time it was read. The GRAINS table holds the grain information. The GRAINFITS table holds information about the predicted and measured g-vectors. All the tables have a FID column. This column can be used to associate the data with the .log file that was read and the time it was read. It can thus be used to distinguish data from different files.

GrainSpotter Database Tables

The following tables have been defined in the database for GrainSpotter :

Table 1 - FILES

Holds the .log file information. It has the following structure :

fid

int

filename

string

timestamp

string

The SQL statement that creates it is:

create table files (fid int, filename varchar(32672), timestamp timestamp)

 

Table 2 - GRAINS

Holds the grain information. It has the following structure :

fid

int

grain

int

gve_expected

int

gve_measured

int

ia

double

u11

double

u12

double

u13

double

u21

double

u22

double

u23

double

u31

double

u32

double

u33

double

The SQL statement that creates it is:

create table grains (fid int, grain int, gve_expected int, gve_measured int, ia double, u11 double, u12 double, u13 double, u21 double, u22 double, u23 double, u31 double, u32 double, u33 double)

 

Table 3 - GRAINFITS

Holds information about the g-vectors associated with the grains. It has the following structure :

fid

int

grain

int

gvector

int

h

int

k

int

l

int

h_pred

int

k_pred

int

l_pred

int

dh

int

dk

int

dl

int

tth_meas

double

tth_pred

double

dtth

double

omega_meas

double

omega_pred

double

domega

double

eta_meas

double

eta_pred

double

deta

double

ia

double

The SQL statement that creates it is:

create table (fid int, grain int, gvector int, h int, k int, l int, h_pred double,k_pred double, l_pred double, dh double, dk double, dl double, tth_meas double, tth_pred double, dtth double, omega_meas double, omega_pred double, domega double, eta_meas double, eta_pred double, deta double, ia double)