GpsMaster/Documentation/Database

Versions 0.62.00 and above allow GPS files to be stored persistently in a relational database.

Database Support is still very basic.

The purpose of this feature is to

• provide a compact, tabular overview over a large number of tracks
• allow reporting features in future versions, like mileage per year and activity.
• enable a group of users to store their tracks in a central repository
• etc

Supported RDBMs

GpsMaster has been tested to work with the following RDBMs:

• Postgresql 8.1.23
• SQLite 3.7.15

JDBC drivers for PostgreSQL and SQLite are included in GpsMaster.

Other RDBMS

Other RDBMS are supposed to work via appropriate JDBC drivers. Include the path to .jar file containing the database-specific JDBC driver in the CLASSPATH environment variable.

If you manage to configure a different RDBMs like MSSQL, Mysql, Oracle etc., please send a notice to info @ gpsmaster.org.

Setup

To enable database support, enter the connection information in the <dbConfig> section of the GpsMaster.config file:

<dbConfig>
<compression>false</compression>
<DSN>jdbc:postgresql://dbserver/gpsmaster</DSN>
<password>gpsmaster</password>
<username>gpsmaster</username>
</dbConfig>

The <compression> parameter determines if compression is to be used to store the GPS data (content of the GPS file) in the database.

PostgreSQL

DDL script:

CREATE TABLE dat_gps
(
  id bigint NOT NULL,
  "name" character varying(255),
  color bigint DEFAULT 0,
  start_dt timestamp with time zone,
  end_dt timestamp with time zone,
  distance double precision NOT NULL DEFAULT 0,
  duration bigint,
  min_lat double precision NOT NULL DEFAULT 0,
  max_lat double precision NOT NULL DEFAULT 0,
  min_lon double precision NOT NULL DEFAULT 0,
  max_lon double precision NOT NULL DEFAULT 0,
  "activity" character varying(25),
  loader_class character varying(100),
  prog_version character varying(100),
  data bytea NOT NULL,
  source_urn character varying(255),
  user_id bigint NOT NULL DEFAULT 0,
  compressed boolean NOT NULL DEFAULT false,
  entry_dt timestamp with time zone NOT NULL DEFAULT now(),
  checksum character varying(32),
  CONSTRAINT pk_id PRIMARY KEY (id)
)
WITHOUT OIDS;
ALTER TABLE dat_gps OWNER TO gpsmaster;

SQLite

DDL script:

CREATE TABLE dat_gps
(
id 		integer primary key,
color 		integer,
name		text,
start_dt		integer,
end_dt		integer,
distance	integer NOT NULL DEFAULT 0,
duration	integer NOT NULL DEFAULT 0,
min_lat		real NOT NULL DEFAULT 0,
max_lat		real NOT NULL DEFAULT 0,
min_lon		real NOT NULL DEFAULT 0,
max_lon	real NOT NULL DEFAULT 0,
activity		text,
loader_class	text,
prog_version	text,
data		BLOB NOT NULL,
source_urn	text,
user_id		integer NOT NULL default 0,
compressed	integer NOT NULL default 0,
entry_dt		integer,
checksum	text
)