microsoft / vscode-powerquery-sdk

Power Query Connector Development SDK for VS Code
MIT License
72 stars 12 forks source link

Function errors at compiling and variable scoping runtime #326

Open fepatrifork opened 6 months ago

fepatrifork commented 6 months ago

Preflight Checklist

Power Query SDK

0.4.0

Regression From

No response

Platform

Windows

Architecture

x64

OS Version

Windows 11

VSCode version

1.88.1

PQSdkTool Path

No response

Bug Description

  1. I'm developing , building and deploy a connector for OpenSearch in PowerBi. When I try to access to the information of a JSON file with file = File.Contents(<path>), it gives me an error:

    Microsoft.Mashup.Engine.Interface.UnpermittedResourceAccessException

The command is tested as inline command from another user Help-request and so, without SDK, work.

  1. Also, a global variable (declared outside any other object or functions) is read-accessible from other objects or function but not writable even though the IDE mark the variable as [section-member] and assigning a valid value.

Steps to Reproduce

1.Launch Visual Studio Code 2. Compile the project 3. Transfer the .mez file to to proper PowerBI folder 4. Run PowerBI and test

Actual Experience

  1. It does not complete the compiling.
  2. Runtime byzantine error as the variable is not modified.

Expected Experience

  1. Compile.
  2. The value of the variable should change with a new value assigned.

Additional Context

No response

bgribaudo commented 6 months ago

Hi @fepatrifork!

Custom connector code is only allowed to invoke a small subset of Power Query's data source functions: https://learn.microsoft.com/en-us/power-query/handling-data-access#data-source-functions

File.Contents is not one of those functions, so isn't allowed to be used from inside a connector.

Idea: What if you built a function, which you package in your .mez file, that accepts a binary value as its input then processes it appropriately? So, a consumer using this function would write something like MyPqExtension.SomeFunction(File.Contents(<path>)). This would keep the call to File.Contents out of the connector while still allowing you to share the custom binary processing logic via the .mez file.

Regarding modifying variables: In M, identifier values are immutable, so the fact that you can't change the variable's value is expected. (M is drawn from a functional programming background, where immutability is more common, as compared to a procedural/OO language.)

Hope this helps!

mattmasson commented 6 months ago

Correct - we don't allow File/Folder access from custom connectors.

fepatrifork commented 6 months ago

Thanks for the explanation and info! I suspecting of that because also is not allow multiple context, but regarding the idea, probably I understood what I have to do, but not sure :). If you are interested I would like to explain why I need one of those, maybe can raise some new discussions or just my ignorance: I'm implementing a Oauth authentication in a connector that is does not have. To initialize the flow i require to input the IDP endpoint the user and the user-secret. The IDP endpoint is often static, but user and the user-secret maybe not.
So one solution is with a 1. config file to load or a 2. dynamic input as parameter. For the solution # 2 there will be also many solutions: 1. Global variable 2. (I prefer that) A prexistent GUI that pass the info to initialize the "Security GUI" ----> I have a solution for that but I do not how to access the data to that (I cannot transform a Function type to Text):

[DataSource.Kind = "Project", Publish = "Project.Publish"]
shared Project.Contents = [authBackend= Value.ReplaceType(ProjectImpl, ProjectType), authSecGUI= Value.ReplaceType(ProjectImplSecGUI, ProjectType)];

// Wrapper function to provide additional UI customization.
ProjectType = type function (
    Server as (
        type text meta [
            Documentation.FieldCaption = "Server",
            Documentation.FieldDescription = "The hostname of the server.",
            Documentation.SampleValues = {"localhost"}
        ]
    ),
    Port as (
        type number meta [
            Documentation.FieldCaption = "Port",
            Documentation.FieldDescription = "Port server listens on.",
            Documentation.SampleValues = {9200}
        ]
    ),
    UseSSL as (
        type logical meta [
            Documentation.FieldCaption = "Use SSL",
            Documentation.FieldDescription = "Use SSL",
            Documentation.AllowedValues = {true, false}
        ]
    ),
    HostnameVerification as (
        type logical meta [
            Documentation.FieldCaption = "Certificate validation",
            Documentation.FieldDescription = "Certificate validation",
            Documentation.AllowedValues = {true, false}
        ]
    ),
    IdPurl as (
        type text meta [
            Documentation.FieldCaption = "Uri IdP",
            Documentation.FieldDescription = "The endpoint of the Identity Provider",
            Documentation.SampleValues = {"http://xxxxxxx or https://xxxxxxx"}
        ]
    ),
    ClientId as (
        type text meta [
            Documentation.FieldCaption = "Client ID",
            Documentation.FieldDescription = "The identifier of the user",
            Documentation.SampleValues = {"Username"}
        ]
    ),
    ClientSecret as (
        type text meta [
            Documentation.FieldCaption = "Client secret",
            Documentation.FieldDescription = "The secret of the user",
            Documentation.SampleValues = {"Client-secret"}
        ]
    )
) as table meta [
    Documentation.Name = " Project"
];

ProjectImplSecGUI = (Server as text, Port as number, UseSSL as logical, HostnameVerification as logical, IdPurl as text, ClientId as text, ClientSecret as text) as record =>
    let
        config = [IdPurl = IdPurl, ClientId = ClientId, ClientSecret = ClientSecret]
    in
        config;

ProjectImpl = (Server as text, Port as number, UseSSL as logical, HostnameVerification as logical, IDPurl as text, ClientId as text, ClientSecret as text) as table =>
    let 
         ....................................................... 

// Data Source Kind description
    Project = [
        let
           // I need a ref to have config info from ProjectImplSecGUI here! 
        in
          // output as records of config info ,
        ...................................... 

Please, can you help me for this? I require to pass informations of the IdP to Project from a config file or a GUI. How different people can set up a IdP provider endpoint and credentials otherwise? It's ok to not allow something, but a valid alternative is necessary. Is impractical to give a pre-compiled .mez file for different type of users and different person.

fepatrifork commented 6 months ago

Correct - we don't allow File/Folder access from custom connectors.

Unfortunately correct, a bad news for me. I also try to define into File/Folder access into a different shared section but is not allow to have two sections?

bgribaudo commented 6 months ago

Hmm...you might have to go with something outside the box, like using an Azure Function to proxy between your connector and the upstream service?

fepatrifork commented 6 months ago

@bgribaudo thanks for the reply, I will explain better, my bad, I was not so clear... The main goal is to connect to a OpenSearch DB through Oauth using a PowerBI OpenSearch connector and all good so far. The Oauth implement the auth code flow type of authentication. Before starting it requires the endpoint of the IdP, and then the _clientid and the _idsecret. Actually I'm in the condition to create this connector with this values statically hardcoded when the .mez file is created. In turns I have one connector for one user, but a realistic use in production is to have the capability to insert dinamically these values. In the code are inserted in a "General GUI" that appears before the standard "Security GUI" here: ......... IdPurl as ( type text meta [ Documentation.FieldCaption = "Uri IdP", Documentation.FieldDescription = "The endpoint of the Identity Provider", Documentation.SampleValues = {"http://xxxxxxx or https://xxxxxxx"} ] ), ClientId as ( type text meta [ Documentation.FieldCaption = "Client ID", Documentation.FieldDescription = "The identifier of the user", Documentation.SampleValues = {"Username"} ] ), ClientSecret as ( type text meta [ Documentation.FieldCaption = "Client secret", Documentation.FieldDescription = "The secret of the user", Documentation.SampleValues = {"Client-secret"} ] ) )

The "Security GUI" is provided by PowerQuery and is not possible to modify granularly, is defined for example in this way with only one type of authentication available (but can be multiple): Authentication = [ OAuth = [ StartLogin = StartLogin, FinishLogin = FinishLogin, Refresh = Refresh, Logout = Logout ] ]