Detailed Notes
Focus for this episode: In this episode, we dig into synchronizing our triggers across databases.
This tutorial walks through the creation of a database synchronization tool in Python. It continues our Python and SQL Tutorials with a useful application that provides a deeper look at SQL structures and commands.
Repository For Code: git clone [email protected]:rbroadhead/dbsync.git
Transcript Text
thank you [Music] well hello and welcome back we are continuing our series of python and SQL tutorials we're working on our database synchronization and we've worked our way up to triggers we looked at proceeds or procedures looked at functions and now we started looking at it last time looked at what triggers sort of looked like and we're going to see if we can do that for our uh we use that to create our triggers and so what we're going to do is we're going to go ahead and we're going to do let's see so we've got our triggers and we're going to do where event object schema equals because we need to make sure that we've got that right here well let's do trigger schema so we definitely need to get our database there and that's probably it for right now so let's take that over what we'll do is we're going to end up uh let's see still like this um go ahead and pull this guy here whoops like that foreign specify a little bit better what we're working with here if we get a little further down so we're going to go through each one again and we're going to do it by the name which is going to be so trigger underscore name is going to be first let's just do this it doesn't like that that's not valid but we'll fix it in a minute oh well let's just get rid of that anyways okay so we're gonna have that so that'll give us our names and now we're going to say I need to create trigger and so let's see if we can get away with simply uh where did we start our uh I need to create query blah blah blah blah blah blah blah blah here we go so here's where we start [Music] so what we want to do oops is create a replace trigger and I'm using this little uh let's do uh it's like this so we can see a little better and maybe not so I'm going to use this so we're going to do create a replace trigger not Tigger but trigger and then we're going to give it its source name which is whatever the name is and then Source name so proc name we're gonna have to take a look at that now what we've got here is we probably want to keep that Source name we didn't do it here but it really because I think we can make it tutorial dot we can do something like that um so what we probably want to do is leave our source name our trigger name which is our product name is going to be zero so let's say that now we're going to do this before update so what we can see here there's the event manipulation and I bet and there's an action timing so let's look at this what we need is uh trigger name comma action timing it was what before update uh before update on address okay so that's going to be the table okay so what we're going to need is we're going to need our uh whoops where did I see that before that was the action timing okay and then I need um event manipulation manipulation and I need to spell that right and let's go ahead and get that but now we're also going to need we need the object table we know we're going to need that and then we're going to need what do we need next uh let's see for each row and so what we're probably going to see is yeah whoops that's loud for nope let's see so I don't think we need that I think we just need each row so it's gonna be four each and we need action orientation orientation and then we need we need the command just going to be the action statement statement so that should be well let's do it this way um we don't want to lose our little piece there so we go back up to our query is that right yep so that's our trigger schema that's our trigger schema which I assume is not here anywhere uh well it is but then do we need yeah I think we do need that so let's leave that so we're going to do create a replace Source name and then this is going to be whoops this does not need that instead it needs a space because we give it the name and then we're going to give it it's not actually proc name but that's okay uh oh we want it in palmro okay so this is going to be a is that right nope we don't want palmro uh we don't need that we don't need that we do need let's make sure this is right so Source proc name and prox okay so that's okay there we go okay so now we're going to do that plus now we're gonna have to sort of walk our way through this one a little bit so the next thing we're going to do is the action timing so that gives us a for example before plus space Plus the event manipulation and then uh this is going to be uh let's do this uh proc Al equals proc SQL Plus so now we've got uh let's see for update so this is going to be on and then we've got to give it our name which is going to be three which is one Let's see Trigger name two three zero one two three which is the event object table cool we got that and then this is going to be plus for each and then we're going to have to give it that scope which is the action orientation and then we're going to add a space let's see it this way and then after that we do a space and we do the statement itself which is five and that should do it so let's try that uh let's see so we don't need a bar cursor anymore and let's not worry about executing it uh let's do yeah let's not worry about executing it and uh uh let's do that so let's come down here we're gonna do our missing triggers missing index missing functions missing procedures let's move this up a little bit and this is going to be missing triggers and that should all be good that should be good we're going to spell this right and then if we run it oh I'm in the wrong place there now if I come in and run it let's see what happens bam okay so we came in here and we've got something here uh start missing triggers command query blah blah blah 462. okay oops what did we miss uh oops so if we go to 137 oops uh that's what we don't need let's try that uh let's go back and try this real quick did we do that right oh okay so it's getting us something let's try it again there we go so now first trigger is going to be create or replace that guy and then the second we're going to do it so let's see if those execute uh that looks good and let's see if that runs boom those were created let's see if it does nothing this time and it does nothing this time so now we have provided our triggers that gives us a solid piece of work here um let's see I don't need any of that I don't need those notes I will be updating this stuff uh got indexes now the challenge is going to become here when we deal with our rows now with our current database we're going to go through and we're just going to blow stuff away and so let's see if that even works right now so if we do it and we say let's take the whole thing that we're working with let's see I don't think I have any more quits I'll make sure before I go too far good okay I don't have any more quits there's no quitting this [Music] and so now we're going to see which it looks like it's doing pretty good uh we're getting through here I probably don't need all of those insert statements see let's do when I do sync rows uh let's see so I don't want to print the insert statement I just want to print uh let's do it here let's do data sync for and give it a table and so we're going to tweak this a little bit now here we go so now we're getting some more stuff and again not really seeing an error so it's looking good the challenge becomes in two cases one is when we have data that is linked that is uh where you've got some sort of foreign key relationship because you cannot delete the parent before you delete the child so we have to in that case if we're going to do something like that if we're going to actually delete then what we're going to need to do is we're going to have to have some way to essentially find those foreign key relationships and then walk the key and so let's put these in our nodes here we'll have a little bit of notes Here notes for data sync so one is we need to figure out how to walk the foreign key foreign key ladder basically that's called hierarchy this thing so going oh yeah it's still going oh way down there there we go so it looked like it all ran which is good but now we're going to figure out how to walk the foreign key hierarchy we need to figure out um and that's for you know that cases and then we also need to do how to uh let's say import related data for a table Max rows won't work because for example let's say we only take the first 10 rows and let's say we have um 15 users but they only use or actually let's say we have 10 users and they use across them they have 15 addresses so when you do a limit of rows which you know if you go back to what we did when we did this we just did uh if we look at columns saying data or sync rows we just came in and we did this limit so we did the rows and we did a limit and remember we started at the the most recent stuff however that could cause US problems because we could have a situation where we are limiting our rows and we're losing data that we need you know those 10 customers may need 15 addresses or what would be a bigger problem is we grab 10 addresses but we don't grab them for the right customers and depending on how that relationship works then of course that would break so we're probably going to need to do is provide a couple of different ways and we'll take this down here so we're going to do multiple ways multiple if I can spell it right multiple ways to limit data moved because we have to think through that one and that's going to be a non-trivial a little piece of work one of the things I also want to do is which will probably come sooner is generalize extract the key functions and provide a different let's say a way to walk to walk multiple databases because we're going to see this at times as well so we want to be able to uh I just want to say sort of clean up is we're going to pull some of this stuff out we're going to clean a couple of these things up and we probably want along with that is we're going to have some parameters let's see command line parameters for what to sync so now we're starting to get a little more complicated here and then what's the let's see with the data uh yeah we'll probably go with that for now so these are some things coming up I wanted to sort of list these out and just do them as part of this you know so it's a nice little quick kind of episode here for triggers and we're going to start getting into the complicated stuff next and how can we possibly do some of these things and probably we're going to start maybe we'll pull some stuff out first but I think one of the first things we're probably gonna have to do is we're going to have to figure out this foreign key hierarchy is we're going to have to figure out like what are the the tables that do not have anything that rely on them and then figure out what are the parents and the children and the grandchildren and such and so forth we've actually seen some of that already we've got some of that information it's just we're going to have to actually think through it and do it in a little bit more intentional approach that being said I think it's time to wrap this one up so go out there and have yourself a great day a great week and we will talk to you next time [Music]
Transcript Segments
thank you
[Music]
well hello and welcome back we are
continuing our series of python and SQL
tutorials we're working on our database
synchronization and we've worked our way
up to triggers we looked at proceeds or
procedures looked at functions and now
we started looking at it last time
looked at what triggers sort of looked
like
and we're going to see if we can do that
for our uh we use that to create our
triggers
and so what we're going to do is we're
going to go ahead and
we're going to do let's see so we've got
our triggers and we're going to do where
event
object schema
equals because we need to make sure that
we've got that right
here well let's do trigger schema
so
we definitely need to get our database
there
and that's probably it for right now
so
let's take that over what we'll do is
we're going to end up
uh let's see
still like this
um
go ahead and pull this guy here whoops
like that
foreign
specify a little bit
better
what we're working with here if we get a
little further down so we're going to go
through each one again
and we're going to do it by the name
which is going to be so trigger
underscore name is going to be first
let's just do this
it doesn't like that that's not valid
but we'll fix it in a minute
oh well let's just get rid of that
anyways okay so we're gonna have that so
that'll give us our names
and now we're going to say
I need to create trigger
and so let's see
if we can get away with simply
uh where did we start our
uh I need to create query blah blah blah
blah blah blah blah blah
here we go
so here's where we start
[Music]
so what we want to do oops is create a
replace trigger
and I'm using this little uh
let's do uh it's like this
so we can see a little better and maybe
not
so I'm going to use this so we're going
to do create a replace trigger not
Tigger but trigger
and then we're going to give it its
source name
which is whatever the name is and then
Source name so proc name
we're gonna have to take a look at that
now what we've got here is we probably
want to keep that Source name we didn't
do it here
but it really because I think we can
make it
tutorial dot we can do something like
that
um so what we probably want to do is
leave our source name our trigger name
which is
our product name is going to be zero so
let's say that now we're going to do
this before update so
what we can see here there's the event
manipulation
and I bet and there's an action timing
so
let's look at this what we need is
uh trigger name
comma
action timing
it was what before update
uh before update on address okay so
that's going to be the table
okay
so what we're going to need is we're
going to need our
uh
whoops where did I see that before that
was the action timing okay
and then I need
um event manipulation
manipulation
and I need to spell that right
and let's go ahead and get that but now
we're also going to need we need the
object table
we know we're going to need that
and then we're going to need what do we
need next uh let's see
for each row and so what we're probably
going to see is
yeah
whoops that's loud for
nope let's see so I don't think we need
that I think we just need each row
so it's gonna be four each and we need
action orientation
orientation
and then we need we need the command
just going to be the action statement
statement so
that should be well let's do it this way
um
we don't want to lose our little piece
there so we go back up to our
query
is that right yep so that's our trigger
schema
that's our
trigger schema
which I assume is not here anywhere
uh well it is but then do we need
yeah I think we do need that
so let's leave that so we're going to do
create a replace
Source name and then this is going to be
whoops
this does not need that instead it needs
a space because we give it the name
and then we're going to give it it's not
actually proc name but that's okay
uh
oh we want it in palmro okay
so this is going to be a
is that right nope we don't want palmro
uh we don't need that we don't need that
we do need let's make sure this is right
so Source proc name and prox okay so
that's
okay there we go okay so now we're going
to do that plus
now we're gonna have to sort of walk our
way through this one a little bit
so the next thing we're going to do is
the action timing so that gives us a for
example before
plus space
Plus
the event manipulation
and then uh
this is going to be
uh let's do this uh proc
Al equals
proc SQL Plus
so now we've got uh let's see for update
so this is going to be
on and then we've got to give it
our name which is going to be three
which is one Let's see Trigger name two
three zero one two three which is the
event object table cool we got that and
then this is going to be plus
for each and then we're going to have to
give it that scope
which is the action orientation
and then
we're going to add a space let's see it
this way
and then after that we do a space
and we do the statement itself which is
five
and that should do it
so let's try that uh let's see so
we don't need a bar cursor anymore and
let's not worry about executing it
uh let's do yeah let's not worry about
executing it
and uh
uh let's do that so let's come down here
we're gonna do our missing triggers
missing index
missing functions missing procedures
let's move this up a little bit
and this is going to be
missing triggers
and that should all be good that should
be good we're going to spell this right
and then
if we run it oh I'm in the wrong place
there now if I come in and run it
let's see what happens
bam okay so we came in here
and we've got something here uh start
missing triggers command query blah blah
blah 462. okay
oops what did we miss
uh
oops so if we go to 137
oops
uh that's what we don't need
let's try that uh let's go back and try
this real quick did we do that right
oh
okay so it's getting us something
let's try it again
there we go so now first trigger is
going to be create or replace
that guy and then the second we're going
to do it so let's see if those execute
uh that looks good
and let's see if that runs
boom those were created let's see if it
does nothing this time and it does
nothing this time so now we have
provided our triggers
that gives us a solid piece of work here
um let's see I don't need any of that I
don't need those notes I will be
updating this stuff uh
got indexes now
the challenge
is going to become here
when we deal with our rows
now with our current database we're
going to go through and we're just going
to blow stuff away
and so let's see if that even works
right now so if we do it and we say
let's take the whole
thing that we're working with let's see
I don't think I have any more quits I'll
make sure before I
go too far
good okay I don't have any more quits
there's no quitting this
[Music]
and so now we're going to see which it
looks like it's doing pretty good uh
we're getting through here
I probably don't need all of those
insert statements
see let's do
when I do sync rows
uh let's see so I don't want to print
the insert statement
I just want to print
uh
let's do it here
let's do
data sync
for
and give it a table
and so we're going to tweak this a
little bit now here we go so now we're
getting some more stuff
and again not really seeing an error so
it's looking good the challenge becomes
in two cases one is when we have data
that is linked that is uh where you've
got some sort of foreign key
relationship because you cannot delete
the parent before you delete the child
so we have to in that case
if we're going to do something like that
if we're going to actually delete then
what we're going to need to do is we're
going to have to have some way to
essentially find those foreign key
relationships and then walk the key
and so let's put these in our nodes here
we'll have a little bit of notes Here
notes for data sync
so one
is we need to figure out
how to walk the
foreign key
foreign key
ladder basically that's called hierarchy
this thing so going oh yeah it's still
going oh
way down there there we go
so it looked like it all ran
which is good but now we're going to
figure out how to walk the foreign key
hierarchy we need to figure out
um
and that's for you know that cases and
then we also need to do how to
uh let's say import
related data
for a table
Max rows won't work
because for example let's say we only
take the first 10 rows and let's say we
have
um
15 users
but they only use
or actually let's say we have 10 users
and they use across them they have 15
addresses
so when you do a limit of rows which you
know if you go back to what we did when
we did this we just did uh
if we look at
columns saying data or sync rows we just
came in
and we did this limit
so we did the rows and we did a limit
and remember we started at the the most
recent stuff however
that could cause US problems
because we could have a situation where
we are
limiting our rows and we're losing data
that we need you know those 10 customers
may need 15 addresses or what would be a
bigger problem is we grab 10 addresses
but we don't grab them for the right
customers and depending on how that
relationship works then of course that
would break
so we're probably going to need to do
is provide a couple of different ways
and we'll take this down here so we're
going to do
multiple ways
multiple if I can spell it right
multiple ways to limit data moved
because we have to think through that
one and that's going to be a non-trivial
a little piece of work
one of the things I also want to do is
which will probably come sooner is
generalize extract
the key functions
and
provide a different
let's say a way to walk
to walk
multiple databases
because we're going to see this at times
as well
so we want to be able to uh I just want
to say sort of clean up is we're going
to pull some of this stuff out we're
going to clean a couple of these things
up
and we probably want along with that is
we're going to have some parameters
let's see command line parameters
for what to sync
so now we're starting to get a little
more complicated here and then what's
the let's see with the data uh yeah
we'll probably go with that for now so
these are some things coming up I wanted
to sort of list these out and just do
them as part of this you know so it's a
nice little quick kind of episode here
for triggers and we're going to start
getting into the complicated stuff next
and how can we possibly do some of these
things and probably we're going to start
maybe we'll pull some stuff out first
but I think one of the first things
we're probably gonna have to do is we're
going to have to figure out this foreign
key hierarchy is we're going to have to
figure out like what are the the tables
that do not have anything that rely on
them and then figure out what are the
parents and the children and the
grandchildren and such and so forth
we've actually seen some of that already
we've got some of that information it's
just we're going to have to actually
think through it and do it in a little
bit more intentional approach
that being said I think it's time to
wrap this one up so go out there and
have yourself a great day a great week
and we will talk to you next time
[Music]