DolphinDB/modules/sqlUtilities.dos

module fundit::sqlUtilities

 
/*
 *  MySQL 连接，使用前应确保 loadPlugin("ODBC")已经被运行过
 *
 *  Create  20240711  使用ODBC连接MySQL数据库                                     Joey
 *  
 */
def connect_mysql() {

    // 阿里云的mysql被魔改过，当前DolphinDB无法支持MySQL插件，只能用ODBC
    // loadPlugin("ODBC")

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

    // 使用Windows的ODBC数据源事先设置号的连接
    // conn = odbc::connect("Dsn=FunditDB-mfdb")
    conn = odbc::connect("Dsn=FunditDB-dev-mfdb")

//    t = odbc::query(conn, "SELECT * FROM pfdb.pf_portfolio_nav LIMIT 100")

    return conn
}

/*
 *  取本地数据库
 *
 *  get_local_database("fundit", "mfdb")
 */
def get_local_database(server_name, db_name) {

    db = database(directory="D:/Program Files/DolphinDB/server/database/" + server_name + "/" + db_name + "/")
    
    return db
}

/*
 * 读本地dolphindb数据表
 *
 * load_table_from_local("fundit", mfdb.fund_performance")
 */
def load_table_from_local(server_name, table_name) {

    db = get_local_database(server_name, table_name.split(".")[0])
    
    return loadTable(db, table_name.split(".")[1])

}

/*
 * 存数据表到mySQL或本地dolphindb，原数据会被替代！
 *
 * save_table(tb_fund_performance, "mfdb.fund_performance", false)
 */

def save_table(tb, table_name, isToMySQL) {


    if(isToMySQL == true) {
    
        tb.addColumn("creatorid" "createtime" "updaterid" "updatetime" "isvalid", [INT, DATETIME, INT, DATETIME, INT])
        
        UPDATE tb SET creatorid = 888888, createtime = now(), updaterid = null, updatetime = null, isvalid = 1
    
        conn = connect_mysql()
        
        odbc::execute(conn, "TRUNCATE TABLE " + table_name + "_dolphin")
        
        odbc::append(conn, tb, table_name + "_dolphin", false)
        
        conn.close()
    
    } else {
    
        db = get_local_database("fundit", table_name.split(".")[0])
    
        saveTable(db, tb, table_name.split(".")[1])
    }
    
}

/*
 * 存私募基金净值到本地dolphindb
 *
 * save_hedge_fund_nav_to_local(tb_nav)
 */
def save_hedge_fund_nav_to_local(tb_nav) {

    save_table(tb_nav, "mfdb.nav", false)

}

/*
 *  根据不同类型的主体返回其净值表的表名、字段名和ID前两位特征字符
 */
def get_nav_table_description(entity_type) {

    tmp_universe = table(100:0, 
                         ['type', 'table_name', 'sec_id_col', 'cumulative_nav_col', 'nav_col', 'prefix'],
                         [STRING, STRING, STRING, STRING, STRING, STRING]);

    // 分别对应：私募，公募，私有基金，股票，市场指数，图译指数，私有指数，图译因子，组合
    INSERT INTO tmp_universe VALUES  ( ['HF', 'MF', 'CF', 'EQ', 'MI', 'FI', 'CI', 'FA', 'PF'],
        ['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'],
        ['fund_id', 'fund_id', 'fund_id', 'sec_id', 'index_id', 'index_id', 'index_id', 'factor_id', 'portfolio_id'],
        ['cumulative_nav', 'cumulative_nav', 'cumulative_nav', 'cumulative_nav', 'close', 'index_value', 'cumulative_nav', 'factor_value', 'cumulative_nav'],
        ['nav', 'nav', 'nav', 'nav', 'close', 'index_value', 'cumulative_nav', 'factor_value', 'cumulative_nav'],
        ['HF', 'MF', 'CF', 'EQ', 'IN', 'IN', 'CI', 'FA', '']);

    return (SELECT * FROM tmp_universe u WHERE u.type = entity_type);
    
}

/*
 *  根据不同类型的主体返回其业绩表的表名、字段名和ID前两位特征字符
 */
def get_performance_table_description(entity_type) {

    tmp_universe = table(100:0, 
                         ['type', 'table_name', 'sec_id_col', 'cumulative_nav_col'],
                         [STRING, STRING, STRING, STRING]);

    // 分别对应：公私募，私有基金，股票，市场/图译指数，私有指数，图译因子，组合
    INSERT INTO tmp_universe VALUES ( 
        ['FD', 'CF', 'EQ', 'IX', 'CI', 'FA', 'PF'],
        ['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'],
        ['fund_id', 'fund_id', 'sec_id', 'fund_id', 'index_id', 'factor_id', 'portfolio_id'],
        ['cumulative_nav', 'cumulative_nav', 'cumulative_nav', 'cumulative_nav', 'cumulative_nav', 'factor_value', 'cumulative_nav'] );

    return (SELECT * FROM tmp_universe u WHERE u.type = entity_type);
    
}