客服电话
APP下载
迪恩网络APP
随时随地掌握行业动态
官方微信
扫描二维码
关注迪恩网络微信公众号
开源软件名称：xmysql开源软件地址：https://gitee.com/yunwisdoms/xmysql开源软件介绍：
Xmysql : One command to generate REST APIs for any MySql databaseWhy this ? Generating REST APIs for a MySql database which does not follow conventions offrameworks such as rails, django, laravel etc is a small adventure that one like to avoid .. Hence this. Setup and Usagexmysql requires node >= 7.6.0 npm install -g xmysqlxmysql -h localhost -u mysqlUsername -p mysqlPassword -d databaseNamehttp://localhost:3000That is it! Simple and minimalistic! Happy hackery! Example : Generate REST APIs for MagentoPowered by popular node packages : (express, mysql) => { xmysql } Features
Use HTTP clients like Postman or similar tools to invoke REST API calls Download node,mysql(setup mysql),sample database -if you haven't on your system. API Overview
Relational Tablesxmysql identifies foreign key relations automatically and provides GET api. /api/blogs/103/commentseg: blogs is parent table and comments is child table. API invocation will result in all comments for blog primary key 103. Support for composite primary keys___ (three underscores)/api/payments/103___JM555205___ : If there are multiple primary keys - separate them by three underscores as shown Pagination_p & _size_p indicates page and _size indicates size of response rows By default 20 records and max of 100 are returned per GET request on a table. /api/payments?_size=50/api/payments?_p=2/api/payments?_p=2&_size=50When _size is greater than 100 - number of records defaults to 100 (i.e maximum) When _size is less than or equal to 0 - number of records defaults to 20 (i.e minimum) Order by / SortingASC/api/payments?_sort=column1eg: sorts ascending by column1 DESC/api/payments?_sort=-column1eg: sorts descending by column1 Multiple fields in sort/api/payments?_sort=column1,-column2eg: sorts ascending by column1 and descending by column2 Column filtering / Fields/api/payments?_fields=customerNumber,checkNumbereg: gets only customerNumber and checkNumber in response of each record /api/payments?_fields=-checkNumbereg: gets all fields in table row but not checkNumber Row filtering / WhereComparison operatorseq - '=' - (colName,eq,colValue)ne - '!=' - (colName,ne,colValue)gt - '>' - (colName,gt,colValue)gte - '>=' - (colName,gte,colValue)lt - '<' - (colName,lt,colValue)lte - '<=' - (colName,lte,colValue)is - 'is' - (colName,is,true/false/null)in - 'in' - (colName,in,val1,val2,val3,val4)bw - 'between' - (colName,bw,val1,val2) like - 'like' - (colName,like,~name) note: use ~ in place of % nlike - 'not like' - (colName,nlike,~name) note: use ~ in place of %Use of comparison operators/api/payments?_where=(checkNumber,eq,JM555205)~or((amount,gt,200)~and(amount,lt,2000))Logical operators~or - 'or'~and - 'and'~xor - 'xor'Use of logical operatorseg: simple logical expression /api/payments?_where=(checkNumber,eq,JM555205)~or(checkNumber,eq,OM314933)eg: complex logical expression /api/payments?_where=((checkNumber,eq,JM555205)~or(checkNumber,eq,OM314933))~and(amount,gt,100)eg: logical expression with sorting(_sort), pagination(_p), column filtering (_fields) /api/payments?_where=(amount,gte,1000)&_sort=-amount&p=2&_fields=customerNumbereg: filter of rows using _where is available for relational route URLs too. /api/offices/1/employees?_where=(jobTitle,eq,Sales%20Rep)FindOne/api/tableName/findOne?_where=(id,eq,1)Works similar to list but only returns top/one result. Used in conjunction with _where Count/api/tableName/countReturns number of rows in table Exists/api/tableName/1/existsReturns true or false depending on whether record exists Group By Having as query params
/api/offices?_groupby=countryeg: SELECT country,count(*) FROM offices GROUP BY country /api/offices?_groupby=country&_having=(_count,gt,1)eg: SELECT country,count(1) as _count FROM offices GROUP BY country having _count > 1 Group By Having as API
/api/offices/groupby?_fields=countryeg: SELECT country,count(*) FROM offices GROUP BY country /api/offices/groupby?_fields=country,cityeg: SELECT country,city,count(*) FROM offices GROUP BY country,city /api/offices/groupby?_fields=country,city&_having=(_count,gt,1)eg: SELECT country,city,count(*) as _count FROM offices GROUP BY country,city having _count > 1 Group By, Order By
/api/offices/groupby?_fields=country,city&_sort=cityeg: SELECT country,city,count(*) FROM offices GROUP BY country,city ORDER BY city ASC /api/offices/groupby?_fields=country,city&_sort=city,countryeg: SELECT country,city,count(*) FROM offices GROUP BY country,city ORDER BY city ASC, country ASC /api/offices/groupby?_fields=country,city&_sort=city,-countryeg: SELECT country,city,count(*) FROM offices GROUP BY country,city ORDER BY city ASC, country DESC Aggregate functions
http://localhost:3000/api/payments/aggregate?_fields=amountresponse body[ { "min_of_amount": 615.45, "max_of_amount": 120166.58, "avg_of_amount": 32431.645531, "sum_of_amount": 8853839.23, "stddev_of_amount": 20958.625377426568, "variance_of_amount": 439263977.71130896 }]eg: retrieves all numeric aggregate of a column in a table http://localhost:3000/api/orderDetails/aggregate?_fields=priceEach,quantityOrderedresponse body[ { "min_of_priceEach": 26.55, "max_of_priceEach": 214.3, "avg_of_priceEach": 90.769499, "sum_of_priceEach": 271945.42, "stddev_of_priceEach": 36.576811252187795, "variance_of_priceEach": 1337.8631213781719, "min_of_quantityOrdered": 6, "max_of_quantityOrdered": 97, "avg_of_quantityOrdered": 35.219, "sum_of_quantityOrdered": 105516, "stddev_of_quantityOrdered": 9.832243813502942, "variance_of_quantityOrdered": 96.67301840816688 }]eg: retrieves numeric aggregate can be done for multiple columns too Union of multiple group by statements
:fire::fire:[ HOTNESS ALERT ] Group by multiple columns in one API call using _fields query params - comes really handy http://localhost:3000/api/employees/ugroupby?_fields=jobTitle,reportsToresponse body{ "jobTitle":[ { "Sales Rep":17 }, { "President":1 }, { "Sale Manager (EMEA)":1 }, { "Sales Manager (APAC)":1 }, { "Sales Manager (NA)":1 }, { "VP Marketing":1 }, { "VP Sales":1 } ], "reportsTo":[ { "1002":2 }, { "1056":4 }, { "1088":3 }, { "1102":6 }, { "1143":6 }, { "1621":1 } { "":1 }, ]}Chart
:fire::fire::fire::fire::fire::fire: [ HOTNESS ALERT ] Chart API returns distribution of a numeric column in a table It comes in SEVEN powerful flavours
This API returns the number of rows where amount is between (0,25000), (25001,50000) ... /api/payments/chart?_fields=amount&min=0&max=131000&step=25000Response[ { "amount": "0 to 25000", "_count": 107 }, { "amount": "25001 to 50000", "_count": 124 }, { "amount": "50001 to 75000", "_count": 30 }, { "amount": "75001 to 100000", "_count": 7 }, { "amount": "100001 to 125000", "_count": 5 }, { "amount": "125001 to 150000", "_count": 0 }]
This API returns distribution between the step array specified /api/payments/chart?_fields=amount&steparray=0,10000,20000,70000,140000Response[ { "amount": "0 to 10000", "_count": 42 }, { "amount": "10001 to 20000", "_count": 36 }, { "amount": "20001 to 70000", "_count": 183 }, { "amount": "70001 to 140000", "_count": 12 }]
This API returns distribution between each step pair /api/payments/chart?_fields=amount&steppair=0,50000,40000,100000Response[ {"amount":"0 to 50000","_count":231}, {"amount":"40000 to 100000","_count":80}]
This API figures out even distribution of a numeric column in table and returns the data /api/payments/chart?_fields=amountResponse[ { "amount": "-9860 to 11100", "_count": 45 }, { "amount": "11101 to 32060", "_count": 91 }, { "amount": "32061 to 53020", "_count": 109 }, { "amount": "53021 to 73980", "_count": 16 }, { "amount": "73981 to 94940", "_count": 7 }, { "amount": "94941 to 115900", "_count": 3 }, { "amount": "115901 to 130650", "_count": 2 }]
This API returns the number of rows where amount is between (0,25000), (0,50000) ... (0,maxValue) Number of records for amount is counted from min value to extended Range instead of incremental steps /api/payments/chart?_fields=amount&min=0&max=131000&step=25000&range=1Response[ { "amount": "0 to 25000", "_count": 107 }, { "amount": "0 to 50000", "_count": 231 }, { "amount": "0 to 75000", "_count": 261 }, { "amount": "0 to 100000", "_count": 268 }, { "amount": "0 to 125000", "_count": 273 }]
/api/payments/chart?_fields=amount&steparray=0,10000,20000,70000,140000&range=1[ { "amount": "0 to 10000", "_count": 42 }, { "amount": "0 to 20000", "_count": 78 }, { "amount": "0 to 70000", "_count": 261 }, { "amount": "0 to 140000", "_count": 273 }]
/api/payments/chart?_fields=amount&range=1[ { "amount": "-9860 to 11100", "_count": 45 }, { "amount": "-9860 to 32060", "_count": 136 }, ... ]Please Note:_fields in Chart API can only take numeric column as its argument. AutochartIdentifies numeric columns in a table which are not any sort of key and applies chart API as before -feels like magic when there are multiple numeric columns in table while hacking/prototyping and you invoke this API. http://localhost:3000/api/payments/autochart[ { "column": "amount", "chart": [ { "amount": "-9860 to 11100", "_count": 45 }, { "amount": "11101 to 32060", "_count": 91 }, { "amount": "32061 to 53020", "_count": 109 }, { "amount": "53021 to 73980", "_count": 16 }, { "amount": "73981 to 94940", "_count": 7 }, { "amount": "94941 to 115900", "_count": 3 }, { "amount": "115901 to 130650", "_count": 2 } ] }]XJOINXjoin query params and values:_join : List of tableNames alternated by type of join to be made (_j, _ij,_ lj, _rj)alias.tableName : TableName as alias_j : Join [ _j => join, _ij => ij, _lj => left join , _rj => right join)_onNumber : Number 'n' indicates condition to be applied for 'n'th join between (n-1) and 'n'th table in list Simple example of two table join:Sql join query: SELECT pl.field1, pr.field2FROM productlines as pl JOIN products as pr ON pl.productline = pr.productlineEquivalent xjoin query API: /api/xjoin?_join=pl.productlines,_j,pr.products&_on1=(pl.productline,eq,pr.productline)&_fields=pl.field1,pr.field2Multiple tables joinSql join query: SELECT pl.field1, pr.field2, ord.field3FROM productlines as pl JOIN products as pr ON pl.productline = pr.productline JOIN orderdetails as ord ON pr.productcode = ord.productcodeEquivalent xjoin query API: /api/xjoin?_join=pl.productlines,_j,pr.products,_j,ord.orderDetails&_on1=(pl.productline,eq,pr.productline)&_on2=(pr.productcode,eq,ord.productcode)&_fields=pl.field1,pr.field2,ord.field3Explanation:
Example to use : _fields, _where, _p, _size in query params /api/xjoin?_join=pl.productlines,_j,pr.products&_on1=(pl.productline,eq,pr.productline)&_fields=pl.productline,pr.productName&_size=2&_where=(productName,like,1972~)Response: [{"pl_productline":"Classic Cars","pr_productName":"1972 Alfa Romeo GTA"}]Please note :Xjoin response has aliases for fields like below aliasTableName + '_' + columnName. Run dynamic queries
Dynamic queries on a database can be run by POST method to URL localhost:3000/dynamic This is enabled ONLY when using local mysql server i.e -h localhost or -h 127.0.0.1 option. Post body takes two fields : query and params.
POST /dynamic { "query": "select * from ?? limit 1,20", "params": ["customers"] }POST /dynamic URL can have any suffix to it - which can be helpful in prototyping eg: POST /dynamic/weeklyReportPOST /dynamic/user/updateUpload single file
POST /uploadDo POST operation on /upload url with multiform 'field' assigned to local file to be uploaded eg: curl --form file=@/Users/me/Desktop/a.png http://localhost:3000/upload returns uploaded file name else 'upload failed' (Note: POSTMAN has issues with file uploading hence examples with curl) Upload multiple files
POST /uploadsDo POST operation on /uploads url with multiform 'fields' assigned to local files to be uploaded
eg: curl --form files=@/Users/me/Desktop/a.png --form files=@/Users/me/Desktop/b.png http://localhost:3000/uploads returns uploaded file names as string Download file
http://localhost:3000/download?name=fileName
Health
{"process_uptime":3.858,"mysql_uptime":"2595"}Shows up time of Xmysql process and mysql server http://localhost:3000/_health?details=1 {"process_uptime":1.151,"mysql_uptime":"2798","os_total_memory":17179869184,"os_free_memory":2516357120,"os_load_average":[2.29931640625,2.1845703125,2.13818359375],"v8_heap_statistics":{"total_heap_size":24735744,"total_heap_size_executable":5242880,"total_physical_size":23521048,"total_available_size":1475503064,"used_heap_size":18149064,"heap_size_limit":1501560832,"malloced_memory":8192,"peak_malloced_memory":11065664,"does_zap_garbage":0}}Provides more details on process. Infact passing any query param gives detailed health output: example below http://localhost:3000/_health?voila {"process_uptime":107.793,"mysql_uptime":"2905","os_total_memory":17179869184,"os_free_memory":2573848576,"os_load_average":[2.052734375,2.12890625,2.11767578125],"v8_heap_statistics":{"total_heap_size":24735744,"total_heap_size_executable":5242880,"total_physical_size":23735016,"total_available_size":1475411128,"used_heap_size":18454968,"heap_size_limit":1501560832,"malloced_memory":8192,"peak_malloced_memory":11065664,"does_zap_garbage":0}}Version
http://localhost:3000/_version {"Xmysql":"0.4.1","mysql":"5.7.15","node":"8.2.1"}When to use ?
When NOT to use ?
Command line options
Options: -V, --version Output the version number -h, --host <n> Hostname of database -> localhost by default -u, --user <n> Username of database -> root by default -p, --password <n> Password of database -> empty by default -d, --database <n> database schema name -r, --ipAddress <n> IP interface of your server / localhost by default -n, --portNumber <n> Port number for app -> 3000 by default -o, --port <n> Port number of mysql -> 3306 by default -a, --apiPrefix <n> Api url prefix -> /api/ by default -s, --storageFolder <n> Storage folder -> current working dir by default (available only with local) -i, --ignoreTables <n> Comma separated table names to ignore -c, --useCpuCores <n> Specify number of cpu cores to use / 1 by default / 0 to use max -y, --readOnly readonly apis -> false by default -h, --help Output usage information Examples: $ xmysql -u username -p password -d databaseSchema全部评论
专题导读
上一篇：Easy-Admin: Easy-Admin发布时间：2022-02-14下一篇：Goku.WebService: 基于 SpringBoot +Spring + SpringMvc+Mybatis+Dubbo 开发分布式RES ...发布时间：2022-02-14热门推荐
热门话题
阅读排行榜
|
请发表评论