One place for hosting & domains

      Install

      How To Install and Use SQLite on Ubuntu 20.04


      How To Install and Use SQLite on Ubuntu 20.04

      The author selected the Free and Open Source Fund to receive a donation as part of the Write for DOnations program.

      Introduction

      SQLite is a free, cross-platform database management system. It is popular for its efficiency and ability to interface with many different programming languages.

      In this tutorial you will install SQLite on Ubuntu 20.04. You will then create a database, read data from it, insert items, delete items, and join items from separate tables.

      Note: This tutorial includes practical instructions for installing and using SQLite. It does not cover larger conceptual issues and production concerns, such as when one should, or should not, consider using a SQLite database. For an excellent overview of popular relational databases and how they compare, check out our article, SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems.

      Additionally, many languages maintain integrations with SQLite. For instructions on using SQLite inside your Python code, check out our tutorial, How To Use the sqlite3 Module in Python 3.

      Prerequisites

      To complete this tutorial, you will need:

      Step 1 — Installing SQLite on Ubuntu 20.04

      To install the SQLite command-line interface on Ubuntu, first update your package list:

      Now install SQLite:

      • sudo apt-get install sqlite3

      To verify the installation, check the software’s version:

      You will receive an output like this:

      Output

      3.31.1 2020-01-27 19:55:54 3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837balt1

      With SQLite installed, you are now ready to create a new database.

      Step 2 — Creating a SQLite Database

      In this step you will create a database containing different sharks and their attributes. To create the database, open your terminal and run this sqlite3 command:

      sqlite3 sharks.db
      

      This will create a new database named sharks. If the file sharks.db already exists, SQLite will open a connection to it; if it does not exist, SQLite will create it.

      You will receive an output like this:

      SQLite version 3.31.1 2020-01-27 19:55:54
      Enter ".help" for usage hints.
      

      Following this, your prompt will change. A new prefix, sqlite>, now appears:

      With your Shark database created, you will now create a new table and populate it with data.

      Step 3 — Creating a SQLite Table

      SQLite databases are organized into tables. Tables store information. To better visualize a table, one can imagine rows and columns.

      The rest of this tutorial will follow a common convention for entering SQLite commands. SQLite commands are uppercase and user information is lowercase. Lines must end with a semi-colon.

      Now let’s create a table and some columns for various data:

      • An ID
      • The shark’s name
      • The shark’s type
      • The shark’s average length (in centimeters)

      Use the following command to create the table:

      • CREATE TABLE sharks(id integer NOT NULL, name text NOT NULL, sharktype text NOT NULL, length integer NOT NULL);

      Using NOT NULL makes that field required. We will discuss NOT NULL in greater detail in the next section.

      After creating the table, an empty prompt will return. Now let’s insert some values into it.

      Inserting Values into Tables

      In SQLite, the command for inserting values into a table follows this general form:

      INSERT INTO tablename VALUES(values go here);

      Where tablename is the name of your table, and values are within parentheses.

      Now insert three rows of VALUES into your sharks table:

      • INSERT INTO sharks VALUES (1, "Sammy", "Greenland Shark", 427);
      • INSERT INTO sharks VALUES (2, "Alyoshka", "Great White Shark", 600);
      • INSERT INTO sharks VALUES (3, "Himari", "Megaladon", 1800);

      Because you earlier specified NOT NULL for each of the variables in your table, you must enter a value for each.

      For example, try adding another shark without setting its length:

      • INSERT INTO sharks VALUES (4, "Faiza", "Hammerhead Shark");

      You will receive this error:

      Output

      Error: table sharks has 4 columns but 3 values were supplied

      In this step you created a table and inserted values into it. In the next step you will read from your database table.

      Step 4 — Reading Tables in SQLite

      In this step, we will focus on the most basic methods of reading data from a table. Recognize that SQLite provides more specific methods for viewing data in tables.

      To view your table with all of the inserted values, use SELECT:

      You will see the previously inserted entries:

      Output

      1|Sammy|Greenland Shark|427 2|Alyoshka|Great White Shark|600 3|Himari|Megaladon|1800

      To view an entry based on its id (The values we set manually), add the WHERE command to your query:

      • SELECT * FROM sharks WHERE id IS 1;

      This will return the shark whose id equals 1:

      Output

      1|Sammy|Greenland Shark|427

      Let’s take a closer look at this command.

      1. First, we SELECT all (*) values from our database, sharks.
      2. Then we look at all id values.
      3. Then we return all table entries where id is equal to 1.

      So far you have created a table, inserted data into it, and queried that saved data. Now you will update the existing table.

      Step 5 — Updating Tables in SQLite

      In the following two sections you will first add a new column into your existing table and then update existing values in the table.

      Adding Columns to SQLite Tables

      SQLite allows you to change your table using the ALTER TABLE command. This means that you can create new rows and columns, or modify existing rows and columns.

      Use ALTER TABLE to create a new column. This new column will track each shark’s age in years:

      • ALTER TABLE sharks ADD COLUMN age integer;

      You now have a fifth column, age.

      Updating Values in SQLite Tables

      Using the UPDATE command, add new age values for each of your sharks:

      • UPDATE sharks SET age = 272 WHERE id=1;
      • UPDATE sharks SET age = 70 WHERE id=2;
      • UPDATE sharks SET age = 40 WHERE id=3;

      Output

      1|Sammy|Greenland Shark|427|272 2|Alyoshka|Great White Shark|600|70 3|Himari|Megaladon|1800|40

      In this step you altered your table’s composition and then updated values inside the table. In the next step you will delete information from a table.

      Step 6 — Deleting Information in SQLite

      In this step you will delete entries in your table based on the evaluation of an argument.

      In the following command you are querying your database and requesting that that it delete all sharks in your sharks table whose age is less than 200:

      • DELETE FROM sharks WHERE age <= 200;

      Typing SELECT * FROM sharks; will verify that Alyoshka and Himari, who were each less than 200 years old, were deleted. Only Sammy the Greenland Shark remains:

      Output

      1|Sammy|Greenland Shark|427|272

      Step 7 — Joining Information in SQLite

      Let’s imagine that we had two tables: our current sharks table and an endangered table. Now what if the endangered table had an id value that mapped to the ids in your sharks table, and it also had a status value that indicated each shark’s conservation status?

      If you wanted to query data from both tables, you could use one of SQLite’s four join commands:

      • INNER JOIN
      • OUTER JOIN
      • LEFT JOIN
      • CROSS JOIN

      Let’s create that second table and then use INNER JOIN to join some data.

      First, create your endangered table:

      • CREATE TABLE endangered (id integer NOT NULL, status text NOT NULL);
      • INSERT INTO endangered VALUES (1, "near threatened");

      Now join your tables:

      SELECT * FROM sharks INNER JOIN endangered on sharks.id = endangered.id;

      Your output will look like this:

      Output

      1|Sammy|Greenland Shark|427|272|1|near threatened

      Note that the output also includes the id value from endangered. You can specify desired output with a more explicit command:

      • SELECT sharks.id, sharks.name, sharks.sharktype, sharks.length, sharks.age, endangered.status FROM sharks INNER JOIN endangered on sharks.id = endangered.id;

      This time the output excludes the second id value:

      Output

      1|Sammy|Greenland Shark|427|272|near threatened

      You have now successfully joined information from multiple tables.

      Conclusion

      SQLite is a useful tool for database management. One can quickly create a database and manipulate it with various commands. Following this tutorial, you now have a basic understanding of SQLite and you are prepared dive deeper into this database management system.

      For an excellent overview of Relational Databases systems and how they compare, check out our article, SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems.

      Additionally, many languages maintain integrations with SQLite. For instructions on using SQLite inside your Python code, check out our tutorial, How To Use the sqlite3 Module in Python 3.

      For specific help with SQLite’s syntax, the official documentation is another excellent resource.



      Source link

      How To Use the WordPress One-Click Install on DigitalOcean


      Introduction

      WordPress is one of the most popular content management and blog platforms in the world, allowing you to create and manage website content efficiently.

      This tutorial will share all the details you need to get your WordPress site set up using the WordPress 1-Click App.

      Software Included

      In addition to the features installed in a regular Ubuntu 20.04 Droplet, the WordPress 1-Click image also includes the following components:

      • Apache: The most widely used web (HTTP) server.

      • MySQL: A commonly used open-source relational database.

      • PHP: PHP language support.

      These components create the LAMP stack, a group of open source software typically installed together to enable a server to host dynamic websites like WordPress. The term LAMP is an acronym that represents the configuration of a Linux operating system with an Apache web server, with site data stored in a MySQL database and dynamic content processed by PHP.

      To improve security, the following additional components are included:

      • UFW: A program to simplify the management of a firewall.
      • fail2ban: A service that automatically updates your firewall configuration to block attackers.

      In this tutorial, we’ll create a WordPress 1-Click Droplet, then explain how to set up your WordPress site, configuring the domain and sharing information on how to access the MySQL database.

      Step 1 — Creating A WordPress Droplet

      To create a WordPress Droplet from the 1-Click App page, press the Create WordPress Droplet button:

      Create WordPress Droplet

      This takes you to the Create Droplets page, where you can customize your settings before creating your WordPress Droplet. How to Create your First Droplet describes each choice in detail. Below, we’ll highlight key settings:

      Choose an image:

      If not already set, you’ll need to switch from the Distributions tab to the Marketplace tab:

      Choose image tab

      Once there, WordPress 5.5.1 on Ubuntu 20.04 should be automatically selected. If not, click “WordPress 5.5.1 on Ubuntu 20.04” to select it as the image.

      Once the image is highlighted, you can accept the defaults or adjust settings according to your use case. We recommend the following changes:

      Choose a plan:

      Choose the size you need here — WordPress One-Click Droplets require 1GB of RAM or more.

      Add block storage:

      Learn more about block storage and add this to your droplet based on your use case.

      Choose a datacenter region:

      Generally, choose the data center closest to your user base.

      Select additional options:

      We recommend selecting Monitoring, a free service that will help you follow Droplet resource usage over time. You may also want to consider enabling automated backups. You can enable this now or anytime in the future.

      Choose a hostname:

      You can make it easier to identify the Droplet by naming it “WordPress,” or naming it after the site you plan to host.

      When you’ve made your choices, click Create Droplet.

      Once the Droplet is created, its address will be displayed:

      Copy address

      Click the address to copy it to your clipboard. In the next step, we’ll use this to view the WordPress site.

      Step 2 — Checking the Placeholder Page

      Open a web browser and paste the Droplet’s IP address into the location bar to visit your new site. Until you log in with SSH, the following page will appear:

      Before logging in with SSH

      This security measure has been put in place to protect the initial configuration of WordPress, in which the administrative user is set up.

      You can safely leave the installation in this state until you are ready to configure WordPress.

      Step 3 — Accessing the Droplet via SSH to Enable Configuration

      To remove the placeholder web page, you’ll first need to connect to the Droplet via SSH. Once you connect, it’s important to complete the WordPress configuration promptly. Until you do, anyone who finds your site could create the administrative user and password.

      To begin, you’ll connect directly to the server from the command line— you’ll need to complete this task once in order to configure WordPress. If you haven’t used a terminal program like SSH or PuTTY before, check out How To Connect To Your Droplet with SSH.

      When you’re ready, open a terminal on your computer and log into your Droplet as root (or your administrative user) via SSH with this command (substitute with your Droplet’s IP address):

      • ssh root@droplet.ip.address

      After the first successful SSH login, you’re ready to set your administrative password and configure WordPress.

      Note:
      You will be prompted to enter your administrative details on the terminal after logging in via SSH or temporary password. You can follow the terminal instructions to finish configuring WordPress, or use the graphical user interface configuration process that’s explained in the next step.

      Step 4 — Configuring WordPress

      After WordPress installation has been enabled by logging into the Droplet via SSH, visit the Droplet’s IP address in a web browser. You should see the following page:

      Screenshot showing the WordPress logo and a list of language

      Follow the on-screen prompts to select the language, give the site a name, and configure its administrative user. Be sure to set a strong password for added security.

      Once you have completed the initial configuration, you can log in as the administrative user you just created and continue to set up your site.

      Step 5 — Configuring a Domain

      To use a domain name instead of the IP address of your Droplet, follow the steps in How To Set Up a Host Name with DigitalOcean or set up a domain name with your own DNS provider. Either way you connect your domain name to your WordPress Droplet, once it resolves using DNS, you will need to update the WordPress configuration to use the domain.

      Log in to your WordPress admin panel at http://DROPLETIPADDRESS/wp-admin using the admin account you created earlier. Click on General under Settings in the navigation bar on the left-hand side of the page. Here, you will see several options you can configure for your site. Update the WordPress Address (URL) and Site Address (URL) fields to reflect your new domain name and click Save Changes.

      Configure to use domain name

      You can customize your site’s appearance, install plugins for added functionality, or create a new post or a page.

      Step 6 — Locating Logins and Passwords

      Additional details of your installation are provided in the MOTD (Message of the Day) which is displayed when you log into your Droplet via SSH:

      -------------------------------------------------------------------------------
      Welcome to DigitalOcean's One-Click WordPress Droplet.
      To keep this Droplet secure, the UFW firewall is enabled.
      All ports are BLOCKED except 22 (SSH), 80 (HTTP), and 443 (HTTPS).
      -------------------------------------------------------------------------------------
      

      If you need to access your MySQL database server you can find your MySQL root user password in the /root/.digitalocean_password file with the following command:

      • cat /root/.digitalocean_password

      Output

      root_mysql_pass="c404aed5e912278adb4EXAMPLE9782f390286d706a9c4f72" wordpress_mysql_pass="619863e454cEXAMPLE15be13d9730db5c0a52ad6a4d2491a"

      Copy the password, located inside the quotation marks on the line that begins root_mysql_pass. Next run the mysql command with the -u flag to specify a user (root) and the -p flag to be prompted for the password as follows:

      mysql -u root -p
      

      When prompted, paste in the password you copied.

      If you prefer to use a graphical user interface, follow the tutorial How To Install and Secure phpMyAdmin on Ubuntu 18.04.

      Conclusion

      Your WordPress site should be up and running. You can enhance its security by following our Initial Server Setup guide to give sudo privileges to your user, lock down root login, and take other steps to make your Droplet ready for production.



      Source link

      How to Install Spaces for WordPress Solutions


      Introduction

      WordPress is one of the most widely used content management systems (CMS) in the world, making up over 33% of the web and providing technical and non-technical users alike a way to build and grow. As WordPress sites scale however, managing large sets of data and assets can sometimes surface latency errors that can affect end user load times.

      In this tutorial, learn how to install Spaces, an S3-compatible object storage service that lets you store and serve large amounts of data, to optimize your WordPress site’s speed and performance.

      Prerequisites

      In order to complete this tutorial, you’ll need a DigitalOcean account, and ideally a WordPress installation on Ubuntu 20.04. There are many different starting points for your WordPress installation, including:

      Whichever you choose, this tutorial will start with the assumption that you have a DigitalOcean account and WordPress installation configured with an administrative user on Ubuntu 20.04.

      Create a Space

      From your DigitalOcean control panel, click the Spaces button, in the sidebar on the left:

      DigitalOcean Control Panel

      If you do not have pre existing Spaces created, you’ll see a button prompting you to create a space. You can click that button and follow along. If you have an existing Spaces installation, you’ll see a list of your existing Spaces. In this case, you can use the Create button on the top right of the screen to create a new Space and follow the rest of this tutorial.

      Spaces splash page

      Next, you’ll make a series of choices to customize your Space. Let’s review each choice for your WordPress installation.

      Select Datacenter Region

      Pick the datacenter region closest to your customers general location.

      Spaces datacenter region

      Enable the CDN

      A content delivery network (CDN) is a geographically distributed group of servers optimized to deliver static content to end users. This static content can be almost any sort of data, but CDNs are most commonly used to deliver web pages and their related files, streaming video and audio, and large software packages.

      Using a CDN with your WordPress installation can give site visitors the speed and efficiency they’ve come to expect with WordPress. Should you choose to enable the CDN, you’ll be presented with two choices:

      Spaces CDN selection

      Enter a subdomain for your Space. For help on configuring your domain or subdomain with DigitalOcean servers, visit our tutorial, “How to Point to DigitalOcean Nameservers From Common Domain Registrars”. Edge Cache TTL is set at 1 hour by default – feel free to change this to fit your use case.

      Static Site Hosting

      You can use Static Site Hosting to serve your static html, images and js files. This can be helpful for WordPress installations to reduce content load time — if you decide to use it for your WordPress installation, you can access your Space via http protocol by adding a CNAME record to your DNS host. CDN custom domain cannot be used with static site hosting.

      Spaces static site hosting selection

      Allowing File Listing

      File listing allows for anyone who queries to list the content of your Space. Choose whether or now to enable this setting, keeping in mind that restricting file listing only restricts access to the metadata detailing file contents. The file itself may still be visible.

      Finalize and Create

      Add your unique subdomain name and select the Project that you’d like your space to be assigned to:

      Spaces finalize and create selection

      If everything looks correct in your Spaces configuration, click Create Space. You’ll be taken to your new space, once created:

      DigitalOcean Spaces Dashboard

      Add to your Space

      There are several ways to add connectivity between your WordPress installation and your new Space.

      If you intend to use DigitalOcean Spaces for your WordPress media library, follow our tutorial, “How to Store WordPress Assets on DigitalOcean Spaces”, to use the DigitalOcean Spaces Sync WordPress plugin to connect your installation with your new Space.

      To extend the speed and performance of asset delivery using your new Space, our tutorial “How to Speed Up WordPress Asset Delivery Using DigitalOcean Spaces CDN” shares tips and suggestions on optimizing the Spaces CDN to deliver media assets efficiently.

      Conclusion

      Using Spaces with your WordPress installation enables you to provide a secure, fast, user-focused experience for site visitors. To dive deeper into using your new Space to benefit the speed and performance of your WordPress installation, visit the following tutorials:

      To learn more about how to optimize your WordPress installation, visit our complete list of resources for WordPress.



      Source link