Working with SQLiteStudio

Slashdot it! Delicious Share on Facebook Tweet! Digg!

Manual Data Entry

To enter or edit data, click on Data . You can choose between Grid view (Figure 5) and Form view .

Figure 5: Data entry in Grid view.

This window also contains a toolbar with the following functions (from left to right) refresh view , insert a new row (plus sign), delete row (minus sign),commit changes (checkmark), and rollback changes (X).

Do not click on a column with AutoIncrement when entering data – in our case, do not click on snum . The field must contain the default value of NULL in italics; otherwise, the input will not work!

The following examples relate to a small database with a total of three tables for managing phone and fax numbers, email addresses, and so on. To do this, we need to create the tables, types , and links . The information necessary for filling is located:

Figure 6: Structure of types.
Figure 7: Structure of table connections.
Figure 8: Creating connections.type with a foreign key.

Table 2

Types

typelong typeshort
Phone landline t
SFTP account s
Mobile m
Fax f
Email e

Table 3

Connections

snum type info
2 m 01231234567
3 t 0987 654321
2 f 0864 975321
3 e mail@example.tld
1 m 0137 246890
1 t 09751 23468
The content of the field order is created automatically.

In this example, foreign keys are used for the type of connection data in connections.type . To avoid the need to continually write out the words Phone, Fax, or Cell in the connection data, there is a shortcut for each category.

To make these shortcuts binding for the entries in connections.type , use the types.typeshort as a foreign key. Figure 8 shows how to create the field. As the Constraint , select the Foreign Key and then click Configure in the same line. This opens another dialog in which you select the table (types ) and the column typeshort (primary key of types ). In the Reactions section, you can make some additional fine adjustments if needed.

You the same with connections.snum to prevent orphaned entries in staff .

Next, populate the tables with sample data. When filling out the connection.type , SQLiteStudio suggests using the matching entries for the fields with the foreign keys if you double-click the box and select the arrow that then appears.

Sorting, Filtering, Searching

In the Grid view of a table, you can double-click on the header of the desired column to sort the data. To search, first select how the search will take place. To do this, press the small arrow next to the funnel symbol next to the search field in the Grid view toolbar. The following methods are available:

  • Filter by text : SQLiteStudio searches all table fields for the search term. A "T" will appear in the filter icon.
  • Filter by Regular Expression : This setting supports searching by pattern. An "R" appears in the filter icon.
  • Filter by SQL expression : SQLiteStudio expects the value to look for in a column. An "S" in the filter icon reflects this setting.

Figure 9 shows a search for all table entries in connections that include a cellphone number. The search was carried out in SQL mode. Clicking on the round X symbol in the search window clears the display of search results.

Figure 9: A search in SQL mode for cellphone connection data.

Buy this article as PDF

Express-Checkout as PDF

Pages: 7

Price $0.99
(incl. VAT)

Buy Ubuntu User

SINGLE ISSUES
 
SUBSCRIPTIONS
 
TABLET & SMARTPHONE APPS
Get it on Google Play

US / Canada

Get it on Google Play

UK / Australia

Related content

  • Getting started with LibreOffice Base

    Creating database applications with wizards and graphic editors – without SQL and programming – that's what the LibreOffice program Base, modeled after Microsoft Access, is all about.

  • Visualizing complex structures using Graphviz

    Network plans, nested dependencies, or binary trees – with Graphviz, you can visualize complex relationships in a simple way.

  • LibreOffice Base

    A database without a search function is like a car without an engine. The second part of our Base series is dedicated to implementing a database search.

  • Convenient private cloud with Seafile

    After the Prism and Tempora revelations, the writing's on the wall: Your data stored on public clouds isn't safe from the clutches of security agencies. Help can come only from a self-administered solution – yet Seafile provides a simple and ingenious approach.

  • Installing and testing Nextcloud

    Leading ownCloud developers, including the project founder Frank Karlitschek, became dissatisfied with the direction of the project, so they started Nextcloud, a fork of the code and a new company. The goal is to create a better balance among the company, clients, and users. We take a look at how Nextcloud is faring.