<commit_before>﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLite;
using AudioAlign.Audio.Project;
using System.Diagnostics;

namespace AudioAlign.Audio.Matching.HaitsmaKalker2002 {
    class SQLiteCollisionMap : IFingerprintCollisionMap {

        private class DTO {
            public UInt32 SubFingerprint { get; set; }
            public int TrackNumber { get; set; }
            public int TrackPositionIndex { get; set; }
        }

        private SQLiteConnection db;
        private Dictionary<AudioTrack, int> trackToNumber;
        private Dictionary<int, AudioTrack> numberToTrack;
        private List<DTO> temp;

        public SQLiteCollisionMap() {
            db = new SQLiteConnection(":memory:");
            db.CreateTable<DTO>();

            trackToNumber = new Dictionary<AudioTrack, int>();
            numberToTrack = new Dictionary<int, AudioTrack>();
            temp = new List<DTO>(1000);
        }

        public void Add(SubFingerprint subFingerprint, SubFingerprintLookupEntry lookupEntry) {
            int index = -1;

            if(!trackToNumber.TryGetValue(lookupEntry.AudioTrack, out index)) {
                index = trackToNumber.Count;
                trackToNumber.Add(lookupEntry.AudioTrack, index);
                numberToTrack.Add(index, lookupEntry.AudioTrack);
            }

            if(index == -1) {
                throw new Exception("something's wrong - this should not happen!!");
            }

            var dto = new DTO {
                SubFingerprint = subFingerprint.Value,
                TrackNumber = index,
                TrackPositionIndex = lookupEntry.Index
            };

            temp.Add(dto);

            if (temp.Count == 1000) {
                db.InsertAll(temp);
                temp.Clear();
            }

            //db.Insert();
        }

        public void CreateLookupIndex() {
            var start = DateTime.Now;
            db.Execute("create index if not exists DTO_SubFingerprint on DTO(SubFingerprint)");
            Debug.WriteLine("CreateLookupIndex duration: " + (DateTime.Now - start));
        }

        public void Cleanup() {
            int count = db.ExecuteScalar<int>("select count(*) from DTO");
            Debug.WriteLine("Cleanup count before: " + count);

            db.Execute("delete from DTO where SubFingerprint in (select SubFingerprint from (select SubFingerprint As SubFingerprint, COUNT(*) As Count from DTO group by SubFingerprint) where Count = 1)");

            count = db.ExecuteScalar<int>("select count(*) from DTO");
            Debug.WriteLine("Cleanup count after: " + count);
        }

        public List<SubFingerprint> GetCollidingKeys() {
            CreateLookupIndex();

            var start = DateTime.Now;
            IEnumerable<DTO> result = db.Query<DTO>("select * from (select SubFingerprint As SubFingerprint, COUNT(*) As Count from DTO group by SubFingerprint) where Count > 1");
            List<SubFingerprint> subFingerprints = new List<SubFingerprint>();
            foreach (DTO dto in result) {
                subFingerprints.Add(new SubFingerprint(dto.SubFingerprint));
            }
            Debug.WriteLine("GetCollidingSubFingerprints duration: " + (DateTime.Now - start));
            return subFingerprints;
        }

        public List<SubFingerprintLookupEntry> GetValues(SubFingerprint subFingerprint) {
            //var start = DateTime.Now;
            IEnumerable<DTO> result = db.Query<DTO>("select * from DTO where SubFingerprint = ?", subFingerprint.Value);
            List<SubFingerprintLookupEntry> lookupEntries = new List<SubFingerprintLookupEntry>();
            foreach (DTO dto in result) {
                lookupEntries.Add(new SubFingerprintLookupEntry(numberToTrack[dto.TrackNumber], dto.TrackPositionIndex));
            }
            //Debug.WriteLine("GetLookupEntries duration: " + (DateTime.Now - start));
            return lookupEntries;
        }
    }
}<commit_msg>BUGFIX last < 1000 subfingerprints stayed in buffer list and didn't get inserted into DB table<commit_after>﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLite;
using AudioAlign.Audio.Project;
using System.Diagnostics;

namespace AudioAlign.Audio.Matching.HaitsmaKalker2002 {
    class SQLiteCollisionMap : IFingerprintCollisionMap {

        private class DTO {
            public UInt32 SubFingerprint { get; set; }
            public int TrackNumber { get; set; }
            public int TrackPositionIndex { get; set; }
        }

        private SQLiteConnection db;
        private Dictionary<AudioTrack, int> trackToNumber;
        private Dictionary<int, AudioTrack> numberToTrack;
        private List<DTO> insertBuffer;

        public SQLiteCollisionMap() {
            db = new SQLiteConnection(":memory:");
            db.CreateTable<DTO>();

            trackToNumber = new Dictionary<AudioTrack, int>();
            numberToTrack = new Dictionary<int, AudioTrack>();
            insertBuffer = new List<DTO>(1000);
        }

        public void Add(SubFingerprint subFingerprint, SubFingerprintLookupEntry lookupEntry) {
            int index = -1;

            if(!trackToNumber.TryGetValue(lookupEntry.AudioTrack, out index)) {
                index = trackToNumber.Count;
                trackToNumber.Add(lookupEntry.AudioTrack, index);
                numberToTrack.Add(index, lookupEntry.AudioTrack);
            }

            if(index == -1) {
                throw new Exception("something's wrong - this should not happen!!");
            }

            var dto = new DTO {
                SubFingerprint = subFingerprint.Value,
                TrackNumber = index,
                TrackPositionIndex = lookupEntry.Index
            };

            insertBuffer.Add(dto);

            if (insertBuffer.Count == 1000) {
                InsertBuffered();
            }

            //db.Insert();
        }

        private void InsertBuffered() {
            db.InsertAll(insertBuffer);
            insertBuffer.Clear();
        }

        public void CreateLookupIndex() {
            InsertBuffered();
            var start = DateTime.Now;
            db.Execute("create index if not exists DTO_SubFingerprint on DTO(SubFingerprint)");
            Debug.WriteLine("CreateLookupIndex duration: " + (DateTime.Now - start));
        }

        public void Cleanup() {
            InsertBuffered();

            int count = db.ExecuteScalar<int>("select count(*) from DTO");
            Debug.WriteLine("Cleanup count before: " + count);

            db.Execute("delete from DTO where SubFingerprint in (select SubFingerprint from (select SubFingerprint As SubFingerprint, COUNT(*) As Count from DTO group by SubFingerprint) where Count = 1)");

            count = db.ExecuteScalar<int>("select count(*) from DTO");
            Debug.WriteLine("Cleanup count after: " + count);
        }

        public List<SubFingerprint> GetCollidingKeys() {
            InsertBuffered();
            CreateLookupIndex();

            var start = DateTime.Now;
            IEnumerable<DTO> result = db.Query<DTO>("select * from (select SubFingerprint As SubFingerprint, COUNT(*) As Count from DTO group by SubFingerprint) where Count > 1");
            List<SubFingerprint> subFingerprints = new List<SubFingerprint>();
            foreach (DTO dto in result) {
                subFingerprints.Add(new SubFingerprint(dto.SubFingerprint));
            }
            Debug.WriteLine("GetCollidingSubFingerprints duration: " + (DateTime.Now - start));
            return subFingerprints;
        }

        public List<SubFingerprintLookupEntry> GetValues(SubFingerprint subFingerprint) {
            if (insertBuffer.Count > 0) {
                InsertBuffered();
            }

            //var start = DateTime.Now;
            IEnumerable<DTO> result = db.Query<DTO>("select * from DTO where SubFingerprint = ?", subFingerprint.Value);
            List<SubFingerprintLookupEntry> lookupEntries = new List<SubFingerprintLookupEntry>();
            foreach (DTO dto in result) {
                lookupEntries.Add(new SubFingerprintLookupEntry(numberToTrack[dto.TrackNumber], dto.TrackPositionIndex));
            }
            //Debug.WriteLine("GetLookupEntries duration: " + (DateTime.Now - start));
            return lookupEntries;
        }
    }
}
{'fraction_non_alphanumeric': 0.0954470002371354, 'fraction_numerical': 0.004861275788475219, 'mean_word_length': 2.22192513368984, 'pattern_counts': {'":': 2, '<': 42, '<?xml version=': 0, '>': 44, '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}}
{'dir': 'git-commits-cleaned', 'id': '5530030', 'n_tokens_mistral': 2247, 'n_tokens_neox': 2178, 'n_words': 531}