Changing field types in a Chado table (Chado API Docs Needed)

[oklonova]

Hi again,

I need to change the type of the field 'value' in the feature_relationship table. It is of type text, but we need to store numbers and want to be able to use this field as a numeric one, for example, to filter by these values in Views. The table has no data now. (This field is meant for comments and does not have any relationship with other fields/tables, so it does not seem to be a problem to change it.) The field type CAN be changed in psql, but this does not affect the way this table is seen by Drupal.

I see that many change field types programmatically in Drupal using hook_update_N (with the functions db_query update and/or db_change_field). Looks like this way can only be used with public tables.

Could you please hint how it is possible to change a field type in a Chado table? (Is it possible?) I was trying to access the Chado schema via different functions from the API like chado_get_schema(), and then use db_change_field, with no luck so far. Tripal API from other modules work fine, so there are some things which I seem to be missing here...

Is this the way to do it? Or would you recommend something else?

Tripal 7-2.1 Chado 1.3

[bradfordcondon]

Hi @oklonova

i think this is more of a Chado question than a Tripal question, and you might be better served reaching out to the Chado community with a explanation of what you are trying to accomplish and ask for suggestions on the most "Chado" way to do it.

For example, My first question might be, is this something that should instead go into a featureloc or featurepos, as both of those tables are designed to store bigint or double precision values for the position of one feature relative to another.

Have oyu checked http://gmod.org/wiki/Chado_Sequence_Module ?

My thinking is that changing feature_relationship's value column type is a bad idea (as now we have 2 flavors of the feature_relationship table, yours and ours), and you should instead look into the other tables i mentioned or, if that wont owrk, creating your own custom table.

[spficklin]

@oklonova I would not recommend you change any of the existing columns in Chado as you may have problems with Chado upgrades in the future and with Tripal compatibility. It's best to leave Chado tables as is. The changes that you see in the hook_update_N functions are for tables in the Drupal schema (public) not for Chado tables.

I think the best solution is to create a new custom table to store your numeric values associated with the feature_relationship table. You can use the chado_create_custom_table API call to do that in your module. Tripal v2 will automatically expose your table to Views for you.

edit: you can also create custom tables via the web GUI if you do not want to program it.

@laceysanderson any additional thoughts?

[oklonova]

Hi, @bradfordcondon, thank you for the reply. Sorry I keep hitting the wrong path when creating issues... I will post the question in the Chado as well. I want to check how we could use the feature_relationship table to store data about pairwise alignments between proteins, and this table is just small and compact enough to store all the data we have (subject_id, object_id, rank as integer, and value (% identity)). All would be well, but we want also to use the value column as a numeric one to be able to sort, filter on etc. That is why I was wondering if it was possible to change it. I was also considering other tables like featurepos and featureloc, but they require other types of data that we do not have and do not need. Alternatively, store % identity as 'rank' in the feature_relationshipprop table, but that would introduce one more join to database queries. Guess storing everything in one table is much more convenient wrt retrieving the data. I have a feeling that I am tweaking the tables in a way, they were originally created to store information about genes, but we have not had problems so far...

[oklonova]

Thank you, @spficklin, for the answer. I was thinking to create a table as a last resort. Is it hook_schema_alter I have to use to define a new table in a custom module?

[spficklin]

Our Blast analysis module uses a custom table to store blast results. I think it's fine to create a custom table when you can't get data into Chado tables. Although. as @bradfordcondon suggests it's always a good idea to reach out to the Chado community because sometimes someone has gone down the same route and has a good idea.

Our approach is to always put the scientific data in Chado. So, I would recommend if you go the route of creating a new table that you put this in Chado because you're storing scientific data. Anytime you interact with Chado you will need to use the Tripal API functions. Anytime you interact with Drupal tables you use the Drupal API functions. So, those hook_schema_alter (and any other Drupal hook for managing tables) only work for tables in the "public" schema. If you want to work with tables in Chado you have to use the Tripal API.

The Tripal API functions for working with custom tables programmatically is found here: http://api.tripal.info/api/tripal/tripal_chado%21api%21tripal_chado.custom_tables.api.inc/group/tripal_custom_tables_api/3.x

Alternatively, if you just need the table for your site and you don't want it built into a module, you can create your table via the web interface at Tripal > Data Storage > Chado > Custom Tables.

[oklonova]

Thanks again, I made it via the interface, but was wondering how to do it programmatically. Detailed steps are good to know for those who go this route for the very first time!

[spficklin]

Hi @oklonova you make a very good point. We are severely lacking in that sort of documentation I have reopened this issue as a reminder to us to create that documentation.

To that end, I have started by creating a branch named 610-tv3-chado_api_docs which adds new Chado API starter files to our new ReadTheDocs compatible documentation in the docs folder of Tripal. When that gets filled out for custom tables I'll let you know.

If you want to create that custom table programmatically then we can try to prioritize that documentation first. Just let us know.

[oklonova]

Thanks for that, indeed would be very much appreciated. I have the table now from the UI, so there is no rush, but good to have for future reference!

[laceysanderson]

I'm marking this as a good first issue. If you have landed here based on that tag... what needs to be done is to transfer the Chado API docs from http://api.tripal.info/api/tripal/3.x to our read the docs. This has been started in 610-tv3-chado_api_docs