CoGe v5

Updating database:

Overview: Major changes to have the database is structured.

• added section for experiment metadata
• updated how lists of data are managed in the system
• updated how user_groups connect to data (all through lists)

Step-by-step modifications:

Modules

Additions

• ExperimentListConnector.pm
• GenomeListConnector.pm
• FeatureListConnector.pm
• ListAnnotation.pm

Deleted

• ListConnector.pm
• Sequence.pm
• SequenceType.pm
• Quantitation.pm
• QuantitationExperiment.pm
• UserGroupDataConnector.pm

Modified

• Experiment.pm: deleted column "link"
• ExperimentAnnotation.pm added column "link"
• Feature.pm: delete relationship to sequences
• Feature.pm: delete relationship to quantiations
• Feature.pm: deleted sub seqs
• DatasetGroup.pm: remove relationship to UserGroupDatasetConnector
• List.pm: Added column "lock"
• List.pm: Added column "user_group_id"
• LIst.pm: Remove column "link"
• List.pm: Remove column "notes"
• List.pm: Remove column "user_id"
• List.pm: Changed column "list_group_id" to "list_collection_id"
• List.pm: Changed column "public" to "restricted"
• List.pm: added relationship: experiment_list_connectors
• List.pm: added relationship: genome_list_connectors
• List.pm: added relationship: feature_list_connectors
• List.pm: added relationship: list_annotations
• List.pm: changed relationship: list_group to list_colelction
• List.pm: changed relationship: user to user_group
• ListType.pm: removed function groups
• ListGroup.pm: Name Changed to List Collection
• ListCollections: Change Module header name: update all references to list_group
• ListCollections: Changed column list_group_id to list_connection_id
• ListCollections: Delete column user_id
• ListCollections: Changed column public to restricted
• AnnotationType: added relationship to FeatureAnnotation
• Annotation renamed to FeatureAnnotation and updated
• ResultSet/Annotation renamed to ResultSet/FeatureAnnotation
• Feature: updated relationship annotations to feature_annotations
• UserGroup: removed relationship to user_group-data_connector
• UserGroup: added relationship to lists

Tables

Delete

• sequence
• sequence_type
• quantitation
• quantitation_experiment
• user_group_data_connector
• list_connector
• user_group_feature_list_permission_connector

Rename

RENAME TABLE old_name TO new_name

• dataset_group to genome
• annotation to feature_annotation
• list_group to list_collection
• list_group_image_connector to list_collection_image_connector

Modify tables

• genome: change dataset_group_id to genome_id
• experiment: remove link
• experiment: dataset_group_id to genome_id
• genomic_sequence: dataset_group_id to genome_id
• dataset_connector: dataset_group_id to genome_id
• experiment: add link
• list:
  • change list_group_id to list_connector_id
  • remove notes
  • remove link
  • remove user_id
  • change public to restrict
  • added user_group_id
  • add locked
• list_collection:
  • change public to restricted
  • change list_group_id to list_collection_id
  • remove note
  • remove user_id
• list_collection_image_connector: updated id
• user: add locked

table values

• data_source: change int size for index to 11
• dataset
  • change name size to 255
  • change description size to 1024
• experiment_type: changed description type to varchar 1024
• feature:
  • feature_id -> int(11)
  • feature_type_id -> int(11)
  • dataset-id -> int(11)
• feature_annotation: link -> text (Failed - table too big)
• feature_type: feature_type_id -> int(11)
• genome_sequence_type: id->int(11); name-> varchar 255
• image
  • id-> int(11)
  • name->255
  • description -> 1024
• organism
  • id-> int(11)
  • name -> 255
• permission
  • id-> 11
  • name ->255
• user
  • id->11
• user_group
  • id->11
  • name->255
  • desc->1024
• user_group_connector
  • all ids -> 11
• user_session
  • ids->11

web_preferences

• • id -> 11

Delayed changes (table are too big to do on SSD)

• location
• feature_annotation

New

• experiment_list_connector
• genome_list_connector
• feature_list_connector
• list_annotation

Usage patterns

• New User: During account creation:
  • a Locked User Group belonging to the user is created with permission "Owner"
  • a Locked List belonging to the Locked User Group is created. The list_type for this group is "Owner"
  • Neither of these may be deleted. New data added by the user (or on the user's behalf by an Admin) is added to the locked list.

To Do:

• Modify DatasetGroup.pm->user_groups()
• Update List.pm: functions to access items in lists (genomes, features, experiments)
• if ($obj->restricted && !$obj->has_access($user) {#skip}