-
Notifications
You must be signed in to change notification settings - Fork 11
Database details
Database is PostgreSQL
Assuming you have it running and installed on your local machine, here is the config information:
http://github.com/wakibbe/LatticeGrid/blob/master/doc/database\_initiation.txt
When you are done with that, you have the database user accounts set up, and the default 'database' spaces set up, and permissions are set to enable rails to connect to the database on the same server. The configuration is different if you want to run the database on one machine and rails on another. For LatticeGrid, the performance hit of them both being on a single server is unimportant in my experience. The Postgres database is equivalent to 'tablespaces' in the Oracle world - each database is 'owned' by a single user and you grant permissions to other users as necessary.
When you are first ready to load data, you change your working directory to the latticegrid root directory, for instance /home/wakibbe/latticegrid
Then you run the Rails rake task that creates all the tables in the tablespace defined in http://github.com/NUBIC/LatticeGrid/blob/master/config/database.yml
- I'll go through that process in a second.
Lets first imagine you are doing this in a development environment, not production. The relevant part of the database.yml file is:
development:
adapter: postgresql
database: latticegrid_development
username: latticegrid
This tells Rails that you are using a postgres database and to load the postgres driver (the current preferred postgres gem driver is called 'pg', as of Sept 2009), the username to use to connect is latticegrid, and the database (default tablespace in Oracle-speak) is latticegrid_development.
Then you run these commands:
rake db:drop # this will drop the database. I run this just to make sure that rails really can control the proper database in Postgres
rake db:create # this recreates what you just dropped!
rake db:migrate # this runs the migration scripts in db/migrate
The db:migrate rake task does all the 'heavy lifting' for creating the tables used by the models and can insert data such as organizational data, investigators, and even pubmed information. LatticeGrid provides other ways to get the data in. However, you should first review the files in db/migrate so you understand the table structure. While you don't have to do this, the rails convention is that each model (object) has a separate db/migrate script, and this script creates the database representation of that model, ie a table.
http://github.com/NUBIC/LatticeGrid/tree/master/db/ http://github.com/NUBIC/LatticeGrid/tree/master/db/migrate/ http://github.com/NUBIC/LatticeGrid/blob/master/db/migrate/20080910_create_investigators.rb
20080910_create_investigators.rb creates a table called 'investigators, and specifies the columns, data types, constraints and any indices. For instance:
t.column :username, :string, :null => false
creates a column called username which specifies a NOT NULL constraint on that column. The line
add_index :investigators, [:username], :unique => true
specifies a unique index on the username column on the table investigators. Some rails developers will say that a better practice is to keep all the indexes and foreign keys in a single migration. Right now the indices creates are bundled with the create scripts and there is a separate foreign key script, 20080916_create_foreign_keys.rb, that runs after the tables are created.
Once you have run the migrations and created the tables, you can view the schema in schema.rb, or better, describe it in Postgres:
logging in to psql
development:
psql latticegrid_development -U latticegrid
production
psql latticegrid_production -U latticegrid
type \d at the prompt to see a view of the schema.
For instance, on my laptop:
WAK-MBP-4:~ _wakibbe$ psql cancerpublications_development -U cancerpublications
psql (8.4.1)
Type "help" for help.
cancerpublications_development=> \d
List of relations
Schema | Name | Type | Owner
--------+----------------------------------+----------+--------------------
public | abstracts | table | cancerpublications
public | abstracts_id_seq | sequence | cancerpublications
public | investigator_abstracts | table | cancerpublications
public | investigator_abstracts_id_seq | sequence | cancerpublications
public | investigator_appointments | table | cancerpublications
public | investigator_appointments_id_seq | sequence | cancerpublications
public | investigator_colleagues | table | cancerpublications
public | investigator_colleagues_id_seq | sequence | cancerpublications
public | investigator_proposals | table | cancerpublications
public | investigator_proposals_id_seq | sequence | cancerpublications
public | investigator_studies | table | cancerpublications
public | investigator_studies_id_seq | sequence | cancerpublications
public | investigators | table | cancerpublications
public | investigators_id_seq | sequence | cancerpublications
public | journals | table | cancerpublications
public | journals_id_seq | sequence | cancerpublications
public | load_dates | table | cancerpublications
public | load_dates_id_seq | sequence | cancerpublications
public | organization_abstracts | table | cancerpublications
public | organization_abstracts_id_seq | sequence | cancerpublications
public | organizational_units | table | cancerpublications
public | organizational_units_id_seq | sequence | cancerpublications
public | proposals | table | cancerpublications
public | proposals_id_seq | sequence | cancerpublications
public | schema_migrations | table | cancerpublications
public | studies | table | cancerpublications
public | studies_id_seq | sequence | cancerpublications
public | taggings | table | cancerpublications
public | taggings_id_seq | sequence | cancerpublications
public | tags | table | cancerpublications
public | tags_id_seq | sequence | cancerpublications
(31 rows)
You can get information on individual tables too:
cancerpublications_development=> \d investigators
Table "public.investigators"
Column | Type | Modifiers
---------------------------------------------+-----------------------------+------------------------------------------------------------
id | integer | not null default nextval('investigators_id_seq'::regclass)
username | character varying(255) | not null
last_name | character varying(255) | not null
first_name | character varying(255) | not null
middle_name | character varying(255) |
email | character varying(255) |
degrees | character varying(255) |
suffix | character varying(255) |
employee_id | integer |
title | character varying(255) |
home_department_id | integer |
campus | character varying(255) |
appointment_type | character varying(255) |
appointment_track | character varying(255) |
appointment_basis | character varying(255) |
pubmed_search_name | character varying(255) |
pubmed_limit_to_institution | boolean | default false
num_first_pubs_last_five_years | integer | default 0
num_last_pubs_last_five_years | integer | default 0
total_pubs_last_five_years | integer | default 0
num_intraunit_collaborators_last_five_years | integer | default 0
num_extraunit_collaborators_last_five_years | integer | default 0
num_first_pubs | integer | default 0
num_last_pubs | integer | default 0
total_pubs | integer | default 0
num_intraunit_collaborators | integer | default 0
num_extraunit_collaborators | integer | default 0
last_pubmed_search | date |
mailcode | character varying(255) |
address1 | text |
address2 | character varying(255) |
city | character varying(255) |
state | character varying(255) |
postal_code | character varying(255) |
country | character varying(255) |
business_phone | character varying(255) |
home_phone | character varying(255) |
lab_phone | character varying(255) |
fax | character varying(255) |
pager | character varying(255) |
ssn | character varying(9) |
sex | character varying(1) |
birth_date | date |
nu_start_date | date |
start_date | date |
end_date | date |
weekly_hours_min | integer | default 35
last_successful_login | timestamp without time zone |
last_login_failure | timestamp without time zone |
consecutive_login_failures | integer | default 0
password | character varying(255) |
password_changed_at | timestamp without time zone |
password_changed_id | integer |
password_changed_ip | character varying(255) |
created_id | integer |
created_ip | character varying(255) |
updated_id | integer |
updated_ip | character varying(255) |
deleted_at | timestamp without time zone |
deleted_id | integer |
deleted_ip | character varying(255) |
created_at | timestamp without time zone |
updated_at | timestamp without time zone |
Indexes:
"investigators_pkey" PRIMARY KEY, btree (id)
"index_investigators_on_username" UNIQUE, btree (username)
Referenced by:
TABLE "investigator_abstracts" CONSTRAINT "fk_investigators_to_investigator_abstracts" FOREIGN KEY (investigator_id) REFERENCES investigators(id)
TABLE "investigator_appointments" CONSTRAINT "fk_investigators_to_investigator_appointments" FOREIGN KEY (investigator_id) REFERENCES investigators(id)
TABLE "investigator_colleagues" CONSTRAINT "fk_investigators_to_investigator_colleagues" FOREIGN KEY (investigator_id) REFERENCES investigators(id)
TABLE "investigator_colleagues" CONSTRAINT "fk_investigators_to_investigator_colleagues_colleague_id" FOREIGN KEY (colleague_id) REFERENCES investigators(id)
TABLE "investigator_proposals" CONSTRAINT "fk_investigators_to_investigator_proposals" FOREIGN KEY (investigator_id) REFERENCES investigators(id)
TABLE "investigator_studies" CONSTRAINT "fk_investigators_to_investigator_studies" FOREIGN KEY (investigator_id) REFERENCES investigators(id)
cancerpublications_development=>
and of course run standard SQL:
cancerpublications_development=> select count(*) from investigators;
count
-------
290
(1 row)
cancerpublications_development=>
For getting data into LatticeGrid, please review: http://github.com/NUBIC/LatticeGrid/blob/master/doc/orgs_and_people_setup.txt
In Rails, the database tables are the persistence mechanism for the models - you need to be careful to keep the database constraints aligned with the model constraints. You can easily add additional constraints on the model and that normally does not affect the underlying representation, but if you have constraints at the database level they should also be represented in the model. For instance, for the above investigators table, the Investigator class has the following model constraints that correspond the the 'NOT NULL' and UNIQUE database contraints placed on the investigators.username column:
validates_uniqueness_of :username
validates_presence_of :username
This enables the Model-Controller-View architecture of Rails to oversee the business rules applied at the interface, just as the constraints on the database tables ensure specific data integrity rules at the database layer.