Menu
▾
▴
[Treebase-guts] SF.net SVN: treebase:[513] trunk/treebase-core/db
|
From: <vga...@us...> - 2010-02-17 22:54:32
|
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.
|
