# Local database

One of the ways to get URL categories is to use the local database provided by SafeDNS. This database is getting updates on a daily basis and therefore the database always contains actual information about websites on the Internet. This database is currently supplied in sqlite3.

---

#### Updating the database

The database is provided in two sets: a binary file (sqlite3 base) and a patch (SQL constructions to bring the existing sqlite3 database to the actual state).

Here is the source to download the database in a binary file:  
`https://url2cat.safedns.com/pubfilter/grandbase.db`

Here is the source to download the database in patches:  
`https://url2cat.safedns.com/api/v1/update/<user_version>`  
Where `<user_version>` is the PRAGMA parameter of the current database version. Inside the resulting patch, the first line indicates the new version of this parameter, and if it matches the requested version, no update is required.

You can check the current version of the database (PRAGMA parameter user\_version) using the command:

```shell
xxd -l 4 -s 60 grandbase.db
```

<div class="CodeMirror cm-s-default" id="bkmrk--0" translate="no"><div class="CodeMirror-copy"><svg height="16" viewbox="0 0 24 24" width="16" xmlns="http://www.w3.org/2000/svg"></svg>  
</div></div>The database can be updated by sending a GET request to the specified source with BASIC authorization parameters.

---

#### Database description

The database contains two tables:

1. Table result
2. Table cat

##### Table result

The table result contains hashed URL entries and their categories.

Table scheme:

<table border="1" id="bkmrk-name-data-domain_has"><tbody><tr><td>Name</td><td>Data</td></tr><tr><td>domain\_hash</td><td>Hash of a domain</td></tr><tr><td>path\_hash</td><td>Hash of a path</td></tr><tr><td>cat\_id</td><td>Categories list</td></tr></tbody></table>

With a primary key on the fields `domain_hash`, `path_hash`.

The data in the `cat_id` field is stored as a blob array to standardize the enumerated type, where each category is stored as an unsigned short.

##### Table cat

The table **cat** contains a list of entries with category names and identifiers.  
Depending on customer requirements, the SafeDNS Octo database can be supplied with a different number of categories with more detailed categorization or unique category names.

Table scheme:

<table border="1" id="bkmrk-name-data-locale-loc"><tbody><tr><td>Name</td><td>Data</td></tr><tr><td>locale</td><td>localization identifier</td></tr><tr><td>cat\_id</td><td>category identifier</td></tr><tr><td>name</td><td>category name</td></tr></tbody></table>

With a primary composite key `locale`, `cat_id`

<div class="pointer-container" id="bkmrk-%C2%A0"><div class="pointer anim is-page-editable"><svg class="svg-icon" data-icon="link" role="presentation" viewbox="0 0 24 24" xmlns="http://www.w3.org/2000/svg"></svg><div class="input-group inline block"> <button class="button outline icon" data-clipboard-target="#pointer-url" title="Copy Link" type="button"><svg class="svg-icon" data-icon="copy" role="presentation" viewbox="0 0 24 24" xmlns="http://www.w3.org/2000/svg"></svg></button></div><svg class="svg-icon" data-icon="edit" role="presentation" viewbox="0 0 24 24" xmlns="http://www.w3.org/2000/svg"></svg></div></div>Identifiers from the field `cat_id` of the "result" table are the foreign key to this table. The field `locale` contains the localization identifier of the language in which is written the name of the category in the name field.  
The `cat_id` field contains the numerical category identifier, the `cat_id` data is not sequential. The name field contains localized category names.