Hi, Finally I've made Copy/Paste table(s) operations !
Usage is very simple and intuitive: on right mouse menu for selected connected table or schema select 'Copy table(s)' and than on other connected schema on right mouse menu select 'Paste table(s). Source target server-db might be different but always connected. Best regards, Vladimir Kokovic, DP senior, Belgrade, Serbia
frmMain-h.diff
Description: Binary data
frmMain-cpp.diff
Description: Binary data
//////////////////////////////////////////////////////////////////////////
//
// pgAdmin III - PostgreSQL Tools
//
// Copyright (C) 2002 - 2011, The pgAdmin Development Team
// This software is released under the PostgreSQL Licence
//
// frmPasteObject.h - Copy/Paste table(s) functions
//
//////////////////////////////////////////////////////////////////////////
#ifndef FRMPASTEOBJECT_H
#define FRMPASTEOBJECT_H
#include "frm/frmMain.h"
#include "schema/pgObject.h"
class frmPasteObject
{
public:
frmPasteObject(frmMain *form, pgObject *sourceobj, pgObject *targetobj);
void process();
virtual ~frmPasteObject();
private:
frmMain *mainform;
pgObject *sourceobj;
pgObject *targetobj;
};
#endif /* FRMPASTEOBJECT_H */
//////////////////////////////////////////////////////////////////////////
//
// pgAdmin III - PostgreSQL Tools
//
// Copyright (C) 2002 - 2011, The pgAdmin Development Team
// This software is released under the PostgreSQL Licence
//
// frmPasteObject.cpp - Copy/Paste table(s) functions
//
//////////////////////////////////////////////////////////////////////////
// wxWindows headers
#include <wx/wx.h>
#include <wx/wfstream.h>
// PostgreSQL headers
#include <libpq-fe.h>
#include "pgAdmin3.h"
#include "frm/frmPasteObject.h"
#include "schema/pgSchema.h"
#include "schema/pgTable.h"
#include "schema/pgColumn.h"
#include "schema/pgSequence.h"
#include "schema/pgConstraints.h"
#include "utils/misc.h"
#include "schema/pgForeignKey.h"
#include "schema/pgIndexConstraint.h"
#include "schema/pgCheck.h"
#include <errno.h>
frmPasteObject::frmPasteObject(frmMain *form, pgObject *sourceobj, pgObject *targetobj)
{
this->mainform = form;
this->sourceobj= sourceobj;
this->targetobj= targetobj;
}
wxArrayString *getSchemaTables(pgSchema *srcschema)
{
wxArrayString *objArray = new wxArrayString();
wxString query = wxT("SELECT relname ")
wxT("FROM pg_namespace n ")
wxT("LEFT JOIN pg_class c ON n.oid=c.relnamespace AND relkind='r' ")
wxT("WHERE nspname='") + srcschema->GetIdentifier() + wxT("'");
query += wxT("ORDER BY relname");
pgSet *objects = srcschema->GetDatabase()->ExecuteSet(query);
if (objects)
{
while (!objects->Eof())
{
if (!objects->GetVal(wxT("relname")).IsNull())
{
objArray->Add(objects->GetVal(wxT("relname")));
}
objects->MoveNext();
}
delete objects;
}
return objArray;
}
/*
* Functions for handling COPY IN/OUT data transfer.
*
* If you want to use COPY TO STDOUT/FROM STDIN in your application,
* this is the code to steal ;)
*/
/*
* handleCopyOut
* receives data as a result of a COPY ... TO STDOUT command
*
* conn should be a database connection that you just issued COPY TO on
* and got back a PGRES_COPY_OUT result.
* copystream is the file stream for the data to go to.
*
* result is true if successful, false if not.
*/
wxString
handleCopyOut(PGconn *conn, wxFile & copystream)
{
bool OK = true;
char *buf;
int ret;
PGresult *res;
wxString lastError;
for (;;)
{
ret = PQgetCopyData(conn, &buf, 0);
if (ret < 0)
break; /* done or error */
if (buf)
{
if (copystream.Write(wxString(buf, wxConvUTF8)) != ret)
{
if (OK) /* complain only once, keep reading data */
lastError.Format(_("could not write COPY data: %s\n"), strerror(errno));
OK = false;
}
PQfreemem(buf);
}
}
if (OK && copystream.Flush())
{
lastError.Format(_("could not write COPY data: %s\n"), strerror(errno));
OK = false;
}
if (ret == -2)
{
lastError.Format(_("COPY data transfer failed: %s"), PQerrorMessage(conn));
OK = false;
}
/* Check command status and return to normal libpq state */
res = PQgetResult(conn);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
lastError.Format(wxT("%s"), PQerrorMessage(conn));
OK = false;
}
PQclear(res);
return lastError;
}
/*
* handleCopyIn
* sends data to complete a COPY ... FROM STDIN command
*
* conn should be a database connection that you just issued COPY FROM on
* and got back a PGRES_COPY_IN result.
* copystream is the file stream to read the data from.
* isbinary can be set from PQbinaryTuples().
*
* result is true if successful, false if not.
*/
/* read chunk size for COPY IN - size is not critical */
#define COPYBUFSIZ 8192
wxString
handleCopyIn(PGconn *conn, wxFile & copystream, bool isbinary)
{
bool OK;
char buf[COPYBUFSIZ];
PGresult *res;
wxString lastError;
int counter = 0;
OK = true;
if (isbinary)
{
for (;;)
{
int buflen;;
buflen = copystream.Read(buf, 1);
if (buflen <= 0)
break;
if (PQputCopyData(conn, buf, buflen) <= 0)
{
OK = false;
break;
}
}
}
else
{
wxFileInputStream input(copystream);
wxTextInputStream textfile(input);
while (input.CanRead()) /* for each bufferload in line ... */
{
counter++;
wxString buf1 = textfile.ReadLine() + wxT("\n");
int buflen = buf1.Length();
if (buf1 == wxT("\n"))
{
break;
}
const wxCharBuffer wc = buf1.ToUTF8();
const char *tmp = wc.data();
int lenc = strlen(tmp);
if (PQputCopyData(conn, tmp, lenc) <= 0)
{
OK = false;
break;
}
}
}
/* Terminate data transfer */
const char *errmsg = "aborted because of read failure";
if (PQputCopyEnd(conn, OK ? NULL : errmsg) <= 0)
OK = false;
/* Check command status and return to normal libpq state */
res = PQgetResult(conn);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
lastError = wxString::FromAscii(PQerrorMessage(conn));
OK = false;
}
PQclear(res);
return lastError;
}
/*
* Execute a \copy command (frontend copy). We have to open a file, then
* submit a COPY query to the backend and either feed it data from the
* file or route its response into the file.
*/
wxString
do_copy(PGconn *conn, wxString & sql, wxFile & copystream)
{
PGresult *result;
struct stat st;
wxString lastError;
result = PQexec(conn, (const char *)sql.mb_str());
switch (PQresultStatus(result))
{
case PGRES_COPY_OUT:
lastError = ::handleCopyOut(conn, copystream);
break;
case PGRES_COPY_IN:
lastError = ::handleCopyIn(conn, copystream, PQbinaryTuples(result));
break;
case PGRES_NONFATAL_ERROR:
case PGRES_FATAL_ERROR:
case PGRES_BAD_RESPONSE:
lastError.Format(_("copy: %s"), PQerrorMessage(conn));
break;
default:
lastError.Format(_("copy: unexpected response (%d)\n"), PQresultStatus(result));
break;
}
PQclear(result);
/*
* Make sure we have pumped libpq dry of results; else it may still be in
* ASYNC_BUSY state, leading to false readings in, eg, get_prompt().
*/
while ((result = PQgetResult(conn)) != NULL)
{
lastError.Format(_("copy: unexpected response (%d)\n"), PQresultStatus(result));
/* if still in COPY IN state, try to get out of it */
if (PQresultStatus(result) == PGRES_COPY_IN)
PQputCopyEnd(conn, (const char *)_("trying to exit copy mode"));
PQclear(result);
}
return lastError;
}
wxString copyTable(ctlTree *browser, pgSchema *srcschema, pgSchema *targetschema, pgTable *table1)
{
pgConn *sourceconn = srcschema->GetConnection();
pgConn *targetconn = targetschema->GetConnection();
bool rc;
wxString lastError;
rc = targetconn->ExecuteVoid(wxT("BEGIN"));
lastError = targetconn->GetLastError();
if (!rc) return lastError;
table1->ShowTreeDetail(browser);
pgCollection *columns = table1->GetColumnCollection(browser);
treeObjectIterator colIt(browser, columns);
pgColumn *column;
wxString colName, defval;
while ((column = (pgColumn *)colIt.GetNextObject()) != 0)
{
column->ShowTreeDetail(browser);
if (column->GetColNumber() > 0)
{
colName = column->GetName();
defval = column->GetDefault();
if (!defval.IsNull() && defval.StartsWith(wxT("nextval(")))
{
wxStringTokenizer parts(defval, wxT("'"));
int i = 0;
while (parts.HasMoreTokens())
{
wxString part = parts.GetNextToken();
if (i == 1)
{
wxString seqsch, seqname;
int subseq = part.Find('.');
if (subseq == wxNOT_FOUND)
{
seqsch = wxT("public");
seqname = part;
}
else
{
seqsch = part.Mid(0, subseq - 1);
seqname = part.Mid(subseq + 1);
}
wxLongLong lastValue, minValue, maxValue, cacheValue, increment;
bool cycled, called;
wxString owner, comment, acl;
pgSequence seq = pgSequence(srcschema, seqname);
pgSet *seqcat = srcschema->GetDatabase()->ExecuteSet(
wxT("SELECT pg_get_userbyid(relowner) AS seqowner, relacl, description\n")
wxT(" FROM pg_class cl\n")
wxT(" LEFT OUTER JOIN pg_description des ON des.objoid=cl.oid\n")
wxT(" WHERE relkind = 'S' AND relnamespace = ") + srcschema->GetOidStr() +
wxT(" AND relname = ") + seq.qtDbString(seqname) + wxT("\n"));
if (seqcat)
{
comment = seqcat->GetVal(wxT("description"));
owner = seqcat->GetVal(wxT("seqowner"));
acl = seqcat->GetVal(wxT("relacl"));
delete seqcat;
}
else
{
goto Error;
}
seq.iSetAcl(acl);
pgSet *sequence = srcschema->GetDatabase()->ExecuteSet(
wxT("SELECT last_value, min_value, max_value, cache_value, is_cycled, increment_by, is_called\n")
wxT(" FROM ") + seq.GetQuotedFullIdentifier());
if (sequence)
{
lastValue = sequence->GetLongLong(wxT("last_value"));
minValue = sequence->GetLongLong(wxT("min_value"));
maxValue = sequence->GetLongLong(wxT("max_value"));
cacheValue = sequence->GetLongLong(wxT("cache_value"));
increment = sequence->GetLongLong(wxT("increment_by"));
cycled = sequence->GetBool(wxT("is_cycled"));
called = sequence->GetBool(wxT("is_called"));
delete sequence;
}
else
{
goto Error;
}
pgSequence seqnew = pgSequence(targetschema, seqname);
wxString sql = wxT("-- Sequence: ") + seqnew.GetQuotedFullIdentifier() + wxT("\n\n")
+ wxT("-- DROP SEQUENCE ") + seqnew.GetQuotedFullIdentifier() + wxT(";")
+ wxT("\n\nCREATE SEQUENCE ") + seqnew.GetQuotedFullIdentifier()
+ wxT("\n INCREMENT ") + increment.ToString()
+ wxT("\n MINVALUE ") + minValue.ToString()
+ wxT("\n MAXVALUE ") + maxValue.ToString()
+ wxT("\n START ") + lastValue.ToString()
+ wxT("\n CACHE ") + cacheValue.ToString();
if (cycled)
sql += wxT("\n CYCLE");
sql += wxT(";\nALTER TABLE ")
+ seqnew.GetQuotedFullIdentifier() + wxT(" OWNER TO ") + qtIdent(owner) + wxT(";\n");
if (!seq.GetConnection()->BackendMinimumVersion(8, 2))
sql += seq.GetGrant(wxT("arwdRxt"), wxT("TABLE ") + seqnew.GetQuotedFullIdentifier());
else
sql += seq.GetGrant(wxT("rwU"), wxT("TABLE ") + seqnew.GetQuotedFullIdentifier());
wxString cmt;
if (!comment.IsNull())
{
cmt = wxT("COMMENT ON SEQUENCE ") + seqnew.GetQuotedFullIdentifier()
+ wxT(" IS ") + seqnew.qtDbString(comment) + wxT(";\n");
sql += cmt;
}
rc = targetconn->ExecuteVoid(sql, false);
if (!rc) goto Error;
break;
}
i++;
}
}
}
}
{
wxString createsql = table1->GetSql(browser);
createsql.Replace(srcschema->GetIdentifier() + wxT("."), targetschema->GetIdentifier() + wxT("."), true);
rc = targetconn->ExecuteVoid(createsql, false);
if (!rc) goto Error;
if (sourceconn->GetDbname() + sourceconn->GetHost() == targetconn->GetDbname() + targetconn->GetHost())
{
wxString copysql =
wxT("\nINSERT INTO ")
+ targetschema->GetQuotedPrefix() + table1->GetQuotedIdentifier()
+ wxT(" (SELECT * FROM ")
+ srcschema->GetQuotedPrefix() + table1->GetQuotedIdentifier()
+ wxT(")\n\n");
rc = targetconn->ExecuteVoid(copysql, false);
if (!rc) goto Error;
}
else
{
wxString tmpFilename;
wxFile tmpFile;
tmpFilename = wxFileName::CreateTempFileName(wxT("copyobject"));
tmpFile.Open(tmpFilename.c_str(), wxFile::write);
if (!tmpFile.IsOpened())
{
lastError = _("Can't create temporary file: ") + tmpFilename;
goto Error1;
}
wxString copysql =
wxT("COPY ")
+ srcschema->GetQuotedPrefix() + table1->GetQuotedIdentifier()
+ wxT(" TO STDOUT");
lastError = ::do_copy(sourceconn->connection(), copysql, tmpFile);
if (lastError == wxEmptyString)
{
tmpFile.Close();
tmpFile.Open(tmpFilename.c_str(), wxFile::read);
if (!tmpFile.IsOpened())
{
lastError = _("Can't open temporary file: ") + tmpFilename;
wxRemoveFile(tmpFilename);
goto Error1;
}
copysql =
wxT("COPY ")
+ targetschema->GetQuotedPrefix() + table1->GetQuotedIdentifier()
+ wxT(" FROM STDIN");
lastError = ::do_copy(targetconn->connection(), copysql, tmpFile);
}
tmpFile.Close();
wxRemoveFile(tmpFilename);
if (lastError != wxEmptyString)
{
goto Error1;
}
}
goto Success;
}
Error:
lastError = targetconn->GetLastError();
Error1:
targetconn->ExecuteVoid(wxT("ROLLBACK"));
return lastError;
Success:
targetconn->ExecuteVoid(wxT("COMMIT"));
return lastError;;
}
void frmPasteObject::process()
{
if (!sourceobj || !targetobj)
{
return;
}
wxArrayString *srcObjArray;
pgSchema *targetschema = (pgSchema *)targetobj;
pgSchema *srcschema = 0;
pgTable *table = (sourceobj->GetMetaType() == PGM_TABLE) ? (pgTable *)sourceobj : 0;
if (table)
{
wxMessageBox(
wxT("Paste source table\n") +
table->GetSchema()->GetDatabase()->GetIdentifier() + wxT(".") + table->GetSchema()->GetIdentifier() + wxT(".") + table->GetIdentifier() + wxT("\n") +
wxT(" into schema\n") + targetschema->GetDatabase()->GetIdentifier() + wxT(".") + targetschema->GetIdentifier());
}
else
{
srcschema = (pgSchema *)sourceobj;
wxMessageBox(
wxT("Paste source schema objects\n") +
srcschema->GetDatabase()->GetIdentifier() + wxT(".") + srcschema->GetIdentifier() + wxT("\n") +
wxT(" into schema\n") +
targetschema->GetDatabase()->GetIdentifier() + wxT(".") + targetschema->GetIdentifier());
}
pgConn *sourceconn = sourceobj->GetConnection();
pgConn *targetconn = targetobj->GetConnection();
if (!sourceconn || !targetconn)
{
wxMessageBox(
_("Both source and target schema connections should be established before paste object operation !"));
return;
}
if (srcschema)
{
srcObjArray = ::getSchemaTables(srcschema);
}
else
{
srcObjArray = new wxArrayString();
srcObjArray->Add(table->GetIdentifier());
srcschema = table->GetSchema();
}
if (srcschema->GetIdentifier() == targetschema->GetIdentifier())
{
wxMessageBox(_("Source and target schema should be different schema for paste object operation !"));
return;
}
srcschema->ShowTreeDetail(mainform->GetBrowser());
wxString msg;
for(unsigned int i = 0; i < srcObjArray->Count(); i++)
{
msg = _("COPY TABLE:") +
srcschema->GetDatabase()->GetIdentifier() + wxT(".") + srcschema->GetIdentifier() + wxT(".") + srcObjArray->Item(i) +
_(" INTO:") + targetschema->GetDatabase()->GetIdentifier() + wxT(".") + targetschema->GetIdentifier();
mainform->GetStatusBar()->SetStatusText(msg, 1);
//future implementation
pgTable *table1 = 0;
wxTreeItemIdValue schemacookie;
wxTreeItemId schemaid = srcschema->GetId();
wxTreeItemId schemaitem = mainform->GetBrowser()->GetFirstChild(schemaid, schemacookie);
bool found = false;
while (schemaitem && !found)
{
pgObject *obj = mainform->GetBrowser()->GetObject(schemaitem);
if (obj && obj->GetMetaType() == PGM_TABLE)
{
wxTreeItemIdValue tablecookie;
wxTreeItemId tableitem = mainform->GetBrowser()->GetFirstChild(obj->GetId(), tablecookie);
while (tableitem)
{
table1 = (pgTable *)mainform->GetBrowser()->GetObject(tableitem);
if (table1->GetIdentifier() == srcObjArray->Item(i))
{
found = true;
break;
}
table1 = 0;
tableitem = mainform->GetBrowser()->GetNextChild(obj->GetId(), tablecookie);
}
}
schemaitem = mainform->GetBrowser()->GetNextChild(schemaid, schemacookie);
}
if (!table1)
{
msg = _("WARNING SOURCE TABLE DISAPEARED:\n") +
srcschema->GetDatabase()->GetIdentifier() + wxT(".") + srcschema->GetIdentifier() + wxT(".") + srcObjArray->Item(i);
}
else
{
msg = ::copyTable(mainform->GetBrowser(), srcschema, targetschema, table1);
if (msg != wxEmptyString)
{
wxMessageBox(msg,
_("Cannot paste object:") +
targetschema->GetDatabase()->GetIdentifier() + wxT(".") + targetschema->GetIdentifier() + wxT(".") + table1->GetIdentifier(),
wxOK | wxICON_ERROR);
}
}
}
msg = wxString::Format(_("%d TABLE(s) COPIED FROM %s TO %s"), srcObjArray->Count(),
(srcschema->GetDatabase()->GetIdentifier() + wxT(".") + srcschema->GetIdentifier()).c_str(),
(targetschema->GetDatabase()->GetIdentifier() + wxT(".") + targetschema->GetIdentifier()).c_str());
mainform->GetStatusBar()->SetStatusText(msg, 1);
delete srcObjArray;
}
frmPasteObject::~frmPasteObject()
{
}
module-mk.diff
Description: Binary data
-- Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers
