Wednesday, March 18, 2015

Creating a constraint to ensure intersection table only joins certain records

I have a database with the following tables

I need to ensure that users only becomes friends with other users of the same department.

Approach 1 – DepartmentId on Friend

On http://stackoverflow.com/a/20443626/263003, diarmuid suggested to add the DepartmentId on the Friend table and use the foreign key constraints of (MyselfId, AppId) and (FriendId, AppId). However, I would have to set the primary key of the User table as (Id, DepartmentId) – this creates a super key and I want to avoid that.

Approach 2 – CHECK CONSTRAINT on VIEW

The simplest approach I could think of is to create a VIEW joining User to Friend to User, and add a CHECK CONSTRAINT on the view. However, CHECK CONSTRAINT only applies on TABLE and not VIEW.

Approach 3 – CROSS JOIN and UNIQUE INDEX on VIEW

On http://stackoverflow.com/a/9751172/263003, Martin Smith uses the approach of selecting the records we do not want, creating duplicate records of those using CROSS JOIN, and ensuring those records cannot exists by using a UNIQUE INDEX. Let’s start by creating the INDEXED VIEW

CREATE VIEW [dbo].[CheckFriendView]
WITH SCHEMABINDING
AS
SELECT myself.Id AS MyselfId, myFriend.Id AS MyFriendId, myself.DepartmentId AS MyDepartmentId, myFriend.DepartmentId AS FriendDepartmentId
FROM  dbo.[User] AS myself
INNER JOIN  dbo.Friend
ON dbo.Friend.MyselfId = myself.Id
INNER JOIN dbo.[User] AS myFriend
ON dbo.Friend.FriendId = myFriend.Id
WHERE myself.DepartmentId <> myFriend.DepartmentId

GO

CREATE UNIQUE CLUSTERED INDEX IDX_CheckFriendView
   ON [dbo].[CheckFriendView] (MyselfId, MyFriendId)

GO

However, I got the error

Cannot create index on view "Demo.dbo.CheckFriendView". The view contains a self join on "Demo.dbo.User".

Let’s avoid self join by moving the join to a WHERE clause

CREATE VIEW [dbo].[CheckFriendView]
WITH SCHEMABINDING
AS
SELECT MyselfId, FriendId
FROM dbo.Friend AS f
WHERE EXISTS (
    SELECT 1
    FROM  dbo.[User] AS myself
    INNER JOIN dbo.Friend
    ON dbo.Friend.MyselfId = myself.Id
    INNER JOIN dbo.[User] AS myFriend
    ON dbo.Friend.FriendId = myFriend.Id
    WHERE myself.Id = f.MyselfId AND
          myFriend.Id = f.FriendId AND
          myself.DepartmentId <> myFriend.DepartmentId
)

GO

CREATE UNIQUE CLUSTERED INDEX IDX_CheckFriendView
   ON [dbo].[CheckFriendView] (MyselfId, FriendId)

GO

Another error comes up

Cannot create index on view "Demo.dbo.CheckFriendView" because it contains one or more subqueries. Consider changing the view to use only joins instead of subqueries. Alternatively, consider not indexing this view.

Looks like SQL Server will never let us join.

Approach 4 – Turning MyselfId and FriendId into a single column

Jonathan avoided the self join by combining the MyselfId and FriendId columns with the help of a numbers table containing 2 entries, then joining into the User table – http://jmkehayias.blogspot.sg/2008/12/creating-indexed-view-with-self-join.html

Let’s start by combining MyselfId and FriendId

CREATE VIEW [dbo].[CheckFriendOneColumnView]
WITH SCHEMABINDING
AS
SELECT MyselfId, CASE WHEN n.Number = 1 THEN MyselfId ELSE FriendId END AS MyselfOrFriendId
FROM dbo.Friend AS f
CROSS JOIN dbo.[Number] AS n

And then add the joining of User

INNER JOIN dbo.[User] AS u
ON f.UserId = u.Id OR f.FriendId = u.Id

Oh wait a minute, this is getting unnecessarily complicated. Could we just use

INNER JOIN dbo.[User] AS u
ON f.UserId = u.Id OR f.FriendId = u.Id

without the cross join?

Finally

We do an INNER JOIN with OR condition. Each row in Friend will become 2 rows, one with the DepartmentId for myself, the other with the DepartmentId for my friend.

Then aggregate the (MyselfId, DepartmentId). Since all the friends should belong to the same department, this aggregation should leave MyselfId as unique.

Lastly add the UNIQUE INDEX on MyselfId.

CREATE VIEW [dbo].[CheckFriendView]
WITH SCHEMABINDING
AS
SELECT f.MyselfId, u.DepartmentId, COUNT_BIG(*) AS BigCount
FROM dbo.Friend AS f
INNER JOIN dbo.[User] AS u
ON f.MyselfId = u.Id OR f.FriendId = u.Id
GROUP BY f.MyselfId, u.DepartmentId

GO

CREATE UNIQUE CLUSTERED INDEX IDX_CheckFriendView
    ON [dbo].[CheckFriendView] (MyselfId)

GO

Note: I had to add COUNT_BIG(*) AS BigCount due to another error

Cannot create index on view 'CheckFriendView' because its select list does not include  a proper use of COUNT_BIG. Consider adding COUNT_BIG(*) to select list.

which basically says I must add it!

Update:

Warning: while the INNER JOIN ON a OR b works, it results in very inefficient query (no JOIN predicate) and slows down every changes in the database!

Saturday, February 28, 2015

React JS in ASP.NET MVC part 2—setting up React

Set up the project and libraries for React

Let’s start by creating a new ASP.NET MVC project

Next, we will use NuGet to download the React libraries that we need

Right-click on References and click Manage NuGet Packages

Search for react.js

Install these libraries:

  • react.js
    This contains the JavaScript for the browser
  • ReactJS.NET (MVC 4 and 5)
    This contains the libraries necessary for server-side rendering
  • ReactJS.NET – JSX for ASP.NET Web Optimization Framework
    This contains the library for transforming JSX into plain JavaScript, and then minified and bundled to speed up download
  • ReactJS.NET – ClearScript V8
    V8 is a faster JavaScript engine which we will use to run our React scripts server-side. This is much better than the Jint engine bundled with ReactJS.NET (MVC 4 and 5)

Next, we prepare the minification and bundling for the React JavaScript library.

Include the React JavaScript in our App_Start\BundleConfig.cs

// React JS
// Should not use the official React CDN at //fb.me/, as it redirects to HTTP and breaks HTTPS sites.
bundles.Add(new ScriptBundle("~/js/reactjs", "//cdnjs.cloudflare.com/ajax/libs/react/0.12.2/react-with-addons.min.js")
{
     CdnFallbackExpression = "window.React" }.Include(
     "~/Scripts/react/react-with-addons-{version}.min.js" ));

Include the ~/js/reactjs script bundle in Views\Shared\_Layout.cshtml

<head>
     <meta charset="utf-8" />
     <meta name="viewport" content="width=device-width, initial-scale=1.0">
     <title>@ViewBag.Title - My ASP.NET Application</title>
     @Styles.Render("~/Content/css")
     @Scripts.Render("~/bundles/modernizr")
     @Scripts.Render("~/js/reactjs") </head>

Next, we speed up server-side rendering by disabling global members in V8

Replace the jsEngineSwitcher sectionGroup with the following

    <sectionGroup name="jsEngineSwitcher">
      <section name="core" type="JavaScriptEngineSwitcher.Core.Configuration.CoreConfiguration, JavaScriptEngineSwitcher.Core" />
      <section name="msie" type="JavaScriptEngineSwitcher.Msie.Configuration.MsieConfiguration, JavaScriptEngineSwitcher.Msie" />
      <section name="jint" type="JavaScriptEngineSwitcher.Jint.Configuration.JintConfiguration, JavaScriptEngineSwitcher.Jint" />
      <section name="v8" type="JavaScriptEngineSwitcher.V8.Configuration.V8Configuration, JavaScriptEngineSwitcher.V8" />
    </sectionGroup>

Replace the jsEngineSwitcher section in Web.config with the following

  <jsEngineSwitcher xmlns="http://tempuri.org/JavaScriptEngineSwitcher.Configuration.xsd">
    <core>
      <engines>
        <add name="V8JsEngine" type="JavaScriptEngineSwitcher.V8.V8JsEngine, JavaScriptEngineSwitcher.V8" />
        <add name="MsieJsEngine" type="JavaScriptEngineSwitcher.Msie.MsieJsEngine, JavaScriptEngineSwitcher.Msie" />
        <add name="JintJsEngine" type="JavaScriptEngineSwitcher.Jint.JintJsEngine, JavaScriptEngineSwitcher.Jint" />
      </engines>
    </core>
    <v8 enableDebugging="false" debugPort="9222" disableGlobalMembers="true" maxNewSpaceSize="0" maxOldSpaceSize="0" maxExecutableSize="0" />
  </jsEngineSwitcher>

We are ready to start writing our first Hello World in React!

Create the folder Scripts/Home and add a JavaScript File named Index.jsx and paste the following

var HomeIndex = React.createClass({
    render: function() {
        return (<div>Hello world!</div>);
} });

We will also bundle this JSX file in App_Start\BundleConfig.cs

bundles.Add(new JsxBundle("~/js/home/index").Include(
     "~/Scripts/Home/Index.jsx" ));

Next, we need to include the JSX for server-side rendering, by adding the following in App_Start\ReactConfig.cs

// If you want to use server-side rendering of React components, 
// add all the necessary JavaScript files here. This includes 
// your components as well as all of their dependencies.
// See http://reactjs.net/ for more information. Example:
ReactSiteConfiguration.Configuration
     .AddScript("~/Scripts/Home/Index.jsx");

Replace the contents in Views\Home\Index.cshtml with the following

@{
     ViewBag.Title = "Home Page"; } @Html.React("HomeIndex", new { }, containerId: "homeIndex") @Scripts.Render("~/js/home/index") @Html.ReactInitJavaScript()

Run our application and we have our Hello World!

Next up—making our grid view

Thursday, February 26, 2015

React JS in ASP.NET MVC part 1—why Isomorphic JavaScript?

The conflict of rich web application and search engine optimization

Be it Gmail, Facebook or Github, we have come to expect interactivity without having to wait for a full page reload. Specifically, these web applications are single-page applications (SPA), where new content arising from interactions are sent and retrieved via AJAX.
There are many frameworks to make developing SPA easier. AngularJS, EmberJS and KnockoutJS comes to mind. However, these traditional frameworks only address one part of the equation—the browser side. The server side spits out the HTML template and data separately, causing search engines not to be able to index the page properly, for the HTML template contains no information worthy of mention in a search query. There is thus the need for additional effort to develop a classic version of the SPA in order to optimize for search engine.
Can we cut the additional effort and run our browser side SPA code on the server side too? That would generate the classic version straight away!

React comes to the rescue

React does exactly that!
React, Meteor and several other Isomorphic JavaScript frameworks arise to make it easy to have rich single-page applications, while generating SEO friendly pages. What’s more, the initial blank or placeholder page that appears before data is loaded is gone, since the template with data is already rendered at the server side.
With some additional care to place HTML forms and submit buttons and intercepting them to do AJAX, the rich web application can still function even with JavaScript turn off. Talk about progressive enhancement and graceful degradation!

In part 2, set up the React libraries

Saturday, February 07, 2015

Get an extra 5GB of storage for Copy when you use this link!

My 25 GB of Dropbox space earned from Space Race is expiring in a month! I'll be left with a meagre 2.5 GB only. ::sob::

Luckily, there's a Dropbox competitor, Copy, offering 15 GB of base storage. What's more, if you use the referral link below, both you and I will get an additional 5GB! That's a lot, especially when Dropbox only gives 10% of that per referral.

Get your 20GB NOW
https://copy.com?r=QPkByG