Carla M. Pugh

#################################
###### CODE RELATED TABLES ######
#################################

### CREATE FULL REDB DATABASE
def create_decompiled_binja_with_all_tables(client):
    create_phase1_decompiled_binja_tables(client)

#### CREATE FULL TABLES
def create_phase1_decompiled_binja_tables(client):
    create_function_analysis_error_dbtable(client)
    create_binja_decompiled_dbtable(client)
    create_binja_disassembly_dbtable(client)
    create_binja_cfg_dbtable(client)

#################################
### DECOMPILED BINJA INDIVIDUAL TABLES
#################################
def create_function_analysis_error_dbtable(client):
    client.command("""
    CREATE TABLE IF NOT EXISTS function_analysis_errors_binja (
        sha256 FixedString(64),
        function_name Nullable(String) CODEC(ZSTD(3)),
        function_address UInt64,
        error_location LowCardinality(String) CODEC(ZSTD(3)),
        error_message Nullable(String) CODEC(ZSTD(3)),
        error_details Nullable(String) CODEC(ZSTD(3)),
        error_type Nullable(String) CODEC(ZSTD(3)),
        error_hash FixedString(32), -- MD5, To help identify duplicate errors
        status Enum8('new' = 1, 'investigating' = 2, 'fixed' = 3, 'wontfix' = 4) DEFAULT 'new',
        analysis_date DateTime64(3, 'UTC'),
        PRIMARY KEY (sha256, function_address, error_location)
    )
    ENGINE = MergeTree()
    ORDER BY (sha256, function_address, error_location);
    """)

def create_binja_decompiled_dbtable(client):
    # Create decompiled_functions_content table
    client.command("""
    CREATE TABLE IF NOT EXISTS code_binja_decompiled_functions_content (
        decompiled_function_hash FixedString(64),
        decompiled_function String CODEC(ZSTD(3)),
        decompiled_function_lower String MATERIALIZED lower(decompiled_function) CODEC(ZSTD(3)),
        function_type Enum8('USER'=1, 'LIBRARY'=2, 'THUNK'=3, 'EXTERNAL'=4, 'UNKNOWN'=5) DEFAULT 'UNKNOWN',  -- NOT NULL TODO: remove THUNK and EXTERNAL
        flattened_score Nullable(Float64),
        mba_score Nullable(Float64),
        analysis_date DateTime64(3, 'UTC'),

        INDEX idx_function_content_token lower(decompiled_function) TYPE tokenbf_v1(32768, 3, 0) GRANULARITY 1,
        --- INDEX idx_text_decompiled_function decompiled_function TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(decompiled_function)) GRANULARITY 64,
        INDEX idx_func_hash decompiled_function_hash TYPE bloom_filter GRANULARITY 1,
    ) ENGINE = ReplacingMergeTree(analysis_date)
    PRIMARY KEY decompiled_function_hash -- TODO: remove is redundant
    ORDER BY decompiled_function_hash;
    """)

    # Create decompiled_functions_references table
    client.command("""
    CREATE TABLE IF NOT EXISTS code_binja_decompiled_functions_references (
        sha256 FixedString(64),
        sha1 FixedString(40),
        md5 FixedString(32),
        decompiled_function_hash FixedString(64),
        disassembled_function_hash Nullable(FixedString(64)),
        decompiled_function_name LowCardinality(String),
        decompiled_function_prototype LowCardinality(String),
        decompiled_function_address UInt64,
        functions_caller Array(String),
        functions_call Array(String),

        INDEX idx_sha256 sha256 TYPE bloom_filter GRANULARITY 1,
        INDEX idx_func_hash decompiled_function_hash TYPE bloom_filter GRANULARITY 1,
        INDEX idx_func_name decompiled_function_name TYPE tokenbf_v1(32768, 3, 0) GRANULARITY 1,
        INDEX idx_func_addr decompiled_function_address TYPE minmax GRANULARITY 1,
        analysis_date DateTime64(3, 'UTC')
    ) ENGINE = ReplacingMergeTree(analysis_date)
    ORDER BY (sha256, decompiled_function_hash);
    """)

def create_binja_disassembly_dbtable(client):
    # Create disassembly_functions_content table
    client.command("""
    CREATE TABLE IF NOT EXISTS code_binja_disassembled_functions_content (
        -- Content hashes for each normalization level
        disassembled_function_hash FixedString(64),             -- This is the hash of the disassembled function without addresses
        
        -- The actual function content at different normalization levels
        disassembled_function String CODEC(ZSTD(3)),
        disassembled_function_no_addresses String CODEC(ZSTD(3)),

        -- Function 
        function_type Enum8('USER'=1, 'LIBRARY'=2, 'THUNK'=3, 'EXTERNAL'=4, 'UNKNOWN'=5) DEFAULT 'UNKNOWN',  -- NOT NULL TODO: remove THUNK and EXTERNAL

        -- Analysis fields
        instructions_count UInt32,
        instructions_types Array(LowCardinality(String)), -- Types of instructions used
        control_flow_count UInt32,                      -- Number of control flow instructions
        memory_access_pattern Array(LowCardinality(String)),  -- Patterns of memory access
        register_usage Array(LowCardinality(String)),         -- Types of registers used
        data_references_count UInt32,                   -- Number of data references
        opcode_frequency_vector Array(Float32),-- Not computed yet
        api_calls_vector Array(Float32),-- Not computed yet
        minhash_signature Array(UInt64),-- Not computed yet
        max_block_size Nullable(UInt32),
        num_calls Nullable(UInt32),
        stack_size Nullable(Int32),
        instruction_type_ratios Array(Float32), -- Not computed yet
        instruction_embedding Array(Float32), -- Not computed yet
        analysis_date DateTime64(3, 'UTC'),

        -- Indexes for each normalization level
        INDEX idx_disasm_ngram disassembled_function TYPE ngrambf_v1(3, 32768, 3, 0) GRANULARITY 1,
        INDEX idx_function_type function_type TYPE set(10) GRANULARITY 1,
        INDEX idx_instr_count instructions_count TYPE minmax GRANULARITY 4

    ) ENGINE = ReplacingMergeTree(analysis_date)
    ORDER BY (disassembled_function_hash);
    """)

    client.command("""
    CREATE TABLE code_binja_disassembled_functions_references
    (
        -- Original fields
        sha256 FixedString(64),
        sha1 FixedString(40),
        md5 FixedString(32),
        disassembled_function_hash FixedString(64),
        decompiled_function_hash Nullable(FixedString(64)),

        disassembled_function_name LowCardinality(String),
        disassembled_function_address UInt64,
        analysis_date DateTime64(3, 'UTC'),
        
        -- New hash fields moved from content table
        ssdeep_disassembly Nullable(String),
        tlsh_disassembly Nullable(FixedString(72)),
        ssdeep_llil Nullable(String),
        tlsh_llil Nullable(FixedString(72)),
        
        -- OPTIMIZED INDEXES for all search patterns
        INDEX idx_func_addr disassembled_function_address TYPE minmax GRANULARITY 1,
        INDEX idx_sha256 sha256 TYPE bloom_filter GRANULARITY 1,
        INDEX idx_tlsh_disasm tlsh_disassembly TYPE bloom_filter GRANULARITY 1,
        INDEX idx_ssdeep_disasm ssdeep_disassembly TYPE bloom_filter GRANULARITY 1,
        INDEX idx_tlsh_llil tlsh_llil TYPE bloom_filter GRANULARITY 1,
        INDEX idx_ssdeep_norm ssdeep_llil TYPE bloom_filter GRANULARITY 1,
        INDEX idx_func_name disassembled_function_name TYPE tokenbf_v1(32768, 3, 0) GRANULARITY 2
    )
    ENGINE = ReplacingMergeTree(analysis_date)
    -- OPTIMIZED ORDER BY: Primary query pattern first, then hash fields for locality
    ORDER BY (sha256, disassembled_function_hash)
    SETTINGS index_granularity = 8192
    """)

def create_binja_llil_dbtable(client):
    # Create llil_functions_content table
    client.command("""
    CREATE TABLE IF NOT EXISTS code_binja_llil_functions_content (
        -- Content hashes for each normalization level
        llil_function_hash FixedString(64),             -- sha256_llil This is the hash of the disassembled function without addresses

        -- Function 
        function_type Enum8('USER'=1, 'LIBRARY'=2, 'THUNK'=3, 'EXTERNAL'=4, 'UNKNOWN'=5) DEFAULT 'UNKNOWN',  -- NOT NULL TODO: remove THUNK and EXTERNAL

        -- Analysis fields
        instructions_types_llil Array(LowCardinality(String)), -- Types of instructions used
        control_flow_count_llil UInt32,                      -- Number of control flow instructions
        memory_access_pattern_llil Array(LowCardinality(String)),  -- Patterns of memory access
        register_usage_llil Map(LowCardinality(String), Tuple(UInt32, UInt32)),         -- Types of registers used
        total_reg_reads UInt32,
        total_reg_written UInt32,
        data_references_count UInt32,                   -- Number of data references
        max_block_size Nullable(UInt32),
        num_calls Nullable(UInt32),
        stack_size Nullable(Int32),
        body_llil_vector Array(Tuple(UInt32, Array(UInt16))),
        analysis_date DateTime64(3, 'UTC'),

        -- Indexes for each normalization level
        INDEX function_type_idx function_type TYPE set(10) GRANULARITY 1,
        INDEX idx_reg_usage mapKeys(register_usage_llil) TYPE bloom_filter(0.01) GRANULARITY 1

    ) ENGINE = ReplacingMergeTree(analysis_date)
    ORDER BY llil_function_hash;
    """)

    client.command("""
    CREATE TABLE code_binja_llil_functions_references
    (
        -- Original fields
        sha256 FixedString(64),
        sha1 FixedString(40),
        md5 FixedString(32),
        llil_function_hash Nullable(FixedString(64)),
        disassembled_function_hash FixedString(64),
        
        -- New hash fields moved from content table
        ssdeep_disassembly Nullable(String),
        tlsh_disassembly Nullable(FixedString(72)),
        ssdeep_llil Nullable(String),
        tlsh_llil Nullable(FixedString(72)),

        analysis_date DateTime64(3, 'UTC'),
        
        -- OPTIMIZED INDEXES for all search patterns
        INDEX idx_sha256 sha256 TYPE bloom_filter GRANULARITY 1,
        INDEX idx_tlsh_disasm tlsh_disassembly TYPE bloom_filter GRANULARITY 1,
        INDEX idx_ssdeep_disasm ssdeep_disassembly TYPE bloom_filter GRANULARITY 1,
        INDEX idx_tlsh_llil tlsh_llil TYPE bloom_filter GRANULARITY 1,
        INDEX idx_ssdeep_llil ssdeep_llil TYPE bloom_filter GRANULARITY 1,
    )
    ENGINE = ReplacingMergeTree(analysis_date)
    -- OPTIMIZED ORDER BY: Primary query pattern first, then hash fields for locality
    ORDER BY (sha256, function_address)
    SETTINGS index_granularity = 8192
    """)

def create_binja_cfg_dbtable(client):
    client.command("""
    CREATE TABLE IF NOT EXISTS code_binja_cfg_blocks_content (
        block_instructions_hash FixedString(64), -- PK
        block_size UInt32,
        instructions_count UInt16,
        branch_type Enum8('DIRECT'=1, 'CONDITIONAL'=2, 'CALL'=3, 'RETURN'=4, 'FALLTHROUGH'=5, 'INDIRECT'=6, 'UNKNOWN'=7),
        block_type Enum8('CODE'=1, 'DATA'=2, 'THUNK'=3),
        flags Array(String),
        analysis_date DateTime64(3, 'UTC'),

        -- Indexes for filtering/analysis
        INDEX idx_branch_type branch_type TYPE set(10) GRANULARITY 4,
        INDEX idx_block_type block_type TYPE set(5) GRANULARITY 4,
        INDEX idx_instr_count instructions_count TYPE minmax GRANULARITY 4,
        INDEX idx_block_size block_size TYPE minmax GRANULARITY 4
    )
    ENGINE = ReplacingMergeTree(analysis_date)
    ORDER BY (block_instructions_hash);
    """)
  
    client.command("""
    CREATE TABLE IF NOT EXISTS code_binja_cfg_blocks_references (
        sha256 FixedString(64),
        sha1 FixedString(40),
        md5 FixedString(32),
        block_instructions_hash FixedString(64),
        disassembled_function_hash FixedString(64),
        
        -- Address information (for CFG traversal and debugging)
        function_address UInt64,
        block_start_address UInt64,
        block_end_address UInt64,
        
        -- CFG structure (addresses refer to block_start_address)
        predecessor_blocks Array(UInt64),
        successor_blocks Array(UInt64),
        depth UInt16,
        position UInt32,
        
        -- Dominator analysis
        dominators Array(UInt16),
        post_dominators Array(UInt16),
        
        analysis_date DateTime64(3, 'UTC'),
        
        -- OPTIMIZED INDEXES based on query patterns
        INDEX idx_sha256 sha256 TYPE bloom_filter GRANULARITY 1,
        INDEX idx_block_hash block_instructions_hash TYPE bloom_filter GRANULARITY 1,
        INDEX idx_func_hash disassembled_function_hash TYPE bloom_filter GRANULARITY 1,
        INDEX idx_func_addr function_address TYPE minmax GRANULARITY 1,
        INDEX idx_block_start block_start_address TYPE minmax GRANULARITY 1,
        INDEX idx_depth depth TYPE minmax GRANULARITY 4,
        INDEX idx_position position TYPE minmax GRANULARITY 4
    )
    ENGINE = ReplacingMergeTree(analysis_date)
    ORDER BY (sha256, function_address, block_start_address)
    SETTINGS index_granularity = 8192;
    """)

def create_binja_strings_dbtable(client):
    # 1. Raw table - INSERT target
    client.command("""
    CREATE TABLE IF NOT EXISTS code_binja_strings_raw (
        sha256 FixedString(64),
        string String CODEC(ZSTD(3)),              -- Decoded UTF-8 string (for searching)
        string_raw String CODEC(ZSTD(3)),          -- Raw bytes from binary (ground truth)
        string_encoding LowCardinality(String),    -- Detected/guessed encoding by binja
        string_offset UInt64,
        string_length UInt32,                      -- Length of decoded string
        string_raw_length UInt32,                  -- Length of raw bytes
        string_entropy Float32
    )
    ENGINE = Null;
    """)
   
    # 3a. Target table for reverse lookup (with indexes)
    client.command("""
    CREATE TABLE IF NOT EXISTS code_binja_strings_by_binary (
        sha256 FixedString(64),
        string_offset UInt64,
        string String CODEC(ZSTD(3)),
        string_raw String CODEC(ZSTD(3)),
        string_encoding LowCardinality(String),
        string_length UInt32,
        string_raw_length UInt32,
        string_entropy Float32,
        
        INDEX idx_sha256 sha256 TYPE bloom_filter GRANULARITY 1,
        INDEX idx_string_ngram lower(string) TYPE ngrambf_v1(3, 32768, 3, 0) GRANULARITY 1,
        --- INDEX idx_string string TYPE tokenbf_v1(32768, 3, 0) GRANULARITY 1,
        --- INDEX idx_string_raw string_raw TYPE tokenbf_v1(32768, 3, 0) GRANULARITY 1,
        INDEX idx_entropy string_entropy TYPE minmax GRANULARITY 4
    )
    ENGINE = ReplacingMergeTree()
    ORDER BY (sha256, string_offset);
    """)
    
    # 3b. Materialized view -> target table
    client.command("""
    CREATE MATERIALIZED VIEW IF NOT EXISTS code_binja_strings_by_binary_mv
    TO code_binja_strings_by_binary
    AS SELECT
        sha256,
        string_offset,
        string,
        string_raw,
        string_encoding,
        string_length,
        string_raw_length,
        string_entropy
    FROM code_binja_strings_raw;
    """)

    client.command("""
    -- 1. Create the new public-only popularity table
    CREATE TABLE mv_string_popularity_public_table (
        string_xxhash64 UInt64,
        sample_count AggregateFunction(uniq, FixedString(64))
    ) ENGINE = AggregatingMergeTree()
    ORDER BY string_xxhash64;
    """)

    client.command("""
    -- 2. Create the MV for new data going forward
    CREATE MATERIALIZED VIEW mv_string_popularity_public
    TO mv_string_popularity_public_table AS
    SELECT
        xxHash64(s.string) AS string_xxhash64,
        uniqState(s.sha256) AS sample_count
    FROM code_binja_strings_raw s
    INNER JOIN catalog_visibility_lookup_v2 v 
        ON s.sha256 = v.sha256 AND v.visibility_scope = 'public'
    GROUP BY string_xxhash64;
    """)

    # client.command("""
    # INSERT INTO mv_string_popularity_public_table
    # SELECT
    #     xxHash64(s.string) AS string_xxhash64,
    #     uniqState(s.sha256) AS sample_count
    # FROM code_binja_strings_by_binary s
    # INNER JOIN catalog_visibility_lookup_v2 v 
    #     ON s.sha256 = v.sha256 AND v.visibility_scope = 'public'
    # GROUP BY string_xxhash64;
    # """)

def create_binja_function_similarity_metrics_dbtable(client):
    client.command("""
    CREATE TABLE IF NOT EXISTS code_binja_function_similarity_metrics (
        disassembled_function_hash FixedString(64),
        
        -- Complexity metrics
        cyclomatic_complexity Nullable(UInt16),
        
        -- Fuzzy hashes (duplicated from reference tables for performance)
        ssdeep_disassembly Nullable(String),
        tlsh_disassembly Nullable(FixedString(72)),
        ssdeep_llil Nullable(String),
        tlsh_llil Nullable(FixedString(72)),
        
        -- Similarity hashes
        minhash Array(UInt8),
        
        analysis_date DateTime64(3, 'UTC'),
        
        -- Indexes
        INDEX idx_complexity cyclomatic_complexity TYPE minmax GRANULARITY 4,
        INDEX idx_ssdeep_disasm ssdeep_disassembly TYPE bloom_filter GRANULARITY 1,
        INDEX idx_tlsh_disasm tlsh_disassembly TYPE bloom_filter GRANULARITY 1,
        INDEX idx_ssdeep_llil ssdeep_llil TYPE bloom_filter GRANULARITY 1,
        INDEX idx_tlsh_llil tlsh_llil TYPE bloom_filter GRANULARITY 1
    )
    ENGINE = ReplacingMergeTree(analysis_date)
    ORDER BY disassembled_function_hash;
    """)

def create_golang_metadata_dbtable(client):
    client.command("""
    CREATE TABLE IF NOT EXISTS redb_golang_metadata (
        sha256 FixedString(64),
        goresym JSON,
        analysis_date DateTime64(3, 'UTC')
    ) ENGINE = ReplacingMergeTree(analysis_date)
    ORDER BY sha256
    """)