Thread: graphical tools

graphical tools

From
"Joel Fradkin"
Date:
I have seen this question a few times and still have not found a tool that
lets me edit the schema.
I have pgadminIII which I like, I also have tried a few other tools some are
not free, but none seem to allow me to just change the type of a field from
say char to varchar etc.
Is there a tool (even if its not free)?
Or do I have to do something like MSSQL does behind the scenes:

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.tblQuestions
    DROP CONSTRAINT DF_tblQuestions_Q_Default_Y_NAnswer
GO
ALTER TABLE dbo.tblQuestions
    DROP CONSTRAINT DF_tblQuestions_QY_N_or_TextAnswerBadScore
GO
ALTER TABLE dbo.tblQuestions
    DROP CONSTRAINT DF_tblQuestions_SecFeedBackTracking
GO
ALTER TABLE dbo.tblQuestions
    DROP CONSTRAINT DF_tblQuestions_SecFeedBackDaysfromclose
GO
CREATE TABLE dbo.Tmp_tblQuestions
    (
    Clinum varchar(16) NOT NULL,
    AuditID int NOT NULL,
    SectionId int NOT NULL,
    QuestionID int NOT NULL,
    Question char(5) NOT NULL,
    QuestionType char(1) NULL,
    QInfo1 varchar(4096) NULL,
    QInfo2 varchar(4096) NULL,
    QInfo3 varchar(4096) NULL,
    QText varchar(4096) NULL,
    QDescr varchar(4096) NULL,
    QCorectAnswerY_N char(1) NULL,
    QWeightedAnswerThreshold decimal(5, 2) NULL,
    QY_N_or_TextAnswerScore decimal(5, 2) NULL,
    QuestionDisplay varchar(25) NOT NULL,
    QNA_acceptible bit NULL,
    Q_Default_Y_NAnswer char(1) NULL,
    QY_N_or_TextAnswerBadScore decimal(5, 2) NULL,
    Q_FeedBackTracking char(1) NULL,
    Q_FeedBackDaysfromclose int NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_tblQuestions ADD CONSTRAINT
    DF_tblQuestions_Q_Default_Y_NAnswer DEFAULT ('E') FOR
Q_Default_Y_NAnswer
GO
ALTER TABLE dbo.Tmp_tblQuestions ADD CONSTRAINT
    DF_tblQuestions_QY_N_or_TextAnswerBadScore DEFAULT (5) FOR
QY_N_or_TextAnswerBadScore
GO
ALTER TABLE dbo.Tmp_tblQuestions ADD CONSTRAINT
    DF_tblQuestions_SecFeedBackTracking DEFAULT ('N') FOR
Q_FeedBackTracking
GO
ALTER TABLE dbo.Tmp_tblQuestions ADD CONSTRAINT
    DF_tblQuestions_SecFeedBackDaysfromclose DEFAULT (30) FOR
Q_FeedBackDaysfromclose
GO
IF EXISTS(SELECT * FROM dbo.tblQuestions)
     EXEC('INSERT INTO dbo.Tmp_tblQuestions (Clinum, AuditID, SectionId,
QuestionID, Question, QuestionType, QInfo1, QInfo2, QInfo3, QText, QDescr,
QCorectAnswerY_N, QWeightedAnswerThreshold, QY_N_or_TextAnswerScore,
QuestionDisplay, QNA_acceptible, Q_Default_Y_NAnswer,
QY_N_or_TextAnswerBadScore, Q_FeedBackTracking, Q_FeedBackDaysfromclose)
        SELECT CONVERT(varchar(16), Clinum), AuditID, SectionId,
QuestionID, Question, QuestionType, QInfo1, QInfo2, QInfo3, QText, QDescr,
QCorectAnswerY_N, QWeightedAnswerThreshold, QY_N_or_TextAnswerScore,
QuestionDisplay, QNA_acceptible, Q_Default_Y_NAnswer,
QY_N_or_TextAnswerBadScore, Q_FeedBackTracking, Q_FeedBackDaysfromclose FROM
dbo.tblQuestions TABLOCKX')
GO
DROP TABLE dbo.tblQuestions
GO
EXECUTE sp_rename N'dbo.Tmp_tblQuestions', N'tblQuestions', 'OBJECT'
GO
ALTER TABLE dbo.tblQuestions ADD CONSTRAINT
    PK_tblQuestions PRIMARY KEY NONCLUSTERED
    (
    Clinum,
    AuditID,
    SectionId,
    QuestionID
    ) WITH FILLFACTOR = 90 ON [PRIMARY]

GO
COMMIT

Joel Fradkin
 

 



Visual data model creation / maintenance tools

From
"Sam Hahn"
Date:
I am interested in what people here think are good tools (free or not) for
creating / maintaining a pg data model. A layout view for relationships is
preferred (with selectable levels of detail, eg. fields, keys). I would like
it to have knowledge of pg datatypes, but this isn't a nogo item, so long as
it can be straightforward to edit in a post-pass (eg. int->serial,
varchar->text). The candidates I'm looking at are:

ERwin
Visio
pgAdminIII
CaseStudio
Axon (ok this will require more postprocessing)
Paper (I prefer not to use just this)

What else is worthy of consideration?

Thanks - Sam


Re: Visual data model creation / maintenance tools

From
Ben Kim
Date:
> I am interested in what people here think are good tools (free or not) for
> creating / maintaining a pg data model. A layout view for relationships is

P.S.

If you are interested in only the layout view of relationship and not
designing, a trivial, despised but useful one is M$ Access. (Sorry for the
guard-up.) You can link tables from Postgresql using odbc and manually
create relationships. It's easier to handle and print than most of the
serious tools. (In fact, I find Access better when there are some (~30)
tables.)

There's also postgresql-autodoc which is supposed to create the
relationship diagram from Postgresql database along with documentation.
The documentation is very nice, but the relationship graphic (for use with
AT&T viz package) gets kind of messed up.

www.embarcadero.com has Dbartisan which is commercial and OK.

These are what I tried myself, but I know there are many, many of ERD
tools.


Regards,

Ben Kim
Database Developer/Systems Administrator
434E Harrington Tower / College of Education
Texas A&M University