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.

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.

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>";
}

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.

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=+/_].

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)

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");

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)

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.

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)

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)

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

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)

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.

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.

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)

• Example of sending messages from bash.
• Error code in CometQL
• Reserved channel names
• JavaScript API
• What it is and why do we need "A public developer token" and "Secret developer key?"

Main article Reserved channels names and channels with extra properties.

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.

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