Difference between revisions of "SQWareWeb:general"

From Wiki_dbSQWare
Jump to: navigation, search
(Page créée avec « == présentation == === utilisation === C'est la partie de restitution 'graphique' de expdbTools pour tous les SGBDs.<br> C'est un module web fait pour fonctionner avec apach… »)
 
(→‎General architecture diagram SQWareWeb™ (when the SQWareProduction™ module is locally installed))
 
(38 intermediate revisions by the same user not shown)
Line 1: Line 1:
−
== présentation ==
+
== Presentation ==
−
=== utilisation ===
+
=== Usage ===
−
C'est la partie de restitution 'graphique' de expdbTools pour tous les SGBDs.<br>
+
SQWareWeb, is the part of 'graphic' restitution of dbSQWare for all RDBMS.<br>
−
C'est un module web fait pour fonctionner avec apache 2 et PHP 5.2 (non compatible 5.3!).<br>
+
It's a web module make to work with apache 2.x et PHP 5.x.<br>
−
Il est écrit en PHP, ajax, OpenFlash et SQL.<br>
+
It is written in PHP, javascript and SQL.<br>
−
SQWareWeb met en forme (graphiques ou tableaux) les données remontées dans [[SQWareRepository:general|SQWareRepository]] par un grande partie des scripts du module [[SQWareProduction:general|SQWareProduction]]. La quasie totalité des données présentées sont dynamiquement générées (volumétries, versions, ...).<br>
+
SQWareWeb formats (graphs or tables) datas uploaded in [[SQWareRepository:general|SQWareRepository]] by many scripts of [[SQWareProduction:general|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.<br>
 
<br>
 
<br>
−
Exemples de pages présentes sur le site:
+
Examples of pages presented :
−
*Inventaire pour chaque SGBD avec volumétrie, version, enplacement, environnement ...
+
*Inventory for all RDBMS with volumetry, version, environnement, ...
−
*Répartition des instances en nombre/volumétrie par environnement/client/version
+
*Distribution of instances in number/volumetry per environnement/client/version
−
*Détail des volumétries (FS, databases, TBS, tables, ...)
+
*Detail of volumetry (FS, databases, TBS, tables, ...)
−
*Trace des passages des scripts de [[SQWareProduction:general|SQWareProduction]] (début, fin, options, durée, code retour, ...)
+
*Trace of scripts executions of [[SQWareProduction:general|SQWareProduction]] (beginning, end, options, duration, return code, ...)
−
*Données de performances
+
*Performances datas
 +
*Capacity planning
 
*...
 
*...
 +
<br>
 +
[http://webdba.dbsqware.com/ Presentation model of SQWareWeb.]
 +
<br>
 +
[[Installation#SQWareWeb| Installation guide for SQWareWeb]]<br>
 +
<br>
 +
 +
=== 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
 +
<br>
 +
{{Warning | Do not change the standard configuration files of dbSQWare.
 +
Use directories $gvsqw_RootSQWareWeb/custom/* for customizations. '''This will facilitate version upgrades'''}}
 +
<br>
 +
 +
=== General architecture diagram SQWareWeb (when the SQWareProduction module is locally installed) ===
 +
[[Image:General_SQWareWeb.jpg]]
 +
 +
== Working ==
 +
=== Rewrite ===
 +
The site works with URL rewriting with $gvsqw_RootSQWareWeb/.htaccess<br>
 +
File contents:
 +
<pre>
 +
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]
 +
</pre>
 +
 +
=== Graphics ===
 +
Graphics are generated with c3.js .<br>
 +
The whole is encapsulated for SQWareWeb in $gvsqw_RootSQWareWeb/js/chart.php<br>
 +
<br>
 +
You just have to write a configuration file like *.chart to generate graphic.<br>
 +
File must contain:
 +
*common
 +
*db
 +
*y_axis
 +
*background
 +
*x_axis
 +
*data_[0-9]
 +
<br>
 +
Example of configuration file (GrowthBases.chart):
 +
<pre>
 +
[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"
 +
</pre>
 +
{{Information|<pre>When you have generation problem, you can use trace flag in [db] section :
 +
'''debug=true'''
 +
'''debug_before_connectdb=true'''
 +
'''debug_connectdb_error=true'''
 +
</pre>}}
 +
 +
=== Tables ===
 +
Tables are generated with jQuery framework (DataTable).<br>
 +
The whole is encapsulated for SQWareWeb in $gvsqw_RootSQWareWeb/js/table.php<br>
 +
<br>
 +
You just have to write a configuration file like *.table to generate tables.<br>
 +
File must contain:
 +
*common
 +
*db
 +
*cols
 +
*format
 +
Et de manière optionnelle:
 +
*link
 +
*targetlink
 +
*format_number
 +
<br>
 +
Example of configuration file (GrowthTables.table):
 +
<pre>
 +
[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"
  
−
=== explication de l'arborescence ===
+
[format]
−
Arborescences se trouvant dans $gvsqw_RootexpdbWebdba:
+
; 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"
 +
</pre>
 +
{{Information|<pre>When you have generation problem, you can use trace flag in [db] section :
 +
'''debug=true'''
 +
'''debug_before_connectdb=true'''
 +
'''debug_connectdb_error=true'''
 +
</pre>}}

Latest revision as of 20:08, 22 December 2017

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


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 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"
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'''
.
Retrieved from "https://wiki.dbsqware.com/index.php?title=SQWareWeb:general&oldid=9191"