Как установить Postgresql на Linux и Windows с. 16
 Скачать 1.38 Mb.
|
|
UPDATE course_chapters SET txtvector = setweight(to_tsvector('russian',ch_title),'B') || ' ' || setweight(to_tsvector('russian',txt),'D'); UPDATE 3 test=# SELECT txtvector FROM course_chapters \gx -[ RECORD 1 ]----------------------------------------- txtvector | 'баз':1B,12 'глав':5 'дан':2B,13 'знакомств':8 'мир':11 'начина':6 'наш':7 'увлекательн':10 -[ RECORD 2 ]----------------------------------------- txtvector | 'баз':7,13 'дан':8,14 'знакомств':4 'мир':6 'наш':10 'перв':1B,11 'продолжа':3 'создад':9 'текстов':12 'шаг':2B -[ RECORD 3 ]----------------------------------------- txtvector | 'интриг':10 'локальн':1B 'мир':11 'начнет':4 'наш':5 'полн':6 'приключен':7 'путешеств':8 'сет':2B,12 93 У лексем появился относительный вес — B и D (из че- тырех возможных — A, B, C, D). Реальный вес мы будем задавать при составлении запросов. Это придаст им до- полнительную гибкость. Во всеоружии вернемся к поиску. Функции to_tsvector симметрична функция to_tsquery, приводящая сим- вольное выражение к типу данных tsquery, который используют в запросах. test=# SELECT ch_title FROM course_chapters WHERE txtvector @@ to_tsquery('russian','базы & данные'); ch_title ------------- Базы данных Первые шаги (2 rows) Можно убедиться, что 'база & данных' и другие грам- матические вариации дадут тот же результат. Мы ис- пользовали оператор сравнения @@ (две собаки), выпол- няющий работу, аналогичную LIKE. Синтаксис оператора не допускает выражение естественного языка с пробе- лами, такие как «база данных», поэтому слова соединя- ются логическим оператором «и». Аргумент russian указывает на конфигурацию, которую использует СУБД. Она определяет подключаемые сло- вари и парсер, разбивающий фразу на отдельные лек- семы. Словари, несмотря на такое название, позволяют выполнять любые преобразования лексем. Например, простой словарь-стеммер типа snowball, используемый 94 по умолчанию, оставляет от слова только неизменяемую часть — именно поэтому поиск игнорирует окончания слов в запросе. Можно подключать и другие словари, такие, как hunspell (позволяет более точно учитывать морфологию слов), unaccent (превращает букву «ё» в «е») и другие. Введенные нами веса позволяют вывести записи по ре- зультатам рейтинга: test=# SELECT ch_title, ts_rank_cd('{0.1, 0.0, 1.0, 0.0}', txtvector, q) FROM course_chapters, to_tsquery('russian','базы & данных') q WHERE txtvector @@ q ORDER BY ts_rank_cd DESC; ch_title | ts_rank_cd -------------+------------ Базы данных | 1.11818 Первые шаги | 0.22 (2 rows) Массив {0.1, 0.0, 1.0, 0.0} задает веса. Это не обязатель- ный аргумент функции ts_rank_cd, по умолчанию мас- сив {0.1, 0.2, 0.4, 1.0} соответствует D, C, B, A. Вес слова повышает значимость найденной строки, помогает ран- жировать результаты. В заключительном эксперименте модифицируем вы- дачу. Будем считать, что найденные слова мы хотим выделить жирным шрифтом в странице html. Функция ts_headline задает наборы символов, обрамляющих слово, а также минимальное и максимальное количе- ство слов в строке: 95 test=# SELECT ts_headline( 'russian', txt, to_tsquery('russian', 'мир'), 'StartSel=, StopSel=, MaxWords=50, MinWords=5' ) FROM course_chapters WHERE to_tsvector('russian', txt) @@ to_tsquery('russian', 'мир'); -[ RECORD 1 ]----------------------------------------- ts_headline | знакомство с увлекательным миром баз данных -[ RECORD 2 ]----------------------------------------- ts_headline | миром баз данных. Создадим нашу -[ RECORD 3 ]----------------------------------------- ts_headline | путешествие в интригующий мир сетей Для ускорения полнотекстового поиска используют- ся специальные индексы GiST, GIN и RUM, отличные от обычных индексов в базах данных. Но они, как и мно- гие другие полезные знания о полнотекстовом поиске, останутся вне рамок этого краткого руководства. Более подробно о полнотекстовом поиске можно узнать в документации PostgreSQL: www.postgrespro.ru/doc/ textsearch.html. Работа с JSON и JSONB Реляционные базы данных, использующие SQL, создава- лись с большим запасом прочности: первой заботой их потребителей была целостность и безопасность данных, 96 а объемы информации были несравнимы с современ- ными. Когда появилось новое поколение СУБД — NoSQL, сообщество призадумалось: куда более простая струк- тура данных (вначале это были прежде всего огромные таблицы с всего двумя колонками: ключ-значение) поз- воляла ускорить поиск на порядки. Они могли обраба- тывать небывалые объемы информации и легко мас- штабировались, вовсю используя параллельные вычис- ления. В NoSQL-базах не было необходимости хранить информацию по строкам, а хранение по столбцам для многих задач позволяло еще больше ускорить и распа- раллелить вычисления. Когда прошел первый шок, стало понятно, что для боль- шинства реальных задач простенькой структурой не обойтись. Стали появляться сложные ключи, потом груп- пы ключей. Реляционные СУБД не желали отставать от жизни и начали добавлять возможности, типичные для NoSQL. Поскольку в реляционных СУБД изменение схемы дан- ных связано с большими вычислительными издержками, оказался как никогда кстати новый тип данных — JSON. Изначально он предназначался для JS-программистов, в том числе для AJAX-приложений, отсюда JS в назва- нии. Он как бы брал сложность добавляемых данных на себя, позволяя создавать линейные и иерархические структуры-объекты, добавление которых не требовало пересчета всей базы. Тем, кто делал приложения, уже не было необходимости модифицировать схему базы данных. Синтаксис JSON похож на XML своим строгим соблюдением иерархии 97 данных. JSON достаточно гибок для того, чтобы рабо- тать с разнородной, иногда непредсказуемой структурой данных. Допустим, в нашей демо-базе студентов появилась воз- можность ввести личные данные: запустили анкету, рас- спросили преподавателей. В анкете не обязательно за- полнять все пункты, а некоторые из них включают графу «другое» и «добавьте о себе данные по вашему усмот- рению». Если бы мы добавили в базу новые данные в привыч- ной манере, то в многочисленных появившихся столб- цах или дополнительных таблицах было бы большое количество пустых полей. Но еще хуже то, что в буду- щем могут появиться новые столбцы, а тогда придется существенно переделывать всю базу. Мы решим эту проблему, используя тип json и появив- шийся позже jsonb, в котором данные хранятся в эко- номичном бинарном виде, и который, в отличие от json, приспособлен к созданию индексов, ускоряющих поиск иногда на порядки. Создадим таблицу с объектами JSON: test=# CREATE TABLE student_details( de_id int, s_id int REFERENCES students(s_id), details json, CONSTRAINT pk_d PRIMARY KEY(s_id, de_id) ); 98 test=# INSERT INTO student_details (de_id, s_id, details) VALUES (1, 1451, '{ "достоинства": "отсутствуют", "недостатки": "неумеренное употребление мороженого" }' ), (2, 1432, '{ "хобби": { "гитарист": { "группа": "Постгрессоры", "гитары":["страт","телек"] } } }' ), (3, 1556, '{ "хобби": "косплей", "достоинства": { "мать-героиня": { "Вася": "м", "Семен": "м", "Люся": "ж", "Макар": "м", "Саша":"сведения отсутствуют" } } }' ), (4, 1451, '{ "статус": "отчислена" }' ); Проверим, все ли данные на месте. Для удобства соеди- ним таблицы student_details и students при помощи конструкции WHERE, ведь в новой таблице отсутствуют имена студентов: 99 test=# SELECT s.name, sd.details FROM student_details sd, students s WHERE s.s_id = sd.s_id \gx -[ RECORD 1 ]-------------------------------------- name | Анна details | { "достоинства": "отсутствуют", + | "недостатки": + | "неумеренное употребление мороженого" + | } -[ RECORD 2 ]-------------------------------------- name | Виктор details | { "хобби": + | { "гитарист": + | { "группа": "Постгрессоры", + | "гитары":["страт","телек"] + | } + | } + | } -[ RECORD 3 ]-------------------------------------- name | Нина details | { "хобби": "косплей", + | "достоинства": + | { "мать-героиня": + | { "Вася": "м", + | "Семен": "м", + | "Люся": "ж", + | "Макар": "м", + | "Саша":"сведения отсутствуют" + | } + | } + | } -[ RECORD 4 ]-------------------------------------- name | Анна details | { "статус": "отчислена" + | } Допустим, нас интересуют записи, где есть информация о достоинствах студентов. Мы можем обратиться к со- держанию ключа «достоинство», используя специальный оператор ->>: 100 test=# SELECT s.name, sd.details FROM student_details sd, students s WHERE s.s_id = sd.s_id AND sd.details ->> 'достоинства' IS NOT NULL \gx -[ RECORD 1 ]-------------------------------------- name | Анна details | { "достоинства": "отсутствуют", + | "недостатки": + | "неумеренное употребление мороженого" + | } -[ RECORD 2 ]-------------------------------------- name | Нина details | { "хобби": "косплей", + | "достоинства": + | { "мать-героиня": + | { "Вася": "м", + | "Семен": "м", + | "Люся": "ж", + | "Макар": "м", + | "Саша":"сведения отсутствуют" + | } + | } + | } Мы убедились, что две записи имеют отношение к до- стоинствам Анны и Нины, однако такой ответ нас вряд ли удовлетворит, так как на самом деле достоинства Ан- ны «отсутствуют». Скорректируем запрос: test=# SELECT s.name, sd.details FROM student_details sd, students s WHERE s.s_id = sd.s_id AND sd.details ->> 'достоинства' IS NOT NULL AND sd.details ->> 'достоинства' != 'отсутствуют'; Убедитесь, что этот запрос оставит в списке только Нину, обладающую реальными, а не отсутствующими достоин- ствами. 101 Но такой способ срабатывает не всегда. Попробуем най- ти, на каких гитарах играет музыкант Витя: test=# SELECT sd.de_id, s.name, sd.details FROM student_details sd, students s WHERE s.s_id = sd.s_id AND sd.details ->> 'гитары' IS NOT NULL \gx Запрос ничего не выдаст. Дело в том, что соответству- ющая пара ключ-значение находится внутри иерархии JSON, вложена в пары более высокого уровня: name | Виктор details | { "хобби": + | { "гитарист": + | { "группа": "Постгрессоры", + | "гитары":["страт","телек"] + | } + | } + | } Чтобы добраться до гитар, воспользуемся оператором #> и спустимся с «хобби» вниз по иерархии: test=# SELECT sd.de_id, s.name, sd.details #> '{хобби,гитарист,гитары}' FROM student_details sd, students s WHERE s.s_id = sd.s_id AND sd.details #> '{хобби,гитарист,гитары}' IS NOT NULL \gx и убедимся, что Виктор фанат фирмы Fender: de_id | name | ?column? -------+--------+------------------- 2 | Виктор | ["страт","телек"] 102 У типа данных json есть младший брат jsonb. Буква «b» подразумевает бинарный (а не текстовый) способ хра- нения данных. Такие данные можно плотно упаковать и поиск по ним работает быстрее. Последнее время jsonb используется намного чаще, чем json. test=# ALTER TABLE student_details ADD details_b jsonb; test=# UPDATE student_details SET details_b = to_jsonb(details); test=# SELECT de_id, details_b FROM student_details \gx -[ RECORD 1 ]-------------------------------------- de_id | 1 details_b | {"недостатки": "неумеренное употребление мороженого", "достоинства": "отсутствуют"} -[ RECORD 2 ]-------------------------------------- de_id | 2 details_b | {"хобби": {"гитарист": {"гитары": ["страт", "телек"], "группа": "Постгрессоры"}}} -[ RECORD 3 ]-------------------------------------- de_id | 3 details_b | {"хобби": "косплей", "достоинства": {"мать-героиня": {"Вася": "м", "Люся": "ж", "Саша": "сведения отсутствуют", "Макар": "м", "Семен": "м"}}} -[ RECORD 4 ]-------------------------------------- de_id | 4 details_b | {"статус": "отчислена"} Можно заметить, что, кроме иной формы записи, изме- нился порядок значений в парах: Саша, сведения о ко- торой, как мы помним, отсутствуют, заняла теперь место 103 в списке перед Макаром. Это не недостаток jsonb отно- сительно json, а особенность хранения информации. Для работы с jsonb набор операторов больше. Один из полезнейших операторов — оператор вхождения в объ- ект @>. Он напоминает #> для json. Найдем запись, где упоминается дочь матери-героини Люся: test=# SELECT s.name, jsonb_pretty(sd.details_b) json FROM student_details sd, students s WHERE s.s_id = sd.s_id AND sd.details_b @> '{"достоинства":{"мать-героиня":{}}}' \gx -[ RECORD 1 ]------------------------------------- name | Нина json | { + | "хобби": "косплей", + | "достоинства": { + | "мать-героиня": { + | "Вася": "м", + | "Люся": "ж", + | "Саша": "сведения отсутствуют",+ | "Макар": "м", + | "Семен": "м" + | } + | } + | } Мы использовали функцию jsonb_pretty(), которая форматирует вывод типа jsonb. Или можно воспользоваться функцией jsonb_each(), разворачивающей пары ключ-значение: 104 test=# SELECT s.name, jsonb_each(sd.details_b) FROM student_details sd, students s WHERE s.s_id = sd.s_id AND sd.details_b @> '{"достоинства":{"мать-героиня":{}}}' \gx -[ RECORD 1 ]------------------------------------- name | Нина jsonb_each | (хобби,"""косплей""") -[ RECORD 2 ]------------------------------------- name | Нина jsonb_each | (достоинства,"{""мать-героиня"": {""Вася"": ""м"", ""Люся"": ""ж"", ""Саша"": ""сведения отсутствуют"", ""Макар"": ""м"", ""Семен"": ""м""}}") Между прочим, вместо имени ребенка Нины в запросе было оставлено пустое место {}. Такой синтаксис до- бавляет гибкости процессу разработки реальных прило- жений. Но главное, пожалуй, возможность создавать для jsonb индексы, поддерживающие оператор @>, обратный ему <@ и многие другие. Среди имеющихся для jsonb индек- сов, как правило, лучше всего подходит GIN. Для json индексы не поддерживаются, поэтому для приложе- ний с серьезной нагрузкой как правило лучше выбирать jsonb, а не json. Подробнее о типах json и jsonb и о функциях для ра- боты с ними можно узнать на страницах документа- ции PostgreSQL postgrespro.ru/doc/datatype-json и postgrespro.ru/doc/functions-json. 105 PostgreSQL для приложения Отдельный пользователь В предыдущей главе мы подключались к серверу баз данных под пользователем postgres, единственным су- ществующим сразу после установки СУБД. Но postgres обладает правами суперпользователя, поэтому прило- жению не следует использовать его для подключения к базе данных. Лучше создать нового пользователя и сде- лать его владельцем отдельной базы данных — тогда его права будут ограничены этой базой. postgres=# CREATE USER app PASSWORD 'p@ssw0rd'; CREATE ROLE postgres=# CREATE DATABASE appdb OWNER app; CREATE DATABASE Подробнее про пользователей и привилегии: postgrespro.ru/doc/user-manag.html и postgrespro.ru/doc/ddl-priv.html. Чтобы подключиться к новой базе данных и работать с ней от имени созданного пользователя, выполните: 106 postgres=# \c appdb app localhost 5432 Password for user app: *** You are now connected to database "appdb" as user "app" on host "127.0.0.1" at port "5432". appdb=> В команде указываются последовательно имя базы дан- ных (appdb), имя пользователя (app), узел (localhost или 127.0.0.1) и номер порта (5432). Обратите внимание, что подсказка изменилась: вместо «решетки» (#) теперь отображается символ «больше» (>) – решетка указы- вает на суперпользователя по аналогии с пользователем root в Unix. Со своей базой данных пользователь app может рабо- тать без ограничений. Например, можно создать в ней таблицу: appdb=> CREATE TABLE greeting(s text); CREATE TABLE appdb=> INSERT INTO greeting VALUES ('Привет, мир!'); INSERT 0 1 Удаленное подключение В нашем примере клиент и СУБД находятся на одном и том же компьютере. Разумеется, вы можете устано- вить PostgreSQL на выделенный сервер, а подключаться к нему с другой машины (например, с сервера приложе- ний). В этом случае вместо localhost надо указать адрес 107 вашего сервера СУБД. Но этого недостаточно: по умол- чанию из соображений безопасности PostgreSQL допус- кает только локальные подключения. Чтобы подключиться к базе данных снаружи, необходи- мо отредактировать два файла. Во-первых, файл основных настроек postgresql.conf (обычно располагается в каталоге баз данных). Найди- те строку, определяющую, какие сетевые интерфейсы слушает PostgreSQL: #listen_addresses = 'localhost' и замените ее на: listen_addresses = '*' Во-вторых, ph_hba.conf — файл с настройками аутен- тификации. Когда клиент пытается подключиться к сер- веру, PostgreSQL находит в этом файле первую сверху строку, соответствующую параметрам соединения по че- тырем параметрам: тип соединения, имя базы данных, имя пользователя и IP-адрес клиента. В той же строке написано, как пользователь должен подтвердить, что он действительно тот, за кого себя выдает. Например, в Debian и Ubuntu в этом файле есть, в числе прочих, такая строка: local all all peer 108 Она говорит о том, что локальные соединения (local) к любой базе данных (all) под любым пользователем (all) должны проверяться методом peer (IP-адрес для локальных соединений, конечно, не указывается). Метод peer означает, что PostgreSQL запрашивает имя текущего пользователя у операционной системы и счи- тает, что ОС уже выполнила необходимую проверку (спросила у пользователя пароль). Поэтому в Linux- подобных операционных системах пользователю обыч- но не приходится вводить пароль при подключении к серверу на своем компьютере: достаточно того, что пароль был введен при входе в систему. А вот Windows не поддерживает локальных соединений, и там строка выглядит следующим образом: host all all 127.0.0.1/32 md5 Она означает, что сетевые соединения (host) к любой базе данных (all) под любым пользователем (all) с ло- кального адреса (127.0.0.1) должны проверяться мето- дом md5. Этот метод подразумевает ввод пользователем пароля. Итак, для наших целей допишите в конец pg_hba.conf следующую строку: host appdb app all md5 Это разрешит доступ к базе данных appdb пользователю app с любого адреса при указании верного пароля. 109 |