using UnityEngine;
using System;
using System.Collections.Generic;

public abstract class ISQLOperation 
{
    public abstract void CreateDateBase(string path);
    public abstract void DeletDateBase(string name);
    public abstract void Instert(System.Object obj);
    public abstract List<T> Select<T>(string condition);
    public abstract void Update(System.Object sql);
    public abstract void DeletTable(string tableName, string condition);
    public abstract void CreatTable<T>();
    public abstract void Execute(string sql);
}

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using SQLite4Unity3d;
using System;
using System.IO;
using System.Linq.Expressions;
using System.Linq;

public class SqlliteOperations : ISQLOperation
{
    public SqlliteOperations()
    {
            CreateDateBase(DefaultName.dateBasePath);
            CreatTable<User>();
    }
    public SQLiteConnection connection;
    /// <summary>
    /// 创建库
    /// </summary>
    /// <param name="path"></param>
    /// <param name="name"></param>
    public override void CreateDateBase(string path)
    {
        try
        {
            connection = new SQLiteConnection(path, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create);
        }
        catch (Exception e)
        {
            Debug.LogError(e.Message+",创建数据库失败。");
        }

    }
    /// <summary>
    /// 删数据不删表
    /// </summary>
    /// <param name="tableName"></param>
    public override void DeletTable(string tableName,string condition)
    {
        using (SQLiteConnection connection = new SQLiteConnection(DefaultName.dateBasePath))
        {
            try
            {
                SQLiteCommand liteCommand = connection.CreateCommand("delete from " + tableName + " where " + connection);
                liteCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Debug.LogError(e.Message+",删除数据库数据错误。");
            }

        }
    }
    /// <summary>
    /// 删库
    /// </summary>
    /// <param name="name"></param>
    public override void DeletDateBase(string name)
    {
        using (SQLiteConnection connection = new SQLiteConnection(DefaultName.dateBasePath))
        {
            try
            {
                SQLiteCommand liteCommand = connection.CreateCommand("drop datebase " + name);
                liteCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Debug.LogError(e.Message + ",删除数据库错误。");
            }

        }
    }

    //"UPDATE User SET Password = \"222222\" WHERE UserName = \"admin\""
    /// <summary>
    /// 修改表
    /// </summary>
    /// <param name="obj"></param>
    public override void Update(object obj)
    {
        using (SQLiteConnection connection = new SQLiteConnection(DefaultName.dateBasePath))
        {
            try
            {
                connection.Update(obj);
            }
            catch (Exception e)
            {
                Debug.LogError(e.Message + ",修改数据库错误:"+ obj.ToString());
            }
            
        }
    }

    /// <summary>
    /// 创建表
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public override void CreatTable<T>()
    {
        using (SQLiteConnection connection = new SQLiteConnection(DefaultName.dateBasePath))
        {
            try
            {
                connection.CreateTable<T>();
            }
            catch (Exception e)
            {
                Debug.LogError(e.Message + ",创建表错误："+typeof(T).Name);
            }
            
        }
    }
    /// <summary>
    /// 插入
    /// </summary>
    /// <param name="obj"></param>
    public override void Instert(System.Object obj)
    {
        using (SQLiteConnection connection = new SQLiteConnection(DefaultName.dateBasePath))
        {
            try
            {
                connection.Insert(obj);
            }
            catch (Exception e)
            {
                Debug.LogError(e.Message + ",插入表错误：" + obj.ToString());
            }
            
        }
    }
    /// <summary>
    /// 查询表
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql"></param>
    /// <returns></returns>
    public override List<T> Select<T>(string sql)
    {
        using (SQLiteConnection connection = new SQLiteConnection(DefaultName.dateBasePath))
        {
            try
            {
                SQLiteCommand command = connection.CreateCommand(sql);
                List<T> list = command.ExecuteQuery<T>();
                return list;
            }
            catch (Exception e)
            {
                Debug.LogError(e.Message + ",查询表错误：" + typeof(T).Name);
                return null;
            }

        }
    }
    /// <summary>
    /// 执行任意sql语句
    /// </summary>
    /// <param name="sql"></param>
    public override void Execute(string sql)
    {
        using (SQLiteConnection connection = new SQLiteConnection(DefaultName.dateBasePath))
        {
            try
            {
                SQLiteCommand liteCommand = connection.CreateCommand(sql);
                liteCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Debug.LogError(e.Message + ",执行sql语句错误:"+ sql);
            }
            
        }
    }
}