4898794edd81be5285ea6e544acbedeaeb31bf78 max Tue Nov 23 08:10:57 2021 -0800 Fixing pointers to README file for license in all source code files. refs #27614 diff --git src/hg/makeDb/schema/dbSnoop/dbSnoop.c src/hg/makeDb/schema/dbSnoop/dbSnoop.c index 6fbe743..d1cc999 100644 --- src/hg/makeDb/schema/dbSnoop/dbSnoop.c +++ src/hg/makeDb/schema/dbSnoop/dbSnoop.c @@ -1,616 +1,616 @@ /* dbSnoop - Produce an overview of a database.. */ /* Copyright (C) 2014 The Regents of the University of California - * See README in this or parent directory for licensing information. */ + * See kent/LICENSE or http://genome.ucsc.edu/license/ for licensing information. */ #include "common.h" #include "linefile.h" #include "hash.h" #include "options.h" #include "dystring.h" #include "jksql.h" #include "obscure.h" #include "tableStatus.h" char *profile = NULL; boolean sortAlpha = FALSE; void usage() /* Explain usage and exit. */ { errAbort( "dbSnoop - Produce an overview of a database.\n" "usage:\n" " dbSnoop database output\n" "options:\n" " -unsplit - if set will merge together tables split by chromosome\n" " -noNumberCommas - if set will leave out commas in big numbers\n" " -justSchema - only schema parts, no contents\n" " -skipTable=tableName - if set skip a given table name\n" " -profile=profileName - use profile for connection settings, default = '%s'\n" " -sortAlpha - if set changes output order of fields to make comparisons between databases which have field order swapped easier.\n" , profile ); } static struct optionSpec options[] = { {"unsplit", OPTION_BOOLEAN}, {"noNumberCommas", OPTION_BOOLEAN}, {"justSchema", OPTION_BOOLEAN}, {"skipTable", OPTION_STRING}, {"profile", OPTION_STRING}, {"host", OPTION_STRING}, {"user", OPTION_STRING}, {"password", OPTION_STRING}, {"sortAlpha", OPTION_BOOLEAN}, {NULL, 0}, }; boolean noNumberCommas = FALSE; boolean unsplit = FALSE; boolean justSchema = FALSE; char *skipTable = NULL; struct slName *chromList; /* List of chromosomes in unsplit case. */ struct fieldDescription /* Information on a field. */ { struct fieldDescription *next; /* Next in list. */ char *name; /* Field name. */ char *type; /* SQL type. */ char *key; /* How is it indexed? PRI/MUL/etc. */ }; struct tableInfo /* Information on a table. */ { struct tableInfo *next; /* Next in list. */ char *name; /* Name of table. */ struct tableStatus *status; /* Result of table status call. */ struct fieldDescription *fieldList;/* List of fields in table. */ }; int tableInfoCmpSize(const void *va, const void *vb) /* Compare two tableInfo. */ { const struct tableInfo *a = *((struct tableInfo **)va); const struct tableInfo *b = *((struct tableInfo **)vb); long long aSize = a->status->dataLength + a->status->indexLength; long long bSize = b->status->dataLength + b->status->indexLength; long long diff = bSize - aSize; if (diff < 0) return -1; else if (diff > 0) return 1; else return 0; } int tableInfoCmpName(const void *va, const void *vb) /* Compare two tableInfo on name. */ { const struct tableInfo *a = *((struct tableInfo **)va); const struct tableInfo *b = *((struct tableInfo **)vb); return strcmp(a->name, b->name); } int tableInfoCmpUpdateTime(const void *va, const void *vb) /* Compare two tableInfo on update time. */ { const struct tableInfo *a = *((struct tableInfo **)va); const struct tableInfo *b = *((struct tableInfo **)vb); char *aT = a->status->updateTime; char *bT = b->status->updateTime; if (aT == bT) return FALSE; else if (aT == NULL) /* Update_time is NULL for InnoDB tables */ return -1; else if (bT == NULL) return 1; else return strcmp(bT,aT); } struct fieldInfo /* Information on a field. */ { struct fieldInfo *next; /* Next in list. */ char *name; /* Name of field. */ struct slName *tableList; /* List of tables using name. */ int tableCount; /* Count of tables. */ }; int fieldInfoCmp(const void *va, const void *vb) /* Compare two fieldInfo. */ { const struct fieldInfo *a = *((struct fieldInfo **)va); const struct fieldInfo *b = *((struct fieldInfo **)vb); return b->tableCount - a->tableCount; } int fieldInfoCmpName(const void *va, const void *vb) /* Compare two fieldInfo on name. */ { const struct fieldInfo *a = *((struct fieldInfo **)va); const struct fieldInfo *b = *((struct fieldInfo **)vb); return strcmp(a->name, b->name); } void noteField(struct hash *hash, char *table, char *field, struct fieldInfo **pList) /* Keep track of field. */ { struct fieldInfo *fi = hashFindVal(hash, field); if (fi == NULL) { AllocVar(fi); hashAddSaveName(hash, field, fi, &fi->name); slAddHead(pList, fi); } slNameAddHead(&fi->tableList, table); fi->tableCount += 1; } struct tableType /* Information on a type. */ { struct tableType *next; /* Next in list. */ char *fields; /* Comma-separated list of fields. */ struct slName *tableList; /* List of tables using this type. */ int tableCount; /* Count of tables. */ }; int tableTypeCmp(const void *va, const void *vb) /* Compare two tableType. */ { const struct tableType *a = *((struct tableType **)va); const struct tableType *b = *((struct tableType **)vb); return b->tableCount - a->tableCount; } void noteType(struct hash *hash, char *fields, char *table, struct tableType **pList) /* Keep track of tables of given type. */ { struct tableType *tt = hashFindVal(hash, fields); if (tt == NULL) { AllocVar(tt); hashAddSaveName(hash, fields, tt, &tt->fields); slAddHead(pList, tt); } slNameAddHead(&tt->tableList, table); tt->tableCount += 1; } void tableSummary(struct tableStatus *status, struct sqlConnection *conn, FILE *f, struct hash *fieldHash, struct fieldInfo **pFiList, struct hash *tableHash, struct tableInfo **pTiList, struct hash *typeHash, struct tableType **pTtList) /* Write out summary of table to file. */ { char query[256]; struct sqlResult *sr; char **row; struct dyString *fields = dyStringNew(0); struct tableInfo *ti; /* Make new table info, and add it to hash, list */ AllocVar(ti); hashAddSaveName(tableHash, status->name, ti, &ti->name); ti->status = status; slAddTail(pTiList, ti); /* List fields. */ sqlSafef(query, sizeof(query), "describe `%s`", ti->name); sr = sqlGetResult(conn, query); while ((row = sqlNextRow(sr)) != NULL) { struct fieldDescription *field; AllocVar(field); field->name = cloneString(row[0]); field->type = cloneString(row[1]); if (row[3][0] != 0) field->key = cloneString(row[3]); slAddHead(&ti->fieldList, field); dyStringPrintf(fields, "%s,", field->name); noteField(fieldHash, ti->name, field->name, pFiList); } noteType(typeHash, fields->string, ti->name, pTtList); sqlFreeResult(&sr); } char *toLongNumber(long long val) /* Print long number possibly with commas. */ { char ascii[32]; if (noNumberCommas) safef(ascii, sizeof ascii, "%lld", val); else sprintLongWithCommas(ascii, val); return cloneString(ascii); } void printTaggedLong(FILE *f, char *tag, long long val) /* Print out tagged value, putting commas in number */ { fprintf(f, "%s:\t%s\n", tag, toLongNumber(val)); } char *findPastChrom(char *table, struct slName *chromList) /* Return first character in table past the chrN_ prefix if any. * if no chrN_ prefix return NULL. The logic here depends on * chromList being sorted with largest name first. */ { struct slName *chrom; for (chrom = chromList; chrom != NULL; chrom = chrom->next) { if (startsWith(chrom->name, table)) { int len = strlen(chrom->name); if (table[len] == '_') return table + len + 1; } } return NULL; } void unsplitTables(struct sqlConnection *conn, struct tableInfo **pList) /* Merge together information on tables that are split by chromosome. */ { /* Note, this routine leaks a little memory, but in this context that's * that's ok. */ struct tableInfo *ti, *newList = NULL, *next; struct hash *splitHash = hashNew(0); chromList = sqlQuickList(conn, NOSQLINJ "select chrom from chromInfo order by length(chrom) desc"); if (chromList == NULL) errAbort("Can't use unsplit because database has no chromInfo table"); for (ti = *pList; ti != NULL; ti = next) { char *pastChrom = findPastChrom(ti->name, chromList); next = ti->next; if (pastChrom != NULL) { struct tableInfo *splitTi; if ((splitTi = hashFindVal(splitHash, pastChrom)) != NULL) { splitTi->status->rows += ti->status->rows; splitTi->status->dataLength += ti->status->dataLength; splitTi->status->indexLength += ti->status->indexLength; } else { ti->name = pastChrom; hashAdd(splitHash, pastChrom, ti); slAddHead(&newList, ti); } } else { slAddHead(&newList, ti); } } slSort(&newList, tableInfoCmpName); *pList = newList; } struct indexInfo /* Stores pertinent info from row returned by mysql in response to * show indexes from table */ { struct indexInfo *next; char *table; /* Name of table. */ boolean nonUnique; /* True if can have duplicates. */ char *indexName; /* Name of this index. Usually but not always same as field. */ int seqInIndex; /* For multiple part indexes, which part. */ char *field; /* Name of field. */ long long cardinality; /* Cardinality of index. */ }; struct indexInfo *indexInfoLoad(char **row) /* Allocate and return index info based on row. */ { struct indexInfo *ii; AllocVar(ii); ii->table = cloneString(row[0]); ii->nonUnique = sqlUnsigned(row[1]); ii->indexName = cloneString(row[2]); ii->seqInIndex = sqlUnsigned(row[3]); ii->field = cloneString(row[4]); if (row[6] != NULL) ii->cardinality = sqlLongLong(row[6]); return ii; } struct indexGroup /* Info about a group of indexes, that is various parts of * a multi-column index. */ { struct indexGroup *next; char *name; /* Name of index. */ struct indexInfo *fieldList; /* Various fields involved in index. */ }; int indexGroupCmp(const void *va, const void *vb) /* Compare two index groups to order by name */ { const struct indexGroup *a = *((struct indexGroup **)va); const struct indexGroup *b = *((struct indexGroup **)vb); return strcmp(a->name, b->name); } int indexInfoCmp(const void *va, const void *vb) /* Compare two index groups to order by name */ { const struct indexInfo *a = *((struct indexInfo **)va); const struct indexInfo *b = *((struct indexInfo **)vb); return a->seqInIndex - b->seqInIndex; } void printIndexes(FILE *f, struct sqlConnection *conn, struct tableInfo *ti) /* Print info about indexes on table to file. */ { char query[256], **row; struct sqlResult *sr; struct indexGroup *groupList = NULL, *group; struct hash *groupHash = newHash(8); struct indexInfo *ii; char *tableName = ti->name; char splitTable[256]; boolean isSplit = FALSE; if (unsplit) { struct slName *chrom; if (!sqlTableExists(conn, tableName)) { for (chrom = chromList; chrom != NULL; chrom = chrom->next) { safef(splitTable, sizeof(splitTable), "%s_%s", chrom->name, tableName); if (sqlTableExists(conn, splitTable)) { tableName = splitTable; isSplit = TRUE; break; } } } } /* Build up information on indexes in this table by processing * mysql show indexes command results. This command returns * a separate row for each field in each index. We'll group these. */ sqlSafef(query, sizeof(query), "show indexes from `%s`", tableName); sr = sqlGetResult(conn, query); while ((row = sqlNextRow(sr)) != NULL) { ii = indexInfoLoad(row); group = hashFindVal(groupHash, ii->indexName); if (group == NULL) { AllocVar(group); hashAddSaveName(groupHash, ii->indexName, group, &group->name); slAddTail(&groupList, group); } slAddTail(&group->fieldList, ii); } sqlFreeResult(&sr); fprintf(f, "%s has ", ti->name); if (!justSchema) fprintf(f, "%d rows and ", ti->status->rows); slSort(&groupList, indexGroupCmp); fprintf(f, "%d indexes\n", slCount(groupList)); for (group = groupList; group != NULL; group = group->next) { long long maxCardinality = 0, nonUnique = FALSE; fprintf(f, "\t"); fprintf(f, "%s: ", group->name); slSort(&group->fieldList, indexInfoCmp); for (ii=group->fieldList; ii != NULL; ii = ii->next) { fprintf(f, "%s", ii->field); if (ii->next != NULL) fprintf(f, "+"); if (ii->cardinality > maxCardinality) maxCardinality = ii->cardinality; nonUnique = ii->nonUnique; } fprintf(f, "\t%s", (nonUnique ? "MUL" : "PRI") ); if (maxCardinality > 0 && !isSplit && !justSchema) fprintf(f, "\t%lld", maxCardinality); else fprintf(f, "\tn/a"); fprintf(f, "\n"); } hashFree(&groupHash); } struct sqlConnection *dbConnect(char *database) /* Connect to database, possibly remotely. */ { char *host = optionVal("host", NULL); char *user = optionVal("user", NULL); char *password = optionVal("password", NULL); struct slPair *settings = NULL; slPairAdd(&settings, "host", host); slPairAdd(&settings, "user", user); slPairAdd(&settings, "password", password); // TODO could add support for other connection params like port, socket, ssl-params if (host != NULL || user != NULL || password != NULL) return sqlConnectRemoteFull(settings, database); else return sqlConnectProfile(profile, database); } void dbSnoop(char *database, char *output) /* dbSnoop - Produce an overview of a database.. */ { FILE *f = mustOpen(output, "w"); struct sqlConnection *conn = dbConnect(database); struct sqlConnection *conn2 = dbConnect(database); int majorVersion = sqlMajorVersion(conn); int minorVersion = sqlMinorVersion(conn); struct sqlResult *sr = sqlGetResult(conn, NOSQLINJ "show table status"); char **row; struct hash *fieldHash = hashNew(0); struct hash *typeHash = hashNew(0); struct hash *tableHash = hashNew(0); struct fieldInfo *fiList = NULL, *fi; struct tableInfo *tiList = NULL, *ti; struct tableType *ttList = NULL, *tt; long long totalData = 0, totalIndex = 0, totalRows = 0; int totalFields = 0; /* Collect info from database. */ while ((row = sqlNextRow(sr)) != NULL) { struct tableStatus *status; if ((majorVersion > 4) || ((4 == majorVersion) && (minorVersion > 0))) memcpy(row+2, row+3, (TABLESTATUS_NUM_COLS-2)*sizeof(char*)); if (row[3]) { status = tableStatusLoad(row); if (skipTable == NULL || differentString(status->name, skipTable)) tableSummary(status, conn2, f, fieldHash, &fiList, tableHash, &tiList, typeHash, &ttList); } } sqlFreeResult(&sr); if (unsplit) unsplitTables(conn, &tiList); /* Print overall database summary. */ for (ti = tiList; ti != NULL; ti = ti->next) { totalFields += slCount(ti->fieldList); totalData += ti->status->dataLength; totalIndex += ti->status->indexLength; totalRows += ti->status->rows; } fprintf(f, "DATABASE SUMMARY:\t%s\n", database); printTaggedLong(f, "tables", slCount(tiList)); printTaggedLong(f, "fields", totalFields); if (!justSchema) { printTaggedLong(f, "bytes", totalIndex + totalData); fprintf(f, "\t"); printTaggedLong(f, "data", totalData); fprintf(f, "\t"); printTaggedLong(f, "index", totalIndex); printTaggedLong(f, "rows", totalRows); } fprintf(f, "\n"); /* Print summary of table sizes ordered by size. */ if (!justSchema) { slSort(&tiList, tableInfoCmpSize); fprintf(f, "TABLE SIZE SUMMARY:\n"); fprintf(f, "%s\t%s\t%s\t%s\t%s\n", "#bytes","name","rows","data","index"); for (ti = tiList; ti != NULL; ti = ti->next) { fprintf(f, "%s\t%s\t%s\t%s\t%s\n" , toLongNumber(ti->status->dataLength + ti->status->indexLength) , ti->name , toLongNumber(ti->status->rows) , toLongNumber(ti->status->dataLength) , toLongNumber(ti->status->indexLength) ); } fprintf(f, "\n"); /* Print summary of table sizes ordered by size. */ slSort(&tiList, tableInfoCmpUpdateTime); fprintf(f, "TABLE UPDATE SUMMARY:\n"); fprintf(f, "%s\t%s\t%s\t%s\n", "#table", "update time", "create time", "checkTime"); for (ti = tiList; ti != NULL; ti = ti->next) { fprintf(f, "%s\t%s\t%s\t%s\n", ti->name, naForNull(ti->status->updateTime), ti->status->createTime, naForNull(ti->status->checkTime)); } fprintf(f, "\n"); } /* Print summary of rows and fields in each table ordered alphabetically */ slSort(&tiList, tableInfoCmpName); fprintf(f, "TABLE FIELDS SUMMARY:\n"); fprintf(f, "%s\t%s\n", "#name", "fields"); for (ti = tiList; ti != NULL; ti = ti->next) { fprintf(f, "%s\t", ti->name); slReverse(&ti->fieldList); if (sortAlpha) slSort(&ti->fieldList, fieldInfoCmpName); struct fieldDescription *field; for (field = ti->fieldList; field != NULL; field = field->next) fprintf(f, "%s,", field->name); fprintf(f, "\n"); } fprintf(f, "\n"); /* Print summary of indexes. */ fprintf(f, "TABLE INDEX SUMMARY\n"); for (ti = tiList; ti != NULL; ti = ti->next) printIndexes(f, conn, ti); fprintf(f, "\n"); /* Print summary of fields and tables fields are used in * ordered by the number of tables a field is in. */ slSort(&fiList, fieldInfoCmp); fprintf(f, "FIELD SUMMARY:\t%d\n", slCount(fiList)); fprintf(f, "#uses\tname\ttables\n"); for (fi = fiList; fi != NULL; fi = fi->next) { struct slName *t; slReverse(&fi->tableList); fprintf(f, "%d\t%s\t", fi->tableCount, fi->name); for (t = fi->tableList; t != NULL; t = t->next) fprintf(f, "%s,", t->name); fprintf(f, "\n"); } fprintf(f, "\n"); /* Print summary of types and tables that use types * ordered by the number of tables a type is used by. */ slSort(&ttList, tableTypeCmp); fprintf(f, "TABLE TYPE SUMMARY (%d types):\n", slCount(ttList)); fprintf(f, "#uses\ttables\tfields\n"); for (tt = ttList; tt != NULL; tt = tt->next) { struct slName *t; slReverse(&tt->tableList); fprintf(f, "%d\t", tt->tableCount); for (t = tt->tableList; t != NULL; t = t->next) fprintf(f, "%s,", t->name); fprintf(f, "\t%s\n", tt->fields); } carefulClose(&f); sqlDisconnect(&conn); } int main(int argc, char *argv[]) /* Process command line. */ { optionInit(&argc, argv, options); profile = optionVal("profile", getDefaultProfileName()); if (argc != 3) usage(); noNumberCommas = optionExists("noNumberCommas"); unsplit = optionExists("unsplit"); justSchema = optionExists("justSchema"); skipTable = optionVal("skipTable", skipTable); sortAlpha = optionExists("sortAlpha"); dbSnoop(argv[1], argv[2]); return 0; }