如何开始使用sqlite3
数据库?
例如,http://openports.se/databases/sqlports包含有关OpenBSD端口的各种信息。我已将其安装在OpenBSD 5.2上,并且根据/var/db/pkg/sqlports-2.3p0/+CONTENTS
创建的唯一文件是share/sqlports
中的/usr/local
。
% file /usr/local/share/sqlports
/usr/local/share/sqlports: SQLite 3.x database
我如何知道它具有哪些表以及每个表具有哪些字段?
最佳答案
下面的代码似乎可以完成这项工作,其中SQL查询取自List tables only show sqlite_sequence:
% sqlite3 /usr/local/share/sqlports \
'select * from sqlite_master where type = "table";' \
; uname -rms
table|Paths|Paths|2|CREATE TABLE Paths (FULLPKGPATH TEXT NOT NULL PRIMARY KEY, PKGPATH TEXT NOT NULL, CANONICAL TEXT NOT NULL)
table|Modules|Modules|4|CREATE TABLE Modules (FULLPKGPATH TEXT NOT NULL, VALUE TEXT NOT NULL, UNIQUE(FULLPKGPATH, VALUE))
table|Broken|Broken|6|CREATE TABLE Broken (FULLPKGPATH TEXT NOT NULL, ARCH TEXT, VALUE TEXT NOT NULL, UNIQUE(FULLPKGPATH, ARCH, VALUE))
table|Configure|Configure|8|CREATE TABLE Configure (FULLPKGPATH TEXT NOT NULL, VALUE TEXT NOT NULL, UNIQUE(FULLPKGPATH, VALUE))
table|OnlyForArch|OnlyForArch|10|CREATE TABLE OnlyForArch (FULLPKGPATH TEXT NOT NULL, VALUE TEXT NOT NULL, UNIQUE(FULLPKGPATH, VALUE))
table|Multi|Multi|13|CREATE TABLE Multi (FULLPKGPATH TEXT NOT NULL, VALUE TEXT NOT NULL, SUBPKGPATH TEXT NOT NULL, UNIQUE(FULLPKGPATH, VALUE))
table|Targets|Targets|16|CREATE TABLE Targets (FULLPKGPATH TEXT NOT NULL, VALUE TEXT NOT NULL, UNIQUE(FULLPKGPATH, VALUE))
table|PseudoFlavors|PseudoFlavors|18|CREATE TABLE PseudoFlavors (FULLPKGPATH TEXT NOT NULL, VALUE TEXT NOT NULL, UNIQUE(FULLPKGPATH, VALUE))
table|Shared_Libs|Shared_Libs|20|CREATE TABLE Shared_Libs (FULLPKGPATH TEXT NOT NULL, LIBNAME TEXT NOT NULL, VERSION TEXT NOT NULL, UNIQUE (FULLPKGPATH, LIBNAME))
table|MasterSites|MasterSites|22|CREATE TABLE MasterSites (FULLPKGPATH TEXT NOT NULL, N INTEGER, VALUE TEXT NOT NULL, UNIQUE(FULLPKGPATH, N, VALUE))
table|Depends|Depends|25|CREATE TABLE Depends (FULLPKGPATH TEXT NOT NULL, FULLDEPENDS TEXT NOT NULL, PKGSPEC TEXT, REST TEXT, DEPENDSPATH TEXT NOT NULL, TYPE TEXT NOT NULL)
table|Categories|Categories|26|CREATE TABLE Categories (FULLPKGPATH TEXT NOT NULL, VALUE TEXT NOT NULL, UNIQUE(FULLPKGPATH, VALUE))
table|Descr|Descr|28|CREATE TABLE Descr (FULLPKGPATH TEXT NOT NULL, VALUE TEXT NOT NULL, UNIQUE(FULLPKGPATH, VALUE))
table|ConfigureArgs|ConfigureArgs|30|CREATE TABLE ConfigureArgs (FULLPKGPATH TEXT NOT NULL, VALUE TEXT NOT NULL, UNIQUE(FULLPKGPATH, VALUE))
table|Flavors|Flavors|32|CREATE TABLE Flavors (FULLPKGPATH TEXT NOT NULL, VALUE TEXT NOT NULL, UNIQUE(FULLPKGPATH, VALUE))
table|NotForArch|NotForArch|34|CREATE TABLE NotForArch (FULLPKGPATH TEXT NOT NULL, VALUE TEXT NOT NULL, UNIQUE(FULLPKGPATH, VALUE))
table|Wantlib|Wantlib|37|CREATE TABLE Wantlib (FULLPKGPATH TEXT NOT NULL, VALUE TEXT NOT NULL, EXTRA TEXT, UNIQUE(FULLPKGPATH, VALUE))
table|Ports|Ports|39|CREATE TABLE Ports (FULLPKGPATH TEXT NOT NULL, AUTOCONF_VERSION TEXT, AUTOMAKE_VERSION TEXT, BROKEN TEXT, BUILD_DEPENDS TEXT, CATEGORIES TEXT NOT NULL, COMES_WITH TEXT, COMMENT TEXT, CONFIGURE_ARGS TEXT, CONFIGURE_STYLE TEXT, DESCR TEXT NOT NULL, DISTFILES TEXT, DISTNAME TEXT, DIST_SUBDIR TEXT, EPOCH TEXT, FLAVORS TEXT, FULLPKGNAME TEXT, HOMEPAGE TEXT, IGNORE TEXT, IS_INTERACTIVE TEXT, LIB_DEPENDS TEXT, MAINTAINER TEXT NOT NULL, MASTER_SITES TEXT, MASTER_SITES0 TEXT, MASTER_SITES1 TEXT, MASTER_SITES2 TEXT, MASTER_SITES3 TEXT, MASTER_SITES4 TEXT, MASTER_SITES5 TEXT, MASTER_SITES6 TEXT, MASTER_SITES7 TEXT, MASTER_SITES8 TEXT, MASTER_SITES9 TEXT, MODULES TEXT, MULTI_PACKAGES TEXT, NOT_FOR_ARCHS TEXT, NO_BUILD INTEGER, NO_REGRESS INTEGER, ONLY_FOR_ARCHS TEXT, PATCHFILES TEXT, PERMIT_DISTFILES_CDROM TEXT NOT NULL, PERMIT_DISTFILES_FTP TEXT NOT NULL, PERMIT_PACKAGE_CDROM TEXT NOT NULL, PERMIT_PACKAGE_FTP TEXT NOT NULL, PKGNAME TEXT, PKGSPEC TEXT, PKG_ARCH TEXT NOT NULL, PSEUDO_FLAVOR TEXT, PSEUDO_FLAVORS TEXT, REGRESS_DEPENDS TEXT, REGRESS_IS_INTERACTIVE TEXT, REVISION TEXT, RUN_DEPENDS TEXT, SEPARATE_BUILD TEXT NOT NULL, SHARED_LIBS TEXT NOT NULL, SHARED_ONLY INTEGER, STATIC_PLIST INTEGER, SUBPACKAGE TEXT, SUPDISTFILES TEXT, TARGETS TEXT NOT NULL, USE_GMAKE INTEGER, USE_GROFF INTEGER, USE_LIBTOOL INTEGER, VMEM_WARNING INTEGER, WANTLIB TEXT, UNIQUE(FULLPKGPATH))
OpenBSD 5.2 amd64
%