Re: Database Schema

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
[email protected]
http://mail.opengroupware.org/mailman/listinfo/users