public Error Insert(AccountModel.Account _account)
{
string query = string.Format("INSERT INTO Account (UUID, Profile, CreatedAt, UpdatedAt) VALUES ('{0}', '{1}', '{2}', '{3}')",
_account.accountID, _account.profile, ModelUtility.NewUtcNow(), ModelUtility.NewUtcNow());
return(SQLiteUtility.Execute(connection, query));
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
string path = Server.MapPath("./");
string dataSource = Path.Combine(path, "Dentaku.db");
using (SQLiteUtility util = new SQLiteUtility(dataSource))
{
util.Connect();
string sql = "SELECT ZEIRITU FROM ZEIRITU";
Dictionary <string, dynamic> parameters = null;
DataTable dataTable = util.Fill(sql, parameters);
if (dataTable.Rows.Count > 0)
{
textBoxZei.Text = dataTable.Rows[0]["ZEIRITU"].ToString();
}
else
{
textBoxZei.Text = "0";
}
}
}
catch (Exception ex)
{
labelMessage.Text = ex.Message;
}
}
public DataSet FindActiveDS()
{
DataSet ds = new DataSet();
SQLiteUtility.ExecuteDataAdapter(SQLiteRepositoryFactory.GetConnection(), "select * from company", "company", ds);
return(ds);
}
public IActionResult Tables()
{
TablesModel model = new TablesModel();
model.TablesList = SQLiteUtility.GetTables(connectionString);
return(View(model));
}
public override void Run()
{
CompanyForm f = new CompanyForm(new Company());
f.CompanySave += delegate(object sender, CompanyEventArgs e) {
using (var d = new SaveFileDialog()) {
d.Title = "Filename for New Company";
d.FileName = f.Company.Name + ".sqlite";
if (d.ShowDialog() == DialogResult.OK)
{
SQLiteConnection con = new SQLiteConnection("Data Source=" + d.FileName + ";Version=3");
con.Open();
SQLiteUtility.ExecuteNonQuery(con, new StreamReader(Path.Combine(ApplicationUtility.RootDirectory, "sqlscript.sql")).ReadToEnd());
con.Close();
NHibernateHelper.CloseSession();
NHibernateHelper.Configuration = new SQLiteDatabase(con.ConnectionString).CreateConfiguration();
new NHibernateCompanyRepository().SaveOrUpdate(f.Company);
new NHibernateUserRepository().SaveOrUpdate(new User("admin", "root"));
}
}
};
WorkbenchSingleton.AddDialog(f);
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
string path = Server.MapPath("./sqlite/");
string dataSource = Path.Combine(path, "Kotsuhi.db");
using (SQLiteUtility util = new SQLiteUtility(dataSource))
{
util.Connect();
string createTbl = "CREATE TABLE IF NOT EXISTS kotsuhi(" +
"data_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"userid TEXT, " +
"target_date TEXT, " +
"transport TEXT, " +
"station_from TEXT, " +
"station_to TEXT, " +
"oneway_cost INTEGER, " +
"ido_kbn TEXT " +
")";
util.Execute(createTbl, null);
}
}
catch (Exception ex)
{
}
}
private void migrateData()
{
string srcDir = Path.Combine(Application.streamingAssetsPath, "db");
string srcFile = Path.Combine(srcDir, Constant.DataBaseFile);
if (!Directory.Exists(Constant.DataBaseDir))
{
Directory.CreateDirectory(Constant.DataBaseDir);
}
if (!File.Exists(Constant.DataBasePath))
{
File.Copy(srcFile, Constant.DataBasePath);
}
Error error;
SQLiteUtility.Open(Constant.DataBasePath, out error);
if (Error.OK != error)
{
this.LogError(error);
return;
}
AccountMock.AutoMigrate();
SQLiteUtility.Close(Constant.DataBasePath, out error);
if (Error.OK != error)
{
this.LogError(error);
}
}
public DataSet FindOpenDS()
{
DataSet ds = new DataSet();
SQLiteUtility.ExecuteDataAdapter(SQLiteRepositoryFactory.GetConnection(), "select * from company", "company", ds);
SQLiteUtility.ExecuteDataAdapter(SQLiteRepositoryFactory.GetConnection(), "select * from invoices where status = 0", "invoices", ds);
SQLiteUtility.ExecuteDataAdapter(SQLiteRepositoryFactory.GetConnection(), "select * from customers where inactive = 0", "customers", ds);
return(ds);
}
public DataSet FindOpenDS()
{
DataSet ds = new DataSet();
SQLiteUtility.ExecuteDataAdapter(SQLiteRepositoryFactory.GetConnection(), "select * from company", "company", ds);
SQLiteUtility.ExecuteDataAdapter(SQLiteRepositoryFactory.GetConnection(), "select * from purchase_orders where status = 0", "purchase_orders", ds);
SQLiteUtility.ExecuteDataAdapter(SQLiteRepositoryFactory.GetConnection(), "select * from vendors where inactive = 0", "vendors", ds);
return(ds);
}
protected void ButtonLogin_Click(object sender, EventArgs e)
{
try
{
string path = Server.MapPath("./");
string dataSource = Path.Combine(path, "WebApp.db");
using (SQLiteUtility util = new SQLiteUtility(dataSource))
{
util.Connect();
string sql = "SELECT * FROM userinfo WHERE userid = @userid";
string userid = TextBoxId.Text;
Dictionary <string, dynamic> parameters = new Dictionary <string, dynamic>
{
{ "userid", userid },
};
DataTable dataTable = util.Fill(sql, parameters);
if (dataTable.Rows.Count > 0)
{
if (dataTable.Rows[0]["password"].ToString().Equals(TextBoxPw.Text))
{
LabelMessage.Text = "認証に成功しました。";
SessionManager.UserInfo userInfo = new SessionManager.UserInfo(Session)
{
UserId = userid,
};
Server.Transfer("~/Menu.aspx", true);
}
else
{
LabelMessage.Text = "パスワードが違います。";
}
}
else
{
LabelMessage.Text = "ユーザーが未登録です。";
}
}
}
catch (Exception ex)
{
LabelMessage.Text = ex.Message;
}
}
static void Main(string[] args)
{
if (args == null)
{
}
using (SQLiteUtility util = new SQLiteUtility("ConsolApp.db"))
{
util.Connect();
string sql = "CREATE TABLE IF NOT EXISTS decimal_table(" +
"id INTEGER PRIMARY KEY AUTOINCREMENT," +
"decimal REAL)";
util.Execute(sql);
}
}
public void AutoMigrate()
{
string query = @"CREATE TABLE IF NOT EXISTS Account(
ID INTEGER PRIMARY KEY NOT NULL,
UUID CHAR(32) UNIQUE NOT NULL,
Profile TEXT NOT NULL,
CreatedAt CHAR(19) NOT NULL,
UpdatedAt CHAR(19) NOT NULL
)";
Error error = SQLiteUtility.Execute(connection, query);
if (error != Error.OK)
{
this.LogError(error.message);
}
}
public AccountModel.Account QueryProfile(string _accountID, out Error _err)
{
_err = Error.OK;
AccountModel.Account account = new AccountModel.Account();
string query = string.Format("SELECT * FROM Account WHERE UUID='{0}'", _accountID);
_err = SQLiteUtility.Execute(connection, query, (_reader) =>
{
while (_reader.Read())
{
account.profile = _reader.GetString(_reader.GetOrdinal("Profile"));
}
});
return(account);
}
void Start()
{
Debug.Log("--------------- Start ------------------------");
Error error;
SQLiteUtility.Open(Constant.DataBasePath, out error);
if (Error.OK != error)
{
this.LogError(error);
}
// setup data
modelAccount.SaveActiveAccount(DataCache.activeAccountID);
executeBootloader();
}
protected void cmdToroku_Click(object sender, EventArgs e)
{
//DataTable dt = (DataTable)MeisaiData.DataSource;
//DataRow dr = dt.NewRow();
//dr["gNo"] = "1";
//dr.EndEdit();
//dt.Rows.Add(dr);
//dt.AcceptChanges();
//this.MeisaiData.DataSource = dt;
//this.MeisaiData.DataBind();
try
{
string path = Server.MapPath("./sqlite/");
string dataSource = Path.Combine(path, "Kotsuhi.db");
using (SQLiteUtility util = new SQLiteUtility(dataSource))
{
util.Connect();
string sql = "INSERT INTO kotsuhi" +
"(userid, target_date, transport, station_from, station_to, oneway_cost, ido_kbn) " +
"VALUES(@userid, @target_date, @transport, @station_from, @station_to, @oneway_cost, @ido_kbn)";
Dictionary <string, dynamic> prams = new Dictionary <string, dynamic> {
{ "userid", new SessionManager.UserInfo(Page.Session).UserId },
{ "target_date", cdrDate.SelectedDate.ToShortDateString() },
{ "transport", txtKikan.Text },
{ "station_from", txtFrom.Text },
{ "station_to", txtTo.Text },
{ "oneway_cost", txtKingaku.Text },
{ "ido_kbn", drpIkoKbn.SelectedValue }
};
util.Execute(sql, prams);
}
cmdShow_Click(sender, e);
}
catch (Exception ex)
{
// 何も見なかったことにする。
}
}
protected void Page_Load(object sender, EventArgs e)
{
string path = Server.MapPath("./sqlite/");
string dataSource = Path.Combine(path, "todo.db");
using (SQLiteUtility util = new SQLiteUtility(dataSource))
{
util.Connect();
string sql = "SELECT * FROM Todo";
Dictionary <string, dynamic> parameters = null;
DataTable table = util.Fill(sql, parameters);
MainRepeater.DataSource = table;
MainRepeater.DataBind();
}
}
protected void btnInsert_Click(object sender, EventArgs e)
{
try
{
string path = Server.MapPath("./sqlite/");
string dataSource = Path.Combine(path, "yougo.db");
using (SQLiteUtility util = new SQLiteUtility(dataSource))
{
util.Connect();
string sql = "INSERT INTO yougo VALUES(\"\",\"\",@name,@biko,@URL);";
string name = this.txtNameItem.Text;
string biko = this.txtBikoItem.Text;
string url = this.txtUrlItem.Text;
Dictionary <string, dynamic> parameters = new Dictionary <string, dynamic>
{
{ "name", name },
{ "biko", biko },
{ "url", url },
};
util.Execute(sql, parameters);
sql = "SELECT * FROM yougo";
DataTable dataTable = util.Fill(sql);
if (dataTable.Rows.Count > 0)
{
Repeater1.DataSource = dataTable;
Repeater1.DataBind();
}
}
}
catch (Exception ex)
{
var aaa = ex;
}
}
void OnDestroy()
{
Debug.Log("--------------- OnDestroy ------------------------");
UIFacade[] facades = this.transform.Find("UIFacades").GetComponentsInChildren <UIFacade>();
foreach (UIFacade facade in facades)
{
facade.Cancel();
}
foreach (UIFacade facade in uIFacades)
{
facade.Cancel();
}
release();
Error error;
SQLiteUtility.Close(Constant.DataBasePath, out error);
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
string path = Server.MapPath("./sqlite/");
string dataSource = Path.Combine(path, "yougo.db");
using (SQLiteUtility util = new SQLiteUtility(dataSource))
{
util.Connect();
string sql = "SELECT * FROM yougo";
string name = this.txtNameItem.Text;
string biko = this.txtBikoItem.Text;
string url = this.txtUrlItem.Text;
Dictionary <string, dynamic> parameters = new Dictionary <string, dynamic>
{
{ "name", name },
{ "biko", biko },
{ "url", url },
};
DataTable dataTable = util.Fill(sql, parameters);
if (dataTable.Rows.Count > 0)
{
Repeater1.DataSource = dataTable;
Repeater1.DataBind();
}
}
}
catch (Exception ex)
{
this.LabelMessage.Text = ex.Message;
}
}
public IActionResult CommandSubmit(string commandText)
{
SQLiteUtility.ExecuteCommand(connectionString, commandText);
return(RedirectToAction("Command", "Data", new { area = "DataWarehouse" }));
}
protected void cmdShow_Click(object sender, EventArgs e)
{
DataTable dt = (DataTable)MeisaiData.DataSource;
try
{
string path = Server.MapPath("./sqlite/");
string dataSource = Path.Combine(path, "Kotsuhi.db");
using (SQLiteUtility util = new SQLiteUtility(dataSource))
{
util.Connect();
string usr = new SessionManager.UserInfo(Page.Session).UserId;
string sql = "SELECT " +
" data_id" +
", target_date" +
", transport " +
", station_from" +
", station_to " +
", oneway_cost" +
", ido_kbn " +
", case when ido_kbn = '片道' then oneway_cost else oneway_cost*2 end as cost" +
" FROM kotsuhi WHERE userid = '" + usr + "' ORDER BY userid , data_id";
DataTable dataTable = util.Fill(sql, null);
dataTable.Columns.Add("day");
dataTable.Columns.Add("station_nyoro");
//dataTable.Columns.Add("cost");
if (dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
dr["day"] = "";
dr["station_nyoro"] = "～";
//if (dr["ido_kbn"] == "0")
//{
// dr["ido_kbn"] = "片道";
// dr["cost"] = dr["oneway_cost"];
//}
//else
//{
// dr["ido_kbn"] = "往復";
// dr["cost"] = (int.Parse(dr["oneway_cost"].ToString()) * 2).ToString();
//}
//dt.Rows.Add(dr);
}
//dataTable.Rows[0][];
// this.MeisaiData.DataSource = dataTable;
//this.MeisaiData.DataBind();
dataTable.AcceptChanges();
this.MeisaiData.DataSource = dataTable;
this.MeisaiData.DataBind();
}
else
{
}
}
}
catch (Exception ex)
{
}
}
public IActionResult QuerySubmit(string query)
{
return(View(SQLiteUtility.GetQueryResult(connectionString, query)));
}
public IActionResult QueryExecute(string name)
{
return(View("QuerySubmit", SQLiteUtility.GetQueryResult(connectionString, savedQueries[name])));
}
public static async Task Run(string path, long startId, bool ignoreHashCheck, bool includeDeleted)
{
string tempFolderPath = Path.Combine(path, "_temp");
string imageFolderPath = Path.Combine(path, "images");
string metadataDatabasePath = Path.Combine(path, "danbooru.sqlite");
string lastPostJsonPath = Path.Combine(path, "last_post.json");
PathUtility.CreateDirectoryIfNotExists(path);
PathUtility.CreateDirectoryIfNotExists(tempFolderPath);
PathUtility.CreateDirectoryIfNotExists(imageFolderPath);
using (SqliteConnection connection = new SqliteConnection(new SqliteConnectionStringBuilder
{
DataSource = metadataDatabasePath,
}.ToString()))
{
connection.Open();
SQLiteUtility.TryCreateTable(connection);
while (true)
{
// Get posts metadata as json
JObject[] postJObjects = null;
await TaskUtility.RunWithRetry(async() =>
{
Log.Info($"Downloading metadata ... ({startId} ~ )");
postJObjects = await DanbooruUtility.GetPosts(startId);
}, e =>
{
Log.Error(e);
return(true);
},
10,
3000);
if (postJObjects.Length == 0)
{
Log.Info("There is no posts.");
break;
}
// Validate post
Log.Info($"Checking {postJObjects.Length} posts ...");
Post[] posts = postJObjects.Select(p => ConvertToPost(p)).Where(p => p != null).ToArray();
Parallel.ForEach(posts, post =>
{
if (string.IsNullOrEmpty(post.Md5))
{
Log.Debug($"Skip for empty MD5 : Id={post.Id}");
return;
}
if (string.IsNullOrEmpty(post.ImageUrl))
{
Log.Debug($"Skip for empty image URL : Id={post.Id}");
return;
}
if (post.IsDeleted && !includeDeleted)
{
return;
}
if (post.IsPending)
{
return;
}
post.IsValid = true;
string metadataPath = GetPostLocalMetadataPath(imageFolderPath, post);
try
{
if (File.Exists(metadataPath))
{
Post cachedPost = ConvertToPost(JObject.Parse(File.ReadAllText(metadataPath)));
if (cachedPost == null || post.UpdatedDate > cachedPost.UpdatedDate)
{
post.ShouldSaveMetadata = true;
post.ShouldUpdateImage = true;
}
}
else
{
post.ShouldSaveMetadata = true;
post.ShouldUpdateImage = true;
}
}
catch (Exception e)
{
post.ShouldSaveMetadata = true;
Log.Error(e);
}
string imagePath = GetPostLocalImagePath(imageFolderPath, post);
if (!File.Exists(imagePath))
{
post.ShouldDownloadImage = true;
return;
}
else
{
if (post.ShouldUpdateImage || !ignoreHashCheck)
{
string cachedImageMd5 = GetMd5Hash(imagePath);
if (post.Md5 != cachedImageMd5)
{
post.ShouldDownloadImage = true;
Log.Info($"MD5 is different to cached image : Id={post.Id}, {post.Md5} (new) != {cachedImageMd5} (cached)");
return;
}
}
}
});
int shouldDownloadCount = posts.Where(p => p.ShouldDownloadImage).Count();
int shouldUpdateCount = posts.Where(p => p.ShouldSaveMetadata).Count();
int pendingCount = posts.Where(p => p.IsPending).Count();
if (shouldUpdateCount > 0 || shouldDownloadCount > 0)
{
Log.Info($"{shouldUpdateCount}/{posts.Length} posts are updated. {pendingCount} posts are pending. Downloading {shouldDownloadCount} posts ...");
}
foreach (Post post in posts)
{
if (!post.IsValid)
{
continue;
}
string metadataPath = GetPostLocalMetadataPath(imageFolderPath, post);
string imagePath = GetPostLocalImagePath(imageFolderPath, post);
string tempImagePath = GetPostTempImagePath(tempFolderPath, post);
PathUtility.CreateDirectoryIfNotExists(Path.GetDirectoryName(imagePath));
try
{
await TaskUtility.RunWithRetry(async() =>
{
if (post.ShouldDownloadImage)
{
Log.Info($"Downloading post {post.Id} ...");
await Download(post.ImageUrl, tempImagePath);
string downloadedMd5 = GetMd5Hash(tempImagePath);
if (downloadedMd5 != post.Md5)
{
Log.Warn($"MD5 hash of downloaded image is different : Id={post.Id}, {post.Md5} (metadata) != {downloadedMd5} (downloaded)");
try
{
File.Delete(tempImagePath);
}
finally { }
try
{
File.Delete(metadataPath);
}
finally { }
throw new Exception();
}
File.Delete(imagePath);
File.Move(tempImagePath, imagePath);
}
if (post.ShouldDownloadImage || post.ShouldSaveMetadata)
{
PathUtility.ChangeFileTimestamp(imagePath, post.CreatedDate, post.UpdatedDate);
}
if (post.ShouldSaveMetadata)
{
File.WriteAllText(metadataPath, post.JObject.ToString());
}
}, e =>
{
return(!(e is NotRetryableException));
}, 10, 3000);
}
catch (NotRetryableException)
{
Log.Error($"Can't retryable exception was occured : Id={post.Id}");
post.IsValid = false;
}
}
Log.Info("Updating database ...");
SQLiteUtility.InsertOrReplace(connection, posts.Where(p => p.IsValid).Select(p => p.JObject));
long lastId = long.Parse(posts.Last().Id);
startId = lastId + 1;
}
try
{
Directory.Delete(tempFolderPath, true);
}
catch (Exception e)
{
Log.Warn(e);
}
Log.Info("Dump command is complete.");
}
}
public IActionResult ExportCSV(string query)
{
return(File(Encoding.UTF8.GetBytes(SQLiteUtility.GetQueryResult(connectionString, query).ConvertToCSV()), "text/csv", "data.csv"));
}
public IActionResult CreateTableSubmit(string tableName, IList <TableAddColumnModel> tableColumns)
{
SQLiteUtility.CreateTable(connectionString, tableName, tableColumns);
return(RedirectToAction("Tables", "Data", new { area = "DataWarehouse" }));
}
public IActionResult EditTable(string tableName)
{
TableModel model = SQLiteUtility.GetTableModel(connectionString, tableName);
return(View(model));
}
public IActionResult EditRow(string tableName, IList <string> row, IList <string> newRow)
{
SQLiteUtility.EditRow(connectionString, tableName, row, newRow);
return(RedirectToAction("EditTable", "Data", new { area = "DataWarehouse", tableName = tableName }));
}
public Error Delete(AccountModel.Account _account)
{
string query = string.Format("DELETE FROM Account WHERE UUID='{0}'", _account.accountID);
return(SQLiteUtility.Execute(connection, query));
}
public IActionResult CommandExecute(string name)
{
SQLiteUtility.ExecuteCommand(connectionString, savedCommands[name]);
return(RedirectToAction("Command", "Data", new { area = "DataWarehouse" }));
}
