admin.sqlite

Imagemap
admin.sqliteSYS_USERSID        INTEGERDELETED        INTEGERF_ACTIVE        INTEGERF_ACT_DATE        TEXTF_NAME        TEXTF_LOGIN        TEXTF_PASSWORD        TEXTF_ROLE        INTEGERF_INFO        TEXTF_ADMIN        INTEGERF_PSW_HASH        TEXTF_IP        TEXTF_UUID        TEXTSYS_TASKSID        INTEGERDELETED        INTEGERTASK_ID        INTEGERF_MANUAL_UPDATE        INTEGERF_DB_TYPE        INTEGERF_DB_NAME        TEXTF_ALIAS        TEXTF_LOGIN        TEXTF_PASSWORD        TEXTF_ENCODING        TEXTF_HOST        TEXTF_PORT        TEXTF_NAME        TEXTF_ITEM_NAME        TEXTF_LANGUAGE        INTEGERF_SERVER        TEXTSYS_ROLESID        INTEGERDELETED        INTEGERF_NAME        TEXTSYS_REPORT_PARAMSID        INTEGERDELETED        INTEGEROWNER_ID        INTEGEROWNER_REC_ID        INTEGERTASK_ID        INTEGERF_INDEX        INTEGERF_NAME        TEXTF_PARAM_NAME        TEXTF_DATA_TYPE        INTEGERF_SIZE        INTEGERF_OBJECT        INTEGERF_OBJECT_FIELD        INTEGERF_REQUIRED        INTEGERF_VISIBLE        INTEGERF_ALIGNMENT        INTEGERF_ENABLE_TYPEHEAD        INTEGERF_LOOKUP_VALUES        INTEGERF_MASTER_FIELD        INTEGERF_HELP        BLOBF_PLACEHOLDER        TEXTF_OBJECT_FIELD1        INTEGERF_OBJECT_FIELD2        INTEGERF_MULTI_SELECT        INTEGERF_MULTI_SELECT_ALL        INTEGERSYS_PRIVILEGESID        INTEGERDELETED        INTEGEROWNER_ID        INTEGEROWNER_REC_ID        INTEGERTASK_ID        INTEGERITEM_ID        INTEGERF_CAN_VIEW        INTEGERF_CAN_CREATE        INTEGERF_CAN_EDIT        INTEGERF_CAN_DELETE        INTEGERITEM_TYP        INTEGEROWNER_ITEM        TEXTSYS_PARAMSID        INTEGERDELETED        INTEGERF_SAFE_MODE        INTEGERF_DEBUGGING        INTEGERF_CON_POOL_SIZE        INTEGERF_LANGUAGE        INTEGERF_AUTHOR        TEXTF_VERSION        TEXTF_MP_POOL        INTEGERF_PERSIST_CON        INTEGERF_SINGLE_FILE_JS        INTEGERF_DYNAMIC_JS        INTEGERF_COMPRESSED_JS        INTEGERF_FIELD_ID_GEN        INTEGERF_TIMEOUT        INTEGERF_DELETE_REPORTS_AFTER        INTEGERF_IGNORE_CHANGE_IP        INTEGERF_HISTORY_ITEM        INTEGERF_LOCK_ITEM        INTEGERF_SYS_GROUP        INTEGERF_THEME        INTEGERF_SMALL_FONT        INTEGERF_FULL_WIDTH        INTEGERF_FORMS_IN_TABS        INTEGERF_MAX_CONTENT_LENGTH        INTEGERF_LANG_VERSION        TEXTF_SECRET_KEY        TEXTF_MODIFICATION        INTEGERF_IMPORT_DELAY        INTEGERF_CLIENT_MODIFIED        INTEGERF_SERVER_MODIFIED        INTEGERF_BUILD_VERSION        INTEGERF_PARAMS_VERSION        INTEGERF_MAINTENANCE        INTEGERF_PRODUCTION        INTEGERF_JAM_VERSION        TEXTTASK_ID        INTEGERSYS_LOOKUP_LISTSID        INTEGERDELETED        INTEGERF_NAME        TEXTF_LOOKUP_VALUES_TEXT        BLOBSYS_LANGUAGESID        INTEGERF_NAME        TEXTF_ABR        TEXTF_RTL        INTEGERDELETED        INTEGERSYS_LANGSID        INTEGERDELETED        INTEGERF_NAME        TEXTF_LANGUAGE        INTEGERF_COUNTRY        INTEGERF_DECIMAL_POINT        TEXTF_MON_DECIMAL_POINT        TEXTF_MON_THOUSANDS_SEP        TEXTF_CURRENCY_SYMBOL        TEXTF_FRAC_DIGITS        INTEGERF_P_CS_PRECEDES        INTEGERF_N_CS_PRECEDES        INTEGERF_P_SEP_BY_SPACE        INTEGERF_N_SEP_BY_SPACE        INTEGERF_POSITIVE_SIGN        TEXTF_NEGATIVE_SIGN        TEXTF_P_SIGN_POSN        INTEGERF_N_SIGN_POSN        INTEGERF_D_FMT        TEXTF_D_T_FMT        TEXTF_ABR        TEXTF_RTL        INTEGERSYS_ITEMSID        INTEGERDELETED        INTEGERPARENT        INTEGERTASK_ID        INTEGERTYPE_ID        INTEGERTABLE_ID        INTEGERHAS_CHILDREN        INTEGERF_NAME        TEXTF_ITEM_NAME        TEXTF_TABLE_NAME        TEXTF_VIEW_TEMPLATE        TEXTF_EDIT_TEMPLATE        TEXTF_FILTER_TEMPLATE        TEXTF_VISIBLE        INTEGERF_CLIENT_MODULE        BLOBF_SERVER_MODULE        BLOBF_INFO        BLOBF_WEB_CLIENT_MODULE        BLOBF_SOFT_DELETE        INTEGERF_INDEX        INTEGERF_EXTERNAL        INTEGERF_VIRTUAL_TABLE        INTEGERF_JS_EXTERNAL        INTEGERF_JS_FILENAME        TEXTF_PRIMARY_KEY        INTEGERF_DELETED_FLAG        INTEGERF_MASTER_ID        INTEGERF_MASTER_REC_ID        INTEGERF_JS_FUNCS        BLOBF_EDIT_LOCK        INTEGERF_GEN_NAME        TEXTF_KEEP_HISTORY        INTEGERSYS_ID        INTEGERF_SELECT_ALL        INTEGERF_RECORD_VERSION        INTEGERSYS_INDICESID        INTEGERDELETED        INTEGEROWNER_ID        INTEGEROWNER_REC_ID        INTEGERTASK_ID        INTEGERF_INDEX_NAME        TEXTDESCENDING        INTEGERF_FIELDS        BLOBF_FOREIGN_INDEX        INTEGERF_FOREIGN_FIELD        INTEGERF_UNIQUE_INDEX        INTEGERF_FIELDS_LIST        TEXTSYS_FILTERSID        INTEGERDELETED        INTEGEROWNER_ID        INTEGEROWNER_REC_ID        INTEGERTASK_ID        INTEGERF_INDEX        INTEGERF_FIELD        INTEGERF_NAME        TEXTF_FILTER_NAME        TEXTF_DATA_TYPE        INTEGERF_TYPE        INTEGERF_VISIBLE        INTEGERF_HELP        BLOBF_PLACEHOLDER        TEXTF_MULTI_SELECT_ALL        INTEGERSYS_FIELD_LOOKUPSID        INTEGERDELETED        INTEGERFIELD_ID        INTEGERF_VALUE        INTEGERF_LOOKUP        TEXTSYS_FIELDSID        INTEGERDELETED        INTEGEROWNER_ID        INTEGEROWNER_REC_ID        INTEGERTASK_ID        INTEGERF_NAME        TEXTF_FIELD_NAME        TEXTF_DATA_TYPE        INTEGERF_SIZE        INTEGERF_OBJECT        INTEGERF_OBJECT_FIELD        INTEGERF_EDIT_FIELD        INTEGERF_MASTER_FIELD        INTEGERF_REQUIRED        INTEGERF_CALCULATED        INTEGERF_DEFAULT        INTEGERF_READ_ONLY        INTEGERF_ALIGNMENT        INTEGERF_ENABLE_TYPEHEAD        INTEGERF_LOOKUP_VALUES        INTEGERF_LOOKUP_VALUES_TEXT        BLOBF_DEFAULT_VALUE        TEXTF_HELP        BLOBF_PLACEHOLDER        TEXTF_OBJECT_FIELD1        INTEGERF_OBJECT_FIELD2        INTEGERF_MULTI_SELECT        INTEGERF_MULTI_SELECT_ALL        INTEGERF_DB_FIELD_NAME        TEXTF_MASK        TEXTF_DEFAULT_LOOKUP_VALUE        INTEGERF_IMAGE_EDIT_WIDTH        INTEGERF_IMAGE_EDIT_HEIGHT        INTEGERF_IMAGE_VIEW_WIDTH        INTEGERF_IMAGE_VIEW_HEIGHT        INTEGERF_IMAGE_PLACEHOLDER        TEXTF_FILE_DOWNLOAD_BTN        INTEGERF_FILE_OPEN_BTN        INTEGERF_FILE_ACCEPT        TEXTF_IMAGE_CAMERA        INTEGERSYS_COUNTRIESID        INTEGERF_NAME        TEXTF_ABR        TEXTDELETED        INTEGERF_RTL        INTEGER
hide
admin.sqlite User Link

Click on admin.sqlite link to download MindMap file for FreeMind or similar software.

Jam.py version 5.4.96

Document version 1.2, 23th Sep 2020

~Introduction~

The notes here are not supported by Jam core developers and structure might change in future Jam releases. The below notes

were used for Importing more than 100 tables into the Jam.py from an legacy system. There are other, more general notes as well.

If updating the file, please post it to http://groups.google.com/forum/#!forum/jam-py for everyone and I'll update this site.

Hope this helps.

Thanks!

~

So what is admin.sqlite DB? According to Jam.py, the DB stores info about the project tables, fields and indexes, as well as code and settings. This means everything we change in Jam.py Application Builder, is recorded in DB. However, sometimes we need to interact with the DB directly, particularly if Jam has no means yet to fulfill the requirements. With Importing tables from some live system, there is no way to add column(s) during the Import since the Jam.py is in, so called, "DB Manual Mode" by the design. Which enables us to Import, but not alter the tables, meaning we need to alter the tables before Import operation. This is particularly true for Jam.py features, like History or Deleted option.

There is also a need to interact with DB directly if the App password is lost or forgotten when Jam.py is in "Safe Mode".

Hence, always backup the admin.sqlite database before performing any of tasks mentioned here.

hide
SYS_ITEMS

Creating a New Item Group or a Table happens here.

hide
SYS_FIELDS

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' "

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;