Custom Databases

[Chris Goluch 10]

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

[Graham Quince 622]

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.

[Chris Goluch 10]

Thanks for your response Graham.

I would love to get some technical details that is for sure.

In regards to the pandemic putting Frog code on the back burner again, I can understand that.

I would still love to see Frog Code get itself out of beta.

[simon brahan 4]

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)

[Chris Goluch 10]

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.

 

 

[Chris Goluch 10]

@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 November 18, 2020 by Chris Goluch

[simon brahan 4]

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.

[Chris Goluch 10]

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.

 

 

[simon brahan 4]

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.

[Chris Goluch 10]

Excellent.

Is it just a support ticket to turn this on?

[simon brahan 4]

Yes, that's right.

[Chris Goluch 10]

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.

[pconkie 637]

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.

[Chris Goluch 10]

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.

 

[ADT 1,273]

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

[pconkie 637]

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!!

[ADT 1,273]

5 hours ago, 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 your email  spending all your time doing!! 😜