# Aspicore GSM Tracker - http://www.aspicore.com
# SQL script to create table for Perl script store_gps_datagrams.pl and the PHP tools

# Change History:
# 2004-11-22 jje - SQL script created for MySQL 4.0
# 2005-03-30 jje - Adapted for MySQL 4.1.1 and above (renamed columns UTC_TIME and UTC_DATE into UTCTIME and UTCDATE)
# 2005-04-04 jje - Added new optional tables for PHP tools

# drop the GPS table if it exists, then recreate it

DROP TABLE IF EXISTS apsi_gps;

# GPS - Main table to contain GPS coordinate data

CREATE TABLE apsi_gps (
	GPSMSGID int AUTO_INCREMENT NOT NULL ,
	TIME_RECEIVED TIMESTAMP  ,		# from server's clock
	PHONE varchar (30) NULL ,		# phone IMEI
	STATUS char (1) NULL ,			# A = valid location fix, V = too few visible satellites
	LATITUDE float NULL ,			# decimal degrees, South is negative
	LONGITUDE float NULL ,			# decimal degrees, West is negative
	SPEED_KNOTS float NULL ,
	COURSE_DEG float NULL ,
	UTCTIME varchar (10) NULL ,		# original UTC time, from GPS
	UTCDATE varchar (6) NULL ,		# original UTC date, from GPS
	LABEL varchar (20) NULL ,		# label entered by the user
	REMOTE_IP varchar (15) NULL ,
	PRIMARY KEY (GPSMSGID)
);

# drop the GSM_CELL table if it exists, then recreate it

DROP TABLE IF EXISTS apsi_gsm_cell;

# GSM_CELL - Optional table to store cell tower information

CREATE TABLE apsi_gsm_cell (
	GSMMSGID int AUTO_INCREMENT NOT NULL ,
	TIME_RECEIVED TIMESTAMP  ,		# from server's clock
	PHONE varchar (30) NULL ,		# phone IMEI
	GPSMSGID int NULL ,				# foreign key to table GPS (cell tower coordinate estimate)
	EVENT_TYPE varchar (3) NULL ,	# "IN" / "OUT"
	CELL_ID int NULL ,
	LAC int NULL ,					# Local Area Code
	MCC int NULL ,					# Country Code
	MNC int NULL ,					# Network Code
	NWNAME varchar (30) NULL ,
	LABEL varchar (20) NULL ,		# label entered by the user
	REMOTE_IP varchar (15) NULL ,
	PRIMARY KEY (GSMMSGID)
);

# drop the TEST table if it exists, then recreate it

DROP TABLE IF EXISTS apsi_test;

# TEST - Optional table to store test messages ("Send GPRS test msg")

CREATE TABLE apsi_test (
	MSGID int AUTO_INCREMENT NOT NULL ,
	TIME_RECEIVED TIMESTAMP  ,		# from server's clock
	PHONE varchar (30) NULL ,		# phone IMEI
	MSG_RAW varchar (80) NULL ,
	REMOTE_IP varchar (15) NULL ,
	PRIMARY KEY (MSGID)
) ;

# drop the PHONE table if it exists, then recreate it

DROP TABLE IF EXISTS aspi_phone;

# PHONE - Optional table to contain user data (phone description) and preferences (tracked IMEI, a friend to be tracked)

CREATE TABLE aspi_phone (
	PHONE varchar (30) NOT NULL ,		# phone IMEI (own phone)
	DESCR1 varchar (30) NULL ,
	DESCR2 varchar (30) NULL ,
	TRACK_PHONE varchar (30) NULL ,		# phone IMEI (phone to be tracked)
	PRIMARY KEY (PHONE)
) ;

# End of file GSM_Tracker_MySQL_tab.sql