;;; -*- Mode: LISP; Package: :cl-user; BASE: 10; Syntax: ANSI-Common-Lisp; -*-
;;;
;;;   Time-stamp: <2008-11-09 23:20:07 madhu>
;;;   Touched: Sun Nov 09 19:20:30 2008 +0530 <enometh@meer.net>
;;;   Bugs-To: enometh@meer.net
;;;   Status: Experimental.  Do not redistribute
;;;   Copyright (C) 2008 Madhu.  All Rights Reserved.
;;;
(defpackage "PLACES-SQLITE3"
  (:use "CL"))
(in-package "PLACES-SQLITE3")

(defvar *places.sqlite-pathname*
  (merge-pathnames ".conkeror.mozdev.org/conkeror/ljy7h6k5.default/places.sqlite"
                   (user-homedir-pathname)))

#+nil
(truename *places.sqlite-pathname*)

(defun open-places.sqlite (&key (database-name *places.sqlite-pathname*)
                           (database-type :sqlite3))
  (clsql:connect (list (namestring (truename database-name)))
                 :database-type database-type))

#+nil
(open-places.sqlite)


;;; ----------------------------------------------------------------------
;;;
;;;
(use-package "CLSQL")

;;CREATE TABLE moz_anno_attributes (id INTEGER PRIMARY KEY,name VARCHAR(32) UNIQUE NOT NULL);
(def-view-class moz_anno_attributes ()
  ((id
    :db-kind :key
    :type integer)
   (name
    :db-constraints (:unique :not-null)
    :type (string 32))))

;; CREATE TABLE moz_annos (id INTEGER PRIMARY KEY,place_id INTEGER NOT NULL,anno_attribute_id INTEGER,mime_type VARCHAR(32) DEFAULT NULL,content LONGVARCHAR, flags INTEGER DEFAULT 0,expiration INTEGER DEFAULT 0,type INTEGER DEFAULT 0,dateAdded INTEGER DEFAULT 0,lastModified INTEGER DEFAULT 0);
(def-view-class moz_annos ()
  ((id
    :db-kind :key
    :type integer)
   (place_id
    :db-constraints :not-null
    :type integer)
   (anno_attribute_id
    :type integer)
   (mime_type				;varchar(32)
    :initform nil
    :type (string 32))
   (content				;longvarchar
    :type string)
   (flags
    :initform 0
    :type integer)
   (expiration
    :initform 0
    :type integer)
   (dateAdded
    :initform 0
    :type integer)
   (lastModified
    :initform 0
    :type integer)))

;; CREATE TABLE moz_bookmarks (id INTEGER PRIMARY KEY,type INTEGER, fk INTEGER DEFAULT NULL, parent INTEGER, position INTEGER, title LONGVARCHAR, keyword_id INTEGER, folder_type TEXT, dateAdded INTEGER, lastModified INTEGER);
(def-view-class moz_bookmarks ()
  ((id
    :db-kind :key
    :type INTEGER)
   (fk
    :type INTEGER
    :initform nil)
   (parent
    :type INTEGER)
   (position
    :type INTEGER)
   (title				;LONGVARCHAR
    :type string)
   (keyword_id
    :type INTEGER)
   (folder_type
    :type TEXT)
   (dateAdded
    :type INTEGER)
   (lastModified
    :type INTEGER)))

;; CREATE TABLE moz_bookmarks_roots (root_name VARCHAR(16) UNIQUE, folder_id INTEGER);
(def-view-class moz_bookmarks_roots ()
  ((root_name
    :type (string 16)
    :db-constraints :UNIQUE)
   (folder_id
    :type INTEGER)))

;; CREATE TABLE moz_favicons (id INTEGER PRIMARY KEY, url LONGVARCHAR UNIQUE, data BLOB, mime_type VARCHAR(32), expiration LONG);
(def-view-class moz_favicons ()
  ((id
    :type INTEGER
    :db-kind :KEY)
   (url					;LONGVARCHAR
    :type string
    :db-constraints :UNIQUE)
   (data
    :type BLOB)
   (mime_type				;VARCHAR(32)
    :type (string 32))
   (expiration
    :type LONG)))

;; CREATE TABLE moz_historyvisits (id INTEGER PRIMARY KEY, from_visit INTEGER, place_id INTEGER, visit_date INTEGER, visit_type INTEGER, session INTEGER);
(def-view-class moz_historyvisits ()
  ((id
    :type INTEGER
    :db-kind :KEY)
   (from_visit
    :type INTEGER)
   (place_id
    :type INTEGER)
   (visit_date
    :type INTEGER)
   (visit
    :type INTEGER)
   (session
    :type INTEGER)))

;; CREATE TABLE moz_inputhistory (place_id INTEGER NOT NULL, input LONGVARCHAR NOT NULL, use_count INTEGER, PRIMARY KEY (place_id, input));
(def-view-class moz_inputhistory ()
  ((place_id
    :type INTEGER
    :db-kind :key
    :db-constraints :NOT-NULL)
   (input				;LONGVARCHAR
    :type string
    :db-kind :key
    :db-constraints :NOT-NULL)
   (use_count
    :type INTEGER)))			;

;; CREATE TABLE moz_items_annos (id INTEGER PRIMARY KEY,item_id INTEGER NOT NULL,anno_attribute_id INTEGER,mime_type VARCHAR(32) DEFAULT NULL,content LONGVARCHAR, flags INTEGER DEFAULT 0,expiration INTEGER DEFAULT 0,type INTEGER DEFAULT 0,dateAdded INTEGER DEFAULT 0,lastModified INTEGER DEFAULT 0);
(def-view-class moz_items_annos ()
  ((id
    :type INTEGER
    :db-kind KEY)
   (item_id
    :type INTEGER
    :db-constraints :NOT-NULL)
   (anno_attribute_id
    :type INTEGER)
   (mime_type
    :type (string 32)
    :initform nil)
   (content				;LONGVARCHAR
    :type string)
   (flags
    :type INTEGER
    :initform 0)
   (expiration
    :type INTEGER
    :initform 0)
   (type
    :type INTEGER
    :initform 0)
   (dateAdded
    :type INTEGER
    :initform 0)
   (lastModified
    :type INTEGER
    :initform  0)))

;; CREATE TABLE moz_places (id INTEGER PRIMARY KEY, url LONGVARCHAR, title LONGVARCHAR, rev_host LONGVARCHAR, visit_count INTEGER DEFAULT 0, hidden INTEGER DEFAULT 0 NOT NULL, typed INTEGER DEFAULT 0 NOT NULL, favicon_id INTEGER, frecency INTEGER DEFAULT -1 NOT NULL);
(def-view-class moz_places ()
  ((id
    :type INTEGER
    :DB-KIND :KEY)
   (url					;LONGVARCHAR
    :type string)
   (title				;LONGVARCHAR
    :type string)
   (rev_host				; LONGVARCHAR
    :type string)
   (visit_count
    :type INTEGER
    :INITFORM 0)
   (hidden
    :type INTEGER
    :INITFORM 0
    :DB-CONSTRAINTS :NOT-NULL)
   (typed
    :type INTEGER
    :INITFORM 0
    :DB-CONSTRAINTS :NOT-NULL)
   (favicon_id
    :type INTEGER)
   (frecency
    :type INTEGER
    :initform -1
    :DB-CONSTRAINTS :NOT-NULL)))

;; CREATE TABLE moz_keywords (id INTEGER PRIMARY KEY AUTOINCREMENT, keyword TEXT UNIQUE);
(def-view-class moz_keywords ()
  ((id
    :type INTEGER
    :DB-KIND :KEY
    :db-constraints :AUTOINCREMENT)
   (keyword				;TEXT
    :type string
    :db-constraints :UNIQUE)))


;;; ----------------------------------------------------------------------
;;;
;;;
#||
(clsql:start-sql-recording)
(clsql:select 'moz_places )
(clsql:select 'moz_bookmarks)

(clsql:locally-enable-sql-reader-syntax)
(clsql:restore-sql-reader-syntax-state)
(clsql:disconnect)

(car (clsql:select 'moz_places
              :where [= [slot-value 'moz_places 'id] 79]
              :flatp t))
||#