Geekly Articles each Day
📜 ⬆️ ⬇️

CometQL - api of work from comets server using MySQL protocol


CometQL is the api for working with the saas of comets using the MySQL protocol.

Why is it cool



For example, to get information about when a user was online, just run the following query:
select id, time from users_time where id = 2;

But the request to send a message to the channel:
 INSERT INTO pipes_messages (name, event, message)VALUES("pipe_name", "event_in_pipe", "text message");

How to connect and try it yourself


You can connect with demo data yourself and try.
 #  app.comet-server.ru #  15 #  lPXBFPqNg3f661JcegBY0N0dPXqUBdHXqj2cHf04PZgLHxT6z55e20ozojvMRvB8 #   CometQL_v1 #      mysql -h app.comet-server.ru -u15 -plPXBFPqNg3f661JcegBY0N0dPXqUBdHXqj2cHf04PZgLHxT6z55e20ozojvMRvB8 -DCometQL_v1


For those who want to try to connect but not at hand console mysql client
You can try out the work of CometQL using the online CometQL command line (located in the lower right corner of any page)

General information about CometQL


CometQL externally is a kind of database. What do we do when, when connecting, select the database CometQL_v1 is actually an instruction indicating which version of api we want to interact with.
It is necessary to understand that there is not a MySQL server on the backend, requests are parsed and are executed directly by the server. And the representation of all possible actions in the form of tables and requests to them is simply convenient and familiar to many webmasters.

Table pipes_messages


Table pipes_messages contains messages transmitted through channels. To send a message to the channel, you must perform an insert query (insert) in this table.
 mysql> insert into pipes_messages (name, event, message)values("pipe_name", "event_in_pipe", "text message"); Query OK, 0 rows affected (0.13 sec)

The “name” and “event” fields must match the following regular expression [0-9A-z = + / _]
A query from pipes_messages will return the message history in the channel if the function of saving history is enabled for this channel (how to enable this function is written below).
 mysql> select * from pipes_messages where name = "p10"; +------+-------+-------+--------------+ | name | index | event | message | +------+-------+-------+--------------+ | p10 | 0 | event | msgData | | p10 | 1 | event | msgqqrrata | | p10 | 2 | evt3 | msgqqrrata | +------+-------+-------+--------------+ 3 rows in set (0.00 sec)

Clears the feed history.
 mysql> delete from pipes_messages where name = 'p10'; Query OK, 0 rows affected (0.13 sec)

Online example of sending messages
')

Table pipes


Table pipes contains information about how many people subscribed to messages from the channels. The table is read only.
 mysql> select * from pipes where name in( "web_admins", "web_php_chat"); +--------------+-------+ | name | users | +--------------+-------+ | web_admins | 3 | | web_php_chat | 2 | +--------------+-------+ 2 rows in set (0.30 sec)

Online sample feed subscriptions

Table users_in_pipes


The users_in_pipes table contains data about which of the authorized users subscribed to the channel. The table is read only.
 mysql> select * from users_in_pipes where name = "web_admins"; +------------+---------+ | name | user_id | +------------+---------+ | web_admins | 2 | | web_admins | 4 | | web_admins | 14 | | web_admins | 9 | +------------+---------+ 4 row in set (0.32 sec)

It is noteworthy that the users field contains the total number of subscribers, both authorized and unauthorized, while the users_in_pipes table contains a list of only authorized subscribers.

Table pipes_settings


Table pipes_settings contains channel logging settings. By default, messages passing through the channel are not remembered. But if you enable the logging mechanism for the channel, then the last n messages passed through this channel will be stored in the comets server.
To enable the channel logging mechanism, you must perform the following request.
 mysql> insert into pipes_settings (name, length) values ('p10', 10); Query OK, 1 row affected (0.00 sec)

Here, the length parameter is how many recent messages will be remembered. Accepts values ​​from 0 to 99.
In order to get the values ​​of the channel settings, you need to run a query from pipes_settings.
 mysql> select * from pipes_settings where name = 'p10'; +------+--------+ | name | length | +------+--------+ | p10 | 10 | +------+--------+ 1 row in set (0.00 sec)

In order to disable the logging mechanism, it is necessary to delete the settings entry from pipes_settings.
 mysql> delete from pipes_settings where name = 'p10'; Query OK, 0 rows affected (0.00 sec)

The mechanism for authorizing users on a comet server


In addition to channels where everyone who knows the name of the channel can subscribe to it, it is possible to authorize users on the comets server and send personal messages to users by their identifiers. User authorization occurs in 2 stages. The first step is to send the user ID in your system and the random hash to the comets server.

 mysql> INSERT INTO users_auth (id, hash )VALUES (1, 'auth_hash1');


At the second stage, this information (user ID and hash) must be passed to JavaScript Api
 $(document).ready(function() { CometServer().start({dev_id:1, user_key:"auth_hash1", user_id:"__" }) });

Here dev_id is the public identifier of the developer .
And now the user will be authorized on the comets server.

Message delivery for authorized users


When sending messages to authorized users by their identifier (insert a query into the users_messages table), messages are delivered to the user on all devices (Up to 16 devices) on which he has been authorized at the moment. This is very convenient in the event that a person came to your site and logged in on it from more than one device (for example, a phone and a computer or just sitting in two different browsers at the same time).

If the person is currently inline, the message is placed in the message queue and will be delivered when the person appears online. Currently, for each user, the maximum queue size is limited.

The main purpose of a message queue is to deliver messages after a short-term transition of a person into the ofline. For example, in cases when a person refreshes the page of the site on which the connection was opened, he leaves in ofline for about 1 second.

Table users_messages


The users_messages table is designed to send messages to authorized users by their ID.
For example, to send a message to a user with id = 2 and the message text 'message', you must run the following query
 mysql> insert into users_messages (id, event, message)values (2, 'event', 'message'); Query OK, 0 row affected (0.00 sec)

The message is either sent to the user immediately or placed in a queue to be sent to the user later.
In order to receive all those messages that have not yet been delivered to the user and are in the queue, you must perform a select query
 mysql> select * from users_messages where id = 2; +----+-------+-------+---------+ | id | index | event | message | +----+-------+-------+---------+ | 2 | 0 | evnt1 | message | | 2 | 1 | evnt2 | messag2 | +----+-------+-------+---------+ 2 rows in set (0.00 sec)

Here you can see that sending expects 2 messages. They will be sent immediately as the user appears online.
The table contains columns:

To clear the queue, use the delete request.
 mysql> delete from users_messages where id = 2; Query OK, 0 rows affected (0.08 sec)

After the message is delivered to the user, it is automatically removed from the message queue.

Table users_time


The users_time table contains data about when users were online. The table is read only. Time data is stored in UNIX time.
 mysql> select * from users_time where id in( 2, 3, 145); +-----+------------+ | id | time | +-----+------------+ | 2 | 0 | | 3 | 1438245468 | | 145 | -1 | +-----+------------+ 3 rows in set (0.31 sec)

Here, the user with id = 2 is currently on the site, the user with id = 3 was online on July 30, and there is no data for the user with id = 145.

Table users_auth


The users_auth table contains data for authorizing users on the comets server.
 mysql> insert into users_auth (id, hash )values (12, 'hash1'); Query OK, 1 row affected (0.13 sec) 

 mysql> select * from users_auth where id in(2, 3, 12); +----+----------------------------+ | id | hash | +----+----------------------------+ | 2 | bjl6knotdb2t1oov958mhuian7 | | 12 | hash1 | +----+----------------------------+ 2 rows in set (0.32 sec)

Here for the user with id = 3 there is no data, and for users 2 and 12 there is data.

It is important to note that in the hash field you can pass only strings with a length of no more than 32 characters and corresponding to the regular expression [0-9A-z = + / _] .
To delete user authorization data, use the delete request.
 delete from users_auth where id = 12; Query OK, 0 rows affected (0.00 sec)

Tips for those who make their public api


Here, in my opinion, the 3 most useful articles for anyone who is going to make a public api interface:


More examples of using CometQL in real projects


Examples of using CometQL are in the article how to make a chat on the site and in the article about the integration of private chat on the site

Source: https://habr.com/ru/post/264425/

More articles:


All Articles