incubator-bloodhound-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Apache Bloodhound" <bloodhound-...@incubator.apache.org>
Subject [Apache Bloodhound] Proposals/BEP-0003/LegacySchemaCompatibility added
Date Mon, 24 Dec 2012 08:22:17 GMT
Page "Proposals/BEP-0003/LegacySchemaCompatibility" was added by jure
Comment: Legacy schema compatibility wiki (examples)
Content:
-------8<------8<------8<------8<------8<------8<------8<------8<--------
= Legacy schema compatibility

As described in [BEP:0003#db-compatibility BEP], trac and 3rd party plugin SQLs are being
translated by `BloodhoundIterableCursor` class to achieve backwards compatibility with the
code that is not product aware. DML statements are translated in both cases, DDL statements
only in case of 3rd party plugins. This document shows a couple of examples of how these SQLs
are translated.
 
== Trac tables #sql-tx-trac

Some examples:

**SELECT**

A query targeted at productized trac tables:

{{{#!sql
SELECT COUNT(*)
FROM
  (SELECT t.id AS id,
          t.summary AS summary,
          t.owner AS OWNER,
          t.status AS status,
          t.priority AS priority,
          t.milestone AS milestone,
          t.time AS time,
          t.changetime AS changetime,
          priority.value AS priority_value
   FROM ticket AS t
   LEFT OUTER JOIN enum AS priority ON (priority.type='priority'
                                        AND priority.name=priority)
   LEFT OUTER JOIN milestone ON (milestone.name=milestone)
   WHERE ((COALESCE(t.status,'')!=%s)
          AND (COALESCE(t.OWNER,'')=%s))
   ORDER BY COALESCE(t.milestone,'')='',
            COALESCE(milestone.completed,0)=0,
            milestone.completed,
            COALESCE(milestone.due,0)=0,
            milestone.due,
            t.milestone,
            COALESCE(priority.value,'')='' DESC,
            CAST(priority.value AS integer) DESC,
            t.id) AS x
}}}

... would be, when executed within 'MYPRODUCT' scope, translated into ...

{{{#!sql
SELECT COUNT(*)
FROM
  (SELECT t.id AS id,
          t.summary AS summary,
          t.owner AS OWNER,
          t.status AS status,
          t.priority AS priority,
          t.milestone AS milestone,
          t.time AS time,
          t.changetime AS changetime,
          priority.value AS priority_value
   FROM
     (SELECT *
      FROM ticket
      WHERE product="MYPRODUCT") AS t
   LEFT OUTER JOIN
     (SELECT *
      FROM enum
      WHERE product="MYPRODUCT") AS priority ON (priority.type='priority'
                                                 AND priority.name=priority)
   LEFT OUTER JOIN
     (SELECT *
      FROM milestone
      WHERE product="MYPRODUCT") AS milestone ON (milestone.name=milestone)
   WHERE ((COALESCE(t.status,'')!=%s)
          AND (COALESCE(t.OWNER,'')=%s))
   ORDER BY COALESCE(t.milestone,'')='',
            COALESCE(milestone.completed,0)=0,
            milestone.completed,
            COALESCE(milestone.due,0)=0,
            milestone.due,
            t.milestone,
            COALESCE(priority.value,'')='' DESC,
            CAST(priority.value AS integer) DESC,
            t.id) AS x
}}}

**INSERT**

INSERTs are translated by adding 'product' column with the proper value.

{{{#!sql
INSERT INTO component(name,owner,description)
VALUES ('Joe', 'Johnny', 'Description')
}}}

... translates to ....

{{{#!sql
INSERT INTO component(product, name,owner,description)
VALUES ('MYPRODUCT',
        'Joe',
        'Johnny',
        'Description')
}}}

**UPDATE**

UPDATEs are translated by adding WHERE clause with the product:

{{{#!sql
UPDATE component
SET name=%s,owner=%s, description=%s
WHERE name=%s
}}}

... translates to ...

{{{#!sql
UPDATE component
SET name=%s,owner=%s, description=%s
WHERE product='MYPRODUCT'
  AND name=%s
}}}

**DELETE**

DELETEs are translated the same way as UPDATEs by adding WHERE clause:

{{{#!sql
DELETE FROM component 
WHERE name=%s
}}}
... translates to ...

{{{#!sql
DELETE FROM component
WHERE product='MYPRODUCT'
  AND name=%s
}}}

== 3rd party plugin tables #sql-tx-plugins

Similar to trac tables, 3rd party plugin SQLs are translated before hitting the
SQL server. The difference is that in addition to productizing the trac tables,
the 3rd party plugin table names are prefixed with the current product prefix.

Some examples:

**SELECT**

{{{#!sql
SELECT bt.bklg_id, t.status, count(*) as total
FROM backlog_ticket bt, ticket t
WHERE t.id = bt.tkt_id
AND (bt.tkt_order IS NULL OR bt.tkt_order > -1)
GROUP BY bklg_id, status
}}}

... translates to ...

{{{#!sql
SELECT bt.bklg_id, t.status, count(*) as total
FROM
  (SELECT *
   FROM MYPRODUCT_backlog_ticket) AS bt,
  (SELECT *
   FROM ticket
   WHERE product="MYPRODUCT") AS t
WHERE t.id = bt.tkt_id
AND (bt.tkt_order IS NULL
     OR bt.tkt_order > -1)
GROUP BY bklg_id, status
}}}

**UPDATE, DELETE, INSERT**

UPDATE, DELETE and INSERT statements are not modified for 3rd party plugin
tables, except for prefixing the table name with the active scope product
prefix.

**CREATE, ALTER, DROP**

For 3rd party plugin tables, DDL SQLs are also translated by prefixing table
names and constraint names.

Some examples:

{{{#!sql
CREATE TABLE holiday (date TEXT, description TEXT)
}}}

... translates to ...

{{{#!sql
CREATE TABLE MYPRODUCT_holiday (date TEXT, description TEXT)
}}}

{{{#!sql
ALTER TABLE estimate ADD COLUMN diffcomment text
}}}

... translates to ...

{{{#!sql
ALTER TABLE MYPRODUCT_estimate ADD COLUMN diffcomment text
}}}
-------8<------8<------8<------8<------8<------8<------8<------8<--------

--
Page URL: <https://issues.apache.org/bloodhound/wiki/Proposals/BEP-0003/LegacySchemaCompatibility>
Apache Bloodhound <https://issues.apache.org/bloodhound/>
The Apache Bloodhound (incubating) issue tracker

This is an automated message. Someone added your email address to be
notified of changes on 'Proposals/BEP-0003/LegacySchemaCompatibility' page.
If it was not you, please report to .

Mime
View raw message