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

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

Be able to perform the viewing effect online 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 Demo sample project that uses JSON to save the format of the demo sample data.

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

3.2. Text database (PHP text DB API)

We downloaded this relatively 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, for example, 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 change,

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

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

Through http://samples.app.ucai.cn/20140603/phptxtdb/examples/addressbook.php?

Lang=de&char=m

You can see a sample of the execution of this text database.

You can then see the results saved.

Is the text not?

3.3. SQLite Database

The SQLite database is also a file database. But not a text database. It is an own binary format.

The first is a library written by C, and very early also published the PHP access extension, now generally used is the sqlite3,php module name is also called Sqlite3.

There are two features of Sqlite:

1, now is a very popular file database, especially the embedded database. It is also widely used in mobile applications.

2, its access to the interface is very similar to MySQL.

Detailed installation is very easy, is the code from the official website, make can be, 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, do not unfold here. Our Open class in May has 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.

But it's worth noting that. Mysql_query This scheme 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/** * Gifted Net public class Demo sample code * * MySQL process operation MySQL Sample * * @author equipmengt * @seehttp://www.ucai.cn *   /$conn = mysql_connect (" 127.0.0.1 "," samples "," FTLY5QB "); if (!mysql_select_db ("samples", $conn)) {    echomysql_error ();    Exit;}

This should be provided by the user, here is a demo sample

$name = ' Wxstars ';

Construct a query

This is the best way to run SQL

Many other examples refer to mysql_real_escape_string ()

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

Run a 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 very 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 () and so on.

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

At the end of the script, I'll take the initiative.

The second example is a sample of the procedural and object-oriented MYQLI operations database.

<?
PHP/** * Gifted Net public class Demo sample code * * MYSQLI Process operation MySQL Sample * * @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 a demo sample

$name = ' Wxstars ';

Construct a query

This is the best way to run SQL

Many other examples refer to mysql_real_escape_string ()

$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/** * Gifted Net Public Course Demo Sample code * * Mysqli Object-oriented operation MySQL Sample * * @author equipmengt * @seehttp://www.ucai.cn */  $mysqli = new Mys Qli ("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 a demo sample

$name = ' Wxstars ';

Construct a query

This is the best way to run SQL

Many other examples refer to mysql_real_escape_string ()

$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/** * Gifted Net Public Course Demo Sample code * * PDO Operation MySQL Sample * * @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 ();}?
>

Summarize. We used three different PHP modules above. One is Phpmysql, one is mysqli, the other is Pdo_mysql three modules to do the same thing separately.

Let's say you study these three libraries alone. Will think it is more boring. When you learn a certain level of mastery. In particular, the control study found that you learn one of the other, learning the other is not difficult. This is just a few steps:

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

2. Test whether the connection is established successfully.

3, assemble the query, pay attention to different modules. The filtering method for the query assembly is also different.

4, run the query, get the result handle. Rather than 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 leaving the program. The result set resource needs to be freed.

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

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, between these softwares. There is no obvious boundary, like the appearance of Kvdb. Just because it's more capable of processing, and MySQL because of the very many limitations. Resulting in a simple scenario, the processing power is not as strong as kvdb. Not that you can't do it.

Second, in fact someone, will be the MySQL transformation, has reached even more than kvdb of 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 Folder

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 code folder.

Download a 5.5 version of the source code, configure

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

An error was found.

Just look at the version number,

Discovery is the version number of the 5.1.73

Thus a 5.1.73 source code is made.

Successfully configured.

And then

Make

Make install

The installation was successful.

The following is again enabled in MySQL

Mysql–uroot–p

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

The plugin was successfully installed.

Again in

/ETC/MY.CNF's [mysqld] section adds

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 see that has been successfully executed, and then netstat look.

Port monitoring succeeded.

Description This plugin is also included in a release version of Perconaserver in MySQL.

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.

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. either 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. On the order and the results. are not required to be 100% accurate, of course there are some technical indicators to measure in the best direction.

C, the exact query of the contents of the query, usually the number of the comparison or pre-matching, and so on.

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

E, accurate query often with the amount of data recorded to a certain extent, it is assumed that the text of the query. The whole speed will drop more obviously.

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 the full-text search function of the MySQL 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 Net Public Course Demo Sample code * * SOLR Full-Text Search client Test * * @author equipmengt * @seehttp://www.ucai.cn *  /$options = Array (   ' Hostna Me ' = ' localhost ',  //  ' login '   = ' username ',  //  ' password ' = ' password ',   ' Port '     

Iv. Summary of the course

Through this lesson above, we stand at a higher altitude and discuss the emergence and application scenarios of the database. Second, from both useful and academic aspects. Discusses some of the frequently used terminology and concepts. Thirdly, we explain the different usage of the database in different cases by examples. It is hoped that this will give you some key ideas to help you to continue to participate in the following courses.

The nature, concept and application of the

Database (ii)