Database file-per-table refactor.

[aethelwulffe]

Starting a project for at least a temporary new setup and upgrade/patch system. This may be replaced with some Laravel thing in the future. PLAN

1. Implement iteration#1 with the crazy hope to be done and tested in 24 hours (yeah, right). OBJECTIVES: Setup and Upgrade will allow installer to configure some or all globals at instanciation. Tables will be managed in individual files with versioning. Installation of additional tables supporting most or all of the items in /modules will be handled by the installer's choices. Iteration 1:
2. Break all existing tables into individual files:
   • File name by table (possibly with version appended, like addresses.1.sql)
   • Remove ALL COMMENTS, and use import function that does NOT simply parse lines for "--" as this can break data imports (such as code descriptions etc..).
3. Setup parses SQL directory for lists of files, then offers a report (hide/unhide div) to review what is installed.

Iteration 2:

1. Setup parses /modules for a configuration file, parses modules/[eachmodule]/SQL for tables.
2. Setup allows for enabling and installing tables for each module, passing arguments for each to the appropriate functions.
3. Setup adds appropriate GLOBALS settings for included modules, as well as allowing other settings such as THEME to be chosen. A full GLOBALS interface may be offered.
4. Same as (1) above, but looks at /interface/forms.

Iteration 3:

1. Move all UPGRADE functions to a state where they are only accessible within the application.
2. Utilize versioning and even full table comparison reporting as well as optional inclusion/exclusion of the same.

[aethelwulffe]

ADDING: SQL file validator [dev tool] for ensuring file does not contain comments, or is otherwise broken.

[teryhill]

May be a directory structure holding the files for the version in addition to File name by table (possibly with version appended, like addresses.1.sql) /Ver_1.0.1

[tmccormi]

Look at the lavavel style migration/setup stull that has already been done and model on that. Each "database table" and it's needs are a separate date/time stamped executable PHP. You may even be able to most of it already.

Tony McCormick, CTO www.mi-squared.com Support: 866-735-0897, Direct: 713-574-6709 My Calendar: http://bit.ly/XznvDo -Verba volant, scripta manent

On Fri, Oct 13, 2017 at 11:48 AM, Terry Hill notifications@github.com wrote:

May be a directory structure holding the files for the version in addition to File name by table (possibly with version appended, like addresses.1.sql) /Ver_1.0.1

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/LibreHealthIO/LibreEHR/issues/719#issuecomment-336537101, or mute the thread https://github.com/notifications/unsubscribe-auth/AARci4jWbOk4XHIY0Pzo1cjEDx8zd9muks5sr7CUgaJpZM4P42Iq .

-- Please be aware that e-mail communication can be intercepted in transmission or misdirected. Please consider communicating any sensitive information by telephone. The information contained in this message may be privileged and confidential. If you are NOT the intended recipient, please notify the sender immediately with a copy to hipaa-security@mrsb-ltd.com and destroy this message.

[teryhill]

Go @aethelwulffe go

[aethelwulffe]

Branch for this: https://github.com/LibreHealthIO/LibreEHR/tree/database_breakup

Separated each table structure like so: /sql/structure/_table_addresses.sql Might rename this to sql/create/ as that is the function used there. These files merely create the fields like so:

CREATE TABLE `addresses` (
  `id` int(11) NOT NULL DEFAULT '0',
  `line1` varchar(255) DEFAULT NULL,
  `line2` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `state` varchar(35) DEFAULT NULL,
  `zip` varchar(10) DEFAULT NULL,
  `plus_four` varchar(4) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `foreign_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Next, I have separated the key declarations and the like with a separate set of files in sql/alter/_alter_addreses.sql These are done as ALTER statements. This provides an error checking breakpoint like so:

ALTER TABLE `addresses`
  ADD PRIMARY KEY (`id`),
  ADD KEY `foreign_id` (`foreign_id`);

There may be more than one ALTER statement in each file, such as for declaring unsigned integers and the like. This makes each step trackable for point breaks.

ALTER TABLE `onsite_documents`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `onsite_documents`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

The third flavor (not done yet) will be where the DEFAULT INSERTS go. All inserts in database.sql will be move to the default insert file list. Many of these will be optional. Most of the ones for things like 'facilities' will be configurable at setup.

Currently, the tables include a default engine and characterset. The intention is that the characterset can be defined during install as well (as it is). I assume I will need to redact those lines.

[tmccormi]

Looks good

Tony McCormick

On Oct 13, 2017 5:07 PM, "Art Eaton" notifications@github.com wrote:

Branch for this: https://github.com/LibreHealthIO/LibreEHR/tree/database_breakup

Separated each table structure like so: /sql/structure/table addresses.sql Might rename this to sql/create/ as that is the function used there. These files merely create the fields like so:

CREATE TABLE addresses ( id int(11) NOT NULL DEFAULT '0', line1 varchar(255) DEFAULT NULL, line2 varchar(255) DEFAULT NULL, city varchar(255) DEFAULT NULL, state varchar(35) DEFAULT NULL, zip varchar(10) DEFAULT NULL, plus_four varchar(4) DEFAULT NULL, country varchar(255) DEFAULT NULL, foreign_id int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Next, I have separated the key declarations and the like with a separate set of files in sql/alter/_alter_addreses.sql These are done as ALTER statements. This provides an error checking breakpoint like so:

ALTER TABLE addresses ADD PRIMARY KEY (id), ADD KEY foreign_id (foreign_id);

There may be more than one ALTER statement in each file, such as for declaring unsigned integers and the like. This makes each step trackable for point breaks.

ALTER TABLE onsite_documents ADD PRIMARY KEY (id); ALTER TABLE onsite_documents MODIFY id int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

The third flavor (not done yet) will be where the DEFAULT INSERTS go. All inserts in database.sql will be move to the default insert file list. Many of these will be optional. Most of the ones for things like 'facilities' will be configurable at setup.

Currently, the tables include a default engine and characterset. The intention is that the characterset can be defined during install as well (as it is). I assume I will need to redact those lines.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/LibreHealthIO/LibreEHR/issues/719#issuecomment-336594049, or mute the thread https://github.com/notifications/unsubscribe-auth/AARci2pF8ekBIwmtCgFn-8apoD1Hj0gEks5sr_tEgaJpZM4P42Iq .

-- Please be aware that e-mail communication can be intercepted in transmission or misdirected. Please consider communicating any sensitive information by telephone. The information contained in this message may be privileged and confidential. If you are NOT the intended recipient, please notify the sender immediately with a copy to hipaa-security@mrsb-ltd.com and destroy this message.