SQWareWeb:general

From wiki_dbSQWare_3.1
Jump to: navigation, search

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


Nuvola apps important.png
Warning:
Do not change the standard configuration files of dbSQWare.

Use directories $gvsqw_RootSQWareWeb/custom/* for customizations. This will facilitate version upgrades.


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

General SQWareWeb.jpg

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"
Nuvola apps information.png
Note:
When you have generation problem, you can use trace flag in [db] section :
'''debug=true'''
'''debug_before_connectdb=true'''
'''debug_connectdb_error=true'''
.


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"
Nuvola apps information.png
Note:
When you have generation problem, you can use trace flag in [db] section :
'''debug=true'''
'''debug_before_connectdb=true'''
'''debug_connectdb_error=true'''
.