SQWareWeb:general

Presentation

Usage

SQWareWeb™, also known technically as SQWareWeb®, is the part of 'graphic' restitution of dbSQWare™ for all RDBMS.
It's a web module make to work with apache2 et PHP 5.2 (not compatible 5.3!).
It is written in PHP, ajax, OpenFlash and SQL.
SQWareWeb formats (graphs or tables) datas uploaded in SQWareRepository™ by many scripts of SQWareProduction™. Almost all of the data presented are dynamically generated (volumes, versions, ...), only the data type repository or settings are stored manually in the relevant tables.

Examples of pages presented :

• Inventory for all RDBMS with volumetry, version, environnement, ...
• Distribution of instances in number/volumetry per environnement/client/version
• Detail of volumetry (FS, databases, TBS, tables, ...)
• Trace of scripts executions of SQWareProduction™ (beginning, end, options, duration, return code, ...)
• Performances datas
• Capacity planning
• ...

Presentation model of SQWareWeb™.
Download the latest stable versions
Installation guide for SQWareWeb™

Explanation of directories

Directories located in $gvsqw_RootexpdbWebdba/ :

• cache : cache system files
• css : Stylesheets
• custom : Custom files for configuration
• custom_ref : This is the model of what should be in custom
• htdocs : Directory containing pages
• images : Directory containing images
• js : Directory containing ajax librairies
• lib : Directory containing template engine
• scripts : Main generators of pages (graphics et tables)
• templates : Left menus

General architecture diagram SQWareWeb™ (when the SQWareProduction™ module is locally installed)

Working

Rewrite

The site works with URL rewriting with $gvsqw_RootexpdbWebdba/.htaccess
File contents:

RewriteEngine On
RewriteRule ^/sqw_oracle_awr/(.*)$ /sqw_oracle_awr/$1 [QSA,L,PT]
RewriteRule ^/sqw_mysql_slow/(.*)$ /sqw_mysql_slow/$1 [QSA,L,PT]
RewriteRule ^/sqw_sybase_checkmep/(.*)$ /sqw_sybase_checkmep/$1 [QSA,L,PT]
RewriteRule ^.+/images/(.*)$ /images/$1 [QSA,L,PT]
RewriteRule ^.+/js/(.*)$ /js/$1 [QSA,L,PT]
RewriteRule ^.+/timeplot/(.*)$ /timeplot/$1 [QSA,L,PT]
RewriteRule ^.+/timeline/(.*)$ /timeline/$1 [QSA,L,PT]
RewriteRule ^.+/ajax/(.*)$ /ajax/$1 [QSA,L,PT]
RewriteRule ^.+/css/(.*)$ /css/$1 [QSA,L,PT]
RewriteRule ^(.*).table$ /scripts/table.php?path=/$1 [QSA,L,PT]
RewriteRule ^(.*).chart$ /scripts/chart.php?path=/$1 [QSA,L,PT]
RewriteCond %{REQUEST_URI} !^/sqw_oracle_awr/
RewriteCond %{REQUEST_URI} !^/sqw_mysql_slow/
RewriteCond %{REQUEST_URI} !^/sqw_sybase_checkmep/
RewriteRule ^(.*)/(.*.html)(\?.*)?$ /scripts/engine.php?path=$1/$2 [QSA,L,PT]
RewriteRule ^(.*)/$ /scripts/engine.php?path=/$1/ [QSA,L,PT]
RewriteRule ^$ /scripts/engine.php?path=/ [QSA,L,PT]

Graphics

Graphics are generated with OpenFlashChart which is under LGPL licence.
Lib is in $gvsqw_RootexpdbWebdba/lib/ofc/open-flash-chart.php
The whole is encapsulated for SQWareWeb in $gvsqw_RootexpdbWebdba/scripts/chart.php

You just have to write a configuration file like *.chart to generate graphic.
File must contain:

• common
• db
• y_axis
• background
• x_axis
• data_[0-9]

Example of configuration file (GrowthBases.chart):

[common]
title="Growth database {DbName} for {Instance}"

[db]
sql="select vbd.DataServer, convert(char(10),vbd.Gather_date,103) as gather_date, vbd.Database_name,
vbd.Size_db as size_db, vbd.Used as used, vbd.Size_db-vbd.Used as free
  from tsqw_VolDb vbd
where vbd.DataServer = '{Instance}'
and vbd.Database_name = '{DbName}'
order by vbd.Gather_date
"

[y_axis]
legend="Volumetry Mo"
min=0
;max=60000
;step=5000

[background]
color="#ffffff"

[x_axis]
field="gather_date" ; field to display in X
legend="Date"
orientation="verticale" ; verticale or horizontal(by default)
size="12" ; in pixels
step=5
legendstep=10
;maxdata=20

[data_1]
field="size_db" ; field to trace on curve
type="line" ; type of graph line, bar, bar_3d, pie, bar_glass, bar_sketch, line_dot (by default)
colour="#0000ff"
pointsize=2 ; in pixel
text="Size of database"

[data_2]
field="used" ; field to trace on curve
type="line"
colour="#ff4500"
pointsize=2 ; in pixel
text="Used"

[data_3]
field="free" ; field to trace on curve
type="line"
colour="#32cd32"
pointsize=2 ; in pixel
text="Free"

Tables

Tables are generated with AJAX framework ActiveWidgets 2.5 which is licensed. This was paid by expdbTools developers and is royalty free distribution of Software in any applications. For more details on this licence.
ActiveWidgets is in $gvsqw_RootexpdbWebdba/js/ActiveWidgets/*
The whole is encapsulated for SQWareWeb in $gvsqw_RootexpdbWebdba/scripts/table.php

You just have to write a configuration file like *.table to generate tables.
File must contain:

• common
• db
• cols
• format

Et de manière optionnelle:

• link
• targetlink
• format_number

Example of configuration file (GrowthTables.table):

[common]
title="Growth {Owner}.{TableName} in {DbName}@{Instance}"

[db]
sql="select vbd.DataServer, convert(char(19),vbd.Gather_date,23) as gather_date, vbd.Database_name,
vbd.Owner, vbd.TableName, vbd.Size_tb
  from tsqw_VolTables vbd
where vbd.DataServer = '{Instance}'
	and vbd.Database_name='{DbName}'
	and vbd.Owner='{Owner}'
	and vbd.TableName='{TableName}'
order by vbd.Gather_date desc
"
;debug=true

[cols]
; Headers of columns
; Attention \' for quotes ...
col_1="Dataserver"
col_2="Date"
col_3="Database"
col_4="Owner"
col_5="TableName"
col_6="Size in Mo"

[format]
; Format of columns : string, number or date
col_1="string"
col_2="date"
col_3="string"
col_4="string"
col_5="string"
col_6="number"