public async Task TestJson()
{
var organisations = new OrganisationTable("orgs");
var users = new UserTable();
var services = new ServiceCollection()
.AddRoscoeSqlServer("Server=localhost;Database=test;");
using (var provider = services.BuildServiceProvider())
{
var db = new RoscoeDb(provider);
var schema = GraphQLSchema.Create(x => new RootQueryType(x, db));
var result = await schema.ExecuteRequestAsync(@"query {
Organisations {
id: Id
Name
UserNames
Users {
Id
Name
Organisation {
Name
}
}
}
}", null);
var json = JsonConvert.SerializeObject(result, Formatting.Indented);
}
}
public async Task QueryTest()
{
var services = new ServiceCollection()
.AddRoscoeSqlServer("Server=localhost;Database=test;");
using (var provider = services.BuildServiceProvider())
{
var db = new RoscoeDb(provider);
var organisations = new OrganisationTable("o");
var query = db.Query(() => new
{
Id = organisations.Id.Value(),
Name = Elvis(organisations.Name.Value(), x => x.ToUpper()),
NameIsFoo = db.Functions
.Case()
.When(organisations.Name == new Foo().Value(), 1.DbValue())
.Else(0)
.EndCase().Value(),
})
.From(organisations)
.ForJsonPath();
var result = await db.ExecuteAsync(query);
}
}
public UserType(IAsyncBatch batch, RoscoeDb db, UserTable users, JsonQueryBuilder queryBuilder)
{
this.batch = batch;
this.db = db;
this.users = users;
this.queryBuilder = queryBuilder;
}
public async Task QueryTest()
{
var services = new ServiceCollection()
.AddRoscoePostgres("Host=localhost;Database=test");
using (var provider = services.BuildServiceProvider())
{
var db = new RoscoeDb(provider);
var organisations = new OrganisationTable("o");
var subquery = db.Query(Guid.Parse("c0cac2b4-4821-4135-900b-4a36b46f0122").DbValue()).Value;
var subquery2 = db.Query(() => Guid.Parse("c0cac2b4-4821-4135-900b-4a36b46f0122").DbValue().Value()).Value;
var from = db.Query(() => new { Id = Guid.Parse("c0cac2b4-4821-4135-900b-4a36b46f0122").DbValue().Value() }).Value;
var query = db.Query(() => new
{
Id = organisations.Id.NullValue(),
Name = organisations.Name.ToLower().Value(),
//IdName = (organisations.Id + organisations.Name).Value(),
})
.From(organisations)
//.InnerJoin(from, from.Value().Id == organisations.Id)
.Where(organisations.Id == subquery.DbValue());
//.InnerJoin(organisations, organisations.Id == organisations.Id)
//.Where(organisations.Name == "")
//.GroupBy(organisations.Id);
var sql = query.ToString();
var result = await db.ExecuteAsync(query);
}
}
public static IWrapper <PostgresQueryCommand <T> > Query <T>(this RoscoeDb db, IDbFragment <T> expression)
{
Check.IsNotNull(db, nameof(db));
return(db.ServiceProvider.GetRequiredService <IPostgresQueryCommandFactory>()
.Create(db.ServiceProvider, expression));
}
public static IWrapper <SqlServerQueryCommand <T> > Query <T>(this RoscoeDb db, Expression <Func <T> > expression)
{
Check.IsNotNull(db, nameof(db));
return(db.ServiceProvider.GetRequiredService <ISqlServerQueryCommandFactory>()
.Create(db.ServiceProvider, expression));
}
public static IWrapper <SqlServerQueryCommand> Query(this RoscoeDb db)
{
Check.IsNotNull(db, nameof(db));
return(db.ServiceProvider.GetRequiredService <ISqlServerQueryCommandFactory>()
.Create(db.ServiceProvider));
}
public async Task UpdateTest()
{
var services = new ServiceCollection()
.AddRoscoePostgres("Host=localhost;Database=test");
using (var provider = services.BuildServiceProvider())
{
var db = new RoscoeDb(provider);
var organisations = new OrganisationTable("o");
var subquery = db.Query(() => Guid.Parse("c0cac2b4-4821-4135-900b-4a36b46f0122").DbValue().Value()).Value;
var query = db.Update(organisations)
.Set(organisations.Name, "new name".DbValue())
//.From(organisations)
//.InnerJoin(organisations, organisations.Id == organisations.Id)
.Where(organisations.Id == subquery.DbValue())
.Returning(() => new
{
Id = organisations.Id.Value(),
Name = organisations.Name.Value(),
});
var sql = query.ToString();
var result = await db.ExecuteAsync(query);
}
}
public PostgresJsonQueryBuilderAdapter(RoscoeDb db, JsonBuildObjectFragment buildObject)
{
Check.IsNotNull(db, nameof(db));
Check.IsNotNull(buildObject, nameof(buildObject));
this.db = db;
this.buildObject = buildObject;
}
public SqlServerJsonQueryBuilderAdapter(RoscoeDb db, IWrapper <IWrapper <SelectClause> > command)
{
Check.IsNotNull(db, nameof(db));
Check.IsNotNull(command, nameof(command));
this.db = db;
this.command = command;
}
public RootQueryType(IAsyncBatch batch, RoscoeDb db)
{
this.batch = batch;
this.db = db;
provider = batch.Add(new RoscoeAsyncOperation <RoscoeDb>(db));
enumProvider = batch.Add(new ObjectDataProvider <OrgType>(token => Task.FromResult(OrgType.Sme)));
}
public static IWrapper <PostgresUpdateCommand> Update(this RoscoeDb db, Table table)
{
Check.IsNotNull(db, nameof(db));
Check.IsNotNull(table, nameof(table));
return(db.ServiceProvider.GetRequiredService <IPostgresUpdateCommandFactory>()
.Create(db.ServiceProvider, table));
}
public static IWrapper <PostgresInsertValuesCommand <T> > InsertValues <T>(this RoscoeDb db, Table table, T expression)
{
Check.IsNotNull(db, nameof(db));
Check.IsNotNull(table, nameof(table));
Check.IsNotNull(expression, nameof(expression));
return(db.ServiceProvider.GetRequiredService <IPostgresInsertValuesCommandFactory>()
.Create(db.ServiceProvider, table, expression));
}
public OrganisationType(
IAsyncBatch batch,
RoscoeDb db,
OrganisationTable organisations,
JsonQueryBuilder queryBuilder)
{
this.batch = batch;
this.db = db;
this.organisations = organisations;
this.queryBuilder = queryBuilder;
}
public async Task TestStrongTypeQuery()
{
var services = new ServiceCollection()
.AddRoscoeSqlServer("Server=localhost;Database=test;");
using (var provider = services.BuildServiceProvider())
{
var db = new RoscoeDb(provider);
var schema = GraphQLSchema.Create(x => new RootQueryType(x, db));
var result = await schema.QueryAsync(x => new
{
Organisations = x.Organisations()
.Select(y => new
{
//Foo = y.Id().GetValue(null),
Id = y.Id().Value() + "foo",
Name = y.Name().Value(),
Type = y.Type().Value(),
Users = y.Users()
.Select(z => new
{
Org = z.Organisation()
.Select(a => new
{
Name = a.Name().Value(),
}),
}),
}),
});
/*
* schema.ExecuteQueryAsync((x, dr) => new
* {
* Organisations = GraphQLSchemaOperations.Select(x.Organisations(), dr, (y, dr2) => new
* {
* Id = (string)y.Id.GetValue(dr2),
* Name = (string)y.Name.GetValue(dr2),
* Users = GraphQLSchemaOperations.Select(y.Users(), dr2, (z, dr3) => new
* {
* Org = GraphQLSchemaOperations.Select(z.Organisation(), dr3, (a, dr4) => new
* {
* Name = (string)a.Name().GetValue(dr4),
* },
* },
* }),
* });
*
*/
var json = JsonConvert.SerializeObject(result, Formatting.Indented);
}
}
public void InsertTest()
{
var services = new ServiceCollection()
.AddRoscoePostgres("");
using (var provider = services.BuildServiceProvider())
{
var db = new RoscoeDb(provider);
var organisations = new OrganisationTable("o");
var selectQuery = db.Query(() => new
{
Id = organisations.Id.NullValue(),
Name = organisations.Name.ToLower().Value(),
//IdName = (organisations.Id + organisations.Name).Value(),
})
.From(organisations);
var updateQuery = db.Update(organisations)
.Set(organisations.Name, "new name".DbValue())
//.From(organisations)
//.InnerJoin(organisations, organisations.Id == organisations.Id)
.Where(organisations.Name == "")
;
var query = db.InsertValues(organisations, new { organisations.Id, organisations.Name })
.Values(new { Id = Guid.NewGuid().DbValue(), Name = "org 1".DbValue() })
.OnConflict(organisations.Id, organisations.Name)
//.DoNothing()
.Update(x => x.Set(organisations.Name, "org 1".DbValue()))
.Returning(() => new
{
Id = organisations.Id.Value(),
});
var selectSql = selectQuery.ToString();
var updateSql = updateQuery.ToString();
var sql = query.ToString();
}
}
public void DeleteTest()
{
var services = new ServiceCollection()
.AddRoscoePostgres("");
using (var provider = services.BuildServiceProvider())
{
var db = new RoscoeDb(provider);
var organisations = new OrganisationTable("o");
var query = db.Delete(organisations)
.From(organisations)
.InnerJoin(organisations, organisations.Id == organisations.Id)
.Where(organisations.Name == "")
.Returning(() => new
{
organisations.Id,
});
var sql = query.ToString();
}
}
public static DerivedTable <T> FromValues <T>(this RoscoeDb db, params Expression <Func <T> >[] values)
{
Check.IsNotNull(db, nameof(db));
return(new DerivedTable <T>("__derived", values));
}
public static IWrapper <SqlServerInsertCommand <TColumns> > Insert <TColumns>(this RoscoeDb db, Table table, TColumns expression, SqlServerQueryCommand <TColumns> values)
{
Check.IsNotNull(db, nameof(db));
Check.IsNotNull(table, nameof(table));
Check.IsNotNull(expression, nameof(expression));
return(db.ServiceProvider.GetRequiredService <ISqlServerInsertCommandFactory>()
.Create(db.ServiceProvider, table, expression, values));
}
public static IWrapper <PostgresQueryCommand> Query(this RoscoeDb db)
{
Check.IsNotNull(db, nameof(db));
return(db.ServiceProvider.GetRequiredService <PostgresQueryCommand>());
}
