CometQL

CometQL - it’s an API for work with comet server through MySQL protocol.

Advantages of CometQL:

  1. Unified API for more than 12 programming languages.
  2. Simple and intelligible query view.
  3. PHP includes resources for maintaining persistent connections with MySQL and now you can use it for co-working with comet server.

So, data is stored in tables and commands for select or insert perform some actions. For example, for receiving information when user was online, you can perform next query:

SELECT * FROM users_time WHERE id = 2;

And what we can see:

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 user with id=2 at the moment online, user with id=3 was online at July 30 and for user with id=145 there is no available data.

How to connect and try by yourself

You can by yourself connect to demo data and try it now. We recommend you to connect via console, because there are some graphics MySQL clients, which work inappropriate with comet server.

# Server app.comet-server.ru
# Login 15
# Password lPXBFPqNg3f661JcegBY0N0dPXqUBdHXqj2cHf04PZgLHxT6z55e20ozojvMRvB8
# Database CometQL_v1
 
# Line to connect from command prompt
mysql -h app.comet-server.ru -u15 -plPXBFPqNg3f661JcegBY0N0dPXqUBdHXqj2cHf04PZgLHxT6z55e20ozojvMRvB8 -DCometQL_v1  --skip-ssl

Else you can use online command prompt. You can find it on the bottom of the right corner on all of the pages.

Here you can test source code based on PHP with CometQL using for online command prompt implementation.

An example of connecting to a comet server from php.

$dev_id = "15"; // Used as login
$dev_key = "lPXBFPqNg3f661JcegBY0N0dPXqUBdHXqj2cHf04PZgLHxT6z55e20ozojvMRvB8"; // Used as a password
 
// The connection looks like we connected to the database. You can use the functions to work with mysql
// But in fact you are connecting to the comet server.
$link = mysqli_connect("app.comet-server.ru", $dev_id, $dev_key, "CometQL_v1");
if(!$link)
{  
    die("Could not create connection with CometQL");
}
 
$result = mysqli_query (  $link, "show status" ); 
if(mysqli_errno($link) != 0)
{
    echo "Error code:<a href='https://comet-server.com/wiki/doku.php/en:comet:cometql:error'  target='_blank' >".mysqli_errno($link)."</a>";
    echo "Error text:<a href='https://comet-server.com/wiki/doku.php/en:comet:cometql:error' target='_blank' >".mysqli_error($link)."</a>";
    exit;
}
 
while($row = mysqli_fetch_assoc($result))
{
    echo "<pre>";
    var_dump($row);
    echo "</pre><br>";
}

Table description

Names of tables and columns always use in lower case.

Notes of current implementation of CometSQL:

  • CometSQL doesn’t implement all methods of SQL. A part of functions planned to realize in future. But some notes were applied in order to optimize inner structure of Comet server.
  • To optimize internal logics, such operations like delete and insert doesn’t return amount of used lines (it belongs to CometQL v.1.0).
  • Supporting such operators like AND, OR and ORDER BY in queries will be realize soon.
Some of hosting providers determine forbiddance (или ban) on external connections – you can face with this on some free or half-free hosting services. In order to check ability to use CometQL, you can take advantage of hosting verification script.

Table pipes_messages

The table pipes_messages contains messages which transported via channels. For sending messages via channel you need to perform query of insert (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)

Fields “name” and “event” must match to next regular term [0-9A-z=+/_].

Find out quantity of attendance on channel is possible by sending query to pipes table, because this message sends to all channel’s subscribers. In this way you can enquire amount of attendance, which get this message.

Selection query from pipes_messages returns history of messages of current channel if the save-function was activate for this channel.

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 messages history of this channel.

mysql> DELETE FROM pipes_messages WHERE name = 'p10';
Query OK, 0 ROWS affected (0.13 sec)

Online example

Input channel name “pipe _name” and click “subscribe”. Now perform with online command prompt insert-query with pipes_messages and check the result.

mysql> INSERT INTO pipes_messages (name, event, message)VALUES("pipe_name", "event_in_pipe", "text message");

Table pipes

The table “pipes” contains information about quantity of subscribers on messages from channels. This table is available only for reading.

mysql> SELECT * FROM pipes WHERE name IN( "web_admins", "web_php_chat");
+--------------+-------+
| name         | users |
+--------------+-------+
| web_admins   | 0     |
| web_php_chat | 0     |
+--------------+-------+
2 ROWS IN SET (0.30 sec)

Online example

Perform this query:

mysql> SELECT * FROM pipes WHERE name IN( "web_admins", "web_php_chat");

Input channel name “web_admins” and click “subscribe”. Now perform query again and you will see that amount of subscribers are increase.

Table users_in_pipes

The table “users_in_pipes” contains data about authorized users which subscribe on channel. This table is available only for reading.

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)
Field user in table pipes contain aggregate amount of subscribers (authorized and non-authorized), besides of table users_in_pipes includes only authorized subscribers list.

Table pipes_settings

The table “pipes_settings” contains settings of logging channels. By default, messages which passes through the channel, doesn’t store. If you activate logging mechanism for channel, comet server will be store last “n” messages. To activate logging mechanism you must perform next query:

mysql> INSERT INTO pipes_settings ("name", "length") VALUES ('p10', 10);
Query OK, 1 ROW affected (0.00 sec)

Here parameter length – it’s a number of last stored messages. It takes values from 0 to 99.

To get setting values, need to perform selection query from pipes_settings.

mysql> SELECT * FROM pipes_settings WHERE name = 'p10';
+------+--------+
| name | LENGTH |
+------+--------+
| p10  | 10     |
+------+--------+
1 ROW IN SET (0.00 sec)

To deactivate logging mechanism, need to delete from pipes_settings settings record.

mysql> DELETE FROM  pipes_settings WHERE name = 'p10';
Query OK, 0 ROWS affected (0.00 sec)

Table users_messages

The table “users_messages” intended sending messages to authorized users by their identifiers.

Sending messages by identifiers gives more powerful protection tools while data is transferring. Also it increases probability to deliver message to right user.

For example, for sending message to user with id=2 and message body “message” you must perform next query:

mysql> INSERT INTO users_messages (id, event, message)VALUES (2, 'event', 'message');
Query OK, 0 ROW affected (0.00 sec)

Message put into queue to be send to user soon or send immediately.

If you want to get all of the undelivered messages and which stuck in queue, perform the query “select”

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)

Now 2 messages wait for sending. They will be sending at once when user becomes online. This table contains 4 columns.

  1. id - user’s identifier.
  2. index - message’s number in queue.
  3. event - name of event.
  4. message - message’s body.

To clearing queue use “delete” query.

mysql> DELETE FROM users_messages WHERE id = 2;
Query OK, 0 ROWS affected (0.08 sec)
After delivering message to user, it will be automatically deleted from queue.

Table users_time

The table users_time contains data about when users were online. This table is available only for reading. Data timing stores 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 user with id=2 at that moment online, user with id=3 – was online at July 30, and for user with id=145 data is unavailable.

Table users_auth

The table users_auth contains data of user’s authorizing on comet 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 user with id=3 no data available, for users with id=2 and id=12 data is exist.

In field hash you can transmit only lines (strings) less than 32 symbols and it must match with regular term [0-9A-z=+/_].

To deleting data of user’s authorizing, you can use “delete” query.

DELETE FROM users_auth WHERE id = 12;
Query OK, 0 ROWS affected (0.00 sec)
To optimize internal logics, such operations like delete and insert doesn’t return amount of used lines (it belongs to CometQL v.1.0).

Other information

Reserved channels names

Main article Reserved channels names and channels with extra properties.

We don’t recommend to use in our project such channels names as «bin_*», «big_*», «push_*», «comet_*» и «sys_*». These names could be used for new functions. And they can have some special properties besides of current channels names.

Also we already have some channels with extra properties:

  • msg – used for sending messages in order to authorizing data;
  • user_status_* - used for automatic notification JS API about users’ status;
  • web_* - channels which can receive messages from CometQL and JS API.

Wrapping over CometQL api

If you have written a wrapper for working with CometQL api for which a framework that would be great if you share its running time with other users. Send us links to your repository on the wrapper support@comet-server.com

Discussion

Enter your comment. Wiki syntax is allowed:
X​ Y X G L