Detailed Notes
Focus for this episode: In this episode, we look at a sync for functions between our 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
[Music] well hello and welcome back we're continuing our series our yeah i guess it's our series of sql tutorials and then we had some python tutorials actually i think we did those reverse order but now we're combining those into our database synchronization script this is in python and this is where we're basically looking at one set of uh one database and it's tables and functions and things like that and data and being able to replicate that into another one actually synchronize it in in particular it's just great if you're doing development and things of that nature it's one of those tools i've wanted to have for a while and figured i'm going to go ahead and build it myself this episode we're going to continue sort of along the lines what we're looking at last time we were doing store procedures and i think we're going to find that for functions it's going to be very similar so if we look at what we did for our store procedures then what we're going to have here is we should be able to do essentially the same thing and if we look in our database let's see well let's try this let's do without routine yeah that's like everything in the world so what we do need is we need our schema and let me see if i can do this i can read it uh oh yeah tutorial did i have that twice uh oh one source once destination okay that makes sense so here we go for our procedures we can see how that works now let's see if i bet if we call it function we're gonna see our functions boom and we have one function add three so let's go ahead and see if we can follow that thought through and do this for functions so did i say function i said function so i'm going to come through here and do function and it's going to be very simple so i need to create a function we're just going to leave it as we'll leave our variable names the same and let's actually jump all the way down here where we did missing procedures this is going to be missing functions this would be great if this is one of those things where there we go so let's go back up here be great if this is one of those we can pretty quickly replicate our functionality and cover more so now we're going to come in here and then when we create it we've got to get the definitions so we're going to do the same thing here and this one's going to be a little more complicated let's see okay so let's start with this let's try to get our source so if we try to go get our definition function schema name is going to be tutorial tutorial and procedure name is going to be add three and if we do that let's see if that works it does look it's very adding three so it's a very simple one we look at our source so now let's take the same thing and we built our variables here ah see and i think oh we may be able to do this exactly the same uh and this is gonna be create a replace function so what we do need here is we're going i don't know if it'll show me the uh let's see if i'll do script function clipboard create let's go look at what yeah see i don't know it doesn't like that right now so let's take this uh specific schema this tutorial so we'll just put that there specific name it's going to be the function which is add three so let's see oh i just saw that there it doesn't like that let's do this okay so we have our parameters we've got one parameter so now what we need to know and right here so it's giving us return so we should be able to build our function completely based on that so let's see what happens and if we run it okay so it doesn't uh oh let's see if we do so here it was saying [Music] so that's what it's saying that we should be able to do so let's do that so for now it's going to be local uh so if we go to our [Music] local mysql which i never can find right somewhere in here a little my sequel rep that's not really the one i want but that'll probably do um so if we do this let's do this okay oh it doesn't like that so let me go change to there we go okay so we have create a replace function and we do it again and if we take that i think we want to do the entire let's see uh let's run here okay so let's go jump to oh not that okay we want to do a function [Music] and we're missing something so let's go do a quick look here and we're going to go here and uh and there it is so we're gonna do cat day 19 and let's see ah so see we have this returns so we need our return so so let's go let's see let's see let's do let's see if we can stored function uh chrissy let's see so my sequel where is the return value stored for a function and and so we're gonna have to do i think we have to do a little bit of research so if we go back over here up here here where oh that one so we need parameters now let's go look at our information schema and let's see if we have return values we have routines but that's what we're only looking at um let's see let's go here let's uh select star from routines for this one and let's see if we can find our returns afloat here we go so that's data type and let's see if it tells me deterministic doesn't tell me that so if i take the same thing and i do uh returns float oh that probably will work so if i come over here and i do um that comes after this returns float and run it uh do i need a nope should be okay there okay so it runs so now i can do drop function add three uh tutorial dot add three and wrong there we go okay so i need to get my returns so what i need to do is from the uh whoops from the so when i get the information schema i need the routine name but i also want the data type oh i don't want that i don't want to save to a random file so up here routine name comma data type and i might as well get it from both and then i'm going to come into ho so there's my there's that so here uh what i want is so that's a destination so here okay so i've got proc name 0 but it's actually proc name 1 is what i'm going to need for this so i'm going to need once i build all of these i'm going to need to do a returns plus proc name one plus a space and i should be good let's see if that works for me uh if i come here and i do a main whoa it worked so now i have on my local um well if i can go here and if i go local and let's find all my functions i'm going to see that boom i've got my function that i just created a few moments ago and i should be ready so if i look at that same one and demo he should be the same thing uh let's do this we'll just store these values just to be safe and we can see function who built it dates definer get your little character set stuff but we're good so if we go to our uh select is going to be a little more complicated but um we'll probably leave it for now because i think we've got what we do is we have what we need so now if we do call and three i'm in the right one no i want to do it locally local call add three two five uh oops i need to select my database and uh tutorial ad three does not exist um oh it's probably c so let's see select that's what i want that's it okay i forgot it's not a call and so now what we have done is we have fairly quickly gotten our procedures added in which helps us quite a bit which leaves us really with triggers are probably the thing that we're going to hit next and i don't know that i have any let's see if we got any in our little testing oh we do so i've got a pair of triggers let's see while we're here because we probably won't get it this time but we can go triggers so let's do a little select star from triggers and we're going to look for uh named my first or actually put trigger in the name or my so if we do select star uh tutorial.oops start from informationschema.triggers there we go oh so it doesn't have any in this so we are not seeing that so let's go let's find a select distinct routine type from information schema routine so see if we've got something else here that we can look at oops i should spell that right oh local i don't want the local i want the demo i don't want tutorial let's go see maybe there we go so i've got my triggers so that's going to be our next thing we're going to do is it looks like we're going to be able to dive into that we actually have our action statement we have our condition so we'll have to go back and look at how do we create a trigger and then i think we've got what we need so we'll be able to do that build that out and start getting those synchronized as well so we're getting pretty far uh we're really down to you know the big thing is going to be we're going to swing back and deal with uh some of our data issues as far as doing partial data because there's going to be some there's going to be some challenges there uh and some of the things we do particularly because we were cleaning the data out you have some things have to be done in a specific order that being said we can call this one a wrap so we'll wrap this up we'll get it out there stuff will be out in the show links as always we will you know we'll have updates she can go dig through and find it at our github account you can check out the source for this and play around and we will be adding more stuff down the road here as we're going through it to make it even more general purpose and maybe even useful for you so that being said go out there and have yourself a great day a great week and we will talk to you next time [Music] you
Transcript Segments
[Music]
well hello and welcome back we're
continuing our series our
yeah i guess it's our series of
sql tutorials and then we had some
python tutorials actually i think we did
those reverse order but now we're
combining those into our database
synchronization script this is in python
and this is where we're
basically looking at one set of
uh one database and it's tables and
functions and things like that
and
data and being able to replicate that
into another one actually synchronize it
in
in particular it's just great if you're
doing development and things of that
nature it's one of those tools i've
wanted to have for a while and figured
i'm going to go ahead and build it
myself
this episode we're going to continue
sort of along the lines what we're
looking at last time we were doing store
procedures
and i think we're going to find that for
functions
it's going to be very similar so if we
look at what we did for
our store procedures
then what we're going to have here
is we should be able to do essentially
the same thing and if we look in our
database
let's see
well let's try this let's do without
routine
yeah that's like everything in the world
so what we do need is we need our schema
and let me see if i can do this i can
read it
uh oh yeah tutorial
did i have that twice uh oh one source
once destination okay that makes sense
so here we go for our procedures we can
see how that works now let's see if
i bet if we call it function we're gonna
see
our functions boom and we have one
function
add three so let's go ahead and see
if we can follow that thought through
and do this for functions
so did i say function i said function
so i'm going to come through here and do
function
and it's going to be very simple so i
need to create a function
we're just going to leave it as we'll
leave our variable names the same
and let's actually jump all the way down
here
where we did missing procedures
this is going to be
missing functions
this would be great if this is one of
those things where
there we go so let's go back up here be
great if this is one of those we can
pretty quickly
replicate our functionality
and cover more so now we're going to
come in here and then when we create it
we've got to get the definitions so
we're going to do the same thing here
and this one's going to be a little more
complicated let's see
okay so let's start with this let's try
to get our
source
so if we try to go get our definition
function schema name is going to be
tutorial
tutorial
and procedure name is going to be add
three
and if we do that let's see if that
works
it does look it's very adding three so
it's a very simple one we look at our
source so now let's take the same thing
and we built
our variables here
ah see and i think oh we may be able to
do this exactly the same
uh and this is gonna be create a replace
function
so what we do need here
is we're going i don't know if it'll
show me the
uh let's see if i'll do script function
clipboard
create let's go look at what yeah see i
don't know it doesn't like that right
now
so
let's take this
uh specific schema
this tutorial so we'll just put that
there
specific name it's going to be the
function which is add three
so let's see oh
i just saw that there it doesn't like
that let's do this
okay so we have our parameters we've got
one parameter so now what we need to
know
and right here so it's giving us return
so we should be able to build our
function completely
based on
that so let's see what happens
and if we run it okay so it doesn't
uh
oh
let's see if we do
so here it was saying
[Music]
so that's what it's saying that we
should be able to do
so let's do
that
so for now it's going to be local uh so
if we go to our
[Music]
local mysql which i never can find right
somewhere in here a little my sequel rep
that's not really the one i want but
that'll probably do
um so if we do this let's do this
okay oh it doesn't like that so let me
go change to there we go
okay so we have create a replace
function
and we do it again
and if we take that i think we want to
do the entire
let's see
uh
let's run here
okay so let's go
jump to
oh not that
okay we want to do a
function
[Music]
and we're missing something so let's go
do a quick look here
and we're going to go
here
and
uh
and there it is so we're gonna do cat
day 19
and let's see
ah so see we have this returns
so we need our return
so
so let's go
let's see
let's see
let's do
let's see if we can
stored function
uh chrissy let's see
so my sequel where is the return
value stored for a function
and
and
so we're gonna have to do i think we
have to do
a little bit of research
so if we go back over here up here here
where oh
that one
so we need
parameters now let's go look at our
information schema
and let's see if we have return values
we
have
routines
but that's what we're only looking at um
let's see let's go here let's uh select
star
from routines
for this one
and let's see if we can find our
returns afloat
here we go so that's data type
and let's see if it tells me
deterministic doesn't tell me that
so if i take the same thing and i do
uh returns float
oh that probably will work so if i come
over here and i do
um
that comes after this
returns
float
and run it uh do i need a
nope should be okay
there okay so it runs so now i can do
drop function
add three
uh tutorial dot add three
and wrong
there we go okay so i need to get my
returns so what i need to do is from the
uh whoops from the
so when i get the information schema i
need the routine name but i also want
the
data type
oh i don't want that i don't want to
save to a random file
so up here
routine name comma data type
and i might as well get it from both
and then i'm going to come into ho so
there's my there's that
so here
uh what i want is
so that's a destination so here
okay so i've got proc name 0 but it's
actually proc name 1 is what i'm going
to need for this so i'm going to need
once i build all of these
i'm going to need to do a returns
plus
proc name one
plus
a space
and i should be good
let's see
if that works for me
uh if i come here and i do a main
whoa
it worked
so now i have on my local
um well
if i can go here and if i go local
and
let's find all my functions i'm going to
see that boom i've got my function that
i just created a few moments ago
and i should be ready so if i look at
that same one
and demo he should be the same thing uh
let's do this
we'll just store these values just to be
safe
and we can see
function who built it dates definer
get your little character set stuff but
we're good
so if we go to our
uh select
is going to be
a little more complicated but
um
we'll probably leave it for now because
i think we've got what we do is we have
what we need so now if we do call
and three i'm in the right one no i want
to do it locally
local call add three two five
uh oops i need to select my database
and
uh tutorial ad three does not exist
um
oh it's probably c so let's see
select that's what i want
that's it okay
i forgot it's not a call
and so now what we have done is we have
fairly quickly gotten our procedures
added in
which helps us quite a bit which leaves
us
really with triggers are probably the
thing that we're going to hit next and i
don't know that i have any
let's see if we got any in our little
testing oh we do so i've got a pair of
triggers
let's see
while we're here
because we probably won't get it this
time but we can go
triggers
so let's do a little select star
from triggers
and we're going to look for
uh named
my
first or actually put trigger in the
name
or my so if we do select star uh
tutorial.oops
start from informationschema.triggers
there we go oh so it doesn't have any in
this so we are not seeing that so let's
go
let's find a select
distinct
routine
type
from information schema routine so see
if we've got something else here that we
can look at
oops i should spell that right
oh
local i don't want the local i want the
demo
i don't want
tutorial
let's go see maybe
there we go
so i've got my triggers
so that's going to be our next thing
we're going to do is it looks like we're
going to be able to dive into that we
actually have our action statement we
have our condition so we'll have to go
back and look at how do we create a
trigger
and then i think we've got what we need
so we'll be able to do that build that
out
and start getting those synchronized as
well so we're getting pretty far
uh we're really down to you know the big
thing is going to be we're going to
swing back
and deal with uh some of our data issues
as far as doing partial data because
there's going to be some there's going
to be some challenges there
uh and some of the things we do
particularly because we were cleaning
the data out you have some things have
to be done in a specific order
that being said
we can call this one a wrap
so we'll wrap this up we'll get it out
there stuff will be out in the show
links as always we will you know we'll
have updates she can go dig through and
find it at our github account you can
check out the source for this
and play around and we will be adding
more stuff down the road here as we're
going through it
to make it even more general purpose and
maybe even useful for you
so that being said go out there and have
yourself a great day a great week and we
will talk to you
next time
[Music]
you