<jupyter_start><jupyter_text>Notes & Queries DatabaseThis chapter, and several following ones, will describe how to create various search context for 19th century issues of *Notes & Queries*. These include:- a monolithic PDF of index issues up to 1892;- a searchable database of index issues up to 1892;- a full text searchable database of non-index issues up to 1900.Original scans of the original publication, as well as automatically extracted search text, are available, for free, from the Internet Archive.   Working With Documents From the Internet ArchiveThe Internet Archive – [`archive.org`](https://archive.org/) – is an incredible resource. Amongst other things, it is home to a large number of out-of-copyright digitised books scanned by the Google Book project as well as other book scanning initiatives.In this unbook, I will explore various ways in which can build tools around the Internet Archive and documents retrieved from it.  Searching the Internet ArchiveMany people will be familiar with the web interface to the [Internet Archive](https://archive.org) (and I suspect many more are not aware of the existence of the Internet Archive at all). This provides tools for discovering documents available in the archive, previewing the scanned versions of them, and even searching within them.At times, the search inside a book can be a bit hit and miss, in part depending on the quality of the scanned images and the ability of the OCR tools - where "OCR" stands for "optical character recognition" - to convert the pictures of text into actual text. Which is to say, *searchable* text.To work with the archive, we'll use the Python programming language. This lets us write instructions for our machine helpers to follow. One of the machine helpers comes in the form of the [`internetarchive` Python package](https://archive.org/services/docs/api/internetarchive/index.html), a collection of routines that can access the Internet Archive at the programming, rather than human user interface, level.*The human  level interface simply provides graphical tools that we can understand, such as menu items and toolbar buttons. Selecting or clicking these simply invokes machine level commands in a useable-for-us way. Writing program code lets us call those commands directly, in a textual way, rather than visually, by clicking menu items and buttons. Copying and pasting simple text instructions that can be used to perform a particular function is often quite straightforward. Modifying such commands may also be relatively straightforward. (For example, given a block of code that downloads a file from a web location using code of the form `download_file("https://example.com/this_file.pdf")`, you could probably work out how to download a file from `http://another.example.com/myfile.pdf`.) Creating graphical user interfaces is hard. Graphical user interfaces also constrains users to using just the functions and features that the designers and developers of the user interface chose to support in the user interface, in just the way that the user interface allows. Being able to instruct a machine using code, even copied and pasted code, gives the end-use far more power over the machine.*Within any particular programming language, *packages* are often used to bundle together various tools and functions that can be used to support particular activities or tasks, or work with particular resources or resource types.One of the most useful tools within the Internet Archive package is the `search_items()` function, which lets us search the Internet Archive.<jupyter_code># If we haven't already installed the package into our computing environment,
# we need to download it and install it.
#%pip install internetarchive

# Load in a function to search the archive
from internetarchive import search_items

# We are going to build up a list of search results
items = []<jupyter_output><empty_output><jupyter_text>Item MetadataAt the data level, the Internet Archive has *metadata*, or "data about data" that provides key information or summary information about each data record. For example, works can be organised as part of different collections via `collection` elements such as `collection:"pub_notes-and-queries"`.For periodicals, there may also be a publication identifier associated with the periodical (for example, `sim_pubid:1250`) or metadata identifying which *volume* or *issue* a particular edition of a periodical may be.In the following bit of code, we search over the *Notes & Queries* collection, retrieving data about each item in the collection.This is quite a large collection, so to run a query that retrieves all the items in it may take a considerable amount of time. Instead, we can limit the search to issues published in a particular year, and further limit the query to only retrieve a certain number of records.<jupyter_code># We can use a programming loop to search for items, iterate through the items
# and retrieve a record for each one
# The enumerate() command will loop trhough all the items, returnin a running count of items
# returned, as well as each separate item
# The count starts at 0...
for count, item in enumerate(search_items('collection:"pub_notes-and-queries" AND year:1867').iter_as_items()):
    # Display thecount, the item identifier and title
    print(count, item.identifier, item.metadata['title'])

    # If we see item with count value of at least 3, which is to say, the fourth item,
    # (we start counting at zero, remember...)
    if count >= 3:
        # Then break out of this loop
        break<jupyter_output>0 sim_notes-and-queries_1867-01-05_11_262 Notes and Queries  1867-01-05: Vol 11 Iss 262
1 sim_notes-and-queries_1867-01-12_11_263 Notes and Queries  1867-01-12: Vol 11 Iss 263
2 sim_notes-and-queries_1867-01-19_11_264 Notes and Queries  1867-01-19: Vol 11 Iss 264
3 sim_notes-and-queries_1867-01-26_11_265 Notes and Queries  1867-01-26: Vol 11 Iss 265<jupyter_text>As well as the "offical" collection, some copies of *Notes and Queries* from other providers are also available in the Internet Archive. For example, there are some submissions from *Project Gutenberg*.The following retrieves an item obtained from the `gutenberg` collection, which is to say, *Project Gutenberg*, and previews its metadata:<jupyter_code>from internetarchive import get_item

# Retrieve an item from its unique identifier
item = get_item('notesandqueriesi13536gut')

# And display its metadata
item.metadata<jupyter_output><empty_output><jupyter_text>The items in the `pub_notes-and-queries` collection have much more metadata available, including `volume` and `issue` data, and the identifiers for the `previous` and `next` issue. In some cases, the identifier values may be human readable, if you look closely enough. For example, *Notes and Queries* was published weekly, typically with two volumes per year, and an index for each. In the `pub_notes-and-queries` collections, the identifier for Volume 11, issue 262, published on January 5th, 1867, is `sim_notes-and-queries_1867-01-05_11_262`; and the identifier for the index of volume 12, published in throughout the second half of 1867, is `sim_notes-and-queries_1867_12_index`.  Available Files As well as the data record, certain other files may be associated with that item such as PDF scans, or files containing the raw scanned text of the document.We have already seen how we can retrieve an item given it's identifier, but let's see it in action again:<jupyter_code>item = get_item("sim_notes-and-queries_1867_12_index")

item.metadata['title'], item.identifier<jupyter_output><empty_output><jupyter_text>We can make a call from this data item to return a list of the files associated with that item, and display their file formats:<jupyter_code>for file_item in item.get_files():
    print(file_item.format)<jupyter_output>Item Tile
JPEG 2000
JPEG 2000
Text PDF
Archive BitTorrent
chOCR
DjVuTXT
Djvu XML
Metadata
JSON
hOCR
OCR Page Index
OCR Search Text
Item Image
Single Page Processed JP2 ZIP
Metadata
Metadata
Page Numbers JSON
JSON
Scandata<jupyter_text>For this item, then, we can get a PDF document, a file containing the search text, a record with information about page numbers, an XML version of the original scanned version, some image scans, and various other things containing who knows what!  A Complete List of *Notes & Queries* IssuesTo help us work with the *pub_notes-and-queries* collection, let's construct a local copy of the most important metadata associated with each item in the collection, specifically the item identifier, date and title, as well as the volume and issue. (*Notes and Queries* also has a higher level of organisation, a *Series*, which means thatvolume and issue numbers can actually recycle, so by itself, a particular `(volume, issue)` pair does not identify a unique item, but a `(series, volume, issue)` or `(year, volume, issue)` triple does.)For convenience, we might also collect the *previous* and *next* item identifiers, as well as a flag that tells us whether access is restricted or not. (For 19th century editions, there are no restrictions; but for more recent 20th century editions, access may be limited to library shelf access).The following cell contains a set of instructions bundled together to define a *function* under a unique function name. Functions provide us with a shorthand way of writing a set of instructions once, then calling on them repeatedly via their function name.In particular, the function takes in an item metadata record, tidies it up a little and returns just the fields we are interested in:<jupyter_code>import csv

def out_ia_metadata(item):
    """Retrieve a subset of item metadata and return it as a list."""
    # This is a nested function that looks up piece of metadata if it exists
    # If it doesn't exist, we set it to ''
    def _get(_item, field):
        return _item[field] if field in _item else ''

    #item = get_item(i.identifier)
    identifier = item.metadata['identifier']
    date =  item.metadata['date']
    title = _get(item.metadata, 'title')
    volume =_get(item.metadata, 'volume')
    issue = _get(item.metadata, 'issue')
    prev_ = _get(item.metadata, 'previous_item')
    next_ = _get(item.metadata, 'next_item')
    restricted = _get(item.metadata,'access-restricted-item')
    
    return [identifier, date, title, volume, issue, prev_, next_, restricted]<jupyter_output><empty_output><jupyter_text>Here's what the data retrieved from an item record by the `out_ia_metadata` function looks like:<jupyter_code># Get an item record form its identifier
item = get_item("sim_notes-and-queries_1867_12_index")

# Display the key metadata
out_ia_metadata(item)<jupyter_output><empty_output><jupyter_text>We can now build up a list of lists containing the key metadata for all editions of *Notes of Queries* in the `pub_notes-and-queries` collection.Our recipe will proceed in the following three steps:- search for all the items in the collection;- build up a list of records where item contains the key metadata, extracted from the full record using the `out_ia_metadata()` function;- open a file (*nandq_internet_archive.txt*), give it a column header line, and write the key metadata records to it, one record per line.The file will be written in "CSV" format (comma separarated variable), a simple text format for describing tabular data. CSV files can be read by spreadsheet applications, as well as other tools, and use comma separators to identify "columns" of information in each row.<jupyter_code># Find records for all items in the collection
items = search_items('collection:"pub_notes-and-queries"')

# This piece of voodoo creates a list from from items in another list
# Specifically, for each item in the items list, grab its essential metadata
# and use that as an item in a new list.
csv_items = [out_ia_metadata(i) for i in items]

# We can now open a file
with open('nandq_internet_archive.txt', 'w') as outfile:
    # Create a "CSV writer" object that can write to the file 
    csv_write = csv.writer(outfile)
    # Write a header row at the top of the file
    csv_write.writerow(['id','date','title','vol','iss','prev_id', 'next_id','restricted'])
    # Then write out list of essential metadata items out, one record per row
    csv_write.writerows(csv_items)<jupyter_output><empty_output><jupyter_text>We can use a simple Linux command line tool (`head`) to show the top five lines of the file:<jupyter_code>!head -n 5 nandq_internet_archive.txt<jupyter_output>id,date,title,vol,iss,prev_id,next_id,restricted

sim_notes-and-queries_1849-11-03_1_1,1849-11-03,Notes and Queries  1849-11-03: Vol 1 Iss 1,1,1,sim_notes-and-queries_1849-1850_1_index,sim_notes-and-queries_1849-11-10_1_2,

sim_notes-and-queries_1849-11-10_1_2,1849-11-10,Notes and Queries  1849-11-10: Vol 1 Iss 2,1,2,sim_notes-and-queries_1849-11-03_1_1,sim_notes-and-queries_1849-11-17_1_3,

sim_notes-and-queries_1849-11-17_1_3,1849-11-17,Notes and Queries  1849-11-17: Vol 1 Iss 3,1,3,sim_notes-and-queries_1849-11-10_1_2,sim_notes-and-queries_1849-11-24_1_4,

sim_notes-and-queries_1849-11-24_1_4,1849-11-24,Notes and Queries  1849-11-24: Vol 1 Iss 4,1,4,sim_notes-and-queries_1849-11-17_1_3,sim_notes-and-queries_1849-12-01_1_5,<jupyter_text>So, with some idea of what's available to us, data wise, and file wise, what can we start to do with it?  Generating a Monolithic PDF Index for *Notes & Queries* Up To 1892 If we want to search for items in *Notes and Queries* "manually", one of the most effective ways is to look up items in the volume indexes. With two volumes a year, this means checking almost 100 separate documents if we want to look up 19th century references. (That's not quite true: from the 1890s, indexes were produced that started to to aggregate indices over several years.) So how might we go about producing a single index PDF for 19th c. editions of *Notes & Queries*? As a conjoined set of original index PDFs, this wouldn't provide us with unified index terms - a search on an index item would return separate entries for each volume index in which the term appeared – but it would mean we only needed to search one PDF document.We'll use the Python `csv` package to simplify saving and load the data:<jupyter_code>import csv<jupyter_output><empty_output><jupyter_text>To begin with, we can load in our list of *Notes and Queries* record data downloaded from the Internet Archive.<jupyter_code># Specify the file name we want to read data in from
def open_metadata_records(fn='nandq_internet_archive.txt'):
    """Open and read metadata records file."""

    with open(fn, 'r') as f:
        # We are going to load the data into a data structure known as a dictionary, or dict
        # Each item in the dictionary contains several elements as `key:value` pairs
        # The key matches the column name in the CSV data file,
        # along with the corresponding value in a given item row

        # Read the data in
        csv_data = csv.DictReader(f)

        # And convert it to a list of data records
        data_records = list(csv_data)
        
    return data_records<jupyter_output><empty_output><jupyter_text>Let's grab the metadata records from our saved file:<jupyter_code>data_records = open_metadata_records()

# Preview the first record (index count starts at 0)
# The object returned is a dictionary / dict
data_records[0]<jupyter_output><empty_output><jupyter_text>By inspection of the list of index entries, we note that at some point cumulative indexes over a set of years, as well as volume level indexes, were made available. Cumulative indexes include:- Notes and Queries 1892 - 1897: Vol 1-12 Index- Notes and Queries 1898 - 1903: Vol 1-12 Index- Notes and Queries 1904 - 1909: Vol 1-12 Index- Notes and Queries 1910 - 1915: Vol 1-12 IndexIn this first pass, we shall just ignore the cumulative indexes.At this point, it is not clear where we might reliably obtain the series information from. To make the data easier to work with, we can parse the date as a date thing (technical term!;-) using tools in the Python `dateparser` package:<jupyter_code>import dateparser<jupyter_output><empty_output><jupyter_text>The parsed data provides ways of comparing dates, extracting month and year, and so on.<jupyter_code>indexes = []

# Get index records up to 1892
max_year = 1892

for record in data_records:
    # Only look at index records
    if 'index' in record['id']:
        # Need to handle a YYYY - YYYY exception
        # If we detect it, ignore it
        if len(record['date'].split()) > 1:
               continue
        
        # Parse the year into a date object
        # Then filter by year
        if dateparser.parse(record['date'].split()[0]).year >= max_year:
            break
        indexes.append(record) 

# Preview the first three index records
indexes[:3]<jupyter_output>/usr/local/lib/python3.9/site-packages/dateparser/date_parser.py:35: PytzUsageWarning: The localize method is no longer necessary, as this time zone supports the fold attribute (PEP 495). For more details on migrating to a PEP 495-compliant implementation, see https://pytz-deprecation-shim.readthedocs.io/en/latest/migration.html
  date_obj = stz.localize(date_obj)<jupyter_text>To generate the complete PDF index, we need to do several things:- iterate through the list of index records;- for each one, download the associated PDF to a directory;- merge all the downloaded files into a single PDF;- optionally, delete the original PDF files.  Working With PDF Files Downloaded from the Internet ArchiveWe can download files from the Internet Archive using the `internetarchive.download()` function. This takes a list of items via a `formats` parameter for the files we want to download. For example, we might want to download the "Text PDF" (a PDF file with full text search), or a simple text file containing just the OCR captured text (`OCR Search Text`), or both.We can also specify the directory into which the files are downloaded.Let's import the packages that help simplify this task, and create a path to our desired download directory:<jupyter_code># Import the necessary packages
from internetarchive import download
from pathlib import Path

# The tqdm package provides a convenient progress bar
# for tracking progress through looped actions
from tqdm.notebook import tqdm<jupyter_output><empty_output><jupyter_text>To keep our files organised, we'll create a directory into which we can download the files:<jupyter_code># Create download dir file path
dirname = 'ia-downloads'

p = Path(dirname)<jupyter_output><empty_output><jupyter_text>One of the ways we can work with the data is to process it using Python programming code.For example, we can iterate through the index records and download the required files:<jupyter_code># Use tqdm for provide a progress bar
for record in tqdm(indexes):
    _id = record['id']
    
    # Download PDF - this may take time to retrieve / download
    # This downloads to a direcotry with the same name as the record id
    # The file name is akin to ${id}.pdf
    download(_id, destdir=p, silent = True,
             formats=["Text PDF", "OCR Search Text"])<jupyter_output><empty_output><jupyter_text>To create single monolithic PDF, we can use another fragment of code to iterate through the downloaded PDF files, adding each one to a single merged PDF file object. We can also create and insert a reference page between each of the original documents to provide provenance if the is no date on the index pages.Let's start by seeing how to create a simple PDF page. The `reportlab` Python package provides various tools for creating simple PDF documents:<jupyter_code>#%pip install --upgrade reportlab
from reportlab.pdfgen.canvas import Canvas<jupyter_output><empty_output><jupyter_text>For example, we can create a simple single page document that we can add index metadata to and then insert in between the pages of each index issue:<jupyter_code># Create a page canvas
test_pdf = "test-page.pdf"
canvas = Canvas(test_pdf)

# Write something on the page at a particular location
# In this case, let's use the title from the first index record
txt = indexes[0]['title']
# Co-ordinate origin is bottom left of the page
# Scale is points, where 72 points = 1 inch
canvas.drawString(72, 10*72, txt)

# Save the page
canvas.save()<jupyter_output><empty_output><jupyter_text>Now we can preview the test page:<jupyter_code>from IPython.display import IFrame

IFrame(test_pdf, width=600, height=500)<jupyter_output><empty_output><jupyter_text>A simple function lets us generate a simple page rendering a short text string:<jupyter_code>def make_pdf_page(txt, fn="test_pdf.pdf"):
    """"""
    canvas = Canvas(fn)

    # Write something on the page at a partcular location
    # Co-ordinate origin is bottom left of the page
    # Scale is points, where 72 points = 1 inch
    canvas.drawString(72, 10*72, txt)

    # Save the page
    canvas.save()
    
    return fn<jupyter_output><empty_output><jupyter_text>Let's now create our monolithic index with metadata page inserts.The `PyPDF2` package contains various tools for splitting and combining PDF documents:<jupyter_code>from PyPDF2 import PdfFileReader, PdfFileMerger<jupyter_output><empty_output><jupyter_text>We can use it merge our separate index cover page and index issue documents, for example:<jupyter_code># Create a merged PDF file creating object
output = PdfFileMerger()

# Generate a monolithic PDF index file by concatenating the pages
# from each individual PDF index file
# Use tqdm for provide a progress bar
for record in tqdm(indexes):
    # Generate some metadata:
    txt = record['title']
    metadata_pdf = make_pdf_page(txt)
    # Add this to the output document
    output.append(metadata_pdf)
    # Delete the metadata file
    Path(metadata_pdf).unlink()

    # Get the record ID
    _id = record['id']

    # Locate the file and merge it into the monolithic PDF
    output.append((p / _id / f'{_id}.pdf').as_posix())
    
# Write merged PDF file
with open("notes_and_queries_big_index.pdf", "wb") as output_stream:
    output.write(output_stream)

output = None<jupyter_output><empty_output><jupyter_text>The resulting PDF document is a large document that collects all the separate indexes in one place, although not as a single, *reconciled* index: if the same index terms exist in multiple index documents, there will be multiple occurrences of that term in the longer document.<jupyter_code># --SPLITHERE--<jupyter_output><empty_output><jupyter_text>Generating a Full Text Searchable Database for *Notes & Queries*Whilst the PDF documents corresponding to each issue of *Notes and Queries* are quite large files, the searchable, OCR retrieved text documents are much smaller and can be easily added to a full-text searchable database.We can create a simple, file based SQLite database that will provide a full-text search facility over each issue of *Notes and Queries*.Let's start by creating the basic database:<jupyter_code>from sqlite_utils import Database

db_name = "nq_demo.db"

# While developing the script, recreate database each time...
db = Database(db_name, recreate=True)<jupyter_output><empty_output><jupyter_text>Populating the Database With Record MetadataLet's start by creating a table in the database that can store our metadata data records, as loaded in from the  data file.<jupyter_code>import dateparser
import datetime

def create_db_table_metadata(db, drop=True):
    # If we want to remove the table completely, we can drop  it
    if drop:
        db["metadata"].drop(ignore=True)
    db["metadata"].create({
        "id": str,
        "date": str,
        "datetime": datetime.datetime, # Use an actual time representation
        "series": str,
        "vol": str,
        "iss": str,
        "title": str, 
        "next_id": str, 
        "prev_id": str,
        "is_index": bool, # Is the record an index record
        "restricted": str, # should really be boolean
    }, pk=("id"))
    

create_db_table_metadata(db)<jupyter_output><empty_output><jupyter_text>We need to do a little tidying of the records, but then we can add them directly to the database:<jupyter_code>def add_patched_metadata_records_to_db(db, data_records):
    """Add metadata records to database."""
    # Patch records to include a parsed datetime element
    for record in tqdm(data_records):
        # Parse the raw date into a date object
        # Need to handle a YYYY - YYYY exception
        # If we detect this form, use the last year for the record
        if len(record['date'].split()[0]) > 1:
            record['datetime'] = dateparser.parse(record['date'].split()[-1])
        else:
            record['datetime'] = dateparser.parse(record['date'])

        record['is_index'] = 'index' in record['title'].lower() # We assign the result of a logical test

    # Add records to the database
    db["metadata"].insert_all(data_records)<jupyter_output><empty_output><jupyter_text>Let's call that function and add our metadata data records:<jupyter_code>add_patched_metadata_records_to_db(db, data_records)<jupyter_output><empty_output><jupyter_text>We can then query the data, for example return the first rows:<jupyter_code>from pandas import read_sql

q = "SELECT * FROM metadata LIMIT 5"

read_sql(q, db.conn)<jupyter_output><empty_output><jupyter_text>Or we could return the identifiers for index issues between 1875 and 1877:<jupyter_code>q = """
SELECT id, title
FROM metadata
WHERE is_index = 1
    -- Extract the year
    AND strftime('%Y', datetime) >= '1875'
    AND strftime('%Y', datetime) <= '1877'
"""

read_sql(q, db.conn)<jupyter_output><empty_output><jupyter_text>Adding an `issues` Table to the DatabaseHaving popped the metadata in the database, let's now create a simple table structure for the searchable text extracted from each issue of *Notes & Queries*.We've already got the metadata stored, so we don't need to put that into the database again. Rather, we'll create a simpler table containing the content and a unique identifier for each record.We can relate this table to the metadata table through a *foreign key*. What this means is that for each entry in the issues table, we also expect to find an entry in the metadata table under the same identifier value.We will also create a full text search table associated with the table:<jupyter_code>def create_db_table_issues(db, drop=True):
    if drop:
        db["issues"].drop(ignore=True)
        db["issues_fts"].drop(ignore=True)

    db["issues"].create({
        "id": str,
        "content": str
    }, pk=("id"), foreign_keys=[ ("id", "metadata", "id") # local-table-id, foreign-table, foreign-table-id)
    ])
    
    # Enable full text search
    # This creates an extra virtual table (issues_fts) to support the full text search
    # A stemmer is applied to support the efficacy of the full-text searching
    db["issues"].enable_fts(["id", "content"], create_triggers=True, tokenize="porter")
    
create_db_table_issues(db)<jupyter_output><empty_output><jupyter_text>To add the content data to the database, we need to download the searchable text associated with each record from the Internet Archive.Before we add the data in bulk, let's do a dummy run of the steps we need to follow.First, we need to download the full text file from the Internet Archive, given a record identifier. We'll use the first data record to provide us with the identifier:<jupyter_code>data_records[0]<jupyter_output><empty_output><jupyter_text>The download step takes the identifier and requests the `OCR Search Text` file, downloading it to the directory we specified previously:<jupyter_code>download(data_records[0]['id'], destdir=p, silent = True,
         formats=["OCR Search Text"])<jupyter_output><empty_output><jupyter_text>The data files are actually download as compressed archive files, as we can see if we review the download directory we saved our test download to:<jupyter_code>import os

os.listdir( p / data_records[0]['id'])<jupyter_output><empty_output><jupyter_text>We now need to uncompress the `.txt.gz` file to access the fully formed text file.The `gzip` package provides us with the utility we need to access the contents of the archive file.In fact, we don't need to actually uncompress the file into the directory, we can open it and extract its contents "in memory".<jupyter_code>import gzip

# Create a simple function to make it even easier to extract the full text content
def get_txt_from_file(id_val, dirname='ia-downloads', typ="searchtext"):
    """Retrieve text from downloaded text file."""
    if typ=="searchtext":
        p_ = Path(dirname) / id_val / f'{id_val}_hocr_searchtext.txt.gz'
        f = gzip.open(p_,'rb')
        content = f.read().decode('utf-8')
    elif typ=="djvutxt":
        p_ = Path(dirname) / id_val / f'{id_val}_djvu.txt'
        content = p_.read_text()
    else:
        content = ""
    return content<jupyter_output><empty_output><jupyter_text>Let's see how it works, previewing the first 200 characters of the unarchived text file:<jupyter_code>get_txt_from_file(data_records[0]['id'])[:200]<jupyter_output><empty_output><jupyter_text>If we inspect the text in more detail, we see there are various things in it that we might want to simplify. For example, quotation marks appear in various guises, such as opening and closing quotes of different flavours. We *could* normalise these to a simpler form (for example, "straight" quotes `'` and `"`), However, *if* opening and closing quotes are reliably recognised they do provide us with a simple text for matching text contained *within* the quotes. So for now, let's leave the originally detected quotes in place. Having got a method in place, let's now download the contents of the non-index issues for 1849.<jupyter_code>q = """
SELECT id, title
FROM metadata
WHERE is_index = 0
    AND strftime('%Y', datetime) = '1849'
"""
results = read_sql(q, db.conn)
results<jupyter_output><empty_output><jupyter_text>The data is return from the `read_sql()` function as a *pandas* dataframe.This *pandas* package provides a very powerful set of tools for working with tabular data, including being able to iterate over he rows of the table and apply a function to each one.If we define a function to download the corresponding search text file from the Internet Archive and extract the text from the downloaded archive file, we can apply that function with a particular column value taken from each row of the dataframe and add the returned content to a new column in the same dataframe.Here's an example function:<jupyter_code>def download_and_extract_text(id_val, typ="searchtext", verbose=False):
    """Download search text from Internet Archive, extract the text and return it."""
    if verbose:
        print(f"Downloading {id_val} issue text")
    if typ=="searchtext":
        download(id_val, destdir=p, silent = True,
             formats=["OCR Search Text"])
    elif typ=="djvutxt":
        download(id_val, destdir=p, silent = True,
             formats=["DjVuTXT"])
    else:
        return ''
    
    text = get_txt_from_file(id_val, typ=typ)
    return text<jupyter_output><empty_output><jupyter_text>The Python *pandas* package natively provides an `apply()` function. However, the `tqdm` progress bar package also provides an "apply with progress bar" function, `.progress_apply()` if we enable the appropriate extensions:<jupyter_code>tqdm.pandas()<jupyter_output><empty_output><jupyter_text>Let's apply our `download_and_extract_text()` function to each row of our records table for 1849, keeping track of progress with a progress bar:<jupyter_code>results['content'] = results["id"].progress_apply(download_and_extract_text)
results<jupyter_output><empty_output><jupyter_text>We can now add that data table directly to our database using the *pandas* `.to_sql()` method:<jupyter_code># Add the issue database table
table_name = "issues"
results[["id", "content"]].to_sql(table_name, db.conn, index=False, if_exists="append")<jupyter_output><empty_output><jupyter_text>*Note that this recipe does not represent a very efficient way of handling things: the pandas dataframe is held in memory, so as we add more rows, the memory requirements to store the data increase. A more efficient approach might be to create a function that retrieves each file, adds its contents to the database, and then perhaps even deletes the downloaded file, rather than adding the content to the in-memory dataframe.* Let's see if we can query it, first at the basic table level:<jupyter_code>q = """
SELECT id, content
FROM issues
WHERE LOWER(content) LIKE "%customs%"
"""
read_sql(q, db.conn)<jupyter_output><empty_output><jupyter_text>This is not overly helpful, perhaps. We can do better with the full text search, which will also allow us to return a snippet around the first, or highest ranked, location of any matched search terms:<jupyter_code>search_term = "<PASSWORD>"

q = f"""
SELECT id, snippet(issues_fts, -1, "__", "__", "...", 10) as clip
FROM issues_fts WHERE issues_fts MATCH {db.quote(search_term)} ;
"""

read_sql(q, db.conn)<jupyter_output><empty_output><jupyter_text>This is okay as far as is goes: we can identify *issues* of *Notes and Queries* that contain a particular search term, retrieve the whole document, and even display a concordance for the first (or highest ranking) occurrence of the search term(s) to provide context for the response. But it's not ideal. For example, to display a concordance of each term in the full text document that matches our search term, we need to generate our own concordance, which may be difficulat where matches are inexact (for example if the match relies on stemming). There are also many pages in each issue of *Notes and Queries* and it would be useful if we could get the result at a better level of granularity.The `ouseful_sqlite_search_utils` package includes various functions for allowing us to tunnel into a text document to retrieve The tools aren't necessarily the *fastest* utilities to run, particularly on large databases, but they get their eventually.One particular utility will split a document into sentences and return each sentence on a separate row of a newly created virtual table. We can then search within these values for our search term, although we are limited to running *exact match* queries, rather than the more forgiving full text search queries:<jupyter_code>from ouseful_sqlite_search_utils import snippets

snippets.register_snippets(db.conn)

q = """
SELECT * FROM
    (SELECT id, sentence
     FROM issues, get_sentences(1, NULL, issues.content)
     WHERE issues.id = "sim_notes-and-queries_1849-11-10_1_2")
WHERE sentence LIKE "% custom %"
"""

# Show the full result record in each case
read_sql(q, db.conn).to_dict(orient="records")<jupyter_output><empty_output><jupyter_text>Extracting PagesTo make for more efficient granular searching, it would be useful if our content was stored in a more granular way.Ideally, we would extract items at the "article" level, but there is no simple way of chunking the document at this level. We could process it to extract items at the sentence or paragraph level and add those to their own table, but that might be *too* granular.However, by inspection of the files available for each issue, there appears to be another level of organisation that we can access: the *page* level. *Page* metadata is provided in the the form of two files:- `OCR Page Index`: downloaded as a compressed `.gz` file the expanded file contains a list of lists. Each inner list contains four integers and each page has an associated inner list. The first and second integers in each inner list are the character count in the search text file representing the first and last characters on the corresponding page;- `Page Numbers JSON`: the pages numbers JSON file, which is downloaded as an uncompressed JSON file contains a JSON object with a `"pages"` attribute that returns a list of records; each record has four attributes: `"leafNum": int` (starting with index value 1), `"ocr_value": list` (a list of candidate OCR values), `"pageNumber": str` and `"confidence": float`. A top-level `"confidence"` attribute gives an indication of how likely it is that page numbers are available across the whole document.We also need the `OCR Search Text` file.Let's get a complete set of necessary files for a few sample records:<jupyter_code>def download_ia_records_by_format(records, path=".", formats=None):
    """Download records from Internet Archive given ID and desired format(s)"""
    formats = formats if formats else ["OCR Search Text", "OCR Page Index", "Page Numbers JSON"]
    
    for record in tqdm(records):
        _id = record['id']
        download(_id, destdir=path,
                 formats=formats,
                 silent = True)
# Grab page counts and page structure files
sample_records = data_records[:5]

download_ia_records_by_format(sample_records, p)<jupyter_output><empty_output><jupyter_text>We now need to figure out how to open and parse the page index and page numbers files, and check the lists are the correct lengths.The Python `zip` function  lets us "zip" together elements from different, parallel lists. We can also insert the same item, repeatedly, into each row using the `itertools.repeat()` function to generate as many repetitions of the same character as are required:<jupyter_code>import itertools<jupyter_output><empty_output><jupyter_text>Example of using `itertools.repeat()`:<jupyter_code># Example of list
list(zip(itertools.repeat("a"), [1, 2], ["x","y"]))<jupyter_output><empty_output><jupyter_text>We can now use this approach to create a zipped combination of the record ID values, page numbers and page character indexes.<jupyter_code>import json
import itertools

#for record in tqdm(sample_records):

record = sample_records[0]

id_val = record['id']
p_ = Path(dirname) / id_val 

# Get the page numbers
with open(p_ / f'{id_val}_page_numbers.json', 'r') as f:
    page_numbers = json.load(f)

# Get the page character indexes
with gzip.open(p_ / f'{id_val}_hocr_pageindex.json.gz', 'rb') as g:
    # The last element seems to be redundant
    page_indexes = json.loads(g.read().decode('utf-8'))[:-1]

# Optionally text the record counts are the same for page numbers and character indexes
#assert len(page_indexes) == len(page_numbers['pages'])

# Preview the result
list(zip(itertools.repeat(id_val), page_numbers['pages'], page_indexes))[:5]<jupyter_output><empty_output><jupyter_text>We could add this page related data directly to the pages table, or we could create another simple database table to store it.Here's what a separate table might look like:<jupyter_code>def create_db_table_pages_metadata(db, drop=True):
    if drop:
        db["pages_metadata"].drop(ignore=True)
    db["pages_metadata"].create({
        "id": str,
        "page_idx": int, # This is just a count as we work through the pages 
        "page_char_start": int,
        "page_char_end": int,
        "page_leaf_num": int, 
        "page_num": str,
        "page_num_conf": float # A confidence value relating to the page number detection
    }, pk=("id", "page_idx")) # compound foreign keys not currently available via sqlite_utils?
    
create_db_table_pages_metadata(db)<jupyter_output><empty_output><jupyter_text>The following function "zips" together the contents of the page index and page numbers files. Each "line item" is a rather unwieldy mixmatch of elements, but we'll deal with those in a moment:<jupyter_code>import itertools
import json

def raw_pages_metadata(id_val):
    """Get page metadata."""

    p_ = Path(dirname) / id_val

    # Get the page numbers
    with open(p_ / f'{id_val}_page_numbers.json', 'r') as f:
        # We can ignore the last value
        page_numbers = json.load(f)
    
    # Get the page character indexes
    with gzip.open(p_ / f'{id_val}_hocr_pageindex.json.gz', 'rb') as g:
        # The last element seems to be redundant
        page_indexes = json.loads(g.read().decode('utf-8'))[:-1]

    # Add the id and an index count
    return zip(itertools.repeat(id_val), range(len(page_indexes)),
               page_numbers['pages'], page_indexes)<jupyter_output><empty_output><jupyter_text>For each line item in the zipped datastructure, we can parse out values into a more readable data object:<jupyter_code>def parse_page_metadata(item):
    """Parse out page attributes from the raw page metadata construct."""
    _id = item[0]
    page_idx = item[1]
    _page_nums = item[2]
    ix = item[3]
    obj = {'id': _id,
           'page_idx': page_idx, # Maintain our own count, just in case; should be page_leaf_num-1
           'page_char_start': ix[0],
           'page_char_end': ix[1],
           'page_leaf_num': _page_nums['leafNum'],
           'page_num': _page_nums['pageNumber'],
           'page_num_conf':_page_nums['confidence']
          }
    return obj<jupyter_output><empty_output><jupyter_text>Let's see how that looks:<jupyter_code>sample_pages_metadata_item = raw_pages_metadata(id_val)

for pmi in sample_pages_metadata_item:
    print(parse_page_metadata(pmi))
    break<jupyter_output>{'id': 'sim_notes-and-queries_1849-11-03_1_1', 'page_idx': 0, 'page_char_start': 0, 'page_char_end': 301, 'page_leaf_num': 1, 'page_num': '', 'page_num_conf': 0}<jupyter_text>We can now trivially add the page metadata to the `pages_metadata` database table. Let's try it with our sample:<jupyter_code>def add_page_metadata_to_db(db, records, verbose=False):
    """Add page metadata to database."""
    
    for record in records:
        id_val = record["id"]
        if verbose:
            print(id_val)
            
        records = [parse_page_metadata(pmi) for pmi in raw_pages_metadata(id_val)]
    
        # Add records to the database
        db["pages_metadata"].insert_all(records)<jupyter_output><empty_output><jupyter_text>And run it with the page metadata records selected via a `id_val`:<jupyter_code># Clear the db table
db["pages_metadata"].delete_where()

# Add the metadata to the table
add_page_metadata_to_db(db, sample_records)<jupyter_output><empty_output><jupyter_text>Let's see how that looks:<jupyter_code>from pandas import read_sql

q = "SELECT * FROM pages_metadata LIMIT 5"

read_sql(q, db.conn)<jupyter_output><empty_output><jupyter_text>Alternatively, we can view the results as a Python dictionary:<jupyter_code>read_sql(q, db.conn).to_dict(orient="records")[:3]<jupyter_output><empty_output><jupyter_text>For each file containg the search text for a particular issue, we also need a routine to extract the page level content. Which is to say, we need to chunk the content based on character indices associated with the first and last characters on each page in the corresponding search text file. This essentially boils down to:- grabbing the page index values;- grabbing the page search text;- chunking the search text according to the page index values. We can apply a page chunker at the document level, paginating the content file, and adding things to the database.The following function will load<jupyter_code>def chunk_page_text(db, id_val):
    """Chunk text according to page_index values."""
    
    q = f'SELECT * FROM pages_metadata WHERE id="{id_val}"'
    page_indexes = read_sql(q, db.conn).to_dict(orient="records")
    
    text = get_txt_from_file(id_val)
        
    for ix in page_indexes:
        ix["page_text"] = text[ix["page_char_start"]:ix["page_char_end"]].strip()

    return page_indexes<jupyter_output><empty_output><jupyter_text>Let's see if we've managed to pull out some page text:<jupyter_code># Create a sample index ID
sample_id_val = sample_records[0]["id"]

# Get the chunked text back as part of the metadata record
sample_pages = chunk_page_text(db, sample_id_val)

sample_pages[:3]<jupyter_output><empty_output><jupyter_text>Modifying the `pages_metadata` Table in the DatabaseUsing the `chunk_page_text()` function, we can add page content to our pages metadata *in-memory*. But what if we want to add it to the database. The `pages_metadata` already exists, but does not include a `text` column. However, we can modify that table to include just such a column:<jupyter_code>db["pages_metadata"].add_column("page_text", str)<jupyter_output><empty_output><jupyter_text>We can also enable a full text search facility over the table. Our interest is primarily in searching over the `page_text`, bit if we include a couple of other columns that can help us key into records in other tables.<jupyter_code># Enable full text search
# This creates an extra virtual table to support the full text search
db["pages_metadata_fts"].drop()
db["pages_metadata"].enable_fts(["id", "page_idx", "page_text"], create_triggers=True, tokenize="porter")<jupyter_output><empty_output><jupyter_text>We can now update the records in the `pages_metadata` table so they include the `page_text`:<jupyter_code>q = f'SELECT DISTINCT(id) FROM pages_metadata;'
id_vals = read_sql(q, db.conn).to_dict(orient="records")

for sample_id_val in id_vals:
    updated_pages = chunk_page_text(db, sample_id_val["id"])
    db["pages_metadata"].upsert_all(updated_pages, pk=("id", "page_idx"))<jupyter_output><empty_output><jupyter_text>We should now be able to search at the page level:<jupyter_code>search_term = "customs"

q = f"""
SELECT * FROM pages_metadata_fts
WHERE pages_metadata_fts MATCH {db.quote(search_term)};
"""

read_sql(q, db.conn)<jupyter_output><empty_output><jupyter_text>We can then bring in additional columns from the original `pages_metadata` table:<jupyter_code>search_term = "customs"

q = f"""
SELECT page_num, page_leaf_num, pages_metadata_fts.* FROM pages_metadata_fts, pages_metadata
WHERE pages_metadata_fts MATCH {db.quote(search_term)} 
    AND pages_metadata.id = pages_metadata_fts.id
    AND pages_metadata.page_idx = pages_metadata_fts.page_idx;
"""

read_sql(q, db.conn)<jupyter_output><empty_output><jupyter_text>Automatically Populating the `pages` Table from the `issues` TableRather than manually adding the page data to the `pages` table, we can automatically create the `pages` table from the content contained in the `issues` table and the page metadata in the `metaday` table.TO DO  - CREATE TABLE AS ;- maybe also as an extra demonstrate how to generate this automatically from a trigger- discuss various advantages and disadvantages of each approach; one is a step wise pipeline (create as) other is reactive and automatic ( trigger)<jupyter_code># --SPLITHERE--<jupyter_output><empty_output><jupyter_text>Building a Complete Full Text Search EngineHaving got various pieces in place, we're now in a position to attempt to create a comprehensive full text search engine over 19th century issue of *Notes & Queries*. As we use the database, thee may well be "optimisations" we can make, for example in trying to tidy up the content a little. But for now, let's just put the pieces we've already assembled together and see how it looks.Let's create a db from scratch:<jupyter_code>RECREATE_FULL_DB = False

dirname = 'ia-downloads'
p = Path(dirname)

if RECREATE_FULL_DB:
    db_full = Database("full_nq.db", recreate=True)

    create_db_table_metadata(db_full)
    data_records = open_metadata_records()
    add_patched_metadata_records_to_db(db_full, data_records)

    create_db_table_issues(db_full)

    create_db_table_pages_metadata(db_full)
    db_full["pages_metadata"].add_column("page_text", str)
    db_full["pages_metadata_fts"].drop(ignore=True)
    db_full["pages_metadata"].enable_fts(["id", "page_idx", "page_text"], create_triggers=True, tokenize="porter")
else:
    db_full = Database("full_nq.db")
# Get the records for a particular year

q = """
SELECT id, title, date, is_index
FROM metadata
WHERE is_index = 0
    AND strftime('%Y', datetime) = '{year}';
"""

results_19th_cent = read_sql(q.format(year=1849), db_full.conn)
results_19th_cent<jupyter_output><empty_output><jupyter_text>We now need to:    - iterate through the records;- download the issue;- carve it into various parts;- add the parts to the database.We have all the pieces we need, so let's do it:<jupyter_code>if RECREATE_FULL_DB:
    for year in tqdm(range(1849, 1900)):
        # Get issues by year
        results_by_year = read_sql(q.format(year=str(year)), db_full.conn)

        # Download issue content by year
        results_by_year['content'] = results_by_year["id"].apply(download_and_extract_text, verbose=False)

        # Add issues content to database
        results_by_year[["id", "content"]].to_sql("issues", db_full.conn, index=False, if_exists="append")

        # For each issue, we need to grab the metadata and store it in the database
        download_ia_records_by_format(results_by_year.to_dict(orient="records"), p)
        add_page_metadata_to_db(db_full, results_by_year.to_dict(orient="records"), verbose=False)

        for record_id_val in results_by_year['id'].to_list():
            updated_pages = chunk_page_text(db_full, record_id_val)
            db_full["pages_metadata"].upsert_all(updated_pages, pk=("id", "page_idx"))
q = """
SELECT COUNT(*)
FROM pages_metadata;
"""

read_sql(q, db_full.conn)
q = """
SELECT COUNT(*) FROM issues;
"""

read_sql(q, db_full.conn)
search_term = "sin <PASSWORD>"

#q = f"""
#SELECT * FROM pages_metadata_fts
#WHERE pages_metadata_fts MATCH {db.quote(search_term)};
#"""

q = """
SELECT id, snippet(pages_metadata_fts, -1, "__", "__", "...", 10) as clip
FROM pages_metadata_fts WHERE pages_metadata_fts MATCH {query} ;
"""

read_sql(q.format(query=db.quote(search_term)), db_full.conn)
# --SPLITHERE--<jupyter_output><empty_output><jupyter_text>Creating a Database Table From Index VolumesAlthough putting textual content into a database allows us to create full text search tools over that content, a lot of work and effort went into creating the original indexes. So can we scrape the text data from the indexes and generate add the index data to a database `original_index` table to create a comprehensive searchable index?To start with, what columns might such a table need? Let's review an example index issue of *Notes & Queries*:<jupyter_code>sample_index_id = indexes[0]["id"]

IFrame( (p / sample_index_id / f'{sample_index_id}.pdf').as_posix(), width=600, height=500)<jupyter_output><empty_output><jupyter_text>Let's also have a look at some of the raw search text for an index issue:<jupyter_code>simple_index_text = download_and_extract_text(sample_index_id)

print(simple_index_text[:1000])
sample_index_text = download_and_extract_text(sample_index_id, typ="djvutxt")

print(sample_index_text[:1000])
sample_index_id<jupyter_output><empty_output><jupyter_text>Inspecting some of the documents shows that there is no guarantee that the search text correctly represents index items on a new line, although in certain documents it appears as if line breaks after each entry are provided (as in the original scanned image).There are also "sub-elements" on separate lines that relate to a major heading that we really need to "fill down" on, although there is may be no indication in the text (e.g. no series of dashes or a tab characters) to indicate the the subsidiary nature of a reference. (Note that there may be further clues in the original XML, for example, from the location of the text.) However, subsidiary entries do often appear to start with a lower case letter, so let's use that as a heuristic: *if the line starts with a lower case letter, it's a subsidiary entry*. More detailed inspection of the index search text also suggests that in some cases `-` separator characters may appear in the search text.To create a complete index, one possible approach is to:- normalise a single entry and all its subsidiary entries onto a single line;- parse a single entry and all its subsidiary entries into appropriate database records.Rather than consider the XML and all the additional processing that incurs, let's try to "repair" the document as best we can. Another thing we *could* try to exploit is the alphabetical order of entries, but let's leave that as an open question and only return to it if we find issues occurring that alphabetisation might help us address.So let's start by repairing the text and normalising the lines before considering how to parse the entries.  Reinserting Line BreaksIf we can identify where line breaks are likely to be missing, we should be able to reinsert them.By inspection of the raw search text, it seems that we have a page number (digits), space character, and then typically the next entry start by a capital letter (subsidiary lines seem to start with a lower case character). We can perform a regular expression substitution to match this pattern and replace the space after the final page number with an end-of-line character.Some lines also start with opening quotes of various flavours (`‘` or `“` for example), or incorrectly recognised quotes rendered as a `*` character. We can also insert line breaks in advance of these:<jupyter_code>import re

def repair_index_missing_line_breaks(text):
    """Attempt to repair missing line breaks."""
    # Add line break after page number
    # allowing a single optional grace character at end for incorrect OCR
    repaired_text  = re.sub(r"([0-9].?\s*\.?)[\s]+([\(‘“\"'\*A-Z])", r'\1\n\2', text)
    
    return repaired_text<jupyter_output><empty_output><jupyter_text>Let's see how that looks:<jupyter_code>repaired_sample_index = repair_index_missing_line_breaks( sample_index_text[:1000] )

print(repaired_sample_index)<jupyter_output>THE
A.
A.(A.) on solemnization of matrimony,
4 bbé Strickland, 237
Abbey of St. <NAME>, bot, <NAME>
Aboriginal chambers near Tilbury, Achilles and the tortoise, 154. 185 346. dam of Bremen’s Julin, 282 443 lAdamson's Reign of Edward I1., 297.
Admiration ! a note of, 86 dur, origin of, 71. 108.
Earicus qui signa fundebat,”’ 297.
A. (E. H.) on baptismal superstition, 197. on curfew, at Morpeth, 312. on Duresme and Dunelm, 206.
<NAME>, 423.
Aérostation, works on, 199. 251. $17. 380. 469. érostation, squib on Lunardi, 469.
A Frog he would,”’ &c. 45. 188 .
(F. R.) on Dr. Magina, 109. on the Darby Ram,
Normandy, 199. of Strata Florida, 493 1, hoods worn by doctors of divinity
269. 2
122.
on Parse
on Hoc
on the Turkish Spy, 12.
After” (the word in the Rubric, 424.
498.
‘Agapemone, the, 17. 49.
Agincourt, Sir Hilary charged at, 158. 190.
Ague, cure for, 130
spiders, a cure for, 259.
A. (J. D.) on meaning of Wraxen, 366.
A. (J. T.) on Captain John Stevens, 359. on Hogarth’s [...]<jupyter_text>Removing Unwanted Line BreaksIf what appear to be page numbers appear on the their own line, they should presumably appear as page numbers for the previous reference.In other cases, a subsidiary reference might incorrectly be place on one line, or a line might end on a comma. In such cases, we might assume the associated line breaks to be unwanted.So let's replace the line breaks in those locations with spaces, and then also replace any double spaces we might have introduced (or that were present withing the original scanned text) with a single space:<jupyter_code>def repair_index_unwanted_line_breaks(text):
    """Attempt to repair extraneous line breaks."""
    # Fix unwanted line end before page number
    repaired_text  = re.sub(r"\n([0-9].*)", r' \1', text)
    # Fix unwanted line end before subsidiary entry (initial lower case character)
    # Identify subsidiary split with a ::: separator
    repaired_text  = re.sub(r"\n([a-z].*)", r' ::: \1', repaired_text)
    # Fix unwanted line break after comma
    #repaired_text  = re.sub(r",\s*\n", r', ZZ', repaired_text)
    
    # Remove duplicate spaces
    repaired_text  = re.sub(r"  ", r' ', repaired_text)
    
    return repaired_text<jupyter_output><empty_output><jupyter_text>How do things look now?<jupyter_code>repaired_sample_index = repair_index_missing_line_breaks( sample_index_text[:1000] )
repaired_sample_index = repair_index_unwanted_line_breaks( repaired_sample_index )

print(repaired_sample_index)<jupyter_output>THE
A.
A.(A.) on solemnization of matrimony, 4 bbé Strickland, 237
Abbey of St. W sndrille, bot, Richard, Aberde
Aboriginal chambers near Tilbury, Achilles and the tortoise, 154. 185 346. dam of Bremen’s Julin, 282 443 lAdamson's Reign of Edward I1., 297.
Admiration ! a note of, 86 dur, origin of, 71. 108.
Earicus qui signa fundebat,”’ 297.
A. (E. H.) on baptismal superstition, 197. on curfew, at Morpeth, 312. on Duresme and Dunelm, 206.
B<NAME>, 423.
Aérostation, works on, 199. 251. $17. 380. 469. érostation, squib on Lunardi, 469.
A Frog he would,”’ &c. 45. 188 .
(F. R.) on Dr. Magina, 109. on the Darby Ram,
Normandy, 199. of Strata Florida, 493 1, hoods worn by doctors of divinity 269. 2 122. ::: on Parse ::: on Hoc ::: on the Turkish Spy, 12.
After” (the word in the Rubric, 424. 498.
‘Agapemone, the, 17. 49.
Agincourt, <NAME> charged at, 158. 190.
Ague, cure for, 130 ::: spiders, a cure for, 259.
A. (J. D.) on meaning of Wraxen, 366.
A. (J. T.) on Captain John Stevens, 35[...]<jupyter_text>Inspecting the above, we see there are "issues" that we might be able to address, such as line entries that should be separated, based on a closer inspection of the XML returned from the scan that includes the position on the page.But at least we have something to work with.  Parsing Entries and Adding Them to a DatabaseLet's now consider how we might structure our database entries.First, we have simple "primary" entries, such as *<NAME> charged at, 158. 190.*We might put this into a record of the form:```json[{"source_id": id_val, "index_term": "Agincourt, Sir Hilary charged at", "page": 158}{"source_id": id_val, "index_term": "Agincourt, Sir Hilary charged at", "page": 190}]```The page numbers are relative to a particular volume, so we also need to be able to capture information to identify what the page numbers are with reference to. The index document filenames take the form *Notes and Queries 1875: Vol 3 Index* so we can parse out the year and volume and add these to the record too.<jupyter_code>from parse import parse

def get_index_metadata_from_title(title):
    """Get year and volume from title."""
    metadata = parse("Notes and Queries {year}: Vol {vol} Index", title)
    if metadata:
        metadata = {"year": metadata["year"], "vol": metadata["vol"]}
    else:
        metadata = {"year": None, "vol": None}
    return metadata<jupyter_output><empty_output><jupyter_text>Here's how it works:<jupyter_code>sample_index_page_title = "Notes and Queries 1875: Vol 3 Index"

get_index_metadata_from_title(sample_index_page_title)<jupyter_output><empty_output><jupyter_text>In the table, we might also provide a `type` column to distinguish between primary (`P`) and subsidiary (`S`) entries, along with subsidiary column which should be empty in simple cases.For a line entry such as *A. (E. H.) on baptismal superstition, 197. on curfew, at Morpeth, 312. on Duresme and Dunelm, 206.* we not the the first entry is actually a subsidiary entry, the `on` keyword identifying the subsidiarity to the main term `A. (E. H.)`.We might then desire to have partial records of the form:```json[{"index_term": "A. (E. H.)", "typ": "S", "page": 197, "subsidiary": "on baptismal superstition"},{"index_term": "A. (E. H.)", "typ": "S", "page": 312, "subsidiary": "on curfew, at Morpeth"},{"index_term": "A. (E. H.)", "typ": "S", "page": 206, "subsidiary": "on Duresme and Dunelm,"}]```Inspection of other records with subsidiary terms suggests that a comma may also be used as to denote initial subsidiarity, as or example illustrated here:`Berkeley (Bishop), adventures of Gau- dentio di Lucca, 247.successful experiments, 217.`In this case, the multiple items are based on the original term before the initial comma (this might be a hasty assumption if the key term itself includes a comma, but the we might hope for an "on" separator to clarify the position.*We also note in that example a possible repair we could make to the original text: removing the `word- split` hyphenation.*<jupyter_code>def clean_text_remove_word_split_hyphenation(text):
    """Remove word split hyphenation."""
    cleaned_text = re.sub(r"([a-z])[-—–][\n]([a-z])", r'\1\2', text)
    
    return cleaned_text<jupyter_output><empty_output><jupyter_text>Let's do a quick test of that:<jupyter_code>test_eol_hyphenation = "Berkeley (Bishop), adventures of Gau-\ndentio di Lucca, 247.successful experiments, 217."

clean_text_remove_word_split_hyphenation(test_eol_hyphenation)<jupyter_output><empty_output><jupyter_text>So let's start by suggesting the following database record structure as something to work towards:<jupyter_code># If we want to remove the table completely, we can drop  it
db["index_entries"].drop(ignore=True)
db["index_entries_fts"].drop(ignore=True)

db["index_entries"].create({
    "source_id": str, 
    "year": str,
    "vol": str,
    "index_term": str, 
    "typ": str,
    "subsidiary": str,
    "page_num": int
})


# Enable full text search
# This creates an extra virtual table (books_fts) to support the full text search
db["index_entries"].enable_fts(["source_id", "index_term", "subsidiary", "year", "vol", "page_num"],
                             create_triggers=True, tokenize="porter")<jupyter_output><empty_output><jupyter_text>We now need to consider various ways of parsing line items, including:    - extracting multiple page numbers for a single entry;- identifying entries that mask subsidiary terms.We have already adopted a convention of using `:::` to separate subsidiary items, so let's apply that a bit further to separate out "on" terms and comma separated terms. We might also have a catch all in case there are elements appearing after a page number that are perhaps rightly new entries but that we shall treat as subsidiaries.We could possibly also try to "fudge" page numbers that look like numbers-ish, for eexample, if there is a set of numbers that ends with an `s` or a `z`. where we might guess (possibly incorrectly) at a `5` or `2`.<jupyter_code>def _repair_index_subsidiary_separator_line(text):
    """Repair entries at line level."""
    
    # Very risky number substitutions
    # We want to access \1 so we need the alternative syntax
    repaired_text  = re.sub(r"([0-9])[sS]\.?", r'\g<1>5', text)
    repaired_text  = re.sub(r"([0-9])[zZ]\.?", r'\g<1>2', repaired_text)
    
    # Subsidiary terms based on "on" - this may be overly aggressive to be starting with
    repaired_text  = re.sub(r"([^(on)]*)( on .*)", r'\1 ::: \2', repaired_text)
    # Subsidiary terms based on dashes at start of line
    repaired_text  = re.sub(r'^[-—–]+', r' ::: ', repaired_text)
    # Subsidiary terms based on multiple dashes within line (unlikely to be hyphen)
    repaired_text  = re.sub(r'[-—–]{2,}', r' ::: ', repaired_text)
    # Subsidiary terms based on dash after a number
    repaired_text  = re.sub(r'([0-9\.,]+\s*)[-—–]+', r'\1 :::', repaired_text)
    
    # Subsidiary terms based on page numbers
    repaired_text  = re.sub(r"([0-9]\.) *([‘“\"'\*A-Za-z])", r'\1 ::: \2', repaired_text)
    # Subsidiary terms based on "on" - this may be overly aggressive
    #repaired_text  = re.sub(r"^([^:]*)( on .*)", r'\1 ::: \2', repaired_text)
    # Or only apply after a number
    #repaired_text  = re.sub(r"([0-9]\.)\s*(on)", r'\1 ::: \2', repaired_text)
    if "::: on" in repaired_text:
        # Also split at start
        repaired_text = re.sub(r"^([^(on)]*) (on)", r"\1 ::: \2", repaired_text)
    # Subsidiary terms based on ","
    #elif ":::" in repaired_text:
    # If we have numbers separated by commas, replace the commas with a .
    repaired_text = re.sub(r'(\s+[0-9]+)\s*,\s*([0-9]+)',r'\1. \2', repaired_text)
    # If we have a comma before a number, separate after the number
    # Allow a grace character
    re.sub(r"^([^:,]*),\s*([0-9][0-9\.\s]+[A-Za-z]?)[^\n]", r'\1 \2:::', repaired_text)
    # If we have a comma appear before a separator, separate on it
    repaired_text  = re.sub(r"^([^:,]*),\s*([^0-9]+)", r'\1 :::\2', repaired_text)

    # Provide a catch all to add separators after what look like page numbers
    repaired_text  = re.sub(r"([0-9]\s*[^:].?)\s*([A-Za-z].*)$", r'\1 ::: \2', repaired_text)
    
    # Remove uncaught dashes at start and end of phrase
    repaired_text = ":::".join([p.strip("-—– ") for p in repaired_text.split(":::")])
    
    return repaired_text


def repair_index_subsidiary_separator(text):
    """Attempt to identify where subsidiary splits occur."""
    # These are applied at the line level
    repaired_lines = [_repair_index_subsidiary_separator_line(line.strip()) for line in text.split("\n") if line]
    
    # Patch back any overly aggressively split lines
    return "\n".join(repaired_lines).replace("\n:::", ":::")<jupyter_output><empty_output><jupyter_text>Let's see how that works:<jupyter_code>repaired_sample_index2 = repaired_sample_index
repaired_sample_index2 = repair_index_subsidiary_separator(repaired_sample_index2)

print(repaired_sample_index2)<jupyter_output>THE
A.
A.(A.):::on solemnization of matrimony, 4 b:::bé Strickland, 237
Abbey of St. W sndrille:::bot, <NAME>
Aboriginal chambers near Tilbury:::Achilles and the tortoise, 154. 185 346.:::dam of Bremen’s Julin, 282 443:::lAdamson's Reign of Edward I1., 297.
Admiration ! a note of:::86:::dur, origin of, 71. 108.
Earicus qui signa fundebat:::”’ 297.
A. (E. H.):::on baptismal superstition, 197.:::on curfew, at Morpeth, 312.:::on Duresme and Dunelm, 206.
Bneas Silvius:::423.
Aérostation:::works on, 199. 251. $17. 380. 469. érostation, squib:::on Lunardi, 469.
A Frog he would:::”’ &c. 45. 188 .
(F. R.):::on Dr. Magina, 109.:::on the Darby Ram,
Normandy:::199.:::of Strata Florida, 493 1,:::hoods worn by doctors of divinity 269. 2 122.::::::on Parse:::on Hoc:::on the Turkish Spy, 12.
After” (the word in the Rubric:::424. 498.
‘Agapemone:::the, 17. 49.
Agincourt:::Sir Hilary charged at, 158. 190.
Ague:::cure for, 130:::spiders, a cure for, 259.
A. (<NAME>.):::on meaning of Wraxen, 366.
[...]<jupyter_text>And for the comma separator:<jupyter_code>text_comma_subsidiary = "Berkeley (Bishop), adventures of Gau- dentio di Lucca, 247.successful experiments, 217."

repair_index_subsidiary_separator(text_comma_subsidiary)<jupyter_output><empty_output><jupyter_text>Having made an attempt at some subsidiary separators, we can now try to parse out the various components. At the start of the line we have the primary entry, then we may have one or more line numbers or one or more subsidiary phrases.Let's look at how to parse out page numbers. There may be one or more page numbers separated by spaces or by `.` characters.<jupyter_code># This is a rather crude approach that just grabs all the numbers we can find
def extract_page_numbers_from_line(text):
    """Extract one or more page numbers from text."""
    # Try to nudge things towards finding numbers at the end of the phrase
    end_of_text = re.sub(r'^[^0-9]*([0-9\.,\s]*$)', r'\1', text)
    start_of_text = text.replace(end_of_text, '')
    # Then just bludgeon out all the possible page numbers
    page_numbers = re.findall(r'\d+', end_of_text)
    return start_of_text, page_numbers<jupyter_output><empty_output><jupyter_text>Let's see how that works:<jupyter_code># Use a test example of subsidiary elements; there is no page number in the first part
[extract_page_numbers_from_line(t) for t in repair_index_subsidiary_separator(text_comma_subsidiary).split(":::")[1:]]<jupyter_output><empty_output><jupyter_text>And if there are no numbers?<jupyter_code>extract_page_numbers_from_line("No numbers here")
def parse_index_line(text):
    """Parse out elements of the index entry."""
    
    # Split the entry in subsidiary parts and clean white space
    parts = [p.strip() for p in text.split(":::")]

    # Do we have one entry or many?
    if len(parts) == 1:
        # There are no subsidiary parts
        # The first part is the main index entry
        # from which we need to separate one or more page references
        entry_text, page_numbers = extract_page_numbers_from_line(parts[0])
        index_entries = [{"index_term": entry_text, "typ": "P",
                         "page_numbers": page_numbers}]
    else:
        # There are subsidiary parts
        # In this case, we get each subsidiary part and its page references
        # Get the subsidiary parts
        index_entries = []
        for p in parts[1:]:
            entry_text, page_numbers = extract_page_numbers(p)
            subsidiary_entry = {"index_term": parts[0],
                                "subsidiary": entry_text, "typ": "S",
                                "page_numbers": page_numbers}
            index_entries.append(subsidiary_entry)

    return index_entries
parse_index_line('“ Noise" derivations of 81. 106. 138. 218. 35')<jupyter_output><empty_output><jupyter_text>So does that work?!<jupyter_code>parse_index_line(repaired_sample_index2.split("\n")[4])<jupyter_output><empty_output><jupyter_text>In the above case, we have an error in that we have rolled one index entry as a subsidiary to an initial index entry because of a missing page number for the first entry.*In this case, alphabetic sorting checks across several index entries (and subsidiaries) might help us detect this error; for example, if a subsidiary term sorts between the index term and the next index term, we might guess that the subsidiary is actually a main index term.*Note that if we construct a full text search across the `index_term` and `subsidiary` columns, we are likely to get false positives but we shouldn't miss anything... We can now try to create a complete set of records that we could upload to out database.To start with, we need the metadata, which means we need the title.<jupyter_code>def get_title_from_id(db, id_val):
    """get the title of the issue from the database."""
    q = f'SELECT title FROM metadata WHERE id="{id_val}"'

    return read_sql(q, db.conn)["title"][0]<jupyter_output><empty_output><jupyter_text>For example:<jupyter_code>index_base_data = get_index_metadata_from_title(get_title_from_id(db, sample_index_id))
index_base_data<jupyter_output><empty_output><jupyter_text>Now we need to separate each line item into multiple items. The `pandas` dataframe can come to out aid here, with its ability to easily split out listed items in one cell onto multiple rows:<jupyter_code>import pandas as pd

example_subsidiary_df = pd.DataFrame(parse_index_line(repaired_sample_index2.split("\n")[4]))
example_subsidiary_df<jupyter_output><empty_output><jupyter_text>We can now "explode" that dataframe against the lists of page numbers to get one row per item:<jupyter_code>example_subsidiary_df.explode('page_numbers').rename(columns={"page_numbers": "page_num"})<jupyter_output><empty_output><jupyter_text>Let's see if we can now put all those pieces together. Essentially, for each index line, we need to generate the complete set of records we want to add to the database.<jupyter_code>def construct_index_records(id_val=None, text=None, metadata=None, retval="explode"):
    """Generate a complete set of index records from original search text document."""
    if id_val is None and text is None:
        return []

    text = download_and_extract_text(id_val, typ="djvutxt") if text is None else text

    records = []
    # Repair the text
    repaired_text = repair_index_missing_line_breaks( text )
    repaired_text = repair_index_unwanted_line_breaks( repaired_text )
    repaired_text = repair_index_subsidiary_separator( repaired_text )

    for line in repaired_text.split("\n"):
        if line:
            new_line = parse_index_line(line)
            records.extend(new_line)
    
    if retval not in ["df", "explode"] or id_val is None:
        # Return the list of dicts, without the metadata
        return records

    # WARNING - if we used provided text, the id_val and the text may actually be inconsistent
    index_base_data = get_index_metadata_from_title(get_title_from_id(db, id_val))
    # Generate a dataframe
    records_df = pd.DataFrame(records)
    
    records_df["source_id"] = id_val
    records_df["year"] = index_base_data["year"]
    records_df["vol"] = index_base_data["vol"]
    
    if retval=="explode":
        return records_df.explode('page_numbers').rename(columns={"page_numbers": "page_num"})
    elif retval=="df":
        return records_df<jupyter_output><empty_output><jupyter_text>And when we run it:<jupyter_code>construct_index_records(sample_index_id)[1000: 1200]<jupyter_output><empty_output><jupyter_text>It's far from ideal, but at least gives us something to work with. So let's add it to the database, and see how a search feels.<jupyter_code>db["index_entries"].insert_all(construct_index_records(sample_index_id).to_dict(orient="records"))<jupyter_output><empty_output><jupyter_text>Let's try a search:<jupyter_code>search_term = "<PASSWORD>"

q = f"""
SELECT * FROM index_entries_fts
WHERE index_entries_fts MATCH {db.quote(search_term)};
"""

read_sql(q, db.conn)<jupyter_output><empty_output><jupyter_text>Let's create a search index over all the index issues up to 1892.<jupyter_code>db["index_entries"].delete_where()
db["index_entries_fts"].delete_where()

# List of indexes already loaded: indexes

for index_record in tqdm(indexes):
    index_records = construct_index_records(index_record["id"])
    db["index_entries"].insert_all( index_records.to_dict(orient="records"))<jupyter_output><empty_output><jupyter_text>And how about a search...<jupyter_code>search_term = "boggart"

q = f"""
SELECT * FROM index_entries_fts
WHERE index_entries_fts MATCH {db.quote(search_term)};
"""

read_sql(q, db.conn)[:50]<jupyter_output><empty_output><jupyter_text>Isle of <NAME>;but see also https://newwoodlesford.xyz/churches-and-chapels/rev-john-kershaw-craig/  Indexes After 1891How about the indexes *after* 1891?  Free Text Search Returning Corresponding Issue MetadataOne thing it might be useful to to is return the volume, issue and page numbers associated with particular search responses.This requires finding the issue and character index of the search term, and then looking up the character index against the page metadata.TO DO  Fuzzy Searching and Partially Matched ContentOn of the problems with the OCR search text is that the OCR process is not completely reliable, which means that the search text might include a large number of typographical errors based on misidentifying scanned letters and words. When running an *exact match* search over the search text, we are thus likely to miss large numbers of search hits in a less than perfectly scanned document.One way round this is to use search tools that detect partially matching terms (for example, *sin-eater* and *bin eater* almost match, differing only in the first letter and the space versus hyphen characters).<jupyter_code>from sqlite_utils import Database

db_name = "nq_demo.db"
db = Database(db_name)
from pandas import read_sql

search_term = ""

q = f"""
"""

read_sql(q, db.conn)<jupyter_output><empty_output><jupyter_text>Creating a Search Engine Over 19th Century Editions of *Notes & Queries*We now have all the ingredients in place to create a search engine over all editions of *Notes & Queries* from the 19th century.  Paragraph Level Chunks TableThe following demonstrates how we might generate a paragraph level table.<jupyter_code>#TO DO - these chunks can then be added to the database, along with page number data, so lets create a database table for that:

PAGE_INDEX_TABLE = "page_indexes4"

db[PAGE_INDEX_TABLE].create({
    "id": str,
    "page_text": str,
    "page_leaf_num": int, 
    "page_num": str, # should really be int
    "page_num_conf": float
}, pk=("id", "page_leaf_num"))

# Enable full text search
# This creates an extra virtual table (books_fts) to support the full text search
db[PAGE_INDEX_TABLE].enable_fts(["id", "page_leaf_num", "page_text"], create_triggers=True, tokenize="porter")<jupyter_output><empty_output><jupyter_text>We also need a recipe for iterating through the records,<jupyter_code>for record in tqdm(indexes+indexes2):
    id_val = record['id']
    _texts = chunk_text(id_val) 

We should now be able to search at a page level:

q3 = '"sin eater"'

_q = f"""
SELECT id, page_leaf_num
FROM page_indexes4_fts WHERE page_indexes4_fts MATCH {db.quote(q3)} ;
"""

for row in db.query(_q):
    print(row['id'], row["page_leaf_num"])
for c in data_records:
    # Only look at index records
    if 'index' in c['id']:
        # Need to handle a YYYY - YYYY exception
        # If we detect it, ignore it
        if len(c['date'].split()) > 1:
               continue
        
        # Parse the year into a date object
        # Then filter by year
        if dateparser.parse(c['date'].split()[0]).year >= maxyear:
            break
        indexes.append(c)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08799179902418769, 'fraction_numerical': 0.015182186234817813, 'mean_word_length': 4.682002506820025, 'pattern_counts': {'":': 80, '<': 396, '<?xml version=': 0, '>': 402, 'https://': 6, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 59, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '97', 'dir': 'jupyter-structured-clean-dedup', 'id': '408957', 'license': 'MIT', 'n_tokens_mistral': 23972, 'n_tokens_neox': 22383, 'n_words': 9219, 'path': 'Notes and Queries DB-ORIGI.ipynb', 'repo_name': 'psychemedia/storynotes', 'size': '77115'}