Buscar en moleculax
Create PHP apps using SQL Server on Ubuntu

 

Step 1.1 Install SQL Server

Note: To ensure optimal performance of SQL Server, your machine should have at least 4 GB of memory. If you need to get Ubuntu, check out the Ubuntu Downloads website.
  1. Register the Microsoft Linux repositories and add their keys
    Terminal


  • curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
    curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list | 
     sudo tee /etc/apt/sources.list.d/mssql-server-2017.list
  • Install SQL Server
    Terminal
  • sudo apt-get update
    sudo apt-get install mssql-server
    Copy
    Results
    Reading package lists... Done
    Building dependency tree
    Reading state information... Done
    The following NEW packages will be installed:
      mssql-server
    ...
    Unpacking mssql-server ...
    Setting up mssql-server ...
  • Setup your SQL Server
    Terminal

    1. sudo /opt/mssql/bin/mssql-conf setup
      Copy
      Results
      Microsoft(R) SQL Server(R) Setup
      
      To abort setup at anytime, press Ctrl-C.
      
      The license terms for this product can be downloaded from 
      http://go.microsoft.com/fwlink/?LinkId=746388 and
      found in /usr/share/doc/mssql-server/LICENSE.TXT.
      
      Do you accept the license terms? If so, please type YES:
      Please enter a password for the system administrator (SA) account:
      Please confirm the password for the system administrator (SA) account:
    You now have SQL Server running locally on your Ubuntu machine! Check out the next section to continue installing prerequisites.

    Step 1.2 Install PHP and other required packages

    Terminal
    sudo apt-get -y install php7.0 libapache2-mod-php7.0 mcrypt php7.0-mcrypt 
    php-mbstring php-pear php7.0-dev apache2
    You have successfully installed PHP on your Ubuntu machine!

    Step 1.3 Install the ODBC Driver and SQL Command Line Utility for SQL Server

    SQLCMD is a command line tool that enables you to connect to SQL Server and run queries.
    Terminal
    curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
    curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | 
    sudo tee /etc/apt/sources.list.d/mssql-tools.list
    sudo apt-get update
    sudo ACCEPT_EULA=Y apt-get install mssql-tools
    sudo apt-get install unixodbc-dev
    echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
    echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
    source ~/.bashrc
    After installing SQLCMD, you can connect to SQL Server using the following command:
    Terminal
    sqlcmd -S localhost -U sa -P yourpassword
    1> # You're connected! Type your T-SQL statements here. Use the keyword 'GO' to 
    execute each batch of statements.
    This how to run a basic inline query. The results will be printed to the STDOUT.
    Terminal
    sqlcmd -S localhost -U sa -P yourpassword -Q "SELECT @@VERSION"
    Results
    --------------------------------------------------------
    Microsoft SQL Server vNext (CTP2.0) - 14.0.500.272 (X64)
     Apr 13 2017 11:44:40
     Copyright (c) Microsoft Corporation
        on Linux (Ubuntu 16.04)
    
    1 rows(s) returned
    
    Executed in 1 ns
    You have successfully installed SQL Server Command Line Utilities on your Ubuntu machine!

    In this section you will create a simple PHP app. The PHP app will perform basic Insert, Update, Delete, and Select.

    Step 2.1 Install the PHP Driver for SQL Server

    Terminal
    sudo pecl install sqlsrv pdo_sqlsrv
    sudo echo "extension= pdo_sqlsrv.so" >> `php --ini | grep "Loaded Configuration" |
      sed -e "s|.*:\s*||"`
    sudo echo "extension= sqlsrv.so" >> `php --ini | grep "Loaded Configuration" | 
    sed -e "s|.*:\s*||"`

    Step 2.2 Create a database for your application

    Create the database using sqlcmd
    Terminal
    sqlcmd -S localhost -U sa -P your_password -Q "CREATE DATABASE SampleDB;"

    Step 2.3 Create a PHP app that connects to SQL Server and executes queries

    Terminal
    mkdir SqlServerSample
    cd SqlServerSample
    Using your favorite text editor, create a new file called connect.php in the SqlServerSample folder. Paste the code below inside into the new file.
    PHP
    
        $serverName = "localhost";
        $connectionOptions = array(
            "Database" => "SampleDB",
            "Uid" => "sa",
            "PWD" => "your_password"
        );
        //Establishes the connection
        $conn = sqlsrv_connect($serverName, $connectionOptions);
        if($conn)
            echo "Connected!"
    ?>
    Run your PHP script from the terminal.
    Terminal
    php connect.php
    Results
    Connected!
    Execute the T-SQL scripts below in the terminal with sqlcmd to create a schema, table, and insert a few rows.
    Terminal
    sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "CREATE SCHEMA TestSchema;"
    sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "CREATE TABLE TestSchema.Employees 
    (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Name NVARCHAR(50), Location NVARCHAR(50));"
    sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "INSERT INTO TestSchema.Employees
    (Name, Location) VALUES (N'Jared', N'Australia'), (N'Nikita', N'India'), (N'Tom', N'Germany');"
    sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "SELECT * FROM TestSchema.Employees;"
    Using your favorite text editor, create a new file called crud.php in the SqlServerSample folder. Paste the code below inside into the new file. This will insert, update, delete, and read a few rows.
    PHP
    
    $serverName = "localhost";
    $connectionOptions = array(
        "Database" => "SampleDB",
        "Uid" => "sa",
        "PWD" => "your_password"
    );
    //Establishes the connection
    $conn = sqlsrv_connect($serverName, $connectionOptions);
    
    //Insert Query
    echo ("Inserting a new row into table" . PHP_EOL);
    $tsql= "INSERT INTO TestSchema.Employees (Name, Location) VALUES (?,?);";
    $params = array('Jake','United States');
    $getResults= sqlsrv_query($conn, $tsql, $params);
    $rowsAffected = sqlsrv_rows_affected($getResults);
    if ($getResults == FALSE or $rowsAffected == FALSE)
        die(FormatErrors(sqlsrv_errors()));
    echo ($rowsAffected. " row(s) inserted: " . PHP_EOL);
    
    sqlsrv_free_stmt($getResults);
    
    //Update Query
    
    $userToUpdate = 'Nikita';
    $tsql= "UPDATE TestSchema.Employees SET Location = ? WHERE Name = ?";
    $params = array('Sweden', $userToUpdate);
    echo("Updating Location for user " . $userToUpdate . PHP_EOL);
    
    $getResults= sqlsrv_query($conn, $tsql, $params);
    $rowsAffected = sqlsrv_rows_affected($getResults);
    if ($getResults == FALSE or $rowsAffected == FALSE)
        die(FormatErrors(sqlsrv_errors()));
    echo ($rowsAffected. " row(s) updated: " . PHP_EOL);
    sqlsrv_free_stmt($getResults);
    
    //Delete Query
    $userToDelete = 'Jared';
    $tsql= "DELETE FROM TestSchema.Employees WHERE Name = ?";
    $params = array($userToDelete);
    $getResults= sqlsrv_query($conn, $tsql, $params);
    echo("Deleting user " . $userToDelete . PHP_EOL);
    $rowsAffected = sqlsrv_rows_affected($getResults);
    if ($getResults == FALSE or $rowsAffected == FALSE)
        die(FormatErrors(sqlsrv_errors()));
    echo ($rowsAffected. " row(s) deleted: " . PHP_EOL);
    sqlsrv_free_stmt($getResults);
    
    
    //Read Query
    $tsql= "SELECT Id, Name, Location FROM TestSchema.Employees;";
    $getResults= sqlsrv_query($conn, $tsql);
    echo ("Reading data from table" . PHP_EOL);
    if ($getResults == FALSE)
        die(FormatErrors(sqlsrv_errors()));
    while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {
        echo ($row['Id'] . " " . $row['Name'] . " " . $row['Location'] . PHP_EOL);
    
    }
    sqlsrv_free_stmt($getResults);
    
    function FormatErrors( $errors )
    {
        /* Display errors. */
        echo "Error information: ";
    
        foreach ( $errors as $error )
        {
            echo "SQLSTATE: ".$error['SQLSTATE']."";
            echo "Code: ".$error['code']."";
            echo "Message: ".$error['message']."";
        }
    }
    ?>
    Run your PHP script from the terminal.
    Terminal
    php crud.php
    Copy
    Results
    Inserting a new row into table
    1 row(s) inserted:
    Updating Location for user Nikita
    1 row(s) updated:
    Deleting user Jared
    1 row(s) deleted:
    Reading data from table
    2 Nikita Sweden
    3 Tom Germany
    4 Jake United States
    Congrats you created your first PHP app with SQL Server! Check out the next section to learn about how you can make your PHP faster with SQL Server’s Columnstore feature.

    Now that you have explored the basics, you are ready to see how you can make your app better with SQL Server. In this module we will show you a simple example of Columnstore Indexes and how they can improve data processing speeds. Columnstore Indexes can achieve up to 100x better performance on analytical workloads and up to 10x better data compression than traditional rowstore indexes.

    Step 3.1 Create a new table with 5 million using sqlcmd

    Terminal
    sqlcmd -S localhost -U sa -P your_password -d SampleDB -t 60000 -Q "WITH a AS 
    (SELECT * FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a(a))
    SELECT TOP(5000000)
    ROW_NUMBER() OVER (ORDER BY a.a) AS OrderItemId
    ,a.a + b.a + c.a + d.a + e.a + f.a + g.a + h.a AS OrderId
    ,a.a * 10 AS Price
    ,CONCAT(a.a, N' ', b.a, N' ', c.a, N' ', d.a, N' ', e.a, N' ', f.a, N' ', g.a, N' ', h.a)
     AS ProductName
    INTO Table_with_5M_rows
    FROM a, a AS b, a AS c, a AS d, a AS e, a AS f, a AS g, a AS h;"

    Step 3.2 Create a PHP app that queries this tables and measures the time taken

    Terminal
    cd ~/
    mkdir SqlServerColumnstoreSample
    cd SqlServerColumnstoreSample
    Using your favorite text editor, create a new file called columnstore.php in the SqlServerColumnstoreSample folder. Paste the following code inside it.
    PHP
    
    $time_start = microtime(true);
    
    $serverName = "localhost";
    $connectionOptions = array(
        "Database" => "SampleDB",
        "Uid" => "sa",
        "PWD" => "your_password"
    );
    //Establishes the connection
    $conn = sqlsrv_connect($serverName, $connectionOptions);
    
    //Read Query
    $tsql= "SELECT SUM(Price) as sum FROM Table_with_5M_rows";
    $getResults= sqlsrv_query($conn, $tsql);
    echo ("Sum: ");
    if ($getResults == FALSE)
        die(FormatErrors(sqlsrv_errors()));
    while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {
        echo ($row['sum'] . PHP_EOL);
    
    }
    sqlsrv_free_stmt($getResults);
    
    function FormatErrors( $errors )
    {
        /* Display errors. */
        echo "Error information: ";
    
        foreach ( $errors as $error )
        {
            echo "SQLSTATE: ".$error['SQLSTATE']."";
            echo "Code: ".$error['code']."";
            echo "Message: ".$error['message']."";
        }
    }
    $time_end = microtime(true);
    $execution_time = round((($time_end - $time_start)*1000),2);
    echo 'QueryTime: '.$execution_time.' ms';
    
    
    ?>

    Step 3.3 Measure how long it takes to run the query

    Run your PHP script from the terminal.
    Terminal
    php columnstore.php
    Copy
    Results
    Sum: 50000000
    QueryTime: 363ms

    Step 3.4 Add a columnstore index to your table.

    Terminal
    sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "CREATE CLUSTERED 
    COLUMNSTORE INDEX Columnstoreindex ON Table_with_5M_rows;"

    Step 3.5 Measure how long it takes to run the query with a columnstore index

    Terminal
    php columnstore.php
    Copy
    Results
    Sum: 50000000
    QueryTime: 5ms
    Congrats you just made your PHP app faster using Columnstore Indexes!

    https://www.microsoft.com/en-us/sql-server/developer-get-started/php/ubuntu/

    .

    Social net



    .

    Colabore con este Blog usando: Mercadopago.

    Powered by

    http://emiliogomez.com.ve