Bible – Cross Reference – Study – Using “scrollmapper/bible_databases” – Load Data

Background

In the last week or two opted to explore the steps it will take to utilize the CrossReference section of the GitHub project “ScrollMapper/bible_databases“.

 

Outline

  1. Artifacts
    • Identify Artifact
    • Review Artifact
    • Download Artifact
    • Review Downloaded Artifact
  2. Load Data
    • Review SQL
    • Apply SQL
    • Review Changes

 

Tasks

Artifacts

Identify Artifacts

ScrollMapper is available @

scrollmapper / bible_databases
Link

 

Review Artifacts

Here are the published files

Image

Textual

  1. cross_reference-mysql.sql
    • SQL File => MySQL dialect
  2. cross_references.txt
    • Text file

 

Download Artifacts

Depending on the targeted Operating System (OS), there are a couple of ways to download the artifacts.

OS / Linux

Specifications
  1. Source:- https://raw.githubusercontent.com/scrollmapper/bible_databases/master/cross_reference-mysql.sql
  2. File:- cross_reference-mysql.sql
Terminal – wget
Syntax

sudo wget -O [filename] [url]
 
Sample

wget https://github.com/scrollmapper/bible_databases/raw/master/cross_reference-mysql.sql -O /tmp/cross_reference-mysql.sql

Output – Text

>wget https://raw.githubusercontent.com/scrollmapper/bible_databases/master/cross_reference-mysql.sql -O /tmp/cross_reference-mysql.sql
--2020-11-10 12:13:19--  https://raw.githubusercontent.com/scrollmapper/bible_databases/master/cross_reference-mysql.sql
Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 151.101.40.133
Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|151.101.40.133|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 12031194 (11M) [text/plain]
Saving to: ‘/tmp/cross_reference-mysql.sql’

/tmp/cross_reference-mysql.sql            100%[=====================================================================================>]  11.47M  3.03MB/s    in 3.8s

2020-11-10 12:13:23 (2.98 MB/s) - ‘/tmp/cross_reference-mysql.sql’ saved [12031194/12031194]

>

Output – Image

 

Review Downloaded Artifact

OS /Linux

cat
cat – syntax

cat [filename]

cat – sample

cat /tmp/cross_reference-mysql.sql | more

output – image

 

Load Data

Review SQL

DDL

SQL

CREATE TABLE IF NOT EXISTS `cross_reference` (
  `vid` int(8) unsigned zerofill NOT NULL COMMENT 'verse ID',
  `r` int(11) NOT NULL COMMENT 'Rank',
  `sv` int(8) unsigned zerofill NOT NULL COMMENT 'Start Verse',
  `ev` int(8) unsigned zerofill NOT NULL COMMENT 'End Verse',
  KEY `vid` (`vid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Textual
Columns
Column Name – Database Column Name – Meaning Explanation
vid Verse ID Verse ID in kjv table
r Rank Ranking
sv Starting Verse ID Starting Correspondent Verse ID in kjv table
ev Ending Verse ID Ending Correspondent Verse ID in the kjv table.

If singular verse, then use 000000000 as filler.

DML

SQL


INSERT INTO `cross_reference` (`vid`, `r`, `sv`, `ev`) 
VALUES
(01001001, 10, 19104030, 00000000),
(01001001, 10, 19134003, 00000000),
(01001001, 10, 20016004, 00000000),
(01001001, 10, 41013019, 00000000),
(01001001, 10, 49003009, 00000000),
(01001001, 11, 13016026, 00000000),
(01001001, 11, 19096005, 00000000),
(01001001, 11, 19121002, 00000000),
(01001001, 11, 45011036, 00000000),
(01001001, 11, 58003004, 00000000),
(01001001, 12, 19033006, 00000000),
(01001001, 12, 19089011, 19089012)

Apply SQL

OS/Linux

mysql
Syntax

mysql -h <hostname> -u <username> --database <database> --verbose -p &lt; <filename>

Sample

mysql -h localhost -u dadeniji --database bible --verbose -p < /tmp/cross_reference-mysql.sql 

 

Review SQL Changes

Outline

  1. Objects
    • cross_reference
  2. Data
    • cross_reference
      • data

Task

Data
cross_reference – Count
SQL

select count(*) as 'numberofRecords'
from bible.cross_reference
;

Output
  1. Number of Records:- 343,609

 

Summary

Loaded the bible.cross_reference table.

Querying the data will have to wait yet more posts.

 

References

  1. MySQL
    • mysql client options
      Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s