ENIP Template

| Field | Value |
| --- | --- |
| ENIP | 3 |
| Title | Local SQLite database for on-demand metadata storage |
| Author | Miguel Molina |
| Status | Draft |
| Created | 2017-11-27 |
| Updated | 2017-11-27 |
| Target version | n/a |

## Abstract

Engine will generate a SQLite database on each node that will keep itself updated on-demand as repositories get to that node, which removes the need for a two steps process, making it a one step process.
The JDBC datasource will be replaced with a custom datasource of our own that will take care of squashing all the joins between tables just as we already do with the git datasource and send to the database a join statement already built, as opposed to retrieving all data and joining later on in Spark.
Said datasource will need to be chainable with the `BlobIterator` so all the blobs don't need to be retrieved for a query, which is what slows us down right now with this approach.

## Rationale

Future-wise, engine needs a way to access random repositories in a fast way without doing a full scan of all repositories. For that, we need a database which stores all the metadata in a repository (all data except for the blobs themselves). For that to be performant, though, we need to move away from using the default JDBC data source, as the joins between it and the git data source make the querying of blobs really slow.

## Specification

There needs to be a SQLite database per node, which would be initialized the very moment the job is executed. Since database is local and does not need authentication, there is no need for passing credentials and/or configuration other than the default path to look for the database to the nodes. If a job does not find the database, it will be created in that moment.

To make use of this SQLite database, we need another datasource of our own, `MetadataDataSource`. This datasource is needed because of some limitations using plain JDBC datasource:

- Does not allow chaining with the `BlobIterator`. The only reason we can do the chaining in the iterators is because we're generating the data and squashing all the joins into a single step-by-step process. With JDBC there is no way to embed that, as data is generated by the JDBC driver and not us.
- Does the joining directly in Spark instead of doing it inside the database itself, which may hurt performance severely on large quantities of repositories.

With `MetadataDataSource` we can have a rule that squashes `MetadataRelation`s into one, just as we already do with `GitRelation`s, construct a query by ourselves with the joinin already done and then send it to SQLite for it to retrieve the data.

`BlobIterator` needs to be changed so it can accept either a `TreeEntriesIterator` or a metadata relation depending on how it's invoked.

First step would be to make this a two steps process: first putting everything inside the metadata database and then running the actual queries on the engine. Then, on top of that, on-demand capabilities would be added as a second-step to remove the need for another step previous to the execution.

Since `BlobIterator` is going to keep needing access to the `RepositorySource` with the repository that is going to be read, it is obvious we need to store the path of the repository in the repositories table. Although this is not a very useful thing to display to the user, it might come in handy to have it as part of the `repositories` schema.

The schema of the `repositories` table would look like this afterwards:

```
id | urls | is_fork | path |
```

Then storing the metadata in a database becomes really trivial.

To make the `BlobIterator` access the `RepositorySource` and the chained `MetadataRelation` we need an RDD of rows from the database and join it with the RDD of `RepositorySource`s we have by their `path`.
Easiest way, but maybe not the most optimal would be:

```scala
val df1 = rdd1.toDF
val df2 = rdd2.toDF

val result = df1.join(df2, df1("path") <==> df2("path")).rdd
```

`MetadataRelation` would basically be a query builder and an iterator to loop over all the results returned by the database using the corresponding JDBC driver (SQLite in this case), that only chains itself with the regular `BlobIterator` in case blobs are requested.

Sadly, most of JDBC datasource code (if any) can't be reused, because it does not deal with `Expression`s in the filters but `Filter`s, but it's code can be a reference for the implementation of our `MetadataRelation`.

To add "on-demand" capabilities we need another extra table containing the status in which every repository or siva file is, that is, containing its `stat`. That way, we can know when a repository or siva file has changed and rebuild the cache accordingly, either by erasing and building again or doing a more sophisticated process that diffs changes and inserts/deletes only what is necessary.

At this point, iterators (either from `GitRelation` or `MetadataRelation`) need to store all the data of a repository in the database before processing it (or diff and act accordingly if it's an outdated repository that already exists in the database).

## Alternatives

Implementing our own data source is crucial to be able to make it chainable with the `BlobIterator` and make it performant, so is the only way forward if we want to put a metadata cache between the repositories and the engine.

Regarding the SQLite choice, it could very well be any other kind of relational database, but the fact that SQLite is in each node and has locality advantage made us consider this option as the better approach.

## Impact

If on-demand functionality is implemented, this should have no impact on the user. API would remain exactly the same. The only possible user-facing change that could be made is adding a configuration setting to change where the database is stored, but having a default location would make it transparent to the user as well unless they actively want to change it. Also, the fact that is a SQLite and not any other kind of database makes it easier on the user (specially the local user), because it does not need any external dependencies such as a certain database installed in order to use the engine.

Data nodes should have enough disk space to store all the metadata that repositories may generate, which could be huge if we're talking millions of repositories.

## References

- [JDBCRDD in Spark](https://github.com/apache/spark/blob/master/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/jdbc/JDBCRDD.scala)
- [JDBCUtils in Spark](https://github.com/apache/spark/blob/master/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/jdbc/JdbcUtils.scala)
