// LastK returns the k most recent command lines in history
func (d Database) LastK(qp conf.QueryParams) ([]byte, error) {
var rows *sql.Rows
var err error
switch qp.Unique {
case true:
rows, err = d.Query(`SELECT * FROM
(SELECT rowid, * FROM history
WHERE user LIKE ? AND host LIKE ? AND command LIKE ? ESCAPE '\'
GROUP BY command
ORDER BY datetime DESC LIMIT ?)
ORDER BY datetime ASC`,
qp.User, qp.Host, qp.Command, qp.Kappa)
default:
rows, err = d.Query(`SELECT * FROM
(SELECT rowid, * FROM history
WHERE user LIKE ? AND host LIKE ? AND command LIKE ? ESCAPE '\'
ORDER BY datetime DESC LIMIT ?)
ORDER BY datetime ASC`,
qp.User, qp.Host, qp.Command, qp.Kappa)
}
if err != nil {
return []byte{}, err
}
defer rows.Close()
res := result.New(qp.Format)
for rows.Next() {
var user, host, command string
var t time.Time
var row int
rows.Scan(&row, &user, &host, &command, &t)
res.AddRow(row, user, host, command, t)
}
return res.Formatted(), nil
}
// DefaultQuery returns history within the search criteria in the format requested
func (d Database) DefaultQuery(qp conf.QueryParams) ([]byte, error) {
// SQLite's regexp extension is problematic; most systems don't have it, loading
// it is extremely error prone (almost impossible to get right), even we manage
// to load it, it doesn't seem to work with our queries. Thus I use go's regexp
// library. This makes bashistdb slower when in regexp mode since it has to process
// all the history entries itself. Also it makes difficult to add pcre support
// to anything but the DefaultQuery
var regex *regexp.Regexp
var err error
commandQuery := "AND command LIKE ?" // This is used for normal searches. Fast.
if qp.Regex {
regex, err = regexp.Compile(qp.Command)
if err != nil {
return []byte{}, err
}
commandQuery = "" // For PCRE we do the search, so we want everything. Slow.
}
var rows *sql.Rows
switch qp.Unique {
case true:
rows, err = d.Query(`SELECT rowid, * FROM history
WHERE user LIKE ? AND host LIKE ? `+commandQuery+` ESCAPE '\'
GROUP BY command ORDER BY DATETIME ASC`,
qp.User, qp.Host, qp.Command)
default:
rows, err = d.Query(`SELECT rowid, * FROM history
WHERE user LIKE ? AND host LIKE ? `+commandQuery+` ESCAPE '\'`,
qp.User, qp.Host, qp.Command)
}
if err != nil {
return nil, err
}
defer rows.Close()
res := result.New(qp.Format)
for rows.Next() {
var user, host, command string
var t time.Time
var row int
rows.Scan(&row, &user, &host, &command, &t)
switch qp.Regex {
case true:
if regex.MatchString(command) {
res.AddRow(row, user, host, command, t)
}
default:
res.AddRow(row, user, host, command, t)
}
}
// Return the result without the newline at the end.
return res.Formatted(), nil
}
// TopK returns the k most frequent command lines in history
func (d Database) TopK(qp conf.QueryParams) ([]byte, error) {
rows, err := d.Query(`SELECT command, count(*) as count FROM history
WHERE user LIKE ? AND host LIKE ? AND command LIKE ? ESCAPE '\'
GROUP BY command ORDER BY count DESC LIMIT ?`,
qp.User, qp.Host, qp.Command, qp.Kappa)
if err != nil {
return []byte{}, err
}
defer rows.Close()
res := result.New("")
for rows.Next() {
var command string
var count int
rows.Scan(&command, &count)
res.AddCountRow(count, command)
}
return res.Formatted(), err
}
// ContentQuery returns matches of a row plus rows before or after the match.
// Think of it as grep -A(fter) / -B(efore) / -C(ontent)
// It works on 4 stages:
// 1. find matches and get their datetime
// 2. for each match, get the content asked by rowid
// 3. if the content of two matches overlap, join them
// 4. given the sets of rowids, get them from the database
func (d Database) ContentQuery(qp conf.QueryParams) ([]byte, error) {
// Using Goland regexp library. Check DefaultQuery() for more info.
var regex *regexp.Regexp
var err error
commandQuery := "AND command LIKE ?" // This is used for normal searches. Fast.
if qp.Regex {
regex, err = regexp.Compile(qp.Command)
if err != nil {
return []byte{}, err
}
commandQuery = "" // For PCRE we do the search, so we want everything. Slow.
}
// Stage 1: find matches and get an array with their datetime
var rows *sql.Rows
rows, err = d.Query(`SELECT datetime, command FROM history
WHERE user LIKE ? AND host LIKE ? `+commandQuery+` ESCAPE '\'`,
qp.User, qp.Host, qp.Command)
if err != nil {
return nil, err
}
defer rows.Close()
var hits []time.Time
for rows.Next() {
var t time.Time
var command string
rows.Scan(&t, &command)
switch qp.Regex {
case true:
if regex.MatchString(command) {
hits = append(hits, t)
}
default:
hits = append(hits, t)
}
}
// Stage 2: for each match create a slice with its content by rowid
var hitsContent [][]int
for _, v := range hits {
var content []int
// Before query also includes the current command, thus is always run.
rows, err = d.Query(`SELECT rowid, datetime FROM
(SELECT rowid, datetime FROM history
WHERE datetime <= ? AND user LIKE ? AND host LIKE ? ESCAPE '\'
ORDER BY datetime DESC LIMIT ?)
ORDER BY datetime ASC`,
v, qp.User, qp.Host, qp.BeforeContent+1) // Here we include current query to before
if err != nil {
return nil, err
}
defer rows.Close()
for rows.Next() {
var row int
var datetime time.Time
rows.Scan(&row, &datetime)
content = append(content, row)
}
// After runs only if needed.
if qp.AfterContent > 0 {
rows, err = d.Query(`SELECT rowid, datetime FROM history
WHERE datetime > ? AND user LIKE ? AND host LIKE ? ESCAPE '\'
ORDER BY datetime ASC LIMIT ?`,
v, qp.User, qp.Host, qp.AfterContent)
if err != nil {
return nil, err
}
defer rows.Close()
for rows.Next() {
var row int
var datetime time.Time
rows.Scan(&row, &datetime)
content = append(content, row)
}
}
hitsContent = append(hitsContent, content)
}
// Stage 3: let's merge the sets that overlap
for i := len(hitsContent) - 2; i >= 0; i-- {
for k, v := range hitsContent[i] {
if v == hitsContent[i+1][0] {
hitsContent[i] = append(hitsContent[i][:k], hitsContent[i+1]...)
if len(hitsContent) > i+2 {
hitsContent = append(hitsContent[:i+1], hitsContent[i+2:]...)
} else {
hitsContent = hitsContent[:i+1]
}
break
}
}
}
var out bytes.Buffer
// Stage 4: get the tuples for each set's rowids and add them formatted to the result
for i := 0; i < len(hitsContent); i++ {
var rowids []string
for _, v := range hitsContent[i] {
rowids = append(rowids, strconv.Itoa(v))
}
rows, err = d.Query(`SELECT rowid, * FROM history
WHERE rowid IN (` + strings.Join(rowids, ",") + `)
ORDER BY datetime ASC`)
if err != nil {
return nil, err
}
defer rows.Close()
res := result.New(qp.Format)
for rows.Next() {
var user, host, command string
var t time.Time
var row int
rows.Scan(&row, &user, &host, &command, &t)
res.AddRow(row, user, host, command, t)
}
out.Write(res.Formatted())
if i < len(hitsContent)-1 {
out.WriteString("\n------------------\n")
}
}
return out.Bytes(), nil
}