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.
-
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.
-
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?
-
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.
Content aside
-
7
Likes
- 5 mths agoLast active
- 24Replies
- 204Views
-
5
Following