src-wad/src/org/openbravo/wad/validation/WADValidator_data.xsql
author Asier Lostalé <asier.lostale@openbravo.com>
Fri, 11 Dec 2009 14:17:11 +0100
changeset 5686 9d31ef67e880
parent 5681 687f1bb51956
child 5691 80a3da9a2565
permissions -rw-r--r--
related to issue 0011431: fixed verification for tabs in different module than their window
<?xml version="1.0" encoding="UTF-8" ?>
<!--
 *************************************************************************
 * The contents of this file are subject to the Openbravo  Public  License
 * Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
 * Version 1.1  with a permitted attribution clause; you may not  use this
 * file except in compliance with the License. You  may  obtain  a copy of
 * the License at http://www.openbravo.com/legal/license.html 
 * Software distributed under the License  is  distributed  on  an "AS IS"
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
 * License for the specific  language  governing  rights  and  limitations
 * under the License. 
 * The Original Code is Openbravo ERP. 
 * The Initial Developer of the Original Code is Openbravo SL 
 * All portions are Copyright (C) 2009 Openbravo SL 
 * All Rights Reserved. 
 * Contributor(s):  ______________________________________.
 ************************************************************************
-->

<SqlClass name="WADValidatorData" package="org.openbravo.wad.validation">
  <SqlMethod name="checkIdentifier" type="preparedStatement" return="multiple">
    <Sql>
      <![CDATA[
          select t.tablename as objectName
          from ad_table t, ad_module m, ad_package p
         where not exists (select 1
                             from ad_column c
                            where c.ad_table_id = t.ad_table_id
                              and c.isidentifier = 'Y')
           and t.ad_package_id = p.ad_package_id
           and m.ad_module_id = p.ad_module_id
           and (m.javapackage in ('1') or to_char('Y')=to_char(?))
      ]]>
    </Sql>
    <Field name="objecttype" value=""/>
    <Field name="currentvalue" value=""/>
    <Field name="expectedvalue" value=""/>
    <Parameter name="module" optional="true" type="replace" after="m.javapackage in (" text="'1'"/>
    <Parameter name="checkAll"/>
  </SqlMethod>
  
  <SqlMethod name="checkKey" type="preparedStatement" return="multiple">
    <Sql>
      <![CDATA[
        select t.tablename as objectName
          from ad_table t, ad_module m, ad_package p
         where not exists (select 1
                             from ad_column c
                            where c.ad_table_id = t.ad_table_id
                              and c.iskey = 'Y')
           and t.ad_package_id = p.ad_package_id
           and m.ad_module_id = p.ad_module_id
           and t.isview='N'
           and (m.javapackage in ('1') or to_char('Y')=to_char(?))
      ]]>
    </Sql>
    <Parameter name="module" optional="true" type="replace" after="m.javapackage in (" text="'1'"/>
    <Parameter name="checkAll"/>
  </SqlMethod>
  
  <SqlMethod name="checkModelObject" type="preparedStatement" return="multiple">
    <Sql>
      <![CDATA[
        select to_char(p.name) as objectname, 'Process' as objectType, o.classname as currentvalue, m.javapackage as expectedvalue 
          from ad_model_object o, ad_process p, ad_module m
         where o.ad_process_id = p.ad_process_id
           and p.ad_module_id = m.ad_module_id
           and o.classname not like m.javapackage||'.%'
           and (1=1 and m.javapackage in ('1') or to_char('Y')=to_char(?))
         union
        select to_char(f.name), 'Form' as objectType, o.classname as currentvalue, m.javapackage as expectedvalue 
          from ad_model_object o, ad_form f, ad_module m
         where o.ad_form_id = f.ad_form_id
           and f.ad_module_id = m.ad_module_id
           and o.classname not like m.javapackage||'.%'
           and (2=2 and m.javapackage in ('2') or to_char('Y')=to_char(?))
         union
        select to_char(c.name), 'Callout' as objectType, o.classname as currentvalue, m.javapackage as expectedvalue 
          from ad_model_object o, ad_callout c, ad_module m
         where o.ad_callout_id = c.ad_callout_id
           and c.ad_module_id = m.ad_module_id
           and o.classname not like m.javapackage||'.%'
           and (3=3 and m.javapackage in ('3') or to_char('Y')=to_char(?))
         union
        select to_char(r.name), 'Reference' as objectType, o.classname as currentvalue, m.javapackage as expectedvalue 
          from ad_model_object o, ad_reference r, ad_module m
         where o.ad_reference_id = r.ad_reference_id
           and r.ad_module_id = m.ad_module_id
           and o.classname not like m.javapackage||'.%'
           and (4=4 and m.javapackage in ('4') or to_char('Y')=to_char(?))
         union
        select  to_char(w.name||' >> '||t.name), 'Tab' as objectType, o.classname as currentvalue, (case when w.ad_module_id='0' then 'org.openbravo.erpWindows.%' else 'org.openbravo.erpWindows.'||m.javapackage||'.%' end) as expectedvalue 
          from ad_model_object o, ad_window w, ad_tab t, ad_module m
         where o.ad_tab_id = t.ad_tab_id
           and t.ad_window_id = w.ad_window_id
           and t.ad_module_id = m.ad_module_id
           and o.classname not like (case when w.ad_module_id='0' then 'org.openbravo.erpWindows.%' else 'org.openbravo.erpWindows.'||m.javapackage||'.%' end)
           and o.classname not like 'org.openbravo.erpCommon.ad_callouts.ComboReloads%'
           and (5=5 and m.javapackage in ('5') or to_char('Y')=to_char(?))
         order by 4, 2, 1
      ]]>
    </Sql>
    <Parameter name="module" optional="true" type="replace" after="1=1 and m.javapackage in (" text="'1'"/>
    <Parameter name="checkAll"/>
    <Parameter name="module" optional="true" type="replace" after="2=2 and m.javapackage in (" text="'2'"/>
    <Parameter name="checkAll"/>
    <Parameter name="module" optional="true" type="replace" after="3=3 and m.javapackage in (" text="'3'"/>
    <Parameter name="checkAll"/>
    <Parameter name="module" optional="true" type="replace" after="4=4 and m.javapackage in (" text="'4'"/>
    <Parameter name="checkAll"/>
    <Parameter name="module" optional="true" type="replace" after="5=5 and m.javapackage in (" text="'5'"/>
    <Parameter name="checkAll"/>
  </SqlMethod>
  
  <SqlMethod name="checkModelObjectMapping" type="preparedStatement" return="multiple">
    <Sql>
      <![CDATA[
        select to_char(p.name) as objectname, 'Process' as objectType, om.mappingname as currentvalue, m.javapackage as expectedvalue 
          from ad_model_object o, ad_process p, ad_module m, ad_model_object_mapping om
         where o.ad_process_id = p.ad_process_id
           and p.ad_module_id = m.ad_module_id
           and om.ad_model_object_id = o.ad_model_object_id
           and om.mappingname not like '/'||m.javapackage||'.%'
           and om.mappingname not like '/'||m.javapackage||'/%'
           and m.ad_module_id !='0'
           and (1=1 and m.javapackage in ('1') or to_char('Y')=to_char(?))
         union
        select to_char(f.name), 'Form' as objectType, om.mappingname as currentvalue, m.javapackage as expectedvalue 
          from ad_model_object o, ad_form f, ad_module m, ad_model_object_mapping om
         where o.ad_form_id = f.ad_form_id
           and f.ad_module_id = m.ad_module_id
           and om.ad_model_object_id = o.ad_model_object_id
           and om.mappingname not like '/'||m.javapackage||'.%'
           and om.mappingname not like '/'||m.javapackage||'/%'
           and m.ad_module_id !='0'
           and (2=2 and m.javapackage in ('2') or to_char('Y')=to_char(?))
         union
        select to_char(c.name), 'Callout' as objectType, om.mappingname as currentvalue, m.javapackage as expectedvalue 
          from ad_model_object o, ad_callout c, ad_module m, ad_model_object_mapping om
         where o.ad_callout_id = c.ad_callout_id
           and c.ad_module_id = m.ad_module_id
           and om.ad_model_object_id = o.ad_model_object_id
           and om.mappingname not like '/'||m.javapackage||'.%'
           and om.mappingname not like '/'||m.javapackage||'/%'
           and m.ad_module_id !='0'
           and (3=3 and m.javapackage in ('3') or to_char('Y')=to_char(?))
         union
        select to_char(r.name), 'Reference' as objectType, om.mappingname as currentvalue, m.javapackage as expectedvalue 
          from ad_model_object o, ad_reference r, ad_module m, ad_model_object_mapping om
         where o.ad_reference_id = r.ad_reference_id
           and r.ad_module_id = m.ad_module_id
           and om.ad_model_object_id = o.ad_model_object_id
           and om.mappingname not like '/'||m.javapackage||'.%'
           and om.mappingname not like '/'||m.javapackage||'/%'
           and m.ad_module_id !='0'
           and (4=4 and m.javapackage in ('4') or to_char('Y')=to_char(?))
         union
        select to_char(w.name||' >> '||t.name), 'Tab' as objectType, om.mappingname as currentvalue, m.javapackage as expectedvalue 
          from ad_model_object o, ad_window w, ad_tab t, ad_module m, ad_model_object_mapping om
         where o.ad_tab_id = t.ad_tab_id
           and t.ad_window_id = w.ad_window_id
           and t.ad_module_id = m.ad_module_id
           and w.ad_module_id = t.ad_module_id
           and om.ad_model_object_id = o.ad_model_object_id
           and om.mappingname not like '/'||m.javapackage||'.%'
           and om.mappingname not like '/'||m.javapackage||'/%'
           and om.mappingname not like '/ad_callouts/ComboReloads%'
           and m.ad_module_id !='0'
           and (5=5 and m.javapackage in ('5') or to_char('Y')=to_char(?))
         order by 4, 2, 1
      ]]>
    </Sql>
    <Parameter name="module" optional="true" type="replace" after="1=1 and m.javapackage in (" text="'1'"/>
    <Parameter name="checkAll"/>
    <Parameter name="module" optional="true" type="replace" after="2=2 and m.javapackage in (" text="'2'"/>
    <Parameter name="checkAll"/>
    <Parameter name="module" optional="true" type="replace" after="3=3 and m.javapackage in (" text="'3'"/>
    <Parameter name="checkAll"/>
    <Parameter name="module" optional="true" type="replace" after="4=4 and m.javapackage in (" text="'4'"/>
    <Parameter name="checkAll"/>
    <Parameter name="module" optional="true" type="replace" after="5=5 and m.javapackage in (" text="'5'"/>
    <Parameter name="checkAll"/>
  </SqlMethod>
  
  
    <SqlMethod name="checkColumnName" type="preparedStatement" return="multiple">
    <Sql>
      <![CDATA[
           select t.tablename||'.'||c.columnname as objectName, 'DB Column Name' as objectType, c.columnname as currentValue, dbp.name as expectedvalue
          from ad_table t, ad_module m, ad_package p, ad_column c, ad_module_dbprefix dbp
         where t.ad_package_id = p.ad_package_id
           and t.ad_table_id = c.ad_table_id
           and c.ad_module_id != p.ad_module_id
           and m.ad_module_id = c.ad_module_id
           and dbp.ad_module_id = c.ad_module_id
           and not exists (select 1 
                             from dual
                            where instr(upper(c.columnname), 'EM_'||upper(dbp.name)||'_') = 1)
           AND NOT EXISTS( SELECT 1
                             FROM AD_EXCEPTIONS
                             WHERE TYPE='COLUMN'
                             AND UPPER(NAME2)=UPPER(T.Tablename)
                             AND UPPER(NAME1)=UPPER(c.Columnname)) 
           and (1=1 and m.javapackage in ('1') or to_char('Y')=to_char(?))
    union
        select t.tablename||'.'||c.columnname as objectName, 'Name' as objectType, to_char(c.name) as currentValue, dbp.name as expectedvalue
          from ad_table t, ad_module m, ad_package p, ad_column c, ad_module_dbprefix dbp
         where t.ad_package_id = p.ad_package_id
           and t.ad_table_id = c.ad_table_id
           and c.ad_module_id != p.ad_module_id
           and m.ad_module_id = c.ad_module_id
           and dbp.ad_module_id = c.ad_module_id
           and not exists (select 1 
                             from dual
                            where instr(upper(c.name), 'EM_'||upper(dbp.name)||'_') = 1)
           AND NOT EXISTS( SELECT 1
                             FROM AD_EXCEPTIONS
                             WHERE TYPE='COLUMN'
                             AND UPPER(NAME2)=UPPER(T.Tablename)
                             AND UPPER(NAME1)=UPPER(c.Columnname)) 
           and (2=2 and m.javapackage in ('2') or to_char('Y')=to_char(?))
          order by 2,1
      ]]>
    </Sql>
    <Parameter name="module" optional="true" type="replace" after="1=1 and m.javapackage in (" text="'1'"/>
    <Parameter name="checkAll"/>
    <Parameter name="module" optional="true" type="replace" after="2=2 and m.javapackage in (" text="'2'"/>
    <Parameter name="checkAll"/>
  </SqlMethod>
  
  <SqlMethod name="checkAuxiliarInput" type="preparedStatement" return="multiple">
    <Sql>
      <![CDATA[
        select i.name as objectName, p.name as expectedvalue
          from ad_auxiliarInput i, AD_Module_DBPrefix p, AD_Module m
         where p.AD_Module_ID = i.AD_Module_ID
           and i.ad_module_id != '0'
           and m.ad_module_id = i.ad_module_id
           and instr(upper(i.NAME), upper(p.name)||'_') != 1
           and (m.javapackage in ('1') or to_char('Y')=to_char(?))
      ]]>
    </Sql>
    <Field name="objecttype" value=""/>
    <Field name="currentvalue" value=""/>
    <Field name="expectedvalue" value=""/>
    <Parameter name="module" optional="true" type="replace" after="m.javapackage in (" text="'1'"/>
    <Parameter name="checkAll"/>
  </SqlMethod>
  
</SqlClass>