Project - Modernize Database

brady.miller · March 3, 2020, 9:42am

The OpenEMR database has been waiting patiently for a student to modernize it. At this time, OpenEMR overrides the sql_mode settings (sets it to empty) in order to ensure compatibility with MariaDB and MySQL and issues are beginning to arise because of this. Goal would be for OpenEMR’s database to support the default sql_mode settings in MariaDB and MySQL8 (note mysql8 has more by default). Goal of this modernization is to also support it for folks that are upgrading OpenEMR from prior versions. Another thing to consider is support for utf8mb4 encoding. After completing this, the student would then be in a good position to plan and begin development on a mechanism to support offsite use of OpenEMR; see here for thoughts and even a prelim PR on this using UUID’s:
2277 UUID by GJDBrink · Pull Request #2360 · openemr/openemr · GitHub

Mentors:
@brady.miller
@sjpadgett
@Julie_b

vishal_io · March 15, 2020, 1:25pm

how get started with this project

brady.miller · March 16, 2020, 6:57am

hi @vishal_io ,
I would probably start adding back the default sql_mode settings and see what breaks to get an idea of the scope of issues/improvements that would be involved.
Additionally would see what breaks when use utf8mb4 to get an idea of the scope of issues/improvements that would be involved.

Shubham_Bansal · March 16, 2020, 9:38pm

Hi @brady.miller
Want to work on this, what could be the best point to start?

stephenwaite · March 16, 2020, 10:15pm

hi @Shubham_Bansal, welcome to OpenEMR! Did you see what Brady was referring to?

github.com

openemr/openemr/blob/master/library/sql.inc#L54-L58


// Turn off STRICT SQL
$sql_strict_set_success = $database->ExecuteNoLog("SET sql_mode = ''");
if (!$sql_strict_set_success) {
    error_log("Unable to set strict sql setting: " . errorLogEscape(getSqlLastError()), 0);
}

Gaurav_Kumar · March 18, 2020, 6:24pm

Hi @brady.miller I’m Gaurav Kumar. Currently I’m a senior Undergraduate student at the Indian Institute of Technology, Mandi. I would like to work on this project. I worked with lablua in Google summer of code 2019 as a student and successfully completed the GSoC project last year. I have worked on a few web development projects which included working with MySQL, SQLite and MongoDB databases. I am quite proficient in MySQL , Javascript , docker and html. Let me know more about the project so that I can draft a proposal for the same.

Regards
Gaurav

brady.miller · March 19, 2020, 5:12am

hi @Gaurav_Kumar and welcome to the OpenEMR project!

Check out the following thread for general starting points on getting involved in the community on github:
OpenEMR is participating in Google Summer of Code 2020!

For this project, a good starting point would be to see what happens (ie. what breaks) when adding back default sql_mode settings during the installation:

github.com

openemr/openemr/blob/master/library/classes/Installer.class.php#L705


    } else {
        $dbh = mysqli_connect($server, $user, $password, $dbname, $port);
    }


    return $dbh;
}


private function set_sql_strict()
{
    // Turn off STRICT SQL
    return $this->execute_sql("SET sql_mode = ''");
}


private function set_collation()
{
    if ($this->collate) {
        return $this->execute_sql("SET NAMES 'utf8'");
    }


    return true;
}

Gaurav_Kumar · March 19, 2020, 3:02pm

Sure @brady.miller , I’ll try doing that.
Thanks