Optimise manifest database

[open768]

The database contains thousands of duplicate strings for mission, sol, instrument, type.

The database would be a lot smaller if integer indexes were used

[open768]

look at https://www.doctrine-project.org/

[open768]

look at https://meekro.com/ for database abstraction

[open768]

meekro doesnt perform cacheing by itself, but can be used alongside stash

Sure! To achieve this, you can modify the code to ensure that read operations fetch data from the cache, and only write operations interact with the database. Here’s how you can adjust the code:

Install Stash (if not already done): composer require tedivm/stash

Configure Stash:

PHP

use Stash\Driver\FileSystem;
use Stash\Pool;

$driver = new FileSystem(['path' => '/path/to/cache']);
$pool = new Pool($driver);

AI-generated code. Review and use carefully. More info on FAQ. Modify MeekroORM to use caching: PHP

class MyORM extends MeekroORM {
    protected static $cache;

    public static function setCache(Pool $cachePool) {
        self::$cache = $cachePool;
    }

    public static function getFromCache($key) {
        $item = self::$cache->getItem($key);
        return $item->isHit() ? $item->get() : null;
    }

    public static function saveToCache($key, $data, $ttl = 3600) {
        $item = self::$cache->getItem($key);
        $item->set($data)->expiresAfter($ttl);
        self::$cache->save($item);
    }

    public static function addEntry($table, $data) {
        parent::insert($table, $data);
        // Invalidate cache after adding a new entry
        self::$cache->clear();
    }

    public static function getLookupTable($table) {
        $cacheKey = 'lookup_table_' . $table;
        $result = self::getFromCache($cacheKey);

        if (!$result) {
            $result = parent::query("SELECT * FROM %b", $table);
            self::saveToCache($cacheKey, $result);
        }

        return $result;
    }
}

// Set the cache pool
MyORM::setCache($pool);

// Example usage
$lookupTable = MyORM::getLookupTable('my_lookup_table');

// Adding a new entry
MyORM::addEntry('my_lookup_table', ['column1' => 'value1', 'column2' => 'value2']);

In this setup:

getLookupTable: Reads from the cache if available, otherwise fetches from the database and caches the result. addEntry: Writes to the database and clears the cache to ensure consistency. This approach ensures that read operations are served from the cache, while write operations update the database and invalidate the cache to keep it up-to-date.