Zobrazení zdroje stránky Migrace RT (RT: Request Tracker http://bestpractical.com/ ) z MySQL do PostgreSQL

Jeden z mých úkolů je testování možnosti nasazení Request Trackeru společně s db PostgreSQL. Jako stávající backend se používá MySQL. Pro zjednodušení migrace nemigruji data způsobem dump/reload, ale používám procedury v plperlu. Přiznám se, že důvod je ryze osobní. Před 5 roky jsem měl s načtením dump souboru MySQL do PostgreSQL docela problémy. Je možné (a nejspíš i pravděpodobné), že aktuálně (začátek roku 2009) by se tyto problémy neopakovaly. Prostřednictvím knihovny DBI se z procedur v perlu dostanu k datům v MySQL.
<pre>
/*
 * Vrati výsledek SQL dotazu do MySQL
 */
CREATE OR REPLACE FUNCTION ext.rmysql(varchar, varchar, varchar,
  varchar) RETURNS SETOF RECORD AS $$
   use DBI;
   my $dbh = DBI->connect('dbi:mysql:'.$_[0],$_[1],$_[2],
      { RaiseError => 1, AutoCommit => });
   my $sth = $dbh->prepare($_[3]);
   $sth->execute(); my $myref;
   while ($dat = $sth->fetchrow_hashref) {return_next($dat);}
   $sth->finish(); $dbh->disconnect();
   return;
$$ LANGUAGE plperlu;

-- test
SELECT name, realname 
   FROM ext.rmysql('rt3','rtreport','*****','SELECT name FROM Users') AS (name varchar, realname varchar);
</pre>

Další skript použiji k vytvoření pohledů, které mi zastupují db linky do originální databáze RT na MySQL. V databázi PostgreSQL již mám vytvořenou strukturu tabulek. Jimi se řídí následující procedura generate_links. Tabulku attachments není možné přenést jedním voláním funkce ext.rmysql. Při zpracování kompletního dotazu dochází k chybám. Takže se přenos rozbije do bloků o 50000 záznamech. Podle aktuálního počtu záznamů v tabulce attachments je nutné nastavit počet iterací - tudíž počet klauzulí UNION.
<pre>
CREATE OR REPLACE FUNCTION generate_links(_database varchar, _user varchar, _password varchar,
                                          _local_table_schema varchar, _dest_table_schema varchar,
                                          _local_table_name varchar)
RETURNS void AS $$
DECLARE
  _create_view varchar;
  _columns text[];
  _columns_def text[];
  _query text;
BEGIN
  EXECUTE 'DROP VIEW IF EXISTS ' ||  quote_schema_ident(_dest_table_schema || '.' || _local_table_name);
  _columns_def := ARRAY(SELECT quote_ident(column_name) || ' '
                                || case when data_type = 'character'
                                        then 'character(' || character_maximum_length || ')'
                                        else data_type end
                          FROM information_schema.columns
                         WHERE table_schema = _local_table_schema
                           AND table_name = _local_table_name);
  _columns := ARRAY(SELECT quote_ident(column_name)
                       FROM information_schema.columns
                      WHERE table_schema = _local_table_schema
                        AND table_name = _local_table_name);
  _columns := check_valuelist(_columns, _columns_def, _local_table_name);
  _query := 'SELECT ' || array_to_string(_columns,', ') || ' FROM ' || initcap_rt(_local_table_name) || '';
  IF _local_table_name <> 'attachments' THEN
    _create_view := 'CREATE VIEW ' || quote_schema_ident(_dest_table_schema || '.' || _local_table_name) || ' AS '
                        || 'SELECT * FROM ext.rmysql(' || quote_literal(_database) || ','
                                || quote_literal(_user) || ',' || quote_literal(_password) || ','
                                || quote_literal(_query ) || ') AS (' || array_to_string(_columns_def,', ') || ')';
  ELSE
    /* pro attachments vygeneruji union po 50000 zaznamech do 3M zaznamu */
    /* tady je potreba nastavit konkretni pocet stranek, podle aktualniho poctu radek */
    _create_view := 'CREATE VIEW ' || quote_schema_ident(_dest_table_schema || '.' || _local_table_name) || ' AS ';
    FOR i IN 0..28 LOOP --137 LOOP
      IF i > 0 THEN _create_view := _create_view || ' UNION ALL '; END IF;
      _create_view := _create_view
                                || 'SELECT * FROM ext.rmysql(' || quote_literal(_database) || ','
                                || quote_literal(_user) || ',' || quote_literal(_password) || ','
                                || quote_literal(_query || ' LIMIT ' || i * 50000 + 1 || ', 50000' )
                                || ') AS (' || array_to_string(_columns_def,', ') || ')';
    END LOOP;
  END IF;
  EXECUTE _create_view;
  RETURN;
END;
$$ LANGUAGE plpgsql STRICT;

CREATE OR REPLACE FUNCTION quote_array(text[])
RETURNS text AS $$
SELECT array_to_string(array(SELECT quote_ident($1[i])
                                FROM generate_series(1, array_upper($1,1)) g(i)),
                       '.')
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION quote_schema_ident(text)
RETURNS text AS $$
SELECT quote_array(string_to_array($1,'.'))
$$ LANGUAGE SQL IMMUTABLE;
</pre>

Jelikož si neodpovídají názvy tabulek v MySQL a v PostgreSQL, mám připravenou funkci, která transformuje názvy v pg na názvy v MySQL:
<pre>
CREATE OR REPLACE FUNCTION initcap_rt(varchar)
RETURNS varchar AS $$
  SELECT CASE lower($1)
                       WHEN 'acl' THEN 'ACL'
                       WHEN 'sessions' THEN 'sessions'
                       WHEN 'scripactions' THEN 'ScripActions'
                       WHEN 'scripconditions' THEN 'ScripConditions'
                       WHEN 'cachedgroupmembers' THEN 'CachedGroupMembers'
                       WHEN 'customfieldvalues' THEN 'CustomFieldValues'
                       WHEN 'customfields' THEN 'CustomFields'
                       WHEN 'groupmembers' THEN 'GroupMembers'
                       WHEN 'objectcustomfieldvalues' THEN 'ObjectCustomFieldValues'
                       WHEN 'objectcustomfields' THEN 'ObjectCustomFields'
                       ELSE initcap($1) END;
$$ LANGUAGE sql IMMUTABLE STRICT;
</pre>

Funkci check_valuelist používám pro vložení transformace sloupců groups.instance a sessions.a_session:
<pre>
CREATE OR REPLACE FUNCTION check_valuelist(_columns text[], _columns_def text[], _table_name text)
RETURNS text[] AS $$
DECLARE
  result text[] := _columns;
BEGIN
  FOR i IN array_lower(_columns,1) .. array_upper(_columns,1)
  LOOP
    IF _columns[i] = 'instance' AND _table_name = 'groups' THEN
      result[i] := 'case coalesce(trim(' || _columns[i]
                ||'),'''') when '''' then null else coalesce(trim('|| _columns[i]
                ||'),'''') end AS ' || _columns[i];
    END IF;
    IF _columns[i] = 'a_session' AND _table_name = 'sessions' THEN
      result[i] := 'null as a_session';
    END IF;
  END LOOP;
  RETURN result;
END;
$$ LANGUAGE plpgsql STRICT;
</pre>

Viz triviální link na externí tabulku a link obsahující transformaci:
<pre>
CREATE VIEW acl AS
 SELECT rmysql.id, rmysql.principaltype, rmysql.principalid, rmysql.rightname, rmysql.objecttype, rmysql.objectid, rmysql.delegatedby, rmysql.delegatedfrom
   FROM ext.rmysql('rt3'::character varying, 'rtreport'::character varying, '*****'::character varying, 'SELECT id, principaltype, principalid, rightname, objecttype, objectid, delegatedby, delegatedfrom FROM ACL'::character varying) rmysql(id integer, principaltype character varying, principalid integer, rightname character varying, objecttype character varying, objectid integer, delegatedby integer, delegatedfrom integer);

CREATE VIEW ext.groups AS
SELECT rmysql.id, rmysql.name, rmysql.description, rmysql.domain, rmysql.type, rmysql.instance
   FROM ext.rmysql('rt3'::character varying, 'rtreport'::character varying, '*****'::character varying, 'SELECT id, name, description, domain, type, case coalesce(trim(instance),'''') when '''' then null else coalesce(trim(instance),'''') end AS instance FROM Groups'::character varying) rmysql(id integer, name character varying, description character varying, domain character varying, type character varying, instance integer);
</pre>

V rámci optimalizace jsem přidal tyto indexy (odezvy RT jsou pak cca 10x kratší):
<pre>
 CREATE INDEX rt_opt_01 ON groups USING btree (instance);
 CREATE INDEX rt_opt_02 ON groups USING btree (lower((domain)::text), lower((type)::text));
 CREATE INDEX rt_opt_03 ON groupmembers USING btree (memberid);
 CREATE INDEX rt_opt_04 ON groupmembers USING btree (groupid);
 CREATE INDEX rt_opt_05 ON objectcustomfieldvalues USING btree (objectid) WHERE (disabled = 0);
 CREATE INDEX rt_opt_06 ON principals USING btree (principaltype, id);
   WHERE (((id <> 1) AND (disabled = 0)) AND ((principaltype)::text = 'User'::text));
 CREATE INDEX rt_opt_07 ON tickets USING btree (id) WHERE (((status)::text = 'new'::text) AND ((type)::text = 'ticket'::text));
 CREATE INDEX rt_opt_08 ON tickets USING btree (owner) WHERE (((status)::text = 'new'::text) OR ((status)::text = 'open'::text));
 CREATE INDEX rt_opt_09 ON transactions USING btree (objectid, created);
 CREATE INDEX rt_opt_10 ON users USING btree (lower((name)::text));
 CREATE INDEX rt_opt_11 ON users USING btree (lower((emailaddress)::text));
</pre>