public static long CreateGroup(string name, bool guest, string parent, byte r, byte g, byte b, SQLiteConnector conn, string[] nodes = null, string prefix = null, string suffix = null)
{
long id;
using (var bl = new SQLiteQueryBuilder(Plugin.SQLSafeName))
{
bl.InsertInto(TableDefinition.TableName,
new DataParameter(TableDefinition.ColumnNames.Name, name),
new DataParameter(TableDefinition.ColumnNames.ApplyToGuests, guest),
new DataParameter(TableDefinition.ColumnNames.Parent, parent),
new DataParameter(TableDefinition.ColumnNames.Chat_Red, r),
new DataParameter(TableDefinition.ColumnNames.Chat_Green, g),
new DataParameter(TableDefinition.ColumnNames.Chat_Blue, b),
new DataParameter(TableDefinition.ColumnNames.Chat_Prefix, prefix),
new DataParameter(TableDefinition.ColumnNames.Chat_Suffix, suffix)
);
id = ((IDataConnector)conn).ExecuteInsert(bl);
}
if (nodes != null)
foreach (var nd in nodes)
{
var nodeId = PermissionTable.InsertRecord(conn, nd, false);
GroupPermissionsTable.InsertRecord(conn, id, nodeId);
}
return id;
}
public static bool Create(SQLiteConnector conn)
{
using (var bl = new SQLiteQueryBuilder(Plugin.SQLSafeName))
{
bl.TableCreate(TableName, Columns);
return ((IDataConnector)conn).ExecuteNonQuery(bl) > 0;
}
}
public static bool DeleteGroup(string name, SQLiteConnector conn)
{
using (var bl = new SQLiteQueryBuilder(Plugin.SQLSafeName))
{
bl.Delete(TableDefinition.TableName, new WhereFilter(TableDefinition.ColumnNames.Name, name));
return ((IDataConnector)conn).ExecuteNonQuery(bl) > 0;
}
}
public static bool Exists(SQLiteConnector conn)
{
using (var bl = new SQLiteQueryBuilder(Plugin.SQLSafeName))
{
bl.TableExists(TableName);
return ((IDataConnector)conn).Execute(bl);
}
}
public static bool DeleteLink(SQLiteConnector conn, long userId, long groupId)
{
using (var bl = new SQLiteQueryBuilder(Plugin.SQLSafeName))
{
bl.Delete(TableDefinition.TableName,
new WhereFilter(TableDefinition.ColumnNames.UserId, userId.ToString()),
new WhereFilter(TableDefinition.ColumnNames.GroupId, groupId.ToString()));
return ((IDataConnector)conn).ExecuteNonQuery(bl) > 0;
}
}
public override void Load(IDataConnector conn)
{
using (var sb = new SQLiteQueryBuilder(Plugin.SQLSafeName))
{
sb.SelectAll(TableDefinition.TableName);
_data = conn.ExecuteArray<UserPermisison>(sb);
}
ProgramLog.Error.Log(this.GetType().Name + ": " + (_data == null ? "NULL" : _data.Length.ToString()));
}
public static long InsertRecord(SQLiteConnector conn, string node, bool deny)
{
using (var bl = new SQLiteQueryBuilder(Plugin.SQLSafeName))
{
bl.InsertInto(TableDefinition.TableName,
new DataParameter(TableDefinition.ColumnNames.Node, node),
new DataParameter(TableDefinition.ColumnNames.Deny, deny)
);
return ((IDataConnector)conn).ExecuteInsert(bl);
}
}
public void QueryBuilder_ShouldHydrateCustomerList()
{
var customers = new SQLiteQueryBuilder <Customer>()
.SetSql("select CustomerId, FirstName, LastName, Company, Address, City, State, Country, PostalCode As Zip, Phone, Fax, Email from Customer")
.GetResult().List;
Assert.True(customers.Count > 0);
Assert.True(customers.All(c =>
c.FirstName.IsNotEmpty() &&
c.LastName.IsNotEmpty() &&
c.CustomerId > 0));
}
public static long InsertRecord(SQLiteConnector conn, long groupId, long permissionId)
{
using (var bl = new SQLiteQueryBuilder(Plugin.SQLSafeName))
{
bl.InsertInto(TableDefinition.TableName,
new DataParameter(TableDefinition.ColumnNames.GroupId, groupId),
new DataParameter(TableDefinition.ColumnNames.PermissionId, permissionId)
);
return ((IDataConnector)conn).ExecuteInsert(bl);
}
}
public void QueryBuilder_ShouldBeAbleToSupplyConnection()
{
using (var conn = new SQLiteConnection(@"Data Source=Data\chinook.db;Version=3;"))
{
var customers = new SQLiteQueryBuilder <Customer>()
.SetSql("select CustomerId, FirstName, LastName, Company, Address, City, State, Country, PostalCode, Phone, Fax, Email from Customer where CustomerId = $CustomerId")
.AddParameter("$CustomerId", 19)
.MapProperty(c => c.FullName, row => $"{row.GetString("FirstName")} {row.GetString("LastName")}")
.GetResult(conn).List;
Assert.True(customers.First().FullName == "Tim Goyer");
}
}
public void CreateTable()
{
var query = Query
.CreateTable("foobar")
.Field <int>("foobarId", FieldOptions.NotNull | FieldOptions.PrimaryKey | FieldOptions.AutoIncrement)
.Field <string>("name", 100, FieldOptions.NotNull)
.Field <string>("info");
var builder = new SQLiteQueryBuilder();
var queryString = builder.GetQueryString(query);
Assert.Equal("CREATE TABLE `foobar` (`foobarId` integer NOT NULL PRIMARY KEY AUTOINCREMENT, `name` text NOT NULL, `info` text) ;", queryString);
}
/// <summary>
/// Get a cursor on all database rows, all rows where key matches value if specified. </summary>
/// <param name="key"> optional key to match. </param>
/// <param name="value"> optional value to match. </param>
/// <returns> cursor on all database rows. </returns>
/// <exception cref="RuntimeException"> if an error occurs. </exception>
//JAVA TO C# CONVERTER WARNING: Method 'throws' clauses are not available in .NET:
//ORIGINAL LINE: private android.database.Cursor getCursor(String key, Object value) throws RuntimeException
private Cursor getCursor(string key, object value)
{
SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
builder.Tables = mManager.TableName;
string[] selectionArgs;
if (key == null)
{
selectionArgs = null;
}
else
{
builder.appendWhere(key + " = ?");
selectionArgs = new string[] { value.ToString().ToString() };
}
return(builder.query(Database, null, null, selectionArgs, null, null, null));
}
public void QueryBuilder_ShouldPopulateInvoicesAndChildCustomer()
{
var invoices = new SQLiteQueryBuilder <Invoice>()
.SetSql(@"
select inv.*, cust.*
from Invoice inv
inner join Customer cust on inv.CustomerId = cust.CustomerId
where cust.CustomerId = 19")
.MapProperty(inv => inv.Customer, row => new Customer
{
CustomerId = row.GetInteger("CustomerId").Value,
FirstName = row.GetString("FirstName"),
LastName = row.GetString("LastName")
})
.GetResult().List;
Assert.True(invoices.First().Customer.FirstName == "Tim");
}
/// <Docs>Implement this to initialize your content provider on startup.</Docs>
/// <remarks>Implement this to initialize your content provider on startup.
/// This method is called for all registered content providers on the
/// application main thread at application launch time. It must not perform
/// lengthy operations, or application startup will be delayed.</remarks>
/// <summary>
/// Raises the create event.
/// </summary>
/// <description>
/// Students: We've coded this for you. We just create a new WeatherDbHelper for later use
/// here.
/// </description>
public override bool OnCreate()
{
_weatherByLocationSettingQueryBuilder = new SQLiteQueryBuilder();
//This is an inner join which looks like
//weather INNER JOIN location ON weather.location_id = location._id
_weatherByLocationSettingQueryBuilder.Tables =
WeatherContract.Weather.TableName + " INNER JOIN " +
WeatherContract.Location.TableName +
" ON " + WeatherContract.Weather.TableName +
"." + WeatherContract.Weather.ColumnLocationKey +
" = " + WeatherContract.Location.TableName +
"." + WeatherContract.Location.Id;
_openHelper = new WeatherDbHelper(Context);
return(true);
}
public void CreateTablePrimaryKey()
{
var query = Query
.CreateTable("foobar")
.Field <int>("foobarId", FieldOptions.PrimaryKey);
var builder = new SQLiteQueryBuilder();
var queryString = builder.GetQueryString(query);
Assert.Equal("CREATE TABLE `foobar` (`foobarId` integer PRIMARY KEY) ;", queryString);
query = Query
.CreateTable("foobar")
.Field <int>("foobarId1", FieldOptions.PrimaryKey)
.Field <int>("foobarId2", FieldOptions.PrimaryKey);
builder = new SQLiteQueryBuilder();
queryString = builder.GetQueryString(query);
Assert.Equal("CREATE TABLE `foobar` (`foobarId1` integer, `foobarId2` integer, PRIMARY KEY (`foobarId1`, `foobarId2`)) ;", queryString);
}
public override ICursor Query(Android.Net.Uri uri, string[] projection, string selection, string[] selectionArgs, string sortOrder)
{
SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
qb.Tables = DATABASE_TABLE_NAME;
switch (uriMatcher.Match(uri))
{
case LOCATIONS:
qb.SetProjectionMap(mProjectionMap);
break;
case LOCATION_ID:
qb.SetProjectionMap(mProjectionMap);
qb.AppendWhere(_ID + "=" + uri.PathSegments.ElementAt(1));
break;
default:
throw new ArgumentException("Unknown URI " + uri);
}
// If no sort order is specified use the default
string orderBy;
if (sortOrder.Length < 1)
{
orderBy = DEFAULT_SORT_ORDER;
}
else
{
orderBy = sortOrder;
}
// Get the database and run the query
SQLiteDatabase db = dbHelper.ReadableDatabase;
ICursor c = qb.Query(db, projection, selection, selectionArgs, null, null, orderBy);
// Tell the cursor what uri to watch, so it knows when its source data changes
c.SetNotificationUri(Context.ContentResolver, uri);
return(c);
}
/**
* Handle incoming queries.
*/
public override ICursor Query(Uri uri, string[] projection, string selection,
string[] selectionArgs, string sortOrder)
{
// Constructs a new query builder and sets its table name
SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
qb.SetTables(MainTable.TABLE_NAME);
switch (mUriMatcher.Match(uri))
{
case MAIN:
// If the incoming URI is for main table.
qb.SetProjectionMap(mNotesProjectionMap);
break;
case MAIN_ID:
// The incoming URI is for a single row.
qb.SetProjectionMap(mNotesProjectionMap);
qb.AppendWhere(IBaseColumnsConstants._ID + "=?");
selectionArgs = DatabaseUtilsCompat.AppendSelectionArgs(selectionArgs,
new string[] { uri.GetLastPathSegment() });
break;
default:
throw new System.ArgumentException("Unknown URI " + uri);
}
if (TextUtils.IsEmpty(sortOrder))
{
sortOrder = MainTable.DEFAULT_SORT_ORDER;
}
SQLiteDatabase db = mOpenHelper.GetReadableDatabase();
ICursor c = qb.Query(db, projection, selection, selectionArgs,
null /* no group */, null /* no filter */, sortOrder);
c.SetNotificationUri(Context.GetContentResolver(), uri);
return(c);
}
Android.Database.ICursor Query(String selection, String[] selectionArgs, String[] columns)
{
var builder = new SQLiteQueryBuilder();
builder.Tables = FTS_VIRTUAL_TABLE;
builder.SetProjectionMap(mColumnMap);
var cursor = builder.Query(databaseOpenHelper.ReadableDatabase,
columns, selection, selectionArgs, null, null, null);
if (cursor == null)
{
return(null);
}
else if (!cursor.MoveToFirst())
{
cursor.Close();
return(null);
}
return(cursor);
}
public static bool Create(SQLiteConnector conn)
{
try
{
using (var bl = new SQLiteQueryBuilder(Plugin.SQLSafeName))
{
bl.TableCreate(TableName, Columns);
((IDataConnector)conn).ExecuteNonQuery(bl);
}
//Set defaults
var pc = CommandParser.GetAvailableCommands(AccessLevel.PLAYER);
var ad = CommandParser.GetAvailableCommands(AccessLevel.OP);
var op = CommandParser.GetAvailableCommands(AccessLevel.CONSOLE); //Funny how these have now changed
CreateGroup("Guest", true, null, 255, 255, 255, conn, pc
.Where(x => !String.IsNullOrEmpty(x.Value.Node))
.Select(x => x.Value.Node)
.Distinct()
.ToArray());
CreateGroup("Admin", false, "Guest", 240, 131, 77, conn, ad
.Where(x => !String.IsNullOrEmpty(x.Value.Node))
.Select(x => x.Value.Node)
.Distinct()
.ToArray());
CreateGroup("Operator", false, "Admin", 77, 166, 240, conn, op
.Where(x => !String.IsNullOrEmpty(x.Value.Node))
.Select(x => x.Value.Node)
.Distinct()
.ToArray());
return true;
}
catch (Exception e)
{
ProgramLog.Log(e);
return false;
}
}
public static bool UpdateGroup(string name, bool guest, string parent, byte r, byte g, byte b, SQLiteConnector conn, string prefix = null, string suffix = null)
{
using (var bl = new SQLiteQueryBuilder(Plugin.SQLSafeName))
{
bl.Update(TableDefinition.TableName, new DataParameter[]
{
new DataParameter(TableDefinition.ColumnNames.Name, name),
new DataParameter(TableDefinition.ColumnNames.ApplyToGuests, guest),
new DataParameter(TableDefinition.ColumnNames.Parent, parent),
new DataParameter(TableDefinition.ColumnNames.Chat_Red, r),
new DataParameter(TableDefinition.ColumnNames.Chat_Green, g),
new DataParameter(TableDefinition.ColumnNames.Chat_Blue, b),
new DataParameter(TableDefinition.ColumnNames.Chat_Prefix, prefix),
new DataParameter(TableDefinition.ColumnNames.Chat_Suffix, suffix)
}, new WhereFilter(TableDefinition.ColumnNames.Name, name));
return ((IDataConnector)conn).ExecuteNonQuery(bl) > 0;
}
}
