6. Working with Databases

Working with WebDNA database

Let's continue with the all-important databases. What is this "database" part of WebDNA? Well, WebDNA handles its own built-in database system. These databases reside in RAM, and are extremely fast, much much faster than disk-based databases. The RAM-based WebDNA databases are loaded from simple tab-delimited text files on disk and written back to disk, so there is no risk to lose content if the server crashes.

Are you familiar with an Excel spreadsheet? There are colums, describing the spreadsheet content (fields), and rows (records), with the data. A WebDNA database is structured the same way, except that it is a text file. Even so, you can open and edit a WebDNA database with Excel. Or FileMaker. Or any database. Or any text editor. Or anything.

Unlike Excel, it is not necessary to specify what kind of content you write inside the database cells: no "string", "date", "number". It is just a text file, and you have full control over your content. No assumptions are made; you tell WebDNA when a date is a date, and a number is a number, so there are no disappearing leading zeroes, for instance (much of the US Northeast have postal codes beginning with 0).

Here is the structure of a very small database (but you can get millions of records should you want it):

ID
NAME
NUMBER
COLOR
AGE
1
Peter
534264
yellow
38
2
John
756233
green
42


Let's name it "database1.db". It will show like this if you open it with a text editor (I use -tab- to indicate a tab, since the columns are tab-delimited):

--------------------------------------------------
ID NAME NUMBER COLOR AGE
1 Peter 534264 yellow 38
2 John 756233 green 42

--------------------------------------------------


The first line contains the field names. The following lines are the individual records, with data in the fields' corresponding columns.

Let's add a new line. Make a new page, "databasetest.dna":
<!--HAS_WEBDNA_TAGS-->
[append db=database1.db&autonumber=ID]name=Scott[!]
[/!]&numberT5194[!]
[/!]&color=violet[!]
[/!]&age#[/append]

which would mean: append to database database1.db a new row (record), with a unique ID number, where the name is "Scott" and the number is "545194" and the colour is "violet" and the age is "23".

Now, each time you hit http://127.0.0.1/databasetest.dna you will have a new line, each time with a different ID. The database now looks like

--------------------------------------------------
ID NAME NUMBER COLOR AGE
1 Peter 534264 yellow 38
2 John 756233 green 42
3 Scott 545194 violet 23
4 Scott 545194 violet 23

--------------------------------------------------


Oops! we now have two lines with "Scott" because you hit the file twice:

Let's delete the extra line
[delete db=database1.db&eqIDdatarq=4]

which would mean "delete from database1.db any entry with an ID equal 4"

Though the databases will work without a unique ID field, it is a very good habit to include a unique field (could be called anything -- ID, sku, MemberID). Imagine how we could delete this specific record without the ID?
[delete db=database1.db&eqnamedatarq=Scott]

with this line, we would delete both lines 3 and 4.

However, there is a way to avoid duplicate records altogether. A very similar context is [replace]:

[replace db=database1.db&eqnamedatarq=Scott&append=t&autonumber=ID][!]
[/!]&numberT5194[!]
[/!]&color=violet[!]
[/!]&age#[/replace]


This will append the data if it is not already there, but only update existing data if it IS already there. (Of course, if you have more than one Scott, it will update them all. Here is the perfect example of why you would use a unique ID to pick out specific records.)

NOTE: In a real-world situation, you would not hard-code all of your data in an append or replace as in these examples. Instead, you would submit a form from the previous page (see step 3) to a page with WebDNA that would look like this:

[replace db=database1.db&eqnamedatarq=[name]&append=t&autonumber=ID][!]
[/!]&number=[number][!]
[/!]&color=[color][!]
[/!]&age=[age][/replace]


image
>>> Next page... Christophe Billiottet

DOWNLOAD WEBDNA NOW!

Top Articles:

Related Readings:

3. Passing Data from a Form

Passing data from a form and retrieving it using WebDNA...

1. Learn WebDNA Programming

Learning WebDNA programming: the very basics...

WebDNA video training - WebDNA Syntax video

...

Kendo UI

This is a Code Library built with WebDNA...

Introduction

A short introduction to WebDNA programming for absolute beginner...

2. Conditional Programming

Conditional programming with WebDNA...