Struktur Direktori di SQL Server 2005

Bagi Anda yang pernah bekerja dengan struktur direktori (directory structure) di sistem filing, Anda pasti tahu betapa menantangnya menggali ke dalam folders untuk menemukan spesifik file. Jika jenis informasi ini disimpan di database, Anda akan mengetahui bagaimana cara untuk mendapatkan data tersebut. Menulis query untuk memperoleh informasi ini seringkali sangat susah dan tidak efisien. Recursion dan XML di SQL Server 2005 bisa dipakai untuk membuat suatu lokasi file on the fly.

Contoh

Contoh ini mencari sebuah dokumen dan membangun path terhadap dokumen tersebut berdasar pada hubungan parent-folder dan child-folder di database. Satu file akan berada pada suatu folder, yang bisa merupakan child folder . Tujuannya adalah untk mengetahui informasi tentang file yang sedang dicari dan kemudian proses itu akan membangun sebuah lokasi terhadap file tersebut.

Menurut pengalaman saya, lokasi dari file path bisa disimpan dengan berbagai macam cara di database, biasanya dengan tujuan agar file tersebut bisa ditampilkan dari suatu situs web. Kebanyakan, full path dari suatu file disimpan di 1 kolom database, namun ada juga suatu lokasi file yang di “normalisasi”, sehingga path harus dibangun kembali jika diperlukan. Tujuan saya menulis artikel ini adalah untuk memecahkan masalah dalam membangun sebuah file path dari struktur hirarki.Skrip di bawah ini membuat sebuah tabel Documents dan tabel Folders. Tabel Documents menyimpan nama file dan direktori dimana dokumen berada. Tabel Folders menyimpan struktur direktori dari satu atau lebih drive lokal atau network. Sebagian besar pekerjaan di contoh ini akan menelusuri struktur folder ini untuk membangun jalur menuju file tersebut.

IF OBJECT_ID('Documents','U') IS NOT NULL

 DROP TABLE Documents    

 IF OBJECT_ID('Folders','U') IS NOT NULL

 DROP TABLE Folders    

 IF OBJECT_ID('udf_BuildDocumentPath','FN') IS NOT NULL

 DROP FUNCTION udf_BuildDocumentPath    

 CREATE TABLE Documents

 (

         DocumentID SMALLINT,

         FolderID SMALLINT,

         DocumentName VARCHAR(255)

 )    

 CREATE TABLE Folders

 (

         FolderID SMALLINT,

         ParentFolderID SMALLINT,

         FolderName VARCHAR(255)

 )

Kode di bawah ini akan menambah data ke dalam tabel yang baru saja kita buat.

INSERT INTO Documents(DocumentID, FolderID, DocumentName)

 VALUES(1,5,'SalesForecast2008.xls')

 INSERT INTO Documents(DocumentID, FolderID, DocumentName)

 VALUES(2,5,'SalesProjection.doc')

 INSERT INTO Documents(DocumentID, FolderID, DocumentName)

 VALUES(3,5,'SalesForecastPresentation.ppt')    

 INSERT INTO Folders(FolderID, ParentFolderID, FolderName)

 VALUES(1,null, 'D:')

 INSERT INTO Folders(FolderID, ParentFolderID, FolderName)

 VALUES(2,1, 'Sales')

 INSERT INTO Folders(FolderID, ParentFolderID, FolderName)

 VALUES(3,2, 'Forecasts')

 INSERT INTO Folders(FolderID, ParentFolderID, FolderName)

 VALUES(4,3, 'Data')

 INSERT INTO Folders(FolderID, ParentFolderID, FolderName)

 VALUES(5,4, '2008')

 GO

Skrip di bawah membuat suatu fungsi yang akan membangun full path pada file berdasarkan DocumentID di tabel Documents. Fungsi ini memakai Common Table Expression (CTE) rekursif untuk melintasi struktur direktori, menghubungkan child folder ID ke parent folder ID di tabel. Pada saat data yang berhubungan dengan full path dari suatu dokumen ditemukan, FOR XML PATH (‘’) digunakan sebagai tumpuan nilai-nilai ini dari data di baris lain untuk menghasilkan gabungan antara keduanya pada baris yang sama. Dari sini, kita hanya tinggal mengembalikan jalur (path) yang dibentuk kepada fungsi pemanggil (caller).

FOR XML Path() adalah salah satu fitur favorit saya di SQL Server 2005 karena memudahkan saya untuk mendapatkan isi dari suatu kolom dari baris yang lain dan menggabungkannya sehingga berada di baris yang sama. Utiliti ini ideal untuk membuat SQL statement yang dinamis, yang memerlukan nilai-nilai yang berbeda di dalam IN() statement.

CREATE FUNCTION udf_BuildDocumentPath

 (

         @DocumentID SMALLINT

 )

 RETURNS VARCHAR(400)

 AS

 BEGIN

         DECLARE @ReturnPath VARCHAR(400)    

 WITH DirectoryPathCTE(DocumentID, FolderID, ParentFolderID, DocumentName, FolderName, LevelNumber)

         AS

         (

         SELECT

                DocumentID, f.FolderID, ParentFolderID, DocumentName, f.FolderName, 0

         FROM

                Documents d

                INNER JOIN folders f on d.FolderID = f.FolderID

         WHERE

                DocumentID = @DocumentID

         UNION ALL

         SELECT

                DocumentID, f.FolderID, f.ParentFolderID, DocumentName, f.FolderName, p.LevelNumber + 1

         FROM

                Folders f

                INNER JOIN DirectoryPathCTE p on p.ParentFolderID = f.FolderID

         )

         SELECT @ReturnPath =

         (

                SELECT

                   FolderName + '' + CASE WHEN LevelNumber = 0 THEN DocumentName ELSE '' END

                FROM

                        DirectoryPathCTE p

                ORDER BY LevelNumber DESC

                FOR XML PATH('')

         )     

         RETURN(@ReturnPath)    

 END

 GO

Sekarang setelah fungsi ini dibuat, saya dapat memanggilnya untuk dokumen-dokumen yang saya punya di tabel Documents, dan jalur (path) untuk file tersebut akan dibuat berdasarkan DocumentID di tabel Documents.

SELECT dbo.udf_BuildDocumentPath(d.DocumentID)

 FROM Documents d

Tertarik dengan artikel ini? Silakan Subscribe dan .
Kalo kamu lebih suka baca artikel lewat email, subscribe lewat Email dan artikel-artikel terbaru akan segera terkirim ke email Anda.
Tertarik gabung dengan komunitas ITEKNOers? Klik aja