Database

When you create a Lianja database, e.g. mydatabase, a mydatabase sub-directory is created in the Lianja data directory (default: C:\lianja\data\).

The database catalog,mydatabase.cat, is created in this sub-directory along with its associated memo (mydatabase.cam) and index (mydatabase.cax) files. The database catalog contains information about its tables and their associated files.

When you create a new table in your database in the Data Workspace, see here for the Guide to the Data Workspace video, or using the CREATE TABLE command, the table is created in the database’s subdirectory (C:\lianja\data\mydatabase\) and its information is added to the database catalog file.

In the Lianja/VFP Command Window in the App Inspector or the Console workspace you can use any of the following commands to get more information about your open database (use OPEN DATABASE if the database is not already open).

Code:

dir
list database
list files

Adding a table to a Database

If you have an existing Lianja table, you can also add this to your database. When you add a table to a database, the physical table and its associated files are not moved or copied: the table’s information (location, indexes etc.) is added to the database catalog file.

You can add a table to a database in the Data workspace from the Tables Additional Commands (or right-click context menu).

In the example below, the attachments table in the southwind database is added to the mydatabase database.
Note that the attachments table is now in the mydatabase database and can be accessed as normal, but its physical location is still C:\lianja\data\southwind\attachments.dbf.

Similarly, if you drag ‘n’ drop a table from Windows Explorer to the Tables panel in the Sidebar, the physical location of the table does not change.



These are the equivalent of using the SQL ADD TABLE command.

In the example below, the command:

Code:
add table c:\lianja\data\southwind\products

adds the products table to the open mydatabase database but products.dbf is referenced from C:\lianja\data\southwind\products.dbf.

In contrast, in the example above, the command:

Code:
copy file C:\lianja\data\southwind\employees.* C:\lianja\data\mydatabase\employees.*

physically copies all files from the southwind database directory with the basename employees to their equivalent in the mydatabase database directory.

To add employees to the mydatabase catalog, use the REBUILD DATABASE command.

Similarly, if you physically copy or move the files at the operating system level, you can then use REBUILD DATABASE to update the database catalog.

Deployment

Important: For deployment, the tables must be physically located in the database’s directory, so use the COPY FILE command or operating system copy/move along with REBUILD DATABASE prior to deployment.


In Lianja v3.0, a database can now have the following event hooks which reside in the database directory. They can be edited in the “Data” workspace of the App Builder.

They are active in desktop/web/mobile/sqlserver and work with VTs and native tables.

You can enable|disable database events with SET DBCEVENTS ON|OFF.

These events can be used to provide audit trails and/or modify the JSON prior to it being committed. Data is base64 encoded JSON.

For native tables the cJSON string is empty and all you need to do is look at the current record buffer and reference the fields. Again these can be changed before they are committed.

Note that if the JSON string is too long then “@” will be passed as a parameter and the jsonDataString() function can be used to access the long JSON data string.

dbc_beforeinsert.prg/.dbo (cDatabase, cBaseTable, cJSON)
dbc_afterinsert.prg/.dbo (cDatabase, cBaseTable, cJSON)

dbc_beforeupdate.prg/.dbo (cDatabase, cBaseTable, cJSON)
dbc_afterupdate.prg/.dbo (cDatabase, cBaseTable, cJSON)

dbc_afterdelete.prg/.dbo (cDatabase, cBaseTable, cJSON)
dbc_afterdelete.prg/.dbo (cDatabase, cBaseTable, cJSON)

If any of the dbc_beforexxx procs returns .f. then the operation is not performed.

If the dbc_library.prg/.dbo exists when a database is opened then the procedure library is loaded as a system library. This allows you to have business procs loaded when the database is open.

Once particular use of these hooks would be to email notifications of changes.


Q:
What tool does one use to create a Lianja Database with tables please?
A:
You can also use the ‘+’ New button at the bottom of the database tab to create a new database.

Have another look at the Guide to the Data Workspace video. The first part does deal with existing databases and importing, but from about 3:30 in it starts with creating a new database, creating tables and adding fields to a table. Tables are not related in the database itself, but using the Relationship Builder in the Pages Workspace.
A2:
I like to create my tables by hand.
For example, in the console, you can run the following.

Code:
create database Bruce
open database Bruce
dir 

create table table1(name char(10), age int)
use table1 current
dir

insert into table1 (name, age) values("Herb",45)
select * from Bruce!table1

 



Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s