SQWareWeb:general

Presentation

Usage

SQWareWeb, is the part of 'graphic' restitution of dbSQWare for all RDBMS.
It's a web module make to work with apache 2.x et PHP 5.x.
It is written in PHP, javascript 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.
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 c3.js .
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"