select.cs

C#数据库中间层处理,支持主流数据库,如sql、oracle等

/*
 * Select.cs
 *
 * Copyright (c) 2001, The HSQL Development Group
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * Redistributions of source code must retain the above copyright notice, this
 * list of conditions and the following disclaimer.
 *
 * Redistributions in binary form must reproduce the above copyright notice,
 * this list of conditions and the following disclaimer in the documentation
 * and/or other materials provided with the distribution.
 *
 * Neither the name of the HSQL Development Group nor the names of its
 * contributors may be used to endorse or promote products derived from this
 * software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL THE REGENTS OR CONTRIBUTORS BE LIABLE FOR
 * ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * This package is based on HypersonicSQL, originally developed by Thomas Mueller.
 *
 * C# port by Mark Tutt
 *
 */
namespace SharpHSQL
{
	using System;
	using System.Collections;

	/**
	 * Class declaration
	 *
	 *
	 * @version 1.0.0.1
	 */
	class Select 
	{
		public bool	     bDistinct;
		public TableFilter[]      tFilter;
		public Expression       eCondition;    // null means no condition
		public Expression[]       eColumn;     // 'result', 'group' and 'order' columns
		public int		     iResultLen;    // number of columns that are 'result'
		public int		     iGroupLen;     // number of columns that are 'group'
		public int		     iOrderLen;     // number of columns that are 'order'
		public Select	     sUnion;	    // null means no union select
		public string	     sIntoTable;    // null means not select..into
		public int		     iUnionType;
		public static int UNION = 1, UNIONALL = 2, INTERSECT = 3, EXCEPT = 4;
		// [e-mail] begin changes from 1.50
		public int limitStart = 0;
		public int limitCount = -1;
		// [e-mail] end changes from 1.50

		/**
		 * Method declaration
		 *
		 *
		 * @throws Exception
		 */
		public void resolve() 
		{
			int len = tFilter.Length;

			for (int i = 0; i < len; i++) 
			{
				resolve(tFilter[i], true);
			}
		}

		/**
		 * Method declaration
		 *
		 *
		 * @param f
		 * @param ownfilter
		 *
		 * @throws Exception
		 */
		public void resolve(TableFilter f, bool ownfilter) 
		{
			if (eCondition != null) 
			{

				// first set the table filter in the condition
				eCondition.resolve(f);

				if (f != null && ownfilter) 
				{

					// the table filter tries to get as many conditions as possible
					// but only if the table filter belongs to this query
					f.setCondition(eCondition);
				}
			}

			int len = eColumn.Length;

			for (int i = 0; i < len; i++) 
			{
				eColumn[i].resolve(f);
			}
		}

		/**
		 * Method declaration
		 *
		 *
		 * @throws Exception
		 */
		public void checkResolved() 
		{
			if (eCondition != null) 
			{
				eCondition.checkResolved();
			}

			int len = eColumn.Length;

			for (int i = 0; i < len; i++) 
			{
				eColumn[i].checkResolved();
			}
		}

		/**
		 * Method declaration
		 *
		 *
		 * @param type
		 *
		 * @return
		 *
		 * @throws Exception
		 */
		public object getValue(int type) 
		{
			resolve();

			Result r = getResult(2);    // 2 records are (already) too much
			int    size = r.getSize();
			int    len = r.getColumnCount();

			Trace.check(size == 1 && len == 1, Trace.SINGLE_VALUE_EXPECTED);

			object o = r.rRoot.data[0];

			if (r.iType[0] == type) 
			{
				return o;
			}

			string s = Column.convertobject(o);

			return Column.convertstring(s, type);
		}

		/**
		 * Method declaration
		 *
		 *
		 * @param maxrows
		 *
		 * @return
		 *
		 * @throws Exception
		 */
		public Result getResult(int maxrows) 
		{
			// [e-mail] begin changes from 1.50
			return getResult( 0, maxrows );
		}
		// [e-mail] end changes from 1.50
		// [e-mail] begin changes from 1.50
		public Result getResult(int start, int cnt) 
		{
			int maxrows=start+cnt;  //<-new, cut definitly
			// [e-mail] begin changes from 1.50
			resolve();
			checkResolved();

			if (sUnion != null && sUnion.iResultLen != iResultLen) 
			{
				throw Trace.error(Trace.COLUMN_COUNT_DOES_NOT_MATCH);
			}

			int     len = eColumn.Length;
			Result  r = new Result(len);
			bool aggregated = false;
			bool grouped = false;

			for (int i = 0; i < len; i++) 
			{
				Expression e = eColumn[i];

				r.iType[i] = e.getDataType();

				if (e.isAggregate()) 
				{
					aggregated = true;
				}
			}

			object[] agg = null;

			if (aggregated) 
			{
				agg = new object[len];
			}

			if (iGroupLen > 0) 
			{    // has been set in Parser
				grouped = true;
			}

			bool simple_maxrows = false;

			if (maxrows != 0 && grouped == false && sUnion == null && iOrderLen == 0) 
			{
				simple_maxrows = true;
			} 
			else 
			{
				simple_maxrows = false;
			}

			int     count = 0;
			int     filter = tFilter.Length;
			bool[] first = new bool[filter];
			int     level = 0;

			while (level >= 0) 
			{
				TableFilter t = tFilter[level];
				bool     found;

				if (!first[level]) 
				{
					found = t.findFirst();
					first[level] = found;
				} 
				else 
				{
					found = t.next();
					first[level] = found;
				}

				if (!found) 
				{
					level--;

					continue;
				}

				if (level < filter - 1) 
				{
					level++;

					continue;
				}

				if (eCondition == null || eCondition.test()) 
				{
					object[] row = new object[len];

					for (int i = 0; i < len; i++) 
					{
						row[i] = eColumn[i].getValue();
					}

					count++;

					if (aggregated) 
					{
						updateAggregateRow(agg, row, len);
					} 
					else 
					{
						r.add(row);

						if (simple_maxrows && count >= maxrows) 
						{
							break;
						}
					}
				}
			}

			if (aggregated &&!grouped) 
			{
				addAggregateRow(r, agg, len, count);
			} 
			else if (grouped) 
			{
				int[] order = new int[iGroupLen];
				int[] way = new int[iGroupLen];

				for (int i = iResultLen, j = 0; j < iGroupLen; i++, j++) 
				{
					order[j] = i;
					way[j] = 1;
				}

				r = sortResult(r, order, way);

				Record n = r.rRoot;
				Result x = new Result(len);

				for (int i = 0; i < len; i++) 
				{
					x.iType[i] = r.iType[i];
				}

				do 
				{
					object[] row = new object[len];

					count = 0;

					bool newgroup = false;

				while (n != null && newgroup == false) 
				{
					count++;

					for (int i = 0; i < iGroupLen; i++) 
					{
						if (n.next == null) 
						{
							newgroup = true;
						} 
						else if (Column.compare(n.data[i], n.next.data[i], r.iType[i])
							!= 0) 
						{

							// can't use .Equals because 'null' is also one group
							newgroup = true;
						}
					}

					updateAggregateRow(row, n.data, len);

					n = n.next;
				}

					addAggregateRow(x, row, len, count);
				} while (n != null);

				r = x;
			}

			if (iOrderLen != 0) 
			{
				int[] order = new int[iOrderLen];
				int[] way = new int[iOrderLen];

				for (int i = iResultLen, j = 0; j < iOrderLen; i++, j++) 
				{
					order[j] = i;
					way[j] = eColumn[i].isDescending() ? -1 : 1;
				}

				r = sortResult(r, order, way);
			}

			// the result is maybe bigger (due to group and order by)
			// but don't tell this anybody else
			r.setColumnCount(iResultLen);

			if (bDistinct) 
			{
				r = removeDuplicates(r);
			}

			for (int i = 0; i < iResultLen; i++) 
			{
				Expression e = eColumn[i];

				r.sLabel[i] = e.getAlias();
				r.sTable[i] = e.getTableName();
				r.sName[i] = e.getColumnName();
			}

			if (sUnion != null) 
			{
				Result x = sUnion.getResult(0);

				if (iUnionType == UNION) 
				{
					r.append(x);

					r = removeDuplicates(r);
				} 
				else if (iUnionType == UNIONALL) 
				{
					r.append(x);
				} 
				else if (iUnionType == INTERSECT) 
				{
					r = removeDuplicates(r);
					x = removeDuplicates(x);
					r = removeDifferent(r, x);
				} 
				else if (iUnionType == EXCEPT) 
				{
					r = removeDuplicates(r);
					x = removeDuplicates(x);
					r = removeSecond(r, x);
				}
			}

			if (maxrows > 0 &&!simple_maxrows) 
			{
				trimResult(r, maxrows);
			}

			// [e-mail] begin changes from 1.50
			if (start > 0) 
			{	//then cut the first 'start' elements
				trimResultFront( r, start );
			}
			// [e-mail] end changes from 1.50

			return r;
		}

		/**
		 * Method declaration
		 *
		 *
		 * @param row
		 * @param n
		 * @param len
		 *
		 * @throws Exception
		 */
		private void updateAggregateRow(object[] row, object[] n,
			int len) 
		{
			for (int i = 0; i < len; i++) 
			{
				int type = eColumn[i].getDataType();