Mi sono sempre chiesto se LIMIT tagliasse i dati “a monte” della query o “a valle”…
Un tempo mettevo LIMIT convinto che servisse, poi mi sono accorto “a naso” che non pareva proprio così…
Una discussione in giro per irc ha chiarito il punto.
Temevo che fosse come in effetti e’: LIMIT taglia i risultati dopo che la query è stata eseguita e i record trovati sono molto più di quelli che vengono restituiti. (cit. datacharmer).
web:Agenda> SELECT SQL_CALC_FOUND_ROWS * FROM folks LIMIT 1; +----+------+----------+------------+--------+---------------------+---------------------+--------+ | id | name | surname | birth | gender | created | updated | active | +----+------+----------+------------+--------+---------------------+---------------------+--------+ | 1 | xxxx | xxxxxxxx | 1973-06-02 | m | 2010-02-11 20:11:00 | 2010-02-13 17:33:04 | 1 | +----+------+----------+------------+--------+---------------------+---------------------+--------+ 1 row in set (0.00 sec) web:Agenda> SELECT FOUND_ROWS(); +--------------+ | FOUND_ROWS() | +--------------+ | 101 | +--------------+ 1 row in set (0.00 sec) web:Agenda> SELECT SQL_CALC_FOUND_ROWS * FROM folks WHERE id=1; +----+------+----------+------------+--------+---------------------+---------------------+--------+ | id | name | surname | birth | gender | created | updated | active | +----+------+----------+------------+--------+---------------------+---------------------+--------+ | 1 | xxxx | xxxxxxxx | 1973-06-02 | m | 2010-02-11 20:11:00 | 2010-02-13 17:33:04 | 1 | +----+------+----------+------------+--------+---------------------+---------------------+--------+ 1 row in set (0.01 sec) web:Agenda> SELECT FOUND_ROWS(); +--------------+ | FOUND_ROWS() | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec)
Un WHERE ben piazzato (se si può mettere), è ben più efficace. Ooookei.
Avendo un database in mano mai visto ma progettato decentemente, può essere interessante cercare di valutare se ci sono campi utili per linkare le relazioni fra di loro (e valutare prima di una modifica quali relazioni dovrebbero essere toccate). (Spesso i campi per creare questi collegamenti sono dei field *_id che si ripetono nelle relazioni interessate.)
Ho avuto la necessità quindi di vedere quante e quali relazioni facessero uso di un determinato campo in un database (o, detta in soldoni, ho cercato di capire dove fossero presenti determinate colonne nelle tabelle del database).
Win. Almeno so dove guardare. 
Dopo gli accidenti tirati precedentemente, ho scoperto che dare un comando CHANGE su una colonna dichiarandola come TIMESTAMP ha il seguente effetto: il default diventa CURRENT_TIMESTAMP e l’extra è ON UPDATE CURRENT_TIMESTAMP.
Avevo letto che CHANGE e MODIFY cancellavano gli attributi (l’Extra) delle colonne cui cambiavano il tipo, e così mi sono messo ad assegnare TIMESTAMP e DATETIME ai due campi created/updated in modo da togliermi i dubbi.
(dal man: “When you use CHANGE or MODIFY, column_definition must include the data type and all attributes that should apply to the new column, other than index attributes such as PRIMARY KEY or UNIQUE. Attributes present in the original definition but not specified for the new definition are not carried forward.“)
sim:test> ALTER TABLE A CHANGE created created DATETIME; sim:test> ALTER TABLE A CHANGE updated updated TIMESTAMP; [...] | created | datetime | YES | | NULL | | | updated | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
Alla fine mi sono un po’ capito leggendo qua.
I dati di tipo timestamp dovrebbero essere dei datetime (e lo sono come formato), ma a differenza di questi ultimi si aggiornano con l’ora attuale.
The TIMESTAMP data type offers automatic initialization and updating.
Dopo aver reso la colonna updated CURRENT_TIMESTAMP con aggiornamento sull’UPDATE dei dati, ho potuto fare:
ALTER TABLE A MODIFY created TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00';
e inserire un paio di dati:
sim:test> INSERT INTO A (word) VALUES ('gastone');
sim:test> INSERT INTO A (word,created) VALUES ('paperino',NULL);
col risultato:
| 5 | gastone | 0000-00-00 00:00:00 | 2010-02-11 23:42:03 | | 6 | paperino | 2010-02-11 23:42:23 | 2010-02-11 23:42:23 |
Sul man c’è scritto pure questo, ma a me continua a sembrare un comportamento strano.
TIMESTAMP columns are NOT NULL by default, cannot contain NULL values, and assigning NULL assigns the current timestamp.
In conclusione, se volete avere dei campi con la creazione e l’aggiornamento automatico delle date incluso, leggetevi la pagina di Mysql sui timestamp.
A forza di spaccarsi la testa sulle cose, ogni tanto se ne esce.
Avendo un database su PostgreSQL, e volendo manipolarlo con strumenti ORM in perl si può usare DBIx::Class (DBIC per gli amici).
DBIC si appoggia a “schemi” di tabelle nel database che gli permettono di interagire col db-server in maniera corretta (e ottimizzata).
Gli schemi sono un po’ pallosi da estrarre, ma a tutto c’è soluzione:
Dopo questo comando nella directory Sim ci sarà un pacchetto Prova.pm (lo schema) che caricherà le classi che rappresentano le tabelle del database XXX (nella dir Sim/Prova/tabella_*.pm).
Le rappresentazioni delle tabelle sono interessanti da guardare.
Da questo momento sarà possibile scrivere script che usino lo schema (Sim::Prova), si connettano attraverso di esso (metodo connect) ed agiscano sui dati del database XXX.
use Sim::Prova;
my $schema = Sim::Prova->connect(‘dbi:Pg:dbname=XXX’, ‘YYY_user’, ‘ZZZ_passwd’, {});
my @all_authors = $schema->resultset(‘Authors’)->all;
foreach (@all_authors) {
print $_->name ."\n";
}
L’oggetto $schema porta la connessione, tramite ->resultset(‘table’) capisce com’è organizzata la tabella nel db e poi accetta i metodi utili per fare qualcosa (CRUD: Create, Retrieve, Update, Delete).
$schema->resultset(‘tabella’)->create( {id => 1, name => ‘Simone’} );
# R
my @rows = $schema->resultset(‘tabella’)->all;
foreach $row (@rows) {
print $_->id ." | ". $_->name ."\n";
}
# U
my $author = $schema->resultset(‘tabella’)->find(1);
# dentro $author c’è il nostro record.
$author->name(‘Mario Rossi’);
# rimpiazza l’attributo "name" di $author con qualcosa di diverso.
# A questo punto il database ha ancora il record non cambiato, ma
# $author contiene un record differente.
$author->update();
# così aggiorno i dati sul db.
# D
# Come update, si identifica il record giusto e…
$author->delete();
Un po’ di basic usage e di manualistica.
Nel delirio perlico che ogni tanto mi prende, mi sono messo a guardare un po’ di codice che richiedeva un differente approccio per collegarsi ai db.
Devo dire che sono rimasto schifosamente affascinato da DBIx::Class e il suo “Schema”, ma non avendoci ancora capito molto, aspetto a scriverci su qualcosa.
Di sicuro invece c’è che DBIx::Simple funziona. Non ho ben capito perchè dovrebbe essere più semplice di DBI, però… funziona.
DBI, come ho già scritto in passato, funge così: si carica l’handler, si prepara la query, la si esegue e si fetcha il risultato nella maniera che si ritiene più opportuna. (*)
use DBI;
my $dsn = "dbi:mysql:database=test";
my $dbh = DBI->connect( $dsn, $username, $password )
or die $DBI::errstr;
my $sth = $dbh->prepare("SELECT * FROM testing")
or die $dbh->errstr;
$sth->execute();
while ( my $row = $sth->fetchrow_hashref() )
{ ... }
Il vantaggio con DBIx::Simple è che prepare() ed execute() sono compresi in un’unica chiamata. Il fetching dei risultati dovrebbe essere un po’ semplificato, ma a me non ha cambiato la vita (i metodi sono al paragrafo DBIx::Simple::Result methods).
use DBIx::Simple;
my $dbh = DBIx::Simple->connect('dbi:Pg:dbname=test',
'username', 'password' ) or die DBIx::Simple->error;
my $res = $dbh->query('SELECT * FROM pluto');
while ( $res->into(my($id, $ti, $ra)) ) {
say "$id $ti $ra";
}
Nota: L’idea del metodo html per ottenere una tabella HTML dal risultato l’ho trovata simpatica.
(*) Attualmente il modo più comodo che ho trovato per fare le chiamate è passando attraverso una subroutine che si mangia l’handler e la query come argomenti:
sub sql_query {
my ($dbh,$query) = @_;
my $result = $dbh->prepare($query)
or print("Failed to set up query: $query\n");
$result->execute()
or print("Failed to exec query: $query\n");
return $result;
}
...
my $dbh = DBI->connect(...)
$risultato = sql_query($dbh, "SELECT * FROM pippo");
while ( my @riga = $risultato->fetchrow_array ) { ... }
A forza di pistolare sui db, sto provando un po’ di tutto per vedere dove mi trovo meglio.
Un db importante sul fronte opensource è PostgreSQL (sito ufficiale e wiki per un po’ di storia).
Per creare utenze all’interno del sistema postgres è possibile utilizzare programmi da shell (createuser) o comandi da prompt (CREATE ROLE utente).
Per avere un’idea dei ruoli creati si puo’ fare un select sulla tabella pg_roles del db postgres (di default)…
postgres=# SELECT rolname FROM pg_roles ; rolname ---------- postgres odg (2 rows)
oppure usare il semplice comando \du
postgres=# \du
List of roles
Role name | Superuser | Create role | Create DB | Connections | Member of
-----------+-----------+-------------+-----------+-------------+-----------
odg | no | no | no | no limit | {}
postgres | yes | yes | yes | no limit | {}
(2 rows)
(Come si vede ho creato un ruolo extra (odg) oltre a quello di default.)
Ok, weekend nel marasma delle webapplication.
Ricapitolo un po’…
* Passaggi dati non numerici (potenzialmente massicci) in POST, ritorno in GET (per forza).
* Controllo in entrata di pagina (isset, is_numeric, blablabla, check di esistenza sul db).
* Funzioni di escape sui dati da inserire nel db. (pg_escape_string, mysql_real_escape)
* Funzioni di escape in output sul codice html (giusto per evitare scriptini e tag interpretabili): htmlentities.
* Funzioni stripslashes per togliere gli escape nel db in output.
* Check con regexp (preg_match) sulle stringhe inserite, ocio ai filename con rischio per gli header serviti.
Un po’ di storie dal php security consortium.