Understanding admin.sqlite¶
The Mind Map for Jam.py V7 admin.sqlite database describes the Jam.py database engine schema. The intention was to quickly find the information needed, as well as the code.
admin.sqlite
Click on admin.sqlite link to download MindMap file for FreeMind or similar software.
Ie, for Linux:
https://ixpeering.dl.sourceforge.net/project/freemind/freemind/1.0.1/freemind-bin-1.0.1.zip?viasf=1
For Windows:
https://sourceforge.net/projects/freemind/
Jam.py version 7.0.52
Document version 1.6, 18th June 2025
~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 and
not used for tables Importing per se.
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.
~
Legend:
- nodes in RED color are used for internationalization
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;
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.
The Caption field text (displayed text) on the Application page. Used for internationalisation.
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.
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, Reports, with ID 2,3,4 respectively)
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 Customer table in Demo, or Catalogs Group).
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)
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