re:updatableViews

From: Rick Elbers <rick_at_elbers.org>
Date: Tue, 26 Sep 2006 18:32:54 +0200
Message-ID: <4hlih2hpv92mhh0mi2m64objp7s9r8ih5n_at_4ax.com>

Dear fellow programmers,

The problem with an in memory database which you want in case of 2 tier applications is that you dont know where to start inserting or deleting records in the actual (server) database. Thats why you might have a good use in 2-tier apps for an UpdatableView.

An important part of the updatable view problem is topological sort as someone pointed out before. Especially with autojoins. Finally I found some time to crank out some code in c#.

 I am asking everyone to verify the code below on his sql database to see if it comes up with a good topological sort of every table. (test code is added)

Rick

using System;

using System.Collections.Generic;
using System.Text;
using System.Data;

namespace Graph
{

    public class DataSetTopologicalSorter     {

        DataTableCollection m_Tables;

        public DataSetTopologicalSorter(DataSet set)
        {
            m_Tables = set.Tables;
        }
        
        private int NrOfAutoJoins(string  tablename)
        {
            int Nr = 0;
            DataTable table = m_Tables[tablename];
            
            foreach (DataRelation Rel in table.ParentRelations)


            {

                if (Rel.ParentTable.Equals(table))
                    Nr++;

            }
            return Nr;
        }
        public List<DataTable> Sort()
        {
            List<DataTable> SortedTables = new List<DataTable>();
            Dictionary<string, int> graph_in_process = 
                new Dictionary<string,int>();
            foreach(DataTable table in m_Tables)
            //put all datatables in graph..


            {

                // in that process find out the nr of in_degrees per

table, use parentrelations corrected for autojoins

graph_in_process.Add(table.TableName,table.ParentRelations.Count);

                //autojoin solved
                graph_in_process[table.TableName]-=

NrOfAutoJoins(table.TableName);

            }
            Queue<DataTable> IndependentVertexes = new
Queue<DataTable>();
            foreach(string name in graph_in_process.Keys)


            {

                // put all datatables with zero in_degrees in queue.
                // also datatables with only itself as in_degree
                if(  graph_in_process[ name ]  == 0)

                    IndependentVertexes.Enqueue(m_Tables[name]);
            }
 
            while(IndependentVertexes.Count>0)


            {

                //go until gueue is empty...
                //start with a in_degree_zero node van de stapel..
                DataTable aTable = IndependentVertexes.Dequeue()
                                     as DataTable;
                //if we want visitor replace this line by  
                //visitor.visit(aTable)
                SortedTables.Add(aTable);
                
                //get to successors (childrelations)
                foreach(DataRelation aRelation in
aTable.ChildRelations)
                {
                    // decrement all connected vertexes( use

getchildtables corrected for autojoins in child)

--graph_in_process[aRelation.ChildTable.TableName];

                  // if the connected vertex is now in_degree zero 
                  //  add it to the queue
                    if ( (
graph_in_process[aRelation.ChildTable.TableName]-= 
                        this.NrOfAutoJoins(
aRelation.ChildTable.TableName))
                        == 0)

IndependentVertexes.Enqueue(aRelation.ChildTable);
                }
            }
            //if queue is not empty we have cycles..
            if (IndependentVertexes.Count > 0)


            {

                SortedTables = null;
                throw new Exception("DataTables are containing cyclic
dependcies");
            }
            return SortedTables;
        }
            

   }

}

To test this code in c#.20
make a datasource of your favorite complex database. That gives a typed dataset. Instantiate that like for instance:

TestDBDataSet set = new TestDBDataSet();

Then gui design a form with a listbox names lstTables and put the following code in form_load:

   private void Form1_Load(object sender, EventArgs e)

        {
            List < DataTable > sortedTables = 
                new Graph.DataSetTopologicalSorter(set).Sort();

            foreach(DataTable aTable in sortedTables)
                this.lstTables.Items.Add(aTable.TableName);

        }

Regards,
Rick Received on Tue Sep 26 2006 - 18:32:54 CEST