Taoffi's blog

prisonniers du temps

doc5ync – word index web page presentation!

Objectives:

  • Display a cloud of index words, each dimensioned relative to its occurrences in e-book information (e-book title, description, author, editor… etc.)
  • On selection of a given word: display the list of e-book references related to the selected word
  • On selection of an e-book: display its information details and all words linked to it

Context:

doc5ync web interface is based on a meta-model engine (simpleSite, currently being renamed to web5ync!).

I talked about meta-models in a past post Here, with some posts about its potential applications here.

The basic concept of meta-models is to describe an object by its set of properties and enable the user to act on these properties by modifying their values in the meta-model database. On runtime, those property values are assigned to each defined object.

In our case, for instance, we have a meta-model describing web page elements, and a meta-model describing the dataset of word index and their related e-books.

For web page elements, the approach considers a web page as a set of html tags (i.e. <div>, <table><tr><td>…, <img… etc.). Where each tag has a set of properties (style, and other attributes) for which you can define the desired values. On runtime, your meta-model-defined web page comes to life by loading its html tags, assigning to each the defined values and injecting the output of the process to the web response.

A dataset is similarly considered as a set of rows (obtained through a data source), each composed of data cells containing values. Data cells can then be either presented and manipulated through web elements (html tags, above) or otherwise manipulated through web services.

Data storage and relationships

As we mentioned in the previous post, index words and their related e-books are stored in database tables as illustrated in the following figure:

Each word of the index provides us with its number of occurrences in e-book text sequences (known on Trie scan).

Html formatting using a SQL view

To reflect this information into a presentation, we used a view to format a html div element for each word relative to its number of occurrences. The query looks like the following code

select
  w.id            as word_id
 , w.n_occurs
 , N'<div style="BASIC STYLE STRING HERE…; display:inline;'

-- add the font-size style relative to number of occurrences
+
case
    when w.n_occurs between 0 and 2    then N' font-size:10pt;'
    when w.n_occurs between 3 and 8    then N' font-size:14pt;'
    when w.n_occurs between 9 and 15    then N' font-size:16pt;'
    when w.n_occurs between 16 and 24    then N' font-size:22pt;'
    when w.n_occurs between 25 and 2147483647    then N' font-size:26pt; '
end

+ N'"'
-- add whatever html attributes we need (hover/click…)
+
N' id="div' + convert(nvarchar(32), w.id)
+ N'" onclick="select_data_cell(''' + convert(nvarchar(32), w.id) + N''');" '
     as word_string_html

-- add other columns if needed
from dbo.doc5_trie_words w
order by w.word_string

 

The above view code provides us with html-preformatted string for each word index in the data row.

Tweaking the data rows into a cloud of words

On a web page, a dataset is commonly displayed as a grid (table / columns / rows), and web5ync knows how to read a data source, and output its rows into that form. But that did not seem to be convenient in our case, because it simply displays index words each on a row which is not really the presentation we are looking for!

 

To resolve this, we simply need to change the dataset web container from a <table> (and its containing rows / cells) into <div> tags (with style=display: inline).

Here a sample of html code of the above presentation:

<table>
  <tr>
    <td>
    <div style="font-size:16pt;" onclick="select_data_cell('29008');">After</div>
  </td>
</tr>
<tr>
  <td>
    <div style="font-size:26pt; onclick="select_data_cell('28526');">after</div>
  </td>
</tr>

<!-- the table rows go on... --> 


And here is a sample html code of the presentation we are looking for:

<div style="display:inline;">
    <div id="td_word_string_html82" style="display:inline;">
       <div style="font-size:26pt;" onclick="select_data_cell('28526');">after</div>
      </div>
</div>

<div style="display:inline;">
    <div id="td_word_string_html83">
      <div style="font-size:16pt;" onclick="select_data_cell('29008');">After</div>
    </div>
</div>

<div style="display:inline;">
   <div style="display:inline;">
      <div style="font-size:10pt;" onclick="select_data_cell('17657');">AFTER</div>
    </div>
</div>

 

Which looks closer to what we want:

Interacting with index words

The second part of our task is to allow the user to interact with the index words: clicking a word = display its related e-books, clicking an e-book = display the e-book details + display index words specifically linked to that e-book.

For this, we are going to use a few of the convenient features of web5ync, namely: Master/details data binding, and Tabs. (I will write more about these features in a future post)

Web5ync master/details binding allows linking a subset of data to a selected item in the master section. Basically, each data section is an iframe. The event of selecting a data row in one iframe can update the document source of one or more iframes. All what we need is: 1. define a column that will be used as the row's id, and 2. define how the value of that id should be passed to the target iframe (typically: url parameter name).

Tabs are convenient in our case as they will allow distributing the information in several areas while optimizing web page space usage.

In the figure above, we have 3 main data tabs: n Explore by words, n Document info and n Selected document words.

On the first tab:

  • clicking a word (in the upper iframe) should display the list of its related e-books (in lower iframe of that same tab)
  • clicking an e-book row on the lower iframe should: first displays its details (an iframe on the second tab), and display all words directly linked to the selected e-book (an iframe in the 3rd tab). (figures below)

In that last tab, we can play once again with the displayed words, to show other documents sharing one of them:

TFS database pause: Change set quantitative statistics sample

Where to find change-sets information?


TFS source control maintains change-sets for projects in the corresponding collection database. Each change-set is of course composed of the modified project files at the time of the Check-in.

Change-set information is stored in the tbl_ChangeSet table of the project collection database. That is: if you have a project collection 'Default Collection' stored in a database named tfs_default_collection, you will find change-sets information in the table tfs_default_collection.dbo.tbl_ChangeSet.

Files information is stored in tbl_files table, and file-versions information in tbl_Version table.

So, to obtain the list of files of a change-set number 861, for instance, you can execute a query similar to the following:


SELECT TOP (100) percent
    chg_set.CreationDate,
    chg_set.ChangeSetId,
    v.FullPath

FROM
    dbo.tbl_ChangeSet (nolock)AS chg_set INNER JOIN
    
dbo.
tbl_Version (nolock)AS v ON chg_set.ChangeSetId = v.VersionFrom LEFT OUTER JOIN
    
dbo.
tbl_File (nolock) AS f ON v.FileId = f.FileId


WHERE (chg_set.ChangeSetId = 861)

ORDER BY chg_set.CreationDate, v.FullPath

 

 

Which may produce results like the following:

CreationDate

ChangeSetId

FullPath

2012-05-05 20:22:31.800

861

$\Research education "agile\SimpleUniConverter\commands\UniConvertCommandModel.cs\

2012-05-05 20:22:31.800

861

$\Research education "agile\SimpleUniConverter\Convert.xaml.cs\

2012-05-05 20:22:31.800

861

$\Research education "agile\SimpleUniConverter\Convert.xaml\

2012-05-05 20:22:31.800

861

$\Research education "agile\SimpleUniConverter\MainWindow.xaml.cs\

2012-05-05 20:22:31.800

861

$\Research education "agile\SimpleUniConverter\MainWindow.xaml\

 

How many bytes changed?


As you may have noticed from the figure above, change-set file information contains a FileLength column. This suggests that we can obtain the number of changed bytes of a given file between two change-sets… through the following query results we can know that the file grew 696-551 = 145 bytes:

CreationDate

ChangeSetId

FullPath

FileLength

2012-04-30 22:33:10.040

843

$\Research education "agile\SimpleUniConverter\Convert.xaml.cs\

551

2012-05-05 20:22:31.800

861

$\Research education "agile\SimpleUniConverter\Convert.xaml.cs\

696

 

Who changed what?


The change-set table provides an CommitterId column through which we can know who made a given check-in. As this column contains an ID (integer refering tbl_identity.IdentityId), this is not straight forward information. We have to join other tables to obtain something useful.

In fact, the committer user name is stored in the Constants table. So, we have to join the Change-set's CommitterId column to tbl_identity.IdentityId, and then join tbl_identity.TeamFoundationId to Constants.TeamFoundationId… we can then get the user name in the Constants.DisplayPart column (or Constants.String column)

 

 

This can be presented by the following change in our initial view diagram:

 

CreationDate

ChangeSetId

FullPath

FileLength

DisplayPart

2012-04-30 22:33:10.040

843

$\Research education "agile\SimpleUniConverter\Convert.xaml.cs\

551

Taoffi

2012-05-05 20:22:31.800

861

$\Research education "agile\SimpleUniConverter\Convert.xaml.cs\

696

Taoffi

 

Changed bytes tracking generalization


It would, ideally, be beneficial to integrate such information into the TFS analytical database.

We still can do some turnaround by directly querying the database tables.

The idea is to list all change-set files in the order of their respective check-in date. To handle the probability of identical dates / files, we can use a row number to distinguish changes. This may be done like in the following view:


CREATE VIEW [dbo].[xtfs_changeset_files_qry]
AS
SELECT
TOP (2147483647)
    ROW_NUMBER() over( ORDER BY chg_set.CreationDate) as row_number,
    chg_set.CreationDate,

    const.DisplayPart        as user_name,
    chg_set.ChangeSetId,

    v.FullPath,
    f.FileLength

FROM dbo.tbl_ChangeSet (nolock)    AS chg_set INNER JOIN
dbo.tbl_Identity (nolock)    AS login ON chg_set.CommitterId = login.IdentityId INNER JOIN
dbo.tbl_Version (nolock)    AS v ON chg_set.ChangeSetId = v.VersionFrom LEFT OUTER JOIN
dbo.tbl_File (nolock)        AS f ON v.FileId = f.FileId inner join
Constants (nolock)        AS const ON const.TeamFoundationId=login.TeamFoundationId

ORDER
BY chg_set.
CreationDate, v.FullPath


GO

 

We can then refer the view to itself (current version / previous version) obtain row-relative quantitative changes:

SELECT v1.row_number,
       v1.CreationDate,
       v1.user_name,
       v1.FullPath AS v1FilePath,
       v1.FileLength AS v1_length,
       v_next.FileLength AS v2_length,
       v_next.FullPath AS v2FilePath,
       ISNULL(v_next.FileLength, 0)
          - ISNULL(v1.FileLength, 0) AS changed_bytes

FROM dbo.xtfs_changeset_files_qry AS v_next RIGHT OUTER JOIN
     dbo.xtfs_changeset_files_qry AS v1 ON v_next.FullPath = v1.FullPath
        AND v_next.row_number = v1.row_number + 1
        OR v_next.FullPath IS NULL
        AND v_next.row_number IS NULL

 

Meta models… what is it and how can it be useful (for you)

 

I spent a long time during the last months writing about meta-models. That was in the context of a book for Editions ENI (France) about some concepts to which I dedicated some work since quite a while (it was around 1998, as long as I remember, that I started studying the basis of the approach).

 

In previous posts (see, for instance, Silverlight database to DataGrid, yet another approach- Part II) I exposed a generic approach for describing database meta-data. The direct practical purpose of those posts was reading and displaying data into Silverlight DataGrid. Through this exercise, we also saw how can our structures help us in integrating business rules at several level (meta-column, meta-table… and so on).

 

In this same exercise, applying the meta-models approach would consist of persisting these structures into the database… to read and create them dynamically.

 

We would, for instance, store our meta-columns / meta-tables definitions into the database and let our software objects load these structures and transform them into ‘live’ data, integrating all defined business and technical constraints.

 

 

 

What are meta-models?

 

I use the term "meta-model" to designate "a description of a description" stored in a database and interpreted (read and presented) by one or more (specific) software objects (classes).

 

That is: the information stored in the database describes an object or a behavior, and the software class that reads this information is aware of the fundamental semantics of this information and is, thus, able to interpret it independently of a particular context.

 

This means that, according to our view, a meta-model is composed of:

 

§  Database objects and structures to store the model's data;

 

§  Software (kernel) objects that know the fundamental semantics of the database model.

 

 

 

The collaborative aspect between the 'description' stored in the database and the 'runtime object' (which reads and interprets that description) is essential for this approach to be applicable.

 

 

 

Why use meta-models?

 

Let us take a sample application where the software should display a page (or form) composed of a Master / Detail connected regions… i.e. a list of items in a Master grid, with a Detail form that displays the details of the item selected in the Master grid.

 

That 'Master/Detail’ layout may be the software application's approach for presenting the information of several entities. The application developer may choose to create a specific page for each manipulated entity… a choice which may be correct in some contexts, but which also remains questionable by its repetitive approach.

 

 

 

In another, more automated, approach, the software developer may prepare templates for each entity's specific Master/Detail information, and load the required template according to the context's entity.

 

This latest approach would be a first step for a meta-modeling approach for entities' presentation.

 

 

 

In fact, storing these templates as part of software objects would make them dependent of their related objects' compiled state. That is: the template cannot evolve without changing and recompiling related objects' code. Storing template's information in a database (meta-model) would circumvent this inconvenience.

 

In the same time, storing templates' information in a database would also require a (more or less important) change in software object's structure and behavior. Using a database meta-model approach (to describe Master/Detail templates in our case), requires the software object to act in a more abstract way. And this is what we called 'collaborative' approach (between database meta-model and runtime objects).

 

 

 

Finally, in the long run, our software object would constitute a 'kernel object' capable of displaying and handling any entity's Master/Detail information stored in a database meta-model.

 

 

 

What can we do with meta-models?

 

The sample exposed above can of course be extended to more elaborate models and interactions between descriptions stored in a database and their software objects' counterpart.

 

Let us again take another practical sample: an application that may need to normalize the appearance of asp.net data grid controls.

 

We know that a GridView control contains some appearance properties like:

 

§  Width;

 

§  HeaderStyle-CssClass;

 

§  AlternatingRowStyle-BackColor;

 

§  … etc.

 

 

 

We may choose to use the database to store values for each of these properties and let our software objects assign these values to each DataGrid (on the page's load event for instance).

 

Now, to handle the appearance of our GridView controls, we just need to assign new values for these properties in the database. A task which requires less effort, less time and fewer technical skills.

 

 

 

Meta-models and Reflection

 

Reflection is the process of introspection (self-examining) of the internal structures of software objects at runtime. .NET implements Reflection in some classes defined in the System.Reflection namespace.

 

 

 

Our previous exercise can be extended to more abstract and useful applications.

 

In fact, a software object (like GridView in the last sample) has properties to which we can dynamically assign values if we may simply know their name:

 

 

 

 

PropertyInfo property = Type.GetProperties().FirstOrDefault(

                            p => string.Compare( p.Name, my_name) == 0);

 

 

 

 

After locating the property inside the specified object's Type (class) we may assign it a specified value:

 

 

 

 

property.SetValue( … )

 

 

 

 

Our meta-model may make use of Reflection mechanisms to store property names and values in the database, to read and assign the specified values to the specified objects at runtime.

 

 

 

This approach may be of interest in many software applications. Its main advantages are, again:

 

§  More adaptable and versatile software;

 

§  Reducing development and maintenance efforts, time and cost!

 

 

 

Meta-models and method invocation

 

What we explained above about using System.Reflection to discover an object's property by name and assign a value to the retrieved property, is also applicable to methods:

 

 

 

 

MethodInfo method = Type.GetMethods().FirstOrDefault(

                            m => string.Compare( m.Name, my_name) == 0);

 

method.Invoke( … )

 

 

 

 

On the other side, user interface’s command elements like Menus, buttons… are often associated with an event handler that represents the entry point for the action of the command.

 

 

 

To explain how a meta-model can be useful in this area, let us take an example of an update button.

 

An update button collects the user entries into the form fields, checks their integrity and submits them to the server for updating the specified table;

 

Having the meta-data and business rule constraints for the columns of the entity, this update operation can be handled through one same handler. Or be handled by a dynamically selected handler specific to the context.

 

So, we know that a data entry form would have an update button. And we may need to assign a specific method as the handler of the Click event for this button.

 

All these required information elements can be stored into a database meta-model. The meta-model software would then:

 

§  Read (or otherwise find) the name of the method assigned as handler of the button's action event;

 

§  Dynamically locate the method in the software (loading the assembly when required);

 

§  Invoke the method.

 

 

 

Again, this meta-model solution can be applied on different contexts (records submission, web service operations invocation… etc.) and/or on different objects or controls (buttons, menus… etc.).

 

 

 

Interoperability and Integration

 

The database meta-model approach may also be used in a way similar to what we already use in Interop assemblies (which create links between 'unmanaged / native' code end 'managed code').

 

A meta-model-aware assembly method may, for instance, prepare entries to methods that reside in non-meta-model-aware assemblies or methods.

 

Such methods can also be defined in different projects independent of the 'meta-model-kernel' assembly. And may, thus, allow a high level integration of existing applications (or data) into the meta-model solution.

 

 

 

Extensibility

 

As we said above, the meta-model as exposed in this paper, is a collaborative ensemble where the database descriptions are tightly connected to the meta-model kernel software.

 

Two more questions though:

 

§  What about the evolution of the meta-model's own kernel?

 

§  How to describe objects that we don't yet know about?

 

 

 

Here comes another important point which seems to represent a good basis for answering the above questions:

 

§  An object is represented by a set of properties;

 

§  A property is, essentially, a definition composed of

 

·         A Name

 

·         And an Attribute (the Attribute defines property's constraints: mainly 'Data Type');

 

§  A Value can be assigned to a property in the context of either a class (static property) or to an instance of this class. (Note: Value should conform to the attribute's constraints).

 

 

 

So we may consider the data of an object as a collection of (Property / Value)… i.e. a collection of ((Name/attribute)/Value).

 

This seems to be, more or less easily, presented in a meta-model, which may allow us to handle future 'unknown objects' or future unknown 'properties'.

 

 

 

Meta-models self-describing

 

On one hand, meta-models are data stored in a database… and on the other, they are extensible. Managing them can also be described by meta-models. This recursive aspect can be of interest to some applications to allow users to autonomously manage software semantic behaviors without the need of extensive technical knowledge.

 

Sorting dates: Ascending, Descending OR…

This is a small story, yet with something special:

Once upon a time, there was a list of events stored into a database table. Each event had (evidenceJ) a date.

A friend of mine asked me to sort these event items.

I asked, as you may imagine: Ascending or Descending?

 

But this, apparently, seemed too simplistic for him…

- I want the most recent to appear first. He said.

- Well, that means you want to sort in descending order.

I gave him a demo of data sorted in descending order…

- No, he said, that is not what I want!

 

After a short discussion, I understood that what he wanted was:

·         To first have the incoming events (i.e. those located after “today’s” date… sorted in an ascending order)

·         Followed by older dates (sorted in ascending order)

 

I first thought: can ORDER BY do something else than sorting in ascending / descending?...

As the answer was, evidently, No… I said: Sorry, but that is not possible!

The question remained in my mind however. And I started looking for a solution (for myself!)

The experimentations didn’t in fact last long, the solution was quickly found:

 

All what we need is to:

§  Categorize dates according to their location relative to Today (or any other logic);

§  Give each category a ‘sortable’ value (integer for example)

§  Sort by the category’s value, followed by the date value.

 

Example:

SELECT TOP (100) PERCENT

      event_date,

      description,

      CASE WHEN event_date >= getutcdate() THEN 1

            ELSE 2

      END               AS date_category  -- categorize date

FROM         dbo.sight_conferences

ORDER BY date_category, event_date

 

 

Screenshot of the output data sample

 

 

 

The key part of this code is the logic of categorizing dates. In this sample, categorization was quite simple (set a category according to date location relative to Today). But, of course, we can use another logic scheme to obtain data sorted accordingly.

Managing databases periodic membership (or: unique time span indexing) Part II

Ho to view periodic relationship data

In the previous post, I exposed a method for uniquely indexing time spans of periodic-memberships.

 

Here, we will discuss the next task: How to view historical data in such structures?

To illustrate this, we will talk about a portfolio sample data, simply because this is a more elaborate case (than the hotel/room reservation case).

 

Suppose we have:

§  A portfolio named ‘Sample portfolio’

§  A set of companies: company 1, company 2 and company 3

§  A set of daily historical data for each company for the period from 2010/01/01 to 2010/01/10

 

Now suppose that:

§  Company 1 and company 3 are ‘permanent members’ of the portfolio starting at 2010/01/01

§  Company 2 is a member of the portfolio for the following periods:

o   2010/01/01 to 2010/01/05

o   2010/01/08 to à endless

 

To query the daily historical data for company 2 related to its parent portfolio, we should thus obtain the data for the periods where it is effectively a member of the portfolio:

§  2010/01/01, 2010/01/02, 2010/01/03, 2010/01/04, 2010/01/05

§  2010/01/08, 2010/01/09 and 2010/01/10

 

(Note that data for 2010/01/06 and 2010/01/07 should be ignored)

 

This can be done using a view similar to the following (TSQL followed by the view design diagram)

 

SELECT     p.name AS portfolio,

                c.name    AS company,

                history.date,

                history.data

 

FROM dbo.portfolio_companies AS pc INNER JOIN

                      dbo.portfolios AS p ON pc.portfolio_id = p.id INNER JOIN

                      dbo.company_data AS history

                ON pc.date_start <= history.date

                               AND ISNULL(pc.date_end, CONVERT(datetime, '9999/01/01')) >= history.date INNER JOIN

                      dbo.companies AS c ON history.company_id = c.id AND pc.company_id = c.id

 

As said above, company 2 is a portfolio member during the following periods:

§  2010/01/01 to 2010/01/05

§  2010/01/08 to à endless

 

 

 

With our sample history data, this view produces the following data for company 2 (8 entries):

 

 

Note: the history data table contains the following data for company 2 (10 entries):

 

 

Managing databases periodic membership (or: unique time span indexing)

Introduction

The relations between some ‘real world’ objects can sometimes be in the form of ‘periodic membership’. By ‘periodic membership’, I mean the context when one object can be member of another during one or more periods of time.

Examples:

·         A person who occupies a hotel room during a period of time;

·         A company which is member of a portfolio during a period of time;

·         … etc.

 

The context also assumes that we want to keep the membership history and be able to build data aggregations that take into account these periodic memberships. (For example: be able to calculate portfolio performance during a period of time that spans several ‘periodic memberships’).

 

A concrete example may better explain the case:

§  We have a portfolio for which we want to maintain historical performance during time;

§  At 2009/01/01, the portfolio contained : company1, company2 and company3;

§  At 2009/03/01, we changed the portfolio components and excluded company2. (Now the portfolio contains only company1 and company3);

§  At 2009/06/01, to calculate the portfolio performance for the period 2009/01/01 to 2009/06/01, we should take company2 into account for the period 2009/01/01 to 2009/03/01 (although it is no more part of the portfolio).

 

Knowing that database engines do not offer a ‘time-overlap’ unique indexing features, resolving this situation needs a structure that allows us to assign and manage periodic membership indexes.

Note: I am surely not the first to confront this context, but I wanted to share my experience to resolve this problem and will be glad to receive any thoughts about it.

 

Periodic membership multiplicity models

In my experience, I encountered two possible types of multiplicities related to periodic relationship:

§  One-to-one periodic membership: a good example for this would be a hotel room that can be occupied only by one person during the same period (i.e. period is unique per room);

§  One-to-many periodic membership: a good example for this would be a portfolio which can have more than one member for the same period (i.e. period is unique per portfolio-member).

In this article, I will explain how to handle both of these types.

 

Hotel room reservation, the basic structure

To demonstrate a hotel reservation periodic membership (uniqueness of room/reservation period), we can create the following table and relationships:

 

§  Hotel clients: contains client list items

§  Hotel rooms: contains room list

§  Hotel room reservations: contains client/room reservation periods

 

Note: the date_end field of the reservations table can be null, to allow ‘permanent’ (or endless) reservations.

 

To ensure uniqueness of reservation periods, we will create a scalar function (returning a Boolean value) that can be used as Check Constraint on the hotel_room_reservations.

 

CREATE FUNCTION [dbo].[is_valid_hotel_reservation_period]

(

       @item_id     int,

       @room_id     int,

       @date_start  datetime,

       @date_end    datetime

)

RETURNS bit

AS

BEGIN

       -- validate the provided parameters    

       if @date_start is null or @room_id is null

             return 0

 

       -- be sure start date < end date 

       if not @date_end is null

       begin

             if @date_start >= @date_end

                    return 0

       end

      

       -- is this the first reservation period for this room?

       if ( select count(*)

                    from dbo.hotel_room_reservations

                    where( room_id = @room_id) ) <= 0

             return 1

      

       -- check overlapped periods

       if @date_end is null              -- is this a permanent (endless) reservattion?

       begin

             if ( select  count(*)

                    from dbo.hotel_room_reservations

                    where( ( room_id = @room_id)

                                  and( id != @item_id)

                                  and(( date_end is null) or (date_end >= @date_start))

                           )) > 0

                    return 0

       end

       else

       begin

             if ( select  count(*)

                    from dbo.hotel_room_reservations

                    where(       (room_id = @room_id)

                                  and( id != @item_id)

                                  and( date_start <= @date_end)

                                  and(( date_end is null) or (date_end >= @date_start))

                           )) > 0

                    return 0

       end

      

 

       -- return OK

       return 1

END

 

We can now use this function as check constraint on the reservation table:

 

 

Overlapping entries in the reservation table would now display an error message like:

The Portfolio/company periodic membership case

In portfolio/company case, membership should allow multiple membership instances for different periods. That is: the same company can be member of the same portfolio several times as long as the membership periods do NOT overlap.

 

Our tables and relationships may look like the following:

 

All what we need is to create a new version of the above function, and use it as a check constraint condition for the portfolio companies table:

 

CREATE FUNCTION [dbo].[is_valid_portfolio_company_period]

(

       @item_id     int,

       @portfolio_id int,

       @company_id  int,

       @date_start  datetime,

       @date_end    datetime

)

RETURNS bit

AS

BEGIN

       -- validate the provided parameters    

       if @company_id is null or @date_start is null or @portfolio_id is null

             return 0

 

       -- be sure start date < end date 

       if not @date_end is null

       begin

             if @date_start >= @date_end

                    return 0

       end

      

       -- is this the first membership period for the portfolio/company?

       if ( select count(*)

                    from dbo.portfolio_companies

                    where( portfolio_id = @portfolio_id)

                           and( company_id = @company_id) ) <= 0

             return 1

      

       -- check overlapped periods

       if @date_end is null              -- is this a permanent (endless) membership?

       begin

             if ( select  count(*)

                    from dbo.portfolio_companies

                    where( company_id = @company_id

                           and( portfolio_id = @portfolio_id)

                           and( id != @item_id)

                           and(( date_end is null) or (date_end >= @date_start))

                           )) > 0

                    return 0

       end

       else

       begin

             if ( select  count(*)

                    from dbo.portfolio_companies

                    where( company_id = @company_id

                           and( portfolio_id = @portfolio_id)

                           and( id != @item_id)

                           and( date_start <= @date_end)

                           and(( date_end is null) or (date_end >= @date_start))

                           )) > 0

                    return 0

       end

      

       return 1

END

 

We can, now, use the function as a check constraint condition for the membership table:

 

 

Possible extensions

It seems that, with some more efforts, this can evolve to a generic Time-Span unique indexer.

Waiting to hear some thoughts about thisJ!

Silverlight database to DataGrid, yet another approach- Part IV

Filtering database data

As we have seen in Part III, the SQL Select statement is composed of the following parts:

SELECT                 [field1], [field2],… [fieldn]

FROM                   [database table or ‘table-like’ store]

WHERE                [conditions]

ORDER BY           [field name] <sort direction>,

                               [field name] <sort direction>

 

It is the ‘WHERE’ clause of the SQL statement that is used to filter the data.

‘WHERE’ is followed by ‘conditions’. Several conditions are concatenated using AND or OR operator.

What is a filter condition?

A ‘condition item’ can be presented as:

[Field name] [Comparison operator] [Filter value]

 

A collection of ‘condition items’ can be presented as something like:

<Condition item> <Concatenation operator> <Other condition item>

 

Examples:

Name = ‘John’

Amount >= 100

(Last_name LIKE ‘%bama’) AND (Age >= 20)

 

That seems easy to be represented by an object:

public class SilverFiltertItem

{

       string              m_field_name,

                           m_filter_value;

       CompareOperator     m_compare_operator  = CompareOperator.EQUAL;

       ConcatOperator      m_concat_operator   = ConcatOperator.AND;

       ...

       ...

The concatenation and comparison operators are members of the following enum types:

public enum filter_concat_operator

{

       and,

       or,

       none

};

 

public enum filter_compare_operator

{

       Equal,

       Not_equal,

       Like,

 

       GreatertThan,

       GreatertThanOrEqual,

       LessThan,

       LessThanOrEqual

       ...

       ...

};

 

Two properties can give us the Compare and Concatenate strings when required:

protected string ConcatString

{

       get

       {

             if( m_concat_opertaor == filter_concat_operator.none)

                    return "";

 

             // return ‘AND’ or ‘OR’

             return Enum.GetName( typeof( filter_concat_operator), m_concat_opertaor).ToUpper();

       }

}

 

protected string CompareString

{

       get

       {

             switch (m_compare_operator)

             {

                    case filter_compare_operator.Equal:

                           return "=";

 

                    case filter_compare_operator.Like:

                           return " Like ";

 

                    case filter_compare_operator.Not_equal:

                           return "!=";

 

                    case filter_compare_operator.GreatertThan:

                           return ">";

 

                    case filter_compare_operator.GreatertThanOrEqual:

                           return ">=";

 

                    case filter_compare_operator.LessThan:

                           return "<";

 

                    case filter_compare_operator.LessThanOrEqual:

                           return "<=";

 

                    default:

                           throw new Exception("Unknown Compare opertor encoutered!");

             }

       }

}

 

The FilterItem object can now provide us with its SQL string through a property like the following:

public string SqlFilterString

{

       get

       {

             if( string.IsNullOrEmpty(m_filter_value)

                           || string.IsNullOrEmpty( m_target_column_name))

                    return "";

 

             string       field_name   = "[" + m_target_column_name + "]",

                          str_ret      = ConcatString + "(";

 

             string str_value    = m_filter_value;

            

             str_ret      += field_name + " " + CompareString + " " + str_value + ") ";

             return str_ret;

       }

 

A filter collection class may look like this:

public class SilverFilter : List<SilverFilterItem>

{

       ...

       ...

 

The filter collection can return the entire SQL filter string through successive calls to its member items:

public string SqlFilterString

{

       get

       {

             if( Count <= 0)

                    return "";

 

             int    ndx          = 0;

             string str_ret      = "";

 

             foreach (SilverFilterItem f in this)

             {

                    if (ndx <= 0)

                           f.Concat_operator = filter_concat_operator.none;

                    else

                    {

                           if( f.Concat_operator == filter_concat_operator.none)

                                  f.Concat_operator   = filter_concat_operator.and;

                    }

 

                    str_ret      += f.SqlFilterString;

                    ndx++;

             }

 

             return str_ret;

       }

 

 

Our DataTable object (see previous posts) can now include a Filter collection object and construct the SQL Where clause:

 

protected SilverFilter    m_filter     = new SilverFilter();

...

...

 

public string SqlCommand

{

       get

       {

             ...

             ...

             string       str_cmd      = "SELECT * FROM " + m_table_name;

             string       str_sort     = m_sort.StrSql;

             string       str_filter   = m_filter.SqlFilterString;

 

             if( ! string.IsNullOrEmpty( str_filter))

                    str_cmd      += " WHERE(" + str_filter + ")";

 

             if( ! string.IsNullOrEmpty( str_sort))

                    str_cmd      += " ORDER BY " + str_sort;

 

             return str_cmd;

       }

 

Note: a better approach would, of course, be to map the filter field names to the table’s meta-data table fields… or directly use MetaDataTable’s columns as filter members (instead of using field names as strings). In this last case, we would be able to check columns’ data types and also act more closely to database’s business logic.

 

In other words: what is proposed here is a simple approach that can evolve according to your needs.

 

 

You can download the sample code: SilverDbDataGrid-2-sorting&filtering.zip (1.87 mb)

Silverlight database to DataGrid, yet another approach- Part III

Sorting and filtering database records

After having succeeded to display our server data into Silverlight DataGrid (see previous post), we will now continue the adventure to complete our solution with some useful and necessary features that Silverlight DataGrid originally proposes (for example, sorting records) and add some new features for filtering records.

 

Silverlight DataGrid allows the user to sort displayed rows by clicking into column headers.

That is nice and helpful. But, in the context of data provided by a server through a database, that doesn’t seem to be quite a correct approach.

In fact, in such context, if you click to sort a column in the ascending order, the displayed data records may not contain the entire values stored in the database. The data should be ‘refreshed’ to represent the effective sorted values according to currently stored database data in its entirety.

 

Sorting database data

How database data can be sorted?

The answer: this can be done using the SQL ‘ORDER BY’ clause.

We use SQL to query the data according to the following (simplified) statement template:

SELECT                [field1], [field2],… [fieldn]

FROM                  [database table or ‘table-like’ store]

WHERE                [conditions]

ORDER BY           [field name] <sort direction>,

                            [field name] <sort direction>

 

The ORDER BY part, contains a list of field names (each of which should be one of the listed SELECT fields, or, at least, one of the queried table or ‘table-like’ fields)

Each field in the ORDER BY clause can have a specified sort direction (ASC for ascending, or DESC for descending). The default being the ascending direction (if the direction is omitted)

 

If we have to represent an ‘order by’ item as an object, the object may then be something like this:

public class SortItem

{

       string       m_field_name;

       SortDirection m_direction  = SortDirection.ASC;

       ...

       ...

 

So, in our case, we can simply sort our DataGrid by building a list (List<SortItem>) of clicked column names (met-data column names) and passing them to the server to compose the ORDER BY part of the query accordingly and return too us the desired sorted data.

 

Implementing the solution

Let’s begin, at the server side, by enriching our service’s SilverDataTable (see Part II) object by a new SortList object, which will simply be a List<> of SortItem.

 

public enum sort_direction

{

       asc,

       desc,

       none

};

 

A SortItem object can be defined as follows:

 

[DataContract]

public class SilverSortItem

{

       protected string           m_target_column_name       = "";

       protected sort_direction   m_sort_direction    = sort_direction.asc;

       ...

       ...

 

We can now define a sort-list object which is mainly a List<SilverSortItem > that takes care for some constraints like, for example, not adding duplicate fields:

 

public class SilverSort : List<SilverSortItem>

{

 

The class exposes an indexer that returns the field by its name:

public SilverSortItem this[string field_name]

{

       get

       {

             if( string.IsNullOrEmpty( field_name) || Count <= 0)

                    return null;

 

             foreach (SilverSortItem e in this)

             {

                    if( string.Compare( e.Target_column_name, field_name, true) == 0)

                           return e;

             }

             return null;

       }

 

An Add method, to add or modify settings of an existing sort field:

public new void Add(SilverSortItem element)

{

       if( element == null || string.IsNullOrEmpty( element.Target_column_name))

             return;

 

       SilverSortItem      e      = this[element.Target_column_name];

 

       if( e == null)

             base.Add( element);

       else

             e.CopyOther( element);

}

 

And a property that returns the SQL sort string of the contained fields:

public string StrSql

{

       get

       {

             if (Count <= 0)

                    return "";

 

             string str_ret = "";

 

             foreach (SilverSortItem s in this)

             {

                    if( str_ret.Length > 0)

                           str_ret      += ", ";

 

                    str_ret += s.StrSql;

             }

             return str_ret;

       }

}

 

Let’s now include a Sort list into our SilverDataTable class:

[DataContract]

public class SilverDataTable

{

       protected SilverSort       m_sort = new SilverSort();

 

 

We can now extend our SqlCommand property to include the sort string:

string str_sort     = m_sort.StrSql;

 

if( string.IsNullOrEmpty( str_sort))

       return "SELECT * FROM " + m_table_name;

return "SELECT * FROM " + m_table_name + " ORDER BY " + str_sort;

 

We will also change our WCF service method to include an optional list of sort field names:

 

[OperationContract]

public SilverDataTable GetData(string str_connet_string,

                           string str_sql_command,

                           SilverSort sort_items,

                           int n_records)

 

 

Sorting… the Client-side job

At the client-side, our DataGrid should send us a message each time a column should be added to the SilverDataTable sort list. And, unfortunately, that is not as ‘intuitive’ as we may like!

Manish Dalal published a very interesting paper about custom sorting and paging inside a DataGrid. His work presents a good (and extensible) start point.

The usable part of his work in our project is the CollectionView class which can give us more control on sorting in response to DataGrid Column Click events.

As you may see at Bea Stollnitz interesting blog, Silverlight DataGrid internally uses a CollectionViewSource (an object that implements ICollectionView interface).

That is: When we set DataGrid’s ItemsSource to an observable collection, it integrates it into its own ICollectionView object.

Fortunately, the DataGrid is smart enough to let us implement this object ourselves, i.e. if we set its ItemsSource to an object that implements the ICollectionView, the DataGrid will use this instead of its own one.

So, let’s create a class that implements the ICollectionView:

public class SilverCollectionView<T> : ObservableCollection<T>, ICollectionView

{

 

The ICollectionView exposes a collection of ‘SortDescriptions’ that we will use, for the requirements of our project, as a list of field names to be sorted. i.e. each time a column will be clicked, we will add the column name to the SortDescriptions list and generate a Refresh event to request the server’s data sorted as desired.

Here is the SilverDatasetView helper class that will integrate our SilverDataTable as an ICollectionView:

public class SilverDatasetView : SilverCollectionView<SilverDataRow>

{

       public SilverDatasetView() : base()

       {

       }

 

       public SilverDatasetView(SilverDataTable service_data_table) : base()

       {

             CopyDataset( service_data_table);

       }

 

       public bool CopyDataset(SilverDataTable data_table)

       {

             if( data_table == null || data_table.Rows == null)

                    return false;

 

             base.SourceCollection      = data_table.Rows;

             return true;

       }

 

Once we receive the data from our database server, we can now set the DataGrid’s ItemsSource to a SilverDatasetView object:

SilverDatasetView   dataset_view = new SilverDatasetView(m_table.Rows);

data_grid.ItemsSource                   = dataset_view;

 

To tell the DataGridColumn how to send us desired sort items, we will set its SortMemberPath to the data column name:

Grid_col.CanUserSort              = true;

Grid_col.SortMemberPath    = data_col.Name;

 

The last part is to respond to the Refresh event of our SilverDatasetView in order to collect the desired sort field names and request the data accordingly:

dataset_view.OnRefresh     += new EventHandler<RefreshEventArgs>(m_dataset_view_OnRefresh);

 

void m_dataset_view_OnRefresh(object sender, RefreshEventArgs e)

{

       if( m_sort_fields == null)

             m_sort_fields = new ObservableCollection<SilverSortItem>();

 

       m_sort_fields.Clear();

 

       SilverSortItem      sort_element;

 

       foreach (SortDescription s in e.SortDescriptions)

       {

             sort_element = new SilverSortItem();

             sort_element.Target_column_name   = s.PropertyName;

             sort_element.Sort_direction             =

                           (s.Direction == ListSortDirection.Ascending)

                           ?(sort_direction.asc)

                           :(sort_direction.desc);

 

             m_sort_fields.Add( sort_element);

       }

      

       RequestSilverDataset();

}

 

RequestSilverDataset() method will, mainly, do the following:

DataServiceClient   cli    = service_helpers.DataServiceProxy(5);

 

cli.GetDataCompleted += new EventHandler<GetDataCompletedEventArgs>(cli_GetDataCompleted);

cli.GetDataAsync( str_connet_string, str_sql_command, m_sort_fields, n_records);

 

That is it… your DataGrid can now be sorted according to the real data on the server.

Don’t forget: Click the column to sort/ shift + click to add the column to the sorted list…

 

Download the sample code:

SilverDbDataGrid-2-sorting.zip (1.36 mb)

Silverlight database to DataGrid, yet another approach- Part I

Introduction

As I exposed in an earlier post, Database structure can be declined into the following hierarchy

§ A table

§ Containing rows

§ Containing data //(arranged into columns)

 

This schema cannot live without the following meta-data structure

§ Table definition

§ Column definition:

§ Data type / data size (storage space reservation maximum size)

 

One important thing to observe is the relation between the meta-data definitions and the data storage or presentation:

 

 

 

.

All these structures and concepts have been used for years through several database access technologies (ado.net for example) using some well known objects like Dataset, DataTable… etc. and everybody was easily able to read and display data into grid controls in Windows and Web forms.

 

Now this question is once again exposed with Silverlight: How to read and display data into a DataGrid!

The reason for this is, mainly, that Silverlight is a “multiplatform browser plug-in”, and, as such, don’t know about database facility objects like Datasets, DataTables…

Also, Silverlight being a “client”, it cannot directly have access to what is stored or otherwise going-on on the server-side end. Accessing such information is done by calling Xml web services hosted at the server.

 

Interesting solutions

Some interesting work has been done to try to solve this dilemma. Some of the solutions proposed a (very interesting) workaround which requests the server’s xml dataset, and entirely creates database objects at runtime (MSIL!) on the Silverlight client side.

Although this seems quite fascinating, it is still a sort of ‘black magic’ solution, and lets me uncomfortable to use it as a sustainable solution. It also misses some of the interesting features of Silverlight, like Binding Converters.

 

Starting at the bottom line

One may think: A Dataset already has all what we need: Meta data definitions, Data rows… etc.

Let’s create a Dataset on the server-side and then write it down to the client through a web service. The client can then consume the received dataset by incorporating it into the required classes.

 

I tried this solution, but ended up by finding myself in front of a great deal of ‘raw’ data that should be reformatted on the client… And, worse, this data reformatting altered some important business logic that should be decided on the server-side.

For example, in a sales representative client application, if a ‘customer’ field value inside an ‘order’ should be only one of the current user’s authorized customers; this would obviously be better decided on the server not the client.

Even the fact of presenting the ‘customer’ field as selection option (combo box) or as a plain text field is better to be decided at the server end.

 

The ‘Dataset’ is a great and useful object, but it only provides ‘raw’ relational data. It needs to be complemented by other features in order for the business logic (at the server-side) to take control over data presentation and manipulation process (at the client-side).

 

 

The diagram below illustrates the basic schema used by the proposed solution to represent the data stored in a database.

 

The diagram’s classes represent the 2 levels of a database table:

§  At the meta-data level : SilverMetatTable, composed of one or more SilverMetaColumn(s);

§  The data storage level is represented by SilverDataTable, which has a meta-data table definition (SilverMetaTable). The data table contains zero or more rows (SilverDataRow), each of which containing data cells (SilverDataCell). Each data cell is linked to the corresponding meta-data column.

 

The meta-data column represents the basic properties of a database column (data type, default value, is/is not nullable… etc.), but can now expose all the desired presentation features and functionalities as required by the business logic.

It may, for example, expose the desired presentation type (plain text / option list… etc.), for an option list: the choice-elements (or where to request them), the data access options (read-only / read-write…) according to session context or application context… etc.

 

Surprisingly coding the required classes for this part of the solution was a matter of a few hundreds of lines of code (approx 4 hours + 2 for repairing some of my ‘chronic’ errors… I do many!)

 

In the next post, I will talk about ‘The client side’ job… and deliver a fully-functional sample application.

See part II

 

Revisiting the Database design foundations

Software is about defining objects structures, relationships and behavioral contour. This is probably a too short sentence to define what software is. But it can be a good commencement for what I would like to expose here.

Years passed for the software industry to mature and be able to express the complexity of this task. And, it seems that there still will be some more years to come for this maturity to gain some sort of stability and normalization.

Remember the lessons about the ‘Date object’ which would never display a ‘February 30’ day. Although that seems so far away, we still encounter things of the same kind (remember, for example, the ‘page 1 of 0’ syndrome!). Those are simple indications about how complex it can be to mimic the ‘real world’ system, its large span of objects, relationships and behaviors.

 

One of the early, and interesting, questions that the Software had to solve was: data storage.

Historically speaking, we can summarize the maturity of the provided solution into three stages:

§ The folder/file structure

§ The data table

§ The ‘relational’ database

 

The ‘data table’ solution seems particularly interesting:

If you have a look at a data table (even within any of the most elaborate of today’s database engines) you can easily state how the simplicity of the structure looks embarrassing:

§ A meta-data space which describes the table’s columns.

§ And, a storage space where the columns’ data is stored into rows.

 

 

 

 

Basically, the column definition describes its data type and storage size. This allows the storage system to organize the data rows according to theses definitions.

 

Of course, with the time going, so many other attributes and artifacts had been added to the data table solution. Column definitions, for example, got more elaborate (ex: default value, auto-increment, indexes, data validation constraints… etc.), Columns’ relationships (foreign keys…), Operations like row insertions, row deletions or row updates can be triggered by procedures that execute particular ‘business’ logic… etc. But the foundations of the solution remain strictly the same as the early reflection and design…

One of the reasons of this design success seems to be the readability of the solution.

 

Anyway, the fact is, today, nearly no one single software solution can live without a database! And that itself is a profound demonstration of the efficiency of the initial design.

 

The Software ‘Code / Database’ schism!

Strangely, databases still live almost apart from the software development cycle. When you talk software, you seem to be supposed talking about classes, inheritance, behaviors, user interface… etc. and databases almost seem so simplistic compared to this ‘alive’ jungle!

The reasons for this schism seem to be cultural and social (within the development community) more than being a verdict against the database solution efficiency.

 

Industrially speaking, this schism seems often to be one of the most common explanations of software projects failures. It is also, probably, one of the profound reasons for some of the software industry’s inefficiencies.

 

What I think is that databases efficiency can be beneficially used for leveraging software objects properties and behaviors. And that database organization and readability bring interesting applications and solutions for controlling objects behaviors in order to create more versatile and scalable software solutions.

At a very simple level, think about application configuration files (which are great solutions but can sometimes hardly be altered without producing inconsistencies).

Think about a truly structured configuration file, one that guaranties no typing errors, no inconsistencies according to application’s specific logic.

This can be, relatively easily, achieved using a Database!

 

Database-stored menus… extending and redirecting actions

Let’s go a step forward and use the database to store, for instance, application menus.

A menu is basically a displayed text that, when clicked, invokes an application’s functionality.

By storing menus in the database, we may be wishing to expose only some of our application’s functionalities (according to specific application version for example). We may also be wishing to let our customer’s IT team to expose different functionalities to user groups according to a predefined business role hierarchy.

We can also use this structure to localize our application menus without having to recompile specific localized versions.

Another advantage: to be able to add a new menu after a new functionality is added to the software, or remove one that no more exist etc.

 

Menus are good examples for the ‘action’ concept. In fact, clicking a menu, a button or other button-like item in the user interface, results in invoking an action in the software. The action to be invoked has an entry point (a function) which is, normally, part of the handled object’s code.

 

Thanks to .NET System.Reflection (or any Reflection-like library) we can retrieve a method by its name. This can allow us to store object’s method names in the database, and, in the action properties, tell the software which method to be called. The software can then search the defined method, and, if found, invokes it when the action is activated.

 

Of course there are some other considerations to be taken into account: invoked function parameters, return value... etc. But the main concept remains correct and feasible.

 

Database-based solutions advantages?

Well, just to name a few:

§  Runtime object properties modification: Imagine a (dynamic) ‘web page section’ object which is initially created as 600/800 pixels dimensions. A database-based solution can allow the user to easily change these properties to obtain, say, a 1027/800 pixels object on runtime… without having to recompile the application’s code;

§  Runtime object properties extension: Software (compiled) objects’ structures NEVER satisfy at 100% real world structures or requirements (at least during a reasonable period of time). With a database-based solution, using, for example, an EAV (Entity Attribute Value) implementation, you can envisage extending compiled software objects… without having to recompile the application’s code;

§  Action processing redirection: Imagine a software functionality that may require authentication only in some contexts and none in other contexts, depending on the customer’s environment. Thanks to database readability and simplicity, the customer can easily choose which entry point to assign to the action according to his or her environment (without having to recompile the software code)

§ 

§