sqlUtilities.dos 4.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131
  1. module fundit::sqlUtilities
  2. /*
  3. * MySQL 连接,使用前应确保 loadPlugin("ODBC")已经被运行过
  4. *
  5. * Create 20240711 使用ODBC连接MySQL数据库 Joey
  6. *
  7. */
  8. def connect_mysql() {
  9. // 阿里云的mysql被魔改过,当前DolphinDB无法支持MySQL插件,只能用ODBC
  10. // loadPlugin("ODBC")
  11. // conn = odbc::connect("Driver={MySQL ODBC 9.0 UNICODE Driver};Server=funditdb-dev.mysql.rds.aliyuncs.com;Database=mfdb;User=pf_user;Password=MzBlMDA0OG", "MySQL")
  12. // 使用Windows的ODBC数据源事先设置号的连接
  13. // conn = odbc::connect("Dsn=FunditDB-mfdb")
  14. conn = odbc::connect("Dsn=FunditDB-dev-mfdb")
  15. // t = odbc::query(conn, "SELECT * FROM pfdb.pf_portfolio_nav LIMIT 100")
  16. return conn
  17. }
  18. /*
  19. * 取本地数据库
  20. *
  21. * get_local_database("fundit", "mfdb")
  22. */
  23. def get_local_database(server_name, db_name) {
  24. db = database(directory="D:/Program Files/DolphinDB/server/database/" + server_name + "/" + db_name + "/")
  25. return db
  26. }
  27. /*
  28. * 读本地dolphindb数据表
  29. *
  30. * load_table_from_local("fundit", mfdb.fund_performance")
  31. */
  32. def load_table_from_local(server_name, table_name) {
  33. db = get_local_database(server_name, table_name.split(".")[0])
  34. return loadTable(db, table_name.split(".")[1])
  35. }
  36. /*
  37. * 存数据表到mySQL或本地dolphindb,原数据会被替代!
  38. *
  39. * save_table(tb_fund_performance, "mfdb.fund_performance", false)
  40. */
  41. def save_table(tb, table_name, isToMySQL) {
  42. if(isToMySQL == true) {
  43. tb.addColumn("creatorid" "createtime" "updaterid" "updatetime" "isvalid", [INT, DATETIME, INT, DATETIME, INT])
  44. UPDATE tb SET creatorid = 888888, createtime = now(), updaterid = null, updatetime = null, isvalid = 1
  45. conn = connect_mysql()
  46. odbc::execute(conn, "TRUNCATE TABLE " + table_name + "_dolphin")
  47. odbc::append(conn, tb, table_name + "_dolphin", false)
  48. conn.close()
  49. } else {
  50. db = get_local_database("fundit", table_name.split(".")[0])
  51. saveTable(db, tb, table_name.split(".")[1])
  52. }
  53. }
  54. /*
  55. * 存私募基金净值到本地dolphindb
  56. *
  57. * save_hedge_fund_nav_to_local(tb_nav)
  58. */
  59. def save_hedge_fund_nav_to_local(tb_nav) {
  60. save_table(tb_nav, "mfdb.nav", false)
  61. }
  62. /*
  63. * 根据不同类型的主体返回其净值表的表名、字段名和ID前两位特征字符
  64. */
  65. def get_nav_table_description(entity_type) {
  66. tmp_universe = table(100:0,
  67. ['type', 'table_name', 'sec_id_col', 'cumulative_nav_col', 'nav_col', 'prefix'],
  68. [STRING, STRING, STRING, STRING, STRING, STRING]);
  69. // 分别对应:私募,公募,私有基金,股票,市场指数,图译指数,私有指数,图译因子,组合
  70. INSERT INTO tmp_universe VALUES ( ['HF', 'MF', 'CF', 'EQ', 'MI', 'FI', 'CI', 'FA', 'PF'],
  71. ['mfdb.nav', 'mfdb.public_nav', 'pfdb.pf_cus_fund_nav', 'mfdb.stock_price', 'mfdb.market_indexes', 'mfdb.indexes_ty_index', 'pfdb.cm_udf_index_nav', 'pfdb.cm_factor_value', 'pfdb.pf_portfolio_nav'],
  72. ['fund_id', 'fund_id', 'fund_id', 'sec_id', 'index_id', 'index_id', 'index_id', 'factor_id', 'portfolio_id'],
  73. ['cumulative_nav', 'cumulative_nav', 'cumulative_nav', 'cumulative_nav', 'close', 'index_value', 'cumulative_nav', 'factor_value', 'cumulative_nav'],
  74. ['nav', 'nav', 'nav', 'nav', 'close', 'index_value', 'cumulative_nav', 'factor_value', 'cumulative_nav'],
  75. ['HF', 'MF', 'CF', 'EQ', 'IN', 'IN', 'CI', 'FA', '']);
  76. return (SELECT * FROM tmp_universe u WHERE u.type = entity_type);
  77. }
  78. /*
  79. * 根据不同类型的主体返回其业绩表的表名、字段名和ID前两位特征字符
  80. */
  81. def get_performance_table_description(entity_type) {
  82. tmp_universe = table(100:0,
  83. ['type', 'table_name', 'sec_id_col'],
  84. [STRING, STRING, STRING]);
  85. // 分别对应:公私募,私有基金,股票,市场/图译指数,私有指数,图译因子,组合
  86. INSERT INTO tmp_universe VALUES ( ['FD', 'CF', 'EQ', 'IX', 'CI', 'FA', 'PF'],
  87. ['mfdb.fund_performance', 'pfdb.pf_cus_fund_performance', 'mfdb.stock_performance', 'mfdb.fund_performance', 'pfdb.cm_udf_index_performance', 'pfdb.cm_factor_performance', 'pfdb.pf_portfolio_performance'],
  88. ['fund_id', 'fund_id', 'sec_id', 'fund_id', 'index_id', 'factor_id', 'portfolio_id']);
  89. return (SELECT * FROM tmp_universe u WHERE u.type = entity_type);
  90. }