Thinkphp environment builds and connects to HGDB to realize CRUD
1. Environment construction and configuration
1.1. Environmental requirements
-
PHP >= 7.1.0
-
Starting from version 6.0, ThinkPHP must be installed and updated through Composer
1.2.php integrated environment
phpEnv official website: https://www.phpenv.cn/
Use phpEnv software to quickly generate the integrated environment required by TP6, including PHP7.4 language, Apache web server, Nginx web server, MySQL database, Composer management tool, phpMyAdmin web terminal, and MySQL management tool.
Open the website directory of the phpEnv integrated environment, and then install and download the ThinkPHP framework file to be generated in this folder. Initially, there is only one folder of localhost.
Open the command line terminal
Execute the composer -v command and find out that the composer version is 2.2.12
1.3. Install thinkphp6
composer create-project topthink/think tp-demo
After the download is complete, a tp-demo folder will appear in the directory of the phpEnv website, which is the root directory of the ThinkPHP framework 
1.4. Website testing
After the installation of ThinkPHP6 is over, we can use phpEnv to set up the site for testing.
Configure the settings of phpEnv: Application software => Settings => Advanced
This setting is to prepare for the subsequent configuration of the local domain name in the website management for hosts mapping.
Start the service => open the home page
Automatically jump to the browser and display PHP information
Open the phpEnv website and add a site
The newly added website domain name is a local domain name, which is useless on the external network, just for the convenience of local testing.
For the root directory of the site, select the public folder in the tp-demo folder of the root directory where the ThinkPHP framework is located.
After confirming the new site, start the phpEnv service
After starting the website service, open the browser and enter the www.thinkphp.com local domain name we set in the address bar to test the connection
1.5. Pseudo-static configuration
phpEnv’s nginx site management supports pseudo-static configuration of mainstream frameworks.
If the default web service is Nginx, then your website may need to configure URL rewriting (pseudo-static).
Common PHP frameworks, such as Laravel and Thinkphp, have built-in rules.
Rewrite method, website => site => configuration => URL rewrite => select thinkphp => save.
After setting pseudo-static rules, the entry file of the project can be hidden to make the link address look more concise and standardized.
For example: http://demo.com/index.php/index/index
After adding pseudo-static rules, it becomes: http://demo.com/index/index
1.6. Load pgsql
Configuration => php => 7.4-php.ini
File path: D:\phpEnv\php\php-7.4\php.ini
Remove the semicolon “;” in front of the line where the extension pdo_pgsql and pgsql are located
Start the service => open the homepage
php has loaded pdo_pgsql and pgsql
2. Development specification
2.1. What is PSR
The PSR specification is PHP Standard Recommendations, which is a writing specification, not a standard, and the code writing through the specification will make the code clear and concise.
2.2.PSR specification compliance
Detailed PSR specification reference URL
https://learnku.com/docs/psr/basic-coding-standard
Naming conventions for functions, methods, classes, and attributes:
Functions use lowercase and underscore nomenclature;
Methods and properties use camel case with the first letter lowercase;
Classes use camelCase with the first letter capitalized;
Constant and configuration naming convention:
Constants and environment-related variables are named in all capital letters and underscores;
Parameters use lowercase and underscore nomenclature;
Database-related naming conventions:
Data tables and fields use lowercase and underscore nomenclature;
Directory and file naming conventions:
Directory name lowercase and underscore nomenclature;
Class files use camelCase with the first letter capitalized;
The category and file name are consistent;
The class file name is defined in a namespace, and the path of the namespace is consistent with the path of the class library;
3. Directory structure
single application mode
www WEB deployment directory (or subdirectory) ├─app application directory │ ├─controller controller directory │ ├─model model directory │ ├─ ... more library directories │ │ │ ├─common.php public function file │ └─event.php event definition file │ ├─config configuration directory │ ├─app.php application configuration │ ├─cache.php cache configuration │ ├─console.php console configuration │ ├─cookie.php Cookie configuration │ ├─database.php database configuration │ ├─filesystem.php file disk configuration │ ├─lang.php multilingual configuration │ ├─log.php log configuration │ ├─middleware.php middleware configuration │ ├─route.php URL and routing configuration │ ├─session.php Session configuration │ ├─trace.php Trace configuration │ └─view.php view configuration │ ├─view view directory ├─route routing definition directory │ ├─route.php route definition file │ └─ ... │ ├─public WEB directory (external access directory) │ ├─index.php entry file │ ├─router.php quick test file │ └─.htaccess for apache rewriting │ ├─extend extended class library directory ├─runtime application runtime directory (writable, customizable) ├─vendor Composer class library directory ├─.example.env environment variable example file ├─composer.json composer definition file ├─LICENSE.txt authorization document ├─README.md README file ├─think command line entry file
4. Entry file
The default application entry file is located at public/index.php, and the default content is as follows
// [ Application entry file ] namespace think; require __DIR__ . '/../vendor/autoload.php'; // Execute the HTTP application and respond $http = (new App())->http; $response = $http->run(); $response->send(); $http->end($response);
5. Development Manual
https://www.kancloud.cn/manual/thinkphp6_0/content
6. Development tools
Commonly used editors are: VS Code, editplus, ultraedit, etc.
It is recommended to use VS Code, download link: https://code.visualstudio.com/download
7. Debug
In the development stage, we need to deal with errors and bugs through error messages or some other information. By default, the debug mode of tp6.0 is turned off, and we need to turn it on.
It is mentioned in the development manual that “the root directory comes with a .example…env file, you can directly rename it to the .env file.” This file is shown in the following figure:
At this point, just name it .env, then open this file and set the debug mode to open
8. Routing
To register a route using the Route class, you must first add a reference at the beginning of the route definition file
use think\facade\Route;
The basic routing definition method is as follows, you need to edit route/app.php for routing registration
Route::rule('routing expression', 'routing address', 'request type');
For example, to register the hello action routed to the Index controller
Route::rule('hello/:name', 'Index/hello');
we visit
http://serverName/hello/zhangsan
will automatically route to
http://serverName/Index/hello/name/zhangsan
The usage of the shortcut registration method is
Route:: shortcut method name ('routing expression', 'routing address');
Route::get('new/<id>','News/read'); // Define GET request routing rules
Route::post('new/<id>','News/update'); // Define POST request routing rules
Route::put('new/:id','News/update'); // Define PUT request routing rules
Route::delete('new/:id','News/delete'); // Define DELETE request routing rules
Route::any('new/:id','News/read'); // Routing rules supported by all requests
9. Controller
The controller file is usually placed under controller, the class name and the file name are case-consistent, and are named with camel case (the first letter is capitalized).
If you want to change the controller directory name, you need to set it in the route.php configuration file:
'controller_layer' => 'controllers',
In single application mode, the controller class is defined as follows
<?php
namespace app\controller;
class User
{<!-- -->
public function login()
{<!-- -->
return 'login';
}
}
The access URL address is (assuming no routing is defined)
http://localhost/user/login
10. HGDB performs CRUD
10.1. Database preparation
create user thinkphp password 'Qwer@1234';
create database thinkphp owner thinkphp;
create database thinkphp2 owner thinkphp;
psql -U thinkphp -d thinkphp
CREATE TABLE student(
id serial,
name character varying(20),
height character varying(12),
nickname character varying(12),
method character varying(20),
PRIMARY KEY (id)
);
insert into student(name,height,nickname,method) values ('zhangsan','170','zhangsan',null);
insert into student(name,height,nickname,method) values ('lisi','170','lisi',null);
psql -U thinkphp -d thinkphp2
CREATE TABLE student(
id serial,
name character varying(20),
height character varying(12),
PRIMARY KEY (id)
);
insert into student(name,height) values ('zhangsan','170');
insert into student(name,height) values ('lisi','170');
10.2. Connect to the database
Configure the following database parameters in database.php under the global or application configuration directory config:
return [
// The database connection configuration used by default
'default' => env('database.driver', 'hgdb'),
?…
// Database connection configuration information
'connections' => [
'hgdb' => [
// database type
'type' => env('database.type', 'pgsql'),
// server address
'hostname' => env('database.hostname', '192.168.100.160'),
// Database name
'database' => env('database.database', 'thinkphp'),
// username
'username' => env('database.username', 'thinkphp'),
// password
'password' => env('database.password', '******'),
// port
'hostport' => env('database.hostport', '5866'),
?…
],
// More database configuration information
],
];
Add the conn method to the app\controller\Index.php file
<?php
namespace app\controller;
use app\BaseController;
use think\facade\Db; //Add Db reference here
class Index extends BaseController
{<!-- -->
public function conn()
{<!-- -->
$res = Db::table('student')->select();
return json($res);
}
}
Start the service, visit the page http://www.thinkphp.com/Index/conn
An error is reported, the authentication method 13 is not supported, and libpq of php needs to be replaced
Replace the following file with D:\phpEnv\php\php-7.4
Restart the service, visit again, and report that the table_msg function does not exist. The reason is that thinkphp needs to customize the table_msg function when connecting to pgsql.
The database needs to execute the script provided in tp-demo\vendor\topthink\think-orm\src\db\connector\pgsql.sql, but the definition of the table_msg function is not compatible with version 4.5 of Higho, The following adjustments are required.
CREATE OR REPLACE FUNCTION pgsql_type(a_type varchar) RETURNS varchar AS
$BODY$
DECLARE
v_type varchar;
BEGIN
IF a_type='int8' THEN
v_type:='bigint';
ELSIF a_type='int4' THEN
v_type:='integer';
ELSIF a_type='int2' THEN
v_type:='smallint';
ELSIF a_type='bpchar' THEN
v_type:='char';
ELSE
v_type:=a_type;
END IF;
RETURN v_type;
END;
$BODY$
LANGUAGE PLPGSQL;
CREATE TYPE public. tablestruct AS (
fields_key_name varchar(100),
fields_name VARCHAR(200),
fields_type VARCHAR(20),
fields_length BIGINT,
fields_not_null VARCHAR(10),
fields_default VARCHAR(500),
fields_comment VARCHAR(1000)
);
CREATE OR REPLACE FUNCTION public.table_msg (a_schema_name varchar, a_table_name varchar) RETURNS SETOF public.tablestruct AS
$body$
DECLARE
v_ret tablestruct;
v_oid oid;
v_sql varchar;
v_rec RECORD;
v_key varchar;
BEGIN
SELECT
pg_class.oid INTO v_oid
FROM
pg_class
INNER JOIN pg_namespace ON (pg_class.relnamespace = pg_namespace.oid AND lower(pg_namespace.nspname) = a_schema_name)
WHERE
pg_class.relname=a_table_name;
IF NOT FOUND THEN
RETURN;
END IF;
v_sql='
SELECT
pg_attribute.attname AS fields_name,
pg_attribute.attnum AS fields_index,
pgsql_type(pg_type. typname::varchar) AS fields_type,
pg_attribute.atttypmod-4 as fields_length,
CASE WHEN pg_attribute.attnotnull THEN ''not null''
ELSE ''''
END AS fields_not_null,
pg_get_expr(pg_attrdef.adbin, pg_attrdef.adrelid) AS fields_default,
pg_description.description AS fields_comment
FROM
pg_attribute
INNER JOIN pg_class ON pg_attribute.attrelid = pg_class.oid
INNER JOIN pg_type ON pg_attribute.atttypid = pg_type.oid
LEFT OUTER JOIN pg_attrdef ON pg_attrdef.adrelid = pg_class.oid AND pg_attrdef.adnum = pg_attribute.attnum
LEFT OUTER JOIN pg_description ON pg_description.objoid = pg_class.oid AND pg_description.objsubid = pg_attribute.attnum
WHERE
pg_attribute.attnum > 0
AND attisdropped <> ''t''
AND pg_class.oid = ' || v_oid || '
ORDER BY pg_attribute.attnum';
FOR v_rec IN EXECUTE v_sql LOOP
v_ret.fields_name=v_rec.fields_name;
v_ret.fields_type=v_rec.fields_type;
IF v_rec.fields_length > 0 THEN
v_ret.fields_length:=v_rec.fields_length;
ELSE
v_ret.fields_length:=NULL;
END IF;
v_ret.fields_not_null=v_rec.fields_not_null;
v_ret.fields_default=v_rec.fields_default;
v_ret.fields_comment=v_rec.fields_comment;
SELECT constraint_name INTO v_key FROM information_schema.key_column_usage WHERE table_schema=a_schema_name AND table_name=a_table_name AND column_name=v_rec.fields_name;
IF FOUND THEN
v_ret.fields_key_name=v_key;
ELSE
v_ret.fields_key_name='';
END IF;
RETURN NEXT v_ret;
END LOOP;
RETURN;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
COMMENT ON FUNCTION public.table_msg(a_schema_name varchar, a_table_name varchar) IS 'Get table information';
--- Overload a function
CREATE OR REPLACE FUNCTION public.table_msg (a_table_name varchar) RETURNS SETOF public.tablestruct AS
$body$
DECLARE
v_ret tablestruct;
BEGIN
FOR v_ret IN SELECT * FROM table_msg('public',a_table_name) LOOP
RETURN NEXT v_ret;
END LOOP;
RETURN;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
COMMENT ON FUNCTION public.table_msg(a_table_name varchar) IS 'get table information';
The page visits http://www.thinkphp.com/Index/conn again
10.3. Multiple data sources
return [
// The database connection configuration used by default
'default' => env('database.driver', 'hgdb'),
?…
// Database connection configuration information
'connections' => [
'hgdb' => [
?…
],
'hgdb2' => [
// database type
'type' => env('database.type', 'pgsql'),
// server address
'hostname' => env('database.hostname', '192.168.100.160'),
// Database name
'database' => env('database.database', 'thinkphp2'),
// username
'username' => env('database.username', 'thinkphp'),
// password
'password' => env('database.password', '******'),
// port
'hostport' => env('database.hostport', '5866'),
],
// More database configuration information
],
];
Add the hgdb2 method to the app\controller\Index.php file
<?php
namespace app\controller;
use app\BaseController;
use think\facade\Db;
class Index extends BaseController
{<!-- -->
public function conn()
{<!-- -->
$res = Db::table('student')
->select();
return json($res);
}
public function hgdb2()
{<!-- -->
$res = Db::connect('hgdb2')
->table('student')
->select();
return json($res);
}
}
Visit http://www.thinkphp.com/Index/hgdb2
10.4. Model
The framework formulates a model class for us to use. Through the model class, the repetitive code can be reduced, and the database can be operated more concisely. When using the model to operate the database table, this model represents the database table that needs to be operated. .
Create a model folder in the same directory as the controller, and create a model file corresponding to the database in it.
Add the stuModel method in the app\controller\Index.php file
<?php
namespace app\controller;
use app\BaseController;
use think\facade\Db;
use app\model\Student;
class Index extends BaseController
{<!-- -->
public function conn()
{<!-- -->
$res = Db::table('student')
->select();
return json($res);
}
public function hgdb2()
{<!-- -->
$res = Db::connect('hgdb2')
->table('student')
->select();
return json($res);
}
public function stuModel()
{<!-- -->
$stu=Student::select();
return json($stu);
}
}
Visit http://www.thinkphp.com/Index/stuModel
10.5. Query Builder
public function selectById()
{<!-- -->
$stu=Db::table('student')
->where('id', 1)
->find();
return json($stu);
}
public function insert()
{<!-- -->
$data = ['name' => 'wangwu', 'height' => '180','nickname' => 'wangwu','method' => 'insert'];
$res=Db::table('student')
->save($data);
dump($res);
}
public function update()
{<!-- -->
$res= Db::name('student')
->where('id', 1)
->update(['method' => 'update']);
dump($res);
}
public function deleteById()
{<!-- -->
$res= Db::table('student')
->delete(1);
dump($res);
}
public function deleteByWhere()
{<!-- -->
$res= Db::table('student')
->where('name','wangwu')
->delete();
dump($res);
}
10.6. Native query
public function selectBySql()
{<!-- -->
$res= Db::query("select * from student where id=:id", ['id' => 2], true);
return json($res);
}
public function updateBySql()
{<!-- -->
$res= Db::execute("update student set name='thinkphp' where id=2");
dump($res);
}