Date: Mon, 20 Dec 1999 15:23:53 +0100
From: Michael Meskes <meskes@postgresql.org>
To: PostgreSQL Interfaces <pgsql-interfaces@postgresql.org>
Subject: Access problems
Message-ID: <19991220152353.A585@fam-meskes.de>
Mail-Followup-To: PostgreSQL Interfaces <pgsql-interfaces@postgresql.org>
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary="pf9I7BMVVzbSWLtt"
User-Agent: Mutt/1.0i


--pf9I7BMVVzbSWLtt
Content-Type: text/plain; charset=us-ascii

I have installed onshore-timesheet on my machine. It works like a char. No I
tried to access the database via M$ Access and found some problems. The ODBC
connection is up and running. However, Access refuses to link in several
tables. 

I attach the table definitions.

It says the following attributes are not correct either in index or in
relationship definition:

table		attribute
-------------------------------
category	category_id
hours		del
job		downloaded
notification	fkpersonnel_id
personnel	personnel_id

Only the client relation is okay.  

So far I wasn't able to see what's wrong with these. Maybe anyone on this
list has an idea. Also ODBC logs don't show something special.

Michael
-- 
Michael Meskes                         | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz    | Go Rhein Fire!
Tel.: (+49) 2431/72651                 | Use Debian GNU/Linux!
Email: Michael@Fam-Meskes.De           | Use PostgreSQL!

--pf9I7BMVVzbSWLtt
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="tables.sql"

CREATE TABLE notification (			// table of who should be notified for actions on a job (not implemented)
	fkjob_id		int,		// the job id number (relates to job_id in job table)
	fkpersonnel_id		text,		// the personnel id number (relates to personnel_id in personnel table)
	constraint notification_pkey primary key ( fkjob_id, fkpersonnel_id )
  );


CREATE TABLE client (				// list of all clients with open or closed jobs
	client_id		int,		// client id (generated externally from &timesheet;)
	client_name		text not null,	// the client's full name as it should appear on reports
	default_bill_rate	money,		// the default billing rate for the client
	default_approved	int,		// if automatically approved, 0 = no, 1 = yes
	constraint client_pkey primary key ( client_id )
  );


CREATE TABLE personnel (			// list of all personnel, employees and supervisors who use timesheet
	personnel_id		text,		// the logon id for the user
	first_name		text,		// user's first name
	middle_name		text,		// user's middle initial
	last_name		text,		// user's last name
	email			text,		// e-mail address
	super_user		text,		// if they have supervisor status or not, 0 = no, 1 = yes
	constraint personnel_pkey primary key ( personnel_id )
  );


CREATE TABLE hours (				// list of all logged, approved and deleted hours in &timesheet;
	hours_id		int,		// id of the hours being logged, generated internally by &timesheet;
	fkjob_id		int not null,	// the job's id as relates to the job in the job table
	fkclient_id		int not null,	// the client's id as relates to the client in the client table
	fkpersonnel_id		text not null,	// the user's id as it relates to the personnel_id in the personnel table
	date_entered		date,		// date the user logged the hours, generated by &timesheet; as the time the entry is made
	intime			text,		// date the user attributes the hours worked 
	time_in			text,		// time the hours entry started
	time_out		text,		// time the hours entry stopped
	total_hours		real not null,	// total hours for a particular entry
	billable		int,		// should this be billable to the client, 0 = no, 1 = yes
	del			text,		// is this a deleted hours entry, 0 = no, 1 = yes
	hours_description	text,		// the description of the hours worked
	comment			text,		// comments about the hours worked
	category		text,		// category for this work (from set list)
	parking			real,		// parking fees accrued for this hours entry
	expense_amount		money,		// 
	downloaded		text,		// downloaded because approved, 0 = no, 1 = yes
	fkapprover_id		text,		// the id of the supervisor who approved the hours
	approval_date		date,		// the date the hours were approved
	constraint hours_pkey primary key ( hours_id )
  );


CREATE TABLE job (				// list of all possible jobs
	job_id			int,		// id of the job, generated internally by &timesheet;
	fkclient_id		int not null,	// the client's id as relates to the client table
	estimated_hours		real,		// estimated number of hours for the completion of this job
	current_hours_estimate	real,		// 
	current_estimate_date	date,		// the date the estimate of hours was made on the job
	open_date		date,		// date the job was opened
	close_date		date,		// date the job was closed
	open			int,		// job is open or not, 0 = no, 1 = yes
	bill_rate		money,		// the billing rate for this job
	job_description		text,		// the description of the job
	default_approved	int,		// any hours logged to the job are automatically approved
	fksupervisor_id		text,		// the id of the supervisor responsible for this job, as relates to the id in the personnel table
	fknotify_id		text,		// list of user id's, as relate to the personnel_ids in the personnel table, to notify of the job's creation
	flag_hrs		int,		// number of hours logged to this job for flagging a warning to the supervisor
	category		text,		// category for this job (from set list)
	downloaded		text,		// if the job has been downloaded or not, 0 = no, 1 = yes
	constraint job_pkey primary key ( job_id )
  );


CREATE TABLE category (				// list of possible categories of work for hours and jobs
	category_id	text,			// the id for the category
	creator_id	text,			// the id for who created the category
	constraint category_pkey primary key ( category_id )
  );


--pf9I7BMVVzbSWLtt
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="indices.sql"

create index client_client_id on client (client_id);

create index hours_date_entered on hours (date_entered);
create index hours_del on hours (del);
create index hours_downloaded on hours (downloaded);
create index hours_fkclient_id on hours (fkclient_id);
create index hours_fkjob_id on hours (fkjob_id);
create index hours_fkpersonnel_id on hours (fkpersonnel_id);
create index hours_approval_date on hours (approval_date);
// unsure about this one, i.e., why it existed
// create index hours_fkpersonnel_id on hours using hash ( fkpersonnel_id text_ops);
create index hours_hours_id on hours (hours_id);
create index hours_id_index on hours (hours_id);

create index job_downloaded on job (downloaded);
create index job_fkclient_id on job (fkclient_id);
create index job_fksupervisor_id on job (fksupervisor_id);
create index job_job_id on job (job_id);

create index personnel_personnel_id on personnel (personnel_id);
create index personnel_super_user on personnel (super_user);

--pf9I7BMVVzbSWLtt--