 Okay. Hello, everybody. I am going to talk about a personal project of mine called Schema. But first I'm going to say a few words about me. My name is Emmanuel Seyman. I'm a CIS admin by trade. So when I don't do a lot of coding, but when I do, I prefer it to be pro. And it usually comes out as a Python. I live in Paris. So I'm a member of the Paris Promonger Group. I'm a member of Parinux, the Paris Linux user group. And I'm an occasional contributor to Bugzilla. Does everybody know what Bugzilla is? If you do not know what Bugzilla is, you are in the wrong talk. A few years ago I contributed to Bugzilla a lot more than I do now. There was one website that I found very useful, which is the one with the URL behind me. And this is a web application that will allow you to choose a version of Bugzilla, click submit, and get a web page that documents the schema of the database that that version of Bugzilla uses. So you get a list of every single table. For every single table you get all the columns. You get all the column types and additional information. And I found this useful. I kept coming back to it back and back time and time again. So this was useful, but it had a few problems to go along with it. The first one is that it is two tables. And there was no possibility to reuse code from Bugzilla in Bugzilla schema. And it was just a waste of opportunity. More importantly, Ravenbrook, the company that was behind Bugzilla schema, stopped supporting it because they didn't have business model for it. So they stopped adding versions of Bugzilla in 2009, which means that the last version of Bugzilla for which you can get the documentation for the database schema is 3.4.2. To give you an idea, we're now on 503. And there's an unstable version called number 511, which is out. So all of this is old and no longer very useful. To top it all off, I've copy-pasted some words, a sentence from the readme. The code was written in ad hoc manner. Adding a version of Bugzilla is relatively complex. It requires you to add that version of Bugzilla in four separate locations in the code. And that's if there are no changes between the Bugzilla in the database schema between that version and the version that immediately precedes it. So this is a pain in the backside, which is why they stopped maintaining it, actually. So problems. Thankfully, they all have solutions. The solution to the fact that it's written in Python is simply to port it to Pro. They actually uploaded the code to GitHub a few years back, saying you can fork this if you want. So I did. Adding a version of Bugzilla is complex. So let's try and make it as simple as possible. Ideally, you'll have a data structure that contains all the versions of Bugzilla. And when you want to add a new version, you'll simply add one entry to that data structure and be done with it. Code is hard to maintain. Well, the solution to that one is writing as little code as possible. And in ProSpeak, that means using Cpan as much as possible. So these are the guiding principles that I started with. The last one, use Cpan as much as possible. So I went looking on Cpan to see what modules I could use. I needed a way to talk to databases and get the schema out of them. There's not a whole lot of choice on Cpan for that. There's really only one which is DBIX class, which does an incredible amount of stuff but also includes a module that's called DBIX class schema, which abstracts the schema from a database and converts it to ProCode. So I thought, hey, this is useful. Let's see what this does. DBIX class has a whole number of extensions that are also on Cpan. One of these is DBIX, DBIX Autodoc, which takes a schema object and converts it to HTML using template toolkit. And I thought, hmm, that might be useful. Let's keep it. And once I had this, I could talk to database schemas and write HTML. So now I needed a way to write a dynamic website. I started looking through Cpan, then gave up because after 50 possibilities, I was drowning in choices. There are an incredible number of ways you can write dynamic websites in Pro using modules on Cpan. I toyed with the big three, Capitalist, Dancer2, Modularlicious, and realized that they all rely on plaque. They're all plaque applications at heart, so I decided to not use any of them and just write a plaque application directly. In hindsight, this probably wasn't a good idea, and I should have stuck with Modularlicious or Dancer2. But hey, it is what it is. So first thing to do, write a proof of concept. DBIX class comes with a pro script called DBIX dump. This will connect to a database, extract the schema, and write the schema object on file. So hey, sounds like something that's useful. So this is the command line I wrote, and I had a schema object on file. Then I looked at DBIX autodoc, and DBIX autodoc comes with a pro script that is called DBIX autodoc, which takes a schema object on file and writes the HTML page that documents it. So I wrote this command line, and I had an HTML page that documents the schema that is used by Bugzera 503. So I was pretty excited about this. I mean, I had written two lines of code, which is one line of code more than I'm used to doing in pro, but never mind. And I had a proof of concept. So I uploaded it to one of my servers, hopped onto the Bugzera IRC channel, and asked the Bugzera developers if they found it interesting. And this is the only response I got. This is Jerv, who basically summed it up by, with, I always thought it was great from a technical point of view, but I never really could understand who would find it useful. And when I realized that this was the only result I was, the only response I was going to get, I decided to stop working on the project and to focus on something that would be a little more useful to people. This was on the 12th of August. This was the second Wednesday of August. And the second Wednesday of every month is when the Paris pro mongers have their meeting. So on that evening, I went to the meeting. We started talking pro, which is what we usually do at the pro mongers meeting. And I told them this story and all the pro mongers who were at the meeting told me this is a great idea. You should keep working on it and you should take all the Bugzera bits and stick them in a Bugzera module so that people can reuse this, not only for Bugzera, but for other applications as well. So I did. Well, I continued working on it. Then I realized that the first thing I had to do was download 200 versions of Bugzera and install all of them, install each one to the point where I had the database set up, run the dbic dump script I showed you so that I could get the corresponding schema object. That took two evenings. It started getting problematic when you start going back to Bugzera 20 something, the Bugzera 2 versions, which all go back to 2002, 2001, which rely on the pro modules that existed back then and don't actually exist now. So I asked the pro mongers for a little help and basically we dumped every single version of Bugzera, well the schema for every single version of Bugzera. You end up with a lot of modules in this case, a lot of schema objects and most of them are identical because the stable versions of Bugzera don't change the database schema. So basically you end up with a bunch of near identical schemas. Thankfully dbix class comes with dbix class schema diff, which tells you if two schemas are the same or different. So I made a list of 200 schemas, ran random through this and removed one of the two if the two were identical. You then end up with 26 schema objects, which represent each of the database schemas that Bugzera went through during its lifetime. Then you get around to writing code, which actually turns out not to be a whole lot of code because most of it has been auto generated by dbix class. So there's a schema psgi file, which is what I used to run plaque and which is really about five lines of code. There's a schema module, which is mostly rooting and there's a schema bugzera module, which basically does the mapping between the version of Bugzera that the user selected and the version of Bugzera that first had the database schema that went with that version. And now I can, okay. Perfect. Thank you. That good? Had the back? Perfect. So this is the index page, which right now doesn't have a lot of stuff. Over here, you have every single version of Bugzera that has ever been released. Let me go back to the last stable version. So you can choose any version of Bugzera, press the view schema, and okay. So you first get a list of all the tables that are in the schema. A short introduction, which right now is hard coded, and you get for every single table, the columns, the column type, additional information, constraints like what's the primary key, what are the foreign keys if there are any, and there you go. There's still work to be done on this. The first thing, the next thing I want to do is abstract out all the Bugzera specific stuff into the schema Bugzera module. One of the persons who helped me on this project was Madang, who's a pro hacker, and who wrote schema rock tables, which is on CPAN right now, because he liked my idea and needed to reuse it for something else. So once I've abstracted out the Bugzera specific parts, I'll go see Madang and see how we can make schema rock tables work with schema, and you'll be able to choose between Bugzera and rock tables and get any version of the schema that goes with any version of rock tables as well. One thing that Bugzera schema did was allow you to choose two versions of Bugzera and show you a diff of the two schemas if they were different, and this is something I haven't added yet, but I plan to do so soon, because again, this is useful when you're looking at the version you currently have, the version you want to update to, and you want to see what database changes are going to happen along the way. And if you have any ideas, well now is the time. And shout out to the Paris ProMongers, because if they hadn't convinced me to keep working on the project, I wouldn't be here, and you'll be listening to someone more interesting than I am. Madang dumped all the Bugzera to something versions, I still have no idea how he did it, and the main maintainers of the ProModules I use, Rabashushi, Ilmari, and Miyagawa, who maintain all the modules and all the contributors who worked on them. You have two minutes, Jeff. It's a great technical schema, but what is it useful for you? The point is to document your database schema. At work, we still have a lot of applications that have no REST API, and basically we interact with them by going through, connecting to their database and writing SQL queries. So we kind of need documentation for the schema, not as we, not the one we think we have, but the one we actually have in production. So this is why I thought this would be an interesting thing to do. Madang uses it because he writes Rackman, a ProModule on Cpan, which interacts with Racktables, and he needed a way to check that his SQL, which versions of Racktables his SQL queries run on, and which ones they don't run on. So this is why he did schema Racktables. So the disk, I can see, might be more useful. That's definitely worth adding. The thing you could add, I don't know how hard this would be, was you give it a thing of SQL, and it figures out what's a table name and what's a column name in the SQL, and just says, yes, this SQL will run against this SQL. No, this SQL will not, because it mentions this table name, but obviously you don't have to run it. So you wouldn't need a full kind of SQL engine that could take an SQL, so they can break it down. Absolutely. And I'm pretty sure that DBIX Classic can do that out of the box. So, yeah, okay. Thank you.