FWD » Core Development » Database

When running import, the user should be able to optionally force the import to limit itself to one the following:

• Only import tables if they are marked shared in the schema. This would be suitable for easily importing the default tenant. OR
• Only import tables if they are marked multi-tenant in the schema. This would avoid importing the shared/=default tenant data into a tenant-specific database.

If not specified, neither of these are active and the entire set of tables are imported.

The key here is that we are making import less trouble since otherwise the customer will have to carefully separate the shared from the multi-tenant dump files. That might be error prone and is definitely more work. It seems pretty easy for us to fix this issue in our part of the process.

1. the default-database (this is exactly what we have now) with schema (.df, configured in p2j.cfg.xml, namespace.importFile) and set of the .d files in a directory with same name as the database in (dataPath parameter);
2. the set of tenant databases. Logically they inherit the schema from the default-database. Question here: where is this list going to be configured? I assume p2j.cfg.xml, namespace.<something>
3. a list of shared tables (or non-shared, maybe, depending which one is shorter - since there can be 1000+ tables in a database); This question here: where is this list configured?
4. for each tenant database, a directory in dataPath with the .d files to be imported. Only non-shared tables will have associated .d files present.

I don't think this is correct.

The default database and the tenant databases have the same exact schema. There is no difference. In OE, it is a single database instance. They don't separate the schema. Instead, each table has a flag that tells if it is shared or multi-tenant.

We need to migrate that flag into our DMOs and read it from there. It should not matter if the .d files for shared and multi-tenant tables are intermixed. Our code should know the difference based on the annotations in the DMOs. We don't want or need any changes in p2j.cfg.xml.

Yep, I forgot about the DMO annotations.
But in order to generate that, the information must come from somewhere. How is this information (which table is tenant-enabled) extracted/dumped from OE? I assume the _File meta should have some details, but I cannot find any field to describe whether a table can be tenant-enabled.

But there is _Tenant table instead. However, except for a few fields, the rest are generic/meaningless attributes. We need a manual to populate this meta table correctly (and I assume there will be queries which will use this table, too).

I do not think the tables from different tenants can be intermixed. If a table is tenant-enabled, it also keeps it schema definition, therefore all instances will have the same _dump-name attribute. Therefore the .d files need to be separated. When I set up my local tenancy enabled FWD server, I used a directory structure like:

/data / simple-db-1/ ts1.d, ts2.d
      / default-db1/ pt1.d, pt2.d, pt3.d, pt4.d
      / db1-tenant1/ pt1.d, pt2.d, 
      / db1-tenant2/ pt1.d,        pt3.d

Ovidiu Maxiniuc wrote:

But in order to generate that, the information must come from somewhere. How is this information (which table is tenant-enabled) extracted/dumped from OE?

If a table is marked as multi-tenant, in the .df file there will be an attribute that specifies this. The following snippet is extracted from a .df file that I used in a multi-tenant environment.

ADD TABLE "test" 
  MULTITENANT yes
  AREA "default_area" 
  DUMP-NAME "test"

Radu got to the schema answer before I could post. :)

I do not think the tables from different tenants can be intermixed. If a table is tenant-enabled, it also keeps it schema definition, therefore all instances will have the same _dump-name attribute. Therefore the .d files need to be separated.

When the customer dumps data, we require that each tenant is dumped into a separate set of .d files, if that is what you mean. However, I don't want the customer to have to separate out the shared vs multi-tenant .d files. That is what I want us to handle, by optionally ignoring the .d files that we aren't supposed to handle.

Radu,
I did not see that attribute in the .df file I have. The table test is defined as:

ADD TABLE "test" 
  AREA "Misc" 
  DUMP-NAME "test"

Greg,
Please note the DUMP-NAME attribute. As noted above, all tenant tables of same 'parent' will be dumped with same name, in this case test.d. To avoid overwriting them, the dumps must be configured with different destination directories. Having their name matching (if possible) the tenant database name is ideal. The import will naturally skip the tables which were previously imported and also those with no matching .d in the source (from FWD PoV) directory.

I guess when a tenant database is exported in 4GL, it can be configured to skip non-tenant tables, and dump those only from the default database. We will have to login on the legacy system and double check this assumption.

I did not see that attribute in the .df file I have. The table test is defined as:[...]

I think that attribute is only there is schemata that have multi-tenancy enabled. Normal schemata don't have it.

Please note the DUMP-NAME attribute. As noted above, all tenant tables of same 'parent' will be dumped with same name, in this case test.d.

OE doesn't provide a tool to export the data for just one tenant. The customer must do that themselves. At that point, it is up to them to get and manage their tenant-specific sets of .d files.

To avoid overwriting them, the dumps must be configured with different destination directories. Having their name matching (if possible) the tenant database name is ideal. The import will naturally skip the tables which were previously imported and also those with no matching .d in the source (from FWD PoV) directory.

On import, the it is up to the customer to choose how they organize the files. I don't think we need to specify it at all. The import tool already allows you to do that, right? Then they can already implement tenant-specific directories or whatever they want.

I guess when a tenant database is exported in 4GL, it can be configured to skip non-tenant tables, and dump those only from the default database. We will have to login on the legacy system and double check this assumption.

I don't know about this. Since the OE tools don't provide support for per-tenant dump, I doubt those tools are smart about the shared data. Yes, we need to test it. But if they are mixed together, then we need to bypass them at import time if we are importing a tenant instance as compared to the default tenant.

I'll let Eric do the code review but from a user perspective, the changes look really good. Nice job!

Code review 9093a/15727-15728:

For a small update, this took me several read-throughs to comprehend. However, I think it's correct.

In particular, I was confused by the logic at import.xml:436, based on the comment above it about the with-default attribute (which doesn't actually exist; it is multi-tenant, with-default). When I finally understood that the multi-tenant and multi-tenant, with-default attributes were not mutually exclusive, it made sense.

I didn't notice in earlier reviews of MT functionality that we use the annotation multi-tenant for tables, but multiTenant for sequences. This inconsistency is a bit confusing. Is there a reason for this?

This is not part of your current update, but could you please augment the javadoc for the area rule in schema.g to account for the KW_NO_DEFAR token (i.e., just an explanation of what it represents).

What testing have you done?

Eric Faulhaber wrote:

Code review 9093a/15727-15728:

For a small update, this took me several read-throughs to comprehend. However, I think it's correct.

I am sorry. It evolved a bit while working on it, but I tried to get final version the best of it.

In particular, I was confused by the logic at import.xml:436, based on the comment above it about the with-default attribute (which doesn't actually exist; it is multi-tenant, with-default). When I finally understood that the multi-tenant and multi-tenant, with-default attributes were not mutually exclusive, it made sense.

• regular / not multi-tenant. The content is accessible for all users. Stored in the shared database, these do not have any attribute when exported, the same as in a non-tenant database. When importing into a tenant database, these are skipped;
• multi-tenant. The content is accessible ONLY to a tenant user. The DEFAULT user cannot access this, therefore these tables are imported only into tenant database, not in the shared one;
• multi-tenant with default. Each tenant (including the DEFAULT) sees his own image of this table, therefore these tables are imported in all databases (shared + private).

The problem occurs when the 4GL exports the .df, to distinct between the last two, instead of adding a with-default option to the 3rd (as it presents the tables in DB Admin GUI), it adds a no-default-area to the 2nd.

I didn't notice in earlier reviews of MT functionality that we use the annotation multi-tenant for tables, but multiTenant for sequences. This inconsistency is a bit confusing. Is there a reason for this?

No. Just that the two implementations happened independently. The flags are tested independently.
I unified them, keeping the table variant as it was easier to change the one used by sequences.

This is not part of your current update, but could you please augment the javadoc for the area rule in schema.g to account for the KW_NO_DEFAR token (i.e., just an explanation of what it represents).

OK. Done.

What testing have you done?

Only local. With PSQL and MariaDB dialects, but this in not important as the changes in code is dialect independent.
I will do an import for hotel project and another large customer application, but these will only prove the non tenant import is not regressed. We do not have a complete MT database snapshot for a proper test.

I committed the changes and rebased the branch to latest trunk. Current revision is 15737.

Ovidiu Maxiniuc wrote:

The regression tests returned with success, as well as my targetted testcases used for development. But these no not actually test the new code.
I asked for the original dumps of MT database from the customer last week, but I have none yet to test. I will update the task as soon I have news.

Can you proceed with testing the simple MT setup you were using before the customer's test container went MT? It seems that as long as each table type and database type are represented, this can be tested functionally.

Eric Faulhaber wrote:

Can you proceed with testing the simple MT setup you were using before the customer's test container went MT? It seems that as long as each table type and database type are represented, this can be tested functionally.

The changes only affect the import process. Both the ST and MT setups provide the databases as gzipped PSQL dumps, therefore the 4GL databases already imported.

I've prepared 4 empty database bzip2 backups and the current metadata split into the two separate directories as we currently load them.

I downloaded the files from your link. Thank you.

• the emptydbwithschema: these archives seem to be used for creation of pre-prepared physical databases for a default and 3 tenants.
  In #9618-42, the scripts I posted do exactly that (only for one tenant, but the others can be parametrised). I've looked a bit into these and I think they are/can be obtained by dumping the schema from the databases obtained from my scripts (or a full dump if the actual import is not yet performed). OTOH, looking into the src/ subfolders, there are some post-precessing you seem to apply to database before the import. This is not recommended, but you can still apply these before (or after?) the actual import, if necessary;
• the second folder contains the files from extras/deploy, split between the default and one tenant database. These are the exact files we already have, output to src/ by the script which launches the docker container. Basically, this is not necessary. The new import code in 9093a branch will analyse the .p2o files associated with your database and will do the import only for appropriate files/tables:
  • It will work as you expect for the tenant database (well, this package is only a few MB so it's clear that it's a scaled-down version of the multi GB version we received a few months ago) since only the 609 MULTITENANT yes tables will be processed;
  • However, my scripts will import all 616 tables in the default physical database since, even if they are declared as multi-tenant, they have AREA "default_table". To prevent these tables to be imported in the default physical database they should have been declared NO-DEFAULT-AREA. But there is none in your original .df file;

The sequences, however, seem to be correctly imported. That is because the sequences do not feature the NO-DEFAULT-AREA.

I am preparing a new tenantMode, which will restrict the import to Default physical database only to tables not marked as multi-tenant, ignoring the 'support for default' flag. I think this is what you desire.

The current default mode will be preserved, in case customers require to import the table with support for default in their default database.

Ovidiu, I think you said last standup that this branch was ready for merge, but it is still in review status, with no update regarding regression testing. Please update this task with its current status.

Indeed, I tried to keep it up-to-date.

However, there were a couple of big updates this wee, so it requires rebasing it again. The good part is that this is a small one, very localised. I will do it this evening.

I rebased 9093a. Pushed up to revision 15848.

I also created #9875 to hold customer specific scripts for importing as examples (not only) of using the new tenantMode import parameter.

I have used the aforementioned scripts to import the customer MT database. It behaved as expected, the MT tables were strictly imported into the private database while the non MT were imported to shared database.

The import into hotel ST database showed no regression as well as the import on other large customer ST database.

I didn't see any recent changes in the branch that were not related to the rebase, so can I assume that since internal testing went well, 9093a is ready for merge to trunk?

The Data Migration chapter of the installation documentation will need updates to reflect MT import considerations.

Eric Faulhaber wrote:

I didn't see any recent changes in the branch that were not related to the rebase, so can I assume that since internal testing went well, 9093a is ready for merge to trunk?

Indeed. Yes, it is. Thank you for reviewing the branch.

The Data Migration chapter of the installation documentation will need updates to reflect MT import considerations.

Added.

Can we set Done to 100%?

I wanted to, but for some unknown reasons to me, I cannot do it. I remember that I wanted to do it, but since the combo is not visible for me I let the task as it is now. From my knowledge, this is the only task with this issue.