Are Stored Procedures, etc possible for OEMR?

cipher-naught wrote on Friday, July 20, 2012:

Are Triggers, Views, Stored Procedures, etc possible?

From our discussions on possible Demographic Improvments, I wanted to update a discussion from 18 months ago about Trigers and Stored Procedures.

I am not sure how to kick off this discussion.  There is a definite discussion of “Are Triggers, Views, Stored Procedures, etc needed?”, but I wanted to start a discussion of “Are Triggers, Views, Stored Procedures, etc possible?”.

I think the first questions from looking through the previous discussion broke down into concerns about:
* Will using Triggers, Views, Stored Procedures, etc (VT-SP) limit OpenEMRs database support?
* Will using VT-SPs limit Shared Hosting options that some users use?

My research looks like the answer is No

This is what I found (wiki:
• MySQL – All (View, Triggers, and Stored Procedures)
• Oracle – All (View, Triggers, and Stored Procedures)
• PostgreSQL  – All (View, Triggers, and Stored Procedures)
• MS SQL - All (View, Triggers, and Stored Procedures)
• DB2 - All (View, Triggers, and Stored Procedures)
• SQLite - Triggers, Views, no stored procedures. (I don’t think people are thinking of moving OpenEMR to SQLite, but wanted to include it.)

Hosting Provider support for MySQL Stored Procedures for shared hosting. I couldn’t find a list of Hosting Provider market share, but these have been listed as Large Hosting Providers.
• Go Daddy – Yes (Stored Procedure Support), but PHP code has to create the Stored Procedures
• Dreamhost – Yes
• Media Temple - No Stored Procedures
• Host Gator - Yes, but may require support email to get turned on.
• Blue Host - Not sure, but appears like Go Daddy
Looks to me that there is little concern about supporting Views, Triggers, and Stored Procedures from the Shared Hosting database support.  What does everyone else think? 

-cipher-naught

PS I will open another discussion on “Are Views, Triggers, and Stored Procedures worth it” later.

sunsetsystems wrote on Friday, July 20, 2012:

I’m not up on the issues with hosting providers, but these things will add complexity and make possible future transitions to other databases more difficult.  Generally we prefer logic to be in PHP code, but if there is a compelling case otherwise we should certainly consider it.

For future reference: the name of the project is OpenEMR, and should not be abbreviated as OEMR since that is the name of a nonprofit corporation.

Rod
www.sunsetsystems.com

bradymiller wrote on Friday, July 20, 2012:

Hi Cipher-Naught,

But the actual code written for the triggers and stored procedures is not always portable. So, if want to bring the the stored procedures and triggers to a new database would likely need to rewrite/test them or each database. This hurdle would essentially lock us into mysql.

And to the hosting support issue and installation issue, goal is to simplify installation. Asking users to toy around with mysql privileges or search/ask for hosting providers that supports triggers would go against that goal.

As I stated in the other thread discussion views, I think the question comes down to this:
Would it be acceptable to include triggers,stored procedures or views in a production release LAMP project?

Isn’t the best way to figure this out simply finding mainstream LAMP projects that use them and then see if there were complaints/problems by users. As of yet, I have not found a mainstream LAMP project that contains them. Let us know if you know of any.

-brady
OpenEMR

fndtn357 wrote on Friday, July 20, 2012:

RE: Hosting Provider support for MySQL Stored Procedures for shared hosting.

Hostgator allows this but you must submit a support ticket for their admins to enable it for you.

tmccormi wrote on Friday, July 20, 2012:

Brady,
   Drupal uses MysSQL views form many of it’s features
-Tony

yehster wrote on Friday, July 20, 2012:

But the actual code written for the triggers and stored procedures is not always portable. So, if want to bring the the stored procedures and triggers to a new database would likely need to rewrite/test them or each database. This hurdle would essentially lock us into mysql.

"ENGINE=“MyISAM” is certainly not cross DB portable as it is.

sunsetsystems wrote on Friday, July 20, 2012:

Brady, Drupal uses MysSQL views form many of it’s features

Tony, can you elaborate on that?  I have a couple of Drupal 5 sites (yeah I know, need to upgrade) that do not seem to contain any MySQL views.  Also I can’t find much mention of them in Drupal 7 docs, but might not be looking in the right place.

Rod
www.sunsetsystems.com

bradymiller wrote on Friday, July 20, 2012:

Hi yehster,
Converting database.sql to an xml sql schema (see the phpgacl database install sql file used in OpenEMR at openemr/gacl/schema.xml ) that can be used across databases via adodb deals with minor issues such as those.
-brady

fndtn357 wrote on Friday, July 20, 2012:

Drupal uses Views extensively through their module called Views. In Drupal 7 parts of it are ported into core and by Drupal 8 it should be fully integrated. Lots of Joins etal. We have distinct caches for each view and several caching mechanisms recommended and usually in place for faster sites.

bradymiller wrote on Friday, July 20, 2012:

Hi James,

Thanks for pointing that out. Out of curiosity, does a core Drupal 7 install (or the future Drupal 8 install) bring any database views into the database? Specifically interested if they are using Views to just simplify queries.

thanks,
-brady
OpenEMR

fndtn357 wrote on Friday, July 20, 2012:

Good question - at this point we primarily use Views to make queries create-able and manage-able by non-programmers. There are ways to create temp or perm tables via Views and the Migrate module to make querying information from more than one database (and or data type) at a time possible. Kind of like mixing and matching different columns from a couple of db’s into a structured table in the primary Drupal database for using.

bradymiller wrote on Friday, July 20, 2012:

Hi James,
As a complete aside, a reporting feature in OpenEMR that made use of Views as Drupal does could potentially by a very powerful/configurable reporting tool (with ability to create custom reports by non-programmers that would be easily transferrable to other OpenEMR instances).
-brady

sunsetsystems wrote on Friday, July 20, 2012:

I just did a default Drupal 7 install.  No views, stored procedures, functions or triggers are in the database.  The Views module is not included.

Rod
www.sunsetsystems.com

fndtn357 wrote on Friday, July 20, 2012:

I take your aside very seriously as I am gearing up to integrate the two somehow together. I am writing a module to access demographics right now. I think they can work well together. I am still a little mystified by gacl. Drupal has such granular permisssions it seems counter-productive, but I could be wrong ~ I am a newer developer in these arenas. There is a fellow interested in working on integrating gacl with Drupal for some reason. I have more to learn. Thanks for your suggestion.

*btw because of the Symfony integration reporting now comes with Twig instead of using phpTemplates or Smarty.

fndtn357 wrote on Friday, July 20, 2012:

Rod, it is not included but you can’t do too much of anything with your Drupal install without it. Start building and you need it for everything that is why it is being incorporated into core soon. Every Drupal installer adds Entity, Views CTools for any kind of functionality.

sunsetsystems wrote on Friday, July 20, 2012:

Noted, thanks.

Rod
www.sunsetsystems.com

bradymiller wrote on Friday, July 20, 2012:

Hi cipher-naught,

For the Database Views, just want to clarify that you plan to use it for two things:

1) Support legacy codebase (ie. have a patient_data view so doesn’t break codebase that calls the patient_data table to grab or update stuff in the new demographic tables). If so, curious to see what you plan to use as the patient_data view that goes along with your proposed table set.

2) Simplify queries. My thoughts are that this can get out of hand rather quickly (envisioning hundreds of potential views); any thoughts to instead encompassing this within modular php functions/classes?

-brady

bradymiller wrote on Friday, July 20, 2012:

My post above was meant for another forum thread here:
http://sourceforge.net/projects/openemr/forums/forum/202506/topic/5453851