pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCalendar Scripts - Quite a complex one
7+ messages / 4 participants
[nested] [flat]
* Calendar Scripts - Quite a complex one
@ 2004-01-06 06:06 Kumar <sgnerd@yahoo.com.sg>
2004-01-06 18:56 ` Re: Calendar Scripts - Quite a complex one Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 7+ messages in thread
From: Kumar @ 2004-01-06 06:06 UTC (permalink / raw)
To: pgsql-sql
Dear Friends,
I am working on Postgres 7.3.4 on RH Linux . For our application, we are in a position to give calendar function (appointments and scheduling) with our application. Can somebody suggest me links or sample scripts for developing the calendar function.
It is much similar to MS outlook. Please shed some light.
Regards
Kumar
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Calendar Scripts - Quite a complex one
2004-01-06 06:06 Calendar Scripts - Quite a complex one Kumar <sgnerd@yahoo.com.sg>
@ 2004-01-06 18:56 ` Peter Eisentraut <peter_e@gmx.net>
2004-01-06 22:13 ` Re: Calendar Scripts - Quite a complex one Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 7+ messages in thread
From: Peter Eisentraut @ 2004-01-06 18:56 UTC (permalink / raw)
To: Kumar <sgnerd@yahoo.com.sg>; pgsql-sql
Kumar wrote:
> I am working on Postgres 7.3.4 on RH Linux . For our application, we
> are in a position to give calendar function (appointments and
> scheduling) with our application. Can somebody suggest me links or
> sample scripts for developing the calendar function.
You can probably lift out the complete calendar functionality from an
existing groupware solution, say, www.egroupware.org. I'm not sure
whether it's practical to do the calendar things in the database, since
you will also need a significant amount of intelligence in the client
to display reasonable calendar graphics, for instance.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Calendar Scripts - Quite a complex one
2004-01-06 06:06 Calendar Scripts - Quite a complex one Kumar <sgnerd@yahoo.com.sg>
2004-01-06 18:56 ` Re: Calendar Scripts - Quite a complex one Peter Eisentraut <peter_e@gmx.net>
@ 2004-01-06 22:13 ` Josh Berkus <josh@agliodbs.com>
2004-01-07 06:06 ` Re: Calendar Scripts - Quite a complex one Kumar <sgnerd@yahoo.com.sg>
0 siblings, 1 reply; 7+ messages in thread
From: Josh Berkus @ 2004-01-06 22:13 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; Kumar <sgnerd@yahoo.com.sg>; pgsql-sql
Peter,
> You can probably lift out the complete calendar functionality from an
> existing groupware solution, say, www.egroupware.org. I'm not sure
> whether it's practical to do the calendar things in the database, since
> you will also need a significant amount of intelligence in the client
> to display reasonable calendar graphics, for instance.
But all of the appointments, holidays, etc can and should be stored in the
database, and by using function programming one can automate generating all
of the raw data for the calendar graphics. We do this with our legal
calendaring app.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Calendar Scripts - Quite a complex one
2004-01-06 06:06 Calendar Scripts - Quite a complex one Kumar <sgnerd@yahoo.com.sg>
2004-01-06 18:56 ` Re: Calendar Scripts - Quite a complex one Peter Eisentraut <peter_e@gmx.net>
2004-01-06 22:13 ` Re: Calendar Scripts - Quite a complex one Josh Berkus <josh@agliodbs.com>
@ 2004-01-07 06:06 ` Kumar <sgnerd@yahoo.com.sg>
2004-01-07 07:49 ` Re: Calendar Scripts - Quite a complex one Chris Travers <chris@travelamericas.com>
0 siblings, 1 reply; 7+ messages in thread
From: Kumar @ 2004-01-07 06:06 UTC (permalink / raw)
To: josh@agliodbs.com; Peter Eisentraut <peter_e@gmx.net>; pgsql-sql
Hi,
The complexity comes while scheduling the appointments. Let us say, I have
scheduled so many meetings in my calendar of various schedules like daily, 3
days once, weekly, bi weekly. monthly, bi monthly, etc.
While I open the calendar for end of this year (say Dec 2004), I need to
show those meetings in my calendar, but I have data until Jan 2004.
What is the best way to show it. Populating the records from Jan 2004 to Dec
2004 in the pgsql function and display it in the calendar, or just write a
query to generate temporary records only for that Dec 2004 and not storing
them at the database.
Please shed some idea.
Regards
Kumar
----- Original Message -----
From: "Josh Berkus" <josh@agliodbs.com>
To: "Peter Eisentraut" <peter_e@gmx.net>; "Kumar" <sgnerd@yahoo.com.sg>;
"psql" <pgsql-sql@postgresql.org>
Sent: Wednesday, January 07, 2004 3:43 AM
Subject: Re: [SQL] Calendar Scripts - Quite a complex one
Peter,
> You can probably lift out the complete calendar functionality from an
> existing groupware solution, say, www.egroupware.org. I'm not sure
> whether it's practical to do the calendar things in the database, since
> you will also need a significant amount of intelligence in the client
> to display reasonable calendar graphics, for instance.
But all of the appointments, holidays, etc can and should be stored in the
database, and by using function programming one can automate generating all
of the raw data for the calendar graphics. We do this with our legal
calendaring app.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Calendar Scripts - Quite a complex one
2004-01-06 06:06 Calendar Scripts - Quite a complex one Kumar <sgnerd@yahoo.com.sg>
2004-01-06 18:56 ` Re: Calendar Scripts - Quite a complex one Peter Eisentraut <peter_e@gmx.net>
2004-01-06 22:13 ` Re: Calendar Scripts - Quite a complex one Josh Berkus <josh@agliodbs.com>
2004-01-07 06:06 ` Re: Calendar Scripts - Quite a complex one Kumar <sgnerd@yahoo.com.sg>
@ 2004-01-07 07:49 ` Chris Travers <chris@travelamericas.com>
2004-01-07 12:39 ` Re: Calendar Scripts - Quite a complex one Kumar <sgnerd@yahoo.com.sg>
0 siblings, 1 reply; 7+ messages in thread
From: Chris Travers @ 2004-01-07 07:49 UTC (permalink / raw)
To: Kumar <sgnerd@yahoo.com.sg>; josh@agliodbs.com; Peter Eisentraut <peter_e@gmx.net>; pgsql-sql
Hi all;
If I understand Kumar's post correctly, he is having some question relating
to the issue of even recurrance. I would highly suggest reading the
ICalendar RFC (RFC 2445) as it has some interesting ideas on the subject.
HERMES (my app with appointment/calendar functionality) doesn't yet support
appointment recurrance, and I have not formalized my approach to this.
However, here is the general approach I have been looking at:
1: Have a separate table of recurrance rules (1:1 with appointments) or have
a recurrance datatype.
2: Build some functions to calculate dates and times when the appointment
would recurr. You can also have a "Recur Until" field so you can limit your
searches this way.
3: Use a view to find recurring appointments on any given day.
This avoids a very nasty problem in the prepopulation approach-- that of a
cancelled recurring meeting. How do you cancel ALL appropriate instances of
the meeting while leaving those that occured in the past available for
records?
Kumar-- if you are working with PHP, I would be happy to work with you in
this endevor so that the same functionality can exist in my open source
(GPL'd) application. I think that the source for this would likely be one
of those things that might be best LGPL'd if added to my app.
Best Wishes,
Chris Travers
----- Original Message -----
From: "Kumar" <sgnerd@yahoo.com.sg>
To: <josh@agliodbs.com>; "Peter Eisentraut" <peter_e@gmx.net>; "psql"
<pgsql-sql@postgresql.org>
Sent: Wednesday, January 07, 2004 1:06 PM
Subject: Re: [SQL] Calendar Scripts - Quite a complex one
> Hi,
>
> The complexity comes while scheduling the appointments. Let us say, I have
> scheduled so many meetings in my calendar of various schedules like daily,
3
> days once, weekly, bi weekly. monthly, bi monthly, etc.
>
> While I open the calendar for end of this year (say Dec 2004), I need to
> show those meetings in my calendar, but I have data until Jan 2004.
>
> What is the best way to show it. Populating the records from Jan 2004 to
Dec
> 2004 in the pgsql function and display it in the calendar, or just write a
> query to generate temporary records only for that Dec 2004 and not storing
> them at the database.
>
> Please shed some idea.
>
> Regards
> Kumar
>
> ----- Original Message -----
> From: "Josh Berkus" <josh@agliodbs.com>
> To: "Peter Eisentraut" <peter_e@gmx.net>; "Kumar" <sgnerd@yahoo.com.sg>;
> "psql" <pgsql-sql@postgresql.org>
> Sent: Wednesday, January 07, 2004 3:43 AM
> Subject: Re: [SQL] Calendar Scripts - Quite a complex one
>
>
> Peter,
>
> > You can probably lift out the complete calendar functionality from an
> > existing groupware solution, say, www.egroupware.org. I'm not sure
> > whether it's practical to do the calendar things in the database, since
> > you will also need a significant amount of intelligence in the client
> > to display reasonable calendar graphics, for instance.
>
> But all of the appointments, holidays, etc can and should be stored in the
> database, and by using function programming one can automate generating
all
> of the raw data for the calendar graphics. We do this with our legal
> calendaring app.
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Calendar Scripts - Quite a complex one
2004-01-06 06:06 Calendar Scripts - Quite a complex one Kumar <sgnerd@yahoo.com.sg>
2004-01-06 18:56 ` Re: Calendar Scripts - Quite a complex one Peter Eisentraut <peter_e@gmx.net>
2004-01-06 22:13 ` Re: Calendar Scripts - Quite a complex one Josh Berkus <josh@agliodbs.com>
2004-01-07 06:06 ` Re: Calendar Scripts - Quite a complex one Kumar <sgnerd@yahoo.com.sg>
2004-01-07 07:49 ` Re: Calendar Scripts - Quite a complex one Chris Travers <chris@travelamericas.com>
@ 2004-01-07 12:39 ` Kumar <sgnerd@yahoo.com.sg>
2004-01-08 10:02 ` Re: Calendar Scripts - Quite a complex one Chris Travers <chris@travelamericas.com>
0 siblings, 1 reply; 7+ messages in thread
From: Kumar @ 2004-01-07 12:39 UTC (permalink / raw)
To: Chris Travers <chris@travelamericas.com>; josh@agliodbs.com; Peter Eisentraut <peter_e@gmx.net>; pgsql-sql
Hi,
yes yes. U understood in a very correct way, as i have 2 tables -
appointments and recurrences. And we are not gonna use PHP.
For future dates, I am not gonna populate, instead I am gonna check for the
recurrences tables for ever appointments and based on the conditions, I am
gonna say how many time that appointment recure in that month and the
timestamp.
To process that I have get all the appointment data and its recurrence
pattern data into the cursor. Is there a way to get the records one by one
from the cursor and calculate it patterns.
CREATE OR REPLACE FUNCTION crm.fn_calendar_daily_activities(timestamp)
RETURNS refcursor AS
'DECLARE
cal_daily_date ALIAS FOR $1;
ref REFCURSOR;
BEGIN
OPEN ref FOR
SELECT ....................
RETURN ref;
END;'
LANGUAGE 'plpgsql' VOLATILE;
How to open the cursor here so that I could check its recurrences pattern.
Please shed some light.
Regards
kumar
----- Original Message -----
From: "Chris Travers" <chris@travelamericas.com>
To: "Kumar" <sgnerd@yahoo.com.sg>; <josh@agliodbs.com>; "Peter Eisentraut"
<peter_e@gmx.net>; "psql" <pgsql-sql@postgresql.org>
Sent: Wednesday, January 07, 2004 1:19 PM
Subject: Re: [SQL] Calendar Scripts - Quite a complex one
> Hi all;
>
> If I understand Kumar's post correctly, he is having some question
relating
> to the issue of even recurrance. I would highly suggest reading the
> ICalendar RFC (RFC 2445) as it has some interesting ideas on the subject.
> HERMES (my app with appointment/calendar functionality) doesn't yet
support
> appointment recurrance, and I have not formalized my approach to this.
> However, here is the general approach I have been looking at:
>
> 1: Have a separate table of recurrance rules (1:1 with appointments) or
have
> a recurrance datatype.
>
> 2: Build some functions to calculate dates and times when the appointment
> would recurr. You can also have a "Recur Until" field so you can limit
your
> searches this way.
>
> 3: Use a view to find recurring appointments on any given day.
>
> This avoids a very nasty problem in the prepopulation approach-- that of a
> cancelled recurring meeting. How do you cancel ALL appropriate instances
of
> the meeting while leaving those that occured in the past available for
> records?
>
> Kumar-- if you are working with PHP, I would be happy to work with you in
> this endevor so that the same functionality can exist in my open source
> (GPL'd) application. I think that the source for this would likely be one
> of those things that might be best LGPL'd if added to my app.
>
> Best Wishes,
> Chris Travers
>
> ----- Original Message -----
> From: "Kumar" <sgnerd@yahoo.com.sg>
> To: <josh@agliodbs.com>; "Peter Eisentraut" <peter_e@gmx.net>; "psql"
> <pgsql-sql@postgresql.org>
> Sent: Wednesday, January 07, 2004 1:06 PM
> Subject: Re: [SQL] Calendar Scripts - Quite a complex one
>
>
> > Hi,
> >
> > The complexity comes while scheduling the appointments. Let us say, I
have
> > scheduled so many meetings in my calendar of various schedules like
daily,
> 3
> > days once, weekly, bi weekly. monthly, bi monthly, etc.
> >
> > While I open the calendar for end of this year (say Dec 2004), I need to
> > show those meetings in my calendar, but I have data until Jan 2004.
> >
> > What is the best way to show it. Populating the records from Jan 2004 to
> Dec
> > 2004 in the pgsql function and display it in the calendar, or just write
a
> > query to generate temporary records only for that Dec 2004 and not
storing
> > them at the database.
> >
> > Please shed some idea.
> >
> > Regards
> > Kumar
> >
> > ----- Original Message -----
> > From: "Josh Berkus" <josh@agliodbs.com>
> > To: "Peter Eisentraut" <peter_e@gmx.net>; "Kumar" <sgnerd@yahoo.com.sg>;
> > "psql" <pgsql-sql@postgresql.org>
> > Sent: Wednesday, January 07, 2004 3:43 AM
> > Subject: Re: [SQL] Calendar Scripts - Quite a complex one
> >
> >
> > Peter,
> >
> > > You can probably lift out the complete calendar functionality from an
> > > existing groupware solution, say, www.egroupware.org. I'm not sure
> > > whether it's practical to do the calendar things in the database,
since
> > > you will also need a significant amount of intelligence in the client
> > > to display reasonable calendar graphics, for instance.
> >
> > But all of the appointments, holidays, etc can and should be stored in
the
> > database, and by using function programming one can automate generating
> all
> > of the raw data for the calendar graphics. We do this with our legal
> > calendaring app.
> >
> > --
> > -Josh Berkus
> > Aglio Database Solutions
> > San Francisco
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
> >
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Calendar Scripts - Quite a complex one
2004-01-06 06:06 Calendar Scripts - Quite a complex one Kumar <sgnerd@yahoo.com.sg>
2004-01-06 18:56 ` Re: Calendar Scripts - Quite a complex one Peter Eisentraut <peter_e@gmx.net>
2004-01-06 22:13 ` Re: Calendar Scripts - Quite a complex one Josh Berkus <josh@agliodbs.com>
2004-01-07 06:06 ` Re: Calendar Scripts - Quite a complex one Kumar <sgnerd@yahoo.com.sg>
2004-01-07 07:49 ` Re: Calendar Scripts - Quite a complex one Chris Travers <chris@travelamericas.com>
2004-01-07 12:39 ` Re: Calendar Scripts - Quite a complex one Kumar <sgnerd@yahoo.com.sg>
@ 2004-01-08 10:02 ` Chris Travers <chris@travelamericas.com>
0 siblings, 0 replies; 7+ messages in thread
From: Chris Travers @ 2004-01-08 10:02 UTC (permalink / raw)
To: Kumar <sgnerd@yahoo.com.sg>; josh@agliodbs.com; Peter Eisentraut <peter_e@gmx.net>; pgsql-sql
Hi Kumar and others;
I have never worked with functions to return references to cursors. Is
there a reason why it has to be done this way rather than returning a setof
appointments?
In that case:
create function app_today returns setof appointment (date) as '
declare
new_appoint appointment;
appoint_recur recurrance
begin
for appointment in [SELECT query]
loop
-- do calculations
if [condition] then
return next;
end if;
end loop;
end;
' language plpgsql;
Note that the function is off my head and not even guaranteed to be exactly
what you need.
Best Wishes,
Chris Travers
----- Original Message -----
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "Chris Travers" <chris@travelamericas.com>; <josh@agliodbs.com>; "Peter
Eisentraut" <peter_e@gmx.net>; "psql" <pgsql-sql@postgresql.org>
Sent: Wednesday, January 07, 2004 7:39 PM
Subject: Re: [SQL] Calendar Scripts - Quite a complex one
> Hi,
> yes yes. U understood in a very correct way, as i have 2 tables -
> appointments and recurrences. And we are not gonna use PHP.
>
> For future dates, I am not gonna populate, instead I am gonna check for
the
> recurrences tables for ever appointments and based on the conditions, I am
> gonna say how many time that appointment recure in that month and the
> timestamp.
>
> To process that I have get all the appointment data and its recurrence
> pattern data into the cursor. Is there a way to get the records one by one
> from the cursor and calculate it patterns.
>
> CREATE OR REPLACE FUNCTION crm.fn_calendar_daily_activities(timestamp)
> RETURNS refcursor AS
> 'DECLARE
> cal_daily_date ALIAS FOR $1;
> ref REFCURSOR;
>
> BEGIN
> OPEN ref FOR
> SELECT ....................
>
> RETURN ref;
>
> END;'
> LANGUAGE 'plpgsql' VOLATILE;
>
> How to open the cursor here so that I could check its recurrences
pattern.
>
> Please shed some light.
>
> Regards
> kumar
>
> ----- Original Message -----
> From: "Chris Travers" <chris@travelamericas.com>
> To: "Kumar" <sgnerd@yahoo.com.sg>; <josh@agliodbs.com>; "Peter Eisentraut"
> <peter_e@gmx.net>; "psql" <pgsql-sql@postgresql.org>
> Sent: Wednesday, January 07, 2004 1:19 PM
> Subject: Re: [SQL] Calendar Scripts - Quite a complex one
>
>
> > Hi all;
> >
> > If I understand Kumar's post correctly, he is having some question
> relating
> > to the issue of even recurrance. I would highly suggest reading the
> > ICalendar RFC (RFC 2445) as it has some interesting ideas on the
subject.
> > HERMES (my app with appointment/calendar functionality) doesn't yet
> support
> > appointment recurrance, and I have not formalized my approach to this.
> > However, here is the general approach I have been looking at:
> >
> > 1: Have a separate table of recurrance rules (1:1 with appointments) or
> have
> > a recurrance datatype.
> >
> > 2: Build some functions to calculate dates and times when the
appointment
> > would recurr. You can also have a "Recur Until" field so you can limit
> your
> > searches this way.
> >
> > 3: Use a view to find recurring appointments on any given day.
> >
> > This avoids a very nasty problem in the prepopulation approach-- that of
a
> > cancelled recurring meeting. How do you cancel ALL appropriate
instances
> of
> > the meeting while leaving those that occured in the past available for
> > records?
> >
> > Kumar-- if you are working with PHP, I would be happy to work with you
in
> > this endevor so that the same functionality can exist in my open source
> > (GPL'd) application. I think that the source for this would likely be
one
> > of those things that might be best LGPL'd if added to my app.
> >
> > Best Wishes,
> > Chris Travers
> >
> > ----- Original Message -----
> > From: "Kumar" <sgnerd@yahoo.com.sg>
> > To: <josh@agliodbs.com>; "Peter Eisentraut" <peter_e@gmx.net>; "psql"
> > <pgsql-sql@postgresql.org>
> > Sent: Wednesday, January 07, 2004 1:06 PM
> > Subject: Re: [SQL] Calendar Scripts - Quite a complex one
> >
> >
> > > Hi,
> > >
> > > The complexity comes while scheduling the appointments. Let us say, I
> have
> > > scheduled so many meetings in my calendar of various schedules like
> daily,
> > 3
> > > days once, weekly, bi weekly. monthly, bi monthly, etc.
> > >
> > > While I open the calendar for end of this year (say Dec 2004), I need
to
> > > show those meetings in my calendar, but I have data until Jan 2004.
> > >
> > > What is the best way to show it. Populating the records from Jan 2004
to
> > Dec
> > > 2004 in the pgsql function and display it in the calendar, or just
write
> a
> > > query to generate temporary records only for that Dec 2004 and not
> storing
> > > them at the database.
> > >
> > > Please shed some idea.
> > >
> > > Regards
> > > Kumar
> > >
> > > ----- Original Message -----
> > > From: "Josh Berkus" <josh@agliodbs.com>
> > > To: "Peter Eisentraut" <peter_e@gmx.net>; "Kumar"
<sgnerd@yahoo.com.sg>;
> > > "psql" <pgsql-sql@postgresql.org>
> > > Sent: Wednesday, January 07, 2004 3:43 AM
> > > Subject: Re: [SQL] Calendar Scripts - Quite a complex one
> > >
> > >
> > > Peter,
> > >
> > > > You can probably lift out the complete calendar functionality from
an
> > > > existing groupware solution, say, www.egroupware.org. I'm not sure
> > > > whether it's practical to do the calendar things in the database,
> since
> > > > you will also need a significant amount of intelligence in the
client
> > > > to display reasonable calendar graphics, for instance.
> > >
> > > But all of the appointments, holidays, etc can and should be stored in
> the
> > > database, and by using function programming one can automate
generating
> > all
> > > of the raw data for the calendar graphics. We do this with our legal
> > > calendaring app.
> > >
> > > --
> > > -Josh Berkus
> > > Aglio Database Solutions
> > > San Francisco
> > >
> > >
> > > ---------------------------(end of
broadcast)---------------------------
> > > TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org
> > >
> > >
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2004-01-08 10:02 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-06 06:06 Calendar Scripts - Quite a complex one Kumar <sgnerd@yahoo.com.sg>
2004-01-06 18:56 ` Peter Eisentraut <peter_e@gmx.net>
2004-01-06 22:13 ` Josh Berkus <josh@agliodbs.com>
2004-01-07 06:06 ` Kumar <sgnerd@yahoo.com.sg>
2004-01-07 07:49 ` Chris Travers <chris@travelamericas.com>
2004-01-07 12:39 ` Kumar <sgnerd@yahoo.com.sg>
2004-01-08 10:02 ` Chris Travers <chris@travelamericas.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox