This part is for users new to SQLite, or to SQL at all. It covers in details visually building database and a table. This helps for developers never done this before.
Browser
Although SQLite databases are usually created programmatically, it helps to have some SQL-browser-usage experience. If you are new to the concept of SQL databases, it may be helpful to create and modify a bit some databases manually. To do that, download (or use any) SQLite browser, such as SQLite database browser downloaded for free here. Samples and screenshots in this part are taken using this browser. (New to SQL?)
Basic4ppc SQL viewer
As a good source code example of the Basic4ppc usage, you may also download Basic4ppc SQL viewer, written in Basic4ppc. This tool shows the contents of an SQLite database and is suitable for the device.
SQLite datatypes
Although not intended to cover all syntax and functions of SQLite, this reference will not be complete without mentioning the basic SQLite datatypes. The method of assigning types to data is very dynamic and flexible, thus being ideal for Basic4ppc. Basically you can place any data type to any column. Nevertheless, the following types can be declared along with the column name when creating the table:
-
TEXT - textual data.
-
NUMERIC - numbers that may include decimal point. The data is stored in the most suitable way.
-
INTEGER - holds integers.
-
REAL - real numbers with decimal point.
-
NONE - data is kept exactly as inserted.
-
BLOB - although not really a data type, BLOB is a unique way of storing data without knowing what it contains. A data stored as BLOB is stored exactly as it is read - as a set of bytes. This strong type is usually used to store images, music and streams of bytes.
SQLite type conversion system is a thing you may want to learn more about here.
Create a Dabebase Manually
It is a good idea to get to know SQLite by manually creating a database.
Open the browser. This screen appears:
Choose New Database from the File menu.
SQLite databases are kept in a single file. This simplifies portability a lot, making it comfortable for use with portable devices. Name your file "DemoDB1" and click Save.
The "Create Table" dialog box appears. A database must contain at least one table to have any meaning. We will create a quick phonebook table example. Name your table t_phoneBook as shown below. We will next add some fields to the table.
Now click the Add button. The "Add database field" dialog appears. Name the field ID and choode Numeric as the field type. This will be used as a uniqe ID for each record in the database.
Click Create. Continue adding the fields Name and Number, until you've added the fields as below:
Click Create to create your table.
A new line appears under the "Database Structure" tab. This line shows the SQL statement required to create the table. The browser does this for you automatically.
Since we wanted the ID field to be a uniqu index, click the Create Index button, circled in red below, and enter the details shown below.
Make sure you choose Not allowed as the Duplicate values property's value.
Press Create.
Note how this had changed the SQL required to define your table:
Now lets browse the table. Click the Browse Data tab.
A table appears with columns ID, Name and Number. These are the fields you have created a minute earlier. Click the New Record button to start adding data to your table:
Double click the empty cell under the Name column header. The Edit database cell dialog appears. Enter a name, John Smith in this example, and click Apply Changes.
The same way, enter 1 as the first record's ID, and continue adding data as follows:
Now, we will take a look at some basic database operations. Start with Searching a record. Press the Find button, and fill the details as shown below. Click Search when done. The browser finds a list of the matching records.
Next, exit the Find dialog box, click the second row of your table and click Delete Record. The row disappears and we are left with the other two.
Last thing to check, we will look at the Execute SQL tab. This powerful tab allows you to execute arbitrary SQL statements, thus manipulating the database as if from within your code. It may be useful later, when you will need to check your SQL statements visually. Click this tab, and enter the SQL statement:
SELECT * from t_phoneBook
WHERE Name="John Smith"
and click Execute Qurey. A list containing only one row (the only one left after we deleted the second "John Smith" row) appears.
Next page: Sample program - discussion and design