WebDNA Software | 6. Working with Databases

6. Working with Databases

Working with WebDNA database

numero = 158
interpreted = N
texte = 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    AGE1     Peter    534264    yellow    382     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[!][/!]&number=545194[!][/!]&color=violet[!][/!]&age=23[/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     AGE1     Peter    534264    yellow    382     John     756233    green     423     Scott    545194    violet    234     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][!][/!]&number=545194[!][/!]&color=violet[!][/!]&age=23[/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]

>>> Next page... 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[!]
[/!]&number=545194[!]
[/!]&color=violet[!]
[/!]&age=23[/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][!]
[/!]&number=545194[!]
[/!]&color=violet[!]
[/!]&age=23[/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]

>>> Next page... Christophe Billiottet

DOWNLOAD WEBDNA NOW!

6. Working with Databases

Top Articles:

Related Readings: