sql_builder.hpp

浏览该文件的文档.

#ifndef NEFORCE_DATABASE_SQL_BUILDER_HPP__
#define NEFORCE_DATABASE_SQL_BUILDER_HPP__

 
#include "NeForce/core/container/vector.hpp"
#include "NeForce/core/memory/unique_ptr.hpp"
#include "NeForce/core/string/string.hpp"
NEFORCE_BEGIN_NAMESPACE__

 
#ifdef DELETE
#    undef DELETE
#endif
 
#ifdef EXCEPT
#    undef EXCEPT
#endif
 
#ifdef INTERSECT
#    undef INTERSECT
#endif

enum class sql_operate : uint8_t {
    SELECT,       
    INSERT,       
    UPDATE,       
    DELETE,       
    DROP_TABLE,   
    CREATE_TABLE, 
    CREATE_VIEW,  
    DROP_VIEW,    
    ALTER_TABLE,  
    CREATE_INDEX, 
    DROP_INDEX,   
    TRUNCATE      
};

enum class sql_join : uint8_t {
    INNER, 
    LEFT,  
    RIGHT, 
    FULL   
};

enum class sql_order : uint8_t {
    ASC, 
    DESC 
};

enum class sql_dialect : uint8_t {
    GENERIC,    
    MYSQL,      
    POSTGRESQL, 
    SQLITE,     
    MSSQL,      
    ORACLE      
};

enum class sql_set_op : uint8_t {
    UNION,         
    UNION_ALL,     
    INTERSECT,     
    INTERSECT_ALL, 
    EXCEPT_,       
    EXCEPT_ALL     
};
 

class NEFORCE_API sql_builder {
public:
    struct select_data {
        vector<string> fields;            
        vector<string> join_clauses;      
        vector<string> group_by_fields;   
        vector<string> rollup_fields;     
        vector<string> cube_fields;       
        vector<string> having_conditions; 
        vector<string> order_by_clauses;  
        int limit_count = -1;             
        int offset_count = -1;            
        int fetch_count = -1;             
        bool distinct = false;            
    };

    struct insert_data {
        vector<string> fields;             
        vector<string> placeholders;       
        vector<vector<string>> extra_rows; 
    };

    struct update_data {
        vector<string> assignments; 
    };

    struct column_definition {
        string name;                 
        string type;                 
        bool not_null = false;       
        bool unique_ = false;        
        bool primary_key = false;    
        bool auto_increment = false; 
        string default_value;        
        string check_expr;           
    };

    struct create_table_data {
        vector<column_definition> columns; 
        vector<string> table_constraints;  
        bool if_not_exists = false;        
        bool temporary = false;            
    };

    struct create_view_data {
        string view_name;        
        string view_query;       
        bool or_replace = false; 
    };

    struct alter_table_data {
        vector<string> actions; 
    };

    struct create_index_data {
        string index_name;      
        string table_name;      
        vector<string> columns; 
        bool unique = false;    
    };

    struct set_op_entry {
        sql_set_op op; 
        string query;  
    };

    struct cte_entry {
        string name;            
        string query;           
        bool recursive = false; 
    };
 
private:
    sql_operate sql_type_ = sql_operate::SELECT; 
    sql_dialect dialect_ = sql_dialect::GENERIC; 
    size_t param_seq_ = 0;                       
    string table_;                               
    string table_alias_;                         
    vector<string> where_conditions_;            
    vector<set_op_entry> set_operations_;        
    vector<cte_entry> cte_entries_;              
    bool drop_if_exists_ = false;                
 
    unique_ptr<select_data> select_data_;             
    unique_ptr<insert_data> insert_data_;             
    unique_ptr<update_data> update_data_;             
    unique_ptr<create_table_data> create_table_data_; 
    unique_ptr<create_view_data> create_view_data_;   
    unique_ptr<alter_table_data> alter_table_data_;   
    unique_ptr<create_index_data> create_index_data_; 
 
private:
    select_data* ensure_select_data();
    insert_data* ensure_insert_data();
    update_data* ensure_update_data();
    create_table_data* ensure_create_table_data();
    create_view_data* ensure_create_view_data();
    alter_table_data* ensure_alter_table_data();
    create_index_data* ensure_create_index_data();
 
    void clear_data() noexcept;
 
    NEFORCE_NODISCARD string make_placeholder(size_t index) const;
 
public:
    sql_builder() = default;

    ~sql_builder() = default;

    sql_builder(const sql_builder& other);

    sql_builder& operator=(const sql_builder& other);

    sql_builder(sql_builder&& other) noexcept = default;

    sql_builder& operator=(sql_builder&& other) noexcept = default;

    sql_builder& select(vector<string> fields);

    sql_builder& select(std::initializer_list<string> fields);

    sql_builder& select(string field);

    sql_builder& select_all() noexcept;

    sql_builder& distinct();

    sql_builder& from(string table) noexcept;

    sql_builder& from(string table, string alias) noexcept;

    sql_builder& join(sql_join type, string table, string on_condition);

    sql_builder& join(string table, string on_condition);

    sql_builder& left_join(string table, string on_condition);

    sql_builder& right_join(string table, string on_condition);

    sql_builder& inner_join(string table, string on_condition);

    sql_builder& full_join(string table, string on_condition);

    sql_builder& where(string condition);

    sql_builder& where_eq(string field, string value);

    sql_builder& where_ne(string field, string value);

    sql_builder& where_gt(string field, string value);

    sql_builder& where_ge(string field, string value);

    sql_builder& where_lt(string field, string value);

    sql_builder& where_le(string field, string value);

    sql_builder& where_like(string field, string pattern);

    sql_builder& where_not_like(string field, string pattern);

    sql_builder& where_in(string field, vector<string> values);

    sql_builder& where_not_in(string field, vector<string> values);

    sql_builder& where_between(string field, string start, string end);

    sql_builder& where_not_between(string field, string start, string end);

    sql_builder& where_is_null(string field);

    sql_builder& where_is_not_null(string field);

    sql_builder& where_exists(string subquery);

    sql_builder& where_not_exists(string subquery);

    sql_builder& or_where(string condition);

    sql_builder& group_by(string field);

    sql_builder& group_by(const vector<string>& fields);

    sql_builder& group_by_rollup(vector<string> fields);

    sql_builder& group_by_cube(vector<string> fields);

    sql_builder& having(string condition);

    sql_builder& order_by(string field, sql_order order = sql_order::ASC);

    sql_builder& order_by_asc(string field);

    sql_builder& order_by_desc(string field);

    sql_builder& limit(int count);

    sql_builder& offset(int count);

    sql_builder& fetch_first(int row_count);

    sql_builder& page(int page_num, int page_size);

    sql_builder& insert_into(string table, vector<string> fields);

    sql_builder& insert_into(string table);

    sql_builder& values(vector<string> values);

    sql_builder& values(std::initializer_list<string> values);

    sql_builder& values(vector<vector<string>> value_rows);

    sql_builder& add_values(vector<string> values);

    sql_builder& columns(vector<string> fields);

    sql_builder& update(string table);

    sql_builder& set(string assignment);

    sql_builder& set(string field, string value);

    sql_builder& set_increment(string field, int value = 1);

    sql_builder& set_decrement(string field, int value = 1);

    sql_builder& remove();

    sql_builder& delete_from(string table);

    sql_builder& drop_table(string table);

    sql_builder& drop_table_if_exists(string table);

    sql_builder& union_(string query);

    sql_builder& union_all(string query);

    sql_builder& intersect(string query);

    sql_builder& intersect_all(string query);

    sql_builder& except_(string query);

    sql_builder& except_all(string query);

    sql_builder& with_(string name, string query);

    sql_builder& with_recursive(string name, string query);

    sql_builder& cross_join(string table);

    sql_builder& select_row_number(string alias, const vector<string>& partition_by = {},
                                   const vector<string>& order_by = {});

    sql_builder& select_rank(string alias, const vector<string>& partition_by = {},
                             const vector<string>& order_by = {});

    sql_builder& select_dense_rank(string alias, const vector<string>& partition_by = {},
                                   const vector<string>& order_by = {});

    sql_builder& select_ntile(int buckets, string alias, const vector<string>& partition_by = {},
                              const vector<string>& order_by = {});

    sql_builder& select_lead(string field, string alias, const vector<string>& partition_by = {},
                             const vector<string>& order_by = {});

    sql_builder& select_lag(string field, string alias, const vector<string>& partition_by = {},
                            const vector<string>& order_by = {});

    sql_builder& select_first_value(string field, string alias, const vector<string>& partition_by = {},
                                    const vector<string>& order_by = {});

    sql_builder& select_last_value(string field, string alias, const vector<string>& partition_by = {},
                                   const vector<string>& order_by = {});

    static string make_case_simple(string field, vector<pair<string, string>> when_then, string else_val = "");

    static string make_case_searched(vector<pair<string, string>> when_then, string else_val = "");

    static string make_cast(string expression, string target_type);

    sql_builder& create_table(string table);

    sql_builder& create_table_if_not_exists(string table);

    sql_builder& create_temp_table(string table);

    sql_builder& create_temp_table_if_not_exists(string table);

    sql_builder& column(string name, string type);

    sql_builder& column_not_null(string name, string type);

    sql_builder& column_primary_key(string name, string type);

    sql_builder& column_unique(string name, string type);

    sql_builder& column_default(string name, string type, string default_val);

    sql_builder& column_check(string name, string type, string check_expr);

    sql_builder& column_auto_increment(string name, string type);

    sql_builder& table_primary_key(vector<string> columns);

    sql_builder& table_foreign_key(string column, string ref_table, string ref_column);

    sql_builder& table_unique(vector<string> columns);

    sql_builder& table_check(string expr);

    sql_builder& alter_table(string table);

    sql_builder& add_column(string name, string type);

    sql_builder& drop_column(string name);

    sql_builder& alter_column_set_type(string name, string new_type);

    sql_builder& alter_column_set_default(string name, string default_val);

    sql_builder& alter_column_drop_default(string name);

    sql_builder& alter_column_set_not_null(string name);

    sql_builder& alter_column_drop_not_null(string name);

    sql_builder& rename_column(string old_name, string new_name);

    sql_builder& add_primary_key(vector<string> columns);

    sql_builder& drop_constraint(string constraint_name);

    sql_builder& create_view(string view_name, string select_query);

    sql_builder& create_or_replace_view(string view_name, string select_query);

    sql_builder& drop_view(string view_name);

    sql_builder& drop_view_if_exists(string view_name);

    sql_builder& create_index(string index_name, string table, vector<string> columns);

    sql_builder& create_unique_index(string index_name, string table, vector<string> columns);

    sql_builder& drop_index(string index_name);

    sql_builder& drop_index_if_exists(string index_name);

    sql_builder& truncate(string table);

    sql_builder& select_count(string field, string alias);

    sql_builder& select_count(string field);

    sql_builder& select_count();

    sql_builder& select_sum(string field, string alias);

    sql_builder& select_sum(string field);

    sql_builder& select_avg(string field, string alias);

    sql_builder& select_avg(string field);

    sql_builder& select_max(string field, string alias);

    sql_builder& select_max(string field);

    sql_builder& select_min(string field, string alias);

    sql_builder& select_min(string field);

    sql_builder& select_distinct(string field);

    sql_builder& select_subquery(string subquery, string alias);

    sql_builder& select_subquery(string subquery);

    sql_builder& from_subquery(string subquery, string alias);

    sql_builder& reset() noexcept;

    NEFORCE_NODISCARD sql_operate type() const noexcept { return sql_type_; }

    NEFORCE_NODISCARD string_view table() const noexcept { return table_.view(); }

    NEFORCE_NODISCARD bool is_empty() const noexcept { return table_.empty(); }

    sql_builder& set_dialect(const sql_dialect dialect) noexcept {
        dialect_ = dialect;
        return *this;
    }

    NEFORCE_NODISCARD sql_dialect dialect() const noexcept { return dialect_; }

    NEFORCE_NODISCARD size_t param_count() const noexcept { return param_seq_; }

    NEFORCE_NODISCARD string placeholder(const size_t index) const { return make_placeholder(index); }

    NEFORCE_NODISCARD string next_placeholder() { return make_placeholder(++param_seq_); }

    sql_builder& set_param(string field) { return set(move(field), next_placeholder()); }

    NEFORCE_NODISCARD string build() const;
};
 // SQL
 
NEFORCE_END_NAMESPACE__
#endif // NEFORCE_DATABASE_SQL_BUILDER_HPP__