Like you said, I'm talking today about using Ecto outside of Phoenix. And I know that a lot of people, when they get started with Elixir, they kind of hear from it by Phoenix or maybe hear it from other way. But I know I heard about Phoenix early on when I was learning about Elixir. But I thought I wanted to not learn Phoenix quite yet until I was pretty proficient in Elixir. So, I went out and looked for ways to do that. And one of the ways I like to learn things is to use a database. A lot of the things I do are backed by data. A lot of the things you guys do are backed by data. And if I could code up something as I'm learning to interact with data, then that helps me in my learning process. And so that's why I decided to go ahead and start learning Ecto, even though I hadn't learned Phoenix yet, in order to start doing that kind of data manipulation and storing things and retrieving things to the database. Even ones that have already been created. So interacting with databases that maybe are legacy databases. So that's kind of what we're gonna talk about today. First of all, I work for a company called Planning Center. I realize today I'm kind of a walking, you can't see it because I'm short. But I'm kind of a walking billboard for Planning Center. I even got socks on from Planning Center. That's not necessarily on purpose. It's just they happen to provide like three quarters of my wardrobe at this point, which I'm very thankful for. Anyway, we make apps for churches. It's a very fun place to work. We love what we do. We love our churches. We love our apps. And we love the people who work with us and our customers. So, check us out. It's just a planning dot center. And they're based in Carlsbad, California, which is just north of San Diego. And for the past nine years, up until a year ago, I lived in Arkansas. My second stint in Little Rock in my life and decided to move. I was remote there and I decided to move out to San Diego to be close. And personal information, but we, my family and I in Little Rock. One of the things we'd like to do is go out to eat and we'd always go get fajitas of this, one or two specific places. We love fajitas. So we thought, okay, San Diego, right next to the border, it's gonna have awesome Mexican food and great fajitas. And we've had the hardest time finding fajitas. We cannot find fajitas. And people are always, I was like, where's the good fajitas? Where's the good fajitas? And they're like, well, we don't have fajitas. This is, that's like Tex-Mex. I was like, oh, really? Okay, well, you say so. So last night I had fajitas. I was very excited to come to Texas and have fajitas again. Yeah, so, first of all, what is Ecto? Ecto is a domain-specific language for writing queries and interacting with databases in Elixir. And that's one of two times I'm gonna directly quote documentation, so this is the most exciting part of the talk. I'm just gonna read straight for docs. Basically, it helps you write queries that interact with your database. Crazy how that's actually the case. So it provides a few different things. First of all, it provides powerful change sets, and we're gonna get into that in a little bit. Another thing it provides is composable queries. You can actually build up your query over a number of different functions if you wanted to based on either user input or some other determinations. It's got connection pooling built in. So if something goes down, I'll make it ring back up the connection to the database. It's got database migrations so you can track the schema of your database over time, so you can put that into Git or whatever you're using for version control and see how that changes over time. And if you need to, you can roll that back or bring that back up in different environments. It also has some built-in security, because it does some escaping and things like that that you may not think of writing straight SQL. And also it has flexibility. So if you need to write straight SQL, it has that ability. It's got something called Fragment. You can write SQL Fragments if you need to and just pipe that straight into the database. So this is actually a, this is like the end of the slides. I've got maybe two more. This is mostly live coding, so it's gonna be very interesting. Hopefully everything works okay. I will tell you I'm gonna make a few mistakes on purpose and I'll probably make a few mistakes not on purpose. I'll leave you to guess which ones are which. And our goal today is gonna be a setup Ecto in a brand new application and start using it a little bit. So we'll set it up. We'll create a to-do application. That seems to be the thing to do, to do. And we're not gonna have like a command line interface. We're not gonna have a web GUI. It's just basically all in IEX. So nothing crazy, beautiful. It's just super simple, ready to just get you guys interested in Ecto. So the goal is to get you kind of peak your interest in Ecto and say, well, if he can do that quickly, maybe that's something I wanna look into for things we're working on and go from there. One thing I did forget to mention is there are different adapters for Ecto that you can use depending on your database. So the default is Postgres. They've also got a MySQL one. They recently released version two. Ecto has recently upgraded to version two and version one actually still has support for MS SQL, MongoDB and SQLite three. So that hasn't been updated yet to version two, I don't think, but those may be coming. All right, so let's do some mix new, right? Let's exit this and let me know if you guys cannot see things, but I'm gonna go from here. All right, so we go mix new. One of the things I like about Elixir is that it provides you with good help documentation. Documentation is like a first class citizen in Elixir, which is really cool. So if you make a mistake, it usually tells you good examples of how to correct that mistake. So in this case, it says, hey, I expected a path to be given. Please use mix new path. So another thing you can do is ask it for help. So we can say mix help new. And it's a little small here. I'm gonna back it out just a little bit for now. But you can see it provides you with examples of how to use mix new. It provides you with the different options you can use. And I'm gonna point out one option that we're gonna use today. And that's the dash dash sub option. It says it can be given to generate an OTP application skeleton, including a supervision tree. So we're gonna be using Ecto and it's gonna have that connection pooling. We wanna make sure that we provide that option so we have that skeleton. We can write it all ourselves if we wanted to, but it's just nice to have that skeleton already built for us. So I'm gonna say mix new to do and then pass that dash dash sub. All right, so we generated our files for our Elixir application. It even says go CD test and then mix test. So CD to do, mix test, let's do that. So CD to do, mix test. We got two tests already. We're already on our way, right? That's sweet. We didn't even write anything. Those are basically just testing that true is true. So we know that everything is working correctly on our basic application. Next we're gonna start a teamwork session here. Pardon the horribly looking status bar there. I usually do it in black and other colors. So from here, we need to edit some files. The first thing we need to do is go into, let me go to Elixir Conf to do and we need to open up mix.exe. And in here, we can define our dependencies. So if we go down here to depths, we need to add two dependencies for application. One is Ecto itself. So I'll type in Ecto here and we're gonna make sure that it's around 2.1. And then we're also gonna bring in something called postgres. And this is basically the database adapter for postgres. It's the default adapter for Ecto. It's what I have installed running right now locally. And so it's easy to use. If you're using MySQL, the dependency package for that is MariaX. And of course there's other ones as well as you can look at the Ecto site to find those dependencies. But we need to do that. We no longer, in Elixir 1.4, we need to define it in our application because it infers that based on our dependencies. So it'll spin up those applications when it spins up ours, which is really cool. So now that we have those defined, we can see go back to our command line mix depths.get and it'll go out and get those dependencies for us. Cool, we're done. No, I'm just kidding. What I wanna do now is mix compile. So one of the dependencies it got was Ecto, of course. And one of the things that Ecto provides are some helper tasks for mix that we can use to get our application running. So if you had looked in just straight mix help before we ran this, you would've noticed there were no Ecto options. But as it's compiling this, we're now gonna get those Ecto options. I know the first time I tried this, I did mix depths.get and I looked in the mix help and there were no Ecto options. So I was like, I added it to my dependencies, why isn't it there? Well that's because I haven't compiled it yet. So make sure you compile before you do this so you can see these. So if you say mix help and I'm just gonna grep out our Ecto options here. Now we can see that we have a few different helpers, which is really cool. The first thing we need to do is create something called a repository. And that is basically what interacts with your database in your application. And it actually provides something, mixecto.gen.repo generates a new repository. So let's try that, mixecto.gen.repo. And again, we have a good error message here. It says it couldn't find repositories for application, but we could do that on the command line by passing it the dash r option. And what dash r expects is a module name for our repository. So let's say mixecto.gen.repo through dash r and we're just gonna call our repo to do.repo. All right, it generated a few files for us and we'll go take a look at those in a second. But it actually again provides us with some helpful tips. It says don't forget to add your new repo to your supervision tree, typically in lib slash to do.exe. So I'm just gonna copy this supervisor and I'm gonna go into my editor here. Go to lib to do.exe and what, it actually changed. I don't know if ecto hasn't updated this quite yet because it looks at 1.4 change where this lives by default, but that's a little misleading. So if it still says that when you're watching this or you're trying this on your laptop and it's not there, it's actually gonna be in to do slash application. So in here, we want to add our supervisor under our children, okay? So when our application starts up, it'll start supervising our repo. Perfect, so that step is done. If we go back, it also says, and to add it to the list of ecto repositories in your configuration files. So ecto tasks work as expected. I would like for them to work as expected, so let's config that. I'll copy that and go into config, config.exe and I'll say we also want to config or to do application with what it provided me. Perfect. Now in here, you'll also see that it has provided some default, some initial things to connect to the database that you told it you wanted to use in this case, Postgres. It says we're using a Postgres adapter. If we were using Maria X, this would go to the MySQL adapter, has the database name and our username and password. So these are some things you're probably gonna need to change. So I'm gonna change those for my local thing. Everybody close your eyes. All right, open them. I don't want you to know my password. Okay, so that's done. We're done with setting up our database. That's all we needed to do. So as long as Postgres is running, if we do mixecto.create, we should create our database successfully. So it's gonna compile all those files we changed. It's gonna give us a saying, it says generated to do app and the database for to do.repo has been created. So we got hooked up correctly, right? So the Postgres was running. It now has a database named, well, whatever I gave it in the config, I forgot to change that. It's to do repo, perfect. So the next thing, we need to actually add a module. So we need to actually add the things that we wanna store in this database. So since this is a to do application, one of the first things we need to do is add a to do item, right? So let's create an item module. So we go to lib to do and we'll create a file item.ex. So we're gonna def module to do.item and in there we're going to use ecto.schema. Now what that'll do is it'll bring in some functions, some macros that will allow us to define how we want item to look like in our database or to our database. So to use that to define that, we use schema and it expects the table name of where it can find these items. So in this case, I'm just gonna say it's in items. And then within that, we're gonna define a few things. First, we're gonna find a field and our first field is gonna be a title and it's gonna be a string and they're also gonna have a completed and that's gonna be a boolean. Ecto also provides this handy function timestamps that basically automatically adds insert or dat and update or dat to your schema. So you don't have to type those out because it realizes that most of the time you're gonna want those. Also I wanna point out that if we had not specified string there in that field, it would have defaulted to string but I like to be explicit and specify that. So now that we have our schema defined, the database still doesn't know about items. We haven't told it to create an items table, we haven't added any fields or columns to an items table and to do that we need what's called a migration. So let's go back into our terminal here and we have another task that helps you create migration. So mix ectogen migration and it expects the name of the migration. So in this case, since we're creating our items table, I'm gonna say create items. It's gonna create, I'm sorry, it's gonna create a default file here for us with a little bit of boilerplate that we can use to use it in our migration and that's in our privrepo migrations folder. So let's go check that out and edit that. I'm gonna back out here, so we've got a privrepo migrations and it time stamps it nicely too so you know exactly how your database is built up over time. Let's open that up. So in here you can see that it uses ecto.migration which provides a lot of the functionality we're gonna use and it has this change method and ecto is really cool in that it knows how to go back and forth on your migration. So if you're going one direction, it'll make changes and if you go the other direction, if you need to roll those back for some reason, it'll actually be smart enough to know how to reverse those most of the time. Now if you have some crazy migrations that you can't really predict what's gonna happen up and down, you can write separate up and down functions. So if we wanted to do that, we can say def up and def down. In this case, we're not gonna write anything crazy so we're just gonna let ecto do its thing and just do everything inside the def change function here. So I'm actually gonna bring up on the side here, actually let's do it below because of size. Let's bring up our items. So let's look at our schema as we create our table here. So inside the change we're gonna say our change is going to be create a table named items. And within there, we're going to add a field called title and it's gonna be a string and we're also gonna add completed which is a boolean and we're gonna add those timestamps. So in this case, our schema in our item module and our migration are like exactly the same almost. That's because this is the first time we're messing with that items. In later migrations, which we'll see here in a minute, it won't match this exactly. It'll just start building it up over time. I'm gonna close this side and we will run that. So we say mix ecto migrates and it'll run that migration. So you can see it's running our create items migration of the change function and it's going forward and it created the table items and it did that in a whopping 0.0 seconds. So glad we had to wait for that. All right, so the next thing we wanna do is let's try it out, right? Let's see if we can interact with our database in the items table. So I'm gonna bring up an IEX session here, which is gonna be our best friend for a while. I'm gonna alias a couple things here to make my typing a little less stressful. So we've alias repo and item. So first thing I wanna do is do this. Now, if you are familiar with Elixir, you're like, wait a minute, he didn't define a struct, but if I press enter here, we have a struct defined for us. And that's another one of the cool things that Ecto does for us. It actually provides us with a struct automatically that we can use in our application. So it knows that we've got a completed field, we've got it inserted at, title updated at, but what the heck is this ID field here? We didn't tell anything about ID. Well, thankfully Ecto is also smart enough to know that usually you're gonna want something like an ID field. Now, if you wanna avoid that, you can tell it, skip the ID field completely. You can say, you know what, I want my items field, my primary key to be called to do ID for whatever reason. Then you can do that. You can override those settings. By default, it's gonna create an ID field for you, which is pretty cool. Now, to insert an item, which sounds like something you wanna do, we will say repo, actually let's see all the items in our database right now. So we can say repo.all and then pass in item. We do that, a couple cool things. First of all, it shows us some debug information, this case, the source of this query is the items table and it's 1.2 milliseconds in the database and then it actually prints out the SQL that it generated based on what I told it that it sent to Postgres to actually get these items. And finally, we have the result of that, which in this case is an empty list, which is what we'd expect because we haven't put anything in there yet. So let's put something in. So let's say repo.insert and we're gonna create an item and I'll say the title is my first item. All right, so again, we have the debug output. This is the SQL it generated and it returns back a tuple of okay in this case because it was okay and then also the record that it provided. In this case, we have our to do item. So if you wanted to, you can pattern match on that. So let's pretend like I did that and we'll say, in case you don't know, v is actually the return of the last statement in IEX. So it's super helpful if you got, oh man, I forgot to pattern match that. I wanted to capture that item. We can do that and now we have item in our capture. Okay, so now we have our item. Perfect, it's got an ID of one. It's got our insert at, it's got my title but the completed is nil. Now if you have a to do application, nil doesn't really mean much to you, right? You either, it's either true or false, really. You can kinda say, well, if it's false or nil or falsi but we wanna make sure that it's either true or false. So let's add a default to that, okay? So we go back into our item module and on this field, on our completed field here with Boolean, we can say default is gonna be false. Let's save that, go back into our IEX, let's recompile our item module and let's insert our new item. Let's say item two equals repo.insert and I'm gonna use this bang method which actually returns not a tuple of okay and then the item but just the item that it inserted itself. So, don't have to pattern match on it. We're gonna insert an item and the title is gonna be my second item. Okay, so now we can see that completed is false even though we didn't provide it with any information as to what to do with completed. And since we didn't do that, it defaulted to false which is exactly what we wanted. But now we have this to-do out there that's still nil, right? We forgot to do that on our first creation, maybe you put this out on the web and you've got a hundred to-dos now that have completed as false and that's not cool, you wanna fix that. So let's modify that, let's update that particular record so that the completed that is false. So there's a couple different ways we can get something from the database. The first is get by and so we can say we want to get an item and we're gonna search for it by the title and I think I called it my first item. So let's just do that. So it's gonna search the database, select, you can see here in the database items where the title is my first item. And so we get back this item here and again I forgot to capture it so I'm gonna go ahead and do that. Let me see, this is the one that has completed nil. Now if you are familiar with Elixir you realize that to update a map, one cool way to do it is to kinda open up the maps and text here, put in your item and then a pipe and then what you wanna update. So let's say completed is false. So if I do that, we can see we get back one where completed is in fact false. So you think perfect, let's use that and update our repo with that information. So we'll say repo.update, feeling all confident and go here item and we're gonna pipe in, completed is actually false. Uh oh, here's an error. Thankfully this is what I was expecting. It says giving a struct to do dot repo update is not supported. Ecto is unable to properly track changes when a struct is given and Ecto change set must be given instead. So this is one of those things I mentioned that's really cool about Ecto is that you have these things called change sets. And this is the second most exciting part where I'm gonna read from documentation so hold on your seats. Change sets allow filtering, casting, validation and definition of constraints when manipulating structs. Ecto change sets provide both validations and constraints which are ultimately turned into errors in case something goes wrong. Now that's pretty cool, that's pretty powerful. You can see things like in the programming Phoenix book one of the things that they do is in a change set they'll actually take a plain text password and hash that password before it actually puts it into the database and that's done inside a change set. You can do other things like validate certain fields or a certain length or match a certain thing. You can call your own custom functions that will validate that true or false. So let's create a change set because we do wanna update that item. So back in our items here, we're going to import Ecto.change set and we're gonna create a function called change set here. Now it's gonna expect an item struct and it's also gonna expect some params but we're gonna give it an empty map as a default here. And within there we're gonna take that item and pipe that directly into a function called cast. And it's gonna say within the params I'm gonna allow two fields here. I'm gonna allow title and completed to actually pass through my check. So if you had some field that you didn't want to go through on your change sets, let's say you had something you never wanted to have change then you can exclude that from your allowed list here and that would never get through to your database as long as you use that change set. So let's use that. So let's recompile our item and let's get our item again. Get by, actually let's do it a different way. So I can say item, it's another way to get something for your repo, repo.get item with the ID of one. And that'll give us that first item with our ID of one, the one that is completed no. So if we look at item.change set and initially for illustration purposes I'm just gonna pass it an empty item and I'm gonna say my changes are gonna be title something. Let's look at what it gives us back. First of all it gives us a struct Ecto.change set and it has this changes attribute which title is something. So it knows that we're trying to change what we passed it which was initially empty. We're trying to change title into something. So far it has no errors because we didn't provide any in the change so that we weren't doing any validations. It provides this the data which actually is what we passed it and it checks if it's valid, it returns if it's valid and you can pattern match on that to see if the things that you're trying to do are valid. It'll actually keep it from going into the database if it's invalid which is really cool but we can use this and let's use this to update that first item where our completed that is no. So we can say item.change set and that was not change set. Change set item and we want to change completed to false and we're gonna pipe that directly into repo.update. So we can see here it did update items. It said completed to true, I'm sorry false and it gave us back this this tuple again which again I forgot to pattern match so let me do that, item equals v. Okay so now we have our updated item with completed false. Perfect, that's what we wanted. So now we've got that. Let's go ahead and start adding users and tying these items to users. So first you need to go back and create a user module here. So it's a user.ex whoop not uex user.ex and def module to do man.user do. And we're also gonna use ecto.schema in here and our schema here is gonna be super simple. It's gonna be users do.  Now a user in our database and our application is going to have many items. So the items are going to belong to a user and that's something we can also define in our schema here. So we can say has many items and it's gonna expect on the second thing where it can find the definition of an item. So in this case it's in our item module. We'll save that. Let's go back over to our item because it's now going to belong to a user. So it's a belongs to a user and you can find that defined in to do.user. So we have user defined as a schema. We have our item updated to know that it belongs to a user but we don't have those changes in the database yet. So what do we need? Everybody, yes, migration, right? So let me split this real quick because I don't wanna get rid of it. So I'm gonna say mix ecto.gen.migration. We're gonna create users. We have that now, that file. So let's go edit that. I'm gonna wanna go back to my user and split my screen here so you can kind of see how we're doing this and go to priv repo migrations and then our create users migration. And again, we're going to create a table user. Table users. And we're gonna add a field called name. It's gonna be a string and we're going to say it has... No, it doesn't have many. We're gonna do our timestamps because the database doesn't care about the has many. It cares about the belongs to. If you know, databases usually have the belongs to, the foreign ID is gonna be on the belongs to field side. So we want to say that the items table should have a user ID field, right? A user ID column. So within that same migration, we can actually alter a table. So we're gonna alter table items and we're going to add a user ID column and it references users. Perfect. You can add as many different things in one migration as you want. Sometimes it's better to split them up. Sometimes it's better to put them together. So it's kind of up to you as to how you wanna split those up. In this case, I'm just gonna keep them together. So we'll go back and if I do mixecto.migrations, it'll list out all the different migrations we've created and their status. So we can see here, we've got create items. It's been migrated. They also have create users now and it's down right now. So we want to migrate that up. So we can say mixecto.migrates. All right, so we've created our table users and we've altered our table items. Perfect. I'm gonna close that because I don't need it anymore. I'm gonna recompile item and I'm going to alias2do.user. And let's create a user. So repo.insert. I'm gonna do a bang. I'm gonna say user with the name of, let's just do me, geo. All right, so now we've got a user in our database. And you can see here it's got an ID, it's got insert that and it has items now. So it knows that we have a group of items that it's expecting in the database to be based off of this particular user, which is really cool. At this point, I'm gonna go in and show you how to delete something because there's not really a better time. So let's say we messed that up somehow and we wanted to delete that. So I'm gonna say repo.get, the first user, and let's pipe that directly into repo.delete. So that's how you delete something. Super simple, just put in what you wanted to delete and it'll delete it from the database. It doesn't need to change that. You can do it that way. So, but we actually do want a user in our database so let's add a user back. So I'm gonna say geo equals repo.insert. Bang, user with the name of geo. All right, cool, so there I am. I'm in the database. Now, if you're familiar with Rails, I come from Rails world and I liked ActiveRecord because I could go user.items and it would load up all the items for me, which is really cool. So maps are kind of the same way in Elixir. So if I say geo.id, we get the id of two, perfect. So if I say geo.insert.assistuneName, faster type. We get geo, that's what we wanted. So let's try geo.items. Items is not loaded. So this is one of the cool things about Ecto that I like as well in that it makes you be kind of explicit about what you actually want loaded from the database at the time you're doing those database loads. You can load it later if you wanted to, but I like that it makes you load it explicitly because there are a lot of times in Rails where you get into N plus one queries and other slowness because of so many database calls. Well this makes it where you have to think about that from up front and actually specify that in your code, which is really cool. So if we wanted to, we can say, we're gonna preload it. So I can say geo equals repo.preload me, I'm gonna preload items. So now if I say geo.items, I get an empty list. So it's preloaded that from the database for us, which is kind of what we wanted, perfect. So let's make the second, or the first item, let's associate that with me, right? Let's update the item id to me. So we have our item and you can see that it has a, well it doesn't right now, so item equals repo, get our first item again. So now we can see that an item has a user id field and it's no right now. So let's update that to user id of two, which is gonna be me. So again we can say repo.update bang and we're gonna pass in our item and our changes that we want. We wanna say user id is two, that messed up. Let me change that real quick. Updates item user, oh, because I don't have to change that. Item.changeset, that's gonna go out here, item.changeset, this is totally one I planned for, right? Let's try that. Okay, so we have a successful database back and forth and it returns the item. But wait a minute, our user id is still nil. Why is that? Well remember in our change set we had to explicitly whitelist the things that we wanted to allow changed in our database. And since we didn't have that in the database we couldn't make the mistake of updating this without being explicit. Another cool thing about Ecto and Elixir in my opinion that he had to be explicit a lot of times. When I first got into it I was like, ah, I gotta write so much. It's so annoying, gotta be so explicit about everything. But now I'm like, it saved my butts many times. So let's go in here and allow user id to be changed. And let's go try that again. So let's recompile our item and now let's try that update. Now we have a user id of two, perfect. So we have to allow those changes through in our whitelist and our change set. Okay cool, so we got about 15 minutes left. Two things I wanna do. One is let's create a new item with the association already created. And then I'm just gonna go through some different queries you can do in Ecto and to try and show some of the power of the querying. So the first thing I'm gonna do is create a new item. Ecto has this thing called build a sock that basically builds an association between two things that are already associated but it'll build the association for you. So in this case, let's build an associated item. So for me, I want to build an association of items and the title is, let's say, speak at Lone Star Elixir Comp. So we can see here it's built something for us. It already has a user id of two which is gonna associate with me and we can pipe that directly into our repo.insert and now we've got an associated item in the database with that one call, which is pretty fun. All right, now some querying. Let's get into some querying. First, before we do that, I'm gonna go a little quick here and create a new column just to add some fun data that we can query. I'm going to add in our items a new field called estimated minutes. And this is just a track, how long we think something's gonna take and it's gonna be an integer. And we need to create a migration for that. So let's bring back up, let's put this again. Mix Ecto.gen.migration. Add estimated minutes to items. All right, let's go back over here. Load that file up. It's gonna be in priv, repo, migrations. And we're going to alter table items and not odd, do. We're gonna add a field of estimated minutes and it's gonna be an integer. I'm gonna close that. That's all we're doing in that migration. So let's migrate that. Mix Ecto.migrate. Cool, so now we have estimated minutes in our items table. Next thing we're gonna do is just add a bunch of random data. So bear with me for a second while I do this. Let's say we're gonna add 75 things in there. So let's pipe that into enum.each and to get the index of that. For that function, we're going to create an item with the title of to do number i and it's gonna be estimated minutes. We're just gonna make a random number here, random between, I'm sorry, uniform. Say between one and 120 or zero and 120 and a user ID is gonna be also random between, yeah, let's do it this way, two and three and you should notice that we don't have a user ID of three which will come into effect here in a second. User ID and then completed, it's also gonna be random between true and false. And we're gonna close that and then pipe that into repo.insert, the bang and in that. So you can see here it gives us this long thing but what we wanted to see is that the key, did I misspell it, estimated minutes, not found, estimated minutes, no I did. Well I didn't do it, oh recompile, thank you. All right, well I'll just tell you real quick what the user ID of three would have done. It would have given us an error for that because it realized in the database that we don't have a user ID of three which is one of those cool constraints because we've sold it that that column is gonna be referencing the user's table on the ID column which is pretty neat but I'm gonna pass that for now.  That, tap that into repo.insert and that. And now we can't do that. Oh, put that three in there again. So this is the thing I was just telling you was gonna happen, I forgot to add the user. So let's add the user repo.insert, I'm gonna add another user. In this case I don't really care, let's just say repo.insert, user with the name of max. Okay, now we've got user ID of three, let's try that one more time. Pipe that into the each and then go to the item and then pipe that into insert and then we'll end that. You're kidding, let's see. Princes are required, there you go, thank you. My up game was wrong, title. I'm just gonna do that into repo.insert, thank you. Okay, so now I've got random data in our database, finally. So let's ask it how many are in there? So earlier we were using repo.all, now it's just give us everything back. It's expecting one or more results, or zero or more results. Now repo.one is expecting at most one result. So we can say repo.one and say from i in items, or item, we want to select the count of i.id. I don't do this a lot. I'm gonna need to import our ecto.query. That'll give us those functions. So we can see we have 79 items in our database, 75 from those tests we did and a few from our initial ones. So we can say, well, how many belong to the user max? So we can do that, so repo.one from i in item, and this is just setting up our variables saying we were looking for something in the items table and we're gonna say that that's gonna, we're gonna reference that as i, and we're gonna join that with u in user on i.userid equals u.id. And we're gonna say where the u.name equals max. And we're gonna select the count of his IDs. So he has 38 items in the database. You can see here that with that it provided this SQL, which is kinda cool. So I didn't write any SQL. I kinda have to know a little bit about database transactions to write this, but I didn't have to write any SQL. So if at some point we needed to switch from Postgres to MySQL or to MSSQL or whatever, we just plug in a different adapter and Ecto would do that for us, which is really neat. Let's do something else. Let's say how many belong to each one of us? So we can say repo.all from i in item, join u in user on i.userid equals u.id where, let's say I'm sorry, group by. And we're gonna group by the user id and we're gonna select. And this is something cool about Ecto too. You can actually tell it how you want the data back. So you can do that in select. So if I didn't tell it specifically in select what I wanted, I'm gonna provide all the different full structs of the records that we were looking for. In this case, all I want is, let's say in a map, I want the count and i.id. And I also want something in name, so u.name. And I want them in maps. So you can see it returns a list of maps for us with the count and the name of the person they belong to, which is pretty neat. So we can also ask, well, what are the easy ones? So let's say we want to search for ones that are under 10 minutes long. So you can say repo.all from i. And how many times am I gonna have to write this? Well, let's just see something easy here. Let's create a query. This is where the composable queries comes in. So if we have that, that we know we're gonna be typing in from i and item, join you and user, we can actually store that in a variable. And then from that point later on, use that variable as the starting point for any other later transactions we wanna do. So let's do that. So let's say our query is gonna be from i and item, not in an item, i and item. We're gonna join up with the u and user on i.userid equals u.id. All right, so that's all we wanted to do. So we've got that query now. You can see that it's returned a query for us. We can pass this variable around. We can add to it. We can modify it. We can do what we need to do to build up the query over time, which is where the composable part of Ecto comes in. So now we can say repo.all from i and u in our query. So here we're saying the i is gonna refer to the first thing we defined, which is the i and item. And I can name this whatever I wanted. I can name this the first thing. But for now I'm just gonna name it i to keep typing. And the u is the second thing, which is the user. In query, we were going to select the easy ones. So we can say where estimated minutes of our item is less than 10. And let's order by the estimated minutes. And let's select in a map, we'll say the to do title is gonna be i.title, our item title. Let's say the time is gonna be i.estimated minutes. And our user, let's put in our username. So there we go, we've got the things that are easy things to do. So I've got this one here, it's only one minute, let's just get it done, right? It's to do number 59, perfect, that's what I wanted to do anyway. So this kind of gives you a demonstration of different things you can do. You can do pass and order by, you can pass in the different searches here. So in the past we've just done equals, we can do less than, we can do greater than. We can specify different returns. So let's say instead of in that map, we wanted just everything in a tuple here. So let's say we just want the title and then the estimated minutes and then the user. So now we get it all back in just straight tuples, which is really cool. Let's say we wanted them in lists, well we can say let's put that in a list. And Ecto will provide that to us in a list. So Ecto is really flexible in providing you exactly the data you need in the format that you need it, which is really cool. So if you have some web view or something you're using in Phoenix or some other app, you can actually say I want these things with these names and in the map and all this kind of stuff and it'll actually provide that to you right away. So you don't have to modify it later. Pretty neat. Couple other things I want to try, let's do this. Let's say, let's do one more. So it can actually use the power of the database itself to do some calculations and do some things that the databases can sometimes do. So let's get the average minutes for each user. So we can say from repo.all, from in this case I'm just gonna type out a given user in our query. We're gonna group our query by our user ID and we're gonna select the user, user.name and let's say our to-dos. Let's say let's get our count item.id and average minutes. And here we're gonna use the average function which again, Ecto will take and pipe that directly into Postgres. So we don't have to, Ecto doesn't have to do anything about it. Postgres is really all that for us. Our item estimated minutes. Forgot a, let's, oh darn it. Does anyone know how to stop that? Let me re-do this, repo, user item, and I think that was it. So let's try that again, repo.all. We're gonna rewrite that from i and item. Join you and user on i.userid equals u.id group by user.id, we're gonna select, so I forgot earlier, for some reason, the select is the only one I always forget the colon. We're gonna select user, u.name, to-dos, our counts i.id and average minutes is going to be our average, yeah, average of our i estimated minutes. All right, let's see if that does it, it does not. Ah, thank you. Of course, import Ecto.query, because I had to back out everything. All right, let's do that. User, oh, because I didn't do, user, I did u this time, u.id. Okay, there we go. So now, you see Ecto, I'm sorry, Postgres did the averaging for us, as you can tell in our SQL here. It sent that straight to Postgres and it averaged everything for us and gave us back the maps we were expecting on that. That's really all the time we have to go into some of those things, but Ecto has a whole lot more things that are really powerful that I'd really encourage you to go check out. I've maybe submitted one or two PRs into Ecto, so this isn't my repo, it's awesome. All I did was maybe correct a couple typos, so you can thank me for that. But go check it out, it is amazing and I know that for me, as I'm learning new things, again, databases are the way to go for me, I know some of you are probably the same. Also for enterprise databases, I know someone was asking earlier about interacting with Oracle and maybe some big MSSQL databases. This can do that. Again, Ecto 1 would have to do that for MSSQL and Oracle hasn't written yet, but maybe that's on you and go from there. But anyway, I blog at jeffreylessall.com. So far, it's like one post every other month or something like that and all it's been on Elixir and most of it's been on Ecto and a lot of them I go through how to build up different sample applications to show you different things to do with Ecto, including multiple repos with multiple databases and different types, dealing with legacy databases, that kind of stuff. So check that out if you're interested. Thank you very much.


Okay, welcome everybody. I'm Tony Addison, a non-resident research fellow at UniWide. And a professor of development economics at the University of Copenhagen. People are just flowing into the meeting now and we have quite a large number. I could ask everybody to keep their microphones muted. This webinar is one of the series of webinars in Wider's Domestic Revenue Mobilization Research Program, which has been going on now for nearly four years. The webinar today is a collaboration between two themes of DRM, the Extractives for Development theme, which is led by myself, and the Illicit Financial Flows theme, which is led by Professor Finn Taft of BRG Copenhagen University. In this one hour event, we are going to have three speakers on the theme today, which is corruption and theft in the global oil and gas sector. The first speaker will be Etienne Romsen. Etienne is president of EnergyCC, an institution which is particularly concerned with climate impacts in the oil and gas sector. He is a former global executive vice president for DNVGL and managing director of Shell Exploration and Production in Kuwait, where he developed and led many complex projects in oil and gas. Etienne has authored two Wider working papers on global oil theft. I will be putting the links to those papers in the chat, as well as co-authoring two further papers for the Wider project with Catherine McPhail, also of EnergyCC, on gas flaring and venting, which I do recommend you take a look at. Our second speaker today will be Giovanna Marcolonga, who is a postdoctoral fellow at the Crime and Law and Economic Analysis Unit at Baconi University, where she works on crime economics and political economy. Giovanna has authored a paper for Wider on the role of Shell companies in tax havens as facilitators of corruption in oil and gas, and we will begin putting a link into the chat for Giovanna's paper. Our third speaker, who will be acting actually as discussant, is Steve Caizzi McGewa, who is a senior consultant researcher at Zika and associates in Washington DC. Steve was previously acting chief economist and vice president of the African Development Bank, an IMF staff member in the Independent Evaluation Unit of the Fund, a research fellow, and has advised many African governments. Etienne will give the first presentation, followed by Giovanna, and then Steve will provide discussion of the two. We will take a selection of questions for the panel from the chat, bearing in mind that this is a one-hour webinar and we aim to try and finish roughly on time. So, welcome everybody and let us without further ado, move to the presentation by Etienne Romsson. Etienne, please. Thanks, Tony, for the introduction. I'll share my screen and hopefully just checking if that is visible. Yes, that's visible. Let me start. And I want to start the topic of global oil theft. We have actually a video kindly created by you and your wider. So I'm going to play it now and hopefully it will come through. Okay, that was that. Okay, indeed. So actually the video gave the key messages already. And let me give some more background to oil theft and some key observations from the study work that I've done. So in this first slide, you can see a sample of known oil theft cases. And this is by no means a complete list of the known thefts. And moreover, many thefts are not discovered nor reported. So one of you does show is that oil theft is a global phenomenon, and it affects many countries. And although low and middle income countries are often worst affected by it, oil theft is not a developing countries problem. UK, the US and Singapore are all examples where large oil theft do occur. So some observations on oil theft. The scale of global oil theft is highly significant. And there are no signs that the amount of money involved or the amount of oil that's being stored in is receding. Current high oil prices and not only increase the value of thefts, but also incentivize more thefts. And that can be understood through the following example, because, for example, smuggling increases when price between countries increase. And if prices are kept in, say, country A, but prices follow global market prices in neighboring country B, then increased market prices will increase the margins of smuggling from country A and B. And there's significant evidence that that is neat in practice the case. The degree of smuggling and tax evasion is directly linked to cross-border price differentials. However, smuggling and tax evasion are only a subset of the range of potential crimes. Unfortunately, many oil theft crimes occur with violence. In addition, digital crimes, so as recently the US colonial pipeline ransomware attack in 2021 exemplified in diverse methods of oil thieves. There are many ways in which oil theft crimes and crime syndicates established themselves. But even if a theft originates as an opportunity crime, the crime syndicates responsible invariably develop into organized crime. One key reasons for this is that the proceeds of oil theft are so large that it makes it worthwhile to professionalize it. In addition, all theft schemes are often very elaborate and thus therefore require coordination amongst multiple parties and therefore organization. Moreover, the probability of oil theft detection is relatively low and oil theft doesn't rank high amongst other crimes. So consequently, syndicates organize their crime so that they can continue the same thefts again and again. The Shell Bookum refinery theft in Singapore, for example, occurred over a period of 12 years and involved multiple syndicates. In this period, a total of 340,000 tons of gas oil was stolen worth over $150 million US. The Shell Bookum oil theft syndicates involved Shell employees, bunkering companies, fuel traders, banker shipping companies, ship crews, surveying companies, and black market fuel customers. In this example, Shell insiders volunteered willingly and masterminded the crimes. However, in other cases such as in Pemex, Mexico, oil workers are often confronted with extreme violence and kidnapping of their family members to support the cartels in the theft crimes. Oil theft is also a source of public money for terrorist organizations such as ISIS or for political independence organizations. Unfortunately, there are many stories of oil theft. The Benin example shows that the fallout of oil theft on this country's economy, even though Benin has no oil production and very little official fuel imports. Benin has no oil to steal that was not already stolen. However, in 2011, there was a series of pirate attacks on the coast of Benin, mostly targeting oil companies, oil tankers rather, that caused this region to be reclassified as a high risk region by insurance companies. Since shipping companies avoided this area altogether. The Port of Continuum is a major source of income for the Benin government. When port activity declined with 70%, the government lost 28% of its revenue in 2012. Even to date, oil piracy remains the problem also in the Gulf of Guinea, and at least two tankers were attacked in the last few days. Acts of piracy appear to be contagious. As you can see, waves of activity spreading from East Africa to Asia and now increasingly to West Africa. Of the potential piracy targets, oil tankers remain on the top of the list. If it's not to hijack the cargoes, then it is to kidnap their international ship crews. Oil tech is causing great harm to the health and the environment and while all companies are increasingly being held responsible for emissions, there are few activities so environmentally damaging as oil tech. The emissions from artisanal refineries are orders of magnitude higher than from any commercial refiner. The scale of the problem is truly enormous. In 2013, it was reported that more than a thousand of such refineries were destroyed by the authorities in Nigeria, and often the destruction went with increased environmental damage. I conservatively estimated in Nigeria alone, more than 2200,000 barrels of day of stolen crude is processed in such facilities. Nigeria does not stand alone in this. In Russia, these illegal polluting refineries occur also and called Samovars there, like the tea kettles. ISIS use the same chemical process to fund the terrorism. Ground pollution from oil tests from pipeline tests is another key problem. In 2018, Mexican authorities identified more than 12,500 illegal pipeline taps. In Nigeria, pipeline vandalism was reported 21,000 times in the period 2010 to 2014. It is common for these pipelines to explode when locals gather to scoop up part of the spoils. In 2019, a gasoline pipeline that was being tapped in Mexico exploded and killed at least 137 people. The worst recorded event is in Nigeria in 1998, where a similar event caused the death of more than 1,082 people. One of the key observations of my studies is that oil theft very frequently diversifies or originates from the so-called hard crimes. And there are many reasons for this, and I highlight a few. Oil theft is so lucrative that thieves frequently target other thieves for their spoils or to take over their territory. These crime on crime activities are generally extremely violent. The skill set involved in oil theft is very practical and practicable in other crimes. The Mexican kettles, for example, used their skills in burying smuggling tunnels, now also to dig tunnels in the pipelines to invisibly tap them from below ground. Smuggling routes for oil theft are also used for smuggling weapons, narcotics, counterfeit pharmaceuticals, and human trafficking. Very often this occurs on their return journey. Crime syndicates have businesses that are very skilled in risk management and diversifications. And when kettles are being pursued, for example, for drugs, they switch to oil and vice versa. The syndicates know that they are being pursued for a certain type of activity and then flexibly adapt to reduce their exposure in this area and increase their focus in other crime areas that are at that moment less in focus. So you may ask, why is oil theft not giving more priority by the international community? In addition to the recent shows on this slide, I think the impact of oil theft is generally underestimated. First of all, people in the tentative underestimate the scale of global oil thefts. In addition, the wider consequences of oil theft have not been well understood and in particular this aspect I've been focusing on in my work. Oil thefts are often very well hidden and to tackle the problem requires a lot of effort. Note that oil and fuel are not illegal substances contrary to drugs and arms and so someone in possession of truckloads full of fuel is not necessarily breaking the law. Another key piece of the puzzle is that the basic data on oil theft is generally missing. Key questions need answers such as how much oil is being stolen, where is it stolen, how is it stolen and where is it transported to and how are the money flows organized. When oil theft is unchecked, these criminal activities start to penetrate legitimate businesses sometimes to provide active cover to thefts. Often, however, these legitimate companies have no idea that they're handling stolen goods or that they are sponsoring criminal activity that goes well beyond oil theft. The opportunity to make money from oil theft goes much beyond ideology. For example, the largest purchase of oil stolen and processed by ISIS was the Assad regime itself. For example, the oil thefts in the Qatar inventive and well organized there are many opportunities to make it much more difficult to conduct their illegal businesses. These solutions focus on the three areas where answers are most needed, whereas the oil stolen, where and how is it transported and traded, and how are the money flows organized. The purpose of this slide is to show that many solutions can be implemented today. And are the focus of collaboration amongst parties. Other solutions focus on improved regulations or apply exists or the application of existing regulations specifically to target oil theft. Finally, technologies provide an important opportunity, particularly in identifying theft events and the transport of stolen oil. Remote sensing by satellites is one of such technologies. For example, when pirates turn off the ship transponders, also called the AIS after boarding a vessel, it takes six days or more to actually find the vessel. In this time, anything can happen to the crew and a cargo. It should be possible to use satellite technologies to detect ships through its fuel emissions or radar signals, particularly if this is done immediately when the AI signal disappears. Other technologies have proven to be highly effective are chemical traces that can detect if an oil parcel is legitimate or stolen and even where it was stolen. All these opportunities give hope and means to significantly reduce global oil theft. However, the solution does require an effort by multiple parties to collaborate in solving these issues together. With a concerted approach on multiple fronts, can we hold oil theft syndicates to account and bring a hold to their detrimental practices. Here on the slide you see a number of those organizations that in my view are quite critical to work together to provide solutions to this problem. And with that, I conclude my presentation and if you have any questions, please note them down in the chat and we'll follow up after my presentation after the presentations have finished. Thank you. Good. Thank you very much, Etienne. And, as Etienne just said, we will be posting links to the papers and there are many interesting papers on the wider website. We now turn to Giovanna from Baconi University so welcome Giovanna and the floor is yours. Thank you. So, thank you for inviting me and thank you Professor Addison first for the event and owner wider for the organization. So today I'm going to talk about the research that I conducted within the initiative was sponsored by UniWider together with Diego Zambiasi who's a lecturer at Newcastle University. So the title is from offshore oil to offshore finance. And I want to start my presentation just giving you two numbers that give you maybe an idea of what we're talking about. So, in a report by the OECD in 2014, they looked at case of international bribes, and specifically at the sectors in which international bribes or transnational bribes were more frequently observed. Among the different sectors here I showed that two-third of them and these pictures taken from their report where there were four sectors that actually covered the majority or two-thirds of the cases of transnational bribes. But there was one sector and it was extractive where about one-fifth or 19% of all bribes were concentrated. And I think that the extractive sector as maybe the presentation that preceded me already pointed out, actually offers some environment, let's say, for corruption. And then, well, we're not the first to study corruption and offering policy suggestions to how to fight corruption in the case of the extractive sector. As seen among the participants, there's also someone from the Natural Resources Governance Initiative. Well, we carefully read a paper or actually a report that was published by the Natural Resources Governance Initiatives, where they analyze 100 cases of corruption specifically in the moment that happens when there is the awarding of licenses and contracts. In the different cases that were reported there, well, there are 28 that are also analyzed in detail in the report and eight of them. So between one-fourth and a third of these cases, they saw the involvement of shell companies in the scheme of corruption. So what are we after here? So we are asking whether the award of oil and gas licenses does increase corruption or is tied to corruption or it creates an environment where corruption is more likely to happen. Well, as you may guess, this is not going to be an easy task to analyze, especially for economists who are always behind and looking for data, but we're going to show what we will try to do. So let's just start from one of the protagonists of this study. So we're talking about awarding of licensing in oil blocks and corruption. So let's start from the licensing and the oil blocks. Well, as we maybe already know, there are large investments that happen in the sector of oil extraction and oil production. Typically, the moment in which there's going to be the award of contracts or licenses is a peculiar moment because it's a moment that requires a large investment because clearly infrastructures that are required for exploration, production and development are highly costly, but also the returns that may come from these investments are very high and they can also last for a long period. And typically, if we look specifically at the moment of the awarding of oil licenses while this happened through different procedures, and I apologize here for oversimplifying here in the explanation. But typically there are two kinds of procedure one is a direct negotiation. So the potential contractor is going to just ask to the government or knock on the door it's called an open door policy of the government to have a direct negotiation. Or they're going to participate in licensing rounds and licensing rounds are typically some specific moment during which a country decides that are going to award that's going to award a certain number of permits or licenses to oil blocks. And this is also going to be the focus of our study. Typically, the areas or the blocks are, they belong to the government of a country to the country itself. And so it's going to be the government that's going to be directly involved in the awarding of the oil blocks, and we can already see that this is going to involve therefore ministries and public figures in the awarding, and they're going to have a really critical role with high discretion. Then, when we're thinking of licensing, and this is just a little bit of context that I'm going to give in this case, it's going to say that I'm talking about two types of licenses so there are multiple types of licenses but here, let's again apologize for oversimplify but we're going to focus on exploration licenses and production licenses. So what's the difference between the two well typically, let's say that we want to start a company that is going to produce all tomorrow. Well, we may consider to start exploring an oil block where no discovery has already happened. In case we find all there then we're going to maybe apply for a production license. So clearly the exploration is something that has a risk associated with that because we may find or not all. And usually the time of the award or the permission to explore an area last between five and 10 years. So the exploration is something that happens once we already know it that oil is there. Then we can guess that probably an exploration license. It's probably in some cases is a prerequisite to apply for a production license. So suppose that there are there's a moment when the government is going to allow to award production licenses. So oftentimes it's going to only allow people that have already carried out an exploration and had an exploration license so if going back to our example where we're starting our oil company tomorrow production company if we deem that there's going to be a large oil field in a specific area. So we're probably going to try our best bet to get an exploration license first in order to tomorrow in case we find the actual oil field and we're going to be able to have higher chances to gain also a production license. Okay, so going back to our question which was does the award of oil and gas licenses increase corruption. I talked about the awarding moment now and then let's focus on the corruption moment. So we're saying there are large rents that come from these investments. And I apologize I says I heard some notes. Okay, so there are high large rents that come from investment in this specific sector and particularly in the investing in getting a permit for an oil exploration. And I have this question because we were saying that the government is directly involved in the awarding procedure. Well, these are two typical suspects that increase the risk of corruption in the sector. But then, as Etienne was saying before well in the case of oil theft it's going to be hard to find data on oil theft. And also in the case of the awarding of licenses and finding data on corruption of oil licenses is going to be a bit tricky. But here we're building on the work that I mentioned initially. So as I was saying, the natural resources, government initiatives when they study the different cases of corruption occurring the awarding of contracts. This is how Shai companies often play a role in facilitating corruption. And here we can think, and I'm going to exemplify just three cases of why Shai companies could play a role. So, first, if we think that there's going to be an option on an awarding procedure of all permits while there's going to be a bunch of companies probably that are going to apply to gain the award. And it could be the case that among these companies they're going to be companies that are linked to politically exposed people, if not directly owned by politically exposed people. And these are Shai companies that actually do not own the infrastructure and the capabilities to carry out the necessary investment to take advantage of the exploration and production. And they have a vested interest in winning the award, and then in case transferring it to a company that does have the actual capability as infrastructure. So the Shai company here will offer the opportunity to the person that's involved, say the person that's linked to the politically exposed person, or the politician himself to hide his identity. So if a company wins, for example, the permit and then resell it. Another case could be that companies that are participating in the awarding round, they have, they're going to try, for example, to bribe the politicians or to buy the people who are administering the process. So clearly it's going to be easier to hide and to carry out their bargaining procedure behind a secret layer as provided by a Shai company, compared to doing it at like clearly and just directly within the country. So one way, for example, the company could set up a Shai company offshore, direct the money there, and then make as a beneficiary of that company, the politician or someone that's linked to the politician who's involved in the awarding procedure. Ultimately, the same politician himself could be interested in embedding some of the rents that are coming, for example, from the signing bonus of the award. And in this case, he could decide to, he or she could decide to siphon off some of the funds by channeling them to a Shai company that's tied to him or whom, of which he is the ultimate beneficiary. And these are just some examples of how Shai companies could play a role in facilitating corruption in a critical moment as is the moment of the awarding of oil licenses. So just some descriptive evidence of what we do find. So this is just to give you an idea of how we started essentially this project. So these three lines that are being graphed on the picture, each represent a different time series or a different evolution of either in the case of the green dots is the total number of Shai companies that are being incorporated over time. The blue line is the oil price over time. And in that, and the red line or the line at the bottom is the number of awards being needed for exploration permits in this case that are being awarded over time. So I'm showing this picture to show you how the three series actually move together very closely or in other words, when there is like an increase in the number of the awards we observe also and that's the red line at the bottom we also observe an increase in the number of Shai companies that are happening in this in the same period. And the blue line is to say why shall we think also that all price plays a role in this case. Well, if oil price increases while the rent of the investment in the oil sector are probably going to be higher, because then that means that the oil we find, then we can evaluate a higher price. So the same contract or the same license could be more profitable and then it could be more likely that we're willing to get involved in a corruption scheme so in other words when there is oil price increase there is high profit higher profitability from the investment, and that could lead to a higher chance of corruption. So we find actually, and to transfer that graph that I've shown you into some words, while we do find that when there is in the six months around the awarding of an exploration license, we observe an increase of 11% in the number of Shai companies that the beneficiaries are tied to the country or they belong to the country that's awarding the contract. And this association is actually stronger during an album, or in other words, when there is an increase in the price of oil, and we're about to award and a country is about to award an exploration permit. We observe an even further increase in the number of Shai companies that are being incorporated compared to when there is no increase in the price of oil. But then you can ask me about how do you find all of these. For the two protagonists of our story, we were talking about awarding of Shai awarding of exploration and production permit and Shai company. So we obtained data from a business intelligence intelligence provider on where the blocks are placed around the world and when different oil permits are being awarded. So we have been it for exploration or production in the different blocks in the different countries over time. So here we have one protagonist, the other protagonist is the Shai companies. Here we exploit leaked data from the Panama Papers and the Paradise Papers. This was a large investigation that was actually made public between 2016 and 2018 and it was the first time that we were able to observe the identities of the people behind the Shai companies that had been incorporated mainly in the Caribbean, and by essentially Mossack Fonseca and Applebee as two main companies that were involved in the incorporation of Shai companies. So here there is like a caveat that's important, which is that not for all the companies that these journalists actually gain information about we were able to link them to some countries but only for a third of them. But for this, let's say about 240,000 companies that we were able to link to a company what we observe we know when those companies were actually started. So what we do is essentially we look at one country, and we say, let's observe the timing at which a country is awarding exploration and production permits. And let's see what's the dynamic or how many new Shai companies are being incorporated in the period of the awarding so around the six months of the award. So these are the same dynamic in Shai companies to a country that in the same period is not awarding any Shai company, any all exploration or production permit. And let's see whether we observe a different dynamic or a different change in the number of Shai companies that are being incorporated in those countries that are actually awarding the permit, compared to the ones that are not awarding the permit. So you only have a few minutes left. Yes, I should like I should be almost done. So, what we expect is that when a country is awarding a Shai some permits, then we should observe a higher increase in the Shai companies that are being opened by this country compared to a country that is not awarding. And this is even higher when there is an increase in the all of price because it's making the investment even more productive and the rent to extracts are even higher. So, to summarize with the picture what we're doing. We observe here that there are three different bars and three different markers so each bar refers to a different type of permits that's being awarded. So the first bar is saying, do we observe an increase in offshore Shai companies whenever in the six months around the award of any license. The second bar touches the zero, the zero marker and so economists when they observe this they say well it doesn't seem the case because like we cannot distinguish it from a zero. Instead when we look at exploration license and remember that these are the most remunerative ones. Instead of that, in the six months around the awarding of an exploration license there seems to be an 11% increase in the shell in the incorporation of Shai companies, and this does not seem to be the case again in the case of production license, there isn't being that it's still touching the zero. And then given that I'm running out of time, I just want to say that the different numbers in this picture are just saying that when there is an increase in the all price, the increase in Shai companies is even higher compared to when there is no increase in the all price. And the reason being that now a permit is even more remunerative and so maybe there is like a higher incentive to get involved in corruption. To conclude, so we started by saying that the OCD estimated that 19% of transactional bribes are related to the extractive sector. And as also in the video that we started with, we was mentioned this is a problem, and especially in countries that are characterized by low growth. And so this calls for an urgent response of policymakers, and we in particular focus on the role Shai companies plays in fostering corruption. And essentially, this is a call for more transparency both in the people who are behind the Shai companies and as well in the whole procedure of the awarding of licenses. And I stopped here and happy to discuss more later. Thank you very much, Giovanna and the papers are available on the wider website. So now I'd like to turn to Steve Caizzi McGewa. Steve, as I mentioned earlier, is Zika Associates Washington. He was previously acting chief economist and vice president the African Development Bank, and also an IMF staff member, among many other things. So, Steve, over to you. Very, very, very many thanks, Tony, for your very generous introductions. Also, thank you wider for bringing me back again to comment on these very interesting papers I found them extremely interesting. I hope I will do justice to them in less than 10 minutes. So I'm going to be brief. When I look at the papers I had four immediate sort of responses and questions. The first one was that it seems like like natural resources, especially oil are sustaining a group or a couple of corrupt individuals and institutions around the world to bend the rules of the international governance system. And with impunity, by the way, and the question arises, what can we do about it. So that's the first one. The second one was certainly the cases are not confined to the usual suspects that different, the difficult to run places of the world in South Africa. It seems to be all over the global south, and even some countries in the developed world. So our question there is, is our convergence of global thinking. Would that be able to change behavior, you think. The question, or comment that I had was that a research, both of your research is your Vanna, and it can. They seem to suggest that the importance of creating credible, credible institutions and agencies of this strength. But the question is, how would this be established under what kind of formats. I think would be the implications for the environmental concerns that we have that it can brought out, including the movement was next zero. And lastly, in terms of the question that came to my mind was how to put some kind of theoretical or analytical discussion around the topics are very interesting topics that you're discussing. So, in the current policy economy literature. I see a chance. Some of the things you're raising up under the literature read and grievance, which occupied a lot of debate. So that you were on once on one hand you have her politicians are going and say, oh, you know, we, we have this political grievance this, you're not respected. And then when they get in, we are caught up by the greed of grabbing as much of this oil or the gas or whatever. And for Giovanna. Also, there is this whole literature on political finance, as opposed to political economy, just political finance, where a whole lot of this money, by the way, it's packed. You know, the money is packed in all these places in the Caribbean and so on. But in a lot of cases doesn't stay there forever. You should don't know if you have been to Africa, but you should attend some of the, the election campaigns in some of the places they look like American election campaigns and wonder where the money has come from. So these, these, these places are packing places that pack the money there and bring it up to do a lot of political work so political finance is one aspect of it. So, again, very quickly I don't, as I said have much time but when I looked at the papers by a team that to very interesting papers, very nicely interconnected. Something came to my mind and I put a kind of rubric on them and there is that there is method madness. That's what I thought when I read through them. There is this complex structure of natural resource value extraction, the, the, which is always much more complicated in terms of financing in terms of technology than the countries where these things happen. So, then that is used of course in all sorts of ways, but the linkages are incredible because they go up even the UN system. But imagine I mean the UN would be the ultimate arbiter of these things but sometimes also indicated as you point out for the case of food for food for oil in Iraq, and so on. So you have this very complicated technologies are very complicated financing spine posed on very weak institutional structures and the greater structures in the ground. And of course, as you said the risks of the law that if the high high returns. And of course, you have specialization there and protecting that the tough and so on. And I very much like your reference to something called a cappuccino effect, which some of you who read the papers will find very fascinating but essentially what it is is you add a bit of fluff on top, put gas into into the oil, or you put air really into the oil, and the volumes increase, and you sell it. So very, very interesting ways. And when reading through a change purpose I thought of new places like Uganda, which is very excited about its oil production. Interestingly, which is very interesting and effective to the population they're really hoping to cash in. But then you have these extremely long pipelines going to be over 2000 kilometers to the sea from from Uganda. You have, you're going to have all these sophisticated technologies once again. So you really worry.  The same data for Nigeria, which has had oil for the last 50 years. You have again political ambition. You have grievance, superimposed on greed, and the situation becomes quite complicated. Now turning to Giovanna, again I found your, your, your, your paper very, very interesting. The small rubric I put on top of yours is, if there's nowhere to hide. Why not try home. What I mean by that is that, yes, you might close all these places and shell companies and so on. But the corrupt people find a way and what to my mind is happening today is that the money is being kept in a lot of African countries that I know I'm giving examples not just happen in Africa but I know Africa quite well. And you can find all these five star hotels on on, you know, and houses that cost one million US dollars. I mean, to get a house one million US dollars here in the US would be something. But there's so many houses there, and you wonder how civil servants, I'm able to do this. Okay, so if you can take our money to show companies you can take our money to Switzerland. We might as well do something about it keep it at home. So there's a lot of impunity in the system that is a bit different to to get rid of it will seem so you have real estate booms, five star hotels. The phenomenon of your bands is smaller than mine. People buying all these sophisticated cars. Yes, you get going. But I also asked myself when I was reading your paper about the technology of transferring this money, the cryptocurrencies and so on. The North African technologies, the Western technologies. I'm very very sure that the agencies that security agencies in all these countries know what's happening. And they could probably help. The problem is how can you get them on board, because somebody knows that these things are happening. For example, related to to this. Whenever there was a study that whenever the IMF or the World Bank made a big disbursement for country X in Africa, they would in a very short time be big. Not necessarily of that money, but because money is tangible. There's already enough for an exchange in the system, then you see a transfer of that money going out. So some of these things are known. And the question is, would there be a coalition of the willing to try and stop these activities going forward. Again, thank you very much for what I thought were interesting papers and try to conclude in one or two sentences. Some of the solutions that it can and maybe even you're going to suggest to appeal to some moral economy to the hearts and minds of the people and so on. But my question is what if the whole system is sort of captured, as you have seen in some cases, where the local elites, you see what happens is in some of these places the local elites are still in money. You might see them as crooks, but there's a section of the population that sees them as our sons and daughters. They're bringing the bread home and so on. So how do we deal with that kind of thing that is, again, sort of going around and I would say that our solutions, rightly, that really impact what's happening on the ground. And surprisingly, are given by the example of the new president of Tanzania. She's a lady called President Samir, quite a low key during the last time of the last president who just passed a while ago. She came in and assuming and people thought that she was going to be a walkover because she comes from a small island linked to Tanzania. But very very soon, she has really gone systematically attacking this story, dismantling and so on, a lot of these corruption hotels in the country. The question is, how do such leaders emerge. We don't know, not even the east. Should we just wait for captains miracle to happen like that one. So there is a big pressure don't really have answers to this but those are some of the big questions that came to my mind as I read is very interesting. Thank you so much. Thank you very much, Steve. So, we're now just going to turn to responses from Etienne and Giovana but I'm going to, as you think about your responses just to some of Steve's many points, and we're starting to run out of time. The question that's come up in the chat and there are quite a lot of comments in the chat is, is how much should we be relying on voluntary practices by the companies. How much should we be looking to more stricter regulation. And you know who's going to be responsible for overseeing that. And one specific question for Etienne that that came up was, if default emission penalties are assessed on oil companies etienne default emissions penalties are assessed on oil companies will this change incentives towards or against illegal activity. Okay, so I'd like to hand over now to Etienne for a brief response and then to give honor. Thank you. Yeah, and I'm also trying to maintain to respond to some of the questions that have come in and then some of the overlap between Giovana's and my presentation, I think, forward to summarize, I would say oil theft and corruption is a business. And that's why it links to all price I said the same thing in my presentation if all price goes up. The old hat goes up and Giovana shows the, the corruption goes up and that clearly that's a business response to an external driver. Let that be no mistake that the organizations and companies that are behind these activities know very well what they're doing. And they're excellent in managing risks and extremely clever and finding new business methods to continue. What is a very lucrative kind of kind of deal it's not a one off it's it's clearly a business proposition. I want to quickly react to one of the questions that came in. This is a quite difficult one and I described that in my report in quite a bit of detail is the whole thing about moral economy that there was just raised as well. Like the government cannot take care of distribution of energy access for its people then you shouldn't be surprised that if say other organizations illegal organizations will do it for you and give the people what they need. I understand the rationale but still I think it's a flawed argument. This is probably the biggest negative of oil theft and corruption practices that actually in the minds legitimate governments in the long run, by giving handouts and involving people in theft and corrupt practices as part of the scheme. And ultimately it doesn't help the society at all. And of course the government has a job to do to provide energy access to its people, but through these practices actually it's, it's compromised in doing so. So it's a bit of cause and effect but I think the root cause of the problem is the theft of the government. Okay, thank you very much, Etienne. So, give on briefly would you like to respond to. Yes, these made or anything that's come up. Yeah, definitely so thanks again like a, especially thank you when some of the questions that you raise are probably some million dollar questions as well. But if I want to give like my contribution so I was actually thinking how to translate these into practical suggestions. And I think that there are some progress so maybe we cannot solve it in one day but there is some progress that can be done. So for example, going back to the specific case of the awarding of our licenses well, as I was saying there are some companies that are applying and they don't have the infrastructure capabilities. So we should first impose or maybe it should be mandatory that the companies that participate do have the requirements in terms of infrastructure and capital to carry out. So we are not linked to politically exposed persons, but then going back to the question of the chat shall we rely on the companies themselves to provide these well no probably this is a role that must be taken by super partners organization and international organizations. As I mentioned before there is already the natural resource governance institution that is already initiative that's already involved in this, and they have been also the, for example in the case of all profits there are the 80 standards that have been developed so the World Bank made some disbursement of loans on countries, meeting some standards in the reporting of their all proceeds and they are all profits. So similar standards could be implemented also in the awarding of all permits. I would say, for example, again, either for the disbursement of international aid of World Bank aid, while this is going to be dispersed as long as the country also meets some requirements in terms of standardization of the process and in the awarding procedures. And finally, probably just to summarize all my discussion the easiest anecdote to all these is transparency so it's true as you were saying that the shy companies are sometimes just a stepping stone to then bring the money back and my question again is, do we know who are the owners of the five star hotel or our child companies behind them, because maybe if we go and have an opportunity to check who's behind them, then we may maybe can also make progress in terms of fighting corruption and the very last point is, who shall we expect to do this? Well, another point that I think it's relevant is that in the way we measure corruption, typically we ask for example people whether they had to pay someone to get their procedure done. It's probably also time to get people to make people aware or to become all of us aware of the fact that corruption is not just paying someone to get an authorization for something sometimes corruption is also someone that is like a government ministry and is siphoning off money abroad. So in the measure way we measure corruption and I'm talking about corruption perception index we also as people as grassroots as population must become aware of the fact that corruption is it has different shapes and some of them are also shared companies and siphoning money to tax savings. Thank you very much, Gio Varna. So, as I said at the very outset of the webinar, this webinar has been brought to you under the wider DRM program, and this particular webinar reflects on two themes, extractives for development, what the theme that I've been leading and illicit financial flows that Professor Fintharp of Copenhagen University has been leading. So do visit our website, there's a lot of material up there for your interest. It remains for me to thank very much our speakers, Etienne Romsen, Gio Varna, Marco Longo, and Steve Casey McGewar for their very interesting and stimulating presentations, I think you'll go away thinking this is a very important issue, which has been under emphasised by the global community. There will be further webinars in this series, so do look out for them on the wider website, or on social media. And I'd like to thank wider and the wider staff, Yuta and Anna for organising this meeting, the practicalities. So, thank you wherever you are in the world. Thank you for coming. Thank you for your participation. Thank you.


Welcome back everyone. Recently, Dragon Quest XI-S's Echoes of Elusive Age Definitive Edition was announced to be coming to Xbox One. This is the first time a Dragon Quest game has ever been on an Xbox console and it's going to be included with Xbox Game Pass. This is honestly very big as there are millions of people out there who will have immediate access to the game. We'll see a huge surge in fans this December when the game drops. Additionally, Dragon Quest XI-S is coming to PS4 and Steam as well for $40, making it cheaper than the Switch version which hasn't budged at all that much on the price. While this should be nothing but good news, a few troubling announcements were made alongside the announcement. You see, this is not the original game with just all of XI-S's content added. It's actually a port of the Switch version up for as with a higher frame rate. What this means is the graphical quality itself will be different looking than the original game. It won't be quite as detailed and the lighting won't look quite as good as the original 11 on PC. From what I've seen in the trailer, this is the case. It definitely looks different than the original game. A lot of people are angry about this, I'm noticing and this is a bit of a bummer. Honestly, the first thing I thought when the game was announced was that we'll finally have XI-S with quality graphics as the original XI looks so much better than the Switch version, especially the PC version with the settings all the way out. While it does suck that this is happening, there's still a lot of content in 11S to be had. The new version has the orchestral soundtrack, the 2D mode, the new story chapters, exclusive armor, party members following you and more. It has mostly everything from the Switch version. Now if it really is a direct port, that means the two features that were cut from the original release, being the targets and the first person camera, are probably not going to be in this version. Overall, if someone were to ask if I still recommend getting this version, I would still say yes. It's cheaper than the original, it actually still looks very good for a Switch port, and you can't beat the added content. Obviously, it doesn't look quite as good as the original, but I think the PC version of 11S would be a good bet because it may be possible to mod the graphics back to the way the original looks. It all depends on the size of the final game. I just hope Square Enix doesn't delist the original because that would be a huge shame honestly. The two games are very different and some people might want to revisit the original for whatever reason. I'm only worried because some devs stop selling the original game when they make an update. I think they should include the original like how Bethesda still sells the original Skyrim, although it is delisted and harder to find, it's still there. So please don't remove this game from your store. That would be a huge mistake. Other than that, Dragon Quest 11S come into consoles as a good thing, and it will allow more people to get into the series and experience the added content of the Switch version. I'm definitely looking forward to messing around with the PC version when it drops. Are you excited for Dragon Quest 11S? What console are you going to play it on? Let me know in the comments and I'll see you next time.


Ready to stretch your medical assistant wings? Our online, self-paced CCMA certification will help you fly the coop. Through a combination of interactive activities, patient simulations, and one-on-one instructor support, you'll have unlimited access to your courses and no assignment deadlines. Prepare for direct patient care experiences across diverse medical environments. And don't forget to explore our optional externship placement program. Prepare for your career in medicine today and soar into the real world.