/// <summary> /// Default constructor. /// </summary> /// <param name="t"></param> /// <param name="alias"></param> /// <param name="outerjoin"></param> public TableFilter(Table t, string alias, bool outerjoin) { tTable = t; iIndex = null; sAlias = alias != null ? alias : t.Name; bOuterJoin = outerjoin; oEmptyData = tTable.NewRow; }
public bool FindFirst() { if (iIndex == null) { iIndex = tTable.PrimaryIndex; } if (eStart == null) { nCurrent = iIndex.First(); } else { ColumnType type = eStart.Arg.ColumnType; object o = eStart.Arg2.GetValue(type); nCurrent = iIndex.FindFirst(o, eStart.Type); } while (nCurrent != null) { oCurrentData = nCurrent.GetData(); if (!Test(eEnd)) { break; } if (Test(eAnd)) { return true; } nCurrent = iIndex.Next(nCurrent); } oCurrentData = oEmptyData; if (bOuterJoin) { return true; } return false; }
public void SetCondition(Expression e) { ExpressionType type = e.Type; Expression e1 = e.Arg; Expression e2 = e.Arg2; if (type == ExpressionType.And) { SetCondition(e1); SetCondition(e2); return; } int candidate; switch (type) { case ExpressionType.NotEqual: case ExpressionType.Like: // todo: maybe use index case ExpressionType.In: candidate = 0; break; case ExpressionType.Equal: candidate = 1; break; case ExpressionType.Bigger: case ExpressionType.BiggerEqual: candidate = 2; break; case ExpressionType.Smaller: case ExpressionType.SmallerEqual: candidate = 3; break; default: // not a condition so forget it return; } if (e1.Filter == this) { // ok include this } else if (e2.Filter == this && candidate != 0) { // swap and try again to allow index usage e.SwapCondition(); SetCondition(e); return; } else { // unrelated: don't include return; } TracingHelper.Assert(e1.Filter == this, "setCondition"); if (!e2.IsResolved) { return; } if (candidate == 0) { AddAndCondition(e); return; } int i = e1.ColumnNumber; Index index = tTable.GetIndexForColumn(i); if (index == null || (iIndex != index && iIndex != null)) { // no index or already another index is used AddAndCondition(e); return; } iIndex = index; if (candidate == 1) { // candidate for both start & end if (eStart != null || eEnd != null) { AddAndCondition(e); return; } eStart = new Expression(e); eEnd = eStart; } else if (candidate == 2) { // candidate for start if (eStart != null) { AddAndCondition(e); return; } eStart = new Expression(e); } else if (candidate == 3) { // candidate for end if (eEnd != null) { AddAndCondition(e); return; } eEnd = new Expression(e); } e.SetTrue(); }
public Index GetNextIndex(Index index) { int i = 0; if (index != null) { for (; i < iIndexCount && GetIndex(i) != index; i++); i++; } if (i < iIndexCount) { return GetIndex(i); } return null; // no more indexes }
public void CreateIndex(int[] column, string name, bool unique) { TracingHelper.Assert(iPrimaryKey != -1, "createIndex"); for (int i = 0; i < iIndexCount; i++) { Index index = GetIndex(i); if (name.Equals(index.Name)) { throw TracingHelper.Error(TracingHelper.INDEX_ALREADY_EXISTS); } } int s = column.Length; // The primary key field is added for non-unique indexes // making all indexes unique int[] col = new int[unique ? s : s + 1]; ColumnType[] type = new ColumnType[unique ? s : s + 1]; for (int j = 0; j < s; j++) { col[j] = column[j]; type[j] = GetColumn(col[j]).ColumnType; } if (!unique) { col[s] = iPrimaryKey; type[s] = GetColumn(iPrimaryKey).ColumnType; } Index newindex = new Index(name, col, type, unique); if (iIndexCount != 0) { TracingHelper.Assert(IsEmpty, "createIndex"); } vIndex.Add(newindex); iIndexCount++; }
public void CreateIndex(Index index) { CreateIndex(index.Columns, index.Name, index.IsUnique); }
/// <summary> /// Process ALTER TABLE statements. /// /// ALTER TABLE tableName ADD COLUMN columnName columnType; /// ALTER TABLE tableName DELETE COLUMN columnName; /// </summary> /// <remarks> /// The only change I've made to Sergio's original code was /// changing the insert's to call insertNoCheck to bypass the trigger /// mechanism that is a part of hsqldb 1.60 and beyond. - Mark Tutt /// </remarks> /// <returns></returns> public Result ProcessAlter() { tTokenizer.GetThis ("TABLE"); string token = tTokenizer.GetString (); cChannel.CheckReadWrite (); // cChannel.check(token,Access.ALTER); --> Accessul nu-l inca controleaza... string tName = token; string swap = tName + "SWAP"; // nimicirea swapului... dDatabase.Execute ("DROP TABLE " + swap, cChannel); Table initialTable = dDatabase.GetTable (token, cChannel); int count = 0; token = tTokenizer.GetString (); if (token.Equals ("ADD")) { token = tTokenizer.GetString (); if (token.Equals ("COLUMN")) { Table swapTable = new Table (dDatabase, true, swap, initialTable.IsCached); // copiem coloanele (fara date) din tabelul initial in swap for (int i = 0; i < initialTable.ColumnCount; i++) { Column aColumn = initialTable.GetColumn (i); swapTable.AddColumn (aColumn); } // end Of copiem coloanele... // aflam daca are PrimaryKey & o cream... string cName = tTokenizer.GetString (); string cType = tTokenizer.GetString (); ColumnType iType = Column.GetColumnType (cType); string sToken = cType; // int primarykeycolumn = -1; bool identity = false; int column = initialTable.ColumnCount + 1; // !-- // stolen from CREATE TABLE... string sColumn = cName; if (iType == ColumnType.VarChar && dDatabase.IsIgnoreCase) { iType = ColumnType.VarCharIgnoreCase; } sToken = tTokenizer.GetString (); if (iType == ColumnType.DbDouble && sToken.Equals ("PRECISION")) { sToken = tTokenizer.GetString (); } if (sToken.Equals ("(")) { // overread length do { sToken = tTokenizer.GetString (); } while (!sToken.Equals (")")); sToken = tTokenizer.GetString (); } // !-- bool nullable = true; if (sToken.Equals ("NULL")) { sToken = tTokenizer.GetString (); } else if (sToken.Equals ("NOT")) { tTokenizer.GetThis ("NULL"); nullable = false; sToken = tTokenizer.GetString (); } /* * if(sToken.Equals("IDENTITY")) { * identity=true; * Trace.check(primarykeycolumn==-1,Trace.SECOND_PRIMARY_KEY,sColumn); * sToken=tTokenizer.getstring(); * primarykeycolumn=column; * } * * if(sToken.Equals("PRIMARY")) { * tTokenizer.getThis("KEY"); * Trace.check(identity || primarykeycolumn==-1, * Trace.SECOND_PRIMARY_KEY,sColumn); * primarykeycolumn=column; * //sToken=tTokenizer.getstring(); * } * //end of STOLEN... */ swapTable.AddColumn (cName, iType, nullable, identity); // under construction... if (initialTable.ColumnCount < initialTable.InternalColumnCount) { swapTable.CreatePrimaryKey (); } else { swapTable.CreatePrimaryKey (initialTable.PrimaryIndex.Columns [0]); } // endof PrimaryKey... // sa ne farimam cu indicii... ;-(( Index idx = null; while (true) { idx = initialTable.GetNextIndex (idx); if (idx == null) { break; } if (idx == initialTable.PrimaryIndex) { continue; } swapTable.CreateIndex (idx); } // end of Index... cChannel.Commit (); dDatabase.LinkTable (swapTable); Tokenizer tmpTokenizer = new Tokenizer ("SELECT * FROM " + tName); Parser pp = new Parser (dDatabase, tmpTokenizer, cChannel); string ff = tmpTokenizer.GetString (); if (!initialTable.IsEmpty) { Record n = ((Result)pp.ProcessSelect ()).Root; do { object[] row = swapTable.NewRow; object[] row1 = n.Data; for (int i = 0; i < initialTable.ColumnCount; i++) { row [i] = row1 [i]; } swapTable.InsertNoCheck (row, cChannel); n = n.Next; } while (n != null); } dDatabase.Execute ("DROP TABLE " + tName, cChannel); // cream tabelul vechi cu proprietatile celui nou... initialTable = new Table (dDatabase, true, tName, swapTable.IsCached); for (int i = 0; i < swapTable.ColumnCount; i++) { Column aColumn = swapTable.GetColumn (i); initialTable.AddColumn (aColumn); } if (swapTable.ColumnCount < swapTable.InternalColumnCount) { initialTable.CreatePrimaryKey (); } else { initialTable.CreatePrimaryKey (swapTable.PrimaryIndex.Columns [0]); } // endof PrimaryKey... // sa ne farimam cu indicii... ;-(( idx = null; while (true) { idx = swapTable.GetNextIndex (idx); if (idx == null) { break; } if (idx == swapTable.PrimaryIndex) { continue; } initialTable.CreateIndex (idx); } // end of Index... cChannel.Commit (); dDatabase.LinkTable (initialTable); // end of cream... // copiem datele din swap in tabel... tmpTokenizer = new Tokenizer ("SELECT * FROM " + swap); pp = new Parser (dDatabase, tmpTokenizer, cChannel); ff = tmpTokenizer.GetString (); if (!swapTable.IsEmpty) { Record n = ((Result)pp.ProcessSelect ()).Root; do { object[] row = initialTable.NewRow; object[] row1 = n.Data; for (int i = 0; i < swapTable.ColumnCount; i++) { row [i] = row1 [i]; } initialTable.InsertNoCheck (row, cChannel); n = n.Next; } while (n != null); // end of copiem... } dDatabase.Execute ("DROP TABLE " + swap, cChannel); count = 4; } else { throw TracingHelper.Error (TracingHelper.UnexpectedToken, token); } } else if (token.Equals ("DELETE")) { token = tTokenizer.GetString (); if (token.Equals ("COLUMN")) { Table swapTable = new Table (dDatabase, true, swap, initialTable.IsCached); string cName = tTokenizer.GetString (); int undesired = initialTable.GetColumnNumber (cName); for (int i = 0; i < initialTable.ColumnCount; i++) { Column aColumn = initialTable.GetColumn (i); if (i != undesired) { swapTable.AddColumn (aColumn); } } int pKey = -1; // !-- if (initialTable.ColumnCount < initialTable.InternalColumnCount) { swapTable.CreatePrimaryKey (); } else { int[] cols = initialTable.PrimaryIndex.Columns; pKey = cols [0]; if ((cols [0] > undesired) || (cols [0] + cols.Length < undesired)) { if (undesired < initialTable.PrimaryIndex.Columns [0]) { // reindexarea... for (int i = 0; i < cols.Length; i++) { cols [i]--; } // endOf reindexarea... } // MT: This initially wouldn't compile, missing the array index on cols[] swapTable.CreatePrimaryKey (cols [0]); } else { swapTable.CreatePrimaryKey (); } } // endof PrimaryKey... // sa ne farimam cu indicii... ;-(( Index idx = null; while (true) { idx = initialTable.GetNextIndex (idx); if (idx == null) { break; } if (idx == initialTable.PrimaryIndex) { continue; } bool flag = true; int[] cols = idx.Columns; for (int i = 0; i < cols.Length; i++) { if (cols [i] == undesired) { flag = false; } } if (flag) { Index tIdx; for (int i = 0; i < cols.Length; i++) { if (cols [i] > undesired) { cols [i]--; } } tIdx = new Index (idx.Name, idx.Columns, idx.ColumnType, idx.IsUnique); swapTable.CreateIndex (tIdx); } } // !-- cChannel.Commit (); dDatabase.LinkTable (swapTable); Tokenizer tmpTokenizer = new Tokenizer ("SELECT * FROM " + tName); Parser pp = new Parser (dDatabase, tmpTokenizer, cChannel); string ff = tmpTokenizer.GetString (); if (!initialTable.IsEmpty) { Record n = ((Result)pp.ProcessSelect ()).Root; do { object[] row = swapTable.NewRow; object[] row1 = n.Data; int j = 0; for (int i = 0; i < initialTable.ColumnCount; i++) { if (i != undesired) { row [j] = row1 [i]; j++; } } swapTable.InsertNoCheck (row, cChannel); n = n.Next; } while (n != null); } dDatabase.Execute ("DROP TABLE " + tName, cChannel); // cream tabelul vechi cu proprietatile celui nou... initialTable = new Table (dDatabase, true, tName, swapTable.IsCached); for (int i = 0; i < swapTable.ColumnCount; i++) { Column aColumn = swapTable.GetColumn (i); initialTable.AddColumn (aColumn); } // !-- if (swapTable.ColumnCount < swapTable.InternalColumnCount) { initialTable.CreatePrimaryKey (); } else { initialTable.CreatePrimaryKey (swapTable.PrimaryIndex.Columns [0]); } // endof PrimaryKey... // sa ne farimam cu indicii... ;-(( idx = null; while (true) { idx = swapTable.GetNextIndex (idx); if (idx == null) { break; } if (idx == swapTable.PrimaryIndex) { continue; } initialTable.CreateIndex (idx); } // end of Index... // !-- cChannel.Commit (); dDatabase.LinkTable (initialTable); // end of cream... // copiem datele din swap in tabel... tmpTokenizer = new Tokenizer ("SELECT * FROM " + swap); pp = new Parser (dDatabase, tmpTokenizer, cChannel); ff = tmpTokenizer.GetString (); if (!swapTable.IsEmpty) { Record n = ((Result)pp.ProcessSelect ()).Root; do { object[] row = initialTable.NewRow; object[] row1 = n.Data; for (int i = 0; i < swapTable.ColumnCount; i++) { row [i] = row1 [i]; } initialTable.InsertNoCheck (row, cChannel); n = n.Next; } while (n != null); // end of copiem... } dDatabase.Execute ("DROP TABLE " + swap, cChannel); count = 3; } else { throw TracingHelper.Error (TracingHelper.UnexpectedToken, token); } count = 3; } Result r = new Result (); r.SetUpdateCount (count); return r; }
/// <summary> /// Constructor using Main and Reference Table. /// </summary> /// <param name="type"></param> /// <param name="main"></param> /// <param name="child"></param> /// <param name="columnMain"></param> /// <param name="columnRef"></param> public Constraint(ConstraintType type, Table main, Table child, int[] columnMain, int[] columnRef) { _type = type; _mainTable = main; _refTable = child; _mainColumns = columnMain; _refColumns = columnRef; _len = columnMain.Length; if (TracingHelper.AssertEnabled) { TracingHelper.Assert(columnMain.Length == columnRef.Length); } _mainData = _mainTable.NewRow; _refData = _refTable.NewRow; _mainIndex = _mainTable.GetIndexForColumns(columnMain); _refIndex = _refTable.GetIndexForColumns(columnRef); }