Business Integration Technology has just released SQL ICE, the Internet Computing Environment for SQL Experts. Although SQL ICE can do a lot of things, one that may be of interest to Drupal Users is the ability to connect to your Drupal database and write MySQL queries to get reports that can be displayed, downloaded, and shared with other users.
All you need to do is add a Connection to the target database and write a query in SQL. The data is exposed as an XML document accessed by a REST Web service. For example, a simple query like:
Select
from_unixtime(timestamp) as "Time",
concat('***', substr(hostname, 4)) as "Source IP (obfuscated)",
title as "Web Page Title",
path as "Drupal Path",
left(url, 80) as URL,
Accesslog.uid as "User ID",
case
when Role.name is not null then Role.name
when Accesslog.uid <> 0 then "authenticated user"
else "anonymous"
end as "Role",
timer as "Page Load Time"
from Accesslog
left outer join Users_Roles on Users_Roles.uid=Accesslog.uid
left outer join Role on Role.rid = Users_Roles.rid
where from_Unixtime(Accesslog.timestamp) > current_datemay be accessed from a URL like:
http://demo.sqlice.com/sqlice/app/query/DrupalAccesslog/output/WEBDISPLAY
(note: this URL actually works. This query is live and runs on our demo Web site.)
(The (non-privileged) user login is username: notadmin, password: Notadmin1. Please contact us if you would like an Admin login.)
It returns an XML doc like:
<?xml version="1.0" encoding="UTF-8" ?>
- <query name="DRUPAL ACCESSLOG" resultSize="402">
- <row number="1">
<column name="Drupal_Path" value="user/register" type="String" />
<column name="Page_Load_Time" value="608" type="Long" />
<column name="Role" value="Anonymous" type="String" />
<column name="Source_IP_(obfuscated)" value=".8.253.100" type="String" />
<column name="Time" value="2011-01-12 09:42:07.0" type="Timestamp" />
<column name="URL" value="http://businessintegrationtechnology.com/user/register" type="String" />
<column name="User_ID" value="0" type="Long" />
<column name="Web_Page_Title" value="User account" type="String" />
</row>
- <row number="2">
<column name="Drupal_Path" value="node/48" type="String" />
<column name="Page_Load_Time" value="1476" type="Long" />
<column name="Role" value="Anonymous" type="String" />
<column name="Source_IP_(obfuscated)" value=".8.253.100" type="String" />
<column name="Time" value="2011-01-12 09:41:28.0" type="Timestamp" />
<column name="URL" value="http://businessintegrationtechnology.com/wordpress/?p=72" type="String" />
<column name="User_ID" value="0" type="Long" />
<column name="Web_Page_Title" value="Business Integration Technology content that has moved" type="String" />
</row>
</query>You can download SQL ICE and use it for any legal purpose, either personal or commercial, absolutely free. Visit http://sqlice.com/ for information and a link to download.
Post questions to me, to info@businessintegrationtechnology.com, or to our SQLICE Forum at http://businessintegrationtechnology.com/forum/14.
Comments
SQL ICE 1.1 with ETL
Business Integration Technology has now released version 1.1 of SQL ICE that includes the ability to extract data (possibly summarized and categorized) from one database with a SQL query and push it to another database using another SQL query. This can make the Drupal mySQL database much more useful by enabling users to push data to it from an enterprise system or to extract Drupal data and push it to enterprise systems. This ETL (extract/transform/load) capability complements SQL ICE's reporting and Web application development capabilities to make a complete Internet computing environment. SQL ICE version 1.1 is free for all legal purposes, including commerical use. Download it at http://BusinessIntegrationTechnology.com/Download_SQLICE.html . Very simple registration (name and e-mail) is required.