Extracting SCOS Auto-Completion Tables from Zip File

[pasetti]

At present, the FCP Editor extracts the tables with the SCOS data from a mySQL Database with a name starting with "SCOS" (see code in vars.php). These tables are then used to create the input for the autocompletion function. I have now established that some potential users keep their SCOS data in a database format which is not compatible with mySQL. In these cases, the SCOS tables are provided as a zip file holding all SCOS tables in csv format. The name of the zip files has the following format: 'MIB*.zip' (see example in the attachment). In order to cover these users, we could extend the editor to do the following:

• When the editor is started, it inspects a pre-defined directory to check whether there is a file with a name like: "MIB_*.zip"
• If it finds such a file, it unzips it and extracts from it the same tables which it now extracts from the SCOS database
• The tables extracted from the zip file are then used to create the inputs for the auto-completion function

One point which is not clear to me is where the pre-defined directory holding the "MIB_*.zip" file should be. We could either make the directory a configuration parameter or we could hard-code it (e.g. it might be: "/etc/fcp_editor").

MIB_v1.2.3_20210503.zip

[Tomas-M]

This is doable, but I'm not sure if the proposed import method is ideal. How does the ZIP file appear on the server? Some user will upload it through the web browser? Of you will upload it?

If user is the one who is uploading it, then we will need some interface for the upload, and perhaps management of the uploaded files.

If you are the one who is uploading it, then it makes more sense to me to just create some import tool, where you upload the ZIP file (using web browser) and it will extract it and put data in mysql database on the server, without the need to check for such ZIP file every time the editor is loaded into user's browser.

Another question I have: are we considering only situation where you have your FCP Editor installation on your own server, or should we instead consider a situation where every user has his own server with his own FCP Editor installation?

[pasetti]

How does the ZIP file appear on the server? Some user will upload it through the web browser? Of you will upload it?

The MIB ZIP file would normally be uploaded by the administrator of the tool (i.e.myself)

If you are the one who is uploading it, then it makes more sense to me to just create some import tool, where you upload the ZIP file (using web browser) and it will extract it and put data in mysql database on the server, without the need to check for such ZIP file every time the editor is loaded into user's browser.

I had not thought about this option but I agree that it is probably more user-friendly

Another question I have: are we considering only situation where you have your FCP Editor installation on your own server, or should we instead consider a situation where every user has his own server with his own FCP Editor installation?

I see it like this:

• There is a group of users (maybe 10-20) who are working on the same satellite project and who need to define a set of flight procedures which use the same SCOS database
• All of these users share access to the same installation of the editor on a server
• One of these users is the "admin" user who is responsible for occasionally updating the SCOS database

Of course, it is also possible to imagine different scenarios where each user has his own installation (maybe even on localhost) and then users share the json representations of their procedures but I think that the first scenario is more likely and more convenient for users.

[Tomas-M]

On your current server, the SCOS database cannot be created separately and the tables (pid, etc) are defined directly in your main database only and there is no way to select which SCOS database (version) to use. Does this limitation apply for the new workflow as well? Or are they going to have less restrictive server configuration which will allow them to create multiple SCOS databases in mysql?

[pasetti]

On your current server, the SCOS database cannot be created separately and the tables (pid, etc) are defined directly in your main database only and there is no way to select which SCOS database (version) to use.

This is a limitation which applies only to a special installation I did for public use of the editor. In general, the baseline is that there may be multiple databases on the mySQL server.

[Tomas-M]

So I propose the following:

• one of the registered users will be special and will have access to the new import tool
• we can use the existing 'example' user for this purpose
• this user will be able to see all SCOS databases, and will be able to upload the ZIP file with CSV data
• when ZIP file is uploaded, it will be processed on server (extracted, data will be loaded to new mysql database with SCOS prefix)

Questions

• do we need to let the user provide a name for the SCOS database? eg. he will write "SCOS_1_2" in some input field next to the upload field, when he uploads the ZIP file, so the server knows what database name to create. Or can this be done automatically, so the server will simply choose next available name? eg. SCOS_1, SCOS_2, ... SCOS_10, .... etc

The proposed solution should take just about 3 hours maximum. I can start working on it later today.

[pasetti]

I think that the proposed solution would be fine. One small comment, can we rename the 'example' user to something more meaningful like (db_admin' or similar?

do we need to let the user provide a name for the SCOS database? eg. he will write "SCOS_1_2" in some input field next to the upload field, when he uploads the ZIP file, so the server knows what database name to create. Or can this be done automatically, so the server will simply choose next available name? eg. SCOS_1, SCOS_2, ... SCOS_10, .... etc

I think it is important to let the "special user" give a name to the SCOS database. This is because a project will typically have many SCOS database releases and each release has a name which normally includes the database version (e.g. v3_5_67).

[Tomas-M]

I can see. In fact, the 'example' user does not have this username. It is just the user configured in the config.php file - $exampleUsername = ... So during the setup of the editor on the target server, you can put there actually anything you like, for example db_admin@company.com You can login with this user to edit pre-defined example diagrams in the editor, and currently it can also be used to see all diagrams of all other users, through the user.php script (in browser). So this actually already is more an admin rather than an example user. :)

[pasetti]

Very good. We can then proceed like this and I will add an explanation in the on-line help of the tool.

[Tomas-M]

How do I find out what columns in CSV files correspond to what database column names?

[Tomas-M]

I will need some settings like this:

ccf: CCF_CNAME=1, CCF_DESCR=2, CCF_DESCR2=3, CCF_TYPE=4, CCF_STYPE=5
cdf: CDF_CNAME=1, CDF_GRPSIZE=2, CDF_PNAME=3
cpc: CPC_PNAME=1, CPC_DESCR=2, CPC_PAFREF=3
pas: PAS_NUMBR=1, PAS_ALTXT=2, PAS_ALVAL=3
pcf: PCF_NAME=1, PCF_DESCR=2, PCF_PID=3
pid: PID_TYPE=1, PID_STYPE=2, PID_SPID=3, PID_DESCR=4
plf: PLF_NAME=1, PLF_SPID=2

So for example I will know that for ccf table, the CCF_DESCR2 column is the 3rd column (=3) in the CSV file Most of the columns are obvious, but some are not. Please specify. If the format can vary, I can make the above mentioned settings editable in the import tool, so user will be able to change which database column name belongs to which CSV column, ... But it will be best if we have currently the pre-made settings for our current situation ready.

[Tomas-M]

Some tables have NULL values in some columns (eg. cpc table in CPC_PAFREF column). How is the NULL value represented in the CSV file?

[Tomas-M]

I propose that the import tool can have a possibility to specify "import instructions", which could look like a freeform text, like this:

As you can see this could allow the user specify which files to read from the ZIP, and what column are at what indexes. So if the input format (in ZIP file) changes (eg. columns swap etc), user will be able to import it without changes to source code.

[pasetti]

Yes, you are right. We need to specify the position of each field in the csv file. Your solution (the editable "import instruction" field) is very good and as flexible as one might want it.

As you say, we should have a sensible default. Here it is for the fields of interest (NB: the first field is field 1, not field 0):

ccf: CCF_CNAME=1, CCF_DESCR=2, CCF_DESCR2=3, CCF_TYPE=7, CCF_STYPE=8
cdf: CDF_CNAME=1, CDF_GRPSIZE=6, CDF_PNAME=7
cpc: CPC_PNAME=1, CPC_DESCR=2, CPC_PAFREF=11
pas: PAS_NUMBR=1, PAS_ALTXT=2, PAS_ALVAL=3
pcf: PCF_NAME=1, PCF_DESCR=2, PCF_PID=3
pid: PID_TYPE=1, PID_STYPE=2, PID_SPID=6, PID_DESCR=7
plf: PLF_NAME=1, PLF_SPID=2

Some tables have NULL values in some columns (eg. cpc table in CPC_PAFREF column). How is the NULL value represented in the CSV file?

I think that, in the csv files, the NULL value is simply represented as an empty string. The separator is the tab (\t) and if you see two consecutive tabs, it means that the value in between the two tabs is a NULL value.

[Tomas-M]

Nice. Currently we have fields in MySQL table defined based on some of your other database, for example in cpc table, the column CPC_DESCR is defined as varchar(24). That means the column can hold only 24 characters.

What should happen if the CSV file contains data longer than the database declaration allows? Should I even bother checking for it? Or should I silently truncate it? I would suggest to change the database declaration for varchars to something longer, such as varchar(255), so we have no such issue. What do you think?

[pasetti]

This is contentious issue among users. The SCOS length limitations are a legacy from the past but they are still there and are a great nuisance for most users. I am not sure what the best course of action is for us... If this does not present problems for the editor, you should maybe increase the field length to 255. Then we will be compatible with future upgrades which, I am sure, will relax these length limitations.

[Tomas-M]

The ZIP archive you sent me contains a directory, and then .dat files in this directory. Is this always going to be the case? Can there be two directories? Or is it possible that the ZIP file will contain only the .dat files in parent directory, not in a subdirectory?

If there can be more than one directory in the ZIP file, which one should I consider for import?

[Tomas-M]

I made it in such a way that it searches the ZIP file for each .dat file, and uses the first one it finds. I have finished the changes. It took me 5 hours, I was not accurate in my estimate, I am sorry. Should I push it to FCP editor public repository?

[pasetti]

I made it in such a way that it searches the ZIP file for each .dat file, and uses the first one it finds.

Thanks, This is a very good solution.

Should I push it to FCP editor public repository?

Yes, please do. I will check it in the next days

[pasetti]

I downloaded the updated editor and successfully installed it. I then logged in as the "example user" (I can see that this confirmed by the tool) but how do I access the menu to upload the SCOS database file?

[pasetti]

OK. I found how to get to the page for the upload of the SCOS database ...

[Tomas-M]

Yeah there is no link to it from the editor's interface, this is actually intentional. It is necesary to load the URL manually in your browser: http://editor-url/users.php

[pasetti]

Thanks for the clarification. I can now reach the upload menu and I can select the file to be uploaded. On my localhost installation, I tried to upload the same zip file which was attached at the beginning of this ticket but something goes wrong. Here is the error log from /var/log/apache2:

[Sat Sep 25 15:00:24.320064 2021] [php7:notice] [pid 16466] [client 127.0.0.1:42296] PHP Notice:  Undefined index: seeas in /var/www/html/fcp-editor/users.php on line 40
[Sat Sep 25 15:00:24.320333 2021] [php7:notice] [pid 16466] [client 127.0.0.1:42296] PHP Notice:  Undefined index: ac in /var/www/html/fcp-editor/users.php on line 63
[Sat Sep 25 15:00:24.320823 2021] [php7:warn] [pid 16466] [client 127.0.0.1:42296] PHP Warning:  Unknown: Session callback expects true/false return value in Unknown on line 0
[Sat Sep 25 15:00:24.320840 2021] [php7:warn] [pid 16466] [client 127.0.0.1:42296] PHP Warning:  Unknown: Failed to write session data using user defined save handler. (session.save_path: /var/lib/php/sessions) in Unknown on line 0
[Sat Sep 25 15:00:40.148103 2021] [php7:notice] [pid 16157] [client 127.0.0.1:42300] PHP Notice:  Undefined index: seeas in /var/www/html/fcp-editor/users.php on line 40, referer: http://localhost/fcp-editor/users.php
[Sat Sep 25 15:00:40.148597 2021] [php7:error] [pid 16157] [client 127.0.0.1:42300] PHP Fatal error:  Uncaught Error: Class 'ZipArchive' not found in /var/www/html/fcp-editor/users.php:73\nStack trace:\n#0 {main}\n  thrown in /var/www/html/fcp-editor/users.php on line 73, referer: http://localhost/fcp-editor/users.php
[Sat Sep 25 15:00:40.148956 2021] [php7:warn] [pid 16157] [client 127.0.0.1:42300] PHP Warning:  Unknown: Session callback expects true/false return value in Unknown on line 0, referer: http://localhost/fcp-editor/users.php
[Sat Sep 25 15:00:40.148986 2021] [php7:warn] [pid 16157] [client 127.0.0.1:42300] PHP Warning:  Unknown: Failed to write session data using user defined save handler. (session.save_path: /var/lib/php/sessions) in Unknown on line 0, referer: http://localhost/fcp-editor/users.php

I suppose I am doing something wrong but I am not sure what...

[Tomas-M]

Your PHP installation does not have ZipArchive class installed. I will add there a check for it with instructions what to do in order to enable it, later today.

[pasetti]

Thanks, Tomas. I installed the ZipArchive php class using the following commad (I am on Ubuntu 18.04):

sudo apt-get install php-zip

I then re-started the apache server (sudo service apache2 restart) and now everything works as expected. I am going to leave the ticket open so that you may add the warning in case the user is missing the ZipArchive class.

[Tomas-M]

Thank you, fixed by e250f6a6d474ea03579c62d8b6e5aad5517878b4, closed.