Joan: /* Fer els informes de vendes */

2011-04-29T15:11:32Z

Fer els informes de vendes

Pàgina nova

==Objectius: Automating PostgreSQL Tasks==
Anem a automatitzar tasques administratives amb diferents tècniques i scripts. Concretament, utilitzaràs scripts fets amb SQL, bash i Perl, i diferents opcions de la comanda psql. Aquesta pràctica és un exemple de com els administradors de sistema i DBAs han de conèixer i no tenir por a treballar en la consola per tal d'automatitzar tasques i estalviar-se feina.

==Desenvolupament Pràctica==
*http://www.onlamp.com/pub/a/onlamp/2004/12/09/pg_automation.html

===omplir la taula PRODUCTS amb valors que vénen en format CSV===
Per començar a treballar, primer de tot hem de crear en el postgres la taula '''products'''. En l'exemple s'utilitza la base de dades ''postgres'' i l'usuari ''postgres''. Fés servir el script ''creacio_taula_productes.sql''.

Ja tenim la taula. Les dades estan en el fitxer products.csv, en format CSV. En aquest cas només hi ha tres files, però n'hi podria haver centenars o milers. Per convertir el format CSV al format INSERT INTO que entén el Postgres, utilitzem un script fet amb llenguatge PERL: create_input_sql.pl. Aquest script l'integrem en el script bash ''load_new_products.sh'', de manera que ara ja tenim tot el procés: com a origen tenim el fitxer products.csv, i com a destí hem de comprovar que s'han importat les dades en la taula PRODUCTS.
<pre>
$ sudo ./load_new_products.sh
</pre>
on s'executa la comanda:
<pre>
./create_input_sql.pl products.csv
</pre>
Per comprovar el bon funcionament del procés podem editar els fitxer load_new_products.log i runme.postgresql.

===Fer els informes de vendes===
Primer de tot, dades d'exemple per a la taula sales (vendes):
<pre>
insert into sales (first_name, last_name, city, state, zip, email_address, product_id, product_name, product_sale_date) values ('John', 'Hancock', 'Medford', 'MA', '02145', 'john_hancock@pretendmail.com', 234, 'The Signature Widget', '2004-10-13');

insert into sales (first_name, last_name, city, state, zip, email_address, product_id, product_name, product_sale_date) values ('Bob', 'Smith', 'Boston', 'MA', '02123', 'bob_smith@pretendmail.com', 234, 'The Bigger Widget', '2004-10-14');

insert into sales (first_name, last_name, city, state, zip, email_address, product_id, product_name, product_sale_date) values ('Jane', 'Doe', 'Somerville', 'MA', '02144', 'jane_doe@pretendmail.com', 234, 'The Nuclear Widget', '2004-10-15');

insert into sales (first_name, last_name, city, state, zip, email_address, product_id, product_name, product_sale_date) values ('John', 'Adams', 'Cambridge', 'MA', '02122', 'john_adams@pretendmail.com', 234, 'The Beer Widget', '2004-10-16');

insert into sales (first_name, last_name, city, state, zip, email_address, product_id, product_name, product_sale_date) values ('Joe', 'Random', 'New York', 'NY', '02122', 'john_smith@pretendmail.com', 234, 'The Stylish Widget', '2004-10-17');
</pre>
Ara ja podem fer els informes. Els farem amb un script on li passis dos paràmetres, les dates d'inici i fi a partir de les quals fer el report. Per exemple:

<pre>
./gen_sales_report_param.sh 2004/10/14 2004/10/16
</pre>

<pre>
#!/bin/sh

# gen_sales_report.sh
PGUSER=postgres
PGPASSWORD=postgres
PGHOST=localhost
PGPORT=5432

export PGUSER PGPASSWORD PGHOST PGPORT

psql --set start_date=\'$1\' \
--set end_date=\'$2\' \
-f generate_monthly_sales_report.postgresql sales_db

#reset PGUSER and PGPASSWORD
PGUSER=""
PGPASSWORD=""
PGHOST=""
PGPORT=""
export PGUSER PGPASSWORD PGHOST PGPORT

#End
</pre>

on el fitxer '''generate_monthly_sales_report.postgresql''' és:
<pre>
-- generate_monthly_sales_report.postgresql
--
-- Outputs one month's-worth of sales to a CSV file that
-- is easily imported into spreadsheet software.

\o report.csv
\a
\f ,
select product_id, product_name, product_sale_date from sales where
product_sale_date >= :start_date and product_sale_date <= :end_date;
\o
</pre>

L'informe que heu d'obtenir en el fitxer '''report.csv''' és similar a:
<pre>
product_id,product_name,product_sale_date
234,The Big Widget,2004-10-13
234,The Signature Widget,2004-10-13
234,The Bigger Widget,2004-10-14
234,The Nuclear Widget,2004-10-15
234,The Beer Widget,2004-10-16
234,The Stylish Widget,2004-10-17
(6 rows)
</pre>
Escollim el format CSV perquè és el format natural per importar la informació als fulls de càlcul.

===Per acabar, cron===
Finalment l'execució d'aquests scripts i la generació dels informes es poden automatitzar de manera que s'executin de forma periòdic. Per exemple, un problema a plantejar seria la generació automàtica d'un informe setmanal de vendes. Hauríem de modificar el script anterior i crear una tasca '''cron''' que ens generi l'informe. Aquest, en comptes de dir-se ''report.csv'' s'hauria de dir report_ddmmyyy.csv (ddmmyyy es substitueix pel dia, mes i any).

==Entrega==
Moodle

==Recursos==
script '''creacio_taula_productes.sql''':
<pre>
drop sequence products_row_num_seq;

create sequence products_row_num_seq cycle;

drop table products;

create table products (
row_num int not null default nextval('products_row_num_seq'),
id int primary key,
name varchar(35) not null,
description varchar(1024) null,
price numeric(8, 2) not null);

comment on table products is 'Records of products sold at the bookstore.';
comment on column products.row_num is 'For de-duping purposes;

drop sequence sales_row_num_seq;

create sequence sales_row_num_seq cycle;

drop table sales;

create table sales (
row_num int not null default nextval('sales_row_num_seq'),
first_name varchar(35) not null,
middle_name varchar(35) null,
last_name varchar(35) not null,
city varchar(35) not null,
state char(2) not null,
zip varchar(5) not null,
email_address varchar(50) not null,
product_id int not null,
product_name varchar(50) not null,
product_sale_date date not null);

comment on table sales is 'Conains records of books sold from the bookstore.';
comment on column sales.row_num is 'For de-duping purposes.';

-- PLEASE don't regard this as good database design; customers should be
-- foreign keys, as should products.

</pre>
script perl per crear els inserts de forma automàtica: '''create_input_sql.pl''':
<pre>
#!/usr/bin/perl

# create_input_sql.pl --> Create PostgreSQL sql statements from
# .csv file for products table, and put them in a file called
# runme.postgresql.

# by Manni Wood

use strict;

my $csv_file_name = $ARGV[0];
unless ($csv_file_name) {
print STDERR "Please enter the name of a CSV file.\n";
print STDERR "Aborting.\n";
exit 1;
}
my $output_file_name = "runme.postgresql";

open (IN, $csv_file_name) || die "Could not read \"$csv_file_name\": $!";
open (OUT, ">$output_file_name") || die "Could not write \"$output_file_name\": $!";
# The first line of this file is the field names, which we do *not*
# want to insert into the database. So read and discard the first line.
$_ = <IN>;

# Instructions to psql to stop on the first error.
print OUT "\\set ON_ERROR_STOP 1\n";
# Do this all as one transaction.
print OUT "begin;\n";

while (<IN>) {
# Blank lines can creep into .csv files. Skip them.
next if (m/^\s*$/);
chomp;
my ($id, $name, $description, $price) = parse_csv($_);

# Make all fields SQL-friendly
$id = nullify_field($id);
$name = quote_or_nullify_field($name);
$description = quote_or_nullify_field($description);
$price = nullify_field($price);

print OUT "insert into products (id, name, description, price) values (" .
$id . ", " .
$name . ", " .
$description . ", " .
$price . ");\n";
}
print OUT "commit;\n";
close (OUT);
close (IN);

sub parse_csv {
# The goal here is to just go ahead and split on commas,
# and then find chunks that start with a " and assume that we've
# broken apart a field containing commas; re-join the chunks to
# the chunk beginning with " until we find a chunk ending with ".
my @chunks;
my $chunk;
my @fields;
my $field;
my $line = shift;

@chunks = split(/,/, $line);

my $i;
for ($i = 0; $i <= $#chunks; ++$i) {
$chunk = $chunks[$i];
$field = $chunk;
# If chunk starts wtih a double-quote but does not end with one,
if (substr($chunk, 0, 1) eq '"' && substr($chunk, -1, 1) ne '"') {
# join the current chunk with the next chunk, replacing the
# comma that got eliminated during the split on commas
++$i;
$chunk = $chunks[$i];
$field .= "," . $chunk;
# and continue to do so until you find a chunk that ends with "
# or you have run out of chunks.
while (substr($chunk, -1, 1) ne '"' && $i <= $#chunks) {
++$i;
$chunk = $chunks[$i];
$field .= "," . $chunk;
}
# Our goal is to get rid of all field delimitors, so
# get rid of the leading double-quote
$field = substr($field, 1);
# and the trailing double-quote.
chop($field);
}
# If the chunk starts and ends with a double-quote,
if (substr($chunk, 0, 1) eq '"' && substr($chunk, -1, 1) eq '"') {
# get rid of the leading double-quote
$field = substr($field, 1);
# and the trailing double-quote.
chop($field);
}
# In CSV files, double quotes are escaped by doubling them up,
# so un-double them.
$field =~ s/""/"/g;
# Finally, we have a field that is completely usable, so add it to the
# array of fields we will return at the end of this subroutine.
push(@fields, $field);
# Clear the field for re-use in the next iteration of this loop.
$field = "";
}
return @fields;
}

sub nullify_field {
# For SQL fields that do not need to be wrapped in single quotes.
# If a field is empty, replace it with the string "null",
# which can safely be used in sql insert statements.
my $field = $_[0];
if ($field) {
return $field;
} else {
return "null";
}
}

sub quote_or_nullify_field {
# For SQL fields that need to be wrapped in single quotes.
# If a field is empty, replace it with the string "null",
# which can safely be used in sql insert statements.
# If it is not empty, replace any single quote with double single
# quotes (that's how you escape them in SQL) and wrap the whole thing
# in single quotes (for use in a SQL insert statement).
my $field = $_[0];
if ($field) {
# Remove backslashes: they cause havoc in SQL.
$field =~ s/\\//g;
# Repeat single quotes: this is how they are escaped in SQL.
$field =~ s/'/''/g;
$field = "'" . $field . "'";
} else {
$field = "null";
}
}
</pre>

'''load_new_products.sh''':
<pre>
#!/bin/sh

# load_new_products.sh
#
# Wrapper script that transforms products.csv into runme.postgresql
# and feeds runme.postgresql to psql.

# gen_sales_report.sh
PGUSER=postgres
PGPASSWORD=postgres
PGHOST=localhost
PGPORT=5432

export PGUSER PGPASSWORD PGHOST PGPORT

./create_input_sql.pl products.csv # outputs runme.postgresql

psql -f runme.postgresql postgres 1> load_new_products.log 2>&1

#reset PGUSER and PGPASSWORD
PGUSER=""
PGPASSWORD=""
PGHOST=""
PGPORT=""
export PGUSER PGPASSWORD PGHOST PGPORT

#End
</pre>

Script per fer els informes: '''gen_sales_report_param.sh''':
<pre>
#!/bin/sh

# gen_sales_report.sh
PGUSER=postgres
PGPASSWORD=postgres
PGHOST=localhost
PGPORT=5432

export PGUSER PGPASSWORD PGHOST PGPORT

psql --set start_date=\'$1\' \
--set end_date=\'$2\' \
-f report_mensual_param.sql postgres

#reset PGUSER and PGPASSWORD
PGUSER=""
PGPASSWORD=""
PGHOST=""
PGPORT=""
export PGUSER PGPASSWORD PGHOST PGPORT

#End
</pre>
Script '''report_mensual_param.sql''':
<pre>
-- generate_monthly_sales_report.postgresql
--
-- Outputs one month's-worth of sales to a CSV file that
-- is easily imported into spreadsheet software.

\o report.csv
\a
\f ,
select product_id, product_name, product_sale_date from sales where
product_sale_date *gt;= '2004-10-01' and product_sale_date <= '2004-10-31';
\o
</pre>

===Ús de l'opció \o===
per enviar el resultat d'una consulta a fitxer:
<pre>
\o [FILE] send all query results to file or |pipe
postgres# \o reposrt.txt
postgres# \o select product_id, product_name, product_sale_date from sales where
product_sale_date >= '2004-10-01' and product_sale_date <= '2004-10-31';
postgres# \o
</pre>
Per obtenir un resultat que sigui aprofitable per a Excel o OpenOffice.org:
<pre>
\o report.csv
\a -> toggle between unaligned and aligned output mode
\f , -> \f [STRING] show or set field separator for unaligned query output (en aquest cas, una coma)
</pre>
<pre>
postgres# select product_id, product_name, product_sale_date from sales where
product_sale_date >= '2004-10-01' and product_sale_date <= '2004-10-31';
postgres# \o
</pre>

===Ús de l'opció \SET===
<pre>
\set [NAME [VALUE]] -> set internal variable, or list all if no parameters
</pre>

==Durarda==
2 hores

Pràctica ASI-C6-ASGBD: Scripts amb PostgreSQL - Historial de revisió

Joan: /* Fer els informes de vendes */