[Treebase-guts] SF.net SVN: treebase:[513] trunk/treebase-core/db

[<vga...@us...>]

Revision: 513
          http://treebase.svn.sourceforge.net/treebase/?rev=513&view=rev
Author:   vgapeyev
Date:     2010-02-17 22:54:26 +0000 (Wed, 17 Feb 2010)

Log Message:
-----------
Misc, including
  - a cumulative script for creating a fresh DB (init_db_uptodate.pg)
  - 3 pre-defined user records in a fresh DB

Modified Paths:
--------------
    trunk/treebase-core/db/README.txt
    trunk/treebase-core/db/TODO.txt
    trunk/treebase-core/db/snapshots/0000_DATA_before_patches_start.sql

Added Paths:
-----------
    trunk/treebase-core/db/init_db_uptodate.pg

Modified: trunk/treebase-core/db/README.txt
===================================================================
--- trunk/treebase-core/db/README.txt	2010-02-17 22:50:46 UTC (rev 512)
+++ trunk/treebase-core/db/README.txt	2010-02-17 22:54:26 UTC (rev 513)
@@ -49,7 +49,9 @@
 was only applied to the development DB instance.  When the patch with an error was already 
 applied to production databases, it could be more prudent to develop a new, error-fixing patch. 
 
+When adding a patch, add a line into init_db_uptodate.pg as well. 
 
+
 Applying a patch
 ----------------
 
@@ -79,21 +81,25 @@
 
 Data snapshot is trickier.  The best bet right now is probably by hand-modifying the previous data snapshot. 
 
+After creating a new snapshot, update init_db_uptodate.pg: change the names of the snapshot scripts
+and remove all \i commands for the patch scripts. 
 
+
 Creating a fresh DB
 ------------------
 
+Use the init_db_uptodate.pg script, which should create the DB from the most recent snapshot 
+and apply all the subsequent patches: 
+
 Use the most recent snapshot: 
 
-psql -d yourdb -U yourusername -f nnn_SCHEMA_xxx.sql
-psql -d yourdb -U yourusername -f nnn_DATA_xxx.sql
+psql -d yourdb -U yourusername -f init_db_uptodate.pg
 
 OR 
 
 psql -d yourdb -U yourusername
 yourdb=> begin transaction;
-yourdb=> \i nnn_SCHEMA_xxx.sql
-yourdb=> \i nnn_DATA_xxx.sql
+yourdb=> \i init_db_uptodate.pg
 yourdb=> commit;  
 
 

Modified: trunk/treebase-core/db/TODO.txt
===================================================================
--- trunk/treebase-core/db/TODO.txt	2010-02-17 22:50:46 UTC (rev 512)
+++ trunk/treebase-core/db/TODO.txt	2010-02-17 22:54:26 UTC (rev 513)
@@ -24,6 +24,8 @@
 Pending data cleanups (on staging):
 ===================================
 
+[Could be worthwhile to do PRIOR to Dec 2009 and Feb 2010 migrations.]
+
 * Remove junk, particularly that created by tests
 
 * Known junk: Submission 22 and its related records
@@ -36,7 +38,7 @@
        (study 22 and submission 22 happen to be the same)." 
      VG: these IDs are from end-of-Jan treebasedev; the current treebasestage may have fewer.   
 
-* Replace "owner" nulls by the special "migration" owner (see Bill's message of around 2010-02-02)
+* Replace "owner" nulls (in submission.user_id?) by the special "migration" owner (see Bill's message of around 2010-02-02)
     "Also because we have reverted to an older instance of the data, the migrated records contain have NULL in the user_id of the submission table. This causes null pointer errors for the unit tests. To fix the problem, I did the following:
      1. I created a user with username "migration" and email address "mig...@tr...". This is the dummy "user" who will now "own" all migrated submissions that lack an owner.
      2. I got the user_id for the "migration" user, and it happened to be 9955
@@ -48,5 +50,6 @@
 ============================
 
 * Synch hibernate_sequence and {sub_matrix,sub_treebaseblock}.collection_id
+  [No need if prod is restored from a dump of stage made after ~2010-02-13]
 
     
\ No newline at end of file

Added: trunk/treebase-core/db/init_db_uptodate.pg
===================================================================
--- trunk/treebase-core/db/init_db_uptodate.pg	                        (rev 0)
+++ trunk/treebase-core/db/init_db_uptodate.pg	2010-02-17 22:54:26 UTC (rev 513)
@@ -0,0 +1,12 @@
+-- Populates an empty DB with tables and dictionary data
+-- corresponding to the most recent snapshot and all subsequent patches. 
+
+\i snapshots/0000_SCHEMA_before_patches_start.sql
+\i snapshots/0000_DATA_before_patches_start.sql
+
+\i patches/0001_constraint-names.sql
+\i patches/0002_drop-geospot_id_sequence.sql
+\i patches/0003_rename-stepmatrixelement-pk.sql
+\i patches/0004_create-indexes.sql
+
+

Modified: trunk/treebase-core/db/snapshots/0000_DATA_before_patches_start.sql
===================================================================
--- trunk/treebase-core/db/snapshots/0000_DATA_before_patches_start.sql	2010-02-17 22:50:46 UTC (rev 512)
+++ trunk/treebase-core/db/snapshots/0000_DATA_before_patches_start.sql	2010-02-17 22:54:26 UTC (rev 513)
@@ -7,8 +7,9 @@
 --  - Converted commented-out DB2 RESTART statements on PK columns to working Postgres RESTART statements on sequences.  
 --  - Added (6, 1, 'Retracted') to citationStatus 
 --  - Removed initial data from tables Person, User, Submission, Study
+--  VG 2010-02-27: initial entries into "user" and "person" tables
+ 
 
-
 insert into citationStatus(citationstatus_id, version, description) values 
 (1, 1, 'In Prep'),
 (2, 1, 'In Review'),
@@ -228,6 +229,22 @@
 alter sequence userrole_id_sequence restart with 4;
 
 
+insert into person(person_id, version, lastname, firstname, mname, phone, email) 
+    values
+    (1, 0, 'Tb2', 'Admin',  '', '', 'tb2...@no...'),
+    (2, 0, 'Tb2', 'User',   '', '', 'tb...@no...'),
+    (3, 0, 'Tb2', 'Editor', '', '', 'tb2...@no...');
+alter sequence person_id_sequence restart with 100;
+
+insert into "user"(user_id, version, username, password, userrole_id, person_id) 
+    VALUES
+    (1,  0, 'tb2admin',  '*', 1, 1),
+    (2,  0, 'tb2user',   '*', 2, 2),
+    (3,  0, 'tb2editor', '*', 3, 3);
+alter sequence user_id_sequence restart with 100;
+
+
+
 insert into studyStatus(studystatus_id, version, description) values 
 (1, 1, 'In Progress'),
 (2, 1, 'Ready'),


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.