DevQuickStart_GraphDatabase

SUPERCEDED

These instructions are superceded by the later instructions in HowToSetupPeriodAndVariableBasedDatabaseStructure

---

Introduction

The data for the regional time-series graphs are stored in a PostGreSQL database, carbones_graph. These instructions explain how to set up this database carbones_graph on a development machine. This is essential when preparing a development machine to work on the code. These steps would also be useful in deploying the application to a different server.

See also the instructions on setting up Eclipse DevQuickStart_Eclipse.

Details

Pre-requisites

• PostGreSQL
• pgAdmin III (not essential, but a very useful tool for administring PostGreSQL)
• The SQL and TXT files containing the database definition.

TODO we should store the SQL and TXT files in the SVN repository.

Currently they are on the CERC network in the location given below. Copy them to a local directory.

• It is best to avoid deeply nested directories or directory names with spaces.

Location of SQL and TXT files

P:\FM\FM862_CARBONES\Web site\20110506_20yearGraphData\20110729_database_Structure\

1. Create database carbones_graph

2. Run the SQL to create the structure of the database: "structure.sql"

The procedure linegraph_multiregion was added to the SQL on 11 Aug 2011. It is also available at Q:\CRs\Utilities\CARBONES_F862\Website\CR0004 Change CSV to show region data in different columns\ChangedFilesAfterReview\linegraph_multiregion.txt

3. Run the following SQL to insert the reference data from the TXT files

• You will need to change the paths in the SQL to the path to your local directory.

  COPY units("units_id","units_name")
  FROM 'E:/PostgreSQL/units.txt'
  WITH DELIMITER E'\t'
  CSV HEADER;

  COPY variable("variable_id","variable_code","variable_name","units_id","isflux")
  FROM 'E:/PostgreSQL/variable.txt'
  WITH DELIMITER E'\t'
  CSV HEADER;

  COPY region("region_id","region_code","region_name","island","isocean")
  FROM 'E:/PostgreSQL/Region.txt'
  WITH DELIMITER E'\t'
  CSV HEADER;

  COPY temporal_filtering("temporal_filtering_id","temporal_filtering_name")
  FROM 'E:/PostgreSQL/temporal_filtering.txt'
  WITH DELIMITER E'\t'
  CSV HEADER;

  COPY averaging_period("averaging_period_id","averaging_period_name")
  FROM 'E:/PostgreSQL/AveragingPeriod.txt'
  WITH DELIMITER E'\t'
  CSV HEADER;

  COPY series("series_id","variable_id","averaging_period_id","temporal_filtering_id")
  FROM 'E:/PostgreSQL/Series.txt'
  WITH DELIMITER E'\t'
  CSV HEADER;

4. Import the latest graph data. The steps are described here.