Jump to content

Custom Databases


Marcus Goluch

Recommended Posts

Hi,

 

I am going to ask a few questions regarding widget development:

  1. what DB software do you use (mogo, mysql, plaintext)?
  2. what npm module is used to manage this database?
  3. with out using any thing like frog forms, frog data viewer, csv files, frog documents of any kind. Could I please get access to give my widgets an actual database table?
  4. Can we get the ability to read console print outs from the server side code execution of widgets?
  5. Can we please get some form of API access for the server side part of EJS templates, mainly for use with databases.

 

Essentially as somebody who has limited experience developing web apps, I find that the current widget system of having to hack in things from locally hosted files, or using the client to connect to a Document based database hosted on 365 is too janky. Client side access for rendering inside an EJS template is exactly what I want but I don't have the tools available to do the really cool stuff I want to do.

 

In a nut shell you can't watch Doctor Who on BBC one if you have a Television, but no TV channels. EJS is our television but right now we have no decent channels to watch.

 

I know, I make a post about this every year but, Frog Code seriously needs to start being worked on again, right now we can not even delete our demo widget projects. I think Frog Code needs a road map and a clear plan, or it needs to be removed if you have no plans on finishing this feature.

 

Sorry to sound like a brat, but I feel this is a seriously huge missed opportunity.

 

Thank you

Link to comment
Share on other sites

Hi @Chris Goluch,

I've asked one of our developers to respond to your more technical questions, but I can answer the product roadmap-type ones.

Frog's front-end was designed around security.  No front-end tools have direct access to the databases - all access is through API requests.  This makes Frog very secure, for instance, you cannot send a "Delete Tables" query.  But it does mean that any data you wish to retrieve needs to have an API you can use to get it first.

Connecting to a 3rd-party database is awkward, because you need somewhere to store the secret keys, which is not possible without a major reworking of customer access to the data.  This was actually looked into, but the work involved was extensive and there just hasn't been that many schools asking for it.  It's a sad fact that there's not many @Chris Goluch in our schools.  We have to focus on features the majority will make use of.  Adding a database to FrogCode is a feature I would like to see, but we already have the Forms for secure data and the Data-Store.  both of these tools are accessible to FrogCode.  I'd be happy to share with you what I know about how to access and write to them.

We made the decision at the start of the year to look again at FrogCode, but then the world changed and priorities rightly shifted.  We do support it and maintain it, but the main focus is on stability.  I recommended leaving the Beta tag on the title for this reason.  It's a work-in-progress, just like the widgets it makes.

  • Thanks 1
Link to comment
Share on other sites

Hi Chris, I'm a senior developer at Frog. Graham asked me to take a look at your questions.

The core of Frog Learn uses a MySQL database behind a custom API; we keep this locked up to internal development to ensure security and integrity of the data, so it's not available for use in Frog Code development.

We have a datastore API available to widget developers that allows you to store and work with server side data specific to your widgets, you can see the documentation for this at the following address on your Frog Learn instance: https://frog.broadway-academy.co.uk/app/docs/. This document is currently missing documentation for the the "create" endpoint, which I've outlined below:

POST to frog.broadway-academy.co.uk/api/fdp/2/datastore/create

Provide the following parameters; note these are similar to the "update" params.

target_uuid String The uuid against which you wish to associate the data (e.g. for a widget this.options.content_uuid)
data String The data you want to store
user_uuid String The uuid of the user that this data is associated with (defaults to current user if this param is not passed)
alias String

The alias for this data, this can be anything you want, and is to help you differentiate the data. For example: 'student.locker_number, student.random_data' max (255 chars)

Link to comment
Share on other sites

Hi Simon,

 

I thank you for your time to explain this.

The idea of using a database and not the frog data store would allow for more complex interactions between other on site facilities.

 

Unfortunately the data store will not do the things I am looking into.

 

I think I might have something I wrote in node over the past few years that might be of interest. It is an SQL wrapper that turns databases into node js class objects and allows you to send full query's in json to the server from the client.

 

I would be happy to discuss this further if you are interested.

 

 

Link to comment
Share on other sites

@simon brahan

 

Hi Simon, I think i have had a bit of a mad idea.

After noticing frog supports oAuth1.0 (can we get 2.0?) with a back end api.

Could I read from / write to, the datastore store from say a node js application.

I could do, some extremely interesting things if the answer is yes.

 

Secondly, @Graham Quince I would be very interested to take a second look at your project, because i may have a use for it.

 

Thank you both, for taking time.

Edited by Chris Goluch
Link to comment
Share on other sites

Yes, Chris, you can use OAuth (either flavour) to communicate with FrogOS. We can add a client of either type to your Frog Learn instance through a support ticket; the client is sensitive so we can't do it through this forum. Once you have that you can create your own web app in any way you like and have it communicate with Frog Learn using the publicly available APIs listed in the documentation I provided above.

Thanks for the suggestion of an SQL wrapper. We use something similar internally; however is we were to make the interface available to third parties we wouldn't be able to ensure the security, integrity or performance of our back end.

Link to comment
Share on other sites

Morning @simon brahan

So looking into this further, can i use the API with out using oAuth.

Say, I wanted to dump a datastore value for every student into a csv from a node js script at 12am every morning. So that another service can consume that csv and do what ever with it.

Otherwise to use oAuth means the user would need to log in first though a web client, its not that much of a problem just extra work.

But yes, I would love to have a play with oAuth if we can set that up. Are there any limits to the API and what impact would its use have on the frog box if any impact at all.

 

In regards to the SQL access, I would simply use a separate sql server. Using the one on the frog box would not be something that would work well long term for 3rd party databases.

 

 

Link to comment
Share on other sites

You need to authenticate as a FrogOS Learn user to ensure your application isn't given access to permissions it shouldn't have. You should be able to log in as an admin user and then use most of Frog Learn's APIs, including the datastore ones.

There are some limits to how many API calls you make in a certain amount of time; you're unlikely to hit these as they're mainly to protect against denial of service or bot attacks which will send many requests very quickly. If you do hit them we can discuss changing the limits for your instance, or providing an API that will let you do the work in fewer calls.

Link to comment
Share on other sites

Thank you again Simon.

 

I have now confused your support team with this request, but I have faith in them ?

 

I think I will let you in on my plans, maybe you will have input on this project idea.

My plan is to create a new server running a Bio-Store extractor and a my little node js webservice.

Every hour, the Bio-Store extractor will pipe out a csv of Bio-Store balances, this csv is then ingested by my node app. The API is then used to set a value in the data store for each user. That value can then be displayed later using a custom widget. ?

I often export all the student data from frog and use a node to perform csv magic for user account management tasks. I think generally this could be come some sort of tool for scheduling custom imports and exports from the frog data store.

Any suggestions, ideas, concerns, criticisms welcome.

Link to comment
Share on other sites

We have several third party integrations of varying complexity all working well with frog. A few examples:

1. Current printing balance pulled from our printer server. Runs on demand. Uses frog data store as a fall back in case print server becomes unavailable.

2. Who is absent today? Update for the frog seating plan just loaded pulled live from the sims server.

3. Haven’t done your register? An annoying but can’t fail to ignore frog notification if you haven’t done your register 20 minutes into each lesson. Again pulled live from sims server.

4. Attendance letter requests. One or more of 5 different letters can be requested for one or more students via a custom attendance portal in frog. The letter mail merge file is created in frog but as there is limited address/parental information in frog it queries sims on the fly to add the missing information before invoking word to start the merge.


My main piece of advice: for data going into frog consider using JSON format rather than csv. JavaScript natively understands this format. You can use a .js file as a container for this to defeat any cross origin issues.

Good luck with your idea.

  • Like 1
  • Thanks 1
Link to comment
Share on other sites

  • 2 weeks later...

I have just been advised by the support team, that getting oAuth access is not possible. This is not because it does not work, but because there is no documentation. Despite the fact documentation is available on the frog box. The only thing thing I can see missing in that documentation is the API request headers for bearer and clientID. Though not much documentation would be required for this API since oAuth is already heavily documented standardised protocol and this form of API is always very similar.

@simon brahancould you please clarify.

 

Link to comment
Share on other sites

On 21/11/2020 at 10:33, pconkie said:

We have several third party integrations of varying complexity all working well with frog. A few examples:

1. Current printing balance pulled from our printer server. Runs on demand. Uses frog data store as a fall back in case print server becomes unavailable.

2. Who is absent today? Update for the frog seating plan just loaded pulled live from the sims server.

3. Haven’t done your register? An annoying but can’t fail to ignore frog notification if you haven’t done your register 20 minutes into each lesson. Again pulled live from sims server.

4. Attendance letter requests. One or more of 5 different letters can be requested for one or more students via a custom attendance portal in frog. The letter mail merge file is created in frog but as there is limited address/parental information in frog it queries sims on the fly to add the missing information before invoking word to start the merge.


My main piece of advice: for data going into frog consider using JSON format rather than csv. JavaScript natively understands this format. You can use a .js file as a container for this to defeat any cross origin issues.

Good luck with your idea.

So when are we going to get our hands on these Conkie Specials?? ?

@pconkie

@Graham Quince

  • Haha 1
Link to comment
Share on other sites

Unfortunately not as simple as installing the widget in frog. There is another server to contend with, whether this is the SIMS server or something else. So lots of additional and sometime quite complicated set-up on the third party. Maybe one day I will have enough time to write some instructions!!

  • Like 1
  • Thanks 1
Link to comment
Share on other sites

On 08/12/2020 at 13:35, pconkie said:

Unfortunately not as simple as installing the widget in frog. There is another server to contend with, whether this is the SIMS server or something else. So lots of additional and sometime quite complicated set-up on the third party. Maybe one day I will have enough time to write some instructions!!

You telling me you’ve got lots on at the moment???  I couldn’t possible think what you're  spending all your time doing!! ?

Link to comment
Share on other sites

  • 3 months later...

So, I have finally found time to sit down and start playing with this.

I now have oath working so I can sign in and obtain a token and secret from my log in.

Anyway I am stuck trying to figure out how to authorize requests on the api.

Just keep getting 401 No auth type given

I have all the correct oauth 1.0 headers, any suggestions?

 

{
  "status": "error",
  "response": {
    "message": "No auth type given",
    "code": "auth-failed",
    "http": 401
  },
  "request": {
    "url": "/auth/get",
    "method": "get",
    "headers": {
      "host": "<REDACTED>",
      "authorization": "OAuth oauth_consumer_key=\"<REDACTED>\",oauth_nonce=\"<REDACTED>\",oauth_signature_method=\"HMAC-SHA1\",oauth_timestamp=\"1616357044\",oauth_token=\"<REDACTED>\",oauth_version=\"1.0\",oauth_signature=\"<REDACTED>\"",
      "connection": "close"
    },
    "params": []
  }
}

 

Maybe @Graham Quincecould get one of the devs to check out it works back end?

Link to comment
Share on other sites

13 hours ago, Marcus Goluch said:

So, I have finally found time to sit down and start playing with this.

I now have oath working so I can sign in and obtain a token and secret from my log in.

Anyway I am stuck trying to figure out how to authorize requests on the api.

Just keep getting 401 No auth type given

I have all the correct oauth 1.0 headers, any suggestions?

 


{
  "status": "error",
  "response": {
    "message": "No auth type given",
    "code": "auth-failed",
    "http": 401
  },
  "request": {
    "url": "/auth/get",
    "method": "get",
    "headers": {
      "host": "<REDACTED>",
      "authorization": "OAuth oauth_consumer_key=\"<REDACTED>\",oauth_nonce=\"<REDACTED>\",oauth_signature_method=\"HMAC-SHA1\",oauth_timestamp=\"1616357044\",oauth_token=\"<REDACTED>\",oauth_version=\"1.0\",oauth_signature=\"<REDACTED>\"",
      "connection": "close"
    },
    "params": []
  }
}

 

Maybe @Graham Quincecould get one of the devs to check out it works back end?

Hi Marcus,

We're aware of an issue with the documentation, but adding 

X-AuthType: oauth_1_0_a

to the header will let Frog know which of the multiple authentication types to use.

 

 

Link to comment
Share on other sites

@Graham Quince This one I think will need a dev to look at.

Can i use /api/fdp/2/user/get to get a students enrolment number. At the moment it does not seem the API provides that.

I am working on a user end extractor/importer for frog. This would allow server admins to import data from services like Bio-Store into our custom widgets.

Link to comment
Share on other sites

On 23/03/2021 at 00:55, Marcus Goluch said:

Its working.....

I now have ULIMITED POWA! ?

 

Now my only question is how far should I got with this application?

Thanks for the help Graham.

 

What are balances?

& Would this be a better way to do Catering  Balance instead of frog data store?

Hi @Marcus Goluch,

Sorry, I've not heard of balances.  Can you post a screenshot or let me know where they are referenced?

To get user info, I tend to use the API: users.getDataInCategory with the params:

  • user_uuid 
  • uuid (where the category uuid is the section of the Users app - annoyingly this uuid is different per school)

 

Link to comment
Share on other sites

Ah right.  I'll ask, but the only thing with Credits that I know of is the FrogPlay credits.  So it's either that or some never released functionality.

I don't believe there is an external version of the category API.  If you can't call the existing one after authenticating in, then I think you'll need to put it on the Ideas Portal. 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...