CONCEPTUAL DOUBT ABOUT A SERVICE (Two users but just one MySql table)

[SergioFeitoza]

I have  a conceptual doubt with the use of Unigui when many different users are using the same database . I need your help to understand this. Sorry for the long text.

I have a calculation code  deployed and working as a free service. The link is at the bottom if anyone wish to see it. It was made with Unigui + Delphi + a small MySql database. I use MyDac components in the MainModule.

In the VCL version, for individual use ,everything work . If the use change a value or delete a line of a table or create a new line I need only the MyDac components. I do not need to write – in addition -  Mysql queries as below. The MyDac components do all the work. If I change a value and click the post button in the dbnavigator the value is saved in the MySql database.

 In my Unigui equal code this is not sufficient. If I change a value it is changed in the screen , work well  - I think in the memory – but when I close the code the is not saved in the MySql table.

To solve this I wrote queries like below editrecClick(Sender: TObject); When I click the edit button in the UniDBnavigator it calls editrecClick(self); and the new value is saved in the MySql table . So, the fact of using editrecClick(self); is equivalent to click the button APPLY in the MySql Workbench.

QUESTION 1: why this editrecClick(self) is not necessary in the VCL version which uses exactly the same database ? There I just click the unidbnavigator without needing this extra code

 

QUESTION 2 (the conceptual doubt ).

Suppose I have two different  users and they do their own calculations. One of the  edit and save their values and the other do something like but with other values. As I have just one database , where are the values of each one saved ?  If they use  and  days after go back to use again the values they saved will be there ( for each different user) ? Where are they saved ? In the MySql general database ? I

 

If my doubt is not clear please tell me and I will try to rewrite it.

Thanks in advance

( ** ) access to the service – just need to register an email.

http://158.69.28.48/webswd/

 

procedure TMainForm.editrecClick(Sender: TObject);

var

  i, test,max,Nregisteredusers:integer;

    NuserX : string;   myDate, Ndate : TDateTime;

    NSITE,NINSTA, NLINK,NLINKFOR,ASSE:integer;

begin

       uniMainModule.USERS.IndexFieldNames:='DATEREGISTER';

       Nregisteredusers:=uniMainModule.USERS.RecordCount;

       NDATE:=  unimainmodule.USERS.fieldByname('DATEREGISTER').AsDateTime;

       NUSERX:= unimainmodule.USERS.fieldByname('LOGIN').AsString;

       NSITE:= unimainmodule.USERS.fieldByname('SITECOGNITORVISITS').AsInteger;

       changeUSERSitem := TMyQuery.Create(nil);

        try

          changeUSERSitem.Connection := uniMainModule.MyConnection;

          changeUSERSitem.SQL.Clear;

          changeUSERSitem.SQL.text:='UPDATE USERS SET SITECOGNITORVISITS =:NSITE …’ +

          ' WHERE LOGIN=:NUSERX AND DATEREGISTER=:NDATE';

          changeUSERSitem.Params.ParamByName('NSITE').AsInteger := NSITE;

          changeUSERSitem.Params.ParamByName('NUSERX').AsString := NUSERX;

          changeUSERSitem.Params.ParamByName('NDATE').AsDateTime := NDATE;

          changeUSERSitem.ExecSQL;

          finally

          FreeAndNil(changeUSERSitem)

          end;

end;

[Farshad Mohajeri]

Hello,

When you update a table from a connection it will not reflect sessions which use different connections unless they refresh their query instances. Of course, it all has to with the isolation level that each connection is adjusted. I'm not an expert on supported isolation levels in MySQL, but I think it is the correct place that you must look into.

[SergioFeitoza]

2 hours ago, Farshad Mohajeri said:

Hello,

When you update a table from a connection it will not reflect sessions which use different connections unless they refresh their query instances. Of course, it all has to with the isolation level that each connection is adjusted. I'm not an expert on supported isolation levels in MySQL, but I think it is the correct place that you must look into.

Hello Farshad

Thank you for the answer . Lets forget MySql for the moment and speak only about a generic database as in the good Unigui DBdemo in  C:\Program Files (x86)\FMSoft\Framework\uniGUI\Demos\Desktop\DBDemo

There we have the fishes database. If I understood well the database is a clientdataset. If I change a value and save the modifications got to a xml file or something like this. So, I imagine that If I change  the value of the first field (see figure) and save-post it in the unidbnavigator it should be saved. I did this operation as in the figure. After this I closed the program . Then I opened again but the changed value was not recorded in the database. I lost the change I did.

My first doubt is why was not saved ?.

The second doubt is the following. Suppose that the changed value was correctly saved. If I deploy the code as a DLL and two different users  do the same  operation ( two seessions) where would be the values of each user be saved.

I imagine that when we refer to "session" both users can save their own values . Is it like this ?

[风吹小机机]

If two users modify the same record, the same field should be modified first, and it will be covered by the last modification,

If two users modify the same record, different fields should be modified differently

[Abaksoft]

Dear Sergio,

I am sure you know this : The best practice to work with CRUD applications is to :

1. Never use DBNavigator and its gadget.

2. use instead your own SQL logic (by your hand) :

- Start a global transaction (Read Commited)

- execute changes

-commit this transaction.

Best regards

[SergioFeitoza]

10 hours ago, 风吹小机机 said:

If two users modify the same record, the same field should be modified first, and it will be covered by the last modification,

If two users modify the same record, different fields should be modified differently

Thank you very much Newbie. Let me focus in the first line of your comment. I wrote the text below to explain better my doubt 

Suppose this is the initial table of the app  made by me before anyone used the app service. Suppose this is  the unique table which is associated with the deployed code (and not a local table)

ID	USERNAME	ValueA	ValueB
1	Sergio	10	12
2	Sergio	8	6

Then suppose  a new  user John register and starts to use the app. Suppose that it is permitted by the code and that   John CREATE a new line in the table. The table will become like       .  I know that I can do the code in such a way that people can not add lines but instead only modify them ( in a session ? in definitive ?)

ID	USERNAME	ValueA	ValueB
1	Sergio	10	12
2	Sergio	8	6
3	John	5	3

 Then a new  user Mary register and starts to use the app. Suppose Mary  CREATE a new line in the table

ID	USERNAME	ValueA	ValueB
1	Sergio	10	12
2	Sergio	8	6
3	John	5	3
4	Mary	1	5

 MY MAIN DOUBT IS

Suppose it is permitted by the code that a user create  new lines. So, when Mary goes to the app can she see the record which was inserted by John ? Or all the users see all the records of anyone ?

This is  the first time  I am creating a service. I was imagining  that when a user does something in a certain session  some internal thing in Unigui is done to create   a separate table which is visible only to that user.

If it is not like this my service can be used only without permitting to save changes.  This is because two competitors could see what the other is doing. In this case is much better that users have only a VCL version of the code which is installed in its own computer.

My Unigui app could still be useful as an online tool  but not permitting to save. The use could edit things in a session but they would not be saved in the deployed database.

This is what I need to understand to define. Maybe I was with a wrong expectation of what is possible with my code.

THE IDEAL THING, THAT I AM STARTING TO THINK THAT IS NOT POSSIBLE, WOULD BE THAT

When John access the service what appear in his table is only what he created or edited and saved. So would be a table like this

ID	USERNAME	ValueA	ValueB
1	Sergio	10	12
2	Sergio	8	6
3	John	5	3

 When Mary access the service what appear in her  table is only what she created or edited and saved. So would be a table like this

ID	USERNAME	ValueA	ValueB
1	Sergio	10	12
2	Sergio	8	6
4	Mary	1	5

Any comment is very welcome. 

[SergioFeitoza]

9 hours ago, Abaksoft said:

Dear Sergio,

I am sure you know this : The best practice to work with CRUD applications is to :

1. Never use DBNavigator and its gadget.

2. use instead your own SQL logic (by your hand) :

- Start a global transaction (Read Commited)

- execute changes

-commit this transaction.

Best regards

Dear Abaksoft. Thank you very much. I am skilled with engineering but very limited in Unigui details. I have learned based on trying several times to get it right and with the help of this forum

I think I understood  your points  except "Start a global transaction (Read Commited"). Please give more details on this.

Also , expalin a little bit more what you mean with "commit this transaction"

I wrote just above in the reply to Newbie a text to better explain my main doubt.

Could you please look on them and comment also?

As a suggestion for the future,  it would be great to have that DBdemo code amplified to include a MySql (or MSAcess) database and giving some light in the issue of multiple users.

Regards and thanks again

[mierlp]

Hi Sergio,

The big difference is that you now go from single user to multi-tenant use. Your VCL application runs stand-alone and now you go to the web and the user is only allowed to see his own records. That is multi-tenant use within your application.

You can easily solve this by creating a separate table with users and a seperate table for the value. You give them a username and password.
Every registered user, and therefore record, has a recordID (unique number). 
After logging in you can, for example, use a variable to link the recordID or use it as a parameter in your query to make a selection from all records of this one user.

When the user creates a new record in a database, you save the user recordID into the table. (OnNewRecord event)

So you will have to do a small redesign of your data model:
- 1 table for users
- 1 table for the values

When the tables are filled they will look like this for example

 

After the user has logged in and you use the recordID to select the records in the table 'Values' (at the right), he only sees his records.

In this example you are logged in as Sergio and only the records in the  VALUES table with UserID = 1 (Sergio) will be showed

 

 

 

[SergioFeitoza]

2 hours ago, mierlp said:

Hi Sergio,

The big difference is that you now go from single user to multi-tenant use. Your VCL application runs stand-alone and now you go to the web and the user is only allowed to see his own records. That is multi-tenant use within your application.

You can easily solve this by creating a separate table with users and a seperate table for the value. You give them a username and password.
Every registered user, and therefore record, has a recordID (unique number). 
After logging in you can, for example, use a variable to link the recordID or use it as a parameter in your query to make a selection from all records of this one user.

When the user creates a new record in a database, you save the user recordID into the table. (OnNewRecord event)

So you will have to do a small redesign of your data model:
- 1 table for users
- 1 table for the values

When the tables are filled they will look like this for example

 

After the user has logged in and you use the recordID to select the records in the table 'Values' (at the right), he only sees his records.

In this example you are logged in as Sergio and only the records in the  VALUES table with UserID = 1 (Sergio) will be showed

 

 

 

 

Hi Mierlp. Thank you very much for the very clear didactic explanation and example.

Now I see  that what I was missing is only to include  the recordID (unique number) in the table with the VALUES. I already have a USERS table (username + password) filled when they register. It is working well. However my Values table does not have a recordID to identify the user

Using the unique recordID as a parameter within a query to filter the table the user will only be allowed to see his own records (and CREATE, EDIT, DELETE and SAVE).

So simple but I forgot to think on this.

Well understood for the OnNewRecord event. I need only to include th recordID in the values table.) and I I will adjust all this.

 

Now I will go back to the issue of the question 1 of the post. I am wandering if the reason for not saving properly in the MySql database, for my Unigui code, is related to some inconsistency because I did not not include  the recordID in the (big) table of Values.

Thanks again for the great help

[mierlp]

Hi

Your problem regarding question 1 will also be solved. Because now a record in the value table is linked to a user. In your previous case, you didn't have that and the record didn't belong anywhere and was visible everywhere.

In addition, I would not use a dbnavigator but just buttons that you turn on / off based on the status of your DataSource.

 

The text is dutch but is as follow:
NIEUW = New (button is default enabled)

BEWAREN = Save (button is default disabled)

ANNULEREN = Cancel or Close (button is default enabled)

Then on the datasource.OnStateChange event i use the following code:

 

  // Form is already created
  if UniMainModule.GetFormInstance(TFormTypeCateringEdit, False)<>nil then
  begin

    // Database in WEERGAVE modus
    FormTypeCateringEdit.btnAdd.Enabled       := TypeCatering.State in [dsBrowse];
    FormTypeCateringEdit.btnSave.Enabled      := TypeCatering.State in [dsInsert,dsEdit];
    FormTypeCateringEdit.btnCancel.Enabled  := TypeCatering.State in [dsBrowse,dsInsert,dsEdit];
  end;

As you can see in the code above:

button NIEUW / NEW is enabled when the database is in Browse mode
button BEWAREN / SAVE is enabled when the database is in Insert or Edit mode so you can save the record
button ANNULEREN / CANCEL is enabled when database is in browse,insert or edit mode. This because you want to have
a cancel function when you either add a new record or change an existing record in addition, the button closes the form.

When the database is in insert / edit mode the function is CANCEL database action.
When the database is in browse mode the function is CLOSE

[SergioFeitoza]

2 hours ago, mierlp said:

Hi

Your problem regarding question 1 will also be solved. Because now a record in the value table is linked to a user. In your previous case, you didn't have that and the record didn't belong anywhere and was visible everywhere.

In addition, I would not use a dbnavigator but just buttons that you turn on / off based on the status of your DataSource.

 

The text is dutch but is as follow:
NIEUW = New (button is default enabled)

BEWAREN = Save (button is default disabled)

ANNULEREN = Cancel or Close (button is default enabled)

Then on the datasource.OnStateChange event i use the following code:

  // Form is already created
  if UniMainModule.GetFormInstance(TFormTypeCateringEdit, False)<>nil then
  begin

    // Database in WEERGAVE modus
    FormTypeCateringEdit.btnAdd.Enabled       := TypeCatering.State in [dsBrowse];
    FormTypeCateringEdit.btnSave.Enabled      := TypeCatering.State in [dsInsert,dsEdit];
    FormTypeCateringEdit.btnCancel.Enabled  := TypeCatering.State in [dsBrowse,dsInsert,dsEdit];
  end;

As you can see in the code above:

button NIEUW / NEW is enabled when the database is in Browse mode
button BEWAREN / SAVE is enabled when the database is in Insert or Edit mode so you can save the record
button ANNULEREN / CANCEL is enabled when database is in browse,insert or edit mode. This because you want to have
a cancel function when you either add a new record or change an existing record in addition, the button closes the form.

When the database is in insert / edit mode the function is CANCEL database action.
When the database is in browse mode the function is CLOSE

Thank you very much again  Mierlp. 

Tomorrow I will go again to the code and adjust it removing DBnavigator. Actually it was without it before and I inserted to do a trial because it was not working well due to the absence of the field UserID in the table of Values.

Your solution for NIEUW,  BEWAREN ,ANNULEREN buttons is good and I will use it.

By the way, after being sometimes in Arnhem, Delft and other places ih Netherlands I can recognize some few words but need to go to the translator to be sure.

Thanks again

[Abaksoft]

On 5/13/2021 at 2:09 PM, SergioFeitoza said:

Dear Abaksoft. Thank you very much. I am skilled with engineering but very limited in Unigui details. I have learned based on trying several times to get it right and with the help of this forum

I think I understood  your points  except "Start a global transaction (Read Commited"). Please give more details on this.

Also , expalin a little bit more what you mean with "commit this transaction"

I wrote just above in the reply to Newbie a text to better explain my main doubt.

Could you please look on them and comment also?

As a suggestion for the future,  it would be great to have that DBdemo code amplified to include a MySql (or MSAcess) database and giving some light in the issue of multiple users.

Regards and thanks again

 

Hello Sergio,

in additional to what was said by Peter (MierlP), here is a small explanation to transactions.

Transactions  is a long story. 

When we worked on the past on 1 PC, all was OK :  No need to isolate transactions from others users.

Now, when we work with many users, each user modify online data, we have to take care on the isolation writes. this is done by the transactions mechanism. 

In short, you can see here an example (with PostGresSQL / DevArt ).

In the example below, a global transaction is started, and if all is OK it will commit the result (write on disk) .

This transaction is by default ReadCommited. You can googled this for further informations.

 

procedure TForm1.UniButton1Click(Sender: TObject);
begin

  PgConnection1.StartTransaction;
  try
  //====================
    Do_SommeOperations;
  //====================

    PgConnection1.Commit;
  except
    on E: Exception do
    begin
      PgConnection1.Rollback;
      showmessage(E.Message);
    end;
  end;


end;


procedure TForm1.Do_SommeOperations;
var MyQuery:TPgSQL;
    Query_Select:TPgQuery;
    MyNumber:integer;

begin

//1. Retreiving somme values
  Query_Select:=TPgQuery.Create(Nil);
  try
    Query_Select.Connection:=PgConnection1;
    Query_Select.SQL.Clear;
    Query_Select.SQL.Add('Select MyNumber From MyTable1 where (ID=100)');
    Query_Select.Open;

    if Query_Select.IsEmpty then
      MyNumber:=-1
    else
    begin
      if Not VarIsNULL(Query_Select.Fields[0].AsVariant) then
        MyNumber:=Query_Select.Fields[0].AsInteger
      else
        MyNumber:=-1;
    end;

  finally
    Query_Select.Free;
  end;






//2. inserting values
  MyQuery:=TPgSQL.Create(Nil);
  try
    MyQuery.Connection:=PgConnection1;


    MyQuery.SQL.Clear;
    With MyQuery.SQL do
    begin
       Add('Insert into MyTable2');
       Add('(');
       Add('ID,');
       Add('MyNumber');
       Add(')');

       Add(' VALUES ');

       Add('(');
       Add('1,');
       Add(':MyNumber');
       Add(')');

    end;
    MyQuery.Execute;



    With MyQuery.Params do
    begin
      ParamByName('MyNumber').DataType:=ftInteger;
      ParamByName('MyNumber').aSinteger  :=MyNumber;
    end;

    MyQuery.Execute;


  finally
    MyQuery.Free;
  end;


end;

 

Best Regards

[SergioFeitoza]

On 5/16/2021 at 3:33 PM, Abaksoft said:

 

Hello Sergio,

in additional to what was said by Peter (MierlP), here is a small explanation to transactions.

Transactions  is a long story. 

When we worked on the past on 1 PC, all was OK :  No need to isolate transactions from others users.

Now, when we work with many users, each user modify online data, we have to take care on the isolation writes. this is done by the transactions mechanism. 

In short, you can see here an example (with PostGresSQL / DevArt ).

In the example below, a global transaction is started, and if all is OK it will commit the result (write on disk) .

This transaction is by default ReadCommited. You can googled this for further informations.

 

procedure TForm1.UniButton1Click(Sender: TObject);
begin

  PgConnection1.StartTransaction;
  try
  //====================
    Do_SommeOperations;
  //====================

    PgConnection1.Commit;
  except
    on E: Exception do
    begin
      PgConnection1.Rollback;
      showmessage(E.Message);
    end;
  end;


end;


procedure TForm1.Do_SommeOperations;
var MyQuery:TPgSQL;
    Query_Select:TPgQuery;
    MyNumber:integer;

begin

//1. Retreiving somme values
  Query_Select:=TPgQuery.Create(Nil);
  try
    Query_Select.Connection:=PgConnection1;
    Query_Select.SQL.Clear;
    Query_Select.SQL.Add('Select MyNumber From MyTable1 where (ID=100)');
    Query_Select.Open;

    if Query_Select.IsEmpty then
      MyNumber:=-1
    else
    begin
      if Not VarIsNULL(Query_Select.Fields[0].AsVariant) then
        MyNumber:=Query_Select.Fields[0].AsInteger
      else
        MyNumber:=-1;
    end;

  finally
    Query_Select.Free;
  end;






//2. inserting values
  MyQuery:=TPgSQL.Create(Nil);
  try
    MyQuery.Connection:=PgConnection1;


    MyQuery.SQL.Clear;
    With MyQuery.SQL do
    begin
       Add('Insert into MyTable2');
       Add('(');
       Add('ID,');
       Add('MyNumber');
       Add(')');

       Add(' VALUES ');

       Add('(');
       Add('1,');
       Add(':MyNumber');
       Add(')');

    end;
    MyQuery.Execute;



    With MyQuery.Params do
    begin
      ParamByName('MyNumber').DataType:=ftInteger;
      ParamByName('MyNumber').aSinteger  :=MyNumber;
    end;

    MyQuery.Execute;


  finally
    MyQuery.Free;
  end;


end;

 

Best Regards

Hi Abaksoft  Thanks    I am trying again with this good example. Really is not a simple thing.