Converti MDF in SQLITE

Un database SQL Server potrebbe dover diventare un file SQLite portatile per un'applicazione desktop, una distribuzione embedded, attività di testing o distribuzione senza richiedere un'istanza SQL Server. La conversione è un'esportazione tra database engine, non una semplice modifica dell'estensione del file: un file MDF deve prima essere aperto da SQL Server e il relativo schema e le righe devono essere copiati in SQLite.

Che cos'è il formato MDF

MDF è il formato del file di dati principale utilizzato da Microsoft SQL Server. Contiene oggetti del database quali tabelle, indici, viste, stored procedure e dati delle tabelle; un file .ldf associato contiene normalmente il transaction log.

Si incontrano file MDF quando si riceve un backup di un database SQL Server o un database detached, si copia un database tra server Windows oppure si lavora con applicazioni basate su SQL Server. Microsoft SQL Server, SQL Server Express, SQL Server Developer e SQL Server LocalDB possono collegare e leggere file MDF. Un MDF non è un formato di interscambio autonomo e gli strumenti SQLite normalmente non possono aprirlo direttamente.

Che cos'è il formato SQLITE

SQLite archivia un intero database relazionale in un singolo file, utilizzando comunemente l'estensione .sqlite, .sqlite3 o .db. Le applicazioni lo utilizzano per l'archiviazione locale, il software embedded, il testing, i dati mobili, il funzionamento offline e lo scambio di dati basato su file.

SQLite non richiede un database server ed è facile da copiare o archiviare. Il suo type system è più flessibile di quello di SQL Server, ma non fornisce equivalenti diretti per ogni funzionalità di SQL Server. Stored procedure, SQL Server Agent jobs, linked servers, autorizzazioni e molte impostazioni a livello di server non possono essere trasferiti in un file SQLite.

Come convertire effettivamente MDF in SQLITE

1. Collegare l'MDF a SQL Server

Installare un'edizione compatibile di SQL Server, preferibilmente Developer per attività non di produzione o Express per un'istanza locale leggera. In SQL Server Management Studio, connettersi all'istanza e scegliere Databases → Attach. Aggiungere il file MDF e verificare il percorso del file di log visualizzato.

Se il database originale è stato detached correttamente, il relativo file LDF dovrebbe essere disponibile accanto all'MDF. Non eseguire comandi di riparazione o di ricostruzione del log sull'unica copia. Un database copiato mentre SQL Server lo stava utilizzando potrebbe essere incoerente e dovrebbe invece essere ripristinato da un backup SQL Server.

2. Esaminare lo schema di origine

Controllare tabelle, viste, chiavi, colonne identity, colonne computed, trigger, collations e tipi specifici di SQL Server non supportati in SSMS. Una query come SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'; elenca le tabelle di base che devono essere esportate.

3. Esportare le tabelle con uno strumento consapevole del database

Per una conversione ripetibile, utilizzare Python con pyodbc per SQL Server e il modulo sqlite3 della standard library. Installare il SQL Server ODBC driver ed eseguire:

python -m pip install pyodbc

L'esempio seguente copia una tabella. Sostituire i dettagli della connessione e il nome della tabella, quindi ripetere l'operazione per ogni tabella necessaria dopo aver creato lo schema SQLite:

import sqlite3
import pyodbc

src = pyodbc.connect(
    "DRIVER={ODBC Driver 18 for SQL Server};"
    "SERVER=localhost;DATABASE=SourceDb;Trusted_Connection=yes;"
    "Encrypt=no;"
)
dst = sqlite3.connect("converted.sqlite")
source_table = "dbo.Customers"
target_table = "Customers"

cur = src.cursor()
cur.execute(f"SELECT * FROM {source_table}")
columns = [d[0] for d in cur.description]

def quote(name):
    return '"' + name.replace('"', '""') + '"'

def sqlite_type(value):
    if isinstance(value, (bytes, bytearray, memoryview)):
        return "BLOB"
    if isinstance(value, bool):
        return "INTEGER"
    if isinstance(value, int):
        return "INTEGER"
    if isinstance(value, float):
        return "REAL"
    return "TEXT"

first = cur.fetchone()
if first is not None:
    types = [sqlite_type(v) for v in first]
    dst.execute("CREATE TABLE " + quote(target_table) + " (" +
                ", ".join(quote(c) + " " + t for c, t in zip(columns, types)) + ")")
    placeholders = ",".join("?" for _ in columns)
    insert = "INSERT INTO " + quote(target_table) + " VALUES (" + placeholders + ")"
    dst.executemany(insert, [first] + cur.fetchall())

dst.commit()
src.close()
dst.close()

Per una migrazione completa, creare esplicitamente le tabelle dallo schema SQL Server invece di dedurre i tipi dalla prima riga. Esportare le tabelle parent prima delle tabelle child, inserire le righe, quindi aggiungere foreign key e indici. Un'opzione grafica è DB Browser for SQLite dopo aver esportato ogni tabella SQL Server in CSV, ma CSV perde i constraint, i tipi esatti, la nullability e i valori binari, quindi è più adatto a dataset piccoli o piatti.

4. Convalidare il file risultante

Aprire converted.sqlite in DB Browser for SQLite o SQLiteStudio e confrontare il numero di righe utilizzando query SQL Server e SQLite come SELECT COUNT(*) FROM Customers;. Eseguire join rappresentativi, controllare date e testo Unicode e verificare che primary key, indici e foreign key siano stati ricreati.

Qualità, compatibilità e limitazioni

Le colonne SQL Server IDENTITY diventano normalmente chiavi integer SQLite, ma SQLite non riproduce il comportamento di generazione degli identity a meno che lo schema di destinazione non utilizzi correttamente INTEGER PRIMARY KEY. SQL Server UNIQUEIDENTIFIER, DECIMAL, XML, geography, hierarchyid, datetimeoffset e varbinary(max) richiedono mapping specifici; i GUID possono essere archiviati come testo o blob da 16 byte e i valori decimal ad alta precisione non dovrebbero essere convertiti tramite floating point binario.

Viste e query richiedono spesso una riscrittura SQL perché le funzioni SQL Server, la sintassi con parentesi quadre, le funzioni per le date e i join non sono completamente compatibili con SQLite. Stored procedure, trigger, indici full-text, autorizzazioni e automazione lato server devono essere riprogettati o omessi. SQLite consente inoltre lettori concorrenti, ma presenta comportamenti diversi per il write-locking e le transazioni, quindi un file convertito non è un sostituto diretto di un database SQL Server multiutente.

Gli online converter sono generalmente inadatti ai file MDF perché necessitano di un SQL Server engine per collegare e interpretare il database, inoltre il caricamento di un MDF potrebbe divulgare tutti i dati contenuti. Mantenere la conversione in locale, a meno che il database non sia stato sanificato e il provider non disponga di una chiara security policy e di una policy di conservazione dei dati.