- Notifications
You must be signed in to change notification settings - Fork1
TDengine driver for PDO, with Swoole support
License
bearlord/pdo_taosw
Folders and files
| Name | Name | Last commit message | Last commit date | |
|---|---|---|---|---|
Repository files navigation
English |简体中文
PDO_TAOSW is aTDengine driver for PDO, with Swoole Support.
TDenginehas agreed to use library files to develop PHP extension.
TDengine is an open-sourced big data platform underGNU AGPL v3.0, designed and optimized for the Internet of Things (IoT), Connected Cars, Industrial IoT, and IT Infrastructure and Application Monitoring. Besides the 10x faster time-series database, it provides caching, stream computing, message queuing and other functionalities to reduce the complexity and cost of development and operation.
10x Faster on Insert/Query Speeds: Through the innovative design on storage, on a single-core machine, over 20K requests can be processed, millions of data points can be ingested, and over 10 million data points can be retrieved in a second. It is 10 times faster than other databases.
1/5 Hardware/Cloud Service Costs: Compared with typical big data solutions, less than 1/5 of computing resources are required. Via column-based storage and tuned compression algorithms for different data types, less than 1/10 of storage space is needed.
Full Stack for Time-Series Data: By integrating a database with message queuing, caching, and stream computing features together, it is no longer necessary to integrate Kafka/Redis/HBase/Spark or other software. It makes the system architecture much simpler and more robust.
Powerful Data Analysis: Whether it is 10 years or one minute ago, data can be queried just by specifying the time range. Data can be aggregated over time, multiple time streams or both. Ad Hoc queries or analyses can be executed via TDengine shell, Python, R or Matlab.
Seamless Integration with Other Tools: Telegraf, Grafana, Matlab, R, and other tools can be integrated with TDengine without a line of code. MQTT, OPC, Hadoop, Spark, and many others will be integrated soon.
Zero Management, No Learning Curve: It takes only seconds to download, install, and run it successfully; there are no other dependencies. Automatic partitioning on tables or DBs. Standard SQL is used, with C/C++, Python, JDBC, Go and RESTful connectors.
phpversion >= 7.2.0swooleversion >= 4.8.0
phpize./configuremake&& make installWhen PDO_TAOSW is compiled, libtaos.so is required. The default path is: /usr/lib/libtaos.so, pointing to: /usr/local/taos/driver/libtaos.so.2.x.x.x. So you need to install the TDengine database first.
Edit the php.ini file or add a new configuration file in the php.d directory.
extension=pdo_taosw.soCreate the database on the client.
CREATEDATABASEdemo;
useSwoole\Coroutine;Coroutine::create(function () {$dbh =newPDO("taosw:host=127.0.0.1;dbname=demo","root","taosdata");});
Creating a data table is similar toMySQL syntax, and the data types are similar, but there are differences.
TDengine has its own database type,NCHAR is similar to MySQL'sVARCHAR, andBINARY is similar to MySQL'sTEXT.
TDengine does not have keywords such asPRIMARY KEY,AUTO INCREMENT,DEFAULT NULL, etc.
The first column ofTDengine must be of typeTIMESTAMP.
Example:
useSwoole\Coroutine;Coroutine::create(function () {$dbh =newPDO("taosw:host=127.0.0.1;dbname=demo","root","taosdata");$sql ="create table device_log_1000 ( created_timestamp TIMESTAMP, v_bool BOOL, v_tinyint TINYINT, v_smallint SMALLINT, v_int INT, v_bigint BIGINT, v_float FLOAT, v_double DOUBLE, v_binary BINARY(60), v_nchar NCHAR(40))";$result =$dbh->exec($sql);var_dump($result);});
$result returns 0 if execution succeeds, -1 if fails.
After execution, log in to theTDengine database client to view the data table structure.
taos> USE DEMO;Database changed.taos> SHOW CREATE TABLE device_log_1000; Table | Create Table |================================================================== device_log_1000 | create table device_log_100... |Query OK,1 row(s)inset (0.002146s)taos> SHOW CREATE TABLE device_log_1000\G;***************************1.row*************************** Table: device_log_1000Create Table: create table device_log_1000 (created_timestampTIMESTAMP,v_bool BOOL,v_tinyint TINYINT,v_smallintSMALLINT,v_intINT,v_bigintBIGINT,v_float FLOAT,v_double DOUBLE,v_binary BINARY(60),v_nchar NCHAR(40))Query OK,1 row(s)inset (0.000267s)taos>
- Call the
PDO::execmethod to directly execute the INSERT SQL statement. - Call the
PDO::preparemethod to perform preprocessing operations.
useSwoole\Coroutine;Coroutine::create(function () {$dbh =newPDO("taosw:host=127.0.0.1;dbname=demo","root","taosdata");$sql ="INSERT INTO device_log_100 ( created_timestamp, v_bool, v_tinyint, v_smallint, v_int, v_bigint, v_float, v_double, v_binary, v_nchar ) VALUES ( NOW, 1, 120, 1200, 12000, 120000, 9.09, 9.000009, 'hello TDengine', 'hello PHP' )";$result =$dbh->exec($sql);var_dump($result);});
$result returns 0 if execution succeeds, -1 if fails.
There is no way to get the number of rows affected.
Notice:
Be sure to call theexec method, not thequery method.
useSwoole\Coroutine;Coroutine::create(function () {try {$dbh =newPDO("taosw:host=127.0.0.1;dbname=demo","root","taosdata");$sql ="INSERT INTO device_log_100 (created_timestamp, v_bool, v_tinyint, v_smallint, v_int, v_bigint, v_float, v_double, v_binary, v_nchar ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";$sth =$dbh->prepare($sql);$v1 =intval(microtime(true) *1000);$v2 =1;$v3 =100;$v4 =2000;$v5 =20000;$v6 =200000;$v7 =8.08;$v8 =8.000008;$v9 ="TDengine test";$v10 ="Taos test";$sth->bindParam(1,$v1,PDO::PARAM_TAOSW_TIMESTAMP);$sth->bindParam(2,$v2,PDO::PARAM_TAOSW_BOOL);$sth->bindParam(3,$v3,PDO::PARAM_TAOSW_TINYINT);$sth->bindParam(4,$v4,PDO::PARAM_TAOSW_SMALLINT);$sth->bindParam(5,$v5,PDO::PARAM_TAOSW_INT);$sth->bindParam(6,$v6,PDO::PARAM_TAOSW_BIGINT);$sth->bindParam(7,$v7,PDO::PARAM_TAOSW_FLOAT);$sth->bindParam(8,$v8,PDO::PARAM_TAOSW_DOUBLE);$sth->bindParam(9,$v9,PDO::PARAM_TAOSW_BINARY);$sth->bindParam(10,$v10,PDO::PARAM_TAOSW_NCHAR);$result =$sth->execute();var_dump($result);$rowCount =$sth->rowCount()var_dump($rowCount); }catch (Exception$e) {printf("%d, %s\n",$e->getCode(),$e->getMessage()); }});
$result returns true if execution succeeds, false if fails.
$rowCount represents the number of rows affected.
Placeholder supports parameter names in the form of:name, and also in the form of?.
Please forget PDO predefined types such asPDO::PARAM_BOOL,PDO::PARAM_INT,PDO::PARAM_STR, etc.
Please use the following custom PDO types:
| CUSTOM PDO TYPE | TDengine DATA TYPE | DESCRIPTION |
|---|---|---|
| PARAM_TAOSW_NULL | TSDB_DATA_TYPE_NULL | NULL |
| PARAM_TAOSW_BOOL | TSDB_DATA_TYPE_BOOL | BOOL |
| PARAM_TAOSW_TINYINT | TSDB_DATA_TYPE_TINYINT | TINYINT |
| PARAM_TAOSW_SMALLINT | TSDB_DATA_TYPE_SMALLINT | SMALLINT |
| PARAM_TAOSW_INT | TSDB_DATA_TYPE_INT | INT |
| PARAM_TAOSW_BIGINT | TSDB_DATA_TYPE_BIGINT | BIGINT |
| PARAM_TAOSW_FLOAT | TSDB_DATA_TYPE_FLOAT | FLOAT |
| PARAM_TAOSW_DOUBLE | TSDB_DATA_TYPE_DOUBLE | DOUBLE |
| PARAM_TAOSW_BINARY | TSDB_DATA_TYPE_BINARY | BINARY |
| PARAM_TAOSW_TIMESTAMP | TSDB_DATA_TYPE_TIMESTAMP | TIMESTAMP |
| PARAM_TAOSW_NCHAR | TSDB_DATA_TYPE_NCHAR | NCHAR |
| PARAM_TAOSW_UTINYINT | TSDB_DATA_TYPE_UTINYINT | UTINYINT |
| PARAM_TAOSW_USMALLINT | TSDB_DATA_TYPE_USMALLINT | USMALLINT |
| PARAM_TAOSW_UINT | TSDB_DATA_TYPE_UINT | UINT |
| PARAM_TAOSW_UBIGINT | TSDB_DATA_TYPE_UBIGINT | UBIGINT |
| PARAM_TAOSW_JSON | TSDB_DATA_TYPE_JSON | JSON |
Constants such asTSDB_DATA_TYPE_INT are partially equal the PDO predefined constants value, resulting in conflict when judging the data type, so the difference of6000 is set artificially.
Just for illustration, no impact on development.
If the field type of the data table can be obtained in advance when performing the insert operation, and then according to the type of the bound data, it can be perfectly compatible, such as:
MySQL's field types are INT, SMALLINT, TINYINT, BIGINT, and the binding parameter is PDO::PARAM_INT, which can also be inserted.
However, when the TDengine API executes the INSERT operation, it cannot obtain the field type of the data table in advance, and can only bind parameters if it is perfectly consistent with the fields of the data table.
PDO::exec writes the complete SQL statement without placeholders.
PDO::prepare must usebindParam orbindValue to avoid preparing SQL without placeholders, otherwise an error will be reported.
Two ways to query data:
- Call the
PDO::querymethod to directly execute the SELECT SQL statement. - Call the
PDO::preparemethod to perform preprocessing operations
useSwoole\Coroutine;Coroutine::create(function () {$dbh =newPDO("taosw:host=127.0.0.1;dbname=demo","root","taosdata");$t1 =strtotime("2022-01-27 16:36:12");$t2 =strtotime("2022-01-27 16:48:13");$start_time =intval($t1 *1000);$end_time =intval($t2 *1000);$sql ="SELECT * FROM device_log_100 WHERE created_timestamp >=$start_time AND created_timestamp <=$end_time LIMIT 3 OFFSET 0";$sth =$dbh->query($sql);$result =$sth->fetchAll(PDO::FETCH_ASSOC);var_dump($result);});
The execution result is as follows:
array(3) { [0]=>array(10) { ["created_timestamp"]=> string(23)"2022-01-27 16:36:12.038" ["v_bool"]=> string(1)"1" ["v_tinyint"]=> string(3)"100" ["v_smallint"]=> string(4)"2000" ["v_int"]=> string(5)"20000" ["v_bigint"]=> string(6)"200000" ["v_float"]=> string(9)"8.08000" ["v_double"]=> string(11)"8.000008000" ["v_binary"]=> string(13)"TDengine test" ["v_nchar"]=> string(9)"Taos test" } [1]=>array(10) { ["created_timestamp"]=> string(23)"2022-01-27 16:36:49.146" ["v_bool"]=> string(1)"1" ["v_tinyint"]=> string(3)"100" ["v_smallint"]=> string(4)"2000" ["v_int"]=> string(5)"20000" ["v_bigint"]=> string(6)"200000" ["v_float"]=> string(9)"8.08000" ["v_double"]=> string(11)"8.000008000" ["v_binary"]=> string(13)"TDengine test" ["v_nchar"]=> string(9)"Taos test" } [2]=>array(10) { ["created_timestamp"]=> string(23)"2022-01-27 16:36:55.365" ["v_bool"]=> string(1)"1" ["v_tinyint"]=> string(3)"100" ["v_smallint"]=> string(4)"2000" ["v_int"]=> string(5)"20000" ["v_bigint"]=> string(6)"200000" ["v_float"]=> string(9)"8.08000" ["v_double"]=> string(11)"8.000008000" ["v_binary"]=> string(13)"TDengine test" ["v_nchar"]=> string(9)"Taos test" }}
Note:created_timestamp uses thetimestamp type when querying, but the query result formats thetime type, which is consistent with theTDengine client query results.
useSwoole\Coroutine;Coroutine::create(function () {$dbh =newPDO("taosw:host=127.0.0.1;dbname=demo","root","taosdata");$t1 =strtotime("2022-01-27 16:36:12");$t2 =strtotime("2022-01-27 16:48:13");$start_time =intval($t1 *1000);$end_time =intval($t2 *1000);$sql ="SELECT * FROM device_log_100 WHERE created_timestamp >= :start_time AND created_timestamp <= :end_time LIMIT 3 OFFSET 0";$sth =$dbh->prepare($sql);$sth->bindParam("start_time",$start_time,PDO::PARAM_TAOSW_TIMESTAMP);$sth->bindParam("end_time",$end_time,PDO::PARAM_TAOSW_TIMESTAMP);$sth->execute();$result =$sth->fetchAll(PDO::FETCH_ASSOC);var_dump($result);});
Note:created_timestamp uses thetimestamp type when querying, but the query result formats thetime type, which is consistent with theTDengine client query results.
For other bound parameters, the data type should be the same as the field type of the data table. If usingPDO::PARAM_INT orPDO::PARAM_STR happens to work fine, it's just luck, not recommended.
No one would have thought that weakly typed PHP requires strong consistency between thedata type and thedata table field type when operating theTDengine database.
TDengine does not support modification operations by default. Data with duplicate timestamps is written to a table that does not support data updates, andthe later written data will bediscarded.
If the user needs the update function of the data, just specify theupdate option of the database as1 when building the database.
Example：
CREATEDATABASEdemoUPDATE1
When writing data with duplicate timestamps to a table that supports the data update function, theold data will beoverwritten.
Up to now, theTDengine version is V2.4, and the data table does not support the DELETE operation.
However,the database supports automatic deletion. The deletion time is related to thekeep parameter specified when the database is created. The default is 3650 days, or 10 years. After 10 years, the database will be automatically logically deleted, and the database files will be automatically backed up to the appropriate location.
PDO could connect to TDengine without specifying a database.
For example:
useSwoole\Coroutine;Coroutine::create(function () {$dbh =newPDO("taosw:host=127.0.0.1","root","taosdata");});
The database of TDengine has a storage time, which is 10 years by default.
If the device logs are saved as a single database, the device log three years ago has no reference value, but the data cannot be deleted, which will occupy the hard disk space and affect the running speed of the database.
If the device logs are saved as multiple databases separated byyear. It will not cause the single database and data set to be too large, and the efficiency of inserting and querying data is maintained. Meaningless device logs can be deleted or archived.
Example：
useSwoole\Coroutine;Coroutine::create(function () {try {$dbh =newPDO("taosw:host=127.0.0.1","root","taosdata");$year =date("Y");$database =sprintf("demo_%s",$year);$sql ="INSERT INTO{$database}.device_log_100 (created_timestamp, v_bool, v_tinyint, v_smallint, v_int, v_bigint, v_float, v_double, v_binary, v_nchar ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";$sth =$dbh->prepare($sql);$v1 =intval(microtime(true) *1000);$v2 =1;$v3 =100;$v4 =2000;$v5 =20000;$v6 =200000;$v7 =8.08;$v8 =8.000008;$v9 ="TDengine test";$v10 ="Taos test";$sth->bindParam(1,$v1,PDO::PARAM_TAOSW_TIMESTAMP);$sth->bindParam(2,$v2,PDO::PARAM_TAOSW_BOOL);$sth->bindParam(3,$v3,PDO::PARAM_TAOSW_TINYINT);$sth->bindParam(4,$v4,PDO::PARAM_TAOSW_SMALLINT);$sth->bindParam(5,$v5,PDO::PARAM_TAOSW_INT);$sth->bindParam(6,$v6,PDO::PARAM_TAOSW_BIGINT);$sth->bindParam(7,$v7,PDO::PARAM_TAOSW_FLOAT);$sth->bindParam(8,$v8,PDO::PARAM_TAOSW_DOUBLE);$sth->bindParam(9,$v9,PDO::PARAM_TAOSW_BINARY);$sth->bindParam(10,$v10,PDO::PARAM_TAOSW_NCHAR);$result =$sth->execute();var_dump($result);$rowCount =$sth->rowCount();var_dump($rowCount); }catch (Exception$e) {printf("%d, %s\n",$e->getCode(),$e->getMessage()); }});
About
TDengine driver for PDO, with Swoole support