<commit_before>//
//  DatabaseUpgradeToVersion19.swift
//  SmartReceipts
//
//  Created by <NAME> on 15/10/2018.
//  Copyright © 2018 <NAME>. All rights reserved.
//

import Foundation

fileprivate let ROWID = "ROWID"
fileprivate let DEPRECATED_PARENT = "parent"

fileprivate typealias TripIdNamePair = (id: Int, name: String)

class DatabaseUpgradeToVersion19: DatabaseMigration {
    
    override func version() -> UInt {
        return 19
    }
    
    override func migrate(_ database: Database) -> Bool {
        return updateTripsPrimaryKey(database: database) &&
            
            updateDistanceParent(database: database) &&
            updateReceiptsParent(database: database) &&
            
            addUUID(table: TripsTable.Name, database: database) &&
            addUUID(table: ReceiptsTable.Name, database: database) &&
            addUUID(table: DistanceTable.Name, database: database) &&
            addUUID(table: CategoriesTable.Name, database: database) &&
            addUUID(table: PaymentMethodsTable.Name, database: database) &&
            addUUID(table: CSVColumnTable.Name, database: database) &&
            addUUID(table: PDFColumnTable.Name, database: database)
    }
    
    private func addUUID(table: String, database: Database) -> Bool {
        var result = database.executeUpdate("ALTER TABLE \(table) ADD COLUMN \(CommonColumns.EntityUUID) TEXT")
        
        var rowIds = [Int]()
        let query = "SELECT \(ROWID) FROM \(table)"
        database.inDatabase { db in
            guard let resultSet = try? db.executeQuery(query, values: nil) else { return }
            while resultSet.next() {
                let rowId = Int(resultSet.int(forColumn: "id"))
                rowIds.append(rowId)
            }
        }
        
        for rowId in rowIds {
            let uuid = UUID().uuidString
            result = result && database.executeUpdate("UPDATE \(table) SET \(CommonColumns.EntityUUID) = '\(uuid)' WHERE \(ROWID) = \(rowId)")
        }
        
        return result
    }
    
    private func updateTripsPrimaryKey(database: Database) -> Bool {
        var result = database.executeUpdate("ALTER TABLE \(TripsTable.Name) RENAME TO \(TripsTable.Name)_tmp")
        
        result = result &&  database.executeUpdate("CREATE TABLE \(TripsTable.Name)(\(TripsTable.Column.Id) INTEGER PRIMARY KEY AUTOINCREMENT, \(TripsTable.Column.Name) TEXT UNIQUE, \(TripsTable.Column.From) DATE, \(TripsTable.Column.To) DATE, \(TripsTable.Column.FromTimezone) TEXT, \(TripsTable.Column.ToTimezone) TEXT, \(TripsTable.Column.Comment) TEXT, \(TripsTable.Column.CostCenter) TEXT, \(TripsTable.Column.DefaultCurrency) TEXT, \(TripsTable.Column.ProcessingStatus) TEXT, \(TripsTable.Column.Filters) TEXT, \(Database.SyncColumns.drive_sync_id) TEXT,\(Database.SyncColumns.drive_is_synced) BOOLEAN DEFAULT 0,\(Database.SyncColumns.drive_marked_for_deletion) BOOLEAN DEFAULT 0,\(Database.SyncColumns.last_local_modification_time) DATE)")
        
        let insertColumns = [
            TripsTable.Column.Name,
            TripsTable.Column.From,
            TripsTable.Column.To,
            TripsTable.Column.FromTimezone,
            TripsTable.Column.ToTimezone,
            TripsTable.Column.Comment,
            TripsTable.Column.CostCenter,
            TripsTable.Column.DefaultCurrency,
            TripsTable.Column.ProcessingStatus,
            TripsTable.Column.Filters,
            SyncStateColumns.Id,
            SyncStateColumns.IsSynced,
            SyncStateColumns.MarkedForDeletion,
            SyncStateColumns.LastLocalModificationTime,
        ].joined(separator: ", ")
        
        result = result &&  database.executeUpdate("INSERT INTO \(TripsTable.Name)(\(insertColumns)) SELECT \(insertColumns) FROM \(TripsTable.Name)_tmp")
        result = result &&  database.executeUpdate("DROP TABLE \(TripsTable.Name)_tmp")
        
        return result
    }
    
    private func updateDistanceParent(database: Database) -> Bool {
        var result = database.executeUpdate("ALTER TABLE \(DistanceTable.Name) ADD COLUMN \(DistanceTable.Column.ParentId) INTEGER")
        
         result = result && database.executeUpdate("UPDATE \(DistanceTable.Name) SET \(ReceiptsTable.Column.ParentId) = (SELECT \(TripsTable.Column.Id) FROM \(TripsTable.Name) WHERE \(TripsTable.Column.Name) = \(DEPRECATED_PARENT) LIMIT 1)")
        
        result = result && database.executeUpdate("ALTER TABLE \(DistanceTable.Name) RENAME TO \(DistanceTable.Name)_tmp")
        
        result = result &&  database.executeUpdate("CREATE TABLE \(DistanceTable.Name) (\(DistanceTable.Column.Id) INTEGER PRIMARY KEY AUTOINCREMENT, \(DistanceTable.Column.ParentId) INTEGER REFERENCES \(TripsTable.Column.Name) ON DELETE CASCADE, \(DistanceTable.Column.Distance) DECIMAL(10, 2) DEFAULT 0.00, \(DistanceTable.Column.Location) TEXT, \(DistanceTable.Column.Date) DATE, \(DistanceTable.Column.Timezone) TEXT, \(DistanceTable.Column.Comment) TEXT, \(DistanceTable.Column.RateCurrency) TEXT NOT NULL, \(DistanceTable.Column.Rate) DECIMAL(10, 2) DEFAULT 0.00, \(Database.SyncColumns.drive_sync_id) TEXT,\(Database.SyncColumns.drive_is_synced) BOOLEAN DEFAULT 0,\(Database.SyncColumns.drive_marked_for_deletion) BOOLEAN DEFAULT 0,\(Database.SyncColumns.last_local_modification_time) DATE)")
        
        
        let insertColumns = [
            DistanceTable.Column.Id,
            DistanceTable.Column.ParentId,
            DistanceTable.Column.Distance,
            DistanceTable.Column.Location,
            DistanceTable.Column.Date,
            DistanceTable.Column.Timezone,
            DistanceTable.Column.Comment,
            DistanceTable.Column.Rate,
            DistanceTable.Column.RateCurrency,
            SyncStateColumns.Id,
            SyncStateColumns.IsSynced,
            SyncStateColumns.MarkedForDeletion,
            SyncStateColumns.LastLocalModificationTime,
        ].joined(separator: ", ")
        
        result = result &&  database.executeUpdate("INSERT INTO \(DistanceTable.Name)(\(insertColumns)) SELECT \(insertColumns) FROM \(DistanceTable.Name)_tmp")
        result = result &&  database.executeUpdate("DROP TABLE \(DistanceTable.Name)_tmp")
        
        return result
    }
    
    private func updateReceiptsParent(database: Database) -> Bool {
        var result = database.executeUpdate("ALTER TABLE \(ReceiptsTable.Name) ADD COLUMN \(ReceiptsTable.Column.ParentId) INTEGER")
        <commit_msg>Added ON DELETE CASCADE to Receipts and Distances in v19 Migration<commit_after>//
//  DatabaseUpgradeToVersion19.swift
//  SmartReceipts
//
//  Created by <NAME> on 15/10/2018.
//  Copyright © 2018 <NAME>. All rights reserved.
//

import Foundation

fileprivate let ROWID = "ROWID"
fileprivate let DEPRECATED_PARENT = "parent"

fileprivate typealias TripIdNamePair = (id: Int, name: String)

class DatabaseUpgradeToVersion19: DatabaseMigration {
    
    override func version() -> UInt {
        return 19
    }
    
    override func migrate(_ database: Database) -> Bool {
        return updateTripsPrimaryKey(database: database) &&
            
            updateDistanceParent(database: database) &&
            updateReceiptsParent(database: database) &&
            
            addUUID(table: TripsTable.Name, database: database) &&
            addUUID(table: ReceiptsTable.Name, database: database) &&
            addUUID(table: DistanceTable.Name, database: database) &&
            addUUID(table: CategoriesTable.Name, database: database) &&
            addUUID(table: PaymentMethodsTable.Name, database: database) &&
            addUUID(table: CSVColumnTable.Name, database: database) &&
            addUUID(table: PDFColumnTable.Name, database: database)
    }
    
    private func addUUID(table: String, database: Database) -> Bool {
        var result = database.executeUpdate("ALTER TABLE \(table) ADD COLUMN \(CommonColumns.EntityUUID) TEXT")
        
        var rowIds = [Int]()
        let query = "SELECT \(ROWID) FROM \(table)"
        database.inDatabase { db in
            guard let resultSet = try? db.executeQuery(query, values: nil) else { return }
            while resultSet.next() {
                let rowId = Int(resultSet.int(forColumn: "id"))
                rowIds.append(rowId)
            }
        }
        
        for rowId in rowIds {
            let uuid = UUID().uuidString
            result = result && database.executeUpdate("UPDATE \(table) SET \(CommonColumns.EntityUUID) = '\(uuid)' WHERE \(ROWID) = \(rowId)")
        }
        
        return result
    }
    
    private func updateTripsPrimaryKey(database: Database) -> Bool {
        var result = database.executeUpdate("ALTER TABLE \(TripsTable.Name) RENAME TO \(TripsTable.Name)_tmp")
        
        result = result &&  database.executeUpdate("CREATE TABLE \(TripsTable.Name)(\(TripsTable.Column.Id) INTEGER PRIMARY KEY AUTOINCREMENT, \(TripsTable.Column.Name) TEXT UNIQUE, \(TripsTable.Column.From) DATE, \(TripsTable.Column.To) DATE, \(TripsTable.Column.FromTimezone) TEXT, \(TripsTable.Column.ToTimezone) TEXT, \(TripsTable.Column.Comment) TEXT, \(TripsTable.Column.CostCenter) TEXT, \(TripsTable.Column.DefaultCurrency) TEXT, \(TripsTable.Column.ProcessingStatus) TEXT, \(TripsTable.Column.Filters) TEXT, \(Database.SyncColumns.drive_sync_id) TEXT,\(Database.SyncColumns.drive_is_synced) BOOLEAN DEFAULT 0,\(Database.SyncColumns.drive_marked_for_deletion) BOOLEAN DEFAULT 0,\(Database.SyncColumns.last_local_modification_time) DATE)")
        
        let insertColumns = [
            TripsTable.Column.Name,
            TripsTable.Column.From,
            TripsTable.Column.To,
            TripsTable.Column.FromTimezone,
            TripsTable.Column.ToTimezone,
            TripsTable.Column.Comment,
            TripsTable.Column.CostCenter,
            TripsTable.Column.DefaultCurrency,
            TripsTable.Column.ProcessingStatus,
            TripsTable.Column.Filters,
            SyncStateColumns.Id,
            SyncStateColumns.IsSynced,
            SyncStateColumns.MarkedForDeletion,
            SyncStateColumns.LastLocalModificationTime,
        ].joined(separator: ", ")
        
        result = result &&  database.executeUpdate("INSERT INTO \(TripsTable.Name)(\(insertColumns)) SELECT \(insertColumns) FROM \(TripsTable.Name)_tmp")
        result = result &&  database.executeUpdate("DROP TABLE \(TripsTable.Name)_tmp")
        
        return result
    }
    
    private func updateDistanceParent(database: Database) -> Bool {
        var result = database.executeUpdate("ALTER TABLE \(DistanceTable.Name) ADD COLUMN \(DistanceTable.Column.ParentId) INTEGER REFERENCES \(TripsTable.Column.Name) ON DELETE CASCADE")
        
         result = result && database.executeUpdate("UPDATE \(DistanceTable.Name) SET \(ReceiptsTable.Column.ParentId) = (SELECT \(TripsTable.Column.Id) FROM \(TripsTable.Name) WHERE \(TripsTable.Column.Name) = \(DEPRECATED_PARENT) LIMIT 1)")
        
        result = result && database.executeUpdate("ALTER TABLE \(DistanceTable.Name) RENAME TO \(DistanceTable.Name)_tmp")
        
        result = result &&  database.executeUpdate("CREATE TABLE \(DistanceTable.Name) (\(DistanceTable.Column.Id) INTEGER PRIMARY KEY AUTOINCREMENT, \(DistanceTable.Column.ParentId) INTEGER REFERENCES \(TripsTable.Column.Name) ON DELETE CASCADE, \(DistanceTable.Column.Distance) DECIMAL(10, 2) DEFAULT 0.00, \(DistanceTable.Column.Location) TEXT, \(DistanceTable.Column.Date) DATE, \(DistanceTable.Column.Timezone) TEXT, \(DistanceTable.Column.Comment) TEXT, \(DistanceTable.Column.RateCurrency) TEXT NOT NULL, \(DistanceTable.Column.Rate) DECIMAL(10, 2) DEFAULT 0.00, \(Database.SyncColumns.drive_sync_id) TEXT,\(Database.SyncColumns.drive_is_synced) BOOLEAN DEFAULT 0,\(Database.SyncColumns.drive_marked_for_deletion) BOOLEAN DEFAULT 0,\(Database.SyncColumns.last_local_modification_time) DATE)")
        
        
        let insertColumns = [
            DistanceTable.Column.Id,
            DistanceTable.Column.ParentId,
            DistanceTable.Column.Distance,
            DistanceTable.Column.Location,
            DistanceTable.Column.Date,
            DistanceTable.Column.Timezone,
            DistanceTable.Column.Comment,
            DistanceTable.Column.Rate,
            DistanceTable.Column.RateCurrency,
            SyncStateColumns.Id,
            SyncStateColumns.IsSynced,
            SyncStateColumns.MarkedForDeletion,
            SyncStateColumns.LastLocalModificationTime,
        ].joined(separator: ", ")
        
        result = result &&  database.executeUpdate("INSERT INTO \(DistanceTable.Name)(\(insertColumns)) SELECT \(insertColumns) FROM \(DistanceTable.Name)_tmp")
        result = result &&  database.executeUpdate("DROP TABLE \(DistanceTable.Name)_tmp")
        
        return result
    }
    
    private func updateReceiptsParent(database: Database) -> Bool {
        var result = database.executeUpdate("ALTER TABLE \(ReceiptsTable.Name) ADD COLUMN \(ReceiptsTable.Column.ParentId) INTEGER REFERENCES \(TripsTable.Column.Name) ON DELETE CASCADE")
        
{'fraction_non_alphanumeric': 0.10929294466854403, 'fraction_numerical': 0.005479869092016135, 'mean_word_length': 2.8681189284662936, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 19, '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': '2405344', 'n_tokens_mistral': 3778, 'n_tokens_neox': 3617, 'n_words': 742}