Draft tables that will allow us to query a presentation id and get all the timestamped entries for that presentation

[zipperer]

Want to ask for presentation id 3192 and get

presentation_id | entry_timestamp     | book        | chapter | verse
3192            | 2014-01-05 00:01:49 | Romans      | 8       | 29
...
3192            | 2014-01-05 01:31:00 | 2Corinthians| 10      | 16

[zipperer]

Copy as of 20240124_12_54 20240124 Table Structure Proposal.pdf

Google Drive source 20240124 Table Structure Proposal

note: will soon change privileges on file to share

[zipperer]

-- Table from "Title Text File" – the list of presentation numbers and titles
-- tablename: presentation'
id   | date_encoded | day    | morning_or_night | presentation_date | title
3192 | SM010514     | Sunday | morning          | 2014-01-05        | Predestination: Increase …
3913 | SN010514     | Sunday | night            | 2014-01-05        | Doctrine of the Devil: ...

-- Table from directory of files ‘Bible Display App CSV txt files’ after split days with two sessions into two files.
-- tablename: file_for_timestamp_entries_book_chapter_verse
id  | filename                  | file_date  | morning_or_night
330 | BDA 2014 1 05_morning.txt | 2014-01-05 | morning
331 | BDA 2014 1 05_night.txt   | 2014-01-05 | night

-- Table from content of files in directory of files ‘Bible Display App CSV txt files’ after 
--  (a) split days with two sessions into two files and 
--  (b) convert to csv.
-- tablename: timestamped_entry_book_chapter_verse

filename                  | filename_date | morning_or_night | entry_timestamp     | book     | chapter | verse
BDA 2014 1 05_morning.txt | 2014-01-05    | morning          | 2014-01-05 00:01:49 | Romans   | 8       | 29
BDA 2014 1 05_night.txt   | 2014-01-05    | night            | 2014-01-05 00:04:45 | 1Timothy | 4       | 1

-- This query gives way to get all timestamped entries for a given presentation id

SELECT presentation.id, entry.entry_timestamp, entry.book, entry.chapter, entry.verse,
FROM timestamped_entry_book_chapter_verse as entry 
JOIN presentation ON entry.filename_date = presentation.presentation_date
WHERE entry.morning_or_night = presentation.morning_or_night AND
      presentation.id = 3192
ORDER BY entry.entry_timestamp

This version duplicates information across two tables:

• file_for_timestamp_entries_book_chapter_verse
• timestamped_entry_book_chapter_verse

Could either:

• get rid of table file_for_timestamp_entries_book_chapter_verse
• use id from file_for_timestamp_entries_book_chapter_verse in timestamped_entry_book_chapter_verse

If we use id from file_for_timestamp_entries_book_chapter_verse in timestamped_entry_book_chapter_verse, then we will need to populate the table with a script that:

• looks up id in file_for_timestamp_entries_book_chapter_verse
• provides that id in (a) an INSERT from the script or (b) output csv

i.e. script will need to connect to db, e.g. by python and psycopg2

[zipperer]

Could either:

• get rid of table file_for_timestamp_entries_book_chapter_verse
• use id from file_for_timestamp_entries_book_chapter_verse in timestamped_entry_book_chapter_verse

First option is simpler. Second option saves space since it reduces how often db stores filenames.

I plan to do first option first in the spirit of 'make it more complex only when you need to'/'delay optimization'.

[zipperer]

-- Table from "Title Text File" – the list of presentation numbers and titles
-- tablename: presentation'
id   | date_encoded | day    | morning_or_night | presentation_date | title
3192 | SM010514     | Sunday | morning          | 2014-01-05        | Predestination: Increase …
3913 | SN010514     | Sunday | night            | 2014-01-05        | Doctrine of the Devil: ...

39 provides a table like this

[zipperer]

-- Table from content of files in directory of files ‘Bible Display App CSV txt files’ after 
--  (a) split days with two sessions into two files and 
--  (b) convert to csv.
-- tablename: timestamped_entry_book_chapter_verse

filename                  | filename_date | morning_or_night | entry_timestamp     | book     | chapter | verse
BDA 2014 1 05_morning.txt | 2014-01-05    | morning          | 2014-01-05 00:01:49 | Romans   | 8       | 29
BDA 2014 1 05_night.txt   | 2014-01-05    | night            | 2014-01-05 00:04:45 | 1Timothy | 4       | 1

To get a table like this I propose:

• start with directory of files 'BDA YYYY MM DD.txt'
• make new directory of files that can have form
  • 'BDA YYYY MM DD.txt' or
  • 'BDA YYYY MM DD_morning.txt' or
  • 'BDA YYYY MM DD_night.txt'
  • where file has the first form when the entries in the file start from 12am and only go up in time and
  • file has second form or third form when the entries in the file start from 12am and go up and then go back down afterward
  • second form for the entries from 12am to up before go back down
  • third form for entries from 12am to up after first batch of entries
  • from new directory of files make third directory of files that can have form
  • 'BDA YYYY MM DD.csv' or
  • 'BDA YYYY MM DD_morning.csv' or
  • 'BDA YYYY MM DD_night.csv'
  • make script that iterates through this directory of csvs and uses psycopg2 to copy content from file into the target table

[zipperer]

A future issue will implement this table and the process to populate it.