Bible Database

Background

As a DBA, I knew I was eventually going to get to the point where I need the Bible as query-able database.

Referenced Work

We are going to use a publicly available effort.

And, it is :-

scrollmapper/bible_databases
Link

Bible versions

Here are bible versions that are available :-

Version Abbreviation url language table
American Standard-ASV1901 ASV http://en.wikipedia.org/wiki/American_Standard_Version English asv
Bible in Basic English BBE http://en.wikipedia.org/wiki/Bible_in_Basic_English English bbe
Darby English Bible DARBY http://en.wikipedia.org/wiki/Darby_Bible English dby
King James Version KJV http://en.wikipedia.org/wiki/King_James_Version English kjv
Webster’s Bible WBT https://en.wikipedia.org/wiki/Webster%27s_Revision English wbt
World English Bible WEB http://en.wikipedia.org/wiki/World_English_Bible English web
Young’s Literal Translation YLT http://en.wikipedia.org/wiki/Young%27s_Literal_Translation English ylt

Database

Tables

Outline

Category Table Description Sample Data
Bible Version [dbo].[bible_version_key] Versions KJV, King James Version

ASV, American Standard Version

Genre [dbo].[key_genre_english] Genre Law,Wisdom, Prophets, Gospels, Acts, Epistles, Apocalyptic
Book [dbo].[key_english] Book Information 1, Genesis, OT
2, Exodus, OT
[dbo].[key_abbreviations_english] Book Information 1, Gen
Content [dbo].[asv] American Standard Version
[dbo].[bbe] Bible in Basic English
[dbo].[dby] Darby English Bible
[dbo].[kjv] Kings James Version
[dbo].[wbt] Webster’s Bible
[dbo].[web] World English Bible
[dbo].[ylt] Young’s Literal Translation

Programmable Objects

Stored Procedures

Search Bible

Outline
Title Object
Search Bible Contents
American Standard Version Search [dbo].[usp_Search_asv]
Bible in Basic English Search [dbo].[usp_Search_bbe]
Kings James Version Search [dbo].[usp_Search_kjv]
Sample Code


USE [bible]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS
(
    SELECT * 
    FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[usp_Search_kjv]') 
    AND type in (N'P', N'PC')
)
BEGIN

    EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[usp_Search_kjv] AS' 

END
GO



ALTER procedure [dbo].[usp_Search_kjv]
(
      @searchTag	    varchar(4000) = null
    , @searchTagIsEmpty bit = null
)
as

    set nocount on;

    declare @separator char(1)
    declare @searchTagFT varchar(4000) = null
    declare @tag		 varchar(4000)
    declare @iNumberofSearchTags smallint
    declare @id smallint

    declare @tblSearchTag TABLE
    (
          [rowNumber] smallint not null
        , [value]	  VARCHAR(600)
        , [xml]		  xml
        , [element]	  xml

    )

    declare @CHAR_QUOTES_DOUBLE char(1)

    set @CHAR_QUOTES_DOUBLE = '"'

    set @separator = ' '
    set @id = 1

    if (
               ( @searchTagIsEmpty is null)
            or ( @searchTagIsEmpty = 1)
      )	
    begin

        if (

                    ( @searchTag is null )
                or  ( @searchTag = '' )

           )
        begin
    
            set @searchTagIsEmpty = 1

        end
        else
        begin
    
            set @searchTagIsEmpty = 0

        end

    end
            
    if (

            ( @searchTagIsEmpty = 0 )
       )
    begin

        if
            (
                     ( @searchTag is not null)
                 and ( @searchTag != '')
            )
        begin

            insert into @tblSearchTag
            (
                  [rowNumber] 
                , [value]	  
                , [xml]		  
                , [element]	  
            )
            select
     
                  [rowNumber] 
                , [value]	  
                , [xml]		  
                , [element]	 

            from   [stringSplit].[itvf_stringSplitUsingXML]
                    (
                          @searchTag		  
                        , @separator
                    )

            set @iNumberofSearchTags = 
                    ( 
                        select max([rowNumber]) 
                        from @tblSearchTag 
                    )
                 
            set @searchTagFT = '('

            set @id = 1

            while (@id <= @iNumberofSearchTags)
            begin

                select
                        @tag = tblST.[value]

                from   @tblSearchTag tblST

                where  [rowNumber] = @id

                set @searchTagFT += 
                                        case
                                            when (@id = 1) then ''
                                            else ' and '
                                        end
                                         
                                        + isNull
                                            (
                                                 @CHAR_QUOTES_DOUBLE 
                                                    + @tag
                                                    + @CHAR_QUOTES_DOUBLE

                                                , ''
                                            )


                set @id += 1

            end

            set @searchTagFT += ')'

        end


        select  

                  tblBB.[n]				
                , tblBT.[t]
                , tblBT.[c]
                , tblBT.[v]

        from [dbo].[kjv]  tblBT
         
        inner join [dbo].[key_english] tblBB 
        
            on tblBT.b = tblBB.[b]  

        where
            (

                CONTAINS
                (
                      tblBT.[t]
                    , @searchTagFT
                )

            )

        order by
                  tblBB.b	
                , tblBT.[c]
                , tblBT.[v]

    end
    else
    begin

        select  
                
                  tblBB.[n]				
                , tblBT.[t]
                , tblBT.[c]
                , tblBT.[v]

        from [dbo].[kjv]  tblBT
         
        inner join [dbo].[key_english] tblBB 
        
            on tblBT.b = tblBB.[b]  

        order by
                  tblBB.b	
                , tblBT.[c]
                , tblBT.[v]

    end



GO


/*


    declare @searchTag	    varchar(4000) = null
    declare @searchTagIsEmpty bit = null

    exec [dbo].[usp_Search_kjv]

      @searchTag	    = 'jesus peter'
    , @searchTagIsEmpty = 0


*/

 

Availed

GitHub

DanielAdeniji/bibleDB
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