Examples of SQL Queries

Once data have been uploaded onto a SQL database they can be queried in a number of different ways. Here are some example queries. All queries have been tested with SQLite, but note that some queries may not run on a particular relational database engine.

Index creation to speed up processing

CREATE INDEX IF NOT EXISTS idx_tokens_eid ON tokens(eid);
CREATE INDEX IF NOT EXISTS idx_tokens_fid_idx on tokens(fid);

CREATE INDEX IF NOT EXISTS idx_fcalls_destid ON fcalls(destid);
CREATE INDEX IF NOT EXISTS idx_fcalls_sourceid ON fcalls(sourceid);

CREATE INDEX IF NOT EXISTS idx_functiondefs_functionid ON functiondefs(functionid);

CREATE INDEX IF NOT EXISTS idx_ids_name ON ids(name);
CREATE INDEX IF NOT EXISTS idx_ids_name ON ids(eid);

CREATE INDEX IF NOT EXISTS idx_includers_cuid ON includers(cuid);
CREATE INDEX IF NOT EXISTS idx_includers_includerid ON includers(includerid);

Identifiers of a given type

SELECT name FROM ids WHERE ids.typedef ORDER BY name;

Number of different files that use a given identifier

SELECT name, Count(*) AS cf FROM (
  SELECT fid, tokens.eid, Count(*) AS c
    FROM tokens
    GROUP BY eid, fid
  ) AS cl
  INNER JOIN ids ON cl.eid = ids.eid
  GROUP BY ids.eid, ids.name
  HAVING cf > 1
  ORDER BY cf DESC, name;

Identifiers occuring ten or more times in a single file

SELECT ids.name AS iname, files.name AS fname, Count(*) AS c
  FROM tokens
  INNER JOIN ids ON ids.eid = tokens.eid
  INNER JOIN files ON tokens.fid = files.fid
  GROUP BY ids.eid, tokens.fid
  HAVING c >= 10
  ORDER BY c DESC, ids.name, files.name;

Identifiers occuring ten or more times in the workspace

SELECT name, Count(*) AS c FROM tokens
  INNER JOIN ids ON ids.eid = tokens.eid
  GROUP BY ids.eid
  HAVING c >= 10
  ORDER BY c DESC, name;

Projects each identifier named main belongs to

SELECT ids.name, projects.name
  FROM ids
  INNER JOIN idproj ON ids.eid = idproj.eid
  INNER JOIN projects ON idproj.pid = projects.pid
  WHERE ids.name = 'main'
  ORDER BY ids.name;

Included files required by other files for each compilation unit and project

SELECT
    projects.name AS projname,
    cufiles.name AS cuname,
    basefiles.name AS basename,
    definefiles.name AS defname
  FROM definers
  INNER join projects ON definers.pid = projects.pid
  INNER JOIN files AS cufiles ON definers.cuid=cufiles.fid
  INNER JOIN  files AS basefiles ON definers.basefileid=basefiles.fid
  INNER JOIN files AS definefiles ON definers.definerid = definefiles.fid;

Identifiers common between files participating in a define/use relationship

SELECT
    min(ids.name) as identifier,
    min(filesb.name) as defined,
    min(filesa.name) as used
  FROM definers
  INNER JOIN tokens AS tokensa ON definers.basefileid = tokensa.fid
  INNER JOIN tokens AS tokensb ON definers.definerid = tokensb.fid
  INNER JOIN ids ON ids.eid = tokensa.eid
  INNER JOIN files as filesa ON tokensa.fid = filesa.fid
  INNER JOIN files as filesb ON tokensb.fid = filesb.fid
  WHERE tokensa.eid = tokensb.eid
  GROUP BY tokensa.eid, definerid, basefileid
  ORDER BY defined, identifier, used;

Function and macro call graph

SELECT source.name AS CallingFunction, dest.name AS CalledFunction
  FROM fcalls
  INNER JOIN functions AS source ON fcalls.sourceid = source.id
  INNER JOIN functions AS dest ON fcalls.destid = dest.id
  ORDER BY CallingFunction, CalledFunction;

Number of C preprocessor directives in the project's source code files excluding the CScout configuration file

SELECT Sum(nppdirective) FROM filemetrics
  WHERE precpp
    AND fid != (SELECT fid FROM files WHERE name LIKE '%.cs');

Macros that cannot be easily converted into C

CREATE INDEX IF NOT EXISTS idx_tokens_eid ON tokens(eid);
CREATE INDEX IF NOT EXISTS idx_functiondefs_functionid ON functiondefs(functionid);
CREATE INDEX IF NOT EXISTS idx_fcalls_destid ON fcalls(destid);
CREATE INDEX IF NOT EXISTS idx_fcalls_sourceid ON fcalls(sourceid);

-- Macros that use local variables
WITH scoped_confused_macros AS (
  SELECT DISTINCT macrodefs.functionid
  FROM functiondefs AS macrodefs
  INNER JOIN functions ON functions.id = macrodefs.functionid
    AND functions.ismacro
  -- Obtain macro's tokens
  INNER JOIN tokens AS macrotokens ON macrotokens.fid = macrodefs.fidbegin
    AND macrotokens.fid = macrodefs.fidend
    AND macrotokens.foffset >= macrodefs.foffsetbegin
    AND macrotokens.foffset < macrodefs.foffsetend
  -- See those that are local objects or labels
  INNER JOIN ids ON ids.eid = macrotokens.eid
    AND ids.ordinary
    AND NOT (ids.macro OR ids.macroarg OR ids.suetag OR ids.sumember OR ids.typedef OR ids.enum)
    AND NOT ids.cscope
    AND NOT ids.lscope
  INNER JOIN files ON macrodefs.fidbegin = files.fid
  -- For macros that get called by C code
  WHERE EXISTS (
      SELECT 1
        FROM fcalls
        WHERE fcalls.destid = macrodefs.functionid
          AND EXISTS (
            SELECT 1
              FROM functions AS cfunctions
              WHERE
                fcalls.sourceid = cfunctions.id
                AND cfunctions.defined
                AND NOT cfunctions.ismacro
          )
          -- And tokens that also appear in the C function body
          AND EXISTS (
            SELECT 1
              FROM functiondefs AS cdefs
              INNER JOIN tokens AS ctokens ON ctokens.eid = macrotokens.eid
              WHERE
                fcalls.sourceid = cdefs.functionid
                AND (cdefs.fidbegin != macrodefs.fidbegin
                  OR cdefs.foffsetbegin > macrodefs.foffsetbegin)
                AND ctokens.fid = cdefs.fidend
                AND ctokens.foffset >= cdefs.foffsetbegin
                AND ctokens.foffset < cdefs.foffsetend
          )
    )
)
SELECT Count(*) FROM
  functions
  INNER JOIN filemetrics ON filemetrics.fid = functions.fid AND filemetrics.precpp
  INNER JOIN functionmetrics AS fnmet ON functions.id = fnmet.functionid AND fnmet.precpp
WHERE ismacro
  AND filemetrics.fid != (SELECT fid FROM files WHERE name LIKE '%.cs')
  AND (
  fnmet.fanin = 0
  OR fnmet.bracenest != 0
  OR fnmet.bracknest != 0
  OR fnmet.nppstringop > 0
  OR fnmet.nppconcatop > 0
  OR fnmet.neparam > 0
  -- Control flow problems
  OR (
    -- Callable macro, rather than macro defining a function
    fnmet.fanin > 0
    AND ((fnmet.ngoto > 0 AND fnmet.nlabel = 0)
      OR (fnmet.nreturn > 1 AND fnmet.ndo = 1))
  )
  OR EXISTS (
    SELECT 1
      FROM scoped_confused_macros AS scm
      WHERE scm.functionid = fnmet.functionid
    )
  )
;