1. Введение
В этой статье мы рассмотрим основы схем баз данных, зачем они нам нужны и чем они полезны. После этого мы сосредоточимся на практических примерах настройки схемы в JDBC с PostgreSQL в качестве базы данных.
2. Что такое схема базы данных
В общем случае схема базы данных представляет собой набор правил, регулирующих базу данных. Это дополнительный уровень абстракции вокруг базы данных. Схемы бывают двух видов:
- Логическая схема базы данных определяет правила, которые применяются к данным, хранящимся в базе данных.
- Схема физической базы данных определяет правила физического хранения данных в системе хранения.
В PostgreSQL схема относится к первому виду. Схема — это логическое пространство имен, содержащее объекты базы данных, такие как таблицы, представления, индексы и т. д. Каждая схема принадлежит одной базе данных, и каждая база данных имеет как минимум одну схему. Если не указано иное, схема по умолчанию в PostgreSQL является общедоступной. Каждый объект базы данных, который мы создаем, без указания схемы принадлежит общедоступной схеме.
Схема в PostgreSQL позволяет нам организовывать таблицы и представления в группы и делать их более управляемыми. Таким образом, мы можем установить привилегии для объектов нашей базы данных на более детальном уровне. Кроме того, схемы позволяют нам иметь несколько пользователей, использующих одни и те же базы данных одновременно, не мешая друг другу.
3. Как использовать схему с PostgreSQL
Чтобы получить доступ к объекту схемы базы данных, мы должны указать имя схемы перед именем данного объекта базы данных, который мы хотим использовать. Например, чтобы запросить продукт таблицы в хранилище схем , нам нужно использовать полное имя таблицы:
SELECT * FROM store.product;
Рекомендуется избегать жестко закодированных имен схем, чтобы предотвратить привязку конкретной схемы к нашему приложению. Это означает, что мы используем имена объектов базы данных напрямую и позволяем системе базы данных определять, какую схему использовать. PostgreSQL определяет, где искать данную таблицу, следуя пути поиска.
3.1. PostgreSQL search_path
Путь поиска представляет собой упорядоченный список схем, определяющих поиск системой базы данных данного объекта базы данных. Если объект присутствует в любой (или нескольких) схемах, мы получаем первое найденное вхождение. В противном случае получаем ошибку. Первая схема в пути поиска также называется текущей схемой. Чтобы просмотреть, какие схемы находятся на пути поиска, мы можем использовать запрос:
SHOW search_path;
Конфигурация PostgreSQL по умолчанию будет возвращать схемы $user и public. Общедоступная схема, о которой мы уже упоминали, схема $user — это схема, названная в честь текущего пользователя, и она может не существовать. В этом случае база данных игнорирует эту схему.
Чтобы добавить схему магазина в путь поиска, мы можем выполнить запрос:
SET search_path TO store,public;
После этого мы можем запросить таблицу продуктов без указания схемы. Кроме того, мы могли бы удалить общедоступную схему из пути поиска.
Установка пути поиска, как мы описали выше, является конфигурацией на уровне ROLE. Мы можем изменить путь поиска во всей базе данных, изменив файл postgresql.conf и перезагрузив экземпляр базы данных.
3.2. URL-адрес JDBC
Мы можем использовать URL-адрес JDBC , чтобы указать все виды параметров во время установки соединения. Обычными параметрами являются тип базы данных, адрес, порт, имя базы данных и т. д. Начиная с версии Postgres 9.4. добавлена поддержка указания текущей схемы с помощью URL.
Прежде чем мы применим эту концепцию на практике, давайте настроим тестовую среду. Для этого мы будем использовать библиотеку testcontainers и создадим следующую тестовую настройку:
@ClassRule
public static PostgresqlTestContainer container = PostgresqlTestContainer.getInstance();
@BeforeClass
public static void setup() throws Exception {
Properties properties = new Properties();
properties.setProperty("user", container.getUsername());
properties.setProperty("password", container.getPassword());
Connection connection = DriverManager.getConnection(container.getJdbcUrl(), properties);
connection.createStatement().execute("CREATE SCHEMA store");
connection.createStatement().execute("CREATE TABLE store.product(id SERIAL PRIMARY KEY, name VARCHAR(20))");
connection.createStatement().execute("INSERT INTO store.product VALUES(1, 'test product')");
}
С помощью @ClassRule мы создаем экземпляр контейнера базы данных PostgreSQL. Затем в методе настройки создайте подключение к этой базе данных и создайте необходимые объекты.
Теперь, когда база данных настроена, давайте подключимся к схеме хранилища, используя URL-адрес JDBC:
@Test
public void settingUpSchemaUsingJdbcURL() throws Exception {
Properties properties = new Properties();
properties.setProperty("user", container.getUsername());
properties.setProperty("password", container.getPassword());
Connection connection = DriverManager.getConnection(container.getJdbcUrl().concat("&" + "currentSchema=store"), properties);
ResultSet resultSet = connection.createStatement().executeQuery("SELECT * FROM product");
resultSet.next();
assertThat(resultSet.getInt(1), equalTo(1));
assertThat(resultSet.getString(2), equalTo("test product"));
}
Чтобы изменить схему по умолчанию, нам нужно указать параметр currentSchema . Если мы вводим несуществующую схему, во время запроса на выборку выдается исключение PSQLException , сообщающее , что объект базы данных отсутствует. ``
3.3. PGSimpleDataSource
Для подключения к базе данных мы можем использовать реализацию javax.sql.DataSource из библиотеки драйверов PostgreSQL с именем PGSimpleDataSource . Эта конкретная реализация поддерживает настройку схемы:
@Test
public void settingUpSchemaUsingPGSimpleDataSource() throws Exception {
int port = //extracting port from container.getJdbcUrl()
PGSimpleDataSource ds = new PGSimpleDataSource();
ds.setServerNames(new String[]{container.getHost()});
ds.setPortNumbers(new int[]{port});
ds.setUser(container.getUsername());
ds.setPassword(container.getPassword());
ds.setDatabaseName("test");
ds.setCurrentSchema("store");
ResultSet resultSet = ds.getConnection().createStatement().executeQuery("SELECT * FROM product");
resultSet.next();
assertThat(resultSet.getInt(1), equalTo(1));
assertThat(resultSet.getString(2), equalTo("test product"));
}
При использовании PGSimpleDataSource драйвер использует общедоступную схему по умолчанию, если мы не устанавливаем схему.
3.4. @Table Аннотация из пакета javax.persistence
Если мы используем JPA в нашем проекте, мы можем указать схему на уровне сущности, используя аннотацию @Table . Эта аннотация может содержать значение для схемы или по умолчанию для пустой строки. Давайте сопоставим нашу таблицу продуктов с сущностью Продукт :
@Entity
@Table(name = "product", schema = "store")
public class Product {
@Id
private int id;
private String name;
// getters and setters
}
Чтобы проверить это поведение, мы настроили экземпляр EntityManager для запроса таблицы продуктов :
@Test
public void settingUpSchemaUsingTableAnnotation(){
Map<String,String> props = new HashMap<>();
props.put("hibernate.connection.url", container.getJdbcUrl());
props.put("hibernate.connection.user", container.getUsername());
props.put("hibernate.connection.password", container.getPassword());
EntityManagerFactory emf = Persistence.createEntityManagerFactory("postgresql_schema_unit", props);
EntityManager entityManager = emf.createEntityManager();
Product product = entityManager.find(Product.class, 1);
assertThat(product.getName(), equalTo("test product"));
}
Как мы упоминали ранее в разделе 3, по разным причинам лучше избегать связывания схемы с кодом. Из-за этого эту функцию часто упускают из виду, но она может оказаться полезной при доступе к нескольким схемам.
4. Вывод
В этом руководстве мы сначала рассмотрели базовую теорию схем баз данных. После этого мы описали несколько способов настройки схемы базы данных с использованием различных подходов и технологий. Как обычно, все образцы кода доступны на GitHub .