Created
September 20, 2012 22:59
-
-
Save mon-key/3758846 to your computer and use it in GitHub Desktop.
Some CL for examining the SQLite dbs dumpbed by MyStuff2
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| ;;; :FILE-CREATED <Timestamp: #{2012-06-05T14:40:40-04:00Z}#{12232} - by MON> | |
| ;;; :FILE mystuff2-model/mystuff2-sqlite.lisp | |
| ;;; ============================== | |
| #| | |
| As of 2012-09-20 unverified with version Mystuff2 v2.0 | |
| Known to work with MyStuff2 version 1.4x with the following CL implementation: | |
| (concatenate 'string (lisp-implementation-type) " " (lisp-implementation-version)) | |
| => "SBCL 1.0.55.4-eee9d17" | |
| The commented examples below and in the doc-strings assume that the connected | |
| database has a collection named "fashion-inventory" which contains the | |
| categories "show-inventory-wearable" and "show-inventory-buttons", each of which | |
| have an attribute named ":inventory-number" the values of which acts as a | |
| ``primary-key'' for these categories. | |
| --- | |
| Usage: | |
| Make sure CL sqlite package is loaded into our system: | |
| (quicklisp:quickload 'sqlite) | |
| (defpackage #:mystuff2-model (:use #:common-lisp)) | |
| (in-package #:mystuff2-model) | |
| --- | |
| To initialize the sqlite database connection assign a path for | |
| `*mystuff2-sqlite-db-pathname*' with `set-mystuff2-sqlite-db-pathname' then | |
| establish an sqlite:sqlite-handle by connecting to the db at the path with | |
| `connect-mystuff2-db'. | |
| :EXAMPLE | |
| (connect-mystuff2-db | |
| (set-mystuff2-sqlite-db-pathname #P"/<SOME-PATH-TO>/mystuff.db")) | |
| We can now prepare statements for executing SQL queries with the handle. | |
| Disconnect the sqlite:sqlite-handle with `disconnect-mystuff2-db'. | |
| |# | |
| (in-package #:mystuff2-model) | |
| (defparameter *mystuff2-sqlite-db-connection* nil) | |
| (defparameter *mystuff2-sqlite-db-pathname* nil | |
| "A pathname to a MyStuff2 sqlite database file. | |
| Normally these have the `cl:file-namestring': | |
| \"mystuff.db\" | |
| :SEE-ALSO `set-mystuff2-sqlite-db-pathname', `*mystuff2-sqlite-db-pathname*'.~%▶▶▶") | |
| (defun set-mystuff2-sqlite-db-pathname (database-pathname) | |
| "Set value of `*mystuff2-sqlite-db-pathname*' to pathname DATABASE-PATHNAME. | |
| An error is signalled if DATABASE-PATHNAME does not satisfy `cl:probe-file'. | |
| :EXAMPLE | |
| \(set-mystuff2-sqlite-db-pathname #P\"/<some-path-to>/mystuff.db\"\)" | |
| (declare (pathname database-pathname)) | |
| (setf *mystuff2-sqlite-db-pathname* | |
| (or (probe-file database-pathname) | |
| (error ":FUNCTION `set-mystuff2-sqlite-database-pathname' -- arg DATABASE-PATHNAME does not satisfy `cl:probe-file'~% got: ~S" | |
| database-pathname)))) | |
| ;; :memory is the temporary in-memory database | |
| (defun connect-mystuff2-db (&key (database-pathname *mystuff2-sqlite-db-pathname*)) | |
| "Connect to the sqlite database at DATABASE-PATHNAME and return an object of | |
| type `sqlite:sqlite-handle'. | |
| An error is signalled if DATABASE-PATHNAME does not satisfy `cl:probe-file'. | |
| :EXAMPLE | |
| \(connect-mystuff2-db :database-pathname *mystuff2-sqlite-db-pathname*\)" | |
| (declare (pathname database-pathname)) | |
| (setf *mystuff2-sqlite-db-connection* | |
| (sqlite:connect | |
| (or (probe-file database-pathname) | |
| (error ":FUNCTION `connect-mystuff2-db' -- arg DATABASE-PATHNAME does not satisfy `cl:probe-file'~% got: ~S" | |
| database-pathname))))) | |
| (defun disconnect-mystuff2-db (&optional (db-handle *mystuff2-sqlite-db-connection*)) | |
| "Disconnect the handle to database specified by DB-HANDLE and return as values | |
| DB-HANDLE and a boolean indicating whether the handle was disconnected. | |
| DB-HANDLE is an object of type `sqlite:sqlite-handle'. | |
| If the sqlite::handle slot is `cl:slot-boundp' it is disconnected as if by | |
| `sqlite:disconnect' else no action is performed on DB-HANDLE. | |
| :EXAMPLE | |
| \(disconnect-mystuff2-db *mystuff2-sqlite-db-connection*\)" | |
| (declare (sqlite:sqlite-handle db-handle)) | |
| (check-type *mystuff2-sqlite-db-connection* sqlite:sqlite-handle) | |
| (if (and (slot-exists-p db-handle 'sqlite::handle) | |
| (slot-boundp db-handle 'sqlite::handle)) | |
| (values (sqlite:disconnect db-handle) t) | |
| (values db-handle nil))) | |
| ;; (execute-to-vector *mystuff2-sqlite-db-connection* "SELECT item_value.text_value FROM item_value;") | |
| (defun execute-to-vector (db sql &rest parameters) | |
| "Executes the query SQL to the database DB with given PARAMETERS. | |
| Like sqlite:execute-to-list but return value is a a vector of lists. | |
| Example: | |
| \(execute-to-vector db \"select id, user_name, real_name from users where user_name = ?\" \"joe\") | |
| => | |
| \#((1 \"joe\" \"Joe the User\") | |
| (2 \"joe\" \"Another Joe\")) | |
| See BIND-PARAMETER for the list of supported parameter types." | |
| (declare (dynamic-extent parameters)) | |
| (sqlite::with-prepared-statement var-statement (db sql parameters) | |
| (let ((result (make-array 0 :fill-pointer 0))) | |
| (loop | |
| (if (sqlite:step-statement var-statement) | |
| (vector-push-extend | |
| (iter:iter (iter:for i iter::from 0 iter::below (the fixnum (sqlite-ffi:sqlite3-column-count (sqlite::handle var-statement)))) | |
| (declare (type fixnum i)) | |
| (iter:collect (sqlite:statement-column-value var-statement i))) | |
| result) | |
| (return))) | |
| result))) | |
| ;; (execute-to-vector *mystuff2-sqlite-db-connection* "select * from item_value where attribute_id = ?" "352758417769") | |
| ;; (category-name-to-id "show-inventory-wearable") => 355763820224 | |
| ;; (category-name-to-id "show-inventory-buttons") => 355763818823 | |
| ;; SELECT `category_id` FROM `category` WHERE `name` = <STRING>; | |
| (defun category-name-to-id (category-name | |
| &key (db-connection *mystuff2-sqlite-db-connection*)) | |
| (declare (mon:string-not-empty category-name)) | |
| (sqlite:execute-single db-connection | |
| "SELECT category_id | |
| FROM category | |
| WHERE name = ?" category-name)) | |
| ;; (category-id-to-name 355763818823) => "show-inventory-buttons" | |
| ;; (category-id-to-name (category-name-to-id "show-inventory-buttons")) ;=> "show-inventory-buttons" | |
| ;; SELECT `name` FROM `category` WHERE `category_id` = <INTEGER>; | |
| (defun category-id-to-name (category-id | |
| &key (db-connection *mystuff2-sqlite-db-connection*)) | |
| ;; (declare (integer category-id)) | |
| (sqlite:execute-single db-connection | |
| "SELECT name | |
| FROM category | |
| WHERE category_id = ?" category-id)) | |
| ;; (attribute-id-category-id 359608984821) => 359588954972 | |
| ;; (category-id-to-name 359588954972) | |
| ;; broken b/c the parent_id might also be an action_phase | |
| (defun attribute-id-category-id (attribute-id &key (db-connection *mystuff2-sqlite-db-connection*)) | |
| (sqlite:execute-single db-connection | |
| "SELECT parent_id | |
| FROM attribute | |
| WHERE attribute_id = ?" attribute-id)) | |
| ;; (attribute-id-to-name 359608984821) => ":inventory-condition-rating" | |
| (defun attribute-id-to-name (attribute-id | |
| &key (db-connection *mystuff2-sqlite-db-connection*)) | |
| ;; (declare (integer category-id)) | |
| (sqlite:execute-single db-connection | |
| "SELECT name | |
| FROM attribute | |
| WHERE attribute_id = ?" attribute-id)) | |
| ;; (item-id-category-id 353345209773) | |
| (defun item-id-category-id (item-id | |
| &key (db-connection *mystuff2-sqlite-db-connection*)) | |
| (sqlite:execute-single db-connection | |
| "SELECT category_id | |
| FROM item | |
| WHERE item_id = ?" item-id)) | |
| ;; (item-id-to-category-name "355763820635") | |
| ;; (item-id-to-category-name "355763820635c") | |
| (defun item-id-to-category-name (item-id &key (db-connection *mystuff2-sqlite-db-connection*)) | |
| (let ((maybe-cat-id (item-id-category-id item-id :db-connection db-connection))) | |
| (when maybe-cat-id | |
| (category-id-to-name maybe-cat-id :db-connection db-connection)))) | |
| ;; (category-set-name-to-category-set-id "fashion-inventory") => 355763818796 | |
| (defun category-set-name-to-category-set-id (category-set-name | |
| &key (db-connection *mystuff2-sqlite-db-connection*)) | |
| (sqlite:execute-single db-connection | |
| "SELECT category_set.set_id | |
| FROM category_set | |
| WHERE category_set.name = ?" category-set-name)) | |
| ;; (category-set-id-to-category-set-name "show-inventory") => 355763818796 | |
| ;; (category-set-id-to-category-set-name (category-set-name-to-category-set-id "fashion-inventory")) => "fashion-inventory" | |
| (defun category-set-id-to-category-set-name (category-set-id | |
| &key (db-connection *mystuff2-sqlite-db-connection*)) | |
| (sqlite:execute-single db-connection | |
| "SELECT category_set.name | |
| FROM category_set | |
| WHERE category_set.set_id = ?" category-set-id)) | |
| ;; (all-category-ids-with-category-set-id 355763818796) | |
| ;; (all-category-ids-with-category-set-id (category-set-name-to-category-set-id "fashion-inventory")) | |
| (defun all-category-ids-with-category-set-id (category-set-id | |
| &key (db-connection *mystuff2-sqlite-db-connection*)) | |
| (loop | |
| with statement = (sqlite:prepare-statement db-connection | |
| "SELECT category_id | |
| FROM category_set_cat | |
| WHERE category_set_cat.set_id = ?;") | |
| initially (sqlite:bind-parameter statement 1 category-set-id) | |
| while (sqlite:step-statement statement) | |
| collect (sqlite:statement-column-value statement 0) | |
| finally (sqlite:finalize-statement statement))) | |
| ;; (all-category-ids-with-category-set-name "fashion-inventory") | |
| ;; => (355763818667 355763818823 355763820224 355763820231 355763820239 | |
| ;; 355763820247 355763820613 355798450706) | |
| (defun all-category-ids-with-category-set-name (category-set-name | |
| &key (db-connection *mystuff2-sqlite-db-connection*)) | |
| (let ((maybe-cat-set-id (category-set-name-to-category-set-id category-set-name :db-connection db-connection))) | |
| (when maybe-cat-set-id | |
| (all-category-ids-with-category-set-id maybe-cat-set-id :db-connection db-connection)))) | |
| ;; (all-category-names-with-category-set-name "fashion-inventory") | |
| (defun all-category-names-with-category-set-name (category-set-name | |
| &key (db-connection *mystuff2-sqlite-db-connection*)) | |
| (let* ((maybe-cat-set-id (category-set-name-to-category-set-id category-set-name :db-connection db-connection)) | |
| (maybe-category-ids | |
| (when maybe-cat-set-id | |
| (all-category-ids-with-category-set-id maybe-cat-set-id :db-connection db-connection)))) | |
| (when maybe-category-ids | |
| (loop | |
| for cat-id in maybe-category-ids | |
| when (category-id-to-name cat-id :db-connection db-connection) | |
| collect it)))) | |
| ;; (category-set-id-for-category-id "355763818823") => 355763818796 | |
| (defun category-set-id-for-category-id (category-id | |
| &key (db-connection *mystuff2-sqlite-db-connection*)) | |
| (sqlite:execute-single db-connection | |
| "SELECT category_set_cat.set_id | |
| FROM category_set_cat | |
| WHERE category_set_cat.category_id = ?" category-id)) | |
| ;; (category-set-name-for-category-id "355763818823") => "fashion-inventory" | |
| (defun category-set-name-for-category-id (category-id | |
| &key (db-connection *mystuff2-sqlite-db-connection*)) | |
| (let ((maybe-cat-set-id (category-set-id-for-category-id category-id :db-connection db-connection))) | |
| (when maybe-cat-set-id | |
| (sqlite:execute-single db-connection | |
| "SELECT category_set.name | |
| FROM category_set | |
| WHERE category_set.set_id = ?" maybe-cat-set-id)))) | |
| ;; (all-item-ids-with-category-id 355763820239 ) | |
| ;; (length (all-item-ids-with-category-id 355763820224)) | |
| ;; (length (all-item-ids-with-category-id 355763820239)) | |
| (defun all-item-ids-with-category-id (category-id | |
| &key (db-connection *mystuff2-sqlite-db-connection*)) | |
| (loop | |
| with statement = (sqlite:prepare-statement db-connection | |
| "SELECT item.item_id | |
| FROM item | |
| WHERE item.category_id = ?") | |
| initially (sqlite:bind-parameter statement 1 category-id) | |
| while (sqlite:step-statement statement) | |
| collect (sqlite:statement-column-value statement 0) | |
| finally (sqlite:finalize-statement statement))) | |
| ;; (category-set-name-to-category-set-id "fashion-inventory") => 355763818796 | |
| ;; (length (all-item-ids-with-category-set-id 355763818796)) => 1846 | |
| (defun all-item-ids-with-category-set-id (category-set-id | |
| &key (db-connection *mystuff2-sqlite-db-connection*)) | |
| (let ((categories (all-category-ids-with-category-set-id category-set-id :db-connection db-connection))) | |
| (when categories | |
| (loop | |
| for category in categories | |
| nconcing (all-item-ids-with-category-id category :db-connection db-connection))))) | |
| ;; (attribute-id-with-attribute-name-and-category-id ":inventory-number" 355763820224) | |
| (defun attribute-id-with-attribute-name-and-category-id (attribute-name category-id | |
| &key (db-connection *mystuff2-sqlite-db-connection*)) | |
| (sqlite:execute-single db-connection | |
| "SELECT attribute.attribute_id | |
| FROM attribute | |
| WHERE parent_id = ? | |
| AND name = ?;" category-id attribute-name)) | |
| ;; (all-attribute-ids-with-attribute-name-and-category-set-name ":inventory-number" "fashion-inventory") | |
| (defun all-attribute-ids-with-attribute-name-and-category-set-name (attribute-name category-set-name | |
| &key (db-connection *mystuff2-sqlite-db-connection*)) | |
| (let* ((category-set-id (category-set-name-to-category-set-id category-set-name :db-connection db-connection)) | |
| (categories (and category-set-id (all-category-ids-with-category-set-id category-set-id :db-connection db-connection)))) | |
| (when categories | |
| (loop | |
| for category in categories | |
| collect (attribute-id-with-attribute-name-and-category-id attribute-name category :db-connection db-connection))))) | |
| ;; (all-attribute-ids-with-attribute-name-and-category-set-id ":inventory-number" 355763818796) | |
| ;; (length (all-attribute-ids-with-attribute-name-and-category-set-id ":inventory-number" 355763818796) | |
| (defun all-attribute-ids-with-attribute-name-and-category-set-id (attribute-name category-set-id | |
| &key (db-connection *mystuff2-sqlite-db-connection*)) | |
| (let ((categories (all-category-ids-with-category-set-id category-set-id :db-connection db-connection))) | |
| (when categories | |
| (loop | |
| for category in categories | |
| collect (attribute-id-with-attribute-name-and-category-id attribute-name category :db-connection db-connection))))) | |
| ;; (all-item-ids-and-text-values-with-attribute-id-as-conses 355763820225) | |
| ;; (all-item-ids-and-text-values-with-attribute-id-as-conses 355763820225 :item-ids-as-string t) | |
| (defun all-item-ids-and-text-values-with-attribute-id-as-conses (attribute-id | |
| &key (db-connection *mystuff2-sqlite-db-connection*) | |
| (item-ids-as-string nil)) | |
| (loop | |
| with statement = (sqlite:prepare-statement db-connection | |
| "SELECT item_value.item_id, item_value.text_value | |
| FROM item_value | |
| WHERE item_value.attribute_id = ?;") | |
| initially (sqlite:bind-parameter statement 1 attribute-id) | |
| while (sqlite:step-statement statement) | |
| collect (cons (or (and item-ids-as-string (princ-to-string (sqlite:statement-column-value statement 0))) | |
| (sqlite:statement-column-value statement 0)) | |
| (sqlite:statement-column-value statement 1)) | |
| finally (sqlite:finalize-statement statement))) | |
| ;; (all-item-ids-and-text-values-with-attribute-id-to-hash-table 355763820225 (make-hash-table :test 'equal) :item-ids-as-string t) | |
| ;; Following errors successfully: | |
| ;; (all-item-ids-and-text-values-with-attribute-id-to-hash-table 355763820225 (make-hash-table) :item-ids-as-string t) | |
| (defun all-item-ids-and-text-values-with-attribute-id-to-hash-table (attribute-id hash-table &key (db-connection *mystuff2-sqlite-db-connection*) | |
| (item-ids-as-string nil) | |
| (%checking-hash-table-test% t)) | |
| (when (and item-ids-as-string %checking-hash-table-test%) | |
| (unless (eql (hash-table-test hash-table) 'equal) | |
| (error ":FUNCTION `all-item-ids-and-text-values-with-attribute-id-to-hash-table'~% ~ | |
| -- with keyword ITEM-IDS-AS-STRING non-nil HASH-TABLE with `cl:hash-table-test' not `equal'~% ~ | |
| hash-table: ~S~%" hash-table))) | |
| (loop | |
| with statement = (sqlite:prepare-statement db-connection | |
| "SELECT item_value.item_id, item_value.text_value | |
| FROM item_value | |
| WHERE item_value.attribute_id = ?;") | |
| initially (sqlite:bind-parameter statement 1 attribute-id) | |
| while (sqlite:step-statement statement) | |
| do (setf (gethash (or (and item-ids-as-string (princ-to-string (sqlite:statement-column-value statement 0))) | |
| (sqlite:statement-column-value statement 0)) | |
| hash-table) | |
| (sqlite:statement-column-value statement 1)) | |
| finally (sqlite:finalize-statement statement)) | |
| hash-table) | |
| ;; (length *tt-cons/hash*) | |
| ;; (defparameter *tt-cons/hash* nil) | |
| ;; (setf *tt-cons/hash* nil) | |
| ;; | |
| ;; (all-item-ids-and-text-values-with-attribute-name-and-category-set-name-as-conses ":inventory-number" | |
| ;; "fashion-inventory" | |
| ;; :item-ids-as-string t) | |
| (defun all-item-ids-and-text-values-with-attribute-name-and-category-set-name-as-conses (attribute-name category-set-name | |
| &key (db-connection *mystuff2-sqlite-db-connection*) | |
| (item-ids-as-string nil)) | |
| (let ((attribute-ids (all-attribute-ids-with-attribute-name-and-category-set-name attribute-name | |
| category-set-name | |
| :db-connection db-connection))) | |
| (when attribute-ids | |
| (loop | |
| for attribute-id in attribute-ids | |
| nconcing (all-item-ids-and-text-values-with-attribute-id-as-conses attribute-id | |
| :db-connection db-connection | |
| :item-ids-as-string item-ids-as-string))))) | |
| ;; (defparameter *item-ids-hash-table* nil) | |
| ;; (setf *item-ids-hash-table* | |
| ;; (all-item-ids-and-text-values-with-attribute-name-and-category-set-name-to-hash-table ":inventory-number" | |
| ;; "fashion-inventory" | |
| ;; (make-hash-table :test 'equal) | |
| ;; :item-ids-as-string t)) | |
| ;; Following errors successfully: | |
| ;; (all-item-ids-and-text-values-with-attribute-name-and-category-set-name-to-hash-table ":inventory-number" | |
| ;; "fashion-inventory" | |
| ;; (make-hash-table) | |
| ;; :item-ids-as-string t) | |
| (defun all-item-ids-and-text-values-with-attribute-name-and-category-set-name-to-hash-table (attribute-name category-set-name hash-table | |
| &key (db-connection *mystuff2-sqlite-db-connection*) | |
| (item-ids-as-string nil)) | |
| "Return a hash-table with sqlite item-ids as hash-key and the value of the | |
| attribute-name associated with the item-id as hash-value. | |
| Returned hash-table is intended for use with `zip-entry-image-paths-for-extract'. | |
| The hash-key/hash-value pairs of returned hash-table have the form: | |
| <SQLITE-ITEM-ID> <ATTRIBUTE-VALUE> | |
| \"355763819863\" \"017064-BTTN\" | |
| :EXAMPLE | |
| \(defparameter *item-ids-hash-table* nil\) | |
| \(setf *item-ids-hash-table* | |
| \(all-item-ids-and-text-values-with-attribute-name-and-category-set-name-to-hash-table \":inventory-number\" | |
| \"fashion-inventory\" | |
| \(make-hash-table :test 'equal\) | |
| :item-ids-as-string t\)\) " | |
| (when item-ids-as-string | |
| (unless (eql (hash-table-test hash-table) 'equal) | |
| (error ":FUNCTION `all-item-ids-and-text-values-with-attribute-name-and-category-set-name-to-hash-table'~% ~ | |
| -- with keyword ITEM-IDS-AS-STRING non-nil HASH-TABLE with `cl:hash-table-test' not `equal'~% ~ | |
| hash-table: ~S~%" hash-table))) | |
| (let ((attribute-ids | |
| (all-attribute-ids-with-attribute-name-and-category-set-name attribute-name | |
| category-set-name | |
| :db-connection db-connection))) | |
| (when attribute-ids | |
| (loop | |
| for attribute-id in attribute-ids | |
| do (all-item-ids-and-text-values-with-attribute-id-to-hash-table | |
| attribute-id | |
| hash-table | |
| :db-connection db-connection | |
| :item-ids-as-string item-ids-as-string | |
| :%checking-hash-table-test% nil))) | |
| hash-table)) | |
| ;; (item-id-inventory-number 356161032450) => "018145-WEAR" | |
| ;; (item-id-inventory-number 356161032450 ":inventory-number") | |
| ;; | |
| ;; Following fails succesfully: | |
| ;; (item-id-inventory-number 356161032450 :inventory-id-key ":bar") | |
| ;; Following errors succesfully: | |
| ;; (item-id-inventory-number 1999999999999) | |
| ;; | |
| ;; :NOTE This is likely horribly inneficient. | |
| (defun item-id-inventory-number (item-id &key (inventory-id-key ":inventory-number") | |
| (db-connection *mystuff2-sqlite-db-connection*)) | |
| (declare (type (unsigned-byte 40) item-id) | |
| (string inventory-id-key)) | |
| (sqlite:execute-single db-connection | |
| ;; "SELECT item_value.text_value, item_value.item_id, attribute.attribute_id, attribute.name, attribute.parent_id, | |
| ;; "SELECT item_value.text_value, item_value.item_id, attribute.attribute_id, attribute.name | |
| "SELECT item_value.text_value | |
| FROM item_value, attribute | |
| WHERE item_value.item_id = ? | |
| AND item_value.attribute_id = attribute.attribute_id | |
| AND attribute.name = ?;" ; AND attribute.name = \":inventory-number\";" | |
| item-id inventory-id-key)) | |
| ;;; ============================== | |
| ;;; EOF |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment