SQWareWeb:general

Presentation

Usage

It is the part of 'graphic' restitution of expdbTools® 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

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

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"

tableaux

Les graphiques sont générés avec le framework ajax ActiveWidgets 2.5 qui est soumis à licence. Celle-ci a été acquittée par les développeurs de expdbTools et est royalty free distribution of Software in any applications. Pour plus de détail sur la cette licence.
ActiveWidgets se trouve dans $gvsqw_RootexpdbWebdba/js/ActiveWidgets/*
Le tout est encapsulé pour SQWareWeb dans $gvsqw_RootexpdbWebdba/scripts/table.php

Il suffit alors de créer un fichier de configuration du type *.table pour générer le tableau.
Le fichier doit contenir les sections suivantes:

• common
• db
• cols
• format

Et de manière optionnelle:

• link
• targetlink
• format_number

Exemple de fichier de configuration (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"