<jupyter_start><jupyter_text>PonyORM MDF Indexer  Imports<jupyter_code>import asammdf
import hashlib
import get_files
import pony.orm
from pony.orm.core import EntityMeta
from pony import orm
import py
import time
import os
import configparser

pony.orm.set_sql_debug(False)
config = configparser.ConfigParser()
config.read('../config.ini')


db = pony.orm.Database(provider="mysql",
                 host=config["mysql"]["host"],
                 user=config["mysql"]["user"],
                 passwd=config["mysql"]["pass"],
                 db=config["mysql"]["db"])
db.generate_mapping()
db.drop_all_tables()<jupyter_output><empty_output><jupyter_text>Database Setup<jupyter_code>db = pony.orm.Database(provider="mysql",
                 host=config["mysql"]["host"],
                 user=config["mysql"]["user"],
                 passwd=config["mysql"]["pass"],
                 db=config["mysql"]["db"])
class MDF(db.Entity):
    name = pony.orm.Required(
        str,
    )
    version = pony.orm.Required(
        str,
    )
    sha256 = pony.orm.Required(
        str, 
        unique=True,
    )
    size = pony.orm.Optional(
        int, 
    )
    size_mb = pony.orm.Optional(
        float, 
    )
    atime = pony.orm.Optional(
        float,
    )
    channels = pony.orm.Set(
        'Channel',
    )
    def __repr__(self):
        return "MDF<{}>".format(self.name)
    
    
class Channel(db.Entity):
    name = pony.orm.Required(
        str,
        unique=True,
    )
    mdfs = pony.orm.Set(
        "MDF",
    )
    def __repr__(self):
        return "Channel<{}>".format(self.name)
        
db.generate_mapping(create_tables=True)<jupyter_output><empty_output><jupyter_text>Helper Functions<jupyter_code>def upsert(cls, get, set=None):
    """
    Interacting with Pony entities.

    :param cls: The actual entity class
    :param get: Identify the object (e.g. row) with this dictionary
    :param set: Additional fields to set if ```get``` returns nothing.
    :return:
    """
    # does the object exist
    assert isinstance(cls, EntityMeta), "{cls} is not a database entity".format(cls=cls)

    # if no set dictionary has been specified
    set = set or {}

    if not cls.exists(**get):
        # make new object
        return cls(**set, **get)
    else:
        # get the existing object
        obj = cls.get(**get)
        for key, value in set.items():
            obj.__setattr__(key, value)
        return obj


channels_cache = {}

def index_data_file(data_file):
    """Index ASAMMDF Data File

    :param data_file: Path to ASAM MDF data file
    :return MDF: PonyORM MDF class
    """
    data_file_ = py.path.local(
        path=data_file,
    )
    
    mdf = asammdf.MDF(data_file)

    channels = list()
    mdf.channels_db.keys()
    for channel in mdf.channels_db.keys():
        if channel in channels_cache:
            channel_ = channels_cache[channel]
        else:
            channel_ = upsert(Channel, {"name": channel})
            channels_cache[channel] = channel_
            db.commit()
        
        channels.append(channel_)

    sha256 = data_file_.computehash(
        hashtype="sha256",
    )
        
    MDF_ = upsert(
        cls=MDF,
        get={"sha256": sha256},
        set={
            "name": data_file_.basename,
            "version": mdf.version,
            "size": data_file_.size(),
            "size_mb": data_file_.size()/1024 ** 2,
            "atime": data_file_.atime(),            
            "channels": channels,
        },
    )

    db.commit()
    
    return MDF_
data_files = get_files.get_files(
    directory="../Data/",
    extensions=[".mdf", ".mf4"],
)
for data_file in data_files:
    break    
t1=time.time()
M = index_data_file(
    data_file=data_file,
)
t2=time.time()

print("Elapsed Indexing Time: {}".format(t2-t1))
list(M.channels)
M
M.sha256
len(M.channels)
M.channels.count()
q = M.channels.select()
q.show()
t1=time.time()
for data_file in data_files:
    M = index_data_file(
        data_file=data_file,
    )
t2=time.time()

print("Elapsed Indexing Time: {}".format(t2-t1))    
mdfs = MDF.select()
mdfs.count()
for mdf in mdfs:
mdf.size_mb
channels = pony.orm.select(channel for channel in Channel if 'eng' in channel.name)
list(channels)
f = list(pony.orm.select(mdf for mdf in MDF).order_by(MDF.size))
f[0].size_mb
list(f[0].channels)
f[-1].size_mb
list(f[-1].channels)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1344632768361582, 'fraction_numerical': 0.008135593220338983, 'mean_word_length': 2.4578125, 'pattern_counts': {'":': 8, '<': 15, '<?xml version=': 0, '>': 15, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '389291', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 1507, 'n_tokens_neox': 1476, 'n_words': 311, 'path': 'DevNotebooks/IndexMDF-MySQL.ipynb', 'repo_name': 'jed-frey/python_MDF_Indexer', 'size': '4425'}