Archive for the ‘Database’ Category

Database

Posted: 2017-01-17 in 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

 



Database

Posted: 2017-01-17 in Database

Q:
Is there AES 256 encryption possible for the Lianja databases?
A:
No


Q:
If I close it before exporting it and after I reopen it, it exports the table but it losses the relationship with the parent and I need to restart the application to recover it.
A:
You can save and restore the state of any active cursors using:

Code:
save datasession
...
restore datasession

So place these commands at the top and bottom of your function. These operate on native Lianja tables and virtual tables. After save datasession all tables will be closed. Restore datasession will reopen them and position on the records which were active prior to the save datasession. It will also restore the active indexes and relationships between the sections.



 

Database

Posted: 2015-11-07 in Database

Q:
have problems opening two (or more databases) at the same time. 
OPEN DATABASE db1 
OPEN DATABASE db2 
Then db1 is closed again.
A:
In Lianja you can only have one database open at a time. This is by design.
you can however reference tables in multiple databases like this:

Code:
use databasename!tablename
select * from databasename!tablename


In Lianja you work against a snapshot of the live data as you state, then in final release the “data source” is a dsn that is used to connect to the live data at runtime.