# coding: utf-8


"""Get Drupal system table records for modules with MySQL.

Usage:
1. Change parameters for database in config_mysql in the top of code.
2. Run this as a script: python this_script_name.pyj

Exported info:
- project
- module name
- status
- version

Sample output:
...
views_date_format_sql,views_date_format_sql,0,7.x-3.1
views_flipped_table,views_flipped_table,0,7.x-1.0
views_php,views_php,0,7.x-1.0-alpha1
views_rules,views_rules,0,7.x-1.0
views_rules,views_rules_test,0,7.x-1.0
...
"""


import sys
import csv
import pymysql
import phpserialize


# Change this config parameters to match your environment.
config_mysql = {
    # Example:
    # host: localhost
    # user: taro
    # password: taro_the_dog
    # db: drupal7
    # charset: utf8, utf8mb4, latin1
    'host': 'YOUR_DB_HOST',
    'user': 'YOUR_DB_USERNAME',
    'password': 'YOUR_DB_PASSWORD',
    'db': 'YOUR_DB_NAME',
    'charset': 'YOUR_DB_CHARSET',
}


def main():
    """Main function when being run as a script.
    """
    modules = select_drupal_system_table_flattened()
    print_rows_as_csv(modules)


def connect():
    """Connect to a MySQL database table.
    """
    config_mysql_instance = config_mysql.copy()
    config_mysql_instance['cursorclass'] = pymysql.cursors.DictCursor

    connection = pymysql.connect(**config_mysql_instance)
    return connection


def select(sql, params, single=False):
    """Run SELECT query.

    Usage:
    select('SELECT `uid`, `pass` FROM `users`', (), False)
    select("SELECT `name`, `type`, `status`, `info` FROM `system`", (), True)
    """
    connection = connect()
    try:
        with connection.cursor() as cursor:
            cursor.execute(sql, params)
            if single:
                result = cursor.fetchone()
            else:
                result = cursor.fetchall()
            return result
    finally:
        connection.close()


def select_phpunserialize(sql, params, phpload_columns):
    """Run SELECT query and unserialize selected columns.

    Usage:
    select_phpunserialize(
        "SELECT `name`,`type`,`status`, `info` FROM `system` WHERE `type`= %s",
        ('module'), ['info'])
    """
    result_raw = select(sql, params)
    def _map(row):
        for k, v in row.items():
            if k in phpload_columns:
                row[k] = phpserialize.loads(v)
        return row
    return list(map(_map, result_raw))


def select_drupal_system_table_flattened():
    """Fetch all records for module in `system` table.
    """
    sql = "SELECT `name`,`type`,`status`,`info` FROM `system` WHERE `type` = %s"
    params = ('module', )
    phpload_columns = ['info']
    modules = select_phpunserialize(sql, params, phpload_columns)

    def _flatten(row):
        return map(_to_str, [
            row['info'].get(b'project'),
            row['name'],
            row['status'],
            row['info'][b'version']])

    def _to_str(value):
        if value is None:
            return ''
        if isinstance(value, bytes):
            return value.decode()
        else:
            return value

    return map(_flatten, modules)


def print_rows_as_csv(rows):
    """Print out rows of list in csv format.
    """
    writer = csv.writer(sys.stdout)
    writer.writerows(rows)


if __name__ == '__main__':
    main()