Talk:Fgdb tables

From FreekiWiki
Jump to navigation Jump to search

Useful SQL snippets

Here is where to put things that might be useful to cut and paste into scripts that could be modified as needed for ad hoc reporting.

Count of volunteers worked over time

SELECT
   EXTRACT( YEAR FROM date_performed ) AS year,
   EXTRACT( MONTH FROM date_performed ) AS month,
   COUNT( DISTINCT volunteer_tasks.contact_id ) AS count
 FROM volunteer_tasks
 WHERE
   volunteer_tasks.duration > 0
 GROUP BY 1, 2
 ORDER BY 1, 2 ;
  • same but for a given period of time
SELECT
   COUNT( DISTINCT volunteer_tasks.contact_id ) AS count
 FROM volunteer_tasks
 WHERE
   volunteer_tasks.duration > 0 AND
   volunteer_tasks.date_performed BETWEEN '2007-01-01' AND now()::date ;


Amount of volunteer hours worked over time

SELECT
   EXTRACT( YEAR FROM date_performed ) AS year,
   EXTRACT( MONTH FROM date_performed ) AS month,
   SUM(volunteer_tasks.duration) AS hours_worked
 FROM volunteer_tasks
 WHERE
   volunteer_tasks.duration > 0
 GROUP BY 1, 2
 ORDER BY 1, 2 ;

All volunteers that have accumulated more than X hours

SELECT
   volunteer_tasks.contact_id,
   contacts.first_name,
   contacts.surname,
   SUM(volunteer_tasks.duration) AS hours_worked,
   MAX( volunteer_tasks.date_performed )
 FROM volunteer_tasks
 LEFT JOIN contacts ON volunteer_tasks.contact_id = contacts.id
 WHERE
   volunteer_tasks.duration > 0
 GROUP BY
   volunteer_tasks.contact_id,
   contacts.first_name,
   contacts.surname
 HAVING SUM(volunteer_tasks.duration) > 1000
 ORDER BY 4 DESC ;

Still needed

  • Number of computer received in a given month (or another specific time period)
    • same but over time, each month or each year
  • Amount of volunteer hours worked by job in a given month (or another specific time period)
    • same but over time, each month or each year

Basic SQL for table creation

CREATE TABLE contact_method_types (
    id serial NOT NULL,
    description character varying(100),
    parent_id integer,
    lock_version integer DEFAULT 0 NOT NULL,
    updated_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now(),
    created_by bigint DEFAULT 1 NOT NULL,
    updated_by bigint DEFAULT 1 NOT NULL
);

COMMENT ON TABLE contact_method_types IS 'types of ways we can contact someone, i.e. phone, email website, online chat name -- not physical address';

CREATE TABLE contact_methods (
    id serial NOT NULL,
    contact_method_type_id integer,
    description character varying(100),
    ok boolean,
    contact_id integer,
    lock_version integer DEFAULT 0 NOT NULL,
    updated_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now(),
    created_by bigint DEFAULT 1 NOT NULL,
    updated_by bigint DEFAULT 1 NOT NULL
);

COMMENT ON TABLE contact_methods IS 'actual ways a specific contact can be contacted (i.e. phone, email, etc.) note: not physical address';

CREATE TABLE contact_types (
    id serial NOT NULL,
    description character varying(100),
    for_who character varying(3) DEFAULT 'any'::character varying,
    lock_version integer DEFAULT 0 NOT NULL,
    updated_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now(),
    created_by bigint DEFAULT 1 NOT NULL,
    updated_by bigint DEFAULT 1 NOT NULL
);

COMMENT ON TABLE contact_types IS 'types of contacts we track, for instance media contact, member, organization';

CREATE TABLE contact_types_contacts (
    contact_id integer DEFAULT 0 NOT NULL,
    contact_type_id integer DEFAULT 0 NOT NULL
);

CREATE TABLE contacts (
    id serial NOT NULL,
    is_organization boolean DEFAULT false,
    sort_name character varying(25),
    first_name character varying(25),
    middle_name character varying(25),
    surname character varying(50),
    organization character varying(100),
    extra_address character varying(52),
    address character varying(52),
    city character varying(30),
    state_or_province character varying(15),
    postal_code character varying(25),
    country character varying(100),
    notes text,
    lock_version integer DEFAULT 0 NOT NULL,
    updated_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now(),
    created_by bigint DEFAULT 1 NOT NULL,
    updated_by bigint DEFAULT 1 NOT NULL
);

CREATE TABLE "defaults" (
    id serial NOT NULL,
    name character varying(100),
    value character varying(100),
    lock_version integer DEFAULT 0 NOT NULL,
    updated_at timestamp without time zone,
    created_at timestamp without time zone,
    created_by integer DEFAULT 1 NOT NULL,
    updated_by integer DEFAULT 1 NOT NULL
);

CREATE TABLE discount_schedules (
    id serial NOT NULL,
    name character varying(25),
    lock_version integer DEFAULT 0 NOT NULL,
    updated_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now(),
    created_by bigint DEFAULT 1 NOT NULL,
    updated_by bigint DEFAULT 1 NOT NULL
);

COMMENT ON TABLE discount_schedules IS 'discount schedules';

CREATE TABLE discount_schedules_gizmo_types (
    id serial NOT NULL,
    gizmo_type_id integer NOT NULL,
    discount_schedule_id integer NOT NULL,
    multiplier numeric(10,3),
    lock_version integer DEFAULT 0 NOT NULL,
    updated_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now(),
    created_by bigint DEFAULT 1 NOT NULL,
    updated_by bigint DEFAULT 1 NOT NULL
);

CREATE TABLE dispersement_types (
    id serial NOT NULL,
    description character varying(100),
    lock_version integer DEFAULT 0 NOT NULL,
    updated_at timestamp without time zone,
    created_at timestamp without time zone,
    created_by integer DEFAULT 1 NOT NULL,
    updated_by integer DEFAULT 1 NOT NULL
);

CREATE TABLE dispersements (
    id serial NOT NULL,
    comments text,
    contact_id integer NOT NULL,
    dispersement_type_id integer NOT NULL,
    lock_version integer DEFAULT 0 NOT NULL,
    updated_at timestamp without time zone,
    created_at timestamp without time zone,
    created_by integer DEFAULT 1 NOT NULL,
    updated_by integer DEFAULT 1 NOT NULL,
    dispersed_at timestamp without time zone NOT NULL
);

CREATE TABLE donations (
    id serial NOT NULL,
    contact_id integer,
    postal_code character varying(25),
    reported_required_fee numeric(10,2) DEFAULT 0.0,
    reported_suggested_fee numeric(10,2) DEFAULT 0.0,
    txn_complete boolean DEFAULT true,
    txn_completed_at timestamp with time zone DEFAULT now(),
    comments text,
    lock_version integer DEFAULT 0 NOT NULL,
    updated_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now(),
    created_by bigint DEFAULT 1 NOT NULL,
    updated_by bigint DEFAULT 1 NOT NULL
);

CREATE TABLE engine_schema_info (
    engine_name character varying(255),
    version integer
);

CREATE TABLE gizmo_attrs (
    id serial NOT NULL,
    name character varying(100),
    datatype character varying(10),
    lock_version integer DEFAULT 0 NOT NULL,
    updated_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now(),
    created_by bigint DEFAULT 1 NOT NULL,
    updated_by bigint DEFAULT 1 NOT NULL
);

CREATE TABLE gizmo_contexts (
    id serial NOT NULL,
    name character varying(100),
    lock_version integer DEFAULT 0 NOT NULL,
    updated_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now(),
    created_by bigint DEFAULT 1 NOT NULL,
    updated_by bigint DEFAULT 1 NOT NULL
);

CREATE TABLE gizmo_contexts_gizmo_typeattrs (
    gizmo_context_id integer NOT NULL,
    gizmo_typeattr_id integer NOT NULL,
    lock_version integer DEFAULT 0 NOT NULL,
    updated_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now(),
    created_by bigint DEFAULT 1 NOT NULL,
    updated_by bigint DEFAULT 1 NOT NULL
);

CREATE TABLE gizmo_contexts_gizmo_types (
    gizmo_context_id integer NOT NULL,
    gizmo_type_id integer NOT NULL,
    lock_version integer DEFAULT 0 NOT NULL,
    updated_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now(),
    created_by bigint DEFAULT 1 NOT NULL,
    updated_by bigint DEFAULT 1 NOT NULL
);

CREATE TABLE gizmo_events (
    id serial NOT NULL,
    donation_id integer,
    sale_id integer,
    dispersement_id integer,
    recycling_id integer,
    gizmo_type_id integer NOT NULL,
    gizmo_context_id integer NOT NULL,
    gizmo_count integer NOT NULL,
    lock_version integer DEFAULT 0 NOT NULL,
    updated_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now(),
    created_by bigint DEFAULT 1 NOT NULL,
    updated_by bigint DEFAULT 1 NOT NULL
);

CREATE TABLE gizmo_events_gizmo_typeattrs (
    id serial NOT NULL,
    gizmo_event_id integer NOT NULL,
    gizmo_typeattr_id integer NOT NULL,
    attr_val_text text,
    attr_val_boolean boolean,
    attr_val_integer integer,
    attr_val_monetary numeric(10,2),
    lock_version integer DEFAULT 0 NOT NULL,
    updated_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now(),
    created_by bigint DEFAULT 1 NOT NULL,
    updated_by bigint DEFAULT 1 NOT NULL
);

CREATE TABLE gizmo_typeattrs (
    id serial NOT NULL,
    gizmo_type_id integer NOT NULL,
    gizmo_attr_id integer NOT NULL,
    is_required boolean DEFAULT true NOT NULL,
    validation_callback text,
    lock_version integer DEFAULT 0 NOT NULL,
    updated_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now(),
    created_by bigint DEFAULT 1 NOT NULL,
    updated_by bigint DEFAULT 1 NOT NULL
);

CREATE TABLE gizmo_types (
    id serial NOT NULL,
    description character varying(100),
    parent_id integer,
    lock_version integer DEFAULT 0 NOT NULL,
    updated_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now(),
    created_by bigint DEFAULT 1 NOT NULL,
    updated_by bigint DEFAULT 1 NOT NULL,
    required_fee numeric(10,2) DEFAULT 0.0,
    suggested_fee numeric(10,2) DEFAULT 0.0
);

CREATE TABLE payment_methods (
    id serial NOT NULL,
    description character varying(100),
    lock_version integer DEFAULT 0 NOT NULL,
    updated_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now(),
    created_by bigint DEFAULT 1 NOT NULL,
    updated_by bigint DEFAULT 1 NOT NULL
);


CREATE TABLE payments (
    id serial NOT NULL,
    donation_id integer,
    sale_id integer,
    amount numeric(10,2) DEFAULT 0.0 NOT NULL,
    payment_method_id integer NOT NULL,
    lock_version integer DEFAULT 0 NOT NULL,
    updated_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now(),
    created_by bigint DEFAULT 1 NOT NULL,
    updated_by bigint DEFAULT 1 NOT NULL
);

CREATE TABLE recyclings (
    id serial NOT NULL,
    comments text,
    lock_version integer DEFAULT 0 NOT NULL,
    updated_at timestamp without time zone,
    created_at timestamp without time zone,
    created_by integer DEFAULT 1 NOT NULL,
    updated_by integer DEFAULT 1 NOT NULL,
    recycled_at timestamp without time zone NOT NULL
);

CREATE TABLE sales (
    id serial NOT NULL,
    contact_id integer,
    postal_code character varying(25),
    reported_discount_amount numeric(10,2) DEFAULT 0.0,
    reported_amount_due numeric(10,2) DEFAULT 0.0 NOT NULL,
    txn_complete boolean DEFAULT true,
    txn_completed_at timestamp with time zone DEFAULT now(),
    discount_schedule_id integer,
    comments text,
    bulk boolean,
    lock_version integer DEFAULT 0 NOT NULL,
    updated_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now(),
    created_by bigint DEFAULT 1 NOT NULL,
    updated_by bigint DEFAULT 1 NOT NULL
);

COMMENT ON TABLE sales IS 'each record represents one sales transaction';

CREATE TABLE schema_info (
    version integer
);

CREATE TABLE volunteer_hours_worked (
    contact_id integer,
    hours_worked numeric
);

CREATE TABLE volunteer_task_types (
    id serial NOT NULL,
    description character varying(100),
    parent_id integer,
    hours_multiplier numeric(10,3) DEFAULT 1.0 NOT NULL,
    instantiable boolean DEFAULT true NOT NULL,
    lock_version integer DEFAULT 0 NOT NULL,
    updated_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now(),
    created_by bigint DEFAULT 1 NOT NULL,
    updated_by bigint DEFAULT 1 NOT NULL,
    required boolean DEFAULT true NOT NULL
);

CREATE TABLE volunteer_task_types_volunteer_tasks (
    volunteer_task_id integer NOT NULL,
    volunteer_task_type_id integer NOT NULL
);

CREATE TABLE volunteer_tasks (
    id serial NOT NULL,
    contact_id integer,
    date_performed date DEFAULT now(),
    duration numeric(5,2) DEFAULT 0.00 NOT NULL,
    lock_version integer DEFAULT 0 NOT NULL,
    updated_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now(),
    created_by bigint DEFAULT 1 NOT NULL,
    updated_by bigint DEFAULT 1 NOT NULL
);

Other tables

Some tables exist that could be dropped. Short lived tables include those that start with rfs_

Also, I am wondering about this: 

CREATE TABLE relationship_types (
    id serial NOT NULL,
    description character varying(100),
    direction_matters boolean,
    lock_version integer DEFAULT 0 NOT NULL,
    updated_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now(),
    created_by bigint DEFAULT 1 NOT NULL,
    updated_by bigint DEFAULT 1 NOT NULL
);

COMMENT ON TABLE relationship_types IS 'abstract type of relationships that can exist between contacts, i.e. sibling, parent-child, spouse, employer-employee';

CREATE TABLE relationships (
    id serial NOT NULL,
    source_id integer,
    sink_id integer,
    flow integer,
    relationship_type_id integer,
    lock_version integer DEFAULT 0 NOT NULL,
    updated_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now(),
    created_by bigint DEFAULT 1 NOT NULL,
    updated_by bigint DEFAULT 1 NOT NULL
);

COMMENT ON TABLE relationships IS 'actual relationship between two contacts';

Probably should be dropped unless we intend to use them.

RfS 17:34, 23 June 2007 (PDT)

Retrieved from "http://wiki.freegeek.org/index.php?title=Talk:Fgdb_tables&oldid=26400"