001package gudusoft.gsqlparser.ir.builder.mssql;
002
003import java.util.Collections;
004import java.util.HashSet;
005import java.util.Set;
006
007/**
008 * Classifies SQL Server built-in functions, system procedures, and extended
009 * stored procedures as external dependencies.
010 * <p>
011 * Classification types:
012 * <ul>
013 *   <li>{@code BUILTIN_FUNCTION} — scalar built-in functions</li>
014 *   <li>{@code AGGREGATE_FUNCTION} — aggregate functions</li>
015 *   <li>{@code SYSTEM_FUNCTION} — system/metadata functions</li>
016 *   <li>{@code SYSTEM_PROC} — system stored procedures (sp_*)</li>
017 *   <li>{@code EXTENDED_PROC} — extended stored procedures (xp_*)</li>
018 *   <li>{@code SYSTEM_SCHEMA} — objects in sys/INFORMATION_SCHEMA</li>
019 *   <li>{@code CROSS_DATABASE} — 3-part name (database.schema.object)</li>
020 *   <li>{@code REMOTE_CALL} — 4-part name (server.database.schema.object)</li>
021 *   <li>{@code DYNAMIC_SQL} — EXEC(@sql) or unresolvable sp_executesql</li>
022 * </ul>
023 */
024public final class MssqlExternalDepClassifier {
025
026    private MssqlExternalDepClassifier() {}
027
028    // ---- Built-in function sets ----
029
030    private static final Set<String> STRING_FUNCTIONS;
031    private static final Set<String> MATH_FUNCTIONS;
032    private static final Set<String> DATETIME_FUNCTIONS;
033    private static final Set<String> CONVERSION_FUNCTIONS;
034    private static final Set<String> SYSTEM_FUNCTIONS;
035    private static final Set<String> JSON_FUNCTIONS;
036    private static final Set<String> CATCH_FUNCTIONS;
037    private static final Set<String> AGGREGATE_FUNCTIONS;
038    private static final Set<String> SYSTEM_PROCS;
039    private static final Set<String> EXTENDED_PROCS;
040    private static final Set<String> SECURITY_SENSITIVE;
041
042    /** Combined set of all built-in/system function names (uppercase). */
043    private static final Set<String> ALL_BUILTINS;
044
045    static {
046        STRING_FUNCTIONS = unmodifiableSet(
047                "ASCII", "CHAR", "CHARINDEX", "CONCAT", "CONCAT_WS", "DIFFERENCE",
048                "FORMAT", "LEFT", "LEN", "LOWER", "LTRIM", "NCHAR", "PATINDEX",
049                "QUOTENAME", "REPLACE", "REPLICATE", "REVERSE", "RIGHT", "RTRIM",
050                "SOUNDEX", "SPACE", "STR", "STRING_AGG", "STRING_ESCAPE",
051                "STRING_SPLIT", "STUFF", "SUBSTRING", "TRANSLATE", "TRIM",
052                "UNICODE", "UPPER"
053        );
054
055        MATH_FUNCTIONS = unmodifiableSet(
056                "ABS", "ACOS", "ASIN", "ATAN", "ATN2", "CEILING", "COS", "COT",
057                "DEGREES", "EXP", "FLOOR", "LOG", "LOG10", "PI", "POWER",
058                "RADIANS", "RAND", "ROUND", "SIGN", "SIN", "SQRT", "SQUARE", "TAN"
059        );
060
061        DATETIME_FUNCTIONS = unmodifiableSet(
062                "CURRENT_TIMESTAMP", "DATEADD", "DATEDIFF", "DATEDIFF_BIG",
063                "DATEFROMPARTS", "DATENAME", "DATEPART", "DATETIME2FROMPARTS",
064                "DATETIMEFROMPARTS", "DATETIMEOFFSETFROMPARTS", "DAY", "EOMONTH",
065                "GETDATE", "GETUTCDATE", "ISDATE", "MONTH",
066                "SMALLDATETIMEFROMPARTS", "SWITCHOFFSET", "SYSDATETIME",
067                "SYSUTCDATETIME", "TIMEFROMPARTS", "TODATETIMEOFFSET", "YEAR"
068        );
069
070        CONVERSION_FUNCTIONS = unmodifiableSet(
071                "CAST", "CONVERT", "TRY_CAST", "TRY_CONVERT", "PARSE", "TRY_PARSE"
072        );
073
074        SYSTEM_FUNCTIONS = unmodifiableSet(
075                "APP_NAME", "COALESCE", "ISNULL", "IIF", "CHOOSE", "NULLIF",
076                "ISNUMERIC", "SCOPE_IDENTITY", "IDENT_CURRENT", "NEWID",
077                "NEWSEQUENTIALID", "OBJECT_ID", "OBJECT_NAME", "DB_ID", "DB_NAME",
078                "SCHEMA_ID", "SCHEMA_NAME", "USER_ID", "USER_NAME",
079                "HAS_PERMS_BY_NAME", "IS_MEMBER", "IS_ROLEMEMBER",
080                "SESSION_USER", "SYSTEM_USER", "CURRENT_USER", "HOST_NAME",
081                "SUSER_SNAME", "@@ROWCOUNT", "@@IDENTITY", "@@ERROR",
082                "@@TRANCOUNT", "@@FETCH_STATUS", "@@SPID", "@@VERSION",
083                "@@SERVERNAME", "@@SERVICENAME"
084        );
085
086        JSON_FUNCTIONS = unmodifiableSet(
087                "ISJSON", "JSON_VALUE", "JSON_QUERY", "JSON_MODIFY",
088                "JSON_OBJECT", "JSON_ARRAY", "JSON_PATH_EXISTS"
089        );
090
091        CATCH_FUNCTIONS = unmodifiableSet(
092                "ERROR_MESSAGE", "ERROR_NUMBER", "ERROR_SEVERITY", "ERROR_STATE",
093                "ERROR_LINE", "ERROR_PROCEDURE"
094        );
095
096        AGGREGATE_FUNCTIONS = unmodifiableSet(
097                "SUM", "COUNT", "AVG", "MAX", "MIN", "COUNT_BIG",
098                "STDEV", "STDEVP", "VAR", "VARP", "GROUPING",
099                "GROUPING_ID", "CHECKSUM_AGG"
100        );
101
102        SYSTEM_PROCS = unmodifiableSet(
103                "SP_EXECUTESQL", "SP_RENAME", "SP_HELPTEXT", "SP_HELP", "SP_COLUMNS",
104                "SP_TABLES", "SP_STORED_PROCEDURES", "SP_ADDLINKEDSERVER",
105                "SP_ADDLINKEDSRVLOGIN", "SP_DROPLINKEDSRVLOGIN", "SP_DROPSERVER",
106                "SP_ADDMESSAGE", "SP_DROPMESSAGE", "SP_ADDTYPE", "SP_DROPTYPE",
107                "SP_ADDROLE", "SP_DROPROLE", "SP_ADDROLEMEMBER", "SP_DROPROLEMEMBER",
108                "SP_GRANTDBACCESS", "SP_REVOKEDBACCESS", "SP_CONFIGURE",
109                "SP_LOCK", "SP_WHO", "SP_WHO2", "SP_SPACEUSED", "SP_DEPENDS",
110                "SP_REFRESHVIEW", "SP_RECOMPILE", "SP_SETAPPROLE", "SP_UNSETAPPROLE",
111                "SP_TRACE_CREATE", "SP_TRACE_SETEVENT", "SP_TRACE_SETSTATUS",
112                "SP_XML_PREPAREDOCUMENT", "SP_XML_REMOVEDOCUMENT",
113                "SP_SEND_DBMAIL", "SP_START_JOB", "SP_STOP_JOB"
114        );
115
116        EXTENDED_PROCS = unmodifiableSet(
117                "XP_CMDSHELL", "XP_LOGININFO", "XP_MSVER", "XP_SENDMAIL",
118                "XP_FILEEXIST", "XP_FIXEDDRIVES", "XP_REGREAD", "XP_REGWRITE",
119                "XP_SERVICECONTROL"
120        );
121
122        SECURITY_SENSITIVE = unmodifiableSet(
123                "XP_CMDSHELL", "XP_REGREAD", "XP_REGWRITE", "XP_SERVICECONTROL",
124                "SP_OACREATE", "SP_OAMETHOD", "SP_OAGETPROPERTY",
125                "OPENROWSET", "OPENQUERY", "OPENDATASOURCE"
126        );
127
128        // Combine all into a single lookup set
129        Set<String> all = new HashSet<String>();
130        all.addAll(STRING_FUNCTIONS);
131        all.addAll(MATH_FUNCTIONS);
132        all.addAll(DATETIME_FUNCTIONS);
133        all.addAll(CONVERSION_FUNCTIONS);
134        all.addAll(SYSTEM_FUNCTIONS);
135        all.addAll(JSON_FUNCTIONS);
136        all.addAll(CATCH_FUNCTIONS);
137        all.addAll(AGGREGATE_FUNCTIONS);
138        all.addAll(SYSTEM_PROCS);
139        all.addAll(EXTENDED_PROCS);
140        ALL_BUILTINS = Collections.unmodifiableSet(all);
141    }
142
143    /**
144     * Returns true if the given name is a known external (built-in/system) dependency.
145     * Uses only the object-name part (last segment of multi-part name).
146     */
147    public static boolean isExternal(String name) {
148        if (name == null) return false;
149        String key = extractObjectName(name).toUpperCase();
150        if (ALL_BUILTINS.contains(key)) return true;
151        // Check prefix patterns
152        if (key.startsWith("SP_") || key.startsWith("XP_")) return true;
153        // Check schema prefixes
154        String upper = name.toUpperCase().trim();
155        if (upper.startsWith("SYS.") || upper.startsWith("INFORMATION_SCHEMA.")) return true;
156        return false;
157    }
158
159    /**
160     * Classifies the external dependency type.
161     *
162     * @return classification string (e.g., "BUILTIN_FUNCTION", "SYSTEM_PROC")
163     */
164    public static String classify(String name) {
165        if (name == null) return "BUILTIN_FUNCTION";
166        String key = extractObjectName(name).toUpperCase();
167        String upper = name.toUpperCase().trim();
168
169        if (upper.startsWith("SYS.") || upper.startsWith("INFORMATION_SCHEMA.")) {
170            return "SYSTEM_SCHEMA";
171        }
172        if (EXTENDED_PROCS.contains(key) || key.startsWith("XP_")) return "EXTENDED_PROC";
173        if (SYSTEM_PROCS.contains(key) || key.startsWith("SP_")) return "SYSTEM_PROC";
174        if (AGGREGATE_FUNCTIONS.contains(key)) return "AGGREGATE_FUNCTION";
175        if (CATCH_FUNCTIONS.contains(key)) return "SYSTEM_FUNCTION";
176        if (SYSTEM_FUNCTIONS.contains(key)) return "SYSTEM_FUNCTION";
177        if (JSON_FUNCTIONS.contains(key)) return "BUILTIN_FUNCTION";
178        if (STRING_FUNCTIONS.contains(key)) return "BUILTIN_FUNCTION";
179        if (MATH_FUNCTIONS.contains(key)) return "BUILTIN_FUNCTION";
180        if (DATETIME_FUNCTIONS.contains(key)) return "BUILTIN_FUNCTION";
181        if (CONVERSION_FUNCTIONS.contains(key)) return "BUILTIN_FUNCTION";
182        return "BUILTIN_FUNCTION";
183    }
184
185    /**
186     * Returns true if the name refers to sp_executesql.
187     */
188    public static boolean isSpExecuteSql(String name) {
189        if (name == null) return false;
190        return extractObjectName(name).toUpperCase().equals("SP_EXECUTESQL");
191    }
192
193    /**
194     * Returns true if the name is security-sensitive (audit-worthy).
195     */
196    public static boolean isSecuritySensitive(String name) {
197        if (name == null) return false;
198        return SECURITY_SENSITIVE.contains(extractObjectName(name).toUpperCase());
199    }
200
201    /**
202     * Extracts the object name (last part) from a potentially multi-part name.
203     */
204    private static String extractObjectName(String name) {
205        String stripped = MssqlNameNormalizer.stripQuotes(name.trim());
206        int dot = stripped.lastIndexOf('.');
207        if (dot >= 0 && dot < stripped.length() - 1) {
208            return MssqlNameNormalizer.stripQuotes(stripped.substring(dot + 1));
209        }
210        return stripped;
211    }
212
213    private static Set<String> unmodifiableSet(String... items) {
214        Set<String> set = new HashSet<String>();
215        for (String item : items) {
216            set.add(item);
217        }
218        return Collections.unmodifiableSet(set);
219    }
220}