SQL Upgrade

OpenEMR Development
1

mdsupport wrote on Monday, August 22, 2016:

Mysql treats database views as VIEW type of tables. If anyone uses views, you may run into a problem where sql_upgrade_fx.php script tries to ‘upgrade’ the text columns in the views. Here is the revised sql statement to apply #IfTextNullFixNeeded directive to database tables and not views :

SELECT col.`table_name`, col.`column_name`, col.`data_type`, col.`column_comment` 
FROM `information_schema`.`columns` col INNER JOIN `information_schema`.`tables` tab 
ON tab.TABLE_CATALOG=col.TABLE_CATALOG AND tab.table_schema=col.table_schema AND  tab.table_name=col.table_name
WHERE (col.`data_type`='tinytext' OR col.`data_type`='text' OR col.`data_type`='mediumtext' OR col.`data_type`='longtext' ) 
AND col.is_nullable='NO' AND col.table_schema=database() AND tab.table_type='BASE TABLE'
2

bradymiller wrote on Tuesday, August 23, 2016:

Hi MD Support,

Is this something that need to go into the upgrade script? If so, will be best to get it on github to get it reviewed and in the codebase before the next release.

thanks,
-brady
OpenEMR

3

mdsupport wrote on Wednesday, August 24, 2016:

Here is the commit incorporating the revised SQL.

Wish views were used in standard codebase as they offer perfect abstraction layer between reports and database.

4

bradymiller wrote on Friday, August 26, 2016:

Hi MD Support,

Thanks for the contribution. I committed this fix to the codebase.

-brady
OpenEMR