NoPublic, bureaucrat, administrator
1,068
edits
Changes
no edit summary
== Changes in database views from mySQL to PostreSQL ==__NOEDITSECTION__
When upgrading from {{IMSMANG}} 5.08.04 to 6.0, the database views need to be adapted, i.e. changed from mySQL MySQL syntax to PostgreSQL syntax. The country focal point is taking care of this. For reference, this section describes the main changes encountered. This can also be used as a tutorial for creating views in PostgreSQL, for {{IMSMANG}} administrators who were used to creating them in mySQLMySQL.
=== Case sensitivity and column names ===__NOEDITSECTION__
PostgreSQL, as opposed to MySQL, is case-sensitive. This means that strings need to be provided in exactly the way they are stored in the database. For example, in the table IMSMAENUM, one out of many values in the column ENUMVALUE is ''Progress Report'', with capital P and capital R. In mySQL MySQL it was possible to write:
<pre>
SELECT *
Therefore, a create view statement like this:<br />
'''mySQLMySQL'''
<pre>
CREATE OR REPLACE VIEW my_view AS
</pre>
Similarly, in mySQL MySQL it is possible to define column names with a mix of upper and lower case as well as blanks and other special characters without any trouble. In PostgreSQL, this is also possible, but in this case the column name '''must''' be enclosed in double quotes. For example, the following create view statement:<br />'''mySQLMySQL'''
<pre>
CREATE VIEW my_view AS
FROM hazreducdeviceinfo;
</pre>
{{Note | This also applies to querying the data. For select rows where the device type is equal to 'xy', the syntax is the following: <pre> ... WHERE "Device Type" = 'xy'</pre>}}
{{Note | If an column name in PostgreSQL is written with upper case but not enclosed into double quotes, it will be created in lower case in the database.}}
=== CAST AS CHAR CHARSET utf8 and _utf8 (mySQL) ===__NOEDITSECTION__
In some (older) mySQL MySQL versions, UTF-8 was not the default character set and strings thus needed to be explicitly converted to UTF-8. This is not the case in PostgreSQL and UTF-8 related syntax as used in MySQL does not work in PostgreSQL. Below are two examples encountered in {{IMSMANG}} views.
'''MySQL'''
</pre>
=== IF and IFNULL (mySQL) / CASE (PostgreSQL) statements (conditional expressions) ===__NOEDITSECTION__The statements IF() and IFNULL() do not exist in PostgreSQL. In PostgreSQL, the CASE statement is used for conditional expressions. The syntax of the CASE statement is as follows:<pre>CASE WHEN condition THEN result [WHEN ...] [ELSE result]END</pre> Therefore, for example the following create view statement:<br />'''MySQL'''<pre>CREATE VIEW `my_view` ASSELECTsum(if((`nominefound_view_device`.`Device Type` = _utf8'SAA'),`nominefound_view_device`.`Quantity`,0)) AS`SAA`,sum(if((`nominefound_view_device`.`Device Type` = _utf8'AP'),`nominefound_view_device`.`Quantity`,0)) AS `AP`,sum(if((`nominefound_view_device`.`Device Type` = _utf8'AIED'),`nominefound_view_device`.`Quantity`,0)) AS `AIED`,sum(if((`nominefound_view_device`.`Device Type` = _utf8'UXO'),`nominefound_view_device`.`Quantity`,0)) AS `UXO`,sum(if((`nominefound_view_device`.`Device Type` = _utf8'AT'),`nominefound_view_device`.`Quantity`,0)) AS `AT`,sum(if((`nominefound_view_device`.`Device Type` = _utf8'Fragments'),`nominefound_view_device`.`Quantity`,0)) AS `Fragments`FROM`my_view2`;</pre> needs to be changed to:<br />'''PostgreSQL'''<pre>CREATE VIEW my_view ASSELECTsum(case when nominefound_view_device.Device Type = 'SAA') then nominefound_view_device.Quantity else 0 end) AS "SAA",sum(case when nominefound_view_device.Device Type = 'AP') then nominefound_view_device.Quantity else 0 end) AS "AP",sum(case when nominefound_view_device.Device Type = 'AIED') then nominefound_view_device.Quantity else 0 end) AS "AIED",sum(case when nominefound_view_device.Device Type = 'UXO') then nominefound_view_device.Quantity else 0 end) AS "UXO",sum(case when nominefound_view_device.Device Type = 'AT') then nominefound_view_device.Quantity else 0 end) AS "AT",sum(case when nominefound_view_device.Device Type = 'Fragments') then nominefound_view_device.Quantity else 0 end) AS "Fragments"FROMmy_view2;</pre>
=== Date manipulations ===__NOEDITSECTION__