Database Schema

[Ken Smith]

Hi All,

I'm attempting to do a one off migration of just under 6000 entries from
an Outlook Calendar in a .pst file into OGo.

I have found that if I use the Outlook Connector and drag and drop the
entries many of them (especially the repeating ones) don't show up
properly.

I'm probably doing this the wrong way, but, I've been experimenting with
using MS Access and ODBC as a tool to get the data in to the OGo
database. But, before I do this on a 'live' system I want to check that
I'm being true to the database schema. Do I just add entries to the
public_date_x table or are there other tables that need updated as well.

Many thanks

Ken

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

--
OpenGroupware.org Users
users@...
http://mail.opengroupware.org/mailman/listinfo/users

[SebastianR]

users@... wrote:
> Hi All,
>
> I'm attempting to do a one off migration of just under 6000 entries from
> an Outlook Calendar in a .pst file into OGo.
are there attendees assigned to the calendar entries?
I don't know anything about outlook calendar.

>
> I have found that if I use the Outlook Connector and drag and drop the
> entries many of them (especially the repeating ones) don't show up
> properly.
and I cannot say much about the outlook connector too.

>
> I'm probably doing this the wrong way, but, I've been experimenting with
> using MS Access and ODBC as a tool to get the data in to the OGo
> database. But, before I do this on a 'live' system I want to check that
> I'm being true to the database schema. Do I just add entries to the
> public_date_x table or are there other tables that need updated as well.
>
there are at least the date_company_assignment and date_info tables too.

do you can create csv files out of your contacts, and appointments?
then it should be easy to write a small python script, and upload the data
via zogi. More about zogi and example scripts you can find here:
http://code.google.com/p/zogi/

Sebastian

> Many thanks
>
> Ken
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>
> --
> OpenGroupware.org Users
> users@...
> http://mail.opengroupware.org/mailman/listinfo/users
>

--
OpenGroupware.org Users
users@...
http://mail.opengroupware.org/mailman/listinfo/users

[Adam Tauno Williams]

> > I'm attempting to do a one off migration of just under 6000 entries from
> > an Outlook Calendar in a .pst file into OGo.
> are there attendees assigned to the calendar entries?
> I don't know anything about outlook calendar.

Yes, Outlook records participants.  I don't know if it *requires*
participants.
 
> > I have found that if I use the Outlook Connector and drag and drop the
> > entries many of them (especially the repeating ones) don't show up
> > properly.
> and I cannot say much about the outlook connector too.

It would be interesting to drop one of the disappearing appointments
while watching the ZideLook server log and see what you see.  But
recurrences are always a problem moving between any two kinds of servers
as they are really complicated (even if the server honors the spec to
the letter) and I don't any servers completely implement the spec.  And
does Exchange/Outlook respect the spec completely?  Probably not.

Also participants are matched by E-MAIL ADDRESS in ZideStore (this is
covered in detail in the next edition of WMOGAG) so if you don't have
participants with e-mail addresses, or with unique e-mail addresses,
data can get mix-mastered.  [ZideStore will create new place-holder
contacts for participants it can't find].  So it is advisable to move
Contact data first and then schedule data.

> > I'm probably doing this the wrong way, but, I've been experimenting with
> > using MS Access and ODBC as a tool to get the data in to the OGo

If you produce any notes/documentation on this process they would be
much appreciated so we can incorporate them into existing documentation.

Basically there is "date_x" core appointment data,
date_company_assignment is the relation between the date and the
contact/team (participants).  "date_info" holds the comment (description
of the appointment).  The "appointment_resource" table, deceptively,
isn't used for anything;  resources are stored [oddly] as a CSV value
(by name, not Id, even more oddly) in the resource_names field.

> > database. But, before I do this on a 'live' system I want to check that
> > I'm being true to the database schema. Do I just add entries to the
> > public_date_x table or are there other tables that need updated as well.
> there are at least the date_company_assignment and date_info tables too.

Yes.   But importing directly into the OGo database can be tricky;  make
sure you generate primary keys correctly (aka
"nextval('key_generator')") and fill in the object_version fields.

> do you can create csv files out of your contacts, and appointments?
> then it should be easy to write a small python script, and upload the data
> via zogi. More about zogi and example scripts you can find here:
> http://code.google.com/p/zogi/

This is the safer way to do things.  It will take care of allot of the
nitty-gritty Db stuff for you.  There is a list specifically for XML-RPC
(zOGI & legacy xmlrpcd) questions regarding either scripting or
development.
<http://mail.opengroupware.org/mailman/listinfo/xmlrpc>.   Feel free to
ask any questions if you decide to go that route.  Questions make the
archives more useful, so nothing is "too dumb".

--
OpenGroupware.org Users
users@...
http://mail.opengroupware.org/mailman/listinfo/users

[Ken Smith]

Adam Tauno Williams wrote:

>>> I'm attempting to do a one off migration of just under 6000 entries from
>>> an Outlook Calendar in a .pst file into OGo.
>>>      
>> are there attendees assigned to the calendar entries?
>> I don't know anything about outlook calendar.
>>    
>
> Yes, Outlook records participants.  I don't know if it *requires*
> participants.
>  

In this case they entries are single person appointments attended by the
'owner' of the appointment.

>  
>  
>>> I have found that if I use the Outlook Connector and drag and drop the
>>> entries many of them (especially the repeating ones) don't show up
>>> properly.
>>>      
>> and I cannot say much about the outlook connector too.
>>    
>
> It would be interesting to drop one of the disappearing appointments
> while watching the ZideLook server log and see what you see.  But
> recurrences are always a problem moving between any two kinds of servers
> as they are really complicated (even if the server honors the spec to
> the letter) and I don't any servers completely implement the spec.  And
> does Exchange/Outlook respect the spec completely?  Probably not.
>
> Also participants are matched by E-MAIL ADDRESS in ZideStore (this is
> covered in detail in the next edition of WMOGAG) so if you don't have
> participants with e-mail addresses, or with unique e-mail addresses,
> data can get mix-mastered.  [ZideStore will create new place-holder
> contacts for participants it can't find].  So it is advisable to move
> Contact data first and then schedule data.
>
>  
>>> I'm probably doing this the wrong way, but, I've been experimenting with
>>> using MS Access and ODBC as a tool to get the data in to the OGo
>>>      
>
> If you produce any notes/documentation on this process they would be
> much appreciated so we can incorporate them into existing documentation.
>
> Basically there is "date_x" core appointment data,
> date_company_assignment is the relation between the date and the
> contact/team (participants).  "date_info" holds the comment (description
> of the appointment).  The "appointment_resource" table, deceptively,
> isn't used for anything;  resources are stored [oddly] as a CSV value
> (by name, not Id, even more oddly) in the resource_names field.
>  

I've exported the data with Outlook from the .pst file into a csv file.
Using Excel I use OLE to link to another spreadsheet which is in the
same format as date_x. I then use Access/ODBC to import the date to the
OGo database. I need to manually create some data, especially for the
repeating appointments.

Fortunately there isn't any real data for the participants or the
resources but I notice that entries are created in those tables by my
test entries. that don't have participants or resources.

I can see that the public_date_info table gets updated when I create a
test appointment. Assuming that key of date_x is the first field called
date_id I can see that field (date_id) appears in the second column of
the table date_info. Again assuming that the first column of date_info
is the key I notice that numerically the key is one greater than the
value of date_id

>  
>>> database. But, before I do this on a 'live' system I want to check that
>>> I'm being true to the database schema. Do I just add entries to the
>>> public_date_x table or are there other tables that need updated as well.
>>>      
>> there are at least the date_company_assignment and date_info tables too.
>>    
>
> Yes.   But importing directly into the OGo database can be tricky;  make
> sure you generate primary keys correctly (aka
> "nextval('key_generator')") and fill in the object_version fields.
>
>  
>> do you can create csv files out of your contacts, and appointments?
>> then it should be easy to write a small python script, and upload the data
>> via zogi. More about zogi and example scripts you can find here:
>> http://code.google.com/p/zogi/
>>    
>
> This is the safer way to do things.  It will take care of allot of the
> nitty-gritty Db stuff for you.  There is a list specifically for XML-RPC
> (zOGI & legacy xmlrpcd) questions regarding either scripting or
> development.
> <http://mail.opengroupware.org/mailman/listinfo/xmlrpc>.   Feel free to
> ask any questions if you decide to go that route.  Questions make the
> archives more useful, so nothing is "too dumb".
>
>  

I know nothing about python so I will need some guidance in that area. I
have to agree that will be a safer route. I'll join that list.

Many thanks

Ken

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

--
OpenGroupware.org Users
users@...
http://mail.opengroupware.org/mailman/listinfo/users

[Adam Tauno Williams-2]

On Fri, 2008-07-04 at 22:09 +0100, Ken Smith wrote:

> Adam Tauno Williams wrote:
> >>> I'm attempting to do a one off migration of just under 6000  
> entries from >>> an Outlook Calendar in a .pst file into OGo.
> >> are there attendees assigned to the calendar entries?
> >> I don't know anything about outlook calendar.
> > Yes, Outlook records participants.  I don't know if it *requires*
> > participants.
> In this case they entries are single person appointments attended by  
> the 'owner' of the appointment.   >>> I have found that if I use the  
> Outlook Connector and drag and drop the >>> entries many of them  
> (especially the repeating ones) don't show up >>> properly.
> >> and I cannot say much about the outlook connector too.
> > It would be interesting to drop one of the disappearing appointments
> > while watching the ZideLook server log and see what you see.  But
> > recurrences are always a problem moving between any two kinds of servers
> > as they are really complicated (even if the server honors the spec to
> > the letter) and I don't any servers completely implement the spec.  And
> > does Exchange/Outlook respect the spec completely?  Probably not.
> > Also participants are matched by E-MAIL ADDRESS in ZideStore (this is
> > covered in detail in the next edition of WMOGAG) so if you don't have
> > participants with e-mail addresses, or with unique e-mail addresses,
> > data can get mix-mastered.  [ZideStore will create new place-holder
> > contacts for participants it can't find].  So it is advisable to move
> > Contact data first and then schedule data.
> >>> I'm probably doing this the wrong way, but, I've been  
> experimenting with >>> using MS Access and ODBC as a tool to get the  
> data in to the OGo > If you produce any notes/documentation on this  
> process they would be
> > much appreciated so we can incorporate them into existing documentation.
> > Basically there is "date_x" core appointment data,
> > date_company_assignment is the relation between the date and the
> > contact/team (participants).  "date_info" holds the comment (description
> > of the appointment).  The "appointment_resource" table, deceptively,
> > isn't used for anything;  resources are stored [oddly] as a CSV value
> > (by name, not Id, even more oddly) in the resource_names field.
> I've exported the data with Outlook from the .pst file into a csv  
> file. Using Excel I use OLE to link to another spreadsheet which is  
> in the same format as date_x. I then use Access/ODBC to import the  
> date to the OGo database. I need to manually create some data,  
> especially for the repeating appointments.
> Fortunately there isn't any real data for the participants or the  
> resources but I notice that entries are created in those tables by  
> my test entries. that don't have participants or resources.

Appointments must have participants;  therefore there is always at least
one record in date_company_assignment for each record in date_x.

> I can see that the public_date_info table gets updated when I create a

??? public_date_info?  There isn't any such table;  perhaps you have
create a view?

> Assuming that key of date_x is the first field called  date_id I

Yep.

> can see that field (date_id) appears in the second column of the  
> table date_info. Again assuming that the first column of date_info  
> is the key I notice that numerically the key is one greater than the  
> value of date_id

That is circumstantial. "date_company_assignment_id" is the primary key
of the table,  it must be assigned with the ?"nextval('key_generator')".
Since the participants are added immediately after the date_x record is
inserted - so they track, but only circumstantially.

> >>> database. But, before I do this on a 'live' system I want to  
> check that >>> I'm being true to the database schema. Do I just add  
> entries to the >>> public_date_x table or are there other tables  
> that need updated as well
> >> there are at least the date_company_assignment and date_info tables too.
> > Yes.   But importing directly into the OGo database can be tricky;  make
> > sure you generate primary keys correctly (aka
> > "nextval('key_generator')") and fill in the object_version fields.
> >> do you can create csv files out of your contacts, and appointments?
> >> then it should be easy to write a small python script, and upload  
> the data >> via zogi. More about zogi and example scripts you can  
> find here:
> >> http://code.google.com/p/zogi/
> > This is the safer way to do things.  It will take care of allot of the
> > nitty-gritty Db stuff for you.  There is a list specifically for XML-RPC
> > (zOGI & legacy xmlrpcd) questions regarding either scripting or
> > development.
> > <http://mail.opengroupware.org/mailman/listinfo/xmlrpc>.   Feel free to
> > ask any questions if you decide to go that route.  Questions make the
> > archives more useful, so nothing is "too dumb".
> I know nothing about python so I will need some guidance in that  
> area. I have to agree that will be a safer route. I'll join that list.

--
OpenGroupware.org Users
users@...
http://mail.opengroupware.org/mailman/listinfo/users

[Ken Smith]

Adam Tauno Williams wrote:
> On Fri, 2008-07-04 at 22:09 +0100, Ken Smith wrote:
{snip}

>> I've exported the data with Outlook from the .pst file into a csv
>> file. Using Excel I use OLE to link to another spreadsheet which is
>> in the same format as date_x. I then use Access/ODBC to import the
>> date to the OGo database. I need to manually create some data,
>> especially for the repeating appointments.
>> Fortunately there isn't any real data for the participants or the
>> resources but I notice that entries are created in those tables by my
>> test entries. that don't have participants or resources.
>
> Appointments must have participants;  therefore there is always at least
> one record in date_company_assignment for each record in date_x.
>
>> I can see that the public_date_info table gets updated when I create a
>
> ??? public_date_info?  There isn't any such table;  perhaps you have
> create a view?

Honestly, I haven't created any views, but the name is more likely
date_info. It just shows as public_date_info through odbc/MSaccess

>
>> Assuming that key of date_x is the first field called  date_id I
>
> Yep.
>
>> can see that field (date_id) appears in the second column of the
>> table date_info. Again assuming that the first column of date_info is
>> the key I notice that numerically the key is one greater than the
>> value of date_id
>
> That is circumstantial. "date_company_assignment_id" is the primary key
> of the table,  it must be assigned with the ?"nextval('key_generator')".
> Since the participants are added immediately after the date_x record is
> inserted - so they track, but only circumstantially.
>

{snip}

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

--
OpenGroupware.org Users
users@...
http://mail.opengroupware.org/mailman/listinfo/users

[Helge Hess]

On 05.07.2008, at 08:28, Ken Smith wrote:
> Honestly, I haven't created any views, but the name is more likely  
> date_info. It just shows as public_date_info through odbc/MSaccess

'public' is the SQL schema, the PostgreSQL default one (proper SQL  
name is public.date_info).

The date_info table stores the comment field of events (separate table  
for Sybase legacy reasons). There MUST be a record for each event,  
otherwise the WebUI will get problems.

Greets,
   Helge
--
Helge Hess
http://helgehess.eu/
--
OpenGroupware.org Users
users@...
http://mail.opengroupware.org/mailman/listinfo/users

[Ken Smith]

Helge Hess wrote:

> On 05.07.2008, at 08:28, Ken Smith wrote:
>> Honestly, I haven't created any views, but the name is more likely
>> date_info. It just shows as public_date_info through odbc/MSaccess
>
> 'public' is the SQL schema, the PostgreSQL default one (proper SQL
> name is public.date_info).
>
> The date_info table stores the comment field of events (separate table
> for Sybase legacy reasons). There MUST be a record for each event,
> otherwise the WebUI will get problems.

Yes - I've seen that happen


>
> Greets,
>   Helge


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

--
OpenGroupware.org Users
users@...
http://mail.opengroupware.org/mailman/listinfo/users