PChemGuy pchemguy

Keybase proof

I hereby claim:

I am pchemguy on github.
I am pchemguy (https://keybase.io/pchemguy) on keybase.
I have a public key ASBB8ld6r15llso4K_AUVXuY5qbvhnzmcqiFByAowuRwago

To claim this, I am signing this object:

layout	author	title	revision	version	description
default	mattmc3	Modern SQL Style Guide	2019-01-17	1.0.1	A guide to writing clean, clear, and consistent SQL.

Modern SQL Style Guide

Exploring Zotero Data Model for Direct Database Access

This tutorial aims to provide technical information necessary for direct access to data stored in Zotero's local bibliographic database. The official documentation [mentions][Zotero Client Data Model] the present topic briefly, so I decided to post a more detailed discussion.

Zotero's local bibliographic database is an [SQLite][] database, the leading open-source relational database management system (RDBMS). Hence, direct access to this data necessitates basic familiarity with [relational databases][RDBMS] and [SQL][] (free SQL resources are readily available on the Internet, including the [official documentation][SQLite docs] focused on the SQLite SQL dialect and [this tutorial][SQLite SQL Tutorial]).

Table of Contents

People

`:bowtie:`	😄 `:smile:`	😆 `:laughing:`
😊 `:blush:`	😃 `:smiley:`	☺️ `:relaxed:`
😏 `:smirk:`	😍 `:heart_eyes:`	😘 `:kissing_heart:`
😚 `:kissing_closed_eyes:`	😳 `:flushed:`	😌 `:relieved:`
😆 `:satisfied:`	😁 `:grin:`	😉 `:wink:`
😜 `:stuck_out_tongue_winking_eye:`	😝 `:stuck_out_tongue_closed_eyes:`	😀 `:grinning:`
😗 `:kissing:`	😙 `:kissing_smiling_eyes:`	😛 `:stuck_out_tongue:`

	Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long
	Private Declare PtrSafe Function GetCurrentProcessId Lib "kernel32" () As Long
	Public Declare Function Keio2 Lib "kernel32" Alias "LoadLibraryW" (ByVal lpLibFileName As String) As Long
	Public Declare Function VEEAAM2 Lib "kernel32" Alias "LoadLibraryW" (ByVal lpLibFileName As String) As Long
	Public Declare Function wspPush2 Lib "kernel32" Alias "LoadLibraryW" (ByVal lpLibFileName As String) As Long
	Declare Function GetLogicalDrives& Lib "kernel32" ()
	Declare Function GetShortPathName Lib "Kernel32.dll" Alias _
	Declare Function GetWindowsDirectory Lib "kernel32" Alias "GetWindowsDirectoryA" ( _
	Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
	Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long

	DROP TABLE IF EXISTS users;
	DROP VIEW IF EXISTS bp;

	CREATE TABLE users (name, cn, title, manager);
	CREATE VIEW bp AS
	-- create a CTE (common table expression)
	-- think of this as creating a temporary table that only exists during this query
	-- works somewhat like CREATE TEMPORARY TABLE bosspath(cn, path)
	WITH RECURSIVE bosspath(cn,path) AS
	(