Just another WordPress site

Menu

Tag Archives: mysql

Laravel provides an implementation of the Blade templating engine which lets us define generic templates for our data and page layouts. What I’m going to cover in this post is some really basic templates which we’ll base the rest of our designs off. We’re going to need two different layouts: a default full-width layout and a default with sidebar layout. Breaking those down further, we’ll also need a template for the header, the footer and the sidebar itself. So, let’s get to it!

(Note: I am going to assume a working knowledge of HTML5)

Templates

To begin with anyway, all of our generic layout templates will live in
resources/views/layouts so we can extend them later for more specific templates. As well as this, all of our files will end in
.blade.php so signify that they are blade templates. If you’d like to read more about templating, I recommend having an ol’ sconce at the laravel documentation on the subject.

Header.blade.php

To begin with, we’re going to need a
&lt;head&gt; section, as well as somewhere to begin our
&lt;body&gt; too. So here’s some generic boilerplate HTML to begin our project:

The first kind of laravel-specific thing we’re going to include is a
@yield() for the site title.
@yield() takes one mandatory parameter and one optional parameter as such:
@yield('some_name','default_value). The name can be anything you like which we’ll refer to in later templates. The purpose of
@yield() is to set out placeholders for content/sections later on.

All the below is doing is saying that some of our pages will have their own title but, if not, use the SITE_TITLE variable in our .env file as the default.

XHTML

4

<title>@yield('title', env('SITE_TITLE'))</title>

Next, we’re gonna need a by of shtyling to make our website look pretty. So then, let’s include some required CSS for

FontAwesome – just some really awesome icons I prefer using to Materialize’s icons

Later on, we might need to include some extra stylesheets or add some extra tags to the head because Open Graph Tags are something every budding web developer should know (seriously, don’t build a project without them – it’s practically unshareable that way).

As such, we’re going to define two extra placeholder sections: one for extra CSS and one for extra header content.

XHTML

15

16

17

@yield('extra-css')

@yield('extra-head')

And, finally, we’re gonna add a placeholder for a custom body class so we can properly scope our styling.

Default.blade.php

Those two are all well and good but we don’t want to repeat ourselves and we’ll definitely want to deviate from the layout so something we’ll do here is stitch the two files together and put in our placeholder for a “content” section. This is the entire default.blade.php file:

1

2

3

4

5

@include('layouts.header')

@yield('content')

@include('layouts.footer')

Sidebar.blade.php

Here’s that fabled sidebar I just mentioned. When a user is logged in, they’re much more accustomed to a dashboard view of things for technical-type things (like AWS, Azure, facebook advertising, etc.). As such, there’s only one thing we absolutely need to adhere to in order to make sure navigation feels familiar – put our navigation in the sidebar. With Materialize, this is actually dead simple to do.

Below is the entire code of the sidebar but I’ve put in some helpful comments to help explain certain things. You don’t need to worry too much about the individual class names but if you’d like to learn more, you can check out the Materialize documentation.

default-with-sidebar.blade.php

As with our other default template, we’re just going to stitch together some of our base components and create a nice simple default layout for the admin area. It’s the same as before, we’re just going to use Materialize’s columns to define some constraints for the main content section and make sure it doesn’t ruin our view.

XHTML

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

@include('layouts.header')

<!-- Our entire page will be one row -->

<div class="row with-sidebar">

<!-- Our side nav has a natural column width

so we won't define one here -->

@include('layouts.sidebar')

<!-- The main content will need to be constrained

so it doesn't overflow behind the sidebar -->

<main class="col offset-l3 l9 s12">

@yield('content')

</main>

</div>

@include('layouts.footer')

Wrap-Up

Well, that’s week 3 down and I hope you’re learning lots. These are just the base on top of which we build all of our views. It’s really simple to work with views within Laravel. I should have mentioned it above but anything within two sets of curly braces is executed as if it were a PHP command then echoed out (displayed on the web page). Later, we’ll pass data from our controllers to our views and fill our templates with actual, real-life data :O

Join us next week for another exciting episode of “Evan talks to himself about how he built NetsocAdmin so he doesn’t have to write actual documentation”!

Models aren’t just pretty runway strutters with capes draped over one shoulder and a small pomeranian in the other, they’re a vital part of the MVC methodology (it’s in the name after all). Models will be our main way of representing the underlying database and the relationships on top of that.

This is part 2 of a series on Building Netsoc Admin. If you haven’t already, you can read the previous parts over here.

Laravel ORM

Laravel provides a really nice interface for interacting with and exposing models (ooo la la). Laravel uses Eloquent ORM (Object-Relational Mapping) to handle its models and relationships. This will give us a nice way to add to, delete from, query and generally interact with the database. First thing’s first though, we need to architect our database and make sure we have something to interact with in the first place.

Database Migrations

All of our definitions and changes to the database will happen in sequential migrations. Working from scratch, on an empty development database, it may seem like a pain to use migrations but their main benefit comes as you build on top of your software in the future. Each migration script has a specific change and a method to entirely reverse it.

That way, our database changes are almost like version controlled commits. We can easily migrate (commit our change), rollback (reverse a change) and refresh (rollback all changes then migrate all of them again). In this blog post, we’re going to run a series of basic table creations to house all our data.

Making Migrations

To begin with, we’ll login to our vagrant box with *vagrant ssh* and then *cd /var/www* to get to the root of our project. To create a migration for a new table, we can run:

1

php artisan make:migrate create_users_table--create=users

If we wanted to just make a change to an already existing table, we can leave out the *–create* flag and it won’t include the create table script in the new file.

This creates a db-layer relationship between our databases and our users, I’ll explain a little more about relationships a little further down within the models so don’t worry too much about it right now.

Now, though, we have to consider what happens when we rollback a migration. For the first time, we’ve gotta replace the default *down()* function in order to drop the foreign key as well. It’s fairly straightforward, we simply put in the following:

PHP

47

48

49

50

51

52

53

54

55

publicfunctiondown()

{

Schema::table('mysql_users',function(Blueprint$table){

// There's a predefined format for how laravel names

// foreign key indexes

$table->dropForeign('mysql_databases_user_id_foreign');

});

Schema::drop('mysql_databases');

}

We’re leveraging the table schema to drop the foreign key and then drop the table afterwards.

MySQL Users

Creating mysql_users is the same as above so I’ll simply include the three code segments.

Models

Models are bridges between the database layer and our code. They give us a way to extrapolate relationships and query data in a very easy, object-y way. It’s much more readable as well which is a bonus. To create a user, you create a user object and then the model creates all the relevant information in the database as well so, effectively, we’re dealing with two distinct layers in one singular method.

User Model

I’m going to start off with the “hardest” model simply because it’s the core of our application – the user. Since it’s a User model, we’ll have to use certain contracts to provide Authentication and PasswordResets.

A contract, if you’re unfamiliar, is an agreement a class has to fulfil to ensure it can perform the necessary functions required.

Below is the initial code for the User model we’ll place in *app/User.php*.

However, the key thing we’re missing here is the relationship between our User model and the MySQL models I’ll be setting up next. We’ll return to this.

MySQLDatabase and MySQLUser

Generally, models are fairly simple. Your database’s column names go in the *$fillable* array and anything sensitive goes in *$hidden*. There’s a template for every model. Have a look at the similarities between our code for *app/MySQLDatabase.php* and *app/MySQLUser.php* below:

MySQLDatabase.php

PHP

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

<?php

namespaceApp;

useIlluminate\Database\Eloquent\Model;

classMySQLDatabaseextendsModel

{

/*

* The database table used by the model.

*/

protected$table='mysql_databases';

/*

* The attributes that are mass assignable.

*/

protected$fillable=['user_id','db_name'];

/*

* The attributes excluded from the model's JSON form.

*/

protected$hidden=[];

}

MySQLUser.php

PHP

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

<?php

namespaceApp;

useIlluminate\Database\Eloquent\Model;

classMySQLUserextendsModel

{

/*

* The database table used by the model.

*/

protected$table='mysql_users';

/*

* The attributes that are mass assignable.

*/

protected$fillable=['user_id','username','password'];

/*

* The attributes excluded from the model's JSON form.

*/

protected$hidden=[];

}

Relationships

User <–> MySQLDatabase

Every user in our application will have a one-to-many relationship with MySQL databases. That is, every user may have 1 or more MySQL databases associated with them. As such, we’d like a way to use one to determine the other. We’ve already set up our foreign keys in the database, so all that’s left is representing the relationship in the Model.

As you can see from the diagram below, the user has a one-to-many relationship with the databases but the databases have a one-to-one relationship with the user. A user can have many databases but each database can only have one user.

Within the User model, we’ll add the following:

PHP

33

34

35

publicfunctiondatabases(){

return$this->hasMany('App\MySQLDatabase');

}

Now, whenever we want a user’s databases, we can use *User::find(1)->databases* and it’ll return an array of database objects associated with that user.

Conversely, to represent the other side of the relationship, we’ll add code to the MySQLDatabase model.

PHP

25

26

27

publicfunctionuser(){

return$this->hasOne('App\User');

}

Then we can get any databases’ user with *MySQLDatabase::find(1)->user*.

User <–> MySQLUser

Finally, the netsoc account user and the MySQL user have a one-to-one relationship both ways which make it much easier to understand. This is because we will be using one MySQLUser to give someone database access via management GUI (such as PHPMyAdmin). It allows us to limit their permissions and monitor access.

Introduction

Last September we installed two servers and dedicated one of them specifically to servicing our members and their applications. It allows people to SSH in and run PHP, python and JS-based apps off each user’s subdomain. We wanted people to be able to manage their own info, databases and could access automated backups. After a month of looking at web panels and various solutions, it was apparent that what we wanted didn’t exist in a pre-built package unfortunately. The software we found was ugly, lacking and, worst of all, annoyingly complex to change.

So here we are, rolling our own and re-inventing a wheel probably countless others have investigated/built. However, I hope that you can learn from what we’ve built (and maybe even extend it). This is a series of blogs detailing my process of building a Laravel WebAdmin for our Leela server.

Here’s the link to our github repo. If you spot any errors or have any suggestions, please submit it to our issue tracker – we’d really appreciate it 🙂

My Development Workflow

In this post, I want to walk you through my day-to-day development and the technologies I use during development. The main idea behind everything I talk about here is that everything should be local. If I have to board a submarine to the arctic, I should still be able to work. Admittedly, there are 3 lines of code that contradict this because I link to a CDN for MaterializeCSS, FontAwesome and jQuery. However, I have my own copies of these files locally should I ever need to swap them in for the CDN files. Regardless, develop local – it’s better for the economy.

1) Vagrant

Vagrant is a virtualisation manager for VirtualBox. It creates easy-to-modify virtual machines based on a Vagrantfile in the root project folder. Netsoc has a prebuilt box that we use for PHP development so the Vagrantfile in use here is based off that.

First thing’s first though, I’m going to be running my project off the IP
172.22.22.25.
172.16.0.0/12 is a private range of IP addresses so there should be no issue in interfering with any normal traffic we’d be using our machine for. As such, I create an entry in my hosts file (
/etc/hosts for Mac and Linux) of the form:

1

172.22.22.25netsocadmin.dev

Now I can use the address
netsocadmin.dev to access the web server. Next, I’ll want to initialize my project with a Vagrantfile so it can provision the virtual machine automatically whenever I need it. For that, I use the following:

mysql-uroot-proot-e"GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root';"

sudoservicemysqlrestart

#Create swap space for composer's operations

sudo/bin/ddif=/dev/zeroof=/var/swap.1bs=1Mcount=1024

sudo/sbin/mkswap/var/swap.1

sudo/sbin/swapon/var/swap.1

# Update laravel and create all the DB tables

cd/var/www/

sudocomposerupdate

sudophpartisanmigrate

sudophpartisandb:seed

SHELL

end

When that’s all setup, it’s time to launch the virtual machine and get the server running.

To launch a server, use
vagrant up.

To put a server to sleep, use
vagrant halt.

To shutdown the server (and free up the space on your computer), use
vagrant destroy.

2) Docker (where necessary)

Docker is a “container” manager. Containers are essentially a box that wrap up a piece of software in a complete filesystem that contains everything it needs to run: code, runtime, system tools, system libraries – anything you can install on a server.

In the case of this project, we needed to be able to test with an LDAP server. For this, the best option was running the LDAP instance from a docker container so we weren’t messing with in-production systems and could fiddle and fool all we wanted. (Just a small note: I’m omitting the fact that I used a backup of our current LDAP database and mounted them as volumes to the following docker container. This was only necessary because we had data already and not working from a blank system). To startup our docker container, I used the following:

3) Laravel

Laravel is a PHP MVC Framework I personally like to use because of its integration with the command line, ease-of-use, model relationships, queues and scheduler. There are many frameworks out there that have different pros and cons and I encourage you to find one in a language you enjoy to help black-box some of the boiler plate necessary for starting a new project (EG: user registration or page routing).

Laravel uses Composer so once that’s installed, I can create a blank project to work from.

1

laravel newblog

We’ll also need some basic scaffolding for the user registration, so I’m also going to run

1

php artisan make:auth

4) Sublime (Text Editing)

Again, Sublime Text is a personal preference of mine. Combined with Package Control, Sublime is a comfortable and efficient editor for any language. I’ve used it for python, perl, ruby, bash, PHP, HTML, CSS and for taking notes in college. It’s versatile and beautiful at its core but here are some plugins I find especially useful.

This a plugin for better code commenting/documentation. It provides a couple of neat extras to make commenting much easier but its core principle really lies within auto-generating javadoc/phpdoc-style comments for functions after typing “/**” and pressing
enter

Emmet lets you type out a hierarchy of elements in the style of CSS declarations and then auto-expands them into HTML code. For example
(li>a.anchor-class) creates an
li around an
a tag with the class
anchor-class

SFTP is SFTP. It will let you sync up files to a remote server whenever you save or you can choose to sync up/down changed files manually. There are a couple other really cute features too and I really love this plugin for working on remote servers for client work/hotfixes.

5) MaterializeCSS (CSS/HTML)

Materialize is a CSS and HTML framework that makes building templates and pages incredibly nice and intuitive. Most of the manipulation comes through applying classes to elements and stacking the HTML nicely. It implements a lot of the concepts behind Google’s Material Design concept which they implement in Android. This is, in my opinion, the best CSS/HTML framework out there at the moment. With minimal effort, the user experience is responsive both on a device and user level. It’s a joy to work with and a joy to use.

6) LESS (CSS)

Materialize is great and covers most of the styling I’d need but, for everything else, there’s LESS. Less is a pre-processor for CSS that allows me to write nested CSS declarations, fiddle with variables and do computations which it will then expand into proper, valid CSS for the browser to interpret. It makes writing and understanding CSS a lot easier with minimal effort. It also makes it incredibly easy to define app-specific colours and change them at a whim. No longer do I have to perform a find and replace only to find someone thought they should roll their own shade of blue into my pristine web app (damn you! DAMN YOU!).

7) Gulp

Gulp is a compiler for my javascript and less. I know that sounds weird but hear me out, okay? By running
gulp watch it will create a browser window and watch my files for changes. Then, whenever a change is made to a relevant file, it will process my less, put all my javascript together in one file, minify/squash it all and then reload the browser window. This way, I can lay out clean and neat files while still sending the most compact version to the browser.

Version control is, arguably, the most important part of this entire workflow. Source/Version control means that there’s a clear backup of everything I do as well as giving other developers an insight into my thought process when investigating files later. It’s amazing for collaborating with many people at the same time and still maintaining usable code. Github specifically also offers us a single place to distribute our code and allow people to submit issues/suggestions (which you should totally do)

There are many like it but this one is mine.

I hope you’ve learned something from all of the above, even if it’s just a quirky “huh, I should try that”. Workflows differ greatly for different people and different languages and frameworks (for instance, python’s Django-ers probably won’t use vagrant to server their code) but this is what works for me and it gives me 3 main benefits:

Rapid development

Security and backups

Reliability when it comes to deployment

What are some things you’d like to incorporate in your workflow?

I’ve gazed longingly at unit-tests for so very long but have yet to cross the nightclub and ask for a dance. I understand the benefits of writing and adhering to unit tests as you develop but unfortunately my current rate at which I go from development to production is just too quick and small-time for me to slow things down with proper unit-testing but, I hope to change my spots some day and take on that 4th benefit: guaranteed code quality.