Created
December 9, 2014 01:04
-
-
Save ariscop/7981e0a9570b0e08d6bc to your computer and use it in GitHub Desktop.
Postgresql compatible traccar schema
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE application_settings ( | |
id bigserial NOT NULL, | |
registrationenabled boolean NOT NULL | |
); | |
CREATE TABLE devices ( | |
id bigserial NOT NULL, | |
name character varying(255), | |
uniqueid character varying(255), | |
latestposition_id bigint | |
); | |
CREATE TABLE positions ( | |
id bigserial NOT NULL, | |
address character varying(255), | |
altitude real, | |
course real, | |
latitude double precision NOT NULL, | |
longitude double precision NOT NULL, | |
other xml, | |
power real, | |
speed real, | |
"time" timestamp without time zone, | |
valid boolean, | |
device_id bigint | |
); | |
CREATE TABLE user_settings ( | |
id bigserial NOT NULL, | |
speedunit character varying(255) | |
); | |
CREATE TABLE users ( | |
id bigserial NOT NULL, | |
admin smallint, | |
login character varying(255), | |
password character varying(255), | |
usersettings_id bigint | |
); | |
CREATE TABLE users_devices ( | |
users_id bigint NOT NULL, | |
devices_id bigint NOT NULL | |
); | |
ALTER TABLE ONLY application_settings | |
ADD CONSTRAINT application_settings_pkey PRIMARY KEY (id); | |
ALTER TABLE ONLY devices | |
ADD CONSTRAINT devices_pkey PRIMARY KEY (id); | |
ALTER TABLE ONLY positions | |
ADD CONSTRAINT positions_pkey PRIMARY KEY (id); | |
ALTER TABLE ONLY user_settings | |
ADD CONSTRAINT user_settings_pkey PRIMARY KEY (id); | |
ALTER TABLE ONLY users | |
ADD CONSTRAINT users_pkey PRIMARY KEY (id); | |
CREATE INDEX positionsindex ON positions USING btree (device_id, "time"); | |
CREATE INDEX time_idx ON positions USING btree ("time"); | |
ALTER TABLE ONLY devices | |
ADD CONSTRAINT devices_latestposition_id_fkey FOREIGN KEY (latestposition_id) REFERENCES positions(id); | |
ALTER TABLE ONLY positions | |
ADD CONSTRAINT positions_device_id_fkey FOREIGN KEY (device_id) REFERENCES devices(id); | |
ALTER TABLE ONLY users_devices | |
ADD CONSTRAINT users_devices_devices_id_fkey FOREIGN KEY (devices_id) REFERENCES devices(id); | |
ALTER TABLE ONLY users_devices | |
ADD CONSTRAINT users_devices_users_id_fkey FOREIGN KEY (users_id) REFERENCES users(id); | |
ALTER TABLE ONLY users | |
ADD CONSTRAINT users_usersettings_id_fkey FOREIGN KEY (usersettings_id) REFERENCES user_settings(id); | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
:extended_info needs to be changed to xml(:extended_info) in the config, works fine otherwise. xml type lets you query protocol and battery level from the xml blob in other