Welcome Guest, Not a member yet? Register   Sign In
CI for MySQL PHP migration project?
#1

(This post was last modified: 01-14-2020, 12:25 PM by Loomes.)

Hello, I have a migration project coming up and want to ask for feedback for my concept and also would like to know if CodeIgniter is the right choice.

The client has a larger database (about 350 tables, some with around 100k datasets) in PostgreSQL 8 which is used by a stand alone application written in Pascal. This application is rather simple, it is just a frontend to the database with which you can create, modify, delete contents and print some info. The reason for migration is mainly that the application runs awefully slow.

The new application is supposed to run as a web application. The most important things for the client are accessability and speed. The client wants / needs to redesign the database (drop few tables, modify some etc.), the frontend can be migrated 1:1, only with minor cosmetical mods. There will only be 4-5 users accessing the DB in parallel, with about 50 existing users in total. The new application also needs user authentication and management which does not exist in the current version.

So my concept is using PHP 7.3 (thread safe), MySQL 8.0 (using PDO, mysqlnd) and CodeIgniter, mainly because of the speed aspect. I would start by redesigning the DB and GUI in communication with the client, which I would like to use MySQL Workbench for (any better alternatives here?). Once the DB is finalized, I imagine to have a certain function / method in CodeIgniter to generate PHP code from the DB-Schema -- is this possible (any sources for that)? The next step would be to customize and modify the automatically generated code and optimize it in terms of speed (any CI specific sources available for optimization, is this even necessary here?).

My personal goal is to have a quick and stable migration and make the DB fast accessible to the client. There is no need to optimize for some milliseconds and the users will not noteworthy increase in future. Do I even need to look for a fast framework like CI or could I use any other, because the main speedup will come from PHP 7.3 and MySQL 8? It's hard for me to estimate. I also thought of Laravel and Phalcon.

I know the concept is very shallow but this would be my first CI project so I only want to know if this is the way to go and if there are any pitfalls I need to take care of. If not, please hint me in the right direction, thanks.
Reply
#2

As far as the database tables just make sure that you have indexes for all of the search fields.

I would do this with the up coming CI 4 which is more up to date then CI 3.

Check out the CI 4 migrations.


CodeIgniter 4 RC3

CodeIgniter 4 User Guide
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply
#3

MySQL is not going to be any faster than Postgres. Why trouble yourself with converting it?
Simpler is always better
Reply
#4

(01-14-2020, 04:47 PM)InsiteFX Wrote: As far as the database tables just make sure that you have indexes for all of the search fields.
I would do this with the up coming CI 4 which is more up to date then CI 3.
Check out the CI 4 migrations.
Thanks for the hints!

(01-14-2020, 06:36 PM)donpwinston Wrote: MySQL is not going to be any faster than Postgres. Why trouble yourself with converting it?
Because it is much easier and cheaper to get MySQL shared hosting than PostgreSQL. Most providers offer MySQL DBs from 1€ per month. Moreover, the DB is gonna be redesigned, so a conversion can't be done anyways.
Reply
#5

I was hoping for a little more feedback / advice here, especially for the part where the database is supposed to be integrated in CodeIgniter. Has someone experiences with Grocery CRUD (Enteprise)? Can someone recommend other systems like Grocery CRUD or are we better off not using such tools? Thanks.
Reply
#6

(This post was last modified: 01-17-2020, 12:30 PM by jreklund.)

Hello, I have a migration project coming up and want to ask for feedback for my concept and also would like to know if CodeIgniter is the right choice.
- CodeIgniter are just like any other PHP Frameworks really. I think it's imported to look at the docs (CodeIgniter 4) and familiar yourself before actually choosing.

The client has a larger database (about 350 tables, some with around 100k datasets) in PostgreSQL 8 which is used by a stand alone application written in Pascal. This application is rather simple, it is just a frontend to the database with which you can create, modify, delete contents and print some info. The reason for migration is mainly that the application runs awefully slow.
- Are all tables accessed at all time? (do they use all 350 tables in all tasks)
- Do you make multiple joins?
- How many queries are you making on page load?
- Are they fetching the same data multiple times? Are some things static?

So my concept is using PHP 7.3 (thread safe), MySQL 8.0 (using PDO, mysqlnd) and CodeIgniter, mainly because of the speed aspect. I would start by redesigning the DB and GUI in communication with the client, which I would like to use MySQL Workbench for (any better alternatives here?). Once the DB is finalized, I imagine to have a certain function / method in CodeIgniter to generate PHP code from the DB-Schema -- is this possible (any sources for that)? The next step would be to customize and modify the automatically generated code and optimize it in terms of speed (any CI specific sources available for optimization, is this even necessary here?).
- PHP 7.4 have just come out (just so you know), there are for me only one feature that's interesting. That you can declare types for variables in classes.
- Personally I have installed MariaDB in all my servers.
- Are you going to display a DB flowchart/diagram to the client? Do they have programming/DB knowledge? If not I don't really see the need to involve them in that process.
- There are no function to generate models. You will need to write your own script after creating a base model and base everything on that.
https://codeigniter4.github.io/userguide...-databases
- If you want pure optimization you will need to write your own SQL queries (just be warned that you can't use the Entity classes etc if you do) and not depending on the Query Builder. But that goes for any framework. You should utilize are the Cache driver and cache the data and flush in on update/write (here is where you are getting the speed).
https://codeigniter4.github.io/userguide...ching.html

My personal goal is to have a quick and stable migration and make the DB fast accessible to the client. There is no need to optimize for some milliseconds and the users will not noteworthy increase in future. Do I even need to look for a fast framework like CI or could I use any other, because the main speedup will come from PHP 7.3 and MySQL 8? It's hard for me to estimate. I also thought of Laravel and Phalcon.
- CodeIgniter won't be faster or slower than any other framework out there. Those are just micro optimization. What really maters are your DB-server. You need to store pretty much everything in memory and utilize a good cache engine* for information that have been read from the DB.

Has someone experiences with Grocery CRUD (Enteprise)? Can someone recommend other systems like Grocery CRUD or are we better off not using such tools? Thanks.
- Only the open source. And that one are based on CodeIgniter 3. I would not recommend it for your project.

Make sure to have good database design with primary keys on things you are joining (and/or looking for). And don't join on anything else than integers (or binary). Also you NEED and I can't stress how much but you REALLY NEED to create your database with InnoDB no matter whatever you read on the internet are saying about MyISAM are faster for reading. With a good configuration on your DB server all your InnoDB content will be stored in memory and it will be blasting fast anyway.

The reason for this recommendation are that you can't make real time backup of MyISAM. If you are going to backup those type of engines you need to kill your application as nothing can access your database when preforming a dump.

I was hoping for a little more feedback / advice here, especially for the part where the database is supposed to be integrated in CodeIgniter.
- This is a Community forum (and rather small, but with some really dedicated people) and as this is a rather complicated matter I couldn't give you an answer during the week. Can't work with this full time, I really need to afford some kebab for dinner.

* I recommend Redis.

TLDR; Perfectly tuned MySQL/MariaDB server with InnoDB and utilizing a caching server e.g. Redis.
Reply
#7

(This post was last modified: 01-17-2020, 04:13 PM by JNapolitanoIT.)

I see you have received some great help here. The advice from jreklund is absolutely invaluable. To wit, I wanted to add something to this thread for you. When it comes down to code generation for your project, to simplify everything you may want to check out Lonnie Ezell's Vulcan CLI toolkit. It has been a life-saver for me. It will generate Controllers, Entities, Models and Migrations for you with a few simple commands. And even though it is in its development stages, it is still super stable and works very well (although the last time I used it a few weeks ago the Entity Classes it generated needed me to do some minor refactoring to match the newer Entity classes functionality).

For example you can easily create a database model with:

Code:
php spark make:model

This will allow you to configure the database table associated with this model and generate a database model inside of app\Models. If you use entities to model your tables data, you can use the following command:

Code:
php spark make:entity

From here, you can use the following command to make a controller, complete with CRUD utility functions and everything. You can easily associate your newly created/generated database models with the controllers when configuring them in the command line.

Code:
php spark make:controller

EDIT:
As for Grocery CRUD; You can read the Grocery CRUD thread here. But in a nutshell Grocery CRUD, in the days of CodeIgniter 2/3 was pretty decent at first glance. It helped beginners incorporate views and pre-built models and such into a project and get CRUD started straight away. However, the truth is that it hasn't aged well and it is not at all practical or well developed. Perhaps in the future it will be rewritten and better optimized and better organized.

You can query your database and present your data in a far simpler way with CodeIgniters Query Builder and by following a basic MVC pattern (present your data within your views, let your models handle your business logic and let your controllers act as intermediaries). This can easily be achieved when you build reusable code modules that do all of your heavy lifting. Then, pull in only the classes and services that you need from these modules, into portions of the application that require them. These modules can be located practically anywhere on your server and be used in multiple applications, just as long as they are registered in the autoloader(s). Simpler to implement, extend, test and modify than anything Grocery CRUD brings to the table.

I hope this helps. Good luck with your migration Smile
A reader lives a thousand lives before he dies. The man who never reads lives only one.
George R.R. Martin

Reply




Theme © iAndrew 2016 - Forum software by © MyBB