← Back to branch summary

~ubuntu-branches/ubuntu/utopic/pgadmin3/utopic-proposed

« back to all changes in this revision

Viewing changes to src/schema/pgForeignKey.cpp

  • Committer: Bazaar Package Importer
  • Author(s): Raphael Enrici
  • Date: 2004-12-14 23:46:39 UTC
  • Revision ID: james.westby@ubuntu.com-20041214234639-tve0i5l49fq13jli
Tags: upstream-1.2.0
Import upstream version 1.2.0

Show diffs side-by-side

added added

removed removed

Lines of Context:
src/schema/pgForeignKey.cpp
 
1
//////////////////////////////////////////////////////////////////////////
 
2
//
 
3
// pgAdmin III - PostgreSQL Tools
 
4
// RCS-ID:      $Id: pgForeignKey.cpp,v 1.25 2004/10/21 14:34:38 andreas Exp $
 
5
// Copyright (C) 2002 - 2004, The pgAdmin Development Team
 
6
// This software is released under the Artistic Licence
 
7
//
 
8
// pgForeignKey.cpp - ForeignKey class
 
9
//
 
10
//////////////////////////////////////////////////////////////////////////
 
11
 
 
12
// wxWindows headers
 
13
#include <wx/wx.h>
 
14
 
 
15
// App headers
 
16
#include "pgAdmin3.h"
 
17
#include "misc.h"
 
18
#include "pgObject.h"
 
19
#include "pgTable.h"
 
20
#include "pgForeignKey.h"
 
21
#include "pgCollection.h"
 
22
 
 
23
 
 
24
pgForeignKey::pgForeignKey(pgSchema *newSchema, const wxString& newName)
 
25
: pgSchemaObject(newSchema, PG_FOREIGNKEY, newName)
 
26
{
 
27
}
 
28
 
 
29
pgForeignKey::~pgForeignKey()
 
30
{
 
31
}
 
32
 
 
33
 
 
34
bool pgForeignKey::DropObject(wxFrame *frame, wxTreeCtrl *browser)
 
35
{
 
36
    return GetDatabase()->ExecuteVoid(wxT(
 
37
        "ALTER TABLE ") + GetSchemaPrefix(fkSchema) + qtIdent(fkTable)
 
38
        + wxT(" DROP CONSTRAINT ") + GetQuotedIdentifier());
 
39
}
 
40
 
 
41
 
 
42
wxString pgForeignKey::GetDefinition()
 
43
{
 
44
    wxString sql;
 
45
    // MATCH FULL/PARTIAL missing; where is this stored?!?
 
46
 
 
47
    sql = wxT("(") + GetQuotedFkColumns()
 
48
        +  wxT(") REFERENCES ") + GetQuotedSchemaPrefix(GetRefSchema()) + qtIdent(GetReferences()) 
 
49
        +  wxT(" (") + GetQuotedRefColumns()
 
50
        +  wxT(") ON UPDATE ") + GetOnUpdate()
 
51
        +  wxT(" ON DELETE ") + GetOnDelete();
 
52
    if (GetDeferrable())
 
53
    {
 
54
        sql += wxT(" DEFERRABLE INITIALLY ");
 
55
        if (GetDeferred())
 
56
            sql += wxT("DEFERRED");
 
57
        else
 
58
            sql += wxT("IMMEDIATE");
 
59
    }
 
60
    return sql;
 
61
}
 
62
 
 
63
 
 
64
wxString pgForeignKey::GetConstraint()
 
65
{
 
66
    wxString sql;
 
67
    sql = GetQuotedIdentifier() 
 
68
        +  wxT(" FOREIGN KEY ") + GetDefinition();
 
69
 
 
70
    return sql;
 
71
}
 
72
 
 
73
 
 
74
wxString pgForeignKey::GetSql(wxTreeCtrl *browser)
 
75
{
 
76
    if (sql.IsNull())
 
77
    {
 
78
        sql = wxT("-- Foreign Key: ") + GetQuotedFullIdentifier() + wxT("\n\n")
 
79
            + wxT("-- ALTER TABLE ") + GetQuotedSchemaPrefix(fkSchema) + qtIdent(fkTable)
 
80
            + wxT(" DROP CONSTRAINT ") + GetQuotedIdentifier() + wxT(";")
 
81
            + wxT("\n\nALTER TABLE ") + GetQuotedSchemaPrefix(fkSchema) + qtIdent(fkTable)
 
82
            + wxT("\n  ADD CONSTRAINT ") + GetConstraint() 
 
83
            + wxT(";\n");
 
84
        if (!GetComment().IsEmpty())
 
85
            sql += wxT("COMMENT ON CONSTRAINT ") + GetQuotedIdentifier() + wxT(" ON ") + GetQuotedSchemaPrefix(fkSchema) + qtIdent(fkTable)
 
86
                +  wxT(" IS ") + qtString(GetComment()) + wxT(";\n");
 
87
    }
 
88
 
 
89
    return sql;
 
90
}
 
91
 
 
92
 
 
93
wxString pgForeignKey::GetFullName() const
 
94
{
 
95
    return GetName() + wxT(" -> ") + GetReferences();
 
96
}
 
97
 
 
98
void pgForeignKey::ShowTreeDetail(wxTreeCtrl *browser, frmMain *form, ctlListView *properties, ctlSQLBox *sqlPane)
 
99
{
 
100
    if (!expandedKids)
 
101
    {
 
102
        expandedKids=true;
 
103
 
 
104
        wxStringTokenizer c1l(GetConkey(), wxT(","));
 
105
        wxStringTokenizer c2l(GetConfkey(), wxT(","));
 
106
        wxString c1, c2;
 
107
 
 
108
        // resolve column names
 
109
        while (c1l.HasMoreTokens())
 
110
        {
 
111
            c1=c1l.GetNextToken();
 
112
            c2=c2l.GetNextToken();
 
113
            pgSet *set=ExecuteSet(
 
114
                wxT("SELECT a1.attname as conattname, a2.attname as confattname\n")
 
115
                wxT("  FROM pg_attribute a1, pg_attribute a2\n")
 
116
                wxT(" WHERE a1.attrelid=") + GetTableOidStr() + wxT(" AND a1.attnum=") + c1 + wxT("\n")
 
117
                wxT("   AND a2.attrelid=") + GetRelTableOidStr() + wxT(" AND a2.attnum=") + c2);
 
118
            if (set)
 
119
            {
 
120
                if (!fkColumns.IsNull())
 
121
                {
 
122
                    fkColumns += wxT(", ");
 
123
                    refColumns += wxT(", ");
 
124
                    quotedFkColumns += wxT(", ");
 
125
                    quotedRefColumns += wxT(", ");
 
126
                }
 
127
                fkColumns += set->GetVal(0);
 
128
                refColumns += set->GetVal(1);
 
129
                quotedFkColumns += qtIdent(set->GetVal(0));
 
130
                quotedRefColumns += qtIdent(set->GetVal(1));
 
131
                delete set;
 
132
            }
 
133
        }
 
134
        wxTreeItemId item=browser->GetItemParent(GetId());
 
135
        while (item)
 
136
        {
 
137
            pgTable *table=(pgTable*)browser->GetItemData(item);
 
138
            if (table->GetType() == PG_TABLE)
 
139
            {
 
140
                coveringIndex = table->GetCoveringIndex(browser, fkColumns);
 
141
                break;
 
142
            }
 
143
            item = browser->GetItemParent(item);
 
144
        }
 
145
    }
 
146
 
 
147
    if (properties)
 
148
    {
 
149
        CreateListColumns(properties);
 
150
 
 
151
        properties->AppendItem(_("Name"), GetName());
 
152
        properties->AppendItem(_("OID"), NumToStr(GetOid()));
 
153
        properties->AppendItem(_("Child columns"), GetFkColumns());
 
154
        properties->AppendItem(_("References"), GetReferences() 
 
155
            + wxT("(") +GetRefColumns() + wxT(")"));
 
156
 
 
157
        properties->AppendItem(_("Covering index"), GetCoveringIndex());
 
158
        properties->AppendItem(_("On update"), GetOnUpdate());
 
159
        properties->AppendItem(_("On delete"), GetOnDelete());
 
160
        properties->AppendItem(_("Deferrable?"), BoolToYesNo(GetDeferrable()));
 
161
        properties->AppendItem(_("Initially?"), 
 
162
            GetDeferred() ? wxT("DEFERRED") : wxT("IMMEDIATE"));
 
163
        properties->AppendItem(_("System foreign key?"), BoolToYesNo(GetSystemObject()));
 
164
        properties->AppendItem(_("Comment"), GetComment());
 
165
    }
 
166
}
 
167
 
 
168
 
 
169
 
 
170
 
 
171
pgObject *pgForeignKey::Refresh(wxTreeCtrl *browser, const wxTreeItemId item)
 
172
{
 
173
    pgObject *foreignKey=0;
 
174
    wxTreeItemId parentItem=browser->GetItemParent(item);
 
175
    if (parentItem)
 
176
    {
 
177
        pgObject *obj=(pgObject*)browser->GetItemData(parentItem);
 
178
        if (obj->GetType() == PG_CONSTRAINTS)
 
179
            foreignKey = ReadObjects((pgCollection*)obj, 0, wxT("\n   AND ct.oid=") + GetOidStr());
 
180
    }
 
181
    return foreignKey;
 
182
}
 
183
 
 
184
 
 
185
 
 
186
pgObject *pgForeignKey::ReadObjects(pgCollection *collection, wxTreeCtrl *browser, const wxString &restriction)
 
187
{
 
188
    pgForeignKey *foreignKey=0;
 
189
 
 
190
    pgSet *foreignKeys= collection->GetDatabase()->ExecuteSet(
 
191
        wxT("SELECT ct.oid, conname, condeferrable, condeferred, confupdtype, confdeltype, confmatchtype, ")
 
192
               wxT("conkey, confkey, confrelid, nl.nspname as fknsp, cl.relname as fktab, ")
 
193
               wxT("nr.nspname as refnsp, cr.relname as reftab, description\n")
 
194
        wxT("  FROM pg_constraint ct\n")
 
195
        wxT("  JOIN pg_class cl ON cl.oid=conrelid\n")
 
196
        wxT("  JOIN pg_namespace nl ON nl.oid=cl.relnamespace\n")
 
197
        wxT("  JOIN pg_class cr ON cr.oid=confrelid\n")
 
198
        wxT("  JOIN pg_namespace nr ON nr.oid=cr.relnamespace\n")
 
199
        wxT("  LEFT OUTER JOIN pg_description des ON des.objoid=ct.oid\n")
 
200
        wxT(" WHERE contype='f' AND conrelid = ") + collection->GetOidStr()
 
201
        + restriction + wxT("\n")
 
202
        wxT(" ORDER BY conname"));
 
203
 
 
204
    if (foreignKeys)
 
205
    {
 
206
        while (!foreignKeys->Eof())
 
207
        {
 
208
            foreignKey = new pgForeignKey(collection->GetSchema(), foreignKeys->GetVal(wxT("conname")));
 
209
 
 
210
            foreignKey->iSetOid(foreignKeys->GetOid(wxT("oid")));
 
211
            foreignKey->iSetTableOid(collection->GetOid());
 
212
            foreignKey->iSetRelTableOid(foreignKeys->GetOid(wxT("confrelid")));
 
213
            foreignKey->iSetFkSchema(foreignKeys->GetVal(wxT("fknsp")));
 
214
            foreignKey->iSetComment(foreignKeys->GetVal(wxT("description")));
 
215
            foreignKey->iSetFkTable(foreignKeys->GetVal(wxT("fktab")));
 
216
            foreignKey->iSetRefSchema(foreignKeys->GetVal(wxT("refnsp")));
 
217
            foreignKey->iSetReferences(foreignKeys->GetVal(wxT("reftab")));
 
218
            wxString onUpd=foreignKeys->GetVal(wxT("confupdtype"));
 
219
            wxString onDel=foreignKeys->GetVal(wxT("confdeltype"));
 
220
            foreignKey->iSetOnUpdate(
 
221
                onUpd.IsSameAs('a') ? wxT("NO ACTION") :
 
222
                onUpd.IsSameAs('r') ? wxT("RESTRICT") :
 
223
                onUpd.IsSameAs('c') ? wxT("CASCADE") :
 
224
                onUpd.IsSameAs('d') ? wxT("SET DEFAULT") :
 
225
                onUpd.IsSameAs('n') ? wxT("SET NULL") : wxT("Unknown"));
 
226
            foreignKey->iSetOnDelete(
 
227
                onDel.IsSameAs('a') ? wxT("NO ACTION") :
 
228
                onDel.IsSameAs('r') ? wxT("RESTRICT") :
 
229
                onDel.IsSameAs('c') ? wxT("CASCADE") :
 
230
                onDel.IsSameAs('d') ? wxT("SET DEFAULT") :
 
231
                onDel.IsSameAs('n') ? wxT("SET NULL") : wxT("Unknown"));
 
232
            wxString cn=foreignKeys->GetVal(wxT("conkey"));
 
233
            cn = cn.Mid(1, cn.Length()-2);
 
234
            foreignKey->iSetConkey(cn);
 
235
            cn=foreignKeys->GetVal(wxT("confkey"));
 
236
            cn = cn.Mid(1, cn.Length()-2);
 
237
            foreignKey->iSetConfkey(cn);
 
238
 
 
239
            foreignKey->iSetDeferrable(foreignKeys->GetBool(wxT("condeferrable")));
 
240
            foreignKey->iSetDeferred(foreignKeys->GetBool(wxT("condeferred")));
 
241
 
 
242
            if (browser)
 
243
            {
 
244
                collection->AppendBrowserItem(browser, foreignKey);
 
245
                        foreignKeys->MoveNext();
 
246
            }
 
247
            else
 
248
                break;
 
249
        }
 
250
 
 
251
                delete foreignKeys;
 
252
    }
 
253
    return foreignKey;
 
254
}