Projekt pod różne DBMS - różnice w SQL

Cześć, problem jest taki. Załóżmy, że aplikacja może pracować zarówno na MSSQL, MySQL(MariaDB), jak i SQLite. I tak faktycznie będzie pracować. I teraz pytanie, jak to ogarnąć nie używając ORMa.

Chodzi mi o to, że mam sobie jakąś klasę bazową, np(pseudokod):

abstract class DatabaseEngine
{
    IDbConnection connection;

    public DatabaseEngine(IDbConnection connection)
    { 
        this.connection = connection;
    }

    public void Connect(string connectionString)
    {
        connection.Open(connectionString);
    }

    //i jakieś metody do wywoływania zapytań w stylu ExecuteQuery, SelectSQL, obsługa transakcji itd.
}

Teraz mam klasy, które po tym dziedziczą:

class MSSQLDb: DatabaseEngine
{
    public MSSQLDb(): base(new MSSQLConnection())
    {

    }
}

class MySQLDb: DatabaseEngine
{
    public MySQLDb(): base(new MySQLConnection())
    {

    }
}

class SQLiteDb: DatabaseEngine
{
    public SQLiteDb(): base(new SQLiteConnection())
    {

    }
}

I fajnie. Mam teraz np. takie repozytorium:

class PersonRepository: Repository<Person>
{
    DatabaseEngine dbContext; //jakoś to przekazuję
    public List<Person> GetAll()
    {
         
    }
}

I teraz tak. W metodzie GetAll powinienem wywołać jakiegoś SQL. Ale problem jest taki, że na różnych DBMS ten SQL może wyglądać nieco inaczej. Np.
MSSQL:

SELECT pole1, pole2, pole3
FROM tabela

MySQL:

SELECT jakas_funkcja(pole1) as pole1, pole2, pole3
FROM tabela

Itd. Jak to pogodzić? W jaki sposób zaprojektować taki system? Chciałbym uniknąć duplikowania zapytań, ale nie za bardzo wiem jak to zaprojektować. Jedyny pomysł na jaki wpadłem to użycie procedur składowanych. Ale... SQLite nie ma procedur składowanych ;)

Nie mam pomysłu, jak to DOBRZE zaprojektować. Próbuję wymyślić coś z jakąś dodatkową warstwą, ale nie za bardzo widzę to póki co. Jakieś sugestie?

Wygląda na to, że próbujesz rozwiązać w ogólny sposób bliżej niesprecyzowany problem :-)
Przy ograniczeniach:

• nie używać ORMa
• nie duplikować zapytań
• wspierać różne silniki bazodanowe

Zauważ, że biznesowo będziesz zawsze miał jakiś interfejs, który będzie realizowany przez jakiś komponent.

public interface EmployeeRepository {
	List<Employee> findEmployee(Serializable id);
}

Gdzieś w bebechach komponentu pojawi się jakieś zapytanie bazodanowe. Samo się nie wygeneruje, więc będziesz pisał je Ty.

Masz:

• sporo standardów SQL (SQL-92 SQL:1999 SQL:2003 SQL:2006 SQL:2008 SQL:2011 SQL:2016 i pewnie trochę starszych)
• sporo silników bazodanowych
• silniki są mnie lub bardziej zgodne z różnymi standardami
• silniki mają rozszerzenia w stosunku do standardów

Podejście 1) Znasz różnice w implementacjach silników bazodanowych i piszesz zapytania nieoptymalne, ale działające na różnych silnikach. Czyli tu czynnikiem ograniczającym fantazję dewelopera jest zbiór silników bazodanowych i zbiór wspólnych cech ze standardów wspieranych przez wybrane silniki.

Podejście 2) Piszesz jakąś warstwę, który generuje SQLa właściwego dla danego silnika? (Jak to się różni od ORMa? :) )

Dlaczego nie chcesz korzystać z gotowego ORMA?

Nie chcę gotowego ORMa, bo go nie potrzebuję. To nie jest system bazodanowy, tylko dosłownie kilka insertów, selectów i updateów. Poza tym to coś w rodzaju API i nie chcę wbijać do tego zewnętrznych frameworków, bo to się mija z celem. Dodałbyś EntityFramework do aplikacji, gdzie wykonujesz 3 - 5 selectów z bazy danych?

Tak, jak napisałem - wiem, że na MySQL będę musiał użyć jakiejś funkcji przy pobieraniu pola (tak, jak w przykładzie, który podałem), a w MSSQL nie będę musiał tego robić. I wolałbym nie duplikować zapytań, no bo jeśli będę miał 3 DBMS, to przy jakiejś zmianie będę musiał modyfikować 3 zapytania, co też jest bez sensu.

Czemu zakładasz, że zapytania generowane przez ORM będą bardziej optymalne niż te pisane przeze mnie? ;)

Mam jakąś taką wizję, że repozytorium ma jakiś "domyślny" SQL do wygenerowania, ale w jakiś magiczny sposób daje znać jakimś klasom, że aktualnie pobiera pracowników i mogą sobie jakoś zmienić tego SQLa. Ale najlepiej nie pisząc go na nowo. Coś w stylu:

if(fieldName == "pole1")
  fieldValue == "jakas_funkcja(pole1) as pole1";

Nie do końca mi się to podoba i nie wiem, czy chcę iść w tą stronę, ale nie ogarnąłem jeszcze żadnego innego sposobu.

Dodałbyś EntityFramework do aplikacji, gdzie wykonujesz 3 - 5 selectów z bazy danych?

Tak. Ostatnio tak zrobiłem. Chyba 1 insert, 1 update i 1 select. Nie lubię pisać od nowa czegoś, co ktoś już ładnie napisał.

Juhas napisał(a):

Serio? Zaprzęgać cały framework do trzech zapytań? :D

Instalacja takiego EntityFrameworka i skonfigurowanie klasy dziedziczącej po DbContext to dosłownie kilka minut pracy. A skoro chcesz używać appki z kilkoma bazami danymi, to całkiem nie rozumiem dlaczego się tak bronisz przed ORMem. On dokładnie do takich celów służy, ma cię odciążyć od myślenia "jak napisać zapytanie, by działało na serwerze X".

EntityFramework to nie jest jedyny słuszny ORM. Polecam zobaczyć np.:
https://github.com/markrendle/Simple.Data
https://github.com/StackExchange/Dapper
https://github.com/FransBouma/Massive
Są to małe, lekkie i szybkie ORMy, które wyeliminują potencjalne problemy, które zapewne zaraz się u Ciebie pojawią (np. SQLInjection).

Tak, jak napisałem - wiem, że na MySQL będę musiał użyć jakiejś funkcji przy pobieraniu pola (tak, jak w przykładzie, który podałem), a w MSSQL nie będę musiał tego robić. I wolałbym nie duplikować zapytań, no bo jeśli będę miał 3 DBMS, to przy jakiejś zmianie będę musiał modyfikować 3 zapytania, co też jest bez sensu.

Jak masz tego 3-5, to nie szukałbym mega uniwersalnych rozwiązań, tylko trzymał zapytania w pliku konfiguracyjnym.

mysql.employee.query1=select x,y,z from a where id=#id#
oracle.employee.query1=select func(x),y,z from a where id=#id#
mssql.employee.query1=select func(x),func(y),z from a where id=#id#

W kodzie używał odwołań do pliku konfiguracyjnego via jakieś mało wyszukane repozytorium świadome dla jakiej bazy ma zwracać
treść zapytania:

	QueryRepository queryRepository = new QueryRepository("oracle");
	Query query = prepareQuery( queryRepository.getQueryText("employee.query1") );
	query.bindParameter(query,1,"123");
	ResultSet rs = query.execute();

Czemu zakładasz, że zapytania generowane przez ORM będą bardziej optymalne niż te pisane przeze mnie? ;)

Nie zakładam tego, po prostu w przypadku opcji 1) zapytania mogą być nieoptymalne w ramach konkretnego silnika bazodanowego, ale za to składniowo zgodne z zakresem standardu SQL-xyz, wspieranego przez wszystkie z wybranych przez Ciebie silników.

Poza tematem.. Nie sądzisz, że jak potrzebujesz funkcji do pobierania danych w zależności od bazy, to coś jest nie tak z projektem? Baza to tylko skład na dane, a Ty chyba chcesz mieć jakąś dodatkową logikę tam umieszczoną (stąd funkcje) i skończysz z rozsmarowaną logiką, trochę na bazie, trochę w aplikacji..

yarel napisał(a):

Poza tematem.. Nie sądzisz, że jak potrzebujesz funkcji do pobierania danych w zależności od bazy, to coś jest nie tak z projektem? Baza to tylko skład na dane, a Ty chyba chcesz mieć jakąś dodatkową logikę tam umieszczoną (stąd funkcje) i skończysz z rozsmarowaną logiką, trochę na bazie, trochę w aplikacji..

Ogólnie to masz rację, ale w tym wypadku sprawa jest banalna.
Przykładowo MySQL nie zna czegoś takiego jak GUID. Żeby przechować GUID w tabeli, trzeba zrobić kolumnę binarną o odpowiedniej wielkości, a potem przy zapisie i odczycie stosować jakieś wymyślne funkcje w stylu hex, unhex i jeszcze string replace (przy zapisie żeby pozbyć się "-" ze stringa reprezentującego GUID). Pewnie, mógłbym trzymać GUID jako string, ale to się mija z celem :)

Natomiast Twoje rozwiązanie wydaje się być całkiem sensowne dla mnie, mimo że duplikuje zapytania. Przyjrzę się temu bliżej.

Liczba tych zapytań i ich treść jest stała? To wystarczy klasa bazowa z uniwersalnymi zapytaniami i klasy dziedziczące nadpisujące jej metody do konkretnych SZBD albo w ogóle definiujące w przypadkach gdy nie da się zrobić uniwersalnie.

@yarel:

Nie sądzisz, że jak potrzebujesz funkcji do pobierania danych w zależności od bazy, to coś jest nie tak z projektem? Baza to tylko skład na dane, a Ty chyba chcesz mieć jakąś dodatkową logikę tam umieszczoną (stąd funkcje)

A o stored procedures kolega słyszał? Poza tym dobre DB to nie tylko skład na dane, ale również bardzo dobre miejsce na wiele obliczeń i walidacji. Przykładowo masz tabelę wizyt u doktora i nie chcesz by 2 wizyty się na siebie nakładały (dla jednego doktora), oczywiście, że możesz zrobić taki test po stronie aplikacji, ale wiąże się z tym parę problemów, np. test oraz rollback w przypadku niepowodzenia musi się odbyć w transakcji, a i wtedy nie wiem, czy nie może się przypadkiem zdarzyć race condition i wylądujesz z pokrywającymi się wizytami. Dodatkowo o samej transakcji jest czasem łatwo zapomnieć. A po stronie DB? Jeden indeks i wszystko masz załatwione. I masz 100% pewność, że nic się nie będzie pokrywało.

Tylko że logika w aplikacji jest łatwiejsza do ogarnięcia niz w bazach danych ;)

hauleth napisał(a):

@yarel:

Nie sądzisz, że jak potrzebujesz funkcji do pobierania danych w zależności od bazy, to coś jest nie tak z projektem? Baza to tylko skład na dane, a Ty chyba chcesz mieć jakąś dodatkową logikę tam umieszczoną (stąd funkcje)

A o stored procedures kolega słyszał? Poza tym dobre DB to nie tylko skład na dane, ale również bardzo dobre miejsce na wiele obliczeń i walidacji. Przykładowo masz tabelę wizyt u doktora i nie chcesz by 2 wizyty się na siebie nakładały (dla jednego doktora), oczywiście, że możesz zrobić taki test po stronie aplikacji, ale wiąże się z tym parę problemów, np. test oraz rollback w przypadku niepowodzenia musi się odbyć w transakcji, a i wtedy nie wiem, czy nie może się przypadkiem zdarzyć race condition i wylądujesz z pokrywającymi się wizytami. Dodatkowo o samej transakcji jest czasem łatwo zapomnieć. A po stronie DB? Jeden indeks i wszystko masz załatwione. I masz 100% pewność, że nic się nie będzie pokrywało.

1. Clue komentarza dotyczyło rozsmarowywania logiki, trochę w aplikacji, trochę w bazie. Jeśli logika dotyka tych samych obiektów, z dwóch różnych miejsc, to może wystąpić konieczność synchronizacji dostępu do tych samych danych z poziomu DB i z poziomu aplikacji. Co wówczas? Dedykowany semafor utrzymywany... na bazie danych jako wiersz w tabelce? Oprócz rywalizacji na poziomie jednego zasobu (wiersz w tabeli), w zależności od tego jak silnik bazodanowy jest zaimplementowany ( odczyty/zapisy - blokujące/nieblokujące), może dojść do sytuacji, w której uzyskanie "locka na wierszu" może być średnio przenośne na inne silniki.

np. taki SELECT FOR UPDATE? - Oracle blokuje na poziomie wiersza. Portujesz z Oracla na MSSQL, zaczynają pojawiać się deadlocki i co? "MSSQL jest słaby bo są deadlocki, a na oraclu nie było". Zdaje się, że musisz mu powiedzieć co ma robić (ALLOW_ROW_LOCKS =ON, ALLOW_PAGE_LOCKS =OFF), co dla programisty traktującego bazę jako "złoty środek do przechowywania stanu współdzielonego" może być nieoczywiste.

1. O SP słyszałem, jedną czy dwie nawet napisałem ;)

Przykład z indeksem (unique - zdaje się, że ten masz na myśli, sądząc po opisie). Dwie transakcje, każda wstawia/aktualizuje unikalne pole i każdej się to udaje w ramach jej sesji (transakcje są izolowane) i dopiero zatwierdzenie transakcji spowoduje błąd u jednej z nich (przynajmniej przy nieblokujących zapisach). Tak czy inaczej błąd o naruszeniu unikalności trzeba obsłużyć po stronie aplikacji.

Z perspektywy aplikacji wszystko co potrzebujesz to synchronizacja dostępu do "lekarza". Jak masz aplikację rozproszoną, to potrzebujesz rozproszonej wersji locka i tyle.
Oczywiście możesz sobie takiego locka implementować na bazie danych, nawet nie wiedząc, że go niejawnie implementujesz. Minus będzie taki, że skalowalność locka będzie mocno związana ze skalowalnością bazy. Responsywność również :)

1. Żeby nie było, że bazy są złe. W moim przypadku:

• są super do przetwarzania danych (analizy, migracje danych)
• super sprawdzają się do raportowania