SQWareWeb:general

Presentation

Usage

SQWareWeb™, also known technically as expdbWeb™, 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 or 5.3.
It is written in PHP, javascript, HTML5 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_RootSQWareWeb/ :

• 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 javascript 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_RootSQWareWeb/.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 ^.+/css/(.*)$ /css/$1 [QSA,L,PT]
RewriteRule ^(.*).table$ /lib/table.php?path=/$1 [QSA,L,PT]
RewriteRule ^(.*).chart$ /lib/chart.php?path=/$1 [QSA,L,PT]
RewriteRule ^(.*).raw$ /lib/raw.php?path=/$1 [QSA,L,PT]
RewriteRule ^(.*)/(admin.*)_passwd.html(\?.*)?$ /lib/$2.php?path=$1/$2_passwd.html [QSA,L,PT]
RewriteRule ^(.*)/(admin.*)_detail.html(\?.*)?$ /lib/$2.php?path=$1/$2_detail.html [QSA,L,PT]
RewriteRule ^(.*)/(admin.*)_general.html(\?.*)?$ /lib/$2.php?path=$1/$2_general.html [QSA,L,PT]
RewriteCond %{REQUEST_URI} !^/sqw_oracle_awr/
RewriteCond %{REQUEST_URI} !^/sqw_mysql_slow/
RewriteCond %{REQUEST_URI} !^/sqw_sybase_checkmep/
RewriteRule ^(.*)/(.*.html)(\?.*)?$ /lib/engine.php?path=$1/$2 [QSA,L,PT]
RewriteRule ^(.*)/$ /lib/engine.php?path=/$1/ [QSA,L,PT]
RewriteRule ^$ /lib/engine.php?path=/ [QSA,L,PT]

Graphics

Graphics are generated with hightcharts.
The whole is encapsulated for SQWareWeb in $gvsqw_RootSQWareWeb/js/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 jQuery framework (DataTable).
The whole is encapsulated for SQWareWeb in $gvsqw_RootSQWareWeb/js/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"