What is a Relational Database Server?
- Stores Data Efficiently:
- A database organizes and stores data in a manner that makes it easily accessible and manageable. Indexes data so that results are returned fast.
- Multiple Related Tables:
- It can consist of several related tables, allowing for the organization of different types of information in a structured way.
- Scales with Needs:
- Databases are designed to scale, meaning they can handle an increasing amount of work or data as the need grows.
- Search, Add, Update via SQL:
- Utilizes Structured Query Language (SQL) for searching, adding, and updating data, providing a powerful tool for managing the database.
- Structured
- building something that has to scale
What Database Servers are Available?
- SQLite: A lightweight database filesystem database. Ideal for development, small applications, and situations where simplicity and minimal setup are advantageous.
- MySQL: A popular open-source relational database management system known for its reliability and ease of use. It's widely used for web applications and supports a variety of programming languages.
- PostgreSQL: An advanced open-source relational database system, offering strong compliance to SQL standards, and known for its robustness, scalability, and support for advanced data types and functionalities.
- MS SQL Server: A comprehensive, enterprise-level database management system developed by Microsoft. Known for its high performance, robust security features, data integration, and analysis tools.
- Oracle Database: A comprehensive, enterprise-grade database solution that provides robust support for large-scale databases, complex applications, and data warehousing needs. Known for its performance, security, and scalability.
- Local and UQCloud Options: SQLite can be used locally, providing an easy-to- manage database environment without the need for a server. Both MySQL and PostgreSQL are available on UQCloud.
Why are databases Essential for Web Development?
- Data Management:
- Databases provide a structured and efficient way to store, manage, and retrieve data, which is crucial for dynamic websites and applications.
- User Interaction:
- They enable personalized user experiences by storing user preferences, orders, and account information, enhancing engagement and satisfaction.
- Scalability:
- Databases can handle increasing amounts of data and user requests, allowing websites to grow and accommodate more traffic seamlessly.
- Data Integrity:
- Ensure consistency and accuracy of the data through constraints, transactions, and relational database design, minimizing errors and data duplication.
- Security:
- Databases offer robust security features to protect sensitive information, such as user data and financial transactions, from unauthorized access and breaches.
Structured Query Language
- Data Definition Language (DDL) - Defines and modifies a schema e.g. CREATE / DROP /
ALTER table; does not manipulate data - Data Manipulation Language (DML) - Language used to retrieve (SELECT), add (INSERT), modify (UPDATE) and DELETE data
- Data Control Language (DCL) statements. Used for providing (GRANT) / withdrawing (REVOKE) access privileges
- Transaction Control Language (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions. Example: COMMIT, ROLLBACK, etc.
Deleting Data
DELETE FROM customers
WHERE name=‘Mary’
Updating Records in a Table
UPDATE StudentRecord
SET Name=‘Jack’
WHERE studentID = 1234
Codeigniter - with Data Access
- Using a Web Framework should make your life as a developer easier
- A Framework will include a number of abstractions to help you write code to interact with a database
- CodeIgniter 4 has numerous features:
- Central config file (.env) to store database connections
- Migrations to help you create tables and have database version control
- Models to simplify reading, inserting, updating and deleting content from a database table (just use simple objects instead of using SQL)
- The Spark command line to help you create Migration files and new model files.
- simplify development
- deal with extractions
What is a Migration?
- Versioned Database Schemas:
- Migrations in CodeIgniter 4 provide a method for version controlling your database schema. This allows for easy management of database changes over time.
- Automated Database Changes:
- They automate the process of applying database changes, such as creating or altering tables, adding indexes, and more, ensuring consistency across different environments.
- Simplifies Development and Deployment Workflow:
- Migrations simplify the development workflow by allowing team members to share and apply database changes in a controlled and systematic manner. Easy to deploy the schema of a database
- Programmatic Schema Manipulation:
- Migrations are PHP classes that contain methods to programmatically execute database operations, giving developers a powerful tool to manipulate the schema.
Use the features in Codeigniter
Web development frameworks come with migrations
create code to describe the changes to the schema
files are versioned and can be stored in file versioning applications (git, etc.)
Codeigniter - Spark
- Running database migrations and seeders to manage database schema and initial data setup
- Starting a development web server
- Generating boilerplate code (new files) for new controllers, models, and other components.
Create a Migration
-
Use the spark make:migration command
-
php spark make:migration create_customers_table -
php spark make:migration create_orders_table -
This will create a new file in the app/Databases/Migrations folder
- The file will have a data time stamp and be versioned
- You need to add the fields that need to be created in the table
- The up() method defines the schema for creating the tables.
- The down() method defines the schema for dropping the tables.
How does this help developer workflow?
- A few scenarios
- You develop locally and then deploy to production:
- You can just run the migration when you deploy and update your application
- You have multiple developers
- When a database change is made, the code for the change is committed to the code repository
- Other developers can then run the migration to update their local databases
- You release open source software
- The same migration code and create SQL create and modify statements for multiple databases
- Each developer that downloads your software can choose their own database server (e.g. Postgres) and not worry about conflicts
- scripting specific way to manage table changes