Lars Willighagen

The [CDLI](https://cdli.ucla.edu/) is developing a [new
website](https://cdli-dev.mpiwg-berlin.mpg.de/). That website's admin
interface for its journals contains a page where a LaTeX source file,
following a specific template, is configured to an HTML page. For this,
apart from the LaTeX file itself, two additional components are needed:
a BibTeX file, containing metadata of the references; and image files.

### Current implementation

The current implementation involves uploading images separately from the
form that creates the article. This can lead to "orphan" images if such
a form is abandoned after uploading the images. The current
implementation has an additional problem, where files are saved in a
single directory, so multiple images with the same file name (say,
`Figure1.jpg`) will overwrite previous images.

``` language-mermaid
#mermaid-svg-zuMp4V2cXsJ0PnCG{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#000000;}#mermaid-svg-zuMp4V2cXsJ0PnCG .error-icon{fill:#552222;}#mermaid-svg-zuMp4V2cXsJ0PnCG .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-zuMp4V2cXsJ0PnCG .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-zuMp4V2cXsJ0PnCG .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-zuMp4V2cXsJ0PnCG .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-zuMp4V2cXsJ0PnCG .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-zuMp4V2cXsJ0PnCG .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-zuMp4V2cXsJ0PnCG .marker{fill:#666;stroke:#666;}#mermaid-svg-zuMp4V2cXsJ0PnCG .marker.cross{stroke:#666;}#mermaid-svg-zuMp4V2cXsJ0PnCG svg{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-zuMp4V2cXsJ0PnCG .actor{stroke:hsl(0,0%,83%);fill:#eee;}#mermaid-svg-zuMp4V2cXsJ0PnCG text.actor > tspan{fill:#333;stroke:none;}#mermaid-svg-zuMp4V2cXsJ0PnCG .actor-line{stroke:#666;}#mermaid-svg-zuMp4V2cXsJ0PnCG .messageLine0{stroke-width:1.5;stroke-dasharray:none;stroke:#333;}#mermaid-svg-zuMp4V2cXsJ0PnCG .messageLine1{stroke-width:1.5;stroke-dasharray:2,2;stroke:#333;}#mermaid-svg-zuMp4V2cXsJ0PnCG #arrowhead path{fill:#333;stroke:#333;}#mermaid-svg-zuMp4V2cXsJ0PnCG .sequenceNumber{fill:white;}#mermaid-svg-zuMp4V2cXsJ0PnCG #sequencenumber{fill:#333;}#mermaid-svg-zuMp4V2cXsJ0PnCG #crosshead path{fill:#333;stroke:#333;}#mermaid-svg-zuMp4V2cXsJ0PnCG .messageText{fill:#333;stroke:#333;}#mermaid-svg-zuMp4V2cXsJ0PnCG .labelBox{stroke:hsl(0,0%,83%);fill:#eee;}#mermaid-svg-zuMp4V2cXsJ0PnCG .labelText,#mermaid-svg-zuMp4V2cXsJ0PnCG .labelText > tspan{fill:#333;stroke:none;}#mermaid-svg-zuMp4V2cXsJ0PnCG .loopText,#mermaid-svg-zuMp4V2cXsJ0PnCG .loopText > tspan{fill:#333;stroke:none;}#mermaid-svg-zuMp4V2cXsJ0PnCG .loopLine{stroke-width:2px;stroke-dasharray:2,2;stroke:hsl(0,0%,83%);fill:hsl(0,0%,83%);}#mermaid-svg-zuMp4V2cXsJ0PnCG .note{stroke:hsl(60,100%,23.3333333333%);fill:#ffa;}#mermaid-svg-zuMp4V2cXsJ0PnCG .noteText,#mermaid-svg-zuMp4V2cXsJ0PnCG .noteText > tspan{fill:#333;stroke:none;}#mermaid-svg-zuMp4V2cXsJ0PnCG .activation0{fill:#f4f4f4;stroke:#666;}#mermaid-svg-zuMp4V2cXsJ0PnCG .activation1{fill:#f4f4f4;stroke:#666;}#mermaid-svg-zuMp4V2cXsJ0PnCG .activation2{fill:#f4f4f4;stroke:#666;}#mermaid-svg-zuMp4V2cXsJ0PnCG:root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}#mermaid-svg-zuMp4V2cXsJ0PnCG sequence{fill:apa;}ClientServerGET /admin/articles/add/cdlj200 (add page)POST /admin/articles/convert-latex200 (converted HTML, list of images)POST /admin/articles/image 'Figure1.jpg'Saves image in 'Figure1.jpg'200POST /admin/articles/add/cdlj300 /articles/ClientServer
```

### New implementation

With a new implementation of the rest of the forms, simplifying a lot of
the code, the issues with the image uploads are also attempted to be
resolved.

#### Saving images together with the metadata

To avoid the problem of the "orphan" images resulting from abandoned
forms, one could submit the images in the same form that creates the
article. If that form is not submitted, or contains invalid data so
cannot be saved, the images will not be uploaded.

#### Saving images according to metadata

The second problem could be solved immediately by saving the images in
subdirectories according to the metadata of the image, e.g. `2021-01/`
where `2021` is the year the article is published and `01` is the
article sequence number within that year. This however assumes that that
metadata does not change after the initial submission.

Both these solutions however creates some constraints on other problems,
because it means that the images can only be saved *after* the user
submits the main form, so *after* the HTML containing

elements with references to the image locations is generated. Somehow,
those image locations should be able to identify the article, *before*
any information about that article is known:

``` language-mermaid
#mermaid-svg-6CZ5geXR2BJYqtFv{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#000000;}#mermaid-svg-6CZ5geXR2BJYqtFv .error-icon{fill:#552222;}#mermaid-svg-6CZ5geXR2BJYqtFv .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-6CZ5geXR2BJYqtFv .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-6CZ5geXR2BJYqtFv .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-6CZ5geXR2BJYqtFv .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-6CZ5geXR2BJYqtFv .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-6CZ5geXR2BJYqtFv .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-6CZ5geXR2BJYqtFv .marker{fill:#666;stroke:#666;}#mermaid-svg-6CZ5geXR2BJYqtFv .marker.cross{stroke:#666;}#mermaid-svg-6CZ5geXR2BJYqtFv svg{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-6CZ5geXR2BJYqtFv .actor{stroke:hsl(0,0%,83%);fill:#eee;}#mermaid-svg-6CZ5geXR2BJYqtFv text.actor > tspan{fill:#333;stroke:none;}#mermaid-svg-6CZ5geXR2BJYqtFv .actor-line{stroke:#666;}#mermaid-svg-6CZ5geXR2BJYqtFv .messageLine0{stroke-width:1.5;stroke-dasharray:none;stroke:#333;}#mermaid-svg-6CZ5geXR2BJYqtFv .messageLine1{stroke-width:1.5;stroke-dasharray:2,2;stroke:#333;}#mermaid-svg-6CZ5geXR2BJYqtFv #arrowhead path{fill:#333;stroke:#333;}#mermaid-svg-6CZ5geXR2BJYqtFv .sequenceNumber{fill:white;}#mermaid-svg-6CZ5geXR2BJYqtFv #sequencenumber{fill:#333;}#mermaid-svg-6CZ5geXR2BJYqtFv #crosshead path{fill:#333;stroke:#333;}#mermaid-svg-6CZ5geXR2BJYqtFv .messageText{fill:#333;stroke:#333;}#mermaid-svg-6CZ5geXR2BJYqtFv .labelBox{stroke:hsl(0,0%,83%);fill:#eee;}#mermaid-svg-6CZ5geXR2BJYqtFv .labelText,#mermaid-svg-6CZ5geXR2BJYqtFv .labelText > tspan{fill:#333;stroke:none;}#mermaid-svg-6CZ5geXR2BJYqtFv .loopText,#mermaid-svg-6CZ5geXR2BJYqtFv .loopText > tspan{fill:#333;stroke:none;}#mermaid-svg-6CZ5geXR2BJYqtFv .loopLine{stroke-width:2px;stroke-dasharray:2,2;stroke:hsl(0,0%,83%);fill:hsl(0,0%,83%);}#mermaid-svg-6CZ5geXR2BJYqtFv .note{stroke:hsl(60,100%,23.3333333333%);fill:#ffa;}#mermaid-svg-6CZ5geXR2BJYqtFv .noteText,#mermaid-svg-6CZ5geXR2BJYqtFv .noteText > tspan{fill:#333;stroke:none;}#mermaid-svg-6CZ5geXR2BJYqtFv .activation0{fill:#f4f4f4;stroke:#666;}#mermaid-svg-6CZ5geXR2BJYqtFv .activation1{fill:#f4f4f4;stroke:#666;}#mermaid-svg-6CZ5geXR2BJYqtFv .activation2{fill:#f4f4f4;stroke:#666;}#mermaid-svg-6CZ5geXR2BJYqtFv:root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}#mermaid-svg-6CZ5geXR2BJYqtFv sequence{fill:apa;}ClientServerGET /admin/articles/add/cdlj200 (add page)POST /admin/articles/convert-latexAt this point the HTMLshould contain linksto the permanent locations of the images200 (converted HTML, list of images)POST /admin/articles/add/cdljAt this pointthe images are savedin a permanent location300 /articles/ClientServer
```

So, what is the solution? I propose the following:

``` language-mermaid
#mermaid-svg-OR3KFJWzCHNmFnJn{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#000000;}#mermaid-svg-OR3KFJWzCHNmFnJn .error-icon{fill:#552222;}#mermaid-svg-OR3KFJWzCHNmFnJn .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-OR3KFJWzCHNmFnJn .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-OR3KFJWzCHNmFnJn .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-OR3KFJWzCHNmFnJn .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-OR3KFJWzCHNmFnJn .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-OR3KFJWzCHNmFnJn .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-OR3KFJWzCHNmFnJn .marker{fill:#666;stroke:#666;}#mermaid-svg-OR3KFJWzCHNmFnJn .marker.cross{stroke:#666;}#mermaid-svg-OR3KFJWzCHNmFnJn svg{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-OR3KFJWzCHNmFnJn .actor{stroke:hsl(0,0%,83%);fill:#eee;}#mermaid-svg-OR3KFJWzCHNmFnJn text.actor > tspan{fill:#333;stroke:none;}#mermaid-svg-OR3KFJWzCHNmFnJn .actor-line{stroke:#666;}#mermaid-svg-OR3KFJWzCHNmFnJn .messageLine0{stroke-width:1.5;stroke-dasharray:none;stroke:#333;}#mermaid-svg-OR3KFJWzCHNmFnJn .messageLine1{stroke-width:1.5;stroke-dasharray:2,2;stroke:#333;}#mermaid-svg-OR3KFJWzCHNmFnJn #arrowhead path{fill:#333;stroke:#333;}#mermaid-svg-OR3KFJWzCHNmFnJn .sequenceNumber{fill:white;}#mermaid-svg-OR3KFJWzCHNmFnJn #sequencenumber{fill:#333;}#mermaid-svg-OR3KFJWzCHNmFnJn #crosshead path{fill:#333;stroke:#333;}#mermaid-svg-OR3KFJWzCHNmFnJn .messageText{fill:#333;stroke:#333;}#mermaid-svg-OR3KFJWzCHNmFnJn .labelBox{stroke:hsl(0,0%,83%);fill:#eee;}#mermaid-svg-OR3KFJWzCHNmFnJn .labelText,#mermaid-svg-OR3KFJWzCHNmFnJn .labelText > tspan{fill:#333;stroke:none;}#mermaid-svg-OR3KFJWzCHNmFnJn .loopText,#mermaid-svg-OR3KFJWzCHNmFnJn .loopText > tspan{fill:#333;stroke:none;}#mermaid-svg-OR3KFJWzCHNmFnJn .loopLine{stroke-width:2px;stroke-dasharray:2,2;stroke:hsl(0,0%,83%);fill:hsl(0,0%,83%);}#mermaid-svg-OR3KFJWzCHNmFnJn .note{stroke:hsl(60,100%,23.3333333333%);fill:#ffa;}#mermaid-svg-OR3KFJWzCHNmFnJn .noteText,#mermaid-svg-OR3KFJWzCHNmFnJn .noteText > tspan{fill:#333;stroke:none;}#mermaid-svg-OR3KFJWzCHNmFnJn .activation0{fill:#f4f4f4;stroke:#666;}#mermaid-svg-OR3KFJWzCHNmFnJn .activation1{fill:#f4f4f4;stroke:#666;}#mermaid-svg-OR3KFJWzCHNmFnJn .activation2{fill:#f4f4f4;stroke:#666;}#mermaid-svg-OR3KFJWzCHNmFnJn:root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}#mermaid-svg-OR3KFJWzCHNmFnJn sequence{fill:apa;}ClientServerGET /admin/articles/add/cdljGenerate random article ID200 (add page with embedded article id)POST /admin/articles/convert-latexGenerate image URLSaccording torandom article ID200 (converted HTML, list of images)POST /admin/articles/add/cdljVerify random article ID,save images,generate sequential ID300 /articles/ClientServer
```


The CDLI is developing a new website. That website’s admin interface for its journals contains a page where a LaTeX source file, following a specific template, is configured to an HTML page. For this, apart from the LaTeX file itself, two additional components are needed: a BibTeX file, containing metadata of the references; and image files.

Re-implementing the upload of images for the LaTeX→HTML converter

Since [Google Summer of Code](https://summerofcode.withgoogle.com/) 2020
I have been contributing code to the new framework of the [Cuneiform
Digital Library Initiative](https://cdli.mpiwg-berlin.mpg.de/) (CDLI), a
digital repository for metadata, transliterations, images, and other
data of cuneiform inscriptions, as well as tools to work with that data.

One of the features of the new CDLI framework is improved editing of
artifact metadata, as well as inscriptions. There are several ways
artifacts will be able to be edited: by uploading metadata in a
CSV-format, by batch edits in the website interface, and by editing
individual artifacts. At the basis of all those pathways is the database
representation of individual revisions. To evaluate the planned
representation, and to see if alternatives are possible, I took a look
at the catalogue metadata (CDLI 2021), and what edits are being made
currently.

### Artifact registrations

![](https://1.bp.blogspot.com/-Wmmc4zWTVu0/YGJSQNJ_-SI/AAAAAAAAuyE/pwudg-SVFaI4FAQqlEbJsBMuVVcY4vYxACLcBGAsYHQ/s0/collection_year2.png)  
***Figure 1: The number of artifacts registered each year, split by the
collection they are in.** Collections with less than 7000 artifacts
represented are combined into the "Other" category to keep the legend
manageable. 13K artifacts without valid creation dates were excluded.*

First, I took a quick look at the composition of the catalogue (Fig. 1).
As it turns out some collections had most of their (included) artifacts
added in a single year, such as the University of Pennsylvania Museum of
Archaeology and Anthropology in 2005 and the Anadolu Medeniyetleri
Müzesi in 2012. Other collections seemed to have had a more steady flows
of artifact entries, particularly the British Museum. Overall, this does
not help much with selecting a database representation of revisions
though.

One of the options we want to evaluate is storing revisions in a linked
format, similarly to how artifacts are stored now, instead of a flat
format. This means that if each of those artifacts has about 10 links in
total --- say 1 material, 1 genre, 1 holding collection, 1 language, 1
associated composite, and 5 publications --- each revision would need 10
rows for the links and 1 row for the rest of the entry. Therefore, the
question is: are 11 rows per revision manageable for 343,371 active
artifacts?

### Revisions

To find out, let's take a look at the daily updates of the catalogue
data. With the git history, we can find out how many artifacts were
edited on each day. Since the commits are made daily multiple
consecutive edits to the same artifact are counted as a single revision.
On the other hand, the removal of an artifact from the catalogue might
be counted as a revision. Whether that balances out is hard to tell, so
these numbers are a rough estimate. The analysis only goes back to 2017
unfortunately, as before that the catalogue was included as a ZIP file.

![](https://1.bp.blogspot.com/-cUbyYip36v4/YGJSR2ar-iI/AAAAAAAAuyI/kP-JfpOVGhMGQ3G0ETf3xaM9VC9fNvRpACLcBGAsYHQ/s0/figure.png)  
***Figure 2: Number of artifact revisions per year.** The 12 largest
revisions are highlighted and explained below.*

Figure 2 highlights in various colors the 12 revisions affecting the
highest number of artifacts. Most of these are 7 consecutive in October
and November of 2017. These involved editing the ID column, something
which should not happen in the current system. Other large revision
usually affected a single column, thereby revising almost every
artifact:

- Padding designation numbers with leading zeroes ("CDLI Lexical 000030,
  ex. 13" → "013"):
  [2020-02-11](https://github.com/cdli-gh/data/commit/8bb9f40470e3d9650b40720e86ba2884bb37667f),
  [2018-01-26](https://github.com/cdli-gh/data/commit/b469b6167b12b8fc266b6ebcd699dc86185cfa8d)
- Addition of new columns:
  [2019-09-10](https://github.com/cdli-gh/data/commit/84aa2655bfd70b0c2cce868cee47c47e697cfb84)
- New default values (\"\" → "no translation" for the column
  `translation_source`):
  [2018-10-30](https://github.com/cdli-gh/data/commit/3cf96f26440ea99a400582bb25925e59f41f8b81)

Outside the top 12 the edits become a lot more meaningful. Often,
credits for new transliterations or translations are added, sometimes
with the previously undetermined language now specified.

As it turns out, approximately 3 million edits are made every year. If
all those edits are stored as linked entities, we are looking at 30
million table rows, *per year*. However, even if the edits are stored in
a flat format there would be 3 million table rows per year already.
Either option might become a problem in 5--10 years, depending on SQL
performance. With indexing it might not be a problem at all: usually the
only query is by identifier anyway.

### Changed fields

That said, let's presume I choose the flat format. Most revisions only
change one or two fields (excluding the modification date which would
not be included). Duplicating the whole row might be wasteful, so what
could I do to avoid that?

Since the flat format is based on the schema of the CSV files used for
batch edits, each column can be considered as text, with an empty string
for empty values. This leaves `NULL` (i.e. "no value") available to
represent "no change". Together with MySQL's `SPARSE` columns only
edited fields would be stored. (Otherwise, each empty value would need
to be signified as such. Now, actual values carry some extra information
to the same end.)

It would also make it even easier to display a list of changes, as there
is no need to compare the value with the previous one. Other operations
with revisions, such as merging two revisions made simultaneously on the
same version of an artifact, would be easier for the same reason.

Since this would not be possible, or not as easy, with a linked format
perhaps it was good the shear volume of edits pointed me that way
anyway.

### References

Cuneiform Digital Library Initiative. (2021, March 8). Monthly release
2021.03 (Version 2021.03). Zenodo.
<http://doi.org/10.5281/zenodo.4588551>


Since Google Summer of Code 2020 I have been contributing code to the new framework of the Cuneiform Digital Library Initiative (CDLI), a digital repository for metadata, transliterations, images, and other data of cuneiform inscriptions, as well as tools to work with that data. One of the features of the new CDLI framework is improved editing of artifact metadata, as well as inscriptions.

Publicaciones de Rogue Scholar

Re-implementing the upload of images for the LaTeX→HTML converter

CDLI catalogue growth over time