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
asier@5256
     1
<?xml version="1.0" encoding="UTF-8" ?>
asier@5256
     2
<!--
asier@5256
     3
 *************************************************************************
asier@5256
     4
 * The contents of this file are subject to the Openbravo  Public  License
asier@5256
     5
 * Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
asier@5256
     6
 * Version 1.1  with a permitted attribution clause; you may not  use this
asier@5256
     7
 * file except in compliance with the License. You  may  obtain  a copy of
asier@5256
     8
 * the License at http://www.openbravo.com/legal/license.html 
asier@5256
     9
 * Software distributed under the License  is  distributed  on  an "AS IS"
asier@5256
    10
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
asier@5256
    11
 * License for the specific  language  governing  rights  and  limitations
asier@5256
    12
 * under the License. 
asier@5256
    13
 * The Original Code is Openbravo ERP. 
asier@5256
    14
 * The Initial Developer of the Original Code is Openbravo SL 
asier@5256
    15
 * All portions are Copyright (C) 2009 Openbravo SL 
asier@5256
    16
 * All Rights Reserved. 
asier@5256
    17
 * Contributor(s):  ______________________________________.
asier@5256
    18
 ************************************************************************
asier@5256
    19
-->
asier@5256
    20
asier@5535
    21
<SqlClass name="WADValidatorData" package="org.openbravo.wad.validation">
asier@5257
    22
  <SqlMethod name="checkIdentifier" type="preparedStatement" return="multiple">
asier@5256
    23
    <Sql>
asier@5256
    24
      <![CDATA[
asier@5534
    25
          select t.tablename as objectName
asier@5531
    26
          from ad_table t, ad_module m, ad_package p
asier@5256
    27
         where not exists (select 1
asier@5256
    28
                             from ad_column c
asier@5531
    29
                            where c.ad_table_id = t.ad_table_id
asier@5256
    30
                              and c.isidentifier = 'Y')
asier@5531
    31
           and t.ad_package_id = p.ad_package_id
asier@5531
    32
           and m.ad_module_id = p.ad_module_id
asier@5558
    33
           and (m.javapackage in ('1') or to_char('Y')=to_char(?))
asier@5256
    34
      ]]>
asier@5256
    35
    </Sql>
asier@5534
    36
    <Field name="objecttype" value=""/>
asier@5534
    37
    <Field name="currentvalue" value=""/>
asier@5534
    38
    <Field name="expectedvalue" value=""/>
asier@5534
    39
    <Parameter name="module" optional="true" type="replace" after="m.javapackage in (" text="'1'"/>
asier@5531
    40
    <Parameter name="checkAll"/>
asier@5256
    41
  </SqlMethod>
asier@5256
    42
  
asier@5258
    43
  <SqlMethod name="checkKey" type="preparedStatement" return="multiple">
asier@5258
    44
    <Sql>
asier@5258
    45
      <![CDATA[
asier@5534
    46
        select t.tablename as objectName
asier@5531
    47
          from ad_table t, ad_module m, ad_package p
asier@5258
    48
         where not exists (select 1
asier@5258
    49
                             from ad_column c
asier@5531
    50
                            where c.ad_table_id = t.ad_table_id
asier@5258
    51
                              and c.iskey = 'Y')
asier@5531
    52
           and t.ad_package_id = p.ad_package_id
asier@5531
    53
           and m.ad_module_id = p.ad_module_id
asier@5546
    54
           and t.isview='N'
asier@5558
    55
           and (m.javapackage in ('1') or to_char('Y')=to_char(?))
asier@5258
    56
      ]]>
asier@5258
    57
    </Sql>
asier@5534
    58
    <Parameter name="module" optional="true" type="replace" after="m.javapackage in (" text="'1'"/>
asier@5531
    59
    <Parameter name="checkAll"/>
asier@5258
    60
  </SqlMethod>
asier@5258
    61
  
asier@5534
    62
  <SqlMethod name="checkModelObject" type="preparedStatement" return="multiple">
asier@5534
    63
    <Sql>
asier@5534
    64
      <![CDATA[
asier@5559
    65
        select to_char(p.name) as objectname, 'Process' as objectType, o.classname as currentvalue, m.javapackage as expectedvalue 
asier@5534
    66
          from ad_model_object o, ad_process p, ad_module m
asier@5534
    67
         where o.ad_process_id = p.ad_process_id
asier@5534
    68
           and p.ad_module_id = m.ad_module_id
asier@5534
    69
           and o.classname not like m.javapackage||'.%'
asier@5558
    70
           and (1=1 and m.javapackage in ('1') or to_char('Y')=to_char(?))
asier@5534
    71
         union
asier@5534
    72
        select to_char(f.name), 'Form' as objectType, o.classname as currentvalue, m.javapackage as expectedvalue 
asier@5534
    73
          from ad_model_object o, ad_form f, ad_module m
asier@5534
    74
         where o.ad_form_id = f.ad_form_id
asier@5534
    75
           and f.ad_module_id = m.ad_module_id
asier@5534
    76
           and o.classname not like m.javapackage||'.%'
asier@5558
    77
           and (2=2 and m.javapackage in ('2') or to_char('Y')=to_char(?))
asier@5534
    78
         union
asier@5534
    79
        select to_char(c.name), 'Callout' as objectType, o.classname as currentvalue, m.javapackage as expectedvalue 
asier@5534
    80
          from ad_model_object o, ad_callout c, ad_module m
asier@5534
    81
         where o.ad_callout_id = c.ad_callout_id
asier@5534
    82
           and c.ad_module_id = m.ad_module_id
asier@5534
    83
           and o.classname not like m.javapackage||'.%'
asier@5558
    84
           and (3=3 and m.javapackage in ('3') or to_char('Y')=to_char(?))
asier@5534
    85
         union
asier@5534
    86
        select to_char(r.name), 'Reference' as objectType, o.classname as currentvalue, m.javapackage as expectedvalue 
asier@5534
    87
          from ad_model_object o, ad_reference r, ad_module m
asier@5534
    88
         where o.ad_reference_id = r.ad_reference_id
asier@5534
    89
           and r.ad_module_id = m.ad_module_id
asier@5534
    90
           and o.classname not like m.javapackage||'.%'
asier@5558
    91
           and (4=4 and m.javapackage in ('4') or to_char('Y')=to_char(?))
asier@5543
    92
         union
asier@5543
    93
        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 
asier@5543
    94
          from ad_model_object o, ad_window w, ad_tab t, ad_module m
asier@5543
    95
         where o.ad_tab_id = t.ad_tab_id
asier@5543
    96
           and t.ad_window_id = w.ad_window_id
asier@5543
    97
           and t.ad_module_id = m.ad_module_id
asier@5543
    98
           and o.classname not like (case when w.ad_module_id='0' then 'org.openbravo.erpWindows.%' else 'org.openbravo.erpWindows.'||m.javapackage||'.%' end)
asier@5543
    99
           and o.classname not like 'org.openbravo.erpCommon.ad_callouts.ComboReloads%'
asier@5558
   100
           and (5=5 and m.javapackage in ('5') or to_char('Y')=to_char(?))
asier@5534
   101
         order by 4, 2, 1
asier@5534
   102
      ]]>
asier@5534
   103
    </Sql>
asier@5534
   104
    <Parameter name="module" optional="true" type="replace" after="1=1 and m.javapackage in (" text="'1'"/>
asier@5534
   105
    <Parameter name="checkAll"/>
asier@5534
   106
    <Parameter name="module" optional="true" type="replace" after="2=2 and m.javapackage in (" text="'2'"/>
asier@5534
   107
    <Parameter name="checkAll"/>
asier@5534
   108
    <Parameter name="module" optional="true" type="replace" after="3=3 and m.javapackage in (" text="'3'"/>
asier@5534
   109
    <Parameter name="checkAll"/>
asier@5534
   110
    <Parameter name="module" optional="true" type="replace" after="4=4 and m.javapackage in (" text="'4'"/>
asier@5534
   111
    <Parameter name="checkAll"/>
asier@5543
   112
    <Parameter name="module" optional="true" type="replace" after="5=5 and m.javapackage in (" text="'5'"/>
asier@5543
   113
    <Parameter name="checkAll"/>
asier@5534
   114
  </SqlMethod>
asier@5534
   115
  
asier@5538
   116
  <SqlMethod name="checkModelObjectMapping" type="preparedStatement" return="multiple">
asier@5538
   117
    <Sql>
asier@5538
   118
      <![CDATA[
asier@5559
   119
        select to_char(p.name) as objectname, 'Process' as objectType, om.mappingname as currentvalue, m.javapackage as expectedvalue 
asier@5538
   120
          from ad_model_object o, ad_process p, ad_module m, ad_model_object_mapping om
asier@5538
   121
         where o.ad_process_id = p.ad_process_id
asier@5538
   122
           and p.ad_module_id = m.ad_module_id
asier@5538
   123
           and om.ad_model_object_id = o.ad_model_object_id
asier@5538
   124
           and om.mappingname not like '/'||m.javapackage||'.%'
martin@5650
   125
           and om.mappingname not like '/'||m.javapackage||'/%'
asier@5538
   126
           and m.ad_module_id !='0'
asier@5558
   127
           and (1=1 and m.javapackage in ('1') or to_char('Y')=to_char(?))
asier@5538
   128
         union
asier@5538
   129
        select to_char(f.name), 'Form' as objectType, om.mappingname as currentvalue, m.javapackage as expectedvalue 
asier@5538
   130
          from ad_model_object o, ad_form f, ad_module m, ad_model_object_mapping om
asier@5538
   131
         where o.ad_form_id = f.ad_form_id
asier@5538
   132
           and f.ad_module_id = m.ad_module_id
asier@5538
   133
           and om.ad_model_object_id = o.ad_model_object_id
asier@5538
   134
           and om.mappingname not like '/'||m.javapackage||'.%'
martin@5650
   135
           and om.mappingname not like '/'||m.javapackage||'/%'
asier@5538
   136
           and m.ad_module_id !='0'
asier@5558
   137
           and (2=2 and m.javapackage in ('2') or to_char('Y')=to_char(?))
asier@5538
   138
         union
asier@5538
   139
        select to_char(c.name), 'Callout' as objectType, om.mappingname as currentvalue, m.javapackage as expectedvalue 
asier@5538
   140
          from ad_model_object o, ad_callout c, ad_module m, ad_model_object_mapping om
asier@5538
   141
         where o.ad_callout_id = c.ad_callout_id
asier@5538
   142
           and c.ad_module_id = m.ad_module_id
asier@5538
   143
           and om.ad_model_object_id = o.ad_model_object_id
asier@5538
   144
           and om.mappingname not like '/'||m.javapackage||'.%'
martin@5650
   145
           and om.mappingname not like '/'||m.javapackage||'/%'
asier@5538
   146
           and m.ad_module_id !='0'
asier@5558
   147
           and (3=3 and m.javapackage in ('3') or to_char('Y')=to_char(?))
asier@5538
   148
         union
asier@5538
   149
        select to_char(r.name), 'Reference' as objectType, om.mappingname as currentvalue, m.javapackage as expectedvalue 
asier@5538
   150
          from ad_model_object o, ad_reference r, ad_module m, ad_model_object_mapping om
asier@5538
   151
         where o.ad_reference_id = r.ad_reference_id
asier@5538
   152
           and r.ad_module_id = m.ad_module_id
asier@5538
   153
           and om.ad_model_object_id = o.ad_model_object_id
asier@5538
   154
           and om.mappingname not like '/'||m.javapackage||'.%'
martin@5650
   155
           and om.mappingname not like '/'||m.javapackage||'/%'
asier@5538
   156
           and m.ad_module_id !='0'
asier@5558
   157
           and (4=4 and m.javapackage in ('4') or to_char('Y')=to_char(?))
asier@5543
   158
         union
asier@5543
   159
        select to_char(w.name||' >> '||t.name), 'Tab' as objectType, om.mappingname as currentvalue, m.javapackage as expectedvalue 
asier@5543
   160
          from ad_model_object o, ad_window w, ad_tab t, ad_module m, ad_model_object_mapping om
asier@5543
   161
         where o.ad_tab_id = t.ad_tab_id
asier@5543
   162
           and t.ad_window_id = w.ad_window_id
asier@5543
   163
           and t.ad_module_id = m.ad_module_id
asier@5686
   164
           and w.ad_module_id = t.ad_module_id
asier@5543
   165
           and om.ad_model_object_id = o.ad_model_object_id
asier@5543
   166
           and om.mappingname not like '/'||m.javapackage||'.%'
martin@5650
   167
           and om.mappingname not like '/'||m.javapackage||'/%'
asier@5543
   168
           and om.mappingname not like '/ad_callouts/ComboReloads%'
asier@5543
   169
           and m.ad_module_id !='0'
asier@5558
   170
           and (5=5 and m.javapackage in ('5') or to_char('Y')=to_char(?))
asier@5538
   171
         order by 4, 2, 1
asier@5538
   172
      ]]>
asier@5538
   173
    </Sql>
asier@5538
   174
    <Parameter name="module" optional="true" type="replace" after="1=1 and m.javapackage in (" text="'1'"/>
asier@5538
   175
    <Parameter name="checkAll"/>
asier@5538
   176
    <Parameter name="module" optional="true" type="replace" after="2=2 and m.javapackage in (" text="'2'"/>
asier@5538
   177
    <Parameter name="checkAll"/>
asier@5538
   178
    <Parameter name="module" optional="true" type="replace" after="3=3 and m.javapackage in (" text="'3'"/>
asier@5538
   179
    <Parameter name="checkAll"/>
asier@5538
   180
    <Parameter name="module" optional="true" type="replace" after="4=4 and m.javapackage in (" text="'4'"/>
asier@5538
   181
    <Parameter name="checkAll"/>
asier@5543
   182
    <Parameter name="module" optional="true" type="replace" after="5=5 and m.javapackage in (" text="'5'"/>
asier@5543
   183
    <Parameter name="checkAll"/>
asier@5538
   184
  </SqlMethod>
asier@5538
   185
  
asier@5534
   186
  
asier@5545
   187
    <SqlMethod name="checkColumnName" type="preparedStatement" return="multiple">
asier@5545
   188
    <Sql>
asier@5545
   189
      <![CDATA[
asier@5545
   190
           select t.tablename||'.'||c.columnname as objectName, 'DB Column Name' as objectType, c.columnname as currentValue, dbp.name as expectedvalue
asier@5545
   191
          from ad_table t, ad_module m, ad_package p, ad_column c, ad_module_dbprefix dbp
asier@5545
   192
         where t.ad_package_id = p.ad_package_id
asier@5545
   193
           and t.ad_table_id = c.ad_table_id
asier@5545
   194
           and c.ad_module_id != p.ad_module_id
asier@5545
   195
           and m.ad_module_id = c.ad_module_id
asier@5545
   196
           and dbp.ad_module_id = c.ad_module_id
asier@5545
   197
           and not exists (select 1 
asier@5545
   198
                             from dual
asier@5545
   199
                            where instr(upper(c.columnname), 'EM_'||upper(dbp.name)||'_') = 1)
asier@5545
   200
           AND NOT EXISTS( SELECT 1
asier@5545
   201
                             FROM AD_EXCEPTIONS
asier@5545
   202
                             WHERE TYPE='COLUMN'
asier@5545
   203
                             AND UPPER(NAME2)=UPPER(T.Tablename)
asier@5545
   204
                             AND UPPER(NAME1)=UPPER(c.Columnname)) 
asier@5558
   205
           and (1=1 and m.javapackage in ('1') or to_char('Y')=to_char(?))
asier@5545
   206
    union
asier@5545
   207
        select t.tablename||'.'||c.columnname as objectName, 'Name' as objectType, to_char(c.name) as currentValue, dbp.name as expectedvalue
asier@5545
   208
          from ad_table t, ad_module m, ad_package p, ad_column c, ad_module_dbprefix dbp
asier@5545
   209
         where t.ad_package_id = p.ad_package_id
asier@5545
   210
           and t.ad_table_id = c.ad_table_id
asier@5545
   211
           and c.ad_module_id != p.ad_module_id
asier@5545
   212
           and m.ad_module_id = c.ad_module_id
asier@5545
   213
           and dbp.ad_module_id = c.ad_module_id
asier@5545
   214
           and not exists (select 1 
asier@5545
   215
                             from dual
asier@5545
   216
                            where instr(upper(c.name), 'EM_'||upper(dbp.name)||'_') = 1)
asier@5545
   217
           AND NOT EXISTS( SELECT 1
asier@5545
   218
                             FROM AD_EXCEPTIONS
asier@5545
   219
                             WHERE TYPE='COLUMN'
asier@5545
   220
                             AND UPPER(NAME2)=UPPER(T.Tablename)
asier@5545
   221
                             AND UPPER(NAME1)=UPPER(c.Columnname)) 
asier@5558
   222
           and (2=2 and m.javapackage in ('2') or to_char('Y')=to_char(?))
asier@5545
   223
          order by 2,1
asier@5545
   224
      ]]>
asier@5545
   225
    </Sql>
asier@5545
   226
    <Parameter name="module" optional="true" type="replace" after="1=1 and m.javapackage in (" text="'1'"/>
asier@5545
   227
    <Parameter name="checkAll"/>
asier@5545
   228
    <Parameter name="module" optional="true" type="replace" after="2=2 and m.javapackage in (" text="'2'"/>
asier@5545
   229
    <Parameter name="checkAll"/>
asier@5545
   230
  </SqlMethod>
asier@5545
   231
  
asier@5681
   232
  <SqlMethod name="checkAuxiliarInput" type="preparedStatement" return="multiple">
asier@5681
   233
    <Sql>
asier@5681
   234
      <![CDATA[
asier@5681
   235
        select i.name as objectName, p.name as expectedvalue
asier@5681
   236
          from ad_auxiliarInput i, AD_Module_DBPrefix p, AD_Module m
asier@5681
   237
         where p.AD_Module_ID = i.AD_Module_ID
asier@5681
   238
           and i.ad_module_id != '0'
asier@5681
   239
           and m.ad_module_id = i.ad_module_id
asier@5681
   240
           and instr(upper(i.NAME), upper(p.name)||'_') != 1
asier@5681
   241
           and (m.javapackage in ('1') or to_char('Y')=to_char(?))
asier@5681
   242
      ]]>
asier@5681
   243
    </Sql>
asier@5681
   244
    <Field name="objecttype" value=""/>
asier@5681
   245
    <Field name="currentvalue" value=""/>
asier@5681
   246
    <Field name="expectedvalue" value=""/>
asier@5681
   247
    <Parameter name="module" optional="true" type="replace" after="m.javapackage in (" text="'1'"/>
asier@5681
   248
    <Parameter name="checkAll"/>
asier@5681
   249
  </SqlMethod>
asier@5681
   250
  
asier@5256
   251
</SqlClass>