AccessSqlParserTest


1   /******************************************************************************
2    * The contents of this file are subject to the   Compiere License  Version 1.1
3    * ("License"); You may not use this file except in compliance with the License
4    * You may obtain a copy of the License at http://www.compiere.org/license.html
5    * Software distributed under the License is distributed on an  "AS IS"  basis,
6    * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
7    * the specific language governing rights and limitations under the License.
8    * The Original Code is                  Compiere  ERP & CRM  Business Solution
9    * The Initial Developer of the Original Code is Jorg Janke  and ComPiere, Inc.
10   * Portions created by Jorg Janke are Copyright (C) 1999-2003 Jorg Janke, parts
11   * created by ComPiere are Copyright (C) ComPiere, Inc.;   All Rights Reserved.
12   * Contributor(s): ______________________________________.
13   *****************************************************************************/
14  package org.compiere.model;
15  
16  import org.compiere.*;
17  
18  import junit.framework.*;
19  
20  /**
21   *  AccessSqlParserTest tests the class
22   *  {@link <code>AccessSqlParser</code>}
23   *  
24   *  @author Jorg Janke
25   *  @version $Id: AccessSqlParserTest.java,v 1.3 2003/10/31 05:30:52 jjanke Exp $
26   */
27  public class AccessSqlParserTest extends TestCase
28  {
29      /**
30       * Construct new test instance
31       *
32       * @param name the test name
33       */
34      public AccessSqlParserTest(String   name)
35      {
36          super(name);
37      }
38  
39      /**
40       * Launch the test.
41       *
42       * @param args String[]
43       */
44      public static void main(String  [] args)
45      {
46          junit.swingui.TestRunner.run(AccessSqlParserTest.class);
47      }
48  
49      /**
50       * Perform pre-test initialization
51       *
52       * @throws Exception
53       *
54       * @see TestCase#setUp()
55       */
56      protected void setUp() throws Exception  
57      {
58          super.setUp();
59          Compiere.startupClient();
60      }
61  
62      /**
63       * Run the oneTable test
64       */
65      public void testOneTable()
66      {
67          String   sql = "SELECT AD_Table_ID, TableName FROM AD_Table WHERE IsActive='Y'";
68          AccessSqlParser fixture = new AccessSqlParser(sql);
69          assertEquals("AccessSqlParser[AD_Table|0]", fixture.toString());
70      }
71  
72      /**
73       * Run the oneTableSyn test
74       */
75      public void testOneTableSyn()
76      {
77          String   sql = "SELECT t.AD_Table_ID, t.TableName FROM AD_Table t WHERE t.IsActive='Y'";
78          AccessSqlParser fixture = new AccessSqlParser(sql);
79          assertEquals("AccessSqlParser[AD_Table=t|0]", fixture.toString());
80      }
81  
82      /**
83       * Run the oneTableSyn test
84       */
85      public void testOneTableSynAS()
86      {
87          String   sql = "SELECT t.AD_Table_ID, t.TableName FROM AD_Table AS t WHERE t.IsActive='Y'";
88          AccessSqlParser fixture = new AccessSqlParser(sql);
89          assertEquals("AccessSqlParser[AD_Table=t|0]", fixture.toString());
90      }
91  
92      /**
93       * Run the twoTable test
94       */
95      public void testTwoTable()
96      {
97          String   sql = "SELECT t.AD_Table_ID, t.TableName, c.AD_Column_ID, c.ColumnName FROM AD_Table t, AD_Column c WHERE t.AD_Table_ID=c.AD_Table_ID AND t.IsActive='Y'";
98          AccessSqlParser fixture = new AccessSqlParser(sql);
99          assertEquals("AccessSqlParser[AD_Table=t,AD_Column=c|0]", fixture.toString());
100     }
101 
102     /**
103      * Run the twoTableSyn test
104      */
105     public void testTwoTableSyn()
106     {
107         String   sql = "SELECT t.AD_Table_ID, t.TableName, c.AD_Column_ID, c.ColumnName FROM AD_Table as t, AD_Column AS c WHERE t.AD_Table_ID=c.AD_Table_ID AND t.IsActive='Y'";
108         AccessSqlParser fixture = new AccessSqlParser(sql);
109         assertEquals("AccessSqlParser[AD_Table=t,AD_Column=c|0]", fixture.toString());
110     }
111 
112     /**
113      * Run the joinInner test
114      */
115     public void testJoinInner()
116     {
117         String   sql = "SELECT t.AD_Table_ID, t.TableName, c.AD_Column_ID, c.ColumnName "
118             + "FROM AD_Table t INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID) WHERE t.IsActive='Y'";
119         AccessSqlParser fixture = new AccessSqlParser(sql);
120         assertEquals("AccessSqlParser[AD_Table=t,AD_Column=c|0]", fixture.toString());
121     }
122 
123     /**
124      * Run the joinOuter test
125      */
126     public void testJoinOuter()
127     {
128         String   sql = "SELECT t.AD_Table_ID, t.TableName, c.AD_Column_ID, c.ColumnName "
129             + "FROM AD_Table t LEFT OUTER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID) WHERE t.IsActive='Y'";
130         AccessSqlParser fixture = new AccessSqlParser(sql);
131         assertEquals("AccessSqlParser[AD_Table=t,AD_Column=c|0]", fixture.toString());
132     }
133 
134     /**
135      * Run the exists test
136      */
137     public void testExists()
138     {
139         String   sql = "SELECT AD_Table.AD_Table_ID, AD_Table.TableName "
140             + "FROM AD_Table "
141             + "WHERE EXISTS (SELECT * FROM AD_Column c WHERE AD_Table.AD_Table_ID=c.AD_Table_ID)";
142         AccessSqlParser fixture = new AccessSqlParser(sql);
143         assertEquals("AccessSqlParser[AD_Column=c|AD_Table|1]", fixture.toString());
144     }
145 
146     /**
147      * Run the exists test with syn
148      */
149     public void testExistsSyn()
150     {
151         String   sql = "SELECT t.AD_Table_ID, t.TableName "
152             + "FROM AD_Table t "
153             + "WHERE EXISTS (SELECT * FROM AD_Column c WHERE t.AD_Table_ID=c.AD_Table_ID)";
154         AccessSqlParser fixture = new AccessSqlParser(sql);
155         assertEquals("AccessSqlParser[AD_Column=c|AD_Table=t|1]", fixture.toString());
156     }
157 
158     /**
159      * Run the embeddedSelect test
160      */
161     public void testEmbeddedSelect()
162     {
163         String   sql = "SELECT t.AD_Table_ID, t.TableName,"
164             + "(SELECT COUNT(c.ColumnName) FROM AD_Column c WHERE t.AD_Table_ID=c.AD_Table_ID) "
165             + "FROM AD_Table t WHERE t.IsActive='Y'";
166         AccessSqlParser fixture = new AccessSqlParser(sql);
167         assertEquals("AccessSqlParser[AD_Column=c|AD_Table=t|1]", fixture.toString());
168     }
169 
170     /**
171      * Run the embeddedFrom test
172      */
173     public void testEmbeddedFrom()
174     {
175         String   sql = "SELECT t.AD_Table_ID, t.TableName, cc.CCount "
176             + "FROM AD_Table t,"
177             + "(SELECT COUNT(ColumnName) AS CCount FROM AD_Column) cc "
178             + "WHERE t.IsActive='Y'";
179 
180         AccessSqlParser fixture = new AccessSqlParser(sql);
181         assertEquals("AccessSqlParser[AD_Column|AD_Table=t,(##)=cc|1]", fixture.toString());
182     }
183 
184     /**
185      * Run the Product & Instance Attribute Query
186      */
187     public void testProductInstanceAttributeQuery()
188     {
189         String   sql = "SELECT p.M_Product_ID, p.Discontinued, p.Value, p.Name, BOM_Qty_Available(p.M_Product_ID,?) AS QtyAvailable, BOM_PriceList(p.M_Product_ID, pr.M_PriceList_Version_ID) AS PriceList, BOM_PriceStd(p.M_Product_ID, pr.M_PriceList_Version_ID) AS PriceStd, BOM_Qty_OnHand(p.M_Product_ID,?) AS QtyOnHand, BOM_Qty_Reserved(p.M_Product_ID,?) AS QtyReserved, BOM_Qty_Ordered(p.M_Product_ID,?) AS QtyOrdered, BOM_PriceStd(p.M_Product_ID, pr.M_PriceList_Version_ID)-BOM_PriceLimit(p.M_Product_ID, pr.M_PriceList_Version_ID) AS Margin, BOM_PriceLimit(p.M_Product_ID, pr.M_PriceList_Version_ID) AS PriceLimit, pa.IsInstanceAttribute FROM M_Product p INNER JOIN M_ProductPrice pr ON (p.M_Product_ID=pr.M_Product_ID) LEFT OUTER JOIN M_AttributeSet pa ON (p.M_AttributeSet_ID=pa.M_AttributeSet_ID) WHERE p.IsSummary='N' AND p.IsActive='Y' AND pr.IsActive='Y' AND pr.M_PriceList_Version_ID=? AND EXISTS (SELECT * FROM M_Storage s INNER JOIN M_AttributeSetInstance asi ON (s.M_AttributeSetInstance_ID=asi.M_AttributeSetInstance_ID) WHERE s.M_Product_ID=p.M_Product_ID AND asi.SerNo LIKE '33' AND asi.Lot LIKE '33' AND asi.M_Lot_ID=101 AND TRUNC(asi.GuaranteeDate)<TO_DATE('2003-10-16','YYYY-MM-DD') AND asi.M_AttributeSetInstance_ID IN (SELECT M_AttributeSetInstance_ID FROM M_AttributeInstance WHERE (M_Attribute_ID=103 AND Value LIKE '33') AND (M_Attribute_ID=102 AND M_AttributeValue_ID=106))) AND p.M_AttributeSetInstance_ID IN (SELECT M_AttributeSetInstance_ID FROM M_AttributeInstance WHERE (M_Attribute_ID=101 AND M_AttributeValue_ID=105) AND (M_Attribute_ID=100 AND M_AttributeValue_ID=102)) AND p.AD_Client_ID IN(0,11) AND p.AD_Org_ID IN(0,11,12) ORDER BY QtyAvailable DESC, Margin DESC";
190         AccessSqlParser fixture = new AccessSqlParser(sql);
191         assertEquals("AccessSqlParser[M_AttributeInstance|M_Storage=s,M_AttributeSetInstance=asi|M_AttributeInstance|M_Product=p,M_ProductPrice=pr,M_AttributeSet=pa|3]", fixture.toString());
192     }
193     
194     /**
195      * Run the Product Attribute Query
196      */
197     public void testProductAttributeQuery()
198     {
199         String   sql = "SELECT p.M_Product_ID, p.Discontinued, p.Value, p.Name, BOM_Qty_Available(p.M_Product_ID,?) AS QtyAvailable, BOM_PriceList(p.M_Product_ID, pr.M_PriceList_Version_ID) AS PriceList, BOM_PriceStd(p.M_Product_ID, pr.M_PriceList_Version_ID) AS PriceStd, BOM_Qty_OnHand(p.M_Product_ID,?) AS QtyOnHand, BOM_Qty_Reserved(p.M_Product_ID,?) AS QtyReserved, BOM_Qty_Ordered(p.M_Product_ID,?) AS QtyOrdered, BOM_PriceStd(p.M_Product_ID, pr.M_PriceList_Version_ID)-BOM_PriceLimit(p.M_Product_ID, pr.M_PriceList_Version_ID) AS Margin, BOM_PriceLimit(p.M_Product_ID, pr.M_PriceList_Version_ID) AS PriceLimit, pa.IsInstanceAttribute FROM M_Product p INNER JOIN M_ProductPrice pr ON (p.M_Product_ID=pr.M_Product_ID) LEFT OUTER JOIN M_AttributeSet pa ON (p.M_AttributeSet_ID=pa.M_AttributeSet_ID) WHERE p.IsSummary='N' AND p.IsActive='Y' AND pr.IsActive='Y' AND pr.M_PriceList_Version_ID=? AND p.M_AttributeSetInstance_ID IN (SELECT M_AttributeSetInstance_ID FROM M_AttributeInstance WHERE (M_Attribute_ID=100 AND M_AttributeValue_ID=101)) ORDER BY QtyAvailable DESC, Margin DESC";
200         AccessSqlParser fixture = new AccessSqlParser(sql);
201         assertEquals("AccessSqlParser[M_AttributeInstance|M_Product=p,M_ProductPrice=pr,M_AttributeSet=pa|1]", fixture.toString());
202     }
203     /** **/
204 }
205 
206 /*$CPS$ This comment was generated by CodePro. Do not edit it.
207  * patternId = com.instantiations.assist.eclipse.pattern.testCasePattern
208  * strategyId = com.instantiations.assist.eclipse.pattern.testCasePattern.junitTestCase
209  * additionalTestNames = oneTable, oneTableSyn, twoTable, twoTableSyn, joinInner, joinOuter, embeddedSelect, embeddedFrom
210  * assertTrue = false
211  * callTestMethod = true
212  * createMain = true
213  * createSetUp = true
214  * createTearDown = false
215  * createTestFixture = false
216  * createTestStubs = false
217  * methods = getSql(),parse()
218  * package = org.compiere.model
219  * package.sourceFolder = dbPort/src
220  * superclassType = junit.framework.TestCase
221  * testCase = AccessSqlParserTest
222  * testClassType = org.compiere.model.AccessSqlParser
223  */
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags