They both are on different databases and don't talk to each other and do not have any special back door access.
Internally we have opened up readonly access to MySQL databases that houses our blogs (on mediatemple) so it is easy for us to issue direct SQL commands against our wordpress database but for SAAS applications the databases are sealed off from the outside world.
In order to derive real time metrics from our MockupTiger web app we followed the above approach of HTTP posting + Sql bridge.
1.1 we define three hard coded variables $login, $pass and $api
1.2 Next, we collect the user submitted POST variables
1.3 We simply validate the POST variable contents to the hard coded values
If they don't match we do not process further
If they match we continue forward
1.4 We define the database variables for connecting to a MySQL database. You can choose to connect to any database of your choice and implement your own procedure how you connect and talk to it.
Once authenticated, we intepret another post variable ( $_POST["x"] ) content to determine what type of data we need to return
NOTE: We are generating the SQL statements inside our sql_bridge.php file depending on the "x" variable.
In the next step (#2) we call the mysql_result_x with the sql statement and collect the output in the $output variable
Next, we convert the output to JSON and return it to the browser.
Here is how the mysql routine is defined and you can define your own routine for other databases.
Now we place the sql_bridge.php file on our webserver and maybe bury it under a sub-directory.
In this tutorial, we simply placed it in subdirectory named "restricted"
By calling this URL it shows blank page, no information is revealed.
Now we define this URL within infocaptor so it can securely pass the POST variables and display data on the dashboard
How to define connection for HTTP Post within InfoCaptor
Within InfoCaptor - Goto Actions and select "Manage Data Sources/Connections"
The connection definition URL accepts the number of fields (nof) variable for http post
If you don't specify nof value then the default value is 3 POST parameters
For e.g if you specify nof=1 then only one parameter name/value pair can be defined
In our case we need only three post variables User, Password and API
But wait, don't we need one more post variable for the "x"?
You are right but we don't need to define it here and will address that shortly
Let us define our connection for HTTP post
1. Give a appropriate connection handle (my_webapp_metrics)
2. Provide the HTTP URL (http://localhost/restricted/sql_bridge.php)
3. Select output Data type. We know that sql_bridge.php returns JSON data (see Fig 3 , bullet point #3)
4. Parameter name = user and Parameter Value = whoareyou
5. Parameter name = pass and Parameter Value =keepguessing
4. Parameter name = api and Parameter Value = DWWRDD366546sdscsd39239ExesTBSD
Click on "Add new Connection Entry"
Now we can use this connection handle inside our dashboard widgets.
1. Create a new page
Since we know that we are getting only count values we can display them on the icon widgets
2. Drag four icons to the canvas (we just chose the rounded squares)
3. Right click on the first one and select "Data Source"
4. Select the connection handle we just defined (my_webapp_metrics)
5. The parameters section is optional for http_post. But here is an interesting piece. Any new variable you define in the parameter section is automatically sent as a post variable along with the rest of the http post URL and whatever you defined in the connection settings.
So this is where we can define the "x" post variable.
And you will see the number populated on the widget icon
Similarly populate the remaining icons
So each icon widget is representing the count from the sql_bridge output.
HTTP Post is a powerful way to interface any system
Pass any number of post variables
Define custom post variables on the fly for each widget
Output can be html, xml, json or just text
In case you are wondering why we did not define the "x" post variable as part of the connection here is a brief explanation.
The connection is suppose to authenticate the request is coming from a known location. So we define the connection using user, password and api variables.
If we had defined the variable "x" in the connection template then we would end with different connections for each different kind of information we wish to display.
The above setup gives us the flexibility of keeping some part of the connection constant and change part of it on the fly.