1

How do I get started with an sqlite3 database?

For example, there's http://openports.se/databases/sqlports that contains various info about OpenBSD ports; I've installed it on OpenBSD 5.2, and the only file it created, as per /var/db/pkg/sqlports-2.3p0/+CONTENTS, is share/sqlports in /usr/local.

% file /usr/local/share/sqlports
/usr/local/share/sqlports: SQLite 3.x database

How do I know which tables it has, and what fields does each table has?

cnst
  • 25,870
  • 6
  • 90
  • 122

2 Answers2

1

The following seems to do the job, with the SQL query taken from 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
%
Community
  • 1
  • 1
cnst
  • 25,870
  • 6
  • 90
  • 122
1

cnst your answer is right. You can also use the . commands . for example to list the databases you enter .databases and to get the tables .tables

Try .help for more possible commands.

Edit: As for OpenBSD pkg info you can find them in a filesystem friendly format, in directories under /var/db/pkg/

ramrunner
  • 1,362
  • 10
  • 20
  • yeap, `.tables` works nicely, but it doesn't show the fields, only the tables. how would i get the fields? also, `/var/db/pkg/` would only contain installed packages, so, it doesn't cut it at all. – cnst Feb 21 '13 at 18:34
  • check out .schema . Also check this SO question (i prefer the PRAGMA answer) http://stackoverflow.com/questions/685206/sqlite-how-to-get-a-list-of-column-names . Finally my preffered way to manage pkgstuff is to have /usr/ports checked out from CVS and then by looking at the Makefiles and the PLISTS you can get all the info you want. Finally i suppose that you have read the manpage of pkg_info :) – ramrunner Feb 22 '13 at 11:31