[GENERAL] ER Diagram design tools (Linux)
I was wondering if anyone knows of any good ER Diagram tools for Postgres that run on Linux. I have been using DBDesigner by FabForce for a couple of years, but development has stopped while MySQL workbench is being built (for windows only). Neither of these applications will talk to Postgres and I've found DBDesigner to be a bit buggy at the best of times (it's still quite good and better than nothing I suppose). I've been using PgAdmin3 which is great for updating/managing tables/view etc, but I would really like something for modelling ER diagrams which will talk directly to Postgres. Does anyone know of any commercial or open source software that will do this? Cheers, Conor ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] JOIN and difference between timestamps
I'm trying to find a count of records based on a number of factors,
one of them being that records in different tables have been created
within 1 hour of each other.
The tables in question look like this:
character_tbl
Column |Type | Modifiers
---+-+---
cha_regist| timestamp without time zone | not null
cha_character | character(1000) |
cha_cid | character(20) | not null
cha_rno | integer | not null
update_tbl
Column |Type |
Modifiers
---+-+
update_id | integer | not null default
nextval('update_tbl_update_id_seq'::regclass)
update_date | timestamp without time zone | not null default
('now'::text)::timestamp(6) with time zone
update_candi | integer | not null
update_cons | character(20) | not null
updated_field | character(32) |
updated_from | character(128) |
updated_to| character(128) |
The character_tbl is a collection of notes related to another entity
within the database, while the update table is log table related to
the same entity the cha_rno and update_candi are the foreign keys in
the respective tables. The cha_cid and update_cons relate to the user
creating the note or updating the record.
I would like to find a count of all notes that are created by a user
(cha_cid) within a time period (March 1 to March 31) where there is
also a corresponding update within the same time frame where the
difference between the timestamps is less than 1 hour.
I can pull all records for a user by selecting with a left join on the
cha_rno and update_candi where the cha_cid = update_cons and the
timestamps are within the range. My problem seems to be when I attempt
to add a HAVING clause to filter on the difference between the
timestamps. I am not seeing the results that I would expect.
I understand that subtraction of timestamps will return an interval,
but I cannot tell if it is in seconds or minutes.
Here's what I've come up with so far for my sql query:
select count(distinct(cha_rno)) from character_tbl
left join update_tbl
on character_tbl.cha_rno = update_tbl.update_candi
where cha_cid = 'cmcternan'
and cha_regist >= '2008-03-01'
and cha_regist < '2008-04-01'
and update_date >= '2008-03-01'
and update_cons = 'cmcternan'
and
(updated_field = 'candi_grade' OR updated_field =
'candi_status_no');
This will give me a result that I would expect. I know that some
records were entered minutes apart, while others were entered a few
hours apart, right now I have no way to filter down through these so
that I can only see the count of records that have been entered
'close' to each other.
Am I completely missing the point here or is this just a very bad idea?
Any ideas are appreciated.
Cheers,
Conor
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] User permissions/Data separation.
I understand that this has been discussed before, but I was hoping that somebody might have some fresh ideas on the problem. I'm using Postgres for my web app, I users that interface with the database through the app. All records are classified with an industry and an occupation. Currently permissions are based around these two values, i.e. User John Doe can view all records with industries 1-10 and occupations 5-50, user Jane Doe can view all records with ALL industries and occupations 1-20. For all the users I am maintaining 2 tables (user_can_see_industry and user_can_see_occupation), at the application level I am pulling these values out of the database and constructing my queries. I'm finding that this approach is not really the best way to manage the permissions, but at the same time I'm having trouble figuring out a better way. Users belong to working groups, and while I'd love to implement a view managing the permissions for each group this does not really meet the business needs of the users (more senior users need to be able to see more records than junior users, group scope is constantly changing, the scope of each group is not clearly defined (this is probably my biggest problem, but that's a different story)). I'm also noticing that with my queries constructed the way they are at the moment I'm hitting a bit of a performance bottleneck, I'm using a very long WHERE/OR statement in each query, which will occasionally slow it down. What would the benefits be implementing a VIEW for each user, would it improve query time at all? When updating their permissions I would obviously have to blow away the old view and create a new one, are there any negative aspects that approach. I've thought of using different schema's to limit access, but I'm having trouble getting my head around that approach. My understanding is that if User A adds a record to Schema A then User B using Schema B will not be able to see these records, if this is the case this will not fit my needs as users should be able to see other users records so long as they have the correct privileges. The situation can also arise where User A enters a record but is not allowed to view it after the fact (the record was given an industry/occupation combination User A is not allowed to view). Anyway, I'm leaning heavily towards implementing individual views, but I was just wondering if anyone has any better ideas, or any reasons to shoot down the idea of using them. Cheers, Conor ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
