So, hello. Today we're going to be talking about sort of rethinking the render theme layers. This is, this is, we're not going to actually be presenting the way it should be. This is going to be a huge discussion because one of the things that we've seen is there's, there's actually an issue on Drupal.org right now in the issue queue that is a huge, long sort of semi-rant, semi-informational thread about the pain that Drupal 7 themers are feeling. And this all has to do with the complexity of our current system with the theme and the render API together. Some of you have seen this slide before. I gave it at DrupalCon San Francisco before Drupal 7 was released because I had a feeling this might be a problem for people to sort of grok the theme system. I said it's a simplified, it really is a simplified version. It's actually a lot more complicated than this. But all the parts are there. Basically that from a code standpoint, the reason why we are where we are is because we had a theme system in Drupal 6 which was great but lacked a little bit of flexibility as far as having sort of granular control over theming a particular piece and not some other pieces. So basically doing sort of field-based showing and hiding sort of on the fly based on traditions because it was rendering things to strings, it was rendering our content to strings a little too quickly. So in Drupal 7, the solution that we ended up with was adding render API into the system. And the render API and the theme API are completely different systems and they didn't really mesh that well in my opinion. So that's sort of the fundamental problem space. So if you look at the node for this particular discussion on Denver's website, there's a link to the particular issue where the themeers went through that I believe Sun started the issue, didn't you? Yes, yeah. So Sun started the issue and then it just kept going and people were ended up being really useful and Alex and I went through and read through all this stuff and were trying to figure out some good ways to maybe solve those problems. And I wanted to sort of summarize the three sort of top problems that people were complaining about in that issue and that was consistency, also consistency and consistency. The theme system, because it's so flexible and powerful, there's like eight different ways that you could possibly render a particular piece of content. And from a theme developer who's coming to the system and goes, okay, I know I want to do, like, what is this comment field? How is this comment field being rendered, right? So the way that it's rendered is different from the way the links are rendered, the way that a user profile is rendered. It's all done subtly differently whether... So a particular variable that's in a TPL can either be a render element that gets rendered using the render function or it could just be a string at that point or we've got Drupal attributes. So it's difficult from the theme developers to figure out because there are so many different ways it could be and it ends up they have to do a lot of sort of print var and getting to debuggers and this is really pretty difficult for a lot of theme developers. So you want to... Alex also went through and sort of ticked notes on this and I didn't make any slides based on your notes but do you want to comment about other things that we saw in that issue? The consistency? Other things besides consistency. I wanted to say that's the number one thing, really. Actually, not at this time. I want to see the rest of what you have to say. We'll be comment later. We're co-presenting... Okay, so what do you think? Thanks for coming. This is my last slide. I don't have anything else. That's it? That's all the slide to have because I wanted this to be discussion really. So first off, those of you who haven't necessarily been in that issue if you wanted to comment about those things, that'd be a good place to start right now. I think one thing that drives me crazy is that we're thrown all sorts of markup that's deep, deep, deep, deep in the code and core does this, contrib does this, some modules do it better than others and give you nice TPL files and what I find most aggravating is just how we're thrown stuff and it's just sort of deal with it. That you don't really get to build your page like you would if you were prototyping a page.  And that's a big frustration because we're handed a lot of problems that really happened before the theming even really, before the theming level we're actually dealing with it and we end up having then to override so much that is sort of assumed and that's one of my big frustrations and I know that goes deep into the Drupal architecture but anything that we can do that helps give us more control over that would be a big win. Thanks for that. Has anyone here seen Jayceen's blog post from, was it yesterday? Where she talks about sort of componentization of the theme system? Actually I wanted to pull that up. Is it on my... So Jayceen spent the last several years thinking about this problem and about six weeks or so coming up with an idea. So one thing is consistency and one thing that I did last week was in the issue queue I opened up about six or seven issues that I just tagged assigned to the theme system component and tagged with consistency so those are things that can just be solved now. Some of them require a lot of work but they don't necessarily require a lot of consensus building, they're pretty obvious like yes this is better, write the code, the patch will get reviewed. Some low hanging fruit. Jayceen, her idea is I think sort of more revolutionary but very cool which is instead of having hundreds of theme functions in core and then another several thousand depending on how many country modules you enable to kind of reduce it to a set of standardized formats and components that's like a manageable amount and then for example when the book module needs to output the book navigation that it doesn't need to define a new template to do that that it can sort of use a standardized component like list or tabs or stuff like that and to have a consistent data structure between those things so that there's maybe like one hook that a front-end developer could use to say instead of using tabs, use this other kind of component but the data structure is the same and suddenly your book navigation is outputting something in a different way but that there's a small number of components that actually output the markup so what you want to do in your theme is really kind of hand control all of the markup that's output you don't need to override 400 functions you can override maybe 20 or 30 or hopefully less so I think it's a great idea and it sounds like a lot of work but I think it's something we can do if enough of us feel like we want to do it Yeah, the component idea was I was actually thinking along the same lines but in a really, really rough way just like, yeah, that sounds like maybe we should think about that and then you went and wrote it all down and formalized it a lot better and I really love this idea of having a sort of very consistent data structure for the particular item that you want to theme and instead of having like a pound type or possibly pound theme inside whatever, it may not be render API anymore, render array but those are the two options we have right now so if we instead had a consistent data structure and there was like one little property that we needed to change in order to change it from a list of unordered links to ordered links or into another maybe just paragraphs or something you could be able to change the way that it's themed just by changing the property of that data structure I mean it would be extremely consistent and not like the problems we have now with pound type and pound theme which are very confusing in a renderable array The other thing I'd say about that is Chicks here Nope, okay And one other thing is that problem with Drupal 7 is that with a lot of these theme functions and render element info, yeah, sorry render elements is that they're not nestable which seems crazy to me, we have like a list of links but you can't put things that are also render elements into the list items, they have to be strings, right It's like well, you know, you realize you can put like a whole set of markup inside individual list items, right and the only way you can do it in Drupal 7 now is like creating custom elements with hook element info or possibly just hacking the render element by adding a pound prefix render function to it It's kind of madness, you know So having those, the consistent data structure and also nestable elements is really, really important and again we're inconsistent about how do you do nesting for the things that actually have nesting and it should be like, the children should be like one consistent property of, you know, the element So, go ahead Chris So, I'll kind of touch on some of those topics a little bit but like, I'm just going to kind of pause it that render array is, like render API sort of failed and it failed really, really early because like we added all of these things like hide and show and all sorts of other stuff to make a themeer's life easier when they were actually down in the whole let's create a template and whatever but the problem is that they have to actually render a region before they can tell whether they're going to have that region and by the time they've done that hide and show is sort of useless to them because we've rendered the whole thing once already so, you know and like these sort of problems emerge really, really early in the Drupal 7 lifecycle and so we're rendering regions that all the elements in it are access false Right, so I mean, I think getting rid of the render API at this point is probably really up for grabs and something that we should seriously consider and so far as like structured content goes and how that stuff might be handed off to theme functions or whatever that ends up looking like I think we have an opportunity here with some of the other core initiatives that are going on to maybe standardize the sort of elements that can theme output so that you could write to a specific use case and change the output of a given block, for example and so there's kind of this notion of wrappers you know, what am I going to wrap the output of this with but that same notion could be extended to who's actually going to theme the output of this that could easily be done and so like that may come back to the whole like if we had a consistent data structure for a given element or component then running it through a consistent set of theme functions or whatever sits in that place becomes really easy and we can identify those theme functions and even expose them in the user interface so that I say what this runs through so if I know that it's a list or all the things that can theme lists oh well you can have it unordered or ordered or whatever you like and so I think there's a real opportunity to consider those things and maybe make them real and legitimate here I'm just kind of throwing that out there Let's see, so a couple of thoughts on that so one is I do think that the layouts initiative and so for anyone who doesn't know that was Chris who was just commenting who's the lead of the D8 layouts initiative and one of the opportunities that we have with the layouts initiative is that anything that's happening like at a block level or larger so everything from like the page to you know down to the individual block that's not going to use render arrays because of just because of the requirements for edge site include and stuff like that and also part of what we're still discussing as part of the layouts initiative is how granular do the blocks get and you know potentially the blocks maybe even get as granular as like a field maybe not quite that granular but possibly that granular probably that granular so if we can pull that off and Chris is confident we can which is great then you know that's a huge set of kind of theme functions and sort of theme or tasks that become you know configuration driven and you know templates that just you know that arrange sort of layouts of string variables which are blocks and I think that might address some of the kind of like page building comment that was mentioned earlier for stuff that's smaller than a block so for stuff that's like you know a single field rendering itself or like a set of links rendering themselves a set of you know menu navigation rendering itself I think some kind of structured data structure for representing the rendering pipeline is still necessary that's my current opinion at this time I was just talking with chicks like half an hour ago and he made the suggestion that maybe we don't want to keep it as a raise maybe we want to move to like an object oriented approach for them since we now have PHP 5.3 and so many other APIs are going object oriented and so maybe we you know maybe using an object oriented API helps us achieve you know the same kind of thing we're doing with render API but maybe in an easier way and it actually could be an easier way for frontend developers because right now you know we have like within page.tpl.php we have things like you know we're checking if you know if a block exists you know if a if a block is going to actually get rendered as anything but the way we're checking it you know kind of has false positives there you know the block will say yes there's a block here so create the sidebar for it but in reality when you actually render the block it renders nothing so you know now there's a sidebar with nothing in it and that's not what the person wanted similarly the whole inconsistency of you know when do you say print variable and when do you say print render variable you know so using objects potentially gives us the opportunity to make those kinds of things a lot smoother you know if you can say if something and evaluate it as a Boolean and find what that is you can say print something and there's you know object oriented lets you kind of automatically render that to a string behind the scenes without the person writing the template having to say render so I think there's a lot of opportunities we have there to move to an object oriented API for the contents of a block but I think we do have to realize that you know we have to make sure that that API really does make things easier for front-end developers you know but I think we can do that let's go through the line here great so I'm really encouraged that this conversation is happening I think it's great it's great to know that lots of other people are feeling sort of the pain that I felt over the years you know as a front-end developer for most I work in a number of different CMSs and for a lot of the CMSs that I work with you know when I have a design I will code it out just sort of in plain sort of HTML and then sort of load those components into the CMS and in Drupal I find it's actually generally it's worse markup but it's more efficient for me to just style the markup the Drupal is generating to go through the theme functions and the field templates and all that sort of stuff and I think that it's like there's front-end developers and there are Drupal front-end developers and that you know any sort of movement forward needs to sort of recognize like I would argue needs to lower the bar for how much a front-end developer needs to know about Drupal in order to modify the Drupal output and I think that there are a lot of good motives for render arrays and controlling output in different places in Drupal as a team developer you know it's a way like in my .info to set a flag for like suppress HTML tags from fields suppress HTML tags from block content you know like be able to set a bunch of flags in a .info file so that I can just get structured arrays and loop through those in my own loop and throw in my own markup in my templates or something like that you know I have zero idea how feasible something like that would be but it seems to me if you know if an object oriented approach is in a .info file or something like that you know pass an argument to the render function or something like that that controls what exactly render is giving me and I can get rid of some of the crazy markup that you know views or panels or you know field that has has done that I haven't taken the time to actually override with the theme function or something like that but I don't know how reasonable it is to think about being able to pass things in a .info file or something like that you can't just go there PHP strip tags well and but I think that's also where what I mentioned earlier about the components is powerful because you know the reason you know views and you know rules and you know book and all the core modules you know output a lot of mark you know field tpl.php you know the reason all those things output markup that's not desired is because you know there's so many theme functions and when the modules are written they're not written by front-end developers and so you know a module developer is making some kind of decision on what markup is appropriate here and sometimes it doesn't go through as much of a careful process and it's hard to you know have like a careful review process for hundreds of theme functions and templates and I think that's where if we can reduce it to a set of standardized components then we can actually like be like okay so what's the best default markup we can create for these components and then at least the defaults make sense we start encouraging module developers to not type pound markup equals something that has HTML tags we get away from that. They cannot create their own theme functions they can just reuse the components that are already available on core. That way if there's something wrong with the markup that's in core maybe we just fix it there and then poop it's fixed in all the modules they're using the same components. And then ideally at that point you know you don't need I mean you know flags and info files sound you know like an interesting idea if you have any component templates and if you want to strip out some markup or add some new markup you can just override you know a couple files and do it that way and it's not that much harder than some kind of info file setting. The question was Fences module helps specifically with the field TPL and making it much leaner and more geared towards the common use cases rather than the worst case use case which is what Drupal 7 sort of looks at when we go through and sort of make these components we should make sure that we're looping in front end developers you know HTML junkies in the process so that as we create these standardized components that there is an initial review by people who really care about their markup and I believe there should be basically an HTML component like if there's a specific HTML5 tag that you want to use there should be a way with Drupal in some component where you can get that HTML5 tag I just want to make sure this discussion I was going to go in a different way so I didn't want to break if we still had ideas about that sorry no we're good so I was a little I feel bad because I haven't read this other than what's on the screen did it come out yesterday or yeah very few people have read there are the last two days so if what I said is actually addressed in there I'm sorry there's a little bit of red flag popping up in my head in that a lot of the reason that we've moved to more specific templates in a lot of cases is to provide a granularity of theme ability and a generic component we end up with our theme links where the only way to override the specific theme links is to take the whole theme links function and then scrape out that one instance where this shows up and then change it and only theme it for that case and am I so in Drupal 7 we have suggestions I mean this we had that as part of use that was actually in 6 too it was sort of secretly in 6 and views used it because it was kind of pretty secret and we sort of made it in core and actually having core modules use it basically in Drupal 6 it was only views that knew about it and then right about when Drupal 7 was about to get released everyone was like oh look there's these really cool things that are already in Drupal 6 we should really expose them and make them better in Drupal 7 so the answer is to make better use of that because it's kind of confusing actually even for me understanding since it was in 6 it's a hard concept suggestions are it would be great for us to figure out why it's a hard concept and make it less hard and then use that so yeah if we have these standardized components but if you do want to so book navigation for example uses a standardized component but if you know on your project you want the book navigation thing to be different from, slightly different from the other one that you have a way to do that and the thing is right now so the theme hook suggestions are based on like a particular string value inside a particular variable inside the template preprocess functions so I feel like it should be a property of this component this standardized data structure so you're basically given the sort of the purpose of this particular element the module that's initially creating this data structure says hey I'm the book module I'm using it for this purpose and it identifies that in the property so that you don't have this issue of I have to override all links because I can look at just oh well the property the suggestion property or whatever it's called for this new component it's right there and it should be fairly easy to just do it sort of conditionally there's another problem with suggestions that you have to know every possible example people might want is a suggestion which is why views ends up with those crazy long template file names so that's also kind of a hard thing with suggestions although one of the things in moving from the Drupal 6 to the Drupal 7 model in sort of moving what views pioneered with Drupal 6 but getting into Drupal 7 core like in Drupal 6 with views suggestions were sort of defined by the caller so the caller theme with Drupal 7 you can do that or you can specify them in a pre-process function so if the module didn't think of all the possible different permutations of things you might want to specify the theme can implement a pre-process function to do that I think it's I think these concepts are still there's something about the implementation or maybe the concepts that I think is still daunting to a lot of themers I'm really interested to learn more about what specifically that is and how we can make that more approachable two items first does anyone remember that we have template engines in Drupal yeah does anyone try to use them in years yeah those are actually broken and have been for at least two versions now I propose we just get rid of that concept because unfortunately I haven't upgraded my Drupal 5 site from a smarty yet yeah we've got that capability in there but it doesn't actually work because everything is built on PHP template right now and anything else is at best a third class citizen I don't even know if you could do that so I think we'll make our lives easier just for implementation we're going to have one templating system maybe it's an evolved PHP template maybe we swap it out for something like twig we should let go of the idea of pluggable template engines because we've already done so we just need to admit it yeah item two whenever as we're coming up with these new models we need to keep in mind the concept of affordance from usability basically the idea is that doing it right should be the easy way to do it and doing it wrong should require extra work right now it is really not easy to use a model developer to know how to use render API right I'm not honestly sure I can say there is a right way to use render API but if there is I haven't figured it out as a model developer which means I cannot give themers something that they can work with because I don't know how so whatever it is we do to solve this problem we need to keep that in mind that model developers by and large don't know everything that a themeer is going to want and if we make them try and guess to figure out what they need to do to make a themeer's life easy they're all going to do it wrong in different ways and we're going to end up with Drupal 7 again so we should try to keep that in mind make it easy for model developers to do the right thing without having to think about it because otherwise they're not going to do the right thing because they're not going to think about it yeah I agree that render API is is not obtuse okay yes and it's a complicated system and I don't think that there is a right way to do anything in the render API I honestly don't feel like the render API is a good solution for the flexibility that we wanted that said I would love to rather than thinking about how do we improve the render API I think we should instead focus on how do we make a consistent API and then go and build that and probably it doesn't include render API yeah is it okay if I go yeah to continue ripping on render API just to give a prefix to to my suggestion yeah render API the problems with like render API are two-by-two render and render and render render and render we're putting a lot of onus on themers to know about a few little differences between different functions but even before that in Drupal 6 we're still having themers good chunk of PHP, and I think that taking the idea of saying, okay, we can rewrite the theme layer again and make it more consistent this time with more functions or different functions just may be making it for a really tough transitional period, and maybe we wouldn't even succeed in our consistency. And so I'd like to propose instead of doing something like that, actually moving towards less functions entirely, like less function calls and less everything and printing out things directly through a string replacement method, basically. So kind of like a theme engine, or like a, I don't want to say smarty, but I just did, so kind of like that. But besides render APIs, problems with complexity, it also has major problems with memory consumption, which is the sheer amount of the giant array that takes up memory. That we've constructed, like printing out the page array, how develop module conveniently lets you look at the page array that will always crash your browser or your server, because the page array is just so freaking big that it's impossible to actually render it out. And besides memory, also security, that you are also, theemars are by far and large the biggest source of security holes. It's not really their fault. It's that they're being given the onuses on them to be security, but they're the last layer before the page gets printed out and XSS holes just come out all over the place through the theme layer. So we need a system that is more efficient memory-wise, and a system that doesn't put the onus on theemars to be worried about security so much. And we don't want them to have so much technical knowledge. The analogy there is sort of like we're giving the theemars a loaded gun and telling them to hammer this nail into a piece of wood, and it's being said, be careful. So to come around for all of that, we made a... Carl and Jed Martin and I made a proposal for a session, but unfortunately we idiotically made it a session and not a core conversation, which is why we're not talking about it. But we conveniently already have a system that is pretty memory-efficient, only renders out parts that are actually necessary, is nestable and is secure, and that is maybe not the actual system that we have in place right now, but we have the methodology through tokens already that provide a nesting ability, that provide safe output, that provide the ability to print out pieces of individual objects without rendering the entire thing, and they don't require any functions or technical knowledge to use. And so it's potentially, if you would like to Google for DribbleCon token templates, I think that's what it was called, token template, yeah. Then click the top one there, and this is our proposal at least, and there's some things that like tokens are not intended to be used like this. We know that, the token system we have today is not intended to be used like this, but the fact is that we can imitate something like the token system to produce something like this and also have the convenience of the tokens that we already have, like node and comment and things like that, that could potentially, I mean we need something like hook variables that provides per template tokens essentially, so we're not generating a million tokens for every template, but that could replace hook variables, basically a token info specific to a particular template, plus throw in the existing tokens that we already have for things like nodes or comments or whatever. So, yeah. I honestly don't think that this is, that's kind of nice, I kind of like that as far as simplifying the actual TPLs. I don't think these are incompatible ideas either though, and then maybe this is something we can tackle faster than the other bits. You notice that the intro slide didn't say rethinking the render API for Drupal 8, it was just, I was about to type Drupal 8 and I'm like, ooh. Yeah, and it probably won't happen for that, but. Actually funny about the theme engine's not actually working, it's kind of too bad because this would be a great theme engine, but unfortunately in Drupal 7 because of the render system, like we're going to, memory wise, it's not going to do a damn thing and that actually would make it a lot worse because now you've got this system plus the render API, and the render API is already crazy, so I mean, you would need to commit to this because the memory problem of render API is only going to be solved if you get rid of it entirely, like if you, and this does other crazy things in that it flips around the page processing. So pagetpl.php gets, doesn't know what's inside of it until it encounters the tokens and then the page template actually decides what gets rendered inside of it. So when it gets to the regions, it says, oh, I need this region and this region. So you avoid the problem of like rendering regions or entire variables that never get used because they're never even generated until the token is encountered. So it would totally, I mean, this is, this, this approach would totally flip around everything about the way Drupal builds a page where it would literally start at the top and go down rather than starting in the middle and coming back up and then going back down. So that's the way render API works. So, and Carl, I think wanted to say something on it too because he, oh, yeah, yeah. Right, right, yeah, yeah, because we can take, yeah. So the comment was for people watching the video was that this actually works on top of PHP template because it just sort of replaces actual TPL and it's a token system on top of render API on the existing PHP template system. Right, so that could make for some kind of conversion process like if we were going through and updating all the templates and all the core, you could have them both for a while at least. So, Carl, you want to say something? Not specifically about this, but I have some kind of vague comments that I just want to bring up in terms of just the whole conversation here. I, you know, I think this is one idea. I think one thing we have to realize is that at the end of the day, we're concatenating strings together and like we're writing HTML and like this isn't, this doesn't have to be the most abstracted, beautiful, theoretically pure thing in the world. I think when we go down that road of trying to make everything very theoretically intact, we end up with XHTML which like didn't really go anywhere. And then we had to, we had to revert to something more practical. So I, that, there's nothing specific about that in general. I just want us to like maybe marinate on the idea that things don't have to be so beautifully abstract that like they're going to cover every single use case. We're going to have a component library that's going to cover everything. I mean, there's, markup is going to just exist in some strange fashion sometimes. Yeah, I mean, HTML5 markup isn't, isn't necessarily perfectly clean already. It's not a perfectly abstract system. Yeah. So, the theme system that we developed that produces that markup can't be, it's got to be modern sort of after HTML5 as well. So the main reason why I want to see if we go to some sort of these component sort of common data structure is because of the, when we built the theme system we built it saying, okay, I need this specific bit of markup, right? And then we built the function to do that. Our, our, our design model was based on specific chunks of HTML that we need. Let's build those specific chunks, right? And then when we tried to generalize, we are, our, the whole API was based on the wrong fundamental idea, right? So what I would like to have the API do is realize that we need to be able to alter these quickly enough that, you know, if we start with that, it'll get messy in the implementation deals, I'm sure, in certain spots, and that's fine. But as long as we start with the common, common idea, it's, hopefully we'll make the messiness less messy than starting with the wrong spot and then ending up a spaghetti. Yeah, and then the other thought I had was, was that I do like the idea of having something that is pluggable in terms of going from data to markup. Because we have various audiences in Drupal. Drupal is a CMS, Drupal is a framework. We have users who want to get into, they want to start with Stark and they want to write every single piece of markup. We have other users who want to download Omega and Delta and Context and use semantic views and do everything clicking. I don't think that we're going to arrive at a system that's going to make everybody happy that does all those things for everyone, you know. And so I think one of the comments I had on the issue that's on D.O. is that there's this kind of embedded assumption in a lot of the core modules that people are going to use Render API. If you look at NodeShow or NodeViewMultiple, it's got those hash parameters in there already that assumes like, oh, this is going to be sent to Render. So that's assuming that Render is what we're going to use, but maybe it's not what we're going to use. So I think for the people who want the UI-driven stuff, yes, let's provide a path for them. Well, let's make sure that that is separate. And that can be sanctioned off from something that is pluggable for people who want to use maybe a different engine. Maybe they want to use a JavaScript-based engine or something like that that they can do that. So I think not only simplifying, but also pulling apart those two things. Thanks. Thanks. I was thinking about what someone said earlier about one of our goals is to make Drupal easier to theme for people who are new to Drupal. One of the challenges with Drupal, and this has gotten a little better through the years, but it still is that the theme and the presentation and content are all intermingled together. And today, most Drupal sites would have a very hard time of just coming up with a new theme and being able to just launch the theme, because so much presentational logic is put into module configuration. You have panels or context or whatever display settings on nodes. And so if you wanted to do a redesign without a rearchitecture of your site, you end up having to go in and not just develop the new theme, test it, and then launch it, then you have to shut down the site or make a clone of it and then do a bunch of configuration changes. And I'd love to see us at least present a paradigm of understanding for the community that in thinking about configuration that the configuration should focus on, as far as the output goes, should focus on a semantic meaning, like is this block small, or is it a site, or is it an article, or navigation, or whatever, and have that in a skinner-like access for people who really don't want to get into the TPL files. But anything that is specific to this theme's presentation maybe should be run through an extra layer so that the layout for these panels is specific to this theme. And if you're developing another theme, you can develop a different set. So it's really kind of like on the one hand, fences and skinner kind of combined into you're getting output, you should be able to choose what is the semantic meaning of this output that's not going to change from theme to theme. And then you have the presentational things that could be, hopefully, targeted more towards theme-specific implementation. Getting into the overriding the output is a lot of the times we're trying to override the output because the markup that's being generated is just sort of thrown in there. And it generally defines the function that generated the markup instead of what kind of semantic meaning it has. And getting the output to focus on the semantic meaning first, and then work backwards from there, I think would be a big win for improving the theming system and providing a frame of understanding for developers who are trying to think of what they're outputting. Yeah, thank you. Yeah, I think that the components that we have to be sort of semantically based, not markup focused necessarily. So yeah, I agree with that, definitely. Let's try to get through all three of you guys who are still up here, because I would love to hear from Chex and these at the end of the line. We got 15, right? Oh wait, did we start at 2.45 or did we? Oh, I thought we started at 3.30. Oh no, that was, no, 2.50. Oh, you're right, we have 15 more minutes. Okay, no worries.  Cool, cool, cool. Oh, I can talk fast. So yeah, I guess I'm just gonna proceed to throw up on you. So this notion that Nate had up on screen here, that's really very. The screen or the, you know, the engine and Carl had up there. Oh, this one. Yeah, that, scroll down. Yeah, I'll get to it in a second. So this is very compatible with the whole notion of like blocks everywhere, because we're injecting blocks with context. And guess what? I can generate tokens from context as long as they have tokens associated with them. And I'm responsible for sending the markup back for say every block. So I have tokens and markup and everything all sitting in one function. Like this is very easy to do from like a perspective of my initiatives standpoint. I really like how that looks. I really like what it affords me. I happen to agree that I actually quite like this a lot better than having PHP in the CPLs. And I know that if you ever get like Rasmus up here, he was like, it was PHP because, you know, it's already a templating language and all that stuff. And you are reducing, you know, some from performance with having, not having to parse the entire thing. But we already have, you know, talking token parsing going on in various parts of the system already. So I think the win of simplifying the TPL will outweigh this slight performance hit that we might have of not using PHP directly in this file. And I also hope that by simplifying the data structure and also with the blocks everywhere or initiative that we can streamline and make the stack run much quicker, therefore it's okay that we have a small performance hit of not using PHP at this time. And I think that the only question I would have, and I'll follow up with the three of you, Carl and Jen and Nate, is like looping inside TPLs. But other than that, I really like this. I'll watch the video, definitely. But I would like to talk to you like at DrupalCon, hopefully. So to carry that thought a little bit. Horse Skype later, busy, crazy. To carry that thought a little bit further, the question then becomes, well this stuff is sitting inside of objects right now with the way that I've got it architected. And I don't know how many people in the room are designer-themer sorts, but like is extending an object and changing a method that is purely that too much to ask? What was that, Jesse? The blocks I showed you? You know, we extend a block and we change its build function so that it's the code that you want with the tokens that you want. Can you give me an example? Yeah, I'll give you, well I can't give an example verbally, probably that's sufficient enough. So I'm just gonna kill that there and transition over to one other thing. There once was a thing called Stark and we spent a lot of time working really hard on that in the Drupal 7 lifecycle and I think it'd be really great if we could spend some time carrying that through all the other theme functions in the system and really reducing down what they output because we don't need all the wrapping that we've got right now. And a lot of the complaints from a wrapping perspective are actually contrib and when we start talking about that the things that people cite most are views and panels. Can I just say that from a contrib standpoint that a lot of times they're just following what they see in core and the feed TPL is a horrible, horrible example that we've given to contrib space module developers. Sure, I think my point there though is like in relation to views and panels you're talking about modules that are actively trying to support IE6 which we will not be doing in Drupal 8 plus. So that stuff is almost certainly going to get simpler anyways and we might wanna drop that example altogether because it is literally trying to support IE6 and won't have to from that point forward. So the Dividis will go away. Yeah, as browsers get more and better support for CSS3 it's much easier to have much more simplified markup which I am so looking forward to. Earl Miles will tell you that I posted an issue one time called lots of cruft in panels flexible layout it was on exactly that Dividis that didn't go over too well. I had kind of a question as it relates to the whole Symphony 2 framework. Somebody told me that there was a templating system in Symphony 2. Is that going to affect this conversation in any measurable way? And the answer is no. So Chris or Larry wanna jump in to answer that question because I don't wanna actually, okay. So Symphony 2 full stack framework uses a templating engine called twig which is also written by Fabien but is not one of the components that the web services initiatives planning to use. I do think we should consider using that as our templating engine. Whether or not we want to, I don't know but I think it is worth considering and looking at that and giving it serious consideration. I don't know it well enough to say. Yes, I think it is worth looking at and asking the question is that the easiest way to resolve these problems? I don't know if it is but I think it is worth looking at. That actually, as James pointed out, it shares some similarities to this token concept. First of all, the stocking concept, if you have been long enough into PHP then this reeks of smarty and we know where that leads. Nowhere good. You already mentioned looping and yes, that's where this tanks. This is the problem what you have is that eventually somewhere in your template maybe not here but in the one that renders the node lengths we need to have loops and then what are you going to do?  This is why we were using PHP template for so long because why do you want to implement another language on PHP? Now, talking of languages implemented in PHP we actually made this mistake. It's called a render API. What I recently realized is basically a declarative language written in PHP. So the mistake we made is that although the data structure is recursive because HTML is recursive. So the data structure is probably good and should stay but we absolutely should not mix the actual processing things into the data definition. So those things need to separate. Just because you have a bunch of links you don't want to put pre-render and everything into that. You just say that these are links. This is the age graph, this is the title. We actually mixed data with presentation which we shouldn't have. So and then the problem that we have with the current system is that it's absolutely impossible to figure out where something is being changed. There's a, that's literally impossible unless you have a debugger which has a watch but even Zen debugger cannot do that pretty well. So on the other hand, on the other hand what we have currently and we should not throw out the baby with the both water is an incredible flexible system. The two are opposite. If you can change something everywhere then you will have a very hard time figuring out where it got changed. If it's only the data and not the logic it's gonna be easier. So a few things that I suggested to Alex is that we need to move to OOP so that if you change something then it's explicit. Then you are calling a method and you can sit on the method put a break point on it or just a print and so you see who is changing you. One, two. As I said I don't think this is workable I don't think we can get rid of PHP out of the templates that's unlikely to work. Third, exactly because HTML is recursive separating, templating as an arbitrary cut of block level, field item, region, page I think that this should be the same pretty much the same day and now. Just once again it should be separated as basically what the basics of web application should be that you don't mix data with presentation so we should re-separate that. This is all I want to say and also what I also want to say is that I don't think that anything we have currently is salvageable we need to throw out and we need to restart it absolutely because it's just grew, grew, grew, grew, grew this is how you got to the wiring diagram that you have there. I can tell you how we got there. Oh I know how we got there because I was there too. We had four API. We had four API, right? We needed, that was good and then we realized that there are things which are not forms, they need to be done in rendered time so we added pre-rendered in parallel we have realized that we need pre-processed for the themes and when the two collided in seven you got this. It was completely good intentions but by now this is just not salvageable. This is completely unsalvageable so I think that we need to restart absolutely from scratch with, we are, write an architecture, come up with an architecture and implement it. I said this to a few people here that I believe that in Drupal 8 we cannot release Drupal 8, we just cannot with this current system where you have these arrays of doom of hash properties on one hand which makes Drupal hard to learn and on the other hand, you have the OOP system which is going to be very alien to a lot of Drupal people so you basically are two paradigms which are hard to different people and so who is going to be able to learn that system? So I think that we cannot release 8 without completely rewriting this. That is awesome. I completely agree with a lot of what you said. So many comments to make on that but can I just start with can somebody please make me a t-shirt that says Drupal 7 and the arrays of doom? Yeah. I'd be terrified if I had to follow that. I'm actually going to turn around and look at the audience for this one. Consider two different audiences. People who want to change the look and feel of their site by pushing buttons and people want to change the look and feel of their site by writing HTML on editing templates. Which of those is more important for us to serve? Both is not an answer. Who says button pushers? Hands. Because they are fundamentally different use cases and one system cannot serve both equally well. And then you have a broken admin. We don't want to say you don't matter but saying, we kind of get away with saying we are primarily targeting one or the other of these. I don't personally care which one it is but we need to decide. Because then you end up with the templates that like the template, the table template which is the template plan. I just want to see a show of hands. Of those two, both is not an answer that we can support. We have been trying to support both for years and have failed at both of them. Okay. The main thing we want to serve is we want our settings that we write in code especially in the visual layer. When we hand our site to the content admin and we've got enterprise sites that run on this, they want to change the color of something. Why can't they have a button that changes our settings that we wrote, where we can still go in and modify it but then they can add their thing on top of it? This is almost starting to sound like features implementation which I think is actually an interesting way to go. Which we're now trying to replace with something better in Drupal 8 called CMI. So people keep saying both. I want you to think about the question. You cannot serve everyone equally in all cases. So who is a bad fit for certain use cases? Which of these is more important? I don't have the answer. Let me ask one concrete question related to that. So should the site builder or content editor be able to through the UI choose whether something is in H1 and H2 or in H3? Yeah. I'm seeing mostly nods. Okay. This is yes, but not necessarily everything. Sorry. A different topic, but I wanted to, I think it was Nate mentioned something about security. I'm on the security team and I think, I mean I totally agree that our theming system is dangerous for themers and I don't think we can expect the themers to fix it. I mean like buffer overflows weren't fixed until we started using managed code or it became impossible to create them. And we're not gonna fix cross site scripting until it becomes impossible to create those holes. And I don't know how we do it. The best, I know Ruby on Rails has an H function and I think basically you create a cultural convention that like print is like, oh my God, why would, no, don't use print. And you instead you have to use H or check plane or whatever it is. I don't know what, but I think if we can, if this new theme layer can incorporate smarter filtering, I think that gives us a huge advantage security-wise. If our elements are objects, I feel like we can make, it's possible we can make that sort of invisible, right? If the things that we're printing are objects, couldn't, just for the video listeners, it depends on how fine-grained the system is and then Rayleigh's gonna say something else. He's asking who here, show of hands for who went to Drupal God and Zegad. You say this after people waving into the microphone? Rasmus Lerdorf, creator of PHP in that Drupal God and Zegad showed us an example of another framework that uses a function call for every single HTML element, which means that it is not possible to write invalid HTML. It also is 10 times slower than Drupal is today because of just how much interaction you have there. So don't get too fine-grained with your objects or you end up with that. Okay, no, okay, sure. I think we're technically out of time so we're sort of in that middle break zone. At some point we might be kicked out, but go ahead. So the reason- Keep going into where I kicked out. So the reason that I keep saying that we should support both is that if we have a good separation between the data and the output. If we have a good separation between the data, it's so weird hearing your voice, between the data and the output, then the clicky interface can be a theme and a templating system can be a theme and that all kind of comes out. So I think we're thinking about it kind of in the wrong way if we're thinking that we can't support both because at a certain point you're dealing with data, at a certain point you're dealing with output and there's like, I'm not saying support both like in the same space maybe that you would have to make that choice and maybe there's clicky interface modules and there's templating interface modules. And that there's certain things that modules might have to do to support clicky interface, right? You need to lean into the microphone. Yeah, so there might be certain things that like what modules would have to support to support clicky interface. But if you allowed it to be more separated, it wouldn't be as big of a sort of worry anymore, right? Yeah. I think the problem you're describing is that halfway templates aren't working. We've got templates that are kind of human readable. We can kind of give them to people who don't know Drupal and they can kind of understand what's happening and we can build an admin interface that can kind of modify that markup and can kind of customize it through button clicking. But we don't really get markup that is actually human readable by people who don't know Drupal. I should say on the template side once it's rendered they can read it. But on the template side they can't read our templates and button clicking can't fully modify our templates. On the topic of one PHP function for all elements I was just about to send out kind of a joke tweet. Jayceen in her blog post mentioned there are only 109 HTML tags. Why do we have hundreds or even thousands of template files? Well, you could boil it down to there's just the opening caret, there's the element, there's Drupal attributes, there's the contents and there's the closing tag. If we wanted to get that granular we could and it would be easier to build an all button clicking interface to do that. But we wouldn't have templates that would be human readable. So I think that whoever was at the mic last was getting to the idea that we could have two templating systems, one that is designed to be human readable, human editable through TPL files and one that is designed entirely for button clicking. And maybe that is the way to do both. But I think what Larry is saying is that these halfway templates, the kind of templates that the semantic views module from Drupal 6 worked with those were hard for me to edit because they had lots of button clicking options. But I also occasionally needed to go into the TPL file and it was hard to discern what should be the realm of button clicking and what should be the realm of editing a TPL file. So what he said and what he asked, Larry's question actually was an answer, it wasn't a question. What he answered is that I have expressed my doubts of how can you have different templating on different levels. What Larry says is that the cutoff is what you can do on the admin side. So what you have basically and that's what he said is that you do need to have two kinds of templating and one is the layout that Chris is going to come up with and which is you can control from the admin interface and that's definitely we need to have a different way to put together because that is going to be written by Drupal. Inside that, we need templating which is going to be written by humans. This means that more or less at least by default we have something like a grid system. So we basically have probably a pluggable layout engine and we have a pluggable templating engine which only comes in once you have the layout in place. And I think that's what Alex said as well at one point that you will have something different up down to the block level and inside the blocks and that's probably what we need to do to facilitate. This means of course that you will not be able to button press to the level where you can change whether a heading is age one, age two or age three because that's not layout. But that's fine, that's fine. If you want to do that you can plug in something like a contemplate or something like that. It's, we need to make a cut. We need to make a compromise. This is what core can do, make it pluggable and work. Sounds good to me. I hate to cut you off here but I'm really, we only got like seven minutes until the next core conversation and which Roy is giving and I don't wanna encourage them that you'll need to let people sit down and get set up and we're gonna have to cut it there. I would love to hear more comments from people if you wanna come up to me afterwards and or next day or whatever. This is a continuing conversation that we need to have as a community. I'm just gonna tack one last thing on. Everything that the chicks and everyone else has just said is come full circle back to my first comment and the whole point of that was yes, we can do all of those things and we could probably make those human readable templates identifiable in such a way that the button clickers can get at them and utilize them. That's the point but yes, what everyone else said? So somebody suggested maybe we could create a groups.dripper.org group about this idea? I don't know. But what? Blocks? Sure, yeah, okay, yeah, let's do that. Okay, so we'll be posting about where we're gonna continue this conversation. We're gonna figure it out.


So, excellencies, distinguished colleagues, ladies and gentlemen, good morning. Welcome you to this broadband commission for sustainable development spring session in Hong Kong. It is a great pleasure to be with you here in Hong Kong, China, for this spring meeting of the broadband commission. Just some of you already come to Hong Kong many times, more than I do, but it's the first time for me to be in Hong Kong in March, in the past always in winter, in December. So I found something new to me, so I hope you will enjoy your visit to Hong Kong at this moment. So it's wonderful to see so many familiar friends and physicists around the table. Indeed, the last time I saw many of you was at the World Economic Forum's annual meeting in Davos, when we hosted a special session attended by a total of five heads of UN agencies and many of our inter-chair CEO commissioners. There's also five head of UN agencies, of course, including Irina Bukova, our co-vice chair, and also Ellen Clark, UNDP, and Ponziola, the UN woman, and two mayor colleagues, Hosein from the director general of UPU and Mr. Lee Yong, the director general of UNIDO. I was very pleased my colleagues came to join us at Davos meeting. There we had an excellent debate about furthering the investment case. So I'm delighted many of you have made the effort to come here to Hong Kong, China. I would like to express my warm thanks to our chairs, His Excellency President Paul Kagami of Rwanda, and Mr. Carlos Slim, represented by here by Mr. Carlos Jack, my dear friend, and my co-vice chair Irina Bukova. I'm deeply impressed by the commitment and dedication shown to the commission by the co-chairs and my co-vice chair. Let me also express my sincere appreciation to our hosts, my dear colleague, Madam Senyafang, and her team from Huawei, who have been fantastic in welcoming and supporting us all. And I know like all of you, I'm looking forward very much to our tour of Huawei tomorrow in Shenzhen. The spring's meeting is always a very productive meeting for the commission, a meeting in which we can share views and speak frankly and openly and encourage you all to do that at this meeting. Distinguished colleagues, this commission remains committed to our central conviction that broadband is critical if we are to achieve the sustainable development goals. And some of you also mentioned that maybe also in particular, move via broadband. I'm delighted to tell you that ITU is carrying this message as far and wide as possible. I personally am engaged in significant outreach with many industry executives and other UN agencies. Of course, I also convey this message to my visit member states when I talk to the head of states and head of governments over the last two years. Including my recent visit to FAO, and we are planning to work with UNIDU on the important of small and medium-sized enterprises, and I would encourage you, our commissioners, to reach out to other stakeholders as well. Or too often, it is assumed that the importance of ICTs for development must be widely understood, but in fact, this is often not the case. ICTs are seen as a secondary sector, a sector where the primary sector will solve everything, but in fact, that is often not the case. ICTs underpin vital achievements and modern services in many other sectors, and governments and industry have to work together to create the enabling environment so badly needed in many countries to facilitate the growth of broadband for sustainable development. In this respect, IT is in fact currently working on preparing a report, insights into ICTs for achieving the SDGs, presenting perspectives from many heads of UN agencies, and a warm thank you to Irina in this regard for contributing your personal insights into broadband and education. During my recent conversation with the new Secretary-General of the United Nations, I also advised him that we have this report, and he is very pleased to get information that the report could help the UN to further facilitate the ICTs' assistance to the other SDGs. The reports find that today, more than ever before, broadband and ICTs offer large-scale opportunities to transform economies, empower individuals, and contribute to development goals. So ICTs offer a lot of promise with many potential opportunities, but there are still significant and substantial digital gaps that need to be bridged, some 3.9 billion people without internet access and around 5 billion people without mobile broadband. And I know Dennis has a lot of ideas about that, and the importance of providing everyone with 3G. I recently worked with Israel and Palestine to try to provide 3G service in the Palestine area, and I was very pleased last week to get some progress report. And we heard a lot in the working group on gender history about how to bridge the digital gender divide, and there are issues in other sectors as well. And we need to be vigilant about putting safeguards in place to protect consumers, given the extraordinary abilities of new technologies. For example, how we are health and education sectors adapt to the digital transition. Most people would agree with healthcare workers having access to confidential health files in the name of diagnosing and treating diseases. But what would happen if that information was leaked or shared with either employees or health insurance providers? In this regard, I was very pleased to see that we had a truly excellent report by the working group on digital health released at the Mobile World Congress, which addressed a lot of issues that made it discussion. I think that must might give us more information. Our commission continues to go from strength to strength everywhere I go. People ask me about the work of the broadband commission. So we are clearly building momentum. And in this respect, I'm pleased to announce new commissioners who will join us at our next meeting. The commissioner of the European Commission, the vice chairman of the European Commission to replace Mr. Odinger, the commissioner who joined us in the past. And Mr. Pei, R.G. Pei, the chair of FCC. And I had a phone call with him recently several times, and he's pleased to join us. And from the interest I see in the commission, I'm convinced we are really building something we can be proud of. Ladies and gentlemen, this is a working meeting, and we are expecting to learn from you with all your insights and some more debates. In particular, as the commission's targets are not six years old, we would like to ask you for your inputs to modernizing and updating them. Noting that over the past five years, they have helped highlight the commission's work and allowed us to measure the progress that has been made. And when we talked with our partners, and I talked with our co-chairs, they all expected new and updated ideas of targets. Our flagship report, the State of broadband provides a valuable snapshot of the current status of the broadband industry. I invite you all to get involved now and contributed to this annual exercise. And we are also looking forward to the high-level political forum, HAPF, which will take place in New York from 10th to 19th of July this year, on the same eradicating poverty and promoting prosperity in a changing world. The HAPF will examine goals one, two, three, five, nine, 14, and 17. And the targets for this, and perhaps the commission should consider hosting some kind of a side event for the HAPF to really bring home the importance of broadband among the many government ministers and policymakers in attendance. That is an idea I just put out there for your consideration. And in fact, this morning, prior to this meeting, with co-chairs and my colleague, a co-vice chair, we exchanged views on this, and we'd like to bring your ideas to that session. And myself and Irina, we are good. So we can see what you think and you would suggest. Thank you very much. And let me conclude by wishing you all a very productive and free-for-meeting. And now I would like to also invite my colleague Irina to have her remarks. Thank you.


Hello everyone and welcome to this Eucadata Service webinar, introducing the Open Database Connector. I'm Margrita, I'm a Senior Communications Officer working for the Eucadata Service and presenting today is Peter Smyth, he's a Research Associate working for the Eucadata Service at the University of Manchester. Thank you all for coming and let's start with what we are discussing today, which is what is ODBC? So what we're going to show and look at in this webinar is the definition and background of ODBC, an explanation of why we would want to use it, because it's not always necessary to use ODBC to connect to a database, but sometimes it has some advantages and that's what we're going to try and cover today. I'll explain what ODBC drivers are available because each database tends to have its own ODBC driver and you've got to make sure there's one available and you've installed the right one for your database. And then I'll go through the process of installing an ODBC driver. They tend to be slightly different for each ODBC driver, but there's enough commonality in the process for once you've seen one you understand what's going to be needed for any other one that you may need to install. And then we'll go through some examples of using ODBC. We've got examples in Python and R and in Excel. So let's start with the definition. What is ODBC? Well, it stands for Open Database Connectivity or probably more commonly referred to as Open Database Connector. It was developed by Microsoft in the early 1990s and effectively it's an API, an application programming interface for accessing database systems. We had a webinar on APIs a couple of months ago, so hopefully some of you will understand what an API is. But essentially what it is, it allows a common interface, a common set of commands which can be given to an application that the application understands and can respond again in a well-defined way and give that response back to the person who made the request. And this makes it very easy to provide interfaces to applications even though kind of scenes application may change over time. The API is often referred to as drivers, which I think I've already done once or twice, and certainly it's a way I would normally refer to as an ODBC connector as an ODBC driver. Oops, overshot there. Let's go back one. Each database system will have its own API and hence its own ODBC driver. So an ODBC driver is associated with a database system, not with the application that's going to make use of that ODBC driver. For Java-based applications, which are typically non-Windows applications if you're using Linux or whatever, there's also something called JDBC which is Java Database Connector and that serves a very similar purpose to ODBC. They are sufficiently similar for us not to need to consider them separately and we'll just be looking at ODBC today. So why would we want to use ODBC? Let's start this by considering the situation where you don't really need to use ODBC at all. If you've used Microsoft Access as a database part of the Microsoft Office package, what you get is a single database system package which contains two parts. You get a database engine which is used to store your data in and actually process the query to retrieve the data from the database, but you also get a GUI, Graphical User Interface, and this is the part which the user, the end user actually sees when they're trying to make use of Microsoft Access. This allows the user to interact with the database engine by, it gives you query answers where you can write your queries in a very graphical way if you want to, and it will also present the results in nice tabular format and allows you to create nice reports which can be generated and used offline. But essentially what we've got here is that there are two parts of this package all in one, all installed at the same time. The database engine and the GUI. So in pictures what we effectively have for Microsoft Access is the GUI and the database engine, and it all comes in the package which installs Microsoft Access and then both the GUI and the database engine are there for you. If you look at some of the bigger databases such as Oracle, Microsoft SQL Server and MySQL, they all have database engines and GUIs just like Microsoft Access does. The difference here is that they're essentially separate products. So what this means is you can store the database engine without installing the GUI and a single GUI installation can give you access to many database engines. Now in practice in our desktop environment that's not a very likely scenario, but for large organizations running multiple databases it can be very useful to just have a single GUI which controls all of the databases. So you can see the advantages of having the GUI actually as a separate item to the database engine itself. If you did install them both then effectively you end up with a very similar to Microsoft Access environment that we looked at a minute ago. The difference here is of course you've got bigger databases and there's more functionality involved. So what we've seen for Microsoft Access is that and for the larger databases what we effectively have is the GUI separated but connected to the database engine. There are separate applications but they're very tightly coupled. Again we're not using ODBC here, that's just the way you would normally install and use these database systems. So the point being that you can install the database engine without installing the GUI. So instead of this situation what you could effectively do is say I don't want the GUI and this is where ODBC comes in. So we now have some database and as I've said before this could be SQL Server, Oracle, MySQL and many many other ones. Almost all of the large database systems will allow you to connect by ODBC. And what we're going to do is get an ODBC connector which is specific to that particular database that we're going to use. And then from the ODBC connector we are going to be able to connect a whole variety of different products and applications. So for example we can use R to connect to ODBC and onto the database or we can use Python which all goes programming environments. Alternatively you can use Excel or things like SPS and Stata and what have you. So just get through that in words. You can have ODBC connections which can be used by many third party applications such as Excel, Stata, SPSS, MS Access. This is a different set to what I've just shown you on the picture but the point is there are many many more which can be used in this way. Equally you can use ODBC connections from programming environments like R and Python as we've already said. A slight complication here is that when you're using ODBC's drivers with these specific packages or these languages you typically need an additional package or library in order to interface the ODBC itself. These packages or libraries are essentially these mini APIs associated with the language themselves. So in Python you have the Pi ODBC package and in R you've got the R ODBC library and these are easily installed. If you have these languages installed on your desktop it's very easy to install these additional packages and libraries to make it to allow you to use ODBC. The next thing we want to do is look at how do we install ODBC drivers. What we're going to do is I'm going to go through a specific example of this but before we do that it should be pointed out that some of them are automatically installed with the software and in particular the Microsoft products like Excel and Access. When you install those on your PC the ODBC drivers associated with them are automatically installed. Notice as in Excel there Excel you can actually use both as a client application to access some database or you can actually use Excel as a sort of database and access from other sources as well. So that's on both sides of the coin and other Microsoft products do that as well. Microsoft SQL Server can be used to access other databases via ODBC but principally it's used to provide its own ODBC driver so other applications can access it. For those which aren't installed you need to download the code from a database vendor or third party site. Let's say download the code.  You can go to vendors like MySQL to get MySQL ODBC connector or you can give the Hive connector. Hive is the relational database associated with Hadoop which is a big data ecosystem which again we've had a few webinars on in the past and that's what we're going to demonstrate today when we do the demonstrations. If you're installing ODBC drivers you will typically need admin rights on your PC or laptop in order to install it. But once you have installed it you only have to install it once and it will be available to all of the users of that machine. So actually going through the process of installing the ODBC driver we're going to do this for the MySQL one just as an example. So you're going to go to the site which is this one here which is I think sort of mentioned at the end and for the information slide. And you download the correct version for your applications and architecture. So for example you might have a Windows machine and you might have a 64 bit application that you're going to use it with. Be careful, a modern Windows system will almost certainly be 64 bit Windows. What you're really interested in is how many bits your application is. Now that may not be something that you've ever considered before so I'm just a little bit of a warning here is that many Microsoft Office installations are installed as 32 bit applications and are in Python if you've installed them yourself you would normally go for the 64 bit installations. But either of those could be swapped the other way around so you really need to know which one it is that you're going for or what one you need. When you go to the MySQL ODBC driver site this is the sort of screen that you're going to get presented with. You select the platform Microsoft Windows and then you've got the option of picking whether you want the 32 bit installer or the 64 bit installer. You can actually download these as zip files if you want to but I would recommend you use the MSI installer because then when it's downloaded you just double click on it and it will run a little wizard to install the ODBC driver for you. What I said before about making sure you get the right one 32 bit or the 64 bit in fact there's nothing to stop you downloading and installing both 32 and 64 bit if you want to. But you do have to configure them separately depending on your application that you're going to use. So let's consider configuring these ODBC drivers now. Once you've installed you need to install a little bit of configuration and the reason for this is that the ODBC driver has to know things like who it is, who you are, what database you're going to talk to and so on. So it varies slightly from one driver and database to another but they're all basically the same sorts of information that you need to give it. And this is things like your username and password because you're going to access a database which is typically going to require you to authenticate to that database. So that explains them. You need to know the location of the database. If you remember back to that little diagram where we had the GUI and the database engine and the whole point is that we've now separated the two. We've stuck ODBC in the middle. Because the two are separated, the actual database engine doesn't actually have to be sitting on your PC anymore. It could be remotely connected to it or you may need to remotely connect to it. So you need to know where this database system is. It could be your PC, it could be some remote location. And again, because we're typically talking about the larger database systems unlike Microsoft Access where the database system itself can actually contain many, many databases, then you need to tell it what database within that system you're actually going to try and access. So what we have next is this is the example of a high ODBC driver that the window that you get when you say you want to configure the driver. And there's only a few, there's lots of things in there, but only a few of them cover things that we need to provide. So typically what we need to do is give it the host addresses, the database system you're trying to access. And you can see here in this example, all I've actually given it is the IP address of the machine on which this Hive database is going to be running. So that's that part. We then have to say what database we're interested in within this Hive installation. And the default database for Hive is a database called default, which is what I've left out of two because that's where all my tables are. But I still need to authenticate and so I still need to provide it with a user name and a password. But without that done, everything else I can leave the same. If you look, that would be the Hive ODBC driver. Looking at the MySQL one, it's actually very similar, a bit cut down. But again, bits of information that you need to give it are root password, a bit called user, which is root in this case, the password for root, the root user, and again the database that you're interested in using. If you may have noticed that on both of these, both of these, there is a little test button just to the right of the bit I've covered in red there. And if you click on the test button, it will actually make a connection to the database just to check for you that you've given all the correct information. It's a very useful thing to do so you make sure you do actually have a legitimate connection into that database system. So having set up your ODBC driver and configured it correctly, we can then think about using it. So what we're going to look at are some examples, and the examples I'm going to show you are, we're going to use Hive as a principal database. So we're going to connect, we're going to connect Hive to Python using Python, and then we're going to use Hive data to R, and then we're going to do it using Excel, which is a third party package. So we're going to leave the presentation for the time being and start looking at some of these packages. First one is Python. Now for Python, I've got a few little strips set up here. I'm using Jupyter notebooks as I've done on previous webinars. Some of the information I'm going to just run through quite quickly because a lot of it is just Python rather than talking about ODBC. But in this first line here, this import statement tells the system what packages I'm interested in, and here I've got my PyODBC. If I run that, it just runs, I don't get any response to it. Now that I've got this package in PyODBC, I can make calls like PyODBC.connect. This is going to make a connection to an ODBC driver, and the ODBC driver I'm interested in is this, Sample Hortonworks Hive DSM. If you run from a couple of screens back when I was configuring the Hive ODBC, that was the name that it had been given. So when I run that, I get a connection, hopefully, and then in this next little paragraph, I can actually start using this connection.  That's about as basic an SQL type command you can get. It just says return all of the rows from that table called GeoAll. And what it's going to do is, in fact, that's what the command is, but then I'm just going to say I only want the first one, which is why I've got this fetch one. And then if that was successful, if it successfully selected a row from that table, I'm going to print the row. So when I run this, what I should see is the ODBC, or the Python program is going to collect ODBC, ODBC connects to the Hive database, it runs the query, selects star from GeoAll on the database. The database returns the results back down into Python, and then I can print what the result was. So if I run that, and I wait a second or two, I can see I've got this as the first row from the GeoAll table. It's got lots of different columns in there, won't go through them all, but that is effectively one row of that table coming back. So now I've actually got the data back into the Python environment. If I wanted to be more ambitious on the second one, again I've got the same select statement, but here I'm going to fetch all of the rows instead of just the first one. And here I'm going to print what is effectively the index number seven from each row being returned. And index number seven is actually this text line, a bit of text up here. So if I run that, it's going to go to the database, go via ODBC to the database, and return all of those lines, all of those values in that column there, and you can see them down there. As there's more than 14,000 of them, we don't really want to go through them all, but the point is I could have put them into other processing and offer whatever it was I needed to do. So just clear that. This next one is really just to illustrate how sort of flexibility system is. You don't have to have all of your query in a single like-like-like. You can set it up as two parts of a variable and you just append them together, and instead of using the cursor execute with actually a string, you can just put the variable name in there, and then it runs the same thing. And here what I'm doing here, I'm counting how many rows that are in the electricity table, which is again a table in high environments on the Hadoop system. If I run that, it comes back into over 3 million rows, which is just counted. Python hasn't counted the high-counted rows and just returned the answer to Python, and all I've done here in the bottom is to show that it came back in about one second. So it's quite fast in processing the queries. And then on this last one, what we've done with that so far is we've just done very basic SQL type statements. But in fact, because we're talking to Hive, we can actually send it any command that Hive understands. And one of the things that Hive understands is this is a command called show tables, which you can probably imagine is going to show me all of the tables that are in that database. So if I run that, then I just get a list of all of the tables available into the database. So for example, I've got a table called DT9. That's all I've got there. So that's using ODBC connector and Python together to access the Hive database. But now I move on to R. In R, again, I'm using the standard R Studio environment, which we've used before on other webinars. I'm just going to run these first few lines up here to set the scene, say what libres I want. Notice that this top library, RODBC, this is the one we're really interested in on this occasion, because that's going to allow us to connect to ODBC. And hopefully, I think that's okay. Then, I haven't done that. I've got a command here, which is very similar to what we saw on Python. The function name is different. It's called ODBC Connect here. But again, all I'm giving it is the name of this Hive ODBC connector I want to make use of. For example, Hornworks Hive DSM. And I'm sending that to a variable name there. So if I just select that line there and run it, and that comes back in a mix, you can tell from this line up here that that's made a connection. And again, the only channel is just a variable name. I could have called it x, y, or z. If I wanted to, I've just happened to call it channel. And from that point, in the same way as we did in Python, I can run another function from our ODBC library called sqlquery, which has quite a meaningful name. I'm going to run an sqlquery. I'm going to use the channel that I've just set up, i.e., the ODBC connector I've just made a connection to. And in here, I'm just going to do a very simple select statement in it written in sql. And if I run that, the results of that select statement, i.e., all of the rows from that ELECT09 job table, are going to come back and they're going to be written into this effect that is going to be a data frame called DF1. So if I run that, and you can see up here, DF1, it's now returned, and I've got 143,290 observations of 13 variables. If I wanted to have a look at them, I can do this and just see the various values for those rows in there. So the point is, having got this far, I've now got the data back from the Hive database, and it's now sitting in a standard R data frame, and from that I can do anything I like to it. So I've just got down here a little bit of R script to draw a couple of graphs. I'm just going to run that all together, just to show that what I've really got is genuine data from Hive, and when it finishes running, I've got a couple of nice graphs. I mean, I've just chosen to do a couple of graphs. Once you've got the data back in the data frame in R, you can do anything you like to it, as you would do with any kind of R analysis that you're interested in doing. Just a bit of variation. I've got here using something very simple and very similar, but here I'm using the MySQL ODBC driver, which I showed you downloading before, and again, it's exactly the same. I've just got the library R ODBC, which I won't need to run because I've already run it. I've changed this very name just to make it distinct, but again, the ODBC Connect statement is exactly the same as we had before, only this time I'm saying MySQL. In here, I'm just going to run a very simple query to return data from a table called City, and I'm going to put this into DF2. When I run that, it runs exactly the same, and you can see here I've got 4,079 variables. The other thing I just wanted to point out when we're doing this, so far this is very simple select statements and SQL statements and the show tables, which I did as well in Python, but you're not actually restricted in how complex these SQL statements can be. In fact, it can be anything which Hive, in this case, because we're using Hive Connector, or anything which Hive accepts as valid SQL or HQL, you can put into this query. So here, what I'm doing here, TableDT9, which we saw before, so I'm not going to run this because it already exists, but here we're creating that TableDT9, and it's selecting various columns from two different tables. I've got to join on here. I'm joining GeoGal in recommends, and I've got substrings and wareclothes and all sorts of things. There's no real limit on how complicated this query can be.  So there's no real restriction here on what you can actually put into this SQL query function, any kind of valid HQL. So that is using R. And finally, we said we'd use Excel. So I've got Excel open here. This is an empty workbook in Excel. If I get to data, and I can say from other sources and from Microsoft query, I should say that depending on what version of Excel you've got, these might be in slightly different positions. But Microsoft query would definitely be there. And from when you click on that, you get open data source and you can see down here, Sample Hive DSN, which is my ODBC connector for Hive. So I'm going to say OK. As soon as I click OK, Excel itself will make the connection to the database, the Hive database, and it will actually get me a list of all of the tables on that Hive database. So again, you can see DT9 is still up there. So what I'm going to do is I'm going to go for something called ELEC months. ELEC months. I'm going to say I want all of the columns. There's four columns in there. If you want to know what columns are, you can expand that and it will come back and tell you. So probably shouldn't have done that. OK. If you click on next, you can do things like filtering. We're not going to bother with any of this, but if you wanted to, you could do. We're going to say you can sort them by things if you want to, but we won't bother. All we're going to interest you is getting this data back into Microsoft Excel. So we click finish. It comes back and say, where do you want to put the data? Well, top left is fine. We just can say OK. And then we wait a few seconds. This little message up here is getting the data from me. Oh, look, it's returned all of the data. And if I go to the end of that table, you can see, well, pretty much 300,000 records it's returned. OK. And of course, once you've got it in Excel, you can do whatever you might want to do with the data, draw graphs or do further processing or whatever. Now, I just wanted to emphasize that. What I was saying before about the complexity of some of these queries, if I just go through this process again and it doesn't really matter at this point what I'm doing. I'm just going to pick this cordy to a record. There's no idea what that is, but there's lots of things in it. I'll just go through this again. But at the end here, instead of just saying return the data Microsoft Excel, I'm going to say view the data or edit the query in Microsoft. Edit query Microsoft query. And I'll just click on finish. Instead of putting the data straight into the Excel worksheet, I actually get Microsoft query come up and it actually shows me what the data is down here. But what is interesting, if I click on the SQL button here, I can see the SQL statement which it sent via the ODBC connector to Hive to get this data back. Now, I can wipe all of that out and write something complicated in there like my show tables or any other valid SQL statement. If I say OK, Microsoft query gets a little bit upset because you can't draw a picture of that but we don't really care about that because then what I get back is my list of tables as I had before and I can just return that into Excel in some way. So I can again write a query to suit what I want rather than just do simple selects and filtering from tables. So again, I've just done a show tables there but again I could have had to create tables or done complex aggregations in the Hive environment and then only have the results returned to Excel. So it's a way of getting the heavy work done on the Hive system, which is on a server somewhere and then you just get the aggregated results back into your application or your programming environment and from there you're in a comfortable environment and you can process that data as you may choose. So that is it for the demonstrations. So back to the PowerPoint slides. And a little bit more information where you can get these ODBC drivers for. I've given you the one for the Hive ODBC and the MySQL one which we use. Basically if you've got any other database like Postgres or anything like that, if you just Google the name of the database followed by ODBC, you'll undoubtedly get a link of where you can download the suitable driver from. And with that we have any questions? Thank you Peter for presenting today and thank you to everyone for attending. Hope you found it interesting and useful.