LiveCode Forums.

Code: Select all

   get libURLFormData("MODE_DEVELOPMENT", k_status_mode_development, "TABLE", t_table_name, "NAME_TABLE_KP",  t_sync_table_kp_name, "DATA_PUSHED", t_sync_table_data_push, "COLUMN_NAMES", t_sync_column_names, "KF_UUID_LAST_PERSON_TO_PUSH_REC", k_kp_uuid_person)
         
         post it to URL "https://www.examplehost.livecodehosting.com/sync/sync_receive_and_process_pushed_data.lc"
         

Code: Select all

set the socketTimeoutInterval to 100

Code: Select all

libURLSetFTPStopTime 30 -- 30-second timeout

Code: Select all

 get libURLFormData("MODE_DEVELOPMENT", k_status_mode_development, "TABLE", t_table_name, "NAME_TABLE_KP",  t_sync_table_kp_name, "DATA_PUSHED", t_sync_table_data_push, "COLUMN_NAMES", t_sync_column_names, "KF_UUID_LAST_PERSON_TO_PUSH_REC", k_kp_uuid_person)
 
         set the socketTimeoutInterval to 300000
         
         post it to URL "https://www.exampleserver.livecodehosting.com/sync/sync_receive_and_process_pushed_data.lc"
         

Code: Select all

<?lc
-- Created: 2019 09 05
-- Created By: HP
-- Modified: 2020 06 15
-- Modified By: HP

----------------------------------
--during sync receives data pushed by user and processes it
----------------------------------
local t_id_connection_hosted

local t_sync_column_names
local t_line_counter
local t_sync_table_data_push_sql_format
local t_values
local t_table_name

local t_kp_pushed
local t_sync_table_kp_name

--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------

    -----
     --Unpackages data that was posted from client to cloud
     put $_POST["MODE_DEVELOPMENT"] into t_status_mode_development
     put $_POST["TABLE"] into t_table_name
     put $_POST["NAME_TABLE_KP"] into t_sync_table_kp_name
     put $_POST["DATA_PUSHED"] into t_sync_table_data_push
     put $_POST["COLUMN_NAMES"] into t_sync_column_names
     put $_POST["KF_UUID_LAST_PERSON_TO_PUSH_REC"] into t_sync_kf_uuid_person_to_last_push_rec
    -----

     --prior to sending we replaced any returns in the post statement with "¶~PREPOSTRETURN~¶"...
     --..so we now need to restablish the returns
     replace "*~PREPOSTRETURN~*" with return in t_table_name
     replace "*~PREPOSTRETURN~*" with return in t_sync_table_kp_name
     replace "*~PREPOSTRETURN~*" with return in t_sync_table_data_push
     replace "*~PREPOSTRETURN~*" with return in t_sync_column_names
     replace "*~PREPOSTRETURN~*" with return in t_sync_kf_uuid_person_to_last_push_rec



     put the number of lines in t_sync_table_data_push into t_data_received_number_of_lines --the number of lines of data received




     -- we have
     --t_table_name              name of table in question
     --t_sync_table_kp_name      name of the local table kp
     --t_sync_table_data_push    as ~~~ separated
     --t_sync_column_names       as ~~~ separated

     set the itemdelimiter to "~~~"

     ------------------------------------
     --process t_sync_table_data_push to put it into an sql_insert format
     put t_sync_table_data_push into t_sync_table_data_push_for_sql_formatting --puts it into a new variable so we can manipulate it and still have access to the orig data if necessary

     put "" into t_counter
     put "" into t_sync_table_data_push_sql_format
     repeat for t_data_received_number_of_lines
        put t_counter + "1" into t_counter

        --this deals with if the first two item is blank
        if char "1" to "6" in line t_counter of t_sync_table_data_push_for_sql_formatting = "~~~~~~" then
           delete char "1" to "3" in line t_counter of t_sync_table_data_push_for_sql_formatting
       end if


        --this deals with if the last item is blank
        if char "-6" to "-1" in line t_counter of t_sync_table_data_push_for_sql_formatting = "~~~~~~" then
           delete char "-3" to "-1" in line t_counter of t_sync_table_data_push_for_sql_formatting
       end if




        replace "~~~" with "', '" in line t_counter of t_sync_table_data_push_for_sql_formatting



        put t_sync_table_data_push_sql_format & "'" & line t_counter of t_sync_table_data_push_for_sql_formatting & "'" & CR into t_sync_table_data_push_sql_format

       -----
        --formats blank values to null
        replace "''" with "null" in t_sync_table_data_push_sql_format
        replace "' '" with "null" in t_sync_table_data_push_sql_format
        -----

     end repeat
     delete char "-1" of t_sync_table_data_push_sql_format --gets rid of CR at end


     ------------------------------------




     --find out which item number the kp is in t_sync_column_names
     put "" into t_counter
     repeat for the number of items in t_sync_column_names
        put t_counter + "1" into t_counter
        if item t_counter of t_sync_column_names = t_sync_table_kp_name then
           put t_counter into t_item_number_of_kp
           exit repeat
        end if
     end repeat



     --we now know what item number the kp is in each row of t_sync_table_data_push...
     --..this is stored in t_item_number_of_kp



     --find out which item number the timestamp_mod_utc is in t_sync_column_names
     put "" into t_counter
     repeat for the number of items in t_sync_column_names
        put t_counter + "1" into t_counter
        if item t_counter of t_sync_column_names = "timestamp_mod_utc" then
           put t_counter into t_item_number_of_timestamp_mod_utc
           exit repeat
        end if
     end repeat



     --we now know what item number the kp is in each row of t_sync_table_data_push...
     --..this is stored in t_item_number_of_kp

     --we now know what item number the timestamp_mod_utc is in each row of t_sync_table_data_push...
     --..this is stored in t_item_number_of_timestamp_mod_utc






     --now we need to cycle through each line of the t_sync_table_data_push to get the kp and...
     --..the timestamp_mod_utc for use in a where statement

     put "" into t_line_counter
     repeat for t_data_received_number_of_lines
        put t_line_counter + "1" into t_line_counter

        put item t_item_number_of_kp of line t_line_counter of t_sync_table_data_push into t_kp_pushed
        put item t_item_number_of_timestamp_mod_utc of line t_line_counter of t_sync_table_data_push into t_timestamp_mod_utc_pushed

        --now select the 'kp' & 'timestamp_mod_utc' from the cloud for the rec with the same t_kp_pushed
        put "SELECT " & t_sync_table_kp_name & ", timestamp_mod_utc" & \
              " FROM " & t_table_name  & \
              " WHERE " & t_sync_table_kp_name & " = '" & t_kp_pushed & "'" & \
              "" into t_sql





              -----
              --opens the connection to the database
              include "/home/exampleserver/public_html/connect/connect_db_hosted.lc"
              --nb the above puts the connection id into t_id_connection_hosted
              -----



        ----opens the connection to the database
       -- put revOpenDatabase("mysql","exampleserver.livecodehosting.com","exampleserver","exampleserver”,”***”) into t_id_connection_hosted

        if t_id_connection_hosted is a number then



           put "~~~" into t_itemdelimiter

           put revDataFromQuery(t_itemdelimiter,return,t_id_connection_hosted,t_sql) into t_cloud_data
        end if



        if t_cloud_data is not "" then
           put item "1" of t_cloud_data into t_kp_cloud --if this has a value we know the cloud based record exists
           put item "2" of t_cloud_data into t_timestamp_mod_utc_cloud

        else if t_cloud_data is "" then
           put "" into t_kp_cloud --if this has not got a value we know the cloud based record does not exist
           put "" into t_timestamp_mod_utc_cloud
        end if

        --we now have the t_timestamp_mod_utc of the cloud based record

        --we now need to delete the cloud based rec if the t_timestamp_mod_utc is less...
        --..than the t_timestamp_mod_utc of the rec being pushed

        if t_kp_cloud is not empty then --if this has a value we know the cloud based record exists

           if t_timestamp_mod_utc_cloud < t_timestamp_mod_utc_pushed then --if this is the case then we...
              --.. know that the rec in the cloud is older than the rec being pushed

              --because the rec in the cloud is older we are going to delete it
              put "DELETE FROM " & t_table_name & \
                    " WHERE " & t_sync_table_kp_name & " = '" & t_kp_pushed & "'" & \
                    "" into t_sql

              revexecutesql t_id_connection_hosted,t_sql

              put the result into t_num_recs_deleted --returns the number of recs deleted or an error message



              insert_and_update_pushed_record_into_cloud



              -- if the rec in the cloud is Newer than the rec being pushed then we want to keep the...
              --..rec in the cloud and not replace it with the rec being pushed

           else if t_timestamp_mod_utc_cloud > t_timestamp_mod_utc_pushed then --if the rec in the cloud is newer
              --do nothing

           end if --end of actions relating to whether rec in cloud is older or newer than rec being pushed

        end if --end of if t_kp_cloud is not empty then



        if t_kp_cloud is empty then --we know that a cloud based version of this record does not exist...
           --..so we need to insert the push record into the cloud

           insert_and_update_pushed_record_into_cloud

        end if --end of if t_kp_cloud is empty



revclosedatabase t_id_connection_hosted



     end repeat



     --revclosedatabase t_id_connection_hosted

     put "data_received_and_processed_by_cloud"





--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--START COMMANDS
--------------------------------------------------------------------------
--------------------------------------------------------------------------

on insert_and_update_pushed_record_into_cloud
   -------------------------------------
   --now need to insert the pushed data into the cloud

   replace "~~~" with ", " in t_sync_column_names

   put line t_line_counter of t_sync_table_data_push_sql_format into t_values


   put "INSERT INTO " & t_table_name & \
         " (" & t_sync_column_names & ")" & \
         " VALUES (" & t_values & ")" & \
         "" into t_sql
   revexecutesql t_id_connection_hosted,t_sql

   put the result into t_num_recs_added


   -- this now updates the 'timestamp_utc_sync_modified_in_cloud' of the rec that was just...
   --.. added to the cloud, so we can tell at a later date when the rec was received by the cloud...
   --.. also updates the
   put the seconds into t_timestamp_utc_sync_modified_in_cloud

   put "UPDATE " & t_table_name & " " & \
         " SET timestamp_utc_sync_modified_in_cloud =" & \
         "'" & t_timestamp_utc_sync_modified_in_cloud & "', " & \
         "sync_kf_uuid_person_to_last_push_rec =" & \
         "'" & t_sync_kf_uuid_person_to_last_push_rec & "'" & \
         " WHERE " & t_sync_table_kp_name & " = '" & t_kp_pushed & "'" & \
         "" into t_update_statement

   revexecutesql t_id_connection_hosted, t_update_statement

   return the result --the number of recs updated


   -------------------------------------
end insert_and_update_pushed_record_into_cloud

--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--END COMMANDS
--------------------------------------------------------------------------
--------------------------------------------------------------------------
?>