Retrieve Records based on a Completed Period of Time (eg. Every 15 Minutes)

When you need to run a batch of transaction in a controlled manner and know when they are processed, this approach may be helpful.  The idea is to retrieve all the records that fall under a complete period of time, for example, if we select a period of 5 minutes, the query should retrieve records created within a full 5 minutes period such as: from 10:00:00-10:04:59, from 10:05:00-10:09:59 and so on… no matter at what time we execute the retrieval process.

So if you need to run a process every 15 time to check for transactions, the query should retrieve transactions done, no matter the hour, every completed block of 15 minutes such as 

                                  xx:00:00 till xx:14:59
                                  xx:15:00 till xx:29:59
                                  xx:30:00 till xx:44:59 
                                  xx:45:00 till xx:59:59. 

where xx is any hour.

No matter at what time the process is executed, the query will retrieve the previously completed period for example, if the current time is 13:42:15 the query will retrieve all the transactions from 13:15:00 till 13:29:59 since it is the previous 15 minute period that was completed.

Note that the maximum waiting time will be the period duration (in the previous example 15 minutes) but your process does not need to run every minute or so and does not need to be pulling.

For example, the following is the query format:

 select *  
 from <tables>  
 where <time stamp> between (SYSDATE-15/1440)-((TO_CHAR(((SYSDATE-15/1440)),'MI')*60 + TO_CHAR(((SYSDATE -15/1440)),'SS'))/86400) + (15*(CASE WHEN (TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'MI'))/15,'0')-1)<=0 THEN 3 ELSE TO_NUMBER((TRUNC(TO_NUMBER(TO_CHAR(SYSDATE,'MI'))/15,'0')-1)) END))/1440   
 AND ((SYSDATE)-((TO_CHAR(((SYSDATE)),'MI')*60 + TO_CHAR(((SYSDATE)),'SS'))/86400) + (15*(TRUNC(TO_NUMBER(TO_CHAR(SYSDATE,'MI'))/15,'0')))/1440)-1/86400   

where <tables> are your tables and the <time_stamp> is the record time that is going to be compared to.

If we execute the query against the current time we have:

 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') current_time,  
 to_char((SYSDATE-15/1440)-((TO_CHAR(((SYSDATE-15/1440)),'MI')*60 + TO_CHAR(((SYSDATE -15/1440)),'SS'))/86400) + (15*(CASE WHEN (TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'MI'))/15,'0')-1)<=0 THEN 3 ELSE TO_NUMBER((TRUNC(TO_NUMBER(TO_CHAR(SYSDATE,'MI'))/15,'0')-1)) END))/1440,'yyyy-mm-dd hh24:mi:ss') period_start,  
 to_char(((SYSDATE)-((TO_CHAR(((SYSDATE)),'MI')*60 + TO_CHAR(((SYSDATE)),'SS'))/86400) + (15*(TRUNC(TO_NUMBER(TO_CHAR(SYSDATE,'MI'))/15,'0')))/1440)-1/86400,'yyyy-mm-dd hh24:mi:ss') period_end  
 from dual  

and the output is:

as you can see, even though the current time is 11:02:31, the period start is the start of the last completed 15 minute period, 10:45:00 and the period end is the end time of the last completed period, 10:59:59.

Lets analyze the first part of the query which obtains the start time of the period:

1:  (SYSDATE-15/1440)  
2:    -   
3:  ((TO_CHAR(((SYSDATE-15/1440)),'MI')*60 + TO_CHAR(((SYSDATE -15/1440)),'SS'))/86400)   
4:    +   
5:  (15* (   
6:      CASE WHEN (  
7:                           TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'MI'))/15,'0')-1)<=0 THEN 3   
8:                 ELSE      TO_NUMBER((TRUNC(TO_NUMBER(TO_CHAR(SYSDATE,'MI'))/15,'0')-1))   
9:            END  
10:       )  
11:  )/1440  

Line 1:	The current time minus 15 minutes. As you know, when you rest units to sysdate it will deduct days, so what we need to deduct is a fraction of a day, for example, for one second is 1/86400 where 86400 is the number of seconds in a day so to deduct 15 minutes we can do 15*60 / (1440 * 60) where 1440 is the number of minutes in an hour. We can eliminate the 60 seconds and the final number is 15/1440.
Line 3:	We obtains the total number of seconds within the current hour. For example, if the time was 13:32:12, we obtain the 32 minutes and the 12 seconds so we can deduct them from the time obtained in line 1
Lines 3-11:	We obtains the period number, being 0 the first quarter, 1 the second, 2 the third and 3 the fourth
Line 7 and 8:	Obtain the number of period in the current minute. If the division by 15 results in 2 it means we are in the second period, etc..
Lines 6-9:	The case verifies that we don't fall between hours, so in case we are in the first minutes of the hour we will use the last quarter from the previous hour.
Lines 1-11:	Obtain the start of the period.

The second part, the end period is:

1:  (     (SYSDATE)  
2:            -  
3:       ((TO_CHAR(((SYSDATE)),'MI')*60 + TO_CHAR(((SYSDATE)),'SS'))/86400)   
4:            +   
5:       (15*(TRUNC(TO_NUMBER(TO_CHAR(SYSDATE,'MI'))/15,'0')))/1440  
6:  )-1/86400   

Line 1:	The current time minus 15 minutes. As you know, when you rest units to sysdate it will deduct days, so what we need to deduct is a fraction of a day, for example, for one second is 1/86400 where 86400 is the number of seconds in a day so to deduct 15 minutes we can do 15*60 / (1440 * 60) where 1440 is the number of minutes in an hour. We can eliminate the 60 seconds and the final number is 15/1440.
Lines 1-6:	Computes the end time of the period
Line 3:	Obtains the total number of seconds within the current hour so if the time is 10:42:15 it will obtain the number of seconds that 42:15 represent
Line 5:	Obtains the number of seconds based on the period number. If the division by 15 results in 2 it means we are in the second period and the total number of minutes is 30
Lines 1-6:	We deduct one second so that the end period will be the minute and 59 seconds.

Step by Step Access Your Facebook Page Information and Statistics

Although Facebook publishes information on how to use their API, there are not enough examples and the information on how to set up everything is not very clear. I have found many questions/answers in different web sites asking on how to do this and how to do that.

It is highly recommended that you read Facebook's Graph API.

The intention of this post is to help you get started and the first thing to do is to have a Facebook page. Once you have your page and some likes on it follow the next steps:

1. Go to https://developers.facebook.com/ and login
2. Register as a Developer
   
   
   
   
   
3. Once you are registered you will be able to add apps to Facebook, this is needed in order to extract information.
4. Click on "Add a New App" if is not open, otherwise just choose "WWW" Website.
   
   
   
   
5. Click on "Skip and Create App ID"
   
   
   
   
6. On the next screen, select an App name, in the category select Application for Pages and then click on Create Application Identifier.
   
   
   
   
7. You application Dashboard will appear, from there you can get your application ID as well as your Application Secret ID.
   
   
   
   
8. Now you have your application ID that you can use to login to Facebook.

The Graph API is a low level HTTP based API that allows you to query data, post new stories, upload photos and other tasks. The API will work on any programming language or tool that supports HTTP such as cURL and urllib. In our examples, we are not going to use any specific tool but row HTTP and since the interface uses SSL we are going to use  openssl in order to open a raw socket.

Every element in Facebook is called a Node and each node has an ID, you need those IDs in order to extract information from the. Please refer to the Graph API for more details on this.

You can get your node id by looking at the address bar or when you put your mouse over an object in the status bar.

For using openssl I am using a Cygwin terminal. 

The first thing we need to do is to get the access key, that key will allow us to use the rest of the APIs.

For getting the access key I will send the following command:

GET /oauth/access_token?client_id=<app id>&client_secret=<app secret>&grant_type=client_credentials HTTP/1.1

Host: graph.facebook.com

Where the <app id> and the <app secret> were taken from the Application Dashboard. 

Note that you need to press Enter twice after you enter the last line:

GET /oauth/access_token?client_id=<app id>&client_secret=<app secret>&grant_type=client_credentials HTTP/1.1

Host: graph.facebook.com

<crlf><crlf>

Lets do an example on how to obtain the access_token:

1. Connect to Facebook API:  openssl s_client -connect graph.facebook.com:443
   After issuing this command, many printouts  appear in the screen
2. Then we type or better just copy an paste the following, remember to update your application id and secret id, the onces below are fake (press Enter twice):
   GET /oauth/access_token?client_id=5153436434343&client_secret=43434h4347851fb&grant_type=client_credentials HTTP/1.1Host: graph.facebook.com
   
   

Once you are connected you can use the API in the same manner, lets do another example but now lets retrieve the page information. 

Please follow the next steps:

1. Connect to Facebook's API (following the previous steps), if you are already connected, just copy the obtained access_token .
2. Update the following request with the obtained access token and with your node (page) id:
   
   GET /v2.5/170838892958036?access_token=515365081974110|5gM5WkAfUZLUm7f2n8t0_icPqb0 HTTP/1.1Host: graph.facebook.com
3. Copy and paste it to the terminal, remember to put two Enters at the end.
   
   
   
   When you paste the request it will be next to the access_token, see the underlined text, do not worry, a space or an enter is not necessary because the previous HTTP response includes a specific number of chars, so you can paste the request there. The text with the green line to the left is the HTTP response headers and the text with the blue line is the API response.
   
   {"name":"Visita Mexico","id":"170838892958036"}

You will have access to public available information and this is because of the permissions you have. If you try to request information for a metric or parameter that your do not have permissions you may get empty data, for example, if we want to retrieve the total number of people who have liked the page we need to use the following request:

GET /v2.5/170838892958036/insights/page_fans?access_token=fUZLUm7f2n8t0_icPqb0 HTTP/1.1
Host: graph.facebook.com 

Where the number 170838892958036 is the node(page) id and the access_token is the one previously obtained, since I do not have permissions I will get the following:


{"data":[],
"paging":
{"previous":"https:\/\/graph.facebook.com\/v2.5\/170838892958036\/insights\/page_fans?access_token=515365081974110\u00257C5gM5WkAfUZLUm7f2n8t0_icPqb0&since=1449087470&until=1449346670",
"next":"https:\/\/graph.facebook.com\/v2.5\/170838892958036\/insights\/page_fans?access_token=515365081974110\u00257C5gM5WkAfUZLUm7f2n8t0_icPqb0&since=1449605870&until=1449865070"
}
}

Please ignore the access_token since it is not the real one but note that the response contains "data": []  which is empty, the expected one should look like:


{
  "data": [
    {
      "name": "page_fans",
      "period": "lifetime",
      "values": [
        {
          "value": 50,
          "end_time": "2015-12-05T08:00:00+0000"
        },
        {
          "value": 50,
          "end_time": "2015-12-06T08:00:00+0000"
        },
        {
          "value": 50,
          "end_time": "2015-12-07T08:00:00+0000"
        }
      ],
      "title": "Lifetime Total Likes",
      "description": "Lifetime: The total number of people who have liked your Page. (Unique Users)",
      "id": "170838892958036/insights/page_fans/lifetime"
    }
  ],
  "paging": {
    "previous": "https://graph.facebook.com/v2.5/170838892958036/insights/page_fans?access_token=CAACEdEose0cBADYixYmUPSmjTZBAjNTDyZCBm5omZAQV6KFwEZBWBM6XUVSr7p3ClYiFHcaocjs3d323nAExn2Sn3pOPKudsZC43wRFYOVON5VitrPUEvHbKIFofb3o1TAJteaCqSxZCo9Pf0Fda6VGqizeKE00ZAJ2P8swfhyS9FKbGBvRZBXqpZAYezP2ZALal8DQ4Uz95iITI9d2OWzbJzs&debug=all&format=json&method=get&pretty=0&suppress_http_code=1&since=1448913822&until=1449173022",
    "next": "https://graph.facebook.com/v2.5/170838892958036/insights/page_fans?access_token=CAACEdEose0cBADYixYmUPSmjTZBAjNTDyZCBm5omZAQV6KFwEZBWBM6XUVSr7p3ClYiFHcaocjs3d323nAExn2Sn3pOPKudsZC43wRFYOVON5VitrPUEvHbKIFofb3o1TAJteaCqSxZCo9Pf0Fda6VGqizeKE00ZAJ2P8swfhyS9FKbGBvRZBXqpZAYezP2ZALal8DQ4Uz95iITI9d2OWzbJzs&debug=all&format=json&method=get&pretty=0&suppress_http_code=1&since=1449432222&until=1449691422"
  }

}

In the above response you can see that the page has 50 likes. 

There are many statistics and information that you can retrieve from Facebook, check all API options. If you are interested in metrics about your Facebook Page such as the number of stories generated about your Page, the total number of people who liked your Page, etc. look at Facebook's Insights Metrics (more info in Insights Metric). 

  

In order to get the extended permissions you have to first submit your app for review, in general the steps:

1. Go to your application dashboard.
2. Click on "Status & Review"
3. Click on "Start a Submission"
   
   
   
   
   
4. You will have to fill the form and follow all the steps. The approval process takes around 7 business days.

PHP Restrict Objects Access based on Security Profiles

When you are building a web application or a web site and you want to restrict access to pages, links, buttons, etc. you can take many approaches, one approach may be to hide the objects you do not want the user to have access to, another approach is to gray/disable objects that are restricted. Both approaches have their advantages and disadvantages. I know customers that do not like things to disappear, they like more a homogeneous interface and some other customers do not like to see options that are not available. So which approach is the best, it depends on your customer and on your application design.

No matter what approach you use, you need to be able to uniquely identify such objects, that way you may create different profiles with different accesses to different elements or objects, but what happens if you have hundreds or thousands objects that you want to control its access.

You can give an ID to each object and when a user logs in, it will load all the allowed IDs into a memory array and you will have to query that array for every restricted object. That may be a lot of memory. Another approach is to query the database for each of the restricted objects, but again you may need to query the database several times on each page or create an array at the beginning and query it for each object.

The approach I like is to uniquely identify each restricted object using a bit, this means that for each byte I can store 8 different objects, so to uniquely identify 80 objects I only need 10 bytes, for 500 objects I need 63 bytes (500 objects/8 bits in a byte) and for 1000 I need 125 bytes.

The object IDs will be defined using a number that is divided in two parts, the first one will identify the byte position within a string and the second one will identify the bit position within that byte. The first one can be any number from 0 till whatever, depending on the number of objects we have and the second one will go from 1 till 8, the number of bits in a byte. The below code defines 3 objects, the first byte (0) defines the position in the security string and the second byte(1,2,3) defines the bit position within that byte.

 const MAIN_PAGE = "01"; // bits: 00000001 
 const MENU_ENTRY_DETAILS = "02";  // bits: 00000010 
 const MENU_ENTRY_UPDATE = "03";  // bits: 00000100




Each user will have a string that will represent its access, that string will contain all the bits that represent all the objects the user has access to. This string is usually build based on the security profile the user has in the database. 

If the user only has access to the first two elements, its security string will be defined as:

 $security = 0b00000011;   

if we want the user to have access to the second and third the security string will be defined as:

 $security = 0b00000110;   

and full access will be:

 $security = 0b00000111;   







Let's do an example to depict this approach, we will set it to hold a maximum of 80 objects (10 bytes). The below code define the restricted objects:

 const MAIN_PAGE = "01";  
 const MENU_ENTRY_DETAILS = "02";  
 const MENU_ENTRY_UPDATE = "03";  
 const MENU_ENTRY_ADD = "04";  
 const PAGE_ORDER_DETAILS = "05";  
 const PAGE_ORDER_DETAILS_EDIT = "06";  
 const PAGE_ORDER_DETAILS_ADD  = "07";  
 const MAIN_PAGE_CHANGE_PASSWORD = "08";  
 const PAGE_USERS_VIEW = "11";  
 const PAGE_USERS_ADD = "12";  
 const PAGE_USERS_EDIT = "13";  
 const PAGE_USER_DELETE = "14";  
 const EXTERNAL_LINKS = "15";  
 const MAIN_PAGE_END_SESSION = "16";  
 const MAIN_PAGE_NEW_SESSION = "17";  

As you can see each one has two digits (in this example), the first digit represents its position within the security string and the second one represents the bit position within a byte, that way the last digits goes from 1 till 8.

When the user logs in, we need to call a function that returns its security string based on its security profile, the next function will do the job for our example:

1:       public static function getSecurityString() {  
2:            $security="";  
3:            $permissions = array_fill(0,self::M_SECURITY_STRING_LEN,0);  
4:            /* begin: this should be ontained from a DB or other source*/  
5:            $objects = array();  
6:            $objects[0] = "01"; //main page  
7:            $objects[1] = "02"; //menu entry details  
8:            $objects[2] = "05"; //page order details  
9:            $objects[3] = "06"; //page order details edit   
10:           $objects[4] = "07"; //page order details add   
11:           $objects[5] = "08"; //main page change password  
12:           $objects[6] = "11"; //page users view  
13:            /* end: this should be ontained from a DB */  
14:            for ($count=0;$count<count($objects);$count++) {  
15:                 $pos = substr($objects[$count],0,strlen($objects[$count])-1);  
16:                 $value = substr($objects[$count],strlen($objects[$count])-1) - 1;  
17:                 if ($pos < self::M_SECURITY_STRING_LEN) {  
18:                      $permissions[$pos] = $permissions[$pos] | (1 << $value);   
19:                 } else {  
20:                      //this should not happen, raise an error  
21:                 }  
22:            }  
23:            for ($count=0; $count < self::M_SECURITY_STRING_LEN; $count++) {  
24:                 $security = $security . pack("C1",$permissions[$count]);  
25:            }  
26:            return $security;  
27:       }  

Lets talk about the above code:

Line 3: initialized the security string with zeros. (no access)
Lines 4-13: Define the objects the user has access to, this will usually come from an external source such as a database. It is hard coded here for simplicity.
Line 14: Executes for every object
Line 15: Extracts the byte position from the security string
Line 16: Extracts the bit position within the byte.
Line 18: Sets the bit from the right byte of the security string
Line 23: Executes for every byte of the security string
Line 24: Builds the security string attaching all the bytes.

Once we have the security string, we can store it on a variable or session variable.

 $secStr = Security::getSecurityString();  

Now for each restricted object you will have code similar to the below one:

1:  if (Security::isAllowed($secStr,Security::PAGE_USER_DELETE)) {  
2:       echo "access is granted";  
3:       //execute you restricted code  
4:  } else {  
5:       echo "access is denied";  
6:       //execute the code when access is denied or hide/gray the elements  
7:  }  

The function isAllowed is defined as follows:

1:  public static function isAllowed($_security, $_ID) {  
2:            $pos=0;  
3:            $value=0;  
4:            $secValue=0;  
5:            $secString=0;  
6:            $pos = substr($_ID,0,strlen($_ID)-1);  
7:            $value = substr($_ID,strlen($_ID)-1) - 1;  
8:            $secArray = unpack('C1', $_security{$pos});  
9:            $secString = $secArray[1];  
10:            $secValue = (1 << $value);  
11:            if ( ($secString & $secValue) != 0) {  
12:                 return true;  
13:            } else {  
14:                 echo "Access Denied to object: " . $_ID;  
15:            }  
16:            return false;  
17:       }  

Line 6: Retrieves, from the element ID, the byte position within the security string.
Line 7: Retrieves, from the element ID,  the bit position within the byte.
Line 8-9: From the security string, retrieves the byte based on the previously obtained position. 
Line 10: Based on the bit position obtained in line 7, we create a byte with that bit set.
Line 11: Compares the bit set in the security string with the one of the element ID.
Line 12: If the value is not zero is allowed.

You need to add some validations in these functions that were omitted for simplicity, for example, in the isAllowed function, you need to make sure that the position is not bigger than the length of the security string.

The definition of the Security class is as follows:

1:  class Security {  
2:       private $m_security="";  
3:       const M_SECURITY_STRING_LEN = 10;  
4:       const TOTAL_OBJECTS = 15;  
5:       const MAIN_PAGE = "01";  
6:       const MENU_ENTRY_DETAILS = "02";  
7:       const MENU_ENTRY_UPDATE = "03";  
8:       const MENU_ENTRY_ADD = "04";  
9:       const PAGE_ORDER_DETAILS = "05";  
10:       const PAGE_ORDER_DETAILS_EDIT = "06";  
11:       const PAGE_ORDER_DETAILS_ADD  = "07";  
12:       const MAIN_PAGE_CHANGE_PASSWORD = "08";  
13:       const PAGE_USERS_VIEW = "11";  
14:       const PAGE_USERS_ADD = "12";  
15:       const PAGE_USERS_EDIT = "13";  
16:       const PAGE_USER_DELETE = "14";  
17:       const EXTERNAL_LINKS = "15";  
18:       const MAIN_PAGE_END_SESSION = "16";  
19:       const MAIN_PAGE_NEW_SESSION = "17";  
20:       public static function getSecurityString() {  
21:            $security="";  
22:            $permissions = array_fill(0,self::M_SECURITY_STRING_LEN,0);  
23:            /* begin: this should be ontained from a DB or other source*/  
24:            $objects = array();  
25:            $objects[0] = "01"; //main page  
26:            $objects[1] = "02"; //menu entry details  
27:            $objects[2] = "05"; //page order details  
28:            $objects[3] = "06"; //page order details edit   
29:            $objects[4] = "07"; //page order details add   
30:            $objects[5] = "08"; //main page change password  
31:            $objects[6] = "11"; //page users view  
32:            /* end: this should be ontained from a DB */  
33:            for ($count=0;$count<count($objects);$count++) {  
34:                 $pos = substr($objects[$count],0,strlen($objects[$count])-1);  
35:                 $value = substr($objects[$count],strlen($objects[$count])-1) - 1;  
36:                 if ($pos < self::M_SECURITY_STRING_LEN) {  
37:                      $permissions[$pos] = $permissions[$pos] | (1 << $value);   
38:                 } else {  
39:                      //this should not happen, raise an error  
40:                 }  
41:            }  
42:            for ($count=0; $count < self::M_SECURITY_STRING_LEN; $count++) {  
43:                 $security = $security . pack("C1",$permissions[$count]);  
44:            }  
45:            return $security;  
46:       }  
47:       public static function isAllowed($_security, $_ID) {  
48:            $pos=0;  
49:            $value=0;  
50:            $secValue=0;  
51:            $secString=0;  
52:            $pos = substr($_ID,0,strlen($_ID)-1);  
53:            $value = substr($_ID,strlen($_ID)-1) - 1;  
54:            $secArray = unpack('C1', $_security{$pos});  
55:            $secString = $secArray[1];  
56:            $secValue = (1 << $value);  
57:            if ( ($secString & $secValue) != 0) {  
58:                 return true;  
59:            } else {  
60:                 echo "Access Denied to object: " . $_ID;  
61:            }  
62:            return false;  
63:       }  
64:  }  

With this approach, you can restrict access to many objects using little memory and I/O. I have use it in a Java environment and it works very good. This PHP version has not been tested, so please let me know if you find issues or improvements.