SqliteDog - modern SQLite database manager
Probably every developer has come across SQLite databases at least once in his career. And if he is busy with mobile applications, then, apparently, simply does not have a chance to avoid them. We also did not become an exception and often use this wonderful database in our projects. As you know, the creators of SQLite for administration officially provided only the console. This gave rise to a host of monsters, a mass of various managers from third-party companies.
Alas, the quantity is not turned into quality. We have found some problems for all applications. Some are slightly shocked. For example, a very popular manager is the free add-on of Firefox browser with the simple name SQLite Manager. 234 entries in its issue tracker (how will it be in Russian?) Will shake faith in the future even with a seasoned IT specialist. "Incorrect display of 8-byte numbers." Yes, quite right, this manager cannot correctly show all the digits in the 8-byte integer representation (apparently because of his javascript nature). Just think who cares about big numbers?
Many managers are universal (they support all popular databases) and simply do not allow using all SQLite features (of which there are many). Another problem: an overloaded interface. Often there was a feeling that the developers simply "dumped" all the functionality of the application on the main screen in the form of a heap of small buttons. It was also annoying to split the possibilities into several applications: data editor, schema editor, data importer, etc. Marketing is good, but inconvenient to use. Finally, a fad nowadays - releasing an application on several platforms at once with a single code base and framework - often leads to a lot of minor bugs in the interface: problems with fonts, incorrect encoding, incorrect line height, “leaving” controls, etc.
')
Our doubts were reinforced by one post at stackoverflow.com, in which it was concluded that "despite the large number of SQLite database managers, there really isn’t any convenience and this plays a negative role in popularizing SQLite." So this project was born.
Allow me to submit: SqliteDog is a modern SQLite database manager. We wanted to slightly change the familiar interface of the database editor, so that it looked more like an “Excel mixture with Chrome” than “SQL Server Management Studio”. We proceeded from the following mantra: the database manager stands on “three pillars”. This data itself, data schema and SQL queries. It is necessary to make the data sampling as simple and convenient as possible, the data should be displayed as useful as possible (see below). You should show the data scheme visually and allow it to be changed arbitrarily. The SQL editor should be beautiful, comfortable; working with SQL should not be a problem. I also wanted to have a visual and convenient mechanism for managing settings (PRAGMA) SQLite, which are many and which have a lot of influence. For example, by default, foreign key restrictions are disabled and the cache size is 2,000 pages of the base. 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 suggestions. And this stage took about 3 months. The basic understanding was this: almost all database editors are built on archaic interface solutions that they copy from each other. Let's give some examples.
This is such atorture control, which is usually located on the left and contains connections to the database and objects of the database connected in trees. To get to, for example, columns of a table you usually need:
- expand the list of databases and find the right one;
- expand the "Tables" branch;
- find the desired table;
- expand the "Columns" branch.
Moreover, the operation itself "deployment" means:
- hitting the mouse in a tiny cross, which each time changes its position;
- the emergence of a new list that needs to be examined and which shifts the already existing representation of the tree.
If you think about it, then a tree is probably the most inconvenient control possible; it is used because of its flexibility (any hierarchy can be shoved). However, the scenario of working with the database, as a rule, is more complicated than just “see the columns”. Suppose a user wants to understand whether a column has some kind of restriction. How in this case the list of columns will help us? No, because the column limit can be set at the table level. And if the user is looking for a particular column in several tables? It is much more convenient for him to open the database schema, where on one screen everything will be immediately necessary (rather than expanding each table). Etc.
In addition, we did not like the (usual) practice of working with several databases in a single instance of the application. It seemed to us that this is a source of confusion and complication: all the time you need to understand in which base you are conducting 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 database from history. As a result, to view and edit the table schema in SqliteDog you need to do just two clicks. At the same time, elements of the list of database objects, of course, do not move, but remain in their places.
As is often the case, one decision entailed another. As soon as we decided that we want to quickly switch between the databases, resetting the entire current state, there was a problem of losing the entered SQL queries. Store SQL queries in files? But why? Again - archaism. We have "at hand" the most powerful DBMS. Query sizes are usually small. To lead their history will not be any problems. So, all queries are automatically saved in a special database. Out of that annoying window “You made a change, do you want to save?”, What a relief. But something and lost. The names of the files carry some information, binding, and now they do not exist at all. Solved this problem by making a search in the history of the query keywords.
All database managers allow you to display the requested data in the table (this is not to take away from them). How do they do it? For example, the width of the columns. Usually, instead of fitting the content, just a uniform division of the entire width by the number of columns is used. It is extremely irrational use of screen space. Or take a presentation of the data. Why not immediately show the color of what type of value: a string or a number? Why not immediately discard the number to the right? Is a blank cell NULL or empty string? To find out, you need to click on the cell and look at some properties, why? If the cell contains a BLOB (binary data) - why should I see its hexadecimal representation? What is the reason? Note that all managers proudly boast of showing pictures in BLOBs. Thanks, of course, but strings and numbers are still used more often. There is also such (rather strange) reception. If the value of the column is a string with carriage returns, then the height of the row of the table increases in proportion to the number of rows. I mean, let's say you have small XML texts stored in a column. When viewing on one screen, a maximum of 3 entries now fit.
In SqliteDog we tried to “squeeze the most” useful when displaying data. Immediately, without additional settings. Numeric values ​​are highlighted in color and replayed to the right. A null value is displayed specifically and is easily distinguishable from an empty string. For BLOB size is shown. If the value string contains control characters ("\ r \ n"), they are highlighted in a different color, the height of the table row does not increase. Pictures, of course, are also displayed. And with one click you can see the picture on the whole screen. By clicking (or pressing F4), the width of the columns of visible rows is uniformly adjusted to their contents. Double clicking on a cell adjusts the width of a particular column. You can scroll through the table using both the mouse and the keyboard. Moreover, scrolling the mouse wheel while holding the Control key allows you to scroll the table to the left and right. All these little things make life easier.
Traditionally, many applications have a “no document” status. For example, at the start. Does the user need this? Suppose 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 this: the connection to the database in SqliteDog is always there . If the user has closed the database, then SqliteDog creates an empty database "in memory" (one of the most useful features of SQLite). The same when starting the application. As soon as this feature appeared, an additional usage scenario immediately appeared. Sometimes you need to quickly recall the name of the function SQLite. We start SqliteDog, at once SQL input window is available. We start to type the name - the list of autocompletion falls out. We find the necessary function, we copy, it is ready.
As development progressed, it became clear that, potentially, the amount of functionality was unlimited. That is the nature of the project. You can always find another "Wishlist". And decide that without it - well, nothing. Therefore, a “razor” decision was made: some of the “big” features go to the “second version”. The decision on features was taken collectively. Thus, a list of active tasks with priorities and a list of features of the “second version” are maintained. It’s convenient to see them together so that you don’t come up with the same thing over and over again. It so happened that the blanks for one opportunity made it easy to implement another, and then the feature “returned” from the second version to the first. Also from the funny moments: some features were postponed for so long, being replaced by more urgent ones, that in the end you can’t drag the tabs into SqliteDog with the mouse. This will be fixed in the “second version” (probably :). Another compromise is the choice of a single platform (Windows). Alas, we could not find an acceptable framework for us that would provide cross-platform without loss of quality and speed. Making your own, that is, reinventing the wheel, is still a bust, the goal was originally different. As a result, the project was “sharpened” under Windows (and took about a year). But we got responsiveness and display quality at the required level.
Zasim stop (but if the community is interested in continuing the "saga", then it will follow). As for the license for the product. We decided that in addition to the commercial, 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 (databases, tables and index) and some data import / export are not available. SqliteDog interface is completely Russified (of course, there is an English broadcast for fans). The Russian version of the site also exists.
http://sqlitedog.com
SqliteDog is a SQLite database manager for efficient work. Its creators decided to abandon the usual interface solutions and simplify and facilitate the interaction as much as possible. Therefore, one application = one base (more precisely, one connection, you can make ATTACH of other bases). These selections are displayed as informative as possible, you can view / sort and even edit the records during the query execution (you can stop the download at any time by pressing Esc). Some features of SqliteDog are unique. For example, transaction management through buttons on the connection panel. Or the table monitoring mode, in which new records are automatically loaded (“Select the last 1000”, click the arrow button in the circle). Or the opportunity to open the base scheme and fix it in a separate window on the second monitor (to always have before my eyes). Or the ability to translate carriage returns in a column value from UNIX to Windows format with a single click.
We will be happy to hear feedback and suggestions for improvement. Thank you for your interest.
Alas, the quantity is not turned into quality. We have found some problems for all applications. Some are slightly shocked. For example, a very popular manager is the free add-on of Firefox browser with the simple name SQLite Manager. 234 entries in its issue tracker (how will it be in Russian?) Will shake faith in the future even with a seasoned IT specialist. "Incorrect display of 8-byte numbers." Yes, quite right, this manager cannot correctly show all the digits in the 8-byte integer representation (apparently because of his javascript nature). Just think who cares about big numbers?
Many managers are universal (they support all popular databases) and simply do not allow using all SQLite features (of which there are many). Another problem: an overloaded interface. Often there was a feeling that the developers simply "dumped" all the functionality of the application on the main screen in the form of a heap of small buttons. It was also annoying to split the possibilities into several applications: data editor, schema editor, data importer, etc. Marketing is good, but inconvenient to use. Finally, a fad nowadays - releasing an application on several platforms at once with a single code base and framework - often leads to a lot of minor bugs in the interface: problems with fonts, incorrect encoding, incorrect line height, “leaving” controls, etc.
')
Our doubts were reinforced by one post at stackoverflow.com, in which it was concluded that "despite the large number of SQLite database managers, there really isn’t any convenience and this plays a negative role in popularizing SQLite." So this project was born.
Allow me to submit: SqliteDog is a modern SQLite database manager. We wanted to slightly change the familiar interface of the database editor, so that it looked more like an “Excel mixture with Chrome” than “SQL Server Management Studio”. We proceeded from the following mantra: the database manager stands on “three pillars”. This data itself, data schema and SQL queries. It is necessary to make the data sampling as simple and convenient as possible, the data should be displayed as useful as possible (see below). You should show the data scheme visually and allow it to be changed arbitrarily. The SQL editor should be beautiful, comfortable; working with SQL should not be a problem. I also wanted to have a visual and convenient mechanism for managing settings (PRAGMA) SQLite, which are many and which have a lot of influence. For example, by default, foreign key restrictions are disabled and the cache size is 2,000 pages of the base. 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 suggestions. And this stage took about 3 months. The basic understanding was this: almost all database editors are built on archaic interface solutions that they copy from each other. Let's give some examples.
DB Objects Tree
This is such a
- expand the list of databases and find the right one;
- expand the "Tables" branch;
- find the desired table;
- expand the "Columns" branch.
Moreover, the operation itself "deployment" means:
- hitting the mouse in a tiny cross, which each time changes its position;
- the emergence of a new list that needs to be examined and which shifts the already existing representation of the tree.
If you think about it, then a tree is probably the most inconvenient control possible; it is used because of its flexibility (any hierarchy can be shoved). However, the scenario of working with the database, as a rule, is more complicated than just “see the columns”. Suppose a user wants to understand whether a column has some kind of restriction. How in this case the list of columns will help us? No, because the column limit can be set at the table level. And if the user is looking for a particular column in several tables? It is much more convenient for him to open the database schema, where on one screen everything will be immediately necessary (rather than expanding each table). Etc.
In addition, we did not like the (usual) practice of working with several databases in a single instance of the application. It seemed to us that this is a source of confusion and complication: all the time you need to understand in which base you are conducting 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 database from history. As a result, to view and edit the table schema in SqliteDog you need to do just two clicks. At the same time, elements of the list of database objects, of course, do not move, but remain in their places.
Saving SQL queries
As is often the case, one decision entailed another. As soon as we decided that we want to quickly switch between the databases, resetting the entire current state, there was a problem of losing the entered SQL queries. Store SQL queries in files? But why? Again - archaism. We have "at hand" the most powerful DBMS. Query sizes are usually small. To lead their history will not be any problems. So, all queries are automatically saved in a special database. Out of that annoying window “You made a change, do you want to save?”, What a relief. But something and lost. The names of the files carry some information, binding, and now they do not exist at all. Solved this problem by making a search in the history of the query keywords.
View selected data in the table
All database managers allow you to display the requested data in the table (this is not to take away from them). How do they do it? For example, the width of the columns. Usually, instead of fitting the content, just a uniform division of the entire width by the number of columns is used. It is extremely irrational use of screen space. Or take a presentation of the data. Why not immediately show the color of what type of value: a string or a number? Why not immediately discard the number to the right? Is a blank cell NULL or empty string? To find out, you need to click on the cell and look at some properties, why? If the cell contains a BLOB (binary data) - why should I see its hexadecimal representation? What is the reason? Note that all managers proudly boast of showing pictures in BLOBs. Thanks, of course, but strings and numbers are still used more often. There is also such (rather strange) reception. If the value of the column is a string with carriage returns, then the height of the row of the table increases in proportion to the number of rows. I mean, let's say you have small XML texts stored in a column. When viewing on one screen, a maximum of 3 entries now fit.
In SqliteDog we tried to “squeeze the most” useful when displaying data. Immediately, without additional settings. Numeric values ​​are highlighted in color and replayed to the right. A null value is displayed specifically and is easily distinguishable from an empty string. For BLOB size is shown. If the value string contains control characters ("\ r \ n"), they are highlighted in a different color, the height of the table row does not increase. Pictures, of course, are also displayed. And with one click you can see the picture on the whole screen. By clicking (or pressing F4), the width of the columns of visible rows is uniformly adjusted to their contents. Double clicking on a cell adjusts the width of a particular column. You can scroll through the table using both the mouse and the keyboard. Moreover, scrolling the mouse wheel while holding the Control key allows you to scroll the table to the left and right. All these little things make life easier.
What is “nothing”?
Traditionally, many applications have a “no document” status. For example, at the start. Does the user need this? Suppose 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 this: the connection to the database in SqliteDog is always there . If the user has closed the database, then SqliteDog creates an empty database "in memory" (one of the most useful features of SQLite). The same when starting the application. As soon as this feature appeared, an additional usage scenario immediately appeared. Sometimes you need to quickly recall the name of the function SQLite. We start SqliteDog, at once SQL input window is available. We start to type the name - the list of autocompletion falls out. We find the necessary function, we copy, it is ready.
Compromises Compromises
As development progressed, it became clear that, potentially, the amount of functionality was unlimited. That is the nature of the project. You can always find another "Wishlist". And decide that without it - well, nothing. Therefore, a “razor” decision was made: some of the “big” features go to the “second version”. The decision on features was taken collectively. Thus, a list of active tasks with priorities and a list of features of the “second version” are maintained. It’s convenient to see them together so that you don’t come up with the same thing over and over again. It so happened that the blanks for one opportunity made it easy to implement another, and then the feature “returned” from the second version to the first. Also from the funny moments: some features were postponed for so long, being replaced by more urgent ones, that in the end you can’t drag the tabs into SqliteDog with the mouse. This will be fixed in the “second version” (probably :). Another compromise is the choice of a single platform (Windows). Alas, we could not find an acceptable framework for us that would provide cross-platform without loss of quality and speed. Making your own, that is, reinventing the wheel, is still a bust, the goal was originally different. As a result, the project was “sharpened” under Windows (and took about a year). But we got responsiveness and display quality at the required level.
License
Zasim stop (but if the community is interested in continuing the "saga", then it will follow). As for the license for the product. We decided that in addition to the commercial, 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 (databases, tables and index) and some data import / export are not available. SqliteDog interface is completely Russified (of course, there is an English broadcast for fans). The Russian version of the site also exists.
http://sqlitedog.com
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 the interaction as much as possible. Therefore, one application = one base (more precisely, one connection, you can make ATTACH of other bases). These selections are displayed as informative as possible, you can view / sort and even edit the records during the query execution (you can stop the download at any time by pressing Esc). Some features of SqliteDog are unique. For example, transaction management through buttons on the connection panel. Or the table monitoring mode, in which new records are automatically loaded (“Select the last 1000”, click the arrow button in the circle). Or the opportunity to open the base scheme and fix it in a separate window on the second monitor (to always have before my eyes). Or the ability to translate carriage returns in a column value from UNIX to Windows format with a single click.
We will be happy to hear feedback and suggestions for improvement. Thank you for your interest.
Source: https://habr.com/ru/post/201268/
All Articles