Konwertuj MDF na SQLITE

Baza danych SQL Server może wymagać przekształcenia w przenośny plik SQLite na potrzeby aplikacji desktopowej, wdrożenia wbudowanego, testowania lub dystrybucji bez konieczności korzystania z instancji SQL Server. Konwersja polega na eksporcie między silnikami baz danych, a nie na prostej zmianie rozszerzenia pliku: plik MDF musi najpierw zostać otwarty przez SQL Server, a następnie jego schemat i wiersze muszą zostać skopiowane do SQLite.

Czym jest format MDF

MDF to podstawowy format pliku danych używany przez Microsoft SQL Server. Zawiera obiekty bazy danych, takie jak tabele, indeksy, widoki, procedury składowane i dane tabel; towarzyszący mu plik .ldf zwykle zawiera dziennik transakcji.

Z plikami MDF można się spotkać podczas otrzymywania kopii zapasowej bazy danych SQL Server lub odłączonej bazy danych, kopiowania bazy danych między serwerami Windows albo pracy z aplikacjami utworzonymi w oparciu o SQL Server. Microsoft SQL Server, SQL Server Express, SQL Server Developer oraz SQL Server LocalDB mogą dołączać i odczytywać pliki MDF. MDF nie jest samodzielnym formatem wymiany, a narzędzia SQLite zwykle nie mogą otworzyć go bezpośrednio.

Czym jest format SQLITE

SQLite przechowuje całą relacyjną bazę danych w jednym pliku, zwykle używając rozszerzenia .sqlite, .sqlite3 lub .db. Aplikacje używają go do przechowywania danych lokalnie, wbudowanego oprogramowania, testowania, danych mobilnych, pracy offline oraz wymiany danych opartej na plikach.

SQLite nie wymaga serwera bazy danych i można go łatwo kopiować lub archiwizować. Jego system typów jest bardziej elastyczny niż system SQL Server, ale nie zapewnia bezpośrednich odpowiedników wszystkich funkcji SQL Server. Procedury składowane, zadania SQL Server Agent, serwery połączone, uprawnienia i wiele ustawień na poziomie serwera nie mogą zostać przeniesione do pliku SQLite.

Jak faktycznie przekonwertować MDF do SQLITE

1. Dołącz plik MDF do SQL Server

Zainstaluj zgodną edycję SQL Server, najlepiej Developer do zastosowań nieprodukcyjnych lub Express w przypadku lekkiej instancji lokalnej. W SQL Server Management Studio połącz się z instancją i wybierz Databases → Attach. Dodaj plik MDF i sprawdź wyświetloną ścieżkę do pliku dziennika.

Jeśli oryginalna baza danych została prawidłowo odłączona, jej plik LDF powinien znajdować się obok pliku MDF. Nie uruchamiaj poleceń naprawy ani odbudowy dziennika na jedynej kopii. Baza danych skopiowana podczas korzystania z niej przez SQL Server może być niespójna i zamiast tego należy ją odtworzyć z kopii zapasowej SQL Server.

2. Sprawdź schemat źródłowy

W SSMS sprawdź tabele, widoki, klucze, kolumny identity, kolumny obliczane, wyzwalacze, sortowania oraz nieobsługiwane typy charakterystyczne dla SQL Server. Zapytanie takie jak SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'; wyświetla tabele bazowe, które należy wyeksportować.

3. Eksportuj tabele za pomocą narzędzia obsługującego bazy danych

W przypadku powtarzalnej konwersji użyj języka Python wraz z pyodbc dla SQL Server oraz standardowego modułu sqlite3. Zainstaluj sterownik ODBC dla SQL Server i uruchom:

python -m pip install pyodbc

Poniższy przykład kopiuje jedną tabelę. Zastąp dane połączenia i nazwę tabeli, a następnie powtórz operację dla każdej wymaganej tabeli po utworzeniu schematu 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()

W przypadku pełnej migracji utwórz tabele jawnie na podstawie schematu SQL Server, zamiast wnioskować o typach na podstawie pierwszego wiersza. Eksportuj tabele nadrzędne przed tabelami podrzędnymi, wstaw wiersze, a następnie dodaj klucze obce i indeksy. Opcją graficzną jest DB Browser for SQLite po wyeksportowaniu każdej tabeli SQL Server do formatu CSV, ale CSV traci ograniczenia, dokładne typy, możliwość przechowywania wartości NULL oraz wartości binarne, dlatego lepiej sprawdza się w przypadku małych lub płaskich zbiorów danych.

4. Zweryfikuj wynikowy plik

Otwórz plik converted.sqlite w DB Browser for SQLite lub SQLiteStudio i porównaj liczbę wierszy za pomocą zapytań SQL Server i SQLite, takich jak SELECT COUNT(*) FROM Customers;. Wykonaj reprezentatywne złączenia, sprawdź daty i tekst Unicode oraz upewnij się, że klucze główne, indeksy i klucze obce zostały odtworzone.

Jakość, zgodność i ograniczenia

Kolumny SQL Server IDENTITY zwykle stają się kluczami całkowitymi SQLite, ale SQLite nie odtwarza zachowania automatycznego generowania wartości, chyba że schemat docelowy odpowiednio używa INTEGER PRIMARY KEY. Typy SQL Server UNIQUEIDENTIFIER, DECIMAL, XML, geography, hierarchyid, datetimeoffset i varbinary(max) wymagają przemyślanych mapowań; identyfikatory GUID można przechowywać jako tekst lub obiekty blob o rozmiarze 16 bajtów, a liczby dziesiętne o wysokiej precyzji nie powinny być konwertowane za pomocą binarnej reprezentacji zmiennoprzecinkowej.

Widoki i zapytania często wymagają przepisania SQL, ponieważ funkcje SQL Server, składnia nawiasów kwadratowych, funkcje daty i złączenia nie są w pełni zgodne z SQLite. Procedury składowane, wyzwalacze, indeksy pełnotekstowe, uprawnienia i automatyzacja po stronie serwera muszą zostać przeprojektowane lub pominięte. SQLite umożliwia również jednoczesny odczyt przez wielu użytkowników, ale ma inny sposób blokowania zapisu i obsługi transakcji, dlatego przekonwertowany plik nie jest bezpośrednim zamiennikiem wieloużytkownikowej bazy danych SQL Server.

Konwertery online zasadniczo nie nadają się do plików MDF, ponieważ potrzebują silnika SQL Server do dołączenia i zinterpretowania bazy danych, a przesłanie pliku MDF może ujawnić wszystkie zawarte w nim dane. Konwersję wykonuj lokalnie, chyba że baza danych została zanonimizowana, a dostawca ma jasną politykę bezpieczeństwa i przechowywania danych.