1

I am developing a software that will serve as back-end for a data-ware house, in which fact definitions will be read from an xml file and corresponding fact/dimension tables will be created on the fly.

I have managed to make it work i.e. the code creates tables, updates table structure if possible otherwise drop table and make an new one, it can insert data into tables and we can query data-ware house from our client application as well. So far so good.

Now I have two problems

1) Too many sql statements. The problem: it will be night mare to maintain

2) Too many sql statements. The problem: I have been asked to support multiple databases that implies more sql statments.

I have to admit that I have not worked much with either of (n)Hibernate or Reflection.Emit.

But I was wondering how hard it would be to generate classes for my tables using Reflection.Emit and then use ActiveRecord/nHibernate to access data? So that I don't have to do the dirty work of dealing with a database directly.

WPFAbsoluteNewBie
  • 1,285
  • 2
  • 10
  • 21
  • it would help to see the API of your backend. If all is queried by strings then considered writing fixed classes with an EAV-model.`class Fact { public virtual int Id { get; set; } /// /// Propertyname/Value pairs /// public virtual IDictionary Properties { get; set; } }` – Firo Jul 28 '11 at 08:56
  • The application interacts with clients using sockets. Clients can send in commands only two commands i.e. to update what fact we are collecting like "UPDATE_FACT_DEFINITIONS FACT_DEFINITION_XML_GOES_HERE" and query a fact i.e. "QUERY QUERY_DESCRIBED_IN_XML" After reading the fact definition file, we create appropriate fact and dimension tables and look for the desired facts in files under a known directory. The code knows the mapping between the file structure and facts. When a query is received, the code knows the tables to query, so it converts the xml to sql and returns the result. – WPFAbsoluteNewBie Jul 28 '11 at 10:51
  • I just looked into EAV-model. But it seems more complex to design. It not only would consume more space but also will be lot slower apparently which I dont want :(. More importantly I want to get rid of sql statements and I have found the following blog post regarding using this approach with nHibernate [link](http://bartreyserhove.blogspot.com/2008/02/dynamic-domain-mode-using-nhibernate.html) Can you point out a better starting point? – WPFAbsoluteNewBie Jul 28 '11 at 11:01

1 Answers1

2
class Fact
{
    public virtual int Id { get; set; }
    IDictionary Properties { get; set; }
}

Template

<hibernate-mapping>
  <class name="Fact">
    <dynamic-component name="Properties">
      <!--placeholder -->
    </dynamic-component>
  </class>
</hibernate-mapping>

replace <!--placeholder --> with generated

<property
  name="P1"
  type="int" />
<property
  name="P2"
  type="string" />

Build

var doc = new System.Xml.XmlDocument();
doc.LoadXml(generatedXml);

new NHibernate.Cfg.Configuration()
    .AddDocument(doc)
...
    .BuildSessionFactory();

Query

var query = session.CreateCriteria<Fact>();

foreach (var restriction in restrictions)
{
    query.Add(Restrictions.Eq(restriction.Name, restriction.Value))
}

var facts = query.List<Fact>();

SendBack(facts);

creating/dropping the table

var dialect = Dialect.GetDialect(config.Properties);
var defaultCatalog = PropertiesHelper.GetString(NHibernate.Cfg.Environment.DefaultCatalog, config.Properties, null);
var defaultSchema = PropertiesHelper.GetString(NHibernate.Cfg.Environment.DefaultSchema, config.Properties, null);

var createTableSql = config.GetClassMapping(typeof(Fact)).Table.SqlCreateString(dialect, config.BuildMapping(), defaultCatalog, defaultSchema);
var dropTableSql = config.GetClassMapping(typeof(int)).Table.SqlDropString(dialect, defaultCatalog, defaultSchema);
Firo
  • 30,626
  • 4
  • 55
  • 94
  • I may have multiple fact classes and each has his its own set of dimensions. At compile time don't know how many facts or dimension will there... I don't want one big fact table that include all dimension. Can I create a separate mapping for each instance of Fact class? – WPFAbsoluteNewBie Aug 18 '11 at 12:54
  • yes you can although you then need multiple sessionfactories to hold the different mapping for the same Fact class and you probably need more than one Fact table? Or maybe different subtypes of fact with table per subclass? need more info – Firo Aug 19 '11 at 08:45
  • may i know why do we use "restrictions".I have to dynamically load table in the grid using nhibernate ,I think it helps me ? – Arun.P May 25 '15 at 10:10
  • restrictions are there to filter the data befor using/showing it. – Firo May 26 '15 at 08:47