Analysis of your database structure with Ninext
Here's a JavaScript code snippet for analyzing the contents of your database. This code can be run from the Ninox console when Ninext is installed :
#{
let tables = Object.values(database.schema.types);
let fields = {};
let nbFields = 0; // serach all tables
tables.forEach(t => {
var fld = Object.values(t.fields);
// add count for each kind of field
fld.forEach(f => {
fields[f.base] = (fields[f.base] ? fields[f.base] : 0) + 1;
});
nbFields += fld.length;
}); // sort kind of field by count
var sorted = {};
Object.entries(fields).sort((a, b) => (a[1] == b[1])?a[0] < b[0]:a[1] < b[1]).forEach(f => {
let caption;
switch (f[0]) {
case "ref": caption = "relation => N:1"; break;
case "rev": caption = "relation <= 1:N"; break;
default: caption = locale["type_" + f[0]]
}
sorted[caption] = f[1];
}); // return the text of analysis result
return JSON.stringify({ nbTables: tables.length, nbFields: nbFields, fieldByType: sorted }, null, '\t');
}#;Sample result:
{
"nbTables": 90,
"nbFields": 1675,
"fieldByType": {
"Formula": 661,
"Text": 288,
"relation <= 1:N": 146,
"relation => N:1": 144,
"Number": 92,
"Yes / No": 92,
"Date": 44,
"Choice": 41,
"Rich text": 35,
"Date + Time": 21,
"Image": 21,
"Multiple choice (dynamic)": 18,
"URL": 16,
"Choice (dynamic)": 13,
"Time": 9,
"Multiple choice": 8,
"Email": 7,
"Color": 7,
"Phone": 4,
"Icon": 4,
"Appointment": 2,
"Collaborator": 1,
"Time interval": 1
}
}It lets you count the number of tables, total fields and fields by type. This will give you an idea of the size of your application. One of the key points is the number of formulas. The more formulas you have, the more complex your database's calculation mechanisms are, and the more you need to maintain them.
have fun! 
24 replies
-
Here are my results:
{ "nbTables": 35, "nbFields": 1251, "fieldByType": { "Formula": 906, "Number": 112, "relation <= 1:N": 52, "relation => N:1": 52, "Text": 46, "Yes / No": 28, "Choice": 19, "Choice (dynamic)": 18, "Date": 6, "URL": 5, "Multiple choice": 3, "Image": 2, "Multiple choice (dynamic)": 2 } }I have lots of formula fields as I do a sports DB so there are lots of stats. Though I can get away with dashboards for most of the stats so I don't have to store all stats for all records.
Surprised that I only have 2 dMC fields. I guess I would have used more, but I guess I don't.
Look forward to seeing other people's stats.
-
Hi
Interesting to see has the following
"relation <= 1:N": 52, "relation => N:1": 52,Which would be expected but you have
"relation <= 1:N": 146, "relation => N:1": 144,How can you have two more 1:N's than N:1's?
Regards John
-
My DB
-
Jacques when run ninext on Documents give me a error, if no run Ninext no error.
-
Have you looked at the source of the error? Does it show up in Ninext's error list?
ps: it seems to me that this question is not related to the subject of this post. So that we can find the information later, could you put your question in a new post?
-
find it Thanks. -
Yiu are right Sorry
-
-
I have just added a function to Ninext to return the analysis:
#{return ninext.schemaAnalysis(true)}#If the parameter is true, it returns a formatted text of a JSON; otherwise, it directly returns the JSON.
The analysis includes several sections:
{ "nbTables": 11, "nbFields": 0, "fieldByType": { "relation => N:1 (#Ninext 2)": 3, "relation <= 1:N (#Ninext 2)": 3, "Yes / No": 1, "Text": 32, "Number": 5, "Image": 2, "Formula": 19, "Email": 1, "Date": 3, "Color": 5, "Choice": 6 }, "numberOfFunctionCodes": 169, "numberOfLinesOfCode": 490, "NbLinesCodePerNbFunctions": { "1": 129, "2": 12, "3": 5, "4": 2, "5": 4, "6": 3, "9": 2, "10": 1, "11": 2, "12": 1, "14": 1, "16": 1, "21": 1, "22": 1, "25": 1, "27": 1, "40": 1, "49": 1 } }- nbTables: number of tables in the database,
- nbFields: total number of fields in the database,
- fieldByType: number of fields sorted by field type. For 1and N:1 relationships, the name of the database they point to is specified in parentheses.
Important: For code analysis, currently, the scripts contained in the reports are not taken into account.
- numberOfFunctionCodes: number of functions and formulas,
- numberOfLinesOfCode: number of lines contained in functions and formulas
- NbLinesCodePerNbFunctions: table of the distribution of the number of functions by the number of lines of code. This allows seeing how many functions or formulas have a lot of lines of code.
-
Hi Jacques
I'm doing something wrong since I don't see the information as you showed it.
-
How do you see it?
-
-
Did you use this code?
#{return ninext.schemaAnalysis(true)}# -
-
Jacques, How can I get access to NINEXT?
-
said:
I have just added a function to Ninext to return the analysis:
#{return ninext.schemaAnalysis(true)}#Where do you put this code?
In the Ninext.start?
ninext.start({ badges: true, viewEvent: true, buttonEvent : true, fieldsInspector: true, nativeJS: true, tableView: true, debugTracer : true, languages : false, errorsViewer : true, darkMode : false, codeFinder : true, autoCloseDialog: " + if res.version like "debug" then "true" else "false" end + " })or somewhere else?
-
In Ninox's console.
-
I use in Formula field and works perfect
-
The Ninox Low Code
-
-
As usual, I created this function because I needed it
. However, it would be better if I added a tab to the Ninext window to display all this information dynamically after each database modification 
. -
Ok, well here is my results with code analysis:
"nbTables": 35, "nbFields": 0, "fieldByType": { "Text": 46, "Number": 112, "Formula": 906, "relation <= 1:N (Test)": 53, "Yes / No": 28, "relation => N:1 (Test)": 53, "Date": 6, "URL": 5, "Multiple choice (dynamic)": 2, "Choice": 19, "Choice (dynamic)": 18, "Multiple choice": 3, "Image": 2 }, "numberOfFunctionCodes": 3291, "numberOfLinesOfCode": 11539, "NbLinesCodePerNbFunctions": { "1": 2411, "2": 228, "3": 242, "4": 35, "5": 82, "6": 31, "7": 53, "8": 26, "9": 16, "10": 12, "11": 26, "12": 9, "13": 8, "14": 10, "15": 8, "16": 5, "17": 3, "18": 3, "19": 1, "20": 5, "21": 3, "23": 2, "24": 1, "25": 1, "26": 9, "27": 4, "31": 1, "35": 2, "36": 1, "37": 3, "39": 1, "41": 1, "43": 1, "45": 1, "50": 1, "52": 1, "53": 3, "54": 1, "55": 3, "57": 1, "61": 1, "62": 1, "63": 1, "64": 1, "69": 3, "70": 2, "72": 1, "73": 1, "79": 1, "82": 1, "84": 1, "85": 1, "86": 1, "95": 1, "101": 2, "102": 1, "105": 1, "107": 1, "112": 1, "113": 1, "116": 1, "136": 1, "138": 2, "139": 3, "140": 1, "145": 1, "152": 1, "182": 1, "294": 1 }I'm not still not sure what is being referenced in NbLinesCodePerNbFunctions.
-
2411 formulas with 1 line,
228 formulas with 2 lines,
242 formulas with 3 lines…
-
Content aside
-
7
Likes
- 3 mths agoLast active
- 24Replies
- 182Views
-
5
Following