He converted the base database to InnoDB and added foreign keys to all critical tables so that you could use phpMyAdmin tools to generate a schema. This allows for easier analysis.
I think we should consider three goals
1) Normalize the DB as much as possible without compromising speed
2) Upgrade all tables so that there are legitimate foreign keys with enforcement of the referencial integrity. ie: you can’t remove records that have dependencies on them (this requires all tables to be InnoDB or better) deprecate MYISAM and provide conversion tools
3) further enhance the sql wrapper scripts to be able to support other database engines, such as postgres, or even nosql engines like mongoDB or couchDB.
The ONC Standards and Interoperability (S&I) Framework is deep into defining a Clinical Information Model (CIM) that several MU-2 objectives will be based upon. Support of the CIM is practically guaranteed to be a MU-2 requirement.
If a model driven approach to development of the OpenEMR 5 DB is possible, the Model Driven Health Tools (MDHT) project, which is working on supporting the CIM, would be useful for implementing the OpenEMR 5 DB.
As a precursor or alternative to native support of the CIM in OpenEMR 5, ONC contractors are offering to develop a “PHP Bridge” to their ToC Reference Implementation (which includes a CIM implementation) which would run as an Apache Java module in OpenEMR’s AMP stack. The time frame for ONC’s development of the “PHP Bridge” for OpenEMR is late October. This group conferences on Tuesdays from 3-4:30pm.
The fact that there are 5 or more different versions of jquery referenced throughout the project bugs me a little and is something that I think should be addressed.
Not sure I agree that enhancing the SQL wrappers to support other DB systems is really a worthwhile effort. What features does MySQL lack that are needed? It will add a lot of complexity to support so many different systems. It’s not like there is any sort of performance bottle next that could be addressed by moving to something as radically different as NoSQL.
Yes #3 is a stretch, but I get asked about alternate database support all the time. It’s something many people have strong opinions about, like editors. I, personally like MySQL just fine.
-tony
Quite honestly, the number of potential sql-injection security holes and cross-scripting vulnerabilities is rather large (I’m not being negative, just transparent); every release several of the holes are posted via the security advisory pipeline, and we generally fix them; at some point, the concern is that as OpenEMR gets more popular a more global codebase type of security advisory may be issued. Also, closing these holes will make OpenEMR way more secure over the internet and in larger implementations where not every user is trustworthy.
The issue is the old code base. Some of it has been converted to the new model. Below are the modules that have been converted:
Messages and Pnotes (patient notes) module, Patient searching modules,Transactions module,Patient history module
Immunization module,Authorization module,demographics.php script,Language admin gui module
But there is still way more to do, and the work is rather laborious and requires a strong knowledge of the codebase. Considering the importance of security and the unlikelihood that a volunteer would willingly proceed with this laborious code walk-through, perhaps some of the OpenEMR5 funds could be portioned for this?
We should consider for supporting for Stored Procedures and triggers.
When an error is happening, presently we are showing the sql query itself,
which is not much usable for a basic user.We should implement a mechanism for error handling which will
capture the error number returned by the DB engine
and on the basis of that give an appropriate message to the user.
Consider the using of partition tables for the tables which will grow large.
Try to avoid the scenario of calling a series of functions for completing a process, by normalising the database ,
in such a way that the ultimate result of the functions can be achieved in simple join queries.
DB field values hardcoded in the code for the control flow should be documented in a table.
Tony’s suggestion for CouchDB is interesting but will be a big task.
Hi,
We’ve discussed stored procedures/triggers a while back; it was discussed quite a bit when Visolve submitted code using this for patient data collection anonymity stuff. The issue was is that it is database specific (ie. it would lock us into MySQL forever and not allow the option of expanding support for other databases); feel free to correct me if I’m wrong. Because of this, despite it being really nice code, we ended up making that code optional (ie. not installed during installation).
-brady
Possible Mysql replacement candidates ,supports these features. if we are considering for database abstraction layer restructuring and can include these features it will be good.
and this was prudently postponed to finish ONC-MU cert. I think this is pretty much independent of the above technical issues. It should move OpenEMR in the direction of the usability that clinicians expect from 2011 EMR software.
Wow, lots of separate wish-list items being raised here. Perhaps best to focus on things that have good prospects for funding, or that a developer is otherwise already is willing to do. Otherwise we get sucked into endless discussion with nothing actually being done.
Of course some of that is good, but my eyes glaze over pretty fast.
We need to put the wish list together, prioritize it, then work out how to fund it or recruit volunteer developers. IT should have maybe two sections, I think. The Major Push that requires project management, like DB and Security and the Minor Enhancements/Bugs that can be tackled in small chucks by independent contributors. Other projects use tools that allow for community to +1 rate each suggestion to get feedback on the community priorities.
Prioritizing… project management… seems to beg for a discussion about Cathedral vs. Bazaar (and not the one Sam just created). In any case I’d recommend looking at the funding part first because without that, nothing happens.
