def split_tyc(): db = SkyMapDatabase() db.commit_query(""" ALTER TABLE hiptyc_tyc_main ADD COLUMN `TYC1` INT AFTER `TYC`, ADD COLUMN `TYC2` INT AFTER `TYC1`, ADD COLUMN `TYC3` INT AFTER `TYC2` """) db.commit_query("""DROP FUNCTION IF EXISTS SPLIT_TYC""") db.commit_query(""" CREATE FUNCTION SPLIT_TYC(str VARCHAR(255), pos INT) RETURNS INT BEGIN SET str = TRIM(str); WHILE INSTR(str, ' ') > 0 DO SET str = REPLACE(str, ' ', ' '); END WHILE; SET str = REPLACE( SUBSTRING( SUBSTRING_INDEX(str, ' ', pos), CHAR_LENGTH( SUBSTRING_INDEX(str, ' ', pos - 1) ) + 1 ) , ' ', '' ); RETURN CAST(str AS UNSIGNED); END; """) db.commit_query(""" UPDATE hiptyc_tyc_main SET TYC1=SPLIT_TYC(TYC, 1), TYC2=SPLIT_TYC(TYC, 2), TYC3=SPLIT_TYC(TYC, 3) """) db.add_index("hiptyc_tyc_main", "TYC1") db.add_index("hiptyc_tyc_main", "TYC2") db.add_index("hiptyc_tyc_main", "TYC3") db.add_multiple_column_index("hiptyc_tyc_main", ("TYC1", "TYC2", "TYC3"), "TYC", unique=True)
def split_tyc(): db = SkyMapDatabase() db.commit_query(""" ALTER TABLE hiptyc_tyc_main ADD COLUMN `TYC1` INT AFTER `TYC`, ADD COLUMN `TYC2` INT AFTER `TYC1`, ADD COLUMN `TYC3` INT AFTER `TYC2` """) db.commit_query(""" UPDATE hiptyc_tyc_main SET TYC1=CAST(substr(TYC, 1, 4) AS UNSIGNED), TYC2=CAST(substr(hiptyc_tyc_main.TYC, 5, 6) AS UNSIGNED), TYC3=CAST(substr(hiptyc_tyc_main.TYC, 11, 2) AS UNSIGNED) """) db.add_index("hiptyc_tyc_main", "TYC1") db.add_index("hiptyc_tyc_main", "TYC2") db.add_index("hiptyc_tyc_main", "TYC3") db.add_multiple_column_index("hiptyc_tyc_main", ("TYC1", "TYC2", "TYC3"), "TYC", unique=True)
def add_tyc2_index(): db = SkyMapDatabase() db.add_multiple_column_index("tyc2_tyc2", ("TYC1", "TYC2", "TYC3"), "TYC", unique=True)