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

#1

I have a bit knowledge with MySQL and I understood that Cockpit can’t support it.

In my point of view and If I didn’t made a mistake, I understood that Cockpit has two options for database: SQL Lite as default and Mongo as a custom choice. Everything is fine.

Trying to understand query and database system, I think I found Mongo system, but I’m confused because de default database is SQL lite. Could anyone explain me?

Beside that, how is the right method to know and execute all possible querys? SQL? MONGO?

#2

Maybe this is somewhat helpful.

The way I got this was: SQLite is used to store internal stuff. The Mongo part is… no idea. I don’t have it setup and it still works (for now, moving to nginx soon).

You interact with Cockpit through the REST endpoints. That stuff here: API (Collections Example)

#3

No problems with the REST endpoints, I can use it. As I say, I don’t know if I’m right about MONGO query, just a hunch.

What I don’t know to do is how to use query parameters. Let’s say that in MySQL I have a products and rating column. I can run a SELECT * FROM products ORDER BY rating if I want to ranking my products.

How can I do that with Cockpit? This is just an example!

#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:

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.

2 Likes
#5

You have answered all of my questions! Finally I understand the right way as Cockpit works. Now I have to learn MongoDB and I hope to develop without any problem.

Thank you very much!