Review of programs for administering SQLite databases. Using sqlite manager to work with sqlite database

SQLite is a database somewhat similar to MySQL. Fundamental difference SQLite from other databases is that the entire database is one file. If in MySQL database is stored somewhere in the wilds of the server and is not available for transfer, then in SQLite everything is outrageously simple: one file - one database.

Of course, the server must support the SQLite driver (just like any other database), but as a rule there are no problems with this now.

SQLite allows you to work with the database using SQL as usual, creating tables, fields, etc. In general, we can say that SQLite is in no way inferior to the usual MySQL, with the exception, perhaps, of more slow work with “heavy” SQL queries for updating data (insert and update). But, again, this is for high-load sites.

A huge advantage of SQLite is its ease of portability. Copy a file - what could be easier? You don’t need to worry about backups, like in MySQL, you don’t need to create a user with a password on the server, you don’t need to create the database itself. With SQLite we just take it and use it.

To work with the database in PHP is better use PDO - PHP Data Objects - this is the so-called. an abstraction that offers a single interface for working with different databases. In theory, using PDO, you can switch to any database without changing SQL queries, for example from MySQL to SQLite. Only the connection parameters change.

This way SQLite will connect via PDO. Nothing is required for this, since PDO itself is already included in PHP, and the SQLite driver is usually included on servers as well.

But, before you start programming, you need to create the base itself. For example, for MySQL there is phpMyAdmin, through which you can execute various operations. There are also similar developments for SQLite, but I will show how this can be done through Firefox browser. To do this, you only need to install the add-on.

To add this add-on to the main FireFox menu(“hamburger”), click Edit and drag the icon to the menu.

This is installed and you can use it.

First, let's create new base data. In SQLite this is separate file, which will have the extension .sqlite . SQLite Manager will prompt you to specify the directory where this file will be stored. Select or create new catalog. This doesn't really matter to us yet. As a result, a sqlite file with a new database will be created.

This file can be moved (and renamed) anywhere, and then opened with the menu command Databases - Connect database.

Now you need to create a table (or tables) in the database.

SQLite Manager automatically creates service tables sqlite_XXX. We don't touch them and they don't bother us.

A table in a database is where structured information is stored. The table must have a set of fields with the specified properties. For example, the field can be integer - for integers, or text - for text. The number of fields can be arbitrary and is determined only by the webmaster’s task.

Let, for example, we have a table pages with fields

id- unique number (auto-increment)
slug- link
text- free text
hits- number of views

After the table is created, pay attention to the “SQL statement that created this object” block. It will contain an SQL query that can be used to create a table. It can be useful if you need to create a table in the database via PHP.

The View and Search tab allows you to edit the table. For example, let’s create two lines where the field slug will be home and contact . These will be two pages: home And website/contact.

Field hits will contain a page view counter. The text can be anything.

That's it, the base is ready, now you can use it.

Let's set the task. Let us have a simple website that will display short link(slug) corresponding text and number of views.

If we do this on local server, then let the site be in the directory sqlite. There is a subdirectory in it db, where we will copy our pages.sqlite.

We can do routing as described in the previous article. File.htaccess

AddDefaultCharset UTF-8 Options -Indexes RewriteEngine on RewriteBase /sqlite/ RewriteCond %(REQUEST_FILENAME) !-f RewriteCond %(REQUEST_FILENAME) !-d RewriteRule (.*) /sqlite/index.php?$1

IN index.php The routing itself will be described in one line:

$page = ($p = key($_GET)) ? $p: "home";

connect the base
we make a selection in it by $page
display the received data

I intentionally simplify the algorithm so as not to complicate the PHP code.

There are two options for working with the database. The first is native PHP code. It's not very complicated, but the abundance of parameters is a little annoying. Therefore, the second option is to use additional wrapper libraries. With them, the code becomes more concise.

I will give the index.php code in the first version:

setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); $sql ="SELECT * FROM pages WHERE slug=:page LIMIT 1"; $sth = $pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)); $sth->execute(array(":page" => $page)); $rows = $sth->fetchAll(); print_r($rows); // output data here ) catch(Exception $e) ( echo $e->getMessage(); ) # end of file

For the second option, I used a php library from the site labaka.ru, which I placed in a subdirectory lib.

index.php code:

This example will display different text for different addresses and the views counter will work. If you need to add a new page, then you just need to create it in the database. For convenience, in SQLite Manager you can connect the database directly from the site directory.

There are a couple of important points to note here.

First of all, all work with PDO should be contained in a try..catch block. Error messages are caught in this way.

The data that is sent to the sql query must go through validation. In PDO, when data prepare is used (PDO::prepare), parameter escaping is forced. This allows you to protect against possible SQL injections.

When the base is connected SQLite data, if there is no database file, it will be created automatically. This is the basis for creating a database immediately after connecting it and creating the necessary tables with the first sql query (which I wrote about above).

One more note about SQLite. Since the database is a file, it can be downloaded from a URL directly through a browser. Therefore, it is better to protect the directory with SQLite files via .htaccess with the Deny from all line. Or place it higher than the main www directory.

SQLite can be useful for small tasks that require a small database: counters, voting, page metadata, etc.

You can refer to this article.

Probably every developer has encountered SQLite databases at least once during his career. And if he is busy with mobile applications, then, apparently, he simply has no chance to avoid them. We are no exception and often use this wonderful database in our projects. As you know, the creators of SQLite officially provide only a console for administration. This gave rise to a whole host of monsters, a huge number of different managers from third-party companies.

Alas, quantity did not translate into quality. U everyone applications we found certain problems. Some are a little shocking. For example, a very popular manager is a free add-on to the Firefox browser with the simple name SQLite Manager. 234 entries in his issue tracker (how would it be in Russian?) will shake the faith in the future of even an experienced IT specialist. "Incorrect display of 8-byte numbers." Yes, that's right, this manager cannot correctly show all the numbers in the representation of an 8-byte integer (apparently due to its javascript nature). Think about it, who cares about big numbers?

Many managers are universal (support all popular databases) and simply do not allow you to use all the features of SQLite (of which there are quite a lot). Another problem: an overloaded interface. Often there was a feeling that the developers simply “dumped” all the functionality of the application onto the main screen in the form of a bunch of small buttons. It was also annoying that the capabilities were divided into several applications: data editor, schema editor, data importer, etc. Marketing is good, but inconvenient to use. Finally, the now fashionable trend of releasing an application on several platforms at once with a single code base and framework often leads to the appearance of a bunch of small bugs in the interface: problems with fonts, incorrect encoding, incorrect line heights, missing controls, etc.

Our doubts were reinforced by one post on stackoverflow.com, which concluded that “despite the large number of SQLite database managers, there is no truly convenient one and this plays a negative role in terms of popularizing SQLite.” This is how this project came into being.

Let me introduce: SqliteDog- modern SQLite database manager. We wanted to slightly change the familiar database editor interface so that it would look more like a cross between Excel and Chrome than SQL Server Management Studio. We proceeded from the following mantra: the database manager stands on “three pillars”. This is the data itself, the data schema and SQL queries. The data selection should be made as simple and convenient as possible, and the data should be displayed as usefully as possible (see below). You should show the data schema clearly and allow it to be changed at will. The SQL editor should be beautiful and convenient; working with SQL shouldn't be a problem. I also wanted to have a visual and convenient mechanism for managing SQLite settings (PRAGMA), of which there are many and which affect a lot. For example, by default, foreign key constraints are disabled and the cache size is 2000 database pages. In 95% of cases these are not the best settings, but they are usually “hidden” inside some submenus.

So, we began to analyze existing applications, collect ideas and wishes. And this stage took about 3 months. The basic understanding was this: almost all database editors are built on archaic interface solutions, which they copy from each other. Let's give a few examples.

Tree of database objects

This is a torture control that is usually located on the left and contains database connections and database objects linked in trees. To get to, for example, table columns you usually need:
- expand the list of databases and find the one you need;
- expand the “Tables” branch;
- find the desired table;
- expand the “Columns” branch.
Moreover, the “unfolding” operation itself means:
- hitting a tiny cross with the mouse, which changes its position every time;
- the appearance of a new list that needs to be examined and which shifts the existing tree view.
If you think about it, a tree is probably the most inconvenient control possible; it is used because of its flexibility (any hierarchy can be inserted). However, the database scenario is usually more complex than just “look at the columns.” Let's say the user wants to understand whether a column has some kind of restriction. How can a list of columns help us in this case? No, because a column constraint can be set at the table level. What if the user is looking for a specific column in multiple tables? It is much more convenient for him to open the database schema, where everything he needs will be on one screen at once (than to expand each table). Etc.
Additionally, we didn't like the (common) practice of working with multiple databases in a single application instance. It seemed to us that this is a source of confusion and complexity: you always need to understand in which database you are performing this or that action. We decided that one instance of the application works with exactly one database (one connection) and abandoned the tree in favor of a linear list of tables and views. You can quickly switch to any base from history. As a result, to view and edit a table schema in SqliteDog you only need to make two clicks. In this case, the elements of the list of database objects, of course, do not move, but remain in their places.

Saving SQL queries

As often happens, one decision led to another. As soon as we decided that we wanted to quickly switch between databases, resetting all current state, the problem of losing entered SQL queries arose. Store SQL queries in files? But why? Again - archaism. We have the most powerful DBMS at our fingertips. Request sizes are typically small. Keeping their history will not be a problem. So, we automatically save all requests in a special database. That annoying “Have you made changes, do you want to save?” window is gone, what a relief. But something was lost. File names carry some kind of information, a link, but now they don’t exist at all. We solved this problem by searching the history using the query keywords.

View selected data in a table

All database managers allow you to display the requested data in a table (this cannot be taken away from them). How do they do it? For example, the width of the columns. Typically, instead of fitting to the content, you simply divide the entire width evenly by the number of columns. This is an extremely wasteful use of screen space. Or take the data representation. Why not immediately show in color what type of value it is: a string or a number? Why can't the number be immediately moved to the right? Is an empty cell NULL or an empty string? To find out, you still need to click on the cell and look at some properties, why? If there is a BLOB (binary data) in a cell - why do I need to see its hexadecimal representation? What's the reason? Note that all managers proudly boast of displaying pictures in BLOBs. Thank you, of course, but strings and numbers are still used more often. There is also this (rather strange) technique. If the column value is a string with carriage returns, then the height of the table row increases in proportion to the number of rows. That is, let’s say you have small XML texts stored in a column. When viewing, a maximum of 3 entries can now fit on one screen.
In SqliteDog, we tried to “squeeze out the maximum” usefulness when displaying data. Immediately, without additional settings. Numerical values are highlighted in color and displayed to the right. The value NULL is displayed differently and is easily distinguishable from the empty string. For BLOB the size is shown. If the value line contains control characters ("\r\n"), they are highlighted in a different color, and the table row height does not increase. Pictures, of course, are also displayed. And with one click you can view the picture in full screen. By clicking (or pressing F4), the width of the columns of visible rows is uniformly adjusted to their content. Double clicking on a cell adjusts the width of a specific column. You can scroll through the table using either the mouse or the keyboard. Moreover, scrolling the mouse wheel while holding down the Control key allows you to scroll the table left and right. All these little things make life easier.

What is "nothing"?

Traditionally, many applications have a "no document" state. For example, at start. Does the user need this? Let's assume the user SqliteDog closes the connection to the database. What does it mean? Does he want to open another database? But why close the current one - just open another one. Does he want to finish the job? But then you just need to close the application. The solution was found as follows: there is a connection to the database in SqliteDog Always. If the user closes the database, then SqliteDog creates an empty database “in memory” (one of the most useful features of SQLite). The same is true when launching the application. As soon as this feature appeared, an additional use case immediately arose. Sometimes you need to quickly remember the name of a SQLite function. We launch SqliteDog, the SQL input window is immediately available. When we start typing the name, an auto-completion list appears. We find the required function, copy it, done.

Compromises Compromises

As development progressed, it became clear that the amount of functionality was potentially limitless. That's the nature of the project. You can always find another “want”. And decide that without her - well, nothing. Therefore, a “razor” decision was made: some of the “big” features go into the “second version”. Decisions on features were made collectively. Thus, a list of active tasks with priorities and a list of “second version” features are maintained. Seeing them together is convenient so as not to come up with the same thing over and over again. It happened that the blanks for one feature made it easy to implement another, and then the feature “returned” from the second version to the first. Also a funny thing: some features were delayed for so long, replaced by more urgent ones, that as a result you can’t drag and drop tabs with the mouse in SqliteDog. This will be fixed in the “second version” (probably:). Another compromise is choosing one platform (Windows). Alas, we could not find a framework acceptable to us that would provide cross-platform without loss of quality and speed. Making your own, that is, reinventing the wheel, is still too much; the goal was originally different. As a result, the project was “tailored” for Windows (and took about a year). But we got responsiveness and display quality at the required level.

License

Then we’ll stop (but if the habra community is interested in continuing the “saga”, then it will follow). Regarding the product license. We decided that in addition to the commercial version, the official free version of SqliteDog will be available. No limits on time, number of rows, number of tables, database size, etc. In the free version, after 30 days of use, only designers (bases, tables and indexes) and some data import/export become unavailable. The SqliteDog interface is completely Russified (of course, there is also an English translation for fans). A Russian-language version of the site also exists.

Conclusion or TL;DR

SqliteDog is a SQLite database manager for efficient work. Its creators decided to abandon the usual interface solutions and simplify and facilitate interaction as much as possible. Therefore, one application = one database (more precisely, one connection, you can ATTACH other databases). The selection data is displayed as informatively as possible; you can view/sort and even edit records while the query is running (you can stop loading at any time by pressing Esc). Some features of SqliteDog are unique. For example, managing a transaction through buttons on the connection panel. Or the table monitoring mode, in which new records are automatically loaded (“Select the last 1000”, press the button with the arrow in the circle). Or the ability to open the base diagram and fix it in a separate window on the second monitor (to always have it in front of your eyes). Or the ability to convert carriage returns in a column value from UNIX to Windows format with one click.

We will be glad to hear feedback and suggestions for improvement. Thanks for your interest.

Tags: Add tags

MV framework supports MySQL and SQLite DBMS. Below is the technology for working with the SQLite database, which is compact and quickly portable from one server to another, while having all the necessary capabilities for a modern DBMS.

MV works with SQLite using the PDO library. On most servers it is enabled, but if the database does not start due to the lack of a driver, then you can read about setting it up. SQLite stores all the information in one file "userfiles/database/sqlite/database.sqlite".

To open a database file to work with tables, you need to install an add-on for the Mozilla Firefox browser called SQLite Manager. The add-on allows you to work with the database in a similar way to phpMyAdmin for MySQL. https://addons.mozilla.org/ru/firefox/addon/sqlite-manager/

When the add-on is installed, you need to launch SQLite Manager and select the desired database file "userfiles/database/sqlite/database.sqlite". After connecting the database file to SQLite Manager, a list of tables will appear in the left column.

To create a new table, click “Table -> Create Table” in the top menu, after which a window for entering table fields will open.

To go to an already created table, just click on the table name in the left column. At the top of the tabs you can see the structure of the table and the list of table records.

When you double click on a record in the table, a window will open for editing the fields of the record.

In general, working in SQLite Manager is not much different from working in phpMyAdmin for MySQL. You can also manage tables and indexes, perform direct queries, and create triggers. All work happens with only one file, which is then copied from the local server to the working one. At the same time, it is important not to forget to set write permissions for the database file and the folder in which it is located. Depending on the server configuration, the rights may be: 777, 770, 775 and others.

Setting up PDO SQLite

If the PDO driver for SQLite is connected, then when you call the phpinfo() function, you can see information about the driver version. If this information is not available, it means that the driver is not connected and must be enabled in the PHP settings.

The PHP libraries (extensions) folder should contain the files php_pdo_sqlite.dll and php_sqlite3.dll.

In the php.ini file, you need to uncomment the lines for connecting these libraries, after which you need to restart the web server.

After restarting the server, information about connecting the PDO driver for SQLite should appear in phpinfo(), as in the screenshot above.

Sqlite is a very simple and fast open source SQL engine. This guide describes the installation and optimal use of Sqlite, as opposed to full-featured RDBMSs such as Mysql and Postgres, and also covers and provides examples of basic CRUD (Create, Read, Update, and Delete) usage.

Don't think that Sqlite is created only for testing and development. For example, it can provide a site, according to conservative estimates, with up to 100,000 views per day. The maximum size of a Sqlite database is 140 terabytes (which is more than enough), it can be much faster than a full-scale RDBMS. The complete database and other required data are stored in a plain file on the host file system, hence no separate server process is needed (eliminating the need for inter-process communication).

Optimal use on VPS

Sqlite focuses on simplicity. Because this database is completely internal, it is often significantly faster than alternative options. Sqlite is ideal for those who need portability (in terms of languages and platforms), simplicity, speed and low memory consumption. The disadvantages of Sqlite are only visible when you need to read or write in parallel to several files: Sqlite supports only one editor; In addition, the usually large file system lag can be inconvenient if several users need to work in Sqlite at the same time. One last possible drawback: Sqlite's syntax is unique, although similar to other SQL systems. Switching to another system if you outgrow Sqlite is quite easy, but it may entail some resource costs.

Installing Sqlite on VPS

The sqlite3 module is part of the standard Python library, therefore, on a standard Ubuntu system or any other system with the Python library installed, no additional installations will be required. To install Sqlite CLI on Ubuntu, use these commands:

sudo apt-get update
sudo apt-get install sqlite3 libsqlite3-dev

If you need to compile Sqlite from source, download the latest version of autoconf from sqlite.org/download.html. During recording:

wget http://sqlite.org/2013/sqlite-autoconf-3080100.tar.gz
tar xvfz sqlite-autoconf-3080100.tar.gz
cd sqlite-autoconf-3080100
./configure
make
make install

Notes on compiling from source:

Don't do this on standard Ubuntu, as it will likely result in a "header and source version mismatch" error due to a conflict between the existing version and the newly installed version;
If the "make" command appears to be waiting for further input, just wait - compiling from source takes a while.

Basic Sqlite command line interface

To create the database, run the command:

sqlite3 database.db

where "database" is the name of the database. If the file "database.db" already exists, Sqlite will open it; if there is no such file, it will be created. The output looks something like this:

SQLite version 3.8.1 2013-10-17 12:57:35
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

Now create a table and add test data to it. This table called “wines” and consists of four columns: ID, wine producer, type of wine, country of origin. The table contains three rows:

CREATE TABLE wines (id integer, producer varchar(30), kind varchar(20), country varchar(20));
INSERT INTO WINES VALUES (1, "Rooiberg", "Pinotage", "South Africa");
INSERT INTO WINES VALUES (2, "KWV", "Shiraz", "South Africa");
INSERT INTO WINES VALUES (3, "Marks & Spencer", "Pinot Noir", "France");

So, a database and a table were created, in which some entries were made. Now press Ctrl + D to exit Sqlite and enter the following (replacing "database" with your database name) which will display the database you just created:

sqlite3 database.db

Now type:

SELECT * FROM wines;

The entries you just made will be displayed:

That's all you need to know about creating and reading in Sqlite. Now you need to familiarize yourself with the update and removal:

UPDATE wines SET country="South Africa" WHERE country="France";

This will update the database and all wines in the list originating in France will be marked as originating in South Africa. Check the result:

SELECT * FROM wines;

The result will be like this:

Now all the wines come from South Africa. Now try removing KWV from the database:

DELETE FROM wines WHERE id=2;
SELECT * FROM wines;

Now there will be one less entry in the list:

All basic database operations have been completed. Finally, here's a little more complex example, illustrating the use of two tables and basic operation associations.

Exit Sqlite using Ctrl + D and reconnect to the new database using

sqlite3 database2.db

A table very similar to “wines” will be created, as well as a “countries” table storing the name of the country and the name of its current president. First, create a "countries" table and add South Africa and France to it (note that you can copy and paste multiple lines of SQLite code at once):

CREATE TABLE countries (id integer, name varchar(30), president varchar(30));
INSERT INTO countries VALUES (1, "South Africa", "Jacob Zuma");
INSERT INTO countries VALUES(2, "France", "Francois Hollande");

Now create the "wines" table again:

CREATE TABLE wines (id integer, kind varchar(30), country_id integer);
INSERT INTO wines VALUES (1, "Pinotage", 1);
INSERT INTO wines VALUES (2, "Shiraz", 1);
INSERT INTO wines VALUES (3, "Pinot Noir", 2);

See what types of wine come from South Africa:

SELECT kind FROM wines JOIN countries ON country_id=countries.id WHERE countries.name="South Africa";

The following result will be displayed:

Pinotage
Shiraz

This is how the basic join operation is performed. Note that Sqlite does a lot of the work itself. In fact, the "JOIN" command defaults to "INNER JOIN", but can only be used keyword"JOIN". There is also no need to specify "wines.country_id" since this value is unique. On the other hand, if you enter the command:

SELECT kind FROM wines JOIN countries ON country_id=id WHERE country_id=1;

An error message will be displayed: “Error: ambiguous column name: id.”. this is fair since there is an "id" column in both tables. In general, Sqlite error messages help to detect and fix any problems fairly quickly, which greatly speeds up the development process. To get more detailed information about syntax, study the official documentation, which contains many graphs: sqlite.org/lang_delete.html ; it is very useful, but if you prefer specific examples, below is a link to a guide covering most types of joins: zetcode.com/db/sqlite/joins/.

In addition, drivers for Sqlite have been developed in all major languages, and Sqlite itself runs on most systems.

Tags: ,