MySQL: filtraggio dati
Gandalf Corvotempesta
l'argomento.
Supponiamo di avere una anagrafica di clienti e di voler
tener traccia di ogni modifica apportata ai dati.
Pensavo di studiare una tabella 'solo insert' in cui anzichￜ
fare update si inserisce sempre e solo un nuovo record.
Esempio:
id, nome, viale, timestamp
1, gandalf, via terra di mezzo, 1234567890
In questo caso, ho un solo record, inserito il 1234567890 con
determinati dati.
Se un giorno volessi cambiare il viale, inserirei un nuovo record:
id, nome, viale, timestamp
1, gandalf, via terra di mezzo, 1234567890
2, gandalf, hobbiville, 2345678901
Cosￜ facendo, il dato aggornato sarￜ sempre l'ultimo in
ordine temporale, e dato che il nome sarￜ sempre lo stesso,
per avere i dati aggiornati di gandalf mi basterebbe fare:
select * from anagrafica where nome='gandalf'
order by timestamp desc limit 1
A lungo andare, potrei "dimenticarmi" di fare l'order by ed il
limit 1, insomma, ￜ un metodo che potrebbe portare ad errori.
Secondo voi, ￜ possibile fare una vista che mi faccia in automatico
l'order by su tutta la tabella lasciando interrogabile il campo
nome?
Ad esempio:
select * from vista where nome ='gandalf'
dovrebbe far la stessa cosa di cui sopra.
Si puￜ fare in qualche modo?
Lucazeo
<gandalf.corvotempe...@gmail.com> wrote:
> A lungo andare, potrei "dimenticarmi" di fare l'order by ed il
A lungo andare avresti anche una tabella enorme e delle difficoltà o
basse prestazioni quando dovrai incrociare o estrarre dati in maniera
più complessa.
Ti conviene creare una tabella a parte per i record storici ed
eventualmente gestirla con un trigger, che in automatico copi la tupla
vecchia nella tabella storica prima di aggiornare l'online.
Lucazeo
Andrea [Work]
> select * from anagrafica where nome='gandalf'
> order by timestamp desc limit 1
>
>
> A lungo andare, potrei "dimenticarmi" di fare l'order by ed il
> limit 1, insomma, � un metodo che potrebbe portare ad errori.
>
> Secondo voi, � possibile fare una vista che mi faccia in automatico
> l'order by su tutta la tabella lasciando interrogabile il campo
> nome?
>
> Ad esempio:
>
> select * from vista where nome ='gandalf'
>
> dovrebbe far la stessa cosa di cui sopra.
>
> Si pu� fare in qualche modo?
Di norma si fa con 1/2 campi data validit�. Tu prendi i dati di quel
cliente attivo in quel periodo. Devi centralizzare la ricerca dei dati,
altrimenti tutte le query dovranno tenerne conto.
Select from clienti where datain<=OGGI e datafi>=OGGI
Il problema classico � quando si emette es. una fattura, dopo 1 anno se
devi ristampare la fattura devi avere i dati del cliente a quella data.
La cosa pi� semplice � clonare nella fattura i campi che ti interessano, ma
poi avrai un comportamento misto, perch� non avrai mai tutti i campi che ti
servono clonati.
Oppure come chiave usi un campo nascosto nel cliente, quando fai una
modifica crei un cliente nuovo con gli stessi codici ma con il campo (es.
autoincrement) diverso. Cos� ogni fattura punter� al suo cliente. Cos� ti
basta ricordare di mettere nelle select tipo WHERE ATTIVO=1, oppure
togliere la condizione quando devi accedere senza condizioni (ogni
ID-Cliente sar� diverso). Quindi devi ricordarti di farlo solo nelle query
che ti devono dare l'elenco dei clienti attivi. Questo ti basta una vista.
Per verificare lo storico invece non metti alcuna condizione.
Non so se mySQL ha qualche sistema particolare per "nascondere" i record in
qualche modo.
Andrea [Work]
> select * from anagrafica where nome='gandalf'
> order by timestamp desc limit 1
>
>
> A lungo andare, potrei "dimenticarmi" di fare l'order by ed il
> limit 1, insomma, � un metodo che potrebbe portare ad errori.
>
> Secondo voi, � possibile fare una vista che mi faccia in automatico
> l'order by su tutta la tabella lasciando interrogabile il campo
> nome?
>
> Ad esempio:
>
> select * from vista where nome ='gandalf'
>
> dovrebbe far la stessa cosa di cui sopra.
>
> Si pu� fare in qualche modo?
Di norma si fa con 1/2 campi data validit�. Tu prendi i dati di quel
cliente attivo in quel periodo. Devi centralizzare la ricerca dei dati,
altrimenti tutte le query dovranno tenerne conto.
Select from clienti where datain<=OGGI e datafi>=OGGI
Il problema classico � quando si emette es. una fattura, dopo 1 anno se
devi ristampare la fattura devi avere i dati del cliente a quella data.
La cosa pi� semplice � clonare nella fattura i campi che ti interessano, ma
poi avrai un comportamento misto, perch� non avrai mai tutti i campi che ti
servono clonati.
Oppure come chiave usi un campo nascosto nel cliente, quando fai una
modifica crei un cliente nuovo con gli stessi codici ma con il campo (es.
autoincrement) diverso. Cos� ogni fattura punter� al suo cliente. Cos� ti
basta ricordare di mettere nelle select tipo WHERE ATTIVO=1, oppure
togliere la condizione quando devi accedere senza condizioni (ogni
ID-Cliente sar� diverso). Quindi devi ricordarti di farlo solo nelle query
che ti devono dare l'elenco dei clienti attivi. Questo ti basta una vista.
Per verificare lo storico invece non metti alcuna condizione.
Es.
C0001 Cliente 1 attivo=0 (id=6516)
C0001 Cliente 1 attivo=1 (id=9506)
I vecchi documetni avranno 6516 come collegamento, i nuovi gli fai
scegliere solo 9506,
Andrea [Work]
> A lungo andare avresti anche una tabella enorme e delle difficolt� o
> basse prestazioni quando dovrai incrociare o estrarre dati in maniera
> pi� complessa.
> Ti conviene creare una tabella a parte per i record storici ed
> eventualmente gestirla con un trigger, che in automatico copi la tupla
> vecchia nella tabella storica prima di aggiornare l'online.
Se per� c'� da recuperare quei dati nelle procedure standard si complicano
di tanto. Basta appunto una procedura di ristampa di una fattura, deve
pescare i dati da un'altra tabella, o meglio non sa se pescarli da una
parte o dall'altra.
Bisogna vedere cosa serve all'OP, se gli basta un semplice LOG di
operazioni si fa su tabella esterna o file esterno. Se deve poi recuperare
quei dati meglio siano tutti disponibili nella stessa tabella IMHO.
Gandalf Corvotempesta
> Oppure come chiave usi un campo nascosto nel cliente, quando fai una
> modifica crei un cliente nuovo con gli stessi codici ma con il campo (es.
> autoincrement) diverso. Cos� ogni fattura punter� al suo cliente. Cos� ti
> basta ricordare di mettere nelle select tipo WHERE ATTIVO=1, oppure
> togliere la condizione quando devi accedere senza condizioni (ogni
> ID-Cliente sar� diverso). Quindi devi ricordarti di farlo solo nelle query
> che ti devono dare l'elenco dei clienti attivi. Questo ti basta una vista.
> Per verificare lo storico invece non metti alcuna condizione.
Questa era una soluzione che avevo preventivato inizialmente.
Per ora ho risolto come da consiglio di Antani su icol.sys ovvero
una select con dentro un select. E funziona.
Sulla base di quella query ho creato una vista, cos� non mi resta
far altro che interrogare direttamente la vista.
Andrea [Work]
> Questa era una soluzione che avevo preventivato inizialmente.
> Per ora ho risolto come da consiglio di Antani su icol.sys ovvero
> una select con dentro un select. E funziona.
> Sulla base di quella query ho creato una vista, cos� non mi resta
> far altro che interrogare direttamente la vista.
Se potessi postare per esteso la soluzione magari torna utile a qualche
altra persona.
Gandalf Corvotempesta
> Se potessi postare per esteso la soluzione magari torna utile a qualche
> altra persona.
select * from anagrafica e where inserimento >= ALL (select inserimento
from
anag i where i.nome=e.nome);
Paolo opg
news:hbv8vv$251$1...@peregrinotuc.motzarella.org:
> Scusate il titolo, ma non sapevo in che modo chiamare
> l'argomento.
>
> Supponiamo di avere una anagrafica di clienti e di voler
> tener traccia di ogni modifica apportata ai dati.
>
> Pensavo di studiare una tabella 'solo insert' in cui anzich�
> fare update si inserisce sempre e solo un nuovo record.
>
[cut]
per una situazione simile, io ho preferito fare un duplicato della tabella
originale e per gli update creare apposita stored procedure.
in tutto il sw, gli upadte sono fatti tramite questa sp.
per prima cosa, la sp copia la riga da modificare nella tabella 'storico',
con timestamp, nome dell'utente che sta facendo la modifica, motivazione
della modifica (nell'interfaccia sono presenti alcune funzioni particolari
per casi specifici).
solo dopo, salva in tabella la modifica richiesta dall'utente.
in questo modo, la tabella delle anagrafiche contiene le anagrafiche (e non
lo storico) e la tabella di log contiene lo storico, con l'aggiunta di
alcune informazioni aggiuntive che nella tabella originale non ha senso
mettere (motivo della modifica, tanto per fare un esempio).
il metodo di cui sopra, ha alcuni interessanti effetti collaterali:
- per quante modifiche facciano gli utenti, il numero di righe nella
tabella 'live' non esplode
- non servono filtri speciali per trovare l'anagrafica che cerchi (vedi le
query che stai cercando di mettere in piedi...).
--
Paolo opg
BE AWARE that this post uses a fake reply-to address
to contact me write to:
janickg ( at ) hotmail ( dot ) com
--
Andrea [Work]
> in questo modo, la tabella delle anagrafiche contiene le anagrafiche (e non
> lo storico) e la tabella di log contiene lo storico, con l'aggiunta di
> alcune informazioni aggiuntive che nella tabella originale non ha senso
> mettere (motivo della modifica, tanto per fare un esempio).
Nel caso di anagrafiche clienti richiamate es. in fatture come avete
risolto il problema?
Avete "clonato" i campi nei documenti?
Per capirci, se il cliente PIPPO prima sta in Via Verdi e poi in Via Rossi,
se ristampi una vecchia fattura deve esserci la Via Verdi.
Lucazeo
In un mio sistema i dati erano riportati in ogni fattura, anche perché
c'era la possibilità di cambiare ogni volta alcuni dati (come quelli
di
spedizione).
Troppa ridondanza? Forse. Ma i record più vecchi di tot erano in uno
storico, interrogato solo in casi particolari. E' stato sacrificato
molto
spazio ma con prestazioni molto buone.
Paolo opg
news:1ftmdwpe4bk1$.18myv9931esom$.d...@40tude.net:
la mia situazione era particolarmente semplice (ogni anagrafica era fine
a se stessa e 'autocontenuta') quindi ho potuto farla molto breve...
per una gestione piu' complessa, la prima cosa che mi viene in mente (ma
che non so quanto sia effettivamente affidabile/robusta/praticabile) e'
un campo aggiuntivo nella tabella documenti che richiama lo storico e di
default e' a null.
su modifica dell'anagrafica, al momento dell'archiviazione, popolo il
campo 'storico' del documento con l'id della riga che ho appena inserito
nella tabella di log.
quando richiamo il documento, se ho il riferimento allo storico prendo
l'anagrafica dall'archivio, se non c'e' la prendo dalla tabella
originale.
l'implementazione richiede una modifica 'minima' alla stored procedure di
update ed eventuali chiamate gia' in uso non andrebbero modificate.
ripeto: soluzione buttata li'.
non so se in ambiente reale sia una soluzione affidabile o abbia qualche
rogna che al volo non mi viene in mente.
Gandalf Corvotempesta
> il metodo di cui sopra, ha alcuni interessanti effetti collaterali:
> - per quante modifiche facciano gli utenti, il numero di righe nella
> tabella 'live' non esplode
Poche decine di modifiche ogni anno.
In media si starￜ sotto i 5.
> - non servono filtri speciali per trovare l'anagrafica che cerchi (vedi le
> query che stai cercando di mettere in piedi...).
Be, con la vista che ho appena creato non mi serve alcun filtro.
--
Non tocca a noi dominare tutte le maree del mondo,
il nostro compito ￜ di fare il possibile per la
salvezza degli anni nei quali viviamo,
sradicando il male dai campi che conosciamo.
Gandalf Corvotempesta
> Troppa ridondanza? Forse. Ma i record pi� vecchi di tot erano in uno
> storico, interrogato solo in casi particolari. E' stato sacrificato
> molto
> spazio ma con prestazioni molto buone.
Anche io adotter� la soluzione con storico.
Diciamo che ho bisogno di avere un tot di dati online,
magari le ultime 2-3 variazioni, tutto il resto potr�
essere archiviato su un database a parte da interrogare
in caso di necessit�.
La soluzione con db a parte dovrebbe permettere di ottenere
buone prestazioni, sarebbero penalizzate solo le join (impossibili
su database differenti) ma nel caso dovessi prendere dati veramente
obsoleti, potrei fare tranquillamente due query distinte, la prima
sul database online contenente le ultime due variazioni, la seconda
sul database offline contenente tutto il resto della schifezza.
--
Non tocca a noi dominare tutte le maree del mondo,
il nostro compito � di fare il possibile per la
Andrea [Work]
> Troppa ridondanza? Forse. Ma i record pi� vecchi di tot erano in uno
> storico, interrogato solo in casi particolari. E' stato sacrificato
> molto
> spazio ma con prestazioni molto buone.
E' la soluzione che abbiamo pure noi.
Come storico noi non memorizziamo i dati precedenti, viene loggato l'utente
che fa la variazione in un posto centralizzato.
Andrea [Work]
> su modifica dell'anagrafica, al momento dell'archiviazione, popolo il
> campo 'storico' del documento con l'id della riga che ho appena inserito
> nella tabella di log.
> quando richiamo il documento, se ho il riferimento allo storico prendo
> l'anagrafica dall'archivio, se non c'e' la prendo dalla tabella
> originale.
Boh alla fine il grosso delle select uno le fa sui documenti memorizzati.
Secondo ma tanto vale avere tutto nella stessa tabella.
La select sui clienti attivi si parla comunque di pochi casi, alla
creazione dei documenti, che sono comunque indicizzabili in qualche modo.
C'� da dire che le reali modifiche sulle anagrafiche non sono tantissime,
il pi� delle volte � l'utente che preme SALVA anche se non ha fatto nulla.
Quindi fare un Update solo dei dati modificati, gi� risparmia il grosso dei
record.
Paolo opg
> Paolo opg ha scritto:
>> il metodo di cui sopra, ha alcuni interessanti effetti collaterali:
>> - per quante modifiche facciano gli utenti, il numero di righe nella
>> tabella 'live' non esplode
>
> Poche decine di modifiche ogni anno.
> In media si star� sotto i 5.
>
5 per ogni anagrafica o 5 in tutto?
o forse sotto i 5 record?
:-)
con numeri bassi, effettivamente questa considerazione passa in secondo
piano.
>> - non servono filtri speciali per trovare l'anagrafica che cerchi
>> (vedi le query che stai cercando di mettere in piedi...).
>
> Be, con la vista che ho appena creato non mi serve alcun filtro.
>
puoi anche spostare o incapsulare la complessita', ma metterla in una
scatola non la elimina, la sposta e basta.
il filtro ti serve, tant'e' vero che hai creato una vista dedicata per
salvarlo e non dimenticartelo...
con la soluzione che ho tirato fuori io, devi creare e manutenere stored
procedures.
col metodo che hai trovato tu, hai una vista e devi manutenere quella.
tutto sta a vedere quanta/quale complessita' vale la pena di gestire.
ma li' si tratta di valutazioni che puoi fare solo tu, in base alla tua
situazione, ai requisiti di progetto, alle tue conoscenze, etc etc etc...
^^
Gandalf Corvotempesta
> 5 per ogni anagrafica o 5 in tutto?
> o forse sotto i 5 record?
> :-)
>
> con numeri bassi, effettivamente questa considerazione passa in secondo
> piano.
5 record modificati in tutto, tra tutte le anagrafiche.
In pratica, io inserisco 1000 anagrafiche, di queste, 5
avranno delle modifiche fatte una singola volta, per cui
5 record aggiuntivi (1 per ogni anagrafica modificata)
> puoi anche spostare o incapsulare la complessita', ma metterla in una
> scatola non la elimina, la sposta e basta.
>
> il filtro ti serve, tant'e' vero che hai creato una vista dedicata per
> salvarlo e non dimenticartelo...
Vero, perￜ un conto ￜ avere una vista pronta per essere utilizzata
(dopotutto, le hanno inventate appositamente), un altro conto ￜ dover
segnare da qualche parte come fare la query corretta.
E dove lo segno? Lo segno direttamente sul server mysql, dentro una
vista :)
Non dico che sia sbagliato ricordarsi la query, dico solo che non voglio
dover girare con i postit ogni volta, voglio memorizzarla ed
abbandonarla li.
Paolo opg
> Il Mon, 26 Oct 2009 15:10:42 +0000 (UTC), Paolo opg ha scritto:
>
>> su modifica dell'anagrafica, al momento dell'archiviazione, popolo il
>> campo 'storico' del documento con l'id della riga che ho appena
>> inserito nella tabella di log.
>> quando richiamo il documento, se ho il riferimento allo storico
>> prendo l'anagrafica dall'archivio, se non c'e' la prendo dalla
>> tabella originale.
>
> Boh alla fine il grosso delle select uno le fa sui documenti
> memorizzati. Secondo ma tanto vale avere tutto nella stessa tabella.
> La select sui clienti attivi si parla comunque di pochi casi, alla
> creazione dei documenti, che sono comunque indicizzabili in qualche
> modo.
>
io separo le due entita' (dato 'vivo' dallo storico) anche per una
questione di abitudine.
preferisco avere oggetti diversi in tabelle diverse e non dover
interpretare ogni riga per attribuirle il significato corretto.
questo comporta che interrogando oggetti dipendenti dall'anagrafica,
dovrei gestire il recupero dell'informazione da due tabelle diverse, a
seconda dei casi.
alla fine il risultato che si ottiene e' lo stesso, ovviamente.
tutto sta a scegliere quale complessita' si riesce a gestire meglio e
dove la si vuole.
> C'� da dire che le reali modifiche sulle anagrafiche non sono
> tantissime, il pi� delle volte � l'utente che preme SALVA anche se non
> ha fatto nulla. Quindi fare un Update solo dei dati modificati, gi�
> risparmia il grosso dei record.
>
--