GcalSync - need better understanding, maybe improvements ?

lcollong

lcollong

FabriKant d'applications web
Hi,

I'm using gcal sync to synchronize 3 different Google agenda. Actually the "events table" is a repeat group of a main table/form. The underlying "xxx_repeat_34" table contains most of the info needed to synchronize. One of the columns is to choose the right calendar. Then, I have setup 3 lists based on the same underlying table with a prefilter on the calendar info. Thus each of these list can be synchronized with the right agenda even if all the events are created from the same form.

It's working pretty well however I have some questions :

1/ The description field is a common field. Thus each of the 3 agenda related list is joined back to the main table on "parent_id" in order to display the "description" which is belonging to the main table not to the repeat group. Fabrik shows that very well. However, in the gcalsync cron plugin, I can select and choose the joined column but it is not updated. Looking to the source around lines 100, it seems that we read the main table itself, not the usual fabrik structure including the joined columns. Would it be possible to grab also the joined columns around line 100 ?

2/ Even it you setup the plugin to synchronize "to" Google, ie upload fabrik events to Google only, it creates rows for existing events in Gcal. Looking at the source, it seems that we store a gcalid in our table each time we synchronize an event. Fair to not synchronize again something which is already up to date. But if one creates an event directly in the Google UI, we should not see it in the fabrik table. This should be only done if "both" of "from" is chosen in the params. In the "to" case, the Google Events not existing in the fabrik table should not be synchronized (also I don't understand why it's doing that as the source code shows an "update", not an "insert"...).

3/ If one of my field is a DBjoin one (title), it's using the raw value (key) instead of the label. Thus Google Calendar shows "4" instead of "Title of the event".

4/ Would be nice to be able to synchronize the place of the event also.

5/ Still something wrong with tz as per this thread : http://fabrikar.com/forums/index.php?threads/gcalsync-setoffset-deprecated-in-joomla-3.36030/
 
I've built what I think is your structure to test with, but I'm a little confused.

Do you have your gcalsync cron plugins running on the main lists (the three copies with the pre-filters), or have you created separate Fabrik lists on the 'xxx_YY_repeat' table?

It's this description which confuses me:

1/ The description field is a common field. Thus each of the 3 agenda related list is joined back to the main table on "parent_id" in order to display the "description" which is belonging to the main table not to the repeat group. Fabrik shows that very well. However, in the gcalsync cron plugin, I can select and choose the joined column but it is not updated. Looking to the source around lines 100, it seems that we read the main table itself, not the usual fabrik structure including the joined columns. Would it be possible to grab also the joined columns around line 100 ?
Click to expand...

... which makes it a bit ambiguous as to which table the sync is actually set to run on.

--hugh
 
BTW, I logged in to both your My Sites, to see if I could find this stuff, but they are both 3.0, with no gcal crons.

-- hugh
 
Hmmm, now it's coming back to me why I didn't handle joined data in the first cut of this.

It's OK writing rows which involve joins to Google, but trying to write GCal events back to the Fabrik table is pretty much not possible, as we just dont have the FK (Foreign Key) data we need. So for example, as you pointed out in #3 above, we don't need to be writing the key value of a join out to GCal, we would need to write the label. But that means, when we read events in from GCal, we'd reading the label. We wouldn't have the key. Which is what we would need, in order to write that data back out to the Fabrik table, when we sync'ed from GCal. This is problematic for simple join elements, and for joined tables, as we don't have any kind of FK data at all - we're reading a single row back from GCal, and have no idea how that correlates to two rows in two tables in Fabrik.

What the gcalsync code does at the moment is basically ignores the Fabrik-style "list data"the main cron model hands it, which is formatted with Fabrik element naming standards, with joined data having different prefixes, etc. Instead, the plugin does a "dumb" read of "SELECT * FROM main_table_name", and just uses whatever it gets from that. It knows nothing about joins, it just expects everything to be simple, textual data. And if it is asked to sync from GCal, it just does a "dumb" row store of whatever it gets back from GCal, using the "short" element names, to just the main table.

So basically, as-is, the plugin just doesn't handle joined data, period. It expects your events table to be a simple, flat table with no joins, and anything you specify for writing to GCal (label, description) has to be a simple text field.

So to accommodate what you want to do, I'll have to add a setting in the plugin to "Use raw table data" (or some such). If that is set to Yes, then it'll act like it does at the moment - expect a simple, non-joined table, and do it's own "dumb" read of all records from it, and treat it as straight textual data, which the plugin can read and write, i.e. sync both ways. This is needed to maintain backward compat with any existing usage, and to allow for reading of simple GCal data back in to Fabrik tables.

If this option is No, then the plugin will use the "Fabrik List" formatted data, which will allow joins, as we'll have the usual tablename___elementname and tablename___elementname_raw arrangement, and can use the "non raw" value (which for a join, or a dropdown, or a checkbox, etc. will be the label)... BUT ... it won't be able to sync from GCal.

I think this will work for you, as you seem to be saying you only need to syn to GCal, not back form it, right?

-- hugh
 
Hi Hugh,

Thanks for working on this one and for the detailed explanations. Maybe you should paste part of your text to the wiki ?

The FK problem in the situation of "from" sync definitively make sense. Your solution sounds perfectly fit the need of those who want only a "to" sync as I need. However I feel uncomfortable with the way the plugin may react in case a user create an event in the Google UI itself as per my point #2 in the first post.

To answer to the others questions :

Yes, I built 3 list pointing on the same "repeat" table which are prefiltred and used to sync. Thus I have 3 cron jobs running on each of these "views". Consequently, one of the fields to sync (the shared one : description) is coming from the join to the master table through parent_id which is set up on each of these lists. This is working fine except for the element "description" coming from the joined table.

I didn't fill in "mySites" for this one as it is a preprod customer site. I have to ask authorization before doing that. Indeed it is a J!3.1.5 site with a full F3.1 installation updated 5-6 weeks ago. If you need to have access I'll manage to add it in mySites.

A bient?t !
 
OK, thanks for the explanation.

I've fixed issue #2, that was just some sloppy bad coding on my part, it should never have been creating local rows if "From" or "Both" was not selected for sync'ing.

So I'm almost there, I've got it working with joined data for syncin "To", I just need to tidy up some code, and put some defensive coding in to prevent attempts at sync'ing "From" when there are joins involved.

-- hugh
 
OK ... try now. I think I finally got it at least half assed functioning now, in both directions.

Something I've also fixed is that we should now properly update the local table with the gcal ID, when we sync an event to Google. I must have been on drugs or something when I first wrote that code, as it was never going to work the way it was written. Well, only in certain cases, anyway.

-- hugh
 
Ooops ! I missed your message. Sorry for the late answer. I'll do some tests ASAP and keep you inform.
 
Hi Hugh,

I have made intensive test but only in "my" configuration. (sync direction set to "To GCal").

It works well as the TZ seems to be correct, the dbjoin element is now displaying the joined value (great !) and the all the data are sent as expected. However I still don't understand why it is inserting rows from the Google setup in our DB each time the cron is run ? The setup is "to" gcal. My own database should never being altered (except for storing the gcalid), isnt'it ?

Does the script intend to resynchronise everything each time ? I thought it was only able to create an entry in gcal for each fabrik record without gcalid ?

I was missing the "place" field in my application so I have added it. It allows to show the map in the GCalendar Joomla component by G4J Project. See screenshots . You'll find herein the modified files as I did not want to use github since I'm far away to be sure this enough for all situations.... If you agree to have a look and push this in the github, it may help someone else ?...

I'm thinking of a way to manage update, in order to update gcal from a modified fabrik record. My idea would be to add a flag element which could be 1 or 0. 0 means don't create nor update. And 1 mean update (if gcalid is filled) or create it (if empty). The flag would be resetted by the script. It is easy to manage the flag from the fabrik form each time the it is updated. What do you think ?
 

Attachments

  • Capture.JPG
    Capture.JPG
    85.6 KB · Views: 262
  • Capture2.JPG
    Capture2.JPG
    50.2 KB · Views: 232
  • gcalsync.zip
    6.2 KB · Views: 193
friendly bump. As per another thread, each time it syncs something, it adds rows in Fabrik table for previously existing gcal entries.
 
You must log in or register to reply here.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    Total: 27 (members: 2, guests: 25)
    Back
    Top