Blog
All Blog Posts | Next Post | Previous Post
TMS WEB Core and More with Andrew:
TMS XData: An Extended Example Part 3 of 3
Thursday, November 3, 2022
If you'd like to access the survey directly, rather than using the <iframe> embedded in this post, please use this link. If you encounter anything noteworthy or otherwise problematic at any point during the survey, please submit feedback using the icon in the top-right corner. Note that the survey should work equally well on a mobile device.
Part 1 of 3
- Introduction
- Bargain: Three Apps For The Price of One!
- Survey Client Initial Layout
- Survey Server Initial Setup
- Swagger
- First Contact - Getting Data from XData
- Survey Admin Client Initial Setup
- Serviceability - Getting Logged In
- More Service Endpoints
- Survey Admin Client: Login
- Survey Admin Client: Managing Surveys
- Connecting SQL to Tabulator
- Global Save and Cancel
- Editing HTML
- QR Codes
- Custom Dropdown
- Survey Client Questions
- Saving Responses
- Storing Data - JSON versus Structure
- Displaying Responses
- To-Do List
- Roadblocks Overcome
- All Done!
Survey Client Questions.
Back at the Survey Client. Here we need to take the JSON from a survey and figure out what to do with it. Really, all we're doing is displaying the HTML that was created in the Survey Admin Client. If it has elements like <input> or <textarea>, that's great, but the question-handling component actually doesn't care about any of that. It just displays questions and uses the JSON it has to figure out what the next question and previous question might be. It looks at the question type to determine whether the "next" and "previous" buttons should be enabled or disabled, and that's about it.
When the "next" button is clicked, things start to get a little more interesting. This is where it records whatever has
been selected on the page, and then displays the next question or previous question. This is then recorded as
an object in a set of "responses" JSON that is uploaded back to the Survey Server after each question. This
continues until there are no more questions. As a first draft, this works pretty well. Maybe more server
communication than we'd like, but it works plenty fast enough and it also means we get partial survey data
collected. Nothing particularly complicated at all.
But wait. What about the "whatever has been selected on the page" part? Seems kind of important. And it is. Basically, when the "question page" is created (when the survey starts), a JavaScript event handler is added to let us know if anything in the page changes (addEventListener). It is set up to trigger any time someone edits a bit of text or clicks a button anywhere on that part of the page.
When that event triggers, we then go and
figure out what has been filled in, without knowing in advance anything about the page contents. We combine
whatever we find into a string and that becomes the "response" to the question. Looks more complicated than
that, but that's all it is really doing. Here's where we set up the event handler - when the survey first
starts.
// Basics of a question CurrentQuestionIndex := 0; CurrentQuestion := TJSObject(SurveyQuestions[CurrentQuestionIndex]); CurrentQuestionID := String(CurrentQuestion['question_id']); CurrentQuestionName := String(CurrentQuestion['question_name']); DisplayQuestion; // Elements may have different triggers. We want to catch them all // We'll deal with multiple firing for the same event later asm divMiddle.addEventListener('change', pas.Unit1.Form1.HandleInput); divMiddle.addEventListener('input', pas.Unit1.Form1.HandleInput); end;
And here's how we handle all the first few question types.
procedure TForm1.HandleInput; var selectioncount: integer; selectionvalue: string; begin // This is invoked from a Javascript Event, so it has forgotten that we're on Form1 // so it seems we need to reference it explicitly. selectioncount := 0; // Pick One if (Form1.CurrentQuestionType = 9) then begin asm var choices = divMiddle.getElementsByTagName('input'); for( var i = 0; i < choices.length; i++){ if (choices[i].checked) { selectioncount += 1; selectionvalue += choices[i].value+' '; } } end; // With this type, all we're looking for is one selection. // As this is a radio button, none are selected by default, but // once selected, not possible to unselect it. if selectioncount = 1 then begin Form1.CurrentResponse := Trim(selectionvalue); Form1.btnNext.Enabled := True; end; end // Text Multi else if Form1.CurrentQuestionType = 7 then begin asm var choices = divMiddle.getElementsByTagName('textarea'); for( var i = 0; i < choices.length; i++){ selectionvalue += choices[i].value+' '; } end; // With this type, all we're looking for is a block of text. Form1.CurrentResponse := Trim(selectionvalue); end // Text Single else if Form1.CurrentQuestionType = 8 then begin asm var choices = divMiddle.getElementsByTagName('input'); for( var i = 0; i < choices.length; i++){ selectionvalue += choices[i].value+' '; } end; // With this type, all we're looking for is a line of text. Form1.CurrentResponse := Trim(selectionvalue); end // Pick Many else if (Form1.CurrentQuestionType = 11) then begin asm var choices = divMiddle.getElementsByTagName('input'); for( var i = 0; i < choices.length; i++){ if (choices[i].checked) { if (selectioncount > 0) { selectionvalue += ','+'"'+choices[i].value+'"'; } else { selectionvalue = '"'+choices[i].value+'"'; } selectioncount += 1; } } end; // Might be a series of checkboxes, so string them all together Form1.CurrentResponse := '['+selectionvalue+']'; Form1.btnNext.Enabled := True; end; end;
Of particular interest here, this method is invoked from a JavaScript event handler. So it is not aware that
it is running inside of an existing Delphi Form. To get around this, the "Form1." prefix is added to form variables
where it might not normally be expected. But the gist of what is going on is that we're using the question type
to determine what elements we should be looking for and adding the values from those elements to the "Current
Response". So if you were to have a "Pick One" question that happened to also have an <input> field, it
would be ignored.
Other question types might include more variations in elements. This would just involve adding more sections to this method to account for them. We could also be less generic by using IDs or other classes to components if there were any concerns about ambiguity. For example, if you had two <input> fields, say first and last name, classes could be added via the Survey Admin Client (and documented in the description of the new Question type) and then referenced here to pull out the correct values. No end to the possibilities here.
But why the two types of events and therefore two addEventListener calls? Well, different HTML elements generate different events. For example, typing into a <textarea> might trigger a "change" event, whereas a radio button click might generate an "input" event. We honestly don't care which, we just want to know something has changed.
It might mean that events are fired more often (they are) but it doesn't particularly matter much as
nothing happens beyond this until someone clicks the "next" button. This way, we don't need to use separate handlers, and we don't have to think too much about different browsers either - one of these is likely
to be triggered no matter the type of input presented in the HTML for a particular question.
Saving Responses.
After each question, the response is saved, and the collection of responses is sent back to the Survey Server. There's a bit of effort needed to check that the response has changed, but not much else. There are other things
that could/should be done here, such as saving the responses to localStorage or perhaps only uploading the
responses after a certain number of questions. But we're not (yet!) serving up millions of surveys, so we can
defer that for the moment. Of more interest though, is that we're sending back all the responses as a single
JSON object. This will be a problem we have to address shortly, but it simplifies things here. Here's what
we're doing.
procedure TForm1.SaveResponses(QuestionID: String; QuestionName: String; ThisResponse: String); var Response: TXDataClientResponse; ClientConn: TXDataWebClient; Blob: JSValue; Data: JSValue; Elapsed: TDateTime; begin Elapsed := Now; LogActivity('Saving Responses.'); SurveyResponses['SurveyID'] := SurveyID; SurveyResponses['ClientID'] := ClientID; SurveyResponses[Trim(QuestionID)+':'+Trim(QuestionName)] := ThisResponse; if TJSJSON.stringify(SurveyResponses) <> LastTransmission then begin LastTransmission := TJSJSON.stringify(SurveyResponses); try if (ServerConn.Connected) then begin try ClientConn := TXDataWebClient.Create(nil); ClientConn.Connection := ServerConn; Response := await(ClientConn.RawInvokeAsync('ISurveyClientService.SaveResponses',[ SurveyID, ClientID, TJSJSON.stringify(SurveyResponses) ])); Blob := Response.Result; Data := Blob; asm Data = await Blob.text(); end; Form1.LogActivity(String(Data)); except on E: Exception do begin Form1.LogActivity('SaveResponses Error: ['+E.ClassName+'] '+E.Message); console.log('SaveResponses Error: ['+E.ClassName+'] '+E.Message); end; end; end; finally end; LogActivity('Responses Saved. '+IntToStr(MillisecondsBetween(Now, Elapsed))+'ms'); end else begin LogActivity('Responses Skipped. '+IntToStr(MillisecondsBetween(Now, Elapsed))+'ms'); end; end;
This isn't much different than what we've done before. The JSON that is sent just includes the responses. It
should probably be a bit smarter. If an answer to a text question is provided, and the user then goes back and removes the text,
it will not pick up on that. It also is going to be a problem to figure out what prior responses are when it
comes to updating the UI. For example, we just pick out a value that might be a radio button value, a
checkbox value, or a text input value. We'll have to do the reverse to "put back" that value if someone hits the
"previous" button. This is currently not implemented.
But, this is the end of the line for the Survey Client. It is doing what is expected of it - displaying
questions and sending responses back to the Survey Server. All that's left then is the ability to look at the
responses.
Storing Data - JSON versus Structure.
So far, we've not spent much time considering what is stored in the database. We have defined fields for some things and generic text blobs for others. Might all seem kind of a mess. The choice about what level of structure is needed for any particular bit of data largely rests upon what kind of access to it is expected, particularly when it comes to SQL and selecting data.
Take the questions for example. We absolutely care that
the questions contain things like the type, and what to use for the title and footer, all solid candidates for fields in the database tables. But the Survey Server doesn't care about that, just the two clients. And they can parse
the JSON just fine. So from the Survey Server's point of view, the question data can be opaque, with just
enough data outside of the JSON to find the questions that belong to a particular survey. The table fields are then more like the addressing on the outside of a box. We need that data to find and route the box, but we don't care at the Survey Server what is actually in the box. The same applies to
the responses.
For other data, like the change history or the notes or the server event logs, we're potentially going to be interested in running queries against that data separately (well, the event logs certainly) so having more fields defined in the table is of more value to the Survey Server. For example, selecting a date range for the record. No fun writing SQL that has to first parse JSON. This is the general guiding principle in how these tables have been laid out. And, yes, this changed many times between when a table was first conceived and when it was finally fully developed.
Storing the response data works well as JSON, and the Survey Admin Client can also parse it to get the data. But there are tradeoffs in this case. What if there are a lot of responses? That's a lot of work for the client. Or what if the responses individually are large? Yep, same problem.
There is also the matter of how to organize the display of the data in the Survey Admin Client. No end to the options here. But what we're generally after is a table showing the individual responses, so we can easily filter all the data for a certain question. This isn't really how the data is organized - there's a record for each set of questions. Not really the same thing at all. So let's transform it.
Here, we're taking a query that contains a field with JSON in it, and creating a new JSON array with a record
for each object (the individual responses) in the original JSON, paired with a header containing the other fields from the query. A bit like running an SQL join
where the second table is a JSON object, really. Here's the whole function.
function TSurveyAdminService.GetAllResponses: TStream; var usr: IUserIdentity; fdc: TFDConnection; qry: TFDQuery; Responses :TJSONArray; Response: TJSONObject; NewResponse: TJSONObject; SID, SName, SGroup, STime, SClient: String; Final: String; i: integer; begin // Got a usable JWT? usr := TXDataOperationContext.Current.Request.User; if (usr = nil) then raise EXDataHttpUnauthorized.Create('Failed authentication'); if not(usr.Claims.Exists('account')) then raise EXDataHttpUnauthorized.Create('Missing account'); // Make sure this account has access to view surveys if Copy(usr.Claims.Find('security').asString,1,1) <> 'W' then raise EXDataHttpUnauthorized.Create('Not Authorized: GetAllResponses'); // CRITICAL TODO: Check that the account has appropriate privileges for this operation // Returning JSON TXDataOperationContext.Current.Response.Headers.SetValue('content-type', 'application/json'); // Result := TMemoryStream.Create; // Create a query Support.ConnectQuery(fdc, qry); // Record what we're up to Support.LogHistory(qry, usr.Claims.Find('account').AsString, '', 'GetAllResponses'); // Populate query: responses, surveys (select) with qry do begin SQL.Clear; SQL.Add(' select utc_stamp, responses.survey_id, client_id, response, survey_name, survey_group'); SQL.Add(' from responses'); SQL.Add(' left outer join surveys'); SQL.Add(' on responses.survey_id = surveys.survey_id;'); end; qry.Open; // Here, a set of responses is stored as a JSON object but what we'd really prefer is a JSON array with // a separate element for each individual response. This would be trival in SQL if there was a record // for each separate response. So here we're 'unpacking' the responses to get the same end result // This could also be done on the client. Responses := TJSONArray.Create; while not(qry.EOF) do begin SID := qry.FieldByName('survey_id').AsString; SGroup := qry.FieldByName('survey_group').AsString; SName := qry.FieldByName('survey_name').AsString; STime := qry.FieldByName('utc_stamp').AsString; SClient := qry.FieldByName('client_id').AsString; Response := TJSONObject.ParseJSONValue(qry.FieldByName('response').AsString) as TJSONObject; i := 0; while (i < Response.Count) do begin // Each record looks like this. Repetitive? Certainly. We also cheat a bit by having the client insert // the query name and survey name as part of the response so we don't have to go and look them up separately. NewResponse := TJSONObject.Create; NewResponse.AddPair('SurveyID',SID); NewResponse.AddPair('SurveyGroup',SGroup); NewResponse.AddPair('SurveyName',SName); NewResponse.AddPair('SurveyTime',STime); NewResponse.AddPair('SurveyClient',SClient); NewResponse.AddPair('Order', TJSONNumber.Create(i)); NewResponse.AddPair('QuestionID', Copy(Response.Pairs[i].JSONString.Value, 1, Pos(':', Response.Pairs[i].JSONString.Value)-1)); NewResponse.AddPair('QuestionName', Copy(Response.Pairs[i].JSONString.Value,Pos(':', Response.Pairs[i].JSONString.Value)+1,maxint)); NewResponse.AddPair('Response', Response.Pairs[i].JSONValue.Value); Final := NewResponse.ToString; NewResponse.Free; // Managing memory here - NewResponse (TJSONObject) has to be treated carefully, so here we're converting it to a string and // then disposing of it, and then adding the string to the Response. Haven't checked whether this catches everything. Responses.Add(TJSONObject.ParseJSONValue(Final) as TJSONObject); i := i + 1; end; qry.Next; Response.Free; end; // Take the results and stream back to client Final := Responses.ToString; Result := TStringStream.Create(Final); // Cleanup What We Created Support.CleanupQuery(fdc, qry); Responses.Free; end;
There are a few extra functions referenced here that we've not covered. For example, our connection to
the database is managed with "Support.ConnectQuery" and "Support.CleanupQuery", which essentially manage new TFDConnection and TFDQuery components each time (not very efficient, but reliable). There is also a
"Support.LogHistory" call which is what generates the contents of a history table, logging all accesses against all the endpoints in the Survey
Server app (this function appears in each endpoint).
But the useful bit of code is what we're after here. It loops through a query, and for each record, it takes apart the JSON in that record and loops through its objects, creating the final result (the JOIN of the SQL data and the JSON data). A bit of a cheat was used by including the question name in the JSON when the Survey Client created the response.
This saves us from having to do another join against the questions table, where we'd also have to decode the JSON to get at the question name. Not so fun. On the other
hand, doing it this way means that if the name of the question is changed after the response was recorded, the response will still show the old question name.
How's that for justifying a hack? In any event, we end up with what we're after - a set of JSON with the data
joined as needed. All that's left is to display it.
Displaying Responses.
Probably the easiest part of the whole exercise, all we're doing is displaying the results of a service
endpoint call in a Tabulator table. Which we've done quite literally a dozen times already.
procedure TForm1.btnResponsesReloadClick(Sender: TObject); begin await(UpdateTable('ISurveyAdminService.GetAllResponses', [], tabResponses, btnResponsesReload)); end;
And the definition of the table wasn't anything special either.
asm this.tabResponses = new Tabulator("#divResponses",{ resizableRows: true, layout: "fitData", columns: [ { title: "SurveyID", field: "SurveyID", visible: false }, { title: "Group", field: "SurveyGroup", visible: true, minWidth: 100, headerFilter: true, bottomCalc: "count", resizable: true }, { title: "Name", field: "SurveyName", visible: true, minWidth: 100, headerFilter: true, resizable: true }, { title: "Time", field: "SurveyTime", visible: true, minWidth: 100, headerFilter: true, resizable: true }, { title: "Question", field: "QuestionName", visible: true, minWidth: 100, headerFilter: true, resizable: true }, { title: "Response", field: "Response", visible: true, minWidth: 100, headerFilter: true, resizable: true } ] }); end;
The only extra bit was to enable the "headerFilter" option, which adds a text field at the top of each column. Filling in the text in these fields filters the contents of the table, as you'd expect. Here's what it looks like.
Viewing Responses.
The survey responses can be seen on the left. The table on the right shows any feedback that has been sent
using the icon in the top-right corner of the survey. Could have some filters for that as well if there were to
be a lot of content generated there. Note also that if someone provides feedback, we capture a log of their
client activity. Here's what it looks like.
Viewing an Activity Log.
This can be useful when troubleshooting problems and could be expanded to include more information about the
browser or OS of the client, other information that might be provided by the survey user, or perhaps other
parameters passed to the survey client if things like customer numbers or referral codes were part of the
survey. A nearly identical facility has been set up for handling feedback about the Survey Admin Client as well.
To-Do List
While our set of three apps is complete enough to start running surveys, there are a number of things that
didn't get done in time for this post and will need to be completed before any kind of customer might be
invited in. Here are ten that come immediately to mind.
- While we've implemented a JWT mechanism and it works well, there is a level of control that is a bit more
fine-grained in terms of limiting what a particular account can do. For example, a user might be able to edit
a survey but not see the results, yet be able to edit and see the results of a different survey. It is
important that this logic is implemented in the server as well, as you'd not want someone to use the REST API
to gain access to data that they don't have access to in the web application. We're assuming the REST API
properly coordinates with any database-level restrictions. Some applications can be implemented using JWT
claims that can greatly simplify this kind of thing.
- Similarly, the idea of keeping the question data separate from the survey information is that there may be instances where a survey is not currently active, so the questions need to be inaccessible. Perhaps the survey is a quiz for example, and you don't want the quiz questions coming out ahead of time. The GetQuestions function on the server needs to inspect the Availability JSON and decide whether or not to return data.
- The database isn't particularly performant, and we aren't doing anything to help it at all. XData has additional capabilities related to caching database connections, hosting multiple applications in one database (tenants), and various other combinations. Moving to a different database engine opens up a world of performance improvement possibilities.
- More options for filtering responses and generating reports to present survey results.
- Notifications for events (when feedback is received, for example) as well as for handling password resets and that kind of thing.
- Support for themes or other display options. Perhaps a format that fits into a leaderboard, for example.
- More question types, including additional branching options. Maybe a signature or photo question, etc.
- Support for going back to previous questions and remembering what option was selected. Harder than it might
seem, given we don't currently pay much attention to what generated the responses.
- Authenticated surveys, or surveys that have a tracking code, so they can be used for customer feedback, that kind of thing.
- More account handling options, including a mechanism for charging for surveys, showing payments, remembering
logins, renewing JWTs, and so on.
Roadblocks Overcome
While many blog posts are short and focused on a small number of things, creating posts with several applications and lots of moving pieces involves a lot more time and effort. And the point of that time and effort is to show what is possible, and how to get there without getting tripped up all the time.
But,
naturally, creating these things invariably ends up in me getting tripped up all the time. I think it
is important to pass along some of these lessons, to give this work a bit more realism and to show that, while
encountering problems is inevitable, there are solutions to be found nearly every time. Hopefully, these posts
dramatically reduce the instances where they might slow you down. Here are a few.
- Setting a claim on the JWT that uses one of the reserved names. I didn't know that reserved names were a
thing. When things stopped working, the troubleshooting started at the top, with various errors manifested
nowhere near the source of the problem, and it took a while to stop and identify where the problem was actually occurring. Here's a post
in the TMS Support Center that shows the kinds of rabbit holes one can fall into very easily.
- Having a SQLite database become corrupted due to XData's multithreading didn't happen until very late in the
development process. Also something that I didn't know was a thing. It only came up when a lot of database
operations (logging in particular) started to overlap in the server but had otherwise been perfectly fine. Sorting out how to get the SQLite database to do proper locking, and changing the endpoints to each create their own connections for each database operation was easy enough once the source of the problem had been identified - the default settings for FireDAC, in this case.
- Originally, passing the link to the survey to the Survey Client was done with just a parameter of "S". All was working fine. It wasn't until I tried scanning the QR Code from an iPhone that I noticed that this parameter was scanned, but not included in the URL that was passed to Safari. The "/?S=" was there, but the value was stripped away. So many (useless) rabbit holes. So little time. Eventually, I just changed the parameter to SurveyID, and away it went. Presumably, S as a parameter is too generic and Apple considers it a tracking code and hides it? Or something? Still not clear on the logic here.
- CSS can be fun, or it can be a nightmare. As an example, ideally, a question would be centered horizontally
and vertically on the page, perhaps using Bootstrap's "d-flex justify-content-center align-items-center". The
first cut of this worked fine, but when the question was longer than the page, it would be cut off at the top
and bottom. Adding an "overflow-y: auto" would generate the scrollbar, fine, but it was scrolling only from
the top cut-off. Adding "margin: auto;" instead of the other Bootstrap classes was the trick. But I suppose that's the great thing about CSS - everything is possible but there might be a bit of fiddling every time you
try something new. And, also, you have to really test the limits of your designs if you want to find all the
problems. This, too, was uncovered late in the process. Generally, lots of time was spent fiddling with
Bootstrap and CSS, but mostly because I want things to look the way that I want, which is often not the
default look.
- Using SunEditor as the HTML editor in this project has been pretty great, very happy with the results. This
is something I've worked with before,
so no major problems were expected. However, SunEditor (and I believe SummerNote as well) filters the elements
that are on the page, removing anything potentially harmful. Like <input> elements or <textarea>
elements. And even if you tell SunEditor to allow those, it turns out you also have to tell SunEditor which
attributes within those you would like to not filter as well, which seems to be conspicuously absent from
their documentation. Here's what we ended up with.
addTagsWhitelist: "i|svg|label|input|select|option|textarea", attributesWhitelist: { input: "type|id|maxlength|value|name|style|class", label: "id|for|style|class", textarea: "id|maxlength|rows|cols|style|class|resize" }
- Also with SunEditor, I struggled a bit just trying to figure out how to display a text file with line
breaks, like in the log files we create. The \n (chr10) is naturally discarded when displayed as HTML. I
was trying things like having SunEditor show the file as source code (which uses CodeMirror)
by default, but it wasn't working so great with the line breaks. Ultimately, I just replaced the line breaks
with "<br />" and instead of CodeMirror, just wrapped the whole text block with "<pre>" and
everything worked out great. No clue why I didn't try that in the first place!
All Done!
And with those out of the way, we're all done! My absolute favorite feature of TMS WEB Core is the ability to
seamlessly transition between Delphi (Pascal) and JavaScript while writing code. But my second favorite
feature of TMS WEB Core is TMS XData, and hopefully, you can see why. The ability to connect a potentially fantastic
UI built with TMS WEB Core to the limitless data accessible from FireDAC is a potent combination. This makes it
easy to develop modern JS/HTML5/CSS applications that run on virtually any hardware, and then connect them to
nearly any database, whether it is found in a highly secure corporate legacy system or a database created on the fly. XData provides a key bridge that helps keep the web application side manageable while providing access to data
using all the capabilities of a Delphi VCL application, something many of us are very comfortable with.
The code for all three projects can be downloaded from the following links. GitHub repositories have also been
created for each. And if you have not filled out the survey yet, please do!
Survey Client Download
Survey Server Download
Survey Admin Client Download
GitHub
Survey Client
GitHub Survey Server
GitHub
Survey Admin Client
I sincerely hope these posts and all the included example code will be a useful resource. But as with all software projects, things will undoubtedly continue to evolve. The plan is to continue building on what is here. First, completing the to-do list, and then building other features, and exploring concepts we didn't have a chance to cover here. If that sounds like it might be of interest to you, please post a comment below about what interests you. Or follow along over in GitHub.
But, if you haven't already, please fill out the survey!
Related Posts
TMS XData: An Extended Example Part 1 of 3
TMS XData: An Extended Example Part 2 of 3
TMS XData: An Extended Example Part 3 of 3
Follow Andrew on 𝕏 at @WebCoreAndMore or join our 𝕏 Web Core and More Community.
Andrew Simard
This blog post has received 4 comments.
Bruno Fierens
Ebikekeme Ere
Ramazan Geven
All Blog Posts | Next Post | Previous Post
Ebikekeme Ere