In quest'articolo vedremo come organizzare un caricamento di massa di immagini su SQL Server con l'ausilio di textcopy e di un batch di comandi DOS.
Descrizione del progetto
Un progetto di lavoro, svolto alcuni mesi orsono, prevedeva l'importazione di oltre 3000 immagini presenti in varie cartelle del filesystem all'interno di un database SQL. Queste, una volta caricate, venivano elaborate da pacchetti DTS e Componenti .NET che provvedevano a:
- aggregarle in modo coerente, in base al nome e al percorso
- rieditarle con l'ausilio di librerie GDI
La parte che mi riguardava era l'importazione delle immagini sul database che doveva essere:
- rapida e performante
- riusabile per altri progetti
- fatta con il minor dispendio di ore uomo (non volevo fare componenti ad-hoc in VB o C#, spesso fonti di errore e perdite di pazienza!)
Alla fine tutti gli obiettivi sono stati raggiunti grazie a un semplice batch di comandi DOS!
L'organizzazione delle immagini sul filesystem
Le immagini si trovano all'interno di diverse cartelle, vedi screenshot:
All'interno di ognuna le immagini sono presenti in due formati gif e jpg, vedi screenshot:
Come procedere per l'importazione?
Indipendentemente dagli strumenti a disposizione i passi da compiere sono:
- Leggere ricorsivamente, partendo dalla directory radice, il contenuto di ogni singola cartella
- Per ogni immagine:
- Valutare il tipo di immagine, se gif o jpg (prenderà strade differenti a seconda del tipo)
- Importare l'immagine in SQL Server su una tabella predefinita
Il punto critico è: come implementare un algoritmo efficiente per la lettura ricorsiva delle immagini all'interno delle directories?
Le strade da seguire possono essere varie, usare l'oggetto FileSystemObject del vecchio VB oppure le API .NET del namespace System.IO per navigare sul filesystem, ma in entrambi i casi sono costretto a:
- scrivere e testare un programma
- rischiare performance scadenti nell'operazione di caricamento delle imagini (a causa dell'overhead dovuto all'uso di oggetti esterni com ADODB.Stream o simili)
- far lievitare il numero di errori e quindi le perdite di tempo necessarie per fissarli
Ripensando a tutto questo ho avuto un'idea: "se potessi leggere il contenuto delle directories come nel vecchio DOS con un semplice comando DIR?"
Osservando lo screenshot sottostante, possiamo notare come l'output di questo comando sia tutto ciò che serve per poter procedere.
Ecco che la lista dei files, con percorso completo, diventa il sorgente dati ideale su cui poter lavorare. Ma come posso leggere l'output prodotto dal comando e processarlo linea per linea?
Qualche minuto ben speso sulla documentazione DOS e scopro il tassello mancante: il comando FOR /F, ora è proprio fatta!
Grazie a FOR /F posso processare l'output video dal comando DIR e per ogni linea (cioè ogni nome di file) lanciare un comando opportuno, nel nostro caso:
prima osql, per popolare la tabella con il nome dell'immagine
dopo textcopy, per importare l'immagine sulla tabella di destinazione
Un tuffo nel passato
Per i nostalgici riporto alcuni collegamenti che illustrano le funzionalità dei comandi utilizzati nel batch che illustrero in seguito:
I componenti "esterni" della soluzione sono due:
- osql, un programma per lanciare istruzioni a SQL Server da prompt DOS
- textcopy, esaminato in precedenza, per importare le immagini su SQL Server
Ci siamo, ecco il batch!
In 30 righe di codice e con grande efficienza il compito è stato svolto egregiamente. Vediamo come si articola:
Inizializzazione e pulizia della console DOS:
@ECHO off
cls
REM ECHO on
Controllo che i parametri di input inseriti siano corretti, altrimento rimando ad una videata per una descrizione di aiuto:
if "%1"=="" Goto :Help
if "%1"=="/?" Goto :Help
REM Assegno le variabili
set FileType=%1
set SourceDir=%2
set SqlServer=%3
set Database=%4
set username=%5
set Password=%6
set LogFile=%7
Inserisco nel file di log le informazioni su data e ora corrente (inizio operazione):
Date/t > %7
time /t >> %7
Elimino e ricreo la tabella TempUpload che servirà come storage temporaneo per le immagini. Essa è costituita due colonne:
[UploadID] di tipo NVARCHAR memorizza percorso e nome dell'immagine, è la chiave primaria della tabella
[Data] di tipo IMAGE memorizza i bytes dell'immagine.
Echo EXEC... DROP TABLE
OSQL /S%SqlServer% /U%username% /P%Password% /d%Database% /Q"if exists (select
* from %Database%.dbo.sysobjects where id = object_id(N'[TempUpload]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1) drop table %Database%.dbo.[TempUpload]" >> %LogFile%
Echo EXEC... CREATE TABLE
OSQL /S%SqlServer% /U%username% /P%Password% /d%Database% /Q"create table
%Database%.dbo.TempUpload ([UploadID] sysname PRIMARY KEY, [Data] Image)"
>> %LogFile%
Processo linea per linea l'output del comando "DIR /b /s" con FOR /F. Ogni immagine trovata durante il parsing viene inserita in TempUpload. Così facendo garantisco che nella colonna UploadID ci siano valori unici, inoltre assegno un valore non NULL (0x0) alla colonna Data per non ottenere errori durante la successiva esecuzione di textcopy.
Echo EXEC... INSERT
FOR /F "tokens=*" %%i IN ('dir/b /s ^"%SourceDir%*.%FileType%^"')
DO OSQL /S%SqlServer% /U%username% /P%Password% /d%Database% /Q"INSERT INTO
%Database%.dbo.TempUpload ([UploadID],[Data]) VALUES ('%%i',0x0)" >>
%LogFile%
Una volta inseriti i nomi con un altro comando FOR /F procedo all'upload dei files con textcopy.
La clausola WHERE viene creata utilizzando il nome del file (WHERE UploadID='[PATH/NOMEFILE]').
Echo EXEC... UPLOAD VIA TEXTCOPY.EXE
FOR /F "tokens=*" %%i IN ('dir/b /s ^"%SourceDir%*.%FileType%^"')
DO textcopy.exe /S%SqlServer% /U%username% /P%Password% /d%Database% /TTempUpload
/CData /F"%%i" /I /W"WHERE [UploadID]='%%i'" >> %LogFile%
Inserisco nel log le informazioni sull'ora corrente (fine operazione)
time /t >> %LogFile%
GOTO END
:Help
Echo UTILIZZO DEL BATCH:
Echo ...
Echo %0 FileType SourceDir SqlServer Database Username Password LogFile
GOTO END
:END
A questo punto non rimane che lanciare il batch dal prompt di DOS con i corretti parametri di input:
batch.bat FileType SourceDir SqlServer Database
Username Password LogFile
ecco un esempio valorizzato correttamente:
batch.bat "gif" "c:wutemp" "(local)" "tempdb" "sa" "secret" "REPORT.log"
Nell'esempio verranno copiati tutti i file di tipo gif presenti nella cartella c:wutemp (ed anche nelle sue sottodirectory!) all'interno del SQL Server locale, precisamente nel database tempdb, utilizzando le credenziali di amministratore nel formato login (sa) e password (secret). Dobbiamo inoltre specificare sempre un file di log per le operazioni svolte, in questo caso REPORT.log.
ed un file di log con un contenuto simile a questo:
12/05/2005
17.13
(1 row affected)
(1 row affected)
(1 row affected)
TEXTCOPY Version 1.0
DB-Library version 8.00.194
Data copied into SQL Server image column from file 'c:wutempIMAGESf01id2small.gif'.
TEXTCOPY Version 1.0
DB-Library version 8.00.194
Data copied into SQL Server image column from file 'c:wutempIMAGESf01id3medium.gif'.
TEXTCOPY Version 1.0
DB-Library version 8.00.194
Data copied into SQL Server image column from file 'c:wutempIMAGESf01id3medium2.gif'.
17.13
Ora è semplice importare tutte le jpg, ma prima di procedere devo provvedere a rinominare la tabella TempUpload in ImagesGif, altrimenti rischierei di eliminare le immagini appena importate!
Use tempdb
GO
EXEC dbo.sp_rename 'TempUpload', 'ImagesGif'
Fatto questo non mi rimane che rilanciare il batch DOS modificando il primo parametro, passando jpg come valore!
batch.bat "jpg" "c:wutemp" "(local)" "tempdb" "sa" "secret" "REPORT.log"
Controllando nel Query Analyzer, possiamo verificare che tutte le immagini sono state caricate!
Il codice del batch può essere personalizzato per ogni esigenza!
Conclusione
Voilà, una volta lanciato il batch le 3000 immagini sono state caricate in un tempo di 6 minuti. Tenendo conto che il mio PC è un Centrino con 1 GB di memoria e un disco da 5400rpm ritengo il risultato soddisfacente!
