1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
|
using System;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using MediaBrowser.Model.Logging;
using SQLitePCL.pretty;
using System.Linq;
using SQLitePCL;
namespace Emby.Server.Implementations.Data
{
public abstract class BaseSqliteRepository : IDisposable
{
protected string DbFilePath { get; set; }
protected SemaphoreSlim WriteLock = new SemaphoreSlim(1, 1);
protected ILogger Logger { get; private set; }
protected BaseSqliteRepository(ILogger logger)
{
Logger = logger;
}
protected virtual bool EnableConnectionPooling
{
get { return true; }
}
static BaseSqliteRepository()
{
SQLite3.EnableSharedCache = false;
int rc = raw.sqlite3_config(raw.SQLITE_CONFIG_MEMSTATUS, 0);
//CheckOk(rc);
}
protected virtual SQLiteDatabaseConnection CreateConnection(bool isReadOnly = false)
{
ConnectionFlags connectionFlags;
//isReadOnly = false;
if (isReadOnly)
{
connectionFlags = ConnectionFlags.ReadOnly;
//connectionFlags = ConnectionFlags.Create;
//connectionFlags |= ConnectionFlags.ReadWrite;
}
else
{
connectionFlags = ConnectionFlags.Create;
connectionFlags |= ConnectionFlags.ReadWrite;
}
if (EnableConnectionPooling)
{
connectionFlags |= ConnectionFlags.SharedCached;
}
else
{
connectionFlags |= ConnectionFlags.PrivateCache;
}
connectionFlags |= ConnectionFlags.NoMutex;
var db = SQLite3.Open(DbFilePath, connectionFlags, null);
var queries = new[]
{
"PRAGMA page_size=4096",
"PRAGMA journal_mode=WAL",
"PRAGMA temp_store=memory",
"PRAGMA synchronous=Normal",
//"PRAGMA cache size=-10000"
};
//foreach (var query in queries)
//{
// db.Execute(query);
//}
db.ExecuteAll(string.Join(";", queries));
return db;
}
internal static void CheckOk(int rc)
{
string msg = "";
if (raw.SQLITE_OK != rc)
{
throw CreateException((ErrorCode)rc, msg);
}
}
internal static Exception CreateException(ErrorCode rc, string msg)
{
var exp = new Exception(msg);
return exp;
}
private bool _disposed;
protected void CheckDisposed()
{
if (_disposed)
{
throw new ObjectDisposedException(GetType().Name + " has been disposed and cannot be accessed.");
}
}
public void Dispose()
{
_disposed = true;
Dispose(true);
GC.SuppressFinalize(this);
}
private readonly object _disposeLock = new object();
/// <summary>
/// Releases unmanaged and - optionally - managed resources.
/// </summary>
/// <param name="dispose"><c>true</c> to release both managed and unmanaged resources; <c>false</c> to release only unmanaged resources.</param>
protected virtual void Dispose(bool dispose)
{
if (dispose)
{
try
{
lock (_disposeLock)
{
WriteLock.Wait();
CloseConnection();
}
}
catch (Exception ex)
{
Logger.ErrorException("Error disposing database", ex);
}
}
}
protected virtual void CloseConnection()
{
}
protected List<string> GetColumnNames(IDatabaseConnection connection, string table)
{
var list = new List<string>();
foreach (var row in connection.Query("PRAGMA table_info(" + table + ")"))
{
if (row[1].SQLiteType != SQLiteType.Null)
{
var name = row[1].ToString();
list.Add(name);
}
}
return list;
}
protected void AddColumn(IDatabaseConnection connection, string table, string columnName, string type, List<string> existingColumnNames)
{
if (existingColumnNames.Contains(columnName, StringComparer.OrdinalIgnoreCase))
{
return;
}
connection.ExecuteAll(string.Join(";", new string[]
{
"alter table " + table,
"add column " + columnName + " " + type + " NULL"
}));
}
}
}
|
