ylib/include/db/sqler.h

#pragma once
#include "base/error.h"
#include "mysql.h"
#include <any>
#include <vector>
namespace ylib
{
	struct where {
		std::string name;
		std::string expression;
		std::any value;
		std::vector<std::any> values; // IN / BETWEEN 等多值条件
		// 0=普通 1=Like 2=自定义 3=IS NULL 4=IS NOT NULL
		// 5=NOT LIKE 6=IN 7=NOT IN 8=BETWEEN 9=NOT BETWEEN
		int type = 0;
	};
	struct limit {
		int64 start = -1;
		int64 count = -1;
	};
	struct keyvalue {
		std::string name;
		std::any value;
		int extra = 0;
	};
	/// <summary>
	/// MYSQL SELECT 查询器
	/// </summary>
	class select :public ylib::error_base {
	public:
		select(mysql::conn* conn);
		select(const ylib::conn_autofree<ylib::mysql::conn>& conn);
		~select();
		/// <summary>
		/// 表名
		/// </summary>
		/// <param name="field"></param>
		/// <returns></returns>
		select& table(const std::string& table_name);
		/// <summary>
		/// 查询字段
		/// </summary>
		/// <param name="field"></param>
		/// <param name="sort"></param>
		/// <returns></returns>
		select& field(const std::vector<std::string>& field);
		/// <summary>
		/// 条件[A=B]
		/// </summary>
		select& where(const std::string& name, const std::string& expression, const std::any& value);
		/// <summary>
		/// 条件[模糊查询]
		/// </summary>
		select& where_like(const std::string& name, const std::string& value);
		/// <summary>
		/// 条件[NOT LIKE]
		/// </summary>
		select& where_not_like(const std::string& name, const std::string& value);
		/// <summary>
		/// 条件[IS NULL]
		/// </summary>
		select& where_is_null(const std::string& name);
		/// <summary>
		/// 条件[IS NOT NULL]
		/// </summary>
		select& where_is_not_null(const std::string& name);
		/// <summary>
		/// 条件[IN]
		/// </summary>
		select& where_in(const std::string& name, const std::vector<std::any>& values);
		/// <summary>
		/// 条件[NOT IN]
		/// </summary>
		select& where_not_in(const std::string& name, const std::vector<std::any>& values);
		/// <summary>
		/// 条件[BETWEEN]
		/// </summary>
		select& where_between(const std::string& name, const std::any& begin, const std::any& end);
		/// <summary>
		/// 条件[NOT BETWEEN]
		/// </summary>
		select& where_not_between(const std::string& name, const std::any& begin, const std::any& end);
		/// <summary>
		/// 条件[自定义]
		/// </summary>
		select& where(const std::string& expression);
		/// <summary>
		/// LIMIT[页]
		/// </summary>
		select& page(uint32 page, uint32 count);
		/// <summary>
		/// LIMIT
		/// </summary>
		select& limit(uint32 start, uint32 count);
		/// <summary>
		/// 排序
		/// </summary>
		select& orderby(const std::string& expression);
		/// <summary>
		/// 分组查询
		/// </summary>
		select& groupby(const std::string& expression);
		/// <summary>
		/// 过滤
		/// </summary>
		select& having(const std::string& expression);
		/// <summary>
		/// 查询数量
		/// </summary>
		/// <returns></returns>
		uint64 count();
		/// <summary>
		/// 查询
		/// </summary>
		ylib::mysql::result* query();

		void clear();
#if 0
		/// <summary>
		/// 查询转换为layui需求模板
		/// </summary>
		/// <returns></returns> 
		ylib::json query_layui();
#endif
	private:
		/// <summary>
		/// 生成SQL片段
		/// </summary>
		/// <param name="field_name"></param>
		/// <param name="where"></param>
		/// <param name="orderby"></param>
		/// <param name="limit"></param>
		void make_sql(std::string& field_name, std::string& where,std::string& groupby,std::string& having,std::string& orderby, std::string& limit, std::vector<std::any>& insert_values);
	public:
		mysql::conn* m_conn = nullptr;
	private:
		ylib::conn_autofree<ylib::mysql::conn> m_autofree;
		std::vector<ylib::where> m_wheres;
		std::string m_table_name;
		std::vector<std::string> m_fields;
		ylib::limit m_limit;
		std::string m_orderby;
		std::string m_groupby;
		std::string m_having;

	};
	/// <summary>
	/// MYSQL UPDATE
	/// </summary>
	class update :public ylib::error_base {
		struct set {
			std::string name;
			std::any value;
			std::string expression;
			// 0=普通 1=表达式
			int type = 0;
		};
	public:
		update(mysql::conn* conn);
		update(const ylib::conn_autofree<ylib::mysql::conn>& conn);
		~update();
		/// <summary>
		/// 表名
		/// </summary>
		/// <param name="field"></param>
		/// <returns></returns>
		update& table(const std::string& table_name);
		/// <summary>
		/// 更新
		/// </summary>
		update& set(const std::string& name, const std::any& value);
		update& set(const std::string& expression);
		/// <summary>
		/// 条件[A=B]
		/// </summary>
		update& where(const std::string& name, const std::string& expression, const std::any& value);
		/// <summary>
		/// 条件[模糊查询]
		/// </summary>
		update& where_like(const std::string& name, const std::string& value);
		/// <summary>
		/// 条件[NOT LIKE]
		/// </summary>
		update& where_not_like(const std::string& name, const std::string& value);
		/// <summary>
		/// 条件[IS NULL]
		/// </summary>
		update& where_is_null(const std::string& name);
		/// <summary>
		/// 条件[IS NOT NULL]
		/// </summary>
		update& where_is_not_null(const std::string& name);
		/// <summary>
		/// 条件[IN]
		/// </summary>
		update& where_in(const std::string& name, const std::vector<std::any>& values);
		/// <summary>
		/// 条件[NOT IN]
		/// </summary>
		update& where_not_in(const std::string& name, const std::vector<std::any>& values);
		/// <summary>
		/// 条件[BETWEEN]
		/// </summary>
		update& where_between(const std::string& name, const std::any& begin, const std::any& end);
		/// <summary>
		/// 条件[NOT BETWEEN]
		/// </summary>
		update& where_not_between(const std::string& name, const std::any& begin, const std::any& end);
		/// <summary>
		/// 条件[自定义]
		/// </summary>
		update& where(const std::string& expression);
		/// <summary>
		/// LIMIT[页]
		/// </summary>
		update& page(uint32 page, uint32 count);
		/// <summary>
		/// LIMIT
		/// </summary>
		update& limit(uint32 start, uint32 count);
		/// <summary>
		/// 排序
		/// </summary>
		update& orderby(const std::string& expression);
		/// <summary>
		/// 查询
		/// </summary>
		uint64 exec();

		void clear();
	private:
		/// <summary>
		/// 生成SQL片段
		/// </summary>
		/// <param name="set"></param>
		/// <param name="where"></param>
		/// <param name="orderby"></param>
		/// <param name="limit"></param>
		void make_sql(std::string& set, std::string& where, std::string& orderby, std::string& limit, std::vector<std::any>& insert_values);
	private:
	public:
		mysql::conn* m_conn = nullptr;
	private:
		ylib::conn_autofree<ylib::mysql::conn> m_autofree;
		std::vector<ylib::where> m_wheres;
		std::string m_table_name;
		std::vector<struct ylib::update::set> m_sets;
		ylib::limit m_limit;
		std::string m_orderby;

	};
	/// <summary>
	/// MYSQL INSERT
	/// </summary>
	class insert :public ylib::error_base {
	public:
		insert(mysql::conn* conn);
		insert(const ylib::conn_autofree<ylib::mysql::conn>& conn);
		~insert();
		/// <summary>
		/// 表名
		/// </summary>
		/// <param name="field"></param>
		/// <returns></returns>
		insert& table(const std::string& table_name);
		/// <summary>
		/// 更新
		/// </summary>
		insert& set(const std::string& name, const std::any& value);
		insert& set_not_pret(const std::string& name, const std::string& value);
		/// <summary> 
		/// 查询
		/// </summary>
		uint64 exec();

		void clear();
	public:
		mysql::conn* m_conn = nullptr;
	private:
		ylib::conn_autofree<ylib::mysql::conn> m_autofree;
		std::string m_table_name;
		std::vector<keyvalue> m_sets;
	};
	/// <summary>
	/// MYSQL DELETE
	/// </summary>
	class delete_ :public ylib::error_base {
	public:
		delete_(mysql::conn* conn);
		delete_(const ylib::conn_autofree<ylib::mysql::conn>& conn);
		~delete_();
		/// <summary>
		/// 表名
		/// </summary>
		/// <param name="field"></param>
		/// <returns></returns>
		delete_& table(const std::string& table_name);
		/// <summary>
		/// 条件[A=B]
		/// </summary>
		delete_& where(const std::string& name, const std::string& expression, const std::any& value);
		/// <summary>
		/// 条件[模糊查询]
		/// </summary>
		delete_& where_like(const std::string& name, const std::string& value);
		/// <summary>
		/// 条件[NOT LIKE]
		/// </summary>
		delete_& where_not_like(const std::string& name, const std::string& value);
		/// <summary>
		/// 条件[IS NULL]
		/// </summary>
		delete_& where_is_null(const std::string& name);
		/// <summary>
		/// 条件[IS NOT NULL]
		/// </summary>
		delete_& where_is_not_null(const std::string& name);
		/// <summary>
		/// 条件[IN]
		/// </summary>
		delete_& where_in(const std::string& name, const std::vector<std::any>& values);
		/// <summary>
		/// 条件[NOT IN]
		/// </summary>
		delete_& where_not_in(const std::string& name, const std::vector<std::any>& values);
		/// <summary>
		/// 条件[BETWEEN]
		/// </summary>
		delete_& where_between(const std::string& name, const std::any& begin, const std::any& end);
		/// <summary>
		/// 条件[NOT BETWEEN]
		/// </summary>
		delete_& where_not_between(const std::string& name, const std::any& begin, const std::any& end);
		/// <summary>
		/// 条件[自定义]
		/// </summary>
		delete_& where(const std::string& expression);
		/// <summary>
		/// LIMIT[页]
		/// </summary>
		delete_& page(uint32 page, uint32 count);
		/// <summary>
		/// LIMIT
		/// </summary>
		delete_& limit(uint32 start, uint32 count);
		/// <summary>
		/// 排序
		/// </summary>
		delete_& orderby(const std::string& expression);
		/// <summary>
		/// 查询
		/// </summary>
		uint64 exec();

		void clear();
	private:
		/// <summary>
		/// 生成SQL片段
		/// </summary>
		/// <param name="where"></param>
		/// <param name="orderby"></param>
		/// <param name="limit"></param>
		void make_sql(std::string& where, std::string& orderby, std::string& limit, std::vector<std::any>& insert_values);
	public:
		mysql::conn* m_conn = nullptr;
	private:
		ylib::conn_autofree<ylib::mysql::conn> m_autofree;
		std::vector<ylib::where> m_wheres;
		std::string m_table_name;
		ylib::limit m_limit;
		std::string m_orderby;

	};
}