Page "Proposals/BEP-0003" was changed by jure Diff URL: <https://issues.apache.org/bloodhound/wiki/Proposals/BEP-0003?action=diff&version=23> Revision 23 Comment: Legacy schema compatibility Changes: -------8<------8<------8<------8<------8<------8<------8<------8<-------- Index: Proposals/BEP-0003 ========================================================================= --- Proposals/BEP-0003 (version: 22) +++ Proposals/BEP-0003 (version: 23) @@ -167,6 +167,226 @@ Another change required is the change of the fore mentioned table keys. As it currently stands, the key used in these tables is limited to the 'name' field. As this (in the modified schema) prevents users from creating versions/milestones/... with the same name for different products, key should be extended with the 'product' field. +==== Legacy schema compatibility + +As changing trac database schema (by adding product column) to the required tables will brake compatibility with existing trac code/3rd party plugins, a solution is required to solve that. + +Requirements for this solution: +* no code changes are required within trac/3rd party plugins to operate within this new schema/environment +* trac/3rd party plugins should see a view of the database depending on what product scope is currently active + +As existing trac/3rd party plugin code should remain unchanged, the only way of accomplishing legacy compatibility with the new database schema is to develop a component, that installs itself directly above the database access layer. By intercepting SQL statements and transforming them, trac/3rd party plugins are given a view of the database that corresponds to the currently active product scope. + +Currently all database access is handled by trac class `IterableCursor`. A new class is implemented called `BloodhoundIterableCursor` that replaces the `IterableCursor` in runtime. + +Functionality of this class is the following: +* parse SQLs targeted at the database +* depending to which table the SQL is targeted, the following transformation is done on the SQL + * for system tables or tables that do not require productization, SQL is passed unchanged + * for 'productized' trac tables mentioned above, a product view of the table is presented to the code + * only DML statements (SELECT, INSERT, UPDATE, DELETE) are translated + * queries targeted at 3rd party plugin tables are modified to prefix 3rd party plugin table names with product prefix + * in addition to DML, DDL statements (CREATE TABLE/INDEX, ALTER TABLE/CONSTRAINT, DROP TABLE) are also translated + +===== trac tables + +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 + +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 +}}} + + === Administration commands #admin-cmd -------8<------8<------8<------8<------8<------8<------8<------8<--------
-- Page URL: <https://issues.apache.org/bloodhound/wiki/Proposals/BEP-0003> 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' page. If it was not you, please report to .
