Extending table to add new columns failed

Tags: #<Tag:0x00007f302bb036c0>

Hello,

I have a task of extending the cohort_member table in core within my particular module. I was going through the wiki page trying to understand how to go about it. I believe I have most of the code in place, but I can’t wrap my head around how to seamlessly integrate the new columns in cohort_member table, since the table is basically a set that is used in the Cohort model?!

Here is the link to the github repo https://github.com/vshl/openmrs-module-expandedcohort

Right now, I cannot view the Cohorts in the Cohort builder, here is the error message:

Log at 2016-07-09 18.20.12.txt (13.1 KB)

Feel free to ask me further questions. Any help is appreciated.

We generally do not recommend modifying core platform tables from modules. You can have the module create its own tables whose names are prefixed with the moduleId. Or you could create a pull request to the core platform with the table modification changes.

The error message you are getting simply means that your database table does not have the “start_date” column.

https://wiki.openmrs.org/display/docs/Extending+a+Table+Through+a+Module

Hi Daniel,

To give little more context, I am following the design directions as mentioned in the following forum. And after some discussion with Darius in this thread, we concluded that I will concentrate my efforts in a module owing to the time constraints and other factors pertaining to the nature of my project.

Do you think, I can achieve the same by duplicating the cohort_member table within my module and at the same time not break functionality with existing cohorts?

Thanks for the reply,

Vishal

You either need to make the changes to core (via PR) or extend the core table(s) in your module. Extending a core table (e.g., adding start and stop date to a cohort member) would typically be done with something like:

create table expandedcohort_cohort_member
  (
    cohort_member_id int PRIMARY KEY,
    start_date datetime,
    stop_date datetime,
    FOREIGN KEY (cohort_member_id) REFERENCES cohort_member (cohort_member_id)
  )

Unfortunately, cohort_member in core was not designed to persist member entries, so does not have it’s own primary key (it only has cohort_id and patient_id). Since rows in cohort_member are purged as members are removed and there’s no cohort_member_id primary key, you can’t extend the core cohort members in your module as Darius suggested. :confounded:

This really needs to be done in core. The only way to add start & stop dates to cohort members via a module would be to alter the core table (adding a cohort_member_id primary key along with start & stop dates) as well as changing core’s behavior with cohort members (currently purging members that are removed). You’ll probably spend more time trying to do this in a module than you would making the changes to core.

As I mentioned multiple times on the other thread, I recommend that you make these changes in (a feature branch of) openmrs-core.

Because that way you’re less likely to run into other dependencies and complexities, like this one.

Thanks Darius and Burke. After discussing with my peer group. I’ll try to make the changes in the core itself. Are there some development guidelines that I need to follow to bring forth the change within core?

If you haven’t already, review the GitHub Code of Conduct and Coding Conventions. In general, it goes something like this:

  • Fork openmrs-core
  • Make a branch (usually named after the ticket)
  • Get feedback from other devs as need via IRC or Telegram.
  • When ready, submit a PR
  • After it is approved, squash your commits into one commit (if multiple commits)
1 Like

Thanks Burke. That’s been useful. Do I have to create the associated ticket in JIRA? I will be forking the openmrs-core repo to my github and will create a feature branch.

@vshankar, sure, go ahead and create a ticket in the “OpenMRS Core” project in JIRA, and share a link to it here. (Someone will mark it ready-for-work and you can claim it.)

In this case I would name the branch something more descriptive, like “expanded-cohort”, but using the ticket number is okay too.

@vshankar even better is to name the branch TicketNumber-expanded-cohort (that way you have the best of both worlds)

Sorry it took long, I had some permission issue before I could create an issue. Here is the newly created ticket https://issues.openmrs.org/browse/TRUNK-4906.

@vshankar I marked the ticket as ready-for-work, so you can now go “claim” it.

Thanks for the help, Darius. Much appreciated.

@burke @darius I’ve been reviewing the openmrs-core code, and I hope the instructions to extend cohort_member table in the Design Forum still stand?

I am trying to gather the requirements to add the additional columns for cohort_member. I have added the additional columns in liquibase and removed the Unique constraint. Now the tricky portion of cohort_member being a collection mapping to Cohort, how does the additional columns (start_date, end_date) fit in within this mapping? Do we keep the mapping as it is or will this change?

Also, the two methods Cohort.getMembers(Date asOfDate) and Cohort.getMemberChanges(Date fromDate, Date toDate): will these two methods just be SQL query calls to return the list of Cohorts?

Thanks.

@vshankar did you get answered on this thread? Review of extending cohort_member table with new columns

Yes, I did. Thanks. I’m currently working on it.