firstrunmigrate/scripts/shares_export.py

from json import loads, dump
from subprocess import run, PIPE
from argparse import ArgumentParser
from pathlib import Path


def main(path : str, php :str, output_shares_path : str):
    output_shares = Path(output_shares_path)

    out = run([php, "occ", "config:list", "--private", "--output", "json"], cwd=path, check=True,
            stdout=PIPE, stderr=PIPE)
    config = loads(out.stdout.decode().strip())

    db_type = config["system"]["dbtype"]
    db_socket = None
    db_host = config["system"]["dbhost"].split(":")
    if len(db_host) == 1:
        db_port = config["system"]["dbport"]
    elif db_host[1].isdigit():
        db_port = db_host[1]
    else:
        db_socket = db_host[1]
        db_port = None
    db_host = db_host[0]
    db_user = config["system"]["dbuser"]
    db_password = config["system"]["dbpassword"]
    db_name = config["system"]["dbname"]
    db_prefix = config["system"]["dbtableprefix"]

    shares = []
    if db_type == "mysql":
        query = f"""SET SESSION group_concat_max_len=4294967295;
SELECT
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'type', s.share_type,
            'permissions', s.permissions,
            'by', p_initiator.configvalue,
            'owner', p_owner.configvalue,
            'path', CASE
                        WHEN fc.path LIKE '__groupfolders/%'
                        THEN (
                                SELECT CONCAT('__groupfolders/', CONCAT(ggf.mount_point, REGEXP_REPLACE(fc.path, '^__groupfolders/[0-9]+(/.*)', '\\\\1')))
                                FROM {db_prefix}group_folders ggf
                                WHERE ggf.folder_id = REGEXP_REPLACE(fc.path, '^__groupfolders/([0-9]+)/.*', '\\\\1')
                            )
                        ELSE fc.path
                    END,
            'target', s.file_target,
            'with', CASE
                        WHEN s.share_type = 0
                        THEN p_with.configvalue
                        WHEN s.share_type = 1
                        THEN s.share_with
                        ELSE null
                    END,
            'attributes', s.attributes,
            'expiration', s.expiration,
            'note', s.note,
            'accepted', s.accepted,
            'group_users_accepted', CASE
                                        WHEN s.share_type = 1
                                        THEN (
                                            SELECT
                                                JSON_OBJECTAGG(
                                                    ss_with.configvalue,
                                                    JSON_OBJECT(
                                                        'accepted', ss.accepted,
                                                        'target', ss.file_target
                                                    )
                                                )
                                            FROM {db_prefix}share ss
                                            INNER JOIN {db_prefix}preferences ss_with ON ss_with.userid = ss.share_with AND ss_with.appid = 'settings' AND ss_with.configkey = 'email'
                                            WHERE ss.parent = s.id
                                        )
                                        ELSE null
                                    END
        )
    )
FROM {db_prefix}share s
INNER JOIN {db_prefix}preferences p_initiator ON p_initiator.userid = s.uid_initiator AND p_initiator.appid = 'settings' AND p_initiator.configkey = 'email'
INNER JOIN {db_prefix}preferences p_owner ON p_owner.userid = s.uid_owner AND p_owner.appid = 'settings' AND p_owner.configkey = 'email'
INNER JOIN {db_prefix}filecache fc ON fc.fileid = file_source
LEFT JOIN {db_prefix}preferences p_with ON p_with.userid = s.share_with AND p_with.appid = 'settings' AND p_with.configkey = 'email'
WHERE s.share_type IN (0, 1)
AND (
        fc.path LIKE 'files/%'
        OR
        fc.path LIKE '__groupfolders/%'
    );
"""
        args = ["mysql", "-Ns", "-h", db_host]
        if db_port:
            args += ["-P", db_port]
        elif db_socket:
            args += ["-S", db_socket]
        args += ["-u", db_user, f"-p{db_password}", db_name]

        out = run(args, cwd=path, check=True, input=query, stdout=PIPE, text=True)
        shares = loads(out.stdout.replace('\\\\', '\\'))
    elif db_type == "psql":
        query = f"""SELECT
    jsonb_agg(
        jsonb_build_object(
            'type', s.share_type,
            'permissions', s.permissions,
            'by', p_initiator.configvalue,
            'owner', p_owner.configvalue,
            'path', TO UPDATE,
            'target', s.file_target,
            'with', CASE
                        WHEN s.share_type = 0
                        THEN p_with.configvalue
                        WHEN s.share_type = 1
                        THEN s.share_with
                        ELSE null
                    END,
            'attributes', s.attributes,
            'expiration', s.expiration,
            'note', s.note,
            'accepted', s.accepted,
            'group_users_accepted', CASE
                                        WHEN s.share_type = 1
                                        THEN (
                                            SELECT
                                                JSONB_OBJECT_AGG(
                                                    ss_with.configvalue,
                                                    jsonb_build_object(
                                                        'accepted', ss.accepted,
                                                        'target', ss.file_target
                                                    )
                                                )
                                            FROM {db_prefix}share ss
                                            INNER JOIN {db_prefix}preferences ss_with ON ss_with.userid = ss.share_with AND ss_with.appid = 'settings' AND ss_with.configkey = 'email'
                                            WHERE ss.parent = s.id
                                        )
                                        ELSE null
                                    END
        )
    )
FROM {db_prefix}share s
INNER JOIN {db_prefix}preferences p_initiator ON p_initiator.userid = s.uid_initiator AND p_initiator.appid = 'settings' AND p_initiator.configkey = 'email'
INNER JOIN {db_prefix}preferences p_owner ON p_owner.userid = s.uid_owner AND p_owner.appid = 'settings' AND p_owner.configkey = 'email'
INNER JOIN {db_prefix}filecache fc ON fc.fileid = file_source
LEFT JOIN {db_prefix}preferences p_with ON p_with.userid = s.share_with AND p_with.appid = 'settings' AND p_with.configkey = 'email'
WHERE s.share_type IN (0, 1);
"""
        pass
    elif db_type == "sqlite":
        pass

    print(f"Got {len(shares)} shares")

    with output_shares.open("w", encoding="UTF-8") as file:
        dump(shares, file)


if __name__ == '__main__':
    parser = ArgumentParser(prog="shares_export")
    parser.add_argument("--path", "-p", default="/var/www/nextcloud")
    parser.add_argument("--php", "-P", default="php")
    parser.add_argument("--output-shares", "-s", default="shares.json")

    args = parser.parse_args()

    main(args.path, args.php, args.output_shares)