Products

Thursday 1 December 2016

Integrating Google+ Social media login with your webpage/website

Elopade.com has a new google+ social media login.

Discussing elopade.com code will be lengthy, so i have come with a simulating google+ integration with jsp & servlet demo example.

so here we go..

project structure:

web.xml
-------------------------------

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" version="3.0">
<display-name>socialMediaLogin</display-name>
<welcome-file-list>
<welcome-file>index.html</welcome-file>
<welcome-file>index.htm</welcome-file>
<welcome-file>index.jsp</welcome-file>
<welcome-file>default.html</welcome-file>
<welcome-file>default.htm</welcome-file>
<welcome-file>default.jsp</welcome-file>
</welcome-file-list>
</web-app>

index.jsp
-------------------------------------

<!DOCTYPE html>
<%
response.setHeader("Cache-Control", "no-cache");
response.setHeader("Cache-Control", "no-store");
response.setDateHeader("Expires", 0);
response.setHeader("Pragma", "no-cache");
if (session.getAttribute("username") == null) {
%>
<html>
<head>
<meta charset="US-ASCII">
<title>Login Page</title>
</head>
<body>

<form action="LoginServlet" method="post">
UserName: <input type="text" name="user" id = "user">
<br>
Password: <input type="password" name="pwd">
<br>
<input type="submit" value="Login">

<a href="https://accounts.google.com/o/oauth2/auth?scope=email&redirect_uri=http://localhost:8080/aSession/oauth2callback&response_type=code&client_id=54353647045-1bj3evf69hq0eop496o5k7lktk1p6rpu.apps.googleusercontent.com&approval_prompt=force">Login With Gmail</a>

</form>
</body>
</html>
<%
} else {
response.sendRedirect("LoginSuccess.jsp");
}
%>

GooglePojo.java
------------------------------------

package com.session;

public class GooglePojo
{
String id;
String email;
boolean verified_email;
String name;
String given_name;
String family_name;

public String getId()
{
return this.id;
}

public void setId(String id)
{
this.id = id;
}

public String getEmail()
{
return this.email;
}

public void setEmail(String email)
{
this.email = email;
}

public boolean isVerified_email()
{
return this.verified_email;
}

public void setVerified_email(boolean verified_email)
{
this.verified_email = verified_email;
}

public String getName()
{
return this.name;
}

public void setName(String name)
{
this.name = name;
}

public String getGiven_name()
{
return this.given_name;
}

public void setGiven_name(String given_name)
{
this.given_name = given_name;
}

public String getFamily_name()
{
return this.family_name;
}

public void setFamily_name(String family_name)
{
this.family_name = family_name;
}

public String toString()
{
return

"GooglePojo [id=" + this.id + ", email=" + this.email + ", verified_email=" + this.verified_email + ", name=" + this.name + ", given_name=" + this.given_name + ", family_name=" + this.family_name + "]";
}
}

GsonUtility.java

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

package com.session;

import com.google.gson.Gson;

import com.google.gson.JsonObject;

import com.google.gson.JsonParser;

public class GsonUtility

{

static Gson gson = new Gson();

public static String tojson(Object object)

{

return gson.toJson(object);

}

public static String getFbAccessTokenFromJson(String j)

{

JsonObject json = (JsonObject)new JsonParser().parse(j);

JsonObject authr = (JsonObject)json.get("authResponse");

String act = authr.get("access_token").getAsString();

return act;

}

public static String getJsonElementString(String name, String gs)

{

try

{

JsonObject json = (JsonObject)new JsonParser().parse(gs);

return json.get(name).getAsString();

}

catch (Exception localException) {}

return null;

}

public static String getElementString(String string, String line1)

{

if (line1.indexOf(string) != -1)

{

int k = string.length();

return line1.substring(k + 1, line1.indexOf("&"));

}

return line1;

}

LoginServlet.java

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

package com.session;

import java.io.IOException;

import java.io.PrintWriter;

import javax.servlet.RequestDispatcher;

import javax.servlet.ServletException;

import javax.servlet.annotation.WebServlet;

import javax.servlet.http.Cookie;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import javax.servlet.http.HttpSession;

import javax.websocket.Session;

/**

* Servlet implementation class LoginServlet

@WebServlet("/LoginServlet")

public class LoginServlet extends HttpServlet {

private static final long serialVersionUID = 1L;

private final String userID = "uid";

private final String password = "pwd";

protected void doPost(HttpServletRequest request,

HttpServletResponse response) throws ServletException, IOException {

String user = request.getParameter("user");

String pwd = request.getParameter("pwd");

if (userID.equals(user) && password.equals(pwd)) {

HttpSession session = request.getSession(true);

session.setAttribute("username", user);

response.sendRedirect("LoginSuccess.jsp");

} else {

getServletContext().getRequestDispatcher("/index.jsp").forward(request, response);

}

OAuth2Callback.java

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

package com.session;

import com.google.gson.Gson;

import java.io.BufferedReader;

import java.io.IOException;

import java.io.InputStreamReader;

import java.io.OutputStreamWriter;

import java.net.MalformedURLException;

import java.net.ProtocolException;

import java.net.URL;

import java.net.URLConnection;

import javax.servlet.ServletException;

import javax.servlet.annotation.WebServlet;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import javax.servlet.http.HttpSession;

@WebServlet("/oauth2callback")

public class OAuth2Callback extends HttpServlet {

private static final long serialVersionUID = 1L;

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

HttpSession session = request.getSession(true);

try

{

String code = request.getParameter("code");

System.out.println(code);

String urlParameters = "code=" +

code +

"&client_id=" + Setup.CLIENT_ID +

"&client_secret=" + Setup.CLIENT_SECRET +

"&redirect_uri=" + Setup.REDIRECT_URL +

"&grant_type=authorization_code";

System.out.println(urlParameters);

URL url = new URL("https://accounts.google.com/o/oauth2/token");

URLConnection conn = url.openConnection();

conn.setDoOutput(true);

OutputStreamWriter writer = new OutputStreamWriter(

conn.getOutputStream());

writer.write(urlParameters);

writer.flush();

String line1 = "";

BufferedReader reader = new BufferedReader(new InputStreamReader(

conn.getInputStream()));

String line;

while ((line = reader.readLine()) != null)

{

line1 = line1 + line;

}

String s = GsonUtility.getJsonElementString("access_token", line1);

url = new URL(

"https://www.googleapis.com/oauth2/v1/userinfo?access_token=" +

s);

conn = url.openConnection();

line1 = "";

reader = new BufferedReader(new InputStreamReader(

conn.getInputStream()));

while ((line = reader.readLine()) != null) {

line1 = line1 + line;

}

GooglePojo data = (GooglePojo) new Gson().fromJson(line1, GooglePojo.class);

writer.close();

reader.close();

request.setAttribute("auth", data);

session.setAttribute("username", data.getName());

request.getRequestDispatcher("/LoginSuccess.jsp").forward(request, response);

} catch (Exception e) {

e.printStackTrace();

request.getRequestDispatcher("/index.jsp").forward(request, response);

}

protected void doPost(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {

}

Setup.java

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

package com.session;

public class Setup {

public static final String CLIENT_ID = "545876977045-1bj3eerere0eop496o5k7lktk1p6rpu.apps.googleusercontent.com";

public static final String CLIENT_SECRET = "LRdeGc8kerreroUBtS2Oy83";

public static final String REDIRECT_URL = "http://localhost:8080/aSession/oauth2callback";

}

LoginSuccess.jsp

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

<%@ page language="java" contentType="text/html; charset=US-ASCII"

pageEncoding="US-ASCII"%>

response.setHeader("Cache-Control", "no-cache");

response.setHeader("Cache-Control", "no-store");

response.setDateHeader("Expires", 0);

response.setHeader("Pragma", "no-cache");

int one = 1;

if ( one == 1) {

// String username = request.getAttribute("username").toString();

/* session.getAttribute("username") != null && !session.getAttribute("username").toString().trim().isEmpty() */

<%@page import="com.session.GooglePojo"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<title>Login Success Page</title>

</head>

<body>

blahs

</body>

</html>

} else {

response.sendRedirect("index.jsp");

}

LogoutSuccess.jsp

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

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"

pageEncoding="ISO-8859-1"%>

response.setHeader("Cache-Control", "no-cache");

response.setHeader("Cache-Control", "no-store");

response.setDateHeader("Expires", 0);

response.setHeader("Pragma", "no-cache");

if (session.getAttribute("username") != null && !session.getAttribute("username").toString().trim().isEmpty()) {

String username = session.getAttribute("username").toString();

session.invalidate();

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<title>Insert title here</title>

</head>

<body>

User <%=username%> Have been Logged Out Successfully.

</body>

</html>

} else {

response.sendRedirect("index.jsp");

}

welcome2.jsp

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

<%@ page language="java" contentType="text/html; charset=US-ASCII"

pageEncoding="US-ASCII"%>

if (session.getAttribute("username") != null && !session.getAttribute("username").toString().trim().isEmpty()) {

String username = session.getAttribute("username").toString();

response.setHeader("Cache-Control", "no-cache");

response.setHeader("Cache-Control", "no-store");

response.setDateHeader("Expires", 0);

response.setHeader("Pragma", "no-cache");

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<title>welcome 2</title>

</head>

<body>

<h3>Hi <%=username %>, Login successful.</h3>

<br>

</footer>

</body>

</html>

} else {

response.sendRedirect("index.jsp");

}

Please note: I have not given my real client id or secre key for this demo. You will have to generate your own secret key and client id for the URL you need to integrate with social media.

You can see how this functionality works with mu current website:elopade.com

Session Management JSP

Managing sessions is a crucial task while creating your website/ web application.

That being said, let's see how to manage sessions based on validity of logged in user id.

What we want is that after successful login, user cannot navigate back to index page until logged out or until session expires. Once once logged out, user cannot navigate to transactional pages.

Here's a simulation program for that. Get the logic from this small flow and you can implement it in any big program.

Here we go..

Project Structure:

web.xml

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

<web-app id="WebApp_ID" version="2.4"

xmlns="http://java.sun.com/xml/ns/j2ee"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee

http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">

<display-name>Spring MVC Application</display-name>

<servlet-name>HelloWeb</servlet-name>

<servlet-class>

org.springframework.web.servlet.DispatcherServlet

</servlet-class>

<load-on-startup>1</load-on-startup>

</servlet>

<servlet-mapping>

<servlet-name>HelloWeb</servlet-name>

<url-pattern>/</url-pattern>

</servlet-mapping>

</web-app>

HelloWeb-servlet.xml

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

<beans xmlns="http://www.springframework.org/schema/beans"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"

xmlns:tx="http://www.springframework.org/schema/tx"

xsi:schemaLocation="

http://www.springframework.org/schema/beans

http://www.springframework.org/schema/beans/spring-beans-3.0.xsd

http://www.springframework.org/schema/context

http://www.springframework.org/schema/context/spring-context-3.0.xsd

http://www.springframework.org/schema/tx

http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">

<context:component-scan base-package="com.user" />

<tx:annotation-driven transaction-manager="hibernateTransactionManager" />

<bean

class="org.springframework.web.servlet.view.InternalResourceViewResolver">

</bean>

</beans>

UserPojo.java

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

package com.user;

import java.io.Serializable;

public class UserPojo implements Serializable {

private static final long serialVersionUID = 1L;

private String username;

private String password;

public UserPojo() {}

public String getUsername() {

return username;

}

public void setUsername(String username) {

this.username = username;

}

public String getPassword() {

return password;

}

public void setPassword(String password) {

this.password = password;

}

HelloController.java

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

package com.user;

import java.io.BufferedReader;

import java.io.InputStreamReader;

import java.io.OutputStreamWriter;

import java.net.URL;

import java.net.URLConnection;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpSession;

import org.json.JSONObject;

import org.springframework.security.web.util.RedirectUrlBuilder;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import org.springframework.web.bind.annotation.RequestParam;

import org.springframework.web.servlet.ModelAndView;

import com.google.gson.Gson;

@Controller

@RequestMapping("/")

public class HelloController {

private final String userID = "uid";

private final String password = "pwd";

@RequestMapping(method = RequestMethod.GET)

public ModelAndView login(HttpSession session) {

if(session.getAttribute("username")==null) {

ModelAndView model = new ModelAndView("login");

model.addObject("message", "JSP page Session Demo.");

return model;

} else {

ModelAndView model = new ModelAndView("LoginSuccess");

model.addObject("message", "Welcome, User "+session.getAttribute("username").toString()+" login successfully.");

return model;

}

@RequestMapping(value = "checkLogin", method = RequestMethod.POST)

public String checkLogin(HttpSession session, UserPojo user,RedirectUrlBuilder redirect) {

if (user.getUsername().equalsIgnoreCase(userID) && user.getPassword().equalsIgnoreCase(password)) {

session.setAttribute("username",user.getUsername());

return "redirect:/LoginSuccess";

} else {

return "redirect:";

}

@RequestMapping(value = "LoginSuccess", method = RequestMethod.GET)

public ModelAndView welcomePage(HttpSession session, UserPojo user,RedirectUrlBuilder redirect) {

ModelAndView model = new ModelAndView("LoginSuccess");

model.addObject("message", "Welcome to JSP page Session Demo.");

return model;

}

@RequestMapping(value = "logout", method = RequestMethod.GET)

public ModelAndView logoutSession(HttpSession session) {

String username = "";

if(session.getAttribute("username")!=null) {

username = session.getAttribute("username").toString();

session.invalidate();

}

ModelAndView model = new ModelAndView("login");

model.addObject("message", "Welcome Spring Login Demo.");

model.addObject("logout", "User "+username+" logout successfully.");

return model;

}

@RequestMapping(value = "welcome", method = RequestMethod.GET)

public ModelAndView welcomePage(HttpSession session) {

ModelAndView model = new ModelAndView("welcome2");

model.addObject("message", "JSP page Session Demo - page 2.");

model.addObject("username", session.getAttribute("username"));

return model;

}

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

<%@ page contentType="text/html; charset=UTF-8" %>

response.setHeader("Cache-Control", "no-cache");

response.setHeader("Cache-Control", "no-store");

response.setDateHeader("Expires", 0);

response.setHeader("Pragma", "no-cache");

<html>

<head>

<title>Login Page</title>

</head>

<body>

<h2>${message}</h2>

UserName : <input type="text" name="username"><br/>

Password : <input type="password" name="password"><br/>

</form>

<h2>${logout}</h2>

</body>

</html>

LoginSuccess.jsp

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

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"

pageEncoding="ISO-8859-1"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

response.setHeader("Cache-Control", "no-cache");

response.setHeader("Cache-Control", "no-store");

response.setDateHeader("Expires", 0);

response.setHeader("Pragma", "no-cache");

if (session.getAttribute("username") != null && !session.getAttribute("username").toString().trim().isEmpty()) {

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<title>Insert title here</title>

</head>

<body>

<c:out value="${message}" /><br/>

</body>

</html>

} else {

response.sendRedirect("");

}

welcome2.java

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

<%@ page language="java" contentType="text/html; charset=US-ASCII"

pageEncoding="US-ASCII"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

response.setHeader("Cache-Control", "no-cache");

response.setHeader("Cache-Control", "no-store");

response.setDateHeader("Expires", 0);

response.setHeader("Pragma", "no-cache");

if (session.getAttribute("username") != null && !session.getAttribute("username").toString().trim().isEmpty()) {

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<title>welcome 2</title>

</head>

<body>

<h3>Hi ${username}, Login successful.</h3>

<br>

</footer>

</body>

</html>

} else {

response.sendRedirect("");

}

Now deploy and run the program

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

welcome page

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

uid: uid

password: pwd

on successful login:

on clicking back button from this page, it is redirected to this page (LoginSuccess page) itself and back navigation is blocked to welcome page.

On clicking logout, you will be redirected to logout page and you would not be able to navigate back to LoginSuccess page:

Wednesday 15 June 2016

Creating your first android app - installation, configurations and app coding

Download android studio from this link:

https://developer.android.com/studio/index.html

Keep clicking next and follow basic instructions and install studio.

Now , create a new project:

Give an application name and company domain. Company domain is pretty much like package in java.

Since we are creating a mobile app, click on mobile/tablet option:

Choose the design template you wish to build upon. I am selecting Empty activity:

Activity Name is named by default as MainActivity, you can change it to anything relevant. This is the piece of code where you write your events code and click on finsih.

On clicking finish editor opens, navigate to MainActivity.

since screen shot is unclear, ill put the entire final code here: [refer to it at the end]

package com.example.jb.khushisoysterdemo1;

import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.TextView;

public class MainActivity extends AppCompatActivity {

        @Override        protected void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_main);
        }
        public void onClickEvent(View v)
        {
            TextView txt=(TextView) (findViewById(R.id.textID1));
            txt.setText("Hey there, this is my 1st android app with event");
        }
    }

Now navigate to gradle.properties

Edit it by removing the last comment, and adding an extra line, so that the last two lines are:

org.gradle.parallel=true

org.gradle.daemon=true

Now gradle.properties should be like:

Now go to File > Settings > Compiler

Add –offline at command-line options to run your app offline with the connected device

Now go to File > Settings > Build, Execution, Deployment > Build Tools > Gradle .

Tick mark “Offline work”.

Click on Apply and press OK.

Now go to res > layout > activity_main.xml

Click on Widgets > Button and drag button to phone screen on the right side.

Label the button as testClick and let the id be buttonID1:

Now drag a Plain TextView from widget. On running this app at the end, this text box should appear/ be made visible in screen on click of “testClick” button.

Give text as textPlaceHolder and give id name as textID1.

We will use button and text widget ids to fire event on button click.

Now, go back to MainActivity and change onCreate to public instead of protected and add a function “onCllickEvent” to trigger on click of button “buttonID1”.

Now go back to activity_main.xml and double click on “testClick” button widget. Navigate to “onClick” proprty and select “onClickEvent”.

Check mark “clickable”

Now to test your app wirelessly on your android device, you have to do following configuartions:

(wireless test on android device is supported from version 5 as per my understanding)

On your android device, navigate to:

Settings > About device and Click on build number 7 times.

Now go to Settings > Developer Options

And check mark “Authorise wireless display” (Check on this point, im not sure if this is compulsary, but i did it)

Note your ip from ADB over network.

NOW,

On your computer, navigate to:

C:\Users\User Name\AppData\Local\Android\Sdk\platform-tools

Now press shift and right click at same time and then select “open command window here”

Now type “adb devices” and click enter. Then write “adb connect ip_address”. In place of “ip_adress”, type in the above noted ip address of your phone and click enter.

U’ll get a reply saying its connected to the ip:

Once connected,

Build and Run your app.

For USB test on android app

- To test your app on device with USB connection:

1. Make sure your device driver is installed in your computer. For example, Samsung device driver is downloaded from http://www.samsung.com/in/support/usefulsoftware/KIES/.

2. In your computer, navigate to:

C:\Users\UserName\AppData\Local\Android\Sdk

And double click on SDK Manager and navigate to Extras and check mark “Google USB Driver”:

Install it.

Then build the project:

Connect your android device with your laptop. You should be able to see your connected device as blue highlighted in this screenshot:

Click OK.

Next, run it and you should be able to see this new app in your android device

Now click on TESTCLICK to see the text message

Tuesday 3 May 2016

Private VS Final in java

Private			Final
	Possible	Example/Other comments.	Possible	Example/Other comments.
Class	N	Only private inner classes are possible.	Y	Final class cannot be inherited
Variable	Y	Private variables cannot be read outside class without being accessed by its own class method.	Y	An initialized final variable cannot be changed ever. Uninitialized Instance final variable - An unitialized final variable MUST be initialized either ONLY in instance block, in which case you cannot initialize it even in constructors. ELSE if there is no instance block, it MUST be initialized in ALL its constructors, failing which there is compile time error. Uninitialized static final variable - I observed that there was a compile time error on declaring a final static variable without being initialized. Initializing it even in static method would not solve the error.
Constructor	Y	If you make a constructor private, you restrict the implicit/default call of super constructor and thus you restrict class being inherited and you restrict object creation of the class having private constructor. So you cannot create an object of a class with private constructor.	N	Constructors cannot be final. Error: Illegal modifier for the constructor in type parentClass; only public, protected & private are permitted
Method	Y	You cannot override a private method. Nor can you access a private method from outside class. You can access private method if another methof of same class calls the private method, in which case you can create object of class and call the method which in turn calls private methods.	Y	A final method cannot be overridden. Can be tested with help of upcasting.

Monday 21 March 2016

Handling back and forward navigation of your webpage by clearing cache

Now you have a system which has popup modals for editing row level data. On cancelling the pop-up modal, you are back at the previous page. However, on clicking browser back button, the edit popup modal pops up. Which is clearly not the flow of screens you would like. So to avoid that, you can use interceptors to avoid cache memory to store URLs which you do not want to be navigated back.

So, lets say you have a main page: itemManagement.jsp and on edit, the URL is itemManagement/edit.html. edit.htl opens up teh pop-up modal. On cancelling that, if you press back button, you would like to skip edit.html. You can do so by simply adding interceptors in spring config file:

<mvc:interceptors>

<mvc:interceptor>

<mvc:mapping path="itemManagement/edit.html/*"/>

<mvc:mapping path="itemManagement/edit.html"/>

</bean>

</mvc:interceptor>

</mvc:interceptors>

Thats pretty much it. Now on clicking back or forward buttons, edit screen does not pops up again, because you have disabled cache storing for this URL. Your browser depends on cache to navigate back and forward to pages but if you disable cache, those pages are skipped while browsing back or forward from your browser's navigation.

Saturday 6 February 2016

Elaborate explanation on part of a Elopade DB design structure

Elopade Database design follows a very crisp structure.

Each table created has an automated procedure linked to it which gives it a "Table ID". You cannot create a table ID unless that table ID is registered to a module. This gives flexibility to have different levels of admin and super users.

1st - we create a table to store user detail. This table initially has only firstname, lastname, uid , pswd, email, modifiedby and createby to store. But after we create 2 more tables, we alter this table to add new columns - table id and roleID. I am deliberately creating this table initially without these columns as at this point table from which tableid needs to be referenced is not yet created. So follow the steps, which may look messy at first but works out just fine.

Just for clarity purpose i have added a naming convention to tables and procedures(which i personally detest to do ) but it is vital, somehow.

so here we go:

create table to hold users detail (note: at this point i have not added createdby, modifiedby or tableid, which i do after few more queries):

create table admin_m_users
(
firstname varchar(50),
lastname varchar(50),
uid varchar(30) primary key,
pwd varchar(200),
emailID varchar(30),
createdDate timestamp,
modifiedDate timestamp
)

create a procedure to auto update insert date. Modified date is saved from the backend program and not with the procedure. Honestly, my stack memory went low so i could not create a trigger for after update. I had increased stack memory to 1 GB in pg config file,, but it wasnt working. So my workaround - I update modified date from application and then save it as a value in DB:

NOTE: Our encryption happens in procedure:

CREATE OR REPLACE FUNCTION admin_after_insert_admin_m_users()
RETURNS TRIGGER
AS $BODY$ BEGIN
IF new.createby IS NULL
THEN
        UPDATE admin_m_users
        SET createdDate = current_timestamp, createby = new.uid, modifiedby = new.uid,
        pwd = crypt(NEW.pwd, gen_salt('bf'))
WHERE uid = new.uid;
ELSE
         UPDATE admin_m_users
        SET createdDate = current_timestamp,
        pwd = crypt(NEW.pwd, gen_salt('bf'))
WHERE uid = new.uid;
END IF;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;

Trigger to execute above procedure:

CREATE TRIGGER admin_trigger_Afterinsert_admin_m_users AFTER INSERT ON admin_m_users
FOR EACH ROW EXECUTE PROCEDURE admin_after_insert_admin_m_users();

Now, lets just insert a value in admin_m_users:
insert into admin_m_users values
('U01', 'PswdNew@007', 'khushboo@elopade.com');

Now you can test and see if created date is auto filled.

Next,
we create a table to link modules with tables. Each table ever created will be registered here. admin_m_users too will get registered here, after which i alter table admin_m_users to add table ID:

create table admin_m_moduleTableLink
(
moduleID varchar(30) NOT NULL,
tableID varchar(30) NOT NULL,
ModuleWithtableDescription varchar(60) UNIQUE,
createBy varchar(30)references admin_m_users(uid),
modifiedBy varchar(30)references admin_m_users(uid),
createdDate timestamp,
modifiedDate timestamp,
CONSTRAINT compositePair PRIMARY KEY (moduleID,tableID)
)

Procedure:

CREATE OR REPLACE FUNCTION admin_after_insert_admin_m_moduleTableLink()
RETURNS TRIGGER
AS $BODY$ BEGIN
        UPDATE admin_m_moduleTableLink
        SET ModuleWithtableDescription = concat(moduleID, tableID), createdDate = current_timestamp
WHERE moduleID = new.moduleID and tableID = new.tableID;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;

Trigger:

CREATE TRIGGER admin_trigger_Afterinsert_admin_m_moduleTableLink AFTER INSERT ON admin_m_moduleTableLink
FOR EACH ROW EXECUTE PROCEDURE admin_after_insert_admin_m_moduleTableLink();

Now register all the tables which will be or can be created:

insert into admin_m_moduleTableLink
values
(
'UserProfile', 'admin_m_encrypt'
);

insert into admin_m_moduleTableLink
values
(
'UserProfile', 'admin_m_users'
);

insert into admin_m_moduleTableLink
values
(
'UserProfile', 'admin_m_roles'
);

insert into admin_m_moduleTableLink
values
(
'UserProfile', 'admin_m_module_usrRelation'
);

insert into admin_m_moduleTableLink
values
(
'Goods', 'admin_m_itemMaster'
);

insert into admin_m_moduleTableLink
values
(
'Goods', 'admin_m_itemBOM'
);

insert into admin_m_moduleTableLink
values
(
'Partner', 'admin_m_businessPartner'
);

Now 'modulewithtabledescription' will be used as tableID in all respective tables:

Now create table for roles, permission values are restricted to Y(yes) and N(No).
Different combinations can define different level of privileges. Admin will have all Y.

create table admin_m_roles
(
roleID varchar(30) primary key,
mread varchar(1),
tableID varchar(40) references admin_m_moduleTableLink(ModuleWithtableDescription),
minsert varchar(1),
mupdate varchar(1),
mdelete varchar(1),
createBy varchar(30)references admin_m_users(uid),
modifiedBy varchar(30)references admin_m_users(uid),
createdDate timestamp,
modifiedDate timestamp,
CHECK ((mread = 'Y' or mread = 'N' ) and (minsert = 'Y' or minsert = 'N') and (mupdate = 'Y' or mupdate = 'N')
and (mdelete = 'Y' or mdelete = 'N'))
)

Procedure to audo fill table id and create date:

CREATE OR REPLACE FUNCTION admin_after_insert_admin_m_roles()
RETURNS TRIGGER
AS $BODY$ BEGIN
        UPDATE admin_m_roles
        SET tableID = 'UserProfileadmin_m_roles', createdDate = current_timestamp
WHERE roleID = new.roleID;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;

trigger for above procedure:

CREATE TRIGGER admin_trigger_Afterinsert_admin_m_roles AFTER INSERT ON admin_m_roles
FOR EACH ROW EXECUTE PROCEDURE admin_after_insert_admin_m_roles();

Now delete procedure admin_after_insert_admin_m_users. (Keep following what i say here):

Delete trigger :admin_trigger_Afterinsert_admin_m_users

ALTER TABLE admin_m_users
ADD tableID varchar(40) references admin_m_moduleTableLink(ModuleWithtableDescription),
ADD createBy varchar(30)references admin_m_users(uid),
ADD modifiedBy varchar(30)references admin_m_users(uid),
ADD roleID varchar(30) references admin_m_roles(roleID);

Note: Now admin_m_users have role added to it along with others above.

Now recreate procedure and trigger for admin_m_users:

CREATE OR REPLACE FUNCTION admin_after_insert_admin_m_users()
RETURNS TRIGGER
AS $BODY$ BEGIN
IF new.createby IS NULL
THEN
        UPDATE admin_m_users
        SET createdDate = current_timestamp, tableID = 'UserProfileadmin_m_users', createby = new.uid, modifiedby = new.uid,
        pwd = crypt(NEW.pwd, gen_salt('bf'))
WHERE uid = new.uid;
ELSE
         UPDATE admin_m_users
        SET createdDate = current_timestamp, tableID = 'UserProfileadmin_m_users',
        pwd = crypt(NEW.pwd, gen_salt('bf'))
WHERE uid = new.uid;
END IF;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;
Trigger:

CREATE TRIGGER admin_trigger_Afterinsert_admin_m_users AFTER INSERT ON admin_m_users
FOR EACH ROW EXECUTE PROCEDURE admin_after_insert_admin_m_users();

Now link role with tables:

create table admin_m_module_usrRelation
(
tableID2 varchar(40) references admin_m_moduleTableLink(ModuleWithtableDescription),
tableID varchar(40) references admin_m_moduleTableLink(ModuleWithtableDescription), -- procedure
roleID varchar(30)references admin_m_roles(roleID),
createBy varchar(30)references admin_m_users(uid),
modifiedBy varchar(30)references admin_m_users(uid),
createdDate timestamp,
modifiedDate timestamp,
CONSTRAINT compositePair1 PRIMARY KEY (tableID2,roleID)
)

Usual procedure and trigger:

CREATE OR REPLACE FUNCTION admin_after_insert_admin_m_module_usrRelation()
RETURNS TRIGGER
AS $BODY$ BEGIN
        UPDATE admin_m_module_usrRelation
        SET tableID = 'UserProfileadmin_m_module_usrRelation', createdDate = current_timestamp
WHERE roleID = new.roleID and tableID2 = new.tableID2;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;

CREATE TRIGGER admin_trigger_Afterinsert_admin_m_module_usrRelation
    AFTER INSERT ON admin_m_module_usrRelation
    FOR EACH ROW
    EXECUTE PROCEDURE admin_after_insert_admin_m_module_usrRelation();

Create Item Master, Restrict Item type to P(Parent), C(Child), PC(ParentAndChild):

create table admin_m_itemMaster
(
tableID varchar(40) references admin_m_moduleTableLink(ModuleWithtableDescription),
itemID varchar(30) primary key,
itemName varchar(40),
itemType varchar(2),
createBy varchar(30)references admin_m_users(uid),
modifiedBy varchar(30)references admin_m_users(uid),
createdDate timestamp,
modifiedDate timestamp,
totalCost float,
CHECK (itemType = 'P' or itemType = 'C' or itemType = 'PC')
)

Procedure to autolink it to registered table ID:

CREATE OR REPLACE FUNCTION admin_after_insert_admin_m_itemMaster()
RETURNS TRIGGER
AS $BODY$ BEGIN
        UPDATE admin_m_itemMaster
        SET tableID = 'Goodsadmin_m_itemMaster', createdDate = current_timestamp
WHERE itemID = new.itemID;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;

Trigger:

CREATE TRIGGER admin_trigger_Afterinsert_admin_m_itemMaster
    AFTER INSERT ON admin_m_itemMaster
    FOR EACH ROW
    EXECUTE PROCEDURE admin_after_insert_admin_m_itemMaster();

Create Business Partener:

create table admin_m_businessPartner
(
tableID varchar(40) references admin_m_moduleTableLink(ModuleWithtableDescription),
BP_ID varchar(30) primary key,
BP_Type varchar(2),
BP_Name varchar(30),
createBy varchar(30)references admin_m_users(uid),
modifiedBy varchar(30)references admin_m_users(uid),
createdDate timestamp,
modifiedDate timestamp,
CHECK (BP_Type = 'C' or BP_Type = 'V' or BP_Type = 'CV')
)

CREATE OR REPLACE FUNCTION admin_after_insert_admin_m_businessPartner()
RETURNS TRIGGER
AS $BODY$ BEGIN
        UPDATE admin_m_businessPartner
        SET tableID = 'Partneradmin_m_businessPartner', createdDate = current_timestamp
WHERE BP_ID = new.BP_ID;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;

CREATE TRIGGER admin_trigger_Afterinsert_admin_m_businessPartner
    AFTER INSERT ON admin_m_businessPartner
    FOR EACH ROW
    EXECUTE PROCEDURE admin_after_insert_admin_m_businessPartner();

Create a BOM table. This table links parent and child items and with relevant metrics. I have written the procedure in a such way that a parent Item should be registered as type 'P' in admin_m_item table:

create table admin_m_itemBOM
(
itemID varchar(30) NOT NULL,
parentID varchar(30) NOT NULL,
quantity int,
quantityUnit varchar(20),
price float,
vendorID varchar(30),
tableID varchar(40) references admin_m_moduleTableLink(ModuleWithtableDescription),
createBy varchar(30)references admin_m_users(uid),
modifiedBy varchar(30)references admin_m_users(uid),
createdDate timestamp,
modifiedDate timestamp,
CONSTRAINT itemID PRIMARY KEY (itemID,parentID),
CONSTRAINT itemIDF FOREIGN KEY (itemID)
REFERENCES admin_m_itemMaster (itemID),
CONSTRAINT parentIDF FOREIGN KEY (parentID)
REFERENCES admin_m_itemMaster (itemID),
CONSTRAINT vendorID FOREIGN KEY (vendorID)
REFERENCES admin_m_businessPartner (BP_ID)
)

Procedure:

CREATE OR REPLACE FUNCTION admin_after_insert_admin_m_itemBOM()
RETURNS TRIGGER
AS $BODY$ BEGIN
        UPDATE admin_m_itemBOM
        SET tableID = 'Goodsadmin_m_itemBOM',createdDate = current_timestamp
WHERE (itemID = new.itemID and parentID = new.parentID) and parentID IN (select itemID from admin_m_itemMaster where itemType = 'P');
delete from admin_m_itemBOM where tableID IS NULL;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;

Note: If parent ID is not among registered parent item, i delete the row in the above procedure.

Trigger to run above procedure:

CREATE TRIGGER admin_trigger_Afterinsert_admin_m_itemBOM
    AFTER INSERT ON admin_m_itemBOM
    FOR EACH ROW
    EXECUTE PROCEDURE admin_after_insert_admin_m_itemBOM();