Database applications. Programs for managing and working with databases. About the db and data directories
Introduction
Database (DB) - this is a named collection of data that reflects the state of objects and their relationships in the subject area.
DBMS – is a computer program responsible for creating, editing, deleting, and generally storing databases (DBs) or collections of data records.
DBMS functions:
1. Determining the structure of the database to be created, its initialization and initial loading.
2. Providing users with the ability to manipulate data (selecting the necessary data, performing calculations, developing an input/output interface, visualization).
3. Ensuring logical and physical data independence.
4. Protecting the logical integrity of the database.
5. Protection of physical integrity.
6. Managing user permissions to access the database.
7. Synchronization of work of several users.
8. Storage environment resource management.
9. Support for the activities of system personnel.
Chapter 1
1.1.Types of DBMS:
Relational
Relational DBMSs are the most common type of database management systems at the moment. They are really easy to use. Relational DBMSs have this name because... they store ordered data in tables. The relational model is based on storing ordered data in columns and rows. Data can be linked within the same table or across different tables. Types of relationalDBMS may lack performance compared to other types of database management systems, but they do not have the processing power and memory search problems of modern PCs. Data in this type of DBMS is stored in a precisely defined form, and they are usually managed using the SQL (Structured Query Language) programming language. You can also specify the most popular types of DBMS - Oracle, MSSqlServer, IBMDB2, PostgreSQL and many others.
Flat file database management systems
FlatFile DBMSs are perhaps the simplest of all. They are also sometimes called a flat model. The idea is to launch a single application that contains the program settings and the original storage model format. Flat files in formatted rows and columns assume that each element in a particular model contains the same information. One well-known example of this type of DBMS is CSV (Comma Separated Values) and another is tables like MS Excel.
Hierarchical
Hierarchical DBMSs are based on the family tree model. Here the elements usually have a one-to-many relationship and it is good to store information with a description of the elements, characteristics, etc. Here you can store a book with chapters and verses. Hierarchical typesDBMS can be used to store databases with songs, recipes, phone models, and generally anything that can be stored in a nested format. Hierarchical types of database management systems may be inferior in efficiency when working with various real objects. One example of hierarchical DBMS types is an XML document.
Network
Network database management systems use a data model similar to a hierarchical one. The main difference is that the structure of a network DBMS allows many parents for many children. Network types of DBMS are based on records and sets, and use SQL for their work. Network-based database management systems tend to be highly flexible, but they became rarely used after the 1960s and 1970s. In searching for the required element, the requesting program goes through all installed elements, which can be too cumbersome. This type of database management systems has been practically replaced by relational ones.
Choosing a program to write a database
In my course work, I used a relational database using Microsoft Access.
Microsoft Access– one of the most popular DBMS for the Microsoft Windows operating system. In addition, the Access DBMS is a powerful development platform with a flexible and functional integrated environment. Microsoft Access can be used as a tool for developing and deploying broad-based business information systems.
Access uses the following main field types:
text: intended for text information and numbers when there is no need to perform mathematical operations with them;
numerical: intended for numbers when used in mathematical calculations;
MEMO: designed to store arbitrary text or comments (up to 64,000 characters long);
monetary: designed to store numbers representing monetary amounts;
date Time: designed to store date and time information;
counter: a special numeric field designed to automatically add a unique number for the current record in the data table.
logical: designed to store only two values “True” and “False”;
OLE object field: intended for storing objects created by other applications (drawings, graphs, diagrams).
In Microsoft Access, there are several ways to select only the data you need when searching for a specific value, a single record, or a group of records.
Using the Search dialog box It's easy to find specific records or specific values in fields. When each occurrence of the required element is found, it moves through the records. If you need to replace specific values found during the search, you should use the Replace dialog box.
Requests make it possible to work with a specific set of records that satisfy the conditions specified for one or more database tables. When you run a query, it becomes possible to work independently with this set of records in a specific form or object in table view.
Sorting makes it possible to arrange rows in a table according to the contents of one or more columns. To sort, select the field by which the sorting will be performed and click the “Sort Ascending” or “Sort Descending” button on the toolbar.
Filtration allows you to temporarily isolate and view a specific set of records that meet certain conditions. To filter, select the data you want to filter by and click the “Filter by selected” button on the toolbar. To change the filter, use the “Change filter” button. To set more detailed conditions for filtering in the “Record” menu, use the “Filter” command and the “Advanced filter” subcommand. To apply a regular or advanced filter, use the “Apply filter” button on the toolbar.
Chapter 2
Formulation of the problem
The purpose of the Hotel database is to automate the process of accounting and analyzing hotel data.
Tasks solved when using the database:
Storing and retrieving data about hotel employees;
Storing and retrieving information about hotel clients;
Storing and retrieving information about numbers;
Storing data on reservations, check-in, check-out of clients and calculating payment for accommodation
Calculation and analysis of information on receipt of payment for accommodation from the company’s clients;
Search for information on the main items: booked rooms, check-in for the period, check-out for the period.
Database creation
To create a MS Access database, the Table Designer was used, which allows you to determine the required table structure.
To display complete information on the condition of the hotel, the areas of its activities and reporting on work performed, the following tables have been created:
1. "Employees"
The table has the following fields: employee code(key field) - data type: counter; Full Name– data type: text, size – 50, optional, no matches allowed; Date of Birth–data type: date\time, optional; education– data type: substitution master – fixed data set (higher, secondary); job title–master of substitutions (list of available positions); address– data type: text, size-50, optional; telephone– data type: numeric, optional field; salary– data type: monetary, field format – rubles, optional. The table in design mode looks like this:
2. "Positions"
This table has the following fields: job code– counter (key field); job title– data type: text (set of main positions).
3. "Numbers"
This table has the following fields: number code– data type counter (key field); category– data type: master of settings (single, double, junior suite, luxury)
Fields: category code– data type: counter (key field), number– text; category– master of substitutions (numbers); number of seats– data type: numeric long integer, optional; number of rooms- similar; Additional Information– data type: MEMO; cost per day– data type: monetary, format – rubles, optional.
5. “Number status”
Fields: number code– counter, key field; number- data type substitution material (similar field from the “Room Categories” table); employee(maid) - substitution master (from a query on the “employees” table, with the selection condition “maid”), fields - reservation, check-in, check-out– data type: date\time, optional field; client – substitution master (from the “Clients” table).
6. "Clients"
Fields: Client code Full Name– data type: text, field size - 50, mandatory; Date of Birth- data type: date\time, mandatory; floor- master of production (husband, wife); document type– production master (passport, military license, identity card), mandatory; series- text, mandatory; number– numeric, mandatory; issued by– text, field size – 50, required, date of issue– date/time, required.
Fields: child code– data type: counter (key field); Client code– data type: substitution wizard (a similar field from the “clients” table); Full Name– data type: text, field size – 50; Date of Birth– data type: date\time.
The data schema looks like:
The Room Status and Employees tables are linked through a request for Employees, with the selection condition “maid”.
For ease of use, queries have been created for the Hotel database.
Request No. 1 – “Reservation”
In the Selection condition line, the following condition is entered:
>=[s] And<=[по]
Meaning the selection of fields for a certain period of time.
Queries 2 and 3 were created similarly.
Request No. 2 - “Check in to the rooms”
Request No. 3 – “Checking out of rooms”
Request No. 4 – “Clients with children.”
The query in design mode looks like this:
This request systematizes information about whether clients have children, which is necessary for certain discounts, benefits, etc.
Request No. 5 – “Payment for accommodation.”
The request contains an expression that reflects the calculation of the cost of living for each client.
Request No. 6 – “Paid for the month.”
The request contains an expression reflecting the calculation of the cost of living for each client:
Payment for accommodation: (State of room. Check-out - Status of room. Check-in + 1) * Categories of rooms. [Cost per day].
As well as selecting fields for the period.
Request No. 7 – “Payment for the schedule.”
Similar to request 6, except for selecting the period for selecting fields. Based on the data from this query, charts for analysis, as well as pivot tables, are built.
Request No. 8 – “Positions Request”.
Request with the selection condition for employees “maid”. Used to substitute data into the Number Status table.
Creating these queries greatly simplifies hotel reporting activities, conveniently systematizes information, and allows you to quickly use it and make changes. The list of requests is presented below:
The reports are as follows:
Working with the database begins by opening the Hotel form:
On the left are buttons for opening reports, on the right for entering and viewing data on main positions.
For example, the Room Information form looks like this:
Here you can see all the information about the hotel room, as well as a list of all the rooms. When I select a position, we can immediately see all the information about the room. In the lower left corner there are buttons for ease of working with records. In the upper right there is a button to open the Enter room category form.
Using the Booking Check-in Check-out button, we will open the form:
Which has three tabs:
Room information – complete information about available rooms;
Dynamics of receipts – dynamics of receipts by number:
Payment summary – summary of payment per room:
The Hotel Employees button opens the following form:
Here you can view information about employees, as well as enter the necessary information.
With the help of displaying reports, the tasks of searching for information about the status of rooms are solved: reservation, check-in, check-out, as well as reflecting payment information and displaying a receipt for the client.
Thus, this database solves the assigned problems.
Conclusion
During the course work, the “Hotel” database was created. This database is intended for employees as it simplifies the customer service process.
This database includes: tables, queries, forms and reports, as well as an autorun window for ease of use.
Literature:
1. Date, K., J. Introduction to database systems. 6th ed. - TO.; M., St. Petersburg: “Williams”, 2000. – 848 p.
2. Khomonenko A.D., Tsygankov V.M., Maltsev M.G. Databases: Textbook for higher educational institutions / Ed. prof. HELL. Khomonenko. – St. Petersburg: CORONA print, 2002. – 672 p.
3. V.V. Korneev, A.F. Gareev, S.V. Vasyutin, V.V. Reich Databases. Intelligent information processing. – M.: Knowledge, 2001.- 496 p.
4. Prag K., Irwin M., “Access User Bible for Windows 2000”, K.: Dialectics, 2001 – 576 pp..
5. Garnaev A.S., “VBA Self-Teacher”, St. Petersburg, 1999. – 104 p.
6. Microsoft Access 2010. Application development using a real example - G. Gurvits - 496p.
7. Database design. Microsoft Access DBMS. Textbook - N. N. Grinchenko, E. V. Gusev, N. P. Makarov - 240 p.
8. Access 2007. Effective use - V. E. Koshelev - 590 p.
DbVisualizer 9.1.5
DbVisualizer is a database tool for developers and database administrators, helping you with both the development and maintenance of your databases.
Firebird 2.5.2
Firebird is a relational database offering many ANSI SQL standard features that runs on Linux, Windows, and a variety of Unix platforms. Firebird offers excellent concurrency, high performance, and powerful language support for stored procedures and triggers.
Reitec.PMM 1.2.1.0
Reitec.PMM is a free tool for the management of test equipment as part of quality assurance.
WowBase 1.1
Create your own database quickly and easily.
MyContacts 3.1
Managing contacts, birthdays and dates/tasks.
LibreOffice Rus for Windows 3.6.4
LibreOffice office suite is the best free alternative for Microsoft Office 2003, 2007 and even 2010.
PhpMyAdmin 3.5.3
phpMyAdmin Rus is a program for managing a MySQL database, which you can download and use for free - both at home and in an organization. Russian version.
Oracle MySQL for Linux, UNIX, *BSD and other *nix 5.5.28 Final / 6.0.6 Beta
Oracle MySQL - Famous database server. Famous for its impeccable stability and speed.
Oracle MySQL for Windows 5.5.28 Final / 6.0.6 Beta
MySQL is one of the most popular open source databases and is completely free to download and use.
Many companies create various feature-rich applications to facilitate database management, development and administration.
Most relational databases, with the exception of MS Access, consist of two separate components: a "back-end" where the data is stored and a "front-end" - the user interface for interacting with the data. This type of design is quite clever because it parallelizes a two-layer programming model that separates the data layer from the user interface and allows the software market to focus directly on improving its products. This model opens the door for third parties to build their own applications to interact with different databases.
On the Internet, anyone can find many products for developing and administering MySQL databases. We decided to collect the 10 most popular tools in one article so that you can save your time.
1.Workbench
First place rightfully belongs to the Workbench tool (developed by Sun Systems/Oracle), which can run on Microsoft Windows, Mac OS X and Linux platforms. Workbench combines database development and administration and is the successor to DBDesigner4.MySQL Workbench distributed under a free license - Community Edition and with an annual paid subscription - Standard Edition. The latter includes additional features that can significantly improve the productivity of both developers and database administrators.
Cost - free.
What makes Workbench popular?
- the ability to present the database model in graphical form, as well as editing data in a table;
- the presence of a simple and functional mechanism for creating relationships between table fields, among which a “many-to-many” relationship is implemented with the ability to create a relationship table;
- the Reverse Engineering function allows you to restore the structure of tables and relationships from the one that was implemented earlier and stored on the database server;
- the presence of an SQL query editor, which makes it possible, when sent to the server, to receive a response in tabular form and other features.
2. Navicat
Second place Navicat(developed by PremiumSoft CyberTech Ltd) is a tool for developing and administering databases that runs on any MySQL server starting from version 3.21. For MySQL, Navicat is available to run on Microsoft Windows, Mac OS X and Linux platforms.
Product prices range from $199 to $379.
What makes Navicat popular?
- Availability of a visual query designer;
- the ability to import, export and backup data;
- ability to create reports;
- SSH and HTTP tunneling;
- migration and synchronization of data and structure;
- task scheduling tool and other features.
3. PHPMyAdmin
PHPMyAdmin is a free open source application designed for administering the MySQL DBMS. PHPMyAdmin is a web interface with which you can administer the MySQL server, run commands and view the contents of tables and databases through a browser.
Cost - free.
What makes PHPMyAdmin popular?
- the ability to manage the MySQL DBMS without directly entering SQL commands;
- as a control panel PHPMyAdmin provides the ability to administer dedicated databases;
- intensive development;
- the ability to integrate PHPMyAdmin into your own developments thanks to the GNU General Public License and other features.
4. dbForge Studio for MySQL
dbForge Studio for MySQL- a tool of interest to both MySQL users and database developers. With its help, you can easily automate routine work and save time. Today, dbForge Studio for MySQL is available in three editions: Express, Standard and Professional, which allows you to choose the tool that you need. You can use dbForge Studio for MySQL in both the commercial and free versions.
There are both free and paid versions, with the latter priced at $49.95 (standard edition) and $99.99 (professional edition).
What makes dbForge Studio popular?
- availability of funds for centralized administration;
- database comparison tools;
- visual query profiler;
- the ability to manage user privileges;
- availability of a Database Designer, which allows you to build visual diagrams;
- improved work with database projects and other features.
5.HeidiSQL
HeidiSQL is a free database management tool. A worthy alternative to PHPMyAdmin, which allows you to create and edit tables, views, triggers, procedures, as well as view and edit data. HeidiSQL also provides the ability to export data both to an SQL file and to the clipboard on other servers.
Cost - free.
What makes HeidiSQL popular?
- the ability to connect to the server using the command line;
- the ability to batch optimize and restore tables;
- the ability to edit columns, indexes and foreign keys of tables, edit the body and parameters of SQL procedures, triggers, etc.;
- easy formatting of unordered SQL;
- table synchronization between different databases and other features.
6. SQL Maestro for MySQL
SQL Maestro for MySQL- a tool for administration, development and management of the most popular DBMS. A convenient graphical interface makes it possible to execute SQL queries and scripts, manage user privileges, export and create backup copies of data.
Depending on the selected license and use case, the cost of this tool ranges from $99 to $1,949.
What makes SQL Maestro for MySQL popular?
- support for MySQL server versions from version 3.23;
- Availability of a database designer;
- the ability to edit, group, sort and filter data;
- visual query builder;
- SSH and HTTP tunneling;
- BLOB editor and other features.
7. EMS SQL Manager for MySQL
EMS SQL Manager for MySQL is a database development and administration tool that supports various MySQL features and works with all versions of MySQL older than 3.23. With its help, you have the ability to visually edit, import and export databases, execute SQL scripts, manage user privileges, visually design MySQL databases.
There is a paid and free version of the application. The latter has a number of functional limitations. The cost of the paid version varies between 95 – 245 US dollars.
What makes EMS SQL Manager for MySQL popular?
- UTF8 data support;
- easy management of various MySQL objects;
- compatible with all versions from 3.23 to 6.0 inclusive;
- availability of graphic and text tools for generating queries;
- SSH and HTTP tunneling;
- convenient report designer and other features.
8. SQLyog
SQLyog is one of the most powerful tools that combines the capabilities of MySQL Administrator, PHPMyAdmin and some other tools for database administration and development. SQLyog runs on Microsoft Windows, Windows NT platforms. and Linux using Wine.
There are both free and paid versions of SQLyog available. The paid version costs from $99 to $1,499 (varies depending on the number of users and license, with or without support).
What makes SQLyog popular?
- convenient query builder;
- possibility of data synchronization;
- Unicode support;
- SSH and HTTP, HTTPS tunneling;
- “smart” auto-shutdown;
- intelligent code completion and other features.
9. DBTools Manager
DBTools Manager- a data management application with built-in support for MySQL, PostgreSQL, MSAccess, MSSQL Server, Oracle and other databases. Supported platforms: Windows 2000, XP, Vista, 7.
DBTools Manager is available in free (Standard) and paid versions (Enterprise). Cost is $69.90 per license, with discounts available when purchasing multiple licenses.
You can learn more about and purchase DBTools Manager here
Let's create a simple database application that displays information from the "Tourists" table and the "Tourist Information" table record from the Microsoft Access database associated with the current record of the "Tourists" table.
To do this, let's create an empty Windows application. Environment Appearance
development is shown in Figure 39.
Rice. 39. Blank application
Figure 39 highlights the “Data” component group, which contains components for accessing and manipulating data.
The binding of database data to the form is carried out by the “Binding Source” component. Let's transfer it to the form. After placing it on the form, the development environment takes the following form (Fig. 40).
Rice. 40. Binding Source component on the form
The component is not visual, so it is displayed in an additional panel. The main property of the component is the DataSource property, which points to the data source. By default, the property is empty, so you need to configure its value. When you select this property in the properties window, the following window appears (Fig. 41).
Rice. 41. List of data sources
The list is currently empty, so you need to create a new data source by selecting the Add Project Data Source command to create a new data source and connect to it. The following dialog box appears (Fig. 42).
Rice. 42. List of data sources
This dialog provides the following choice of data sources:
Database - Database;
Service - A service is some service that provides data. Most often this is a Web service;
Object - Object for selecting an object that will generate data and objects to work with it.
In our case, you need to select the “Database” item. A window for selecting a data connection appears (Fig. 43).
Rice. 43. Selecting a data connection
The purpose of this dialog is to create a connection string that will describe the connection parameters for the ADO engine, such as the database type, its location, user names, security features, etc.
The dialog drop-down list contains all previously created connections. If the required connection is not in the list, then you should use the “New connection” button. Pressing the button causes the following dialog to appear (Fig. 44).
In this dialog, you select the data source type (in this case, Microsoft Access), the database name (in this case, the name and location of the database file), and the username and password used to connect to the database. The "Advanced" button allows you to set a large number of parameters related to various parts of the ADO engine. Using the “Test Connection” button will ensure that the entered parameters are correct and the connection is working.
Rice. 44. Creating a new connection
The last step of the dialogue is to select those tables or other database objects that are needed in this data source. The selection window is shown in Figure 45.
Rice. 45. Selecting the necessary tables
In this window, the “Tourists” and “Tourist Information” tables are selected. Since no objects other than tables were created in the database, only tables are displayed in Figure 45. This completes the creation of the data source. After clicking the “Finish” button, a DataSet component appears next to the BindingSource component on the form.
Now the data connected above needs to be displayed on the form. The simplest way to display data is to use the DataGridView component from the Data component group. The component is visual and looks like this on the form (Fig. 46).
Rice. 46. DataGridView Component
The component settings window immediately appears, which determines its data editing capabilities: “Enable Adding”, “Enable Editing”, “Enable Deleting”; the ability to change the sequence of columns: “Enable the ability to change the order of columns” (“Enable Column Reordering”); as well as the ability to be attached to the parent container.
In order for the component to display data, you must select a data source in the drop-down list. Selecting the drop-down list causes the following dialog to appear (Fig. 47).
Rice. 47. Selecting a data source for DataGridView
In this case, we chose the “Tourists” table as the data source. This selection changes the screen form as follows (Fig. 48).
Rice. 48. The DataGridView component displays the table structure
The figure shows that another BindingSource component has appeared and a TableAdapter component that works with the “Tourists” table. Please note that in design-time or during the development process, the data from the table is not displayed.
Now you need to display the data from the linked table “Tourist Information”. To do this, place another DataGridView component on the form and select the following as a data source (Fig. 49).
Rice. 49. Selecting a data source for the second DataGridView
Here, the data source is not the “Tourist Information” table itself, but the connection (Binding Source) between the “Tourists” and “Tourist Information” tables. This selection ensures that only those rows from the Tourist Information table that are associated with the current row in the Tourists table are selected. This choice also ensures that associated data is updated and deleted correctly. The operation of the resulting application is shown in Figure 50.
Rice. 50. Database application at work
Navigating through data using the arrow keys is awkward. To simplify data navigation, there is a BindingNavigator component. Let's place it on the form (Fig. 51).
Rice. 51. BindingNavigator component on the form
This component allows you to navigate between table records, add and delete table rows. The functionality and appearance of the component can be customized because it is a ToolStripContainer menu strip.
The property that determines the table through which navigation is performed is the BindingSource property. Let's set the value of this property to "touristsBindingSource". In operation, the component looks like this (Fig. 52).
Rice. 52. BindingNavigator component at work
Editing data in the cells of the DataGridView component with appropriate settings is possible, but it is inconvenient and not rational. In particular, it is difficult to check entered values for errors. Therefore, for the “Tourists” table we will make a screen form that allows you to display data in TextBox components and edit them. To do this, place a container of the Panel type on the form, and on it three TextBox components as follows (Fig. 53).
Rice. 53. Screen panel for editing entries in the “Tourists” table
Now you need to bind the TextBox components to the corresponding fields of the “Tourists” table. To do this, we use the property from the DataBindings - Advanced group, shown in Figure 54.
Rice. 54. Property “DataBindings - Advanced”
Selecting this property leads to the appearance of the dialog shown in Figure 55. This dialog allows you not only to bind data, but also to set an event within which the data will be updated, as well as formatting the data when outputting it.
For the top TextBox component, in the Binding drop-down list, select “touristsBmdmgSource” as the data source and the source field as “Last Name”. For the middle and bottom TextBox components, select the same data source and the “Name” and “Patronymic” fields, respectively.
The developed application in operation looks like this (Fig. 56).
Rice. 55. Dialog window for the “DataBindings - Advanced” property
Rice. 56. Data binding to visual components
However, when changes are made, all new data remains only on the form. They are not saved in the database, and when the application is called again, of course, they will be missing. This occurs because the data was loaded into a DataSet object, which is an in-memory copy of the table. All actions are performed with this copy. In order for changes to be reflected in the database, you must execute the Update method of the TableAdapter class. Thus, in the application being developed, it is necessary to place the “Update” button and write the following program code in the Click event handler:
touristsTableAdapterUpdate(bDTur_firmDataSet); information_about_touristsTableAdapter.Update(bDTur_firmDataSet);
This code updates information in the Tourists and Tourist Information tables provided by the data source. Note that this method is overloaded, and its variants allow you to update both an individual table row and a group of rows.
- Tutorial
- Recovery Mode
Hi all! My name is Oleg and I am an amateur Android programmer. An amateur because at the moment I am making money by programming in a completely different direction. And this is a hobby to which I devote my free time. Unfortunately, I don’t know any Android programmers and I get all my basic knowledge either from books or from the Internet. In all the books and articles on the Internet that I read, creating a database for an application devotes very little space and, in fact, the entire description comes down to creating a class that is an inheritor SQLiteOpenHelper and subsequent implementation of SQL code into Java code. Apart from the fact that we get poorly readable code (and if more than 10 tables appear in our application, then remembering all these relationships between tables is hell), then in principle we can live, of course, but somehow we don’t want to.
I forgot to say the most important thing, you can say that this is my first attempt at writing here. And so let's go.
About the eternal question: why?
To be honest, I honestly don’t know why books and articles on programming for Android don’t describe tools for designing database architecture and some patterns for working with databases at the stage of their creation. It would seem easy to add just a couple of pages to a book or write a separate article (as I am doing now) - but no. In this article, I will briefly go through the tools that I use in my work and in more detail about the code that is responsible for the initial creation of the database, which from my point of view looks more readable and convenient.
If our application has more than 5 tables, then it would be a good idea to use some tool for visual design of the database architecture. Since this is a hobby for me, I use a completely free tool called Oracle SQL Developer Data Modeler (you can download it).
This program allows you to visually draw tables and build relationships with them. Many database architecture design mistakes can be avoided with this design approach (I’m already telling you this as a professional database programmer). It looks something like this:
Having designed the architecture itself, we proceed to the more tedious part, which consists in creating SQL code to create tables. To help with this issue, I already use a tool called SQLiteStudio (which in turn can be downloaded here).
This tool is an analogue of such well-known products as SQL Naviagator, Toad etc. But as the name suggests, it is designed to work with SQLite. It allows you to visually create a database and get the DDL code of the created tables. By the way, it also allows you to create Views, which you can also use in your application if you wish. I don’t know how correct it is to use views in Android programs, but I used them in one of my applications.
As a matter of fact, I no longer use any third-party tools, and then the magic begins with Android Studio. As I wrote above, if we start implementing SQL code into Java code, the output we will get is poorly readable, and therefore poorly extensible, code. Therefore, I put all SQL instructions into external files that are located in my directory assets. In Android Studio it looks something like this:
About the db and data directories
Inside the directory assets I created two directories db_01 And data_01. The numbers in the directory names correspond to the version number of my database with which I am working. In the directory db I store the SQL scripts for creating tables themselves. And in the directory data The data necessary for the initial filling of tables is stored.
Now let's look at the code inside my DBHelper which I use in my projects. First, the class variables and the constructor (no surprises here):
Private static final String TAG = "RoadMap4.DBHelper";
String mDb = "db_"; String mData = "data_"; Context mContext;
int mVersion;
and here it gets more interesting:
@Override public void onCreate(SQLiteDatabase db) ( ArrayList
Logically it is divided into two cycles, in the first cycle I receive a list of SQL instructions for creating a database and then execute them, in the second cycle I already fill the previously created tables with initial data. So, step one:
Private ArrayList
Filling out the tables makes it more and more fun. My tables are filled not only with hard-coded values, but also with values from resources and UUID keys (I hope to someday come to a network version of my program so that my users can work with shared data). The structure of the files with the initial data looks like this:
Despite the fact that my files have the sql extension, there is no sql code inside, but something like this:
Priorities
pri_id:UUID:UUID
pri_name:string:normal
pri_color:color:colorGreen
pri_default:int:1
priorities
pri_id:UUID:UUID
pri_object:string:object_task
pri_name:string:hold
pri_color:color:colorBlue
pri_default:int:0
priorities
pri_id:UUID:UUID
pri_object:string:object_task
pri_name:string:important
pri_color:color:colorRed
pri_default:int:0
priorities
pri_id:UUID:UUID
pri_name:string:normal
pri_color:color:colorGreen
pri_default:int:1
priorities
pri_id:UUID:UUID
pri_object:string:object_project
pri_name:string:hold
pri_color:color:colorBlue
pri_default:int:0
priorities
pri_id:UUID:UUID
pri_object:string:object_project
pri_name:string:important
pri_color:color:colorRed
pri_default:int:0
The file structure is like this: I make a function call split(":") in relation to a line and if I get that its size is 1, then this means the name of the table where the data should be written. Otherwise it is the data itself. The first field is the name of the field in the table. The second field is the type by which I determine what I need to write in this very field. If it's a UUID, that means I need to generate a unique UUID value. If string means I need to extract a string value from the resources. If color, then again, you need to extract the color code from the resources. If int or text, then I simply convert this value to int or String without any physical movements. The code itself looks like this:
@Override public void onCreate(SQLiteDatabase db) ( ArrayList
