Hello, so Tiki provides a very powerful and flexible database abstraction layer.
Thanks to a concrete example which expanded for three years.
We have learned a lot.
As we start a similar project, we have time to reflect on lessons learned, pitfalls to
avoid.
And why not share everything with us, with you.
So first I described the context, what the project was about, how we did it, what the
challenges were, and what we learned as a summary.
So I'm Jean-Marc Kipps.
I discovered free software last century.
I'm in the Tiki community since 2006, live in Strasbourg.
I'm alone in front of you, but I don't want to believe that I did all that alone.
It was a team project.
It was headed by Evoludata, and a lot of people helped.
Some of them are in the room.
The customers were the peak team from the Institut Nationale et santé publique du
Québec.
The end users are medical testing laboratories.
So that's the website.
As you can see, everything is in French, but I'll translate as much as I can, and I translated
before I did the screenshots.
It's a way of cheating.
This is the team, it's quality control, actually.
And what I do is that every year they produce by medical samples.
They ship them to registered labs, so peak ships, and the labs have to register.
They have to register because not all the labs do the same analysis.
It depends on the machines they own.
They have to be certified for all the analyses they can do, and so they have to choose them.
Then they do the test, and they send results, and peak analyses, the results, sales reports,
and recommendations.
And this is what they call one campaign, and there are many campaigns which are linked
to group together in the program, et cetera.
That's one of the processes.
They used to do that using faxes.
So at first you think, hey, how hard is it to be better than fax?
It actually faxes hugely flexible.
So for example, different medical disciplines did things in different ways for totally valid
reasons.
So we had to adapt, but there were also clever people.
So they also used that project in order to kind of streamline and make their processes.
So we met in the middle.
Everybody improved.
And of course there are other processes, but I don't have time to explain everything.
That's just an example.
So yes, they also have every year to draft, review, approve, and publish the programs
that the people can register afterwards and manage those registrations.
So in general, that's the website.
If you don't have an account, and if you're not involved in a process, it does not match
in it for you, even if you understand French.
What's in it is this is, for example, the example of what I mentioned, that's the management
of the program.
So they have all the interface they need.
They can edit it.
They can view it.
They can go and edit the campaign which are linked to the subprogram.
They can go to other pages.
There's a lot of it.
Every table, as you can guess, is actually data linked to this program, but in other
tables and sometimes in all other tables and other tables.
So it's not simple.
This isn't too hard.
That's the process where they approve.
They discuss on it.
That's just comments, and then they validate it when they agree together.
This is actually the same subprogram, but that's the end user view.
So we have that flexibility, and also that's where they actually click when they want to
register, as I said, they would.
But there are lots of variety.
Here is another program that you have plenty more combined.
You can click on them because it's not the time of the year when they register.
So as I said, it's rather complex.
How we did it.
Tiki, in case you don't know, has plenty of features, and you have to choose the ones
you want for each project.
So basically, we use the wiki pages in Tiki in order to embed widgets, which we call plugins,
in the wiki pages, and that's where the logic is.
Well, you can also use them for documentation.
We have file galleries.
We don't use them a lot.
But there are some documents to share.
Trackers is the huge thing.
Trackers is the Tiki name for the database abstraction layer, because it's starting
it, it's starting, started as a bug tracker with grew and grew and grew, and now it's
a full-fledged database abstraction, but it's hard to rename things afterwards.
The fact each tracker item still has a status, open, pending, et cetera, and we use it.
The categories are useful because that's what we use for the permission system.
The scheduler, I'll get back to it.
It will be simpler.
The performance-related features, the main one is that, well, when you have a lot of
data, the important thing is how you set and index it, and the default is MySQL full
text, but you really need to install elastic search for that.
We really had to because there are too many limitations, especially in the number of fields
that MySQL can do.
And the rest are basically, we had to raise everything, and it's easy to do because we
do it within Tiki.
It's just configurable.
So all the time we doubled some memory limits, et cetera.
So trackers.
Trackers are basically, you can think about trackers as tables.
Each tracker is a table.
We have 86 of them so far and still growing.
This is the tracker admin view, which end users don't see, but the customers love it
because they feel empowered.
They can see what's going on.
They can edit stuff.
We have activated inline editing.
So when you see that, you can click on any of those little widgets and edit what's there,
correct typo, a filter on what you want to see, sort on every column, et cetera.
So that goes out of, that allows to do a lot of things without bothering to set up a whole
workflow and it's really useful.
So I said that trackers are like tables and tables have fields.
So there are plenty of kind of fields and these are also, you can just add them, et cetera.
So the useful ones or the auto increment was, is really practical because it allows to access
and display the item ID of each tracker item.
Item link, it's super powerful.
The item link, well, if you're familiar with SQL, think about foreign keys.
The item link links to another tracker.
So when you edit the item, you have a selection of item from the other tracker and you can
link track, well, two trackers.
You can link tracker items from one tracker to tracker items to another tracker.
Once you manage to link those, items, it is super useful because it does the other thing.
For example, as we said, these are the campaign.
Each campaign is linked to one subprogram and once it's linked to the subprogram, I
mean, the subprogram has a year of the subprogram.
So the campaign just gets the year from the associated subprogram.
You don't have to do a double entry, et cetera.
So you get those data.
It's all indexed together and when you display the campaign, you have all these, all these
values from other trackers.
So when you start to link trackers, as you can guess, yeah, it starts to look like, you
know, database schematic.
That's a schematic I did.
By the way, in a wiki page, in a tiki page with a draw widget, I needed it for a workflow
because otherwise I couldn't figure out what to do.
And so this is, I linked all the item links and the item lists and I put color because
the color is about the fact that, about the fact that when you link tracker items and you
delete or change the status of a tracker item, you may want the related tracker item to be
also deleted or its status to change or not.
That's configurable and that's why I wanted to keep track of it.
Yeah, still not 86 trackers.
That was, yeah.
So how it dealt with source management.
We had three, not four environments.
We set up a dedicated private GitLab repo.
We had our branches and we stuck dev and test on those branches.
So every commit would instantly update the site.
We get from one to the other by merging and terrific and production is not locked in production.
Then the staging environment we called test is approved.
We create a tag with the date and we run that in production.
And so that means that, well, we have auditability about our versioning system tells us what
we were running at what time, how it evolved, what we had in production at a given time
if we want to recreate production at a former date when you hit a bug and you try to figure
out is that a regression or is it something we missed last year and it was already there.
All our commits were very careful.
We put that we do not edit the Tiki files as much as we can.
We add our templates in our theme or in the custom translation.
That means that when we do a merge and we want to get the novelties from the Tiki community
called and the security improvements, we do not get merge conflicts.
The database management is just the opposite flow because the reference is in production.
That's where we have the real data.
Some of this data has been entered by end users.
Some of it is those wiki pages we edit in order to show you later why we have code in
our wiki pages.
The nice thing is that we can try that.
We synchronize test and dev from that.
Then we do experiments.
Then we get that to be validated.
If it's okay, that's the approved edit.
Then we synchronize.
Tiki takes care of keeping a history of changes in the wiki pages, in the tracker items.
There's an activity log and that's how we get our auditability for that part.
I just said that all our environments are running the same database.
You may get how this is an issue.
What we do is that one single file here is not a versioned.
This one is specific to each Tiki because this is the one which has the database credentials
and it also has a link to configuration file which can be versioned because we have an
item for a section in the configuration file.
That means that in the same configuration file, each environment uses another section.
In this section, we can override any Tiki preference.
This has two very big advantages.
The first one is that all the security preferences and others can be set in that file and cannot
be accidentally modified through the Tiki admin panel.
The other is that we can have different things in different sections.
That allows us, for example, to ensure that only the production server can send email
notifications.
You do not want your end users to get notifications from a test server or a dev server that I'm
not supposed to know about.
What else?
Yes, and you can change the browser's title.
You can change the theme options and end up having your browser tabs like this to have
different colors when you are working in production or in staging or in dev.
That avoids big mistakes when you are editing a site where you want to be sure that you're
not editing fraud when you want to do stuff in dev.
So there's still the part about how you do that.
So Tiki has a no-code, low-code approach, but at some stage you just have to accept that
the project is really complex and abundant.
The great thing is that there are options for doing really complicated stuff.
These are basically the list widgets which we call plugin because the list widget is
super useful because that's what allows you to display stuff which come from anywhere
in Tiki, but here we are only interested in the tracker items.
List execute is very similar to list, but that's not for displaying.
That's for listing stuff and doing things for on a whole bunch of tracker items at the
same time like deleting them or changing their status.
Custom search is also closely related and this is for allowing people to do searches,
to filter, to end user have control in this case.
So that's a list widget example.
You are not going to understand how it works like we don't have the time.
We ourselves have that documentation page.
We spent a lot of time.
It's plenty of info.
Everything is there, there are examples and all that.
We spent a lot of time in it.
Basically the general idea is that this is something we can put in a web page.
There is a section which says what filters, what we are going to display.
There is a section about how we want to output it.
The more there are predefined templates, but if we want full control, you just give a smart
ETPL file and then you can code whatever you like.
You can even change the formatting before it gets to the template.
And if your filter doesn't match anything, there is an alternate in order to an alternate
section.
So that allows you to do all the pages you saw before.
You have to realize that when I say you can do whatever you like in the template, one
of the things you can do in the template is call another list plugin.
The syntax is slightly different from the web page.
And that allows you to collect information to trackers which are linked to other trackers,
etc.
And you can go on and on and on and on if you like.
There are no limits at this point.
So that's basically what we used nearly all the time for all the pages, for all the workflows.
The scheduler is also really useful because sometimes some processes are just too complex.
There are two special cases and all that.
And we had especially like the scoring system.
We just wrote a script which was directly doing the calculation and updating the values
in the database.
And the scheduler is our way of ensuring that things can run whenever you like.
For example by night because luckily neither our customers or the end users really wanted
to work outside of working hours.
So we can run everything we like during the night, especially nightly script for calculating
scoring things or index rebuilds, whatever.
So what were the challenges?
One of the challenges we had was that page because that page was awesome because we had
lots of information.
It doesn't show here but actually those columns have related information which are in different
trackers.
So that's one of the cases where you have those templates which call another list plugin,
which call another list plugin, which call another list plugin.
So obviously the first year everything was great and you had everything here.
We were using table sorter.
You can sort on any column.
You can filter in these places and you can move the pagination around.
It was all client side, meaning all the data was in the page and after the third year it
starts to get you cloud player timeouts.
So we have rewritten the templates in order to optimize to do some caching ourselves in
the code.
And then we had to raise the memory limit because you know, trade-offs.
But that's not a solution which is going to last forever.
So it's solved for this year and they want to have five years of data, I understand.
We'll see that.
So basically this will need to be rewritten using custom search and just paginate.
Or here they have the download button because they will all those information in CSV so
that they can do more data mining.
So we will provide, we will rewrite that but let them download subsets of the data.
And that should solve that.
And figure out, I'll talk about CSV extract here.
That was another issue we have.
That's a, every link here generates a CSV file which again gets data from plenty of trackers.
So for the big labs we did have some timers and we were about to do the same thing, you
know, rewrite the optimize, the TPL, etc.
But luckily we had another idea which is to talk with the customers who explained that,
well, that those data hardly ever change.
So the solution is not to calculate them when you click on the button.
We are just going to use some caching and have a nightly mechanism or, you know, generate
the caches at the right time and just link to a file.
Yeah.
So mainly our lessons and improvements meaning that since I said that we are going to have
a similar project which is about to start.
We wanted to see what we, well, essentially it worked.
The customers were happy but we can still improve stuff.
So what we are going to improve is use more sophisticated Tiki permission mechanisms which
is called templated groups.
That's for the permissions about what people are allowed to see depending on the groups
they are in.
We just used a simple way and then we had to add another layer of security in the smarty
templates.
We want to avoid that in the new project.
Make sure all the layers of data are present in the design.
Well, that's always hard because, well, it's always hard to realize that there is a missing
table or tracker.
It makes a lot of extra work to discover that too late.
Then again, I'm totally convinced that it would be even worse if we were working in
real SQL.
The other thing is, as I said, the other lesson is, well, table sorter is not a tool for data
mining huge data sets.
That's the summary of it.
So you have to get your customers to accept that sometimes they have to use pagination
and not have everything available and there are technical limits.
Same thing for identifying huge CSVs.
And also we have taken advantage of this.
We are going to improve the list plugin which will be expanded with sublist sections which
basically will allow us to do joins without having to do that in TPL files.
And that's about it.
Thank you, Jean-Marc.