7

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

null
    • Fred
    • 5 mths ago
    • Reported - view

    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.

    • John_Halls
    • 5 mths ago
    • Reported - view

    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

      • Ninox developper
      • Jacques_TUR
      • 5 mths ago
      • Reported - view

       Yes, I saw that and I'm surprised too. I don't have an explanation for it yet. I'll have to look into it.

      • Ninox developper
      • Jacques_TUR
      • 5 mths ago
      • Reported - view

       

      I found the origin of the problem. I had three relationships pointing to another table. In this case, the analysis cannot account for the relationships in the other direction. I have now added the name of the database pointed to by the relationship fields (see here: https://forum.ninox.com/t/g9ym625?r=83ymmd9).

      It turns out that I also had a flaw in my schema with two N:1 relationships both pointing to the same 1
      relationship field. Thanks to this analysis, I was able to identify and correct it.

    • Rafael Sanchis
    • Rafael_Sanchis
    • 5 mths ago
    • Reported - view

    My DB

    • Rafael Sanchis
    • Rafael_Sanchis
    • 5 mths ago
    • Reported - view

    Jacques when run ninext on Documents give me a error, if no run Ninext no error. 😮

      • Ninox developper
      • Jacques_TUR
      • 5 mths ago
      • Reported - view

      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?

      • Rafael Sanchis
      • Rafael_Sanchis
      • 5 mths ago
      • Reported - view

       👍 find it Thanks.

      • Rafael Sanchis
      • Rafael_Sanchis
      • 5 mths ago
      • Reported - view

      Yiu are right Sorry

    • Ninox developper
    • Jacques_TUR
    • 5 mths ago
    • Reported - view

    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.
      • Rafael Sanchis
      • Rafael_Sanchis
      • 5 mths ago
      • Reported - view

       

      Hi Jacques 

      I'm doing something wrong since I don't see the information as you showed it.

      • Ninox developper
      • Jacques_TUR
      • 5 mths ago
      • Reported - view

       How do you see it?

      • Rafael Sanchis
      • Rafael_Sanchis
      • 5 mths ago
      • Reported - view

       

      • Ninox developper
      • Jacques_TUR
      • 5 mths ago
      • Reported - view

       Did you use this code?

      #{return ninext.schemaAnalysis(true)}#
      
      • Rafael Sanchis
      • Rafael_Sanchis
      • 5 mths ago
      • Reported - view

       😱 👍

    • NYNNA
    • Louis_Cornacchia
    • 5 mths ago
    • Reported - view

    Jacques, How can I get access to NINEXT?

      • Ninox developper
      • Jacques_TUR
      • 5 mths ago
      • Reported - view

      Send me an email to Jacques.tur@umangenius.com and I will return the installation procedure to you.

    • Fred
    • 5 mths ago
    • Reported - view
     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?

      • Ninox developper
      • Jacques_TUR
      • 5 mths ago
      • Reported - view

       In Ninox's console. 

      • Rafael Sanchis
      • Rafael_Sanchis
      • 5 mths ago
      • Reported - view

       

      I use in Formula field and works perfect

      • Rafael Sanchis
      • Rafael_Sanchis
      • 5 mths ago
      • Reported - view

       The Ninox Low Code 😲

    • Ninox developper
    • Jacques_TUR
    • 5 mths ago
    • Reported - view

    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 😅.

    • Fred
    • 5 mths ago
    • Reported - view

    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.

      • Ninox developper
      • Jacques_TUR
      • 5 mths ago
      • Reported - view

       

      2411 formulas with 1 line,

      228 formulas with 2 lines,

      242 formulas with 3 lines…

Content aside

  • 7 Likes
  • 5 mths agoLast active
  • 24Replies
  • 204Views
  • 5 Following