Questo articolo ci mostra come creare le tabelle che compongono un database SQL Server utilizzando un semplice Script, partiremo con una descrizione del database e di quelle che saranno le sue caratteristiche per poi trasformare la nostra descrizione in script.
Introduzione
Prima di descrivere che cosa ci sarà nel nostro database di studio, utilizzeremo lo script creato nel precedente articolo per creare il database vuoto. per farlo effettueremo due operazioni:
- Sul disco D:\Sql.dir\Data, la cartella dove ho indicato a SQL Server di tenere i propri database, andiamo a generare la cartella dove inseriremo il database
Perché è necessario creare la cartella prima di creare il database, semplicemente perché lo script SQL non ha il permesso di creare cartelle sul disco, pertanto se la cartella non esiste non potrà creare i files del database. - Utilizzeremo lo script preparato nell’articolo precedente, che è disponibile QUI, per creare un database che chiameremo “Recipes”. Per praticità, ne riporto solo la parte centrale qui sotto:
CREATE DATABASE [Recipes] ON PRIMARY (NAME = 'Recipes', FILENAME = 'D:\SQL.DIR\Data\Recipes\Recipes.mdf', SIZE = 10MB, MAXSIZE = 1024MB, FILEGROWTH = 10MB ), FILEGROUP [BINARYDATA] (NAME = 'Recipes_Bin', FILENAME = 'D:\SQL.DIR\Data\Recipes\Recipes_Bin.ndf', SIZE = 50MB, MAXSIZE = 2048MB, FILEGROWTH = 50MB ) LOG ON (NAME = 'Recipes_Log', FILENAME = 'D:\SQL.DIR\Data\Recipes\Recipes_Log.ldf', SIZE = 10MB, MAXSIZE = 1024MB, FILEGROWTH = 50%) GO
Il caso di studio, cosa deve contenere il nostro database
Essendo una appassionata di cucina, e chi è stato al primo Workshop di Dotnetwork.it può decantarvi i miei meravigliosi biscottini, ho deciso di creare un database per memorizzare delle ricette di cucina, tale database molto semplice conterrà quanto necessario a rappresentare una ricetta. pertanto ecco lo schema del database che andremo a generare:
Lo schema qui sopra, come potete osservare, contiene le seguenti tabelle:
- TbRecipes, la tabella delle ricette, che contiene oltre ad una chiave univoca un campo per inserire i dati di preparazione della ricetta.
- TbIngredients, la tabella degli ingredienti, che contiene quanto necessario a identificare un ingrediente e la sua unità di misura.
- TbMeasureUnits, la tabella delle unità di misura, che contiene quanto necessario a creare un unità di misura che prevede due standard, questo perché intendo fare in modo che automaticamente gli ingredienti possano essere rappresentati nei due sistemi più usati su internet, il sistema metrico e il sistema US Standard Customary Units che prevede le libbre, le once, le tazze e quant’altro. Le mie unità di misura dovranno permettere di inserire il valore nello standard decimale e convertirlo in quello US o viceversa, in base a come sceglie di lavorare chi userà il database.
- TbRecipeIngredients, è la tabella che collega gli ingredienti ad una ricetta con la loro quantità
- TbRecipeLinks, è la tabella che permette di inserire dei link ipertestuali in una ricetta, ad esempio il webcast dove viene illustrato come si fa.
- TbVersions, essendo un database di produzione a tutti gli effetti, questa sarà la tabella che permetterà di gestire il versioning del database, quindi aggiornarlo da un applicazione, verificare ed eseguire una applicazione che si collega al database solo se la versione è compatibile oppure basare gli script di aggiornamento sulla versione del database.
Il primo step nella creazione del database
La prima cosa da fare per costruire il database è creare le tabelle al suo interno, pertanto creiamo tutte le tabelle che abbiamo descritto utilizzando uno script, eccone la prima parte.
/* ID:2013.00.00.00_01 01-CreateTables.sql
*******************************************************************************
Ricordarsi di sostituire ##DNWDbName## con il nome che si intende dare al DataBase
*******************************************************************************
*/
USE [##DNWDbName##]
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('[dbo].[TbIngredients]') AND type in ('U')) BEGIN
PRINT 'CREATE TABLE [TbIngredients]'
CREATE TABLE [dbo].[TbIngredients](
[IDIngredient] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[Ingredient] [nvarchar](255) NULL,
[IDMeasureUnit] [nvarchar](20) NULL,
[LastUpdated] [timestamp] NULL
) ON [PRIMARY]
END
Cosa facciamo per creare uno script neutro e ripetibile, innanzitutto, come ho detto non assegnamo un nome predefinito al database, perché magari potrebbe servirci averne più di uno per fare i test, quindi chiamarlo per forza Recipes sarebbe poco opportuno, faremo la sostituzione prima di eseguire lo script.
Come potete notare, abbiamo dato un ID e un nome al file che eseguiremo, serve sempre perché quando lavoriamo in produzione è importante aver traccia di quando e come abbiamo creato uno script, perché magari si può correggerlo in futuro oppure sostituirlo e soprattutto sapere qual’è l’ordine in cui gli script devono essere eseguiti, di qui il prefisso numerico nel nome del file.
Se vi chiedete perché non abbia fatto uno script unico per fare tutto, ci sono 2 motivi, primo motivo, per dividere in vari post le lezioni. Secondo motivo, cercare dentro uno script di mille righe o più diventa difficile e pericoloso, meglio avere uno script per ogni azione più pulito da manutenere.
Vi faccio notare una sola cosa nello script qui sopra, ho deciso di utilizzare un GUID (Uniqueidentifier) per la chiave primaria delle tabelle principali, perché se per caso volessi scambiare ricette con un amica che ha il mio stesso software, la procedura di invio dati da scrivere nel programma sarebbe probabilmente molto semplice.
Non vi tedio con lo script di tutte le altre tabelle, perché lo trovate nel codice esempio, aggiungo solo il codice relativo alla tabella di versioning del database.
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('[dbo].[TbVersions]') AND type in ('U')) BEGIN
PRINT 'CREATE TABLE TbVersions'
CREATE TABLE [dbo].[TbVersions] (
[IDVersion] nvarchar(32) NOT NULL,
[UpdateData] datetime NULL CONSTRAINT [DF_TbVersions_UpdateData] DEFAULT (getdate()),
[UpdateDescription] nvarchar(255) NULL,
[PrgVersion] nvarchar(32) NULL,
[SysUser] nvarchar(255) NULL CONSTRAINT [DF_TbVersions_SysUser] DEFAULT (suser_sname())) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT 1 FROM TbVersions WHERE IDVersion = '2013.00.00.00_01')
INSERT INTO TbVersions (IDVersion, UpdateDescription, PrgVersion) VALUES ('2013.00.00.00_01', '01-CreateTables', '2013.0.0.0');
GO
Si tratta di una tabella creata in modo arbitrario da me, contiene un ID che ha la forma di una versione file di Visual Studio, che sempre arbitrariamente io inizio con l’anno in cui scrivo lo script. Una descrizione di quello che è l’aggiornamento che lo script inserito ha fatto sul database, che solitamente per pigrizia è il nome del file, una versione del programma, che ci permette da programma di controllare se il database a cui ci colleghiamo è compatibile con la versione del software da cui stiamo collegandoci, questo permetterà ad esempio di creare delle funzioni di aggiornamento automatico del database al momento dell’installazione di una nuova release database.
Ci sono anche 2 campi compilati automaticamente dal sistema che indicano quando è stato fatto l’aggiornamento e da quale utente di sistema.
Le ultime tre righe, inseriscono la riga relativa allo script di creazione tabelle all’interno della tabella versioni stessa.
Conclusioni
Abbiamo creato il database nel nostro server, abbiamo creato le tabelle che lo compongono, però come potete notare, non abbiamo ancora creato le Primary Keys o le foreign keys, di questo ci occuperemo nella puntata successiva.
Gli script T-SQL del codice di esempio sono scaricabili al seguente link e sono comprensivi di tutto quello che è spiegato nella breve serie di articoli.
