Spojrzenie w DAL

Po stworzeniu bazy danych, nadszedł wreszcie czas na pierwsze linie kodu. Na początek – generowanego automatycznie.

Object-relational mapping
Wśród technologii wybranych do realizacji projektu wymieniłem LINQ to SQL – i tego właśnie narzędzia ORM będę używał.
(Dygresja: spotkałem się z dwoma tłumaczeniami nazwy Language-Integrated Query na język polski: zintegrowany język zapytań, język zapytań zintegrowanych. Czy poprawnym tłumaczeniem nie byłoby: zapytanie (zapytania) zintegrowane z językiem?)
Aby wprawić Linq2Sql w ruch, należy stworzyć odpowiedni DataContext – klasę, która będzie realizowała połączenie z bazą danych. W tym celu wysłużę się programem SqlMetal. Jest to narzędzie generujące potrzebny mi kod na podstawie pliku bazy danych. Jako że nie trawię aplikacji konsolowych, korzystam z SqlMetal Builder (interfejsu graficznego dla SqlMetal). Chwała automatyzacji, ponad trzy tysiące linii kodu napisały się same w kilka sekund.

Repository Pattern
Kolej na kodowanie własnoręczne. W tworzeniu warstwy dostępu do danych wykorzystam wzorzec projektowy repository. Opiera się on na klasach reprezentujących repozytorium dla każdego obiektu biznesowego. Repozytoria powinny udostępniać standardowe operacje CRUD, a także inne, właściwe dla poszczególnych obiektów biznesowych, operacje (np. selekcji).
Tworzę zatem bazowy interfejs repozytorium:

namespace InvoiceInvoker.Logic.RepositoryInterfaces
{
	public interface IRepository<T>
	{
		T GetById(int id);
		void Add(T item);
		void Remove(int itemId);
		void Update(T item);
	}
}

Niektóre obiekty wymagają rozszerzenia tego interfejsu. Przykładowo, faktury:

public interface IInvoiceRepository : IRepository<Invoice>
{
	List<Invoice> GetAll();
	List<Invoice> GetByStatus(string status);
	List<Invoice> GetByDate(DateTime dateFrom, DateTime dateTo);
	List<Invoice> GetByCustomer(string companyName);
	List<Invoice> GetByProduct(string productName);
	List<Invoice> GetByValue(decimal valueFrom, decimal valueTo);
	List<Invoice> GetByExpression(Func<Invoice, bool> expression);
}

Implementacja repozytoriów
Korzystanie z klasy DataContext rodzi pytanie: jaki powinien być czas życia pojedynczego jej obiektu? Jedna instancja na cały projekt, na jedno repozytorium, czy na jedną operację? MSDN podaje:
In general, a DataContext instance is designed to last for one „unit of work” however your application defines that term. A DataContext is lightweight and is not expensive to create. A typical LINQ to SQL application creates DataContext instances at method scope (…)
Mam zatem odpowiedź. Mogę przejść do implementacji poszczególnych repozytoriów. Przykładowe metody repozytorium faktur:

public class InvoiceRepository : InvoiceInvoker.Logic.RepositoryInterfaces.IInvoiceRepository
{
	private int registeredSellerId;
	private string connectionString;
	private DataLoadOptions loadOptions;

	public InvoiceRepository(int registeredSellerId)
	{
		this.registeredSellerId = registeredSellerId;

		connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

		loadOptions = new DataLoadOptions();
		loadOptions.LoadWith<Invoice>(x => x.Customer);
		loadOptions.LoadWith<Invoice>(x => x.Products);
		loadOptions.LoadWith<Invoice>(x => x.Seller);
	}

	public List<Invoice> GetAll()
	{
		using (MainDataContext dataContext = new MainDataContext(connectionString))
		{
			dataContext.LoadOptions = loadOptions;
			return dataContext.Invoices.Where(i => i.RegisteredSellerId == registeredSellerId).ToList();
		}
	}

	// ...

	public List<Invoice> GetByProduct(string productName)
	{
		using (MainDataContext dataContext = new MainDataContext(connectionString))
		{
			dataContext.LoadOptions = loadOptions;
			var query = from invoice in dataContext.Invoices
						where invoice.RegisteredSellerId == registeredSellerId &&
							  invoice.Products.Any(x => x.Name.StartsWith(productName))
						orderby invoice.CreationDate descending
						select invoice;
			return query.ToList();
		}
	}

	public List<Invoice> GetByExpression(Func<Invoice, bool> expression)
	{
		using (MainDataContext dataContext = new MainDataContext(connectionString))
		{
			dataContext.LoadOptions = loadOptions;
			var mainQuery = from invoice in dataContext.Invoices
						where invoice.RegisteredSellerId == registeredSellerId
						orderby invoice.CreationDate descending
						select invoice;
			return mainQuery.Where(expression).ToList();
		}
	}

	// ...

	public void Add(Invoice item)
	{
		using (MainDataContext dataContext = new MainDataContext(connectionString))
		{
			dataContext.Invoices.InsertOnSubmit(item);
			dataContext.SubmitChanges();
		}
	}

	// ...
}

Zaznaczone linie pokazaują łatwość, wygodę i swobodę, cechujące proces konstruowania zapytań z użyciem LINQ i Lambda Expressions (na szczególne podkreślenie zasługuje linia trzydziesta siódma). Tonę swobody zapewnia również metoda GetByExpression, pozwalająca uzyskać listę faktur spełniających zupełnie dowolne kryteria… Chociaż może „dowolne” to za dużo powiedziane – wszystkie metody grupowej selekcji zwracają tylko faktury przypisane do konkretnego sprzedawcy (pole registeredSellerId).
Wspomnę jeszcze, że nie każda tabela bazy danych doczekała się własnego repozytorium. Nie przewiduję możliwości bezpośredniego grzebania patykiem w wątpiach tabel Sellers, Customers i Products (patrz poprzedni post), dlatego nie stworzyłem ich repozytoriów (choć być może w przyszłości pojawią się jakieś klasy mające dostęp do tych tabel).

Tak oto powstała pierwsza warstwa aplikacji. Autor lirycznie odchodzi w siną dal stronę zachodzącego słońca.

PS. Upubliczniłem wreszcie projekt na CodePlex. Dodałem też listy Done i To do na stronie projektu. Jak pokazuje lista Done, do kodowania mam większy zapał, niż do blogowania – zaległości postaram się jednak w nadchodzących dniach nadrobić, więc zapraszam do śledzenia bloga!

Księga pierwsza: projektowanie bazy danych

Zacznę od pytania: co chcę przechowywać w bazie? I natychmiast odpowiem: na pewno faktury. Zerknięcie na założenia projektowe (pkt. 1) pozwala rozszerzyć odpowiedź. Potrzebował będę również takich tabel jak: klienci, produkty, szablony faktur. Przydałoby się również miejsce na przechowanie danych o użytkownikach programu – tabela sprzedawcy.
Mam już ogólny zarys bazy danych, więc zadaję kolejne pytanie: co powinna zawierać faktura VAT? Odpowiedź, już nie natychmiastową, przynoszą: wikipedia, serwis Moja firma, aplikacja inFakt (na której, nie ukrywam, zamierzam się podczas swojej pracy wzorować) i przykładowa faktura. Takie zagłębienie się w arkana ekonomii daje mi wiedzę potrzebną do stworzenia tabeli Invoices:
1. Id [int] – identyfikator faktury
2. SellerId [int] – sprzedawca [relacja]
3. CustomerId [int] – klient [relacja]
4. Number [nvarchar] – numer kolejny faktury
5. CreationDate [datetime] – data wystawienia faktury
6. SaleDate [datetime] – data sprzedaży
7. PaymentType [nvarchar] – sposób płatności
8. PaymentDeadline [datetime] – termin płatności
9. PaymentCurrency [nvarchar] – waluta
10. ToPay [money] – do zapłaty
11. ToPayInWords [nvarchar] – słownie do zapłaty
12. Paid [money] – zapłacono
13. LeftToPay [money] – pozostało do zapłaty
14. Remarks [nvarchar] – uwagi
15. Status [nvarchar] – status faktury (utworzona / wystawiona / zapłacona)
EDIT: 16. RegisteredSellerId [int] – użytkownik programu [relacja] /EDIT
Nie ma tu pola mówiącego o produktach, ponieważ produkty zostaną połączone z fakturą relacją wiele (produktów) do jednej (faktury).

Jak na razie idzie jak po maś taniej margarynie, a takiej nie można ufać. Dlatego przełknijmy i przetrawmy spokojnie to, co już zaprojektowałem. W utworzonej tabeli pojawiają się perspektywy relacji (np. relacja produkty – faktury), co nie powinno dziwić, ale powinno zastanowić. Wyobraźmy sobie bowiem taką sytuację: użytkownik wystawia fakturę na jakiś produkt, niech to będzie ta tania margaryna. Po miesiącu postanawia jednak podnieść jej cenę. Przy obecnej strukturze bazy danych, taka operacja spowodowałaby zmianę kwoty faktury sprzed miesiąca – hańba! Problem leży w tym, że produkty zdefiniowane przez użytkownika (podatne na edycję) są jednocześnie produktami przypisywanymi do faktur (ich parametrów zmieniać nie należy). Rozwiązaniem może być zastosowanie dwóch tabel produktów:
1. produkty definiowane przez użytkownika,
2. produkty przypisywane do faktur, niezmienne (niedostępne dla użytkownika);
i tak też uczynię. Oczywiście podobnego zabiegu wymagają tabele klientów i sprzedawców.

Możemy zatem zająć się zawartością kolejnych tabel. Podlinkowane wcześniej źródła wiedzy w zupełności wystarczą, w końcu baza będzie zawierać (prawie) jedynie dane potrzebne do wystawienia faktury. Godzinę (lektury, buszowania po inFakt i projektowania) później, mam (tabele Registered to tabele dla danych definiowanych przez użytkownika, pozostałe – dla danych „niezmiennych”, przypisywanych do faktur):

RegisteredSellers:
1. Id [int] – identyfikator sprzedawcy
2. FirstName [nvarchar] – imię sprzedawcy
3. LastName [nvarchar] – nazwisko sprzedacy
4. CompanyName [nvarchar] – nazwa firmy sprzedawcy
5. Street [nvarchar] – ulica [adres firmy]
6. City [nvarchar] – miasto [adres firmy]
7. PostalCode [nvarchar] – kod pocztowy [adres firmy]
8. BankName [nvarchar] – nazwa banku
9. BankAccountNumber [nvarchar] – numer konta firmy
10. BankSwift [nvarchar] – swift banku (nieobowiązkowy)
11. Nip [nvarchar] – NIP sprzedawcy
12. Regon [nvarchar] – REGON firmy (nieobowiązkowy)
13. InvoiceNumberFormat [nvarchar] – format numerów faktur (patrz pkt. 2 założeń projektu)
14. LastInvoiceNumber [nvarchar] – numer kolejny ostatniej faktury

Sellers: (opis kolumn jw.; zawiera tylko dane, które znajdą się na fakturze)
1. Id [int]
2. SellerName [nvarchar]
3. CompanyName [nvarchar]
4. Street [nvarchar]
5. City [nvarchar]
6. PostalCode [nvarchar]
7. BankName [nvarchar]
8. BankAccountNumber [nvarchar]
9. BankSwift [nvarchar]
10. Nip [nvarchar]

RegisteredCustomers:
1. Id [int] – identyfikator klienta
2. CustomerName [nvarchar] – imię i nazwisko klienta
3. CompanyName [nvarchar] – nazwa firmy klienta
4. Street [nvarchar] – ulica [adres firmy]
5. City [nvarchar] – miasto [adres firmy]
6. PostalCode [nvarchar] – kod pocztowy [adres firmy]
7. Nip [nvarchar] – NIP klienta
8. Email [nvarchar] – e-mail klienta (nieobowiązkowy)
9. Www [nvarchar] – strona www klienta (nieobowiązkowa)
10. Phone [nvarchar] – telefon klienta (niebowiązkowy)
11. Remarks [nvarchar] – uwagi (nieobowiązkowe)
EDIT: 12. RegisteredSellerId [int] – użytkownik programu [relacja] /EDIT

Customers: (opis kolumn jw.; zawiera tylko dane, które znajdą się na fakturze)
1. Id [int]
2. CustomerName [nvarchar]
3. CompanyName [nvarchar]
4. Street [nvarchar]
5. City [nvarchar]
6. PostalCode [nvarchar]
7. Nip [nvarchar]

RegisteredProducts: (opis kolumn niżej)
1. Id [int]
2. Name [nvarchar]
3. Pkwiu [nvarchar]
4. MeasureUnit [nvarchar]
5. NetPrice [money]
6. VatRate [nvarchar]
EDIT: 7. RegisteredSellerId [int] – użytkownik programu [relacja] /EDIT

Products: (wyjątkowo zawiera więcej kolumn niż tabela Registered)
– Id [int] – identyfikator produktu
– InvoiceId [int] – faktura [relacja]
– Name [nvarchar] – nazwa produktu
– Pkwiu [nvarchar] – klasyfikacja PKWiU produktu
– MeasureUnit [nvarchar] – jednostka miary produktu
– Quantity [money] – ilość (typ money dla uniknięcia konfliktu typów przy mnożeniu)
– NetPrice [money] – cena netto produktu
– NetValue [money] – wartość netto produktu (ilość * cena)
– VatRate [nvarchar] – stawka VAT produktu
– VatValue [money] – wartość VAT (wartość netto * stawka VAT)
– GrossValue [money] – wartość brutto produktu (wartość netto + wartość VAT)

Trochę się tego uzbierało, nie ma co. Ostatnie elementy podam na sucho, bez barwnego wprowadzenia, bo nie sądzę, żeby którykolwiek z czytelników dobrnął aż tutaj.

InvoicePatterns: (tabela zawierająca szablony faktur)
1. Id [int] – identyfikator szablonu
2. RegisteredSellerId [int] – sprzedawca (z tabeli Registered) [relacja]
3. RegisteredCustomerId [int] – klient (jw.) [relacja]
4. PaymentType [nvarchar] – sposób płatności
5. PaymentCurrency [nvarchar] – waluta
6. Remarks [nvarchar] – uwagi

InvoicePatternRegisteredProducts: (realizuje relację RegisteredProducts – InvoicePatterns)
– InvoicePatternId [int] – szablon faktury [relacja]
– RegisteredProductId [int] – produkt (z tabeli Registered) [relacja]

Na deser (spodziewam się, że niewielu śmiałków dotrwa do deseru – niech żałują!) wypiszę zastosowane relacje:

1. Sellers – Invoices (jeden do jednego)
2. Customers – Invoices (jeden do jednego)
3. Invoices – Products (jeden do wielu)

4. RegisteredSellers – InvoicePatterns (jeden do wielu)
5. RegisteredCustomers – InvoicePatterns (jeden do wielu)
6. RegisteredProducts – InvoicePatterns (wiele do wielu)

Et voila! Winszuję wytrwałym czytelnikom, zapowiadając jednocześnie, że kolejne wpisy nie będą tak długie i nużące.

EDIT:
Przejażdżka rowerem i świeże powietrze sprowadziły na mnie olśnienie: w żadnym miejscu nie przypisałem faktury do użytkownika! Niedopatrzenie naprawiłem dodając kolumnę RegisteredSellerId [int] do tabeli Invoices i relację RegisteredSellers – Invoices (jeden do wielu).
Posłuszny celnej sugestii czytelnika tomash2310, zamieszczam również miły oku schemat bazy danych.

EDIT 2:
Zdałem sobie sprawę, że błąd popełniłem nie tylko w przypadku faktur, ale również klientów i produktów. Dodałem zatem odpowiednie kolumny (RegisteredSellerId [int] do tabel: RegisteredCustomers i RegisteredProducts) i relacje (RegisteredSellers – RegisteredCustomers i RegisteredSellers – RegisteredProducts). Zaktualizowałem też graficzny schemat bazy danych, kończąc tym samym, przynajmniej tymczasowo, pracę nad jej strukturą:

database

Czaję bazę?

Punkt drugi listy wypunktowanej w poprzednim wpisie głosi: projektowanie bazy danych. Jest to dość kłopotliwy problem, który można rozwiązać na wiele sposobów. Kłopotliwość polega na wybraniu rozwiązania najlepiej dostosowanego do wymagań projektowych. Wiadomo, że każdy błąd popełniony podczas projektowania mści się tym bardziej, im później zostanie wykryty i im więcej warstw aplikacji opiera się na błędnym module. Na bazie, jak to na bazie, opiera się właściwie wszystko – dlatego błędy na tym etapie są szczególnie niemile widziane.

Do dzieła. Przypomnę wymagania dotyczące tego etapu: „chcę nadawać wydatkom kategorie”, „chcę móc zdefiniować wydatki stałe”. Dosyć oczywiste wydają się więc tabele bazy danych: kategorie wydatków, wydatki jednorazowe, wydatki stałe – i ich przyjemniejsze odpowiedniki: kategorie przychodów, przychody jednorazowe, przychody stałe.

Kategorie nie powinny sprawić problemu – niech zawierają, oprócz identyfikatora, tylko nazwę.

Wydatki (przychody) jednorazowe niech cechuje ich wartość, kategoria, nazwa (warto odróżnić kategorię od nazwy: w kategorii „alkohol” może znajdować się np. wydatek o nazwie „komandos z braciszkiem”), data, flaga oczekujący / zapłacony (otrzymany) i notatki (np. n-ta z kolei notatka: „zapamiętać: nigdy więcej komandosa”).

Wydatki (przychody) stałe wymagają, jak wspomniałem dwie notki niżej, zastanowienia. Pierwsza moja myśl: mogłyby mieć przypisany okres czasu (np. czynsz za akademik: październik 2009 – czerwiec 2010). Przy rozliczaniu jakiegoś okresu (np. pierwszy kwartał 2010) wyliczałbym mnożnik danego wydatku (w danym przykładzie: 3, bo rozliczam 3 miesiące) i obliczał kwotę, o którą wydatek ten uszczuplił portfel użytkownika. Nieco niewygodne, ale oszczędne – każdy wydatek to tylko jeden wiersz w tabeli bazy. Wtem! Druga moja myśl: akcja „weryfikuj wydatki stałe”, o której mowa w wymaganiach, wymaga, aby dany wydatek stały każdego miesiąca osobno mógł być oznaczony jako zapłacony. Okresowość wydatków może być realizowana przez dodanie ich tylu, ilu miesięcy dotyczą (po jednym wydatku na miesiąc). Decyduję się więc na rozwiązanie najprostsze: wydatki (przychody) stałe mają identyczną strukturę z jednorazowymi. W polu „data” dni nie mają znaczenia – wydatek (przychód) dotyczy całego miesiąca.

Podsumowując, baza danych zawiera sześć tabel:
– ExpenseCategories / IncomeCategories o polach:
1) Id [int]
2) Name [nvarchar]

– Expenses / ConstantExpenses / Incomes / ConstantIncomes o jednolitej strukturze pól:
1) Id [int]
2) ExpenseCategoryId / IncomeCategoryId [int]
3) Name [nvarchar]
4) Value [money]
5) Date [datetime]
6) Paid / Gained [bit]
7) Notes [nvarchar]