Meeting 20240125 -- share version 0 of the database to link message numbers with <timestamp, book, chapter, verse> entries

[zipperer]

We got running on Michael's Ubuntu machine the database that allows us to query "Show me the <timestamp, book, chapter, verse> entries for message number N.

[zipperer]

On my machine

$ docker commit --author andrew --message 'store current version of bible display app database' az_postgres_ubuntu1 bible_display_app:v0
$ docker save --output /Users/andrew/grace_and_truth_ministries/bible_display_app_db.tar bible_display_app:v0
$ ls -ltrh /Users/andrew/grace_and_truth_ministries/bible_display_app_db.tar
-rw-------  1 andrew  staff   838M Jan 25 15:04 /Users/andrew/grace_and_truth_ministries/bible_display_app_db.tar
[andrew@ayer bible_verse_timestamp_files (main % u=)]$

Then put bible_display_app_db.tar on Dropbox. Michael downloaded the file from Dropbox. On Michael's machine

# move file to home directory
$ docker load --input bible_display_app_db.tar
$ docker run --interactive --name bda_db bible_display_app:v0 # will use --detach next time
[in other terminal tab]
$ docker exec -it bda_db /bin/bash

then within docker container running on Michael's machine

$ service postgresql status
$ service postgresql start
$ service postgresql status
$ psql --username postgres

postgres=# \d presentations_encoded_date
postgres=# SELECT * FROM  presentations_encoded_date ORDER BY presentation_date LIMIT 10;
postgres=# \d bible_display_app_file_entries_with_morning_or_night
postgres=# SELECT * FROM  bible_display_app_file_entries_with_morning_or_night ORDER BY entry_timestamp LIMIT 10;

postgres=# SELECT id, entry.morning_or_night, entry.entry_timestamp, entry.book, entry.chapter, entry.verse                       
postgres-#  FROM presentations_decoded_date                                                                                                   
postgres-#  JOIN bible_display_app_file_entries_with_morning_or_night as entry ON presentations_decoded_date.presentation_date = entry.entry_date                                                                                                                              
postgres-# WHERE presentations_decoded_date.morning_or_night = entry.morning_or_night AND                                                    
postgres-# id = 3193 ORDER BY entry_timestamp; 

Same SQL commands with better syntax highlighting

SELECT * FROM  presentations_encoded_date ORDER BY presentation_date LIMIT 10;

SELECT * FROM  bible_display_app_file_entries_with_morning_or_night ORDER BY entry_timestamp LIMIT 10;

SELECT id, entry.morning_or_night, entry.entry_timestamp, entry.book, entry.chapter, entry.verse                       
FROM presentations_decoded_date                                                                                                   
JOIN bible_display_app_file_entries_with_morning_or_night as entry ON presentations_decoded_date.presentation_date = entry.entry_date                                                                                                                              
WHERE presentations_decoded_date.morning_or_night = entry.morning_or_night AND                                                    
      id = 3193 
ORDER BY entry_timestamp; 

[zipperer]

$ docker run --interactive --name bda_db bible_display_app:v0 # will use --detach next time

When we next run the container on Michael's machine, we should use port forwarding from the host machine to the container so that we can connect to the database in the container from TablePlus on the host machine. e.g. similar to

$ docker run --interactive --detach --name bda_db    --publish 5433:5432/tcp      bible_display_app:v0