db_to_all_synced.py

#!/usr/bin/env python
# coding: utf-8

import pandas as pd
import itertools
from src.io_functions import *
from data import *
import omero.clients
from omero.gateway import BlitzGateway
import importlib
import sqlite3
from datetime import timedelta

def create_links():
    """
    Creates links between different data sources in the database.

    This function deletes duplicate objects, tags, and links, and then creates links between
    eGroupWare and Netstore, and between eGroupWare and OMERO based on overlapping data.

    Returns:
    None
    """
    delete_duplicate_objects()
    delete_duplicate_tags()
    delete_duplicate_links()

    db_name = 'sync_database.db'
    check_db_table(db_name, "link")
    omero_df = get_dataframe('omero')
    fs_storage_df = get_dataframe("fs_storage")
    # link_df = get_dataframe("link")
    egroupware_df = get_dataframe("egroupware")
    # Get project name and files from database per object_id
    # (fs_storage_df['object_id'] restricted to "fs_storage")
    # netstore_joined_df = get_netstore_data(db_name, 919)
    netstore_df = get_dataframe('fs_storage')
    omero_df = get_dataframe('omero')

    # Create the link(s):

    check_db_table(db_name, 'link')

    # Create the links in the database between eGroupWare and Netstore
    for project_id in egroupware_df['project_id']:
        # print("project_id", project_id, ": project_name: ", egroupware_df[egroupware_df['project_id'] == project_id]['project_name'].iloc[0])
        for object_id in netstore_df['object_id']:
            netstore_joined_df = get_netstore_data(db_name, object_id)
            egroupware_joined_df = get_egroupware_data(db_name, project_id)
            overlap_df = get_col_overlap_df(
                netstore_joined_df.iloc[0:1], egroupware_joined_df.iloc[0:1], 'object_name_x', 'project_name')
            check = 0
            for p in list(overlap_df['percentage']):
                if p >= 0.8:
                    print(
                        f"I matched the eGroupWare-Netstore: {list(egroupware_joined_df.iloc[0:1]['project_name'])}-{list(netstore_joined_df.iloc[0:1]['object_name_x'])} = {p}")
                    # check = 1

                    link = {'src_id': project_id,
                            'src_table': 'project_registration',
                            'tar_id': object_id,
                            'tar_table': 'object',
                            'tar_source': 'fs_storage',
                            'overlap_ratio': p,
                            'manual_validated': 0,
                            'created_timestamp': datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
                            'modified_timestamp': datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
                            'notes': f"{list(egroupware_joined_df.iloc[0:1]['project_name'])[0]}-{list(netstore_joined_df.iloc[0:1]['object_name_x'])[0]} object[source == 'fs_storage'] get_col_overlap_df(netstore_joined_df.iloc[0:1], egroupware_joined_df.iloc[0:1], 'object_name_x', 'project_name')"}
                    # print(link)
                    insert_dict_to_database(db_name, 'link', link)

    # Create the links between eGroupWare and OMERO in the database
    for project_id in egroupware_df['project_id']:
        # print("project_id", project_id, ": project_name: ", egroupware_df[egroupware_df['project_id'] == project_id]['project_name'].iloc[0])
        for object_id in omero_df['object_id']:
            omero_joined_df = omero_df[omero_df['object_id'] == object_id]
            omero_joined_df = omero_joined_df[omero_joined_df['object_type'] == 'Project']
            egroupware_joined_df = get_egroupware_data(db_name, project_id)
            overlap_df = get_col_overlap_df(
                egroupware_joined_df.iloc[0:1], omero_joined_df.iloc[0:1], 'project_name', 'object_name')
            check = 0
            for p in list(overlap_df['percentage']):
                if p >= 0.8:
                    print(
                        f"I matched the eGroupWare-OMERO: {list(egroupware_joined_df.iloc[0:1]['project_name'])}-{list(omero_joined_df.iloc[0:1]['object_name'])} = {p}")
                    # check = 1

                    link = {'src_id': project_id,
                            'src_table': 'project_registration',
                            'tar_id': object_id,
                            'tar_table': 'object',
                            'tar_source': 'omero',
                            'overlap_ratio': p,
                            'manual_validated': 0,
                            'created_timestamp': datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
                            'modified_timestamp': datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
                            'notes': f"{list(egroupware_joined_df.iloc[0:1]['project_name'])[0]}-{list(omero_joined_df.iloc[0:1]['object_name'])[0]}; object[source == 'omero']; get_col_overlap_df(egroupware_joined_df.iloc[0:1], omero_joined_df.iloc[0:1], 'project_name', 'object_name')"}
                    # print(link)
                    insert_dict_to_database(db_name, 'link', link)

    delete_duplicate_objects()
    delete_duplicate_tags()
    delete_duplicate_links()

def sync_omero():
    """
    Synchronizes OMERO data with the database and performs necessary operations.

    This function inserts OMERO data into the database, creates links between different data sources,
    and synchronizes the data with OMERO.

    Returns:
    None
    """
    auto_insert_omero_to_database()
    db_name = 'sync_database.db'
    docker_vol_path = "/OMERO"
    # netstore_joined_df = get_netstore_data(db_name, 919)
    netstore_df = get_dataframe('fs_storage')
    omero_df = get_dataframe('omero')
    link_df = get_dataframe('link-fs_storage')
    conn = BlitzGateway('inplace', 'omero', host='0.0.0.0', port=4064)
    conn.connect()
    # tags = conn.getObjects("TagAnnotation")
    # Auto insert linked entries to OMERO and RSpace
    for index, row in link_df.iterrows():
        if float(row['overlap_ratio']) >= 0.8:
            # Get a dataframe with eGroupWare info for a specific link
            src_df = get_link_object_from_id(
                db_name, row['src_table'], row['src_id'])
            # Get a dataframe with Netstore info for a specific link
            tar_df = get_link_object_from_id(
                db_name, row['tar_table'], row['tar_id'])
            # Set the name variable to the project name from eGroupWare
            # TODO: (add an artificial "_new" because it already exists, to test the creation)
            try:
                egroupware_project_name = src_df['project_name'][0]
                omero_project_name = egroupware_project_name
                fs_storage_project_folder = tar_df['object_name'][0]
            except:
                print("wrong target df!", row)
                continue
            # TODO: lern_multi3 is too large to fast import in OMERO so skip it!
            # if fs_storage_project_folder == "zlern_multi3":
            #    continue

            #############################################################################
            #### OMERO Sync #############################################################
            # Check if project already exists in OMERO
            # search_result = omero_df[omero_df['project'].str.contains(egroupware_project_name)]
            # if search_result.empty:
            # If the project already exists in OMERO, don't create it again
            project_id = get_object_by_name(egroupware_project_name)
            if check_for_omero_entries(db_name, list(tar_df['object_name'])[0], object_type="Project").empty and project_id == None:
                print("I create: ", egroupware_project_name)
                project = create_object(
                    egroupware_project_name, type="Project")
                project_id = project.getId()
            else:
                continue
            # Get the path of the images to take it as dataset inside the project
            # TODO: Use every specific path for every image (currently it just uses the longest path)
            # TODO: Add the real server path as key:value; also all the other non-bioformat/non-importable data as key:value pairs with links
            src = f"/fs_storage_omero-import/{fs_storage_project_folder}"
            base_path = f"/home/omero-import/{fs_storage_project_folder}"

            filelist = list(get_dataset_fs_storage_name(
                db_name, base_path)['object_name'])
            filelist = sorted(filelist, key=len)
            fs_storage_dataset_folder = get_remaining_path(
                filelist[0], base_path).split("/")[0]
            print(
                f"fs_storage_project_folder = {fs_storage_project_folder}; \n fs_storage_dataset_folder = {fs_storage_dataset_folder}")

            project = conn.getObject("Project", project_id)
            dataset_id = ""
            for child in project.listChildren():
                dataset = conn.getObject("Dataset", child.getId())
                dataset_id = dataset.getId()
            if dataset_id == "":
                dataset = create_object(fs_storage_dataset_folder, "Dataset")
                dataset_id = dataset.getId()
                create_link(project, dataset, "projectdataset")
                create_bulk_import_file(dataset_id,
                                        os.path.join(
                                            src.split("/")[-1], fs_storage_dataset_folder),
                                        docker_vol_path="/NETSTORE_omero-import"
                                        )
            # print(f"try to import: {src} into {project_id}:{dataset.getId()}")
            print("I add to dataset_id = ", dataset_id,
                  os.path.join(src.split("/")[-1], fs_storage_dataset_folder))
            # src path includes the "real folder of the project", but for import you need to include the docker volume mainfolder
            """
            omero_inplace_import(dataset_id,
                                 os.path.join(
                                     src.split("/")[-1], fs_storage_dataset_folder),
                                 docker_vol_path='/NETSTORE_omero-import',
                                 server="localhost",
                                 username="inplace",
                                 password="omero")
            """
            # Create the tags from link_df in OMERO and link it to the specific projects
            # Check if tag already exists:
            tag_tuple = get_tags_tuple(conn)

            # Split the tags string using both separators: "-" & ","
            # possible_tags = [item for sublist in row['egroupware_df-egroupware_df_tags'].split('-') for item in sublist.split(',')]
            # possible_tags =
            tag_df = get_tags_from_id(
                db_name, row['tar_id'], df_type='fs_storage')
            possible_tags = list(tag_df['translated_tag_name'])
            # print(possible_tags)
            tag_descriptions_dict = get_description_dict()
            for p_tag in possible_tags:
                tag_id = is_string_in_list(p_tag, tag_tuple)
                try:
                    if not tag_id:
                        description = get_tag_description(
                            p_tag, tag_descriptions_dict)
                        tag = create_tag(p_tag, description)
                        create_link(project, tag)
                    else:
                        print(f'add tag to project {project.getId()}')
                        tag = conn.getObject("TagAnnotation", tag_id)
                        create_link(project, tag)
                except:
                    print("tag processing error in OMERO db")
            """
    """
    # Import has to be done before RSpace
    auto_insert_omero_to_database()
    conn.close()
    # TODO: Make the bulk import work (can't copy the import-list file, rights issues)
    try:
        omero_inplace_bulk_import(bulk_file_src_path="./data/image_import_files.csv",
                                  bulk_file_tar_path="/home/cni/slicer-omero/omero_server_volume",
                                  docker_vol_path=docker_vol_path,
                                  server="localhost", username="inplace", password="omero")
    except:
        print("no bulk import files, file!, so nothing to import to OMERO")

def sync_rspace():
    """
    Synchronizes RSpace data with the database and performs necessary operations.

    This function retrieves data from the database, creates links between different data sources,
    and synchronizes the data with RSpace.

    Returns:
    None
    """
    netstore_df = get_dataframe('fs_storage')
    omero_df = get_dataframe('omero')
    link_df = get_dataframe('link-fs_storage')
    conn = BlitzGateway('inplace', 'omero', host='0.0.0.0', port=4064)
    conn.connect()
    # tags = conn.getObjects("TagAnnotation")
    # Auto insert linked entries to OMERO and RSpace
    print(len(link_df))
    for index, row in link_df.iterrows():
        if float(row['overlap_ratio']) >= 0.8:
            # Get a dataframe with eGroupWare info for a specific link
            src_df = get_link_object_from_id(
                db_name, row['src_table'], row['src_id'])
            # Get a dataframe with Netstore info for a specific link
            tar_df = get_link_object_from_id(
                db_name, row['tar_table'], row['tar_id'])
            # Set the name variable to the project name from eGroupWare
            # TODO: (add an artificial "_new" because it already exists, to test the creation)
            try:
                egroupware_project_name = src_df['project_name'][0]
                omero_project_name = egroupware_project_name
                fs_storage_project_folder = tar_df['object_name'][0]
            except:
                print("no target df!", row)
                continue
            # TODO: lern_multi3 is too large to fast import in OMERO so skip it!
            # if fs_storage_project_folder == "zlern_multi3":
            #    continue

            ##############################################################################
            #### RSpace Sync #############################################################
            # Placeholder variable to print if a folder and/or document is being created
            new = [0, 0]
            print("fs_storage_folder: ", fs_storage_project_folder)
            print("egroupware_project_name: ", egroupware_project_name)
            tag_df = get_tags_from_id(
                db_name, row['tar_id'], df_type='fs_storage')
            possible_tags = ",".join(list(tag_df['translated_tag_name']))

            header = create_rspace_document_header(
                egroupware_project_name, fs_storage_project_folder, row['overlap_ratio'])
            files = create_rspace_files_table(
                header, row['tar_id'], db_name='sync_database.db')
            # If there are no files in the possible document inside the folder, don't create it
            if files == []:
                continue

            html_table = generate_html_table(files, header)
            # Check if a folder sharing the same name of egroupware_project_name already exists:
            folder_json = search_folder(egroupware_project_name)
            if folder_json == 1:
                new[0] = 1
                # Create the folder in RSpace named by egroupware_project
                folder_json = json.loads(
                    create_rspace_folder(egroupware_project_name))
            # Create the notebook in RSpace named by fs_storage_folder name
            # Use dataset as document name in RSpace
            src = f"/fs_storage_omero-import/{fs_storage_project_folder}"
            base_path = f"/home/omero-import/{fs_storage_project_folder}"
            filelist = list(get_dataset_fs_storage_name(
                db_name, base_path)['object_name'])
            filelist = sorted(filelist, key=len)
            fs_storage_dataset_folder = get_remaining_path(
                filelist[0], base_path).split("/")[0]

            document_json = search_documents(fs_storage_dataset_folder)
            if document_json == 1:
                new[1] = 1
                # Create a document in RSpace named by egroupware_project
                document_json = json.loads(create_rspace_document(
                    fs_storage_dataset_folder, html_table, folder_json['id'], possible_tags))
            if new[0]:
                print(f"I created a new folder: {egroupware_project_name}")
            if new[1]:
                print(f"I created a new document: {fs_storage_dataset_folder}")
            print(
                "########################################################################################")

    conn.close()

def main():
    """
    Main function to execute the synchronization process.

    This function removes the OMERO import file if it exists, creates links between different data sources,
    and synchronizes the data with OMERO and RSpace.

    Returns:
    None
    """
    if os.path.exists("./data/image_import_files.csv"):
        os.remove("./data/image_import_files.csv")
    else:
        print("OMERO import file already reset")
    create_links()
    sync_omero()
    sync_rspace()
"""
if __name__ == "__main__":
    db_name = 'sync_database.db'
    docker_vol_path = "/OMERO"
    main()
"""
    
# TODO: clear the trash code below (if its trash)
"""

    # old code: should not work
    # auto insert linked entries rspace

    link_df = get_dataframe("link")
    print(link_df.head())
    fs_storage_df = get_dataframe("fs_storage")

    for index, row in link_df.iterrows():
        new = [0, 0]
        if float(row['percentage0']) >= 0.8:
            # scrap the possible omero_project_name & the actual folder on fs_storage from diff column in link_df
            tmp = row.iloc[0].split("(")
            egroupware_project_name = tmp[0].strip()
            if len(egroupware_project_name.split("-")) > 1:
                egroupware_project_name = egroupware_project_name.split("-")[0]
            tmp = tmp[-1].split(")")
            # it gives the filesystem folder for
            if tmp[-1][1] == "-":
                fs_storage_folder = tmp[-1][1:].strip()
            else:
                fs_storage_folder = tmp[-1].split("-")[-1]

            print("fs_storage_folder: ", fs_storage_folder)
            print("egroupware_project_name: ", egroupware_project_name)
            header = f"<p>egroupware_project_name: {egroupware_project_name}\n</p>"
            header += f"<p>fs_storage_folder: {fs_storage_folder}\n</p>"
            header += f"<p>similarity ratio (egroupware_project_name; ntstore_folder): {row['percentage0']}\n</p>"
            # create placeholder html_table
            html_table = generate_html_table(files, header)
            # check if a folder sharing the same name of egroupware_project_name already exists:
            folder_json = search_folder(egroupware_project_name)
            if folder_json == 1:
                new[0] = 1
                # create the folder in rspace named by egroupware_project
                folder_json = json.loads(
                    create_rspace_folder(egroupware_project_name))
            # create the notebook in rspace named by fs_storage_folder name
            document_json = search_documents(fs_storage_folder)
            if document_json == 1:
                new[1] = 1
                # create a document in rspace named by egroupware_project
                document_json = json.loads(create_rspace_document(
                    fs_storage_folder, html_table, folder_json['id'], row['egroupware_df-egroupware_df_tags']))
            if new[0]:
                print(f"I created a new folder: {egroupware_project_name}")
            if new[1]:
                print(f"I created a new document: {fs_storage_folder}")
            print("############################################")
    """

# TODO: create tumbnails for the images and readout metadata
"""
    import os
    from pathlib import Path
    import nibabel as nib
    import numpy as np

    def filelist(startpath):
        files = []
        for path in Path(startpath).rglob('*'):
            if path.is_file():
                files.append(str(path))
        return files

    fs_storage_df = get_dataframe("fs_storage")
    for index, row in fs_storage_df.iterrows():
        fs_storage_folder = row['project']
        full_path = f"/home/omero-import/{fs_storage_folder}"
        #print(full_path)
        if os.path.isdir(full_path):
            files = filelist(full_path)
            for i, f in enumerate(files):
                if f.split(".")[-1] == "gz" and f.split(".")[-2] == "nii":
                    print(f)

                    # Load the NIfTI image
                    img = nib.load(f)

                    # Get the image data as a numpy array
                    data = img.get_fdata()

                    # Calculate the maximum intensity projection along the z-axis
                    mip = np.max(data, axis=2)

                    # If you want to save the MIP as a new NIfTI image
                    new_img = nib.Nifti1Image(mip, affine=img.affine)
                    nib.save(new_img, "/".join(f.split("/")[:-1] + ['mip_image.nii']))
                    break
    """