Maintaing the custom tables in openemr

prabhu_k · February 14, 2024, 2:45pm

Could you please advise on the best approach for integrating custom tables into OpenEMR? Should we adhere to OpenEMR standards and keep our own tables within the system, or is it preferable to create extensions and maintain our own database? Additionally, how would either approach impact system upgrades in the future? If we are going to create extensions how we are going to maintain the table.sql file in the system

Visolve-AI-Team · February 15, 2024, 11:12am

You have already answered to your questions in your message! You want to add custom tables and you dont wan to do that in “standard” tables that are already in OpenEMR.

But whatever you are doing is useful to others then consisder doing that in OpenEMR DB.

- ViSolve OpenEMR Support Team

adunsulag · February 16, 2024, 3:16pm

Don’t modify the core tables if you want to be able to easily handle upgrades. You can keep tables in the same database schema but I’d recommend prefixing them with some kind of namespace id so you don’t run into naming collisions in the future.

The table.sql file can use much of the same OpenEMR upgrade syntax that OpenEMR’s upgrade scripts use. Look at the sql files in the OpenEMR sql folder to see the syntax.

I believe there is a mechanism to have individual upgrade sql files in your module’s sql folder but I forget the mechanism. @Amiel’s team I believe implemented that functionality. It may only be available for Laminas/Zend modules though and not for modules that don’t follow that framework (IE modules stored in the custom_modules folder instead of the zend_modules folder).

casper · February 19, 2025, 7:37pm

Hi @adunsulag yha your correct that adding a new table using modules is only available only in Zend, since i am getting some error on doing it on the custom modules using your module custom skeleton. Is there any documentation on how to used the Zend module to create a new database table?.

adunsulag · February 19, 2025, 8:23pm

You can add tables in custom_modules for example look at the oe-module-ehi-exporter module. I was talking about individual upgrade files independent of the table.sql file. If you are running into a problem you’ve likely got a syntax error.

github.com

openemr/openemr/blob/bbc52c8261abceb658e34c97f83310a4144c3b14/interface/modules/custom_modules/oe-module-ehi-exporter/table.sql#L1


      
          #used for cleaning up the tables if needed
          #DROP TABLE IF EXISTS ehi_export_job_task_patients;
          #DROP table IF EXISTS ehi_export_job_tasks;
          #DROP TABLE IF EXISTS ehi_export_job_patients;
          #DROP TABLE IF EXISTS ehi_export_job;
          
          #IfNotTable ehi_export_job
          CREATE TABLE `ehi_export_job`(
             `ehi_export_job_id` int(11) NOT NULL AUTO_INCREMENT,
             `uuid` BINARY(16) DEFAULT NULL,
             `user_id` BIGINT(20) NOT NULL COMMENT 'FK to users.id - represents the user that started the export process',

casper · February 20, 2025, 2:14am

Thank you @adunsulag where does this link to? what i did is i add the execution to create a table in the install.php.

Our goal is that when the module is install in the module manager it will automatically create some table in the open emr database.

Thanks
Rey

adunsulag · February 20, 2025, 2:20am

The install button will automatically trigger the loading of the table.sql file in the root folder of the module. Its all built in. That link is a module built into OpenEMR that has a table.sql sample you can look at that will install the ehi_export_job table when the install button is pushed on the oe-module-ehi-exporter module entry in the Modules list.

casper · February 20, 2025, 4:53am

Hi @adunsulag thank you so much, new table is already created when the module is installed, what happened is that i did not used table.sql as the filename, since when i rename it to table.sql and reinstall the module new table is created again thank you