[FIREBIRD] Utilizzare gli array nelle stored prcedure.

[das]

Volevo capire come si dichiarano gli array all'interno delle stored procedure.
So che non possono essere passati come parametro, ma per quello ho già rimediato creando una tabella dove immagazzinare al volo tutti i dati.
A proposito, esiste la possibilità di creare una tabella in RAM ?

Grazie

[das]

UP

[cdimauro]

Originariamente inviato da das

Volevo capire come si dichiarano gli array all'interno delle stored procedure.
So che non possono essere passati come parametro, ma per quello ho già rimediato creando una tabella dove immagazzinare al volo tutti i dati.

Non puoi dichiararli dentro una stored procedure:

DECLARE VARIABLE

Declares a local variable. Available in triggers and stored procedures.
Syntax DECLARE VARIABLE var datatype;

Argument Description

var Name of the local variable, unique within the trigger or procedure
datatype Datatype of the local variable; can be any InterBase datatype except Blob and arrays

A proposito, esiste la possibilità di creare una tabella in RAM ?

Grazie

Che io ricordi no.

[das]

Originariamente inviato da cdimauro

Non puoi dichiararli dentro una stored procedure:

DECLARE VARIABLE

Declares a local variable. Available in triggers and stored procedures.
Syntax DECLARE VARIABLE var datatype;

Argument Description

var Name of the local variable, unique within the trigger or procedure
datatype Datatype of the local variable; can be any InterBase datatype except Blob and arrays

Che io ricordi no.

In altre parole non sono supportati gli array ?

[cdimauro]

No.

Tra l'altro io non li ho mai usati: non mi sembrano di nessuna utilità pratica (almeno per quello che c'ho fatto finora coi db).

Molto più utili ho trovato, invece, le tabelle temporanee.

[das]

Originariamente inviato da cdimauro

No.

Tra l'altro io non li ho mai usati: non mi sembrano di nessuna utilità pratica (almeno per quello che c'ho fatto finora coi db).

Molto più utili ho trovato, invece, le tabelle temporanee.

Ma non sono in RAM giusto ? Gli array avrebbero dato un notevole aiuto sul piano della velocità per evitare di caricare più di una volta gli stessi dati dall'hard disk.

[cdimauro]

No, le tabelle temporanee risiedono su file temporanei creati al volo al momento del bisogno.

Comunque considera che FireBird cerca di tenere in memoria quanti più dati possibile fra quelli usati più di recente, per cui se configuri opportunamente la cache (cosa che io non ho mai fatto: non ho MAI toccato il file di configurazione, se non per specificare il path della cartella da usare per i file temporanei; già di default le impostazioni vanno bene) i tuoi dati starebbero quasi sempre in memoria.

Se ciò non fosse sufficiente allora c'è qualcosa che non va nel modello, e opterei per effettuare caching delle informazioni a livello applicativo o, meglio ancora, usando un server centrale allo scopo.
Ad esempio le applicazioni che scrivo sono per lo più server che, ovviamente, fanno accesso a un database, esponendo delle API ai client. Questi server li ho configurati per effettuare caching in memoria delle informazioni a cui si fa accesso più frequentemente, e il tutto in maniera trasparente rispetto ai client.

Comunque sono tutte ipotesi. Se mi dici più o meno a cosa stai lavorando, la dimensione dei dati, e la quantità di memoria della macchina su cui gira FireBird, magari possiamo trovare una soluzione che calza meglio per i tuoi scopi.

[das]

Praticamente sto creando un piccolo motore di ricerca. Le tabelle sono così organizzate:

Tabella "Link"

CRCLink(ChiavePrimaria)    CRCDominio    CRCPesrcorso    Fallimenti   TitoloPag.


Tabella "Domini":
CRCDominio(ChiavePrimaria) Tipo  LivelliX  Dominio

Tabella "Percorsi":
CRCPercorso(ChiavePrimaria)  Percorso   xFile

Tabella "Parole":
CRCParola(ChiavePrimaria)   Parola

Tabella "ParoleLink":
CRCParole   CRCLink   Sessione   Sequenza

Tabella "Originali":
CRCLink  Dati(blob)

Tabella "Differenze"
CRCLink(ChiavePrimaria)   Diff

dove tipo serve a specificare se è un link di tipo http, https, etc.
LivelliX serve per tutti i livelli superiori al primio. Ad esempio nel caso di http://www.blog.pincopallino.com/tratra/index.html
Tipo=http
LivelliX=www.blog
Dominio=pincopallino.com
Percorso=tratra
xFile=index.html

Tutti i CRC64 sono memorizzati in campi BIGINT.

In Parole sono memorizzate tutte le parole trovate, abbinate al loro CRC. In teoria questa tabella non servirebbe ma non ho ancora implementato un algoritmo CRC64 in php per cui per per ogni parola che cerco vado a vedere lì quale ne è il crc64.

In ParoleLink ci sono gli abbinamenti tra le parole ed i link nei quali sono contenute.
Ad esempio se cerco in quale sito c'è scritto "CIAO", faccio il crc64 di CIAO, faccio un select CRCLink,Sequenza from ParoleLink where CRCParole=CRC64('CIAO').

In Sequenza c'è la lista delle posizioni in cui la parola si trova. Ad esempio se in una pagina web c'è scritto soltanto: "Tanto va la gatta al lardo che ci lascia la zampata" ed io cerco la parola "LA", nel campo Sequenza di "LA" trovero scritto: "3,10" perchè questa parola si trova in 3a ed in 10ma posizione nella pagina.
Questo campo è particolarmente utile per cercare frasi esatte o frasi in cui due parole sono vicine anche se non accanto. (E' utile anche se non sembra, a me sarebbe servito un sacco di volte su google)


Ecco finalmente il mio problema:
se devo cercare solo una parola singola è tutto ok, ma se devo trovare quei siti che contengono più di un termine, potrei fare a livello applicativo una ricerca per ognuno e poi fare l'intersezione ma mi pare uno spreco enorme.
Basterebbe infatti memorizzare in RAM una tabella temporanea che contenga tutti i risultati estratti da ParoleLink dopo la ricerca del primo lemma, e poi per il secondo effettuare una ricerca all'interno di quella, salvare il risultato in RAM e cancellare il precedente. E così vià per tutte le parole. In questo modo ogni termine successivo nella ricerca richiederebbe sempre meno tempo rispetto al precedente.

Solo che FireBird non supporta gli array e nemmeno le tabelle in RAM, quindi non ho idea di come creare una stored procedure che possa fare una cosa del genere.

Inoltre volevo chiederti: ti pare che il database sia strutturato bene ?
Non sono per nulla esperto di database ed ogni cosa l'ho fatta cercando di attenermi a regole generali di buon senso, ma non sono sicuro che sia ottimizzato al massimo soprattutto perche in aroleLink non può esistere una chiave primaria dato che sia CRCParole che CRCLink si ripetono più di una volta.

[gugoXX]

Anche in SqlServer2005 e in Oracle (almeno la 9i) non e' possibile passare array nativi verso il database.
SqlServer2008 ha aggiustato la mancanza, e non so se Oracle ha allineato i provider (ODBC, OleDB, etc.) per supplire a questa mancanza.

Però è un problema abbastanza comune, e si e' sempre risolto.
E' sufficiente dedicare un parametro ad essere una stringa concatenata (tipicamente comma separated) dei valori che interessano.
Lato Database si provvedera' a splittare questa stringa, inserendo i singoli valori come righe di una tabella temporanea, che verra' messa in Join con il resto della query.

Se io volessi p.es. i record degli utenti con chiave 5 oppure 7 oppure 56, allora eseguirei la stored procedure

exec DammiGliUtenti ("5,7,56");

Al cui interno ci sara'
1 Crea la tabella temporanea "@Pluto" avente la colonna "Numero" e 3 righe, con appunto rispettivamente i valori 5, 7 e 56
2 Select User.* From User Join @Pluto ON (User.PK = Pluto.Numero)


Ext: Sotto SqlServer2005+ e Oracle9i+ ci sono anche le Table Function, che sono funzioni di database che accettano in input uno o piu' parametri e restituiscono una tabella, pronta per essere usata in una query.

Invece che la creazione manuale della tabella temporanea, una volta scritta la table function il cui codice e' praticamente identico al punto 1, si potrà scrivere

SELECT User.*
FROM User
JOIN Splitter("5,7,56") as split ON (User.PK = split.Numero)
WHERE....

[das]

Originariamente inviato da gugoXX

Anche in SqlServer2005 e in Oracle (almeno la 9i) non e' possibile passare array nativi verso il database.
SqlServer2008 ha aggiustato la mancanza, e non so se Oracle ha allineato i provider (ODBC, OleDB, etc.) per supplire a questa mancanza.

Però è un problema abbastanza comune, e si e' sempre risolto.
E' sufficiente dedicare un parametro ad essere una stringa concatenata (tipicamente comma separated) dei valori che interessano.
Lato Database si provvedera' a splittare questa stringa, inserendo i singoli valori come righe di una tabella temporanea, che verra' messa in Join con il resto della query.

Se io volessi p.es. i record degli utenti con chiave 5 oppure 7 oppure 56, allora eseguirei la stored procedure

exec DammiGliUtenti ("5,7,56");

Al cui interno ci sara'
1 Crea la tabella temporanea "@Pluto" avente la colonna "Numero" e 3 righe, con appunto rispettivamente i valori 5, 7 e 56
2 Select User.* From User Join @Pluto ON (User.PK = Pluto.Numero)


Ext: Sotto SqlServer2005+ e Oracle9i+ ci sono anche le Table Function, che sono funzioni di database che accettano in input uno o piu' parametri e restituiscono una tabella, pronta per essere usata in una query.

Invece che la creazione manuale della tabella temporanea, una volta scritta la table function il cui codice e' praticamente identico al punto 1, si potrà scrivere

SELECT User.*
FROM User
JOIN Splitter("5,7,56") as split ON (User.PK = split.Numero)
WHERE....

Sì però ormai è tutto fatto con FireBird, tra la'ltro ho scelto quello perchè è gratis. Prima usavo mysql che però ha la licenza a pagamento a meno che tu non rilasci il sorgente del tuo programma.

Il motore di ricerca che sto facendo è nato un po' per gioco per sostituirlo a phpdig nel sito che vedi in firma. Fatto sta che durante lo sviluppo mi è venuta in mente l'idea di venderlo come shareware quindi l'uninca cosa che posso usare è Firebird.

Comunque il mio problema non è passare degli array alla funzione, perchè per quello mi basta semplicemente creare una tabella con tutti i valori che voglio e fare in modo che la stored procedure attinga poi i dati da lì.

Il mio problema è che i risultati che ottengo devono a loro volta essere memorizzati in una tabella temporanea o in un array ad accesso veloce: in RAM, senza il bisogno di andare a scrivere sull'hard disk.

[gugoXX]

Ah, non avevo capito.
Allora fai con la doppia NOT EXISTS secca

(simile a )

SELECT * FROM Link (piu' magari altre Join che servono nel risultato)
WHERE NOT EXISTS
  (SELECT 1 FROM @tmp WHERE NOT EXISTS
     (SELECT 1 FROM Parole par 
          JOIN ParoleLink innerlink ON (par.CRC32=innerlink .CRC32)
          WHERE  par.Parola = @tmp.Valore AND innerlink.crclink = Link.crcLink
     )
  )

In pratica tira fuori ciascun Link tale per cui non ci sia nemmeno una parola contenuta nella tabella temporanea, che non sia contenuta nell'elenco delle parole del Link

PS: Ti consiglio di utilizzare come chiave primaria di Parole direttamente la parola stessa e non un CRC32.
In pratica la tabella parole risulterebbe una tabella virtuale inutile, e piazzeresti solo un indice sul nuovo campo "Parola" della tabella ParoleLink)
Sebbene possa sembrare non performante, se la parte di disegno relativa alla tabella Parole e' utilizzata solo per la ricerca di cui sopra, risparmiresti una JOIN che a quel livello può essere costosa, e la query sarebbe anche un pochino piu' semplice.

[cdimauro]

Concordo con le tue osservazioni, gugo.

Comunque personalmente caricherei in memoria queste tabelle (alla partenza dell'applicazione), utilizzando opportuni dizionari/hash/map, in modo da velocizzare la ricerca.
L'optimum sarebbe tirar sù un server a cui invocare le richieste. Ovviamente se l'applicazione è standalone, non se ne parla nemmeno.

Al posto dei CRC utilizzerei degli ID autoincrementanti da assegnare alle parole, in modo da utilizzare al massimo interi a 32 bit e velocizzare il look-up nei dizionari (all'inizio della ricerca sarebbe sufficiente convertire le parole nei rispettivi ID, e cercare soltanto questi). Così si evita anche il calcolo del CRC, che comunque non garantisce l'unicità.

Non capisco a cosa serva il campo Sessione. Personalmente lo toglierei di mezzo, oppure lo trasferirei in un'altra tabella se non è mai coinvolto nelle operazioni di ricerca (per intenderci: se non è usato in una WHERE). Meglio compattare il più possibile la tabella che verrà maggiormente utilizzata per le ricerche, perché in questo modo saranno presenti più record in ogni singola pagina, riducendo di conseguenza il caricamento delle pagine.

[das]

Originariamente inviato da gugoXX

Ah, non avevo capito.
Allora fai con la doppia NOT EXISTS secca

(simile a )

SELECT * FROM Link (piu' magari altre Join che servono nel risultato)
WHERE NOT EXISTS
  (SELECT 1 FROM @tmp WHERE NOT EXISTS
     (SELECT 1 FROM Parole par 
          JOIN ParoleLink innerlink ON (par.CRC32=innerlink .CRC32)
          WHERE  par.Parola = @tmp.Valore AND innerlink.crclink = Link.crcLink
     )
  )

In pratica tira fuori ciascun Link tale per cui non ci sia nemmeno una parola contenuta nella tabella temporanea, che non sia contenuta nell'elenco delle parole del Link

PS: Ti consiglio di utilizzare come chiave primaria di Parole direttamente la parola stessa e non un CRC32.
In pratica la tabella parole risulterebbe una tabella virtuale inutile, e piazzeresti solo un indice sul nuovo campo "Parola" della tabella ParoleLink)
Sebbene possa sembrare non performante, se la parte di disegno relativa alla tabella Parole e' utilizzata solo per la ricerca di cui sopra, risparmiresti una JOIN che a quel livello può essere costosa, e la query sarebbe anche un pochino piu' semplice.

Originariamente inviato da cdimauro

Concordo con le tue osservazioni, gugo.

Comunque personalmente caricherei in memoria queste tabelle (alla partenza dell'applicazione), utilizzando opportuni dizionari/hash/map, in modo da velocizzare la ricerca.
L'optimum sarebbe tirar sù un server a cui invocare le richieste. Ovviamente se l'applicazione è standalone, non se ne parla nemmeno.

Al posto dei CRC utilizzerei degli ID autoincrementanti da assegnare alle parole, in modo da utilizzare al massimo interi a 32 bit e velocizzare il look-up nei dizionari (all'inizio della ricerca sarebbe sufficiente convertire le parole nei rispettivi ID, e cercare soltanto questi). Così si evita anche il calcolo del CRC, che comunque non garantisce l'unicità.

Non capisco a cosa serva il campo Sessione. Personalmente lo toglierei di mezzo, oppure lo trasferirei in un'altra tabella se non è mai coinvolto nelle operazioni di ricerca (per intenderci: se non è usato in una WHERE). Meglio compattare il più possibile la tabella che verrà maggiormente utilizzata per le ricerche, perché in questo modo saranno presenti più record in ogni singola pagina, riducendo di conseguenza il caricamento delle pagine.

Mentre per scandire i siti e aggiornare il database ho usato il freepascal per fare le ricerche sto usando php. Mentre l'applicazione in freepascal è quasi finita, quella in PHP sta vedendo la luce in questi giorni.
Ora mi piacerebbe fare come hai suggerito tu: caricare la tabella in memoria una volta sola e lavorare sempre su quella; credo sia più veloce che usare WHERE NOT EXISTS. Solo che in PHP (del quale so molto poco) non so se è possibile. Cioè, quando un utente remoto carica una pagina non viene creata una nuova istanza pulita di tutti gli oggetti che si trovano nella pagina?

A limite sto pensando di creare un'applicazione server del quale poi il programma php sarebbe solo un frontend.

Per quanto riguarda i crc, li ho preferiti agli ID perchè mi consentono di fare una query in meno per ogni parola cercata. Ossia, anzichè andare a vedere qual'è l'ID di una parola per poi fare la ricerca con quell'ID, uso il CRC che posso sapere anche senza interrogare il database.
La tabella parole infatti mi serve provvisoriamente perchè non ho ancora implementato un algoritmo di CRC64 in php.
Per quanto riguarda le possibili collisioni, considerando quanto è piccolo il numero di parole esistenti rispetto alla quantità rappresentabile con 64bit credo che siano quasi impossibili.

Il campo sessione l'avevo messo all'inizio del progetto per consentire di tenere in memoria anche le ricerche del passato sapendo anche la data a cui risale ogni sessione. Solo che anch'io sto pensando di toglierlo perchè ora non so bene come gestirlo. Nel caso lo rimetto in una versione successiva del programma.

[cdimauro]

Originariamente inviato da das

Mentre per scandire i siti e aggiornare il database ho usato il freepascal per fare le ricerche sto usando php. Mentre l'applicazione in freepascal è quasi finita, quella in PHP sta vedendo la luce in questi giorni.

Ottima la scelta di FreePascal, ma francamente non mi affiderei a PHP per il tipo di lavoro che devi fare: ha pochi strumenti sintattici e prestazioni abbastanza scarse.

Ora mi piacerebbe fare come hai suggerito tu: caricare la tabella in memoria una volta sola e lavorare sempre su quella; credo sia più veloce che usare WHERE NOT EXISTS.

Dipende da come implementi il tutto, ma in questo caso per andare peggio di un database bisogna metterci i mezzi.

Solo che in PHP (del quale so molto poco) non so se è possibile. Cioè, quando un utente remoto carica una pagina non viene creata una nuova istanza pulita di tutti gli oggetti che si trovano nella pagina?

Non sono molto addentrato sull'argomento, ma con apache + mod_php penso che sia così.

Con mod_python ho notato una certa persistenza di informazioni, ma non ho indagato e non so di preciso quali siano i vincoli.

Posso dirti che con Python ho realizzato da qualche settimana un server HTTP (realizzato interamente con questo linguaggio) che è perfettamente in grado di effettuare caching di informazioni e, in generale, di tenere in memoria e condividere informazioni per tutte le richieste che arriveranno.
Poi è molto più leggero e performante dell'accoppiata apache + mod_python, per cui ho in programma, se possibile, di rimpiazzare tutti i server che ho realizzato con questa nuova soluzione.
L'ho implementato in poco tempo a partire da uno "di base" che viene fornito già nella libreria standard di Python, a cui ho aggiunto velocemente alcune comode funzioni (start, stop e restart, smistamento delle chiamate e altro).

A limite sto pensando di creare un'applicazione server del quale poi il programma php sarebbe solo un frontend.

Questa sarebbe un'ottima soluzione. Personalmente ti consiglio di usare Ice come middleware: http://www.zeroc.com/index.html

Usa un protocollo abbastanza veloce, è molto semplice realizzare server e client, e la cosa molto importante è che è possibile utilizzare diversi linguaggi per implementare sia i server che i client, pur mantenendo la stessa interfaccia. Il vantaggio di quest'approccio è che lo rende ovviamente più "generale" (non ci si lega a un solo linguaggio), ma soprattutto oggi puoi implementare un server con un linguaggio e domani farlo con un altro anche completamente diverso e potrai passare dall'uno all'altro in maniera a dir poco banale senza che nessun client si accorgerà mai del cambiamento.

Per quanto riguarda i crc, li ho preferiti agli ID perchè mi consentono di fare una query in meno per ogni parola cercata. Ossia, anzichè andare a vedere qual'è l'ID di una parola per poi fare la ricerca con quell'ID, uso il CRC che posso sapere anche senza interrogare il database.
La tabella parole infatti mi serve provvisoriamente perchè non ho ancora implementato un algoritmo di CRC64 in php.
Per quanto riguarda le possibili collisioni, considerando quanto è piccolo il numero di parole esistenti rispetto alla quantità rappresentabile con 64bit credo che siano quasi impossibili.

Personalmente vedo ancora meglio la soluzione con l'id autoincrementante. E' vero che ti costringe a fare una query in più, ma usando un indice il problema è decisamente ridotto.

Inoltre ha il vantaggio di usare la metà dello spazio rispetto al CRC64, e questo è importante per compattare i dati delle tabelle che ne fanno uso, perché vuol dire che su una pagina del db ci staranno molte più informazioni e, quindi, verrà ridotto il caricamento delle medesime.

Infine se usi la soluzione di tenere in memoria le tabelle (almeno questa la terrei sicuramente in memoria), con un solo look-up al dizionario/hash/map a partire dal nome ottieni praticamente subito l'ID: impiegheresti decisamente meno tempo che a calcolarti il CRC64 (per gli hash generalmente il tempo medio per l'accesso è O(1)).

Il campo sessione l'avevo messo all'inizio del progetto per consentire di tenere in memoria anche le ricerche del passato sapendo anche la data a cui risale ogni sessione. Solo che anch'io sto pensando di toglierlo perchè ora non so bene come gestirlo. Nel caso lo rimetto in una versione successiva del programma.

Toglilo, che è meglio. Se hai intenzione di inserirlo, se non serve alla ricerca, mettilo su una tabella a parte.

[das]

OK, grazie. Mi hai dato diversi suggerimenti utili.