-------------------------------------------------------------------------------
--  The Astronomia project                  http://astronomia.sourceforge.net/
--  
--                                                    (c)2002 Yannick Tailliez
--                                      Yannick.Tailliez@users.sourceforge.net
--
-- Under GNU-GPL License (see http://www.gnu.org)
-------------------------------------------------------------------------------
-- This SQL script make the large part of the database
-- form scratch. This is for use with Mysql embeeded (v4.0).
--
-- Naming convention :
-- All sql name are lower case, the tables names begin with a Upper case 
-- character.
-------------------------------------------------------------------------------

-- ----------------------------------------------------------------------------
-- List of the planets
-- shortname is a id
-- name is the name of the planet in English.
--
-- 

CREATE TABLE Planets ( shortname CHAR(3) PRIMARY KEY , name VARCHAR(7) NOT NULL UNIQUE );

INSERT INTO Planets VALUES('MER','Mercury');
INSERT INTO Planets VALUES('VEN','Venus');
INSERT INTO Planets VALUES('EAR','Earth');
INSERT INTO Planets VALUES('MAR','Mars');
INSERT INTO Planets VALUES('JUP','Jupiter');
INSERT INTO Planets VALUES('SAT','Saturn');
INSERT INTO Planets VALUES('URA','Uranus');
INSERT INTO Planets VALUES('NEP','Neptune');
INSERT INTO Planets VALUES('PLU','Pluto');
                     
-- ----------------------------------------------------------------------------
-- List vsop87 bodies
-- shortname is a id, the same of the Planets table for common bodies.

CREATE TABLE Vsop_body ( shortname CHAR(3) NOT NULL PRIMARY KEY, body_key TINYINT NOT NULL);

INSERT INTO Vsop_body VALUES('SUN',0);
INSERT INTO Vsop_body VALUES('MER',1);
INSERT INTO Vsop_body VALUES('VEN',2);
INSERT INTO Vsop_body VALUES('EAR',3);
INSERT INTO Vsop_body VALUES('EMB',-3);
INSERT INTO Vsop_body VALUES('MAR',4);
INSERT INTO Vsop_body VALUES('JUP',5);
INSERT INTO Vsop_body VALUES('SAT',6);
INSERT INTO Vsop_body VALUES('URA',7);
INSERT INTO Vsop_body VALUES('NEP',8);
            
-- ----------------------------------------------------------------------------
-- Information for each version of vsop87 theory
-- version is the version of the theory ' ' and form 'A' to 'E'
-- center is the center of the coordonate system of the serie 
--    'H'eliocentric  or  'B'arycentric
-- equinox is the dynamical equinox and ecliptic use by the serie
--    'S' J2000.0  or  'M' of the date
-- result is the data provied by the serie
--    'O'rbital elements  or  'R' rectangular coordonate or
--    'R' spherical coordonate 

CREATE TABLE Vsop_info ( version CHAR(1) NOT NULL PRIMARY KEY,
                         center  CHAR(1) NOT NULL,
                         equinox CHAR(1) NOT NULL,
                         result  CHAR(1) NOT NULL
                       );

INSERT INTO Vsop_info VALUES(' ','H','S','O');
INSERT INTO Vsop_info VALUES('A','H','S','R');
INSERT INTO Vsop_info VALUES('B','H','S','S');
INSERT INTO Vsop_info VALUES('C','H','M','R');
INSERT INTO Vsop_info VALUES('D','H','M','S');
INSERT INTO Vsop_info VALUES('E','B','S','R');

-- ----------------------------------------------------------------------------
-- All terms of all series of all versions
-- body_key see Vsop_body.key
-- version see Vsop_info.version
-- variable : same value as provide by vsop87
--    unit : au for distances, radians for angles
--    - for the elliptic coordinates (main version) :
--      1 : semi-major axis
--      2 : mean longitude
--      3 : k = e cos(p)                  e : eccentricity
--      4 : h = e sin(p)                  p : perihelion longitude
--      5 : q = sin(g) cos(G)             g : semi-inclination
--      6 : p = sin(g) sin(G)             G : ascending node longitude
--    - for the rectangular coordinates (versions A,C,E) :
--      1 : X
--      2 : Y
--      3 : Z
--    - for the spherical coordinates (versions B,D) :
--      1 : Longitude
--      2 : Latitude
--      3 : Radius
-- rank : rank of the term in the serie (same variable, body and alpha);
-- alpha, a, b, c : Data of the serie
 
CREATE TABLE Vsop_data ( body_key TINYINT NOT NULL,
                         version CHAR(1) NOT NULL,
                         variable TINYINT NOT NULL,
                         rank SMALLINT NOT NULL,
                         alpha TINYINT UNSIGNED NOT NULL,
                         a DECIMAL(18,11) NOT NULL,
                         b DECIMAL(14,11) NOT NULL,
                         c DECIMAL(20,11) NOT NULL,
                         PRIMARY KEY (body_key, version, variable, rank, alpha)
                       );

-- See the separate file to insert the vsop data

-- ---------------------------------------------------------------------------
COMMIT;