GoAdmio

sqlite

Create a SQLite database file in a hosted site's sqlite folder and register it in GoAdmio with a generated access password, or remove that registration

On this page: Overview · Quick start · Syntax · Arguments · Output · Examples · Troubleshooting · Details · Risks · See also


Overview

SQLite keeps a whole database in one file, so a website can use it without a database server. /go/sqlite prepares such a database for a hosted domain. It:

It can also remove the registry entry again with the delete action.

[!WARNING] Creating a database makes the whole sqlite folder of the domain readable and writable by every user on the server. See Risks for how to tighten this.

[!WARNING] To remove an entry, delete must be the third argument. /go/sqlite example.com delete does not delete anything. It creates a database named delete.


Quick start

# Create a database called mydb for example.com
/go/sqlite example.com mydb

The file is created as /home/example.com/sqlite/mydb.db. A PHP site can open it with sqlite:/home/example.com/sqlite/mydb.db.


Syntax

# Create
/go/sqlite <domain> <name>

# Remove the registry entry
/go/sqlite <domain> <name> delete

The command writes the call and a timestamp to /var/log/go/sqlite.log. Run it as root.


Arguments

Position Argument Required Notes
1 <domain> Yes The hosted domain. Its account username is looked up from it.
2 <name> Yes The database name. The file is called <name>.db. Use letters, numbers, _, and - only.
3 delete To remove Removes the registry entry. Any other value, or none, creates.

Nothing is checked. The command does not test that the domain is hosted on this server. A misspelled domain creates a new /home/<domain>/sqlite folder. A name or domain with / or .. in it can create files in other places.


Output

Creating:

Database mydb created successfully...

Removing:

SQLite Access deleted successfully.

Both messages are printed even if the step did nothing, for example when the database already existed. The generated password is not printed.


Examples

# Create a database
/go/sqlite example.com mydb
# Remove its registry entry (delete is the third argument)
/go/sqlite example.com mydb delete
# Look at the files
ls -l /home/example.com/sqlite
# Open the database and look at its tables
sqlite3 /home/example.com/sqlite/mydb.db ".tables"
# Delete the database file itself (the command does not do this)
rm /home/example.com/sqlite/mydb.db
# Look up the stored password for a domain's databases
sudo sqlite3 /var/lib/sqlite/sqlite.db "select Name, Pass from SQLite where Domain='example.com'"

Troubleshooting

Symptom Likely cause Fix
A database named delete was created delete was the second argument. Run /go/sqlite <domain> <name> delete, using delete as the name, to remove its entry, then remove the file by hand.
After delete, the .db file is still there delete only removes the registry entry. Remove the file with rm.
A new folder appeared under /home The domain was misspelled, so the command created the folder. Check that it is empty, then remove it.
sqlite3: command not found SQLite is not installed. Install it with installsqlite.
I don't know the password It is not printed. Look it up in GoAdmio, or with the last command in Examples.
The same name appears twice in the registry Running the command again for the same name adds another entry with a new password. Remove the entries with delete, then create it again.
Other accounts can read or change the database The folder is set to 777. See Risks.

Details

What it does

  1. Records the call in /var/log/go/sqlite.log.
  2. Looks up the account's username from the Site table in /var/lib/sqlite/site.db.
  3. Removing (third argument is delete): deletes the entry that matches the name and domain from the SQLite table in /var/lib/sqlite/sqlite.db, and prints the confirmation.
  4. Creating (anything else):
    1. Creates /home/<domain>/sqlite if it is missing.
    2. If <name>.db does not exist, creates it with a table named SQLite that holds one row: the SQLite version and a timestamp. An existing file is left as it is.
    3. Sets the permissions of the whole folder and its files to 777, and the owner to the site's user.
    4. Generates a random 16-character password.
    5. Adds an entry with the name, domain, username, password, and time to the registry.
    6. Prints the confirmation.
flowchart TD
    A["sqlite domain name [delete]"] --> B["Look up the site's username"]
    B --> C{"Third argument is delete?"}
    C -- "Yes" --> D["Delete the registry entry, print confirmation"]
    C -- "No" --> E["Create the sqlite folder if missing"]
    E --> F["Create name.db if it does not exist"]
    F --> G["chmod 777 and chown the folder"]
    G --> H["Generate password, add registry entry, print confirmation"]

What is created

Item Location
The database file /home/<domain>/sqlite/<name>.db, outside the website's www folder, so it cannot be downloaded from the web
The registry entry The SQLite table in /var/lib/sqlite/sqlite.db: name, domain, username, password, time
The starting table A table called SQLite in the new file, with ID, Version, and TimeStamp columns

A SQLite file has no password of its own. The generated password is recorded for GoAdmio's SQLite access.


Risks


See also

Command Description
installsqlite Install the latest SQLite version on a GoAdmio server
mysql Create, edit, or delete a MySQL/MariaDB database and user for a hosting account
postgre Create, edit, or delete a PostgreSQL database and user
create Create a GoAdmio account (this creates the sqlite folder)
fix Reset ownership and permissions for one account or all accounts
backup Back up one or all GoAdmio accounts