orotool/README.md

# orotool #

Your Origins RO API client

## Purpose ##

Work in progress

## Licence ##

orotool is released under the GPL3+

## Compiling the project ##

To compile orotool you will need:

* [meson](https://mesonbuild.com/)
* [gcc](https://gcc.gnu.org/) with C++17 support

### Dependencies ###

* [lzma](https://tukaani.org/xz/) (optional)
* [curlcpp](https://github.com/JosephP91/curlcpp) (compulsory at the moment, bundled)
* curl (compulsory at the moment)
* libev (compulsory)
* [boost](https://www.boost.org/) (compulsory)
* [simdjson](https://github.com/simdjson/simdjson) (compulsory at the moment, bundled)
* [restc-cpp](https://github.com/jgaa/restc-cpp) (optional, bundled, currently broken)
* [RapidJSON](https://rapidjson.org/) (optional, currently broken)
* [SQLiteCpp](https://srombauts.github.io/SQLiteCpp/) (compulsory at the moment, bundled)
* SQLite (compulsory at the moment)
* [date](https://howardhinnant.github.io/date/date.html) (compulsory, bundled)
* [duckhandy](https://alarmpi.no-ip.org/gitan//King_DuckZ/duckhandy/) (compulsory, bundled)
* [sprout](https://github.com/bolero-MURAKAMI/Sprout) (compulsory)
* [magic_enum](https://github.com/Neargye/magic_enum/) (compulsory, bundled)
* [gnulib](https://git.savannah.gnu.org/gitweb/?p=gnulib.git) (compulsory, bundled)

### Building ###

1. download the source code
2. create an empty directory
3. cd into your new directory and run `meson setup -Dbuildtype=release <path_to_orotool_source>`
4. compile with the command `ninja`
5. if compilation succeeds, you will find the binary inside the src directory
6. run `ninja install` to copy files into your system install location

You can change the command at step 3 to customise certain aspects of orotool.
You can for example specify `--sysconfdir <path>` for the path where `orotool.conf`
should be found in. `-Dprefix` will change the install prefix. For example:

```
meson setup -Dprefix=/usr -Dsysconfdir=/etc -Dbuildtype=release ~/dev/orotool/
```

Other options can be found in `meson_options.txt`. In meson you specify options
with the `-Doption` syntax to the setup subcommand, just like `-Dbuildtype`.
For example `-Dbase_url`.

For options of type `feature` (see inside `meson_options.txt`) possible values
are: `enabled`, `disabled`, `auto`. A short description of each:

* `enabled` the feature must be compiled in, configuration will fail if dependencies are missing
* `disabled` the feature is disabled
* `auto` the feature will only be compiled in if the required dependencies are present on the system

For more informations please read the meson [documentation](https://mesonbuild.com/Build-options.html#features).

Details about supported options:

* `base_url` allows you to change the url of the remote API address (mostly useful when debugging)
* `def_sqlite_db_name` this is the default value for the `db_path` option in the config file
* `tests` enable building tests (currently it only has effect on some subprojects)
* `with_sqlite` if you want to compile with SQLite support (this is the only available backend at the moment)
* `rest_lib` which method will be used to access the remote API. `nap` is the internal one (curl+simdjson). `restc-cpp` is currently broken due to a [bug](https://github.com/jgaa/restc-cpp/issues/101) upstream
* `with_lzma` adds support for JSON logs compression (option `json_store_mode=xz` in the config file)

### Debug build ###

If you want to work on orotool I suggest the following configuration (assuming ~/dev/orotool is where the source code is):

```
meson setup -Dbase_url=http://localhost:8080 --sysconfdir ~/dev/orotool -Dbuildtype=debug ~/dev/orotool
```

This will cause the config file in your source tree to be used by orotool, so you
will have to enter an API key there. It doesn't have to be valid since you're
not connecting to the real server, but it should be still recognised by the
program as valid. Try a dummy value like aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa for example.
`ninja install` is still safe to run.

It will also set localhost as the url to the rest API. You can install and run
[SoapUI](https://www.soapui.org/) to make your computer respond on localhost:8080.
You can use the provided `sample_responses/originsro.xml` file with SoapUI.

After compiling you can run `ln -s src/orotool` in the build directory to make a
symlink to the binary. You can then run the program with `./orotool`. When testing
locally I usually run this command in just one line: `rm -fv originsro.db3 ; ninja && ./orotool`.
It will delete the SQLite database if present, compile if needed and then run orotool.

## Configuration ##

You can customise certain runtime aspects of orotool in `orotool.conf` (location
of this file depends on your build configuration). The file provided in the repository
has some sane defaults except for the API key which you have to set yourself. To
obtain an API key login to your OriginsRO control panel and generate a key [here](https://cp.originsro.org/masteraccount/view/).

## Contact ##

The best way to get in touch is on IRC:

* *Rizon* - irc.rizon.net:6697 - `King_DuckZ` - #OriginsRO
* *Freenode* - chat.freenode.net:6697 - `King_DuckZ`

Other ways you can get in touch:

* Matrix: `@duckz:alarmpi.no-ip.org`
* XMPP: `king_duckz@jabber.at`
* Discord: `DuckZ#0896`

## Recipes ##

This is a collection of tricks, commands and SQL queries that you might find helpful.

### Exporting JSON to file ###

If you saved JSON data to the database in compressed format (`store_raw_json=true` and `json_store_mode=xz`) you can export them to separate files with the following bash command (assuming current directory contains a originsro.db3 DB):

```
mkdir -pv split_json
cd split_json

digits=0000; n=0; while IFS= read -r line; do \
base64 --decode <<<"$line" | unxz --decompress > output"${digits:${#n}:${#digits}}${n}".txt; \
(( n++ )); \
done < <(sqlite3 ../debug/originsro.db3 'select source from source_store where format=2')
```

This will not export plain uncompressed JSON (`format=1`)

### Getting min/max price of each item on the market now ###

With this query you can retrieve the min and max price of each item currently being sold (assuming database is up to date), along with their names, the name of the shop selling them and the total count of items of that type across all shops and all prices. Note that refined equipment or equipment with cards are considered to be all equivalent to the plain item.

```
SELECT title, name, MIN(price) AS min_price, MAX(price) AS max_price, SUM(amount) AS amount FROM (
SELECT shops.title, items.name, shop_items.price, items.item_id,
	shop_items.amount
	FROM shops
	LEFT JOIN shop_snapshots ON shop_snapshots.shop_id=shops.id
	LEFT JOIN shop_items ON shop_items.snapshot_id=shop_snapshots.id
	LEFT JOIN items ON shop_items.item_id=items.id
	WHERE shops.type=1 AND
		shop_snapshots.last_seen_date=(SELECT max(last_seen_date) FROM shop_snapshots)
) GROUP BY item_id
```

"Currently open" means shops seen on the same date the most recent shop was seen. In my current case my database was last updated 6 hours ago, this means I'm seeing the list of items that were being sold 6 hours ago.

### Estimate JSON storage size ###

This returns an estimate in MiB of the total storage occupied by the saved JSON data

```
SELECT ROUND(CAST(SUM(pgsize) AS REAL)/(1024.0*1024.0), 2) FROM dbstat WHERE name='source_store'
```

### Deleting JSON logs ###

You can delete the stored JSON whenever you want. It is adviced that you also set all `source_id` references to 0 in order to avoid wrong references to the new JSON logs getting stored in the future:

```
UPDATE items SET source_id=0;
UPDATE fame_list SET source_id=0;
UPDATE icons SET source_id=0;
UPDATE shop_snapshots SET source_id=0;
DROP TABLE IF EXISTS source_store;
VACUUM;
```