Download the PHP package zachleigh/petrol without Composer

Petrol

Database Fuel

A framework for parsing files and filling databases.

Contents

Quick Example

• Standard Version
• Laravel Version

Installation

Examples

Command Library

Database Notes

Quick Example

Standard Example

Let's parse a file and fill a Mysql table with one line of code.

Our file, simple.txt.

We've got names, emails, and address, seperated by spaces and slashes.

Our database table, simple_table.

A column for each item in simple.txt plus an auto-incrementing id column.

Install Petrol.

Navigate to vendor/zachleigh/Petrol in the console and make a .env file.

Move simple.txt into Petrol/src/Files/.

Make a new filler file.

Sweet. We now have a FillSimpleTable file in Petrol/src/Fillers/.

Open it up.

Enter the database columns in the $columns array. (We don't need the id column.)

Enter your line parsing code in the parse method.

Fill your table.

Done. Reward yourself with a drink of your choice. (A more detailed version of this tutorial can be found here.)

Laravel Example

Let's parse a file and fill a Mysql table with one line of code in a Laravel application.

Our file, simple.txt.

We've got names, emails, and address, seperated by spaces and slashes.

Our database table, simple_table.

A column for each item in simple.txt plus an auto-incrementing id column.

Install Petrol in your application.

Register the Petrol service provider in app/Providers/AppServiceProvider.php.

Run php artisan and make sure the Petrol commands are listed.

Make the Petrol directory in the app/ directory.

You now have a Petrol directory in /app with Files and Fillers directories in it.

Move simple.txt into the Files directory.

Make a new filler file.

Sweet. We now have a FillSimpleTable file in app/Petrol/Fillers/.

Open it up.

Enter the database columns in the $columns array. (We don't need the id column.)

Enter your line parsing code in the parse method.

Fill your table.

Done. Reward yourself with a drink of your choice. (A more detailed version of this tutorial can be found here.)

Installation

Requirements

• PHP 7.1 or higher

  Linux users can find PHP releases in their distribution repositories. For other operating systems, visit the php installation guide for instructions.

• composer

  Check the composer documentation for installation instructions.

Install

If requirements are met, you can install the package in two ways.

Download

Download here, cd into the Petrol directory and run

Finished. How easy was that?

Through composer

If you install through composer, the program will be in vendor/zachleigh/petrol

A Little Setup

Executables

Once you have Petrol installed, you may want to make the 'petrol' file executable so it can be run without having to type php before the command. Not executable:

Executable:

Environment Setup

You will need to create a .env file and fill in the appropriate information. You can do this manually by copying the .env.example file to .env or by using the built-in command line tool.

Config File

One more step, then you're ready to go. Open up config.php and make sure that 'database' is set to the database of your choice. Currently, only mysql is supported out of the box.

Examples

• Simple Table
• XML Table
• XML Table JSON Array

Simple Table

Let's fill up a simple database.

For this brief tutorial, we will be filling the simple_table Mysql database table with info from the simple.txt file. simple.txt

We have slash seperated names, email addresses, and physical addresses that need to get into our database.

Step 1

Before we do anything else, we need to put our data file, simple.txt, in Petrol/src/Files/.

Step 2

Once our file is where it needs to be, we can make a new Filler with the 'new' command. The first argument of the 'new' command is the name of the table, simple_table in our case. The option --file is the name of the file to use, in this case simple.txt.

The Filler will be in Petrol/src/Fillers/ and will be called FillCamelCasedTableName, in our case FillSimpleTable. It will look something like this.

Let's take a look at it.

• Our file and table names are saved on the object as $file and $table. You shouldn't have to do anything else with these.
• Our filler uses the traits Parser and User. Parser contains general methods to help you parse files and User is where you can put your own functions.
• Our filler is already attached to our database through the $connection object on the class.
• You can choose to auto fill your database (default), fill it manually, or dump it to the console for debugging.
• There is a $columns array where we must enter our database column names.
• There is a $variables array where we can enter variables to be set before looping through the file lines. These will be set on the object and can be accessed with $this->variable.
• There is a parse method where we will be given the file's lines one at a time.

Step 3

We now need to set our database columns in the protected $columns array. Our sample file, simple.txt, contains names, emails, and address so our database should have name, email, and address columns. We will put these in the $columns array.

Note that the column names need to match the columns in the mysql table exactly, excluding an auto-incrementing id column. In this case, our mysql table looks like this:

Step 4

Ok, let's write our parsing logic. Petrol will iterate over $file line-by-line and for each line, it will run the protected method parse(). In parse, we have access to $line, one individual line from the file. We can write our line parsing logic there. The parse() method must return an array representing one row of the database. In other words, we must return a $key => $value pair array where each $key equals a column name (defined in $columns) and its $value equals the value we wish to assign to that column. In our example, we want to seperate the name, email, and address fields in the file and then assign them to name, email, and address keys in the returned array.

Let's look at this a little deeper. First, we pass $line and '/' (the symbol we want to break the line with) to cleanExplode. cleanExplode is a method in the Parser trait. It is similar to the standard php explode function, except that it also trims white space from each item in the returned array. We then use array_combine to match the column keys with the values returned from cleanExplode. The resulting data structure looks like this.

Notice that the keys in each array equal the $column variable items. This is a must. You've been warned.

Step 5

We're almost done. After saving the Filler file, we simply run the fill command. The command requires one argument, the database table name. In our case this is simple_table.

And we are done. You just parsed a file and filled a Mysql table with one line of code.

If you're getting errors, you can use the --errors flag to dump any Mysql PDO errors you may be getting. You can also set $fill to 'dump' to dump to the console instead of filling your database.

XML Table

In this short tutorial, we'll be filling a table using an XML file. We will be using the books.xml file and filling the books table in our database. books.xml

We have 12 books, each with an id stored as an attribute on the book tag, an author, a title, a genre, a price, a publish date, and a description. In this tutorial, we will be filling a table that has one column for each field. Our database will look like this:

Step 1

Move books.xml into Petrol/src/Files.

Step 2

Make an new Filler with the new command.

Step 3

In the newly created FillBooks file, enter the database columns in the $columns array.

Step 4

Next, we need to write our parsing logic. Because this is an XML file, we can use the XmlParser helper trait. At the top of the FillBooks file, add XmlParser to the list of traits.

We will also need to fill our table manually, so set $fill to 'manual'.

Now we can use all the methods available in XmlParser to help us.

Let's have a look at this. First, when using the the XmlParser helpers, we need to set the root XMNL tag with setRootTag(). This identifies when the XML to be read actually starts and makes parsing easier. Because we want to get an attribute (book_id) as well, we are going to have to do a two step parse. First, we will convert each book tree into an array with xmlToArrays(). This method requires $line and the root tag of the tree you want to convert to an array, in our case 'book.' This will return null unless the closing book tag has been reached, in which case it will return an array. Because the xmlToArrays method returns null unless the closing tag has been reached, we need to check for null before proceeding.

Once we get our array, we can get the 'book_id' attribute off the array with the getAttributeFromArray method. (Note that this method is still in development and needs to be tested more.) Once we have the attribute, we can convert our array to a data array with arrayToData, which requires the array we generated before as well as $columns. This method will return a $key => $value pair array where $key is a column name and $value is a value from the XML file.

We then simply place 'book_id' on the returned data array and manually insert the row with $this->connection->insertRow.

If we didnt have to get the 'book_id' attribute, this would have been even easier. Instead of converting the XML to an array and then converting the array to returnable data, we could have simply converted the XMl directly to a data array.

Step 5

All we have to do now is fill our table.

Finished.

XML Table JSON Array

In some situations, it is better to enter the XML data into a database as a JSON array. This isn't always desired because it makes searching the database more difficult and requires decoding of the returned values, but for some situations, this is a good way to go about handling XML data.

This very brief tutorial is similar to the previous tutorial, except our database table and our parsing function will be different. Our database structure:

So in our Filler file, our columns array will look like this:

Our parsing function will look like this:

Rather than convert the array returned from xmlToArrays into an array that can be fed to our Mysql statement, we will instead json encode it. When you get the database value on the other end, you can simply use json_decode to get back the original structure of the array. Its not for every situation, but in some cases its the best solution.

Command Library

• fill
• make
• new

fill

Fill a database table using the Filler made with the new command. table_name needs to match name of database table used when creating the Filler.

Options

errors

Dump Mysql PDO errors

quiet

Quiet all user input prompts.

make

Make something

Things you can make

env

Make a .env file with database credentials

new

Make a new Filler to parse a file and fill a database table. table_name needs to match the database table name.

Options

file

Set the file to be used by the Filler.

path

Set the new Filler save location. (For testing purposes only.)

Database Notes

Requirements

Your database must meet the following requirements:

• Tables must have an auto-incrementing 'id' column
• Column names must exactly match the expected field names.

Other database types

Currently, only Mysql is supported. The database connection uses php PDO drivers that can be changed out fairly easily. Currently, PDO supports 12 database types. Check the driver list for more information. If you wish to make an adapter for one of these database types, adapter name rules must be followed.

• Cuprid: CupridDatabase
• FreeTDS / Microsoft SQL Server / Sybase: DblibDatabase
• Firebird: FirebirdDatabase
• IBM DB2: IbmDatabase
• IBM Informix Dynamic Server: InformixDatabase
• MySQL: MysqlDatabase
• Oracle Call Interface: OciDatabase
• ODBC v3 (IBM DB2, unixODBC and win32 ODBC): OdbcDatabase
• PostgreSQL: PgsqlDatabase
• SQLite 3 and SQLite 2: SqliteDatabase
• Microsoft SQL Server / SQL Azure: SqlsrvDatabase
• 4d: FourD (A class naming rule exception exists for this, but it is untested)

The adapter class should be in its own file in src/Core/Database/Databases/ and must implement DatabaseInterface. If you make a new adapter, please let me know so I can include it in the main program. If you dont know how to write a new adapter, let me know and I'll do it if time permits.

Besides making an adapter, you will also have to make a new array for the database type in 'connections' in config.php.