SPB Git

Repository Public

RulesNames.swift 9.5 KB 205 lines swift

1// =============================================================================2//                  SP BOUCHER | SOFTWARE & RESEARCH3//             ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project          : ORIX Sheet9// File             : Packages/SheetAnalytics/Sources/Audit/Auditor/RulesNames.swift10// Module           : Audit11// Repository       : https://git.spboucher.ai/orix-sheet12// Created          : 2026-11-2113// Last Modified    : 2026-11-2114//15// =============================================================================16// AUTHOR & PROFESSIONAL AFFILIATION17// =============================================================================18//19// Author           : Simon-Pierre Boucher20// Position         : Professor of Finance21// Institution      : Université du Québec en Outaouais (UQO)22// Department       : Département des sciences administratives23// Organization     : SP Boucher24//25// Website          : https://spboucher.ai26// Git              : https://git.spboucher.ai27//28// =============================================================================29// OFFICIAL CONTACT DIRECTORY30// =============================================================================31//32// General          : info@spboucher.ai33// Professional     : contact@spboucher.ai34// Direct           : simon@spboucher.ai35// Research         : research@spboucher.ai36// Development      : dev@spboucher.ai37// Support          : support@spboucher.ai38// Security/Admin   : admin@spboucher.ai39// Notifications    : notifications@spboucher.ai40//41// Preferred Contact: support@spboucher.ai42//43// =============================================================================44// COPYRIGHT & INTELLECTUAL PROPERTY45// =============================================================================46//47// Copyright (c) 202648// Simon-Pierre Boucher49//50// All rights reserved where applicable.51//52// This file is governed by the applicable project license,53// copyright notices, institutional agreements and relevant law.54//55// Unless expressly authorized by the applicable license or56// rights holder, copying, redistribution, sublicensing,57// publication and commercial exploitation are not permitted.58//59// Existing third-party rights and notices remain unaffected.60//61// =============================================================================62// CONFIDENTIALITY & SECURITY63// =============================================================================64//65// Classification   : Public66// Distribution     : Public repository; use is governed by the project license67// Security Contact : admin@spboucher.ai68//69// Access to this source does not independently grant permission70// to redistribute or disclose confidential materials.71//72// Do not embed credentials, private keys, access tokens,73// certificates or confidential personal information.74//75// =============================================================================76// LICENSE & MAINTENANCE77// =============================================================================78//79// License          : None specified (no license file in this repository)80// Maintainer       : Simon-Pierre Boucher81// Technical Contact: dev@spboucher.ai82//83// =============================================================================84// END OF SP BOUCHER SOURCE HEADER85// =============================================================================8687import Foundation88import SheetCore8990extension ModelAuditor.Scan {91    // MARK: Defined names9293    mutating func ruleNames() {94        guard !wb.names.isEmpty else { return }95        var used = Set<String>()96        func collect(_ e: Expr) {97            e.walk { node in98                switch node {99                case .name(let n, _): used.insert(n.uppercased())100                case .call(let n, _): used.insert(n.uppercased())101                default: break102                }103            }104        }105        for list in formulas { for f in list { collect(f.expr) } }106        func collect(text: String?) { if let t = text, let e = try? FormulaParser.parse(t) { collect(e) } }107        for sheet in wb.sheets {108            for v in sheet.meta.validations { collect(text: v.formula1); collect(text: v.formula2) }109            for cf in sheet.meta.conditionalFormats {110                switch cf.kind {111                case .cellIs(_, let fs): for f in fs { collect(text: f) }112                case .formula(let f): collect(text: f)113                case .colorScale(let pts): for p in pts { collect(text: p.value) }114                case .dataBar(let a, let b, _): collect(text: a.value); collect(text: b.value)115                case .iconSet(_, let pts, _): for p in pts { collect(text: p.value) }116                default: break117                }118            }119        }120        var parsed = [String: Expr]()121        for (key, dn) in wb.names { if let e = try? FormulaParser.parse(dn.formula) { parsed[key] = e } }122        // names used by other names count as used only if those names are used themselves: iterate to a fixed point123        var changed = true124        while changed {125            changed = false126            for (key, dn) in wb.names where used.contains(dn.name.uppercased()) {127                guard let e = parsed[key] else { continue }128                let before = used.count129                collect(e)130                if used.count != before { changed = true }131            }132        }133        for (key, dn) in wb.names.sorted(by: { $0.key < $1.key }) {134            let scope = dn.scopeSheetID.flatMap { wb.sheetIndex(id: $0) }135            if options.rules.contains(.brokenName) {136                var problem: String?137                if let e = parsed[key] {138                    let parts = brokenParts(e)139                    if !parts.isEmpty { problem = parts.joined(separator: ", ") }140                } else {141                    problem = "its definition is not a valid formula"142                }143                if let problem {144                    add(.brokenName, .error, sheet: scope, cells: [], key: dn.name,145                        evidence: "The defined name \(dn.name) is defined as =\(dn.formula): \(problem).",146                        explanation: "Every formula that uses this name returns an error.",147                        action: "Redefine the name so that it points to existing cells, or delete it.")148                    continue149                }150            }151            if options.rules.contains(.unusedName), !dn.hidden, !used.contains(dn.name.uppercased()) {152                add(.unusedName, .info, sheet: scope, cells: [], key: dn.name,153                    evidence: "The defined name \(dn.name) (=\(dn.formula)) is not used by any formula, validation or conditional format.",154                    explanation: "Unused names clutter the name list and may point to data that readers assume is in use.",155                    action: "Delete the name, or use it in the formulas that read those cells.")156            }157        }158    }159160    // MARK: Slow formulas161162    mutating func ruleSlow() {163        var total = 0164        for list in formulas { total += list.count }165        guard total > 0, options.slowFormulaTopN > 0 else { return }166        let stride = max(1, Int((Double(total) / Double(max(1, options.profileSampleLimit))).rounded(.up)))167        var timings = [(Int, ModelAuditor.FormulaCell, Double)]()168        let engine = self.engine169        let reps = max(1, options.profileRepetitions)170        let all = formulas171        // one deep thread for the whole profile: `evaluate` then runs inline instead of spawning a thread per call172        CalcEngine.runDeep {173            var n = 0174            for (si, list) in all.enumerated() {175                for f in list {176                    defer { n += 1 }177                    guard n % stride == 0 else { continue }178                    var best = Double.infinity179                    for _ in 0..<reps {180                        let t0 = DispatchTime.now().uptimeNanoseconds181                        _ = engine.evaluate(f.expr, sheet: si, at: f.ref)182                        best = min(best, Double(DispatchTime.now().uptimeNanoseconds - t0) / 1e9)183                    }184                    timings.append((si, f, best))185                }186            }187        }188        let slow = timings.filter { $0.2 >= options.slowFormulaThreshold }.sorted { ($0.2, $1.0, $1.1.ref) > ($1.2, $0.0, $0.1.ref) }.prefix(options.slowFormulaTopN)189        for (rank, (si, f, seconds)) in slow.enumerated() {190            var cellsRead = 0191            for a in engine.precedents(sheet: si, row: f.row, col: f.col) {192                cellsRead += a.range.isWholeColumns || a.range.isWholeRows ? wb.sheets[a.sheet].cells.count : a.range.count193            }194            let ms = Value.generalString((seconds * 1e6).rounded() / 1000)195            var shown = text(f)196            if shown.count > 100 { shown = String(shown.prefix(97)) + "…" }197            add(.slowFormula, seconds >= 10 * options.slowFormulaThreshold ? .warning : .info, sheet: si, cells: [f.ref],198                evidence: "#\(rank + 1) slowest of \(timings.count) formulas timed: \(f.ref.a1): \(shown) takes \(ms) ms per evaluation and reads about \(cellsRead) cells.",199                explanation: "This formula dominates recalculation time; every change to its inputs costs this much again"200                    + (stride > 1 ? " (1 formula in \(stride) was timed, so similar formulas may be as slow)." : "."),201                action: "Narrow its ranges, avoid whole-column references, or compute shared parts once in helper cells.")202        }203    }204}205
Files main
Menu

Theme