Creating a 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;