Re: Rolling my own replication

On Fri, 2002-07-26 at 00:30, Shanmugasundaram Doraisamy wrote:
> Dear Rob,
>       Your posting was something simillar to what we were just going to
> start.  We have not yet formulated a way of doing it.  We will keep you
> posted if we hit upon any solution. Also please do let us know if it
> worked and if you don't mind the final code that worked.  Thanks in
> advance,
>
> Yours sincerely,
> Shan.

Teh upload of updated rows workes, but I have only tested it on two
machines over a lan, with 10 or so over a dial up I am expecting timing
issues...

The code is fiarly simple at the moment, and I shall be cleaning it up
soon.  But I am still a bit unsure of how it will all work in the field,
and then with more than one slave I am unsure yet how to handle multiple
updates to the same data, or to even check for them at the moment...

basically I have a table in the database listing all tables I need to
sync with the master db,  A conection is opend to each database.

I select * from eactable where f_new = True then take the results and
insert them into the master like so (in python):

def send_new():
    "Hunts through the tables for new rows to sync with master"
    Lcr = DBcon.cursor()
    Mcr = MasterDBcon.cursor()
    sql = "SELECT * FROM syncro_tables"
    if verbose:
        print sql
    #ask master for tables to update.
    Mcr.execute(sql)
    tables = Mcr.fetchall()
    for i in tables:
        if log:
            syslog(LOG_INFO,"Scanning table " + i[0] + " for new data.")
        sql = "SELECT * from "
        sql = sql + i[0]
        sql = sql + " WHERE f_new='TRUE'"
        if verbose:
            print "Scanning table " + i[0] + " for new data."
            print sql
        Lcr.execute(sql)
        for j in range(Lcr.rowcount):
            result = Lcr.fetchone()
            sql = "INSERT INTO " + i[0] + " VALUES ("
            for k in range(len(result)):
                if result[k] == None:
                    sql = sql + "NULL, "
                else:
                    sql = sql + "'" + str(result[k]) + "', "
            sql = sql[:-2]
            sql = sql + ")"
            if verbose:
                print sql
            try:
                Mcr.execute(sql)
            except Error, Msg:
                msg = "SQL Statement was -> "
                msg = msg + sql
                if verbose:
                    print Msg, msg
                if log:
                    syslog(LOG_WARNING, "SQL Error on Master Database")
                    syslog(LOG_WARNING, str(Msg))
                    syslog(LOG_WARNING, msg)
            MasterDBcon.commit()
        #reset new flag
        sql = "UPDATE " + i[0] + " SET f_update='RESET' WHERE f_new='FALSE'"
        if verbose:
            print sql
        Lcr.execute(sql)
    Lcr.close()
    DBcon.commit()
    Mcr.close()
    MasterDBcon.commit()


def send_update():
    "Hunts through the tables for updated rows to sync with master"
    Lcr = DBcon.cursor()
    Mcr = MasterDBcon.cursor()
    sql = "SELECT * FROM syncro_tables"
    if verbose:
        print sql
    #ask master for tables to update.
    Mcr.execute(sql)
    tables = Mcr.fetchall()
    for i in tables:
        if log:
            syslog(LOG_INFO,"Scanning table " + i[0] + " for updated data.")
        sql = "select attname from pg_class,pg_attribute where
(pg_class.relname='" + i[0] +"') and
(pg_class.oid=pg_attribute.attrelid) and (pg_attribute.attnum >= 0)
order by attnum"
        Lcr.execute(sql)
        columns = Lcr.fetchall()
        if verbose:
            print "Columns in table " + i[0] +": "
            for l in range(len(columns)):
                print columns[l]
        sql = "SELECT * from "
        sql = sql + i[0]
        sql = sql + " WHERE f_update='YES' "
        if verbose:
            print "Scanning table " + i[0] + " for updated data."
            print sql
        Lcr.execute(sql)
        # build update and update master
        for j in range(Lcr.rowcount):
            result = Lcr.fetchone()
            sql = "UPDATE " + i[0] + " SET "
            # range from 5 as we dont want to update control fields
            for k in range(5,len(result)):
                cname = columns[k]
                sql = sql + cname[0] + " = "
                if result[k] == None:
                    sql = sql + "NULL, "
                else:
                    sql = sql + "'" + str(result[k]) + "', "
            sql = sql[:-2]
            sql = sql + " WHERE (sequence_key = '" + str(result[0]) + "') and
(location_key = '" +str(result[1]) + "')"
            if verbose:
                print sql
            try:
                Mcr.execute(sql)
            except Error, Msg:
                msg = "SQL Statement was -> "
                msg = msg + sql
                if verbose:
                    print Msg, msg
                if log:
                    syslog(LOG_WARNING, "SQL Error on Master Database")
                    syslog(LOG_WARNING, str(Msg))
                    syslog(LOG_WARNING, msg)
            MasterDBcon.commit()
        #reset new flag
        sql = "UPDATE " + i[0] + " SET f_update='RESET' WHERE f_update='YES' "
        if verbose:
            print sql
        Lcr.execute(sql)
    Lcr.close()
    DBcon.commit()
    Mcr.close()
    MasterDBcon.commit()




--

*
*  Rob Brown-Bayliss
*