public IEnumerable <Tuple <string, string> > Fields(string table)
{
using (var tableSchema = Database.GetSchema("Columns", new string[] { null, null, table }))
foreach (DataRow row in tableSchema.Rows)
{
yield return(new Tuple <string, string>(row["COLUMN_NAME"].ToString(), row["DATA_TYPE"].ToString()));
}
}
public MainForm()
{
//
// The InitializeComponent() call is required for Windows Forms designer support.
//
InitializeComponent();
RssCache = new List<RssCacheItem>();
try {
this.AppDataPath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
if (!System.IO.Directory.Exists(this.AppDataPath + "\\BmReader")){
System.IO.Directory.CreateDirectory(this.AppDataPath + "\\BmReader");
}
this.AppDataPath += "\\BmReader";
if (!System.IO.File.Exists(this.AppDataPath + "\\data.db")){
//System.Data.SQLite.SQLiteConnection.CreateFile(this.AppDataPath + "\\data.db");
}
conn =
new System.Data.SQLite.SQLiteConnection(String.Format("Data Source={0};Version=3;", this.AppDataPath + "\\data.db"));
conn.Open();
if(conn.GetSchema("Tables",new string[]{null, null, "URLRSS", null}).Rows.Count == 0){
System.Data.SQLite.SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE URLRSS (id integer primary key, Url CHAR(1000), active Boolean true)";
cmd.ExecuteNonQuery();
}
System.Data.SQLite.SQLiteDataAdapter da = new System.Data.SQLite.SQLiteDataAdapter("select * from urlrss", conn);
System.Data.SQLite.SQLiteCommandBuilder cb = new System.Data.SQLite.SQLiteCommandBuilder(da);
LoadFeeds();
} catch (Exception err){
MessageBox.Show(err.Message);
Application.Exit();
}
}
public static bool Create_db()
{
bool bolR;
var con = new System.Data.SQLite.SQLiteConnection();
var cmd = new System.Data.SQLite.SQLiteCommand();
string str_sql;
bolR = true;
Directory.CreateDirectory(mPathWEBAPI + "Data");
if (!File.Exists(mStrSQLiteDBFile))
{
try
{
System.Data.SQLite.SQLiteConnection.CreateFile(mStrSQLiteDBFile);
con = new System.Data.SQLite.SQLiteConnection()
{
ConnectionString = mStrSQLiteConnString
};
con.Open();
// con.ChangePassword(mStrDBPassword)
cmd.Connection = con;
str_sql = Conversions.ToString(Operators.ConcatenateObject(Operators.ConcatenateObject(Operators.ConcatenateObject(Operators.ConcatenateObject(Operators.ConcatenateObject(Operators.ConcatenateObject(@"
CREATE TABLE IF NOT EXISTS [users] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL DEFAULT 1,
[username] VARCHAR(50) NOT NULL,
[name] VARCHAR(512) NOT NULL,
[password] VARCHAR(512) NOT NULL,
[email] VARCHAR(512) DEFAULT (null),
[role] VARCHAR(512) DEFAULT (null),
[status] INTEGER DEFAULT (1),
[lastaccess] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[laststatus] INTEGER DEFAULT (200),
[lastipaddr] VARCHAR(20)
);
UPDATE [sqlite_sequence] SET seq = 1 WHERE name = 'users';
CREATE UNIQUE INDEX [id]
ON [users] (
[id] ASC
);
INSERT INTO users (username, name, password, role) VALUES ('admin', 'Administrator', '", PrepMySQLString(SimpleHash.ComputeHash("123456", "SHA256", null))), @"', 'Administrators');
INSERT INTO users (username, name, password, email, role) VALUES ('robs', 'Roberto Gaxiola', '"), PrepMySQLString(SimpleHash.ComputeHash("123456", "SHA256", null))), @"', '*****@*****.**', 'Administrators');
CREATE TABLE IF NOT EXISTS [tokens] (
[id] INTEGER NOT NULL DEFAULT 1 PRIMARY KEY AUTOINCREMENT,
[date] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[userid] INTEGER NOT NULL,
[refresh_token] VARCHAR(1024) NOT NULL,
[status] INTEGER NOT NULL DEFAULT(1),
[ipaddr] VARCHAR(20)
);
CREATE TABLE IF NOT EXISTS [swagger] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL DEFAULT 1,
[username] VARCHAR(50) NOT NULL,
[password] VARCHAR(512) NOT NULL,
[status] INTEGER DEFAULT (1),
[lastaccess] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[laststatus] INTEGER DEFAULT (200),
[lastipaddr] VARCHAR(20)
);
UPDATE [sqlite_sequence] SET seq = 1 WHERE name = 'swagger';
INSERT INTO swagger (username, password) VALUES ('admin', '"), PrepMySQLString(SimpleHash.ComputeHash("123456", "SHA256", null))), "');"));
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
WriteActivityLog(ex.Message, 2);
return(false);
}
finally
{
con.Close();
}
}
try
{
con = new System.Data.SQLite.SQLiteConnection()
{
ConnectionString = mStrSQLiteConnString
};
con.Open();
cmd.Connection = con;
var dtB = con.GetSchema("Columns");
if (dtB.Select("COLUMN_NAME = 'ipaddr' AND TABLE_NAME = 'tokens'").Length == 0)
{
str_sql = "ALTER TABLE tokens ADD COLUMN [ipaddr] VARCHAR(20);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
}
if (dtB.Select("COLUMN_NAME = 'name' AND TABLE_NAME = 'users'").Length == 0)
{
str_sql = "ALTER TABLE users ADD COLUMN [name] VARCHAR(512);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
}
if (dtB.Select("TABLE_NAME = 'validations'").Length == 0)
{
str_sql = @"CREATE TABLE IF NOT EXISTS [validations] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
[date] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[requestUri] TEXT,
[method] VARCHAR(20),
[status] INTEGER,
[statusMsg] TEXT,
[ipaddr] VARCHAR(20),
[userid] INTEGER,
[username] VARCHAR(50),
[role] VARCHAR(512),
[email] VARCHAR(512),
[nbf_date] VARCHAR(256),
[iat_date] VARCHAR(256),
[exp_date] VARCHAR(256),
[nbf] INTEGER,
[iat] INTEGER,
[exp] INTEGER,
[iss] VARCHAR(256),
[aud] VARCHAR(256),
[jti] VARCHAR(1024),
[token] TEXT
);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
// con.Close()
}
if (dtB.Select("COLUMN_NAME = 'method' AND TABLE_NAME = 'validations'").Length == 0)
{
str_sql = "ALTER TABLE validations ADD COLUMN [method] VARCHAR(20);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
str_sql = @"CREATE TABLE IF NOT EXISTS [validationsbk] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
[date] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[requestUri] TEXT,
[method] VARCHAR(20),
[status] INTEGER,
[statusMsg] TEXT,
[ipaddr] VARCHAR(20),
[userid] INTEGER,
[username] VARCHAR(50),
[role] VARCHAR(512),
[email] VARCHAR(512),
[nbf_date] VARCHAR(256),
[iat_date] VARCHAR(256),
[exp_date] VARCHAR(256),
[nbf] INTEGER,
[iat] INTEGER,
[exp] INTEGER,
[iss] VARCHAR(256),
[aud] VARCHAR(256),
[jti] VARCHAR(1024),
[token] TEXT
);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
str_sql = @"INSERT INTO validationsbk
SELECT id,date,requestUri,method,status,statusMsg,ipaddr,userid,username,role,email,nbf_date,iat_date,exp_date,nbf,iat,exp,iss,aud,jti,token
FROM validations;";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
str_sql = @"DROP table validations;
ALTER TABLE validationsbk RENAME TO validations;";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
}
if (dtB.Select("TABLE_NAME = 'swagger'").Length == 0)
{
str_sql = Conversions.ToString(Operators.ConcatenateObject(Operators.ConcatenateObject(@"CREATE TABLE IF NOT EXISTS [swagger] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL DEFAULT 1,
[username] VARCHAR(50) NOT NULL,
[password] VARCHAR(512) NOT NULL,
[status] INTEGER DEFAULT (1),
[lastaccess] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[laststatus] INTEGER DEFAULT (200),
[lastipaddr] VARCHAR(20)
);
UPDATE [sqlite_sequence] SET seq = 1 WHERE name = 'swagger';
INSERT INTO swagger (username, password) VALUES ('admin', '", PrepMySQLString(SimpleHash.ComputeHash("123456", "SHA256", null))), "');"));
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
// con.Close()
}
if (dtB.Select("TABLE_NAME = 'cardex_swagger'").Length == 0)
{
str_sql = @"CREATE TABLE IF NOT EXISTS [cardex_swagger] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
[date] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[requestUri] TEXT,
[status] INTEGER,
[statusMsg] TEXT,
[username] VARCHAR(50),
[ipaddr] VARCHAR(20)
);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
}
if (dtB.Select("TABLE_NAME = 'params'").Length == 0)
{
str_sql = @"CREATE TABLE IF NOT EXISTS [params] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
[swagAuth] INTEGER DEFAULT (0)
);
INSERT INTO params (swagAuth) VALUES (0);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
}
con.Close();
}
catch (Exception)
{
}
// If mBolAuto = False Then MsgBox("Error durante actualizacion de tablas" & vbCrLf & str_sql & vbCrLf & ex.Message)
finally
{
con.Close();
}
return(bolR);
}
/// <summary>
/// 加载数据
/// </summary>
internal void LoadData()
{
IsDataReady = false;
IsNotSuccessLoadData = false;
IsNotFoundDatabase = false;
try
{
Config.GetConfig().GetConfigs();
LoadSqlConn();
bool IsDBKeyChange = false;
bool IsUIDChange = false;
if (string.IsNullOrWhiteSpace(Resources.GetRes().DB_KEY))
{
Resources.GetRes().DB_KEY = "".GenereteRandomCode(32, 2);
IsDBKeyChange = true;
}
if (string.IsNullOrWhiteSpace(Resources.GetRes().UID))
{
Resources.GetRes().UID = "".GenereteRandomCode(32, 1);
IsUIDChange = true;
}
if (IsDBKeyChange || IsUIDChange)
{
Config.GetConfig().SetConfig(Config.GetConfig().ReadConfig().GetLines().ToList());
}
//检查数据库文件是否存在
if (!File.Exists(CONS_PATH))
{
IsNotFoundDatabase = true;
DbOperator dbOperator = new DbOperator();
string adminPassword = "******";
adminPassword = Key.GetKeys().Encryption(adminPassword);
dbOperator.CreateNewDB(CONS_PATH, Resources.GetRes().DB_KEY, adminPassword);
IsNotFoundDatabase = false;
ExceptionPro.ExpInfoLog("Successflully created a new database!");
}
InitialConn(Resources.GetRes().DB_KEY);
#if !DEBUG
//检查数据库文件密码是否为空
bool IsInvalidDB = false;
try
{
using (System.Data.SQLite.SQLiteConnection _con = new System.Data.SQLite.SQLiteConnection("Data Source=" + CONS_PATH + ";Password=;"))
{
_con.Open();
DataTable tables = _con.GetSchema("Tables");
IsInvalidDB = true;
}
}
catch
{
IsInvalidDB = false;
}
if (IsInvalidDB)
{
IsNotFoundDatabase = true;
throw new OybabException("Invalid database!");
}
#endif
//备份
Backup.Instance.BackupFile();
//加载缓存
LoadCache();
}
catch (Exception ex)
{
ExceptionPro.ExpLog(ex, null, false, "Exception_DatabaseLoadFailed");
}
}
