Search This Blog

Wednesday, August 31, 2016

Linq - Group by into an array

LINQ has lot of capability that we seldom use.For example sometime we need to group a column into a string array or collection of some sort like say list based on another column.

Here I will demonstrate same into an array.

For purpose of demonstration I will create a class in some existing project

  public class KeyValue
    {
        public string KeyCol { get; set; }
        public string ValueCol { get; set; }
    }
 
Now I will create simple array of type 'KeyValue' just created.

  var wordList = new Model.DTO.KeyValue[] {
                new Model.DTO.KeyValue {KeyCol="key1", ValueCol="value1" },
                new Model.DTO.KeyValue {KeyCol="key2", ValueCol="value1" },
                new Model.DTO.KeyValue {KeyCol="key3", ValueCol="value2" },
                new Model.DTO.KeyValue {KeyCol="key4", ValueCol="value2" },
                new Model.DTO.KeyValue {KeyCol="key5", ValueCol="value3" },
                new Model.DTO.KeyValue {KeyCol="key6", ValueCol="value4" }
            };

Lets build a linq query on this collection(Array).Query groups 'KeyCol' property based on 'ValueCol' into group 'g' i.e. means g is collection of 'KeyCol'

            var query =from m in wordList
                     group m.KeyCol by m.ValueCol into g
                     select new { Name = g.Key, KeyCols = g.ToList().ToArray<string>() };

You can check resultset my looping  'query' 

    foreach (var rec in query)
    {
        var name = rec.Name;
        var strArray = rec.KeyCols;
    }

e.g in loop above when 'name' becomes 'value1' ,'strArray' will have value string[]{'key1','key2'} and so on.

we can further generalize this query to create object array in group by.

Wednesday, August 17, 2016

Table valued function parameter in postgres SQL

   In Postgres -SQL server whenever we create a new table it automatically creates a composite Type that corresponds with structure of table. we can use this type to create a variable ,it can hold a single row of corresponding table.
In situation where we need to pass whole result-set as parameter to Postgres function there seems to be no native support in postgres unlike Microsoft SQL server so converting a existing MSSQL Procedure to Postgres function become quite a headache.
we can create an equivalent representation of result set in XML or JSON and pass it to function; inside function we can deserialize this into result-set for purpose of further operation on result-set that way we can compensate absence of Table Valued Parameters in Postgres SQL .
 Along with JSON & XML we can also create an array of objects from result-set to function as parameter.
Here I will demonstrate this approach with inbuilt Array functionality of Postgres.

Lets create a Table as follows
CREATE TABLE public."Projects"
(
   id integer NOT NULL,
  "createdAt" timestamp with time zone,
  "updatedAt" timestamp with time zone,
  name character varying(255) NOT NULL,
  "UserId" integer
);

For purpose of demonstration add few records into “Projects” table with two different user ids say 1 & 2.

Now let’s create a one more table that will used for passing result-set to our function, function is trivial one it just give count of rows user wise

CREATE table projectlist
(
                list int[],
                user_id int
);

Now we will use ROW construct to serialize our result-set and save that as a single row in projectlist table as follows

insert into projectlist(user_id,list)
select  1 "UserId",ARRAY(select id from "Projects" where "UserId" =1) ;

insert into projectlist(user_id,list)
select  2 "UserId",ARRAY(select id from "Projects" where "UserId" =2) ;

The Array function will convert values passed to it as an array e.g if it got 3 records as input  14,15,16 then resultant value will {14,15,16}. unnest is another Postgres function that do exactly reverse of it that we will use for de-serialization.
Below is my function that will read the record set

CREATE OR REPLACE FUNCTION public.demo_func(
    IN  list1 projectlist,
    IN  list2 projectlist)
  RETURNS TABLE(user_id int, count bigint) AS
$BODY$
BEGIN
               
        RETURN QUERY
                WITH  list1_cte AS (
                              
                ),
                list2_cte  AS (
                              
                )
                select  list1_cte.user_id,count(*) as count from list1_cte group by list1_cte.user_id
                union
                select  list2_cte.user_id,count(*) as count from list2_cte group by list2_cte.user_id;

END
$BODY$
LANGUAGE plpgsql ;

Now we need to join all dots and call our function by passing serialized result set.We create two table type variables list1 & list2 and give them serialized data that we had already inserted into our table created for same purpose.
   After that we will call our function that will return some records that I am looping using cursor.

DO
$$
DECLARE list1 projectlist;
DECLARE list2 projectlist;
DECLARE cursor_op_record RECORD;
DECLARE cursor_var refcursor;
BEGIN
                select p.* into list1  from projectlist p where user_id=1;
                select q.* into list2  from  projectlist q where user_id=2;
   OPEN cursor_var FOR
   select user_id,count from public.demo_func(list1,list2);

   RAISE INFO 'user_id, count';
   LOOP
                FETCH cursor_var INTO cursor_op_record;
                EXIT WHEN NOT FOUND;

        RAISE INFO '%,%', cursor_op_record.user_id, cursor_op_record.count;
   END LOOP;
END$$;

Here  our table projectlist has list column which is just an int[] but we can also replace it with array of composite type object and pass a single tuple.

Monday, August 8, 2016

Some useful queries for Postgres beginners:


    After Mysql ,Now Postgres SQL has been gaining popularity at multiple level in software development.It can be freely downloaded from internet.Postgres Sql server comes with command line utilities to query,backup , restore and what more.For all those spoiled by GUI tools like me ,PgAdmin 3 is right choice.

PgAdmin 3 is GUI tool similar to SQL Server Management Studio for MSSQL server which is freely available. Postgres SQL comes with tones of new features. Its DML syntax is quite similar to Oracle,Mysql along with some own stuff but point to note is postgres is case sensitive means table with name Users' is different from table with name 'users'.

During my exploration of postgres SQL, I collected some useful queries, obliviously from resources on internet.Many of them are from stackoverflow.Thanks to Stackoverflow for being great place for seeking information.

 There are multiple ways to do same thing,neither I claim its the efficient way to do task yet may be useful to beginners to find it in one place.

Here are some queries from my collection

Lists Databases:

SELECT datname FROM pg_database WHERE datistemplate = false;

Lists all tables in the current database:

SELECT * 
FROM information_schema.tables 
WHERE table_type = 'BASE TABLE' 
    AND table_schema = 'public' 
ORDER BY table_type, table_name

Creating a table with Identity /auto increment columns:

CREATE SEQUENCE user_id_seq;
CREATE TABLE user (
    user_id smallint NOT NULL DEFAULT nextval('user_id_seq')
);

ALTER SEQUENCE user_id_seq OWNED BY user.user_id;

All Constraints on Given table:

select * from information_schema.table_constraints where table_name='Projects'

Postgres tables without foreign key

SELECT *
FROM
information_schema.tables
WHERE
table_type = 'BASE TABLE'
AND table_schema = 'public'
and table_name not in (select distinct table_name from information_schema.table_constraints where constraint_type = 'FOREIGN KEY')


Postgres tables with foreign key:

SELECT
tc.constraint_name, tc.table_name, kcu.column_name,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE
constraint_type = 'FOREIGN KEY'

Pg-dump backup & restore at command prompt:

Pg-dump is command line utility in postgres used to backup & restore database.

Backup:

pg_dump -U postgres dbname -f dbname_pg_dump_20160715.sql

Restore:

psql -h localhost -d dbname -U postgresuser  dbname < E:\Repo\project\DBScript\dbname_pg_dump_20160715.sql

List foreign key on given table:

SELECT
tc.constraint_name, tc.table_name, kcu.column_name,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='Projects';


Table referencing given table:
Below query list all table that reference User table.

SELECT
tc.constraint_name, tc.table_name, kcu.column_name,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND ccu.table_name='Users';

List functions in given database:
SELECT
distinct routines.routine_name
FROM
information_schema.routines
WHERE
routines.specific_schema=' public'
ORDER BY
routines.routine_name


more detailed query can be

SELECT
routines.routine_name,
parameters.data_type,
parameters.ordinal_position,
routines.specific_schema
FROM
information_schema.routines
JOIN information_schema.parameters ON
routines.specific_name=parameters.specific_name
WHERE
routines.specific_schema='public' AND routine_type='FUNCTION'
ORDER BY
routines.routine_name,
parameters.ordinal_position;

Creating New user:

CREATE USER MyUser WITH PASSWORD 'abcd546fg';
GRANT ALL PRIVILEGES ON DATABASE DbName to MyUser;

REVOKE TRUNCATE ON MyUser.public."Users" FROM MyUser;
REVOKE DELETE ON MyUser.public."Users" FROM MyUser;

GRANT EXECUTE ON  FUNCTION DbName.public.fn_test;



Change Owner of Table,Sequence:

ALTER TABLE DbName.public.my_table;
ALTER SEQUENCE "Tablename_Columnname_seq" OWNER TO MyUser;
ALTER DATABASE DbName OWNER TO MyUser;

Rename User:
ALTER USER MyUser RENAME TO MyNewUser;

Set password:
ALTER USER MyUser WITH PASSWORD 'dljhfdkjs67N'

List all sequence:
SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';


Postgres Service Start, Stop(Window 10 only):
NET STOP postgresql-x64-9.5
NET START postgresql-x64-9.5


Find list of table with given column name:

select table_name from information_schema.columns where column_name = 'id'

Grant Create database access:
 ALTER USER xdba CREATEDB;

Make user super user:
 
ALTER USER xdba WITH SUPERUSER;


Please feel free to correct queries if  needed.


Wednesday, August 3, 2016

Setting Up GitHub on Debian 8

to setup GitHub on your machine just open a Bash terminal and become super user by issuing “Su” command

>su

It will ask for password of super user (actually substitute user though) provide same ,now it will drop you to familiar terminal prompt.
Using super user login ,We need to update our Repository List as follows
>apt-get update

after updating repo list we will install git to our Debian machine
>apt-get install git-core

Now we have to register at GitLab after registering there create a project say “myproject”,suppose your gitlab user name is “ yourusername” & email id used for registration is “youremail@server .com”
Generating SSH Key:
Now we might need to generate SSH key for secure communication between GitLab server & our machine.
Run following command to check if ssh key is already generated on your debian machine

>cat ~/.ssh/id_rsa.pub  
 if it yield some response then its there else generate new one as follows

>ssh-keygen -t rsa -C "youremail@server.com"

copy newly generated SSH key or existing one by looking at output of command below
>cat ~/.ssh/id_rsa.pub

response is something similar to below
ssh-rsa LMRPTYB3NzaC1yc2FRFGHFGFABAAABAQDs+y4/vM7GHVGHGhjhkjuzq/DFDHHD.FJFGJFyB5NIMKF/ZFVQ0RTBwAcxtyg++LgoVX3h5YLLdRbxn2Q54M0hAIlo05OZXQlAWDwE1RF+Mfis7IHEldHjV/qmnvcEca0ODsiLFJJFGJ7U/kHEEkfZqGFGHFtrfthfghfhVE3S+GHGHJGHGHGHGHGHJG/GGFHJGHJGHJGihkjgyhg youremail@server.com

   copy whole stuff from response of command starting from ssh-rsa till end.
now go to your githab profile setting into “SSH Key” tab and add key.
Now we need to tail our machine about destination as follows

>git config --global user.name "yourusername" git config --global user.email "youremail@server.com"
now move to your existing project root as follows

>cd existing_folder 

now you can push your project to GitLab as follows. 

>git init 
>git remote add origin git@gitlab.com:youremailusername/myproject .git 
>git add . 
 [Note: the dot(.) in above command is not by mistake but required tell all files within folder] 

>git commit -m "my first githab commit" 
>git push -u origin master
 
suppose we want to push a new project then move to folder where your project will reside and issue following command that will copy content in GitHab project newly created through website obiviously at this point it is empty,yet it will create .git folder which will act as setting for git library to which git destination project belong what is current state & current branch

>git clone git@gitlab.com:youremailusername/myproject.git 
>add a readme file with some information about project  as follows
>cd myproject touch README.md git add README.md 
>nano  README.md
>git commit -m "add README" 
>git push -u origin master
Now check on GitLab server  if readme.md file saved on server under given project.