[Answers] 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.

---

---

All topics in [Answers] alphabetically: http://www.lianja.com/community/show...ll=1#post12352

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.

---

---

All topics in [Answers] alphabetically: http://www.lianja.com/community/show...ll=1#post12352

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.

http://www.lianja.com/community/atta...0&d=1457606129

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.

http://www.lianja.com/community/atta...1&d=1457606400

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.

http://www.lianja.com/community/atta...2&d=1457606795In 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.

Quote:

---

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

---

http://www.lianja.com/community/atta...6&d=1440174800

---

---

All topics in [Answers] alphabetically: http://www.lianja.com/community/show...p?2717-Answers

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:
https://www.lianja.com/community/att...8&d=1494831824

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 ?
https://www.lianja.com/community/att...2&d=1494565622
Also, what is the command to browse all captions?

A5:
e.
https://www.lianja.com/community/att...3&d=1494567071

Code:

---

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.

---

---

All topics in [Answers] alphabetically: http://www.lianja.com/community/show...ll=1#post12352

These answers are also systematized on the site "Lianja developer": https://lianjadeveloper.wordpress.co...gory/database/