Issue with Cloudsync compatibility

[Bucking_Horn]

I share your generic concern for not using SQL keywords like GROUP as a name, and I think the Pi-hole development team does so as well - but I also think that point has been made quite early on in this discussion.

However, I do not share your take on the custom solution you are using. (click for details)

Pi-hole itself does not claim to support syncing multiple instances, and neither does it make any assertions towards supporting those.

At the same time, it does not forbid third parties to develop a syncing solution.
Pi-hole's source code is publically available, you can build on that.

stevejenkins/pihole-cloudsync dates about two years back.
Back then, Pi-hole 4 was using pihole-FTL.db only, and gravity was a term for a collection of blocklist files. Naturally, that solution would have relied on file based operations.

gravity.db was introduced along with client-based filtering and group management some 8 months ago with Pi-hole 5 in May last year, changing file based operation into database interactions.

That allowed for a major step forward in terms of fine-grained control of DNS blocking if you care to make use of it, while it will just function as usual for those users that don't.

That major shift didn't happen in the dark:
There has been an extensive public beta-phase starting in January 2020, four months before Pi-hole 5 was rolled out.
That would have been the right time for 3rd party tool maintainers to familiarise with the changes and raise their concerns, and let me assure you that the Pi-hole development is always more than keen to listen (though it may take strong, valid arguments to convince them of your ways).

---

It is still possible to sync two Pi-hole instances with Pi-hole 5.x.

If you are not depending on pihole-cloudsync, have a look at other community solutions. e.g. GitHub - vmstan/gravity-sync: 💫 The easy way to synchronize the DNS configuration of two Pi-hole 5.x instances..

[jfb]

@dispo2

I notice that someone also edited the title of this thread.

I edited it. The problem is with your Cloud sync script and the new title more accurately reflects the topic of the thread.

[Coro]

@dispo2

I also think that the potential problems being highlighted by the foreign key errors are not new, they were always present due to the way that cloudsync works - they just werent being reported because cloudsync was using the distro sqlite not the FTL version.

Yes.

@dispo2

Many of the tables and views are interrelated.

Yes but where exactly is the problem?
Example: You want to sync the adlist table.

1. The existing and new lists are identical:
   Just don't do anything.
2. The existing and new lists are the same but some properties like the comment changed:
   Use UPDATE instead of hard flushing the table. No foreign key issues.
3. The lists have changed:
   Selectively delete the adlist that should be removed by first issuing something like DELETE FROM gravity WHERE adlist_id = <id of your list> and only then removing the adlist itselt: DELETE FROM adlist WHERE id = <id of your list>.
   You should delete the orphan entries from gravity because they are pointless (they point to no parent row as @DL6ER said). Then you add new adlists to the table.
   Whenever adlist changed, you will have to run pihole -g in any way to get a meaningful database. What I want to say is that you'd loose the corresponding gravity entries either way on the next pihole -g run when the corresponding lists are gone.
   Again, when doing it like this, no foreign key issues.

[DanSchaper]

https://www.reddit.com/r/pihole/comments/l6ok7c/if_youre_using_piholecloudsync_check_your_synced/

[DanSchaper]

Reverted topic change since it's not really working.