EVOLUTION-MANAGER

Edit File: mongo_c.result

set connect_enable_mongo=1; # # Test the MONGO table type # CREATE TABLE t1 (Document varchar(1024) field_format='*') ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME=restaurants OPTION_LIST='Driver=C,Version=0' DATA_CHARSET=utf8; SELECT * from t1 limit 3; Document {"_id":{"$oid":"58ada47de5a51ddfcd5ed51c"},"address":{"building":"1007","coord":[-73.856076999999999089,40.848447000000000173],"street":"Morris ParkAve", "zipcode":"10462"},"borough":"Bronx","cuisine":"Bakery","grades":[{"date":{"$date":1393804800000},"grade":"A","score":2},{"date":{"$date":1378857600000},"grade":"A","score":6},{"date":{"$date":1358985600000},"grade":"A","score":10},{"date":{"$date":1322006400000},"grade":"A","score":9},{"date":{"$date":1299715200000},"grade":"B","score":14}],"name":"Morris ParkBakeShop", "restaurant_id":"30075445"} {"_id":{"$oid":"58ada47de5a51ddfcd5ed51d"},"address":{"building":"469","coord":[-73.96170399999999745,40.66294200000000103],"street":"Flatbush Avenue", "zipcode":"11225"},"borough":"Brooklyn","cuisine":"Hamburgers","grades":[{"date":{"$date":1419897600000},"grade":"A","score":8},{"date":{"$date":1404172800000},"grade":"B","score":23},{"date":{"$date":1367280000000},"grade":"A","score":12},{"date":{"$date":1336435200000},"grade":"A","score":12}],"name":"Wendy'S","restaurant_id":"30112340"} {"_id":{"$oid":"58ada47de5a51ddfcd5ed51e"},"address":{"building":"351","coord":[-73.985135599999992451,40.767691900000002647],"street":"West 57Street", "zipcode":"10019"},"borough":"Manhattan","cuisine":"Irish","grades":[{"date":{"$date":1409961600000},"grade":"A","score":2},{"date":{"$date":1374451200000},"grade":"A","score":11},{"date":{"$date":1343692800000},"grade":"A","score":12},{"date":{"$date":1325116800000},"grade":"A","score":12}],"name":"Dj ReynoldsPubAndRestaurant", "restaurant_id":"30191841"} DROP TABLE t1; # # Test catfunc # CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME=restaurants CATFUNC=columns OPTION_LIST='Level=1,Driver=C,Version=0' DATA_CHARSET=utf8 ; SELECT * from t1; Column_Name Data_Type Type_Name Column_Size Buffer_Length Decimal_Digits Nullable Bpath _id 1 CHAR 24 24 0 0 address_building 1 CHAR 10 10 0 0 address.building address_coord 1 CHAR 512 512 0 0 address.coord address_street 1 CHAR 38 38 0 0 address.street address_zipcode 1 CHAR 5 5 0 0 address.zipcode borough 1 CHAR 13 13 0 0 cuisine 1 CHAR 64 64 0 0 grades_0 1 CHAR 512 512 0 1 grades.0 name 1 CHAR 98 98 0 0 restaurant_id 1 CHAR 8 8 0 0 DROP TABLE t1; # # Explicit columns # CREATE TABLE t1 ( _id VARCHAR(24) NOT NULL, name VARCHAR(255) NOT NULL, cuisine VARCHAR(255) NOT NULL, borough VARCHAR(255) NOT NULL, restaurant_id VARCHAR(255) NOT NULL) ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME=restaurants CONNECTION='mongodb://localhost:27017' LRECL=1024 DATA_CHARSET=utf8 OPTION_LIST='Driver=C,Version=0'; SELECT * FROM t1 LIMIT 10; _id name cuisine borough restaurant_id 58ada47de5a51ddfcd5ed51c Morris Park Bake Shop Bakery Bronx 30075445 58ada47de5a51ddfcd5ed51d Wendy'S Hamburgers Brooklyn 30112340 58ada47de5a51ddfcd5ed51e Dj Reynolds Pub And Restaurant Irish Manhattan 30191841 58ada47de5a51ddfcd5ed51f Riviera Caterer American Brooklyn 40356018 58ada47de5a51ddfcd5ed520 Tov Kosher Kitchen Jewish/Kosher Queens 40356068 58ada47de5a51ddfcd5ed521 Brunos On The Boulevard American Queens 40356151 58ada47de5a51ddfcd5ed522 Kosher Island Jewish/Kosher Staten Island 40356442 58ada47de5a51ddfcd5ed523 Wilken'S Fine Food Delicatessen Brooklyn 40356483 58ada47de5a51ddfcd5ed524 Regina Caterers American Brooklyn 40356649 58ada47de5a51ddfcd5ed525 Taste The Tropics Ice Cream Ice Cream, Gelato, Yogurt, Ices Brooklyn 40356731 DROP TABLE t1; # # Test discovery # CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME=restaurants OPTION_LIST='Level=1,Driver=C,Version=0' DATA_CHARSET=utf8; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `_id` char(24) NOT NULL, `address_building` char(10) NOT NULL `FIELD_FORMAT`='address.building', `address_coord` varchar(512) NOT NULL `FIELD_FORMAT`='address.coord', `address_street` char(38) NOT NULL `FIELD_FORMAT`='address.street', `address_zipcode` char(5) NOT NULL `FIELD_FORMAT`='address.zipcode', `borough` char(13) NOT NULL, `cuisine` char(64) NOT NULL, `grades_0` varchar(512) DEFAULT NULL `FIELD_FORMAT`='grades.0', `name` char(98) NOT NULL, `restaurant_id` char(8) NOT NULL ) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='MONGO' `TABNAME`='restaurants' `OPTION_LIST`='Level=1,Driver=C,Version=0' `DATA_CHARSET`='utf8' SELECT * FROM t1 LIMIT 5; _id address_building address_coord address_street address_zipcode borough cuisine grades_0 name restaurant_id 58ada47de5a51ddfcd5ed51c 1007 Morris Park Ave 10462 Bronx Bakery {"date":{"$date":1393804800000},"grade":"A","score":2} Morris Park Bake Shop 30075445 58ada47de5a51ddfcd5ed51d 469 Flatbush Avenue 11225 Brooklyn Hamburgers {"date":{"$date":1419897600000},"grade":"A","score":8} Wendy'S 30112340 58ada47de5a51ddfcd5ed51e 351 West 57 Street 10019 Manhattan Irish {"date":{"$date":1409961600000},"grade":"A","score":2} Dj Reynolds Pub And Restaurant 30191841 58ada47de5a51ddfcd5ed51f 2780 Stillwell Avenue 11224 Brooklyn American {"date":{"$date":1402358400000},"grade":"A","score":5} Riviera Caterer 40356018 58ada47de5a51ddfcd5ed520 97-22 63 Road 11374 Queens Jewish/Kosher {"date":{"$date":1416787200000},"grade":"Z","score":20} Tov Kosher Kitchen 40356068 DROP TABLE t1; # # Dropping a column # CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME=restaurants DATA_CHARSET=utf8 COLIST='{"projection":{"grades":0}}' OPTION_LIST='Driver=C,Version=0,level=0' ; SELECT * FROM t1 LIMIT 10; _id address borough cuisine name restaurant_id 58ada47de5a51ddfcd5ed51c {"building":"1007","coord":[-73.856076999999999089,40.848447000000000173],"street":"Morris ParkAve", "zipcode":"10462"} Bronx Bakery Morris Park Bake Shop 30075445 58ada47de5a51ddfcd5ed51d {"building":"469","coord":[-73.96170399999999745,40.66294200000000103],"street":"Flatbush Avenue", "zipcode":"11225"} Brooklyn Hamburgers Wendy'S 30112340 58ada47de5a51ddfcd5ed51e {"building":"351","coord":[-73.985135599999992451,40.767691900000002647],"street":"West 57Street", "zipcode":"10019"} Manhattan Irish Dj Reynolds Pub And Restaurant 30191841 58ada47de5a51ddfcd5ed51f {"building":"2780","coord":[-73.982419999999990523,40.579504999999997494],"street":"Stillwell Avenue", "zipcode":"11224"} Brooklyn American Riviera Caterer 40356018 58ada47de5a51ddfcd5ed520 {"building":"97-22","coord":[-73.860115199999995639,40.731173900000001709],"street":"63 Road", "zipcode":"11374"} Queens Jewish/Kosher Tov Kosher Kitchen 40356068 58ada47de5a51ddfcd5ed521 {"building":"8825","coord":[-73.880382699999998408,40.764312400000001446],"street":"Astoria Boulevard", "zipcode":"11369"} Queens American Brunos On The Boulevard 40356151 58ada47de5a51ddfcd5ed522 {"building":"2206","coord":[-74.137728600000002643,40.611957199999999091],"street":"Victory Boulevard", "zipcode":"10314"} Staten Island Jewish/Kosher Kosher Island 40356442 58ada47de5a51ddfcd5ed523 {"building":"7114","coord":[-73.906850599999998508,40.619903399999998328],"street":"Avenue U", "zipcode":"11234"} Brooklyn Delicatessen Wilken'S Fine Food 40356483 58ada47de5a51ddfcd5ed524 {"building":"6409","coord":[-74.005288999999990551,40.628886000000001388],"street":"11 Avenue", "zipcode":"11219"} Brooklyn American Regina Caterers 40356649 58ada47de5a51ddfcd5ed525 {"building":"1839","coord":[-73.948260899999993967,40.640827100000002758],"street":"Nostrand Avenue", "zipcode":"11226"} Brooklyn Ice Cream, Gelato, Yogurt, Ices Taste The Tropics Ice Cream 40356731 DROP TABLE t1; # # Specifying Jpath # CREATE TABLE t1 ( _id VARCHAR(24) NOT NULL, name VARCHAR(64) NOT NULL, cuisine CHAR(200) NOT NULL, borough CHAR(16) NOT NULL, street VARCHAR(65) FIELD_FORMAT='address.street', building CHAR(16) FIELD_FORMAT='address.building', zipcode CHAR(5) FIELD_FORMAT='address.zipcode', grade CHAR(1) FIELD_FORMAT='grades.0.grade', score INT(4) NOT NULL FIELD_FORMAT='grades.0.score', `date` DATE FIELD_FORMAT='grades.0.date', restaurant_id VARCHAR(255) NOT NULL) ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME='restaurants' DATA_CHARSET=utf8 OPTION_LIST='Driver=C,Version=0' ; SELECT * FROM t1 LIMIT 1; _id 58ada47de5a51ddfcd5ed51c name Morris Park Bake Shop cuisine Bakery borough Bronx street Morris Park Ave building 1007 zipcode 10462 grade A score 2 date 2014-03-03 restaurant_id 30075445 SELECT name, street, score, date FROM t1 LIMIT 5; name street score date Morris Park Bake Shop Morris Park Ave 2 2014-03-03 Wendy'S Flatbush Avenue 8 2014-12-30 Dj Reynolds Pub And Restaurant West 57 Street 2 2014-09-06 Riviera Caterer Stillwell Avenue 5 2014-06-10 Tov Kosher Kitchen 63 Road 20 2014-11-24 SELECT name, cuisine, borough FROM t1 WHERE grade = 'A' LIMIT 10; name cuisine borough Morris Park Bake Shop Bakery Bronx Wendy'S Hamburgers Brooklyn Dj Reynolds Pub And Restaurant Irish Manhattan Riviera Caterer American Brooklyn Kosher Island Jewish/Kosher Staten Island Wilken'S Fine Food Delicatessen Brooklyn Regina Caterers American Brooklyn Taste The Tropics Ice Cream Ice Cream, Gelato, Yogurt, Ices Brooklyn Wild Asia American Bronx C & C Catering Service American Brooklyn SELECT COUNT(*) FROM t1 WHERE grade = 'A'; COUNT(*) 20687 SELECT * FROM t1 WHERE cuisine = 'English'; _id name cuisine borough street building zipcode grade score date restaurant_id 58ada47de5a51ddfcd5ed83d Tea And Sympathy English Manhattan Greenwich Avenue 108 10011 A 8 2014-10-23 40391531 58ada47de5a51ddfcd5ed85c Tartine English Manhattan West 11 Street 253 10014 A 11 2014-08-14 40392496 58ada47de5a51ddfcd5ee1f3 The Park Slope Chipshop English Brooklyn 5 Avenue 383 11215 B 17 2014-09-29 40816202 58ada47de5a51ddfcd5ee7e4 Pound And Pence English Manhattan Liberty Street 55 10005 A 7 2014-02-11 41022701 58ada47de5a51ddfcd5ee999 Chip Shop English Brooklyn Atlantic Avenue 129 11201 A 9 2014-10-08 41076583 58ada47ee5a51ddfcd5efe3f The Breslin Bar & Dining Room English Manhattan West 29 Street 16 10001 A 13 2014-06-09 41443706 58ada47ee5a51ddfcd5efe99 Highlands Restaurant English Manhattan West 10 Street 150 10014 A 12 2014-10-22 41448559 58ada47ee5a51ddfcd5f0413 The Fat Radish English Manhattan Orchard Street 17 10002 A 12 2014-07-26 41513545 58ada47ee5a51ddfcd5f0777 Jones Wood Foundry English Manhattan East 76 Street 401 10021 A 12 2014-12-03 41557377 58ada47ee5a51ddfcd5f0ea2 Whitehall English Manhattan Greenwich Avenue 19 10014 Z 15 2015-01-16 41625263 58ada47ee5a51ddfcd5f1004 The Churchill Tavern English Manhattan East 28 Street 45 10016 A 13 2014-08-27 41633327 58ada47ee5a51ddfcd5f13d5 The Monro English Brooklyn 5 Avenue 481 11215 A 7 2014-06-03 41660253 58ada47ee5a51ddfcd5f1454 The Cock & Bull English Manhattan West 45 Street 23 10036 A 7 2014-08-07 41664704 58ada47ee5a51ddfcd5f176e Dear Bushwick English Brooklyn Wilson Avenue 41 11237 A 12 2014-12-27 41690534 58ada47ee5a51ddfcd5f1e91 Snowdonia Pub English Queens 32 Street 34-55 11106 A 12 2014-10-28 50000290 58ada47ee5a51ddfcd5f2ddc Oscar'S Place English Manhattan Hudson Street 466 10014 A 10 2014-08-18 50011097 SELECT * FROM t1 WHERE score = building; _id name cuisine borough street building zipcode grade score date restaurant_id DROP TABLE t1; # # Specifying Filter # CREATE TABLE t1 ( _id CHAR(24) NOT NULL, name CHAR(64) NOT NULL, borough CHAR(16) NOT NULL, restaurant_id CHAR(8) NOT NULL) ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME=restaurants DATA_CHARSET=utf8 FILTER='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' OPTION_LIST='Driver=C,Version=0' ; SELECT name FROM t1 WHERE borough = 'Queens'; name La Baraka Restaurant Air France Lounge Tournesol Winegasm Cafe Henri Bistro 33 Domaine Wine Bar Cafe Triskell Cannelle Patisserie La Vie Dirty Pierres Bistro Fresca La Crepe Bliss 46 Bistro Bear Cuisine By Claudette Paris Baguette The Baroness Bar Francis Cafe Madame Sou Sou Crepe 'N' Tearia Aperitif Bayside Llc DROP TABLE t1; # # Testing pipeline # CREATE TABLE t1 ( name VARCHAR(64) NOT NULL, borough CHAR(16) NOT NULL, date DATETIME NOT NULL, grade CHAR(1) NOT NULL, score INT(4) NOT NULL) ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME='restaurants' DATA_CHARSET=utf8 COLIST='{"pipeline":[{"$match":{"cuisine":"French"}},{"$unwind":"$grades"},{"$project":{"_id":0,"name":1,"borough":1,"date":"$grades.date","grade":"$grades.grade","score":"$grades.score"}}]}' OPTION_LIST='Driver=C,Version=0,Pipeline=1' ; SELECT * FROM t1 LIMIT 10; name borough date grade score Tout Va Bien Manhattan 2014-11-10 01:00:00 B 15 Tout Va Bien Manhattan 2014-04-03 02:00:00 A 13 Tout Va Bien Manhattan 2013-07-17 02:00:00 C 36 Tout Va Bien Manhattan 2013-02-06 01:00:00 B 22 Tout Va Bien Manhattan 2012-07-16 02:00:00 C 36 Tout Va Bien Manhattan 2012-03-08 01:00:00 C 7 La Grenouille Manhattan 2014-04-09 02:00:00 A 10 La Grenouille Manhattan 2013-03-05 01:00:00 A 9 La Grenouille Manhattan 2012-02-02 01:00:00 A 13 Le Perigord Manhattan 2014-07-14 02:00:00 B 14 SELECT name, grade, score, date FROM t1 WHERE borough = 'Bronx'; name grade score date Bistro Sk A 10 2014-11-21 01:00:00 Bistro Sk A 12 2014-02-19 01:00:00 Bistro Sk B 18 2013-06-12 02:00:00 DROP TABLE t1; # # try level 2 discovery # CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME=restaurants FILTER='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' COLIST='{"projection":{"cuisine":0}}' OPTION_LIST='Driver=C,level=2,version=0'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `_id` char(24) NOT NULL, `address_building` char(6) NOT NULL `FIELD_FORMAT`='address.building', `address_coord_0` double(12,6) NOT NULL `FIELD_FORMAT`='address.coord.0', `address_street` char(25) NOT NULL `FIELD_FORMAT`='address.street', `address_zipcode` char(5) NOT NULL `FIELD_FORMAT`='address.zipcode', `borough` char(13) NOT NULL, `grades_0_date` datetime NOT NULL `FIELD_FORMAT`='grades.0.date', `grades_0_grade` char(14) NOT NULL `FIELD_FORMAT`='grades.0.grade', `grades_0_score` int(11) NOT NULL `FIELD_FORMAT`='grades.0.score', `name` char(32) NOT NULL, `restaurant_id` char(8) NOT NULL ) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='MONGO' `TABNAME`='restaurants' `COLIST`='{"projection":{"cuisine":0}}' `FILTER`='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' `OPTION_LIST`='Driver=C,level=2,version=0' SELECT name, borough, address_street, grades_0_score AS score FROM t1 WHERE grades_0_grade = 'B'; name borough address_street score Le Gamin Brooklyn Vanderbilt Avenue 24 Bistro 33 Queens Ditmars Boulevard 15 Dirty Pierres Bistro Queens Station Square 22 Santos Anne Brooklyn Union Avenue 26 Le Paddock Brooklyn Prospect Avenue 17 La Crepe Et La Vie Brooklyn Foster Avenue 24 Francis Cafe Queens Ditmars Boulevard 19 DROP TABLE t1; # # try CRUD operations # false CREATE TABLE t1 (_id INT(4) NOT NULL, msg CHAR(64)) ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME='testcoll' OPTION_LIST='Driver=C,Version=0' ; DELETE FROM t1; INSERT INTO t1 VALUES(0,NULL),(1,'One'),(2,'Two'),(3,'Three'); SELECT * FROM t1; _id msg 0 NULL 1 One 2 Two 3 Three UPDATE t1 SET msg = 'Deux' WHERE _id = 2; DELETE FROM t1 WHERE msg IS NULL; SELECT * FROM t1; _id msg 1 One 2 Deux 3 Three DELETE FROM t1; DROP TABLE t1; true # # List states whose population is equal or more than 10 millions # false CREATE TABLE t1 ( _id char(5) NOT NULL, city char(16) NOT NULL, loc_0 double(12,6) NOT NULL `FIELD_FORMAT`='loc.0', loc_1 char(12) NOT NULL `FIELD_FORMAT`='loc.1', pop int(11) NOT NULL, state char(2) NOT NULL) ENGINE=CONNECT CONNECTION='mongodb://localhost:27017' TABLE_TYPE=MONGO TABNAME='cities' OPTION_LIST='Driver=C,Version=0' DATA_CHARSET='utf8'; # Using SQL for grouping SELECT state, sum(pop) AS totalPop FROM t1 GROUP BY state HAVING totalPop >= 10000000 ORDER BY totalPop DESC; state totalPop CA 29754890 NY 17990402 TX 16984601 FL 12686644 PA 11881643 IL 11427576 OH 10846517 DROP TABLE t1; # Using a pipeline for grouping CREATE TABLE t1 (_id CHAR(2) NOT NULL, totalPop INT(11) NOT NULL) ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME='cities' DATA_CHARSET=utf8 COLIST='{"pipeline":[{"$group":{"_id":"$state","totalPop":{"$sum":"$pop"}}},{"$match":{"totalPop":{"$gte":10000000}}},{"$sort":{"totalPop":-1}}]}' OPTION_LIST='Driver=C,Version=0,Pipeline=1' ; SELECT * FROM t1; _id totalPop CA 29754890 NY 17990402 TX 16984601 FL 12686644 PA 11881643 IL 11427576 OH 10846517 DROP TABLE t1; true # # Test making array # CREATE TABLE t1 ( _id int(4) NOT NULL, item CHAR(8) NOT NULL, prices_0 INT(6) FIELD_FORMAT='prices.0', prices_1 INT(6) FIELD_FORMAT='prices.1', prices_2 INT(6) FIELD_FORMAT='prices.2', prices_3 INT(6) FIELD_FORMAT='prices.3', prices_4 INT(6) FIELD_FORMAT='prices.4') ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME='testcoll' DATA_CHARSET=utf8 OPTION_LIST='Driver=C,Version=0' ; INSERT INTO t1 VALUES (1,'journal',87,45,63,12,78), (2,'notebook',123,456,789,NULL,NULL), (3,'paper',5,7,3,8,NULL), (4,'planner',25,71,NULL,44,27), (5,'postcard',5,7,3,8,NULL); SELECT * FROM t1; _id item prices_0 prices_1 prices_2 prices_3 prices_4 1 journal 87 45 63 12 78 2 notebook 123 456 789 NULL NULL 3 paper 5 7 3 8 NULL 4 planner 25 71 NULL 44 27 5 postcard 5 7 3 8 NULL DROP TABLE t1; # # Test array aggregation # CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME='testcoll' COLIST='{"pipeline":[{"$project":{"_id":0,"item":1,"total":{"$sum":"$prices"},"average":{"$avg":"$prices"}}}]}' OPTION_LIST='Driver=C,Version=0,Pipeline=YES' ; SELECT * FROM t1; item total average journal 285 57.000000 notebook 1368 456.000000 paper 23 5.750000 planner 167 41.750000 postcard 23 5.750000 DROP TABLE t1; true set connect_enable_mongo=0;