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)