I need help to understand Cockpit, database concepts and query system

Support
4

Cockpit is written to work with MongoDB and it has a wrapper (MongoLite) to use SQLite with MongoDB syntax. When you use it with SQLite, you can’t use the familiar SQL queries anymore. You have to use mongo queries. Cockpit passes the filters (nearly) directly to MongoDB , so while using Cockpit, you learn the mongo syntax along the way.

MongoLite passes custom functions to SQLite to filter the stored json strings. It is not fully implemented, so if you miss some very unique filter options or features in the future, you might have to switch to MongoDB.

So the default for Cockpit is MongoDB, but you can use it out of the box with SQLite. You don’t have to setup a mongo database to use cockpit.

If you never used MongoDB or MongoLite, but are familiar with (My)SQL, you’ll realize big differences. MongoDB is a json based, non-relational database and it is designed to work very fast with massive database requests. Use the search engine of your choice to find more differences and advantages.

If you want to understand the structure of the SQLite databases and tables, have a look at the phpLiteAdmin addon.

This is, how the data of a collection named “pages” is stored:

cp-phpliteadmin
cp-phpliteadmin.png1314×730 72.8 KB

That’s why SELECT * FROM products ORDER BY rating doesn’t work.

Some simple translation of the query…:

REST API get:

/api/collections/get/products?token=xxtokenxx&sort[rating]=1

REST API post:

/api/collections/get/products

{"token":"xxtokenxx","sort":{"rating":1}}

PHP:

$options = [
    'sort' => ['rating' => 1] // 1: ASC, -1: DESC
];

cockpit('collections')->find('products', $options);

Sorting is simple. It gets more complicated if you wonder how to “translate” WHERE rating = 1 or WHERE foo LIKE "%bar%". Most questions are answered here or in the issues already, like how to use regex filters, $and, $or etc. That’s the hardest thing to learn. You have to think in a different way than MySQL works. Don’t try to translate a WHERE clause. Adjust filters instead.

I hope, that clears things up.

4 Likes