General notes:
The App "Safe Mode" - controls if the login is enabled/disabled
Use below to disable "Safe Mode" if the App is "locked", meaning no one can log in:
update SYS_PARAMS set f_safe_mode=0
General notes:
Used for DropDown Lists. Sometimes it is needed to reference a varchar in the DB as an identifier for a List. Jam v5 does not support this.
Follow below steps to bypass Jam limitation (not tested, sourced from Google Jam.py group:
1. I create a lookup_list in GUI (Task): [[1,"value_1"],[2,"value_2"]]
2. In admin.sqlite > sys_lookup_lists (table) > f_lookup_values_text (column), I update the value with [["value_1","value_1"],["value_2","value_2"]]
3. I create a field with the GUI and I chose the lookup_list above.
4. In admin.sqlite > sys_fields (table), I update the column "f_data_type" ( value: 2 => 1) and "f_size" ( null => 20 ) linked to my field
5. I make an alter table to change the datatype of the column (int to varchar) in my physical DB
6. I restart the server (maybe not necessary)
Creating a New Item Group or a Table happens here.
When new project is created with jam-project, there are only 9 records (ending with ID=5) and each record might have corresponding record in SYS_FIELDS (ie Catalogs, Journals, Details, with ID 2,3,4 respectively)
Used to identify the Parent Group. It is actually very simple to move a table from one Group to another, if the Group does not have Common fields.
We just need to look at the ID information for everything.
Ie, Group "test import" has a Table with ID=8, and we want to move it to Group Journals with ID=3:
update SYS_ITEMS set PARENT = 3 where id = 8;
Used as boolean in ver6 in combination with F_DELETED_FLAG (inc ver5 but it is integer atm)
If the Table has no "deleted" field or similar candidate, it must be added, ie (Order table is a problem for Python, so we exclude it):
import sqlite3
connection = sqlite3.connect("db.sqlite3")
cursor = connection.cursor()
tableQuery = "select * from sqlite_master where type = 'table' and name != 'Order' and name != 'sqlite_sequence'"
cursor.execute(tableQuery)
tableList = cursor.fetchall()
for table in tableList:
renameTable = "ALTER TABLE %s"%(table[1]) + " ADD COLUMN DELETED integer"
print(renameTable)
cursor.execute(renameTable)
connection.close()
Used to specify if the table is Virtaul Table. To find virtual tables in a project:
select f_name, f_item_name from sys_items where f_virtual_table = 1;
Used to define what "deleted" field is for relevant table. In Demo F_DELETED_FLAG is 6, 4 or 2 for F_SOFT_DELETE=1. The 6,4 and 2 are ID rows in SYS_FIELDS ("Deleted Flag" F_NAME for OWNER_REC_ID tables 4,3,2 respectively).
However, some rows have F_DELETED_FLAG set and no F_SOFT_DELETE, which is ok since we do not want soft delete for this tables (ie Consumer table in Demo, or Catalogs Group).
Creating new Table Field, or Importing Tables with all definitions happens here as well as in SYS_ITEMS. Export of the Project uses it, ie if we add a table field manually to DB (by you or DBA's), and we Export Jam project, the exported DB will not be consistent with added field from before.
Hence, we need to insert manually created DB fields into the SYS_FIELDS for Jam to consume it. If New Group Item is created for Imported tables, we need to identify the SYS_ITEMS.ID first. For example, after jam-project command and New Group Item is added in Builder immediately after, 10th record has ID=6, PARENT=1,TASK_ID=1,TYPE_ID=6 etc. The important part is TYPE_ID=6 which is a new PARENT for imported tables.
Than we Import tables into that New Group Item and ID increases with PARENT=6, TASK_ID=1,TYPE_ID=10 etc for every table imported. For example:
select ID,PARENT,TASK_ID,TYPE_ID from SYS_ITEMS where PARENT=6 and TASK_ID=1 and TYPE_ID=10
Now that we have information about our Imported tables and the ID for each table, we can import this data into SYS_FIELDS:
INSERT INTO SYS_FIELDS (DELETED, F_NAME, F_FIELD_NAME, F_DATA_TYPE, OWNER_ID, OWNER_REC_ID, TASK_ID, F_ALIGNMENT, F_DB_FIELD_NAME, F_DEFAULT_LOOKUP_VALUE)
SELECT 0, 'deleted', 'deleted', 7, 3, ID, 1, 1, 'DELETED', 0
FROM SYS_ITEMS
WHERE PARENT=6 and TASK_ID=1 and TYPE_ID=10
We inserted all manually added records into SYS_FIELDS and now SYS_ITEMS.F_DELETED_FLAG should be updated for every table with corresponded SYS_FIELDS.ID which identifies "Deleted Flag":
UPDATE SYS_ITEMS
SET F_DELETED_FLAG = (
SELECT id
FROM SYS_FIELDS
WHERE SYS_FIELDS.OWNER_REC_ID = SYS_ITEMS.ID AND SYS_FIELDS.F_NAME = 'deleted'
);
Now we can set deleted=0 to all tables:
import sqlite3
connection = sqlite3.connect("yourdb.sqlite3")
cursor = connection.cursor()
tableQuery = "select * from sqlite_master where type = 'table' and name != 'Order' and name != 'sqlite_sequence'"
cursor.execute(tableQuery)
tableList = cursor.fetchall()
for table in tableList:
setdelTable = "UPDATE %s"%(table[1]) + " SET deleted = 0"
print(setdelTable)
connection.commit()
cursor.execute(setdelTable)
connection.close()
And finally we update the F_SOFT_DELETE where ID => 7 because ID=6 is New Group Item where we imported all tables:
update sys_items set f_soft_delete = 1 where id >= 7;
When new project is created with jam-project, there are only 9 records (last ending with ID=5). Setting ie "Deleted Flag" for "Soft Delete" in Jam Builder, is creating so called "Common Fields". It is not possible to Import tables if an item has Common Fields. This behaviour might change in v6.
Identifies the Table owning fields.
When an Table is about to be Imported into Jam, the Table fields can't be added to the DB, they can only be removed from Jam Builder as ignored in the App.
For full Jam functionality (meaning SoftDelete), the Table has no "deleted flag" field which can be used for "Deleted Flag" (see SYS_ITEMS). The field should be added manually to DB after the Import, and admin.sqlite must be updated to enable "Soft Delete" feature. See above SYS_FIELDS procedure. This is particularly important when Importing a large number of tables from some other database, not created my Jam.py Builder.