round robin implementation issue

[Paolo Di Prodi]

Hello guys,
I am implementing a round robin database for my project in SQLAlchemy and I have some problems with TRIGGERS.
Everything works but for each INSERT OR REPLACE, the trigger is not called at all so in this case I would have only 25 rows rather than 50.

Any suggestion to debug it?

Here's my code:

        def InitRoundRobin(self):
            """
            Creates all the tables necessary one for the network swap events and one for the motion
            """
            #print "Creating database "+self.__file_path
            self.__file_event = Table('network', self.__metadata,
                Column('rrd_key', Integer, Sequence('rrd_key_seq'), primary_key=True),
                Column('name', String, default="NONE"),
                Column('location', String, default="NONE"),
                Column('type', String,default="NUM"),
                Column('direction', String,default="INP"),
                Column('value', String,default="0"),
                Column('time',  TIMESTAMP(), default=now()),
                UniqueConstraint('name', 'time', name='name_time')
            )
            self.__file_event.create()  
           
            self.__file_rrd = Table('rrdkey', self.__metadata,
                Column('rrd_key', Integer, Sequence('rrd_key_seq'), primary_key=True)
            )
            self.__file_rrd.create()   
            operation = self.__file_rrd.insert()
            ## attempt an insert
            result=operation.execute(rrd_key=0);
           
            self.rrd_ins = DDL('''\
            DROP TRIGGER IF EXISTS rrd_ins;
            DELIMITER $$
            CREATE TRIGGER  rrd_ins
            BEFORE INSERT ON network
            FOR EACH ROW
            BEGIN
              SET @rrd_key = 0;
              SET @rows = 10;
              IF NEW.rrd_key = 0 THEN
                SELECT rrd_key + 1
                  FROM rrdkey
                  INTO @rrd_key;
                SET NEW.rrd_key = @rrd_key;
              END IF;
              IF (NEW.rrd_key % @rows) THEN
                SET NEW.rrd_key = NEW.rrd_key % @rows;
              ELSE
                SET NEW.rrd_key = @rows;
              END IF;
              UPDATE network SET rrd_key = NEW.rrd_key;
            END;
            $$
            DELIMITER;''')
            event.listen(self.__file_event, 'after_create', self.rrd_ins)

And the REPLACE code:

        def addEntryReplace(self, location,name,value,type):
            #print "Adding entry"
            #operation = self.__file_event.insert()
            t = text("INSERT OR REPLACE INTO network (location,name,value,type,time) VALUES (:location,:name,:value,:type,:time)")
            print t
            #operation.prefix_with("OR REPLACE");
            result=self.__connection.execute(t, location=location, name=name, value=value, type=type, time=now())        

This is called like this:

        def loopTest(self):
            self.__connection=connection = self.__db.connect()
            for i in range(0, 50):
                self.addEntryReplace("CASA","PARAM",i,"TYPE")
                print "Added entry ", i

            self.__connection.close()

[Michael Bayer]

On Oct 3, 2012, at 7:15 AM, Paolo Di Prodi wrote:

Hello guys,
I am implementing a round robin database for my project in SQLAlchemy and I have some problems with TRIGGERS.
Everything works but for each INSERT OR REPLACE, the trigger is not called at all so in this case I would have only 25 rows rather than 50.

Any suggestion to debug it?

Since you're using a plain SQL string, you'd probably want to run that same SQL in the command line for your database.    Getting it to work with just the plain database console would be the first step.  From there, the client API should work the same way.

I'm not sure what database this is, but you should carefully review its documentation to see what the specified behavior is regarding an INSERT trigger and REPLACE.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/1vqI9TpN-IEJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.