[Davical-general] changing data in PostgreSQL

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

[Davical-general] changing data in PostgreSQL

David Newman
How to do batch record changes, updating multiple records in PostgreSQL
so they appear in DAViCAL?

I made updates to the database, but the changes do not show up in DAViCAL.

Details:

There are many contacts at one company that moved from, let's say, 1233
Main St. to 1234 Main St.

I did this in PostgreSQL:

update addressbook_address_adr set street_address =
replace(street_address, '1233 Main St.', '1234 Main St.');

That seems to have worked. This command:

select * from addressbook_address_adr where street_address='1233 Main St.';

returns nothing, and the same command with '1234' returns all the
updated records.

However, DAViCAL (as viewed from Apple's Contacts.app) still shows all
contacts at 1233 Main St.

Thanks in advance for troubleshooting clues.

dn


------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.clktrk
_______________________________________________
Davical-general mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/davical-general
Reply | Threaded
Open this post in threaded view
|

Re: changing data in PostgreSQL

Christoph Steidl
hi david,

there is the full .vcf-file stored in caldav_data.caldav_data
i think you will have to replace that, too.


cheers,
chris

--On Montag, Dezember 01, 2014 16:45:41 -0800 David Newman
<[hidden email]> wrote:

> How to do batch record changes, updating multiple records in PostgreSQL
> so they appear in DAViCAL?
>
> I made updates to the database, but the changes do not show up in DAViCAL.
>
> Details:
>
> There are many contacts at one company that moved from, let's say, 1233
> Main St. to 1234 Main St.
>
> I did this in PostgreSQL:
>
> update addressbook_address_adr set street_address =
> replace(street_address, '1233 Main St.', '1234 Main St.');
>
> That seems to have worked. This command:
>
> select * from addressbook_address_adr where street_address='1233 Main
> St.';
>
> returns nothing, and the same command with '1234' returns all the
> updated records.
>
> However, DAViCAL (as viewed from Apple's Contacts.app) still shows all
> contacts at 1233 Main St.
>
> Thanks in advance for troubleshooting clues.
>
> dn
>
>
> -------------------------------------------------------------------------
> ----- Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
> from Actuate! Instantly Supercharge Your Business Reports and Dashboards
> with Interactivity, Sharing, Native Excel Exports, App Integration & more
> Get technology previously reserved for billion-dollar corporations, FREE
> http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.clk
> trk _______________________________________________
> Davical-general mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/davical-general



--

Christoph Steidl
Systems Engineer

netcar24 GmbH
Technologiezentrum Koblenz
Universitätsstrasse 3
56070 Koblenz

TEL: +49 261 - 88 54 300
FAX: +49 261 - 88 54 350

[hidden email]
www.netcar24.com

An ARI Company
www.arifleet.de
www.arifleet.com


-------------------------------------------------------------------
Geschäftsführer: Christopher G. Conroy
Sitz der Gesellschaft: Koblenz
Handelsregisternummer HRB 20055, Amtsgericht Koblenz

Diese E-Mail enthält vertrauliche und/oder rechtlich geschützte
Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail
irrtümlich erhalten  haben, informieren Sie bitte sofort netcar24 und
vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte
Weitergabe dieser Mail ist nicht  gestattet.

Disclaimer: This email and any files transmitted with it are confidential
and intended solely for the use of the individual or entity to whom they
are addressed. If you are not the intended recipient, please notify
netcar24 immediately and delete this email from your system. Please note
that any views or opinions presented in this email are solely those of
the author and do not necessarily represent those of the company.
Finally, the recipient should check this email and any attachments for
the presence of viruses.
Although this message was scanned for viruses before being sent, the
company accepts no liability for any loss, damage or expense resulting
directly or indirectly from the access of this e-mail or any attached
files.
--------------------------------------------------------------------


------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.clktrk
_______________________________________________
Davical-general mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/davical-general
Reply | Threaded
Open this post in threaded view
|

Re: changing data in PostgreSQL

Lionel Elie Mamane
And probably also some kind of "last changed" date / serial / ... (if
not done automatically by a PostgreSQL trigger) so that the sync with
the client notices there is a change.

On Tue, Dec 02, 2014 at 08:59:13AM +0100, Christoph Steidl wrote:

> hi david,
>
> there is the full .vcf-file stored in caldav_data.caldav_data
> i think you will have to replace that, too.
>
>
> cheers,
> chris
>
> --On Montag, Dezember 01, 2014 16:45:41 -0800 David Newman
> <[hidden email]> wrote:
>
> > How to do batch record changes, updating multiple records in PostgreSQL
> > so they appear in DAViCAL?
> >
> > I made updates to the database, but the changes do not show up in DAViCAL.
> >
> > Details:
> >
> > There are many contacts at one company that moved from, let's say, 1233
> > Main St. to 1234 Main St.
> >
> > I did this in PostgreSQL:
> >
> > update addressbook_address_adr set street_address =
> > replace(street_address, '1233 Main St.', '1234 Main St.');
> >
> > That seems to have worked. This command:
> >
> > select * from addressbook_address_adr where street_address='1233 Main
> > St.';
> >
> > returns nothing, and the same command with '1234' returns all the
> > updated records.
> >
> > However, DAViCAL (as viewed from Apple's Contacts.app) still shows all
> > contacts at 1233 Main St.
> >
> > Thanks in advance for troubleshooting clues.
> >
> > dn
> >
> >
> > -------------------------------------------------------------------------
> > ----- Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
> > from Actuate! Instantly Supercharge Your Business Reports and Dashboards
> > with Interactivity, Sharing, Native Excel Exports, App Integration & more
> > Get technology previously reserved for billion-dollar corporations, FREE
> > http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.clk
> > trk _______________________________________________
> > Davical-general mailing list
> > [hidden email]
> > https://lists.sourceforge.net/lists/listinfo/davical-general
>
>
>

------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.clktrk
_______________________________________________
Davical-general mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/davical-general
Reply | Threaded
Open this post in threaded view
|

Re: changing data in PostgreSQL

Ján Máté-2
In reply to this post by David Newman
Hi David,

it is not as easy as it looks like. If you insert data into DAViCal then:

- the raw data is stored in the caldav_data table (caldav_data column)
- BUT DAViCal also stores additional data in the following tables:
        - calendar_*
        - addressbook_*

And even if you property insert the raw data + properly update the
remaining tables, the changes will be NOT visible for "good" clients
which use sync-collection REPORT (real synchronization instead of
manually comparing and downloading a ton of data). For these clients
for each change in caldav_data table you must perform the following:

for UPDATE operation:
        SELECT write_sync_change((SELECT collection_id FROM collection WHERE dav_name='/user/collection/'), 200, '/user/collection/resource.ics or vcf');

for INSERT operation:
        SELECT write_sync_change((SELECT collection_id FROM collection WHERE dav_name='/user/collection/'), 201, '/user/collection/resource.ics or vcf');

for DELETE operation:
        SELECT write_sync_change((SELECT collection_id FROM collection WHERE dav_name='/user/collection/')), 404, '/user/collection/resource.ics or vcf');

Alternatively you can force full synchronization in these clients (if it
is possible) or remove and re-add the CalDAV/CardDAV account ...


JM


> On 02 Dec 2014, at 01:45, David Newman <[hidden email]> wrote:
>
> How to do batch record changes, updating multiple records in PostgreSQL
> so they appear in DAViCAL?
>
> I made updates to the database, but the changes do not show up in DAViCAL.
>
> Details:
>
> There are many contacts at one company that moved from, let's say, 1233
> Main St. to 1234 Main St.
>
> I did this in PostgreSQL:
>
> update addressbook_address_adr set street_address =
> replace(street_address, '1233 Main St.', '1234 Main St.');
>
> That seems to have worked. This command:
>
> select * from addressbook_address_adr where street_address='1233 Main St.';
>
> returns nothing, and the same command with '1234' returns all the
> updated records.
>
> However, DAViCAL (as viewed from Apple's Contacts.app) still shows all
> contacts at 1233 Main St.
>
> Thanks in advance for troubleshooting clues.
>
> dn
>
>
> ------------------------------------------------------------------------------
> Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
> from Actuate! Instantly Supercharge Your Business Reports and Dashboards
> with Interactivity, Sharing, Native Excel Exports, App Integration & more
> Get technology previously reserved for billion-dollar corporations, FREE
> http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.clktrk
> _______________________________________________
> Davical-general mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/davical-general

------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.clktrk
_______________________________________________
Davical-general mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/davical-general

smime.p7s (5K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: changing data in PostgreSQL

David Newman


On 12/2/14, 6:53 AM, Ján Máté wrote:

> Hi David,
>
> it is not as easy as it looks like. If you insert data into DAViCal then:
>
> - the raw data is stored in the caldav_data table (caldav_data column)
> - BUT DAViCal also stores additional data in the following tables:
> - calendar_*
> - addressbook_*
>
> And even if you property insert the raw data + properly update the
> remaining tables, the changes will be NOT visible for "good" clients
> which use sync-collection REPORT (real synchronization instead of
> manually comparing and downloading a ton of data). For these clients
> for each change in caldav_data table you must perform the following:
>
> for UPDATE operation:
> SELECT write_sync_change((SELECT collection_id FROM collection WHERE dav_name='/user/collection/'), 200, '/user/collection/resource.ics or vcf');
>
> for INSERT operation:
> SELECT write_sync_change((SELECT collection_id FROM collection WHERE dav_name='/user/collection/'), 201, '/user/collection/resource.ics or vcf');
>
> for DELETE operation:
> SELECT write_sync_change((SELECT collection_id FROM collection WHERE dav_name='/user/collection/')), 404, '/user/collection/resource.ics or vcf');
>
> Alternatively you can force full synchronization in these clients (if it
> is possible) or remove and re-add the CalDAV/CardDAV account ...

Thanks all for your responses. This was waaaay more trouble than it was
worth. I thought I'd followed these directions exactly, but after
removing and re-adding the CardDAV account, I ended up with two sets of
addresses for the records I'd changed.

It took me less time to edit contacts manually than to try to repair the
entries in the database.

Some sort of global search/replace feature would be useful; batch
editing of records seems like a standard problem...

Thanks again.

dn


>
>
> JM
>
>
>> On 02 Dec 2014, at 01:45, David Newman <[hidden email]> wrote:
>>
>> How to do batch record changes, updating multiple records in PostgreSQL
>> so they appear in DAViCAL?
>>
>> I made updates to the database, but the changes do not show up in DAViCAL.
>>
>> Details:
>>
>> There are many contacts at one company that moved from, let's say, 1233
>> Main St. to 1234 Main St.
>>
>> I did this in PostgreSQL:
>>
>> update addressbook_address_adr set street_address =
>> replace(street_address, '1233 Main St.', '1234 Main St.');
>>
>> That seems to have worked. This command:
>>
>> select * from addressbook_address_adr where street_address='1233 Main St.';
>>
>> returns nothing, and the same command with '1234' returns all the
>> updated records.
>>
>> However, DAViCAL (as viewed from Apple's Contacts.app) still shows all
>> contacts at 1233 Main St.
>>
>> Thanks in advance for troubleshooting clues.
>>
>> dn
>>
>>
>> ------------------------------------------------------------------------------
>> Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
>> from Actuate! Instantly Supercharge Your Business Reports and Dashboards
>> with Interactivity, Sharing, Native Excel Exports, App Integration & more
>> Get technology previously reserved for billion-dollar corporations, FREE
>> http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Davical-general mailing list
>> [hidden email]
>> https://lists.sourceforge.net/lists/listinfo/davical-general
>

------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
_______________________________________________
Davical-general mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/davical-general
Reply | Threaded
Open this post in threaded view
|

Re: changing data in PostgreSQL

Josef Kufner
David Newman wrote, on 2.12.2014 23:39:
> It took me less time to edit contacts manually than to try to repair the
> entries in the database.
>
> Some sort of global search/replace feature would be useful; batch
> editing of records seems like a standard problem...

Better than touching DB directly is to use some nice CalDAV library and
connect your script as standard client. Then Davical will handle all of
it for you.


------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
_______________________________________________
Davical-general mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/davical-general

signature.asc (836 bytes) Download Attachment