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)

}