Monday, November 21, 2016

Rethinkdb replace multiple fields with object and array as nested field type

I came across a requirement where I need to replace multiple fields in a document. In my case the document has two fields, one as object and other as array. What was needed is modify the nested array and object in the same query.

Let’s take an example document for this case-
{
 "badgeCount" : {
  "09cf79ad-cce7-4826-8a66-e0653eabae4e" : 0,
  "5cdf9a50-b4e3-4240-8ddc-979b25820745" : 1
 },
 "createdDate" : new Date(),
 "deleted" : false,
 "roomName" : "my room",
 "description" : "my room description",
 "id" : "c58f3c08-4d84-41c7-b705-88cd081dfa04",
 "joinedUserIds" : [
  "09cf79ad-cce7-4826-8a66-e0653eabae4e",
  "5cdf9a50-b4e3-4240-8ddc-979b25820745"
 ]
}
This document is about a room, where a user can join a room via joinedUserIds and they have badgeCount which says how many new messages are there in the room. Each item in the array joinedUserIds is a use id and there is a property of the same user id in the badgeCount object.

So, in my case what was needed, when a user leaves a room we need to remove the user id from joinedUserIds and also from badgeCount.

Solution goes like this-
r.db('test').table('room').get("c58f3c08-4d84-41c7-b705-88cd081dfa04").replace(function (s) {
 return s.without({
  badgeCount : {
   "09cf79ad-cce7-4826-8a66-e0653eabae4e" : true
  }
 }).without("joinedUserIds").merge({
  joinedUserIds : s("joinedUserIds").filter(function (id) {
   return id.ne("09cf79ad-cce7-4826-8a66-e0653eabae4e");
  })
 })
})
We have solved this by replace with chained without. First without is removing 09cf79ad-cce7-4826-8a66-e0653eabae4e from badgCount. Result of first without is, it will remove 09cf79ad-cce7-4826-8a66-e0653eabae4e from badgeCount object. Second without removes joinedUserIds and then adds it back with merge and filter.

Tuesday, April 26, 2016

SQL like IN clause in RethinkDB

Let’s consider a simple example of product. We need to get all product where product ids in a given list. A product document goes like this-
[
      {
         “ID”:0,
         “Name”:”Bread”,
         “Description”:”Whole grain bread”,
         “ReleaseDate”:”1992-01-01T00:00:00″,
         “DiscontinuedDate”:null,
         “Rating”:4,
         “Price”:”2.5″
      },
      {
         “ID”:1,
         “Name”:”Milk”,
         “Description”:”Low fat milk”,
         “ReleaseDate”:”1995-10-01T00:00:00″,
         “DiscontinuedDate”:null,
         “Rating”:3,
         “Price”:”3.5″
      },
      {
         “ID”:2,
         “Name”:”Vint soda”,
         “Description”:”Americana Variety – Mix of 6 flavors”,
         “ReleaseDate”:”2000-10-01T00:00:00″,
         “DiscontinuedDate”:null,
         “Rating”:3,
         “Price”:”20.9″
      },
   …
   ]
An equivalent SQL statement in this case will be –
SELECT ID, Name, Description from Products WHERE ID IN (1,2)
To implement this we can use filter with expr and map like below-
r.table('products').filter(function(product) {
        return r.expr([1,2]).contains(product('ID'))
    }).map(function (product) {
        return {
            id : product('ID'),
            name : product('Name'),
            description: product('Description')
        }
    });


Tuesday, February 17, 2015

Creating a mongo DB replica set in windows desktop

I was going through mongo DB online course “M101JS: MONGODB FOR NODE.JS DEVELOPERS” and got stuck on the assignment “HOMEWORK: HOMEWORK 6.5”. As I was working on windows and the steps given was for MAC or Linux environment. I thought of sharing this with others. The original steps are as follows-

In this homework you will build a small replica set on your own computer. We will check that it works with validate.js, which you should download from the Download Handout link. Create three directories for the three mongod processes.

On unix or mac, this could be done as follows:
mkdir -p /data/rs1 /data/rs2 /data/rs3
Now start three mongo instances as follows. Note that are three commands. The browser is probably wrapping them visually.
mongod --replSet m101 --logpath "1.log" --dbpath /data/rs1 --port 27017 --smallfiles --oplogSize 64 --fork 
mongod --replSet m101 --logpath "2.log" --dbpath /data/rs2 --port 27018 --smallfiles --oplogSize 64 --fork
mongod --replSet m101 --logpath "3.log" --dbpath /data/rs3 --port 27019 --smallfiles --oplogSize 64 –fork
Windows users: Omit -p from mkdir. Also omit --fork and use start mongod with Windows compatible paths (i.e. back slashes "\") for the --dbpath argument (e.g; C:\data\rs1).

Now connect to a mongo shell and make sure it comes up
mongo --port 27017
Now you will create the replica set. Type the following commands into the mongo shell:
config = { _id: "m101", members:[
          { _id : 0, host : "localhost:27017"},
          { _id : 1, host : "localhost:27018"},
          { _id : 2, host : "localhost:27019"} ]
         };
rs.initiate(config);
At this point, the replica set should be coming up. You can type
rs.status()
to see the state of replication.

The steps I have followed as below-

I kept all the database file in the following folder - D:/Anup/POCs/MongoDB/data/test_replicaset. So the create directory looks like below-
mkdir "D:/Anup/POCs/MongoDB/data/test_replicaset/rs1" "D:/Anup/POCs/MongoDB/data/test_replicaset/rs2" "D:/Anup/POCs/MongoDB/data/test_replicaset/rs3"


Now before moving further please make sure that in command prompt you are already in the mongo DB bin folder. In my case the mongo.exe and mongod.exe are in the folder -D:\Anup\POCs\MongoDB.



Now as per the given instruction the mnogo DB command for create replica set for windows looks like below-
mongod --replSet m101 --logpath "D:/Anup/POCs/MongoDB/data/test_replicaset/1.log" --dbpath D:/Anup/POCs/MongoDB/data/test_replicaset/rs1 --port 27017 --smallfiles --oplogSize 64 start mongod  
mongod --replSet m101 --logpath "D:/Anup/POCs/MongoDB/data/test_replicaset/2.log" --dbpath D:/Anup/POCs/MongoDB/data/test_replicaset/rs2 --port 27018 --smallfiles --oplogSize 64 start mongod 
mongod --replSet m101 --logpath "D:/Anup/POCs/MongoDB/data/test_replicaset/3.log" --dbpath D:/Anup/POCs/MongoDB/data/test_replicaset/rs3 --port 27019 --smallfiles --oplogSize 64 start mongod
But for some reason I was unable to execute the script in one go. So what I have done is opened three command prompts and executed the command separately like below ( removing start mongod at the end).



Next I have opened another command prompt to run mongo command-
mongo --port 27017
Once the mongo shell connected to test database, executed the remaining of the config command and checked the status. It’s started running.


Wednesday, December 24, 2014

Consuming Microsoft web API from Angular JS application – Cross domain – Cross site: JSONP

In this post we are going to explore how to consume web API from angular JS application. This is a cross domain call where the web API will reside in a different application than the angular call. We will do this with JSONP.

In this example we will be using visual studio 2013, web API 2 and angular JS 1.3.8

Here I am using the Product web API sample from asp.net site. You can find the steps here. Follow the steps for creating the web API project.

Now if we check the URL(api/products), we will get listing of all products in the browser in xml format. In this example we will use JSON data as result, we can change this by changing formatter. We can add the following code to change the formatter in WebApiConfig at the end of Register(HttpConfiguration config) method.
config.Formatters.Clear();
config.Formatters.Add(new JsonMediaTypeFormatter());
Check the difference after changing the formatter.



Now we are ready for consuming it from angular JS.

We can add the angular JS reference using NuGet like below-



If you have issue with NuGet, you can download the latest version of angular JS from the angular site and refer it to the page.

Now let’s define a module(ProductModule) and a controller(ProductCtrl) for reading the products from the web API method.
var product = angular.module("ProductModule", []);
product.controller("ProductCtrl", ["$scope", "$http", function ($scope, $http) {
//to do    
}]);
Here we have added $http as we need to consume a service. We can do the call to API service with a callback and jsonp as calling method. We are using jsonp here are the angular site and the api are two different web site and hence cross domain.
var product = angular.module("ProductModule", []);

product.controller("ProductCtrl", ["$scope", "$http", function ($scope, $http) {
    $http({
        method: 'jsonp',
        url: 'http://localhost:51116/api/products?callback=JSON_CALLBACK'
    })
        .success(function (data, status, headers, config) {
            $scope.Products = data;
            
        })
        .error(function (data, status, headers, config) {
            
        });
}]);
As we are using jsonp, we need to append a callback to the calling URI, hence ?callback=JSON_CALLBACK'. Angular JS internally handles and returns the result with the data parameter in the success method.

Now let’s design a HTML page for consuming the result from the service. HTML goes like this-
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title></title>
</head>
<body ng-app="ProductModule">
    <table ng-controller="ProductCtrl">
        <thead>
            <tr>
                <th>Id</th>
                <th>Name</th>
                <th>Category</th>
                <th>Price</th>
            </tr>
        </thead>
        <tbody>
            <tr ng-repeat="product in Products">
                <td>{{product.Id}}</td>
                <td>{{product.Name}}</td>
                <td>{{product.Category}}</td>
                <td>{{product.Price}}</td>
            </tr>
        </tbody>
    </table>
    <script src="Scripts/angular.js"></script>
    <script src="Domain JS/ProductCtrl.js"></script>
</body>
</html>
Now before running the application please check the URL specially the port number. If this is correct, you should be getting some 404 error. Let’s check this in chrome developer tool. Screenshots are there in the image below. The Ajax request is 200 OK and there is proper data in the response tab-



Then what is the Issue. If you check the request URL, it is http://localhost:51116/api/products?callback=angular.callbacks._0 But we have provided the URL as http://localhost:51116/api/products?callback=JSON_CALLBACK. This is not an issue. Angular JS is internally changing JSON_CALLBACK to angular.callback._0. Now let’s check the response. It’s coming as-
[{"Id":1,"Name":"Tomato Soup","Category":"Groceries","Price":1.0},{"Id":2,"Name":"Yo-yo","Category":"Toys","Price":3.75},{"Id":3,"Name":"Hammer","Category":"Hardware","Price":16.99}]
Here is the problem. As it is a JSONP request the response should be like angular.callback._0(response) that is –
angular.callback._0( [{"Id":1,"Name":"Tomato Soup","Category":"Groceries","Price":1.0},{"Id":2,"Name":"Yo-yo","Category":"Toys","Price":3.75},{"Id":3,"Name":"Hammer","Category":"Hardware","Price":16.99}])
That is what Web API should be doing and the JSON formatter should support this. The formatter that we have used (JsonMediaTypeFormatter) is not having this feature. There is a nice work around for this. We can add JsonpFormatter for this. Please check the below link for detail-

https://github.com/WebApiContrib/WebApiContrib.Formatting.Jsonp

Let’s install JsonpFormatter formatter using NuGet. Search for WebApiContrib.Formatting.Jsonp in NuGet manager and install like below-



Next we need to add the callback capability and expose json data like below-
GlobalConfiguration.Configuration.AddJsonpFormatter();
GlobalConfiguration.Configuration.Formatters.XmlFormatter.SupportedMediaTypes.Clear();
Now the results is coming in correct format and successful. The complete source code is attached here.

Monday, August 4, 2014

Accessing derived class properties in the base class public method and creating dynamic queries

There are many ORM models that work on Object Relation Mapping. It generates queries on the fly based on the XML config file and reflection.

This post is intended to explain a simple scenario of how we can write a method like on a base class say ”save”, and any class that is derived from this base class should automatically call the save method, create a dynamic query and save the data on database.

Now let’s take a simple class named Person that has only two fields as below-
public class Person {
    public string FirstName { get; set; }
    public string LastName { get; set; }
}
And on doing the following code it should take the value from the instance “p”, create a dynamic query and save the data to database-
Person p = new Person() { FirstName="John", LastName="Smit" };
        p.save();
We can follow few steps for doing that.

Step 1 : Let’s create an class label attribute that will carry the name of the table for saving data in the database. It goes like this-
[System.AttributeUsage(System.AttributeTargets.Class )]
public class TableName : System.Attribute
{
    public string name;
    public TableName() 
    {
    }
    public TableName(string name)
    {
        this.name = name;
    }
}
Step 2 : is to create a base class that will implement a public method “save”. Ideally we should make this class as generic class as we need to access the property and the value of the properties. So, we can have this class as –
public class DBObjectwhere T:class { 
    public void save(){

        //implementation goes here
            
    }
}
So the inheritance will be below. Here we have added TableName attribute and tblPerson is the database table name-
[TableName(name="tblPerson")]
public class Person : DBObject {
    public string FirstName { get; set; }
    public string LastName { get; set; }
}
Step 3 : What we need to do in this step is five things.
  1. Get the name of the table
  2. Create comma separated table field name
  3. Create comma separated table field value to insert
  4. Create the query
  5. Do database call 

Step 3-1: We are passing type information to DBObject so we can get custom attribute(TableName) value through T like below-
 (typeof(T).GetCustomAttributes(false).FirstOrDefault() as TableName).Name
Step 3-2: We can get comma separated field names from T using reflection like below-
string.Join(",", (from p in typeof(T).GetProperties().AsEnumerable()
                                   select p.Name).ToArray());
Step 3-3: We can get comma separated field values from T using reflection like below-
string.Join(",", (from p in typeof(T).GetProperties().AsEnumerable()
                                   select "'"+ p.GetValue(this,null)+"'").ToArray());
Step 3-4: We can create final query as following-
string.Format("insert into {0} ({1}) values ({2})  ", attr.name, fields, values);
So the complete save method will look like below-
    public void save(){
        var attr = typeof(T).GetCustomAttributes(false).FirstOrDefault() as TableName;
        string fields, values;
        fields = string.Join(",", (from p in typeof(T).GetProperties().AsEnumerable()
                                   select p.Name).ToArray());
        values = string.Join(",", (from p in typeof(T).GetProperties().AsEnumerable()
                                   select "'"+ p.GetValue(this,null)+"'").ToArray());
        string query = string.Format("insert into {0} ({1}) values ({2})  ", attr.name, fields, values);
    }
Step 3-5: Variable query will finally result the following string for this example-
insert into tblPerson (FirstName,LastName) values ('John','Smit')  
This is the query we are looking for. I am not completing the data base call part.

How to upload multiple records in C# and stored procedure without looping

Recently I come across a question regarding how to save multiple records in sql server using C# and without using any loop.

Let’s take a simple case of saving list of persons. Let’s have the following class as person-

public class Person {
        public string PFirstName { get; set; }
        public string PLastName { get; set; }
        public int PAge { get; set; }
    }
Then let’s create a simple data base table called Person as below-
CREATE TABLE [dbo].[Person](
	[FirstName] [varchar](100) NULL,
	[LastName] [varchar](100) NULL,
	[Age] [int] NULL
) ON [PRIMARY] 
Now we can solve this problem by passing XML data as input to a stored procedure and using SQL XML for parsing data and saving to database.

So, let’s first create a list of persons for this example like below-
 List personList =new List(){
                new Person(){ PFirstName="abc", PLastName= "smit", PAge=32},
                new Person(){ PFirstName="bcd", PLastName= "pal", PAge=32}
            };
 
Now let’s parse this list to XML for saving and put it into a string variable-
 string SProc = "dbo.save_person_bulk";
            string ConnectonString = @"Data Source=(local);Initial Catalog=sample;Integrated Security=True;";
            using (SqlConnection sqlConnection = new SqlConnection(ConnectonString))
            {
                sqlConnection.Open();

                using (SqlCommand sqlCommand = new SqlCommand(SProc, sqlConnection))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Add(new SqlParameter("@person_data", SqlDbType.VarChar)
                          {
                              Value = xmlPersonData
                          });
                    using (DataTable dataTable = new DataTable())
                    {
                        using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter())
                        {
                            sqlDataAdapter.SelectCommand = sqlCommand;
                            sqlDataAdapter.Fill(dataTable);
                        }
                    }
                }
            }
On doing the list parsing the resulting XML will look like this- Looking at the generated XML and the stored procedure the code is self explanatory.

Tuesday, May 14, 2013

Enterprise Library 6.0: The LogWriter has not been set for the Logger static class. Set it invoking the Logger.SetLogWriter method

A common approach to create a log entry using the following code-
LogEntry entry = new LogEntry();
entry.Message = "I am logging";
Logger.Write(entry);
This works fine with Enterprise Library 5.0. But in 6.0 it gives the error in the title. We can solve this by the following-
Logger.SetLogWriter(new LogWriterFactory().Create());
LogEntry entry = new LogEntry();
entry.Message = "I am logging";
Logger.Write(entry);
Or we can also try-
IConfigurationSource configurationSource = ConfigurationSourceFactory.Create();
LogWriterFactory logWriterFactory = new LogWriterFactory(configurationSource);
Logger.SetLogWriter(logWriterFactory.Create());
LogEntry entry = new LogEntry();
entry.Message = "I am logging";
Logger.Write(entry);