Whenever you develop a Laravel-based application that makes it into production you’ll probably provide some kind of API. Either for internal usage or for the customer to consume. However you most likely want to install some kind of rate limiting mechanism to make sure nobody is overusing your API and thus risking to take down your hosting infrastructure.

A well-known mechanism to prevent APIs from being overloaded with requests (kind of DoS) is a rate limiting. You’ll define a maximum number of requests in a given amount of time and when a client hits that maximum your web server will answer with HTTP 429 Too Many Requests. This indicates the client that there is some kind of cooldown time it needs to wait until further requests will be processed.

Rate Limiting Middleware

Laravel provides a convenient throttle middleware that provides that kind of rate limiting. It even supports different limits based upon a model so you can limit the amount of requests based upon a dynamic parameter.

1
2
3
4
5
6
7
8
9
10
11
12

<?php

// Allow up to 60 requests in 1 minute for that route (= 1 req/s)
Route::get('api/v1/user', 'Api\UserController@index')->middleware('throttle:60,1');

// Allow up to 60 requests in 60 minutes for that route (= 1 req/m)
Route::post('api/v1/user', 'Api\UserController@store')->middleware('throttle:60,60');

// Rate Limiting for a whole group of routes
Route::group(['middleware' => 'throttle:60,1'], function () {
    // [...]
});

Figure 1 — Rate Limiting using Laravel’s “throttle” middleware

This is a pretty cool default functionality. However it comes with a downside you will experience in production: The request still hits your Laravel application before being denied due to an exceeded limit which means the request still generates load on your web-server.

Of course the impact isn’t as high as it normally would be but in fact the web-server needs to boot up the Laravel framework and the request will pass any middleware that is processed before the throttle middleware itself. This alone could cause some database queries to be executed before the check for the exceeded rate limit even happens.

Finally the request causes impact on your cache server (e.g. Redis). Laravel’s rate limiting middleware stores the client’s IP address alongside with the amount of requests in a given time period and performs a check on every request.

Load Balancer Rate Limiting

A technique I often use when bringing Laravel-based applications into production is a combination of Laravel Middleware Rate Limiting alongside with another rate limiting at the load balancer.

A load balancer is a piece of software that usually sits in front of your web-server stack. The traffic goes from the left to the right until it hits your Laravel application.

Figure 2 — Position of the Load Balancer in the Web-Server Stack

It should be desirable to kill unwanted requests as early as possible in that chain of processing to reduce load in the backend. One of the most used load balancers is HAProxy. Although the website looks like it’s software from the 90s, it’s battle-tested and under very active development. At the time of writing this article HAProxy has reached stable version 2.1 and is “cloud ready” for usage with modern technologies like Kubernetes.

HAProxy is mainly a Layer 7 HTTP Load Balancer (however it also supports some more protocols, which is pretty awesome). That means that it can handle SSL offloading, can look into the user’s request and decide a few key things upon the request details:

• First of all it can decide which backend to use for the incoming request which means you could split up your application into two different Laravel applications: One for the frontend and another one for the backend.
• It can restrict some URIs to a given IP range or require basic authentication for it. That way I’m able to protect the Laravel Horizon Dashboard in production — it’s only accessible from a specific VPN IP range for additional security.
• It can split your user’s request between several backend web-servers which means you are able to scale your deployment. You no longer need to get bigger and bigger machines, you can just add some. And you can remove them if no longer needed (e.g. after a huge sale event, when running a web shop).

However this article will focus on the configuration of rate limiting within HAProxy for the sake of performance and stability of your web-server deployment.

Configuration of HAProxy

Before diving right into the configuration of the rate limiting itself it’s important to configure some basic limitations of HAProxy. You may want to configure a maximum amount of parallel connections that load balancer is allowed to handle at one time.

1
2
3
4
5

backend laravel
    timeout queue 10s
    server app-1 10.10.100.101:8080 check maxconn 30
    server app-2 10.10.100.102:8080 check maxconn 30
    server app-3 10.10.100.103:8080 check maxconn 30

Figure 3 — Max Connection Settings for HAProxy

When there are more than 90 (3 times 30 connections) concurrent connections HAProxy will put those requests in a queue and will forward them once the active connection count drops below 30 again. Usually this happens within milliseconds, so your users will barely notice under normal circumstances. The queue will be flushed after 10 seconds and the client receives an HTTP 503 Service Unavailable which means HAProxy couldn’t elect a backend server to serve the request to the user.

One would ask why you should limit those connections to the backend server. The idea behind this procedure is that it’s better to serve some HTTP errors to some clients than bury your web backend under such a heavy workload your application becomes inoperable. It’s a kind of a protection for your infrastructure.

HAProxy Rate Limiting

To integrate rate limiting functionalities into HAProxy you need to configure a so called stick table within your frontend configuration block. That table works kind of like the Laravel throttle middleware under the hood. It stores a definable value as dictionary key and some counters that belong to that key.

To recognize a user we will use its requesting IP address as dictionary key. And the value we are interested in is the amount of HTTP connection the client establishes.

1
2
3
4
5
6

frontend app
    bind :80
    stick-table type ipv6 size 100k expire 30s store http_req_rate(10s)
    http-request track-sc0 src
    http-request deny deny_status 429 if { sc_http_req_rate(0) gt 20 }
    default_backend laravel

Figure 4 — Establish Rate Limiting within HAProxy

The first two lines of that configuration example are plain and basic frontend definitions in HAProxy. You create a new frontend (something that receives a user’s request) and bind it to port 80 which is the default HTTP port.

In Line 3 you create a stick-table that stores IP addresses as “dictionary key”, has a maximum size of 100k, thats values expire after 30 seconds and that stores the request rate of the latest 10 seconds for each client. The reason why we are using ipv6 as table type is that the default ip type is not able to store IPv6 addresses due to a limitation of the key length. Although the type suggests that the table can only store IPv6 addresses this is not the case; it can easily store both, so don’t worry.

Afterwards we initialize a so called sticky counter (sc) to count the incoming HTTP requests of each user and deny the request with a HTTP 429 Too Many Requests if the HTTP request rate exceeds 20 requests for the given amount of time we defined in Line 3 (in our case this are seconds).

HAProxy will automatically take care of the table and purge old entries. So after some time the clients will be able to connect to the server again.

Downsides

However there are some downsides you should consider when performing rate limiting with HAProxy. The Laravel Throttle Middleware has some neat features for usability.

Figure 5 — X-RateLimit headers of the Laravel Throttle Middleware

As you can see Laravel will automatically add some additional headers to its response when a request got rate-limited. You can see the hard limit of requests (5 in the example) and the remaining amount of requests you can perform before getting a HTTP 429 from the server. Furthermore it provides a counter and a unix timestamp that shows you when you are allowed to perform new requests after a rate limit hit.

You won’t get those headers with the provided HAProxy configuration above. Therefore I personally decided to use the load balancer rate limiting technique alongside with Laravel’s rate limiting middleware. You easily can configure much higher limits within your load balancer than at your Laravel application and you still get some kind of protection against flooding your application.

For example you could set up the Laravel throttle middleware to prevent more requests than 60 per minute so the user gets one request per second. Then you could configure HAProxy to limit the requests when there are more than 120 requests per minute. So if your user is using your API correctly and honors the rate limiting headers he won’t ever hit your load balancer rate limit. But if the user just ignores the headers and continues flooding your application with requests although they get denied by your Laravel middleware he’ll run into your load balancer rate limiting at some point.

By doing this you can efficiently prevent your infrastructure from being flooded with requests.

Conclusion

• Laravel provides a convenient default middleware to throttle requests using the cache backend you set up for your application.
• In production it may be a problem that your user’s requests still hit your web-server backend although the user is already rate-limited.
• Rate limiting via Laravel Middleware costs more than rate limiting at the edge of your web stack (at the load balancer).
• HAProxy provides a convenient way to achieve rate limiting using stick-tables and some easy deny rules at the frontend.
• It’s better to show some users a HTTP error that burying your infrastructure under heavy load (no matters whether it’s a DoS-attack or just a high amount of legit traffic).

In the future I’ll publish more articles about production-specific experiences with Laravel I made in the past. I hope you can get some takeaways for your own projects.

Hosting is fun and there are many ways to fine-tune your application very individually. One-Click-Hosting solutions may suite for many projects, but when it comes to performance and security you may prefer a tailored solution.

来源：

https://medium.com/swlh/laravel-rate-limiting-in-production-926c4d581886

SQL databases tend to be rigid.

If you have worked with them, you would agree that database design though it seems easier, is a lot trickier in practice. SQL databases believe in structure, that is why it’s called structured query language.

On the other side of the horizon, we have the NoSQL databases, also called schema-less databases that encourage flexibility. In schema-less databases, there is no imposed structural restriction, only data to be saved.

Table of Contents

• Why Use JSON
• The Schema
• The CRUD Operations
• A Primer for Web Applications
• Creating the Migrations
• Creating the Models
• Resource Operations
• Curtains

Though every tool has it’s use case, sometimes things call for a hybrid approach.

What if you could structure some parts of your database and leave others to be flexible?

MySQL version 5.7.8 introduces a JSON data type that allows you to accomplish that.

In this tutorial, you are going to learn.

1. How to design your database tables using JSON fields.
2. The various JSON based functions available in MYSQL to create, read, update, and delete rows.
3. How to work with JSON fields using the Eloquent ORM in Laravel.

Why Use JSON

At this moment, you are probably asking yourself why would you want to use JSON when MySQL has been catering to a wide variety of database needs even before it introduced a JSON data type.

The answer lies in the use-cases where you would probably use a make-shift approach.

Let me explain with an example.

Suppose you are building a web application where you have to save a user’s configuration/preferences in the database.

Generally, you can create a separate database table with the id, user_id, key, and value fields or save it as a formatted string that you can parse at runtime.

However, this works well for a small number of users. If you have about a thousand users and five configuration keys, you are looking at a table with five thousand records that addresses a very small feature of your application.

Or if you are taking the formatted string route, extraneous code that only compounds your server load.

Using a JSON data type field to save a user’s configuration in such a scenario can spare you a database table’s space and bring down the number of records, which were being saved separately, to be the same as the number of users.

And you get the added benefit of not having to write any JSON parsing code, the ORM or the language runtime takes care of it.

The Schema

Before we dive into using all the cool JSON stuff in MySQL, we are going to need a sample database to play with.

So, let’s get our database schema out of the way first.

We are going to consider the use case of an online store that houses multiple brands and a variety of electronics.

Since different electronics have different attributes(compare a Macbook with a Vacuumn Cleaner) that buyers are interested in, typically the Entity–attribute–value model (EAV) pattern is used.

However, since we now have the option to use a JSON data type, we are going to drop EAV.

For a start, our database will be named e_store and has three tables only named, brands, categories, and products respectively.

Our brands and categories tables will be pretty similar, each having an id and a name field.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

CREATE DATABASE IF NOT EXISTS `e_store`
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;

SET default_storage_engine = INNODB;

CREATE TABLE `e_store`.`brands`(
    `id` INT UNSIGNED NOT NULL auto_increment ,
    `name` VARCHAR(250) NOT NULL ,
    PRIMARY KEY(`id`)
);

CREATE TABLE `e_store`.`categories`(
    `id` INT UNSIGNED NOT NULL auto_increment ,
    `name` VARCHAR(250) NOT NULL ,
    PRIMARY KEY(`id`)
);

The objective of these two tables will be to house the product categories and the brands that provide these products.

While we are at it, let us go ahead and seed some data into these tables to use later.

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

/* Brands */
INSERT INTO `e_store`.`brands`(`name`)
VALUES
    ('Samsung');

INSERT INTO `e_store`.`brands`(`name`)
VALUES
    ('Nokia');

INSERT INTO `e_store`.`brands`(`name`)
VALUES
    ('Canon');

/* Types of electronic device */
INSERT INTO `e_store`.`categories`(`name`)
VALUES
    ('Television');

INSERT INTO `e_store`.`categories`(`name`)
VALUES
    ('Mobilephone');

INSERT INTO `e_store`.`categories`(`name`)
VALUES
    ('Camera');

Next, is the business area of this tutorial.

We are going to create a products table with the id, name, brand_id, category_id, and attributes fields.

1
2
3
4
5
6
7
8
9
10
11
12

CREATE TABLE `e_store`.`products`(
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
    `name` VARCHAR(250) NOT NULL ,
    `brand_id` INT UNSIGNED NOT NULL ,
    `category_id` INT UNSIGNED NOT NULL ,
    `attributes` JSON NOT NULL ,
    PRIMARY KEY(`id`) ,
    INDEX `CATEGORY_ID`(`category_id` ASC) ,
    INDEX `BRAND_ID`(`brand_id` ASC) ,
    CONSTRAINT `brand_id` FOREIGN KEY(`brand_id`) REFERENCES `e_store`.`brands`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE ,
    CONSTRAINT `category_id` FOREIGN KEY(`category_id`) REFERENCES `e_store`.`categories`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE
);

Our table definition specifies foreign key constraints for the brand_id and category_id fields, specifying that they reference the brands and categories table respectively. We have also specified that the referenced rows should not be allowed to delete and if updated, the changes should reflect in the references as well.

The attributes field’s column type has been declared to be JSON which is the native data type now available in MySQL. This allows us to use the various JSON related constructs in MySQL on our attributes field.

Here is an entity relationship diagram of our created database.

Our database design is not the best in terms of efficiency and accuracy. There is no price column in the products table and we could do with putting a product into multiple categories. However, the purpose of this tutorial is not to teach database design but rather how to model objects of different nature in a single table using MySQL’s JSON features.

The CRUD Operations

Let us look at how to create, read, update, and delete data in a JSON field.

Create

Creating a record in the database with a JSON field is pretty simple.

All you need to do is add valid JSON as the field value in your insert statement.

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

/* Let's sell some televisions */
INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Prime' ,
    '1' ,
    '1' ,
    '{"screen": "50 inch", "resolution": "2048 x 1152 pixels", "ports": {"hdmi": 1, "usb": 3}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Octoview' ,
    '1' ,
    '1' ,
    '{"screen": "40 inch", "resolution": "1920 x 1080 pixels", "ports": {"hdmi": 1, "usb": 2}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Dreamer' ,
    '1' ,
    '1' ,
    '{"screen": "30 inch", "resolution": "1600 x 900 pixles", "ports": {"hdmi": 1, "usb": 1}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Bravia' ,
    '1' ,
    '1' ,
    '{"screen": "25 inch", "resolution": "1366 x 768 pixels", "ports": {"hdmi": 1, "usb": 0}, "speakers": {"left": "5 watt", "right": "5 watt"}}'
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Proton' ,
    '1' ,
    '1' ,
    '{"screen": "20 inch", "resolution": "1280 x 720 pixels", "ports": {"hdmi": 0, "usb": 0}, "speakers": {"left": "5 watt", "right": "5 watt"}}'
);

Instead of laying out the JSON object yourself, you can also use the built-in JSON_OBJECT function.

The JSON_OBJECT function accepts a list of key/value pairs in the form JSON_OBJECT(key1, value1, key2, value2, ... key(n), value(n)) and returns a JSON object.

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140

/* Let's sell some mobilephones */
INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Desire' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("GSM" , "CDMA" , "HSPA" , "EVDO") ,
        "body" ,
        "5.11 x 2.59 x 0.46 inches" ,
        "weight" ,
        "143 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "4.5 inches" ,
        "resolution" ,
        "720 x 1280 pixels" ,
        "os" ,
        "Android Jellybean v4.3"
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Passion' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("GSM" , "CDMA" , "HSPA") ,
        "body" ,
        "6.11 x 3.59 x 0.46 inches" ,
        "weight" ,
        "145 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "4.5 inches" ,
        "resolution" ,
        "720 x 1280 pixels" ,
        "os" ,
        "Android Jellybean v4.3"
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Emotion' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("GSM" , "CDMA" , "EVDO") ,
        "body" ,
        "5.50 x 2.50 x 0.50 inches" ,
        "weight" ,
        "125 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "5.00 inches" ,
        "resolution" ,
        "720 x 1280 pixels" ,
        "os" ,
        "Android KitKat v4.3"
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Sensation' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("GSM" , "HSPA" , "EVDO") ,
        "body" ,
        "4.00 x 2.00 x 0.75 inches" ,
        "weight" ,
        "150 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "3.5 inches" ,
        "resolution" ,
        "720 x 1280 pixels" ,
        "os" ,
        "Android Lollypop v4.3"
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Joy' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("CDMA" , "HSPA" , "EVDO") ,
        "body" ,
        "7.00 x 3.50 x 0.25 inches" ,
        "weight" ,
        "250 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "6.5 inches" ,
        "resolution" ,
        "1920 x 1080 pixels" ,
        "os" ,
        "Android Marshmallow v4.3"
    )
);

Notice the JSON_ARRAY function which returns a JSON array when passed a set of values.

If you specify a single key multiple times, only the first key/value pair will be retained. This is called normalizing the JSON in MySQL’s terms. Also, as part of normalization, the object keys are sorted and the extra white-space between key/value pairs is removed.

Another function that we can use to create JSON objects is the JSON_MERGE function.

The JSON_MERGE function takes multiple JSON objects and produces a single, aggregate object.

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95

/* Let's sell some cameras */
INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Explorer' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        '{"sensor_type": "CMOS"}' ,
        '{"processor": "Digic DV III"}' ,
        '{"scanning_system": "progressive"}' ,
        '{"mount_type": "PL"}' ,
        '{"monitor_type": "LCD"}'
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Runner' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        JSON_OBJECT("sensor_type" , "CMOS") ,
        JSON_OBJECT("processor" , "Digic DV II") ,
        JSON_OBJECT("scanning_system" , "progressive") ,
        JSON_OBJECT("mount_type" , "PL") ,
        JSON_OBJECT("monitor_type" , "LED")
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Traveler' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        JSON_OBJECT("sensor_type" , "CMOS") ,
        '{"processor": "Digic DV II"}' ,
        '{"scanning_system": "progressive"}' ,
        '{"mount_type": "PL"}' ,
        '{"monitor_type": "LCD"}'
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Walker' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        '{"sensor_type": "CMOS"}' ,
        '{"processor": "Digic DV I"}' ,
        '{"scanning_system": "progressive"}' ,
        '{"mount_type": "PL"}' ,
        '{"monitor_type": "LED"}'
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Jumper' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        '{"sensor_type": "CMOS"}' ,
        '{"processor": "Digic DV I"}' ,
        '{"scanning_system": "progressive"}' ,
        '{"mount_type": "PL"}' ,
        '{"monitor_type": "LCD"}'
    )
);

There is a lot happening in these insert statements and it can get a bit confusing. However, it is pretty simple.

We are only passing objects to the JSON_MERGE function. Some of them have been constructed using the JSON_OBJECT function we saw previously whereas others have been passed as valid JSON strings.

In case of the JSON_MERGE function, if a key is repeated multiple times, it’s value is retained as an array in the output.

A proof of concept is in order I suppose.

1
2
3
4
5
6
7

/* output: {"network": ["GSM", "CDMA", "HSPA", "EVDO"]} */
SELECT JSON_MERGE(
    '{"network": "GSM"}' ,
    '{"network": "CDMA"}' ,
    '{"network": "HSPA"}' ,
    '{"network": "EVDO"}'
);

We can confirm all our queries were run successfully using the JSON_TYPE function which gives us the field value type.

1
2

/* output: OBJECT */
SELECT JSON_TYPE(attributes) FROM `e_store`.`products`;

Read

Right, we have a few products in our database to work with.

For typical MySQL values that are not of type JSON, a where clause is pretty straight-forward. Just specify the column, an operator, and the values you need to work with.

Heuristically, when working with JSON columns, this does not work.

1
2
3
4
5
6
7

/* It's not that simple */
SELECT
    *
FROM
    `e_store`.`products`
WHERE
    attributes = '{"ports": {"usb": 3, "hdmi": 1}, "screen": "50 inch", "speakers": {"left": "10 watt", "right": "10 watt"}, "resolution": "2048 x 1152 pixels"}';

When you wish to narrow down rows using a JSON field, you should be familiar with the concept of a path expression.

The most simplest definition of a path expression(think JQuery selectors) is it’s used to specify which parts of the JSON document to work with.

The second piece of the puzzle is the JSON_EXTRACT function which accepts a path expression to navigate through JSON.

Let us say we are interested in the range of televisions that have atleast a single USB and HDMI port.

1
2
3
4
5
6
7
8

SELECT
    *
FROM
    `e_store`.`products`
WHERE
    `category_id` = 1
AND JSON_EXTRACT(`attributes` , '$.ports.usb') > 0
AND JSON_EXTRACT(`attributes` , '$.ports.hdmi') > 0;

The first argument to the JSON_EXTRACT function is the JSON to apply the path expression to which is the attributes column. The $ symbol tokenizes the object to work with. The $.ports.usb and $.ports.hdmi path expressions translate to “take the usb key under ports” and “take the hdmi key under ports” respectively.

Once we have extracted the keys we are interested in, it is pretty simple to use the MySQL operators such as > on them.

Also, the JSON_EXTRACT function has the alias -> that you can use to make your queries more readable.

Revising our previous query.

1
2
3
4
5
6
7
8

SELECT
    *
FROM
    `e_store`.`products`
WHERE
    `category_id` = 1
AND `attributes` -> '$.ports.usb' > 0
AND `attributes` -> '$.ports.hdmi' > 0;

Update

In order to update JSON values, we are going to use the JSON_INSERT, JSON_REPLACE, and JSON_SET functions. These functions also require a path expression to specify which parts of the JSON object to modify.

The output of these functions is a valid JSON object with the changes applied.

Let us modify all mobilephones to have a chipset property as well.

1
2
3
4
5
6
7
8

UPDATE `e_store`.`products`
SET `attributes` = JSON_INSERT(
    `attributes` ,
    '$.chipset' ,
    'Qualcomm'
)
WHERE
    `category_id` = 2;

The $.chipset path expression identifies the position of the chipset property to be at the root of the object.

Let us update the chipset property to be more descriptive using the JSON_REPLACE function.

1
2
3
4
5
6
7
8

UPDATE `e_store`.`products`
SET `attributes` = JSON_REPLACE(
    `attributes` ,
    '$.chipset' ,
    'Qualcomm Snapdragon'
)
WHERE
    `category_id` = 2;

Easy peasy!

Lastly, we have the JSON_SET function which we will use to specify our televisions are pretty colorful.

1
2
3
4
5
6
7
8

UPDATE `e_store`.`products`
SET `attributes` = JSON_SET(
    `attributes` ,
    '$.body_color' ,
    'red'
)
WHERE
    `category_id` = 1;

All of these functions seem identical but there is a difference in the way they behave.

The JSON_INSERT function will only add the property to the object if it does not exists already.

The JSON_REPLACE function substitutes the property only if it is found.

The JSON_SET function will add the property if it is not found else replace it.

Delete

There are two parts to deleting that we will look at.

The first is to delete a certain key/value from your JSON columns whereas the second is to delete rows using a JSON column.

Let us say we are no longer providing the mount_type information for cameras and wish to remove it for all cameras.

We will do it using the JSON_REMOVE function which returns the updated JSON after removing the specified key based on the path expression.

1
2
3
4

UPDATE `e_store`.`products`
SET `attributes` = JSON_REMOVE(`attributes` , '$.mount_type')
WHERE
    `category_id` = 3;

For the second case, we also do not provide mobilephones anymore that have the Jellybean version of the Android OS.

1
2
3

DELETE FROM `e_store`.`products`
WHERE `category_id` = 2
AND JSON_EXTRACT(`attributes` , '$.os') LIKE '%Jellybean%';

As stated previously, working with a specific attribute requires the use of the JSON_EXTRACT function so in order to apply the LIKE operator, we have first extracted the os property of mobilephones(with the help of category_id) and deleted all records that contain the string Jellybean.

A Primer for Web Applications

The old days of directly working with a database are way behind us.

These days, frameworks insulate developers from lower-level operations and it almost feels alien for a framework fanatic not to be able to translate his/her database knowledge into an object relational mapper.

For the purpose of not leaving such developers heartbroken and wondering about their existence and purpose in the universe, we are going to look at how to go about the business of JSON columns in the Laravel framework.

We will only be focusing on the parts that overlap with our subject matter which deals with JSON columns. An in-depth tutorial on the Laravel framework is beyond the scope of this piece.

Creating the Migrations

Make sure to configure your Laravel application to use a MySQL database.

We are going to create three migrations for brands, categories, and products respectively.

1
2
3

$ php artisan make:migration create_brands
$ php artisan make:migration create_categories
$ php artisan make:migration create_products

The create_brands and create_categories migrations are pretty similar and and a regulation for Laravel developers.

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

/* database/migrations/create_brands.php */

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateBrands extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('brands', function(Blueprint $table){
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('brands');
    }
}

/* database/migrations/create_categories.php */

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateCategories extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('categories', function(Blueprint $table){
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('categories');
    }
}

The create_products migration will also have the directives for indexes and foreign keys.

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

/* database/migrations/create_products */

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateProducts extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('products', function(Blueprint $table){
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('name');
            $table->unsignedInteger('brand_id');
            $table->unsignedInteger('category_id');
            $table->json('attributes');
            $table->timestamps();
            // foreign key constraints
            $table->foreign('brand_id')->references('id')->on('brands')->onDelete('restrict')->onUpdate('cascade');
            $table->foreign('category_id')->references('id')->on('categories')->onDelete('restrict')->onUpdate('cascade');
            // indexes
            $table->index('brand_id');
            $table->index('category_id');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('products');
    }
}

Pay attention to the $table->json('attributes'); statement in the migration.

Just like creating any other table field using the appropriate data type named method, we have created a JSON column using the json method with the name attributes.

Also, this only works for database engines that support the JSON data type.

Engines, such as older versions of MySQL will not be able to carry out these migrations.

Creating the Models

Other than associations, there is not much needed to set up our models so let’s run through them quickly.

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

/* app/Brand.php */

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Brand extends Model
{
    // A brand has many products
    public function products(){
        return $this->hasMany('Product')
    }
}

/* app/Category.php */

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Category extends Model
{
    // A category has many products
    public function products(){
        return $this->hasMany('Product')
    }
}

/* app/Product.php */

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    // Cast attributes JSON to array
    protected $casts = [
        'attributes' => 'array'
    ];

    // Each product has a brand
    public function brand(){
        return $this->belongsTo('Brand');
    }

    // Each product has a category
    public function category(){
        return $this->belongsTo('Category');
    }
}

Again, our Product model needs a special mention.

The $casts array which has the key attributes set to array makes sure whenever a product is fetched from the database, it’s attributes JSON is converted to an associated array.

We will see later in the tutorial how this facilitates us to update records from our controller actions.

Resource Operations

Creating a Product

Speaking of the admin panel, the parameters to create a product maybe coming in through different routes since we have a number of product categories. You may also have different views to create, edit, show, and delete a product.

For example, a form to add a camera requires different input fields than a form to add a mobilephone so they warrant separate views.

Moreover, once you have the user input data, you will most probabaly run it through a request validator, separate for the camera, and the mobilephone each.

The final step would be to create the product through Eloquent.

We will be focusing on the camera resource for the rest of this tutorial. Other products can be addressed using the code produced in a similar manner.

Assuming we are saving a camera and the form fields are named as the respective camera attributes, here is the controller action.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

// creates product in database
// using form fields
public function store(Request $request){
    // create object and set properties
    $camera = new \App\Product();
    $camera->name = $request->name;
    $camera->brand_id = $request->brand_id;
    $camera->category_id = $request->category_id;
    $camera->attributes = json_encode([
        'processor' => $request->processor,
        'sensor_type' => $request->sensor_type,
        'monitor_type' => $request->monitor_type,
        'scanning_system' => $request->scanning_system,
    ]);
    // save to database
    $camera->save();
    // show the created camera
    return view('product.camera.show', ['camera' => $camera]);
}

Fetching Products

Recall the $casts array we declared earlier in the Product model. It will help us read and edit a product by treating attributes as an associative array.

1
2
3
4
5
6

// fetches a single product
// from database
public function show($id){
    $camera = \App\Product::find($id);
    return view('product.camera.show', ['camera' => $camera]);
}

Your view would use the $camera variable in the following manner.

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
26
27
28
29
30

<table>
    <tr>
        <td>Name</td>
        <td>{{ $camera->name }}</td>
    </tr>
    <tr>
        <td>Brand ID</td>
        <td>{{ $camera->brand_id }}</td>
    </tr>
    <tr>
        <td>Category ID</td>
        <td>{{ $camera->category_id }}</td>
    </tr>
    <tr>
        <td>Processor</td>
        <td>{{ $camera->attributes['processor'] }}</td>
    </tr>
    <tr>
        <td>Sensor Type</td>
        <td>{{ $camera->attributes['sensor_type'] }}</td>
    </tr>
    <tr>
        <td>Monitor Type</td>
        <td>{{ $camera->attributes['monitor_type'] }}</td>
    </tr>
    <tr>
        <td>Scanning System</td>
        <td>{{ $camera->attributes['scanning_system'] }}</td>
    </tr>
</table>

Editing a Product

As shown in the previous section, you can easily fetch a product and pass it to the view, which in this case would be the edit view.

You can use the product variable to pre-populate form fields on the edit page.

Updating the product based on the user input will be pretty similar to the store action we saw earlier, only that instead of creating a new product, you will fetch it first from the database before updating it.

Searching Based on JSON Attributes

The last piece of the puzzle that remains to discuss is querying JSON columns using the Eloquent ORM.

If you have a search page that allows cameras to be searched based on their specifications provided by the user, you can do so with the following code.

1
2
3
4
5
6
7
8
9
10

// searches cameras by user provided specifications
public function search(Request $request){
    $cameras = \App\Product::where([
        ['attributes->processor', 'like', $request->processor],
        ['attributes->sensor_type', 'like', $request->sensor_type],
        ['attributes->monitor_type', 'like', $request->monitor_type],
        ['attributes->scanning_system', 'like', $request->scanning_system]
    ])->get();
    return view('product.camera.search', ['cameras' => $cameras]);
}

The retrived records will now be available to the product.camera.search view as a $cameras collection.

Deleting a Product

Using a non-JSON column attribute, you can delete products by specifying a where clause and then calling the delete method.

For example, in case of an ID.

1

\App\Product::where('id', $id)->delete();

For JSON columns, specify a where clause using a single or multiple attributes and then call the delete method.

1
2
3

// deletes all cameras with the sensor_type attribute as CMOS
\App\Product::where('attributes->sensor_type', 'CMOS')->delete();
}

Curtains

We have barely scratched the surface when it comes to using JSON columns in MySQL.

Whenever you need to save data as key/value pairs in a separate table or work with flexible attributes for an entity, you should consider using a JSON data type field instead as it can heavily contribute to compressing your database design.

If you are interested in diving deeper, the MySQL documentation is a great resource to explore JSON concepts futher.

来源：

https://scotch.io/tutorials/working-with-json-in-mysql

https://learnku.com/laravel/t/2066/the-correct-method-for-filling-large-quantity-of-false-data

Deployer

A deployment tool for PHP

https://deployer.org/

https://learnku.com/articles/10985/deployer-real-experience-sharing

一、Vagrant安装

去官网 下载安装对应版本即可，一路傻瓜式安装。

二、VirtualBox安装

去官网 下载安装对应版本即可，一路傻瓜式安装。

三、安装 Homestead Vagrant Box

当安装完 VirtualBox 以及 Vagrant 后，推荐直接安装 laravel/homestead Box，此 Box 已经包含了 Laravel 开发的常用软件，包括配置也已经帮忙处理好了。具体的安装和配置可以参考这里 Laravel 虚拟开发环境 Homestead

四、本地安装Vagrant Box

国内由于众所周知的网络原因导致直接安装速度很慢甚至无法安装，下面介绍本地添加安装的方法。

通过别的方式下载到最新的laravel/homestead box包。

第一种方式

1. 通过命令添加本地的 Box 包到 Vagrant。

1
2

vagrant box add laravel/homestead virtualbox.box  #添加本地box virtualbox.box为你本地box的文件路径

1. 通过上面的命令添加 Box 之后，如果我们直接运行 vagrant up 命令，会发现还是会去下载 box ，所以我们需要再配置一下默认的 box。我们通过查看 box 列表会发现我们添加的 laravel/homestead 没有正确的版本号。

1
2
3

vagrant box list #查看本机已安装box列表
laravel/homestead (virtualbox, 0) #会发现版本为0

1. 在上面 三、安装 Homestead Vagrant Box 步骤中会下载一个 Homestead 项目，我们可以直接修改目录下的 Vagrantfile 文件，加入下面的配置即可

1
2
3
4

config.vm.box = "laravel/homestead" #box的名字（需与盒子列表中的一致）
config.vm.box_version = "4.0"       #box的版本号（需与盒子列表中的一致）
config.vm.box_check_update = false  #box是否检查更新

1. 或者直接修改 这个目录下的 ~/.vagrant.d/boxes/laravel-VAGRANTSLASH-homestead/0 的0为你当前box的版本号即可，即 ~/.vagrant.d/boxes/laravel-VAGRANTSLASH-homestead/4.0 ，并在这个目录添加一个metadata_url 文件，文件内容如下

1
2

https://atlas.hashicorp.com/laravel/homestead

1. 执行 vagrant up 或者 homestead up 即可

第二种方式

1. 通过创建一个 metadata.json 文件的形式添加 box ，文件内容如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

{
  "name": "laravel/homestead", //box的名字
  "versions": [
    {
      "version": "4.0", //box的版本号
      "providers": [
        {
          "name": "virtualbox",
          "url": "virtualbox.box" //本地box的文件路径
        }
      ]
    }
  ]
}

1. 之后执行下面的命令来添加 box

1
2

vagrant box add metadata.json

1. 执行 vagrant up 或者 homestead up 即可

五、配置PhpStorm

Vagrant插件配置

1. 先下载 PhpStorm 的 Vagrant 插件，然后打开 Preferences -> Vagrant ，其中的 Vagrant executable 选项填写 vagrant 的安装目录 /opt/vagrant/bin/vagrant ，Instance folder 选项填写 三、安装 Homestead Vagrant Box 步骤中下载的 Homestead 项目目录 比如 /Users/xxx/Homestead

   具体可以参考下图

   

2. 打开 Preferences -> Languages&Frameworks -> PHP，首先选择自己支持的 PHP 版本，之后在 CLI Interpreter 选项选择后面的 … ，在弹出的菜单中选择 + 号添加服务器配置，选择第一项 From Docker,Vagrant 那项，弹出的菜单如下图所示，直接点击确认即可，具体可以参考下图

   

3. 打开 Preferences -> Languages&Frameworks -> PHP -> Servers 给自己的项目起一个名字，填写项目的host 地址，Debugger 选项选择 Xdebug 然后在 Project files 项目目录填写项目在 Vagrant 上的绝对目录，就是在 Homestead 项目下 Homestead.yaml 文件下配置的项目目录 sites 下的项目目录，注意是项目目录，不是 public 目录，具体可以参考下图

   

PHP Web Application配置

1. 打开 Run -> Edit Configurations ，选择 + 号添加 PHP Web Application ，然后填写项目名称，Server 选择上面步骤添加的服务器即可，具体可以参考下图

   

2. 上面的配置全部配置好之后，就可以 Run→Run ProjectName or Run→Debug ProjectName 直接运行项目，愉快的玩耍吧😄😄😄

来源：

http://frank-zhu.github.io/laravel5/2017/10/20/php-larvavel-vagrant-xdebug-phpstorm/

In this short tutorial I’ll show you how to make permanent redirect from a www URL to non-www and vice versa. I’ll assume that you have superuser privileges, sudo or root access and Nginx already configured, as well as DNS records. More specifically, you need to have an A records for www.yourdomain.com and yourdomain.com .

Redirect non-www to www

To redirect users from a plain, non-www domain to a www domain, you need to add this snippet in your Nginx domain configuration file:

1
2
3
4

server {
    server_name    yourdomain.com
    return         301 http://www.yourdomain.com$request_uri;
}

Save your configuration and exit. Before restarting Nginx make sure to test your configuration:

1

root@secure:~# nginx -t

You should have something like this as output:

1
2
3

root@secure:~# nginx -t
nginx: the configuration file /etc/nginx/nginx.conf syntax is ok
nginx: configuration file /etc/nginx/nginx.conf test is successful

Now when everything is checked you can restart Nginx:

1

root@secure:~# service nginx restart

Now, if you curl your plain domain, you should get a 301 Moved Permanently response:

1
2
3
4
5
6
7
8

root@secure:~# curl -I yourdomain.dev
HTTP/1.1 301 Moved Permanently
Server: nginx/1.8.0
Date: Fri, 26 Jun 2015 08:36:15 GMT
Content-Type: text/html
Content-Length: 184
Connection: keep-alive
Location: http://www.yourdomain.dev/

Redirect www to non-www

In the previous example you saw how to redirect users from a plain non-ww domain to a www domain. However, if you want to redirect from a www to a plain non-www domain you need to add following snippet in your domain configuration file:

1
2
3
4

server {
        server_name www.yourdomain.com;
        return 301 $scheme://yourdomain.com$request_uri;
}

After any change in Nginx configuration files you should test it for syntax errors:

1
2
3

root@secure:~# nginx -t
nginx: the configuration file /etc/nginx/nginx.conf syntax is ok
nginx: configuration file /etc/nginx/nginx.conf test is successful

And if you curl the www domain you should get same 301 Moved Permanently response:

1
2
3
4
5
6
7
8

root@secure:~# curl -I www.yourdomain.dev
HTTP/1.1 301 Moved Permanently
Server: nginx/1.8.0
Date: Fri, 26 Jun 2015 08:52:26 GMT
Content-Type: text/html
Content-Length: 184
Connection: keep-alive
Location: http://yourdomain.dev/

And that’s it. You have properly configured permanent redirect.

REFERENCES:

NGINX 301 Redirects

Nginx rewrite non-www-prefixed domain to www-prefixed domain

Nginx config for www to non-www and non-www to www redirection

How To Redirect www to Non-www with Nginx on CentOS 7

来源：

https://bosnadev.com/2015/06/26/how-to-redirect-www-to-non-www-and-vice-versa-with-nginx/

Open Images V4

Open Images 包含大约9百万张图片，带有 image-level 的标签和 object-level 的矩形框标注信息。

V4 的训练集包含174万张图片、1460万个矩形框和600个类别，是世界上最大的带有目标位置标注的数据集。矩形框由专业的标注人员标注，以确保准确性和一致性。而且图片种类多种多样、场景复杂，平均每张图片包含8.4个物体。同时， image-level 的标注分类为数千，详情如下表：

image-level 标注信息：

object-level 标注信息：

更多详细信息可以参考：https://storage.googleapis.com/openimages/web/factsfigures.html 。

Object level

每一行定义一个矩形框：

1
2
3
4
5
6
7
8
9
10

CopyImageID,Source,LabelName,Confidence,XMin,XMax,YMin,YMax,IsOccluded,IsTruncated,IsGroupOf,IsDepiction,IsInside
000026e7ee790996,freeform,/m/07j7r,1,0.071905,0.145346,0.206591,0.391306,0,1,1,0,0
000026e7ee790996,freeform,/m/07j7r,1,0.439756,0.572466,0.264153,0.435122,0,1,1,0,0
000026e7ee790996,freeform,/m/07j7r,1,0.668455,1.000000,0.000000,0.552825,0,1,1,0,0
000062a39995e348,freeform,/m/015p6,1,0.205719,0.849912,0.154144,1.000000,0,0,0,0,0
000062a39995e348,freeform,/m/05s2s,1,0.137133,0.377634,0.000000,0.884185,1,1,0,0,0
0000c64e1253d68f,freeform,/m/07yv9,1,0.000000,0.973850,0.000000,0.043342,0,1,1,0,0
0000c64e1253d68f,freeform,/m/0k4j,1,0.000000,0.513534,0.321356,0.689661,0,1,0,0,0
0000c64e1253d68f,freeform,/m/0k4j,1,0.016515,0.268228,0.299368,0.462906,1,0,0,0,0
0000c64e1253d68f,freeform,/m/0k4j,1,0.481498,0.904376,0.232029,0.489017,1,0,0,0,0

1. ImageID：图片 ID
2. Source：标注来源。
   • freeform 和 xclick 为手工标注。
   • activemil 使用方法1标注，并且人工验证保证 IoU>0.7。
3. LabelName：标签ID。
4. Confidence：置信度，总是为1。
5. XMin, XMax, YMin, YMax：矩形框坐标，已经归一化，范围为[0,1]。
6. IsOccluded: 物体是否被其他物体遮挡。
7. IsTruncated: 物体是否被截断（超出图像范围）。
8. IsGroupOf: 物体是否属于一组。 (如一群人)，包含5个以上物体，且彼此接触、互相遮挡。
9. IsDepiction: 物体是否为虚拟对象，如卡通人，不是现实世界物体。
10. IsInside: 图片是否从物体内部拍摄， 如从汽车或者建筑内部拍摄。

Image level

1
2
3
4
5
6
7
8
9
10

CopyImageID,Source,LabelName,Confidence
000026e7ee790996,verification,/m/04hgtk,0
000026e7ee790996,verification,/m/07j7r,1
000026e7ee790996,crowdsource-verification,/m/01bqvp,1
000026e7ee790996,crowdsource-verification,/m/0csby,1
000026e7ee790996,verification,/m/01_m7,0
000026e7ee790996,verification,/m/01cbzq,1
000026e7ee790996,verification,/m/01czv3,0
000026e7ee790996,verification,/m/01v4jb,0
000026e7ee790996,verification,/m/03d1rd,0

1. Source： 表明标签如何生成：
   • verification Google 标注人员验证。
   • crowdsource-verification Crowdsource App 验证。
   • machine 机器生成的标签。
2. Confidence：人工验证的正样本为1，负样本为0，机器生成的为分数，通常 >= 0.5，其值越大，越不可能为 Fase Positive。
3. Class Names：标签ID。
4. ImageID：图片ID。

下载

可以再在这里找到详细的下载列表，如下图：

下载列表

而图片可以从Figure Eight或CVDF下载：

参考链接

1. https://storage.googleapis.com/openimages/web/factsfigures.html
2. https://ai.googleblog.com/2018/04/announcing-open-images-v4-and-eccv-2018.html

1. We don’t need no bounding-boxes: Training object class detectors using only human verification, Papadopolous et al., CVPR 2016. ↩

来源：

https://xblog.lufficc.com/blog/open-images-v4

Laravel 项目中一些常用的包

https://packalyst.com/ Packalyst is a directory of Packages for your Laravel projects

假如有如下数据（Users）：

查询 admin:

1

select * from users where is_admin = '是';

查询不是 admin:

如果使用下面的 sql, 查不到任何数据

1

select * from users where is_admin != '是';

可使用如下 sql:

1

select * from users where is_admin is null;

https://blog.csdn.net/qq_33666602/article/details/80280122

操作系统： Windows 10

安装 Instant Client

官网（Instant Client for Microsoft Windows (x64) 64-bit | Oracle 中国） 下载 instantclient-basic-windows.x64-21.6.0.0.0dbru.zip 这种即可，版本自选，下载完成后解压，添加到环境变量。
注意下载链接的 Description 最后一项：

The 21c Basic package requires the Microsoft Visual Studio 2017 Redistributable.

如果没有安装，则打开 Pl/SQL 时会出现类似如下的错误：

1
2
3

不能初始化 "..\oci.dll"

Make sure you have the 64 bits Oracle Client installed.

按照 Description 中的链接下载安装。

语言设置：

在环境变量中加入：

1
2

变量名：NLS_LANG
变量值：SIMPLIFIED CHINESE_CHINA.ZHS16GBK

参考：

1、https://blog.csdn.net/stevendbaguo/article/details/54971914

2、https://wenku.baidu.com/view/2297e9bc3968011ca2009130.html

日期格式设置：

在环境变量中加入：

1
2
3
4
5

变量名：NLS_DATE_FORMAT
变量值：YYYY-MM-DD HH24:MI:SS

变量名：NLS_TIMESTAMP_FORMAT
变量值：YYYY-MM-DD HH24:MI:SS

参考：https://www.cnblogs.com/stono/p/5533492.html

环境变量->系统变量 -> 新建：

重新启动 PL/SQL。