/* User-defined functions */
static async Task getTotalFunding(string udfId, string databaseId, string collectionId)
{
var collection = await client.ReadDocumentCollectionAsync(UriFactory.CreateDocumentCollectionUri(databaseId, collectionId));
var collectionSelfLink = collection.Resource.SelfLink;
var udfFileName = string.Format(@"Scripts\{0}.js", udfId);
var udf = new UserDefinedFunction
{
Id = udfId,
Body = File.ReadAllText(udfFileName)
};
await tryDeleteUDF(collectionSelfLink, udf.Id);
await client.CreateUserDefinedFunctionAsync(collectionSelfLink, udf);
var results = client.CreateDocumentQuery <dynamic>(collectionSelfLink,
"Select r.name as Name, udf.TotalFunding(r) as TotalFunding from root r where r.name='Cisco'",
new FeedOptions {
EnableCrossPartitionQuery = true
});
foreach (var result in results)
{
Console.WriteLine("The result is {0}", result);
}
}
public void UserDefinedFunctionExecute0Test()
{
mockUDFProxy.Setup(m => m.Apply(It.IsAny<IColumnProxy[]>()));
UserDefinedFunction<int> udf = new UserDefinedFunction<int>(mockUDFProxy.Object);
udf.Execute0();
mockUDFProxy.Verify(m => m.Apply(new IColumnProxy[]{}), Times.Once);
}
private async Task CreateUserDefinedFunctionsIfNotExistsAsync()
{
try
{
var userFunction = new UserDefinedFunction
{
Id = "stringify",
Body = @"function stringify(obj){
if(!obj) {
return ''
}
return JSON.stringify(obj);
}"
};
await _client.CreateUserDefinedFunctionAsync(GetCollectionUri(), userFunction);
}
catch { }
try
{
var userFunction = new UserDefinedFunction
{
Id = "array_firstOrDefault",
Body = @"function array_firstOrDefault(arr){
if(!arr || !arr.length) {
return null;
}
return arr[0];
}"
};
await _client.CreateUserDefinedFunctionAsync(GetCollectionUri(), userFunction);
}
catch { }
}
public UserDefinedFunction GetUserDefinedFunction(VariableNameToken functionName)
{
UserDefinedFunction function = null;
userDefinedFunctions.TryGetValue(functionName.Name, out function);
return(function);
}
// Create the UDF if does not exists..
private async static Task CreateUdfIfNotExists(DocumentClient client, string udfId)
{
Uri collectionLink = UriFactory.CreateDocumentCollectionUri(DbName, CollectionName);
var udfBody = System.IO.File.ReadAllText(@"" + udfId + ".js");
var udfDefinition = new UserDefinedFunction
{
Id = udfId,
Body = udfBody
};
bool needToCreate = false;
Uri udfUri = UriFactory.CreateUserDefinedFunctionUri(DbName, CollectionName, udfId);
try
{
await client.ReadUserDefinedFunctionAsync(udfUri);
}
catch (DocumentClientException dce)
{
if (dce.StatusCode != HttpStatusCode.NotFound)
{
throw;
}
else
{
needToCreate = true;
}
}
if (needToCreate)
{
await client.CreateUserDefinedFunctionAsync(collectionLink, udfDefinition);
}
}
public static void ScriptUserDefinedFunctions(Database sDatabase, ScriptingOptions cOption, ScriptingOptions rOption, string funcName, string schemaName)
{
UserDefinedFunction userFunction = sDatabase.UserDefinedFunctions[funcName, schemaName];
userFunction.Script(cOption);
userFunction.Script(rOption);
}
public async Task <ActionResult> Add(List <Matcher> model, string updateButton)
{
if (updateButton.Equals("Cancel"))
{
return(Redirect(CacheHelper.GetPreviousPage(_cache)));
}
try
{
UserDefinedFunction udf = await DigitalTwinsHelper.GetUserDefinedFunction(CacheHelper.GetObjectId(_cache), _cache, Loggers.SilentLogger, true);
if (udf.Matchers == null)
{
udf.Matchers = new System.Collections.ObjectModel.ObservableCollection <Matcher>();
}
foreach (Matcher matcher in model)
{
if (matcher.Add)
{
udf.Matchers.Add(matcher);
}
}
await DigitalTwinsHelper.UpdateUserDefinedFunctionAsync(_cache, Loggers.SilentLogger, udf);
return(Redirect(CacheHelper.GetPreviousPage(_cache)));
}
catch (Exception ex)
{
await FeedbackHelper.Channel.SendMessageAsync(ex.Message, MessageType.Info);
return(View());
}
}
public string Format(UserDefinedFunction udf)
{
var server = udf.CreateWrapper();
var values = new Dictionary <string, object>(StringComparer.InvariantCultureIgnoreCase)
{
{ Const.Catalog, udf.Parent.Name },
{ Const.Schema, udf.Schema },
{ Const.Name, udf.Name },
{ Const.NameEscaped, udf.Name.EscapeName() },
{ Const.Body, udf.TextBody },
{ Const.Header, udf.ScriptHeader(true) },
{ Const.Needs, GetNeeds(server, udf, DatabaseObjectTypes.StoredProcedure | DatabaseObjectTypes.UserDefinedFunction | DatabaseObjectTypes.View) ?? string.Empty }
};
switch (udf.FunctionType)
{
case UserDefinedFunctionType.Inline:
values[Const.FuncType] = Const.FuncInline;
break;
case UserDefinedFunctionType.Table:
values[Const.FuncType] = Const.FuncMulti;
break;
default:
values[Const.FuncType] = Const.FuncScalar;
break;
}
return(Const.Script.UserDefinedFunction.Format().With(values));
}
private async Task MergeTree(UserDefinedFunction udf, IEnumerable <Guid> spacePath, int level)
{
level++;
Guid spaceId = spacePath.First();
Space space = await DigitalTwinsHelper.GetSpaceAsync(spaceId, _cache, Loggers.SilentLogger);
if (spacePath.Count() == 1)
{
if (!space.UDFs.Exists(u => u.Id == udf.Id))
{
space.UDFs.Add(udf);
}
}
if (!spaces.Exists(s => s.Space.Id == space.Id))
{
int index = spaces.FindIndex(s => s.Space.Id == space.ParentSpaceId);
spaces.Insert(index + 1, new UISpace()
{
Space = space, MarginLeft = $"{25 * level - 1}px"
});
}
else
{
spaces.First(s => s.Space.Id == space.Id).Space = space;
}
if (spacePath.Count() > 1)
{
await MergeTree(udf, spacePath.Skip(1), level);
}
}
public JSListInstance FindAll(object a)
{
if (!(a is UserDefinedFunction))
{
throw new JavaScriptException(this.Engine, ErrorType.Error, "expecting comparison function");
}
UserDefinedFunction f = (UserDefinedFunction)a;
List <object> list = new List <object>();
foreach (object obj in this.array)
{
if (TypeConverter.ToBoolean(f.Call(this.Engine.Global, obj)))
{
list.Add(obj);
}
}
JSListInstance result = new JSListInstance(this.Engine.Object.InstancePrototype);
foreach (object obj in list)
{
result.Add(obj);
}
return(result);
}
public void SaveFields(UserDefinedFunction udf)
{
udf.ID = ID;
udf.Name = Name;
udf.Detail = Detail;
udf.Confirm = Confirm;
}
private async Task LoadAsync(UserDefinedFunction udf)
{
SpaceList = await DigitalTwinsHelper.GetSpacesAsync(_cache, Loggers.SilentLogger);
if (udf != null)
{
UDF = udf;
Content = await DigitalTwinsHelper.GetUserDefinedFunctionContent(UDF.Id, _cache, Loggers.SilentLogger);
//We get the matcher list to add the related conditions
List <Matcher> matchersWithConditions = new List <Matcher>();
var matchers = await DigitalTwinsHelper.GetMatchersBySpaceId(UDF.SpaceId, _cache, Loggers.SilentLogger, false);
foreach (Matcher matcher in UDF.Matchers)
{
matchersWithConditions.Add(matchers.First(t => t.Id == matcher.Id));
}
UDF.Matchers = new ObservableCollection <Matcher>(matchersWithConditions);
}
else
{
UDF = new UserDefinedFunction();
}
}
public DataTable getFunctionProperties(string dbName, string fName)
{
DataTable dt = new DataTable();
BuildPropertiesTable(dt);
Database db = ser.Databases[dbName];
UserDefinedFunction fun = db.UserDefinedFunctions[fName];
DataRow drCollation = dt.NewRow();
drCollation["Property"] = "排序规则";
drCollation["Value"] = db.Collation;
DataRow drRowCount = dt.NewRow();
drRowCount["Property"] = "返回值";
drRowCount["Value"] = "--";
DataRow drCreateDate = dt.NewRow();
drCreateDate["Property"] = "创建时间";
drCreateDate["Value"] = fun.CreateDate == null ? "" : fun.CreateDate.ToString();
DataRow drLastModifiedDate = dt.NewRow();
drLastModifiedDate["Property"] = "最后修改时间";
drLastModifiedDate["Value"] = fun.DateLastModified == null ? "" : fun.DateLastModified.ToString();
dt.Rows.Add(drCollation);
dt.Rows.Add(drRowCount);
dt.Rows.Add(drCreateDate);
dt.Rows.Add(drLastModifiedDate);
return(dt);
}
public bool CreateRoomMenuOption(object a, object b, object c)
{
if (this.CanAddControls || this.CanCreate)
{
if (!(a is Undefined) && b is UserDefinedFunction)
{
String text = a.ToString();
UserDefinedFunction callback = (UserDefinedFunction)b;
if (ScriptManager.RoomMenuOptions.Find(x => x.Text == text) == null)
{
JSCheckState cstate = JSCheckState.Unused;
if (c is bool)
{
cstate = ((bool)c) ? JSCheckState.Checked : JSCheckState.Unchecked;
}
ScriptManager.RoomMenuOptions.Add(new CustomJSMenuOption
{
Callback = callback,
Text = text,
CanCheck = cstate != JSCheckState.Unused,
IsChecked = cstate == JSCheckState.Checked
});
return(true);
}
}
}
return(false);
}
public async Task <bool> CreateUdf(string scriptFileName)
{
string udfId = Path.GetFileNameWithoutExtension(scriptFileName);
var udf = new UserDefinedFunction
{
Id = udfId,
Body = File.ReadAllText(scriptFileName)
};
Uri collectionUrl =
UriFactory.CreateDocumentCollectionUri(
DocumentDbManager.DatabaseId,
DocumentDbManager.CollectionId);
// 存在したら削除
var currentUdf = this.client.CreateUserDefinedFunctionQuery(collectionUrl).Where(u => u.Id == udfId).AsEnumerable().FirstOrDefault();
if (currentUdf != null)
{
var sp = await this.client.DeleteUserDefinedFunctionAsync(
UriFactory.CreateUserDefinedFunctionUri(
DocumentDbManager.DatabaseId,
DocumentDbManager.CollectionId,
currentUdf.Id));
}
// 作成
udf = await client.CreateUserDefinedFunctionAsync(collectionUrl, udf);
return(true);
}
/// <summary>
/// Create Cosmos DB Stored Procedure
/// </summary>
/// <param name="collectionUri"></param>
/// <param name="udfId"></param>
/// <returns></returns>
private async Task <UserDefinedFunction> CreateUDFAsync(Uri collectionUri, string udfId, string resourceName)
{
try
{
var udfBody = ResourceProvider.GetResourceString(ResourceNamespaces.CosmosDBSp, resourceName);
var udfDefinition = new UserDefinedFunction
{
Id = udfId,
Body = udfBody
};
var udfTask = Client.CreateUserDefinedFunctionAsync(collectionUri, udfDefinition);
while (!udfTask.IsCompleted && !udfTask.IsFaulted)
{
Thread.Sleep(5);
}
if (udfTask.IsFaulted)
{
throw new Exception("CreateUserDefinedFunction faulted");
}
var response = await udfTask;
return(response.Resource);
}
catch (Exception ex)
{
}
return(null);
}
public void FillFields(UserDefinedFunction udf)
{
ID = udf.ID;
Name = udf.Name;
Detail = udf.Detail;
Confirm = udf.Confirm;
}
public static async Task RunAsync(string[] args)
{
var client = DocumentDbExtension.CreateClient();
var database = await client.GetDatabaseAsync("IoTEvents");
var temperatureSamples = await client.GetCollectionAsync(database, "temperatureSamples");
var udf = new UserDefinedFunction
{
Id = "region"
,
Body = File.ReadAllText(@"Javascript\udf.Region.js")
};
await client.TryDeleteUDF(temperatureSamples.SelfLink, udf.Id);
var result = await client.CreateUserDefinedFunctionAsync(temperatureSamples.SelfLink, udf);
var sqlQuerySpec = new SqlQuerySpec {
QueryText = "SELECT s.Sensor.Id, udf.region(s) as regione FROM s"
};
var query = client.CreateDocumentQuery <dynamic>(temperatureSamples.SelfLink, sqlQuerySpec);
var items = query.ToList();
Console.ReadLine();
}
public CFunction(UserDefinedFunction sp)
{
_f = sp;
_db = sp.Parent;
InitializeComponent();
}
public async Task <UserDefinedFunction> SaveUdfAsync(Connection connection, DocumentCollection collection, string id, string function, string altLink)
{
var item = new UserDefinedFunction {
Id = id, Body = function
};
var client = GetClient(connection);
if (!string.IsNullOrEmpty(altLink))
{
var oldId = altLink.Split('/').Last();
if (item.Id != oldId)
{
var itemList = await GetUdfsAsync(connection, collection).ConfigureAwait(false);
if (itemList.Any(t => t.Id == item.Id))
{
throw new Exception("An item with the same id already exists!");
}
}
await client.DeleteUserDefinedFunctionAsync(altLink).ConfigureAwait(false);
}
var response = await client.CreateUserDefinedFunctionAsync(collection.SelfLink, item).ConfigureAwait(false);
return(response.Resource);
}
/// <summary>
/// Maps each group of the current DataFrame using a UDF and
/// returns the result as a DataFrame.
///
/// The user-defined function should take an Apache Arrow RecordBatch
/// and return another Apache Arrow RecordBatch. For each group, all
/// columns are passed together as a RecordBatch to the user-function and
/// the returned RecordBatch are combined as a DataFrame.
///
/// The returned <see cref="RecordBatch"/> can be of arbitrary length and its
/// schema must match <paramref name="returnType"/>.
/// </summary>
/// <param name="returnType">
/// The <see cref="StructType"/> that represents the shape of the return data set.
/// </param>
/// <param name="func">A grouped map user-defined function.</param>
/// <returns>New DataFrame object with the UDF applied.</returns>
public DataFrame Apply(StructType returnType, Func <RecordBatch, RecordBatch> func)
{
ArrowGroupedMapWorkerFunction.ExecuteDelegate wrapper =
new ArrowGroupedMapUdfWrapper(func).Execute;
UserDefinedFunction udf = UserDefinedFunction.Create(
Reference.Jvm,
func.Method.ToString(),
CommandSerDe.Serialize(
wrapper,
CommandSerDe.SerializedMode.Row,
CommandSerDe.SerializedMode.Row),
UdfUtils.PythonEvalType.SQL_GROUPED_MAP_PANDAS_UDF,
returnType.Json);
IReadOnlyList <string> columnNames = _dataFrame.Columns();
var columns = new Column[columnNames.Count];
for (int i = 0; i < columnNames.Count; ++i)
{
columns[i] = _dataFrame[columnNames[i]];
}
Column udfColumn = udf.Apply(columns);
return(new DataFrame((JvmObjectReference)Reference.Invoke(
"flatMapGroupsInPandas",
udfColumn.Expr())));
}
public async Task Run()
{
WriteLine("1. Use UDF");
WriteLine("2. Create");
UDFOption option = (UDFOption)ProgramHelper.EnterInt("");
switch (option)
{
case UDFOption.Use:
{
// The function work with zips collection and msnet18sql database.
// If you need a different collection or database, please write a piece of code to input data into the program.
string id = ProgramHelper.EnterText("Id ");
SqlQuerySpec query = new SqlQuerySpec()
{
QueryText = "SELECT c.id, c.loc, udf.udfCityState(c) as CityState FROM c where c.id = @id",
Parameters = new SqlParameterCollection()
{
new SqlParameter("@id", id)
}
};
Document document = _documentRepository.ReadDocumentByQuery <Document>(UriFactory.CreateDocumentCollectionUri(DatabaseId, CollectionId), query).ToList().FirstOrDefault();
WriteLine(document.ToString());
break;
}
case UDFOption.Create:
{
UserDefinedFunction udfDefinition = new UserDefinedFunction
{
Id = "udfRegex",
Body = File.ReadAllText(@"Scripts\udfRegex.js")
};
string databaseName = "";
DocumentCollection collectionName = null;
if (!InsertCollAndDatabase(ref databaseName, ref collectionName))
{
Warning("Collection >>> " + collectionName + " <<< don't exist.");
collectionName = await _collectionRepository.CreateCollection(databaseName, collectionName.Id);
ProgramHelper.Wait();
}
UserDefinedFunction newUDFunction = await _udfRepository.CreateUDFAsync(UriFactory.CreateDocumentCollectionUri(databaseName, collectionName.Id), udfDefinition);
WriteLine(string.Format("Created trigger {0}; RID: {1}", newUDFunction.Id, newUDFunction.ResourceId));
break;
}
default:
break;
}
}
public void UserDefinedFunctionExecute1Test()
{
mockUDFProxy.Setup(m => m.Apply(It.IsAny<IColumnProxy[]>()));
UserDefinedFunction<int> udf = new UserDefinedFunction<int>(mockUDFProxy.Object);
Column column1 = GeneratorColum();
udf.Execute1(column1);
mockUDFProxy.Verify(m => m.Apply(new IColumnProxy[] { column1.ColumnProxy }), Times.Once);
}
public DBFunctionView(UserDefinedFunction function, bool isSource, DifferencesType dt, string connectStr = "", string compareConnectStr = "")
{
DBFunction = function;
IsSourceDB = isSource;
Differences = dt;
ConnectString = connectStr;
CompareConnectString = compareConnectStr;
}
public void ScriptObject(UserDefinedFunction function, Options options)
{
StringCollection script = function.Script(_scriptingOptions);
string path = _directoryService.GetFunctionsDirectory(options.Root);
string fileName = Path.Combine(path, string.Concat(function.Name, ".sql"));
_fileWriter.WriteFile(fileName, script.Cast <string>());
}
/// <summary>
/// Initializes a new instance of the <see cref="ScriptableObject"/> class.
/// </summary>
/// <param name="userDefinedFunction">Object that can be scripted.</param>
public ScriptableObject(UserDefinedFunction userDefinedFunction)
{
new { userDefinedFunction }.AsArg().Must().NotBeNull();
this.Name = userDefinedFunction.Name;
this.ObjectToScript = userDefinedFunction;
this.DatabaseObjectType = ScriptableObjectType.UserDefinedFunction;
}
public void UserDefinedFunctionExecute0Test()
{
mockUDFProxy.Setup(m => m.Apply(It.IsAny <IColumnProxy[]>()));
UserDefinedFunction <int> udf = new UserDefinedFunction <int>(mockUDFProxy.Object);
udf.Execute0();
mockUDFProxy.Verify(m => m.Apply(new IColumnProxy[] {}), Times.Once);
}
private SqlSmoObject FunctionObj(SmoScriptingContext ctx)
{
var function = new UserDefinedFunction(ctx.Database, ctx.Metadata.Name, ctx.Metadata.Schema);
function.Refresh();
return(function);
}
private string getFunctionAlterSql(UserDefinedFunction function)
{
string sql = "";
sql += string.Format("--script for function {0}\r\n", function.Name);
sql += createToAlter(function.TextHeader) + function.TextBody + "\r\n" + "--\r\n";
return(sql.ToString());
}
/// <summary>
/// ########## ########### # ####### ### Oracle
/// </summary>
/// <param name="userConnection">################ ###########</param>
/// <param name="procedureName">### ########## #######</param>
/// <returns></returns>
private BaseStoredProcedure getOracleProcedure(UserConnection userConnection, string procedureName)
{
BaseStoredProcedure result;
result = new UserDefinedFunction(userConnection, procedureName);
((UserDefinedFunction)result).ReturnType = UserDefinedFunctionReturnType.Table;
return(result);
}
/// <summary>
/// If a UDF is found on the DocumentCollection for the Id supplied it is deleted
/// </summary>
/// <param name="collectionLink">DocumentCollection to search for the UDF</param>
/// <param name="udfId">Id of the UDF to delete</param>
/// <returns></returns>
private static async Task TryDeleteUDF(string collectionLink, string udfId)
{
UserDefinedFunction udf = client.CreateUserDefinedFunctionQuery(collectionLink).Where(u => u.Id == udfId).AsEnumerable().FirstOrDefault();
if (udf != null)
{
await client.DeleteUserDefinedFunctionAsync(udf.SelfLink);
}
}
public void UserDefinedFunctionExecute1Test()
{
mockUDFProxy.Setup(m => m.Apply(It.IsAny <IColumnProxy[]>()));
UserDefinedFunction <int> udf = new UserDefinedFunction <int>(mockUDFProxy.Object);
Column column1 = GeneratorColum();
udf.Execute1(column1);
mockUDFProxy.Verify(m => m.Apply(new IColumnProxy[] { column1.ColumnProxy }), Times.Once);
}
public ScriptableObject(IScriptingSource scriptingSource, ScriptSchemaObjectBase schemaObject)
{
_scriptingSource = scriptingSource;
_schemaObject = schemaObject;
_table = _schemaObject as Table;
_view = _schemaObject as View;
_sproc = _schemaObject as StoredProcedure;
_udf = _schemaObject as UserDefinedFunction;
}
public static string ArgAssignedToOutputTest()
{
using (HlslProgram program = new HlslProgram())
{
Type vsData = CreateVSType(program);
UserDefinedFunction udf = new UserDefinedFunction("vs_main");
Value argValue = udf.AddArgument(vsData);
DeclExpr output = new DeclExpr(vsData, argValue);
udf.AddExpr(output);
udf.AddExpr(new ReturnExpr(output));
program.AddFunction(udf);
return program.EmitRawShaderCode();
}
}
public static void Go()
{
var server = new Server(".");
Database database;
if (server.Databases.Contains("Test_Randal_Sql") == false)
{
database = new Database(server, "Test_Randal_Sql");
database.Create();
}
else
database = server.Databases["Test_Randal_Sql"];
if(database.StoredProcedures.Contains("mySp") == false)
{
var sp = new StoredProcedure(database, "mySp");
sp.TextMode = false;
sp.AnsiNullsStatus = false;
sp.QuotedIdentifierStatus = false;
sp.TextBody = "return -1";
sp.Create();
}
if (database.UserDefinedFunctions.Contains("myFunc") == false)
{
var func = new UserDefinedFunction(database, "myFunc");
func.TextMode = false;
func.ExecutionContext = ExecutionContext.Caller;
func.FunctionType = UserDefinedFunctionType.Scalar;
func.ImplementationType = ImplementationType.TransactSql;
func.DataType = DataType.Int;
func.TextBody = "begin return(-1); end";
func.Create();
}
if(database.Views.Contains("myView") == false)
{
var view = new View(database, "myView");
view.TextMode = false;
view.AnsiNullsStatus = false;
view.TextBody = "select 42 [AnswerToEverything]";
view.Create();
}
}
public void UserDefinedFunctionExecute6Test()
{
mockUDFProxy.Setup(m => m.Apply(It.IsAny<IColumnProxy[]>()));
UserDefinedFunction<int> udf = new UserDefinedFunction<int>(mockUDFProxy.Object);
Column column1 = GeneratorColum();
Column column2 = GeneratorColum();
Column column3 = GeneratorColum();
Column column4 = GeneratorColum();
Column column5 = GeneratorColum();
Column column6 = GeneratorColum();
udf.Execute6(column1, column2, column3, column4, column5, column6);
mockUDFProxy.Verify(m => m.Apply(new IColumnProxy[] { column1.ColumnProxy, column2.ColumnProxy, column3.ColumnProxy, column4.ColumnProxy,
column5.ColumnProxy, column6.ColumnProxy}), Times.Once);
}
/// <summary>
/// Replaces a user defined function as an asynchronous operation.
/// </summary>
/// <param name="client">document client.</param>
/// <param name="userDefinedFunctionUri">the self-link for the attachment.</param>
/// <param name="function">the updated user defined function.</param>
/// <param name="options">the request options for the request.</param>
/// <returns>The task object representing the service response for the asynchronous operation.</returns>
public static Task<ResourceResponse<UserDefinedFunction>> ReplaceUserDefinedFunctionExAsync(this DocumentClient client, UserDefinedFunction function, RequestOptions options = null)
{
SwapLinkIfNeeded(client, function);
return client.ReplaceUserDefinedFunctionAsync(function, options);
}
private void AddAssemblyButton_Click(System.Object sender,
System.EventArgs e)
{
Cursor csr = null;
Database db;
SqlAssembly asm;
UserDefinedFunction udf;
UserDefinedFunctionParameter parm;
ListViewItem AssemblyListViewItem;
try
{
csr = this.Cursor; // Save the old cursor
this.Cursor = Cursors.WaitCursor; // Display the waiting cursor
// Get selected database
db = (Database)DatabasesComboBox.SelectedItem;
asm = new SqlAssembly(db, "UtilityConversion");
asm.Owner = "dbo";
asm.AssemblySecurityLevel = AssemblySecurityLevel.Safe;
// This allows the assembly to be on a different server from SQL Server
// Use string array version which serializes the assembly
asm.Create(new String[] { AssemblyFileTextBox.Text });
udf = new UserDefinedFunction(db, "StringToInt32");
udf.TextMode = false;
udf.ImplementationType = ImplementationType.SqlClr;
udf.AssemblyName = "UtilityConversion";
udf.ClassName = "Microsoft.Samples.SqlServer.Conversions";
udf.MethodName = "StringToInt32";
udf.FunctionType = UserDefinedFunctionType.Scalar;
udf.DataType = DataType.Int;
parm = new UserDefinedFunctionParameter(udf, "@Input");
udf.Parameters.Add(parm);
parm.DataType = DataType.NVarChar(255);
udf.Create();
ShowAssemblies(true);
// Select the assembly just added
AssemblyListViewItem = AssembliesListView.FindItemWithText(
asm.Name);
AssemblyListViewItem.Selected = true;
AssemblyListViewItem.EnsureVisible();
}
catch (SmoException ex)
{
ExceptionMessageBox emb = new ExceptionMessageBox(ex);
emb.Show(this);
}
finally
{
this.Cursor = csr; // Restore the original cursor
}
}
public DbUserDefinedFunction(UserDefinedFunction smoObject)
{
this.smoObject = smoObject;
}
async Task AddUdf(string body, object idObject)
{
string id = idObject as string;
try
{
UserDefinedFunction udf = new UserDefinedFunction();
udf.Body = body;
udf.Id = id;
ResourceResponse<UserDefinedFunction> newudf;
using (PerfStatus.Start("CreateUDF"))
{
newudf =
await
_client.CreateUserDefinedFunctionAsync((Parent.Tag as DocumentCollection).GetLink(_client),
udf, Program.GetMain().GetRequestOptions());
}
Nodes.Add(new DocumentNode(_client, newudf.Resource, ResourceType.UserDefinedFunction));
// set the result window
string json = newudf.Resource.ToString();
Program.GetMain().SetResultInBrowser(json, null, false, newudf.ResponseHeaders);
}
catch (AggregateException e)
{
Program.GetMain().SetResultInBrowser(null, e.InnerException.ToString(), true);
}
catch (Exception e)
{
Program.GetMain().SetResultInBrowser(null, e.ToString(), true);
}
}
public void GrantAccessToSchemaObjects()
{
var helper = new TestHelper();
try
{
var schema = helper.GetSchema();
//schema.Owner = helper.GetUser().Name;
//schema.Alter();
var table = new Table(helper.GetDatabase(), "Table1", schema.Name);
table.Columns.Add(new Column(table, "Col1", DataType.Int));
table.Columns.Add(new Column(table, "Col2", DataType.NVarCharMax));
table.Create();
helper.AddCleanup(table);
var view = new View(helper.GetDatabase(), "View1", schema.Name)
{
TextMode = false,
TextBody = String.Format("SELECT Col1, Col2 FROM [{0}].[{1}]", table.Schema, table.Name)
};
//view.TextHeader = String.Format("CREATE VIEW [{0}].[{1}] AS", view.Schema, view.Name);
view.Create();
helper.AddCleanup(view);
var scalarTsqlFn = new UserDefinedFunction(helper.GetDatabase(), "ScalarTsqlFunction", schema.Name)
{
TextMode = false,
DataType = DataType.DateTime,
ExecutionContext = ExecutionContext.Caller,
FunctionType = UserDefinedFunctionType.Scalar,
ImplementationType = ImplementationType.TransactSql,
TextBody = "BEGIN RETURN GETDATE() END"
};
scalarTsqlFn.Create();
helper.AddCleanup(scalarTsqlFn);
var inlineTsqlFn = new UserDefinedFunction(helper.GetDatabase(), "InlineTsqlFunction", schema.Name)
{
TextMode = false,
ExecutionContext = ExecutionContext.Caller,
FunctionType = UserDefinedFunctionType.Inline,
ImplementationType = ImplementationType.TransactSql,
TextBody = String.Format("RETURN SELECT * FROM [{0}].[{1}]", view.Schema, view.Name)
};
inlineTsqlFn.Create();
helper.AddCleanup(inlineTsqlFn);
// TODO: Create table valued function
// TODO: Create Clr scalar func
// TODO: Create Clr inline func (Exists?)
// TODO: Create Clr table valued func
// TODO: Create Clr Aggregate
var proc = new StoredProcedure(helper.GetDatabase(), "sproc1", schema.Name)
{
TextMode = false,
AnsiNullsStatus = false,
QuotedIdentifierStatus = false,
TextBody = String.Format("SELECT * FROM [{0}].[{1}]()", inlineTsqlFn.Schema, inlineTsqlFn.Name)
};
proc.Create();
helper.AddCleanup(proc);
// TODO: Create Clr Sproc
// TODO: Create Constraint
// TODO: Create Queue
// TODO: Create Statistic
// TODO: Create Synonym
var user = helper.GetUser();
var permissable = new IObjectPermission[]
{
table,
view,
scalarTsqlFn,
inlineTsqlFn,
proc,
};
permissable.Do(tg => tg.GrantAll(user.Name));
permissable.Do(tg => tg.DenyAll(user.Name));
permissable.Do(tg => tg.RevokeAll(user.Name));
// change all owners
table.Owner = user.Name;
table.Alter();
view.Owner = user.Name;
view.Alter();
scalarTsqlFn.Owner = user.Name;
scalarTsqlFn.Alter();
inlineTsqlFn.Owner = user.Name;
inlineTsqlFn.Alter();
proc.Owner = user.Name;
proc.Alter();
}
finally
{
helper.Cleanup();
}
}
public string Format(UserDefinedFunction udf)
{
var server = udf.CreateWrapper();
var values = new Dictionary<string, object>(StringComparer.InvariantCultureIgnoreCase)
{
{ Const.Catalog, udf.Parent.Name },
{ Const.Schema, udf.Schema },
{ Const.Name, udf.Name },
{ Const.NameEscaped, udf.Name.EscapeName() },
{ Const.Body, udf.TextBody },
{ Const.Header, udf.ScriptHeader(true) },
{ Const.Needs, GetNeeds(server, udf, DatabaseObjectTypes.StoredProcedure | DatabaseObjectTypes.UserDefinedFunction | DatabaseObjectTypes.View) ?? string.Empty }
};
switch (udf.FunctionType)
{
case UserDefinedFunctionType.Inline:
values[Const.FuncType] = Const.FuncInline;
break;
case UserDefinedFunctionType.Table:
values[Const.FuncType] = Const.FuncMulti;
break;
default:
values[Const.FuncType] = Const.FuncScalar;
break;
}
return Const.Script.UserDefinedFunction.Format().With(values);
}
public static string SimpleStructMemberTest()
{
using (HlslProgram program = new HlslProgram())
{
Type vsData = CreateVSType(program);
UserDefinedFunction udf = new UserDefinedFunction("vs_main");
Value argValue = udf.AddArgument(vsData);
DeclExpr output = new DeclExpr(vsData);
udf.AddExpr(output);
udf.AddExpr(new AssignmentExpr(
new StructMemberExpr(output.Value, "position").Value,
new StructMemberExpr(argValue, "position").Value));
StructMemberExpr[] otherMembers = new StructMemberExpr[] {
new StructMemberExpr(output.Value, 1),
new StructMemberExpr(output.Value, 2),
new StructMemberExpr(output.Value, 3),
new StructMemberExpr(output.Value, 4),
};
foreach (StructMemberExpr SME in otherMembers)
udf.AddExpr(new AssignmentExpr(SME.Value, new LiteralExpr(SME.Value.ValueType).Value));
udf.AddExpr(new ReturnExpr(output));
program.AddFunction(udf);
return program.EmitRawShaderCode();
}
}
private static async Task RunUDF(string colSelfLink)
{
// 1. Create UDF.
var udfFileName = @"JS\Tax.js";
var udfId = Path.GetFileNameWithoutExtension(udfFileName);
var udf = new UserDefinedFunction
{
Id = udfId,
Body = File.ReadAllText(udfFileName),
};
await TryDeleteUDF(colSelfLink, udf.Id);
await client.CreateUserDefinedFunctionAsync(colSelfLink, udf);
// 2. Create a few documents.
await client.CreateDocumentAsync(colSelfLink, new
{
type = "Company",
name = "Zucker",
headquarters = "Germany",
locations = new []
{
new {country = "Germany", city = "Berlin"},
new {country = "Russia", city = "Novosibirsk"}
},
income = 50000
});
await client.CreateDocumentAsync(colSelfLink, new
{
type = "Company",
name = "Estel",
headquarters = "Russia",
locations = new[]
{
new {country = "Russia", city = "Novosibirsk"},
new {country = "Germany", city = "Berlin"}
},
income = 70000
});
await client.CreateDocumentAsync(colSelfLink, new
{
type = "Company",
name = "Pyramid",
headquarters = "USA",
locations = new[]
{
new {country = "USA", city = "Seattle"}
},
income = 100000
});
// 3. Execute a query against UDF: use UDF as part of the SELECT clause.
var results = client.CreateDocumentQuery<dynamic>(colSelfLink, string.Format("SELECT r.name AS company, Tax(r) AS tax FROM root r WHERE r.type='Company'", udfId));
// 4. Prints the results.
Console.WriteLine("Tax per company:");
foreach (var result in results)
{
Console.WriteLine("{0}", result);
}
}
public static string SimpleFunctionCallTest()
{
using (HlslProgram program = new HlslProgram())
{
Type vsData = CreateVSType(program);
Type f1 = TypeRegistry.GetFloatType();
Type f4 = TypeRegistry.GetVectorType(f1, 4);
DeclExpr wvpMatrixDecl = new DeclExpr(TypeRegistry.GetMatrixType(f4, 4), "WorldViewProjection");
program.AddGlobal(wvpMatrixDecl);
UserDefinedFunction udf = new UserDefinedFunction("vs_main");
Value argValue = udf.AddArgument(vsData);
DeclExpr output = new DeclExpr(vsData);
udf.AddExpr(output);
// Initialize the position element -- multiply input position by WVP matrix.
Function fn = program.GetFunctionByName("mul");
CallExpr wvpMul = new CallExpr(fn, new Expr[] { new StructMemberExpr(argValue, "position"), wvpMatrixDecl });
udf.AddExpr(new AssignmentExpr(new StructMemberExpr(output.Value, "position").Value, wvpMul.Value));
// Initialize the rest of the struct to zero.
StructMemberExpr[] otherMembers = new StructMemberExpr[] {
new StructMemberExpr(output.Value, 1),
new StructMemberExpr(output.Value, 2),
new StructMemberExpr(output.Value, 3),
new StructMemberExpr(output.Value, 4),
};
udf.AddExpr(new CommentExpr("Ensuring that a valid comment is being emitted."));
udf.AddExpr(new CommentExpr("Even one that consists of multiple lines."));
udf.AddExpr(new CommentExpr(string.Format("Or embedded newlines.{0}Like this!", Environment.NewLine)));
udf.AddExpr(new CommentExpr("Or this.\n(not a proper newline.)"));
foreach (StructMemberExpr SME in otherMembers)
udf.AddExpr(new AssignmentExpr(SME.Value, new LiteralExpr(SME.Value.ValueType).Value));
udf.AddExpr(new ReturnExpr(output));
program.AddFunction(udf);
return program.EmitRawShaderCode();
}
}
/// <summary>
/// 注册全局的自定义函数
/// </summary>
/// <param name="functionName">函数名称</param>
/// <param name="function">函数</param>
public void RegisterGlobalFunction(string functionName, UserDefinedFunction function)
{
if (string.IsNullOrEmpty(functionName)) throw new ArgumentNullException("functionName");
if (function == null) throw new ArgumentNullException("function");
TagContainer.UserDefinedFunctions.Add(functionName, function);
foreach (Template child in TagContainer.ChildTemplates)
{
child.RegisterGlobalFunction(functionName, function);
}
}
/// <summary>
/// 注册全局的自定义函数
/// </summary>
/// <param name="function">函数</param>
public void RegisterGlobalFunction(UserDefinedFunction function)
{
this.RegisterGlobalFunction(function.Method.Name, function);
}