The Salty Economist

Things I Should Have Learned in High School
posts - 56, comments - 0, trackbacks - 0

Getting Stock Prices: F# & NHibernate (3)

Now that we can read from our database, the next step is to save our price data using FluentNHibernate.  The first thing, we need to do is to add a simple table to store our stock price data:

CREATE TABLE [STOCK_PRICE] (
 [Id] [int] IDENTITY (1, 1) NOT NULL ,
 [COMPANY_ID] [int] NOT NULL ,
 [CLOSING_PRICE] [float] NOT NULL ,
 [VOLUME] [float] NOT NULL ,
 [THE_DATE] [datetime] NOT NULL ,
 CONSTRAINT [PK_STOCK_PRICES] PRIMARY KEY  CLUSTERED
 (
  [Id]
 )  ON [PRIMARY] ,
 CONSTRAINT [FK_STOCK_PRICES_COMPANY] FOREIGN KEY
 (
  [COMPANY_ID]
 ) REFERENCES [COMPANY] (
  [Id]
 )
) ON [PRIMARY]

The only interesting thing of note here is the foreign key to the company table.  Next we need to add a STOCK_PRICE object.  Because we want to use FluentNHibernate, the class objects have to mirror our database:

-------------------------------------
VB
-------------------------------------
Option Explicit On

Namespace StockPrices

    Public Class STOCK_PRICE

        Private _ID As Integer
        Private _COMPANY_ID As Integer
        Private _VOLUME As Double
        Private _CLOSING_PRICE As Double
        Private _THE_DATE As Date

        Public Overridable Property Id() As Integer

            Get
                Id = _ID
            End Get

            Set(ByVal value As Integer)
                _ID = value
            End Set

        End Property

        Public Overridable Property COMPANY_ID() As Integer

            Get
                COMPANY_ID = _COMPANY_ID
            End Get

            Set(ByVal value As Integer)
                _COMPANY_ID = value
            End Set

        End Property

        Public Overridable Property VOLUME() As Double

            Get
                VOLUME = _VOLUME
            End Get

            Set(ByVal value As Double)
                _VOLUME = value
            End Set

        End Property

        Public Overridable Property CLOSING_PRICE() As Double

            Get
                CLOSING_PRICE = _CLOSING_PRICE
            End Get

            Set(ByVal value As Double)
                _CLOSING_PRICE = value
            End Set

        End Property

        Public Overridable Property THE_DATE() As Date

            Get
                THE_DATE = _THE_DATE
            End Get

            Set(ByVal value As Date)
                _THE_DATE = value
            End Set

        End Property

        Public Sub New(ByVal CompanyId As Integer, ByVal aDate As String, ByVal aPrice As Double, ByVal aVolume As Double)

            COMPANY_ID = CompanyId
            THE_DATE = CDate(aDate)
            CLOSING_PRICE = aPrice
            VOLUME = aVolume

        End Sub

        Public Sub New()


        End Sub

    End Class

    Public Class COMPANY

        Private _ID As Integer
        Private _CompanyName As String
        Private _CompanyTicker As String
        Private _StockPrices As IList(Of STOCK_PRICE)

        Public Overridable Property STOCK_PRICES() As IList(Of STOCK_PRICE)

            Get
                STOCK_PRICES = _StockPrices
            End Get

            Set(ByVal value As IList(Of STOCK_PRICE))
                _StockPrices = value
            End Set

        End Property

        Public Overridable Property Id() As Integer

            Get
                Id = _ID
            End Get

            Set(ByVal value As Integer)
                _ID = value
            End Set

        End Property

        Public Overridable Property COMPANY_NAME() As String

            Get
                COMPANY_NAME = _CompanyName
            End Get

            Set(ByVal value As String)
                _CompanyName = value
            End Set

        End Property

        Public Overridable Property COMPANY_TICKER() As String

            Get
                COMPANY_TICKER = _CompanyTicker
            End Get

            Set(ByVal value As String)
                _CompanyTicker = value
            End Set

        End Property

        Sub New()

            _StockPrices = New List(Of STOCK_PRICE)

        End Sub

    End Class

End Namespace

-------------------------------------

(1)  The properties must be tagged as Overridable in order to make them equivalent to virtual properties in C#
(2)  The STOCK_PRICES IList in the COMPANY_CLASS must be of type IList -- List will not work.  It took some work to sort that out.
(3)  The STOCK_PRICES IList:

Public Overridable Property STOCK_PRICES() As IList(Of STOCK_PRICE)
    Get
       STOCK_PRICES = _StockPrices
    End Get

    Set(ByVal value As IList(Of STOCK_PRICE))
        _StockPrices = value
    End Set

End Property

will hold all our stock price data for a given company (ticker).  In setting these objects up to be isomorphic with our database, we can take advantage of FluentNHIbernate's automappings.  This allows us load all a company's stock prices at the same time we load the company.  This saves us the hassle of loading up a company, getting its Id, and the using that Id to query the database for the company's stock prices.  FluentNHIbernate will do it all for you.  I will show this below.

-----------------------------------------------------------
Here are the classes in C# for those who can't stomach VB
-----------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace StockPrices
{
    public class STOCK_PRICE {

        public virtual int Id { get; set; }
        public virtual int COMPANY_ID { get; set; }
        public virtual double VOLUME { get; set; }
        public virtual double CLOSING_PRICE { get; set; }
        public virtual DateTime THE_DATE { get; set; }

        public virtual void New(int CompanyId, string aDate, double aPrice, double aVolume)
        {

            THE_DATE = Convert.ToDateTime(aDate);
            CLOSING_PRICE = aPrice;
            VOLUME = aVolume;

        }

        public virtual void  New() {


        }

    }

    public class COMPANY {

        public virtual int Id { get; set; }
        public virtual string COMPANY_NAME { get; set; }
        public virtual string COMPANY_TICKER { get; set; }
        public virtual IList<STOCK_PRICE> STOCK_PRICES { get; set; }

        public virtual void New()
        {

            STOCK_PRICES = new List<STOCK_PRICE>();

        }

    }
}
-----------------------------------------------------------

As an aside, about the only difference between using VB and C# I have run into has to do with the following statement:

let autoMappings = (AutoPersistenceModel.MapEntitiesFromAssemblyOf<StockPrices.COMPANY>()).Where(fun t -> if t.Namespace = "StockPrices.StockPrices")

This statement is used setup your auto mappings in FluentNHibernate.

In VB, you need both the Assembly Name and the Namespace.

In C#, you just need the Namespace.

Why the difference.  That's way beyond me.  I suppose I will figure out how to write my class objects in F# some time.

Any how, let's now write the simple program to save our stock price data...

Here it is:

----------------------------------------------------------

#light
open System
open System.Collections.Generic
open System.IO
open StockPrices

open FluentNHibernate.AutoMap
open FluentNHibernate

let properties = new Dictionary<string, string>()

let connString = "server='BIG_ROCK\LOGGERSEDGE';Initial Catalog=SMDATA;User ID=sa;Password=XXXXXX"

properties.Add("connection.provider", "NHibernate.Connection.DriverConnectionProvider")
properties.Add("dialect", "NHibernate.Dialect.MsSql2000Dialect")
properties.Add("connection.driver_class", "NHibernate.Driver.SqlClientDriver")
properties.Add("show_sql", "true")
properties.Add("connection.connection_string", connString)

let autoMappings = (AutoPersistenceModel.MapEntitiesFromAssemblyOf<StockPrices.COMPANY>()).Where(fun t -> if t.Namespace = "StockPrices.StockPrices" then true else false)

let aConfig = (new NHibernate.Cfg.Configuration()).AddProperties(properties).AddAutoMappings(autoMappings)

let sessionFactory = aConfig.BuildSessionFactory()

let aSession = sessionFactory.OpenSession()

aSession.BeginTransaction()

let companyList = aSession.CreateCriteria(typeof<StockPrices.COMPANY>).List()

for someObj in companyList do

    let xCo = someObj :?> StockPrices.COMPANY
    printfn "Company Name: %s,  Ticker: %s" xCo.COMPANY_NAME xCo.COMPANY_TICKER
   
    let prices = GetPrices.getStockPrices xCo.COMPANY_TICKER 2009 1 1 2009 5 31
   
    for aObs : GetPrices.Observation in prices do
        let aPrice (aObs : GetPrices.Observation) =
            match aObs.Event with
            | GetPrices.Event.StkPrice(p) -> GetPrices.toFloat(p.Close)
            | GetPrices.Event.Dividend(d) -> GetPrices.toFloat(d)
            | _ -> -999.0
       
        printfn "Here's %s: %s %10.5f" xCo.COMPANY_NAME (aObs.Date.ToShortDateString()) (aPrice(aObs))
       
        let xPrice = new StockPrices.STOCK_PRICE(xCo.Id, aObs.Date.ToShortDateString(), aPrice(aObs), 0.0)
        let id = aSession.Save(xPrice)
        ()

aSession.Transaction.Commit()
aSession.Close()

------------------------------------------------

I pretty much covered everything here in my last post, with the exception of the two lines in aquamarine.

The first line instantiates a new price object and passes it its values when it is created.  Note above that I have overloaded the New() function.

The second statement:

let id = aSession.Save(xPrice)

does all the heavy lifting.  It tells NHibernate to save (insert) my xPrice object.  And what is really cool, is that it returns the Id of the new row (I am using Identity columns for my Id's).  I don't use it here, but if I had other related records, it could come in very handy.

I do have to commit my data to actually save it.  The relevant line is:

aSession.Transaction.Commit()

Piece of cake.

The next chore is to read what we've written.

Print | posted on Friday, July 3, 2009 5:07 PM |

Powered by:
Powered By Subtext Powered By ASP.NET