DO $$
DECLARE
 version_new_ CONSTANT integer := 5;-- new version number --
 ver_ integer;
 exists_ boolean;
BEGIN
  LOCK TABLE variables;
  SELECT version_is('Ls') INTO ver_;
  IF ver_ < version_new_ THEN
    -- begin update --

  SELECT EXISTS (SELECT true FROM information_schema.tables WHERE  table_schema = 'public' AND table_name = 'object_log_hours') INTO exists_;

IF NOT exists_ THEN

-- Table: object_log_hours

-- DROP TABLE object_log_hours;

CREATE TABLE object_log_hours
(
  _id bigserial,
  _object integer NOT NULL,
  period_time timestamp with time zone NOT NULL,
  thread_name text,
  work_name text,
  total_time bigint DEFAULT 1,
  circles bigint DEFAULT 0,
  work_time bigint DEFAULT 0,
  longest_work bigint DEFAULT 0,
  log_count integer DEFAULT 1,
  CONSTRAINT object_log_hours_pkey PRIMARY KEY (_id),
  CONSTRAINT object_log_hours__object_fkey FOREIGN KEY (_object)
      REFERENCES object (_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);
ALTER TABLE object_log_hours
  OWNER TO su;
GRANT ALL ON TABLE object_log_hours TO su;
GRANT SELECT, UPDATE ON TABLE object_log_hours TO usr;

-- Index: object_log_hours__object_period_time_idx

-- DROP INDEX object_log_hours__object_period_time_idx;

CREATE INDEX object_log_hours__object_period_time_idx
  ON object_log_hours
  USING btree
  (_object, period_time);


END IF;

---------------------------------------------------------------------
-- Function: object_log_trunc_hours(integer, integer)

DROP FUNCTION IF EXISTS object_log_trunc_hours(integer, integer);

CREATE OR REPLACE FUNCTION object_log_trunc_hours(
    object_ integer,
    period_hours_ integer)
  RETURNS void AS
$BODY$
DECLARE
  end_period_time_ timestamp with time zone;
BEGIN

  end_period_time_ := date_trunc('hours', now() - interval '1 hour' * period_hours_);
  INSERT INTO object_log_hours(_object, period_time, thread_name, work_name, 
    total_time, circles, work_time, longest_work, log_count)
  (SELECT _object, date_trunc('hours', period_time) AS period_hours, thread_name, work_name, 
     SUM(total_time), SUM(circles), SUM(work_time), MAX(longest_work), COUNT(_id)
     FROM object_log 
     WHERE _object=object_ AND period_time < end_period_time_ 
     GROUP BY _object, period_hours, thread_name, work_name
     ORDER BY period_hours, thread_name, work_name
  );
  DELETE FROM object_log WHERE _object=object_ AND period_time < end_period_time_;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1000;
ALTER FUNCTION object_log_trunc_hours(integer, integer)
  OWNER TO su;


    -- end update --
    PERFORM version_set('Ls', version_new_);
  END IF;
END$$;