#region License
// Разработано: Коротенко Владимиром Николаевичем (Vladimir N. Korotenko)
// email: koroten@ya.ru
// skype:vladimir-korotenko
// https://vkorotenko.ru
// Создано: 09.04.2019 23:04
#endregion
using SpecialForcesDirectory.Code;
using System.IO;
namespace SpecialForcesDirectory.Dbl
{
public class DataContext
{
private static readonly object Lock = new object();
private static DataContext _ctx;
public ContentDatabase ContentData { get; private set; }
public UserDatabase UserDatabase { get; private set; }
private DataContext()
{
ContentData = new ContentDatabase();
UserDatabase = new UserDatabase();
}
public static DataContext Instance
{
get
{
lock (Lock)
{
if (_ctx != null) return _ctx;
_ctx = new DataContext();
return _ctx;
}
}
}
public static void Close()
{
if (_ctx == null) return;
lock (Lock)
{
if (_ctx.ContentData != null)
{
_ctx.ContentData.Clear().Wait();
_ctx.ContentData = null;
}
if (_ctx.UserDatabase == null) return;
_ctx.UserDatabase.Clear().Wait();
_ctx.UserDatabase = null;
_ctx = null;
}
}
/// <summary>
/// Перезаписывает базу данных при обновлении, корректно закрывая ресурсы.
/// </summary>
/// <param name="file"></param>
public static void UpdateFile(byte[] file)
{
var fp = BaseContentDatabase.DatabasePath;
Close();
lock (Lock)
{
File.WriteAllBytes(fp, file);
_ctx = new DataContext();
}
}
public static void DropFile()
{
var fullPath = BaseContentDatabase.DatabasePath;
var userDb = BaseUserDatabase.DatabasePath;
Close();
lock (Lock)
{
File.Delete(fullPath);
File.Delete(userDb);
}
}
}
}#region License
// // Разработано: Коротенко Владимиром Николаевичем (Vladimir N. Korotenko)
// // email: koroten@ya.ru
// // skype:vladimir-korotenko
// // https://vkorotenko.ru
// // Создано: 13.08.2020 14:07
#endregion
using System.Collections.Generic;
using System.Threading.Tasks;
using SpecialForcesDirectory.Code;
using SpecialForcesDirectory.Models;
using SQLite;
namespace SpecialForcesDirectory.Dbl
{
public class ContentDatabase : BaseContentDatabase
{
#region Category repository
private async Task<SQLiteAsyncConnection> GetCategoryConn()
{
return await GetDatabaseConnection<Category>().ConfigureAwait(false);
}
public async Task<IEnumerable<Category>> GetCategories()
{
var conn = await GetCategoryConn();
return await AttemptAndRetry(() => conn.Table<Category>().ToListAsync()).ConfigureAwait(false);
}
public async Task<Category> GetCategory(int id)
{
var conn = await GetCategoryConn();
return await AttemptAndRetry(() => conn.GetAsync<Category>(id)).ConfigureAwait(false);
}
#endregion
#region Content Repository
private async Task<SQLiteAsyncConnection> GetCtxConn()
{
return await GetDatabaseConnection<Content>().ConfigureAwait(false);
}
public async Task<IEnumerable<Content>> GetItems()
{
var conn = await GetCtxConn();
return await AttemptAndRetry(() => conn.Table<Content>().ToListAsync()).ConfigureAwait(false);
}
public async Task<Content> GetItem(int id)
{
var conn = await GetCtxConn();
return await AttemptAndRetry(() => conn.GetAsync<Content>(id)).ConfigureAwait(false);
}
public async Task<int> DeleteItem(int id)
{
var conn = await GetCtxConn();
return await AttemptAndRetry(() => conn.DeleteAsync<Content>(id)).ConfigureAwait(false);
}
public async Task SaveItem(Content item)
{
var conn = await GetCtxConn();
if (item.Id == 0) await AttemptAndRetry(() => conn.InsertAsync(item)).ConfigureAwait(false);
await AttemptAndRetry(() => conn.UpdateAsync(item)).ConfigureAwait(false);
}
public async Task<IEnumerable<SRawItem>> GetItemsByQuery(string q, int lim)
{
var qu = q.ToUpper();
var list = new List<SRawItem>();
var conn = await GetCtxConn();
var result = conn.Table<Content>()
.Where(x => x.EnableTab1 == 1 && x.Tab1.ToUpper().Contains(qu)
|| x.EnableTab2 == 1 && x.Tab2.ToUpper().Contains(qu)
|| x.EnableTab3 == 1 && x.Tab3.ToUpper().Contains(qu)).Take(lim);
var r = await result.ToListAsync();
foreach (var content in r)
{
var st = GetSt(content, qu);
var title = await conn.Table<Category>().Where(x => x.Id == content.CatId).FirstAsync();
var ni = new SRawItem
{
Body = st.Body,
CatId = content.CatId.ToString(),
Title = title.Title,
Id = content.Id.ToString(),
MType = "a",
Tab = st.Tab
};
list.Add(ni);
}
return list;
}
private static TabStruct GetSt(Content content, string q)
{
if (content.Tab1.ToUpper().Contains(q))
return new TabStruct { Tab = "1", Body = content.Tab1 };
if (content.Tab2.ToUpper().Contains(q))
return new TabStruct { Tab = "2", Body = content.Tab2 };
return new TabStruct { Tab = "3", Body = content.Tab3 };
}
private struct TabStruct
{
public string Body { get; set; }
public string Tab { get; set; }
}
#endregion
public async Task Clear()
{
await BaseContentDatabase.Close();
}
}
}<PackageReference Include="sqlite-net-pcl" Version="1.8.116" />FILE* f = fopen("myfile.txt", "w");
int result = flock(fileno(f)), LOCK_EX);
// . . . .
// Locked file in use
// . . . .
int release = flock(f, LOCK_UN); // Unlock the file . . .using System;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Polly;
using SQLite;
using Xamarin.Essentials;
namespace SpecialForcesDirectory.Code
{
public abstract class BaseContentDatabase
{
public static readonly string DatabasePath = Path.Combine(FileSystem.AppDataDirectory, Settings.DataBaseName);
static readonly Lazy<SQLiteAsyncConnection> DatabaseConnectionHolder = new Lazy<SQLiteAsyncConnection>(() => new SQLiteAsyncConnection(DatabasePath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.SharedCache));
private static SQLiteAsyncConnection DatabaseConnection => DatabaseConnectionHolder.Value;
protected static async ValueTask<SQLiteAsyncConnection> GetDatabaseConnection<T>()
{
if (DatabaseConnection.TableMappings.Any(x => x.MappedType == typeof(T))) return DatabaseConnection;
// On sqlite-net v1.6.0+, enabling write-ahead logging allows for faster database execution
await DatabaseConnection.EnableWriteAheadLoggingAsync().ConfigureAwait(false);
await DatabaseConnection.CreateTablesAsync(CreateFlags.None, typeof(T)).ConfigureAwait(false);
return DatabaseConnection;
}
public static Task<T> AttemptAndRetry<T>(Func<Task<T>> action, int numRetries = 10)
{
return Policy.Handle<SQLiteException>().WaitAndRetryAsync(numRetries, PollyRetryAttempt).ExecuteAsync(action);
static TimeSpan PollyRetryAttempt(int attemptNumber) => TimeSpan.FromMilliseconds(Math.Pow(2, attemptNumber));
}
protected static async Task Close()
{
await DatabaseConnection.CloseAsync();
}
}
public class ContentDatabase : BaseContentDatabase
{
#region Category repository
private async Task<SQLiteAsyncConnection> GetCategoryConn()
{
return await GetDatabaseConnection<Category>().ConfigureAwait(false);
}
public async Task<IEnumerable<Category>> GetCategories()
{
var conn = await GetCategoryConn();
return await AttemptAndRetry(() => conn.Table<Category>().ToListAsync()).ConfigureAwait(false);
}
public async Task<Category> GetCategory(int id)
{
var conn = await GetCategoryConn();
return await AttemptAndRetry(() => conn.GetAsync<Category>(id)).ConfigureAwait(false);
}
#endregion
#region Content Repository
private async Task<SQLiteAsyncConnection> GetCtxConn()
{
return await GetDatabaseConnection<Content>().ConfigureAwait(false);
}
public async Task<IEnumerable<Content>> GetItems()
{
var conn = await GetCtxConn();
return await AttemptAndRetry(() => conn.Table<Content>().ToListAsync()).ConfigureAwait(false);
}
public async Task<Content> GetItem(int id)
{
var conn = await GetCtxConn();
return await AttemptAndRetry(() => conn.GetAsync<Content>(id)).ConfigureAwait(false);
}
public async Task<int> DeleteItem(int id)
{
var conn = await GetCtxConn();
return await AttemptAndRetry(() => conn.DeleteAsync<Content>(id)).ConfigureAwait(false);
}
public async Task SaveItem(Content item)
{
var conn = await GetCtxConn();
if (item.Id == 0) await AttemptAndRetry(() => conn.InsertAsync(item)).ConfigureAwait(false);
await AttemptAndRetry(() => conn.UpdateAsync(item)).ConfigureAwait(false);
}
public async Task<IEnumerable<SRawItem>> GetItemsByQuery(string q, int lim)
{
var qu = q.ToUpper();
var list = new List<SRawItem>();
var conn = await GetCtxConn();
var result = conn.Table<Content>()
.Where(x => x.EnableTab1 == 1 && x.Tab1.ToUpper().Contains(qu)
|| x.EnableTab2 == 1 && x.Tab2.ToUpper().Contains(qu)
|| x.EnableTab3 == 1 && x.Tab3.ToUpper().Contains(qu)).Take(lim);
var r = await result.ToListAsync();
foreach (var content in r)
{
var st = GetSt(content, qu);
var title = await conn.Table<Category>().Where(x => x.Id == content.CatId).FirstAsync();
var ni = new SRawItem
{
Body = st.Body,
CatId = content.CatId.ToString(),
Title = title.Title,
Id = content.Id.ToString(),
MType = "a",
Tab = st.Tab
};
list.Add(ni);
}
return list;
}
private static TabStruct GetSt(Content content, string q)
{
if (content.Tab1.ToUpper().Contains(q))
return new TabStruct { Tab = "1", Body = content.Tab1 };
if (content.Tab2.ToUpper().Contains(q))
return new TabStruct { Tab = "2", Body = content.Tab2 };
return new TabStruct { Tab = "3", Body = content.Tab3 };
}
private struct TabStruct
{
public string Body { get; set; }
public string Tab { get; set; }
}
#endregion
public async Task Clear()
{
await BaseContentDatabase.Close();
}
}
[Table("quote")]
public class Quote
{
[PrimaryKey]
[AutoIncrement]
[Column("id")]
public int Id { get; set; }
[Column("author")] public string Author { get; set; }
[Column("body")] public string Body { get; set; }
[Column("image")] public string Image { get; set; }
[Column("created")] public DateTime Created { get; set; }
}
public class QuoteRepository : BaseUserDatabase
{
private async Task<SQLiteAsyncConnection> GetConn()
{
return await GetDatabaseConnection<Quote>().ConfigureAwait(false);
}
public async Task<IEnumerable<Quote>> GetItems()
{
var conn = await GetConn();
return await AttemptAndRetry(() => conn.Table<Quote>().ToListAsync()).ConfigureAwait(false);
}
public async Task<Quote> GetItem(int id)
{
var conn = await GetConn();
return await AttemptAndRetry(() => conn.GetAsync<Quote>(id)).ConfigureAwait(false);
}
public async Task<int> DeleteItem(int id)
{
var conn = await GetConn();
return await AttemptAndRetry(() => conn.DeleteAsync<Quote>(id)).ConfigureAwait(false);
}
public async Task<int> SaveItem(Quote item)
{
var conn = await GetConn();
return await AttemptAndRetry(() => conn.InsertOrReplaceAsync(item)).ConfigureAwait(false);
}
public async Task DeleteAll()
{
var conn = await GetConn();
await AttemptAndRetry(() => conn.DeleteAllAsync<Quote>()).ConfigureAwait(false);
}
}
}synchronous = OFF + locking_mode = EXCLUSIVE + journal_mode = OFF
GET /api/dbsync/getdictionaries/
{
"schema": "0.4",
[
{ "name":"cityes", "sql": "create table ....."},
.....
]
}