Database catalog [examples]

Enable files to be automatically added to a database catalog
SET AUTOCATALOG
close databases
set autocatalog to southwind
set autocatalog on
do myapp

Catalog names available in the database
SYSCATALOGS
SELECT * FROM syscatalogs

https://www.lianja.com/doc/index.php/SYSCATALOGS


Table columns available in the catalog
SYSCOLUMNS
SELECT * FROM syscolumns;
WHERE type_name = 'DATETIME'

https://www.lianja.com/doc/index.php/SYSCOLUMNS


Access rights for each table available in the catalog
SYSTABLEPRIVILAGES
SELECT * FROM systableprivileges

https://www.lianja.com/doc/index.php/SYSTABLEPRIVILEGES


Tables available in the catalog
SYSTABLES
SELECT * FROM systables;
WHERE table_type = 'TABLE'

https://www.lianja.com/doc/index.php/SYSTABLES


User-defined types (UDTs) defined in the schema
SYSUDTS

 

SELECT * FROM sysudts

https://www.lianja.com/doc/index.php/SYSUDTS


select * from syscolumns;
  where table_cat = "southwind" and table_name = "orders"
select * from sysindexinfo;
  where table_cat = "southwind" and table_name = "orders"

https://www.lianja.com/doc/index.php/Using_Recital_SQL

https://www.lianja.com/doc/index.php/Using_Lianja_SQL


Advertisements

Database [examples]

Define database metadata
ALTER DATABASE
// Name value pairs for use with the MetaData Editor
alter database southwind metadata "company=Lianja Inc.;contact=sales@lianja.com"
open database southwind
? databaseMetaData() // --> 'company=Lianja Inc.;contact=sales@lianja.com'
// Assignments are not cumulative
alter database southwind metadata "purpose=Sample Database"
open database southwind
? databaseMetaData() // --> 'purpose=Sample Database'
// Specifying an empty string clears the metadata
alter database southwind metadata ""
open database southwind
? databaseMetaData() // --> ''
 
 
// JSON string
alter database sw2 metadata '{"purpose":"Documentation Test Database","owner":"Yvonne Milne"}'
open database sw2
? databasemetadata() // --> '{"purpose":"Documentation Test Database","owner":"Yvonne Milne"}'

https://www.lianja.com/doc/index.php/ALTER_DATABASE


Sets the specified database as the default database for subsequent operations or SQL queries
OPEN DATABASE
OPEN DATABASE hr EXCLUSIVE
SELECT staff_no, lastname from staff
CLOSE DATABASES

https://www.lianja.com/doc/index.php/OPEN_DATABASE


Closes the currently open database
CLOSE DATABASE
OPEN DATABASE hr EXCLUSIVE
SELECT staff_no, lastname from staff
CLOSE DATABASES

Copies the specified database to a new database
COPY DATABASE
copy database southwind to temp if exists

Display table information about the active database
DISPLAY TABLES
OPEN DATABASE southwind
DISPLAY TABLES

https://www.lianja.com/doc/index.php/DISPLAY_TABLES


Creates a database with the specified name
CREATE DATABASE
CREATE DATABASE hr
CREATE TABLE staff;
  (staff_no CHAR(6) NOT NULL UNIQUE,;
  lastname CHAR(15) NOT NULL,;
  firstname CHAR(10),;
  hiredate DATE,;
  location CHAR(15),;
  supervisor CHAR(6),;
  salary DECIMAL(6,0),;
  picture VARBINARY,;
  history LONG VARCHAR,;
  commission DECIMAL(4,1))

Removes the specified database and all its tables
DROP DATABASE
DROP DATABASE temp

List information about the active database
LIST DATABASE
OPEN DATABASE southwind
LIST DATABASE

List the currently available databases
LIST DATABASES
list databases

Packs each table in the active database or packs the catalog and rebuilds the catalog index tags for a specified database
PACK DATABASE
open database southwind
pack database
close databases
pack database southwind

Rebuilds a database catalog
REBUILD DATABASE
open database southwind
rebuild database
close database
rebuild database southwind
rebuild database ?

Rebuilds the indexes for each table in the active database or rebuilds the catalog index tags for a specified database
REINDEX DATABASE
open database southwind
reindex database
close databases
reindex database southwind

Display information about the active database
DISPLAY DATABASE
open database southwind
display database to file sw_info

Display the currently available databases
DISPLAY DATABASES
display databases

https://www.lianja.com/doc/index.php/DISPLAY_DATABASES


Display the currently available databases 
DISPLAY SCHEMAS
display schemas

https://www.lianja.com/doc/index.php/DISPLAY_SCHEMAS


List the currently available databases
LIST SCHEMAS
list schemas

Display the complete status of the session
LIST STATUS
open database southwind
list status

Display the complete status of the session
DISPLAY STATUS
display status

https://www.lianja.com/doc/index.php/DISPLAY_STATUS


Name of the currently open database
DBC
open database hr
? dbc()
hr
close databases

https://www.lianja.com/doc/index.php/DBC()


List table information about the active database
LIST TABLES
OPEN DATABASE southwind
LIST TABLES

https://www.lianja.com/doc/index.php/LIST_TABLES


Add the specified table to the currently active database
ADD TABLE
CREATE TABLE freetable (freeid char(10))
open database southwind
ADD TABLE freetable

https://www.lianja.com/doc/index.php/ADD_TABLE


Check whether a database is open
DBUSED
open database hr exclusive
? dbused("hr")
.T.
close databases

https://www.lianja.com/doc/index.php/DBUSED()


Name of the currently open database
DATABASE
open database hr
? database()
hr
close databases

https://www.lianja.com/doc/index.php/DATABASE()


Place the name of the open database and its path into a dynamic array.
ADATABASES
OPEN DATABASE hr EXCLUSIVE
nDatabases = adatabases(aDBCNames)
CLOSE DATABASES

https://www.lianja.com/doc/index.php/ADATABASES()


Schema names available in the database
SYSSCHEMAS
SELECT * FROM sysschemas

https://www.lianja.com/doc/index.php/SYSSCHEMAS


Table types available in the database system
SYSTABLETYPES
SELECT * FROM systabletypes

https://www.lianja.com/doc/index.php/SYSTABLETYPES


Description of all data types supported by the database
SYSTYPEINFO
SELECT * FROM systypeinfo

https://www.lianja.com/doc/index.php/SYSTYPEINFO


Load table names from the current database into an array and return the number of tables
ATABLES
open database southwind
nTables = atables(table_arr)
? nTables

https://www.lianja.com/doc/index.php/ATABLES()


Check whether database exists
DATABASEEXISTS

 

if databaseexists("lianja")
    open database lianja
else
    create database lianja
endif

https://www.lianja.com/doc/index.php/DATABASEEXISTS()


create database test
drop database test

Opening a Lianja Database

open database southwind

https://www.lianja.com/doc/index.php/Using_Navigational_Data_Access_Commands_in_Recital


Open

open database southwind

close a database:

close database

https://www.lianja.com/doc/index.php/Working_with_data_in_Visual_FoxPro


create database test
drop database test

https://www.lianja.com/doc/index.php/Using_Lianja_SQL


use example order account_no
copy to new;
  rest;
  fields account_no, last_name;
  while account_no < "00050";
  for state = "MA"
use example
copy structure to new;
  fields account_no, last_name
use example
copy structure extended to stru_example;
  fields account_no, last_name
// modify records in stru_example table as required
create new from stru_example

https://www.lianja.com/doc/index.php/Using_Navigational_Data_Access_Commands_in_Lianja


Data dictionary [examples]

Create a new applications data dictionary from an xad file
CREATE DICTIONARY
use accounts
create dictionary from accounts.xad

Display the currently active dictionary
LIST DICTIONARY
use demo
list dictionary

Display the currently active dictionary
DISPLAY DICTIONARY
use demo
display dictionary

Copy dictionary attributes to a table file
COPY DICTIONARY
use accounts
copy dictionary to accounts

https://www.lianja.com/doc/index.php/COPY_DICTIONARY


Change or deactivate the active dictionary
SET DICTIONARY
set dictionary off
use customer.rdb
copy to cust2
use cust2
set dictionary to customer

https://www.lianja.com/doc/index.php/SET_DICTIONARY


Database

Q:

The Attributes of a field in a section has a checkbox for Inherit dictionary caption. Checked or unchecked doesn’t seem to change anything?

Where is this dictionary caption set?

Can a caption be a function? For example, I have a field with a caption “Include VAT”. I must change this for different countries, e.g. in Australia VAT is called GST, so the caption should be “Include GST”. What would be a good way to implement this.

Is there any capability to setup captions for different languages?

A:

Re ‘Inherit dictionary caption’:

When ‘Inherit dictionary rules’ is set to true at both section and formitem level, ‘Inherit dictionary caption’ determines whether this also applies to the formitem caption. If ‘Inherit dictionary caption’ is true, the formitem inherits the column’s field caption (create/modify column), if false, the formitem can have a caption customized in the UI.

A2:

To do this, I created an external table with the additional field definitions, then I created a procedure that reads the formfields of each section and dynamically edits caption, format, and other properties

Q3:

Is it possible to change the caption in the dictionary via programming? Or to see all the captions in a table to make it easier to change them?
Can the captions be imported from a file?

A3:

Close your App and you can do all that and more in the console using ALTER TABLE. https://www.lianja.com/doc/index.php/ALTER_TABLE

Note that you also have the “MetaData Editor” for additional customization. You could customize the UI based on a “locale” as metadata can be conditional as of Lianja 3.4.

A4:

this is a part of my code

this code

Code:
//////////////////////////////////////////////////////////////////////////////
proc dd_GetFormItem(oPage, oSection)
	//////////////////////////////////////////////////////////////////////////////
	private oFormItem, m_EDT, k
	
	// per ogni campo della sezione
	for k = 1 to oSection.count
		oFormitem = oSection.item(k)
		m_EDT = dd_GetItemEDT(oFormitem)
		
		m_field = oPage.id + "." + oSection.id + "." + oFormitem.id
		
		dd_SetFieldPropertyX(oFormitem)		
	endfor
endproc

loop trough the formItem of the “oSection”

then

Code:
//////////////////////////////////////////////////////////////////////////////
proc dd_SetFieldPropertyX(psField)
//////////////////////////////////////////////////////////////////////////////

	m_field = psField

        m_field.caption = m_caption

I’ve created a data structure, where, into an external table, I set some properies.
In this way, in a large application, if I need to change the properties of one field, I do not need to change all the single small Lianja application.

This is like to use the Meta type, but I’ve write this before that.

In this way I change:

Code:
m_field.DataMappingGet = []
m_field.DataMappingSet = []
m_field.autosuggest = []
m_field.autosuggestHeaders = []
m_field.choicelist = []
m_field.caption = []
m_field.tooltip = []

I compose the DataMappingGet and Set with lookup, based on my configuration table.

for example, if I configure a field as a enumerate:

Code:
m_field.DataMappingGet = [keylookup("vt_srv_dd_enumerated", "enumStore", "] + rtrim(m_EDT) + [" + _" + "{}", display)]
m_field.DataMappingSet = [keylookup("vt_srv_dd_enumerated", "enumDisplay", "] + rtrim(m_EDT) + [" + "_" + "{}", store)]
m_field.choicelist = [select display from vt_srv_dd_enumerated where edt = "] + rtrim(m_EDT) + ["]

This formfield properties:
Name:  2017-05-15 09_02_24-ABM.jpg Views: 25 Size:  37.8 KB

Q4:

What table do I alter?

For example:
In my table vcf!acc there is a field DESCRIPTN which has caption: The a/c description
Can you show me the Alter command to change the caption to My description ?

Name:  2017-05-12_10-59-18.jpg Views: 49 Size:  36.2 KB

Also, what is the command to browse all captions?

A5:

e.

Name:  Screen Shot 2017-05-12 at 12.30.04 PM.jpg Views: 49 Size:  89.1 KB

alter table vcf!acc modify constraint descriptn description “My Description”

select * from syscolumns
select * from syscolumnconstraints


Q:

The database I create does not appear in the list.
What I am doing is :
In the Data workspace under Database Names, right click and click on New.
When asked for database name, I enter a name.
Table workspace appears. When I go back to the Database workspace the newly created database is not there. I even tried by adding a table and closing/reopening Lianja. Still the database is not there. However the database folder with all files is present under c:\lianja\data

A:

If you happened to have created a Project, and that Project is selected, the database has to be added to the Project in order to be visible.

So, you can try this: go to the Projects tab, select the (default) project, and then click the Data tab, and see if your database is visible.

A2:

I closed the default project and the database now appears in the list.


Q:

in run time, I intend to create a user_settings table based on the user name

A:

You should be looking at database tenancies.

A2:

is this a user from the same company and database, or different companies with what should be different databases? If the latter, tenancies. If the former, an FK from user in the data table, etc.


Q:

in App Builder this work..
but in App Center, this command:

Code:
select 0
SELECT * FROM syscolumnconstraints where lower(table_schem) = lower(dbname) into cursor cur_columnconstraints

 

not work..

error message: File ‘syscolumnconstraints.dbf’ does not exist.

I think this is not a real table..

A:

Correct: it is not a table. It is effectively (don’t know what it really is) a stored procedure that reads the information for you. You can’t write to it

System tables do not exist in the system database, they are pseudo tables that are created dynamically.


 

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

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

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.