/// <summary>
/// Finds column by name in results.
/// Returns 0-based index, or -1 if not found.
/// </summary>
/// <param name="name">Column name in results, as returned by sqlite3_column_name. Case-sensitive.</param>
public int ColumnIndex(string name)
{
int n = ColumnCount;
if (n > 0 && !name.NE())
{
if (AStringUtil.IsAscii(name))
{
for (int i = 0; i < n; i++)
{
byte *b = SLApi.sqlite3_column_name(_st, i);
if (BytePtr_.AsciiEq(b, name))
{
return(i);
}
}
}
else
{
var bname = AConvert.ToUtf8(name);
for (int i = 0; i < n; i++)
{
byte *b = SLApi.sqlite3_column_name(_st, i);
if (BytePtr_.Eq(b, bname))
{
return(i);
}
}
}
}
return(-1);
}
///
protected virtual void Dispose(bool disposing)
{
if (_db != default)
{
var r = SLApi.sqlite3_close_v2(_db);
Debug.Assert(r == 0); SLUtil.Warn(r, "sqlite3_close");
_db = default;
}
}
///
protected virtual void Dispose(bool disposing)
{
if (_st != default)
{
//note: don't throw, because sqlite3_finalize can return error of previous sqlite3_step etc
SLApi.sqlite3_finalize(_st);
_st = default;
}
}
//rejected: not thread-safe. Other .NET wrappers ignore it too.
//void _Err(string func)
//{
// var r = SLApi.sqlite3_errcode(_db);
// if(r != 0 && r != SLError.Row) throw new SLException(r, _db, func);
//}
/// <summary>
/// Called by GetX functions when sqlite3_column_x returns null/0.
/// Shows warning if sqlite3_errcode is not 0 or Row.
/// Does not throw exception because it is not thread-safe.
/// </summary>
/// <param name="func"></param>
void _WarnGet([CallerMemberName] string func = null)
{
var r = SLApi.sqlite3_errcode(_db);
if (r != 0 && r != SLError.Row)
{
SLUtil.Warn(r, func, "Note: it may be a false positive if this database connection is used by multiple threads simultaneously without locking.");
}
}
/// <summary>
/// Returns <c>sqlite3_column_int64(column)</c>.
/// </summary>
/// <remarks>
/// Use this function to get integer values of size 8 bytes: long, ulong, 64-bit enum, maybe DateTime.
/// </remarks>
public long GetLong(SLIndexOrName column)
{
long r = SLApi.sqlite3_column_int64(_st, _C(column));
if (r == 0)
{
_WarnGet();
}
return(r);
}
///// <summary>
///// Returns <c>DateTime.FromBinary(sqlite3_column_int64(column))</c>.
///// </summary>
///// <param name="column"></param>
///// <param name="convertToLocal">If the value in database is stored as UTC, convert to local.</param>
///// <exception cref="ArgumentException">The value in database is not in the valid DateTime range.</exception>
///// <seealso cref="Bind(int, DateTime, bool)"/>
//public DateTime GetDateTime(SLIndexOrName column, bool convertToLocal = false)
//{
// var r = DateTime.FromBinary(GetLong(column)); //info: it's OK if 0/null
// if(convertToLocal && r.Kind == DateTimeKind.Utc) r = r.ToLocalTime();
// return r;
//}
/// <summary>
/// Returns <c>sqlite3_column_double(column)</c>.
/// </summary>
public double GetDouble(SLIndexOrName column)
{
double r = SLApi.sqlite3_column_double(_st, _C(column));
if (r == 0)
{
_WarnGet();
}
return(r);
}
/// <summary>
/// Returns <c>sqlite3_column_int(column)</c>.
/// </summary>
/// <remarks>
/// Use this function to get integer values of size 4, 2 or 1 bytes: int, uint, short, ushort, byte, sbyte, enum.
/// </remarks>
public int GetInt(SLIndexOrName column)
{
int r = SLApi.sqlite3_column_int(_st, _C(column));
if (r == 0)
{
_WarnGet();
}
return(r);
}
/// <summary>
/// Calls sqlite3_reset and/or sqlite3_clear_bindings. Returns self.
/// </summary>
/// <param name="resetStatement">Call sqlite3_reset. Default true.</param>
/// <param name="clearBindings">Call sqlite3_clear_bindings. Default true.</param>
public ASqliteStatement Reset(bool resetStatement = true, bool clearBindings = true)
{
//note: don't throw, because sqlite3_reset can return error of previous sqlite3_step
if (resetStatement)
{
SLApi.sqlite3_reset(_st);
}
if (clearBindings)
{
SLApi.sqlite3_clear_bindings(_st);
}
return(this);
}
int _B(SLIndexOrName p)
{
if (p.name == null)
{
return(p.index);
}
int r = SLApi.sqlite3_bind_parameter_index(_st, AConvert.ToUtf8(p.name));
if (r == 0)
{
throw new SLException($"Parameter '{p.name}' does not exist in the SQL statement.");
}
return(r);
}
/// <summary>
/// Calls sqlite3_step.
/// Returns true if results data available (sqlite3_step returned SQLITE_ROW).
/// </summary>
/// <exception cref="SLException">Failed.</exception>
public bool Step()
{
var r = SLApi.sqlite3_step(_st);
if (r == SLError.Row)
{
return(true);
}
if (r != SLError.Done)
{
_Err(r, "sqlite3_step");
}
return(false);
}
/// <summary>
/// Returns <c>sqlite3_column_blob(column)</c> and gets blob size.
/// </summary>
/// <param name="column"></param>
/// <param name="nBytes">Blob size.</param>
public void *GetBlob(SLIndexOrName column, out int nBytes)
{
int icol = _C(column);
void *r = SLApi.sqlite3_column_blob(_st, icol);
if (r == null)
{
nBytes = 0; _WarnGet();
}
else
{
nBytes = SLApi.sqlite3_column_bytes(_st, icol);
}
return(r);
}
/// <summary>
/// Calls sqlite3_prepare16_v3.
/// </summary>
/// <param name="db"></param>
/// <param name="sql">Single SQL statement.</param>
/// <param name="persistent">Use flag SQLITE_PREPARE_PERSISTENT.</param>
/// <exception cref="ArgumentNullException">db is null.</exception>
/// <exception cref="SLException">Failed.</exception>
/// <exception cref="NotSupportedException">sql contains more than single SQL statement.</exception>
public ASqliteStatement(ASqlite db, string sql, bool persistent = false)
{
_db = db ?? throw new ArgumentNullException();
int flags = persistent ? 1 : 0; //SQLITE_PREPARE_PERSISTENT
fixed(char *p = sql)
{
char *tail = null;
_Err(SLApi.sqlite3_prepare16_v3(db, p, sql.Length * 2, flags, ref _st, &tail), "sqlite3_prepare");
if (tail != null && tail - p != sql.Length)
{
throw new NotSupportedException("sql contains more than single SQL statement");
}
}
}
/// <summary>
/// Opens or creates a database file.
/// </summary>
/// <param name="file">
/// Database file. Can be:
/// - Full path. Supports environment variables etc, see <see cref="pathname.expand"/>
/// - ":memory:" - create a private, temporary in-memory database.
/// - "" - create a private, temporary on-disk database.
/// - Starts with "file:" - see <google>sqlite3_open_v2</google>.
/// </param>
/// <param name="flags"><google>sqlite3_open_v2</google> flags. Default: read-write, create file if does not exist (and parent directory).</param>
/// <param name="sql">
/// SQL to execute. For example, one or more ;-separated PRAGMA statements to configure the database connection. Or even "CREATE TABLE IF NOT EXISTS ...".
/// This function also always executes "PRAGMA foreign_keys=ON;PRAGMA secure_delete=ON;".
/// </param>
/// <exception cref="ArgumentException">Not full path.</exception>
/// <exception cref="SLException">Failed to open database or execute sql.</exception>
/// <remarks>
/// Calls <google>sqlite3_open_v2</google>.
/// <note>If a variable of this class is used by multiple threads, use <c>lock(variable) { }</c> where need.</note>
/// </remarks>
public sqlite(string file, SLFlags flags = SLFlags.ReadWriteCreate, string sql = null)
{
bool isSpec = file != null && (file.Length == 0 || file == ":memory:" || file.Starts("file:"));
if (!isSpec)
{
file = pathname.normalize(file);
if (flags.Has(SLFlags.SQLITE_OPEN_CREATE) && !filesystem.exists(file, true).File)
{
filesystem.createDirectoryFor(file);
}
}
var r = SLApi.sqlite3_open_v2(Convert2.Utf8Encode(file), ref _db, flags, null);
if (r != 0)
{
Dispose();
throw new SLException(r, "sqlite3_open " + file);
}
Execute("PRAGMA foreign_keys=ON;PRAGMA secure_delete=ON;" + sql);
}
/// <summary>
/// Opens or creates a database file.
/// </summary>
/// <param name="file">
/// Database file. Can be:
/// - Full path. Supports environment variables etc, see <see cref="APath.ExpandEnvVar"/>
/// - ":memory:" - create a private, temporary in-memory database.
/// - "" - create a private, temporary on-disk database.
/// - Starts with "file:" - see sqlite3_open_v2 documentation in SQLite website.
/// </param>
/// <param name="flags">sqlite3_open_v2 flags, documanted in SQLite website. Default: read-write, create file if does not exist (and parent directory).</param>
/// <param name="sql">
/// SQL to execute. For example, one or more ;-separated PRAGMA statements to configure the database connection. Or even "CREATE TABLE IF NOT EXISTS ...".
/// This function also always executes "PRAGMA foreign_keys=ON;PRAGMA secure_delete=ON;".
/// </param>
/// <exception cref="ArgumentException">Not full path.</exception>
/// <exception cref="SLException">Failed to open database or execute sql.</exception>
/// <remarks>
/// Calls sqlite3_open_v2.
/// <note>If a variable of this class is used by multiple threads, use <c>lock(variable) { }</c> where need.</note>
/// </remarks>
public ASqlite(string file, SLFlags flags = SLFlags.ReadWriteCreate, string sql = null)
{
bool isSpec = file != null && (file.Length == 0 || file == ":memory:" || file.Starts("file:"));
if (!isSpec)
{
file = APath.Normalize(file);
if (flags.Has(SLFlags.SQLITE_OPEN_CREATE) && !AFile.ExistsAsFile(file, true))
{
AFile.CreateDirectoryFor(file);
}
}
var r = SLApi.sqlite3_open_v2(AConvert.ToUtf8(file), ref _db, flags, null);
if (r != 0)
{
Dispose();
throw new SLException(r, "sqlite3_open " + file);
}
Execute("PRAGMA foreign_keys=ON;PRAGMA secure_delete=ON;" + sql);
}
/// <summary>
/// Returns <c>sqlite3_column_text(column)</c> as string.
/// </summary>
public string GetText(SLIndexOrName column)
{
int icol = _C(column);
if (_db.IsUtf16) //both these codes would work, but with long strings can be significantly slower if SQLite has to convert text encoding
{
char *t = SLApi.sqlite3_column_text16(_st, icol);
if (t != null)
{
return(new string(t, 0, SLApi.sqlite3_column_bytes16(_st, icol)));
}
}
else
{
byte *t = SLApi.sqlite3_column_text(_st, icol);
if (t != null)
{
return(AConvert.FromUtf8(t, SLApi.sqlite3_column_bytes(_st, icol)));
}
}
_WarnGet();
return(null);
}
/// <summary>Calls sqlite3_bind_blob64. Returns self.</summary> /// <exception cref="SLException">Failed.</exception> public ASqliteStatement Bind(SLIndexOrName sqlParam, void *blob, long nBytes) => _Err(SLApi.sqlite3_bind_blob64(_st, _B(sqlParam), blob, nBytes), "sqlite3_bind_blob64");
/// <summary>
/// Calls sqlite3_exec to execute one or more SQL statements that don't return data.
/// </summary>
/// <param name="sql">SQL statement, or several ;-separated statements.</param>
/// <exception cref="SLException">Failed to execute sql.</exception>
public void Execute(string sql)
{
var b = AConvert.ToUtf8(sql);
byte *es = null; //gets better error text than sqlite3_errstr; sqlite3_errmsg gets nothing after sqlite3_exec.
var r = SLApi.sqlite3_exec(_db, b, default, default, &es);
/// <summary> /// sqlite3_column_name. /// </summary> public string ColumnName(int index) => AConvert.FromUtf8(SLApi.sqlite3_column_name(_st, index));
/// <summary>Calls sqlite3_bind_int64. Returns self.</summary> /// <exception cref="SLException">Failed.</exception> public ASqliteStatement Bind(SLIndexOrName sqlParam, long value) => _Err(SLApi.sqlite3_bind_int64(_st, _B(sqlParam), value), "sqlite3_bind_int64");
/// <summary>Calls sqlite3_bind_double. Returns self.</summary> /// <exception cref="SLException">Failed.</exception> public ASqliteStatement Bind(SLIndexOrName sqlParam, double value) => _Err(SLApi.sqlite3_bind_double(_st, _B(sqlParam), value), "sqlite3_bind_double");
/// <summary>Calls sqlite3_bind_text16. Returns self.</summary> /// <exception cref="SLException">Failed.</exception> public ASqliteStatement Bind(SLIndexOrName sqlParam, string value) => _Err(SLApi.sqlite3_bind_text16(_st, _B(sqlParam), value, (value?.Length ?? 0) * 2), "sqlite3_bind_text16");
/// <summary>Calls sqlite3_bind_null. Returns self.</summary> /// <exception cref="SLException">Failed.</exception> /// <remarks>Usually don't need to call this function. Unset parameter values are null. The Bind(string/void*/Array/List) functions set null too if the value is null.</remarks> public ASqliteStatement BindNull(SLIndexOrName sqlParam) => _Err(SLApi.sqlite3_bind_null(_st, _B(sqlParam)), "sqlite3_bind_null");
