Форум программистов, компьютерный форум, киберфорум
SQLite
Войти
Регистрация
Восстановить пароль
Блоги Сообщество Поиск  
 
 
17 / 5 / 0
Регистрация: 16.04.2016
Сообщений: 371

Когда лучше разбивать бд на отдельные таблицы и побитовые маски в бд

17.10.2026, 01:14. Показов 3908. Ответов 65
Метки нет (Все метки)

Студворк — интернет-сервис помощи студентам
Всех приветствую. У меня есть два вопроса по правильной организации структуры бд. два бесплатных ии (браузерный ии от гугла и gpt) ответили по-разному на мой первый вопрос.

1. Часть моей бд будет содержать Библию. Подскажите пожалуйста,как сделать бд минимального объёма? У меня есть несколько вариантов. Первый вариант для стиха создать таблицу с полями номер_книги (берётся из id с таблицей с книгами),номер_главы,номер_стиха,текст_с тиха и скрытое поле rowid,которое есть в sqlite и не только (все поля,кроме текста стиха и row_id - составной первичный ключ). Второй вариант - создать две таблицы. В первой - номер_книги,номер_главы,rowid (номер книги и номер главы - составной первичный ключ),а во второй - поля номер стиха,id (ссылается на rowid из первой таблицы) (эти поля составной первичный ключ),текст стиха и rowid (id стиха,который,к примеру,будет использоваться для зачал,которые,к примеру,читаются на православной Литургии,вечерней службе и т.д). А может есть ещё более оптимальный вариант. При каком варианте объём бд будет минимальный,а при каком - скорость выполнения sql запросов максимальной. Объясните пожалуйста почему. Какой бы вариант,и почему лучше выбрать,на Ваш взгляд,для android приложения. В первом варианте меня смущает дублирование данных для номера книги и номера главы. Также на будущее объясните пожалуйста,в каких случаях в бд лучше разбивать таблицы с большим количеством столбцов на несколько таблиц,а в каких - объединить в одну таблицу. К примеру что лучше сделать - отдельную таблицу с молитвами,отдельную - с акафистами,а отдельную - с канонами,или объединить молитвы,акафисты и каноны в одну таблицу,добавив числовое поле для молитвы со значением 0,для акафиста со значением 1,а для канона - со значением 2. Почему тот или иной вариант на ваш взгляд лучше? Понимаю,что,к примеру,для Библии уже есть куча готовых бд,но во-первых я не уверен,что найду оптимальную по структуре бд,а во-вторых мне это важно понимать и в будущем,поскольку у меня,скорее всего,будут похожие случаи. В моём случаи дублируется два числовых столбца,а если таких столбцов будет три или более (я понимаю,что текстовые столбцы дублироваться не должны)?

2. С целью оптимизации моей бд (как я ранее писал,Библия - это часть моей бд) я хочу использовать побитовые маски,к примеру чтобы отмечать,в каких православных поместных Церквях отмечается тот или иной праздник. Я спрашивал у гугловского ии в браузере мнение по этому поводу и он ответил мне,что хоть объём таблицы будет меньше,но поиск по бд замедлится,поскольку sqlite вынужден будет вычислять побитовое выражение для каждого значения таблицы. Также ии ответил мне,что при таком подходе я смогу хранить только 64 значения (я имею ввиду id записи). Также я понимаю,что поскольку каскадное удаление/обновление не будет работать,мне нужно будет написать соответствующий триггер,который,кстати,браузерный ии мне тоже подсказал. А вы что думаете по этому поводу? Использовали ли Вы на своём опыте побитовые маски в sqlite или в каких-либо диалектах sql? Интересно,может быть есть какие-то диалекты sql,которые поддерживают побитовый поиск,что ускорит работу с бд? Заранее благодарю всех за помощь.
0
IT_Exp
Эксперт
34794 / 4073 / 2104
Регистрация: 17.06.2006
Сообщений: 32,602
Блог
17.10.2026, 01:14
Ответы с готовыми решениями:

Как из таблицы со списком, когда аудитории заняты, собрать таблицу со списком когда они свободны
Есть таблица: ID НомерАудитории Дата ВремяНачалааЗанятия ВремяКонца занятия 1 123 ...

Посоветуйте как лучше разместить таблицы в сети с каким лучше доступом
Нужен совет с каким доступом лучше разместить временные таблицы в сети? если у меня таблицы...

Проектирование таблицы Клиенты(и юрид. и физ. лица)Нужно ли разбивать на подтаблицы?
Вопрос в следуещем: есть таблица Клиенты, в которой находится некая общая информация про клиента....

65
923 / 296 / 58
Регистрация: 01.06.2023
Сообщений: 847
21.10.2026, 09:37
Студворк — интернет-сервис помощи студентам
Цитата Сообщение от КАВ Посмотреть сообщение
Для чего мне это поможет? Для того,чтобы читать текст из бд быстрее? По поводу markdown,его тоже нужно будет парсить и я не думаю,что он будет быстрее html.
Цитата Сообщение от КАВ Посмотреть сообщение
Вы имеете ввиду макросы на уровне sqlite? Если да,не знал,что они там есть.
Такая разметка лишь уменьшит объем базы так как текст будет физически меньше по размеру. Расшифровка ляжет уже плечи приложения. БД не поддерживает такого понятия как макросы.

Цитата Сообщение от КАВ Посмотреть сообщение
Понятно. А что Вы думаете,если добавить таблицу с переводами,где будет id перевода,id,связанный с таблицей языка и поле,которое будет вести себя как rowid и на которое мы будем ссылаться в каждом стихе. К примеру мы получаем id стиха,потом получаем id_text данного стиха,затем получаем id строки из таблицы переводов для выбранного нами перевода,используя id_text стиха,а потом получаем саму строку для конкретного языка,используя id_строки. Возможно объяснил непонятно.
Под рукой нет Вашей БД но принцип организации я бы ввел следующий

тип_книги(id,кодовое_имя)

тексты (id_текста, код_языка, текст) -- id_текста повторятся для разных языков, код_языка - двухбуквенный код или int число от справочника

книга (id_книги, id_типа_книги, кодовое_имя_книги, id_текста_с_названием_книги)

книга_содержание (id_содержания, id_книги, id_текста_заголовка, номер_главы, id_текста_содержания) --В данном случае можно как раз как Вы и хотели и сэкономить на заголовках переиспользуя одинаковые тексты заголовков

Кстати будет ли эта экономия вообще,поскольку и для стиха нужен будет roid,чтобы потом на него ссылаться,к примеру,в евангельских чтениях на каждый день (в таблицу с зачалами я хочу добавлять id стиха). Кстати ведь у стиха будет и номер стиха (по крайней мере я об этом подумываю,хотя его можно прописывать и в тексте каждого стиха). И тогда будет вообще дубляж - таблица со стихами,содержащая номер книги,номер главы,номер стиха,id_text стиха,который ссылается на id строки из таблицы переводов,который ссылается на id в огромной языковой таблице,где будет код языка,id строки и текст строки (по крайней мере у меня такая задумка).
Формат ссылки у Вас останется прежний <КодовоеИмяКниги>.<Номер_главы>.<Диапазо н_номеров_стихов>

Информация о языке останется внешней глобальной настройкой и не участвует в ссылке.

Кстати как Вы вычислили объём,т.е,что экономия,всё-таки будет,хоть и небольшая.
Это просто "экспертное мнение".

К примеру как быть,если будет два дублирующихся столбца - номер главы/номер книги - а если три - а если н.
не смотря на то что они содержат одинаковую информацию по смыслу у них разное предназначение. точно не помню но вроде в таблице с описаниями есть столбец с "саном" (могу ошибаться), так вот он записан текстом и повторяется во многих строках. Здесь полезно вынести значение текстовое в отдельную таблицу а на месте использовать его ID.

А вот в этих случаях с числовыми столбцами как быть?
Если их всего несколько, то возможно не стоит. Почитайте про нормализацию БД

Я тоже об этом думал,но побитовые маски сэкономили бы объём бд.
Церквей 15 а праздников сколько? и что если нужно запросить не в каких церквях проходит событие а наоборот
. какие события происходят в церквях тогда ни какие индексы не помогут на битовых масках. придется идти полным перебором

Еще по организации БД - у Вас не увидел ни одного индекса, без них поиск работает плохо.

Добавлено через 1 час 50 минут
Еще не много мыслей по поводу хранения

Кликните здесь для просмотра всего текста

Язык все таки в отдельную таблицы вывел бы. А в интерфейсе бы добавил бы возможность менять приоритеты

SQL
1
2
3
4
5
6
7
8
CREATE TABLE lang (
 id INTEGER PRIMARY KEY,
 code text,
 priority INTEGER 
)
 
INSERT INTO lang VALUES (1,'cz',1);
INSERT INTO lang VALUES (2,'ru',2);

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
--Таблица нужна лишь для ключа
CREATE TABLE texts(
  id_text INTEGER PRIMARY KEY 
)
 
--Вариации текста на разных языках
CREATE TABLE texts_content (
  id_text INTEGER,
  id_lang INTEGER,
  text_value text
)
 
--Тип книги
CREATE TABLE book_type(
  id INTEGER PRIMARY KEY,
  code text,
  description text  
)
 
INSERT INTO book_type VALUES(1,'BIBLE','Библия')
 
--Сущность одной книги
CREATE TABLE book (
  id INTEGER PRIMARY KEY ,
  id_book_type INTEGER,
  code text,
  id_text_name INTEGER
)
 
INSERT INTO texts VALUES(1);
INSERT INTO texts_content VALUES(1,2,'Первая книга Моисеева. БЫТИЕ');
INSERT inot book VALUES(1,1,'Gen',1)
 
--Главы или части книги
CREATE TABLE book_chapter (
  id INTEGER PRIMARY KEY,
  id_book INTEGER,
  chapter INTEGER,
  id_text_chapter INTEGER,
  id_text_content INTEGER
)
 
INSERT INTO texts VALUES(2);
INSERT INTO texts_content VALUES(2,2,'Глава 1');
INSERT INTO texts VALUES(3);
INSERT INTO texts_content VALUES(3,2,'<p>1 В начале сотворил Бог небо и землю.
<p>2 Земля же была безвидна и пуста, и тьма над бездною, и Дух Божий носился над водою.
...');
INSERT INTO book_chapter VALUES (1,1,1,2,3);

Получение текста в приоритетном переводе

SQL
1
2
3
4
5
6
SELECT text_value
FROM texts_content INNER JOIN lang ON lang.id = texts_content.id_lang
WHERE 
  texts_content.id_text = :id_text 
ORDER BY lang.priority
LIMIT 1
Получение текста в требуемом переводе

SQL
1
2
3
4
5
SELECT text_value
FROM texts_content INNER JOIN lang ON lang.id = texts_content.id_lang
WHERE 
  texts_content.id_text = :id_text 
  AND lang.code = :lang_code

prayers удобно уклыдываются на структуру хранения книг

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
--для prayers ввести отдельный тип книг
INSERT INTO book_type VALUES(2,'prayers','prayers');
 
--Каждую группу оформить как отдельную книгу
INSERT INTO texts VALUES(5);
INSERT INTO texts_content VALUES(5,2,'Молитвенные правила');
 
INSERT inot book VALUES(2,2,'MOL_RULE',5);
 
--Каждый отдельный prayers - это отдельная глава, а номер главы использовать для хранения id2 
 
INSERT INTO texts VALUES(4);
INSERT INTO texts_content VALUES(4,1,'Мlтвы ќтрєнніz');
INSERT INTO texts_content VALUES(4,2,'Молитвы утренние');
 
INSERT INTO texts VALUES(6);
INSERT INTO texts_content VALUES(6,1,'<FONT COLOR=#aa2c2c> <i>Воспрzнyвъ без8 лёности, и3 и3стрезви1всz, востaвъ t снA, рцы2 сіE:</i> </FONT>
...');
INSERT INTO texts_content VALUES(6,2,'<FONT COLOR=#aa2c2c> <i>Востав от сна, прежде всякого другого дела, стань благоговейно, представляя себя пред Всевидящим Богом, и, совершая крестное знамение, произнеси:</i> </FONT>
...');
INSERT INTO book_chapter VALUES (2,2,1 /*id2 из оригинала*/,4,6);
0
17 / 5 / 0
Регистрация: 16.04.2016
Сообщений: 371
22.10.2026, 16:39  [ТС]
Простите,что не заметил сообщения раньше. поэтому отвечаю сейчас.
тип_книги(id,кодовое_имя)
Вот я примерно так же думаю,только вместо кодового имени будет id из таблицы со строками,чтобы,к примеру,строку Евангелие от Матфея можно было перевести на разные языки.
код_языка - двухбуквенный код или int число
Второе мне нравится больше.
книга (id_книги, id_типа_книги, кодовое_имя_книги, id_текста_с_названием_книги)
А вторая таблица зачем? Код книги,думаю,не важен,поскольку,к примеру,новый или старый завет я буду определять по id книги.
книга_содержание (id_содержания, id_книги, id_текста_заголовка, номер_главы, id_текста_содержания)
Может вместо номера главы использовать id главы,т.е сделать такую таблицу,чтобы можно было с лёгкостью перескочить на нужную главу,а для глав сделать отдельную таблицу,содержащую номер книги,номер главы и id главы (наследующийся от roid). Я не говорю,что Вы не правы. Просто я хочу понять,как поступать в таких случаях на будущее,т.е когда есть много числовых столбцов,которые дублируются. Кстати у нас в бд,как Вы написали,каждый стих - это отдельный параграф. Но вообще я хочу,чтобы каждый стих был отдельной записью в таблице,поскольку,как будет далее,я хочу переиспользовать его в чтениях на каждый день. Да и,к примеру,проще будет отметить отдельный стих.
<Диапазо н_номеров_стихов>
Т.е id столбец с начальным id стиха и столбец с конечным id стиха,или по-другому? Если так,то,наверное,такое же можно провернуть и с главами,т.е создать таблицу,где будет три столбца - id книги,id начальной главы,id конечной главы. Ведь я могу гарантировать,что для каждой главы/каждого стиха будет свой id. Кстати тут вырисовывается ещё одна проблема - в нашем приложение будут (и уже есть) зачала,т.е что читается на Литургии,что на других службах,к примеру на службу в Страстную Пятницу. В этом случае хотелось бы получать не только id стихов,но и id книги и id главы. Кстати в этом случаи диапазон стихов здесь не пройдёт,поскольку бывает так,что в зачалах какие-то стихи пропускаются. К примеру часть из 10-й главы,а часть из 11-й. Поэтому может логичнее в таблицу со стихом добавлять и id книги и id главы,поскольку при выборке списка чтений должен выводиться номер книги,номер глав и номер стиха. "К примеру Лк., 54 зач., X, 38-42; XI, 27-28" значит,что мы должны выбрать 54 зачало которое зависит от id книги Евангелие от Луки,при этом с десятой главы мы болжны выбрать стихи с 38 по 42 включительно,а с 11-й - стихи с 27 по 28. При клике на это зачало должны вывестись сами стихи на выбранном языке.
Это просто "экспертное мнение".
Понятно. Просто я пытался вывести формулу,которая бы вычисляла этот момент,но так и не смог этого сделать.
Здесь полезно вынести значение текстовое в отдельную таблицу а на месте использовать его ID.
Вот для текстовых мне понятно,поскольку мне здесь уже это объясняли и кстати я уже давно выпустил новую версию нашего приложения,бд для которого я проектировал в основном используя советы из этого форума,которые мне давали. И,по-моему Вы мне тоэже тогда помогали,за что Вам огромное спасибо.
Если их всего несколько, то возможно не стоит. Почитайте про нормализацию БД
Вот тут и загвоздка,что значит несколько. Просто у меня,как мне кажется,не стандартная ситуация. Если бы были строковые столбцы,тут не было бы вопросов,а когда они числовые - вопрос,что будет лучше по объёму.
тогда ни какие индексы не помогут на битовых масках.
А,здесь Вы правы. Ведь,к примеру,я захочу выбрать все праздники,которые отмечает Русская Православная Церковь,но мне придётся вычислять каждое значение,чтобы проверить,нет ли в побитовых значениях id Русской Православной Церкви. Очень жаль,что sqlite как и,наверное,другие диалекты sql,не предусматривают побитового индексирования. Кстати интересно когда,на Ваш взгляд,побитовые маски могут быть реально полезны,ведь,к примеру,в sqlite есть побитовые операторы,да и,наверное,не только в нём.
Еще по организации БД - у Вас не увидел ни одного индекса, без них поиск работает плохо.
А вот с индексами у меня реальная проблема,согласен. Просто мне непонятно,когда они ннужны,а когда - нет. Когда нужны составные первичные ключи,а когда - простые первичные ключи,а когда лучше создавать индексы. Вот что,кстати,мне ещё до создания этой темы отвечала гугловская браузерная нейронка.
Наименьший объем и максимальную скорость в SQLite даст одна таблица БЕЗ составных ключей, использующая WITHOUT ROWID или явный INTEGER PRIMARY KEY.
Но если выбирать строго из ваших двух вариантов, то Первый вариант значительно лучше как по объему, так и по скорости. Ниже подробно разобрано, почему.

Сравнение ваших вариантов
Вариант 1 (Одна таблица: книга, глава, стих, текст)
Вы хотите сделать составной PK из (номер_книги, номер_главы, номер_стиха).
• Объем: В SQLite составной первичный ключ создает автоматический индекс. Сама таблица со скрытым rowid занимает данные + индекс.
• Скорость: Высокая для поиска конкретного стиха, так как индекс сразу находит нужную строку.
Как сделать Вариант 1 еще лучше (Идеал для Android):
Вместо составного ключа сделайте id INTEGER PRIMARY KEY (это автоматически привяжется к rowid и не займет лишнего места), а поля book, chapter, verse сделайте обычными INTEGER. Для быстрого поиска создайте один составной индекс по этим трем полям.
Либо используйте таблицу WITHOUT ROWID, где составной ключ (book, chapter, verse) станет физическим адресом строки, что сэкономит место на B-деревьях.
Вариант 2 (Две таблицы: Книги/Главы + Стих/Текст)
• Объем: Самый большой. Разделение на две таблицы создает огромные накладные расходы. Каждая таблица в SQLite — это отдельное B-дерево. Вам придется хранить внешние ключи (ID главы) в каждой из ~31 000 строк со стихами. Потери на служебные данные страниц БД перевесят любую мнимую экономию.
• Скорость: Самая низкая. Чтобы прочитать главу или стих, SQLite придется делать операцию JOIN (соединение таблиц). На мобильных устройствах это лишний расход процессора и памяти.
Вывод для Библии в Android: Используйте одну таблицу. Текст Библии статичен, он никогда не меняется. Дробить его на таблицы «Главы» и «Стихи» — это избыточная нормализация, которая только вредит.
[]
Поэтому тут ещё на будущее хотелось бы,чтобы Вы мне объяснили,когда нужен в моём случаи,а когда не нужен составной первичный ключ,когда - простой,а когда нужно создавать индекс. Как я понимаю можно ориентироваться на команду sqlite explain query plan (или как-то так).
Язык все таки в отдельную таблицы вывел бы. А в интерфейсе бы добавил бы возможность менять приоритеты.
А зачем нам приоритеты языка? Кстати код языка (а точнее его имя) я бы,наверное,добавил как целочисленное поле,которое бы тоже ссылалось на огромную таблицу с переводами.
CREATE TABLE texts(
  id_text INTEGER PRIMARY KEY
)
А вот тут мне непонятно,зачем отдельная таблица с одним столбцом,если как раз можно запихнуть это в таблицу с переводами texts_content. Просто лично для меня странно иметь таблицу с одним столбцом,хотя,возможно,в бд это встречается,просто я об этом не знаю. Кстати нам будет нужно использовать явные ссылки (вроде reference) и сделать,чтобы поддерживалось каскадное удаление/каскадное обновление. Также мне непонятно,зачем нам отдельная таблица для типа книги,если всё это можно впихнуть в таблицу с книгой - id_книги,id_name_книги. Я не говорю,что Вы не правы. Просто я хочу понять на будущее,почему то или иное решение на Ваш взгляд наиболее правильное. P.s бегло просмотрел код,поэтому не всё заметил. Спаси Вас Господи.
0
923 / 296 / 58
Регистрация: 01.06.2023
Сообщений: 847
22.10.2026, 19:41
Цитата Сообщение от КАВ Посмотреть сообщение
Вот я примерно так же думаю,только вместо кодового имени будет id из таблицы со строками,чтобы,к примеру,строку Евангелие от Матфея можно было перевести на разные языки.
Это всего лишь мои мысли и проектирование как мне бы было проще организовать/обобщить информацию. Кодовое имя здесь больше нужно что бы потом в коде не id оперировать а именем. Так потом проще читать код. Например получить список всех книг определенного типа:

SQL
1
SELECT * FROM book WHERE book.id_book_type = (SELECT id FROM book_type WHERE code = 'BIBLE')
Цитата Сообщение от КАВ Посмотреть сообщение
А вторая таблица зачем? Код книги,думаю,не важен,поскольку,к примеру,новый или старый завет я буду определять по id книги.
таблица книга - включит в себя Вашу текущую bible с своим типом. Например "Первая книга Моисеева. БЫТИЕ" - это запись в таблицы "книга" в которой есть ссылка на запись "тип_книги" с кодом "BIBLE".

завязываться на id это скажем так плохая практика.

Цитата Сообщение от КАВ Посмотреть сообщение
Может вместо номера главы использовать id главы
id_содержания это и есть id в данном случае главы, в интерпретации book_chapter как раз используется id

Цитата Сообщение от КАВ Посмотреть сообщение
для глав сделать отдельную таблицу,содержащую номер книги,номер главы и id главы (наследующийся от roid). Я не говорю,что Вы не правы. Просто я хочу понять,как поступать в таких случаях на будущее,т.е когда есть много числовых столбцов,которые дублируются. Кстати у нас в бд,как Вы написали,каждый стих - это отдельный параграф. Но вообще я хочу,чтобы каждый стих был отдельной записью в таблице,поскольку,как будет далее,я хочу переиспользовать его в чтениях на каждый день. Да и,к примеру,проще будет отметить отдельный стих.
Если как сейчас текст всей главы помещается в одну строку то выделение не имеет смысла так как соединение будет 1 к 1.

Если тексты потом делить на стихи, т.е. одна запись в таблице - это один стих, тогда выделение "Оглавления" в отдельную структуру имеет смысл. т.е. структура приобретает вид (книга) - 1:N - (Глава) - 1:N (Стих). На самом деле стих можно и не выделять в отдельную структуру. Здесь можно организовать иерархическую структуру, когда глава 2-го уровня (он же стих) ссылается на главу 1-го уровня.

book_chapter (id, id_book, id_chapter_parent, id_text_chapter , chapter , id_text_content )

Пример заполнения.

idid_bookid_chapter_parentid_text_chapterchapterid_text_contentПримечание
11NULL11NULLЭта запись только описания главы, собственного текста не имеет. Номер - это номер главы
211NULL13Далее записи стихов. 'номер' - это номер стиха. Своего заголовка не имеют, но у них есть содержание. id_chapter_parent ссыдыется на главу 1-го уровня (1)
311NULL24 
411NULL35 

Выигрыша по месту не даст, но позволит быстрее искать нужные стихи. при этом если в будущем понадобится 3-й уровень детализации. То даже не понадобится менять структуру таблиц, просто запись 3-го уровня будет ссылаться на записи 2-го уровня


Цитата Сообщение от КАВ Посмотреть сообщение
Лк., 54 зач., X, 38-42; XI, 27-28
Интересная адресация, но в целом реализуемо на уровне парсера ссылок, парсер должен будет сделать несколько запросов и склеить тексты. Не очень понял про "Зачало", опущу его и опишу как понял. Принимаем вариант когда содержание разбито еще и по номерам стихов.

Сначала определим книгу, код "Лк" на разных языках будет так же выглядеть по разному? Если да тогда нужна будет еще одна таблица с перечнем кодов на разных языках. Но пока остановимся на том что у всех один

SQL
1
SELECT id FROM book WHERE book.id_book_type = (SELECT id FROM book_type WHERE code = 'BIBLE') AND book.code = 'ЛК'
Дальше для каждого фрагмента определяем диапазон Ур1: 10, Ур2: 38-42

Запрос для определения заголовка главы

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT 
 id,
 ( --Извлекаем текст заголовка в приоритетном переводе
  SELECT text_value
  FROM texts_content INNER JOIN lang ON lang.id = texts_content.id_lang
  WHERE 
    texts_content.id_text = id_text_chapter
  ORDER BY lang.priority
  LIMIT 1
 ) sTitle
FROM book_chapter 
WHERE 
  id_book = $1 --Код книги из прошлого запроса
  AND id_chapter_parent IS NULL --Верхний уровень
  AND chapter = 10 --Затребованный номер главы
Запрос для извлечения текста стихов


SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT 
  GROUP_CONCAT (--Извлекаем текст в приоритетном переводе
   '<p>' || chapter || ' ' || ( 
    SELECT text_value
    FROM texts_content INNER JOIN lang ON lang.id = texts_content.id_lang
    WHERE 
      texts_content.id_text = id_text_content
    ORDER BY lang.priority
    LIMIT 1
   ) || '</p>', CHAR(10)   
  ) sContent
FROM book_chapter 
WHERE 
  id_chapter_parent = $2 --Id главы из прошлого запроса, он уникален поэтому код книги больше не нужен
  AND chapter BETWEEN 38 AND 42 --Затребованные номера стихов
Последние два запроса повторяем для второго фрагмента Ур1: 11, Ур2: 27-28 и объединяем фрагменты текстов.

Цитата Сообщение от КАВ Посмотреть сообщение
Кстати интересно когда,на Ваш взгляд,побитовые маски могут быть реально полезны,ведь,к примеру,в sqlite есть побитовые операторы,да и,наверное,не только в нём
Реально полезны когда по ним не нужно искать и это просто маркеры свойств, в sqlite основой для быстрого поиска являются бинарные деревья и вот битовые маски на них плохо ложатся.

Цитата Сообщение от КАВ Посмотреть сообщение
А вот с индексами у меня реальная проблема,согласен. Просто мне непонятно,когда они ннужны,а когда - нет. Когда нужны составные первичные ключи,а когда - простые первичные ключи,а когда лучше создавать индексы. Вот что,кстати,мне ещё до создания этой темы отвечала гугловская браузерная нейронка.
Тут все очень просто если набор полей есть в поисковом запросе - то нужен индекс.

SQL
1
2
3
4
5
--Частичный индекс для поиска фрагментов верхнего уровня
CREATE INDEX ix_book_chapter_top book_chapter(id_book, chapter) WHERE id_chapter_parent IS NULL;
 
--Частичный индекс для поиска фрагментов не верхнего уровня
CREATE INDEX ix_book_chapter_top book_chapter(id_chapter_parent, chapter) WHERE id_chapter_parent IS NOT NULL;
Еще полезно создавать индексы на FK ключи если они используются - это уменьшит время поиска при валидации и удалении

Цитата Сообщение от КАВ Посмотреть сообщение
А зачем нам приоритеты языка?
Что бы если книга была не доступна на выбранном языке, но у нее есть перевод на родственном, то можно было бы посмотреть в переводе

Цитата Сообщение от КАВ Посмотреть сообщение
Кстати код языка (а точнее его имя) я бы,наверное,добавил как целочисленное поле,которое бы тоже ссылалось на огромную таблицу с переводами.
Да посмотрите мои заметки, доступные языки собраны в таблицу lang, в таблице с текстами "texts_content" используется как раз числовой код языка

Цитата Сообщение от КАВ Посмотреть сообщение
А вот тут мне непонятно,зачем отдельная таблица с одним столбцом,если как раз можно запихнуть это в таблицу с переводами texts_content. Просто лично для меня странно иметь таблицу с одним столбцом,хотя,возможно,в бд это встречается,просто я об этом не знаю. Кстати нам будет нужно использовать явные ссылки (вроде reference) и сделать,чтобы поддерживалось каскадное удаление/каскадное обновление.
А это как раз для ссылочной целостности, просто так получилось что таблица выродилась до одного первичного ключа. Например в таблице book_chapter есть поле которые ссылается не texts.id а не на texts_content.id_text, так как поле id_text в texts_content не уникально и организовать ссылочную целостность средствами СУБД не возможно.

В моей схеме фрагмент текста во всех его вариантах перевода - это самостоятельная сущность. И например для наименований глав может переиспользоваться в разных книгах если наименования одинаковые.

Цитата Сообщение от КАВ Посмотреть сообщение
Также мне непонятно,зачем нам отдельная таблица для типа книги,если всё это можно впихнуть в таблицу с книгой - id_книги,id_name_книги.
Все очень просто, вместо того что бы хранить кучу разрозненных текстов в разных таблицах все объединить под единую структуру. т.е. то что у Вас сейчас хранится в таблицах bible, description_holiday, holiday_lessons, prayers*, psaltur*, termin* все свести к единой структуре. При этом тип книги это отражение название таблицы в Вашей текущей структуре.

Единая структура позволит держать единый код как для получения текстов так и для их редактирования это в перспективе упрощает разработку
0
17 / 5 / 0
Регистрация: 16.04.2016
Сообщений: 371
22.10.2026, 23:39  [ТС]
Кодовое имя здесь больше нужно что бы потом в коде не id оперировать а именем. Так потом проще читать код.
Ну в коде можно будет сделать соответствующие константы для этого,к примеру:
C
1
#def evangelie_matfei = 33
Число взял с головы. Просто моя цель - сделать минимальный объём,хотя,как говорил один веб программист в таких случаях,это экономия на спичках.
завязываться на id это скажем так плохая практика.
А вот этого не знал. Интересно почему,если не считать сложность читаемости кода? Ведь,к примеру,в случаи смены id каскадное обновление изменит id везде. Кстати перестроятся ли индексы в этом случаи,или для этого нужно будет писать свой триггер?
Если как сейчас текст всей главы помещается в одну строку то выделение не имеет смысла так как соединение будет 1 к 1.
Что значит в одну строку? Я ведь могу в одну строку,очень грубо говоря,поместить войну и мир,правда,боюсь,даже самый мощный сервак загнётся от чтения такой строки,не говоря о смарте,поскольку,как я понял sqlite,вроде,выделяет страницы под чтение длинных строк,и чем длиннее строка,тем медленнее она читается. Как я догадываюсь,Вы имели ввиду классическую строку длинной 256 символов,включая нулевой символ,или максимальный размер строки,для которого sqlite не нужны дополнительные страницы.
На самом деле стих можно и не выделять в отдельную структуру. Здесь можно организовать иерархическую структуру, когда глава 2-го уровня (он же стих) ссылается на главу 1-го уровня.
Это должна быть таблица с двумя полями? Кстати об этом я тоже подумывал ведь,к примеру,одна из книг Библии - псалтирь,читается отдельно. В Библии это не будет заметно,а вот если его читать за живых и за усопших (в нашей бд она дублируется),то он разделяется на кафизмы,кафизмы на псалмы и в каждом псалме - отдельный стих,но это я буду организовывать по-другому,чтобы не затрагивать таблицу с Библией. Я не уверен,может ли один столбец с одной таблицы ссылаться на значение другого столбца этой же таблицы. Как будет в этом случаи работать каскадное удаление/каскадное обновление? В общем идея-то хорошая,но я не очень представляю,как организовать многоуровневую иерархическую структуру в sqlite,как и в любом другом sql диалекте. Как в этом случаи будет работать индексация.
id id_book id_chapter_parent id_text_chapter chapter id_text_content Примечание
Понятно. Буду разбираться. Кстати в этом случаи объём бд будет меньше? Получается если id_chapter_parent не равен 0,это стих,а в противном случаи - глава. Кстати я подумал,ведь в этом случаи нам нужно будет одно текстовое поле - для главы это заголовок,а для стиха это будет текст стиха. Как я ранее писал,надеюсь sqlite не запретит мне ссылаться на одно из значений таблицы,хотя если не делать этот столбец как reference,то всё будет норм,но я хочу,чтобы это был reference,согласно правил создания бд. Просто из-за этого вопроса я и думал выделить стих в отдельную таблицу,хотя над иерархической структурой бд я тоже задумывался.
Выигрыша по месту не даст, но позволит быстрее искать нужные стихи. при этом если в будущем понадобится 3-й уровень детализации. То даже не понадобится менять структуру таблиц, просто запись 3-го уровня будет ссылаться на записи 2-го уровня
А вот это классно,что можно будет ссылаться на предыдущий уровень. Просто,кроме кафизм,есть момент,где тоже пригодились бы более глубокие уровни. Кстати Вы уверены,что выигрыша в объёме это не даст? Главное,чтобы не было проигрыша. Ещё раз огромное спасибо Вам за идею с иерархической структурой,поскольку я сам об этом думал,но не знаю,что об этом подумает sqlite,если я буду делать reference на значения этой же таблицы. Тогда по родительской главе,надеюсь,я смогу запросить номер этой главы,чтобы понять,к примеру,к какой главе по номеру (не по id) относится запись.
Интересная адресация, но в целом реализуемо на уровне парсера ссылок, парсер должен будет сделать несколько запросов и склеить тексты.
Нет,я хочу сделать так,чтобы на основе моей выборки по конкретному зачалу (к примеру я знаю,что в зачале 10 Евангелия от Луки в 10-й главе читаются стихи с 38 по 42 включительно,а в главе 11 стихи с 27 по 28 включительно). И вот по моей задумке мой запрос должен мне отдать id зачала,а по id зачала я получу тип книги и номера глав и стихов в этих главах (по сути их ид,из которых я всё это буду получать). А при клике на это зачало я получу уже тексты этих стихов. По идее для каждого зачала можно просто добавить текст,поскольку на каждом языке он будет разный,правда мне всё равно нужно будет делать выборку типа книги,номера глав и номера стихов в этих главах. Кстати мне ещё предстаит сортировка,чтобы стихи не шли в разнобой. Надеюсь это можно будет реализовать в предложенной Вами иерархической структуре,правда я даже не представляю какие sql запросы в этом случаи будут,хотя бы примерно Хотя читая Ваше сообщение дальше,надеюсь пойму (просто я для ускорения процесса читаю часть сообщения и отвечаю на него).
Реально полезны когда по ним не нужно искать и это просто маркеры свойств, в sqlite основой для быстрого поиска являются бинарные деревья и вот битовые маски на них плохо ложатся.
Но ведь есть вероятность,что я захочу выбрать всех пользователей,у кого есть то или иное свойство и тогда мне придётся снова использовать перебор. Кстати,на Ваш взгляд,есть ли вероятность,что какой-нибудь диалект sql реализует побитовый поиск,чтобы побитовые маски массово применялись? Ведь это,на мой взгляд,могло бы уменьшить размеры бд.
Частичный индекс
Что значит частичный индекс? Кстати иногда,наверное,можно использовать и unique_индекс. Кстати индекс,наверное,нужен составной,если мы ищем по нескольким полям,к примеру используем and или or? FK,как я понял,это внешний ключ,который используется для построения связей одной таблицы с другой. Помнится мы всё это изучали в институте,но пока ты делаешь упражнения,ты мало что понимаешь и когда ты со всем этим сталкиваешься на практике - ты не представляешь,как ко всему этому подступиться. Да и у нас в институте были бд гораздо проще,чем моя бд. Мне кажется,что эта бд вообще самая сложная (или одна из самых сложных бд),которые только есть,или Вам встречались бд и посложнее?
Что бы если книга была не доступна на выбранном языке, но у нее есть перевод на родственном, то можно было бы посмотреть в переводе
Вы умеете читать мои мысли,как прочитали мою мысль про иерархическую структуру. Просто бывает так,что,к примеру,не всё есть на церковно-славянском. Тогда я хочу отображать текст на русском. Но для этого я хочу создать отдельную таблицу - id_языка id_родственного языка,при этом выборки должны работать и в обратную сторону (правда я не представляю,как это реализовать через sql запросы,хотя пока пишу,соображаю как). К примеру если нету текста на церковно-славянском,выбираем язык из первого столбца - русский,а если нету на русском - выбираем из второго столбца - церковнославянский. Просто не хочется создавать лишние записи,в которых первый и второй столбец будут просто поменяны местами.
так как поле id_text в texts_content не уникально
Но ведь для этого мы можем сделать составной первичный ключ,содержащий id_текста и id_языка,а для поиска создадим индекс,или Вы не сторонник составных первичных ключей?
Все очень просто, вместо того что бы хранить кучу разрозненных текстов в разных таблицах все объединить под единую структуру. т.е. то что у Вас сейчас хранится в таблицах bible, description_holiday, holiday_lessons, prayers*, psaltur*, termin* все свести к единой структуре.
А,т.е Вы хотите сделать,чтобы,к примеру,молитвы,акафисты и каноны - это книги Библии. Но ведь на самом деле это не так. У молитв нет глав,хотя если делать иерархическую структуру,то это может мне очень сильно помочь - к примеру у нас есть молитвенные правила,состоящие из определённых молитв. Мы можем создать книгу с утренними молитвами с соответствующим заголовком,а потом каждая молитва будет иметь в качестве родителя id этой книги/главы. Кстати тогда нам и книги не нужны,т.е по сути и имя книги,и имя главы,и стихи главы и все молитвы с акафистами могут храниться в одной здоровенной таблице,правда я боюсь представить,какие будут тормоза при этом,хотя с индексами,наверное,всё будет не так уж и плохо.
0
923 / 296 / 58
Регистрация: 01.06.2023
Сообщений: 847
23.10.2026, 11:49
Цитата Сообщение от КАВ Посмотреть сообщение
Ну в коде можно будет сделать соответствующие константы
Можно, но это не решает вопрос читаемости именно бд.

Цитата Сообщение от КАВ Посмотреть сообщение
Ведь,к примеру,в случаи смены id каскадное обновление изменит id везде.
ID первичного ключа обычно не меняют

Цитата Сообщение от КАВ Посмотреть сообщение
Что значит в одну строку? Я ведь могу в одну строку,очень грубо говоря,поместить войну и мир,правда,боюсь,даже самый мощный сервак загнётся от чтения такой строки,не говоря о смарте,поскольку,как я понял sqlite,вроде,выделяет страницы под чтение длинных строк,и чем длиннее строка,тем медленнее она читается. Как я догадываюсь,Вы имели ввиду классическую строку длинной 256 символов,включая нулевой символ,или максимальный размер строки,для которого sqlite не нужны дополнительные страницы.
По строкой я имел ввиду одну запись в таблице. И да у Вас тоже есть записи размером около 100кб.
Цитата Сообщение от КАВ Посмотреть сообщение
Я не уверен,может ли один столбец с одной таблицы ссылаться на значение другого столбца этой же таблицы.
Может, в SQL даже есть специальный конструкции что бы обходить такие рекурсивные ссылки

Цитата Сообщение от КАВ Посмотреть сообщение
В общем идея-то хорошая,но я не очень представляю,как организовать многоуровневую иерархическую структуру в sqlite,как и в любом другом sql диалекте. Как в этом случаи будет работать индексация.
См. приведенный пример там уже все есть, не хватает только ссылок и индексов

Цитата Сообщение от КАВ Посмотреть сообщение
Кстати в этом случаи объём бд будет меньше?
Нет, объем не будет меньше (если только тексты не повторяются), так как каждая запись требует служебные структуры.

Цитата Сообщение от КАВ Посмотреть сообщение
Тогда по родительской главе,надеюсь,я смогу запросить номер этой главы,чтобы понять,к примеру,к какой главе по номеру (не по id) относится запись.
Да это решаемо запросами SQL вплоть до полного сбора пути одним запросом



Цитата Сообщение от КАВ Посмотреть сообщение
Нет,я хочу сделать так,чтобы на основе моей выборки по конкретному зачалу
Мне не хватает понимания по данному примеру, но при организации ссылок я бы отказался от идеи использования id, Хотя они и однозначно адресуют объект, но они искусственные, в то время как допустим название книги и ее главы это устоявшиеся система координат. И допустим при изменении структуры БД ссылки организованные на id придется все переписывать.

Цитата Сообщение от КАВ Посмотреть сообщение
Но ведь есть вероятность,что я захочу выбрать всех пользователей,у кого есть то или иное свойство и тогда мне придётся снова использовать перебор.
Нет классической реалицонной базой это решается дополнительной таблицей

Допустим у вас есть таблицы Церковь(id) и таблица Событие(id). Что бы в БД хранить факт имеет ли место событие, нужно добавить запись в таблицу СобытияВЦеркви(id_церкви, id_события). Есть запись - событие есть, нет записи - события нет.
Поиск осуществляется почти мгновенно так как на СобытияВЦеркви должно быть два индекса, отдельно по полю id_события и отдельно по двум полям (id_церкви, id_события). Если нужна история а были ли эти события в истории ранее. То в таблицу вводится еще два поля СобытияВЦеркви(id_церкви, id_события, период_с, период_по). Тогда что бы пометить что событие имело место только до 2026 года то ставим в период_по дату '31.12.2025' и корректируем запрос что бы учитывать эти границы.
Да по объему это больше место чем битовые маски но удобство хранения и быстрота поиска здесь все перевешивает.

Цитата Сообщение от КАВ Посмотреть сообщение
Кстати,на Ваш взгляд,есть ли вероятность,что какой-нибудь диалект sql реализует побитовый поиск,чтобы побитовые маски массово применялись? Ведь это,на мой взгляд,могло бы уменьшить размеры бд.
Не думаю что современных программистов особо беспокоит размер. Как мне тут недавно заявили "ну и что что программа занимает много места на сервере, дайте больше RAM, хочу сервер 128 гб оперативки" а это всего лишь опросник.

Цитата Сообщение от КАВ Посмотреть сообщение
Что значит частичный индекс?
Строя индекс по сути вы строите еще одну таблицу только вместо первичного ключа у нее составной ключ из столбцов входящих в индекс. Частичный индекс лишь говорит что в индекс нужно включить только номера записей исходной таблицы отвечающие условию. Экономия места, больше скорость обработки.

Цитата Сообщение от КАВ Посмотреть сообщение
Кстати индекс,наверное,нужен составной,если мы ищем по нескольким полям,к примеру используем and или or?
Да, см примеры. А or очень плохо подается индексированию и порой проще сделать несколько запросов а потом объединить результаты.

Цитата Сообщение от КАВ Посмотреть сообщение
Мне кажется,что эта бд вообще самая сложная (или одна из самых сложных бд),которые только есть,или Вам встречались бд и посложнее?
Да, и такие перечисленные приемы что рекомендую вам там лишь базовые кирпичики

Цитата Сообщение от КАВ Посмотреть сообщение
К примеру если нету текста на церковно-славянском,выбираем язык из первого столбца - русский,а если нету на русском - выбираем из второго столбца - церковнославянский. .... Просто не хочется создавать лишние записи,в которых первый и второй столбец будут просто поменяны местами.
Забудьте о хранении языков в другом столбце/таблице. См примеры. Другой перевод - это всего лишь еще одна запись в таблице texts_content с другим кодом языка. Нет перевода - нет записи.

Приоритет выбора так же смотрите в примерах.

Цитата Сообщение от КАВ Посмотреть сообщение
Но ведь для этого мы можем сделать составной первичный ключ,содержащий id_текста и id_языка,а для поиска создадим индекс,или Вы не сторонник составных первичных ключей?
На такую запись потом придется ссылаться так же с указанием на язык, что ломает все задумку об едином оглавлении(скелете) для всех переводов.

Цитата Сообщение от КАВ Посмотреть сообщение
А,т.е Вы хотите сделать,чтобы,к примеру,молитвы,акафисты и каноны - это книги Библии.
Ну да, только в моем терминологии это все книги.
Цитата Сообщение от КАВ Посмотреть сообщение
Кстати тогда нам и книги не нужны,т.е по сути и имя книги,и имя главы,и стихи главы и все молитвы с акафистами могут храниться в одной здоровенной таблице,правда я боюсь представить,какие будут тормоза при этом,хотя с индексами,наверное,всё будет не так уж и плохо.
Таблица книги как раз нужна для управляемости. И поиск не будет долгим. 30 тысяч записей в оглавлении это не объемы для современной вычислительной техники. Ставлю на то что можно уложиться по времени реакции как просто кликнуть на страничку в вебе.

Добавлено через 1 час 16 минут
По поводу объема БД, смотрите в сторону сжатия текстов. Например самая жирная таблица DESCRIPTION_HOLIDAY содержит описания размером до 60к символов. Можно применять следующие методы

- Если текст может быть представлен на в 1 байтовой кодировке, то хранить его в этой кодировке (win1251).
- Сам текст жать gzip и в бд хранить сжатым
- Если после сжатия поле по размеру больше чем исходный текст, то используем текст как есть.

сжатый текст может быть вдвое меньше исходного

вот что ии подсказала по поводу сжатия значений:

Кликните здесь для просмотра всего текста
Java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
private static final byte[] MAGIC = { 'G', 'Z', '1' };
 
public static byte[] compressWithMarker(String text) throws Exception {
    byte[] gz = TextCompression.compress(text);
    byte[] result = new byte[MAGIC.length + gz.length];
    System.arraycopy(MAGIC, 0, result, 0, MAGIC.length);
    System.arraycopy(gz, 0, result, MAGIC.length, gz.length);
    return result;
}
 
public static String decompressWithMarker(byte[] data) throws Exception {
    if (data == null) return null;
 
    if (data.length > MAGIC.length) {
        boolean marked = true;
        for (int i = 0; i < MAGIC.length; i++) {
            if (data[i] != MAGIC[i]) {
                marked = false;
                break;
            }
        }
        if (marked) {
            byte[] gz = new byte[data.length - MAGIC.length];
            System.arraycopy(data, MAGIC.length, gz, 0, gz.length);
            return TextCompression.decompress(gz);
        }
    }
 
    return new String(data, StandardCharsets.UTF_8);
}


Добавлено через 2 часа 50 минут
Кликните здесь для просмотра всего текста
Java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
import android.util.Base64;
 
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.nio.charset.StandardCharsets;
import java.util.zip.GZIPInputStream;
import java.util.zip.GZIPOutputStream;
 
public final class TextCompression {
 
    private TextCompression() {}
 
    public static byte[] compress(String text) throws Exception {
        if (text == null) return null;
 
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        try (GZIPOutputStream gzip = new GZIPOutputStream(bos)) {
            gzip.write(text.getBytes(StandardCharsets.UTF_8));
        }
        return bos.toByteArray();
    }
 
    public static String decompress(byte[] data) throws Exception {
        if (data == null) return null;
 
        try (GZIPInputStream gzip = new GZIPInputStream(new ByteArrayInputStream(data));
             ByteArrayOutputStream bos = new ByteArrayOutputStream()) {
 
            byte[] buffer = new byte[8192];
            int n;
            while ((n = gzip.read(buffer)) > 0) {
                bos.write(buffer, 0, n);
            }
            return bos.toString("UTF-8");
        }
    }
}
Java
1
2
3
4
5
6
7
public void insertNote(SQLiteDatabase db, String title, String body) throws Exception {
    ContentValues values = new ContentValues();
    values.put("title", title);
    values.put("body_gzip", TextCompression.compress(body));
 
    db.insert("notes", null, values);
}
Java
1
2
3
4
5
6
7
8
9
10
11
public String getNoteBody(SQLiteDatabase db, long id) throws Exception {
    try (Cursor cursor = db.rawQuery(
            "SELECT body_gzip FROM notes WHERE id = ?",
            new String[]{ String.valueOf(id) })) {
 
        if (!cursor.moveToFirst()) return null;
 
        byte[] compressed = cursor.getBlob(0);
        return TextCompression.decompress(compressed);
    }
}
0
17 / 5 / 0
Регистрация: 16.04.2016
Сообщений: 371
23.10.2026, 19:11  [ТС]
Можно, но это не решает вопрос читаемости именно бд.
Ну для этого,в случаи необходимости,я могу написать доку на бд,зато это уменьшит объём бд. Хотя,возможно,это позволит более чётко понять - где молитвы,где старый завет Библии,а где - новый завет Библии. А так мне придётся постоянно запоминать числа id,чтобы по ним ориентироваться. Поэтому от части Вашу логику можно понять.
ID первичного ключа обычно не меняют
Ну в целом,наверное,Вы правы,но с другой стороны мало ли что может быть.
И да у Вас тоже есть записи размером около 100кб.
Это,наверное,молитвенные правила,или жития святых. Кстати даже при такой неправильной организации бд моё приложение не тормозит,хотя по идее должно бы.
Может, в SQL даже есть специальный конструкции что бы обходить такие рекурсивные ссылки
и они есть в sqlite? Вроде нам в институте про такое не рассказывали,хотя могу и ошибаться. А без этих конструкций будет ошибка?
Нет, объем не будет меньше (если только тексты не повторяются), так как каждая запись требует служебные структуры.
Ну текст это,по сути,целочисленная переменная ссылающаяся на таблицу переводов. Если что-то повторится,мы можем сослаться на тот же id.
Да это решаемо запросами SQL вплоть до полного сбора пути одним запросом
Интересно хотя бы примерно получить этот запрос.
И допустим при изменении структуры БД ссылки организованные на id придется все переписывать.
Так разве при каскадном обновлении это не сработает. Я думаю для каждого зачала сделать таблицу с полями id_зачала,номер зачала,сокращённый текст зачала (по сути id в таблице с переводами) и id книги,а в таблице связей сделать таблицу,содержащую id зачала и id записи,по которому мы сможем определить и номер главы,и номер стиха,и текст самого стиха. Эти две колонки в таблице связей будут составным первичным ключом,поскольку почти у каждой книги своя нумерация зачал.
Нет классической реалицонной базой это решается дополнительной таблицей
Я понимаю. Я имею ввиду ту ситуацию,где побитовые маски реально оправданы.
"ну и что что программа занимает много места на сервере, дайте больше RAM, хочу сервер 128 гб оперативки" а это всего лишь опросник.
Ну тут надо смотреть конкретный случай - что за опросник,как организована структура бд и т.д. Иногда,как Вы сами отметили,лучше пожертвовать объёмом,но увеличить скорость работы с бд. Просто побитовые маски часто используются и,на мой взгляд,очень жаль,что ни один sql диалект не реализовал алгоритмы побитовый поиск. Кстати мне нейронка предлагала сделать вычисляемые индексы (пишу примерно),но она сказала,что тогда побитовые маски утратят свой смысл. Кстати,прикрепил zip файл нашего разговора с браузерной гугловской нейронкой (в архиве текстовый файл). Интересно,сможете ли Вы найти там моменты,где она врёт,поскольку она не раз меня подводила и врала.
Частичный индекс лишь говорит что в индекс нужно включить только номера записей исходной таблицы отвечающие условию.
А,т.е я могу создать индекс специально для того,чтобы выбирать те праздники,которые есть у Русской Православной Церкви. Но если меня,к примеру,заинтересует Греческая Православная Церковь.
А or очень плохо подается индексированию и порой проще сделать несколько запросов а потом объединить результаты.
Это баг sqlite,или это просто особенность реализации. Если я буду использовать or,будет происходить полное сканирование таблицы? Интересно,это где-то задокументировано,или это личный Ваш опыт?
Да, и такие перечисленные приемы что рекомендую вам там лишь базовые кирпичики
А,значит у меня не самая сложная бд? Мне интересно,как,к примеру,организовала бд телега,ютуб (да и этот форум тоже),рутуб,вконтакте и т.д. Какие там индексы и другие оптимизации? Есть ли там побитовые маски и т.д. Мне кажется что,к примеру,для сообщений там бы не помешала иерархическая структура и возможно она есть,я имею ввиду если то или иное сообщение ответ на какое-то сообщение,а ведь то сообщение может быть тоже ответом на какое-то сообщение и так до исходного сообщения. Какой там диалект - sql,или другой? А может,как я сейчас думаю,там не sql,а что-то гораздо круче,что недоступно нам,простым смертным. Кстати мы в институте изучали на последнем четвёртом курсе ndx (или mdx,не помню). Там были кубы данных. Правда я эту тему понял гораздо меньше,чем sql и по ней у нас был последний экзамен перед госэкзаменом. Интересно,используется ли где-то эта технология?
На такую запись потом придется ссылаться так же с указанием на язык
Так ведь мы и хотим выбирать запись на конкретном языке,а для id мы можем создать отдельный индекс,я имею ввиду для выборки только id. Единственное в чём,на мой взгляд,плюс - это уникальность id,т.е что нам не надо будет создавать дополнительный индекс для поиска id.
Таблица книги как раз нужна для управляемости.
Что значит для управляемости. Вы имеете ввиду для того,чтобы было легко отличить стих от книги?
30 тысяч записей в оглавлении это не объемы для современной вычислительной техники.
Я имею ввиду если в таблицу впихнуть и Библию,и молитвы,и акафисты и жития святых (хотя для жития святых я буду делать отдельную таблицу,но всё равно это,на мой взгляд,очень много). Хотя если использовать таблицу с оглавлениями,а не выбирать книги по типу с одной большой таблицы,это,может,и не так долго. Я просто волнуюсь за выборку из большой таблицы,где будет и Библия,и молитвы,и акафисты с канонами и,возможно,молитвенные правила. Боюсь,что там будет несколько сотен тысяч записей. Хотя,наверное,индексы сделают поиск мгновенным.
Если после сжатия поле по размеру больше чем исходный текст, то используем текст как есть.
Я не понял из примера,но,возможно,невнимательно посмотрел,но как мы при доставании текста из бд поймём,нуждается ли он в дикомпрессии,или нет? Или нам надо добавлять соответствующее поле в sql таблицу. Кстати переделывать бд я буду,наверное,с компа. Может есть готовые утилиты,которые сжимают текст. А вот реализацию дикомпрессии текста мне придётся писать самому,поскольку восстанавливаться он будет на смарте. Кстати Вы случаем не android разработчик? Просто Вы как раз нашли пример на java,как я и хочу. Если Вы опытный android разработчик и имели дело с ndk я задам Вам один вопрос,а если нет,то это не имеет смысла.
Вложения
Тип файла: zip sql.zip (13.3 Кб, 2 просмотров)
0
17 / 5 / 0
Регистрация: 16.04.2016
Сообщений: 371
23.10.2026, 19:14  [ТС]
Кстати забыл спросить,а sqlite не может сжимать текст,ведь,к примеру,для сжатия бд вроде существует команда vacuum.
0
923 / 296 / 58
Регистрация: 01.06.2023
Сообщений: 847
23.10.2026, 20:16
Цитата Сообщение от КАВ Посмотреть сообщение
и они есть в sqlite?
Да есть, называется рекурсивным запросом, когда часть запроса использует результаты другой части запроса. Для понимания на что может это маленькая СУБД. Есть такая игра 5-букв. где за 5 попыток нужно отгадать слово из 5 букв. Есть вот такой запрос , который гарантированно решает эту задачу за 5 попыток.

Цитата Сообщение от КАВ Посмотреть сообщение
Ну текст это,по сути,целочисленная переменная ссылающаяся на таблицу переводов. Если что-то повторится,мы можем сослаться на тот же id.
Да в этом случае будет экономия. но часто ли в книгах тексты повторяются?

Цитата Сообщение от КАВ Посмотреть сообщение
Интересно хотя бы примерно получить этот запрос.
Без заполненной бд это не много неудобно писать

Цитата Сообщение от КАВ Посмотреть сообщение
Так разве при каскадном обновлении это не сработает.
Я имел ввиду ссылки, которые у Вас раскиданы по текстам в виде обычного текста

Цитата Сообщение от КАВ Посмотреть сообщение
А,т.е я могу создать индекс специально для того,чтобы выбирать те праздники,которые есть у Русской Православной Церкви. Но если меня,к примеру,заинтересует Греческая Православная Церковь
Если это оправдано то да можно и так на практике в такой постановке он мало полезен. Посмотрите примеры там два разных варианта индексов как раз специализированный для поиска главы 1-го уровня и специализированный для поиска подглав.

Цитата Сообщение от КАВ Посмотреть сообщение
Это баг sqlite,или это просто особенность реализации.
Это ограничения логики реального мира. Представьте себе что вы архивариус у картотеки и вам поступил запрос соберите всех людей у которых фамилия начинается с "И" или 1980 года рождения. Даже если у вас есть отсортированные карточки с фамилиями и отдельные карточки по годам рождения, задача собрать такие личные дела превращается не в очень простую задачу. Обычно сводится к тому что сначала выполняется одно условие потом второе и результаты объединяются.

Цитата Сообщение от КАВ Посмотреть сообщение
а для id мы можем создать отдельный индекс,я имею ввиду для выборки только id.
На такой столбец нельзя создать ссылку, так как можно ссылаться только на уникальный ключ. Таким уникальным ключом и является первичный ключ таблицы texts.

Цитата Сообщение от КАВ Посмотреть сообщение
Что значит для управляемости. Вы имеете ввиду для того,чтобы было легко отличить стих от книги?
Что бы можно было однозначно определить что все оглавление относится к конкретной книге, просто по опыту скажу что это удобно.

Цитата Сообщение от КАВ Посмотреть сообщение
Боюсь,что там будет несколько сотен тысяч записей.
СУБД справится

Цитата Сообщение от КАВ Посмотреть сообщение
Кстати Вы случаем не android разработчик?
Нет, просто от ИИ иногда получается получить нужные ответы.

Цитата Сообщение от КАВ Посмотреть сообщение
Я не понял из примера,но,возможно,невнимательно посмотрел,но как мы при доставании текста из бд поймём,нуждается ли он в дикомпрессии,или нет?

В примере сжатый текст снабжается заголовком специальным по нему и определяется что достали из таблицы сырой текст или сжатый фрагмент. Я бы даже на это выделили не 3 байта, а достаточно 1 с значением из диапазона кодов 1-31.
- 1 - й вариант сжатый в кодировке 1251
- 2 - й сжатый в кодировке utf-8

Цитата Сообщение от КАВ Посмотреть сообщение
Кстати переделывать бд я буду,наверное,с компа.
Это правильно. При разработке я даже Вам предлагаю не заморачиваться с сжатием. А вот когда нужно будет опубликовать ее, то обрабатываете ее и сжимаете все что можете.


Цитата Сообщение от КАВ Посмотреть сообщение
,а sqlite не может сжимать текст
В стандартном варианте нет, а расширения не подключаются в java вроде. Даже нельзя подключить пользовательские функции написанные на java

Цитата Сообщение от КАВ Посмотреть сообщение
для сжатия бд вроде существует команда vacuum.
Это про другое. База сжимается за счет того убирается не используемое место, оставшееся от удаленных строк.
Если база только создана и в ней не проводились команды update/delete то vacuum не нужен. Записи при вставке укладываются плотно.
0
Невнимательный
 Аватар для ft4l
3148 / 1386 / 362
Регистрация: 08.02.2013
Сообщений: 8,199
Записей в блоге: 2
23.10.2026, 20:40
КАВ, https://sqlite.org/lang.html Там вроде есть про VACUUM , но тоже на английском,... когда уже давно искал на русском, в сети были доки только от старых версий.

есть sqlar, по сути архиватор файлов , почти как zip-архивы
/usr/share/doc/sqlite3/sqlar.html

An SQLite Archive is an ordinary SQLite database file that contains the following table as part of its schema:
Code
1
2
3
4
5
6
7
CREATE TABLE sqlar(
  name TEXT PRIMARY KEY,  -- name of the file
  mode INT,               -- access permissions
  mtime INT,              -- last modification time
  sz INT,                 -- original file size
  data BLOB               -- compressed content
);
но там даже содержимое надо доставать с чем-то типа sqlar_uncompress(data,sz)

Или есть варианты с zipfile, просто с *.zip как с таблицей работать
/usr/share/doc/sqlite3/zipfile.html
For example, to inspect the contents of zip archive "test.zip" from the current directory:
SELECT * FROM zipfile('test.zip');
или там же
Column NameContents
nameFile name/path for the zip file record.
modeUNIX mode, as returned by stat(2) for the zip file record (an integer). This identifies the type of record (file, directory or symbolic link), and the associated user/group/all permissions.
mtimeUTC timestamp, in seconds since the UNIX epoch (an integer).
szSize of associated data in bytes after it has been uncompressed (an integer).
rawdataRaw (possibly compressed) data associated with zip file entry (a blob).
dataIf the compression method for the record is either 0 or 8 (see below), then the uncompressed data associated with the zip file entry. Or, if the compression method is not 0 or 8, this column contains a NULL value.
methodThe compression method used to compress the data (an integer). The value 0 indicates that the data is stored in the zip archive without compression. 8 means the raw deflate algorithm.

Virtual Table Interface (read/write access)
...
CREATE VIRTUAL TABLE temp.zip USING zipfile('test.zip');
Such a virtual table has the same columns as the table-valued function described in the previous section.
0
923 / 296 / 58
Регистрация: 01.06.2023
Сообщений: 847
23.10.2026, 20:54
По поводу общения с ии, Вы сразу ему ограничили его в выборе перечислив варианты, да и их условия можно понять что книга будет только одна.

При рассуждении ии вообще не представляет что у вас текстов на 50 Мб и они разные, поэтому дает советы что часть таблиц не заполнит страницы БД.

Рассматривали ли вы использование модуля FTS5 (Full-Text Search)
Если боритесь за место, это Вам противопоказано такие индексы не дешевы. Так как собираются все варианты слов + список с указателями где они встретились.

С хранением текста большими кусками согласен, но если потом нужны будут фрагменты, то все плохо становится. Хотя обработать блок в 60 кб не такая уж и не посильная задача

Добавлено через 2 минуты
Цитата Сообщение от ft4l Посмотреть сообщение
Это все достигается подключением расширений. ИИ говорит что их нельзя подключить в стандартной sqlite java на андроид

Добавлено через 1 минуту
Цитата Сообщение от ft4l Посмотреть сообщение
Там вроде есть про VACUUM
Это не сжатие как ZIP, а просто оптимизация расположения записей.
1
17 / 5 / 0
Регистрация: 16.04.2016
Сообщений: 371
23.10.2026, 21:53  [ТС]
Есть такая игра 5-букв. где за 5 попыток нужно отгадать слово из 5 букв. Есть вот такой запрос
Посмотрел я запрос,правда пока не понял,как он работает и где в нём рекурсия. Может в части as (select
value, ...). Интересно,где автор раздобыл саму бд. Также мне интересно,а за сколько попыток можно будет разгадать слово из н букв? Боюсь представить,сколько этот запрос будет выполняться. Надеюсь в моём случаи запрос будет гораздо легче. Кстати если запрос использует результат второго запроса,второй запрос называется подзапрос,хотя,как я понимаю,не все подзапросы значат,что основной запрос рекурсивный.
Да в этом случае будет экономия. но часто ли в книгах тексты повторяются?
Ну если брать молитвенные правила,то есть молитвы,которые там повторяются. Но это происходит не слишком уж часто. Вообще у меня есть идея в самих текстах делать отдельные строки где между двумя символами "$" будет текст sql запроса. Программа будет получать эту строку и заменять её на результат выполнения sql запроса. В любом случаи,повторяются тексты,или нет,я буду использовать целочисленные переменные,чтобы поддерживать многоязычность.
Обычно сводится к тому что сначала выполняется одно условие потом второе и результаты объединяются.
Ну да,Вы правы. По сути нам будут нужны два индекса. А чем же лучший вариант без or с join? Кстати если взять or по одному полю,то индексирование будет работать лучше? Я знаю,где у меня,к примеру,хранятся карточки с фамилиями людей,начинающихся с буквы А,а где - с буквы ё.
На такой столбец нельзя создать ссылку
А,вот этого не знал. Тогда есть смысл с таблицами с уникальным id. Это ограничение sqlite,или любого sql диалекта? Конечно,можно обойти всё это триггерами,т.е реализовать каскадное удаление/обновление,но не знаю,насколько это лучше,к тому же при добавлении новых таблиц их нужно будет добавлять в триггер. Просто обидно,что из-за особенностей sqlite,или любых субд придётся увеличить объём бд. Кстати если не секрет,как Вы собрали мою бд? Просто в текстовом редакторе объединили все sql файлы в один,или написали скрипт,который делает это,или попросили об этом нейронку? Кстати Вы написали,в какой таблице у меня самые длинные записи. Интересно,можно ли sql запросом получить имя таблицы и длину самой длинной записи,или надо делать выборку по каждой таблице с текстовыми полями?
а расширения не подключаются в java вроде.
Ну я думаю реализовать это на языке c/c++,я имею ввиду работу с бд,а потом дёргать нативные методы в java коде,поскольку у меня есть идея сделать кроссплатформенное приложение. Ещё изучу,что мне тут подсказали по сжатию бд. Интересно,если я всё-таки буду реализовывать дикомпрессию на java,есть ли программа,которая сжимает бд по тому алгоритму,который подсказала нейронка. Кстати спасибо Вам и за идею по сжатию текста. Просто я не думал,что таким образом я смогу сильно урезать бд. Я думаю для простоты весь текст хранить в стандартной кодировке utf-8.
0
923 / 296 / 58
Регистрация: 01.06.2023
Сообщений: 847
23.10.2026, 22:26
Цитата Сообщение от КАВ Посмотреть сообщение
Интересно,где автор раздобыл саму бд.
За основу взял словарь русских букв. Да, рекурсии тут нет.

Цитата Сообщение от КАВ Посмотреть сообщение
Боюсь представить,сколько этот запрос будет выполняться.
несколько миллисекунд, база на самом деле маленькая и быстро проходится даже простым перебором

Цитата Сообщение от КАВ Посмотреть сообщение
Вообще у меня есть идея в самих текстах делать отдельные строки где между двумя символами "$" будет текст sql запроса.
Не самый лучший вариант. довольно много лишних символов остается. посмотрите первое сообщение мое про макровыражения

Цитата Сообщение от КАВ Посмотреть сообщение
А чем же лучший вариант без or с join?
В том что субд не всегда может понять что можно взять данные из одного индекс и дополнить из другого. в этом случае задача решается лоб. Идем в архив и просматриваем все личные дела и проверяем условие в каждом. Два отдельных запроса не оставляют СУБД выбор.

Цитата Сообщение от КАВ Посмотреть сообщение
Это ограничение sqlite,или любого sql диалекта?
Без первичного ключа "жизнь есть", просто сложнее и не работают связи. На распространяемой базе, так как он статична можно обойтись без этих связей и сэкономить на таблице texts(id_text) но вот на базе которую Вы будет наполнять/редактировать желательно оставить.

Цитата Сообщение от КАВ Посмотреть сообщение
Кстати если не секрет,как Вы собрали мою бд?
Распаковал архивы а дальше через консоль исполнил каждый файл за файлом. Инструменты есть на официальном сайте (Command-line tools for Windows).

Цитата Сообщение от КАВ Посмотреть сообщение
Интересно,можно ли sql запросом получить имя таблицы и длину самой длинной записи,или надо делать выборку по каждой таблице с текстовыми полями?
Имя самой большой таблицы можно получить так же с помощью tools. Размер самого большого фрагмента нашел уже в конкретной таблице запросом.

Цитата Сообщение от КАВ Посмотреть сообщение
Я думаю для простоты весь текст хранить в стандартной кодировке utf-8.
На самое рациональное решение. Если для cs еще оправдано, его кодовая база не помещается в стандартную однобайтовую кодовую таблицу (хотя тут тоже могут быть варианты), то вот современная кириллица хорошо помещается, и можно использовать стандартную кодировку win-1251. В UTF-8 кириллица гарантированно будет занимать два байта на символ.
0
17 / 5 / 0
Регистрация: 16.04.2016
Сообщений: 371
23.10.2026, 23:09  [ТС]
Да, рекурсии тут нет.
Хорошо,а какой запрос может быть рекурсивным для решения моей задачи,хотя бы примерно?
посмотрите первое сообщение мое про макровыражения
А,ну по сути их тоже можно использовать и формировать из них sql запрос. Просто если использовать sql запрос напрямую,можно будет делать сложнейшие выборки,соединения,отступы где надо и т.д. А изобретать свою систему макросов сложнее.
можно обойтись без этих связей
Но тогда придётся самостоятельно реализовывать каскадное удаление/обновление. Кстати тогда и индекс по id тоже придётся делать,а это,вроде,тоже занимает место,т.е по сути я меняю мыло на шило (или наоборот).
Размер самого большого фрагмента нашел уже в конкретной таблице запросом.
Т.е получение самой большой таблицы не делается через запрос,а через командную строку? Интересно,какой фрагмент у меня самый большой и в какой таблице?
В UTF-8 кириллица гарантированно будет занимать два байта на символ.
А какие символы уже не подходят для win1251? Вообще в java вроде всё хранится в utf-8 (по крайней мере в android). Конечно,всё это можно легко переписать,но не знаю,насколько это оправдано,особенно если я ещё всё это буду сжимать. К тому же я,возможно,буду добавлять другие языки и иметь проблемы с кодировкой я не хочу.
0
923 / 296 / 58
Регистрация: 01.06.2023
Сообщений: 847
23.10.2026, 23:59
Цитата Сообщение от КАВ Посмотреть сообщение
Хорошо,а какой запрос может быть рекурсивным для решения моей задачи,хотя бы примерно?
Вот так зная id оглавления для фрагмента книги можно получить список оглавлений одним запросом.

Кликните здесь для просмотра всего текста
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
WITH RECURSIVE part_path AS (
    -- Стартовая часть: лист дерева
    SELECT
        p.id_book_part,
        p.id_book_part_parent,
        p.id_text_title,
        0 AS depth
    FROM book_part p
    WHERE p.id_book_part = :id_book_part
 
    UNION ALL
 
    -- Подъём к родительской части
    SELECT
        parent.id_book_part,
        parent.id_book_part_parent,
        parent.id_text_title,
        child.depth + 1
    FROM part_path child
    JOIN book_part parent
      ON parent.id_book_part = child.id_book_part_parent
)
SELECT
    GROUP_CONCAT(t.text_value, ' / ') AS chapter_titles
FROM (
    SELECT
        pp.depth,
        tc.text_value
    FROM part_path pp
    JOIN texts_content tc
      ON tc.id_text = pp.id_text_title
    WHERE pp.id_text_title IS NOT NULL AND tc.id_lang = (SELECT id_lang FROM lang WHERE code = 'ru')
    ORDER BY pp.depth DESC
) t;


Цитата Сообщение от КАВ Посмотреть сообщение
А,ну по сути их тоже можно использовать и формировать из них sql запрос.
Да, можно

Цитата Сообщение от КАВ Посмотреть сообщение
А изобретать свою систему макросов сложнее.
ИИ в данном вопросе справляется хорошо, вы не первый кто решает такую задачу

Цитата Сообщение от КАВ Посмотреть сообщение
Но тогда придётся самостоятельно реализовывать каскадное удаление/обновление.
А зачем Вам в продуктовой базе поддержка удалений, кто то на телефоне будет править БД?

Цитата Сообщение от КАВ Посмотреть сообщение
Кстати тогда и индекс по id тоже придётся делать,а это,вроде,тоже занимает место,т.е по сути я меняю мыло на шило (или наоборот).
Ну вроде как есть варианты без первичного ключа если не ошибаюсь но нужно пробовать, мне просто мои устои не позволяют держать таблицу без первичного ключа.

Цитата Сообщение от КАВ Посмотреть сообщение
Интересно,какой фрагмент у меня самый большой и в какой таблице?
SQL
1
2
3
4
5
6
7
Исследуйте 
 
SELECT *, LENGTH(holiday_description) FROM DESCRIPTION_HOLIDAY ORDER BY LENGTH(holiday_description) DESC;
 
SELECT *, LENGTH(text_prayers) FROM PRAYERS_RU_AK ORDER BY LENGTH(text_prayers) DESC;
 
SELECT *, LENGTH(chapter_text) FROM BIBLE_BOOK ORDER BY LENGTH(chapter_text) DESC;
Цитата Сообщение от КАВ Посмотреть сообщение
К тому же я,возможно,буду добавлять другие языки и иметь проблемы с кодировкой я не хочу.
Тогда не заморачивайтесь с кодовыми таблицами.
0
17 / 5 / 0
Регистрация: 16.04.2016
Сообщений: 371
24.10.2026, 02:14  [ТС]
FROM part_path child
Интересный синтаксис,как и FROM book_part p. Как я понял,part_path это временная таблица,поскольку уже почитал немного про рекурсивные запросы. Я читал,что в некоторых sql диалектах можно не использовать ключевое слово recursive,в других же диалектах,таких как sqlite,будет выдана ошибка,если не использовать это ключевое слово. Также мне непонятно,откуда у нас взялось p,parent и child. Как я понял,мы получали список оглавлений от ребёнка к родителю. А если мы захотим решить обратную задачу - получить всех детей конкретной главы,т.е все подглавы и ниже,нам тоже нужен будет рекурсивный запрос. Тогда,наверное,в бд нам нужно будет поле child,чтобы легко можно было понять что,к примеру,в первом послании Апостола Павла,которое относится к книге Библия (новый завет) содержится столько -то глав,в каждой из которой содержится столько-то стихов. Кстати я подумал,что при иерархической структуре данных нам можно просто создать,к примеру,книгу Библия,а уже книги Библии,главы этих книг и сами стихи можно представить как иерархическая структура. Такая же ситуация может быть и для молитвослова и т.д. По сути Вы это же и предлагаете.
А зачем Вам в продуктовой базе поддержка удалений, кто то на телефоне будет править БД?
Ну из пользователей никто,а вот мы вдвоём будем править,поэтому нехотелось бы,чтобы из-за нашей невнимательности остались лишние записи,которые ведут на не существующие значения. Кстати я тут нагуглил,что при создании ссылки на столбец он должен быть уникальным,даже если он не первичный ключ.
Тогда не заморачивайтесь с кодовыми таблицами.
Я не совсем понял Вас,что Вы имеете ввиду.
Кстати нужно ли нам поле title,если мы можем иметь только поле для текста при такой структуре. Ещё вспомнил пример,где у меня тоже может стоять вопрос о дублировании числовых столбцов. У меня есть задумка (не зна.ю,реализую ли я её,или нет) сделать бд для одного храма. Так вот в одной из таблиц у меня будет три целочисленных поля - тип службы (Литургия/молебен и т.д),священник,который совершает эту службу и время в миллисекундах,чтобы знать,когда и во сколько будет служба. Одну службу могут совершать несколько священников,если храм большой. Так вот,не лучше ли разбить эту таблицу на две - тип службы,священник,id_записи и на вторую,где будет столбец,ссылающийся на id_записи из первой таблицы и время в миллисекундах,чтобы получить время и дату для совершения тойй или иной службы. Я думаю,что расписание будет обновляться каждые две недели. Понимаю,что это очень далёкое будущее и даже не факт,что я буду этим заниматься,но на всякий случай спрошу Вашего совета - как правильно поступить в этом случаи и почему. Кстати я вообще раньше думал тип службы и id священника сделать такими полями,чтобы можно было использовать побитовые маски,т.е тогда бы объём бд уменьшился,но судя по Вашим советам этого не стоит делать,хотя и бд уже будет храниться на сервере,а значит все вычисления будут гораздо быстрее. Как я ранее писал,это вопрос на перспективу,чтобы если я вдруг буду этим заниматься,не задавать этот вопрос. Кстати иерархическая структура бд,возможно,поможет мне решить ещё одну задачу,если я когда-то буду её решать.
0
923 / 296 / 58
Регистрация: 01.06.2023
Сообщений: 847
24.10.2026, 02:18
Посчитал тут сколько будет экономии если заменить unicode на win1251 кодировку только на таблице DESCRIPTION_HOLIDAY и вышло около 11 мб. ни какая оптимизация индексов Вам не даст такой экономии

Кликните здесь для просмотра всего текста
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
WITH RECURSIVE cte (letter, txt, cnt) AS (
    SELECT 
      SUBSTR(holiday_description, 1, 1), 
      REPLACE(holiday_description || name || name_title || link_source,SUBSTR(holiday_description, 1, 1),''), 
      LENGTH(holiday_description || name || name_title || link_source) - LENGTH(REPLACE(holiday_description || name || name_title || link_source,SUBSTR(holiday_description, 1, 1),'')) 
    FROM DESCRIPTION_HOLIDAY
    
    UNION ALL
    
    SELECT SUBSTR(txt, 1, 1), REPLACE(txt,SUBSTR(txt, 1, 1),''), LENGTH(txt) - LENGTH(REPLACE(txt,SUBSTR(txt, 1, 1),''))
    FROM cte WHERE LENGTH(txt) > 0 
) --select *from cte
SELECT SUM(summa), COUNT(*) FROM (
  SELECT letter, unicode(letter), SUM(cnt) summa
  FROM cte
  WHERE 
    unicode(letter) > 255
  GROUP BY 
   letter 
  HAVING 
    SUM(cnt) > 1024
  ORDER BY 3 DESC) t


Сжатие самой длинной строки utf8 дает степень сжатия около 28%. Сжатие в кодировке 1251 даст степень сжатия 24% (от строки utf8). т.е. с сжатием эффект от замены кодировки уже и не так выгодно выглядит
0
923 / 296 / 58
Регистрация: 01.06.2023
Сообщений: 847
24.10.2026, 09:29
Цитата Сообщение от КАВ Посмотреть сообщение
Тогда,наверное,в бд нам нужно будет поле child,
нет, достаточно все тех же полей. заводить еще одно поле не нужно

Цитата Сообщение от КАВ Посмотреть сообщение
Ну из пользователей никто,а вот мы вдвоём будем править,поэтому нехотелось бы,чтобы из-за нашей невнимательности остались лишние записи
Все верно, поэтому и предлагаю ведите две БД, одна полная (все доп таблица для ссылочной целостности, строки без сжатия и прочих уловок оптимизации) для редактирования, вторая урезанная только для использования в приложении в режиме чтения.

Цитата Сообщение от КАВ Посмотреть сообщение
Кстати нужно ли нам поле title,если мы можем иметь только поле для текста при такой структуре.
Это как бы разные понятия, я бы не объединял. Если поле не заполнено (null), оно на диске занимает только 1 байт.

Цитата Сообщение от КАВ Посмотреть сообщение
Так вот в одной из таблиц у меня будет три целочисленных поля - тип службы (Литургия/молебен и т.д),священник,который совершает эту службу и время в миллисекундах,чтобы знать,когда и во сколько будет служба. Одну службу могут совершать несколько священников,если храм большой. Так вот,не лучше ли разбить эту таблицу на две - тип службы,священник,id_записи и на вторую,где будет столбец,ссылающийся на id_записи из первой таблицы и время в миллисекундах,чтобы получить время и дату для совершения тойй или иной службы
Да, все верно напрашивается 2 таблицы, при этом если священник один, то его можно указать сразу в таблице служб. Если их несколько то заводить отдельно в расширенный состав. Поиск по расширенному составу конечно будет страдать, зато компактно.

Служба(id_службы (PK), id_Типа_службы, id_церкви, Id_священника, дата_начала)
РасширенныйСоставСлужбы(id_службы, Id_священника)

+ справочные таблицы
Церковь(id_церкви, Название, Местоположение, ...)
Священник(Id_священника, Фамилия, Имя, Отчество, Сан, ...)
Тип_службы(id_Типа_службы, Заголовок, ...)

Цитата Сообщение от КАВ Посмотреть сообщение
Кстати я вообще раньше думал тип службы и id священника сделать такими полями,чтобы можно было использовать побитовые маски,т.е тогда бы объём бд уменьшился
если у конкретной службы может быть одновременно несколько типов службы одновременно и типов служб меньше 8 то битовое поле здесь может пригодится, но Вы теряет возможность индексного поиска по типам служб. Но не вижу практического смысла в этом.

то же самое с священниками упаковывая их в одно поле теряете возможность быстрого поиска "а в каких службах участвует священник?". С битовыми полями еще сложность возникает с тем что если людей будет много то структура битового поля будет очень разреженной, теряя место на сброшенных флагах. так к примеру при наличии 128 священников нужно хранить в базе 16 байт ради указания последнего священника из списка. Вместо 1 байта с использованием ID.

Добавлено через 9 минут
а еще хранение в битовой структуре указателей (когда номер бита это id в другой таблице) очень не удобно когда нужно получить информацию об этом бите сразу одним запросом. Например одним запросом найди все службы в церкви в эту пятницу и верни список ФИО всех священников.

Классическими указателями это стоит всего -соединись с таблицей по ID. С битовыми полями придется сначала получить маску, в основной программе извлечь позиции всех единичных битов в виде массива, а заетем по массиву найти фио участников
0
17 / 5 / 0
Регистрация: 16.04.2016
Сообщений: 371
24.10.2026, 14:30  [ТС]
т.е. с сжатием эффект от замены кодировки уже и не так выгодно выглядит
Но если в начале заменить кодировку на win1251,а потом ещё и сжать,то,как я понимаю,экономия будет заметная. Просто я вижу в списке ошибок от пользователей,что не всем хватает места на такую бд (в основном exception,вроде,возникает на дешёвых китайских смартах,таких как дуги и т.д). Но макросы,сжатия,смену кодировки и т.д я,наверное,оставлю на десерт. Может я буду это делать поэтапно. Может чего-то вообще не будет. Кстати а в каких случаях мне может понадобится utf-8? Спасибо за пример запроса,который перекодирует всю строку. Кстати как Вы вычислили процент сжатия,а точнее чем Вы сжимали - Вы использовали код на java,который Вы мне предложили,или рекурсивный запрос не только перекодирует,но и сжимает данные?
Все верно, поэтому и предлагаю ведите две БД, одна полная (все доп таблица для ссылочной целостности, строки без сжатия и прочих уловок оптимизации) для редактирования, вторая урезанная только для использования в приложении в режиме чтения.
В принципе,наверное,Вы правы. Сначало правим основную бд,потом делаем её копию и удаляем ссылочную таблицу и всякие reference. Главное потом всё правильно перенести из одной бд в другую и это меня больше всего беспокоит. Хотя,если честно,я в дальнейшем планирую написать специальное приложение для этих целей,поскольку человек,который делал приложение до меня,вообще почти не разбирается в программировании и я добровольно вызвался ему помочь.
Это как бы разные понятия, я бы не объединял.
Ну формально да,но по сути-то если у нас заголовок null,то стих не null и наоборот,поэтому,наверное,можно и объединить,или я не прав? Правда в этом случаи нам будет сложнее понять,что мы спустились на самый нижний уровень дерева,или это будет не так сложно?
Если их несколько то заводить отдельно в расширенный состав.
Ну вообще предполагается,что когда-то их будет несколько. Я вообще стараюсь делать что-то с перспективой на будущее,чтобы когда меня попросят что-то добавить я не кусал бы губы думая,что,мол,блин,надо переделывать код,я этого не предусмотрел,или как выкрутиться при неправильно организованной структуре,чтобы не переписывать ничего заново. Кстати исходя из этого мы снова возвращаемся к вопросу - когда нужно делить таблицу на две,а когда - нет. Если дублируются строковые данные,то конечно лучше повторять id,а не строки. А вот как быть с числовыми данными,а особенно с таблицами связей,ведь в моём примере со службами Вы определили,что лучше разбить таблицу на две. Хотя тут это будет выглядить логичнее,т.е разбиение на две таблицы,поскольку,к примеру,если даже хранить расписание служб на месяц,экономия бд будет существенная.
если у конкретной службы может быть одновременно несколько типов службы одновременно и типов служб меньше 8
А в postgrsql у нас максимально можно поместить 128 флагов? Просто в sqlite,вроде,это число равно 64. Я хочу использовать побитовые маски,поскольку может быть так,что несколько священников могут совершать службу и/или совершать таинство,к примеру исповедовать,причащать и т.д. Как и один священник,особенно если храм маленький,может совершать и исповедь,и Причастие после службы. Но я всё-таки,наверное,откажусь от идеи с побитовыми масками если буду делать такую бд. Пока что ближайшее будущее это переписать наше приложение,а более ближайшее - переделать бд для нашего приложение. Это я просто консультируюсь,чтобы если я буду когда-то решать эту задачу,я зашёл бы в эту тему и прочитал бы Ваши советы. Кстати я ещё не упомянул,что в таблице со служителями будет и тип служителя - дьякон,иерей (это уже священник),протоиерей и т.д,как и для служба и таинство будут в одной таблице,но это не важно для этого вопроса - важно стоит ли разбивать таблицы связей,и я понял,что стоит.
С битовыми полями еще сложность возникает с тем что если людей будет много то структура битового поля будет очень разреженной, теряя место на сброшенных флагах. так к примеру при наличии 128 священников нужно хранить в базе 16 байт ради указания последнего священника
А,получается sql диалекты смотрят на самую длинную запись в таблице и для каждой записи отводят максимальное число байт для каждой записи? По поводу количества священников,боюсь что для одного храма даже 4-5 священников это много,хотя,конечно,в монастырях,к примеру в нашем Свято-Елизаветенском их где-то 20 и ещё два или три дьякона,т.е если бы я делал бд для монастыря,то в ней было бы примерно 22-23 записи. Но это скорее исключение,чем правило.
найди все службы в церкви в эту пятницу и верни список ФИО всех священников.
Так в этом,как раз таки,нет проблемы. Сложнее перевести ближайшую пятницу в мс (время служб,как я ранее писал,я хочу сделать в миллисекундах) и от этого диапазона плясать. По поводу ФИО я тоже об этом думал,т.е что побитовое разбиение придётся или делать программно,или что-то придумывать в sql (второе,конечно,лично для меня гораздо сложнее,хоть и интереснее). Просто,к примеру,в android разработке рекомендуют с целью улучшения производительности всё,что можно,делать в sql,поскольку,как я понимаю,тратится время на переход из jvm в нативную часть (sqlite написан на c). Да и в веб разработке,думаю,этот подход тоже гораздо лучше.
0
923 / 296 / 58
Регистрация: 01.06.2023
Сообщений: 847
25.10.2026, 22:25
Цитата Сообщение от КАВ Посмотреть сообщение
Но если в начале заменить кодировку на win1251,а потом ещё и сжать,то,как я понимаю,экономия будет заметная.
Цитата Сообщение от КАВ Посмотреть сообщение
Кстати как Вы вычислили процент сжатия,а точнее чем Вы сжимали
Проценты сжатия я привел выше. Перегон в кодировку 1251 с последующим сжатием дает выигрыш всего в пару процентов, уже не уверен что есть смысл этим заморачиваться усложняя код основного приложения.

По сжатию просто взял самый длинный текст поместил в файл и сжал архиватором в gzip


Цитата Сообщение от КАВ Посмотреть сообщение
Кстати а в каких случаях мне может понадобится utf-8?
Для расширенного алфавита старославянского

Цитата Сообщение от КАВ Посмотреть сообщение
рекурсивный запрос не только перекодирует,но и сжимает данные
ни в коем разе, без расширений sqlite не умеет сжимать данные

Цитата Сообщение от КАВ Посмотреть сообщение
Кстати исходя из этого мы снова возвращаемся к вопросу - когда нужно делить таблицу на две,а когда - нет.
Почитайте все таки еще раз про нормализацию БД

Цитата Сообщение от КАВ Посмотреть сообщение
Правда в этом случаи нам будет сложнее понять,что мы спустились на самый нижний уровень дерева,или это будет не так сложно?
Не сложно, запросом проверяете есть ли дочерние записи

Цитата Сообщение от КАВ Посмотреть сообщение
А в postgrsql у нас максимально можно поместить 128 флагов? Просто в sqlite,вроде,это число равно 64. Я хочу использовать побитовые маски,поскольку может быть так,что несколько священников могут совершать службу и/или совершать таинство,к примеру исповедовать,причащать и т.д.
Давайте так, логическое поле не важно где оно как бит в поле или как самостоятельное поле должно на вопрос давать только ответ "да" "нет" (Имеет право на запись - Да/ чтение - Нет / Действующий документ - Да
), не подразумевая еще какой то смысл . Хранить логическими полями ссылки на другие сущности по моему субъективному видению мира на столько же противоестественно как любовные отношения между мужчинами.

Цитата Сообщение от КАВ Посмотреть сообщение
А,получается sql диалекты смотрят на самую длинную запись в таблице и для каждой записи отводят максимальное число байт для каждой записи?
Конкретно в sqlite нет, используется динамическая длина, размер хранится в заголовке записи.

Цитата Сообщение от КАВ Посмотреть сообщение
Сложнее перевести ближайшую пятницу в мс (время служб,как я ранее писал,я хочу сделать в миллисекундах)
Вообще не вижу проблем. Не думаю что в Java нет функции по округлению текущей даты до первого дня недели. Дальше просто прибавляете к этой дате нужно число дней. Из полученной даты извлекаете целое число. Правда вот вы топите за экономию но тут же на ровном месте добавляет 2 байта к дате события. Можно же не в мс хранить.
0
17 / 5 / 0
Регистрация: 16.04.2016
Сообщений: 371
26.10.2026, 06:15  [ТС]
Перегон в кодировку 1251 с последующим сжатием дает выигрыш всего в пару процентов
Ну это выигрыш для одной таблицы. Получается больше всего сэкономил именно перегон в другую кодировку.
Для расширенного алфавита старославянского
А я не могу для одних языков хранить текст в одной кодировке,а для других - в другой?
Почитайте все таки еще раз про нормализацию БД
Посмотрел. Мне кажется что,к примеру,в бд для храма используются нормальные формы выше,чем 3. Кстати в переделанной бд для нашего приложения,наверное,тоже,но тут я предполагаю,поскольку не всматривался слишком детально,хотя и буду.
Не сложно, запросом проверяете есть ли дочерние записи
Понятно. Получается,нам надо выбрать такие id,которые не являются родителем никому? Если выводить все дочерние уровни - от родителя к ребёнку,тоже нужен будет рекурсивный запрос?
Конкретно в sqlite нет, используется динамическая длина, размер хранится в заголовке записи.
Тогда в чём же будет проблема,т.е почему будет разреженность? Вообще это будет использоваться для бэкенда,если я буду делать бд,поэтому,скорее всего,будет использоваться postagresql и,к примеру,python.
Можно же не в мс хранить.
Ну тогда нужны будут поля число,месяц,год,час и минута - пять полей. Тогда для часа и минуты нужна будет дополнительная таблица,поскольку службы проходят в основном в одно и то же время в зависимости от дня. Или Вы предлагаете дату и время запихнуть в одну строку?

Кстати хотел спросить - как Вы думаете,к примеру для таблицы с Библией нейронка справится,чтобы её переделать. Просто думаю,как автоматизировать этот процесс. Поскольку у нас несколько стихов в одной строке,думаю найти готовую бд с Библией,где всё разбито по стихам.
Просто вручную заполнять всё это как-то не улыбается,поэтому я думаю поручить часть вещей нейронке и,возможно,наваять какой-нибудь скрипт на python для этого.
0
Надоела реклама? Зарегистрируйтесь и она исчезнет полностью.
BasicMan
Эксперт
29316 / 5623 / 2384
Регистрация: 17.02.2009
Сообщений: 30,364
Блог
26.10.2026, 06:15

Большая таблица, надо ли её разбивать?
Делаю БД для учета заказов Издательства и дальнейшему распределению их на производстве. ...

Как вести в базе данных логи. Кто вошел? Когда вошел? Когда вышел?
Привет, народ! Пожалуйста подскажите как вести таблицу в БД с логами о пользователях вошедших в...

для каждой записи с таблицы 1,поле ФИО, выделит отдельно фамилию,имя,отчестово и запишет их в соответсвтующие поля таблицы 2.
Написать программу,которая: для каждой записи с таблицы 1,поле ФИО, выделит отдельно...

Какой запрос лучше и почему лучше?
Не знаю какой запрос лучше. Вопросы: 1. Рационально ли использовать 1 вариант? Если второй...

Создать маску ввода для поля таблицы
Здравствуйте, уважаемые форумчане! Вопрос в том, как через SQL добавить полю таблицы типа VARCHAR2...


Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:
40
Ответ Создать тему
Новые блоги и статьи
Просмотровщик HLP и конвертер в CHM
Argus19 16.11.2026
Просмотровщик HLP и конвертер в CHM Давно хотел переделать справку Microsoft® Win32® Programmer's Reference, чтобы иметь её под рукой. Но использовать несколько программ не удобно. Искал другие. . .
BitTorrent (версия 7.11 English) и Меню Настройки
russiannick 15.11.2026
Пункты, отвечающие за отключение обновлений в BitTorrent версии 7. 11 (английской) Options Preferences General Language
Транслитерация текста с кириллицы на латиницу (вариант №4)
Maks 15.11.2026
Что-то я перемудрил с предыдущими версиями, в этот раз сделал всё проще, обратившись к функции "СтрокаЛатиницей" из типового общего модуля "СтроковыеФункцииКлиент". В данной функции нет апострофов,. . .
BitTorrent (версия 4.3.9) и Меню Настройки
russiannick 15.11.2026
В меню Настройки можно отключить автоматическое обновление программы. Карта этого меню приложена ниже. Нужный пункт выделен красным. Сервис Настройки Поведение
Транслитерация текста с кириллицы на латиницу (вариант №3)
Maks 15.11.2026
Обновленная версия предыдущей обработки. Изменения коснулись букв: Ъ, Ы, Ь, Э, отображения которых зависят от положения реквизита "Транслитерация имён". Изменено отображение буквы Й. Добавлена. . .
Транслитерация текста с кириллицы на латиницу (вариант №2)
Maks 13.11.2026
Обновленная версия ранее созданной обработки. Добавлено: - Проверка исходного текста на кириллицу, через обращение к стандартной функции "ТолькоКириллицаВСтроке" из общего модуля. . .
Часы с термометром и праздничным календарем
yariko 12.11.2026
Сегодня мы рассмотрим как можно сделать отличные многофункциональные часы. Итак, поставим конкретную задачу: разработать часы с календарем, двумя термодатчиками, выводом поздравительных сообщений. . .
Транслитерация текста с кириллицы на латиницу (вариант №1)
Maks 11.11.2026
Решил облегчить жизнь себе и коллегам админам при создании электронных почтовых ящиков для пользователей, в результате написал обработку "Транслитерация текста" с кириллицы на латиницу, а в ней. . .
КиберФорум - форум программистов, компьютерный форум, программирование
Powered by vBulletin
Copyright ©2000 - 2026, CyberForum.ru