The essence and concept of database and its application practice (II.)

Please see the original: http://www.ucai.cn/blogdetail/7034?mid=1

Can be run online to see the effect Oh!

Iii. the relationship and practice of various databases

3.1, self-made Simple text format (even in PHP, with data storage configuration)

A, for example, there is a ANGULARJS sample project that uses JSON to save the format of the sample data.

B, PHP Save configuration data is more common, TP is a typical.

3.2. Text database (PHP text DB API)

We downloaded the more famous text database engine from Http://www.c-worker.ch/txtdbapi/index.php#download.

Usage scenarios for text data this is a little bit of a confession.

1, such as your virtual host, support PHP, but do not support MySQL, and do not support SQLite when you can play a useful role.

Download the need to modify,

Two configurations of the txt-db-api.php.

$API _home_dir=dirname (__file__). Directory_separator;    $DB _dir=dirname (__file__). Directory_separator;

by Http://samples.app.ucai.cn/20140603/phptxtdb/examples/addressbook.php?lang=de&char=M

You can see an example of how this text database runs.

You can then see the results saved.

Is the text not?

3.3. SQLite Database

The SQLite database is also a file database, but is not a text database. It is an own binary format. The first is a library written by C, and very early release of PHP access extension, now generally used is the sqlite3,php module name is also called Sqlite3.

There are two features of Sqlite:

1, is now very popular file database, especially embedded database, in mobile applications are also very common.

2. Its access interface is very similar to that of MySQL.

The specific installation is very simple, is from the official website code, make can, but the code is really not a small one.

3. SQLite3 is already part of the relational database family, so it follows acid. Support for SQL statements is also good, and people on the Web have written it and SQL to import each other code.

There are also management tools similar to MySQL,

http://sourceforge.net/projects/sqlitemanager/

You can search for a bunch of sqlite Manager.

As for the specific operation, not to start here, our May public class, a special talk about this. SQLite and applications will be explained in more detail.

3.4. MySQL Database

MySQL from the use of speaking, everyone is more familiar with. However, it is worth noting that the Mysql_query method is obsolete in PHP 5.5.

Let's do three simple examples here. Use the deprecated MySQL and mysqli to manipulate the database with PDO.

The first example is the process of working with MySQL. The use of a very common database operation function, that is, PHP MySQL extension functions. These functions will be obsolete in version 5.5, so we have to make a change.

<?php/** * Excellent network Public Lesson example code * * MySQL process operation MySQL example * * @author equipmengt * @seehttp://www.ucai.cn *   /$conn = mysql_connect ("12 7.0.0.1 "," samples "," FTLY5QB "); if (!mysql_select_db ("samples", $conn)) {    echomysql_error ();    Exit;}

This should be provided by the user, here is an example

$name = ' Wxstars ';

Construct a query

This is the best way to execute SQL

See Mysql_real_escape_string () for more examples

$query = sprintf ("SELECT * from Users    wherename= '%s '",   mysql_real_escape_string ($name));

Execute Query

$result = mysql_query ($query); <span style= "font-family:arial, Helvetica, Sans-serif; Background-color:rgb (255, 255, 255); " > </span>

Check Results

The following shows the actual queries sent to MySQL, along with the errors that occurred. This is helpful for debugging.

if (! $result) {   $message  = ' Invalid query: '. Mysql_error (). "\ n";    $message. = ' Whole query: '. $query;   Die ($message);}

Use of results

Trying print $result does not remove information from the resulting resource

So you must use at least one of the MySQL result functions

See Mysql_result (), mysql_fetch_array (), mysql_fetch_row (), etc.

while ($row = Mysql_fetch_assoc ($result)) {    echo$row[' id ']. " \ n ";    echo$row[' name ']. " \ n ";    echo$row[' email ']. " \ n ";}

Releasing resources for the associated result set

It will be done automatically at the end of the script.

The second example is the process and object-oriented MYQLI operation database.

<?php/** * Excellent NET Public Lesson example code * * MYSQLI Procedure Operation Example of MySQL * * @author equipmengt * @seehttp://www.ucai.cn */$mysqli = Mysqli_connect ("127 .0.0.1 "," samples "," FTLY5QB "," samples ", 3306); /* Check connection */if (! $mysqli) {   printf ("Connect failed:%s\n", Mysqli_connect_error ());    Exit ();}

This should be provided by the user, here is an example

$name = ' Wxstars ';

Construct a query

This is the best way to execute SQL

See Mysql_real_escape_string () for more examples

$query = sprintf ("SELECT * from Users WHERE name= '%s '", Mysqli_escape_string ($mysqli, $name)); echo $query;/* Select queries return a resultset */if ($result = Mysqli_query ($mysqli, $query)) {   printf ("Select return Ed%d rows.\n ", Mysqli_num_rows ($result));      $row =mysqli_fetch_assoc ($result);   Print_r ($row);    /* Freeresult set *    /Mysqli_free_result ($result);} mysqli_close ($mysqli);? > <?php/** * Excellent network Public Lesson example code * * Mysqli Object-oriented operation example of MySQL * * @author equipmengt * @seehttp://www.ucai.cn */  $mysqli = new MySQL I ("127.0.0.1", "samples", "FTLY5QB", "samples"); /* Check connection */if ($mysqli->connect_errno) {   printf ("Connect failed:%s\n", $mysqli->connect_error); C7/>exit ();}

This should be provided by the user, here is an example

$name = ' Wxstars ';

Construct a query

This is the best way to execute SQL

See Mysql_real_escape_string () for more examples

$query = sprintf ("SELECT * from Users wherename= '%s '", $mysqli->real_escape_string ($name));  echo $query;/* Select queries return a resultset */if ($result = $mysqli->query ($query)) {printf ("Select returned%d        Rows.\n ", $result->num_rows);   $row = $result->fetch_assoc ();    Print_r ($row);  /* Freeresult set */$result->close ();} $mysqli->close ();? > <?php/** * Excellent network Public Lesson example code * * PDO Operation MySQL Example * * @author equipmengt * @seehttp://www.ucai.cn */* * * mysql hostname * * * $hostname = ' 127.0.0.1 '; /* * MySQL username * */$username = ' samples '; /* * MySQL Password * */$password = ' ftly5qb ';    try{$dbh =new PDO ("mysql:host= $hostname;d bname=samples", $username, $password); /* * echo a message saying we haveconnected * * */echo ' Connected to database '.     \ n ";    /* * The SQL SELECT statement */$sql = "SELECT * from users"; foreach ($dbh->query ($sql) as $row) {print$row[' id '). ' - ' . $row [' name ']. ' - ' . $row [' EmaiL '].    "\ n"; }/* * Close the database connection * */$DBH =null;} catch (Pdoexception $e) {echo$e->getmessage ();}?   >

In summary, we used three different PHP modules, one is Phpmysql, one is mysqli, the other is Pdo_mysql three modules to do the same thing separately. If you study these three libraries alone, will feel more boring, when you learn a certain degree, mastery, especially when comparing learning, you learn one of them, learn the other is not difficult. There are just a few steps:

1, establish the connection, in the establishment of the connection need to submit user name, password, host, library name, port and other data.

2. Check if the connection is established successfully.

3, the Assembly of the query, attention to different modules, the query assembly when the filter method is also different.

4, executes the query, obtains the result handle, but is not the direct data.

5. Get the data from the result handle by a very similar function.

6. Put the data in the result handle and output.

7. When you leave the program, you need to release the result set resource.

8, in the end, need to open a relationship database connection.

3.5, MySQL Kvdb a plug-in

Why use this plugin and demonstrate this plugin for a few purposes.

One, let everyone know MySQL and kvdb, these software, and there is no obvious boundaries, like the appearance of Kvdb, just its processing power is stronger, and MySQL due to a lot of limitations, resulting in a simple scenario, processing power is not as strong as kvdb. Not that you can't do it.

Two, in fact, someone, will be the MySQL transformation, has reached even more than kvdb a height. That's what this plugin does, it's said to be 750,000 QPS. And it can be used in production, and some distributions include this module in.

Http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html

Third, let us also know about the installation of MySQL plug-in. Learn more about MySQL's power.

Https://github.com/DeNA/HandlerSocket-Plugin-for-MySQL

wget Https://github.com/DeNA/HandlerSocket-Plugin-for-MySQL/archive/master.zip-O Handlersocket-plugin-for-mysql.zip

Unzip Handlersocket-plugin-for-mysql.zip

Go to Catalogue

CD handlersocket-plugin-for-mysql-master/sh autogen.sh./configure

An error has been found and is required to be configured together with the MySQL source directory.

Downloaded a 5.5 version of the source, configuration

./configure--with-mysql-source=. /mysql/mysql-5.5.37/

An error was found.

Had to look at the version,

Discovery is a version of 5.1.73

Thus under a 5.1.73 source.

Successfully configured,

And then

Make

Make install

Successfully installed. The following is enabled in MySQL

Mysql–uroot–p

Execute Install plugin handlersocket soname ' handlersocket.so '; Install the plugin.

The plugin was successfully installed.

Again in

/ETC/MY.CNF under the [mysqld] section

Loose_handlersocket_port    = 9998loose_handlersocket_port_wr    = 9999loose_handlersocket_threads    = 4loose _HANDLERSOCKET_THREADS_WR  = 1loose_handlersocket_address    = [IP address you want to listen to]

Then restart Mysqld

Show Processlist again.

We saw that it had run successfully, and then netstat a glance.

Port monitoring succeeded.

Description, this plug-in is already included in another release version of MySQL, Perconaserver.

http://www.mysqlperformanceblog.com/2010/12/14/percona-server-now-both-sql-and-nosql/

Let's use its PHP client to test it.

Download:

https://code.google.com/p/php-handlersocket/downloads/detail?name=php-handlersocket-0.3.1.tar.gz&can=2 &q=

Install the module.

Create a new test code.

New Table

Create Database Hstestdb;

CREATE TABLE ' hstesttbl ' (' k ' int (one) not null auto_increment, ' V ' char (255) NOT null DEFAULT ', PRIMARY KEY (' K ')) ENGI  Ne=innodb DEFAULT Charset=utf8 <?php$host = ' 101.251.196.91 '; $port = 9998; $port _WR = 9999; $dbname = ' hstestdb '; $table = ' Hstesttbl '; GET$HS = new Handlersocket ($host, $port);    $hs->openindex (1, $dbname, $table, Handlersocket::P rimary, ' k,v ')) {echo$hs->geterror (), Php_eol;    echo "Get error!\n"; Die ();} $retval = $hs->executesingle (1, ' = ', Array (' K1 '), 1, 0); Var_dump ($retval); $retval = $hs->executemulti (Array (1, ' = ', Array (' K1 '), 1, 0), array (1, ' = ', Array (' K2 '), 1, 0)); Var_dump ($retval);  Unset ($HS); UPDATE$HS = new Handlersocket ($host, $port _WR);    $hs->openindex (2, $dbname, $table, ' ', ' V '))) {Echo$hs->geterror (), Php_eol; Die ();}    if ($hs->executeupdate (2, ' = ', Array (' K1 '), Array (' V1 '), 1, 0) = = = False) {Echo$hs->geterror (), Php_eol; Die ();}  Unset ($HS); INSERT$HS = new Handlersocket ($hosT, $port _WR); if (! (    $hs->openindex (3, $dbname, $table, ' ', ' k,v '))) {Echo$hs->geterror (), Php_eol; Die ();} if ($hs->executeinsert (3, Array (' K2 ', ' v2 ')) ===false) {Echo$hs->geterror (), Php_eol;} if ($hs->executeinsert (3, Array (' K3 ', ' v3 ') ===false) {echo ' A ', $hs->geterror (), Php_eol;}  if ($hs->executeinsert (3, Array (' K4 ', ' v4 ') ===false) {echo ' B ', $hs->geterror (), Php_eol;} unset ($HS); DELETE$HS = new Handlersocket ($host, $port _WR);    $hs->openindex (4, $dbname, $table, ",")) {Echo$hs->geterror (), Php_eol; Die ();}    if ($hs->executedelete (4, ' = ', Array (' K2 ')) ===false) {Echo$hs->geterror (), Php_eol;  Die ();}

3.6. Full-Text Search

Full-Text Search is not a different domain from data retrieval. There are several features:

A, accurate database query, whether in order, or in the data structure is very determined.

B, full-text search is generally oriented to large data volume, so the query results, in order and results, are not required to achieve 100% accuracy, of course, there are some technical indicators to measure the best direction forward.

C, the exact query of the contents of the query, is usually a digital comparison or pre-matching.

D, the content of the full-text search, is often a paragraph of one or more words in the text. So we only look at text-based data.

E, accurate query often with the amount of data recorded to a certain extent, if it is for the text of the query, the whole speed will fall more obvious.

F, and the full text search generally, with the increase in data volume, the decline can not be so obvious.

There are three ways to implement full-text search in PHP, one is to use the full-text search function of MySQL's MyISAM engine. The second is to use the Sphinx with MySQL tightly. Third, the use of a more professional full-text search engine, Lucene. and use Slor to achieve the volume of inquiry.

Download SOLR

Wget-c Http://mirrors.cnnic.cn/apache/lucene/solr/4.8.1/solr-4.8.1.zip

CD Solr-4.8.1/examplejava–jar Start.jar

To build a good index by tutorial:

Http://lucene.apache.org/solr/4_8_1/tutorial.html

Http://101.251.196.91:8983/solr/collection1/select?q=%E6%9C%8D%E5%8A%A1

PHP Module Download:

Http://pecl.php.net/package/solr

<?php/** * Gifted Nets Public Lesson Sample code * * SOLR Full-Text Search client Test * * @author equipmengt * @seehttp://www.ucai.cn *  /$options = array (   ' hostname ' = = ' localhost ',  //  ' login '   = ' username ',  //  ' password ' = ' password ',   ' Port '     

Iv. Summary of the course

Through this lesson above, we stand at a relatively high level to discuss the emergence and application of the database. Secondly, it explores some common terms and concepts from both practical and academic aspects. Thirdly, we explain the different usage of the database in different cases by examples. It is hoped that this will give you some basic ideas to help you to continue to participate in the following courses.