6. Приклад із використанням PostgreSQL 9.2.1

6.1. Встановлення інструментів

Необхідно встановити такі інструменти:

SGBD: [http://www.enterprisedb.com/products-services-training/pgdownload#windows];
інструмент адміністрування: EMS, SQL Manager для PostgreSQL, безкоштовне програмне забезпечення [http://www.sqlmanager.net/fr/products/postgresql/manager/download].

У наведених нижче прикладах користувач postgres має пароль postgres.

Запустимо PostgreSQL, а потім інструмент [SQL Manager Lite for PostgreSQL], за допомогою якого ми будемо адмініструвати SGBD.

у [1] ми запускаємо SGBD та PostgreSQL із служб Windows;
у [2] служба запущена;

Тепер запускаємо утиліту [SQL Manager Lite for MySQL], за допомогою якої будемо адмініструвати SGBD та [3].

у [4] ми створюємо нову базу даних;
у [5] вказуємо назву бази даних;

у файлі [5] ми входимо в систему як postgres / postgres;
у [6] вводимо деяку інформацію;
у [7] підтверджується замовлення SQL, яке буде виконано;

у [8] база даних створена. Тепер її потрібно зареєструвати в [EMS Manager]. Інформація правильна. Виконуємо [OK];
у [9] ми входимо в систему;
у [10], [EMS Manager] відображає базу даних, яка наразі порожня. Зазначимо, що таблиці належатимуть до схеми під назвою public [11].

Тепер ми підключимо проект VS 2012 до цієї бази даних.

6.2. Створення бази даних на основі сутностей

Почнемо з дублювання папки проєкту [RdvMedecins-SqlServer-01] у [RdvMedecins-PostgreSQL-01] та [1]:

у [2], у VS 2012 видаляємо проект [RdvMedecins-SqlServer-01] із рішення;

у [3] проект було видалено;
у [4] додаємо ще один. Цей проект міститься в папці [RdvMedecins-PostgreSQL-01], яку ми створили раніше;

у файлі [5] завантажений проект має назву [RdvMedecins-SqlServer-01];
у [6] змінюємо його назву на [RdvMedecins-PostgreSQL-01];

у [7] до розв’язання додають ще один проєкт. Він міститься у папці [RdvMedecins-SqlServer-01] того проєкту, який ми раніше видалили з розв’язання;
у [8] проект [RdvMedecins-SqlServer-01] знову включено до рішення.

Проєкт [RdvMedecins-PostgreSQL-01] ідентичний проєкту [RdvMedecins-SqlServer-01]. Нам потрібно внести кілька змін. У [App.config] ми змінимо рядок підключення, а [DbProviderFactory] потрібно адаптувати до кожного SGBD.


<!-- рядок підключення до бази даних -->
  <connectionStrings>
    <add name="monContexte" connectionString="Server=127.0.0.1;Port=5432;Database=rdvmedecins-ef;User Id=postgres;Password=postgres;" providerName="Npgsql" />
  </connectionStrings>
  <!-- фабричний провайдер -->
  <system.data>
    <DbProviderFactories>
      <add name="Npgsql Data Provider" invariant="Npgsql" support="FF" description=".Net Framework Data Provider for Postgresql Server" type="Npgsql.NpgsqlFactory, Npgsql, Version=2.0.11.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />
    </DbProviderFactories>
  </system.data>

рядок 3: ім’я користувача та його пароль;
рядки 7–9: файл DbProviderFactory. У рядку 8 згадується файл DLL [Npgsql], якого у нас немає. Його можна отримати за допомогою NuGet та [1]:

у [2] у полі пошуку вводимо ключове слово postgresql;
у [3] вибираємо пакет [Npgsql]. Це коннектор ADO.NET для PostgreSQL;

у [4] додано два номери;
у [5], у [App.config] потрібно вказати правильну версію DLL. Її можна знайти у властивостях.

У файлі [Entites.cs] потрібно адаптувати схему таблиць, які будуть згенеровані:


  [Table("MEDECINS", Schema = "public")]
  public class Medecin : Personne
  {...}

  [Table("CLIENTS", Schema = "public")]
  public class Client : Personne
  {...}

  [Table("CRENEAUX", Schema = "public")]
  public class Creneau
  {...}

  [Table("RVS", Schema = "public")]
  public class Rv
  {...}

Раніше, під час створення бази даних PostgreSQL, ми бачили, що таблиці належали до схеми з назвою public.

Налаштовуємо виконання проекту:

у [1] ми присвоюємо іншу назву збірці, яка буде згенерована;
у [2] також задаємо інший простір імен за замовчуванням;
у [3] вказуємо програму, яку потрібно виконати.

На цьому етапі помилок компіляції немає. Запустимо програму [CreateDB_01]. Отримуємо таке виключення:

Exception non gérée : System.Data.MetadataException: Le schéma spécifié n'est pas valide. Erreurs :
(11,6) : erreur 0040: Le type rowversion n'est pas qualifié avec un espace de noms ou un alias. Seuls les types primitifs peuvent être utilisés sans qualification.
(23,6) : erreur 0040: Le type rowversion n'est pas qualifié avec un espace de noms ou un alias. Seuls les types primitifs peuvent être utilisés sans qualification.
(33,6) : erreur 0040: Le type rowversion n'est pas qualifié avec un espace de noms ou un alias. Seuls les types primitifs peuvent être utilisés sans qualification.
(43,6) : erreur 0040: Le type rowversion n'est pas qualifié avec un espace de noms ou un alias. Seuls les types primitifs peuvent être utilisés sans qualification.
   à System.Data.Metadata.Edm.StoreItemCollection.Loader.ThrowOnNonWarningErrors
()
   ...
   à RdvMedecins_01.CreateDB_01.Main(String[] args) dans d:\data\istia-1213\c#\d
vp\Entity Framework\RdvMedecins\RdvMedecins-Oracle-01\CreateDB_01.cs:ligne 15

Пригадуємо, що таку саму помилку ми отримували з MySQL та Oracle. Це пов’язано з типом поля Timestamp в сутностях. Вносимо ті самі зміни, що й для Oracle. У сутностях замінюємо три рядки


    [Column("TIMESTAMP")]
    [Timestamp]
    public byte[] Timestamp { get; set; }

на такі:


    [ConcurrencyCheck]
    [Column("VERSIONING")]
    public int? Versioning { get; set; }

Змінюємо тип стовпця з byte[] на int?. У SGBD ми будемо використовувати збережені процедури, щоб збільшувати це ціле число на одиницю щоразу, коли буде вставлено або змінено рядок.

Вносимо зазначену зміну для всіх чотирьох об’єктів, а потім запускаємо програму знову. У результаті отримуємо таку помилку:

Exception non gérée : System.Data.DataException: An exception occurred while initializing the database. See the InnerException for details. ---> System.Data.ProviderIncompatibleException: DeleteDatabase n'est pas pris en charge par le fournisseur.
   à System.Data.Common.DbProviderServices.DbDeleteDatabase(DbConnection connection, Nullable`1 commandTimeout, StoreItemCollection storeItemCollection)
   ...
   à System.Data.Entity.Internal.LazyInternalContext.InitializeDatabase()
   à RdvMedecins_01.CreateDB_01.Main(String[] args) dans d:\data\istia-1213\c#\d
vp\Entity Framework\RdvMedecins\RdvMedecins-Oracle-01\CreateDB_01.cs:ligne 15

У рядку 1 вказано, що коннектор ADO.NET з PostgreSQL не може видалити існуючу базу даних. Точно так само, як і в разі з Oracle. Тому нам доводиться вручну створити базу даних [RDVMEDECINS-EF] за допомогою інструменту [EMS Manager for PostgreSQL]. Ми не описуємо всі етапи, а лише найважливіші.

База даних PostgreSQL матиме такий вигляд:

Таблиці

у [1], ID є первинним ключем типу serial. Цей тип PostgreSQL — це ціле число, яке автоматично генерується SGBD.

Різні таблиці мають ті самі первинні та зовнішні ключі, що й у попередніх прикладах. Зовнішні ключі мають атрибути ON, DELETE та CASCADE.

Послідовності

Як і в Oracle, тут ми створили послідовності. Це генератори послідовних чисел. Їх є 5: [1].

у [2] ми бачимо властивості послідовності [CLIENTS_ID_SEQ]. Вона генерує послідовні числа з кроком 1, починаючи з 1 і аж до дуже великого значення.

Усі послідовності побудовані за одним і тим самим зразком.

[CLIENTS_ID_seq] буде використовуватися для генерації первинного ключа таблиці [CLIENTS];
[MEDECINS_ID_seq] буде використовуватися для генерації первинного ключа таблиці [MEDECINS];
[CRENEAUX_ID_seq] буде використовуватися для генерації первинного ключа таблиці [CRENEAUX];
[RVS_ID_seq] буде використано для генерації первинного ключа таблиці [RVS];
[sequence_versions] буде використовуватися для генерації значень стовпців [VERSIONING] у всіх таблицях.

Тригери

Тригер — це процедура, що виконується SGBD до або після події (вставлення, зміна, видалення) у таблиці. У нас є 4 тригери [1]:

Розглянемо код DDL тригера [CLIENTS_tr], який заповнює стовпець [VERSIONING] таблиці [CLIENTS]:

CREATE TRIGGER "CLIENTS_tr"
  BEFORE INSERT OR UPDATE 
  ON public."CLIENTS" FOR EACH ROW 
EXECUTE PROCEDURE public.trigger_versions();

рядки 1–3: перед кожною операцією INSERT або UPDATE над таблицею [CLIENTS];
рядок 4: виконується процедура [public.trigger_versions()].

Процедура [public.trigger_versions()] виглядає наступним чином:

BEGIN
NEW."VERSIONING":=nextval('sequence_versions');
return NEW;
END

рядок 2: NEW — це рядок, який буде вставлено або змінено. NEW. «VERSIONING» — це стовпець [VERSIONING] цього рядка. Їй присвоюється наступне значення генератора чисел: «sequence_versions». Таким чином, стовпець ["VERSIONING"] змінюється при кожному виконанні операції INSERT / UPDATE над таблицею [CLIENTS].

Тригери [MEDECINS_tr, CRENEAUX_tr, RVS_tr] працюють аналогічно. Чотири стовпці ["VERSIONING"] отримують свої значення з тієї самої послідовності.

Скрипт для створення таблиць бази даних PostgreSQL та [RDVMEDECINS-EF] розміщено у папці [RdvMedecins / databases / postgreSQL]. Користувач може завантажити та виконати його для створення своїх таблиць.

Після цього можна запускати різні програми проекту. Вони дають ті самі результати, що й із сервером SQL, за винятком програми [ModifyDetachedEntities], яка вилітає з тієї самої причини, що й у випадку з Oracle. Проблему вирішують таким самим чином. Достатньо скопіювати програму [ModifyDetachedEntities] з проєкту [RdvMedecins-Oracle-01] у проєкт [RdvMedecins-PostgreSQL-01].

Програма [LazyEagerLoading] вилітає з таким винятком:

Exception non gérée : System.Data.EntityCommandExecutionException: Une erreur s'est produite lors de l'exécution de la définition de la commande. Pour plus de détails, consultez l'exception interne. ---> Npgsql.NpgsqlException: ERREUR: 42601: erreur de syntaxe sur ou près de « LEFT »
   à Npgsql.NpgsqlState.<ProcessBackendResponses_Ver_3>d__a.MoveNext()
   ...
   à RdvMedecins_01.LazyEagerLoading.Main(String[] args) dans d:\data\istia-1213\c#\dvp\Entity Framework\RdvMedecins\RdvMedecins-PostgreSQL-01\LazyEagerLoading.cs:рядок 23

Помилковий код такий:


      using (var context = new RdvMedecinsContext())
      {
        // слот № 0
        creneau = context.Creneaux.Include("Medecin").Single<Creneau>(c => c.Id == idCreneau);
        Console.WriteLine(creneau.ShortIdentity());
}

У рядку № 1 винятку зазначена помилка нагадує з'єднання, оскільки LEFT є ключовим словом з'єднання. Оскільки у рядку 4 наведеного вище коду вимагається негайне завантаження залежності [Medecin] від сутності [Creneau], EF здійснив з'єднання між таблицями [CRENEAUX] та [MEDECINS]. Однак, схоже, що коннектор ADO.NET згенерував некоректне завдання SQL. Ми перепишемо код наступним чином:


      using (var context = new RdvMedecinsContext())
      {
        // слот № 0
        creneau = context.Creneaux.Find(idCreneau);
        Console.WriteLine(creneau.ShortIdentity());
        // примусово завантажується відповідний лікар
        // це можливо, оскільки ми все ще перебуваємо у відкритому контексті
        Medecin medecin = creneau.Medecin;
}

рядок 4: шукаємо інтервал без з’єднання;
рядок 8: отримуємо відсутню залежність.

Це працює. Знову ми бачимо, що зміна SGBD впливає на код. Насправді тут причиною є не SGBD, а його з’єднувач ADO.NET.

6.3. Багаторівнева архітектура на основі EF 5

Повернемося до нашого прикладу, описаного в параграфі 2.

Почнемо з побудови шару [DAO] для доступу до даних. Для цього ми дублюємо консольний проєкт VS 2012 [RdvMedecins-SqlServer-02] у [RdvMedecins-PostgreSQL-02] [1]:

у [2] видаляємо проект [RdvMedecins-SqlServer-02];

у [3] додаємо існуючий проект до рішення. Беремо його з папки [RdvMedecins-PostgreSQL-02], яку щойно створили;
у [4] новий проєкт має назву того, що було видалено. Ми змінимо його назву;

у [5] ми змінили назву проєкту;
у [6] ми змінюємо деякі його властивості, наприклад, назву збірки;
на [7], папку [Models] видалено, щоб замінити її папкою [Models] з проєкту [RdvMedecins-PostgreSQL-01]. Адже обидва проєкти використовують однакові шаблони.

у [8] — поточні посилання на проект;
у [9] за допомогою інструменту NuGet було додано коннектор ADO.NET з PostgreSQL.

У файлі [App.config] замінюють дані з бази SQL Server на дані з бази PostgreSQL. Їх можна знайти у файлі [App.config] проекту [RdvMedecins-PostgreSQL-01]:


  <!-- ланцюг з'єднання з базою даних -->
  <connectionStrings>
    <add name="monContexte" connectionString="Server=127.0.0.1;Port=5432;Database=rdvmedecins-ef;User Id=postgres;Password=postgres;" providerName="Npgsql" />
  </connectionStrings>
  <!-- фабрика провайдерів -->
  <system.data>
    <DbProviderFactories>
      <add name="Npgsql Data Provider" invariant="Npgsql" support="FF" description=".Net Framework Data Provider for Postgresql Server" type="Npgsql.NpgsqlFactory, Npgsql, Version=2.0.11.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />
    </DbProviderFactories>
</system.data>

Об’єкти, що керуються Spring, також змінюються. Наразі маємо:


  <!-- конфігурація Spring -->
  <spring>
    <context>
      <resource uri="config://spring/objects" />
    </context>
    <objects xmlns="http://www.springframework.net">
      <object id="rdvmedecinsDao" type="RdvMedecins.Dao.Dao,RdvMedecins-SqlServer-02" />
    </objects>
</spring>

У рядку 7 вказано збірку проекту [RdvMedecins-SqlServer-02]. Тепер збірка має назву [RdvMedecins-PostgreSQL-02].

Тепер ми готові виконати тест рівня [DAO]. Перед цим необхідно заповнити базу даних (програма [Fill] з проєкту [RdvMedecins-PostgreSQL-01]). Тестова програма завершується з наступним винятком:

2012/10/12 13:56:27:188 [INFO]  Spring.Context.Support.XmlApplicationContext - A
pplicationContext Refresh: Completed
Liste des clients :
Client [47,Mr,Jules,Martin,468]
Client [48,Mme,Christine,German,469]
Client [49,Mr,Jules,Jacquard,470]
Client [50,Melle,Brigitte,Bistrou,471]
Liste des médecins :
Medecin [42,Mme,Marie,Pelissier,472]
Medecin [43,Mr,Jacques,Bromard,497]
Medecin [44,Mr,Philippe,Jandot,510]
Medecin [45,Melle,Justine,Jacquemot,511]
L'erreur suivante s'est produite : RdvMedecinsException[3,GetCreneauxMedecin,Une erreur s'est produite lors de l'exécution de la définition de la commande. Pour plus de détails, consultez l'exception interne.]

У рядку 13 повідомлення вказує, що помилка сталася в методі [GetCreneauxMedecin] шару [DAO]. Цей метод має такий вигляд:


    // список вільних часів прийому конкретного лікаря
    public List<Creneau> GetCreneauxMedecin(int idMedecin)
    {
      // список часових проміжків
      try
      {
        // відкриття контексту збереження даних
        using (var context = new RdvMedecinsContext())
        {
          // отримання даних про лікаря та його часові слоти
          Medecin medecin = context.Medecins.Include("Creneaux").Single(m => m.Id == idMedecin);
          // повертається список часових проміжків лікаря
          return medecin.Creneaux.ToList<Creneau>();
        }
      }
      catch (Exception ex)
      {
        throw new RdvMedecinsException(3, "GetCreneauxMedecin", ex);
      }
}

У рядку 11 можна впізнати ключове слово Include, яке вже раніше призводило до збою попередньої програми. Попередній код можна замінити на такий:


    // список часових проміжків певного лікаря
    public List<Creneau> GetCreneauxMedecin(int idMedecin)
    {
      // список часових проміжків
      try
      {
        // відкриття контексту збереження даних
        using (var context = new RdvMedecinsContext())
        {
          // повертає список вільних часів лікаря
          return context.Creneaux.Where(c => c.MedecinId == idMedecin).ToList<Creneau>(); 
        }
      }
      catch (Exception ex)
      {
        throw new RdvMedecinsException(3, "GetCreneauxMedecin", ex);
      }
}

Новий код навіть виглядає логічнішим за старий. У будь-якому разі, цього разу тестова програма пройшла успішно.

Ми створюємо DLL для цього проєкту так само, як це було зроблено для проєкту [RdvMedecins-SqlServer-02], і збираємоусі файли DLL цього проєкту в папку [lib], створену в [RdvMedecins-PostgreSQL-02]. Це будуть посилання на веб-проєкт [RdvMedecins-PostgreSQL-03], про який йтиметься далі.

Тепер ми готові до створення шару [ASP.NET] нашого додатка:

Ми почнемо з проєкту [RdvMedecins-SqlServer-03]. Скопіюємо папку цього проєкту в [RdvMedecins-PostgreSQL-03] та [1]:

у [2], за допомогою VS 2012 Express для веб-розробки відкриваємо рішення з папки [RdvMedecins-PostgreSQL-03];
у [3] ми змінюємо як назву рішення, так і назву проєкту;

у [4] — поточні посилання проекту;
у [5] видаляємо їх;
на [6], щоб замінити їх посиланнями на DLL, які ми щойно зберегли у папці [lib] проекту [RdvMedecins-PostgreSQL-02].

Залишилося лише змінити файл [Web.config]. Замінюємо його поточний вміст на вміст файлу [App.config] з проєкту [RdvMedecins-PostgreSQL-02]. Зробивши це, запускаємо веб-проєкт. Він працює. Не забуваємо заповнити базу даних перед запуском веб-додатку.